Tarefa 18 TabelasDinamicas

Prévia do material em texto

Tarefa 18 – Criar Tabelas Dinâmicas a partir de 
Listas de Excel 
 
Microsoft Excel 0 
 
Tarefa 18: Criar Tabelas Dinâmicas a partir de Listas de Excel 
 
1. Alguns conceitos sobre Tabelas Dinâmicas 
Com tabelas dinâmicas podemos criar dinâmica e imediatamente resumos de uma lista Excel ou de 
uma base de dados externa. As tabelas dinâmicas permitem cruzar dados, resumindo dados de registos 
em dois ou mais caminhos combinando valores de diferentes campos. 
1.1 Restrições das Tabelas Dinâmicas 
Para que uma tabela dinâmica funcione correctamente devemos atender aos seguintes conselhos e 
restrições: 
• Devemos identificar cada uma das colunas com um nome (label) porque o Excel irá fazer com 
que cada label seja o nome de campo na Tabela Dinâmica. 
• Porque a Tabela Dinâmica cria os necessários totais baseados nos campos que temos na tabela, 
devemos remover quaisquer totais automáticos da lista de dados. 
• O Excel usa toda a lista, incluindo células escondidas. Se não queremos que os dados 
escondidos não constem da Tabela Dinâmica, devemos filtrar a lista de dados usando o Filtro 
Avançado. 
• O Excel permite criar uma Tabela Dinâmica que contenha um máximo de 8.000 itens. Só 
podemos ter 256 campos na área da Página e 256 campos na área de Dados. As outras áreas 
estão somente limitadas aos limites próprios da(s) folha(s) de cálculo. 
1.2 A Tabela Dinâmica e os seus campos 
Adicionamos campos importados para a Tabela Dinâmica arrastando-os para uma das quatro 
diferentes áreas: Página, Coluna, Linha e Dados. Apesar de podermos adicionar um campo a qualquer 
uma das áreas da Tabela Dinâmica, o posicionamento determina o layout da Tabela. 
Uma das características interessantes das Tabelas Dinâmicas é que o Excel insere automaticamente a 
linha e a coluna permitindo indicar o total para os valores nas linhas e colunas correspondentes da 
Tabela Dinâmica. 
 
As tabelas dinâmicas permitem, assim, organizar dados e calcular informação resumida utilizando 
categorias (campos) e funções resumo (soma, média, etc.), permitindo analisar dados rapidamente. 
1.3 Exemplo a partir de uma Lista de Dados 
 
 
 
 
 
 
Criar TD 
Tarefa 18 – Criar Tabelas Dinâmicas a partir de 
Listas de Excel 
 
Microsoft Excel 0 
 
1.3.1 Acrescentar, retirar detalhes ou esconder itens. 
 
 
 
 
 
 
 
 
 
 
 
 
1.3.2 Alterar rapidamente a estrutura de visualização dos dados. 
 
 
 
 
 
 
 
 
 
 
 
1.3.3 Criar gráficos que se alteram em função dos dados e estrutura da tabela 
 
 
 
 
 
 
 
 
 
 
 
1.4 Como se organizam os dados numa Tabela Dinâmica 
Os dados organizam-se através da escolha de campos e em função dos seus itens. 
1.4.1 Campos 
São categorias de dados e correspondem a campos da lista de dados. 
Aumentar um nível 
de detalhe, colocando 
Distrito em linha. 
As categorias podem ser arranjadas em função da análise 
desejada. É possível mover o campo Distrito para uma 
orientação em coluna. 
Representação gráfica (sem totais)
0
500
1000
1500
2000
2500
3000
3500
4000
4500
Ja
n
Fe
v
M
ar Ab
r
M
ai
Ja
n
Fe
v
M
ar Ab
r
M
ai
Ja
n
Fe
v
M
ar Ab
r
M
ai
Braga Porto Viseu
Meses/Distritos
Ve
nd
as
r1
r2
r3
Tarefa 18 – Criar Tabelas Dinâmicas a partir de 
Listas de Excel 
 
Microsoft Excel 0 
 
1.4.2 Itens 
São categorias de um campo que determinam a forma de resumo dos dados. 
 
 
 
1.4.3 As correspondências entre os campos e itens dos dados e os elementos da tabela 
As correspondências são as seguintes: 
• Campos – tornam-se “cabeçalhos de itens” 
• Itens – tornam-se cabeçalhos de linhas ou colunas 
 
