Você pode exibir informações sobre um/vários parâmetros usando filtragem de dados no Excel.

Existem duas ferramentas para essa finalidade: AutoFiltro e Filtro Avançado. Eles não excluem, mas ocultam dados que não atendem às condições. O filtro automático executa as operações mais simples. O filtro avançado tem muito mais opções.

AutoFiltro e Filtro Avançado no Excel

Tenho uma tabela simples que não está formatada nem declarada como lista. Você pode ativar o filtro automático através do menu principal.

Se você formatar o intervalo de dados como uma tabela ou declará-lo como uma lista, o filtro automático será adicionado imediatamente.

Usar um filtro automático é simples: você precisa selecionar a entrada com o valor desejado. Por exemplo, exiba as entregas para a loja nº 4. Coloque uma marca de seleção ao lado da condição de filtragem correspondente:

Vemos imediatamente o resultado:

Recursos da ferramenta:

1. O filtro automático funciona apenas em uma faixa ininterrupta. Tabelas diferentes na mesma planilha não são filtradas. Mesmo que tenham o mesmo tipo de dados.
2. A ferramenta trata a linha superior como cabeçalhos de coluna – esses valores não estão incluídos no filtro.
3. É permitido aplicar várias condições de filtragem ao mesmo tempo. Mas cada resultado anterior pode ocultar os registros necessários para o próximo filtro.

O filtro avançado tem muito mais recursos:

1. Você pode definir quantas condições de filtragem forem necessárias.
2. Os critérios para seleção de dados são visíveis.
3. Usando o filtro avançado, o usuário pode encontrar facilmente valores exclusivos em uma matriz multilinha.



Como fazer um filtro avançado no Excel

Um exemplo pronto - como usar um filtro avançado no Excel:

Apenas as linhas contendo o valor “Moscou” permaneceram na tabela original. Para cancelar a filtragem, você precisa clicar no botão “Limpar” na seção “Classificar e Filtrar”.

Como usar o filtro avançado no Excel

Vamos considerar o uso de um filtro avançado no Excel para selecionar linhas contendo as palavras “Moscou” ou “Ryazan”. As condições de filtragem devem estar na mesma coluna. No nosso exemplo - um abaixo do outro.

Preenchendo o menu de filtros avançados:

Obtemos uma tabela com linhas selecionadas de acordo com um determinado critério:

Vamos selecionar as linhas que contêm o valor “Nº 1” na coluna “Loja” e “>1.000.000 rublos” na coluna de custo. Os critérios de filtragem devem estar nas colunas apropriadas da tabela de condições. Em uma linha.

Preencha os parâmetros de filtragem. Clique OK.

Deixemos na tabela apenas as linhas que contêm a palavra “Ryazan” na coluna “Região” ou o valor “>10.000.000 rublos” na coluna “Custo”. Como os critérios de seleção pertencem a colunas diferentes, nós os colocamos em linhas diferentes sob os títulos correspondentes.

Vamos usar a ferramenta “Filtro Avançado”:

Esta ferramenta pode trabalhar com fórmulas, o que permite ao usuário resolver praticamente qualquer problema ao selecionar valores em arrays.

Regras básicas:

1. O resultado da fórmula é o critério de seleção.
2. A fórmula escrita retorna VERDADEIRO ou FALSO.
3. O intervalo inicial é especificado por meio de referências absolutas, e o critério de seleção (na forma de uma fórmula) é especificado por meio de referências relativas.
4. Se TRUE for retornado, a linha será exibida após a aplicação do filtro. FALSO – não.

Vamos exibir linhas contendo quantidades acima da média. Para isso, além da placa com os critérios (na célula I1), inserimos o nome “Maior quantidade”. Abaixo está a fórmula. Usamos a função MÉDIA.

Selecione qualquer célula no intervalo de origem e chame “Filtro Avançado”. Indicamos I1:I2 como critério de seleção (links relativos!).

Apenas as linhas onde os valores da coluna “Quantidade” estão acima da média permanecem na tabela.

Para deixar apenas linhas não repetidas na tabela, na janela “Filtro avançado”, marque a caixa ao lado de “Apenas registros únicos”.

Clique OK. As linhas duplicadas ficarão ocultas. Somente entradas exclusivas permanecerão na planilha.

