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.
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.
Ao clicar em Filter , cada coluna na primeira linha terá automaticamente um pequeno botão suspenso adicionado à direita.
Agora vá em frente e clique na seta suspensa na coluna Cidade(City column) . Você verá algumas opções diferentes, que explicarei abaixo.
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ê.
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.
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.
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.
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.
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”) .
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.
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)
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) .
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.
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.
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) .
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!
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.
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) .
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.
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.
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!
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.
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!
Related posts
Mapeando seus dados do Excel
Use a nova função “Inserir dados da imagem” do Excel Mobile
Use funções de resumo para resumir dados no Excel
Use o Excel como uma ferramenta para copiar dados da Web
Adicionar um Regression Trendline Linear a um Excel Scatter Plot
Como Automatically Backup um Word Document para onedrive
Use o Excel para descobrir uma taxa de juros efetiva a partir de uma taxa de juros nominal
Como ocultar planilhas, células, colunas e fórmulas no Excel
Como calcular Variance em Excel
Migrar dados do MS Access para o banco de dados SQL Server
Como rastrear alterações no Excel
Como criar um Flowchart em Word and Excel
Um guia para todas as extensões de arquivo do Excel e o que elas significam
13 OneNote Tips & Tricks para organizar suas anotações Melhor
AutoAjustar Larguras de Colunas e Alturas de Linhas no Excel
Como remover, quebrar ou quebrar uma senha esquecida do Excel XLS
Como segurar Password Protect um Excel File
Como usar PROCV no Excel
Como usar os recursos de AutoRecuperação e Backup Automático do Excel
Como adicionar e imprimir imagens de plano de fundo do Excel