Excel 2010 Avanado

Senac So Paulo 159

OBJETIVOS Apresentar recursos para consolidao e resumo de dados.

Excel 2010 Avanado

160 Senac So Paulo

Excel 2010 Avanado

Senac So Paulo 161

Atividade 1 Consolidao de Dados

Objetivo : Apresentar o recurso Consolidar Dados.

Tarefa : Reunir informaes de vrias planilhas e arquivos.

Nesta atividade voc somar os valores previstos de entrada e sada dos meses de janeiro, fevereiro e maro contidos nas planilhas do arquivo Movimento.xlsx e valores do ms de abril que se encontram no arquivo Movimento-abril.xlsx. Ser utilizado o recurso Consolidar. Este recurso consolida os dados de duas formas: consolidao por posio e consolidao por categoria.

Na consolidao por posio presume-se que o dado mantm sua posio relativa, como no exemplo abaixo:

As informaes de Janeiro do Item 1 ficam sempre no canto superior esquerdo, de Fevereiro do Item 1 direita e assim por diante. Nesta situao os cabealhos de coluna e de linha no so importantes para a consolidao, bastando apenas selecionar o intervalo de dados.

Porm as tabelas podem no manter a posio dos dados, como no exemplo abaixo:

Neste caso a consolidao deve considerar os cabealhos das colunas e das linhas como referencial para consolidar as informaes. Deve-se incluir os cabealhos no intervalo a consolidar.

Excel 2010 Avanado

162 Senac So Paulo

1. Abra o arquivo Movimento.xlsx. Analise as informaes das planilhas Janeiro, Fevereiro e Maro. 2. Posicione-se na clula A3 da planilha Consolidado. 3. Clique na opo Consolidar do grupo Ferramentas de dados da guia Dados.

4. Escolha a funo desejada para a consolidao das planilhas. Nesse caso indique Soma.

5. V para o campo Referncia, clique na guia da planilha Janeiro e selecione o intervalo de dados a ser considerado para consolidar.

Excel 2010 Avanado

Senac So Paulo 163

6. Clique em Adicionar. 7. Repita os passos 5 e 6 para os meses Fevereiro e Maro.

Os dados do ms de abril esto em um arquivo separado que no est na memria.

8. Clique no boto Procurar... 9. Localize o arquivo Movimento-Abril.xlsx. 10. Depois de selecion-lo no quadro Procurar, clique em OK. 11. Clique na tecla de funo F2. 12. Clique em qualquer local dentro da caixa de referncia para desfazer a seleo. 13. Tecle End e digite o nome do intervalo que contm os dados: Abril.

14. Clique em Adicionar.

Sempre use a tecla F2 caso voc no tenha acesso com o cursor para digitar o intervalo. Se tentar usar outro mtodo, possvel que voc perca a indicao do caminho e do nome do arquivo.

Excel 2010 Avanado

164 Senac So Paulo

15. Para que os itens sejam relacionados e seus valores considerados corretamente, assinale Coluna esquerda em Usar rtulos na.

16. Clique em OK.

Excel 2010 Avanado

Senac So Paulo 165

Atividade 2 Consolidao de Dados com Vnculo

Objetivo : Consolidar dados de vrias planilhas mantendo o vnculo do total com as

planilhas de origem.

Tarefa : Consolidar os dados das planilhas dos arquivos Movimento e Movimento-Abril, para que qualquer alterao nos dados por ser vista na planilha de totais.

1. V para a planilha Vinculado. 2. Posicione-se na clula A3 da planilha Vinculado. 3. Clique na opo Consolidar do grupo Ferramentas de dados da guia Dados.

4. Escolha a funo Soma para a consolidao das planilhas. 5. V para o campo Referncia, 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 Maro.

Excel 2010 Avanado

166 Senac So Paulo