O que fazer se, a partir de uma grande quantidade de informações, você precisar encontrar os dados necessários relacionados a uma pessoa ou data específica? E então, por exemplo, copiá-lo para uma folha separada? A maneira mais fácil de criar um filtro automático é no Excel. A peculiaridade do filtro é ocultar a quantidade de informações que não atendem às suas necessidades e deixar apenas os dados solicitados pelo filtro. As strings geradas pelo filtro podem ser formatadas, impressas, editadas, etc.

Além disso, você já usou um filtro avançado? Uma ferramenta bastante útil!

Então, como fazer um filtro automático no Excel (selecionar apenas as células necessárias)?

Você pode selecionar as células desejadas com um valor específico por cor ou por condição de duas maneiras simples:

1. Seguindo o caminho na faixa “Dados” - “Classificação e Filtro” - “Filtro Automático” ( veja a foto no início do artigo)

2. Ao clicar com o botão direito após selecionar a tabela, no menu que se abre, filtro - “Filtro”

Condições básicas para usar o filtro automático:

• Capacidade de trabalhar apenas com uma lista completa. Aqueles. se houver duas listas, o filtro não poderá ser aplicado.
• Você pode filtrar por duas colunas. Mas primeiro você precisa filtrar um e depois o segundo.
• Você também pode filtrar os dados por cor ou pelo conteúdo de alguma parte da frase.
• Você pode realizar uma ampla variedade de tarefas com valores numéricos

Filtro avançado. Como filtrar por condição em uma célula

O filtro avançado é usado para condições de seleção mais complexas. Use-o se

— você precisa definir um grande número de condições de uma só vez
— você precisa exibir a lista recebida em um local separado.

A essência do filtro avançado é que na tabela adjacente (t2) você indica todas as condições de filtragem (t1) e as exibe na nova tabela t3.

Antes de trabalhar com o filtro automático avançado, você precisará preparar uma mesa. Aqueles. mova os títulos da tabela existente para um local separado e em uma coluna separada

Para localizá-lo, abra a aba “Dados”, seção “Classificação e Filtro”, clique no botão “Avançado” e, na janela que se abre, preencha o intervalo original (se necessário).

A faixa de condições também deve ser preenchida (t2). Se precisar copiar para outro local, marque a caixa de seleção apropriada. A segunda tabela é exatamente a tabela 2 - uma tabela de condições, a terceira é o que acontece.

Objetivo do trabalho: realizar classificação de dados, familiarizar-se com o método de filtragem de entradas de lista, filtragem automática e trabalhar com formulários de dados.

Exercício 1.

Classifique os dados da Tabela 5.5 várias vezes de acordo com os seguintes critérios - em ordem alfabética dos sobrenomes dos compradores, em ordem decrescente do valor da transação, em ordem crescente da data da transação, em combinação de características (sobrenome, data, valor).

Método de fazer o trabalho

1. Abra uma nova pasta de trabalho e salve-a como “Classificar” em sua pasta de trabalho .

2. Crie a tabela mostrada na Figura 5.56.

Figura 5.56 – Tabela inicial com dados

3. Defina opções de formatação para a tabela.

Fonte Times New Roman, tamanho de fonte 12 pt., para títulos, estilo negrito e alinhamento central, quebra automática de texto, preenchimento cinza; para a parte principal. Como lembrete, os comandos de formatação estão disponíveis na faixa de opções Início Þ Células .

4. Para classificar pelo campo de sobrenome do comprador, coloque o cursor em qualquer lugar desta coluna e execute o comando Dados Þ Classificação (Fig. 5.51) .

Na caixa de diálogo que se abre, no campo Ordenar por Selecione Sobrenome do comprador. Ascendente.

5. Repita todos os passos do passo 4 e defina a ordenação por “Valor da Transação”, em ordem decrescente.

6. Reordene pelo campo “Data da transação”, em ordem crescente.

7. Copie a tabela para uma nova planilha e classifique-a por um conjunto de características. Para fazer isso, chame o comando Dados Þ Classificação. Instalar Ordenar por sobrenomes em ordem crescente, Então por data em ordem crescente, Por último, por valor em ordem decrescente.

8. Usando um comando Renomear Dê nomes a essas duas folhas.

Tarefa 2. Selecione informações da lista com base no comando AutoFiltro.

Método de fazer o trabalho.

1. Na folha 4, crie uma tabela e preencha-a com as informações da tabela 5.5.

2. Renomeie Sheet4, dando-lhe o nome de “AutoFilter #1”.

