Como filtrar dados no Excel

Recentemente, escrevi um artigo sobre como usar funções de resumo no Excel para resumir facilmente grandes quantidades de dados, mas esse artigo levou em consideração todos os dados da planilha. E se você quiser apenas examinar um subconjunto de dados e resumir o subconjunto de dados?

No Excel , você pode criar filtros em colunas que ocultarão linhas que não correspondem ao seu filtro. Além disso, você também pode usar funções especiais no Excel para resumir dados usando apenas os dados filtrados.

Neste artigo, mostrarei as etapas para criar filtros no Excel e também usar funções internas para resumir esses dados filtrados.

Criar filtros simples no Excel

No Excel , você pode criar filtros simples e filtros complexos. Vamos começar com filtros simples. Ao trabalhar com filtros, você deve sempre ter uma linha na parte superior que é usada para rótulos. Não é um requisito ter essa linha, mas facilita um pouco o trabalho com filtros.

dados de exemplo excel

Acima, tenho alguns dados falsos e quero criar um filtro na coluna Cidade . (City)No Excel , isso é muito fácil de fazer. Vá em frente e clique na guia Dados na faixa de opções e, em seguida, clique no botão (Data)Filtro(Filter) . Você não precisa selecionar os dados na planilha ou clicar(sheet or click) na primeira linha.

filtro de dados excel

Ao clicar em Filter , cada coluna na primeira linha terá automaticamente um pequeno botão suspenso adicionado à direita.

filtro adicionado excel

Agora vá em frente e clique na seta suspensa na coluna Cidade(City column) . Você verá algumas opções diferentes, que explicarei abaixo.

opções de filtro excel

Na parte superior, você pode classificar rapidamente todas as linhas pelos valores na coluna Cidade(City column) . Observe que, ao classificar os dados, ele moverá a linha inteira, não apenas os valores na coluna Cidade(City column) . Isso garantirá que seus dados permaneçam intactos como antes.

Além disso, é uma boa ideia adicionar uma coluna na frente chamada ID e numerá(ID and number) -la de uma a quantas linhas você tiver em sua planilha. Dessa forma, você sempre pode classificar pela coluna ID(ID column) e recuperar seus dados na mesma ordem em que estavam originalmente, se isso for importante para você.

dados ordenados excel

Como você pode ver, todos os dados da planilha agora são classificados com base nos valores da coluna Cidade(City column) . Até agora, nenhuma linha está oculta. Agora vamos dar uma olhada nas caixas de seleção na parte inferior da caixa de diálogo de filtro(filter dialog) . No meu exemplo, tenho apenas três valores exclusivos na coluna Cidade(City column) e esses três aparecem na lista.

linhas filtradas excel

Eu fui em frente e desmarquei duas cidades e deixei uma marcada. Agora eu tenho apenas 8 linhas de dados sendo exibidas e o restante está oculto. Você pode facilmente dizer que está olhando para dados filtrados se verificar os números das linhas na extrema esquerda. Dependendo de quantas linhas estão ocultas, você verá algumas linhas horizontais extras e a cor dos números será azul.

Agora, digamos que eu queira filtrar em uma segunda coluna para reduzir ainda mais o número de resultados. Na coluna C, tenho o número total de membros em cada família e quero ver os resultados apenas para famílias com mais de dois membros.

filtro de numero excel

Vá em frente e clique na seta suspensa na Coluna C(Column C) e você verá as mesmas caixas de seleção para cada valor exclusivo na coluna. No entanto, neste caso, queremos clicar em Filtros de número(Number Filters) e depois clicar em Maior que( Greater Than) . Como você pode ver, existem várias outras opções também.

é maior que o filtro

Uma nova caixa de diálogo aparecerá e aqui você poderá digitar o valor do filtro. Você também pode adicionar mais de um critério com uma função AND ou OR(AND or OR function) . Você poderia dizer que deseja linhas em que o valor seja maior que 2 e não igual a 5, por exemplo.

dois filtros se destacam

Agora estou com apenas 5 linhas de dados: famílias apenas de Nova Orleans(New Orleans) e com 3 ou mais membros. Fácil(Easy) o suficiente? Observe que você pode limpar facilmente um filtro em uma coluna clicando no menu suspenso e, em seguida, clicando no link Limpar filtro do “Nome da coluna”(Clear Filter From “Column Name”) .

limpar filtro excel

Então é isso para filtros simples no Excel . Eles são muito fáceis de usar e os resultados são bastante diretos. Agora vamos dar uma olhada em filtros complexos usando a caixa de diálogo Filtros avançados .(Advanced)

Criar filtros avançados no Excel

Se você deseja criar filtros mais avançados, deve usar a caixa de diálogo Filtro (filter dialog)avançado(Advanced) . Por exemplo, digamos que eu queira ver todas as famílias que moram em Nova Orleans(New Orleans) com mais de 2 membros em sua família OU(OR) todas as famílias em Clarksville com mais de 3 membros em sua família E(AND) apenas aquelas com um endereço de e-mail final .EDU . Agora você não pode fazer isso com um filtro simples.

Para fazer isso, precisamos configurar a planilha do Excel de(Excel sheet) forma um pouco diferente. Vá em frente e insira algumas linhas acima do seu conjunto de dados e copie os rótulos dos títulos exatamente na primeira linha, como mostrado abaixo.

configuração de filtro avançada

Agora, aqui está como os filtros avançados funcionam. Você deve primeiro digitar seus critérios nas colunas na parte superior e clicar no botão Avançado(Advanced) em Classificar e filtrar( Sort & Filter) na guia Dados .(Data)

