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

Catalogo de imágenes en Excel – Nota aclaratoria

Desde la publicación de la nota sobre el manejo de una base de datos con imágenes en Excel, hace más de dos años, he recibido muchos pedidos de aclaración sobre la técnica empleada. Sucede que varios de mis lectores no han logrado usar los métodos allí descritos en sus propios modelos.
En un intento de facilitar la explicación he creado una animación que pueden descargar en este enlace.
Una vez pulsado el enlace se abrirá la página de descarga. El enlace no es visible a primera vista



El archivo está comprimido, por lo que deberá ser descomprimido



Pueden apretar el botón Open o el botón Save para guardar el archivo y abrirlo más tarde.
El archivo comprimido contiene tres archivos.



Para ver la animación deben activar el archivo HTML (la animación se abrirá en una página del navegador).
Están invitados a dejar comentarios en especial sobre la técnica de animación como herramienta didáctica.

Technorati Tags: MS Excel

Agrupar datos de tablas dinámicas de acuerdo a valores numéricos

Hasta ahora hemos visto como agrupar (y desagrupar) datos en una tabla dinámica en base a campos de fechas o texto. Existe también la posibilidad de agrupar de acuerdo a valores numéricos.
Volviendo a al ejemplo de las tiendas, supongamos que queremos agruparlas por el monto de ventas en intervalos de 150000, es decir, un grupo con tiendas con ventas de 0 a 149999, otro grupo de 150000 a 299999 y así sucesivamente.
Antes de agrupar nuestra tabla dinámica se ve así



Como sólo podemos agrupar y desagrupar por campos de filas o de columnas, agregamos el campo Ventas (que ya aparece en el área de datos) al área de filas



Seleccionamos el campo Ventas del área de filas (en nuestro ejemplo la celda B2) y abrimos el menú Agrupar y mostrar detalle-Agrupar



Como Excel ha detectado que el campo contiene números (y éstos no son fechas) abre automáticamente el formulario Agrupar-Automático.
Excel detecta automáticamente los valores mínimo (Comenzar en) y máximo (Terminar en) del rango, los cuales pueden ser cambiados de ser necesario. En nuestro caso ponemos 0 como mínimo y 500000 como máximo; en la ventanilla Por del formulario ponemos 150000. Apretamos Aceptar



Ahora tenemos que arrastrar el campo Ventas del área de filas y ponerlo a la izquierda del campo Tienda. Al hacerlo obtenemos este resultado



Podemos obtener el mismo resultado creando un campo adicional en la base de datos, pero este método es más eficiente.

Technorati Tags: MS Excel

Agrupar y desagrupar datos manualmente en tablas dinámicas (segunda nota)

Ayer vimos como agrupar datos en tablas dinámicas de Excel basándonos en campos de texto.
Ahora veremos como agregar o quitar líneas a los campos agrupados. Excel no tiene un menú para editar campos agrupados. Podemos agrupar (como mostramos ayer) o desagrupar usando la opción Desagrupar del asistente de tablas dinámicas.
Hay situaciones en las cuales queremos agregar o quitar un miembro a un grupo ya existente. En esta nota mostraré las técnicas para hacerlo.
Volvamos sobre nuestro ejemplo de ayer donde hemos creado el grupo Norte



Si queremos agregar la Tienda 12 a este grupo empezamos por seleccionar todos los miembros del grupo y también Tienda 12 (apretando Ctrl mientras hacemos clic con el mouse)



Abrimos el menú Agrupar y mostrar detalle del asistente de tablas dinámicas y apretamos agrupar



Tienda 12 aparece ahora incluida en el grupo Norte



Nótese que el nombre del grupo ha cambiado al nombre por defecto, Grupo1.

Para quitar alguno de los miembros empezamos por seleccionar todos los miembros del grupo, menos aquel que queremos quitar (en nuestro ejemplo Tienda 19)



Ahora volvemos a usar Agrupar y mostrar detalle-Agrupar, lo que hará que Tienda 10 quede fuera del grupo



Podemos combinar ambas técnicas en una sola. Por ejemplo, si queremos quitar del grupo las tiendas 13 y 16 y agregar la tienda 2, seleccionamos todas las tiendas del grupo excepto 13 y 16 y seleccionamos también la tienda 2. Luego usamos Agrupar y mostrar detalle-Agrupar, obteniendo el resultado deseado en una sola operación.

Technorati Tags: MS Excel

Más sobre agrupar datos en tablas dinámicas de Excel

En la nota Tablas Dinámicas en Excel – Agrupar datos vimos como agrupar datos en una tabla dinámica de Excel basándonos en un campo de fechas.
Pero esta funcionalidad no se limita sólo a fechas. Podemos agrupar datos basándonos también en campos de texto.
Supongamos, una vez más, esta tabla que muestra las ventas de cada tienda de una cadena



Empezamos creando la tabla dinámica



Nuestro objetivo es agrupar las tiendas por zonas: Norte, Sur, Este y Oeste. El problema es que la tabla de datos no incluye un campo que nos permita agrupar las tiendas por zonas. Una solución posible es agregar el campo, pero esto no es necesario.
Empezamos por seleccionar todas las tiendas que pertenecen a la zona Norte. No es necesario que el rango sea continuo de manera que después de seleccionar la primer tienda apretamos Ctrl al seleccionar las restantes



En nuestro caso hemos seleccionado las tiendas 1, 13, 16, 19 y 3 que corresponden a la zona Norte. En el asistente de Tablas Dinámicas abrimos el menú Agrupar y mostrar detalle--Agrupar.



Excel crea un nuevo campo de filas (Tienda2) y agrupa las tiendas que hemos seleccionado en Groupo1.



Cambiamos el nombre del grupo a Norte



Repetimos el proceso para el resto de las zonas



Ahora podemos cambiar el nombre del campo de Tienda2 a Zonas y arrastrar el campo Tienda fuera de la tabla



Un detalle a tomar en cuenta es que Excel crea el nuevo campo sin subtotales



Es recomendable cambiar la definición a Automático, de manera que cuando abrimos el detalle o agregamos el campo Tienda, podamos ver el total por zona

Technorati Tags: MS Excel