3. Para aplicar a AutoFiltragem, coloque o cursor na área da lista e execute o comando Dados ÞFiltro. Setas para baixo aparecerão ao lado dos nomes das colunas da tabela, revelando uma lista de valores possíveis. Na coluna “Gênero”, selecione “M”. Copie a tabela para a folha 5 e renomeie-a como “Autofiltro nº 2”.

4. Na planilha “Autofiltro nº 1”, na coluna “Gênero”, abra a lista de filtragem e selecione “Todos”. Em seguida, na coluna “Data de Nascimento”, selecione “Condição” na lista de filtragem e defina a condição (Fig. 5.57):

Tabela 5.5

Sobrenome	Nome	data de emprego	Data de nascimento	Chão	Salário	Idade
Pachkov	Igor	16.05.74	15.03.49	M
Andreeva	Ana	16.01.93	19.10.66	E
Erokhin	Vladimir	23.10.81	24.04.51	M
Popov	Alexei	02.05.84	07.10.56	M
Tyunkov	Vladimir	03.11.88	19.07.41	M
Notkin	Eugênio	27.08.85	17.08.60	M
Kubrina	Marina	20.04.93	26.06.61	E
Gudkov	Nikita	18.03.98	05.04.58	M
Gorbatov	Michael	09.08.99	15.09.52	M
Bystrov	Alexei	06.12.00	08.10.47	M
Krylov	Tatiana	28.12.93	22.03.68	E
Bersheva	Olga	14.12.01	22.12.74	E
Rusanova	Ter esperança	24.05.87	22.01.54	E

Figura 5.57 – Configurando condições de filtragem

5. Copie a tabela filtrada para a planilha 6 e renomeie-a como “Autofiltro No. Na folha AutoFiltro nº 1, desmarque.

Figura 5.58 – Filtro personalizado

6. Na coluna “Sobrenome”, selecione “Condição” na lista de filtragem e defina uma condição para selecionar todos os funcionários cujo sobrenome comece com “B” (Fig. 5.58).

7. Copie a lista filtrada para a planilha 7 e renomeie-a como “Autofiltro No. 4”.

8. Na planilha “Autofiltro nº 1” para a coluna “Sobrenome” defina “Todos”, e na coluna “Salário” defina “Primeiros 10...” onde na caixa de diálogo digite “Mostrar os 5 maiores elementos da lista”.

9. Salve o arquivo.

Tarefa 3. Selecione registros da lista usando o comando Filtro avançado.

Metodologia para fazer o trabalho.

1. Vá para a Planilha 8 e renomeie-a como "Filtro Avançado".

2. Copie a tabela da tarefa anterior (Tabela 5.5) para esta folha e cole-a a partir da linha 7. As primeiras 6 linhas são reservadas para definir condições.

3. Vamos criar uma série de condições. Suponha que precisemos selecionar os nomes dos funcionários que ganham mais de 5.000 rublos. Ou cuja idade ultrapassa os 50 anos. Preencha as condições conforme mostrado na Figura 5.59.

Figura 5.59 – Condições para um filtro avançado

4. Execute o comando Dados Þ Adicionais . Preencha a caixa de diálogo da seguinte forma (Fig. 5.60):

Figura 5.60 – Janela de parâmetros avançados de filtro

Veja os resultados da seleção. Ao escrever condições em uma linha, o AND lógico é implementado. Ao escrever condições em linhas diferentes, elas são consideradas conectadas pelo OR lógico. Consideramos a primeira opção, agora consideraremos a segunda.

5. Suponha que precisamos exibir apenas os funcionários cujos sobrenomes começam com as letras A, G ou N. Preencha o intervalo de condições (Figura 5.61).

Figura 5.61 – Condições para um filtro avançado

6. Execute o comando DadosÞAdicionais e preencha a caixa de diálogo (Figura 5.62).

Figura 5.62 – Janela de parâmetros avançados de filtro

Visualize os resultados da seleção de registros.

1. Imprima uma lista de todos os funcionários cujos salários são superiores à média. Antes de criar este filtro, insira a fórmula =AVERAGE(F8:F20) na célula H2 para calcular o salário médio.

2. Em seguida, na célula A2 inserimos a condição calculada =F8>$H$2, que se refere à célula H2 (Figuras 5.63 e 5.64).

Figura 5.63 – Condições para um filtro avançado

Figura 5.64 – Parâmetros avançados de filtro