faixa de filtro avançada

Então, o que exatamente podemos digitar nessas células? OK, então vamos começar com o nosso exemplo. Nós só queremos ver dados de Nova Orleans ou Clarksville(New Orleans or Clarksville) , então vamos digitá-los nas células E2 e E3(E2 and E3) .

cidade filtro avançado

Quando você digita valores em linhas diferentes, significa OR. Agora queremos famílias de Nova Orleans(New Orleans) com mais de dois membros e famílias Clarksville com mais de 3 membros. Para fazer isso, digite >2 em C2 e >3 em C3.

filtros avançados excel

Como >2 e New Orleans estão na mesma linha, será um operador AND(AND operator) . O mesmo vale para a linha 3(row 3) acima. Finalmente, queremos apenas as famílias com endereço de e-mail final .EDU. Para fazer isso, basta digitar *.edu em D2 e ​​D3(D2 and D3) . O símbolo * significa qualquer número de caracteres.

intervalo de critérios excel

Depois de fazer isso, clique em qualquer lugar em seu conjunto de dados e, em seguida, clique no botão Avançado(Advanced) . O campo List Rang e automaticamente descobrirá seu conjunto de dados desde que você clicou nele antes de clicar no botão Avançado(Advanced button) . Agora clique no pequeno botão à direita do botão de intervalo de critérios(Criteria range) .

selecionar intervalo de critérios

Selecione tudo(Select everything) de A1 a E3 e clique no mesmo botão novamente para voltar à caixa de diálogo Filtro Avançado(Advanced Filter dialog) . Clique em OK(Click OK) e seus dados agora devem ser filtrados!

filtrar resultados

Como você pode ver, agora tenho apenas 3 resultados que atendem a todos esses critérios. Observe que os rótulos do intervalo de critérios precisam corresponder exatamente aos rótulos do conjunto de dados para que isso funcione.

Obviamente, você pode criar consultas muito mais complicadas usando esse método, então brinque com ele para obter os resultados desejados. Por fim, vamos falar sobre a aplicação de funções de soma a dados filtrados.

Resumindo Dados Filtrados

Agora, digamos que eu queira resumir o número de membros da família nos meus dados filtrados. Como eu faria isso? Bem, vamos limpar nosso filtro clicando no botão Limpar(Clear) na faixa de opções. Não se preocupe, é muito fácil aplicar o filtro avançado novamente simplesmente clicando no botão Avançado(Advanced button) e clicando novamente em OK.

limpar filtro no excel

Na parte inferior do nosso conjunto de dados, vamos adicionar uma célula chamada Total e, em seguida, adicionar uma função de soma para somar o total de membros da família. No meu exemplo, acabei de digitar =SUM(C7:C31) .

soma total excel

Então, se eu olhar para todas as famílias, tenho 78 membros no total. Agora vamos em frente e reaplicar nosso filtro Avançado(Advanced filter) e ver o que acontece.

filtro total errado

Opa! Em vez de mostrar o número correto, 11, ainda vejo que o total é 78! Por que é que? Bem, a função SUM(SUM function) não ignora linhas ocultas, portanto, ainda está fazendo o cálculo usando todas as linhas. Felizmente, existem algumas funções que você pode usar para ignorar linhas ocultas.

O primeiro é SUBTOTAL . Antes de usarmos qualquer uma dessas funções especiais, você deve limpar seu filtro e digitar a função.

Depois que o filtro estiver limpo, vá em frente e digite =SUBTOTAL( e você verá uma caixa suspensa com várias opções. Usando esta função, você primeiro escolhe o tipo de função de soma(summation function) que deseja usar usando um número.

Em nosso exemplo, eu quero usar SUM , então eu digitaria o número 9(number 9) ou apenas clicaria nele na lista suspensa. Em seguida, digite uma vírgula e selecione o intervalo de células.

função subtotal

Ao pressionar enter, você verá que o valor de 78 é o mesmo que anteriormente. No entanto, se você agora aplicar o filtro novamente, veremos 11!

subtotal no filtro

Excelente! É exatamente isso que queremos. Agora você pode ajustar seus filtros e o valor sempre refletirá apenas as linhas que estão sendo exibidas no momento.

A segunda função que funciona exatamente da mesma forma que a função SUBTOTAL(SUBTOTAL function) é AGGREGATE . A única diferença é que há outro parâmetro na função AGGREGATE(AGGREGATE function) onde você deve especificar que deseja ignorar linhas ocultas.

função agregada

O primeiro parâmetro é a função de soma que(summation function) você deseja usar e, assim como SUBTOTAL , 9 representa a função SUM(SUM function) . A segunda opção é onde você deve digitar 5 para ignorar as linhas ocultas. O último parâmetro é o mesmo e é o intervalo de células.

Você também pode ler meu artigo sobre funções de resumo para aprender a usar a função AGGREGATE(AGGREGATE function)  e outras funções como MODE , MEDIAN , AVERAGE , etc. com mais detalhes.

Espero que este artigo seja um bom ponto de partida(starting point) para criar e usar filtros no Excel . Se você tiver alguma dúvida, sinta-se à vontade para postar um comentário. Aproveitar!



About the author

Josh tem mais de 10 anos de experiência na indústria de software e wireless, especificamente nas áreas de programação e análises do Android. Atualmente, ele é engenheiro de software sênior da Microsoft, trabalhando em vários produtos do MS Office. Josh tem um forte interesse em ajudar outras pessoas a aprender novas ferramentas de software e está sempre disposto a compartilhar suas dicas e truques com aqueles que perguntam.



Related posts