Filtrar una tabla usando información en celdas fuera de la tabla

Question

Tengo una tabla que extrae información de múltiples fuentes diferentes a lo largo de mi libro de trabajo. Hay 4 categorías por las que la tabla se filtrará regularmente y será utilizada por usuarios que no son buenos en Excel.

Me gustaría configurar una sección de filtrado con listas que filtren la siguiente tabla. La captura de pantalla muestra la configuración propuesta con los valores de la lista en las celdas , B4y la siguiente tabla es lo que se filtraría según los valores que se ingresan.C4D4E4

¿Alguien sabe cómo lograr esto o algo similar?

^{(Haga clic en la imagen para ampliar)}

Answer 1

¿Quiere decir que desea filtrar por un valor de celda específico, en lugar de filtrar con el filtrado de tabla integrado?

Bueno, puedes llamar al autofiltro con una macro y usar el valor de las celdas como criterio.

Puede colocar la macro en la hoja y hacer que se llame automáticamente cada vez que una de las celdas relevantes cambie con una Worksheet_Changesub.

Hay muchas formas de escribir esto, pero debemos especificar qué tabla queremos filtrar y también debemos especificar qué fila filtrar.

Este es el código que puse en la hoja (haciendo clic derecho en la pestaña de la hoja y seleccionando "mostrar código") para mi ejemplo:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tabl As String, i As Long, C as Range
tabl = "Table1" 'Name of the Table
If Not Application.Intersect(Range(Target.Address), Range("B4:E4")) Is Nothing And Target.Count < 5 Then
    For Each C In Target
        tCol = Range(tabl & "[" & C.Offset(-1).Value2 & "]").Column
        If C.Value2 = "" Then
            ListObjects(tabl).Range.AutoFilter Field:=tCol
        Else
            ListObjects(tabl).Range.AutoFilter Field:=tCol, Criteria1:=C.Value2
        End If
    Next C
End If
End Sub

Resultado:

Editar
Se agregó un bucle para poder borrar todos los filtros a la vez.

Este código requiere que sus encabezados sean los mismos en la celda de entrada y en la tabla, o generará un error.

-

Worksheet_ChangeSe llama cuando se cambian las celdas de la hoja de trabajo.
Esto se usa a menudo junto con
If Not Application.Intersect(Range(Target.Address), Range("")) Is Nothing Then
para restringir la ejecución del submarino a menos que se apunten a áreas específicas.

Tener una restricción Target.Counttambién es una buena idea para evitar que el código falle. A menudo querrás restringirlo a un solo objetivo, a menos que tengas la intención de realizar un bucle en la selección, como hice yo en este caso.

Si desea tener listas desplegables con las opciones, aquí hay un enfoque VBA:excel-eliminación-de-duplicados-con-validación-de-datos

Answer 2

Me gustaría sugerir una fórmula de matriz (CSE) que le ayudará a extraer varias filas según algunos criterios.

Cómo funciona:

• Supongo que los datos de origen están dentro del rango A2:E10.
• El rango de criterios es A16:E16.
• Ingrese esta fórmula en la celda A20, termine con Ctrl+Mayús+Entrar, llene Derecha y luego Abajo.

{=IFERROR(INDEX($A$2:$E$10,SMALL(IF(MMULT(($A$2:$E$10=$A$16:$E$16)*1,{1;1;1;1;1;1;1})=COUNTA($A$16:$E$16),MATCH(ROW($A$2:$A$10),ROW($A$2:$A$10)),""),ROWS($A$1:A1)),COLUMNS($A$1:A1)),"")}

Nota:

• Si la celda/celdas de criterios quedan en blanco, esta fórmula devolverá todos los registros.

• Puede poner uno o más de un criterio para filtrar registros relacionados.

Déjame explicarte el mecanismo del Comando.

• MMULT functionNo puedo trabajar con valores booleanos, por lo que para que funcione, la fórmula debe multiplicar la matriz por 1.

MMULT(($A$2:$E$10=$A$16:$E$16)*1,{1;1;1;1;1;1;1})

se convierte,

MMULT({0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 1, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0},{1;1;1;1;1;1;1})

y regresa,

{0;0;1;0;0;2;1;1;2;1}

y,

MMULT(($A$2:$E$10=$A$16:$E$16)*1, {1;1;1;1;1;1;1})=COUNTA($A$16:$E$16)

se convierte,

{0;0;1;0;0;2;1;1;2;1}=COUNTA($A$16:$E$16)

se convierte,

{0;0;1;0;0;2;1;1;2;1}=2

y regresa,

{FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}.

IF(MMULT(($A$2:$E$10=$A$16:$E$16)*1, {1;1;1;1;1;1;1})=COUNTA($A$16:$E$16), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), "")

se convierte,

IF({FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), "")

• PEQUEÑOLa función obtiene el k-ésimo número más pequeño en una matriz.

• ÍNDICELa función devuelve un valor de un rango o matriz de celda, según un número de fila y columna.