8. Clique no boto Procurar... 9. Localize o arquivo Movimento-Abril.xlsx. 10. Depois de selecion-lo no quadro Procurar, clique em OK. 11. Clique na tecla de funo F2. 12. Clique em qualquer local dentro da caixa de referncia para desfazer a seleo. 13. Tecle End e digite o nome do intervalo que contm os dados: Abril. 14. Clique em Adicionar. 15. Para que os itens sejam relacionados e seus valores considerados corretamente, assinale Coluna

esquerda em Usar rtulos na.

16. Assinale Criar vnculos com dados de origem.

17. Clique em OK. Analise as clulas que contm os valores. Agora elas possuem frmulas. Qualquer alterao 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 nmeros e sinais do lado esquerdo da planilha a qual chamamos de Estrutura de Tpicos. Sua funo permitir ao usurio ocultar ou exibir dados que interferem nas frmulas criadas. Agora voc far uso deste recurso e ter uma viso mais completa na prxima atividade.

19. Clique nos botes que se assemelham a sinais de adio. As linhas que contm os itens que contribuem para o subtotal so exibidas. Ao clicar novamente no boto, que agora tem aparncia do sinal de subtrao, os itens so ocultos novamente.

20. Clique no nmero 2 e todos os detalhes de todos os subtotais so exibidos. Se voc clicar no nmero 1 os itens sero recolhidos.

Excel 2010 Avanado

Senac So Paulo 167

Atividade 3 Estrutura de Tpicos

Objetivo : Apresentar o recurso de Estrutura de tpicos que configura os dados para serem

exibidos ou ocultos.

Tarefas : Introduzir o recurso AutoTpicos e o recurso Estrutura de Tpicos para agrupar dados arbitrrios.

1. Abra o arquivo Tpicos.xlsx. 2. Clique na parte Inferior do boto Agrupar do grupo Estrutura de Tpicos da guia Dados.

3. Clique em AutoTpicos.

Os sinais so usados para ocultar/exibir os itens que compem cada subtotal. Os nmeros permitem a escolha do nvel de subtotal que ser exibido para todos os subtotais.

4. Clique nos sinais e nmeros para familiarizar-se com seu funcionamento.

O recurso AutoTpicos cria os agrupamentos reconhecendo os itens que compem os clculos. Considera, portanto, a lgica usada na construo da frmula.

Voc vai agrupar itens que no contribuem para os clculos da planilha. Como o Excel no pode reconhecer a lgica devemos informar arbitrariamente os itens a serem agrupados.

5. Selecione as colunas F, G e H. 6. Clique na parte Inferior do boto Agrupar do grupo Estrutura de Tpicos da guia Dados. 7. Clique em Agrupar.

Excel 2010 Avanado

168 Senac So Paulo

Uma vez que a inteno Agrupar, podemos clicar diretamente na parte superior do boto Agrupar.

Se a seleo no for feita pelos botes 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 boto Desagrupar do grupo Estrutura de tpicos da guia Dados.

Para retirar a estrutura de tpicos da planilha clique na parte inferior do boto Desagrupar e escolha Limpar Estrutura de Tpicos.

Excel 2010 Avanado

Senac So 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 classificao necessria para que os itens que formam um mesmo grupo fiquem juntos. O recurso percorre o campo escolhido e subtotaliza a cada vez que a informao naquela coluna fica diferente. Se os dados no estiverem juntos, vrios subtotais do mesmo item podem compor o resultado.

3. Clique no boto Subtotal do grupo Estrutura de Tpicos da guia Dados.

4. No campo A cada alterao em: escolha E/S. 5. Escolha a funo Soma. 6. Marque os campos a serem totalizados. Neste exemplo use Valor. 7. Clique em OK. 8. Clique novamente no boto Subtotal do grupo Estrutura de Tpicos da guia Dados para acrescentar

outro subtotal.

9. Escolha o campo Categoria, mantenha a funo Soma selecionada e o campo Valor para adicionar subtotal.

