Cap 09- Excel 2010 Avançado

Prévia do material em texto

Excel 2010 – Avançado 
 
 
 
Senac São Paulo 159 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
OBJETIVOS 
 Apresentar recursos para consolidação e resumo de dados. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
 
160 Senac São Paulo 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 161 
 
Atividade 1 – Consolidação de Dados 
 
Objetivo : • Apresentar o recurso Consolidar Dados. 
Tarefa : • Reunir informações de várias planilhas e arquivos. 
 
Nesta atividade você somará os valores previstos de entrada e saída dos meses de janeiro, fevereiro e 
março contidos nas planilhas do arquivo Movimento.xlsx e valores do mês de abril que se encontram no 
arquivo Movimento-abril.xlsx. 
Será utilizado o recurso Consolidar. Este recurso consolida os dados de duas formas: consolidação por 
posição e consolidação por categoria. 
Na consolidação por posição presume-se que o dado mantém sua posição relativa, como no exemplo 
abaixo: 
 
 
As informações de Janeiro do Item 1 ficam sempre no canto superior esquerdo, de Fevereiro do Item 1 à 
direita e assim por diante. Nesta situação os cabeçalhos de coluna e de linha não são importantes para a 
consolidação, bastando apenas selecionar o intervalo de dados. 
 
 
Porém as tabelas podem não manter a posição dos dados, como no exemplo abaixo: 
 
 
Neste caso a consolidação deve considerar os cabeçalhos das colunas e das linhas como referencial para 
consolidar as informações. Deve-se incluir os cabeçalhos no intervalo a consolidar. 
Excel 2010 – Avançado 
 
 
 
 
162 Senac São Paulo 
 
 
1. Abra o arquivo Movimento.xlsx. Analise as informações das planilhas Janeiro, Fevereiro e Março. 
2. Posicione-se na célula A3 da planilha Consolidado. 
3. Clique na opção Consolidar do grupo Ferramentas de dados da guia Dados. 
 
 
4. Escolha a função desejada para a consolidação das planilhas. Nesse caso indique Soma. 
 
 
5. Vá para o campo Referência, clique na guia da planilha Janeiro e selecione o intervalo de dados a ser 
considerado para consolidar. 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 163 
6. Clique em Adicionar. 
7. Repita os passos 5 e 6 para os meses Fevereiro e Março. 
 
 
Os dados do mês de abril estão em um arquivo separado que não está na memória. 
 
8. Clique no botão Procurar... 
9. Localize o arquivo Movimento-Abril.xlsx. 
10. Depois de selecioná-lo no quadro Procurar, clique em OK. 
11. Clique na tecla de função F2. 
12. Clique em qualquer local dentro da caixa de referência para desfazer a seleção. 
13. Tecle End e digite o nome do intervalo que contém os dados: Abril. 
 
 
14. Clique em Adicionar. 
 
 
Sempre use a tecla F2 caso você não tenha acesso com o cursor para digitar o intervalo. Se 
tentar usar outro método, é possível que você perca a indicação do caminho e do nome do 
arquivo. 
 
Excel 2010 – Avançado 
 
 
 
 
164 Senac São Paulo 
15. Para que os itens sejam relacionados e seus valores considerados corretamente, assinale Coluna 
esquerda em Usar rótulos na. 
16. Clique em OK. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 165 
 
Atividade 2 – Consolidação de Dados com Vínculo 
 
Objetivo : • Consolidar dados de várias planilhas mantendo o vínculo do total com as 
planilhas de origem. 
Tarefa : • Consolidar os dados das planilhas dos arquivos Movimento e Movimento-Abril, 
para que qualquer alteração nos dados por ser vista na planilha de totais. 
 
1. Vá para a planilha Vinculado. 
2. Posicione-se na célula A3 da planilha Vinculado. 
3. Clique na opção Consolidar do grupo Ferramentas de dados da guia Dados. 
 
 
4. Escolha a função Soma para a consolidação das planilhas. 
5. Vá para o campo Referência, clique na guia da planilha Janeiro e selecione o intervalo de dados a ser 
considerado para consolidar. 
 
 
6. Clique em Adicionar. 
7. Repita os passos 5 e 6 para os meses Fevereiro e Março. 
 
Excel 2010 – Avançado 
 
 
 
 
166 Senac São Paulo 
 
