Formato condicional con iconos en Excel 2007

Sin lugar a dudas, las posibilidades del formato condicional en Excel 2007 han sido ampliadas enormemente. Sin embargo, algunas de las posibilidades están lejos de ser intuitivas.

Por ejemplo, el uso de las colecciones de iconos. Supongamos, como me plantea un lector, que tenemos una lista de fechas de vencimientos. Queremos sacar partido de los semáforos del formato condicional de Excel 2007 de manera que si la fecha del vencimiento cae más de 30 días de la fecha corriente aparezca un semáforo verde; si la fecha está dentro de los 30 días un semáforo amarillo y si la fecha ha vencido, un semáforo rojo. Es decir, esto:




Empecemos por agregar una columna auxiliar que muestre la diferencia en días entre la fecha corriente y la fecha de vencimiento



Para poner los semáforos en esta columna abrimos el menú de Formato Condicional –Nueva regla, elegimos el conjunto de iconos (semáforo con recuadro), en Tipo elegimos Número, para el semáforo verde ponemos “>30” y para el amarillo “>=0”. Esto hace que el rojo quede para los valores inferiores a 0. Como dije al principio, no muy intuitivo que digamos



El resultado será



Una posibilidad interesante es editar la regla y marcar la opción “Mostrar icono únicamente”



Pero si queremos prescindir de la columna auxiliar tendremos que definir las reglas del formato condicional de esta manera



EL primer cambio es que definimos el tipo de regla como Fórmula; en Valor creamos una referencia a la celda que contiene la fecha corriente ($B$1 en nuestro caso) + 30 para el semáforo verde y $B$1 para el amarillo (el rojo queda definido por defecto)

Technorati Tags: MS Excel

Gráficos Intercambiables en Excel

En mi blog sobre gráficos y presentación de datos acabo de publicar una nota mostrando cómo intercambiar dinámicamente el tipo de gráfico de una serie de datos, sin usar macros.

La idea es poner una lista desplegable y que al elegir el tipo de gráfico, éste cambie automáticamente



Aquí se puede descargar el archivo del ejemplo.

Technorati Tags: MS Excel

Más sobre formato personalizado de números en Excel

A partir de la nota anterior sobre formato personalizado de números en Excel (también pueden ver esta otra nota), un lector me plantea la siguiente consulta: “como puedo dar formato a una celda tal que si el número no es entero muestre siempre cinco decimales y si el número es entero, ninguno”.

Nuestro lector intento usar el siguiente formato personalizado "#,##0.#####”, el cual tiene el inconveniente de mostrar el separador de miles (coma o punto, dependiendo de las definiciones regionales) aún cuando el número es entero, como puede verse en este ejemplo

Si usamos Excel 2007 la solución del problema es bien sencilla. En lugar de formato personalizado de números usamos Formato Condicional creando dos reglas, una para cuando el número es entero y otra para cuando no lo es.

Suponiendo que el rango de números que queremos formar está en la columna C, para evaluar si el número es entero o no usamos la formula

=C1-TRUNCAR(C1)

Esta fórmula resta del número la parte entera; si el número es entero el resultado es 0. De esta manera podemos crear dos reglas para el formato condicional de las celdas

=(C1-truncar(C1))<>0 que VERDADERO si el número no es entero

=(C1-truncar(C1))=0 que da VERDADERO si el número es entero

Como pueden ver, Excel 2007 tiene una pestaña para dar formato a números en Formato condicional, lo que no existe en las versiones anteriores.

El resultado ahora es el buscado

Con las versiones de Excel 97-2003, no podemos usar formato condicional para formar números

Tampoco podemos usar un formato personalizado condicional, ya que éste no acepta fórmulas. El único camino que nos queda por delante es programar un evento.

Suponiendo que el rango a formar sea la columna C, en el módulo de Vba para la hoja ponemos este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngToFormat As Range, test

On Error Resume Next
Set rngToFormat = [C:C]
test = Target.Value - Int(Target)


If Union(Target, rngToFormat).Address = rngToFormat.Address Then
Select Case test
Case 0
Target.NumberFormat = "General"
Case Else
Target.NumberFormat = "#,##0.00000"
End Select
End If
On Error GoTo 0

End Sub


Esta macro se dispara cada vez que introducimos un valor en alguna de las celdas de la columna C; evalúa si el número es entero o no y aplica el formato correspondiente.

Technorati Tags: MS Excel

Indicadores de color con formato condicional (Excel 2003)

En la nota sobre formato condicional en Excel 2007 mostrábamos como crear este efecto

En cada celda de la columna C aparece una fecha cuyo sentido y color resalta el resultado de comparar los resultados con el plan.

