Filtro de tabla dinámica en Excel (tabla de contenido)

  • Introducción al filtro de tabla dinámica
  • ¿Cómo filtrar una tabla dinámica en Excel?

Introducción al filtro de tabla dinámica

Pivot Table es una herramienta de hoja de cálculo fácil de usar en Excel que nos permite resumir, agrupar, realizar operaciones matemáticas como SUMA, PROMEDIO, CONTAR, etc. de los datos organizados que se almacenan en la base de datos. Además de las operaciones matemáticas, Pivot tiene una de las mejores características, es decir, el filtrado, que nos permite extraer resultados definidos de nuestros datos.

Veamos las múltiples formas de usar el filtro en PIVOT.

¿Cómo filtrar una tabla dinámica en Excel?

Veamos algunos de los ejemplos y su explicación para Filtrar tabla dinámica en Excel.

Puede descargar este filtro de tabla dinámica aquí - Filtro de tabla dinámica

Ejemplo # 1 - Crear filtro incorporado en la tabla PIVOT

Paso 1: Tengamos los datos en una de las hojas de trabajo.

Los datos anteriores consisten en 4 columnas diferentes con Sl.No, Flat No's, Carpet Area y SBA.

Paso 2: vaya a la pestaña Insertar y seleccione la tabla dinámica como se muestra a continuación.

Cuando hace clic en la tabla dinámica, aparece la ventana "Crear tabla dinámica".

Tenemos la opción de seleccionar una tabla o un rango para crear una tabla dinámica o también podemos usar una fuente de datos externa. También tenemos la opción de colocar el informe de la tabla dinámica ya sea en la misma hoja de trabajo o en la nueva hoja de trabajo y podemos ver como se muestra en la imagen de arriba.

Paso 3: el campo de tabla dinámica estará disponible en el extremo derecho de la hoja como se muestra a continuación. Podemos observar el campo Filtro, donde podemos arrastrar los campos a los filtros para crear un filtro en la tabla Pivote.

Arrastremos el campo Flat no's a Filters y podemos ver que se habría creado el filtro para Flat no's.

A partir de esto, podemos filtrar los números planos según nuestros requisitos y esta es la forma normal de crear un filtro en la tabla dinámica.

Ejemplo # 2 - Crear un filtro para las áreas de valor

En general, cuando tomamos datos en áreas de valor no se creará ningún filtro para esos campos. Podemos verlo a continuación.

Podemos observar claramente que no hay una opción de filtro para áreas de valor, es decir, Suma de SBA y Suma de área de alfombra. Pero en realidad podemos crearlo y eso nos ayuda en varios propósitos de toma de decisiones. En primer lugar, tenemos que seleccionar cualquier celda al lado de la tabla y hacer clic en el filtro en la pestaña de datos. Podemos ver que el filtro se mete en las áreas de valor.

A medida que obtuvimos los filtros, ahora podemos realizar diferentes tipos de operaciones desde áreas de valor, así como ordenarlas de mayor a menor para conocer las mejores ventas / área / cualquier cosa. Del mismo modo, podemos ordenar de menor a mayor, ordenando por color e incluso podemos realizar filtros numéricos como <=, =, > y muchos más. Esto juega un papel importante en la toma de decisiones en cualquier organización.

Ejemplo # 3 - Visualización de la lista de elementos múltiples en un filtro de tabla dinámica

En el ejemplo anterior, aprendimos a crear un filtro en Pivot. Ahora veamos la forma en que mostramos la lista de diferentes maneras. Las 3 formas más importantes de mostrar una lista de elementos múltiples en un filtro de tabla dinámica son: -

  • Usar rebanadoras
  • Crear una lista de celdas con criterios de filtro
  • Lista de valores separados por comas

1. Usando rebanadoras:

Tengamos una tabla dinámica simple con diferentes columnas como Región, Mes, Número de unidad, Función, Industria, Categoría de edad.

A partir de este ejemplo, vamos a considerar la función en nuestro filtro y veamos cómo se puede enumerar usando segmentadores de datos y varía según nuestra selección.

Es simple, ya que solo seleccionamos cualquier celda dentro de la tabla dinámica e iremos a analizar la pestaña en la cinta de opciones y elegiremos insertar rebanador y luego vamos a insertar el rebanador en nuestra área de filtro, así que en este caso la "Función" archivado en nuestra área de filtro y luego presiona Ok y eso agregará un rebanador a la hoja.

Podemos ver que los elementos resaltados en la segmentación son aquellos que están resaltados en nuestros criterios de filtro en el menú desplegable del filtro. Ahora, esta es una solución bastante simple que muestra los criterios de filtro. Con esto, podemos filtrar fácilmente varios elementos y podemos ver el resultado variando en áreas de valor. En el siguiente ejemplo, está claro que habíamos seleccionado las funciones que son visibles en la segmentadora y podemos encontrar el recuento de la categoría de edad para diferentes industrias (que son etiquetas de fila que habíamos arrastrado al campo de etiqueta de fila) que están asociadas con aquellas funciones que están en la rebanadora. Podemos cambiar la función según nuestros requisitos y podemos observar que los resultados varían según los elementos seleccionados.

