Formato condicional y precisión de cálculo de Excel

En el pasado he tocado el tema de la precisión de cálculo en Excel y los problemas que puede generar. Para evitar una ola de pánico entre mis lectores señalemos que en la práctica estos problemas aparecen muy raramente, pero que existen…existen.

El caso que voy a presentar está basado en una consulta de una amiga, investigadora en el área de la biología. Supongamos esta tabla de mediciones de lapsos

Como puede observarse, en la columna “Lapso transcurrido” se ha calculado cuántos minutos transcurrieron entre una observación y la anterior. Queremos controlar que no hayan transcurrido más de 15 minutos entre cada observación, por lo que aplicamos formato condicional con la siguiente fórmula

Al hacerlo surge un problema: a pesar que todos los intervalos son de 15 minutos, como podemos controlar a ojo desnudo, Excel marca ciertas filas como si el intervalo hubiera excedido el tiempo permitido

El problema reside en la precisión de cálculo de Excel (problema del floating point que no desarrollaré aquí, pero pueden consultar este artículo de la base de conocimientos de Microsoft). Para hacer visible el problema voy a copiar los valores de la columna C a la columna D aplicando el formato numérico General

Aparentemente todos los valores son idénticos (15/1440 = 0.10416667), pero si agregamos más decimales a la presentación en pantalla de los números vemos lo siguiente

¡Los números, que expresan el intervalo en formato decimal, son distintos a pesar que los intervalos son idénticos! Como Excel utiliza la forma decimal para hacer los cálculosde tiempo y fechas, nuestro Formato Condicional falla ( los marcados con la X, son un poco más grandes y por lo tanto son señalados por el formato condicional).

Una solución posible es reducir la precisión de cálculo a 10 lugares decimales usando REDONDEAR

Otra solución, más precisa, es usar la función MINUTO para expresar el intervalo como número entero

y en el formato condicional usar =$C4>15

Formato condicional con iconos - Algunos trucos

Supongamos una tabla que muestra las ventas de cada mes. Podemos aplicar formato condicional con iconos para enfatizar si las ventas del mes fueron mayores o menores que la del mes anterior

En la tabla vemos con claridad que las ventas de febrero superaron las de enero y que en abril se vendió menos que en marzo.
Mis astutos lectores ya habrán descubierto que los iconos no están en la columa C, donde aparecen los datos de ventas, sino en la columna contigua D.
Si seleccionamos la celda D4, veremos que ésta contiene la fórmula ="C4-C3"

Sobre el resultado de la celda aplicamos el formato condicional

Hemos aplicado "Mostrar icono únicamente", para ocultar los resultados de las celda en la columna D y así crear la ilusión que los iconos están en la columna C.

Una variante interesante es si queremos que sólo aparezcan las flechas rojas en los meses en que las ventas fueron menores que en el anterior. En ese caso usaremos una opción poco utilizada del formato condicional.

Abrimos las definiciones del formato condicional del ejemplo anterior y agregamos una nueva regla

Como puede apreciarse usamos la opción "Utilice una fórmula..." y no definimos formato. Al apretar "Aceptar" veremos este cuadro

Como ven hemos señalado la opción "Detener si es verdad" (también nos aseguramos que esta regla aparezca en primer lugar). Al apretar "Aceptar" obtenemos

Lamentablemente la opción "Utilice una fórmula..." no tiene la opción "Mostrar icono únicamente", lo cual es bastante obvio (esta opción no tiene iconos). Para superar este inconveniente definimos el color de la fuente en las celdas de manera que coincida con el fondo

No, Excel no puede ayudarte a ganar la lotería

A lo largo de la vida de este blog he recibido no pocas consultas relacionadas con todo tipo de cálculos para acertar los números de la lotería. Digamoslo claramente: no, no se puede predecir el resultado de los sorteos de la lotería.
No sólo no se pueden predecir los resultados, sino que también las posibilidades ganar el primer premio son prácticamente inexistentes. Supongamos, por ejemplo, una loteria donde hay que acertar 6 de un conjunto de 49 números. Para calcular la probabilildad de acertar la cambinación ganadora, si n es el conjunto de 49 números que participan en el sorteo y k es cada una de las combinaciones posibles de 6 números, sin repeticiones, la expresión

n!
-----------------
k! * (n-k)!