Un compañero de trabajo me preguntaba hoy si se puede lograr el mismo efecto en Excel 2003. Mi respuesta es que si, se puede lograr algo parecido y es lo que voy a mostrar en esta nota.
Lo que podemos hacer con facilidad en Excel 2003 es poner un fondo de color de acuerdo al resultado, pero lo que queremos es mostrar las flechas como en Excel 2007.

Empezamos por crear una lista con las figuras de los indicadores. Para esto usamos el menú Insertar-Símbolos

Elegimos los triángulos del subconjunto Figuras Geométricas como sustitutos de las flechas (otra alternativa sería elegir el subconjunto Flechas). Elegimos el primer triángulo de la izquierda y lo ponemos en una celda



Después de introducir los restantes símbolos nuestro cuadro se verá así:



Como pueden ver, los símbolos aparecen en negro. Los colores serán determinados luego con Formato Condicional. Usamos la opción Fórmula y definimos tres condiciones



Es importante notar que al momento de definir las condiciones del formato condicional la celda activa debe ser D2 y la referencia en la fórmula debe ser relativa (sin los símbolos $).

Nuestro próximo paso es poner una fórmula en el rango E2:E6 que dé como resultado el triángulo correspondiente al valor de la celda respectiva en el rango D2:D6. En la celda E2 ponemos esta fórmula

=SI(D2>0,$A$10,SI(D2<0,$a$11,$a$12))>que copiamos al resto del rango. El color de las flechas será determinado por el formato condicional

Para hacer el efecto un poco más parecido al que logramos con Excel2007 podemos poner la columna de las flechas a la izquierda de la columna Control. También podemos quitar las líneas de división, combinar las celdas D1 y C1 para crear la ilusión de que se trata de una única columna y así obtener esta resultado



Otra posibilidad es usar las flechas de Windings, como éstas



O estas otras de Windings3

Technorati Tags: MS Excel

Totales acumulados en tablas dinámicas de Excel.

Crear totales acumulados en tablas dinámicas de Excel es fácil, como mostraremos en esta nota.
Supongamos estos datos de ventas de dos sucursales



A partir de estos datos construimos una tabla dinámica normal



Para lograr que la tabla muestre los totales acumulados, abrimos el menú de configuración de campo, apretamos el botón Opciones, en Mostrar datos como elegimos Total en y en Campo base elegimos Mes



La tabla dinámica muestra ahora el acumulado por mes



Para mostrar el acumulado por Sucursal tenemos que agregar este campo a la tabla. Volvamos al total normal y agreguemos el campo Sucursal en el área de columnas



Ahora al definir Mostrar datos como Total en y Campo base Mes, veremos



Si cambiamos la base a Sucursal, veremos



Ahora los acumulados son por fila, a lo largo del mes.

También podemos arrastrar el campo Sucursal al área de filas, a la izquierda de Mes y poniendo la base a Mes veremos el acumulado por mes de cada sucursal

Technorati Tags: MS Excel

La función SUMAPRODUCTO con fechas

La función SUMAPRODUCTO nos permite, entre otras cosas, contar y sumar con más de una condición. Sin embargo existe un problema cuando queremos usar fechas como criterio.
Supongamos esta tabla con dos columnas: criterio 1 contiene los valores "si" o "no" y criterio 2 contiene fechas que van del 01/01/2009 al 10/01/2009



Queremos contar cuantas celdas en el rango A2:A11 cumplen las siguientes tres condiciones: el valor de la celda es "si" y la fecha de la celda correspondiente en la columna B es mayor del 05/01/2009 y menor del 10/01/2009

Si usamos esta fórmula

=SUMAPRODUCTO((A2:A11="si")*(B2:B11>05/01/2009)*(B2:B11<10/01/2009))

veremos que el resultado es 0 y que Excel a modificado la fórmula de esta manera

=SUMAPRODUCTO((A2:A11="si")*(B2:B11>5/1/2009)*(B2:B11<10/1/2009))

No sólo lo ceros han desaparecido sino que Excel interpreta, por ejemplo, 5/1/2009 como operación aritmética cuyo resultado es 0.00248880039820806.

Para obligar a Excel a considerar las fechas como criterios tenemos tres posibilidades:

1 - usar el número de serie de la fecha. Por ejemplo, en lugar de B2:B11>05/01/2009 usaremos B2:B11> 39818. Para saber cuál es el número de serie correspondiente a una fecha podemos seleccionar la celda con la fecha, abrir el menú Formato de Celda y apuntar a Números a la opción General



2 - usar la función FECHA. En nuestro ejemplo la función sería

=SUMAPRODUCTO((A2:A11="si")*(B2:B11>FECHA(2009,1,5))*(B2:B11<FECHA(2009,1,10)))


3 - usar referencias a celdas que contengan las fechas que queremos usar como criterio. En nuestro ejemplo, si la fecha del primer criterio la ponemos en la celda C1 y la segunda fecha en la celda C2, la fórmula sería

