Tarefa 18 – Criar Tabelas Dinâmicas a partir de
Listas de Excel
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
Microsoft Excel
0
Tarefa 18 – Criar Tabelas Dinâmicas a partir de
Listas de Excel
1.3.1 Acrescentar, retirar detalhes ou esconder itens.
Aumentar um nível
de detalhe, colocando
Distrito em linha.
1.3.2 Alterar rapidamente a estrutura de visualização dos dados.
As categorias podem ser arranjadas em função da análise
desejada. É possível mover o campo Distrito para uma
orientação em coluna.
1.3.3 Criar gráficos que se alteram em função dos dados e estrutura da tabela
4500
4000
3500
3000
2500
2000
1500
1000
500
0
r1
r2
Braga
Porto
Abr
Mai
Fev
Mar
Jan
Abr
Mai
Fev
Mar
Jan
Abr
Mai
Fev
Mar
r3
Jan
Vendas
Representação gráfica (sem totais)
Viseu
Meses/Distritos
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.
Microsoft Excel
0
Tarefa 18 – Criar Tabelas Dinâmicas a partir de
Listas de Excel
1.4.2 Itens
São categorias de um campo que determinam a forma de resumo dos dados.
Campos
Trimestre
Mês
Região
Distrito
Representante
Vendas
Unidades
Itens
1T
2T
Jan Fev Mar Abr Mai
Centro Norte
Braga Porto Viseu
r1 r2 r3
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)
Campo Página
Campo Coluna
Campo Dados
Campo Linha
Microsoft Excel
0
Tarefa 18 – Criar Tabelas Dinâmicas a partir de
Listas de Excel
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.
Microsoft Excel
0
Tarefa 18 – Criar Tabelas Dinâmicas a partir de
Listas de Excel
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.
Microsoft Excel
0
Tarefa 18 – Criar Tabelas Dinâmicas a partir de
Listas de Excel
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.
Microsoft Excel
0
Tarefa 18 – Criar Tabelas Dinâmicas a partir de
Listas de Excel
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.
Microsoft Excel
0
Tarefa 18 – Criar Tabelas Dinâmicas a partir de
Listas de Excel
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.
12000
10000
8000
r3
r2
6000
r1
4000
2000
0
Jan
Fev
Mar
->
e) - Realizar as operações sobre o gráfico de forma a ficar com o seguinte aspecto:
Vendas por Representante
7000
6000
Vendas
5000
4000
r1
3000
r2
2000
r3
1000
0
Jan Fev
Mar Abr
Meses
Mai
f) - Restrinja os dados de modo a somente aparecerem os do 1º Trimestre.
Vendas por Representante
7000
6000
Vendas
5000
4000
3000
r1
r2
2000
r3
1000
0
Jan
Fev
Mar
Meses
Microsoft Excel
0
Tarefa 18 – Criar Tabelas Dinâmicas a partir de
Listas de Excel
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 é:
Microsoft Excel
0
Tarefa 18 – Criar Tabelas Dinâmicas a partir de
Listas de Excel
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.
Microsoft Excel
0
Tarefa 18 – Criar Tabelas Dinâmicas a partir de
Listas de Excel
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;
Microsoft Excel
0
Tarefa 18 – Criar Tabelas Dinâmicas a partir de
Listas de Excel
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.
Microsoft Excel
0
Tarefa 18 – Criar Tabelas Dinâmicas a partir de
Listas de Excel
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;
Microsoft Excel
0
Tarefa 18 – Criar Tabelas Dinâmicas a partir de
Listas de Excel
•
Í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.
Microsoft Excel
0
Tarefa 18 – Criar Tabelas Dinâmicas a partir de
Listas de Excel
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.
Microsoft Excel
0
Tarefa 18 – Criar Tabelas Dinâmicas a partir de
Listas de Excel
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.
Microsoft Excel
0