donde el símbolo ! indica la operación factorial. Excel nos permite realizar el cálculo con facilidad usando la función COMBINAT(). La expresión =1/COMBINAT(n,k) nos da la probabilidad de que una combinación dada de seis números salga en el sorteo

Como puede apreciarse hay casi 14 millones de combinaciones posibles, es decir una probabilidad de 0.00000007 que nuestros números formen la combinación ganadora.

Sin embargo cada tanto alguién gana y se convierte enn un nuevo millonario. Por lo que seguiremos jugando y soñando qué hacer cuando la diosa fortuna golpee a nuestra puerta.
Y si bien Excel no nos puede ayudar a ganar la lotería, si puede ayudarnos a controlar si hemos obtenido algún premio.
Supongamos que nuestra lotería premia combinaciones de por lo menos 3 números ganadores. Supongamos también que siempre apostamos a la misma combinación y supongamos también que hemos jugado esta combinación durante diez sorteos y ahora queremos comprobar si hemos tenido suerte. Para hacerlo usaremos Formato Condicional y también SUMAPRODUCTO. La hoja Excel que aparece aquí abajo es interactiva y hay que desplazar la hoja hacia la derecha para ver las columnas K y L (también puede descargarse el archivo usando el icono en el ángulo inferior derecho).



Para señalar con un color de fondo los números sorteados que coinciden con "Mis_números" usamos format condicional con la opción Fórmula

Usamos ESNUMERO combinada con COINCIDIR para obtener resultado VERDADERO si el número evaluado coincide con alguno de los números del rango "Mis_numeros" lo hace que se aplique el formato.

En la columna L contamos la cantidad de aciertos logrados en cada sorteo con la fórmula

=SUMAPRODUCTO(--(E3:J3=Mis_numeros))

Usamos el doble guión para que Excel evalue los valores VERDADERO como 1 y los FALSO como 0.
La regla del formato condicional en la columna L es

Formato condicional en gráficos de Excel - Tercera nota

En las notas anteriores del tema mostré códigos para generar efectos de formato condicional en gráficos de Excel. En la primera publiqué un código para crear una gama de colores en gráficos con una única serie. En la segunda nota, ampliamos el código para gráficos con más de una serie.

El código de esta tercera nota permite señalar máximos y/o mínimos en gráficos de Excel. Los códigos anteriores se basan en los valores del eje de las categorías. Es decir, suponen que los valores de las series están ordenados, de mayor a menor o de menor a mayor, en el eje de la X.

El código que publico en esta nota, evalúa los puntos de la serie elegida, determina los puntos con los valores máximos y mínimos, y los rellena con el color elegido por el usuario. Éste, a su vez, puede elegir señalar sólo el máximo, sólo el mínimo o ambos.

Siguiendo con nuestro ejemplo

al seleccionar el gráfico y apretar el botón Aplicar, se abre un formulario donde podemos elegir qué serie formar,

 qué señalar y que color darle a nuestras elecciones (rojo, verde o azul)

y al elegir el Verde para el máximos obtenemos este resultado

El cuaderno con el ejemplo y el código puede descargarse aquí.

Formato condicional en gráficos de Excel - segunda nota

En la primer nota del tema mostré una macro para aplicar un formato condicional sencillo a un gráfico de Excel. Esa macro aplica el formato siempre a los puntos de la primer serie de datos del gráfico. Es decir, que si tenemos más de una serie de datos en nuestro gráfico, no podemos elegir a qué serie aplicar el formato.

En esta nota mostraré una macro que nos permite elegir la serie de datos a la cual queremos aplicar el formato. Recordemos que estamos hablando de un formato muy sencillo (una gama de colores combinado de izquierda a derecha) y que en notas posteriores trataremos el tema de señalar máximos y mínimos de una serie y el formato de acuerdo al valor del punto de la serie.

Empecemos por agregar una serie de datos a nuestro ejemplo que ahora muestra los saldos de las cuentas para los años 2011 y 2012

Nuestra macro ahora evalúa cuantas series hay en el gráfico. Si hay más de una serie, se abre un formulario que nos permite elegir la serie a la cual queremos aplicar el formato

Una vez elegida la serie, elegimos los parámetros del formato (ver la explicación en la nota anterior)

y obtenemos el resultado buscado

El cuaderno con el ejemplo y los códigos puede descargarse aquí. También pueden descargar este complemento e instalarlo en la máquina. Luego pueden crear un icono en la barra de acceso rápido para poder utilizarlo con comodidad en todo cuaderno.

