Material de Ensino,Tabelas Dinâmicas

Aprenda a criar uma Tabela Dinâmica (passo a passo)

Posted on by Carlos Roberto Roa
20
dez

A Tabela Dinâmica (em inglês, Pivot Table) é uma poderosa ferramenta de consolidação e análise de dados, sendo muitas vezes mais prático a sua utilização do que a elaboração de outras tabelas de apoio com formulas mais complexas.

Construção da Tabela Dinâmica

Guia de Etapas

1. Construção da base de dados
2. Importação dos dados para Tabela Dinâmica
3. Seleção dos dados que irão compor a Tabela Dinâmica
4. Configuração e formatação dos campos valores
5. Formatação do Designer da Tabela Dinâmica
6. Detalhes Finais

Etapa 1: Construção da base de dados (Tabela)

É interessante ressaltar que como a nossa base de dados é de pequeno porte iremos criar uma tabela em Excel, caso contrário teríamos que utilizar softwares específicos de Banco de Dados, como: Access ou SQL.

Crie uma tabela em Excel similar ao exemplo:

País de OrigemFornecedorClienteProdutoQtd Valor Unitário
(R$) 		Valor Total
(R$)
BrasilBR1Bicicletas MalucasBike MMX EX100$ 845$ 84.500
AlemanhaDE2Bicicletas MalucasBike MMX Plus55$ 1.100$ 60.500
Coréia da sulKR1Videogames IradosInfância Mania1000$ 990$ 990.000
BrasilBR1Bonecas PoderosasPepeta500$ 200$ 100.000
ÍndiaIN2Bolas no GolPlacar Digital25$ 5.000$ 125.000
Estados UnidosUSA1Bonecas PoderosasSupresinha800$ 100$ 80.000
Estados UnidosUSA2Bicicletas MalucasBike MMX Plus80$ 1.050$ 84.000
Estados UnidosUSA3Videogames IradosInfância Mania350$ 950$ 332.500
Estados UnidosUSA3Bolas no GolTrave Listrada50$ 320$ 16.000
Estados UnidosUSA3Videogames IradosInfância Mania250$ 1.000$ 250.000
ChinaCN1Bicicletas MalucasBike MMX EX320$ 800$ 256.000
ChinaCN4Bicicletas MalucasBike MMX Turbo93$ 1.200$ 111.600
ChinaCN1Bolas no GolBola de couro1456$ 25$ 36.400
ChinaCN1Bonecas PoderosasSupresinha567$ 75$ 42.525
ChinaCN5Bonecas PoderosasSupresinha456$ 75$ 34.200
ChinaCN2Bonecas PoderosasPepeta234$ 220$ 51.480
ChinaCN2Videogames IradosInfância Mania892$ 987$ 880.404
ChinaCN3Bicicletas MalucasBike MMX Turbo234$ 1.125$ 263.250
ChinaCN3Bicicletas MalucasBike MMX EX756$ 798$ 603.288
ChinaCN3Bolas no GolBola de couro133$ 33$ 4.389
PortugalPT1Quebra Cabeça EternoPaisagens Utópicas490$ 115$ 56.350
BrasilBR2Bolas no GolTrave Listrada33$ 345$ 11.385
AlemanhaDE1Videogames IradosFuturo Virtual2000$ 1.350$ 2.700.000
Estados UnidosUSA2Quebra Cabeça EternoPaisagem Utópica390$ 108$ 42.120
ChinaUSA1Quebra Cabeça RealPaisagem Real2000$ 167$ 334.000

Atenção! Todos os cabeçalhos (títulos ou rótulos das colunas) devem estar preenchidos, caso contrário não será gerado a tabela dinâmica e o Excel lhe enviará o seguinte erro:

erro_tab_dim
Figura 1 – Mensagem de erro devido a falta de coluna rotulada

Etapa 2: Importação dos dados para Tabela Dinâmica

Passo 1: Selecione a tabela inteira