8. Clique no botão Procurar... 
9. Localize o arquivo Movimento-Abril.xlsx. 
10. Depois de selecioná-lo no quadro Procurar, clique em OK. 
11. Clique na tecla de função F2. 
12. Clique em qualquer local dentro da caixa de referência para desfazer a seleção. 
13. Tecle End e digite o nome do intervalo que contém os dados: Abril. 
14. Clique em Adicionar. 
15. Para que os itens sejam relacionados e seus valores considerados corretamente, assinale Coluna 
esquerda em Usar rótulos na. 
16. Assinale Criar vínculos com dados de origem. 
 
 
17. Clique em OK. Analise as células que contém os valores. Agora elas possuem fórmulas. Qualquer 
alteração nas planilhas mensais refletirá no resultado da planilha Vinculado. 
18. Altere alguns valores das planilhas mensais e compare os reflexos nas planilhas Consolidado e 
Vinculado. 
 
O Excel introduziu uma área cinza com números e sinais do lado esquerdo da planilha a qual chamamos de 
Estrutura de Tópicos. Sua função é permitir ao usuário ocultar ou exibir dados que interferem nas fórmulas 
criadas. Agora você fará uso deste recurso e terá uma visão mais completa na próxima atividade. 
19. Clique nos botões que se assemelham a sinais de adição. As linhas que contém os itens que contribuem 
para o subtotal são exibidas. Ao clicar novamente no botão, que agora tem aparência do sinal de 
subtração, os itens são ocultos novamente. 
20. Clique no número 2 e todos os detalhes de todos os subtotais são exibidos. Se você clicar no número 1 
os itens serão recolhidos. 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 167 
 
Atividade 3 – Estrutura de Tópicos 
 
Objetivo : • Apresentar o recurso de Estrutura de tópicos que configura os dados para serem 
exibidos ou ocultos. 
Tarefas : • Introduzir o recurso AutoTópicos e o recurso Estrutura de Tópicos para agrupar 
dados arbitrários. 
 
1. Abra o arquivo Tópicos.xlsx. 
2. Clique na parte Inferior do botão Agrupar do grupo Estrutura de Tópicos da guia Dados. 
 
 
3. Clique em AutoTópicos. 
 
 
 Os sinais são usados para ocultar/exibir os itens que compõem cada subtotal. Os 
números permitem a escolha do nível de subtotal que será exibido para todos os 
subtotais. 
 
4. Clique nos sinais e números para familiarizar-se com seu funcionamento. 
 
 
O recurso AutoTópicos cria os agrupamentos reconhecendo os itens que compõem os cálculos. 
Considera, portanto, a lógica usada na construção da fórmula. 
 
Você vai agrupar itens que não contribuem para os cálculos da planilha. Como o Excel não pode 
reconhecer a lógica devemos informar arbitrariamente os itens a serem agrupados. 
5. Selecione as colunas F, G e H. 
6. Clique na parte Inferior do botão Agrupar do grupo Estrutura de Tópicos da guia Dados. 
7. Clique em Agrupar. 
 
Excel 2010 – Avançado 
 
 
 
 
168 Senac São Paulo 
 
 
 
Uma vez que a intenção é Agrupar, podemos clicar diretamente na parte superior do botão 
Agrupar. 
 
 
Se a seleção não for feita pelos botões seletores das colunas e das linhas, uma mensagem 
aparece para você informe se deseja agrupar as linhas ou colunas selecionadas. 
. 
 
 
8. Para desagrupar, selecione as linhas ou colunas e clique no botão Desagrupar do grupo Estrutura de 
tópicos da guia Dados. 
 
 
Para retirar a estrutura detópicos da planilha clique na parte inferior do botão Desagrupar e 
escolha Limpar Estrutura de Tópicos. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 169 
 
Atividade 4 – Subtotais 
 
Objetivo : • Apresentar o recurso Subtotal que resume dados em uma planilha. 
Tarefa : • Resumir os dados por Tipo e por categoria. 
 
1. Abra o arquivo Movimento Financeiro2.xlsx. 
2. Classifique pela coluna E/S e Categoria. 
 
