Para a grande maioria dos usuários do Excel, quando a palavra “filtragem de dados” surge em sua cabeça, apenas o filtro clássico usual da guia Dados - Filtro (Dados - Filtro):
Esse filtro é uma coisa familiar, sem dúvida, e na maioria dos casos serve. No entanto, há situações em que você precisa filtrar por um grande número de condições complexas em várias colunas de uma só vez. O filtro usual aqui não é muito conveniente e eu quero algo mais poderoso. Tal ferramenta poderia ser filtro avançado, especialmente com um pouco de “acabamento com arquivo” (segundo a tradição).
Base
Para começar, insira algumas linhas vazias acima de sua tabela de dados e copie o cabeçalho da tabela para lá – este será um intervalo com condições (destacado em amarelo para maior clareza):
Deve haver pelo menos uma linha vazia entre as células amarelas e a tabela original.
É nas células amarelas que você precisa inserir os critérios (condições), segundo os quais a filtragem será realizada. Por exemplo, se você precisar selecionar bananas no "Auchan" de Moscou no III trimestre, as condições serão assim:
Para filtrar, selecione qualquer célula do intervalo com os dados de origem, abra a guia Data E clique no botão Adicionalmente (Dados — Avançado). Na janela que se abre, um intervalo com dados já deve ser inserido automaticamente e teremos apenas que especificar o intervalo de condições, ou seja, A1:I2:
Observe que o intervalo de condições não pode ser alocado “com margem”, ou seja, você não pode selecionar linhas amarelas vazias extras, porque uma célula vazia no intervalo de condições é percebida pelo Excel como a ausência de um critério, e uma célula vazia linha como um pedido para exibir todos os dados indiscriminadamente.
Interruptor Copie o resultado para outro local permitirá que você filtre a lista não exatamente nesta planilha (como com um filtro normal), mas para descarregar as linhas selecionadas em outro intervalo, que precisará ser especificado no campo Colocar resultado no intervalo. Neste caso, não usamos esta função, deixamos Lista de filtros em vigor e clique OK. As linhas selecionadas serão exibidas na planilha:
Adicionando uma macro
“Bem, onde está a conveniência aqui?” você pergunta e você estará certo. Você não apenas precisa inserir condições nas células amarelas com as mãos, mas também abrir uma caixa de diálogo, inserir intervalos lá, pressionar OK. Triste, concordo! Mas “tudo muda quando eles vêm ©” – macros!
Trabalhar com um filtro avançado pode ser bastante acelerado e simplificado usando uma macro simples que executará automaticamente o filtro avançado quando as condições forem inseridas, ou seja, alterando qualquer célula amarela. Clique com o botão direito do mouse na guia da planilha atual e selecione o comando Texto original (Código fonte). Na janela que se abre, copie e cole o seguinte código:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Não é Nada Então On Erro Retomar Próximo ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1").CurrentRegion End If End Sub Este procedimento será executado automaticamente quando qualquer célula da planilha atual for alterada. Se o endereço da célula alterada estiver na faixa amarela (A2:I5), essa macro remove todos os filtros (se houver) e reaplica o filtro estendido à tabela de dados de origem começando com A7, ou seja, tudo será filtrado instantaneamente, imediatamente depois de inserir a próxima condição:
Assim, tudo é muito melhor, certo? 🙂
Implementando consultas complexas
Agora que tudo está sendo filtrado em tempo real, podemos ir um pouco mais fundo nas nuances e desmontar os mecanismos de consultas mais complexas no filtro avançado. Além de inserir correspondências exatas, você pode usar vários caracteres curinga (* e ?) e sinais de desigualdade matemática em várias condições para implementar uma pesquisa aproximada. O caso do personagem não importa. Para maior clareza, resumi todas as opções possíveis em uma tabela:
| Critério | Resultado |
| gr* ou gr | todas as células começando com GrIe Grorelha, Grabacaxi, Granatomia etc. |
| = cebola | todas as células exatamente e apenas com a palavra Arco, ou seja, correspondência exata |
| *vivo* ou *vivo | células contendo Liv como sublinhado, ou seja ОLivque, Livep, SegundoLiv etc. |
| =p*v | palavras que começam com П e terminando com В ie Пprimeiroв, Пéterв etc. |
| Como | palavras que começam com А e contendo ainda СIe Аpelсin, Аnanaс, Asai etc. |
| =*s | palavras terminadas em С |
| =???? | todas as células com texto de 4 caracteres (letras ou números, incluindo espaços) |
| =m??????n | todas as células com texto de 8 caracteres começando com М e terminando com НIe Мandariн, Мansiedadeн etc. |
| =*n??a | todas as palavras terminadas com А, onde é a 4ª letra a partir do final НIe vigaнikа, Segundoнozа etc. |
| >=e | todas as palavras que começam com Э, Ю or Я |
| <>*o* | todas as palavras que não contêm uma letra О |
| <>*vich | todas as palavras, exceto aquelas terminadas em HIV (por exemplo, filtrar mulheres pelo nome do meio) |
| = | todas as células vazias |
| <> | todas as células não vazias |
| > = 5000 | todas as células com valor maior ou igual a 5000 |
| 5 ou = 5 | todas as células com valor 5 |
| > = 3/18/2013 | todas as células com data posterior a 18 de março de 2013 (inclusive) |
Pontos sutis:
- O sinal * significa qualquer número de caracteres e ? – qualquer personagem.
- A lógica no processamento de consultas de texto e numéricas é um pouco diferente. Assim, por exemplo, uma célula de condição com o número 5 não significa procurar todos os números que começam com cinco, mas uma célula de condição com a letra B é igual a B*, ou seja, procurará qualquer texto que comece com a letra B.
- Se a consulta de texto não começar com o sinal =, você pode colocar mentalmente * no final.
- As datas devem ser inseridas no formato americano mês-dia-ano e por fração (mesmo se você tiver Excel e configurações regionais).
Conectivos lógicos AND-OR
Condições escritas em células diferentes, mas na mesma linha, são consideradas interconectadas por um operador lógico И (E):
Aqueles. filtro bananas para mim no terceiro trimestre, precisamente em Moscou e ao mesmo tempo de Auchan.
Se você precisar vincular condições com um operador lógico OR (OR), então eles só precisam ser inseridos em linhas diferentes. Por exemplo, se precisarmos encontrar todos os pedidos do gerente Volina para pêssegos de Moscou e todos os pedidos de cebolas no terceiro trimestre em Samara, isso pode ser especificado em várias condições da seguinte forma:
Se você precisar impor duas ou mais condições em uma coluna, basta duplicar o cabeçalho da coluna no intervalo de critérios e inserir o segundo, terceiro etc. abaixo dele. termos. Assim, por exemplo, você pode selecionar todas as transações de março a maio:
Em geral, depois de “terminar com um arquivo”, um filtro avançado se mostra uma ferramenta bastante decente, em alguns lugares não pior que um autofiltro clássico.
- Superfiltro em macros
- O que são macros, onde e como inserir código de macro no Visual Basic
- Tabelas inteligentes no Microsoft Excel