Excel Tabelas dinâmicas

Prévia do material em texto

Microsoft Excel 1 
 
 
 
 
 
 
 
 
Conceitos teóricos 
As Tabelas Dinâmicas são tabelas interactivas que resumem elevadas 
quantidades de dados, usando estrutura e métodos de cálculo 
especializados. 
Trata-se de uma abordagem simplista de um conjunto de ferramentas de 
análise muito utilizadas actualmente, denominadas genericamente de OLAP 
(On line Analytical Processing), que, de entre outras características, permite 
uma análise multidimensional dos dados. 
A análise OLAP é executada normalmente sobre a informação histórica das 
empresas mantida de forma centralizada e integrada e variante no tempo, 
num “repositório” de informação. 
A principal vantagem da utilização da funcionalidade “Tabelas dinâmicas” é 
permitir “a análise de dados que produz o resumo de registos num formato 
tabular conciso”. Este formato é muito interessante do ponto de vista do 
analista de negócio, já que permite uma visão sucinta e rápida de 
informação relativa aos diversos parâmetros analíticos da empresa e a 
animação dos modelos criados. 
 
 
 
 
 
 
 
 
 
Tarefa Orientada 18 
Tabelas dinâmicas 
Análise de dados através de tabelas dinâmicas. 
Análise de dados que produz o resumo de 
registos num formato tabular conciso. 
 
Microsoft Excel 2 
 
Organização dos dados numa Tabela Dinâmica 
Os dados organizam-se através da escolha de “Campos” e em função dos 
seus “Itens”. Os “Campos” são categorias de dados que correspondem a 
campos da lista de dados. Os “Itens” são categorias de um campo que 
determinam a forma de resumo dos dados. 
 
As correspondências entre os campos e itens dos dados e os elementos da 
tabela são as seguintes: 
ƒ Campos – tornam-se “cabeçalhos de itens”; 
ƒ Itens – tornam-se cabeçalhos de linhas ou colunas. 
Os tipos de campos que existem nas Tabelas Dinâmicas são: 
ƒ Campos linha – com orientação em linha; 
ƒ Campos coluna – com orientação em coluna; 
ƒ Campos página – com orientação em página; 
ƒ Campos dados – tipicamente com valores 
numéricos a resumirem (por omissão 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 … (ilimitado) 
Unidades … (ilimitado) 
Campo Página Campo Coluna 
Campo Linha 
Campo Dados 
Campos e itens respectivos. 
 
Microsoft Excel 3 
 
 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 (etiqueta) 
porque o Excel irá fazer com que cada etiqueta seja o nome de 
campo na Tabela Dinâmica. 
ƒ Devemos remover quaisquer totais automáticos da lista de dados, pois 
a Tabela Dinâmica cria os necessários totais baseados nos campos que 
temos na tabela. 
ƒ 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. 
 
Tarefa proposta 
No ficheiro “ExcelTarefa18.xls”, encontra-se um conjunto de dados relativos 
às vendas de produtos de uma empresa do ramo de distribuição. 
As vendas estão categorizadas por um conjunto de parâmetros de análise, 
como mês, zona, vendedor, família de produtos e são mostrados 
relativamente a cada conjunto desses parâmetros duas medidas de avaliação 
de desempenho: vendas (em valores e unidades) e lucro bruto. 
Trata-se de um tipo de análise bastante interessante e que é continuamente 
realizada (de forma mais alargada e complexa) com o conjunto de 
tecnologias citado na introdução (OLAP). 
 
Microsoft Excel 4 
 
1. Criação da Tabela Dinâmica (TD) 
Pretende-se efectuar a análise das vendas (euros) dos diferentes vendedores 
por mês de actividade: 
 
a) Abrir ficheiro “ExcelTarefa18.xls”, colocar cursor na lista de dados e 
activar no menu “Dados Æ Relatório de tabelas e de gráficos 
dinâmicos…”. Seguir os passos ilustrados nas figuras seguintes: 
 
 
 
A estrutura da TD aparece desenhada bem como uma janela com todos os 
campos da Lista de Dados. 
Se a célula seleccionada estiver dentro da zona marcada a azul, a janela 
“Listas de campos…” é mostrado, caso contrário não. 
 
b) Arraste o campo “Trimestre/Ano” da janela “Lista de campos…” para a 
zona dos campos de “página”. 
 
Microsoft Excel 5 
 
 
c) Coloque os campos “Mês”, “Vendedor” e “Vendas” nas zonas “linha”, 
“coluna” e “dados” respectivamente. 
 
Reparar que para cada campo é mostrado os itens existentes. Por 
exemplo, para o campo “Vendedor” aparecem os 3 itens existentes na 
lista de dados para esse campo. 
Por omissão, é calculada a soma do campo de dados (vendas). 
Se os itens não aparecerem na ordem desejada, pode arrastar a célula 
para a posição correcta. 
d) Analise os dados e responda às seguintes questões: 
ƒ Qual o melhor vendedor? 
ƒ Quais os melhores dois meses? 
 
NOTA: No caso de fechar a janela “Lista de campos…” ( ) tem de utilizar o 
botão direito do rato sobre um dos campos e seleccionar “Mostrar lista de 
campos”. 
 
 
Microsoft Excel 6 
 
2. Filtragem na Tabela Dinâmica 
a) Para analisar com mais detalhe a informação respeitante apenas aos dois 
melhores meses, pode-se filtrar quais os meses a mostrar: 
 
Pode-se efectuar análise semelhante para o campo coluna. 
b) Mostre todos os meses e analise apenas os dados do 1º trimestre de 2001. 
 