A classificação é necessária para que os itens que formam um mesmo grupo fiquem juntos. O recurso 
percorre o campo escolhido e subtotaliza a cada vez que a informação naquela coluna fica diferente. Se os 
dados não estiverem juntos, vários subtotais do mesmo item podem compor o resultado. 
3. Clique no botão Subtotal do grupo Estrutura de Tópicos da guia Dados. 
 
 
4. No campo A cada alteração em: escolha E/S. 
5. Escolha a função Soma. 
6. Marque os campos a serem totalizados. Neste exemplo use Valor. 
7. Clique em OK. 
8. Clique novamente no botão Subtotal do grupo Estrutura de Tópicos da guia Dados para acrescentar 
outro subtotal. 
9. Escolha o campo Categoria, mantenha a função Soma selecionada e o campo Valor para adicionar 
subtotal. 
10. Desmarque a opção Substituir subtotais atuais. 
11. Marque a opção Quebra de página entre grupos. 
Excel 2010 – Avançado 
 
 
 
 
170 Senac São Paulo 
 
 
12. Clique em OK. 
 
 
Os Subtotais adicionais são inseridos antes dos subtotais inseridos anteriormente. Por essa 
razão, quando optamos pela quebra de página entre grupos quando já existem subtotais 
aplicados, a quebra de página pode não ser a desejada. É necessário efetuar a quebra dos grupos 
manualmente. Siga os passos descritos a seguir. 
 
13. Clique no botão Quebras do grupo Configurar Página da guia Layout da Página. 
 
 
14. Clique na opção Redefinir todas as quebras de página. 
15. Localize a linha pontilhada vertical que indica a largura máxima a ser impressa. 
16. Torne ativa a célula imediatamente à direita da linha e logo abaixo do subtotal. 
17. Clique no botão Quebras e escolha Inserir Quebra de Página. 
 
 
18. Repita os passos 15, 16 e 17 para cada subtotal desejado. 
19. Clique na opção Imprimir da guia Arquivo. 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 171 
 
 
20. Visualize a primeira página da impressão à direita. 
 
 
Nas páginas seguintes os cabeçalhos das colunas não são repetidos. 
21. Clique na guia Layout da Página. 
22. Clique no botão Imprimir Títulos do grupo Configurar Página. 
 
 
Excel 2010 – Avançado 
 
 
 
 
172 Senac São Paulo 
23. Selecione na planilha as linhas a repetir na parte superior. 
 
 
24. Clique em OK. As linhas selecionadas serão repetidas em cada página impressa. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 173 
 
Atividade 5 – Cenários 
 
Objetivo : • Apresentar o recurso Gerenciador de Cenários para realizar simulações. 
Tarefa : • Formular hipóteses para analisar os efeitos nos saldos ao se alterarem as 
entradas e as saídas. 
 
Você irá simular variações de valores para as planilhas analisando cenários prováveis. Você criará um 
primeiro cenário com os dados atuais da planilha para poder recuperar estes dados no futuro. 
Em seguida você criará um cenário prevendo aumento de 25% nos itens de Entrada de janeiro, aumento 
de 15% nas Saídas de fevereiro e queda de 10% nas Saídas de março. 
1. Abra o arquivo Cenários.xlsx. 
2. Vá para a planilha Janeiro. 
3. Clique no botão Teste de Hipóteses do grupo Ferramentas de Dados da guia Dados. 
 
 
4. Escolha Gerenciador de Cenários... A lista aparece vazia pois nenhum cenário foi criado ainda. Clique 
no botão Adicionar. 
5. Preencha o campo Nome do Cenário com Dados Originais. 
Você deve selecionar as células variáveis. São as células que terão seus valores alterados para 
análise dos resultados. 
 
 
As células selecionadas não devem conter fórmulas, pois quando um cenário é aplicado as 
fórmulas serão substituídas por valores. 
 
6. Selecione os intervalos B3:B4 e B6:B9, no campo Células variáveis. 
 
Excel 2010 – Avançado 
 
 
 
 
174 Senac São Paulo 
 
7. Preencha o campo Comentário como achar necessário. 
8. Clique em OK. 
9. As células variáveis aparecem para receber novos valores. Como a idéia é recuperar os valores atuais, 
eles não serão alterados. 
 
 
10. Clique em OK para que o cenário seja criado como os valores atuais. 
11. Clique em Adicionar. 
12. Preencha o campo Nome do cenário com Aumento de 25% nas Entradas de janeiro. 
13. Mantenha as mesmas células variáveis. 
14. Preencha o campo Comentário. 
 
 
15. Clique em OK. 
16. Clique em OK. 
17. Digite a fórmula que representa o cálculo de acréscimo de 25% ou digite o valor já calculado. 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 175 
 
 
18. Clique em OK. 
 
 
Surge um quadro avisando que as fórmulas foram convertidas nos valores correspondentes ao 
cálculo. 
 
 
O Gerenciador de cenários agora apresenta os cenários que já foram criados. 
 
 
19. Selecione o cenário que quer analisar e clique no botão Mostrar. 
 
 
Para alterar os valores de um cenário clique no botão Editar... e modifique os valores das células 
variáveis. Clique em Excluir para eliminar um cenário. 
 