Tipos de campos: 
• Campos linha – campo com orientação em linha; 
• Campos coluna – campo com orientação em coluna; 
• Campos página – campo com orientação em página; 
• Campos dados – tipicamente com valores numéricos a resumir (por defeito através da função 
SOMA se os dados forem numéricos ou, em caso contrário, por defeito com função 
CONTAGEM) 
 
 
 
 
 
 
 
 
 
Campos Itens 
Trimestre 1T 2T 
Mês Jan Fev Mar Abr Mai 
Região Centro Norte 
Distrito Braga Porto Viseu 
Representante r1 r2 r3 
Vendas 
Unidades 
Campo Página Campo Coluna 
Campo Linha 
Campo Dados 
Tarefa 18 – Criar Tabelas Dinâmicas a partir de 
Listas de Excel 
 
Microsoft Excel 0 
 
2. Execução da tarefa: Criar Tabela Dinâmica 
a) - Abrir ficheiro “Vendas_TD.XLS”; 
b) - Colocar cursor na lista de dados; 
c) - Activar menu “Dados -> Relatório de tabelas e de gráficos dinâmicos…” 
 
 
d) - Seguir os passos seguintes: 
 
 
i. Clicar “Seguinte”… 
 
Como inicialmente tínhamos colocado o cursor na Lista de Dados, o 
Assistente seleccionou a área, ou intervalo, por nós. Assim, … 
 
ii. Clicar em “Seguinte”… 
 
Escolhemos “folha de cálculo existente” com o nome de TabelaDinâmica, 
célula A23. 
 
Tarefa 18 – Criar Tabelas Dinâmicas a partir de 
Listas de Excel 
 
Microsoft Excel 0 
 
iii. Clicar “Concluir”… 
 
A estrutura da TD aparece desenhada bem como uma janela com todos os 
campos da Lista de Dados. 
 
iv. Bastará, então, “arrastar” o(s) campo(s) para as respectivas áreas. 
Interessa-nos que: na área Página fique o campo Trimestre; na área 
Coluna o campo Representante; na área Linha o campo Mês e na área 
Dados o campo Vendas. O layout da TD vai modificando-se ao “ritmo” 
da introdução de novos campos… 
 
 
v. Active o Assistente de Tabelas Dinâmicas… e clique em Esquema. 
 
 
vi. Verificamos que aparece o esquema da TD com os campos que 
arrastámos. Aproveitemos, então, para aumentar o detalhe da TD 
introduzindo na área Linha o campo Distrito de modo que este fique 
acima do campo Mês. 
 
Tarefa 18 – Criar Tabelas Dinâmicas a partir de 
Listas de Excel 
 
Microsoft Excel 0 
 
vii. Clicando em OK, fechamos o formulário, e em Concluir fechamos o 
“Assistente”… 
 
 
viii. Arrastemos o campo Distrito para a área Coluna e verifique-se o 
resultado… 
 
 
ix. Verificamos que junto aos campos que arrastámos se encontra um botão 
. Ao clicarmos neste aparece a lista completa dos itens 
correspondentes ao campo, pelo que poderemos escolher todos ou 
alguns. Assim, no campo Representante escolhamos somente o r1. 
 -> 
x. Outra forma de “ocultar itens” é clicar com o botão direito do rato sobre 
eles e escolher Ocultar na caixa de menu. 
 
Tarefa 18 – Criar Tabelas Dinâmicas a partir de 
Listas de Excel 
 
Microsoft Excel 0 
 
e) - Actualizar alterações dos dados origem: 
Quando modificamos os dados de origem, a TD não se Auto-actualiza, pelo que temos de 
ser nós a fazê-lo. Para isso temos dois modos: 
i. Clicando o botão direito do rato na TD aparece um menu. Neste escolher 
a opção “Actualizar dados”. 
 
ii. Colocando o cursor na TD a barra de ferramentas das Tabelas Dinâmicas 
fica activa (se não tiver a barra das TD, personalize o Excel…). Clicando 
no botão actualizamos os dados. 
Barra de Ferramentas das TD 
f) - Retirar campos 
i. Para retirar campos, basta arrastá-los para fora da TD… ou 
ii. Activando o Esquema, no Assistente, proceder de forma semelhante… 
2.1 Criar Gráficos a partir de Tabelas Dinâmicas 
a) - Façamos com que a TD tenha o seguinte layout: 
 
 
b) - Seleccionar a área que conterá os dados do nosso gráfico: A3:D9. 
 
 
Tarefa 18 – Criar Tabelas Dinâmicas a partir de 
Listas de Excel 
 