selecao_tab_dim
Figura 2 – Seleção da tabela para posterior geração da Tabela Dinâmica

Passo 2: Vá ao Menu “Inserir” e  selecione “Tabela Dinâmica”

tabela_dim_inserir
Figura 3 – Esquema para criação da Tabela Dinâmica

Passo 3: Confira o intervalo que aparece na tela e clique OK.

tabela_dim_criar
Figura 4 – Intervalo referente a Tabela Dinâmica

Etapa 3: Seleção dos dados que irão compor a Tabela Dinâmica

Passo 1: Na “Lista de campos da tabela dinâmica”, selecione cada um dos campos com mouse e arraste com o botão esquerdo pressionado para as respectivas áreas: Filtro de Relatório, Rótulos de Linhas, Rótulos de Colunas e Valores.

    • Filtro de Relatório: São os campos que o relatório estará condicionado com a aplicação dos filtros.
    • Rótulos de Linhas: São os campos mais usuais, as informações a serem apresentados em forma de linha.
    • Rótulos de Colunas: Campo que geralmente é utilizado quando se quer consolidar dados em colunas para uma análise evolutiva ou comparativa, como por exemplo meses ou anos.
    • Valores: Campo em que são consolidados valores numéricos.
tabela_dim_rotulos
Figura 5 – Esquema da Lista de campos

Passo 2: Organize a posição dos campos conforme a necessidade de análise, no nosso caso, organizaremos em “Rótulo de Linha”, primeiro o Cliente, segundo o Produto e por último o Fornecedor, visto que a intenção é identificar quais produtos/fornecedores um Cliente trabalha.

tabela_dim_organiza
Figura 6 – Organização dos campos na área de dados

Observação:  Este passo poderia ser encurtada caso selecionássemos a ordem correta dos “Rótulos de Linha”.

Etapa 4: Configuração e formatação dos campos valores

Configure e formate o campo valor, “Valor Total (R$)” e “Qtd”.

    1. Selecione o campo valor (rótulo) 
    2. Clique com o botão direito do mouse e selecione “Configurações do Campo Valor…”
    3. Verifique no quadro “Resumir campo de valor por” se a operação esta correta (Soma, Contagem…) 
    4. Selecione “Formato do Número” e configure o formato desejado

a) Campo “Valor Total (R$)”

tab_dim_configuracao_valor
Figura 7 – Formatação do Campo “Valor Toral (R$)”

b) Campo “Qtd”

tab_dim_configuracao_qtd
Figura 8 – Formatação do Campo “Qtd”

Etapa 5: Formatação do Designer da Tabela Dinâmica

Passo 1: Formate o Layout

a) Layout do relatório

confire_dim_1
Figura 9 – Esquema para a formatação do Layout

b) Linhas em Branco

confire_dim_2
Figura 10 – Esquema para inserir linhas adicionais após cada item

Passo 2: Formate o estilo  em “Ferramentas da Tabela Dinâmica” em Design.

tabela_dim_estilo
Figura 11 – Esquema de alteração o design

Etapa 6: Detalhes finais

Renomeie os títulos dos campos valores para “Quantidade” e “Valor Total (R$)”.

tabela_dim_renomeia
Figura 12 – Alteração dos rótulos

 

Ative a atualização automática dos dados, ao entrar na planilha

tab_dim_atualiza
Figura 13 – Configuração da atualização automática após abrir a planilha

download

Bons estudos e espero que tenham gostado!

 

Carlos Roberto Roa

Graduado em Engenharia de Produção pelo Instituto Mauá de Tecnologia, e entusiasta de Big Data, atuo em Inteligência de Mercado. A união da paixão por exatas e planilhas, motivou o surgimento do Acadêmicos do Excel, ambiente que visa contribuir com soluções e ideias para, Educação, Gestão Empresarial, Entretenimento e Aprendizado em Excel do Básico ao Avançado.

Deixe uma resposta

%d blogueiros gostam disto: