Cap 08 - Excel 2010 Avançado

Prévia do material em texto

Excel 2010 – Avançado 
 
 
 
Senac São Paulo 137 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
OBJETIVOS 
 Resumir dados de planilhas utilizando a Tabela Dinâmica. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
 
138 Senac São Paulo 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 139 
 
Atividade 1 – Criação da Tabela Dinâmica 
 
Objetivo : • Resumir dados usando a Tabela Dinâmica. 
Tarefas : • Construir Tabela Dinâmica que apresente dados agrupados por projeto. 
 
Utilizando os dados do arquivo Movimento Financeiro.xlsx você criará uma apresentação da soma dos 
valores agrupados por projeto. 
1. Abra o arquivo Movimento Financeiro.xlsx. 
2. Clique na metade superior do botão Tabela Dinâmica do grupo Tabelas da guia Inserir. 
 
 
 
Se você clicar na metade inferior do botão, as sub-opções Tabela Dinâmica e Gráfico Dinâmico 
aparecem. Escolha, então, Tabela Dinâmica. 
 
 
 
3. Para preencher o campo Tabela/Intervalo do quadro Criar Tabela Dinâmica, selecione o intervalo 
A3:G56 da planilha Dados. 
4. Selecione Nova Planilha para criar a Tabela Dinâmica em uma planilha independente. 
 
 
5. Clique em OK. Surge uma nova guia Ferramentas de tabela Dinâmica com duas subguias: Opções e 
Design. 
Excel 2010 – Avançado 
 
 
 
 
140 Senac São Paulo 
 
 
À direita surge o Painel de Tarefas Lista de Campos da Tabela Dinâmica 
. 
 
Na lista de campos você escolherá os campos (colunas) que utilizará no relatório de tabela dinâmica. 
• Filtro de Relatório – permite que você escolha um ou mais campos para filtrar dados no seu 
relatório. 
• Rótulos de Linha – são os campos onde itens iguais são agrupados. 
• Rótulos de Coluna – são os campos que serão indicados quando necessitarmos do cruzamento de 
duas informações. 
• Valores – corresponde a uma ou mais colunas que terão suas informações consolidadas. 
 
6. Marque o campo Projeto e o campo Valor. Será construído o relatório agrupando os valores por 
Projeto. 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 141 
 
Atividade 2 – Atualização e Inclusão de Dados 
 
Objetivo : • Modificar os dados existentes e acrescentar novos dados tornando-os visíveis na 
Tabela Dinâmica. 
Tarefas : • Alterar dados já existentes na guia Dados e atualizar a Tabela Dinâmica. 
 • Inserir uma nova linha entre linhas já existentes e nova linha após o último 
projeto e torná-los visíveis na Tabela Dinâmica. 
 
1. Torne a célula A13 como célula ativa na planilha Dados. 
2. Insira uma nova linha. 
3. Acrescente os dados: 
• Data: 23/10/2010 
• Projeto: Hidroelétrica Torrente 
• Item: Venda de Ações 
• Categoria: Dinheiro dos Sócios 
• E/S: Entrada 
• Valor: 10.000.000,00 
• Estado: Paraná. 
 
4. Volte à Tabela Dinâmica. Este novo projeto não foi incluído. 
5. Clique no botão Atualizar do grupo Dados da guia Opções e o novo projeto será relacionado no final. 
 
 
6. Clique no botão classificar para ordenar os projetos. 
 
 
7. Inclua mais uma entrada na última linha vazia da planilha Dados. Digite: 
• Data: 20/06/2011 
• Projeto: Aeroporto Perus 
• Item: Duplicatas – Desconto 
• Categoria: Contas a Receber 
• E/S: Entrada 
Excel 2010 – Avançado 
 
 
 
 
142 Senac São Paulo 
• Valor: 500.000,00 
• Estado: Goiás. 
 
A linha acrescentada está fora do intervalo de dados reconhecido pela Tabela Dinâmica. Portanto, o 
recurso Atualizar não terá efeito neste caso. É necessário redefinir o intervalo. 
8. Volte à Tabela Dinâmica. 
9. Clique em Alterar Fonte de Dados do grupo Dados da guia Opções. 
 
 
10. Digite ou redefina com o mouse o novo intervalo que inclua a linha acrescentada. Após a definição do 
novo intervalo um novo quadro surge. 
 
 
11. Clique em OK. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 143 
 
Atividade 3 – Criação de outros níveis 
 
Objetivo : • Acrescentar subníveis à Tabela Dinâmica. 
Tarefa : • Subdividir os valores de cada projeto por Entrada e Saída. Cada um desses níveis 
será, também subdividido por Categoria. 
 