Microsoft Excel 0 
 
c) - Clicar no Assistente de Gráficos . Uma folha de gráfico é gerada automaticamente. 
 
 
d) - Por defeito os nomes dos campos aparecem no gráfico. Para que estes não apareçam 
activar a opção Ocultar botões de campo de gráfico dinâmico do menu Gráficos 
Dinâmicos. 
 -> 
0
2000
4000
6000
8000
10000
12000
Jan Fev Mar
r3
r2
r1
 
 
e) - Realizar as operações sobre o gráfico de forma a ficar com o seguinte aspecto: 
Jan Fev Mar Abr Mai
0
1000
2000
3000
4000
5000
6000
7000
Ve
nd
as
Meses
Vendas por Representante
r1
r2
r3
 
 
f) - Restrinja os dados de modo a somente aparecerem os do 1º Trimestre. 
Jan Fev Mar
0
1000
2000
3000
4000
5000
6000
7000
Ve
ndas
Meses
Vendas por Representante
r1
r2
r3
 
Tarefa 18 – Criar Tabelas Dinâmicas a partir de 
Listas de Excel 
 
Microsoft Excel 0 
 
3. Alguns tópicos avançados 
Aqui abordaremos os Totais; Formatação; Tipo de Cálculo e Agrupamento de Itens de Campo. 
3.1 Totais 
Podemos omitir os totais que se encontram na TD. Para isso fazemos: 
a) - Clicando com o botão direito do rato sobre a TD, escolher no menu a opção Opções de 
tabela… 
 
 
b) - No formulário retirar as opções assinaladas… 
 
c) - O Resultado é: 
 
 
Tarefa 18 – Criar Tabelas Dinâmicas a partir de 
Listas de Excel 
 
Microsoft Excel 0 
 
3.1.1 Subtotais 
Supondo a TD seguinte: 
 
Queremos tirar os subtotais dos meses. Para isso fazemos: 
a) - Duplo clique sobre o campo ao qual queremos tirar os subtotais. Aparece um formulário, e 
neste seleccionamos Nenhuns para a opção Subtotais. 
 
O resultado final será: 
 
3.1.2 Subtotais em campos interiores. 
Podemos acrescentar subtotais somente para alguns itens de um determinado campo. Assim: 
a) - Para apresentar subtotais para um campo linha ou coluna interno, faça duplo clique no campo 
pretendido, ou no menu de contexto do campo pretendido, e seleccionar definições de campo. 
Tarefa 18 – Criar Tabelas Dinâmicas a partir de 
Listas de Excel 
 
Microsoft Excel 0 
 
 
 
b) - Depois clique sobre Personalizados em Subtotais e, em seguida, faça clique sobre uma 
função de sumário na caixa situada à direita. 
 
 
c) - Para remover subtotais faça clique sobre Nenhuns… 
 
 
3.2 Formatação 
3.2.1 Alterar o formato numérico da área de dados de um relatório de TD 
a) - Seleccione uma célula na área de dados do relatório de TD; 
b) - Na barra de ferramentas TD faça clique sobre Definições de campo…; 
c) - Faça clique sobre Número; 
 
Tarefa 18 – Criar Tabelas Dinâmicas a partir de 
Listas de Excel 
 
Microsoft Excel 0 
 
d) - Na lista Categoria, faça clique sobre a categoria de formato que deseja. Por exemplo, moeda; 
e) - Seleccione as opções desejadas e, em seguida, faça clique sobre OK. 
 -> 
3.2.2 Mudar o nome de um campo ou item de uma TD 
a) - Faça duplo clique sobre o campo ou item cujo nome deseja mudar. No nosso caso 
Representante; 
 
b) - Escreva um novo nome: Vendedor; 
 
c) - Prima Enter ou clique OK. 
 
Tarefa 18 – Criar Tabelas Dinâmicas a partir de 
Listas de Excel 
 
Microsoft Excel 0 
 