Sin embargo, si tiene muchos elementos en su lista aquí y es realmente largo, es posible que esos elementos no se muestren correctamente y que tenga que desplazarse mucho para ver qué elementos se seleccionan para que nos lleve a la solución de nido de enumerar los criterios de filtro en las celdas.

Entonces, "Crear lista de celdas con criterios de filtro" viene a nuestro rescate.

2. Crear una lista de celdas con criterios de filtro:

Vamos a usar una tabla dinámica conectada y básicamente vamos a usar la segmentadora anterior aquí para conectar dos tablas dinámicas juntas. Ahora, creemos una copia duplicada de la tabla dinámica existente y péguela en una celda en blanco de una nueva hoja.

Así que ahora tenemos una copia duplicada de nuestra tabla dinámica y vamos a modificar un poco para mostrar ese campo de Funciones en el área de filas. Para hacer esto, tenemos que seleccionar cualquier celda dentro de nuestra tabla dinámica aquí y pasar a la lista de campos de la tabla dinámica e ir a eliminar Industry de las filas, eliminar la categoría Count of Age del área de valores y vamos a tomar la función que está en el área de filas de nuestro área de filtros, por lo que ahora podemos ver que tenemos una lista de nuestros criterios de filtro si miramos aquí en nuestro menú desplegable de filtros, tenemos la lista de elementos que está allí en segmentadores y filtro de función también .

Ahora tenemos una lista de nuestros criterios de filtro y esto funciona porque estos dos pivotes están conectados por el rebanador. Si hacemos clic con el botón derecho en cualquier parte de la segmentación de datos e informamos conexiones, conexiones de la tabla dinámica, se abrirá un menú que nos muestra que ambas tablas dinámicas están conectadas a medida que se marcan las casillas de verificación.

Lo que significa que cada vez que se realiza un cambio en el primer pivote, se reflejará automáticamente en el otro. Las tablas se pueden mover a cualquier lugar, se pueden usar en cualquier modelo financiero y la etiqueta de fila también se puede cambiar.

3. Lista de valores separados por comas:

Entonces, la tercera forma de mostrar nuestros criterios de filtro es en una sola celda con una lista de valores separados por comas y podemos hacerlo con la función TEXTJOIN . Todavía necesitamos las tablas que usamos anteriormente y solo usamos la fórmula para crear esta cadena de valores y separarlos con comas.

Esta es una nueva fórmula o nueva función que se introdujo en Excel 2016 y se llama TEXTJOIN (si no tiene Excel 2016, también puede usar la función concatenar) la unión de texto hace que este proceso sea mucho más fácil.

TEXTJOIN básicamente nos da tres argumentos diferentes.

Delimitador: que puede ser una coma o un espacio.

Ignorar vacío: verdadero o falso para ignorar las celdas vacías o no.

Texto: agregue o especifique un rango de celdas que contienen los valores que queremos concatenar.

Escribamos text join- (delimitador, que sería ", " en este caso, TRUE (ya que deberíamos ignorar las celdas vacías), A: A (ya que la lista de elementos seleccionados del filtro estará disponible en esta columna) para unir cualquier valor y también ignorar cualquier valor vacío en el filtro de tabla dinámica)

Ahora vemos cómo obtener una lista de todos nuestros criterios de filtro unidos por una cadena. Básicamente, se trata de una lista de valores separados por comas y, si no queremos mostrar estos criterios de filtro en la fórmula, podemos ocultar la celda.

Simplemente seleccione la celda y suba para analizar la pestaña de opciones, haga clic en los encabezados de campo y eso ocultará la celda.

Así que ahora tenemos la lista de valores en sus criterios de filtro. Ahora, si hacemos cambios en el filtro de pivote, se refleja en todos los métodos. Podemos usar cualquiera de allí. Pero eventualmente para una solución de separación separada por comas y se requiere la lista. Si no desea mostrar las tablas, se pueden ocultar

Cosas para recordar

  • El filtrado no es un aditivo porque cuando seleccionamos un criterio y si queremos volver a filtrar con otro criterio, el primero se descartará.
  • Tenemos una característica especial en el filtro, es decir, "Cuadro de búsqueda", que nos permite anular la selección manual de algunos de los resultados que no queremos. Por ejemplo: si tenemos la lista enorme y también hay espacios en blanco, entonces para seleccionar espacios en blanco podemos seleccionarlos fácilmente buscando espacios en blanco en el cuadro de búsqueda en lugar de desplazarnos hacia abajo hasta el final.
  • Se supone que no debemos excluir ciertos resultados con la condición en el filtro, pero podemos hacerlo mediante el uso de "filtro de etiqueta". Por ejemplo: si queremos seleccionar cualquier producto con una determinada moneda, como rupias o dólares, etc., podemos usar el filtro de etiquetas: "no contiene" y debería dar la condición.

Artículos recomendados

Esta es una guía para el filtro de tabla dinámica en Excel. Aquí discutimos cómo crear un filtro de tabla dinámica en Excel junto con ejemplos y plantilla de Excel. También puede consultar nuestros otros artículos sugeridos para obtener más información:

  1. Ordenar datos en tabla dinámica
  2. Mesa pivotante con varias hojas
  3. Formato condicional de Excel en una tabla dinámica
  4. Tabla dinámica de VBA | Plantilla de Excel
  5. Tabla dinámica de actualización de VBA (ejemplos)

Categoría:

Consejos de Excel