El símbolo de promedio

Recientemente, estuve trabajando con una columna que indicaba los promedios de ciertos valores. Dado que el título que utilizaba (así es, "Promedio") era sustancialmente más lago que los valores mostrados, quise reemplazar el título y poner únicamente el símbolo de promedio.

Los lectores seguramente sabrán que dicho símbolo es una "X", con una barra horizontal encima. Como consideré que este era un símbolo especial, ejecuté Insertar - Símbolo... para buscarlo.

Cuál va siendo mi sorpresa al descubrir que este símbolo no existe en este cuadro (gracias, Bill). Así que me puse a buscar otras formas de lograr mi objetivo. Lo primero que se me ocurrió fue escribir el caracter "_" y, en otro renglón de la misma celda (lo cual se logra con Alt + Enter, en modo edición) la letra "X". El resultado fue aceptable.

En su sitio Contextures, Debra Dalgleish propone esta solución:

1. Cambiar la fuente a Symbol.
2. Tipear el caracter de acento grave (`)
3. Cambiar la fuente a la que utilizaremos para la "X" y tipearla.

Aunque no obtuve buenos resultados en pantalla, Debra sostiene que la impresión sí debe verse bién.

Formato condicional avanzado III

Continúa de la nota anterior.

Hasta ahora, solo hemos utilizado la opción "Valor de la celda" de la primera lista desplegable del cuadro Formato condicional. Como recordarán, la segunda opción de esta lista se llama Fórmula. Es con esta opción, con la que se pueden establecer condiciones de formato mucho más complejas y por lo tanto útiles, ya que podremos valernos de cualquier función Excel integrada y/o referirnos a cualquier otra celda del libro.

Supongamos que tenemos una columna con fechas, y queremos aplicar formato a las que tengan una semana o menos de antigüedad. Seleccionamos entonces el rango, pongamos A2:A150, damos Formato - Formato condicional... y seleccionamos la opción Fórmula:

En la sección Fórmula: escribimos:

=A2>(HOY()-7)

Cualquier fórmula que ingresemos en esta sección deberá ser, en realidad, una expresión lógica; es decir, una expresión cuyos únicos resultados posibles sean VERDADERO o FALSO. En nuestro caso, si en la celda A2 tenemos la fecha del día de ayer, la fórmula devolverá VERDADERO. Y si tenemos una fecha de hace más de un mes, devolverá FALSO. Cada vez que el resultado de la fórmula sea VERDADERO, Excel aplicará el formato que hayamos especificado.

Observemos ahora que la referencia que hacemos a la celda A2 es una referencia relativa. Al hacerlo, estamos forzando a Excel a ajustar las referencias al resto de las celdas (recordemos que hemos seleccionado un rango: A2:A150), tal y como sucede cuando copiamos una fórmula normalmente. De esta forma, si revisamos las condiciones de cada celda, veremos que son:

=A2>(HOY()-7)
=A3>(HOY()-7)
=A4>(HOY()-7)
...
=A150>(HOY()-7)

Por el contrario, si el caso fuera comparar todas las celdas contra la fecha de la celda B2, entonces utilizaríamos una referencia absoluta para la celda B2 (y relativa para A2):

=A2>$B$2

De esta forma, Excel ajustará las condiciones como sigue:

=A2>$B$2
=A3>$B$2
=A4>$B$2
...
=A150>$B$2

Para cambiar el tipo de referencia, se puede usar F4 o escribir directamente los signos $ en la fórmula. Téngase presente que la celda utilizada como primer argumento (A2) debe ser la celda activa. De lo contrario, Excel podría ajustar erróneamente las referencias.

Una vez comprendido lo anterior, las condiciones que pueden elaborarse para formatos condicionales solo están limitadas por la imaginación del usuario. Algunos ejemplos:

=A2=MAX($A$2:$A$101). Formatea el valor máximo del rango.

=ESERROR(B2). Esta condición aplica formato a las celdas que contengan resultados de error en el rango. Puede utilizarse para ocultar dichos resultados, aplicando color de fuente blanco.

=CONTAR.SI($A$2:$A$100,A2)>1. Formatea valores duplicados en el rango. Para encontrar los no duplicados, cambiamos el signo > por =.

=ESTEXTO(A2). Señala los valores no numéricos en el rango.

=RESIDUO(FILA(),2). Formatea las filas del rango alternadamente. Por ejemplo, para colorear las filas impares.

=A1>PROMEDIO($A$2:$A$17). Muestra los valores numéricos que están por arriba del promedio del rango.

=RESIDUO(SUBTOTALES(3,$A$1:$A2),2). Formatea alternadamente las filas de un rango filtrado. Nótese la referencia mixta a la celda A2: columna absoluta, fila relativa.

Entre muchos otros. Toca al lector definir los formatos a aplicar.

Link.

Formato condicional avanzado II

Continúa de la nota anterior.

Veamos ahora algunas consideraciones a tomar en cuenta al utilizar formatos condicionales.

Tal como vimos en la nota previa, podemos establecer un máximo de tres condiciones, cada una con su correspondiente formato. Las condiciones que establecimos fueron:

Valor de la celda igual a 0
Valor de la celda menor o igual que 30,000
Valor de la celda mayor o igual que 70,000

Cuando ninguna de las condiciones se cumpla (es decir, cuando el valor de la celda esté entre 30,001 y 69,999) Excel no aplicará ningún formato, manteniendo el formato original de la celda. Ahora bien, si hubiéramos aplicado condiciones incluyentes, es decir, condiciones que pudieran ser cumplidas simultáneamente por un mismo valor, entonces Excel aplicaría el formato de la primera condición satisfecha. Supongamos que trabajamos con las siguientes condiciones:

Valor de la celda igual a 10
Valor de la celda menor o igual que 30
Valor de la celda entre 25 y 40

Si ingresamos el valor 10, entonces se satisfarían las condiciones 1 y 2. Por tanto, Excel aplicará el formato de la condición 1. Si ingresamos el valor 28, entonces se satisfarían las condiciones 2 y 3. Por tanto, Excel aplicará el formato de la condición 2. Debemos determinar cuidadosamente el orden en que establecemos las condiciones, sobre todo si son incluyentes.

Cuando copiamos una celda (o rango) que no contiene formato condicional, y lo pegamos en una celda (o rango) que sí tiene formato condicional, Excel eliminará este sin advertencia alguna. No estaría mal un mensaje de alerta advirtiendo al usuario la posibilidad de perder los formatos. (Gracias Bill). En estos casos, si queremos conservar los formatos condicionales del rango de destino, debemos usar Pegado especial - Valores. Si, por el contrario, queremos conservar los formatos del rango de origen, usamos Pegado especial - Formatos, o simplemete, Pegar, ya que al copiar una celda, Excel copia también su formato condicional. Así pues, hay que tener cuidado al copiar y pegar de o en celdas formateadas.

Para borrar el formato condicional, abrimos el cuadro Formato condicional, y damos clic en el botón Eliminar... Selecionamos las condiciones que queremos eliminar y aceptamos el cuadro. Este cuadro siempre presenta cuadros de selección para tres condiciones, incluso si el rango solo contiene una o dos. Otro error de los genios de Microsoft. Alternativamente, podemos usar Edición - Borrar - Formatos. No obstante, esto eliminará también los formatos no condicionales del rango.

Si queremos seleccionar las celdas a las que hemos aplicado formato condicional, podemos usar el cuadro Ir a Especial. Damos Edición - Ir a... (o F5), clic en Especial...:

Activamos la opción Celdas con formatos condicionales. Tenemos dos opciones: para seleccionar todas las celdas con formatos condicionales, activamos las opción Todos. Para seleccionar solo aquellas celdas con los mismos formatos condicionales de la celda activa, seleccionamos Iguales a celda activa.

Habrá ocasiones en que surja la necesidad de usar referencias a otras hojas. Pero si indicamos la referencia directamente en el cuadro Formato condicional, Excel responderá con un mensaje de error, diciendo que esto es imposible.

Para solucionar esto, simplemente establecemos la referencia en una celda de la hoja en la que aplicaremos el formato, por ejemplo:

=Hoja2!A2

Luego, usamos esta celda en el cuadro Formato condicional. Alternativamente, podemos definir un nombre que haga referencia a la otra hoja, y escribir este nombre en dicho cuadro.

Continuamos en la siguiente nota.

Formato condicional avanzado

El formato condicional es una de las características más útiles con que cuenta Excel. Desafortunadamente no es algo que el usuario normal aproveche al máximo. En efecto, es posible elaborar condiciones de formato que buena parte de los usuarios ni siquiera imagina. Conforme avancemos en este tema se irá haciendo cada vez más evidente el poder de esta característica.

Básicamente, el formato condicional nos permite aplicar determinado formato a las celdas, basado en el contenido de las mismas. Por ejemplo, podemos indicarle a Excel que nos resalte con color rojo celdas con valor igual a cero, con verde aquellas cuyo valor sea menor o igual a 30,000, y con azul las que tengan valores mayores o iguales a 70,000, ningún formato con cualquier otro valor. Este formato cambiará automáticamente según cambie el valor de las celdas analizadas. Así, podremos identificar fácilmente las celdas que cumplan con cualesquiera de las condiciones. Si nos tocó trabajar con una lista de 3,000 datos, es indudable la utilidad que pueden tener los formatos condicionales. Además del color de celda (tramas) podemos especificar color y tipo de borde y estilo y color de fuente, así como el tipo de subrayado.

Procedamos ahora a aplicar los formatos de nuestro ejemplo. Suponiendo que tenemos los siguientes datos:

Comencemos seleccionando el rango al cual aplicaremos el formato condicional (tercera columna). Ejecutamos Formato - Formato condicional... para abrir el siguiente cuadro:

Excel está listo para aceptar la primera condición ("Condición 1"). La primera lista desplegable solo tiene dos valores: "Valor de la celda" y "Fórmula". Seleccionamos el primero. Esta opción sirve para basarnos única y exclusivamente en el contenido de la celda a formatear. La segunda lista (la cual solo veremos si seleccionamos Valor de la celda) sirve para especificar el operador que utilizaremos para evaluar el contenido de la celda. Este operador puede ser uno de los siguientes ocho:

entre (predeterminado)

no está entre

igual a

no igual a

mayor que

menor que

mayor o igual que

menor o igual que

Lógicamente, si seleccionamos el primer o el segundo operadores, deberemos especificar dos valores, los límites entre, mientras que si seleccionamos cualquier otro operador, solo necesitaremos especificar un solo valor. Retomando nuestro ejemplo, seleccionamos "igual a" y en el cuadro de texto a continuación escribimos 0. El siguiente paso es especificar el formato que utilizaremos. Para ello damos clic en el botón Formato..., que nos lleva a este cuadro, versión modificada del cuadro Formato de celdas "normal":

De aquí podemos ver que el formato condicional no permite cambiar ni la protección ni la alineación ni el formato numérico de la celda.

Activamos la pestaña Tramas, seleccionamos el color rojo y damos Aceptar. Si queremos ver en este momento el efecto de esta primera condición, damos clic en Aceptar:

de otra forma damos clic en el botón Agregar>>, con lo cual Excel agrega otro apartado para que especifiquemos la Condición 2:

Para esta segunda condición (siempre según nuestro ejemplo), seleccionamos el operador menor o igual a en la segunda lista desplegable, y ponemos 30,000 en el cuadro de texto. Clic en el botón Formato..., pestaña Tramas y seleccionamos el color verde. Clic en Aceptar.

Solo nos resta especificar la tercera condición. Presionamos Agregar>> y repetimos los pasos anteriores, pero esta vez especificando los valores Valor de la celda, Mayor o igual a, 70,000, color azul, Aceptar:

Volvemos a dar clic en Aceptar y obtenemos:

Si cambiamos los valores de las celdas, veremos que el color de la misma se ajustará automáticamente de acuerdo a las condiciones que especificamos.

Continuamos en la siguiente nota.

La cámara fotográfica

La cámara fotográfica de Excel permite tomar una "fotografía" de un rango de celdas y después insertarla en cualquier otra parte de la hoja o del libro, como si fuera una imagen cualquiera. La principal ventaja de esto es que nuestras fotografías (de 64 megapixeles) serán dinámicas: cualquier cambio que ocurra en el rango de origen se verá reflejado en la imagen.

Para poder utilizar esta característica, primero debemos sacarla del olvido, personalizando las barras de herramientas. Procedemos como sigue:

Ponemos a Excel en modo Personalizar. Para ello, damos Herramientas - Personalizar. En el cuadro de diálogo Personalizar que aparece, vamos a la pestaña Comandos, seleccionamos la categoría Herramientas y buscamos el icono de la cámara (que se llama, así es, "Cámara"):

Finalmente arrastramos el icono hacia una de nuestras Barras de herramientas y damos clic en Cerrar.

Para empezar a tomar instantáneas, seleccionamos el rango que queremos fotografiar. Luego damos clic en el botón Cámara y seleccionamos con el mouse la zona donde queremos colocar la imagen:

Como sucede con cualquier imagen, podemos ajustar su tamaño y formato (clic derecho - Formato de imagen...):

Incluso, podemos girar la imagen. De lo único que debemos cuidarnos es de no colocar la imagen sobre el mismo rango fotografiado, ya que aparecería "eco" en la imagen:

Indudablemente esta es una herramienta útil. Pero sabiamente, Microsoft decidió ocultarla en el fin del mundo.

Gráficos velocímetro II

Continúa de la nota anterior.

Procedamos a la parte difícil, la aguja de nuestro velocímetro. Esto se hace con un gráfico de dispersión XY (que es distinto a un gráfico de líneas). Para lograr que la aguja marque la proporción exacta en el disco (i. e. la proporción de circunferencia correcta), necesitamos convertir el alcance (siguiendo el ejemplo, 88%) en radianes para posteriormente, con las funciones SENO y COS, calcular las coordenadas x y y del punto correspondiente. Luego simplemente agregamos el punto (0, 0) para que Excel los una con la línea correspondiente.

Recordemos los datos a graficar y la tabla auxiliar que ya hemos hecho para la elaboración del disco:

Calculemos ahora cúantos grados debe medir cada punto porcentual. Para ello escribimos en la celda B8 la fórmula:

=(B3-D2)/(D5-D2)*PI()

Resultando 1.837571.

Ahora elaboramos en A10:C12 otra tabla auxiliar:

En B12 escribimos la fórmula:

=-COS(B8), y en C12:

=SENO(B8)

Con esto tenemos ya las coordenadas de los dos puntos que necesitamos: (0, 0) y (0.2636, 0.9646). El siguiente paso es copiar estas dos puntos en la gráfica. Para ello seleccionamos el rango A10:C12, y damos Edición - Copiar (o Ctrl + Insert). Seleccionamos los datos de la gráfica (el "disco") y damos Edición - Pegado especial... (no clic derecho - Pegado especial...). Excel mostrará el siguiente cuadro de diálogo:

Seleccionamos los valores mostrados y damos Aceptar. La gráfica queda:

Evidentemente no es lo que buscábamos. Seleccionamos la segunda serie de datos (la exterior), y damos clic derecho - Tipo de gráfico - Dispersión XY subtipo Dispersión - Aceptar.

Seleccionamos el Eje Y, clic derecho - Formato de ejes... Escala. En valor mínimo escribimos -1, en valor máximo 1 y en Eje de valores (X) cruza en: establecemos 0. Repetimos los mismo con el eje Y:

Damos clic derecho en la nueva serie de datos, Formato de serie de datos... y en Tramas - línea seleccionamos Personalizada y el estilo, color y grosor preferido. En Marcador seleccionamos Ninguno. Aceptar.

Si queremos reducir el tamaño de la aguja entonces aumentamos la escala de ambos ejes. Finalmente eliminamos ambos ejes y sus etiquetas:

Para obtener los colores exactos de Sales Force, necesitamos modificar la paleta de colores de Excel, tema de otra futura nota.

Gráficos velocímetro

Varias empresas utilizan los servicios de Sales Force (por cierto, uno de los peores softwares que he utilizado. Está a años luz de la flexibilidad y facilidad de uso que nos proporciona Excel. Realmente, en cuanto a confiabilidad de la información es absolutamente pésimo. A menos que el usuario tenga un PhD en bases de datos) para gestionar su información. Sales Force utiliza gráficos tipo velocímetro para mostrar alcances:

Excel no soporta de forma predeterminada este tipo de gráfico. Sin embargo, combinando un par de gráficas, podemos obtener algo prácticamente idéntico. Esto fue lo que logré:

Este gráfico es en realidad un gráfico tipo anillo de cuatro secciones (de las cuales una ocupa el 50% del mismo y no tiene color de línea ni de relleno), combinado con un gráfico de dispersión XY (cuyo origen está en el centro del gráfico y que tiene dos puntos, que al unirlos con una línea, forman la "aguja" del velocímetro), con los formatos adecuados. Seguimos los siguientes pasos:

Comencemos por elaborar la escala (el "disco") del velocímetro. Supongamos que queremos graficar los siguientes datos:

Si deseamos que la escala vaya de 0 a 150%, con divisiones de 50%, elaboramos la siguiente tabla auxiliar:

Es decir, del lado izquierdo tenemos las etiquetas que llevarán los segmentos, y, del lado derecho, los valores proporcionales de cada segmento. Como cada uno de los primeros tres segmentos miden lo mismo (50%), ponemos el mismo valor (0.5, puede ser cualquier otro) a cada uno. El cuarto segmento, como ocupará la mitad del "disco", tiene un valor igual a la suma de los tres primeros (1.5).

Seleccionamos la tabla e iniciamos el Asistente para gráficos. En Tipo de gráfico, seleccionamos Anillos, subtipo Anillos. Clic dos veces en Siguiente... para ir directamente al paso 3. Aquí, en la pestaña Título no ponemos nada, en Leyenda desactivamos Mostrar leyenda y en Rótulos de datos activamos la opción Categoría (sugeridos). Damos clic en Finalizar obteniendo el siguiente gráfico:

A continuación, damos clic derecho en el gráfico, Formato de Serie de datos... y vamos a la pestaña Opciones. En Ángulo del primer sector, establecemos 270, Aceptar.

Finalmente, seleccionamos la sección inferior del gráfico (150%), damos clic derecho, Formato de punto de datos... En la pestaña Tramas, en Borde y en Área seleccionamos ninguno. Movemos las etiquetas, y les damos formato. Quedando:

Continuamos en la siguiente entrada.