Las tablas dinámicas ofrecen un filtro Top 10. Es genial. Es flexible. Pero lo odio y te diré por qué.
A continuación, se muestra una tabla dinámica que muestra los ingresos por cliente. El total de ingresos es de $ 6,7 millones. Tenga en cuenta que el cliente más grande, Roto-Rooter, representa el 9% de los ingresos totales.
¿Qué pasa si mi gerente tiene la capacidad de atención de un pez dorado y solo quiere ver a los cinco clientes principales? Para comenzar, abra el menú desplegable en A3 y seleccione Value Filters, Top 10.
El diálogo de filtros Top 10 súper flexible permite Top / Bottom. Puede hacer 10, 5 o cualquier otro número. Puede solicitar los cinco artículos principales, el 80% superior o suficientes clientes para llegar a $ 5 millones.
Pero aquí está el problema: el informe resultante muestra cinco clientes y el total de esos clientes en lugar de los totales de todos. Roto-Rooter, que anteriormente era el 9% del total, es el 23% del nuevo total.
Pero primero, algunas palabras importantes sobre autofiltro
Me doy cuenta de que esto parece una pregunta fuera de lo común. Si desea activar los menús desplegables de Filtro en un conjunto de datos normal, ¿cómo lo hace? Aquí hay tres formas realmente comunes:
- Seleccione una celda en sus datos y haga clic en el icono de Filtro en la pestaña Datos o presione Ctrl + Shift + L.
- Seleccione todos sus datos con Ctrl + * y haga clic en el icono Filtro en la pestaña Datos.
- Presione Ctrl + T para formatear los datos como una tabla.
Estas son tres formas realmente buenas. Siempre que conozca alguno de ellos, no hay absolutamente ninguna necesidad de conocer otro camino. Pero aquí hay una forma increíblemente oscura pero mágica de activar el filtro:
- Vaya a su fila de encabezados y luego vaya a la celda de encabezado más a la derecha. Mueve una celda a la derecha. Por alguna razón desconocida, cuando está en esta celda y hace clic en el ícono de Filtro, Excel filtra el conjunto de datos a su izquierda. No tengo idea de por qué funciona esto. Realmente no vale la pena hablar de ello porque ya hay tres formas realmente buenas de activar los menús desplegables de Filtro. A esta celda la llamo la celda mágica.
Y ahora, de vuelta a las tablas dinámicas
Existe una regla que dice que no puede usar Autofiltro cuando está en una tabla dinámica. ¿Vea abajo? El icono de Filtro está atenuado porque he seleccionado una celda en la tabla dinámica.
No sé por qué Microsoft lo atenúa. Debe ser algo interno que diga que AutoFilter y una tabla dinámica no pueden coexistir. Entonces, hay alguien en el equipo de Excel que está a cargo de atenuar el ícono de Filtro. Esa persona nunca ha oído hablar de la celda mágica. Seleccione una celda en la tabla dinámica y el filtro se atenuará. Haga clic fuera de la tabla dinámica y el Filtro se habilitará nuevamente.
Pero espera. ¿Qué pasa con la celda mágica de la que te acabo de hablar? Si hace clic en la celda a la derecha del último encabezado, Excel se olvida de atenuar el icono de Filtro.
Efectivamente, Excel agrega menús desplegables de Autofiltro a la fila superior de su tabla dinámica. Y AutoFilter funciona de manera diferente a un filtro de tabla dinámica. Vaya al menú desplegable Ingresos y elija Filtros numéricos, 10 principales….
En el cuadro de diálogo Top 10 Autofilter, elija Top 6 Items. Eso no es un error tipográfico … si desea cinco clientes, elija 6. Si desea 10 clientes, elija 11.
Para Autofiltro, la fila de total general es el elemento más grande de los datos. Los cinco clientes principales ocupan las posiciones 2 a 6 en los datos.
Precaución
Claramente, estás haciendo un agujero en la estructura de Excel con este truco. Si luego cambia los datos subyacentes y actualiza su tabla dinámica, Excel no actualizará el filtro porque, hasta donde Microsoft sabe, ¡no hay forma de aplicar un filtro a una tabla dinámica!
Nota
Nuestro objetivo es mantener esto en secreto para Microsoft porque es una característica bastante interesante. Ha estado "roto" durante bastante tiempo, por lo que hay muchas personas que podrían estar confiando en él a estas alturas.
Una solución completamente legal en Excel 2013+
Si desea una tabla dinámica que le muestre los cinco clientes principales, pero el total de todos los clientes, debe mover sus datos fuera de Excel. Si tiene Excel 2013 o una versión más reciente ejecutándose en Windows, existe una forma muy conveniente de hacerlo. Para mostrarte esto, eliminé la tabla dinámica original. Elija Insertar, Pivot Table. Antes de hacer clic en Aceptar, seleccione la casilla de verificación Agregar estos datos al modelo de datos.
Construya su tabla dinámica como de costumbre. Utilice el menú desplegable en A3 para seleccionar Filtros de valor, Top 10 y pregunte por los cinco clientes principales. Con una celda en la tabla dinámica seleccionada, vaya a la pestaña Diseño en la Cinta y abra el menú desplegable Subtotales. La última opción en el menú desplegable es Incluir elementos filtrados en totales. Normalmente, esta opción está atenuada. Pero debido a que los datos se almacenan en el modelo de datos en lugar de en un caché dinámico normal, esta opción ahora está disponible.
Elija la opción Incluir elementos filtrados en totales, y su total general ahora incluye un asterisco y el total de todos los datos, como se muestra a continuación.
Este truco de la celda mágica me vino originalmente de Dan en mi seminario en Filadelfia y fue repetido 15 años después por un Dan diferente de mi seminario en Cincinnati. Gracias a Miguel Caballero por sugerir esta característica.