=SUMAPRODUCTO((A2:A11="si")*(B2:B11>C1)*(B2:B11<C2))


Esta última opción es la preferible ya que nos permite determinar los criterios dinámicamente sin necesidad de modificar la fórmula.

Technorati Tags: MS Excel

Determinar la última fecha de un acontecimiento.

Un ejemplo similar, y tal vez más práctico, del problema de determinar la posición de un elemento en un vector es la consulta que me dirige un lector de México.
Dada una lista de clientes y fechas de contacto, queremos determinar cuál es la fecha más reciente en la cual hemos contactado a cada uno de los clientes.
Podemos resolver este problema de dos maneras: con fórmulas matriciales y con tablas dinámicas.

Supongamos esta tabla

Solución con fórmulas matriciales.

Empezamos creando una lista donde en la primer columna ponemos los nombres de los clientes



El segundo paso consiste en definir dos nombres: clientes que contiene el rango A2:A15 y fechas que contiene el rango B2:B15

En la celda D2 ponemos esta formula matricial

=MAX((clientes=D2)*fechas)

Como toda fórmula matricial la introducimos apretando simultáneamente Ctrl+Mayúsculas+Enter. Ahora copiamos la fórmula a las restantes celdas del rango



Para explicar como funciona esta fórmula matricial, veamos una solución que usaría columnas auxiliares.



En la primer columna auxiliar comparamos si el cliente de la fila coincide con el cliente que estamos buscando (en nuestro caso Cliente 1).



En la segunda columna auxiliar simplemente multiplicamos la columna de las fechas por la primer columna auxiliar. Dado que Excel interpreta VERDADERO como el valor 1 y FALSO como 0, obtenemos fechas sólo para el cliente 1. Ahora es evidente que la fórmula =MAX(D2:D15) nos dará la última fecha del Cliente 1.
Al usar nuestra fórmula matricial hacemos lo mismo pero evaluando cada uno de los clientes de acuerdo a la fila en la tabla de resultados.

Esta solución tiene dos inconvenientes:
1 - si se trata de una hoja con una gran cantidad de datos, el recalculado de la hoja puede ser muy lento.
2 - Cada vez que agreguemos un cliente a nuestra base de datos, tendremos que modificar la tabla de resultados agregando el cliente y copiando la fórmula.

Estos inconveniente se pueden superar usando tablas dinámicas en lugar de fórmulas matriciales.

Solución con tablas dinámicas.

Seleccionamos la celda A1 (o cualquier celda de la lista) y creamos una tabla dinámica



Arrastramos el campo Clientes al área de filas y el campo Fechas al área de datos



Activamos el menú de configuración de campo para el área de datos y hacemos estos cambios:

# - elegimos resumir por Máx



# elegimos el formato de fecha para los valores



Como podemos ver obtenemos el mismo resultado como con las fórmulas matriciales



Por supuesto, la fila Total General no tiene ningún sentido, por lo cual quitamos la señal de la opción Totales generales de columnas en el menú Opciones de tabal dinámica.



Esta solución es más eficiente que la anterior. Podemos usar rangos dinámicos para que las nuevas entradas sean procesadas automáticamente al actualizar la tabla dinámica.

El cuaderno con el ejemplo se puede descargar aquí

Technorati Tags: MS Excel

Gráficos de distribución normal (Gauss) con Excel.

Hace unos días me preguntaba un lector cómo se puede crear con Excel un gráfico tipo campana de Gauss para representar un informe de desviaciones estándar.
Empecemos por citar a Wikipedia:

…la llamada distribución normal…[se utiliza] como modelo en multitud de variables (peso, altura, calificaciones...), en cuya distribución los valores más usuales se agrupan en torno a uno central y los valores extremos son escasos.

La representación gráfica de la distribución normal se ve así:



Lo que nos dice este gráfico es que en la distribución normal los miembros de la muestra se concentran alrededor del promedio y son más escasos a medida que nos vamos a los extremos. En la distribución normal cerca del 68% de los miembros de la muestra se ubican a en el área definida por un desvío estándar en cada sentido

En esta nota veremos cómo crear gráficos de distribuciones normales con las herramientas que nos provee Excel.
Para definir una distribución normal necesitamos una colección de datos de los cuales calcularemos el promedio y el desvío estándar. Por ejemplo, para crear este gráfico



hemos definido el desvío estándar en la celda B1 (1) y el promedio en la celda B2 (0), en el rango A5:A29 hemos puesto una serie de datos que va de -3 a +3, es decir de menos tres desvíos estándar a más tres desvíos estándar



Los valores en el rango B5:B29 están calculados con la fórmula

=DISTR.NORM(A5;Media;DesvEst;FALSO)

donde Media  DesvEst es el nombre de la celda B1 y Media el de la celda B2. Los valores del rango A5:A29 son el resultado de tomar 3 desvíos estándar de “izquierda a derecha” (-3 x DesvEst = -3).
También podemos representar la distribución normal con un gráfico de área