Agora você criará novo cenário com a hipótese de aumento de 15% nas Saídas de fevereiro. Lembre-se de 
criar um cenário para recuperar os dados originais. 
Excel 2010 – Avançado 
 
 
 
 
176 Senac São Paulo 
20. Vá para a planilha Fevereiro. 
21. Clique no botão Teste de Hipóteses do grupo Ferramentas de Dados da guia Dados. 
22. Escolha Gerenciador de Cenários... 
23. Clique no botão Adicionar. 
24. Preencha o campo Nome do Cenário com Dados Originais. 
25. No campo Células variáveis informe os intervalos B3:B5 e B7:B9. 
26. Preencha o campo Comentário. 
27. Clique em OK até retornar ao Gerenciador de cenários. 
28. Clique no botão Adicionar. 
29. Preencha o campo Nome do Cenário com Aumento de 15% nas saídas de fevereiro. 
30. Nas células variáveis B7, B8 e B9 escreva a fórmula que acrescenta 15% a cada valor. 
 
 
31. Clique em OK. 
32. Clique OK no quadro de aviso. 
33. Clique em Mostrar para analisar os valores. 
 
Você criará um cenário que simula a hipótese de queda de 10% nas saídas de março. Antes crie um 
cenário para recuperar os dados originais da planilha. 
34. Vá para a planilha Março. 
35. Clique no botão Teste de Hipóteses do grupo Ferramentas de Dados da guia Dados. 
36. Escolha Gerenciador de Cenários... 
37. Clique no botão Adicionar. 
38. Preencha o campo Nome do Cenário com Dados Originais. 
39. No campo Células variáveis informe os intervalos B3:B5 e B7:B10. 
40. Preencha o campo Comentário. 
41. Clique em OK até retornar ao Gerenciador de cenários. 
42. Clique no botão Adicionar. 
43. Preencha o campo Nome do Cenário com Queda de 10% nas saídas de março. 
44. Nas células variáveis B7, B8, B9 e B10 escreva a fórmula que diminui 10% de cada valor. 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 177 
 
 
45. Clique em OK. 
46. Clique OK no quadro de aviso. 
47. Clique em Mostrar para analisar os valores. 
 
Quando você tem uma pasta de trabalho com os mesmos dados, você pode aproveitar cenários que já 
foram criados em outra pasta. 
Agora você abrirá um arquivo e importará os cenários criados nas atividades anteriores. 
48. Abra o arquivo Cenários-Mesclar.xlsx. 
49. Clique no botão Teste de Hipóteses do grupo Ferramentas de Dados da guia Dados. 
50. Escolha Gerenciador de Cenários... 
51. Clique no botão Mesclar.Na caixa de combinação Pasta: aparecem os arquivos do Excel que estão abertos na memória. Na caixa de 
listagem Planilha: aparecem as planilhas do arquivo selecionado. Os dados do arquivo correspondem aos 
valores de janeiro e, portanto, importaremos os cenários de janeiro. 
52. Certifique-se que o arquivo Cenários.xlsx está selecionado no campo Pasta:. 
53. Selecione a planilha Janeiro. 
54. Clique em OK. 
 
No Gerenciador de cenários aparecem os cenários que foram importados. 
Excel 2010 – Avançado 
 
 
 
 
178 Senac São Paulo 
 
 
55. Volte ao arquivo Cenários.xlsx. 
56. Vá para a planilha Março. 
57. No Gerenciador de cenários clique no botão Resumir... 
58. Selecione a célula B6 que representa a soma das entradas e com a tecla CTRL pressionada clique em 
B11 que representa a soma das saídas e B12 que representa o saldo. 
 
 
59. Clique em OK. Será criada uma planilha com o resumo dos cenários que foram criados. 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 179 
 Exercício Proposto 
 
1. Abra a pasta Despesas.xlsx e consolide os dados das lojas na planilha Trimestre. 
2. Crie Cenários para aumento de 20% no Gás para a Loja Centro em janeiro. 
3. Crie Cenários para queda de 10% no item telefone para a loja SUL em março. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
 
180 Senac São Paulo 
 
ANOTAÇÕES