Filtragem (amostragem) de dados em uma tabela permite exibir apenas as linhas cujo conteúdo da célula atende a uma condição especificada ou a várias condições. Usando filtros, o usuário pode exibir ou excluir (ocultar) entradas da lista de uma forma conveniente.

Ao contrário da classificação, a filtragem não reordena os dados, mas apenas oculta os registros que não atendem aos critérios de seleção especificados.

Os registros selecionados podem ser formatados ou excluídos, copiados para uma área separada da tabela, impressos ou usados ​​para cálculos ou gráficos subsequentes.

A filtragem de dados em planilhas pode ser feita de duas maneiras: usando filtro automático ou filtro avançado.

Filtrando dados usando um filtro automático. Para realizar esta operação você precisa de:

1) coloque o cursor dentro da tabela;

2) digite o comando do menu DADOSÞFiltroÞAutofiltro;

3) clicando no botão de seta, expanda a lista da coluna pela qual será feita a seleção;

4) especifique os valores necessários ou selecione uma linha "doença" e defina critérios de seleção na caixa de diálogo Filtro automático personalizado.

As condições para seleção de registros em uma coluna específica podem consistir em duas partes independentes, conectadas por um link lógico E/OU.

Cada parte da condição pode incluir:

· um valor que pode ser selecionado em uma lista ou conter caracteres curinga. Os caracteres curinga são usados ​​como um asterisco * - para indicar um número arbitrário de caracteres ou um ponto de interrogação? - para substituir um caractere;

· operador de relação (comparação). Ao especificar critérios de seleção, os seguintes operadores de comparação podem ser usados:

É igual a<>Não igual

< Меньше < = Меньше или равно

> Maior que > = Maior ou igual a

Para recuperação todas as linhas da tabela de origem, você precisa clicar no botão de seta (azul) e selecionar a linha na lista suspensa "Todos" ou execute o comando DADOSÞFiltrosÞExibir tudo.

Para cancelamentos modo de filtro, você precisa colocar o cursor dentro da tabela e inserir o comando do menu novamente DADOSÞFiltroÞ Filtro automático(remova o interruptor).

Filtre dados usando um filtro avançado. O filtro avançado permite criar vários critérios seleções e realizar uma filtragem mais complexa dos dados da planilha, especificando um conjunto de condições de seleção para diversas colunas.

Para filtrar entradas da lista, o filtro avançado fornece dois tipos de critérios:

· critérios de comparação;

· critérios calculados.

A filtragem de registros usando um filtro avançado é feita usando o comando de menu DADOSÞFiltroÞFiltro avançado.

Uma característica importante deste modo é que antes de executar o comando de filtragem propriamente dito, é necessário criar uma área especial para definir as condições de filtragem de dados - gama de condições de seleção(intervalo de critérios).

O intervalo de condições deve conter uma linha com títulos de coluna e diversas linhas para especificar as condições de seleção. Normalmente, para criar um intervalo de condições, primeiro você copia a linha com títulos de coluna para um local separado (em outro ou na mesma planilha - geralmente acima da tabela original) e, em seguida, insere os critérios de seleção para colunas individuais nas linhas abaixo.

Deve haver pelo menos uma linha vazia entre os valores das condições de seleção e a tabela.

Se os critérios de seleção (condições de filtragem) forem inseridos em uma linha para colunas diferentes, eles serão considerados conectados pela condição “AND”. Se os critérios de seleção forem escritos em linhas diferentes, eles serão considerados conectados pela condição “OR”.

Após gerar uma série de condições com critérios de seleção de registros, coloque o cursor dentro da tabela e digite o comando DADOSÞFiltroÞFiltro avançado e na caixa de diálogo Filtro avançado indicar o intervalo de células da tabela e o endereço ou nome de um intervalo de condições pré-gerado.

As entradas podem ser filtradas no local ou simultaneamente com filtragem cópia de para a área especificada na planilha atual.

Para copiar as linhas filtradas para outra área da planilha, selecione a opção Copie os resultados para outro local, ir para o campo Coloque o resultado no intervalo e indique a célula superior esquerda da área onde serão inseridos os dados selecionados.

Filtrando dados em uma lista

Um filtro é uma maneira rápida e fácil de localizar um subconjunto de dados e trabalhar com ele em uma lista. A lista filtrada exibe apenas as linhas que correspondem condições(condição, restrição especificada para seleção de registros) especificada para a coluna. Neste caso, as linhas restantes acabam sendo escondido.

Tudo isso simplifica o processo de inserção e exclusão de registros, bem como o processo de busca de informações.