Nos campos de página apenas se pode filtrar por item. 
 
3. Adicionar campos à Tabela Dinâmica 
Após uma análise geral do negócio, pode-se querer efectuar uma análise 
mais detalhada. Por exemplo, analisar os vendedores por distrito e mês. 
a) Utilize a janela “Lista de campos…” para adicionar o distrito às linhas. 
 
b) Se o campo Distrito ficou como a figura seguinte (distritos por mês em vez 
de meses por distrito), arraste o campo para a posição correcta. 
 
Microsoft Excel 7 
 
 
Se desejar retirar um campo, basta arrastá-lo para fora das zonas azuis. 
c) Para efectuar a análise de vendedores por distrito, arraste o campo 
Distrito da zona de linhas para a zona de colunas. 
 
d) Retire o campo distrito e filtre os dados apenas para o 2ºT de 2002. 
 
4. Altera dados da lista 
Se desejar alterar os dados iniciais ou inserir novos dados, é necessário 
efectuar a actualização na tabela dinâmica. 
a) Altere o mês dos últimos três registos da lista de dados para “Mai”. 
b) Através do menu Dados, com o botão direito do rato ou através da barra 
de ferramentas TD, escolha a opção “Actualizar dados” . 
 
c) Insira dois novos registos para o mês de “Jun” e repita a operação. 
d) Volte a colocar os dados originais e retire o filtro do Trimestre/Ano. 
 
Microsoft Excel 8 
 
5. Gráficos e Tabelas Dinâmicas 
Para apresentar os resultados das análises efectuadas podemos utilizar 
gráficos em vez das tabelas obtidas. 
a) Seleccionar a área que conterá os dados do nosso gráfico. 
 
b) Ao usar o Assistente de Gráficos ( ) uma folha de gráfico é gerada 
automaticamente. Com o botão direito do rato sobre o gráfico, mova o 
gráfico para a folha da TD. 
 
c) Altere o tipo de gráfico e filtre os dados apenas para o 1ºT de 2001. 
 
d) Se não desejar que apareçam os botões dos campos, seleccioneo gráfico 
e na barra de ferramentas TD escolher a opção: 
 
 
 
Microsoft Excel 9 
 
6. Tópicos avançados 
Para além do abordado, as Tabelas Dinâmicas permitem operações mais 
avançadas, tais como, Totais, Formatação, Tipo de Cálculo e Agrupamento 
de Itens de Campo. 
7. Totais 
Podemos omitir os totais que se encontram na TD. 
Clicando com o botão direito do rato sobre a TD, escolher no menu a opção 
“Opções de tabela…”. No formulário retirar as opções assinaladas… 
 
O Resultado é: 
 
8. Subtotais 
Supondo a TD seguinte: 
 
Microsoft Excel 10 
 
Queremos tirar os subtotais dos meses. Para isso fazemos: 
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á: 
 
9. Subtotais em campos interiores. 
Podemos acrescentar subtotais somente para alguns itens de um 
determinado campo. 
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 campos”. 
 
b) Seleccione Personalizados em Subtotais e, em seguida, faça clique sobre 
uma função de sumário na caixa situada à direita (Soma). 
 
Microsoft Excel 11 
 
 
c) Para remover subtotais faça clique sobre Nenhuns… 
 
d) Para utilizar uma função de sumário diferente ou para mostrar mais do 
que um tipo de subtotal, faça clique sobre as funções de sumário que 
deseja utilizar na caixa à direita de Personalizados. Consulte uma 
descrição das funções de sumário disponíveis. 
 
10. Formatação 
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; 
 
d) Na lista Categoria, faça clique sobre a categoria Moeda. 
e) Seleccione as opções desejadas e, em seguida, faça clique sobre OK. 
 
Microsoft Excel 12 
 
 
 
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 Vendedor; 
 
b) Escreva um novo nome: Representante; 
 
c) Prima Enter ou clique OK. 
 
 
Microsoft Excel 13 
 
Tipos de Cálculo 
Podemos efectuar vários tipos de “prospecções” sobre os dados que 
dispomos, criando sumários variados. 
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; 
 
Microsoft Excel 14 
 
ƒ % 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; 
ƒ Í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. 
 
Agrupar Itens de Campo 
Agrupar ou Desagrupar Itens Numéricos 
Utilizado para formar classes quando se coloca um campo numérico nas 
orientações linha ou coluna. Adicione as Unidades nas colunas: 
 … 
 
Microsoft Excel 15 
 
Para agrupar itens numéricos, execute os seguintes passos para uma TD 
representada na figura anterior, que, como verificamos, é de difícil leitura: 
a) Seleccionamos o item numérico que pretendemos agrupar clicando com o 
botão direito do rato sobre o respectivo (no caso o campo Unidades). No 
menu escolher as opções “Agrupar e mostrar detalhes ->Agrupar…”. 
 
b) No formulário Agrupamento, podemos indicar o valor inicial e final do 
grupo e qual o intervalo para definir cada classe. Indique os valores 
mostrados na figura. 
 
c) Clique em OK e verifique o resultado. 
 
 
 
 
 
 
 
 
 
Microsoft Excel 16 
 
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. 
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). 
 
 
Microsoft Excel 17 
 
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 18 
 
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. 
j) Agrupe os itens v1 e v2; Altere o tipo de cálculo dos dados para normal e 
desagrupe os itens v1 e v2. 
k) Inclua na tabela dinâmica as vendas em unidades e valor.