Autofiltro en tablas dinámicas - otro truco

Si bien este post trata sobre Autofiltro en Tablas Dinámicas, la idea surgió de uno de los videos del curso sobre Power Query, Power Pivot y Power BI de Miguel Escobar que recomiendo considerar a todo analista que usa Excel y quiera potenciar su profesionalidad (aclaración: si, recibo una comisión por cada inscripción pero ésto no quita de la calidad del curso).

Volviendo a nuestro tema, en mi post Filtrar por etiquetas en tablas dinámicas con dos criterios mostré como podemos agregar Autofiltro a una tabla dinámica para lograr filtrados que no serían posibles con el filtrado incorporado de la tabla dinámica.

Viendo uno de los videos del curso vi otro beneficio que puede obtenerse con esta técnica. Miguel ha incorporado como bono tres videos de Bill Jelen (Mr. Excel) que se titulan "Tres razones por las qué amo Power Pivot". En uno de ellos Bill muestra otro beneficio que podemos obtener agregando Autofiltro a una tabla dinámica.

Veamos la siguiente situación

Agreguemos ahora una columna que muestre el porcentaje del total de cada cliente como muestro en este video



Ahora podemos ver todos las ventas a los clientes de la vendedora Anna, ordenados de mayor a menor y el peso relativo de cada uno del total.

Si queremos mostrar los cinco clientes más importantes podemos usar el filtro de la tabla dinámica

y en la ventanilla de "Diez mejores" ponemos 5; éste es el resultado

Excel efectivamente nos muestra los cinco clientes con más ventas pero ahora el total general es el de los cinco clientes, no el total general del cuadro anterior y lo mismo sucede con los porcentajes. El cliente Rattlesnake Canyon Grocery que representa el 14.72% de las ventas ahora muestra el 24.44%.

La técnica que mostré en el post que menciono al principio de de esta nota puede ayudarnos a superar este inconveniente.

Si seleccionamos alguna celda de la tabla veremos que la opción Autofiltro está deshabilitada (no así, curiosamente, la opción "borrar" del filtro)

Siguiendo la técnica mencionada, seleccionamos la celda inmediatamente a la derecha de la última etiqueta de las columnas de la tabla. Al hacerlo veremos que ahora podemos aplicar la opción "Filtro" . Al hacerlo el Filtro se aplicará también a los campos de datos de la tabla

Ahora vamos a usar la opción "Diez mejores" del filtro del campo "Ventas" (que antes no existía) para mostrar los cinco principales clientes

La única diferencia con el método anterior es que para mostrar los primero cinco vamos a introducir seis en la definición del filtro (es decir, el número de filas a mostrar más uno)

Podemos ver que con esta técnica el total general y los porcentajes se mantienen.

El motivo por el que elegimos 6 para mostrar 5 es que con esta técnica la fila del total general es una de las incluidas en el recuento, de manera que para mostrar cinco clientes tenemos que definir seis filas.

Reportes dinámicos con Power Query

La introducción de las nuevas herramientas de Excel, Power Query y PowerPivot, han facilitado enormemente la tarea de analizar datos y crear reportes. La evolución que comenzó hace veinte años con las tablas dinámicas, ha terminado por convertir a Excel en una verdadera herramienta de BI (Business Intelligence).
Hoy en día Excel puede "digerir" cualquier cantidad de datos de prácticamente cualquier origen. Los analistas de datos sabemos que la principal dificultad es crear un reporte dinámico que sea "a prueba de balas" aun en manos del más insoportable de los gerentes de nuestra compañía.

Veamos el siguiente escenario: a partir de los datos de ventas (en nuestro ejemplo usaremos la consulta Invoices de la base de datos Northwind) creamos una reporte que muestra las ventas de los 10 principales clientes de la empresa. Después de volcar los datos en una hoja de Excel (o crear una conexión a la base de datos), creamos una tabla dinámica

y la filtramos con la posibilidad Filtros de Valor-Diez mejores

Diez minutos después de haber enviado el reporte nuestro jefe nos preguntará qué tiene que hacer para ver 25 clientes en lugar de 15.
Aquí comienza nuestro dilema. Una posibilidad es explicarle al jefe como cambiar los valores del filtro. La pregunta contiene la respuesta: si supiera como hacerlo no lo hubiera preguntado y si no lo sabe la probabilidad que entienda la explicación y no arruine el reporte es mínima.
La solución es crear un mecanismo tal que al introducir un número en una celda de la hoja, la cantidad de clientes en el reporte cambie, como en este ejemplo

Una posibilidad es usar una macro que tome el valor de la celda C3 y lo use como variable para cambiar el valor del filtro. En este ejemplo, en lugar de macros, estoy usando el valor de la celda C3 para cambiar la consulta hecha con el Power Query a la base de datos.
Veamos como construir el modelo paso por paso.
Empezamos por crear una consulta a la base de datos (en nuestro ejemplola base Northwind en Access). Las transformaciones que hacemos con Power Query pueden verse en este video



Después de crear la conexión, agrupamos las filas por cliente creando un campo que totaliza la ventas por cliente; ordenamos las filas en orden decreciente por total de ventas; agregamos una columna Índice que nos servirá de indicador; eliminamos las columnas agrupadas y expandimos "Todas Filas" de manera que volvemos a tener todas las columnas originales más el índice; finalmente filtramos la consulta con el criterio "<=15".

