Calcular el promedio de los últimos n elementos de una serie.

Un lector me envía esta consulta:

En la columna A tengo una lista larga de nombres (por ej A, B, A, A , B, A; etc); en la columna B tengo un puntaje asociado a cada nombre.
Necesito calcular el promedio de los últimos 4 (o podría ser 5 o 6) puntajes correspondientes al nombre respectivo.

Supongamos que esta es nuestra lista

Como queremos calcular usando los últimos 4 (o cualquier otro número) miembros de la lista, por nombre, vamos a crear una columna auxiliar con el número de orden para cada valor de cada nombre. Esto lo haremos con la función CONTAR.SI

En la fórmula =CONTAR.SI($A$3:A3,A3) la primera referencia del rango de búsqueda ($A$3) es absoluta. De esta manera la referencia se va "expandiendo" a medida que copiamos la fórmula a los largo de la columna

Una vez agregada la columna auxiliar podemos calcular el promedio de los últimos n valores de cada nombre usando esta fórmula

=SUMAPRODUCTO((Nombre=G2)*(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1)*Valor)/SUMAPRODUCTO((Nombre=G2)*(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1))

Personalmente no me gustan esta "super fórmulas", por lo que prefiero dividirlas en dos (o más) fórmulas intermedias

La fórmula en la celda G5 (Suma) es

=SUMAPRODUCTO((Nombre=G2)*(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1)*Valor)
La fórmula en G6 (Recuento) es

=SUMAPRODUCTO((Nombre=G2)*(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1))
Y la obvia fórmula en G7 (Promedio) es =G5/G6

Antes de analizar cómo funcionan las fórmulas aclaremos que hemos asignado los rangos de valores a nombres definidos

¿Cómo funciona la fórmula en G5?

Primero debemos calcular cuál en el número de orden mayor para el nombre en la lista (el último, por ejemplo para B será 13), lo que hacemos con

MAX((Nombre=G2)*(Nro._Orden))

Como esta fórmula está incluida dentro de SUMAPRODUCTO, Excel la calculará en forma matricial. Si queremos usarla independientemente tendremos que introducirla apretando simultáneamente Ctrl-Mayúscula-Enter.

Para que Excel busque los últimos 4 valores usamos

(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1)

que en el caso de buscar el resultado para B, da 10. Esto podemos comprobarlo seleccionando la parte de la fórmula y apretando F9 (luego apretamos ESC para deshacer el cálculo)

En el próximo paso

(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1)

SUMAPRODUCTO asigna el valor VERDADERO a todos los valores de número de orden que sean mayores o iguales a 10. Siendo 13 el mayor, habrá 4 VERDADEROS en el vector.

La expresión (Nombre=G2) dentro de SUMAPRODUCTO crea una vector con valores VERDADERO para las fila del nombre buscado y FALSO para el resto.

La multiplicación de ambos vectores crea un vector de valores VERDADERO sólo para las filas en que ambas condiciones se cumplan.

Finalmente al multiplicar este vector por el de los valores, obtenemos la suma de las filas que cumplen ambas condiciones simultáneamente.

La fórmula para el Recuento hace lo mismo sin multiplicar por los valores.

Un último detalle es el formato numérico personalizado en la celda G3

El archivo puede descargarse aquí

Reportes dinámicos con segmentación de datos en Tablas Excel

¿Se acuerdan de la funcionalidad Segmentación de Datos? Microsoft introdujo en Excel 2010 esta funcionalidad para las tablas dinámicas. La herramienta permite filtrar reportes de tablas dinámicas con ciertas ventajas sobre el filtro tradicional.

En Excel 2013 también las Tablas cuentan con esta herramienta. Esto abre la posibilidad de crear reportes dinámicos que pueden ser muy útiles en presentaciones de datos. Veamos este reporte hecho con esta herramienta

Como pueden ver en la animación, la tabla oculta todas las filas no relevantes a la elección que efectuamos en los cuadros de segmentación. La ventaja de este método sobre el tradicional Autofiltro, es que vemos las elecciones que hemos efectuado. Además cuando elegimos un valor los cuadros de segmentación oscurecen todos los valores que dejan de ser relevantes.

La mejor parte de todo esto es que es muy fácil crear estos reportes.

Empezamos por un rango que contiene datos de ventas, que hemos convertido en Tabla.

Nuestra tabla tiene fechas por día, pero en nuestro informe queremos seleccionar por años y meses. Las Tablas no tienen la funcionalidad Agrupar de las tablas dinámicas, así que tendremos que dar un rodeo. A la derecha de la columna "Enviado" agregamos las columnas "Año" y "Mes". Los valores los generamos con las funciones AÑO y MES, obviamente

Ahora tenemos que hacer un poco de espacio por encima y a la izquierda de la tabla para poder ubicar los cuadros de segmentación de datos. Al seleccionar alguna celda de la tabla, se hace visible el menú Herramientas de Tabla donde activamos la segmentación de datos

Elegimos los campos con los que queremos filtrar nuestro informe y apretamos Aceptar. Excel crea los cuadros

Nuestra próxima tarea es cambiar el diseño de los cuadros de segmentación. Para hacer esto seleccionamos un cuadro de segmentación para hacer visible el menú de Herramientas de Segmentación

Elegimos un estilo de la galería de estilos y lo aplicamos a los cuadros. Si ninguno de los estilos nos satisface, podemos duplicar y modificar alguno de los estilos existentes

Otra herramienta importante en el menú es el grupo de comandos Botones. Aquí podemos definir el número de columna que tendrá el cuadro (por defecto tiene una sola), el ancho y el alto

Finalmente ocultamos las columnas Mes y Año, ubicamos los cuadros en las posiciones que nos parezcan convenientes y quitamos las líneas de cuadrícula de la hoja (Vista-Mostrar)

El resultado final

El archivo se puede descargar aquí.

Generar hojas a partir del filtro de una tabla dinámica de Excel.

Excel tiene muchas funcionalidades que hacen la vida del usuario más fácil; pero no todas son evidentes. Parafraseando a Antoine de Saint-Exupery, podríamos decir que "mucho de lo útil en Excel (lo esencial) es invisible a los ojos".

En este caso nuestro "principito" resultó ser José María Murillo que respondiendo a una consulta en el foro de Excel en castellano en Linkedin, menciona la funcionalidad "mostrar páginas de filtros de informes" en tablas dinámicas.

La situación es la siguiente: tenemos una base de datos con las ventas del año por cliente y por agente de ventas. Creamos una tabla dinámica para resumir las ventas; en el área de filtro usamos el campo "Agente de Ventas" para generar un informe para cada vendedor

Si queremos imprimir un informe para cada vendedor, tendríamos que seleccionar el vendedor, imprimir el informe, seleccionar el próximo vendedor, imprimir el informe y así sucesivamente hasta el último.

En lugar de esta tarea sisífica, podemos usar la funcionalidad "Mostrar páginas…" que se oculta en Herramientas-Analizar-Tabla Dinámica-Opciones (en Opciones hay que asegurarse de hacer clic en el triángulo invertido a la derecha de la opción)

Al seleccionar la opción "Mostrar…" se abre una ventanilla con los campos del filtro (en nuestro ejemplo hay sólo uno)

Al seleccionar el campo, Excel crea automáticamente una hoja para cada valor (agente de ventas en nuestro ejemplo) en el campo del filtro



Ahora podemos imprimir todas las hojas de los agentes en una única operación.

Si queremos enviar los informes por correo electrónico debemos recordar que cada informe refleja todos los datos de la base de datos y el agente puede cambiar el valor del filtro para "espiar" las ventas de sus colegas.