¿Cómo filtrar una tabla dinámica según el valor de una celda específica en Excel?
En Excel, los Tabla Dinámica se utilizan ampliamente para resumir, analizar y explorar datos de forma eficiente. De forma predeterminada, el filtrado dentro de un Tabla Dinámica suele realizarse seleccionando los elementos deseados en el menú desplegable del filtro. Aunque este enfoque ofrece flexibilidad, existen ciertos escenarios en los que se necesita un método de filtrado más dinámico; por ejemplo, puede desear que los resultados del Tabla Dinámica cambien automáticamente en función del valor introducido en una celda específica de la hoja de cálculo. Esto resulta especialmente útil al preparar paneles, automatizar flujos de trabajo o crear informes interactivos para usuarios finales que podrían no sentirse cómodos con el filtrado manual.
Excel no incluye una función estándar que vincule de forma nativa el valor de una celda a un filtro de tabla dinámica (sin recurrir a código). No obstante, existen varias técnicas prácticas para lograrlo, cada una con sus propias ventajas y consideraciones. Este tutorial comienza presentando un método sencillo con VBA que conecta directamente una celda a un filtro de tabla dinámica, de modo que esta se actualice al instante cuando cambie el valor de la celda. Además, exploraremos alternativas como el uso de fórmulas de Excel (por ejemplo, GETPIVOTDATA o FILTER) para mostrar resultados filtrados, así como el empleo de segmentadores como controles gráficos de filtrado. Conocer estas opciones le permitirá elegir el enfoque más adecuado para su flujo de trabajo en Excel y la experiencia del usuario.
➤ Filtrar Tabla Dinámica según un valor específico de celda con código VBA
➤ Fórmula de Excel – Mostrar resultados filtrados de Tabla Dinámica según un valor de celda
➤ Otros métodos integrados de Excel – Usar segmentadores como filtros interactivos de tablas dinámicas
Filtrar Tabla Dinámica en función del valor de una celda específica con código VBA
Si desea una interactividad verdaderamente dinámica —es decir, que al escribir un valor en una celda el filtro de la Tabla Dinámicaresponda automáticamente al cambio—, VBA ofrece una solución directa. Esta funcionalidad resulta especialmente útil en paneles, plantillas para compañeros de trabajo o situaciones en las que necesite ajustar rápidamente el filtro modificando únicamente una celda. No obstante, este método requiere una familiaridad básica con el editor de VBA y, como ocurre con todas las macros, su libro debe guardarse en un formato habilitado para macros ().xlsm).
El siguiente código VBA le permite vincular dinámicamente una celda de la hoja de cálculo a un filtro de Tabla Dinámica. Siga estos pasos cuidadosamente y asegúrese de modificar el Nombre de la hoja de cálculo, el nombre del Tabla Dinámica y la referencia del campo según sea necesario en su libro:
Paso 1:Introduzca en una celda de la hoja de cálculo el valor por el que desea filtrar su Tabla Dinámica (por ejemplo, escriba o seleccione el valor de filtrado en la celda)H6).
Paso 2: Abra la hoja de cálculo que contiene su Tabla Dinámica. Haga clic con el botón derecho en la pestaña de la hoja, en la parte inferior de Excel, y seleccione Ver código en el menú contextual. Esto abrirá la ventana del editor de VBA correspondiente a la hoja.
Paso 3:En la ventana abierta de Microsoft Visual Basic para Aplicaciones(VBA), pegue el siguiente código en el módulo de código de la hoja (no en un módulo estándar):
Código VBA: Filtrar Tabla Dinámica en función del valor de una celda
Private Sub Worksheet_Change(ByVal Target As Range)
'Actualizado por 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 cálculo que contiene la tabla dinámica. Ajuste según sea necesario.
- "PivotTable2" es el nombre de su Tabla dinámica. Puede encontrarlo en la pestaña PivotTable Analyze.
- «Category» es el campo que deseas filtrar. Debe coincidir exactamente con el nombre de la condición.
- H6 es la celda de filtrado. Asegúrese de que el valor coincida con un elemento de la lista de filtros.
- Los valores del filtro deben coincidir carácter por carácter. Los espacios adicionales o errores tipográficos pueden provocar errores o resultados en blanco.
Paso 4: Pulse Alt + Q para cerrar el editor de VBA y volver a Excel.
Ahora, su tabla dinámica se filtrará automáticamente para mostrar únicamente los datos que coincidan con el valor introducido en la celda H6. Esta macro se ejecuta cada vez que cambia el valor en H6, facilitando un ajuste dinámico del resumen de sus datos.
Puede modificar el valor en la celda de filtrado en cualquier momento: la tabla dinámica se actualizará al instante cada vez que cambie o reemplace el contenido de la celda.
Resolución de problemas:
- Asegúrese de que las macros estén habilitadas en su libro.
- Compruebe cuidadosamente que la hoja de cálculo, la tabla dinámica y el nombre de la condición coincidan con su configuración real.
- Asegúrese de que el valor del filtro en H6 coincida exactamente con los valores de la tabla dinámica.
- Este enfoque con VBA funciona para filtros de un solo campo. Para varios campos, se requiere una programación adicional.
Fórmula de Excel – Mostrar resultados filtrados del Tabla Dinámica en función del valor de una celda
Para los usuarios que prefieren no habilitar macros, Excel ofrece enfoques basados en fórmulas para mostrar resultados de la tabla dinámica en función del valor de una celda específica. Aunque funciones como GETPIVOTDATA y FILTER no modifican realmente la configuración del filtro de la tabla dinámica, pueden hacer referencia dinámicamente a los resultados resumidos y presentarlos en respuesta a la entrada del usuario.
Esta solución resulta especialmente útil al crear tablas resumen personalizadas, paneles o informes que reflejen criterios cambiantes introducidos por el usuario, sin alterar la vista original del Tabla Dinámica.
Uso de GETPIVOTDATA:
Supongamos que su tabla dinámica (denominada)"PivotTable2") resume las ventas por categoría y que el valor del filtro se introduce en la celda H6. Puede utilizar la función GETPIVOTDATA para mostrar las ventas totales de la categoría especificada en H6:
1.Seleccione la celda en la que desea mostrar el resultado resumido (por ejemplo,)I6):
=GETPIVOTDATA("Sum of Sales", $A$4, "Category", $H$6)2. Pulse Intro. Cuando cambie el valor en H6, el resultado en I6 se actualizará automáticamente para reflejar el resumen correspondiente de la Tabla Dinámica.
Si su tabla dinámica utiliza un nombre de condición distinto o un diseño diferente, ajuste la fórmula en consecuencia. Para generar automáticamente una fórmula de GETPIVOTDATA, escriba = en una celda y, a continuación, haga clic en una celda con valor dentro de su tabla dinámica. Excel insertará la fórmula adecuada, que podrá editar según sus necesidades.
Uso de FILTER con una tabla auxiliar:
Si desea extraer registros detallados de su conjunto de datos original (en lugar de únicamente resúmenes del Tabla Dinámica), y utiliza Excel 365 o Excel 2019, la función FILTERpermite realizar un filtrado dinámico en función del valor de una celda:
Suponga que sus datos de origen están en el rango A1:C100 y que la columna Category se encuentra en la columna A.
1.Seleccione la celda inicial donde deben aparecer los registros filtrados (por ejemplo,)J6):
=FILTER(A2:C100, A2:A100 = H6, "No data")2. Pulse Entrar. Las filas coincidentes se expandirán automáticamente en las celdas adyacentes, mostrando todos los registros cuya categoría coincida con el valor de H6. Al actualizar H6, los resultados se refrescarán al instante.
Para coincidir con agrupaciones de una tabla dinámica o filtrar según varios criterios, considere combinar GETPIVOTDATA y FILTRAR, o amplíe la fórmula con condiciones lógicas adicionales.
📝 Consejos y advertencias:
- Estas fórmulas no modifican el filtro real del Tabla Dinámica. Únicamente proporcionan una vista independiente y dinámica basada en los valores de las celdas.
- Para cambiar directamente los filtros del Tabla Dinámica, se requiere VBA.
- Asegúrese de que los nombres de las condiciones utilizados en la función
GETPIVOTDATAcoincidan exactamente con los de la tabla dinámica (incluyendo mayúsculas y espacios). - Si ve errores
#REF!, compruebe que sus referencias sean válidas y que la estructura de la tabla dinámica no haya cambiado.
Otros métodos integrados de Excel – Use segmentadores como filtros interactivos de Tabla Dinámica
Si las soluciones basadas en VBA o fórmulas no se adaptan completamente a su flujo de trabajo, los segmentadores de Excel ofrecen un método interactivo alternativo para filtrar una tabla dinámica. Los segmentadores son controles visuales que permiten filtrar datos mediante una interfaz intuitiva de un solo clic. Aunque no se pueden vincular directamente a valores de celdas —es decir, no puede modificar una celda para controlar un segmentador—, resultan muy eficaces e intuitivos en paneles e informes destinados a usuarios no técnicos.
Cómo agregar y usar un segmentador:
- Seleccione cualquier celda dentro de su Tabla Dinámica.
- Vaya a la pestaña PivotTable Analyze(o a la pestaña)Analyze en versiones anteriores) y haga clic en Insert Slicer.
- En el cuadro de diálogo Insert Slicers, marque el campo por el que desea filtrar (por ejemplo,)Category) y, a continuación, haga clic en OK.
- El segmentador aparecerá en su hoja de cálculo. Haga clic en un botón para filtrar la Tabla Dinámica por ese valor. Mantenga pulsada la tecla Ctrl para seleccionar varios elementos.
Los segmentadores se pueden formatear, redimensionar y vincular a varias Tabla Dinámica para lograr un filtrado sincronizado en distintos informes. Son especialmente útiles en paneles o libros compartidos en los que los usuarios no estén cómodos con los filtros desplegables, pero necesiten filtrar datos fácilmente sin recurrir a VBA ni editar fórmulas.
Limitaciones: Los segmentadores no admiten la vinculación nativa a valores de celdas. Si tu flujo de trabajo requiere un filtrado dinámico controlado mediante entradas en celdas, considera los segmentadores como una herramienta complementaria y no como sustituto de los métodos basados en VBA o fórmulas.
Además, si sus datos están almacenados en una Tabla de Excel (no en una tabla dinámica), aún puede usar segmentadores seleccionando la tabla e yendo a la pestaña Diseño de tabla > Insertar segmentador.
Resolución de problemas: Si el segmentador no parece filtrar la tabla dinámica, compruebe las conexiones del informe(en la pestaña)Segmentador o Analizar) para asegurarse de que esté correctamente vinculado a la(s) tabla(s) dinámica(s) correspondiente(s).
Cada uno de los métodos anteriores cumple una finalidad distinta: VBA permite el filtrado vinculado directamente a celdas, las fórmulas ofrecen una visualización dinámica de los resultados y los segmentadores proporcionan un filtrado gráfico fácil de usar. Elija el enfoque que mejor se adapte a sus necesidades en cuanto a automatización, flexibilidad y facilidad de uso. Los filtros desplegables tradicionales de Tabla Dinámica siguen disponibles como opción básica alternativa.
Artículos relacionados:
- ¿Cómo combinar varias hojas en un Tabla Dinámica en Excel?
- ¿Cómo crear un Tabla Dinámica a partir de Archivo de Texto en Excel?
- ¿Cómo vincular el filtro de Tabla Dinámica a una celda determinada en Excel?
Las mejores herramientas de productividad para Office
Potencie sus habilidades en Excel con Kutools para Excel y experimente una eficiencia como nunca antes.Kutools para Excel ofrece más de 300 funciones avanzadas para aumentar su productividad y Ahorrar tiempo.Haga clic aquí para obtener la función que más necesita...
Office Tab aporta una interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Active la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas dentro de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y elimine cientos de clics del ratón cada día!
Todos los complementos de Kutools en un solo instalador.
Kutools for Office es la suite que incluye complementos para Excel, Word, Outlook y PowerPoint, además de Office Tab Pro, ideal para equipos que trabajan en distintas aplicaciones de Office.
- Suite integral— complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un instalador, una licencia— configuración en minutos (compatible con MSI)
- Rendimiento mejorado en conjunto— productividad optimizada en todas las aplicaciones de Office
- Prueba gratuita de 30 días con todas las funciones— sin registro ni tarjeta de crédito
- La mejor relación calidad-precio— ahorre frente a la compra individual de complementos