10. Desmarque a opo Substituir subtotais atuais. 11. Marque a opo Quebra de pgina entre grupos.

Excel 2010 Avanado

170 Senac So Paulo

12. Clique em OK.

Os Subtotais adicionais so inseridos antes dos subtotais inseridos anteriormente. Por essa razo, quando optamos pela quebra de pgina entre grupos quando j existem subtotais aplicados, a quebra de pgina pode no ser a desejada. necessrio efetuar a quebra dos grupos manualmente. Siga os passos descritos a seguir.

13. Clique no boto Quebras do grupo Configurar Pgina da guia Layout da Pgina.

14. Clique na opo Redefinir todas as quebras de pgina. 15. Localize a linha pontilhada vertical que indica a largura mxima a ser impressa. 16. Torne ativa a clula imediatamente direita da linha e logo abaixo do subtotal. 17. Clique no boto Quebras e escolha Inserir Quebra de Pgina.

18. Repita os passos 15, 16 e 17 para cada subtotal desejado. 19. Clique na opo Imprimir da guia Arquivo.

Excel 2010 Avanado

Senac So Paulo 171

20. Visualize a primeira pgina da impresso direita.

Nas pginas seguintes os cabealhos das colunas no so repetidos.

21. Clique na guia Layout da Pgina. 22. Clique no boto Imprimir Ttulos do grupo Configurar Pgina.

Excel 2010 Avanado

172 Senac So Paulo

23. Selecione na planilha as linhas a repetir na parte superior.

24. Clique em OK. As linhas selecionadas sero repetidas em cada pgina impressa.

Excel 2010 Avanado

Senac So Paulo 173

Atividade 5 Cenrios

Objetivo : Apresentar o recurso Gerenciador de Cenrios para realizar simulaes.

Tarefa : Formular hipteses para analisar os efeitos nos saldos ao se alterarem as entradas e as sadas.

Voc ir simular variaes de valores para as planilhas analisando cenrios provveis. Voc criar um primeiro cenrio com os dados atuais da planilha para poder recuperar estes dados no futuro.

Em seguida voc criar um cenrio prevendo aumento de 25% nos itens de Entrada de janeiro, aumento de 15% nas Sadas de fevereiro e queda de 10% nas Sadas de maro.

1. Abra o arquivo Cenrios.xlsx. 2. V para a planilha Janeiro. 3. Clique no boto Teste de Hipteses do grupo Ferramentas de Dados da guia Dados.

4. Escolha Gerenciador de Cenrios... A lista aparece vazia pois nenhum cenrio foi criado ainda. Clique no boto Adicionar.

5. Preencha o campo Nome do Cenrio com Dados Originais. Voc deve selecionar as clulas variveis. So as clulas que tero seus valores alterados para anlise dos resultados.

As clulas selecionadas no devem conter frmulas, pois quando um cenrio aplicado as frmulas sero substitudas por valores.

6. Selecione os intervalos B3:B4 e B6:B9, no campo Clulas variveis.

Excel 2010 Avanado

174 Senac So Paulo

7. Preencha o campo Comentrio como achar necessrio. 8. Clique em OK. 9. As clulas variveis aparecem para receber novos valores. Como a idia recuperar os valores atuais,

eles no sero alterados.

10. Clique em OK para que o cenrio seja criado como os valores atuais. 11. Clique em Adicionar. 12. Preencha o campo Nome do cenrio com Aumento de 25% nas Entradas de janeiro. 13. Mantenha as mesmas clulas variveis. 14. Preencha o campo Comentrio.

15. Clique em OK. 16. Clique em OK. 17. Digite a frmula que representa o clculo de acrscimo de 25% ou digite o valor j calculado.

Excel 2010 Avanado

Senac So Paulo 175

18. Clique em OK.

Surge um quadro avisando que as frmulas foram convertidas nos valores correspondentes ao clculo.

O Gerenciador de cenrios agora apresenta os cenrios que j foram criados.