Formato condicional en gráficos de Excel - primera nota

Excel no tiene un método incorporado para aplicar formato condicional en gráficos. En el pasado mostramos técnicas que nos permiten aplicar formatos dinámicos en gráficos. Pero formato condicional va más allá de eso.

Supongamos este gráfico, donde los puntos de la serie (cantidad de cuentas según saldo) están ordenados según la categoría (monto del saldo)

Ahora supongamos que queremos aplicar algo similar al formato condicional de manera que el color de las columnas donde las columnas tengan una gama de colores que vayan del del rojo para el primer punto (los saldos más negativos) al verde (los saldos más positivos). Es decir, esto:

Una posibilidad es hacerlo manualmente. El método manual tiene dos inconvenientes:

• Es tedioso y cansador si se trata de muchos puntos
• La elección de los colores de la gama es dificultosa

Como con toda tarea cansadora, aburrida y dificultosa, la solución es desarrollar una macro.

Mi solución se basa en la función RGB del Vba.

Algunas palabras sobre el tema. RGB significa (en inglés) Rojo (Red), Verde (Green) y Azul (Blue). La combinación de estos colores primarios de la luz permite generar una amplia gama de colores. Cada gama de estos colores está representado por un número que va de 0 (ausencia del color) a 255. La función RGB tiene tres argumentos, uno para cada color primario. Esta tabla muestra algunos ejemplos:

El cuaderno se puede descargar apretando el símbolo de Excel en la barra inferior de la imagen. Cambiando los números en el rango de valores (cualquier número entero entre 0 y 255) poderá verse en la columna "Color" el color resultante.

Este video muestra como RGB genera colores a medida que vamos cambiando aleatoriamente los valores de los argumentos



En esta primera nota mostraré una macro sencilla para formar los puntos de una serie en el gráfico con una gama de colores (rojo a verde, azul a rojo, etc.). Esta macro puede usarse para el  caso de un gráfico con una única serie de datos.
En las próximas notas veremos como transformar la macro para gráficos con más de una serie de datos y como adaptarla para señalar máximos y mínimos de una serie.

En esta macro hacemos los siguiente:

1. Evaluamos si el objeto activo es un gráfico, en caso contrario aparecerá una advertencia pidiendo al usuario que elija un gráfico.
2. Aparecerá un formulario donde el usuario debe señalar un valor inicial para cada color. Las posibilidades son "255" (empieza con este valor y se va reduciendo hasta cero); "0" (empieza en cero y va aumentando hasta 255); "255-K" (constante 255) y "0-K" (constante 0).
3. Calculamos el "salto" de cada color dividiendo 256 por el número de puntos de la serie. Con este factor aumentamos o disminuimos el valor inicial del color en intervalos constantes. En caso de 255-K o 0-K, el valor es contante.
4. Con un loop For...Next vamos generando los valores de la función RGB para cada punto y lo aplicamos al punto de la serie.

Este modelo consiste en una macro, un userform (formulario) con los controles para la elección de los colores y sus correspondientes eventos. Este video muestra como funciona la macro.



El cuaderno con el ejemplo y los códigos puede descargarse aquí. También pueden descargar este complemento e instalarlo en la máquina. Luego pueden crear un icono en la barra de acceso rápido para poder utilizarlo con comodidad en todo cuaderno.

Como señalo más arriba, en las próximas notas subire un código más completo (gráficos con más de una serie y posibilidad de señalar máximos y mínimos).

Eliminar "en blanco" en tablas dinámicas

Al resumir datos con una tabla dinámica, si falta algún valor en algunos de los campos de fila, Excel mostrará el texto "(en blanco)" en la celda correspondiente. Por ejemplo, si construimos un reporte dinámico a partir de esta tabla

al poner el campo "Responsable" en el área de filas, veremos esto

Muchos usuarios, yo entre ellos, prefieren que la celda permanezca en blanco de manera que sea fácil detectar las filas donde no se ha definido el responsable.

Excel no tiene un método nativo para definir esta situación. Si bien en el menú "Opciones de tabla dinámica-Diseño y formato" existe la alternativa de definir valores para celdas vacías

esta opción se aplica sólo al área de los datos.

Veamos cuales son nuestras posibilidades:

