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