19. Selecione o cenrio que quer analisar e clique no boto Mostrar.

Para alterar os valores de um cenrio clique no boto Editar... e modifique os valores das clulas variveis. Clique em Excluir para eliminar um cenrio.

Agora voc criar novo cenrio com a hiptese de aumento de 15% nas Sadas de fevereiro. Lembre-se de criar um cenrio para recuperar os dados originais.

Excel 2010 Avanado

176 Senac So Paulo

20. V para a planilha Fevereiro. 21. Clique no boto Teste de Hipteses do grupo Ferramentas de Dados da guia Dados. 22. Escolha Gerenciador de Cenrios... 23. Clique no boto Adicionar. 24. Preencha o campo Nome do Cenrio com Dados Originais. 25. No campo Clulas variveis informe os intervalos B3:B5 e B7:B9. 26. Preencha o campo Comentrio. 27. Clique em OK at retornar ao Gerenciador de cenrios. 28. Clique no boto Adicionar. 29. Preencha o campo Nome do Cenrio com Aumento de 15% nas sadas de fevereiro. 30. Nas clulas variveis B7, B8 e B9 escreva a frmula 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 cenrio que simula a hiptese de queda de 10% nas sadas de maro. Antes crie um cenrio para recuperar os dados originais da planilha.

34. V para a planilha Maro. 35. Clique no boto Teste de Hipteses do grupo Ferramentas de Dados da guia Dados. 36. Escolha Gerenciador de Cenrios... 37. Clique no boto Adicionar. 38. Preencha o campo Nome do Cenrio com Dados Originais. 39. No campo Clulas variveis informe os intervalos B3:B5 e B7:B10. 40. Preencha o campo Comentrio. 41. Clique em OK at retornar ao Gerenciador de cenrios. 42. Clique no boto Adicionar. 43. Preencha o campo Nome do Cenrio com Queda de 10% nas sadas de maro. 44. Nas clulas variveis B7, B8, B9 e B10 escreva a frmula que diminui 10% de cada valor.

Excel 2010 Avanado

Senac So 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 cenrios que j foram criados em outra pasta.

Agora voc abrir um arquivo e importar os cenrios criados nas atividades anteriores.

48. Abra o arquivo Cenrios-Mesclar.xlsx. 49. Clique no boto Teste de Hipteses do grupo Ferramentas de Dados da guia Dados. 50. Escolha Gerenciador de Cenrios... 51. Clique no boto Mesclar.

Na caixa de combinao Pasta: aparecem os arquivos do Excel que esto abertos na memria. Na caixa de listagem Planilha: aparecem as planilhas do arquivo selecionado. Os dados do arquivo correspondem aos valores de janeiro e, portanto, importaremos os cenrios de janeiro.

52. Certifique-se que o arquivo Cenrios.xlsx est selecionado no campo Pasta:. 53. Selecione a planilha Janeiro. 54. Clique em OK.

No Gerenciador de cenrios aparecem os cenrios que foram importados.

Excel 2010 Avanado

178 Senac So Paulo

55. Volte ao arquivo Cenrios.xlsx. 56. V para a planilha Maro. 57. No Gerenciador de cenrios clique no boto Resumir... 58. Selecione a clula B6 que representa a soma das entradas e com a tecla CTRL pressionada clique em

B11 que representa a soma das sadas e B12 que representa o saldo.

59. Clique em OK. Ser criada uma planilha com o resumo dos cenrios que foram criados.

Excel 2010 Avanado

Senac So Paulo 179

Exerccio Proposto

1. Abra a pasta Despesas.xlsx e consolide os dados das lojas na planilha Trimestre. 2. Crie Cenrios para aumento de 20% no Gs para a Loja Centro em janeiro. 3. Crie Cenrios para queda de 10% no item telefone para a loja SUL em maro.

Excel 2010 Avanado

180 Senac So Paulo

ANOTAES