El segundo paso es crear una conexión a la celda C3 para poder controlar la cantidad de filas filtradas. La clave aquí es crear un nombre definido que se refiera a la celda. La forma más práctica es seleccionar la celda y reemplazazr la referencia en el cuadro de nombres por un nombre, en nuestro ejemplo "TopCust"

Ahora usamos la opción "Obtener datos - desde una tabla o rango" y en la consulta creada usamos "Rastrear desagrupando datos" (Drill Down)

con este resultado

La consulta, que recibe el nombre del nombre definido (TopCust), la guardamos creando sólo una conexión.

Ahora volvemos a editar la primer consulta y en el último paso, Filas Filtradas, reemplazamos el valor fijo por la segunda consulta "TopCust"

Ahora, al cambiar el valor de la celda C3 (TopCust) al apretar "Actualizar todo" el modelo se actualizará

Un detalle crítico es deshabilitar las actualización en segundo plano, como muestro en este post.

Si queremos que nuestro modelo sea totalmente dinámico podemos agregar una evento (macro) de manera que al cambiar el valor en la celda C3, se ejecute el método RefreshAll. En el módulo de la hoja ponemos este evento

 Private Sub Worksheet_Change(ByVal Target As Range)  
   If Target.Address = Range("TopCust").Address Then ThisWorkbook.RefreshAll  
 End Sub  

Cálculo de lapsos con Power Query

Comencé a publicar posts sobre Power Query hace casi cuatro años (este fue mi primer post sobre el tema). Desde entonces no sólo he publicado artículos sobre esta herramienta (indispensable para todo quien trabaje con masas de datos) sino que también se ha convertido en mi herramienta principal. Puedo decir que hoy en día la mayoría de las soluciones que desarrollo en Excel se basan, total o parcialmente, en el Power Query.

Hoy voy a mostrar como calcular lapsos, el tiempo transcurrido entre dos instantes, usando el Power Query. Supongamos que tenemos esta serie de mediciones y queremos saber cuánto tiempo ha transcurrido entre cada medición

Podemos hacerlo usando fórmulas de Excel, por supuesto. Pero supongamos que tenemos una gran cantidad de datos (en el caso real que traté había más de 50 mil mediciones) y que tenemos que realizar otras transformaciones por lo que Power Query será la herramienta ideal para el caso.

Como casi todas las notas de este blog ésta está dirigidas al usuario medio o medio-avanzado. Es decir, voy a prescindir de tecnicismos y pondré el énfasis en los aspectos prácticos.

Observando detenidamente el ejemplo podemos ver que hay dos tablas: una contiene una sola fila con la fecha y hora del comienzo de las mediciones y la segunda con las mediciones. Antes de crear las consultas consideremos lo siguiente: el cálculo de cada lapso es el tiempo transcurrido entre una medición y la inmediata anterior; pero el primer lapso es entre la primera medición de la tabla y el punto inicial. Éste dato se encuentra en otra tabla. Para poder usar este dato vamos a hacer lo siguiente:

Cargamos la tabla del punto inicial a una consulta

Con un clic del mouse abrimos la opcíón "Rastrear..." (drill down)

con este resultado

En la ventanilla Propiedades de la consulta cambiamos el nombre a "Inicio" y cerramos la consulta con la opción "sólo crear conexión". Luego cargamos la tabla de mediciones, sobre la cual haremos todos los cálculos

Nótese los diferentes iconos en las consultas, lo que nos indica que Power Query las está tratando de forma distinta. De hecho, la segunda es una tabla (como lo indica el icono) mientras que “Inicio” es una especia de variable (podemos cambiar el valor en la hoja de Excel y el cambio se reflejará en la consulta).

Ahora vamos a editar la consulta de las mediciones donde haremos nuestras cálculos. Empezamos por agregar una columna Índice con base cero

Cambiamos el nombre del último paso a "AgregInd" (nemotécnica para Indice Agregado)

Ésto nos permite trabajar con más facilidad en los próximos pasos.

Ahora agregamos una columna personalizada con esta fórmula

con este resultado

Debemos prestar atención a estos detalles en nuestra fórmula

if [Índice]=0 then[Medicion]-Inicio else [Medicion]-AgregInd[Medicion]{[Índice]-1}

• usamos directamente Inicio para referirnos a la consulta que contiene el punto de partida, como si estuviéramos usando una variable (o constante a los efectos de la consulta);
• para referirnos a la fila anterior anteponemos el nombre del paso anterior (AgregInd) al nombre de la columna sobre la cual queremos operara (Medicion, en nuestro caso) y usamos la expresión Indice - 1 entre corchetes para indicar que queremos referirnos a la fila anterior (no voy a entrar aquí en explicaciones sobre los objetos de Power Query, tablas y listas).

Dos detalles par finalizar:

• eliminamos la columna Indice
• cambiamos el tipo de dato de la columna Lapso usando la opción Transformar - Detectar tippo de datos

Todo lo que nos queda por hacer es cargar la consulta a una tabla, si así lo queremos, o dejarla como "sólo conexión".

El archivo con el ejemplo se puede descargar aquí