El filtro Top 10 de la tabla dinámica da un total de las filas visibles
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.
¿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.
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.
- 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, 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 …
Entonces, hay una regla que dice que no puede usar Autofiltros 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.
Realmente nunca consideré por qué Microsoft atenúa esto. 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 célula 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 hablé? 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 el Autofiltro funciona de manera diferente a los filtros de tabla dinámica. Vaya al menú desplegable Ingresos y elija Filtros numéricos, Top 10 …
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 sabe Microsoft, ¡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 2016, 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 que dice 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 el total, y su total general ahora incluye un asterisco y el total de todos los datos.
Este truco me vino originalmente de Dan en mi seminario en Filadelfia. Gracias a Miguel Caballero por sugerir esta característica.
Ver video
- El filtro Top 10 de la tabla dinámica da un total de las filas visibles
- Incluir elementos filtrados en los totales está atenuado
- Forma extraña de invocar el filtro de datos desde la celda mágica
- Los filtros de datos no están permitidos en tablas dinámicas
- Excel no logra atenuar el filtro de datos de la celda mágica
- Pregunte por los 6 primeros para obtener los 5 primeros más el Gran Total
- Útil para filtrar por un elemento dinámico específico
- Excel 2013 o posterior: una forma diferente de obtener el total real
- Envíe sus datos a través del modelo de datos
- Incluir elementos filtrados en totales estará disponible
- Obtener total con asterisco
- Aprendí este truco hace más de 10 años de Dan en Filadelfia
Transcripción del video
Learn Excel for Podcast, Episodio 1999 - Tabla dinámica Los cinco mejores
Estoy publicando un podcast de este libro completo. Hay una lista de reproducción, haga clic en la I en la esquina superior derecha para seguir esa lista de reproducción. Bienvenido de nuevo al netcast. Soy Bill Jelen.
Muy bien, vamos a crear una tabla dinámica y queremos mostrar, no a todos los clientes, sino solo a los cinco clientes principales. INSERT, tabla dinámica. De acuerdo, pondré Cliente en el lado izquierdo y Ingresos. Muy bien, aquí está nuestra lista completa de clientes anotada como 6.7 millones de dólares. Excel, hace que sea fácil hacer los cinco primeros. Vaya a Etiquetas de fila, Filtros de valor, top 10. No tiene por qué ser superior. Puede ser superior o inferior. No tiene que ser cinco. Pueden ser veinte, cuarenta, puede ser lo que sea. Ochenta por ciento superior, dame suficientes registros para llegar a tres millones de dólares o cuatro millones de dólares, pero aquí vamos. Los cinco elementos principales. Ahora recuerde 6.7 millones de dólares, haga clic en Aceptar y mi gran problema aquí es que el gran total no es de 6.7 millones. Cuando le dé esto al vicepresidente de ventas, se volverá loco y me dirá, espera un segundo,Sé que hice más de 3,3 millones de dólares. Bien, entonces vamos a deshacer, deshacer eso y volver a los datos originales.
Ahora, este próximo truco lo aprendí durante uno de mis seminarios de Power Excel en Filadelfia. Un tipo llamado Dan en la fila dos, me mostró esto. Hace más de diez años que me enseñó este truco, y primero tenemos que hablar de los filtros. Entonces, normalmente, si va a usar el Filtro normal, este Filtro aquí, elige cualquier celda en su conjunto de datos y hace clic en el Icono de Filtro, o algunas personas eligen el Conjunto de Datos completo, CONTROL * y hacen clic en el Icono de Filtro, pero hay una tercera forma. Una forma que a nadie le importa. Si va a la última celda de encabezado, en mi caso, ese es el costo en L1, y vaya una celda a la derecha. Yo llamo a esto la celda mágica, no tengo idea de por qué, pero por alguna razón desconocida, desde esta celda, puedo filtrar el conjunto de datos adyacente. Muy bien, es como una forma extraña y a nadie le importa esto.
Correcto, debido a que hay otras dos formas realmente buenas de invocar un filtro, nadie necesita saber sobre la celda mágica, pero aquí está la cosa, mira dentro de una tabla dinámica, está atenuada. No está autorizado a utilizar esos filtros. Va en contra de las reglas. Ahora, si salgo aquí, soy más que bienvenido a usar el Filtro, pero por dentro se destacan. No sé quién es la persona que lo atenúa, pero nunca han escuchado mi pequeña charla sobre la celda mágica, porque si voy a la última celda de encabezado y voy una celda a la derecha, mire eso, se olvidan de atenuar el filtro y ahora acabo de agregar los viejos filtros automáticos a la tabla dinámica. Entonces vengo aquí, voy a Filtros numéricos, eso es diferente a los Filtros de valor. Todavía se llama Top Ten. Ligeramente diferente, voy a pedir los cinco primeros, no los seis primeros.Los seis primeros porque para este Filtro, el Gran Total es solo otra fila, y el Gran Total es el elemento más grande y luego, cuando me piden los elementos 2 a 6, obtengo los cinco elementos principales.
Muy bien, ahí estamos. Un truco de filtro genial, que nos da los cinco elementos principales y el total real de todos. Bien ahora, un par de cosas. No te olvides de la celda mágica. De acuerdo, no hay forma de desactivar este filtro, a menos que regrese a la celda mágica. Muy bien, necesitas recordar la celda mágica. Además, si cambia los datos subyacentes y actualiza la tabla dinámica, ellos no actualizarán el filtro porque, por lo que sabe Microsoft, no se le permite tener un filtro.
Esto es útil para otras cosas. A veces tenemos productos que van por encima. Vayamos aquí en forma tabular. No es necesario, solo me gusta obtener títulos reales. Gizmo, Widget, Gadgets, Doodads. Muy bien, tal vez seas el administrador de Doodads y necesites ver solo los clientes que tenían un valor particular y Doodads. Así que voy a la celda mágica, enciendo el Filtro y luego bajo Doodads puedo pedir elementos que sean mayores que cero. Haga clic en Aceptar. Muy bien, ese tipo de filtrado no sería posible en una tabla dinámica normal, pero es posible usando la celda mágica.
Bien, ahora deshagamos la lista. Desactivemos este filtro y eliminemos la tabla dinámica, y si está en Excel 2013 o nuevo, le mostraré una forma completamente legal de obtener el total correcto en la parte inferior. Inserte la tabla dinámica, aquí abajo en la parte inferior, comenzando en Excel 2013, este cuadro muy inocuo no suena muy emocionante, agregue estos datos al modelo de datos. Eso envía los datos, detrás de escena, al motor Power Pivot. Cree exactamente el mismo informe. Clientes por el lado izquierdo. Ingresos en el corazón de la tabla dinámica. Luego, vaya a los filtros regulares, los filtros de valor top 10. Pregunte por los cinco primeros. Observe nuevamente que tenemos 6,7 millones de dólares después de hacer esto, 3,3 millones de dólares, pero aquí está la diferencia. Cuando voy a la pestaña Diseño, en Subtotales, esta función se llama Incluir elementos filtrados en totales,ya no está atenuado. En una tabla dinámica normal no está disponible. Tenemos un pequeño asterisco allí y es el total de todo. Muy bien, ahora, por supuesto, eso solo funciona en Excel 2013 o versiones posteriores.
Muy bien, me tomará seis semanas publicar este libro completo aquí en YouTube. Aquí hay muchos buenos consejos. Consejos que podrían empezar a ahorrarle tiempo de inmediato. Compre el libro completo ahora mismo y tendrá acceso a los 40. En realidad, son más de 40 consejos. Teclas de acceso directo de Excel. Todo tipo de cosas geniales en este libro.
Muy bien, recapitulación. Entonces, cuando hacemos un filtro de los 10 mejores de la tabla dinámica, nos da el total pero solo las filas visibles, no las cosas que filtró. Sí, si vamos a la segunda pestaña y buscamos Subtotales, Elementos filtrados y Totales, está atenuado, pero hay una forma extraña de invocar el Filtro de datos antiguo desde la celda mágica. La última celda de encabezado, vaya una celda a la derecha, no puede usar Filtros y Tablas dinámicas, pero si va a la celda mágica, se olvidan de atenuarla. Ahora, en el Filtro de números, solicita los seis primeros para obtener los cinco primeros, más el total general. También es útil para filtrar a un elemento dinámico específico: Doodads, cualquier cosa que tenga más de 0 en Doodads o top 5 Doodads. Excel 2013 o posterior, hay una forma diferente de obtener el Total Verdadero.Marque esa casilla para el modelo de datos y luego incluir elementos filtrados en los totales estarán disponibles. Obtienes el total con un asterisco. Y gracias a Dan en Filadelfia que me mostró en uno de mis seminarios de Power Excel, hace más de diez años, y me dio este pequeño truco. Una forma de que el filtro se cuele a través de la pared de la mesa giratoria del club. Normalmente no permiten ese filtro automático.
Oye, quiero darte las gracias por pasar. Nos vemos la próxima vez, para otro netcast de MRExcel.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast1999.xlsx