1. Marque E/S e Categoria no painel da lista de campos da tabela dinâmica e os dados são apresentados 
com as subdivisões desejadas. 
 
 
2. Selecione a célula de qualquer subtotal e clique em Configurações do Campo do grupo Campo Ativo 
da guia Opções. 
3. Marque a opção Nenhum para ocultar os subtotais. 
 
Excel 2010 – Avançado 
 
 
 
 
144 Senac São Paulo 
 
4. Clique em OK. 
5. Na coluna Soma de Valor, dê um duplo clique na linha correspondente a um subtotal. Será incluída uma 
nova planilha na pasta de trabalho contendo os itens que formam o referido subtotal. 
 
 
Quando criamos uma Tabela Dinâmica com a opção Nova Planilha, uma nova planilha é 
acrescentada. É aconselhável renomearmos as planilhas que vão sendo incluídas com nomes que 
indiquem o conteúdo da planilha. 
 
Para alterar o layout de forma a cruzar as informações você pode arrastar os campos E/S para o diagrama 
na posição que representa os Rótulos das Colunas. 
6. Na parte inferior do painel de tarefas, arraste os campos E/S da área Rótulos de Linha para a área 
Rótulos de Colunas. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 145 
 
Atividade 4 – Formatar a Tabela Dinâmica 
 
Objetivo : • Alterar a aparência da tabela dinâmica para facilitar a visualização. 
Tarefa : • Aplicar formato monetário à Tabela Dinâmica. 
 • Aplicar Bordas à Tabela Dinâmica e alterar as funções agregadas utilizadas. 
 
1. Escolha e selecione o Estilo desejado na guia Design, no grupo Estilos de Tabela Dinâmica. 
 
 
2. Na guia Opções, no grupo Campo Ativo, clique em Configurações do Campo. 
 
 
No quadro que surge, analise as várias opções de funções que estão disponíveis para resumir o campo. 
Além de somar, você pode contar, calcular a média aritmética, obter o valor máximo ou mínimo, etc. 
3. Clique em Formato do Número e escolha Moeda. 
 
Excel 2010 – Avançado 
 
 
 
 
146 Senac São Paulo 
 
 
4. Clique em OK. Este será o resultado. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 147 
 
Atividade 5 – Agrupar Itens 
 
Objetivo : • Formar grupos de itens. 
Tarefa : • Agrupar os projetos pelas regiões. 
 
1. Crie uma nova Tabela Dinâmica. Volte à planilha Dados, clique em Tabela Dinâmica do grupo Tabelas 
da guia Inserir. 
2. Selecione os campos Estado, Projeto e Valor. 
 
 
Os Estados correspondem às regiões Sul (Santa Catarina) e Sudeste (São Paulo, Rio de Janeiro e Minas 
Gerais). 
3. Selecione o grupo Minas Gerais, mantenha a tecla CTRL pressionada e selecione os demais Estados da 
região Sudeste (Rio de Janeiro e São Paulo). 
4. Clique em Agrupar Seleção do grupo Agrupar da guia Opções. 
 
Excel 2010 – Avançado 
 
 
 
 
148 Senac São Paulo 
 
 
5. Digite o nome da região, Sudeste, sobre Agrupar1. 
6. Selecione Paraná e Santa Catarina. 
7. Clique em Agrupar Seleção do grupo Agrupar da guia Opções. 
8. Digite Sul onde aparece Agrupar2. 
9. Digite Centro-Oeste no grupo Goiás. 
 
 
Clique em Desagrupar para desfazer os grupos.Excel 2010 – Avançado 
 
 
 
Senac São Paulo 149 
 
Atividade 6 – Aplicar Filtro de Relatório 
 
Objetivo : • Selecionar os dados a serem visualizados. 
Tarefa : • Filtrar para visualizar os projetos de um só Estado. 
 
1. Crie outra Tabela Dinâmica em uma nova planilha. 
2. Escolha os campos Projeto, Estado e Valor. 
3. Arraste o campo Estado para a área de Filtro de Relatório. 
 
 
4. Nas células A1 e B1 surge o Filtro de Relatório. 
5. Clique na caixa de combinação da célula B1. 
 
 
6. Clique no Estado que quer exibir. Os projetos e respectivos valores dos Estados não selecionados 
ficarão ocultos. 
 
Excel 2010 – Avançado 
 
 
 
 
150 Senac São Paulo 
 
Marque a caixa de verificação Selecionar Vários Itens para exibir mais de um item por vez. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 151 
 
Atividade 7 – Agrupar campos por períodos 
 
