¿Cómo filtrar una tabla dinámica basada en un valor de celda específico en Excel?
En Excel, las tablas dinámicas se utilizan ampliamente para resumir, analizar y explorar datos de manera eficiente. Por defecto, el filtrado dentro de una tabla dinámica suele realizarse seleccionando los elementos deseados del menú desplegable de filtro. Aunque este enfoque ofrece flexibilidad, hay ciertos escenarios donde se necesita un método de filtrado más dinámico: por ejemplo, puede que quieras que los resultados de la tabla dinámica cambien automáticamente en función del valor introducido en una celda específica de la hoja de trabajo. Esto es especialmente útil al preparar cuadros de mando, automatizar flujos de trabajo o construir informes interactivos para usuarios finales que podrían no sentirse cómodos con el filtrado manual.
Excel no proporciona una función estándar que vincule nativamente el valor de una celda a un filtro de tabla dinámica (sin usar código). Sin embargo, hay varias técnicas prácticas para abordar este requisito, cada una con sus propias ventajas y aspectos a considerar. Este tutorial introduce primero un método VBA sencillo para conectar directamente una celda a un filtro de tabla dinámica, de modo que la tabla dinámica se actualice instantáneamente cuando cambia el valor de la celda. Además, cubriremos métodos alternativos, como usar fórmulas de Excel (por ejemplo, GETPIVOTDATA, FILTER) para mostrar resultados filtrados y usar Segmentadores como controles gráficos de filtro. Comprender estas opciones te ayuda a elegir el mejor método para tu flujo de trabajo en Excel y experiencia del usuario.
➤ Filtrar tabla dinámica basada en un valor de celda específico con código VBA
➤ Fórmula de Excel - Mostrar resultados filtrados de la tabla dinámica basados en un valor de celda
➤ Otros métodos integrados de Excel - Usar Segmentadores como filtros interactivos de Tabla Dinámica
Filtrar tabla dinámica basada en un valor de celda específico con código VBA
Si deseas una verdadera interactividad dinámica —es decir, cuando escribes un valor en una celda y el filtro de la tabla dinámica responde automáticamente al cambio— VBA ofrece una solución directa. Esto es particularmente útil en cuadros de mando, plantillas para colegas o situaciones donde se necesitan ajustes rápidos de filtros cambiando una sola celda. Sin embargo, este método requiere un conocimiento básico del editor de VBA, y, como ocurre con todas las macros, tu libro debe guardarse en un formato habilitado para macros (.xlsm).
El siguiente código VBA te permite vincular dinámicamente una celda de hoja de trabajo a un filtro de tabla dinámica. Sigue estos pasos cuidadosamente y asegúrate de modificar el nombre de la hoja de trabajo, el nombre de la tabla dinámica y la referencia del campo según sea necesario en tu libro:
Paso 1: Introduce el valor por el cual quieres filtrar tu tabla dinámica en una celda de la hoja de trabajo (por ejemplo, escribe o selecciona el valor de filtrado en la celda H6).
Paso 2: Abre la hoja de trabajo que contiene tu tabla dinámica objetivo. Haz clic derecho en la pestaña de la hoja en la parte inferior de Excel y selecciona Ver código en el menú contextual. Esto abre la ventana del editor de VBA para la hoja de trabajo.
Paso 3: En la ventana abierta de Microsoft Visual Basic for Applications (VBA), pega el siguiente código en el módulo de código de la hoja de trabajo (no en un módulo estándar):
Código VBA: Filtrar tabla dinámica basada en el valor de una celda
Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
Dim xPTable As PivotTable
Dim xPFile As PivotField
Dim xStr As String
On Error Resume Next
If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable2")
Set xPFile = xPTable.PivotFields("Category")
xStr = Target.Text
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True
End Sub📝 Notas:
- "Sheet1" es la hoja de trabajo que contiene la tabla dinámica. Ajusta según sea necesario.
- "PivotTable2" es el nombre de tu tabla dinámica. Puedes encontrarlo en la pestaña Analizar tabla dinámica.
- "Category" es el campo que deseas filtrar. Debe coincidir exactamente con el nombre del campo.
- H6 es la celda de filtrado. Asegúrate de que el valor coincida con un elemento en la lista de filtros.
- Los valores de filtro deben coincidir carácter por carácter. Espacios adicionales o errores tipográficos pueden causar errores o resultados en blanco.
Paso 4: Presiona Alt + Q para cerrar el editor de VBA y regresar a Excel.
Ahora, tu tabla dinámica debería filtrar automáticamente para mostrar solo los datos que coincidan con el valor introducido en la celda H6. Esta macro se ejecuta cada vez que el valor en H6 cambia, lo que facilita ajustar dinámicamente el resumen de tus datos.
Puedes modificar el valor en la celda de filtro en cualquier momento; la tabla dinámica se actualizará instantáneamente siempre que el contenido de la celda cambie o se reemplace.
Solución de problemas:
- Asegúrate de que las macros estén habilitadas en tu libro.
- Verifica nuevamente que los nombres de la hoja de trabajo, la tabla dinámica y los campos coincidan con tu configuración real.
- Asegúrate de que el valor de filtro en H6 coincida exactamente con los valores de la tabla dinámica.
- Este enfoque de VBA funciona para filtros de un solo campo. Para múltiples campos, se requiere un script adicional.
Fórmula de Excel – Mostrar resultados filtrados de la tabla dinámica basados en un valor de celda
Para usuarios que prefieren no habilitar macros, Excel ofrece enfoques basados en fórmulas para mostrar resultados de la tabla dinámica basados en un valor de celda específico. Aunque funciones como GETPIVOTDATA y FILTER no cambian realmente la configuración del filtro de la tabla dinámica, pueden referenciar y presentar dinámicamente resultados resumidos que responden a la entrada del usuario.
Esta solución es especialmente útil al construir tablas de resumen personalizadas, cuadros de mando o informes que reflejan criterios cambiantes ingresados por el usuario, sin alterar la vista original de la tabla dinámica.
Usando GETPIVOTDATA:
Supongamos que tu tabla dinámica (llamada "PivotTable2") resume las ventas por categoría, y el valor de filtro se introduce en la celda H6. Puedes usar GETPIVOTDATA para mostrar las ventas totales para la categoría especificada en H6:
1. Selecciona la celda donde deseas mostrar el resultado del resumen (por ejemplo, I6):
=GETPIVOTDATA("Sum of Sales", $A$4, "Category", $H$6)2. Presiona Enter. Cuando cambies el valor en H6, el resultado en I6 se actualizará automáticamente para reflejar el resumen correspondiente de la tabla dinámica.
Si tu tabla dinámica utiliza diferentes nombres de campos o disposiciones, ajusta la fórmula en consecuencia. Para generar automáticamente una fórmula GETPIVOTDATA, escribe = en una celda, luego haz clic en una celda de valor dentro de tu tabla dinámica. Excel insertará la fórmula apropiada, que puedes editar según sea necesario.
Usando FILTER con una tabla auxiliar:
Si deseas extraer registros detallados de tu conjunto de datos original (en lugar de solo resúmenes de la tabla dinámica), y estás usando Excel 365 o Excel 2019, la función FILTER permite filtrar dinámicamente basado en un valor de celda:
Supongamos que tus datos fuente están en el rango A1:C100 y la Categoría está en la columna A.
1. Selecciona la celda inicial donde deberían aparecer los registros filtrados (por ejemplo, J6):
=FILTER(A2:C100, A2:A100 = H6, "No data")2. Presiona Enter. Las filas coincidentes se desbordarán en celdas adyacentes, listando todos los registros donde la categoría coincide con el valor en H6. Al actualizar H6, los resultados se actualizarán instantáneamente.
Para coincidir con agrupaciones de la tabla dinámica o filtrar por múltiples criterios, considera combinar GETPIVOTDATA y FILTER, o extender la fórmula con condiciones lógicas adicionales.
📝 Consejos y advertencias:
- Estas fórmulas no modifican el filtro real de la tabla dinámica. Solo proporcionan una vista separada y dinámica basada en los valores de las celdas.
- Para cambiar los filtros de la tabla dinámica directamente, se requiere VBA.
- Asegúrate de que los nombres de los campos usados en
GETPIVOTDATAcoincidan exactamente con aquellos en la tabla dinámica (mayúsculas y espaciado). - Si ves errores
#¡REF!, verifica que tus referencias sean válidas y que la estructura de la tabla dinámica no haya cambiado.
Otros métodos integrados de Excel – Usa Segmentadores como filtros interactivos de Tabla Dinámica
Si las soluciones basadas en VBA o fórmulas no se ajustan completamente a tu flujo de trabajo, los Segmentadores de Excel proporcionan otro método interactivo para filtrar tablas dinámicas. Los segmentadores son controles visuales de filtro que permiten a los usuarios filtrar datos con una simple interfaz de clic. Aunque no se pueden vincular directamente a valores de celda —es decir, no puedes cambiar una celda para controlar un segmentador— son intuitivos y muy efectivos para cuadros de mando e informes usados por usuarios no técnicos.
Cómo agregar y usar un Segmentador:
- Selecciona cualquier celda dentro de tu tabla dinámica.
- Ve a la pestaña Analizar tabla dinámica (o pestaña Analizar en versiones anteriores) y haz clic en Insertar segmentador.
- En el cuadro de diálogo Insertar Segmentadores, marca el campo por el cual deseas filtrar (por ejemplo, Categoría), luego haz clic en Aceptar.
- El Segmentador aparecerá en tu hoja de trabajo. Haz clic en un botón para filtrar la tabla dinámica por ese valor. Mantén presionado Ctrl para seleccionar varios elementos.
Los segmentadores se pueden formatear, redimensionar y vincular a múltiples tablas dinámicas para filtrado sincronizado en diferentes informes. Son especialmente útiles en cuadros de mando o libros compartidos donde los usuarios pueden no estar cómodos con filtros desplegables pero aún necesitan filtrar datos fácilmente sin usar VBA o editar fórmulas.
Limitaciones: Los segmentadores no admiten enlace nativo a valores de celda. Si tu flujo de trabajo requiere filtrado dinámico controlado por la entrada de una celda, los segmentadores deben considerarse una herramienta complementaria en lugar de un sustituto para métodos basados en VBA o fórmulas.
Además, si tus datos están almacenados en una tabla de Excel (no en una tabla dinámica), todavía puedes usar segmentadores seleccionando la tabla y yendo a la pestaña Diseño de tabla > Insertar segmentador.
Solución de problemas: Si parece que el segmentador no filtra la tabla dinámica, revisa las Conexiones de informe (bajo la pestaña Segmentador o Analizar) para asegurarte de que esté correctamente conectado a la(s) tabla(s) dinámica(s) deseada(s).
Cada uno de los métodos anteriores sirve para un propósito diferente: VBA permite el filtrado vinculado a una celda, las fórmulas proporcionan una visualización dinámica de los resultados y los segmentadores ofrecen un filtrado gráfico fácil de usar. Elige el enfoque que mejor coincida con tus necesidades de automatización, flexibilidad y facilidad de uso. Los filtros desplegables tradicionales de la tabla dinámica siguen disponibles como una opción básica de respaldo.
Artículos relacionados:
- ¿Cómo combinar múltiples hojas en una tabla dinámica en Excel?
- ¿Cómo crear una tabla dinámica desde un archivo de texto en Excel?
- ¿Cómo vincular el filtro de una tabla dinámica a una celda determinada en Excel?
Las mejores herramientas de productividad para Office
Mejora tu dominio de Excel con Kutools para Excel y experimenta una eficiencia sin precedentes. Kutools para Excel ofrece más de300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haz clic aquí para obtener la función que más necesitas...
Office Tab incorpora la interfaz de pestañas en Office y facilita mucho tu trabajo
- Habilita la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abre y crea varios documentos en nuevas pestañas de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumenta tu productividad en un50% y reduce cientos de clics de ratón cada día!
Todos los complementos de Kutools. Un solo instalador
El paquete Kutools for Office agrupa complementos para Excel, Word, Outlook y PowerPoint junto con Office Tab Pro, ideal para equipos que trabajan en varias aplicaciones de Office.
- Suite todo en uno: complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un solo instalador, una licencia: configuración en minutos (compatible con MSI)
- Mejor juntos: productividad optimizada en todas las aplicaciones de Office
- Prueba completa de30 días: sin registro ni tarjeta de crédito
- La mejor relación calidad-precio: ahorra en comparación con la compra individual de complementos