Filtrar uma tabela usando informações em células fora da tabela

Question

Tenho uma tabela que extrai informações de várias fontes diferentes em toda a minha pasta de trabalho. Existem 4 categorias nas quais a tabela será filtrada regularmente e usada por usuários que não são bons em Excel.

Gostaria de montar uma seção de filtragem com listas que filtrassem a tabela abaixo. A captura de tela mostra a configuração proposta com os valores da lista nas células , , B4e C4a tabela abaixo sendo o que seria filtrado com base nos valores inseridos.D4E4

Alguém sabe como fazer isso ou algo semelhante?

^{(Clique na imagem para ampliar)}

Answer 1

Você quer dizer que deseja filtrar por um valor de célula específico, em vez de filtrar com a filtragem de tabela integrada?

Bem, você pode chamar o filtro automático com uma macro e usar o valor das células como critério.

Você pode colocar a macro na planilha e chamá-la automaticamente sempre que uma das células relevantes for alterada por uma Worksheet_Changesub.

Há muitas maneiras de escrever isso, mas precisamos especificar qual tabela queremos filtrar e também qual linha filtrar.

Este é o código que coloquei na planilha (clicando com o botão direito na guia da planilha e selecionando "mostrar código") para o meu exemplo:

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
Adicionado loop para poder limpar todos os filtros de uma vez.

Este código exige que seus cabeçalhos sejam iguais na célula de entrada e na tabela, ou ocorrerá um erro.

-

Worksheet_Changeé chamado quando as células da planilha são alteradas.
Isso geralmente é usado em conjunto com
If Not Application.Intersect(Range(Target.Address), Range("")) Is Nothing Then
para restringir a execução do submarino, a menos que áreas específicas sejam direcionadas.

Ter uma restrição Target.Counttambém é uma boa ideia, para evitar que o código trave. Muitas vezes você deseja restringi-lo a um único alvo, a menos que pretenda fazer um loop na seleção, como fiz neste caso.

Se você gosta de listas suspensas com as opções, há uma abordagem VBA aqui:excel removendo duplicatas com validação de dados

Answer 2

Eu gostaria de sugerir uma fórmula Array (CSE), que irá ajudá-lo a extrair várias linhas com base em poucos critérios.

Como funciona:

• Presumo que os dados de origem estejam em Range A2:E10.
• O intervalo de critérios é A16:E16.
• Insira esta fórmula na célula A20e termine com Ctrl+Shift+Enter, preencha para a direita e depois para baixo.

{=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)),"")}

Observação:

• Se Células/Células de Critérios ficarem em branco, esta Fórmula retornará Todos os registros.

• Você pode colocar um ou mais critérios para filtrar registros relacionados.

Deixe-me explicar o mecanismo do Comando.

• MMULT functionnão pode trabalhar com valores booleanos, portanto, para que isso funcione, a Fórmula deve multiplicar a matriz por 1.

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

torna-se,

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})

e retorna,

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

e,

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

torna-se,

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

torna-se,

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

e retorna,

{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)), "")

torna-se,

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

• PEQUENOA função obtém o k-ésimo menor número em um array.

• ÍNDICEA função retorna um valor de um intervalo ou matriz de células, com base em um número de linha e coluna.