Objetivo : • Agrupar informações por períodos de tempo com base num campo de Data. 
Tarefa : • Criar Tabela Dinâmica que relaciona os dados dos projetos agrupados por 
categoria de itens e por período de tempo. 
 
1. Crie uma nova Tabela Dinâmica em uma nova planilha. 
2. Escolha os campos E/S, Categoria, Valor e Data. 
 
 
3. Arraste o campo Data para a área Rótulos de Colunas. 
 
 
Surge uma coluna para cada data relacionada no campo Data. Você vai agrupar estas datas em períodos 
que tenham significado para quem analisa. 
4. Selecione a primeira data. Clique em Agrupar Campo do grupo Agrupar da guia Opções. 
Excel 2010 – Avançado 
 
 
 
 
152 Senac São Paulo 
 
 
5. Clique em Anos. O item Meses já estava marcado e você pode acrescentar vários níveis de 
agrupamento de datas. Cada grupo em destaque formará um grupo. Caso deseje desmarcá-lo clique 
novamente sobre o período. 
6. Clique em OK. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 153 
 
Atividade 8 – Segmentação de Dados 
 
Objetivo : • Aplicar filtros aprimorados em tabelas dinâmicas. 
Tarefa : • Utilizar o recurso de segmentação de dados para filtrar dados por Projeto e por 
Estado. 
 
1. Ainda na tabela dinâmica da atividade anterior, clique no botão Segmentação de Dados, do grupo Filtro 
da guia Inserir. 
 
 
2. Marque os campos Projeto e Estado e clique em OK. 
3. Posicione as Segmentações de Dados. Todos os itens aparecem com cor destacada indicando que 
nenhum filtro foi aplicado ainda. 
4. Clique no Estado do Rio de Janeiro. Somente este Estado fica destacado. No outro quadro aparecem 
destacados os projetos deste Estado. Os dados na tabela dinâmica correspondem somente aos dados 
dos projetos do Rio de Janeiro. 
 
 
Você pode fazer a seleção de vários Estados mantendo a tecla CTRL pressionada enquanto 
clique com o mouse nos Estados escolhidos. 
 
5. Clique em um destes projetos. O resultado é a apresentação dos valores deste projeto. Neste caso, 
usamos um filtro combinando os dois campos, Estado e Projeto. 
Excel 2010 – Avançado 
 
 
 
 
154 Senac São Paulo 
 
 
 
Para desaplicar o(s) filtro(s), clique no botão Limpar Filtro de cada Segmentação de 
Dados. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 155 
 
Atividade 9 – Gráfico Dinâmico 
 
Objetivo : • Demonstrar a construção e o uso do Gráfico Dinâmico. 
Tarefa : • Construir gráfico dinâmico referente à Tabela Dinâmica. 
 
1. Em uma célula da Tabela Dinâmica, clique no botão Gráfico Dinâmico do grupo Ferramentas da guia 
Opções. 
 
 
 
Você também pode criar o gráfico com a tecla de função F11. 
 
2. Escolha o tipo e clique em OK. 
 
 
Use os recursos de gráfico do Excel. A única diferença entre um gráfico normal do Excel e do 
gráfico dinâmico é que este possui caixas de combinação para aplicar 
filtro. 
 
3. Clique no botão Alternar Linha/Coluna para que o eixo X apresente os meses e os anos. 
Excel 2010 – Avançado 
 
 
 
 
156 Senac São Paulo 
 
 
4. Use as caixas de combinação para filtrar pelo campo desejado. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 157 
 
Atividade 10 – Mostrar Páginas de Relatório 
 
Objetivo : • Utilizar recurso para criar uma cópia da Tabela Dinâmica para item do Filtro de 
Relatório. 
Tarefa : • Criar Páginas de Relatório para a Tabela Dinâmica dos projetos. 
 
1. Acrescente o campo Estado na área Filtro de Relatório. 
 
 
2. Clique no botão Tabela Dinâmica, na opção Opções e, em seguida, em Mostrar Páginas do Filtro de 
Relatório. 
 
 
Excel 2010 – Avançado 
 
 
 
 
158 Senac São Paulo 
3. Escolha Estado e clique em OK. 
 
 
Será criada uma cópia da Tabela Dinâmica para cada Estado constante na planilha Dados. 
 
 
 Exercício Proposto 
 
1. Abra o arquivo Movimento-ex-din.xlsx e construa uma tabela dinâmica que mostre os valores das 
Entradas e Saídas por Estado. 
2. Use os recursos para formatar a tabela dinâmica. 
3. Crie um gráfico dinâmico baseada nesta tabela.