Cap 06 - Excel 2010 Avançado

Prévia do material em texto

Excel 2010 – Avançado 
 
 
 
Senac São Paulo 107 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
OBJETIVOS 
 Selecionar dados aplicando AutoFiltro. 
 Classificar dados por uma ou mais chaves. 
 Aplicar o Filtro Avançado. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
 
108 Senac São Paulo 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 109 
Filtrando e classificando no Excel 
Neste capítulo você destacará dados de interesse através da aplicação do recurso filtro e classificará o 
conteúdo das planilhas por uma chave e por mais de uma chave. Você criará planilhas que apresentarão 
resultados desejados. Com base em uma lista de produtos você criará planilhas que apresentarão os 
produtos separados por departamento. 
 
 
Atividade 1 – Aplicar o AutoFiltro 
 
Objetivo : • Conhecer o recurso AutoFiltro 
Tarefas : • Aplicar o AutoFiltro no arquivo Filtro Avançado.xlsx 
 
Nesta atividade você utilizará o arquivo Filtro Avançado.xlsx e aplicará o AutoFiltro para destacar um grupo 
de registros que correspondem aos produtos de determinado Departamento. Abra o arquivo 
Automação.xlsx e acesse a guia Pesquisa. 
1. Abra o arquivo Filtro Avançado.xlsx. 
2. Exiba o AutoFiltro. Na guia Dados, no grupo Classificar e Filtrar clique no comando Filtro. 
 
 
Botões para aplicação do filtro aparecem ao lado do rótulo de cada coluna. 
 
3. Clique no botão da coluna Departamento e selecione a opção Selecionar Tudo para desmarcar todas 
as opções. 
4. Em seguida, marque apenas a opção Telefones e Celulares. 
 
 
Excel 2010 – Avançado 
 
 
 
 
110 Senac São Paulo 
5. Clique no botão OK. 
6. O resultado é a exibição das linhas onde constam o departamento escolhido. As linhas que 
correspondem a outros departamentos ficam ocultas. 
 
 
A coluna onde o filtro foi aplicado exibe uma imagem representando a aplicação do filtro. 
 
 
7. Para mostrar todas as linhas novamente clique no botão ao lado do nome do campo Departamento, 
escolha Selecionar Tudo e clique em OK. 
 
 
Podemos reexibir todas as linhas clicando na opção Limpar do grupo Classificar e Filtrar da guia 
Dados. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 111 
 
Atividade 2 – Aplicar o filtro a mais de um campo 
 
Objetivo : • Apresentar dados que atendam a critérios combinados. 
Tarefa : • Aplicar um filtro que exiba os produtos do departamento Telefones e Celulares 
cujo preço seja inferior a R$ 600,00. 
 
1. Aplique o AutoFiltro que apresenta os produtos do departamento Telefones e Celulares. 
2. Clique no botão da coluna Preço. 
3. Escolha a opção Filtros de número e escolha a opção É Menor do Que... 
 
 
4. Digite o limite superior - 600. 
5. Clique em OK. 
 
 
 
Clique em Limpar do grupo Classificar e Filtrar quando desejar exibir todas as linhas. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
 
112 Senac São Paulo 
 
ANOTAÇÕES 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 113 
 
Atividade 3 – Aplicar filtro alternativo 
 
Objetivo : • Aplicar filtro quando parte da informação é conhecida. 
Tarefa : • Aplicar filtro para produtos iniciados pela letra N e filtro para produtos que 
contenham como parte de sua descrição a informação - 14”. 
 
1. Clique no botão de filtro do campo Produto. 
2. Escolha em Filtros de Texto e a opção Começa com... 
 
 
3. Preencha com a parte conhecida da informação. Pressione OK. 
 
 
 
4. Clique em Limpar para restaurar todos os produtos. 
5. Clique no botão de filtro da coluna Descrição. 
Excel 2010 – Avançado 
 
 
 
 
114 Senac São Paulo 
6. Escolha Filtros de Texto e opção Contém... 
 
 
 
 
As opções que aparecem na lista suspensa quando pressionamos o botão do filtro dependem do 
tipo de informação que predomina na coluna. Os tipos podem ser Filtros de Texto, Filtros de 
Número e Filtros de Data. 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 115 
 
Atividade 4 – Classificar com o AutoFiltro 
 