3.3 Tipos de Cálculo 
Podemos efectuar vários tipos de “prospecções” sobre os dados que dispomos, criando sumários 
variados. 
3.3.1 Alterar tipo de cálculo de dados 
a) - Seleccione uma célula na área de dados do relatório de TD; 
b) - Na barra de ferramentas TD faça clique sobre Definições de campo…; 
c) - Escolher Opções; 
 
 
d) - Em Mostrar dados como: seleccionar Tipo de cálculo. 
 
 
e) - Seleccionar campos e/ou itens em “campos base” e/ou “Item base”, tendo em conta a função 
escolhida. Os tipos existentes são: 
• Diferença de - visualiza dados como a diferença entre o campo e item especificado; 
• % De - visualiza dados como % do campo e item especificados; 
• % Diferença De - combina os 2 tipos de cálculos anteriores, ou seja, utiliza o mesmo 
método que a função Diferença De, mas mostra a diferença como uma percentagem dos 
dados base; 
• Executar total em - visualiza valores acumulados; 
• % de linha - mostra os dados de cada linha como uma % do total dessa linha; 
• % de Coluna - mostra os dados de cada coluna como uma % do total dessa coluna; 
• % de total - Mostra os dados como uma percentagem do total Global de todos os dados; 
Tarefa 18 – Criar Tabelas Dinâmicas a partir de 
Listas de Excel 
 
Microsoft Excel 0 
 
• Índice remissivo - aplica o seguinte método de cálculo: (valor_na_célula) * (Total_global) 
/(total global da linha)*(total global da coluna). 
Escolha o tipo % de linha e verifique o resultado. 
 
 
3.3.2 Ordenar Item por Valor na Área de Dados 
Para esta demonstração, “construa” a seguinte TD: 
 
Queremos ordenar pelo campo Distrito. Para tal, façamos os seguintes passos: 
a) - Seleccionar o Campo Distrito. 
Tarefa 18 – Criar Tabelas Dinâmicas a partir de 
Listas de Excel 
 
Microsoft Excel 0 
 
b) - Seleccionar no Menu Principal a opção Dados. Nesta opção escolher a opção Ordenar. 
Abrir-se-á um formulário com opções de ordenação. Escolha as opções da figura. 
 
Se for seleccionada uma célula da área de dados, recorrendo ao botão Opções é possível 
escolher os tipos de ordenação (da esquerda para a direita, ou de cima para baixo). 
 
c) - Recorrendo, ainda, ao botão Opções, é possível escolher ordenações especiais (Ex: ordenação 
por meses) 
 
 Neste caso, deixe em Normal e após fechar os formulários, clicando em OK, verifique o 
resultado. 
 
 Na folha seguinte encontram-se alguns exercícios que poderá utilizar para aplicar os conceitos 
adquiridos. 
Tarefa 18 – Criar Tabelas Dinâmicas a partir de 
Listas de Excel 
 
Microsoft Excel 0 
 
a) Detalhe as vendas no mês de Janeiro, em função do distrito; Detalhe também as vendas em todos os 
outros meses excepto Fevereiro, utilizando o processo mais simples; Oculte os detalhes das vendas 
no mês de Janeiro; Posteriormente remova o campo distrito criado. 
b) Oculte as vendas no mês de Janeiro; Posteriormente, volte a visualizar a linha que ocultou. 
c) Adicione à tabela dinâmica o campo distrito, em linha e à esquerda do campo mês. 
d) Oculte os itens Braga e Porto. Posteriormente, voltar a visualizar os itens Braga e Porto. 
e) Altere a orientação do campo Distrito para coluna; Oculte os totais e subtotais; Altere a orientação 
do campo distrito para linha. 
f) Represente graficamente o conteúdo da tabela dinâmica sem seleccionar o campo na orientação 
página. 
g) Altere a ordem de apresentação dos itens deslocando o item Viseu para o início. Observe o gráfico; 
Posteriormente, reponha a ordem que existia entre os itens. Oculte os detalhes do item Braga; 
Observe o gráfico e volte a visualizar os detalhes que ocultou. 
h) Altere a tabela dinâmica por forma a visualizar as vendas por meses e vendedores somente para o 2º 
trimestre. 
i) Na tabela dinâmica criada no ponto anterior apresente as diferenças de vendas relativas ao 1º mês 
do 2º trimestre.