Reemplazar "(en blanco)" con un espacio: seleccionamos una de las celdas vacías en la tabla dinámica e introducimos un espacio. Hacemos esto porque Excel no permite introducir valores nulos como elementos en el campo de filas o columnas.
Podemos hacer lo mismo usando Buscar y Reemplazar (Ctrl+L)

Contrariamente a lo que podría esperarse, al actualizar la tabla no hace falta volver a aplicar el método. Aún si introducimos una nueva fila en la base de datos sin responsable, la celda correspondiente en la tabla dinámica aparecerá en blanco.

Formato condicional: podemos usar la opción "Fórmula" para detectar las celdas donde aparece el texto "(en blanco)" y cambiar el color de la fuente al color del fondo para volver "invisible" el texto

Formato condicional para resaltar máximos y mínimos según criterios

Hace casi un mes que no publico nada en el blog. No por pereza o desidia sino por una inesperada carga de trabajo. Cuento con la comprensión de los lectores que han quedado sin respuesta a sus consultas.

Uno de mis lectores me consulta cómo usar formato condicional para resaltar mínimos (o máximos). Supongamos una lista de precios donde varios productos aparecen con precios distintos

Cómo extraer los máximos y mínimos según criterios (producto, en nuestro caso), ya hemos visto en la nota que publiqué en abril de año pasado. En nuestro ejemplo la solución sería

Las fórmulas para calcular los máximos y mínimos son matriciales (se introducen apretando simultáneamente las teclas Ctrl-Mayúsculas-Enter):

• para los máximos: =MAX(($A$2:$A$13=D2)*$B$2:$B$13)

• para los mínimos: =MIN(SI(($A$2:$A$13=D2),$B$2:$B$13))

Podemos usar las mismas fórmulas para aplicar el formato condicional. Por ejemplo, para resaltar los mínimos en la lista de precios

En formato condicional no hace falta introducir la fórmula en forma matricial.

Análisis de encuestas con Formato Condicional

Las mejoras e innovaciones introducidas  han convertido a Formato Condicional en el nuevo Excel (2007/2010) en una aun más excelente herramienta para analizar datos. En esta nota mostraré como usar Formato Condicional para resumir en forma eficiente y sencilla los resultados de una encuesta.

Hace unas semanas recibí una consulta sobre como resumir concisamente una encuesta sobre el nivel de servicio de ciertos departamentos de una empresa. La encuesta fue montada usando la herramienta Formularios de la aplicación Documentos de Google.

Google va acumulando los resultados en una hoja de cálculo que puede descargarse a Excel. Los encuestados debían calificar en una escala de 0 (pésimo) a 5 (excelente) el funcionamiento de ciertos departamentos de servicios de la empresa.

Veremos como podemos crear una “termo-mapa” para mostrar en forma concisa y práctica los resultados de la encuesta.

Empezamos por convertir la matriz de los resultados en una tabla (Insertar-Tabla)

El próximo paso es crear una tabla dinámica, que ubicamos en una nueva hoja. Personalmente no me agrada el diseño por defecto de las tablas dinámicas en el nuevo Excel, por lo que uso Opciones de tablas-Mostrar para definir el diseño clásico de tabla dinámica que también permite arrastrar los campos a la cuadrícula

Arrastramos le campo de departamentos al área de las filas y cada uno de los campos de las preguntas al área de los valores. Resumimos los valores con la función Promedio y ajustamos el formato de los números.

Quitamos “Promedio de” en los encabezados de la columna para hacerlos más legibles.

Seleccionamos el área de valores y aplicamos Formato Condicional –Escala de Color

El resultado habla por si mismo.

Cuanto más verde es el fondo, mejor la calificación y, por lo contrario, cuando más rojo peor la calificación. Claramente podemos ver que el mejor departamento en la calificación general es Recursos Humanos. El nivel de servicio del Taller es claramente deplorable!

Coordinar horarios con Excel

Esta nota viene a colación de mi reencuentro con un entrañable amigo de la juventud. Nos unen maravillosos recuerdos de la juventud pero nos separan 15 husos horarios.

Después de intercambiar nuestras direcciones de Skype empezamos a coordinar el día y la hora para comunicarnos. Fijamos el próximo fin de semana, pero ¿a qué hora? Aquí es donde Excel puede darnos una buena ayuda.

Yo resido en la Ciudad 1 y mi amigo en la Ciudad 2. La diferencia horaria es de 15 horas y el día elegido para conversar es el sábado 18 de febrero. Las horas activas, es decir, cuando nuestro estado mental permite una conversación más o menos coherente, son entre las 9 de la mañana y las 10 de la noche (cada uno en su ciudad). Este es el modelo que construí en Excel para ver las horas en que ambos coincidimos