Objetivo : • Apresentar o AutoFiltro como alternativa de classificação. 
Tarefa : • Classificar por ordem alfabética de produtos. 
 
1. Clique em Limpar para retirar os filtros aplicados. 
2. Clique no botão de filtro da coluna Produto. 
3. Escolha a opção Classificar de A a Z para colocar os dados da coluna em ordem crescente ou 
Classificar de Z a A para colocar os dados em ordem decrescente. 
 
 
Quando a coluna estiver preenchida com números as opções serão Classificar do Menor para o 
Maior, Classificar do Maior para o Menor. Quando a coluna estiver preenchida com datas, as 
opções serão Classificar do Mais Antigo para o Mais Novo e Classificar do Mais Novo para o 
Mais Antigo. 
 
 
Clique no botão Filtro do grupo Classificar e Filtrar quando quiser retirar os botões do 
AutoFiltro. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
 
116 Senac São Paulo 
 
ANOTAÇÕES 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 117 
 
Atividade 5 – Classificação Avançada 
 
Objetivo : • Apresentar o recurso de classificação por mais de uma chave. 
Tarefa : • Classificar os funcionários da planilha Nome.xlsx por Estado Civil e Nome. 
 
1. Abra o arquivo Nome.xlsx. 
2. Na planilha Nome, selecione qualquer célula dentro da lista. 
3. Clique no comando Classificar do grupo Classificar e Filtrar da guia Dados. 
 
 
4. Escolha na caixa de diálogo a Coluna Estado Civil. 
 
 
5. Clique em Adicionar Nível para definir uma segunda chave de classificação. 
 
 
6. Escolha a coluna Nomes na linha que surgiu e clique em OK. 
 
 
 
Excel 2010 – Avançado 
 
 
 
 
118 Senac São Paulo 
 
ANOTAÇÕES 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 119 
 
Atividade 6 – Classificação por lista personalizada 
 
Objetivo : • Classificar os dados por uma ordem estabelecida pelo usuário. 
Tarefa : • Classificar os funcionários por Cargo em ordem hierárquica. 
 
Você criará uma lista personalizada para poder classificar os cargos em uma ordem de importância 
arbitrária. Para isso você digitará os cargos na lista personalizada na seguinte ordem: Presidente, Diretor(a), 
Gerente, Subgerente, Analista, Contador(a), Secretária, Escriturário, Comprador, Vendedor, Conferente e 
Estagiário. 
1. Clique no comando Classificar do grupo Classificar e Filtrar da guia Dados. 
2. Escolha a coluna Cargo e escolha Lista Personalizada na caixa de combinação Ordem. 
 
 
3. Digite os cargos na ordem especificada pressionando ENTER após cada cargo ou separando por 
vírgulas. 
 
 
4. Clique em Adicionar e a lista será criada. 
Excel 2010 – Avançado 
 
 
 
 
120 Senac São Paulo 
 
 
5. Clique em OK e OK novamente para classificarpela lista personalizada. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 121 
 
Atividade 7 – Classificação por cores 
 
Objetivo : • Utilizar cores como critério de classificação. 
Tarefa : • Classificar os produtos mais vendidos através de codificação por cores. 
 
Você vai classificar o arquivo Produtos Mais Vendidos.xlsx onde a cor verde representa os produtos mais 
vendidos, a cor vermelha os produtos de venda mediana e a cor azul os produtos com pouca venda. 
1. Abra o arquivo Produtos mais Vendidos.xlsx. 
2. Clique no comando Classificar do grupo Classificar e Filtrar da guia Dados. 
3. Escolha a coluna Produto e Cor da Célula na caixa de combinação Classificar em. 
 
 
 
Existem as opções Cor da Fonte e Ícone da Célula, neste último caso quando for aplicada a 
Formatação Condicional. 
 
4. Estabeleça a ordem de importância das cores, adicionando nível a cada cor definida. 
 
 
5. Clique em OK para efetuar a classificação. 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
 
122 Senac São Paulo 
 
ANOTAÇÕES 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 123 
 
Atividade 8 – Filtro avançado 
 
Objetivo : • Aplicar filtro utilizando critérios avançados. 
Tarefas : • Separar os dados do arquivo Filtro Avançado.xlsx gerando uma planilha para 
cada departamento. 
 
