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