En resumen, para crear un gráfico de distribución normal necesitamos la serie que de datos que queremos analizar, obviamente, la función PROMEDIO, la función DESVEST para calcular el desvío estándar y la función DISTR.NORM para calcular la distribución normal para la media y desviación estándar de cada dato de la serie.


Para ampliar un poco más la explicación pongamos por ejemplo una serie de 500 datos.



Los datos los hemos puesto en el rango Datos (A2:A501); en E1 hemos calculado el promedio de los datos y en E2 el desvío estándar.
En el rango C5:C25 creamos una serie de grupos, es decir, intervalos de frecuencia de los datos. Para calcular cuántos datos caen en cada grupo usamos la función FRECUENCIA

={FRECUENCIA(datos;grupos)}

Esta es una función matricial y la introducimos pulsando Ctrl+Mayúsculas +Enter luego de haber seleccionado todo el rango.
Finalmente calculamos al distribución normal con

=DISTR.NORM(C5;$E$1;$E$2;FALSO)

en el rango E5:E25

Con estos datos podemos crear un histograma (también pueden consultar esta nota) y en el mismo gráfico agregar la distribución normal de los datos con un gráfico de línea (usando un eje secundario)




Este gráfico nos permite comparar el histograma creado a partir de los datos con la distribución normal teórica.

El archivo de este ejemplo se puede descargar aquí.



Technorati Tags: MS Excel

Calcular la posición de un elemento en un rango de Excel.

Un lector me consultaba cómo calcular la posición del último 5 en esta lista



Podemos ver que el último 5 ocupa el octavo lugar en la lista (o vector) y que se encuentra en la fila 9 de la hoja. En esta nota veremos cómo encontrar dinámicamente la última posición de cualquier elemento de la lista.

Por comodidad empezamos por definir un nombre que contenga el rango de la lista




En la celda D1 ponemos el número de la lista cuya última posición buscamos (esto nos permitirá usar nuestra fórmula en forma dinámica) y en la celda E1 ponemos esta fórmula matricial

=MAX((FILA(Valores)-1)*(Valores=$D$1))

Como toda fórmula matricial debe ser ingresada a la celda apretando simultáneamente Ctrl+Mayúscula+Enter



Podemos explicar esta fórmula mostrando la alternativa de usar columnas auxiliares. En ese caso empezamos por construir una columna auxiliar para calcular la posición del elemento en la lista. Esto lo hacemos calculando la fila de la celda con la función FILA y restando del resultado el número de filas desde la fila 1 hasta la primer fila de la lista (en nuestro caso 1)



Nuestra segunda columna auxiliar calculará si el valor de la celda coincide con el valor que estamos buscando



Como vemos el número 5 ocupa el tercer y el octavo lugar en la lista.

Nuestra tercer columna auxiliar consiste en multiplica Auxiliar1 por Auxiliar 2



Ahora es fácil ver que la fórmula =MAX(H2:H12) da como resultado 8, que es la posición del último 5 en la lista. Todo esto lo hemos comprimido en una única fórmula matricial, como mostramos más arriba.

Ahora, envalentonados con nuestro dominio de Excel, queremos calcular la posición del primer 5. No podemos usar la función MIN ya que en el vector de la fórmula matricial siempre habrá algún 0 y por eso siempre nos dará la posición 0 como resultado.
Aquí echamos mano a la función K.ESIMO.MENOR. Esta función da como resultado el k-ésimo menor valor de un conjunto de datos. En nuestro caso queremos encontrar el menor valor de la matriz (FILA(Valores)-1)*(Valores=$D$1) (es decir, Auxiliar 3) sin tomar en cuenta los valores 0.
Para esto necesitamos calcular cuantos 0 hay en el vector. La idea más obvia es usar CONTAR.SI, pero esta función tiene un problema: no se puede usar en fórmulas matriciales. En lugar de CONTAR.SI usaremos SUMA:

=K.ESIMO.MENOR(( FILA(Valores)-1)*(Valores=Hoja1!$D$1)*(((Valores=D1)<>0));SUMA(--(FILA(Valores)*(Valores=D1)=0))+1)

Recordemos que ésta es una fórmula matricial (Ctrl+Mayúsculas+Enter)

Para simplificar nuestra fórmula definimos el nombre "auxiliar" que contendrá la matriz creada por la expresión

auxiliar =( FILA(Valores)-1)*(Valores=Hoja1!$D$1)



Ahora podemos construir la fórmula en forma más legible:

=K.ESIMO.MENOR(auxiliar*(((Valores=D1)<>0));SUMA(--(auxiliar=0))+1)




El archivo con el ejemplo se puede descargar aquí

Technorati Tags: MS Excel