1. Abra o arquivo Filtro Avançado.xlsx. Você vai criar uma lista dos departamentos sem repetição. 
2. Selecione a coluna Departamento. 
3. Clique com o botão direito e a opção Copiar ou use qualquer outro método de cópia. 
 
 
4. Selecione a planilha Critérios e a célula A1. 
5. Cole o conteúdo da área de transferência. 
6. Mantendo a coluna selecionada, clique no botão Remover Duplicata do grupo Ferramenta de Dados, 
da guia Dados. 
 
 
Excel 2010 – Avançado 
 
 
 
 
124 Senac São Paulo 
7. Certifique-se que a caixa de verificação Meus dados contêm cabeçalhos esteja marcada. Caso não 
esteja marque-a. clique em OK. 
 
 
8. Clique em OK para concluir. 
 
 
Você vai criar as áreas de critérios necessárias para o uso do filtro avançado. Um critério é constituído de 
uma célula com o nome da coluna que contém o dado a ser pesquisado tendo logo abaixo a célula que 
contém o dado pesquisado. Ela é constituída, portanto, de um intervalo de pelo menos duas células. 
9. Construa os critérios como abaixo: 
 
 
10. Insira e renomeie uma planilha para os departamentos de Informática, Telefones e Celulares, Câmeras 
e Filmadoras, Eletrônicos e uma planilha para os produtos dos outros departamentos. 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 125 
 
11. Você vai iniciar agora a construção das planilhas utilizando o Filtro Avançado. Selecione todas as linhas 
da planilha Geral e renomeie este intervalo como Geral. 
12. Vá para a planilha Informática. 
13. Clique no botão Avançado do grupo Classificar e Filtrar da guia Dados. 
 
 
14. No quadro que surge marque a opção Copiar para outro local. 
15. Preencha o Intervalo da lista com Geral. 
16. Preencha o intervalo de critérios clicando na aba da planilha Critérios e selecionando as células A1 e 
A2. 
17. Vá para Copiar para e clique na célula A1 da planilha Informática. 
 
 
18. Clique em OK. 
 
 
19. Repita os passos 12 a 17 iniciando o processo nas planilhas Telefones e celulares, Câmeras e 
Filmadoras, Eletrônicos utilizando os intervalos de critérios correspondentes. 
20. Vá para a planilha OUTROS. 
Excel 2010 – Avançado 
 
 
 
 
126 Senac São Paulo 
21. Clique no botão Avançado do grupo Classificar e Filtrar da guia Dados. 
22. Marque a opção Copiar para outro local. 
23. Preencha Intervalo da lista com Geral. 
24. Preencha Intervalo de critérios selecionando o intervalo A14:A18 da planilha Critérios. 
 
 
 
Quando colocamos o cabeçalho do campo e várias linhas de dados a pesquisar, o filtro avançado 
considera que um ou outro nome relacionado atenderá o critério. 
 
25. Vá para Copiar para e clique na célula A1 da planilha OUTROS. 
 
 
26. Clique em OK. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 127 
 
Atividade 9 – Filtro avançado com critérios múltiplos 
 
Objetivo : • Aplicar filtro utilizando critérios avançados combinados. 
Tarefas : • Separar os dados do arquivo Filtro Avançado.xlsx gerando uma planilha com os 
produtos do departamento Informática com preço inferior a R$ 1.200,00. 
 
1. Vá à planilha Critérios, digite Preço na célula B1 e <1200 na célula B2. 
 
 
O intervalo A1:B2 da planilha Critérios formará o critério combinado a ser usado no filtro. Este critério fará 
com que o filtro avançado retorne os produtos de informática e com preço inferior a 1200. 
 
 
Só os produtos que atenderem a estas duas condições aparecerão no resultado. 
 
2. Insira uma nova planilha. 
3. Renomeie como Informática - menor que 1200. 
4. Clique no botão Avançado do grupo Classificar e Filtrar da guia Dados. 
5. Marque a opção Copiar para outro local. 
6. Preencha Intervalo da lista com Geral. 
7. Preencha Intervalo de critérios selecionando o intervalo A1:B2 da planilha Critérios. 
 
 
8. Clique em OK. 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
 
128 Senac São Paulo 
 Exercício Proposto 
 
1. Abra a pasta de trabalho Nome-exercício.xlsx e crie uma nova planilha separando as informações por 
cidade usando o filtro avançado. 
2. Ainda na mesma pasta e usando o filtro avançado, crie uma planilha com os dados das pessoas casadas 
e outra com os dados das pessoas solteiras.