En el cuadro de definiciones (el rango E2:F6) ponemos la fecha, la diferencia horaria, y las horas de comienzo y final del horario de actividad y en el rango H2:I25 vemos las horas en que coincidimos con fondo verde y texto en negrita.

¿Cómo funciona el modelo?

Las celdas de la tabla de horarios (el rango E2:F6 H2:I25) contienen esta fórmula:

=$F$2+((FILA()-2)/24) para Ciudad 1

=$F$2+((FILA()-2+$F$3)/24) para Ciudad 2

donde $F$3 es la diferencia horaria entre las ciudades

La expresión (FILA()-2)/24 calcula la hora del día en saltos de 1 hora, usando el valor de la función FILA y dividiendo por 24 (ese es el método que usa Excel para los cálculos de horas). Esta fórmula nos permite pasar al día siguiente cuando se cumplen las primeras 24 horas.

Para mostrar el resultado con el día de la semana usamos formato personalizado:

Para poner el fondo verde y el texto en negrita usamos formato condicional. La técnica que propongo usa columnas auxiliares ocultas, como seguramente mis perspicaces lectores habran ya notado (en la primer imagen la primer columna visible es la columna D).

Como puede verse, la fórmula usada es muy sencilla: =$C2=1. Veamos qué hay en C2

En A2 ponemos la fórmula:

=Y(HORA(H2)>=HORA($F$5);HORA(H2)<=HORA($F$6))

El resultado es verdadero si la hora de la fecha evaluada en la columna de la ciudad 1 cae dentro del rango establecido entre F5 y F6; lo mismo con la fórmula en la celda B2 para la ciudad 2

=Y(HORA(I2)>=HORA($F$5);HORA(I2)<=HORA($F$6))

En la columna C multiplicamos ambos resultados. Como se trata de expresiones lógicas, dan 1 si ambos resultados son VERDADERO y 0 si alguno de los resultados es FALSO.

Si bien podemos crear una fórmula compleja que combine las tres fórmulas y usarla directamente en el formato condicional, esta técnica tiene en mi opinión la ventaja de exponer en forma más clara la forma del cálculo.

Formato personalizado numérico condicional en Excel

En los albores de este blog ya había tocado el del formato personalizado de números. Vuelvo sobre el tema debido a la consulta que puso uno de mis lectores en un comentario de la entrada:

tengo unos registros en donde guardo la hora del día, pero quiero que me ponga madrugada si la hora es de 0 a las 5, mañana si es de 6 a 12, tarde de 13 a 19, y noche de 20 a 24, ¿se podrá con el formato personalizado?

En formato personalizado de celda de Excel existe la posibilidad de usar formatos condicionales. El método es muy elemental y admite sólo tres condiciones, de las cuales dos son explícitas y la tercera implícita. Las condiciones, o criterios, son comparaciones numéricas sencillas.

Si la consulta de mi estimado lector se limitara a tres condiciones, digamos mañana, tarde y noche, se podría hacer. Para el caso que queramos que aparezca "mañana" si la hora es anterior o igual a las 12:00, "tarde" si cae entre las 12:00 y las 18:00 y noche para el resto, la sintaxis es la siguiente

[<=0,5]"Mañana";[>0,75]"Noche";"Tarde"

Las dos condiciones explícitas las ponemos entre [] y la implícita es "todo el resto".

El valor 0,5 resulta de dividir 12 por 24; el valor 0,75 de dividir 18 por 24. Como ya explicamos en el pasado, en Excel las horas son números que resultan de dividir la hora por el total de horas del día (24).

En esta imagen podemos ver las horas 08:00 y 16:00 con tres formatos distintos: hora, general y personalizado condicional

Dado que el formato numérico condicional está limitado a tres criterios, no podemos resolver la consulta de mi lector con esta herramienta. La alternativa es usar formato condicional, que a partir de Excel 2007 no está limitado a tres condiciones. En el caso de la consulta los criterios son:

Usaremos formato condicional con la opción Fórmula, definiendo una fórmula para cada uno de los criterios. Para evitar problemas de precisión, usaremos la función HORANUMERO para calcular los números de series de las horas

El cuaderno con el ejemplo se puede descargar aquí.