A vantagem de usar filtros é que o resultado da filtragem pode ser copiado para uma área separada da tabela e imediatamente utilizado nos cálculos

Existem dois comandos disponíveis no Microsoft Excel para filtrar listas:

• Filtro automático, incluindo filtro por seleção, para condições de seleção simples;
• Filtro avançado para condições de seleção mais complexas.

Ao contrário da classificação, um filtro não altera a ordem das entradas na lista. A filtragem oculta temporariamente as linhas que você não deseja exibir.

As linhas selecionadas por filtragem no Microsoft Excel podem ser editadas, formatadas, criadas em gráficos e impressas sem alterar a ordem das linhas ou movê-las.

Filtro automático usado nos casos em que é necessário filtrar dados rapidamente especificando uma ou duas condições de seleção simples. Estas condições são impostas ao conteúdo das células de uma coluna específica.

Ao usar o comando Filtro automático Os botões de seta aparecem à direita dos nomes das colunas na lista filtrada.

Microsoft Excel indica itens filtrados em azul

Quando uma condição de seleção deve ser aplicada simultaneamente a células em duas ou mais colunas;

Quando três ou mais condições de seleção precisam ser aplicadas a células na mesma coluna:

Quando a condição de seleção utiliza o valor obtido como resultado do cálculo de uma determinada fórmula.

Um filtro avançado pode usar operações AND, OR e também criar critérios calculados.

Ambos os comandos são chamados como resultado da seleção do comando Dados/Filtro.

Usando o comando Filtro avançado você pode filtrar a lista da mesma maneira que usa o comando Filtro automático, mas as listas suspensas das colunas não são exibidas.

Para pesquisar dados usando um filtro avançado, você deve primeiro preparar os dados adequadamente:

1 Criar BANCO DE DADOS ( Gama original).

2. Criar Faixa de condição, que especifica as condições de pesquisa de dados. A linha superior deste intervalo deve conter cabeçalhos de campo que correspondam exatamente aos cabeçalhos de campo no intervalo original (lista pronta).

O intervalo de condições deve incluir pelo menos uma linha vazia de células imediatamente abaixo dos cabeçalhos. EM esta linha ou linhas as condições são anotadas e tudo o que está escrito na coluna sob o título do campo refere-se especificamente a este campo.

As condições de todas as colunas de cada linha são conectadas pela operação lógica " E", e então todas as linhas são conectadas pela operação lógica" OU»

3 No cardápio Dados selecione uma equipe Filtro, então Filtro avançado.

No campo de entrada Gama de condições indica o intervalo de células na planilha que contém suas condições. No campo de entrada Coloque o resultado no intervalo indica o intervalo de células no qual as linhas são copiadas. Este campo só está disponível quando o botão de opção está selecionado Copiar para outro local.

4 Verifique se todos os intervalos são automaticamente preenchidos corretamente e corrija-os se necessário.

Exemplos de condições de seleção complexas:

Várias condições para uma coluna Se houver duas ou mais condições de seleção para uma coluna, insira essas condições de seleção diretamente abaixo uma da outra em linhas separadas Vendedor Belov Baturin Roschin Uma condição para múltiplas colunas Para localizar diversas colunas de dados que atendam à mesma condição de seleção, insira todas as condições de seleção em uma linha do intervalo de condições de seleção. Tipo Vendedor Vendas Frutas Belov >1 000 Condições diferentes para colunas diferentes Tipo Vendedor Vendas Frutas Belov >1 000

Para localizar dados que atendam a uma condição em uma coluna ou a outra condição em outra coluna, insira as condições de seleção em linhas diferentes do intervalo de condições de seleção. Por exemplo,

Um dos dois conjuntos de condições para duas colunas

Para localizar linhas que correspondam a um dos dois conjuntos de critérios, cada um contendo condições para mais de uma coluna, insira os critérios de seleção em linhas separadas. Por exemplo,

Vendedor Vendas Belov >3 000 Baturin >1 500 Mais de dois conjuntos de condições para uma coluna

Para localizar linhas que correspondam a mais de dois conjuntos de critérios, inclua várias colunas com os mesmos títulos. Por exemplo,

Data de publicação: 09/10/2015; Leia: 205 | Violação de direitos autorais de página | Encomende escrever um artigo

site - Studopedia.Org - 2014-2020. Studiopedia não é autora dos materiais postados. Mas fornece uso gratuito(0,002s) ...