127
Autor: Robert F. Martim Criado em: 09/04/2009 Publicado: www.juliobattisti.com.br Última edição: 2/7/2009 Contato: [email protected] Série “Como Fazer”: Excel 2007 Tabelas Dinâmicas por Robert Friedrick Martim

SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Embed Size (px)

Citation preview

Page 1: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

Série “Como Fazer”: Excel 2007

Tabelas Dinâmicas

por Robert Friedrick Martim

Page 2: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

Page 3: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

i

Desenvolvimento inteligente

Nota sobre direitos autorais

Este eBook é de autoria de Robert F Martim, sendo comercializado através do site

www.juliobattisti.com.br ou através do site de leilões Mercado Livre:

www.mercadolivre.com.br.

Ao adquirir este eBook você tem o direito de lê-lo na tela do seu computador e de imprimir

quantas cópias desejar, desde que sejam para uso pessoal. É vetada a distribuição deste eBook,

mediante cópia ou quaisquer outros meios de reprodução, para outras pessoas. Se você recebeu

este eBook através de e-mail ou via FTP de algum site da Internet, ou através de CD de Revista,

saiba que você está com uma cópia pirata, não autorizada. Se for este o seu caso entre, em

contato com o autor através do e-mail [email protected] ou comunique diretamente ao

nosso site através do e-mail [email protected].

Ao regularizar a sua cópia, você estará remunerando, mediante uma pequena quantia, o trabalho

do autor e incentivando que novos trabalhos sejam disponibilizados.

Visite periodicamente o site www.juliobattisti.com.br para ficar por dentro das novidades!

Page 4: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

ii

Desenvolvimento inteligente

Pré-requisitos

Para completar este curso, você precisa conhecer o ambiente de trabalho Excel 2003 e possuir

uma cópia do Excel 2007. Uma versão Trial pode ser baixada do site da Microsoft.

Neste módulo discutirei como utilizar os novos recursos da Tabela Dinâmica no Excel 2007.

Page 5: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

iii

Desenvolvimento inteligente

Objetivos deste eBook

Este módulo visa à introdução dos novos recursos da Tabela Dinâmica do Excel 2007.

O trabalho foi desenvolvido a partir da demanda dos usuários do site www.juliobattisti.com.br. O

material procura analisar questões pertinentes ao dia-a-dia de seu trabalho.

A linguagem utilizada é descontraída e com o mínimo de jargão possível. O objetivo é ter um

ebook com conteúdo relevante e de fácil compreensão.

Quaisquer dúvidas referentes a este módulo podem ser colocadas diretamente no fórum Excel no

endereço www.juliobattisti.com.br/forum/

Comentários e sugestões para melhora do material podem ser enviados diretamente para o autor

no endereço [email protected]

Page 6: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

iv

Desenvolvimento inteligente

ÍNDICE ANALÍTICO

Introdução .................................................................................................................................... 1

Bem-vindo a série “Como Fazer”. .......................................................................................... 1

Antes de continuar ................................................................................................................ 1

1. Algumas novidades do Excel 2007 .................................................................................... 2

1.1. Lista das novidades e seus benefícios.......................................................................... 4

1.1.1. Faixa de Opções .............................................................................................. 4

1.1.2. Mais linhas e colunas ....................................................................................... 4

1.1.3. Ordenação simultânea de campos ................................................................... 4

1.1.4. Nova formatação condicional ........................................................................... 5

1.1.5. Novas funções.................................................................................................. 5

1.1.6. Visualização Dinâmica ..................................................................................... 5

1.1.7. Layout da Página ............................................................................................. 5

1.1.8. Estilos de formatação ....................................................................................... 5

2. Compreendendo Tabelas Dinâmicas ................................................................................. 7

2.1. TDs, Tabelas e matriciais: como obter resultados iguais ............................................ 12

2.2. Atualizacao de dados ................................................................................................. 16

3. Tabelas dinâmicas no Excel 2007: o que há de novo? ................................................... 17

3.1. Acessando a ferramenta de tabela dinâmica .............................................................. 17

3.2. Ferramentas de Tabela Dinâmica: Tabset de Extensibilidade .................................... 18

3.3. Adicionando suas próprias ferramentas ao Tabset de Extensibilidade ....................... 18

3.4. Lista de campos da tabela dinâmica ........................................................................... 20

3.5. Acessando as opções da tabela dinâmica .................................................................. 21

4. Compreendendo os elementos de uma Tabela Dinâmica .............................................. 24

4.1. Elementos de uma Tabela Dinâmica .......................................................................... 24

4.2. Onde devo ir para inserir uma tabela dinâmica? ......................................................... 27

4.3. Onde foi parar o Assistente de Tabela Dinâmica no Excel 2007? ............................... 28

4.4. Opções da tabela dinâmica ........................................................................................ 31

4.4.1. Layout e Formato ........................................................................................... 31

4.4.2. Totais e filtros ................................................................................................. 34

4.4.3. Exibição ......................................................................................................... 35

4.4.4. Impressão ...................................................................................................... 37

4.4.5. Dados ............................................................................................................. 37

Page 7: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

v

Desenvolvimento inteligente

4.5. O que é uma tabela unidimensional de campo único? ................................................ 38

4.6. O que é uma tabela bidimensional de campo único? .................................................. 38

4.7. O que é uma tabela unidimensional e bidimensional de múltiplos campos? ............... 38

4.8. Adicionando campos à tabela dinâmica: método clássico e novo ............................... 39

4.9. Removendo campos da tabela dinâmica .................................................................... 41

5. Compreendendo as fontes de dados para Tabelas Dinâmicas ...................................... 42

5.1. Banco de dados ou lista do Microsoft Office Excel ..................................................... 42

5.2. Fonte de dados externos ............................................................................................ 44

No caso anterior, nós utilizamos a fonte externa para alimentar a TD diretamente. ............ 46

5.3. Vários intervalos de consolidação ............................................................................... 46

5.4. Outro relatório de tabela dinâmica ou de gráfico dinamico.......................................... 51

6. Formatação ........................................................................................................................ 53

6.1. Estilos de Tabela Dinâmica ........................................................................................ 53

6.2. Formatando campo..................................................................................................... 55

6.3. Layout do relatório de tabela dinâmica ....................................................................... 55

6.4. Preservando formatação ............................................................................................ 57

6.5. Modificando nome de campos .................................................................................... 58

7. Trabalhando com campos ................................................................................................ 59

7.1. Trabalhando com campos de linha e de coluna .......................................................... 59

7.2. Trabalhando com sumários e subtotalizações: subtotais simples e múltiplos ............. 62

7.2.1. Modificando o tipo de subtotal apresentado (mostrar como % do total, etc) ... 64

7.3. Utilizando data base para comparar valores (gastos de um trimestre base comparados com outro) .................................................................................................................. 68

7.4. Trabalhando com índices (comparação relativa) ........................................................ 72

7.5. Trabalhando com campos e itens calculados ............................................................. 74

7.5.1. O que é um campo ou item calculado? .......................................................... 74

7.5.2. Criando um campo e/ou item calculado .......................................................... 77

7.5.3. Editando/Excluindo campos/itens calculados ................................................. 81

7.5.4. Determinando ordem de cálculo ..................................................................... 82

7.5.5. Determinando ordem de apresentação .......................................................... 83

7.6. Determinando os “Top 10” (os “10 Primeiros”) ............................................................ 85

8. Salvando a TD como um documento HTM interativo ..................................................... 87

9. Tabulando dados de Pesquisas com TDs ....................................................................... 88

9.1. Tabulando e analisando dados de uma pesquisa de opinião: cenário 1 – tabulação global .......................................................................................................................... 88

Page 8: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

vi

Desenvolvimento inteligente

9.2. Tabulando e analisando dados de uma pesquisa de opinião: cenário 2 – tabulação por cidade ......................................................................................................................... 93

10. Tabelas dinâmicas e VBA ................................................................................................. 98

10.1. Gravando uma macro ................................................................................................. 98

10.2. Adicionando uma nova tabela dinâmica.................................................................... 100

10.2.1. Adicionando uma nova tabela dinâmica em uma nova pasta de trabalho ..... 101

10.3. Adicionando uma nova tabela dinâmica utilizando os objetos PivotTable e PivotCache 103

10.4. Adicionando campos a TD usando VBA: orientação de campos............................... 104

10.4.1. Removendo campos de uma TD .................................................................. 107

10.4.2. Adicionando campos via matriz (Array) ........................................................ 107

10.5. Copiando valores de uma TD e colando como valores fixos e desconectados ......... 108

10.6. Cálculo manual ou automático? ................................................................................ 110

11. Problemas em potencial ................................................................................................. 111

11.1. Campos nulos (vazios) e em brancos: qual a diferença e como determiná-la? ......... 111

11.2. Descontinuidade nos dados ...................................................................................... 114

11.3. A função INFODADOSTABELADINÂMICA .............................................................. 116

12. Sobre o autor ................................................................................................................... 118

Page 9: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

1

Desenvolvimento inteligente

SÉRIES: COMO FAZER – Excel 2007

Tabelas Dinâmicas por Robert Friedrick Martim

Introdução

Bem-vindo a série “Como Fazer”.

Nas séries que serão escritas olharemos aspectos distintos do Excel 2007 de acordo com a

demanda do site Júlio Battisti (http://www.juliobattisti.com.br). A intenção principal é fornecer ao

internauta uma ferramenta que concentre a atenção na solução de um problema específico.

Neste módulo veremos como utilizar as novas ferramentas de Tabela Dinâmica no Excel 2007 e

como aplicá-las aos mais variados problemas encontrados no nosso dia-a-dia.

Antes de continuar

Sugestões serão sempre bem-vindas e esperamos que o leitor participe pro ativamente no

desenvolvimento do material aqui apresentado.

Finalmente, todo um esforço foi feito para assegurar que o material aqui apresentado está livre de

qualquer erro, mas no improvável evento de encontrar algo errado, por favor, não deixe de me

comunicar, pois terei prazer em revisar o material imediatamente.

Page 10: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

2

Desenvolvimento inteligente

1. Algumas novidades do Excel 2007

Iniciaremos fazendo um reconhecimento da nossa área de trabalho.

O novo Office mudou bastante a sua cara e para aqueles que se acostumaram com os antigos

menus o novo visual pode ser um choque tremendo. Não obstante, uma vez que você aprenda a

nova interface, você começará a ver os benefícios que ela nos traz.

O primeiro benefício é que a maioria dos comandos agora fica agrupada no “RibbonX” (Faixa de

Opções) e não enterrados em algum local obscuro do Excel. Além disso, o RibbonX é sensível ao

contexto e irá adicionar novas guias dependendo do que você esteja fazendo.

Como o RibbonX ocupa bastante espaço, quanto maior for a resolução de seu monitor melhor

será o benefício da Faixa de Opções:

Figura ‎1-1 Faixa de Opções em baixa resolução agrupa ainda mais os itens de um grupo

A figura acima mostra a Faixa de Opções com uma resolução menor. Note que o grupo que

usaremos (grupo Estilo) é compresso e, portanto, não é possível saber o que há nele até que o

mesmo seja expandido. Em uma resolução maior nós temos uma área bem mais visível da Faixa

de Opções e o grupo que usaremos é exposto:

Figura ‎1-2 Faixa de Opções em maior resolução expande grupos

Assim sendo, quanto maior for sua resolução maior será o benefício da Faixa de Opções.

Page 11: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

3

Desenvolvimento inteligente

A ferramenta de formatação condicional pode ser rapidamente acessada na guia Início no grupo

Estilo conforme mostra a figura abaixo:

Figura ‎1-3 Acessando a ferramenta de formatação condicional

Note a pequena “seta para baixo” no “botão” de formatação condicional. Este pequeno botão nos

permite expandir as opções disponíveis no botão. Este “botão”, na verdade, funciona como os

antigos “menus popups” os quais expõem novos clicáveis os abrem novos clicáveis ou executam

algum tipo de comando:

Figura ‎1-4 Expandindo a opção de formatação condicional

Page 12: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

4

Desenvolvimento inteligente

Antes de continuar, porém, abro um curto subtópico para discutir algumas novas questões do

Excel 2007. Estas novas questões irão lhe ajudar a compreender melhor a nova versão.

1.1. Lista das novidades e seus benefícios

Vamos das uma rápida olhada nas novidades do Excel 2007 e seus benefícios (ou não benefícios)

para os usuários novos e antigos. Para facilitar a identificação, quebro a lista para que a mesma

possa ser encontrada a partir do índice analítico.

1.1.1. Faixa de Opções

Faixa de Opções (ou RibbonX) é uma das primeiras novidades que qualquer usuário notará. A

grande vantagem da Faixa de Opções é que ele agrupa tarefas comuns e expõe os controles que

o usuário precisa para resolver um problema qualquer.

A Faixa de Opções é sensível ao contexto e a ela são adicionadas novas guias e controles

dependendo do trabalho que você esteja fazendo.

A grande desvantagem da Faixa de Opções é o espaço que ela ocupa. Portanto, quanto maior a

resolução de seu monitor mais você se beneficiará dela.

1.1.2. Mais linhas e colunas

O novo Excel possui 1.048.576 linhas por 16.384 colunas, dando um total de mais de 17 bilhões

de células para o leitor “brincar” à vontade.

Para os que achavam 65.536 linhas por 256 colunas pouco, o choro pode terminar agora. O leitor

pode rapidamente checar o número de linhas e colunas utilizando o código abaixo:

Sub linhasColunas()

Cells(1, 1) = ActiveSheet.Rows.Count

Cells(1, 2) = ActiveSheet.Columns.Count

End Sub

1.1.3. Ordenação simultânea de campos

Uma reclamação antiga do Excel era a possibilidade de apenas ordenarmos três campos, no

máximo. É bem verdade que podemos ordenar mais de três campos utilizando um método nada

ortodoxo e lento, contudo, o novo Excel nos permite ordenar até 64 campos simultaneamente.

Page 13: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

5

Desenvolvimento inteligente

Provavelmente, você se perderá na ordenação lá pelo quinto campo e terá que confiar no

resultado final, não obstante, a nova possibilidade é extremamente bem-vinda.

1.1.4. Nova formatação condicional

A nova formatação condicional, o assunto deste curso, está “show de bola”. Para aqueles que

amam formatação condicional e as usam para identificar tudo que é tipo de coisa no Excel, a nova

interface da FC está nota 1000.

No princípio pode parecer um pouco complexa, mas uma vez que você domine os novos

conceitos desta ferramenta você verá como ela é bastante ilimitada.

1.1.5. Novas funções

O Excel 2007 agora possui novas funções que resolvem muitos dos problemas que antes tiravam

o sono de muitos usuários como é o caso de soma condicional.

1.1.6. Visualização Dinâmica

Permite visualizar o resultado de uma formatação antes que ela seja aplicada. Recurso muito útil

na hora de darmos o toque final ao nosso trabalho.

1.1.7. Layout da Página

Para os usuários de Word que amam o layout de impressão, agora, ficou muito mais fácil formatar

a sua planilha com o Layout da Página.

1.1.8. Estilos de formatação

Com os novos estilos de formatação ficou bastante simples dar aquele toque profissional ao seu

trabalho no Excel. Basta selecionar um dos vários estilos da galeria e aplicá-lo. Sem contar que

com a Visualização Dinâmica você verá o resultado antes de aplicar o estilo.

Estas são apenas algumas das mais variadas novidades do novo Excel. Infelizmente, o espaço

aqui é curto para discutir cada uma delas, mas acredito que os pontos acima dêem ao leitor uma

visão geral do que esperar da nova versão.

Page 14: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

6

Desenvolvimento inteligente

Feita as introduções vamos ao que interessa: formatação condicional e validação de dados.

Page 15: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

7

Desenvolvimento inteligente

2. Compreendendo Tabelas Dinâmicas

A principal razão para usarmos Tabelas Dinâmicas é que elas nos permitem analisar grandes

quantidades de dados de forma significativa. Tabelas Dinâmicas nos permitem trabalham com

uma massa de dados que seria indecifrável ou insignificativa em sua ausência.

Nós podemos, por exemplo, organizar, subtotalizar, agrupar, etc, de forma simples e rápida

através de Tabelas Dinâmicas. Esta organização, subtotalização, agrupamento, etc, não se

restringe a dados contidos em uma planilha Excel. Uma tabela dinâmica pode utilizar dados de

banco de dados externo ou consultas pré-definidas. A importância desta característica se torna

evidente quando o total de registros excede o limite de uma planilha Excel.

Com o Excel 2007, o total de registros aumentou, mas mesmo assim devemos lembrar que o

Excel não é um banco de dados e não deve ser utilizado para esta finalidade.

O nome Tabela Dinâmica não faz jus ao nome original em inglês: Pivot Table (ou “Tabela

Pivotante”). A razão para este nome em inglês é tão singela quanto o nome: quando temos um

objeto complexo em nossas mãos, como um Cubo Mágico (Cubo de Rubik) nós o giramos de

várias formas analisando-o por diversos ângulos. Com uma tabela dinâmica, nós fazemos o

mesmo; porém, com uma massa de dados ao invés de um cubo.

Suponha, por exemplo, que você é responsável pelo almoxarifado e deseja criar um banco de

dados para controlar estoque. Você deveria utilizar o Access e posteriormente analisar os dados

no Excel, contudo, como o Excel é o mais popular entre os aplicativos do Office, ele acaba virando

um PPO (Pau Para toda Obra).

Se este é o seu caso ou você não tem acesso ao Access, então é importante que você conheça

bem as ferramentas que o Excel possui para que você possa tomar controle dos pontos fortes e

fracos do Excel. Principalmente em dominar os pontos fortes para tirar o melhor proveito do

aplicativo e conhecer os pontos fracos para que eles não causem danos ao seu trabalho.

Certamente que gravar os dados não resolve outros problemas ou responde outras perguntas

importantes sobre o seu estoque. Se você conhece os dados, você será capaz de construir

análises mais complexas e detalhadas sobre o mesmo. Você poderá fazer um controle maior

sobre o que sai e entra, efetivamente reduzindo custo e mantendo uma boa rotatividade de

produtos perecíveis. Você poderá criar uma análise comparativa por setor, produto consumido,

Page 16: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

8

Desenvolvimento inteligente

quem solicitou a baixa, com que freqüência alguém solicita uma baixa, cruzar dados entre setores,

solicitantes, produtos e época de baixas e compras, e uma pletora de outras perguntas complexas

que podem ser facilmente respondidas através de uma tabela dinâmica.

E se você acha que o exemplo acima é complexo para um simples almoxarifado, imagine uma

distribuidora com diversos escritórios espalhados pelo país? Como conciliar e analisar a

distribuição, as vendas por região e por vendedor? Como saber o percentual de vendas de cada

região em relação ao total da empresa como um todo? Ou como saber dentro disso tudo quem foi

o melhor vendedor e qual foi o produto mais vendido? Como cruzar todos estes dados por todas

as regiões do país e analisar cada detalhe?

Aqui, se você alguma vez criou uma consulta circular no Excel (uma consulta que retorna dados

dentro da própria pasta que funciona como a fonte de dados), você chegará à conclusão que ela

será incapaz de responder tantas perguntas simultaneamente. Você precisaria de diversas

consultas que analisassem cada situação e depois ficar como louco cruzando tais consultas para

chegar ao nível de detalhe que estou falando. Sem contar a redundância dos dados, pois você

estaria simplesmente filtrando dados dentro da planilha que já contém os dados! Já imaginou o

caos?

Se seu chefe pedisse uma média ponderada no cenário da distribuidora acima, você

provavelmente iria ficar careca tentando resolver o problema num mar de números. Com tabelas

dinâmicas talvez você até arranque uma meia dúzia de fios de cabelos, mas certamente não ficará

careca por causa disso.

E como funciona uma tabela dinâmica1?

1 Na maioria dos casos abreviarei Tabela Dinâmica para “TD”.

Page 17: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

9

Desenvolvimento inteligente

A figura abaixo mostra uma tabela simples com alguns dados:

Figura ‎2-1 Dados de entrada para uma tabela dinâmica

Os dados acima podem ser sumarizados em uma tabela dinâmica da seguinte forma:

Figura ‎2-2 Soma da Venda Total para cada produto

Com a pequena tabela dinâmica acima podemos facilmente ver os totais para as vendas de cada

produto sem a necessidade de criarmos fórmulas mirabolantes para somar por produto. Contudo,

não temos os detalhes de cada venda. Aqui, podemos fazer algo similar ao que é geralmente feito

em um relatório.

Page 18: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

10

Desenvolvimento inteligente

Se o nome do produto é o cabeçalho e sob tal cabeçalho vem listado cada total de cada venda

efetuada temos o seguinte cenário:

Figura ‎2-3 Dados não agrupados em uma TD

Aqui, temos no cabeçalho cada produto com a lista das vendas e o total geral de vendas por

produtos. O resultado é basicamente o mesmo que o anterior, pois estamos interessados no total.

Contudo, aqui, ainda temos os detalhes das vendas.

Para situações mais complexas, você precisará criar filtro ou consulta que reúna os dados críticos

para análise e utilizar a tabela resultante para criar a sua tabela dinâmica no Excel.

A tradução do inglês para o português talvez não faça jus ao original em inglês PivotTable.

Embora a tradução para Tabela Pivotante possa ser “feia” acredito que ela faça mais jus ao real

significado do que uma Tabela Dinâmica realmente seja.

Pivotante refere-se a algo que gira sobre um ponto fixo e é exatamente isso que os dados fazem

dentro de uma tabela dinâmica. Os dados giram sobre o eixo da tabela e você pode girá-los à

vontade sobre tal eixo.

Page 19: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

11

Desenvolvimento inteligente

Esta aparente simplicidade nos fornece resultados extremamente complexos. Além disso, se os

dados em um formato não resultam no sumário que você deseja você pode simplesmente arrastar

o campo para outro local dentro da tabela para rearranjar os dados.

Em nossa tabela de dados também possuímos datas. Datas em tabelas dinâmicas têm papel

crítico, pois com as datas podemos agrupar dados para análise por períodos. Por exemplo,

poderíamos comparar as vendas mês a mês. Ou ano a ano. O tipo de agrupamento pode variar

com a necessidade.

Isso pode ser facilmente feito simplesmente arrastando o campo para a tabela:

Figura ‎2-4 Agrupamento por mês

Com o agrupamento por mês podemos rapidamente identificar que o produto A tem uma

constância maior nas vendas. Que em abril o único produto vendido foi o produto A e novamente

em julho.

Estes tipos de respostas não podem ser obtidos diretamente do conjunto de dados, pois o

resultado é unidirecional2.

E se você não gosta da forma como os resultados são apresentados, pela característica pivotante

da tabela dinâmica, você pode simplesmente mover um dos campos para outra área qualquer ou

removê-lo da tabela:

2 Embora seja possível criar uma fórmula matricial ou tabela para representar tal cenário.

Page 20: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

12

Desenvolvimento inteligente

Figura ‎2-5 Agrupamento de todos os produtos

Em questão de segundos, você cria outra apresentação dos dados sem esforço algum. Agora,

junte a força de filtros com consultas bem organizadas com tabelas dinâmicas, e você tem uma

ferramenta de análise de dados extremamente poderosa.

Ao longo deste curso, você notará que não existe uma forma “correta” de se criar uma tabela

dinâmica. Tabelas dinâmicas são usadas para criar sumários que nos ajudam a interpretar os

dados. Como a tabela final é feita dependerá da necessidade individual, do tipo de análise sendo

feita, e principalmente de sua compreensão dos dados. Na verdade, eu diria que o mais

importante de tudo é conhecer os dados. Sem conhecê-los o seu trabalho será muito mais

complexo.

Esta é apenas uma introdução ao que é possível fazer com tabelas dinâmicas. A seguir apresento

os componentes de uma tabela dinâmica e o que cada um faz antes de entrarmos na real criação

das mesmas.

2.1. TDs, Tabelas e matriciais: como obter resultados iguais

Mencionei antes que é possível simular um cenário de uma tabela dinâmica utilizando uma

fórmula matricial3 ou uma tabela. Apenas para matar a curiosidade, vejamos como isso é feito. O

conceito é relativamente simples, embora o procedimento possa ser extramente complexo

dependendo do tipo de dados e quantidade de dados que você esteja lidando.

Lembre-se que estou apenas dando um exemplo. Não utilize este método para efetuar cálculos

complexos, pois além de trabalhoso consome muito mais de processamento do que uma TD.

3 Caso o leitor não tenha conhecimento sobre matriciais, veja o curso no link:

http://www.juliobattisti.com.br/cursos/excelmatric/default.asp

Page 21: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

13

Desenvolvimento inteligente

A primeira coisa que precisamos fazer é definir nosso problema:

Figura ‎2-6 Configurando o cálculo em tabela

Na célula C25 nós inserimos a fórmula do cálculo que desejamos efetuar (neste caso a soma por

produto). Aqui, utilizo um formato geral similar a TD para facilitar a visualização.

Disponha os nomes dos produtos em uma coluna única (várias linhas por uma coluna) e defina o

critério (neste caso defini como sendo A na célula B23). Utilize o critério na fórmula para a soma

baseado no critério único.

Agora, siga os passos abaixo para criar a tabela:

Selecione a área B25:C29;

Vá a guia Dados grupo Ferramentas de Dados Teste de Hipóteses Tabela de

Dados;

Na nova janela que abrir, defina a célula de entrada da coluna como sendo B23;

Clique OK.

Figura ‎2-7 Argumentos da tabela de dados

A figura acima mostra a caixa de entrada para a tabela. Após clicarmos em OK, a tabela será

preenchida com as somas por produto:

Page 22: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

14

Desenvolvimento inteligente

Figura ‎2-8 Resultado do cálculo de uma Tabela

A tabela utiliza dois argumentos: linha e coluna. Neste caso, temos apenas coluna como

argumento da tabela. Os resultados são propagados para as demais células e obtemos o que

procuramos. Podemos também adicionar um somatório sob a tabela para criar o total geral:

Figura ‎2-9 Adicionando total geral à tabela

Como podemos ver, obtemos exatamente o mesmo resultado que obteríamos em uma tabela

dinâmica.

Outra forma de efetuar os mesmos cálculos seria utilizando uma função de banco de dados, uma

fórmula matricial ou SOMASE. Por exemplo, abaixo utilizo a função SOMASE:

Page 23: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

15

Desenvolvimento inteligente

Figura ‎2-10 Utilizando SOMASE

Como as áreas são nomeadas, basta seleciona o nome do produto e as áreas de dados e arrastar

a fórmula para baixo.

Para a fórmula matricial complica um pouco, pois o leitor precisa compreender de multiplicação de

matrizes. O efetuar tal cálculo faríamos:

Comparar nome do produto com o critério (A, B, C e D) transposto;

O resultado acima geraria uma matriz n x 4 (n linhas por 4 colunas, pois temos 4 produtos

distintos);

Transpomos tal resultado e o multiplicamos por 1 para obtermos uma matriz de zeros e

uns com uma dimensão 4xn (4 linhas e n colunas);

Multiplicaríamos esta matriz de zeros e uns pela matriz contendo os valores totais para

obtermos o que desejamos:

Figura ‎2-11 Utilizando matrizes em fórmula matricial

Obtemos mais uma vez o mesmo resultado, mas com um nível de complexidade muito maior.

Sem contar que o cenário aqui é simples. Imagine um cenário mais complexo com colunas,

múltiplas linhas, cabeçalhos, etc?

Page 24: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

16

Desenvolvimento inteligente

Seria o completo caos tentar resolver tais questões. Sem contar com a imprevisibilidade. E se o

seu chefe resolve mudar tudo de posição?

Portanto, embora seja possível não é nem um pouco recomendável fazer algo tão complexo

utilizando métodos manuais.

O que vejo às vezes é a argumentação de que uma TD não retorna no formato desejado. Se não

retornar, não tem problema. Analise com a TD, depois copie e cole no formato desejado e mesmo

assim ainda será mais rápido, prático e consumirá menos recursos.

Não se apegue a detalhes que pouco ajudam na solução do problema. Ataque o problema de

frente e resolva-o. Depois, preocupe-se com questões de formatação e apresentação dos

resultados.

2.2. Atualizacao de dados

As informações contidas em uma tabela dinâmica não são atualizadas automaticamente, isto é,

caso ocorra uma mudança na fonte de dados esta mudança não será refletida em sua TD. Este

fato independe da localização dos dados, ou seja, os dados podem estar na pasta de trabalho ou

um servidor SQL remoto… dá no mesmo. Além disso, pressionar F9 (recalcular) não recalcula ou

atualiza a sua TD.

Para atualizar os dados:

1. Clique direito sobre a TD e selecione “Atualizar”, ou;

2. Clique no botão “Atualizar” sob a guia “Opções”. Note que a TD precisa estar ativa para

que esta guia esteja visível, ou;

3. Determine um intervalo para atualização nos casos de dados externos. Mais adiante no

curso veremos como acessar esta opção.

Page 25: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

17

Desenvolvimento inteligente

3. Tabelas dinâmicas no Excel 2007: o que há de novo?

Neste tópico, discutirei as novidades da Tabela Dinâmica no Excel 2007. A principal característica

que o leitor deve manter em mente diz respeito à compatibilidade. Outra questão diz respeito ao

acesso da ferramenta.

3.1. Acessando a ferramenta de tabela dinâmica

No Excel 2003 o acesso era feito pelo menu “Dados”. No Excel 2007 o acesso é feito pela guia

Inserir conforme mostra a figura abaixo:

Figura ‎3-1 Acessando a ferramenta de Tabela Dinâmica

Como o acesso é feito por uma guia diferente da guia de formatação, você pode adicionar a

ferramenta à Barra de Ferramentas de Acesso Rápido (BAR). Para tanto, clique com o botão

direito do mouse sobre o botão “Tabela Dinâmica”. A opção de adição à BAR será exibida:

Figura ‎3-2 Adicionando o botão “Tabela Dinâmica” à BAR

Uma vez que o botão tenha sido adicionado à BAR, o mesmo poderá ser acessado rapidamente

sem a necessidade de troca de guias:

Page 26: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

18

Desenvolvimento inteligente

Figura ‎3-3 Botão “Tabela Dinâmica” adicionado à BAR

NOTA:

A forma como você clica no botão determina qual elemento é inserido. Note que o botão “Tabela

Dinâmica” é, na verdade, um botão simples e um botão do tipo “Split”. No caso da figura acima o

botao adicionado ao BAR é o “spliButton” (botão split)

3.2. Ferramentas de Tabela Dinâmica: Tabset de Extensibilidade

No Excel 2003, nós tínhamos uma barra de ferramentas que era sensível ao contexto do objeto.

No Excel 2007, nós temos algo similar denominado “tabset de extensibilidade”:

Figura ‎3-4 Tabset de extensibilidade

A figura mostra apenas parte de toda a faixa de opções para o tabset Ferramentas de Tabela

Dinâmica. O leitor deve estudar atentamente a posição de cada elemento dentro da guia.

3.3. Adicionando suas próprias ferramentas ao Tabset de Extensibilidade

Neste tópico cobrirei rapidamente a customização do TabSet de extensibilidade da Tabela

Dinâmica. Caso o leitor tenha interesse de aprender mais sobre a customização da Faixa de

Page 27: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

19

Desenvolvimento inteligente

Opções, veja o curso sobre o assunto no link:

http://www.juliobattisti.com.br/cursos/ex2007ribbon/default.asp.

A figura abaixo mostra a customização aplicada:

Figura ‎3-5 Customizando o Tabset de Extensibilidade da Tabela Dinâmica

Como não existe um Object Model (OM) em VBA para lidar com a criação de tais elementos da

Faixa de Opções, nós utilizamos XML para tanto. O leitor precisará baixar o programa CustomUI

Editor (gratuito na Internet) para poder acessar o arquivo Excel 2007 e adionar o seguinte código

XML para customizar a Guia conforme exemplo acima:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

<ribbon>

<contextualTabs>

<tabSet

idMso="TabSetPivotTableTools">

<tab

id="rxtab"

label="Minhas Ferramentas de TD">

</tab>

</tabSet>

</contextualTabs>

</ribbon>

</customUI>

NOTA:

É importante lembrar que manter um arquivo Excel aberto no Excel 2007 ou aberto no CustomUI

Editor fará com que um dos dois perca as últimas alterações.

Page 28: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

20

Desenvolvimento inteligente

3.4. Lista de campos da tabela dinâmica

No Excel 2007 a lista de campos da tabela dinâmica está mais inteligente e fácil de usar. Além do

formato padrão de visualização, podemos escolher entre quatro outros modelos diferentes de

visualização conforme é mostrado na figura abaixo:

Figura ‎3-6 Botão Lista de campos da tabela dinâmica

Os elementos são descritos abaixo:

Nome do elemento Descrição da função do elemento

Escolha campo Exibe todos os campos disponíveis para uso na tabela dinâmica.

Filtro de Relatório Área onde usamos o campo da seção de página da tabela dinâmica para filtragem

da tabela.

Rótulos de Coluna Área onde usamos o campo que pivotará os dados por coluna.

Rótulo de Linha Área onde usamos o campo que pivotará os dados por linha.

Valores Área onde usamos o campo de origem dos dados

Page 29: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

21

Desenvolvimento inteligente

Adiar atualização

do Layout

Adia a atualização do layout até que todos os campos tenham sido inseridos e o

botão “Atualizar” tenha sido clicado. Note que desmarcar esta opção desfaz o

layout criado com a opção marcada.

3.5. Acessando as opções da tabela dinâmica

A caixa de diálogo de opções da tabela dinâmica mudou bastante, contendo agora um formato

que agrupa funções similares.

Figura ‎3-7 Acessando as opções da tabela dinâmica

Abaixo descrevo rapidamente os métodos de acesso à caixa de diálogo.

Page 30: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

22

Desenvolvimento inteligente

Método 1

1. Clique em qualquer local de sua tabela dinâmica

2. Quando a guia de extensibilidade for ativada, seleciona a guia “Opções”

3. No primeiro grupo (canto esquerdo) chamado “Opções” clique sobre o botão de mesmo

nome

Método 2

1. Siga os mesmo passos do método 1, mas clique sobre a pequena seta do botão “Opções”

(não clique sobre a palavra). Um menu será expandido conforme Figura 3-8

Figura ‎3-8 Acessando as opções da tabela dinâmica

Método 3

1. Clique com o botão direito sobre a tabela dinâmica;

2. Selecione a opção “Opções da tabela dinâmica…”

Page 31: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

23

Desenvolvimento inteligente

Figura ‎3-9 Acessando as opções da tabela dinâmica

Page 32: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

24

Desenvolvimento inteligente

4. Compreendendo os elementos de uma Tabela Dinâmica

Este curto tópico é para introduzi-lo aos elementos que compõem uma tabela dinâmica. Você

precisará estudar estes elementos para compreender melhor como eles funcionam.

As explicações são basicamente o que você pode encontrar no próprio Ajuda do Excel e a melhor

forma de realmente aprendê-los é testar diferentes combinações com os dados que você possui.

A dica que posso dar é que você deve iniciar com um pequeno banco de dados ou planilha com

algumas informações e alguns campos ao invés de um banco de dados ou planilha enorme e com

diversos campos.

Isso facilitará a sua compreensão e visualização do que ocorre quando você cruza os dados. Com

um banco de dados ou planilha muito grande e muitos campos, certamente causará confusão

para o mais inexperiente, pois a tendência é entrar em pânico quando não se consegue fazer o

sumário desejado. Isso muitos vezes tem a ver com o próprio formato dos dados (como eles estão

gravados no banco de dados ou planilha e o tipo de relacionamento existente entre os dados) e

requer uma abordagem diferente para se chegar ao resultado esperado.

4.1. Elementos de uma Tabela Dinâmica

A figura a seguir mostra os componentes da tabela dinâmica apresentada anteriormente:

Figura ‎4-1 Componentes da tabela dinâmica

Para evitar um emaranhado de setas na figura eu omiti os itens os quais posso me referir sem a

necessidade de setas dentro da figura.

Page 33: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

25

Desenvolvimento inteligente

Abaixo você encontra o significado de cada um destes elementos (nem todos os elementos

encontram-se na Figura 4-1):

Campos

Campos referem-se às categorias de dados de uma tabela dinâmica. No exemplo acima,

nomeProduto e Meses são os campos da tabela dinâmica. Meses é o campo de linha

(rótulo de linha) ao passo que nomeProduto é o campo de página (Filtro Relatório).

Campos podem ser os mesmos contidos na tabela fonte ou podem ser campos criados

pelo usuário a partir dos campos já existentes.

Item

Item refere-se a cada elemento dentro de um campo qualquer. Por exemplo, no caso

acima, as letras de A a D poderiam ser os itens do campo de coluna ao passo que os

meses de janeiro a julho poderiam ser os itens do campo de linha. Itens podem ser

expandidos e colapsados quando existem detalhes disponíveis para os mesmos (discutirei

isso mais adiante no curso). Observe que o ponto de interseção entre um item de linha e

um item de coluna é o sumário de dados para o cruzamento entre os campos de linha e

coluna para este item.

Campo de linha (Rótulos de Linha)

Um campo de linha refere-se ao campo na área de linha da tabela dinâmica. É fácil

confundir um campo de linha com um item de linha. Campos de linha referem-se aos

campos utilizados como cabeçalhos para os itens de linha (os campos que possuem a seta

de campo suspenso).

Campo de coluna (Rótulos de Coluna)

Um campo de coluna refere-se ao campo que mostra os itens na vertical. Novamente, é

fácil chamar um item de coluna de campo, por causa da maneira como uma planilha é

apresentada no Excel. Como sempre vemos os campos de uma planilha no topo, a

tendência é olhar uma tabela dinâmica e chamar um item de coluna de campo de coluna.

Campos de coluna possuem uma seta de campo suspenso.

Campo de filtro (Filtro Relatório)

Campo de filtro refere-se ao campo na parte superior da tabela dinâmica. Este campo é

utilizado para filtrar os dados apresentados nos campos de linha e coluna.

Campo de detalhes (Valores)

Refere-se ao campo que recebe os dados a serem sumarizados. O campo não precisa

Page 34: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

26

Desenvolvimento inteligente

necessariamente receber um valor numérico. Este campo irá agrupar os dados de diversas

formas. Você pode somar, contar, retirar média e desvios padrões, etc deste campo. Ou

ainda criar os seus próprios campos.

Seta suspensa

Seta suspensa é a pequena seta ao lado de um campo de linha, coluna e/ou filtro

utilizado para expandir os itens contidos sob tal campo. A seta suspensa é utilizada para

filtrar os itens que devem ser sumarizados no campo selecionado4:

Figure ‎4-1 Expandindo itens utilizando a seta suspensa

Ao expandir RL (Rótulos de Linha, o qual contém a agrupamento dos meses), você pode

selecionar os meses que devem aparecer em sua tabela dinâmica.

4 Caso o layout do relatório esteja no Formato Compacto (Design Layout Layout do Relatório Mostrar em Formato Compacto)

voce precisa clicar com o botao direito do mouse sobre a seta suspensa para visualizar os campos filtráveis.

Page 35: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

27

Desenvolvimento inteligente

4.2. Onde devo ir para inserir uma tabela dinâmica?

Para inserir uma tabela dinâmica é bastante simples (e já vimos isso anteriormente, mas

entraremos em outros detalhes agora). Mesmo sem selecionar os dados o Excel ainda é capaz de

determinar a área contendo os dados desde que a mesma esteja em um formato apropriado, isto

é, contenha cabeçalhos (nomes de campos), não possua registros nulos e seja contínua. Caso o

seu conjunto de dados possua registros nulos, selecione a área que contém os dados e siga as

instruções abaixo para iniciar o Assistente de Tabela Dinâmica:

Vá até a guia Inserir grupo Tabelas botão split Tabela Dinâmica

Tipo:

Tabela Dinâmica: Cria uma tabela dinâmica;

Gráfico Dinâmico: Cria um gráfico dinâmico.

Uma nova janela será aberta. Nesta janela, selecione uma das opções para a fonte de

dados:

Fonte de dados:

Tabela/Intervalo: Especifica uma área de uma planilha contida em uma pasta de

trabalho Excel como sendo a fonte de dados;

Fontes de dados externos: Especifica uma fonte externa de dados;

Figura ‎4-2 Primeiro passo para criar a Tabela Dinâmica

Page 36: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

28

Desenvolvimento inteligente

Determine se a tabela dinâmica deve ser inserida em uma nova planilha ou na planilha ativa. Caso

escolha a planilha ativa assegure-se que não há informações no caminho da tabela. Embora um

aviso seja emitido é possível substituir dados por erro. Geralmente, tenho preferência por uma

planilha nova.

4.3. Onde foi parar o Assistente de Tabela Dinâmica no Excel 2007?

Para todos aqueles que já usaram TDs no Excel 97-2003 a figura do Assistente é certamente

bem-vinda por várias razões. Por exemplo,você pode determinar a consolidação de várias áreas

de dados, pode criar uma TD ou GD, etc. Porém, no Excel 2007 quando nós inciamos o processo

não há mais tal Assistente. Não obstante, ele continua presente no Excel, embora escondido dos

olhos de todos.

Para acessar o assistente, siga os passos:

1. Clique em alguma parte da tabela de dados;

2. Digite em seqüência as teclas Alt d a. Note que esta não é a mesma seqüência de

aceleração no Excel 97-2003.

Figura ‎4-3 Primeiro passo para criar a Tabela Dinâmica usando o Assistente

Page 37: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

29

Desenvolvimento inteligente

Nesta primeira etapa, selecione uma das opções para a fonte de dados e para tipo (Tabela ou

Gráfico Dinâmico) antes de continuar:

Fonte de dados:

Banco de dados ou lista do Microsoft Office Excel: Especifica uma área de uma

planilha contida em uma pasta de trabalho Excel como sendo a fonte de dados;

Fontes de dados externos: Especifica uma fonte de dados externa;

Vários intervalos de consolidação: Especifica áreas múltiplas de consolidação;

Outro relatório de tabela dinâmica ou de gráfico dinâmico: Determina se a fonte

provém de uma Tabela ou Gráfico Dinâmico já existente no documento em questão.

Este método evita redundância de consultas e minimiza o uso de memória. Somente

será possível utilizar tal opção se existir uma Tabela ou Gráfico Dinâmico disponível

no documento (se efetivamente existir um PivotCache).

Tipo de relatório:

Tabela Dinâmica: Cria uma tabela dinâmica;

Relatório de Gráfico Dinâmico: Cria um gráfico dinâmico.

3. Clique em “Avançar” para continuar ou “Concluir” para terminar. Sugiro “Avançar” para se

beneficiar do Assistente, principalmente se o leitor for iniciante.

Assumindo que o leitor clicou em “Avançar”, o próximo passo requer a seleção da área que

contém os dados na planilha. Caso a célula selecionada esteja dentro do intervalo de dados e o

intervalo esteja no formato correto o Excel é capaz de determinar sozinho o intervalo de dados:

Figura ‎4-4 Segundo passo do Assistente de TD

Page 38: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

30

Desenvolvimento inteligente

Clique em avançar para determinar o local de saída da TD. Caso já exista alguma tabela dinâmica

na pasta de trabalho o Excel avisará que ela já existe e se você deseja utilizá-la para construir

uma nova tabela. Caso o leitor clique em “Não”, o Excel para avança para o último passo:

Figura ‎4-5 Último passo do Assistente de TD

Determine se a tabela dinâmica deve ser inserida em uma nova planilha ou na planilha ativa. Caso

escolha a planilha ativa assegure-se que não há informações no caminho da tabela. Embora um

aviso seja emitido é possível substituirmos dados por engano. Geralmente, tenho preferência por

uma planilha nova.

Este é o último passo do Assistente. O Assistente de layout deve ser acessado a partir das

opções da TD. Este assunto é coberto adiante.

Page 39: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

31

Desenvolvimento inteligente

4.4. Opções da tabela dinâmica

Vamos investir agora um tempo na análise das opções de uma tabela dinâmica.

Figura ‎4-6 Acessando as opções da tabela dinâmica

Como podemos ver acima, as opções são separadas em cinco grupos: 1) Formato e Layout; 2)

Totais e Filtros; 3) Exibicao; 4) Impressão e 5) Dados.

Deste modo, quebrarei cada um destes grupos em tópicos para que possamos analisá-los

separadamente e maximizar a compreensão de cada item dentro de cada grupo.

Iniciarei pelas opções de formatação.

4.4.1. Layout e Formato

A nossa primeira parada diz respeito às opções de layout e formatação. Todas as opções são

tratadas separadamente:

Page 40: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

32

Desenvolvimento inteligente

Mesclar rótulos

Mesclar rótulos nada mais é do que um mesclado de células. Observe o a figura abaixo:

Figura ‎4-7 Mesclando células

Note que as células selecionadas são mescladas quando esta opção é ativada. A vantagem de ter

as células mescladas é que ao selecionar o espaço já selecionamos o valor ao invés de uma

célula. Na formatação padrão, nós devemos especificamente selecionar a célula que contém o

valor ao invés da área que contém o valor.

Abaixo e acima

Modifica a disposição dos campos dentro da tabela. Observe as duas figuras a seguir

Figura ‎4-8 Disposição “Abaixo e Acima”

A figura acima mostra a disposição “Abaixo e Acima” ao passo que a figura abaixo mostra a

disposição para o layout “Acima e Abaixo”:

Page 41: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

33

Desenvolvimento inteligente

Figura ‎4-9 Disposição “Acima e Abaixo”

Para valores de erro, mostrar

Utilize esta opção para mostrar um erro por você determinado ao invés dos erros internos como

#NÚM!, #VALOR!, etc;

Para células vazias, mostrar

Quando não há informação para uma linha/coluna o Excel deixará em branco o ponto onde um

valor “seria esperado”. A figura na próxima página mostra isso ocorrendo:

Figura ‎4-10 Células vazias

Utilize esta opção para determinar um valor para as células em branco. Apenas lembre-se de

manter consistência, isto é, se você está somando utilizando o valor zero. Note também que ao

inserir o valor “0” a TD pode ser interpretada como sendo o valor “zero” ao invés de “não há

dados”.

Page 42: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

34

Desenvolvimento inteligente

Figura ‎4-11 Células vazias preenchidas com “zeros”

4.4.2. Totais e filtros

Sob esta tabulação, o leitor poderá inserir totais por linhas e colunas, além de poder permitir

múltiplos filtros por campo e permitir a utilização de listas personalizadas na classificação dos

dados.

O leitor deve apenas lembrar que totais por linha totalizam os dados dispostos na horizontal ao

passo que os totais de coluna totalizam os dados dispostos na vertical.

Figura ‎4-12 Total geral para colunas e linhas

Outra forma de ativar/desativar totais é acessar a guia Design (da guia de extensibilidade) e sob o

grupo Layout botão split Totais Gerais selecione a o total desejado.

Page 43: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

35

Desenvolvimento inteligente

4.4.3. Exibição

Na parte de exibição, nós podemos executar vários comandos que nos permitem modificar o

método de exibição da tabela dinâmica.

Mostrar botões de expandir/recolher

Esta opção permite a alternância entre a visualização e não visualização do botão de

expandir/recolher.

Figura ‎4-13 Botões de expandir/recolher

A expansão para itens pode ocorrer através deste botão ou pelo duplo-clique (caso o botão não

esteja visível). Outra opção é acessar a TD e quando a guia de extensibilidade for ativada, vá até

a guia Opções grupo Campo Ativo Expandir Campo Inteiro:

Figura ‎4-14 Expandir/recolher Campo Inteiro

Legendas de campos e lista suspensa de filtros

Page 44: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

36

Desenvolvimento inteligente

Esta opção permite a alternância entre a visualização e não visualização do botão de

expandir/recolher.

Figura ‎4-15 Exibindo legendas de campos e lista suspensa de filtros

A exibição destes elementos é útil para impressão final de um relatório de tabela dinâmica.

Layout clássico

Exibe a TD no layout clássico, isto é, no layout pré-Excel 2007. Neste caso, note que em caso de

campos passíveis de extensão, o item oculto será exibido em outra coluna dentro da área de

rótulo de linha.

A vantagem da utilização deste layout é que podemos arrastar os campos para dentro/fora de

uma tabela dinâmica.

Utilize a tabela abaixo como guia dos símbolos de arraste do mouse:

Ao arrastarmos um campo, caso a área em azul seja o topo da pequena representação da TD, isso indica que o campo será inserido na área do campo de filtro;

Ao arrastarmos um campo, caso a área em azul seja o canto esquerdo da pequena representação da TD, isso indica que o campo será inserido na área do campo de linha;

Ao arrastarmos um campo, caso a área em azul seja a parte superior da pequena representação da TD, isso indica que o campo será inserido na área do campo de coluna;

Page 45: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

37

Desenvolvimento inteligente

Ao arrastarmos um campo, caso a área em azul seja a área central da pequena representação da TD, isso indica que o campo será inserido na área de dados;

Ao arrastarmos um campo, caso a figura representando a TD muda para um pequeno “X” o campo será removido.

Contraste também o layout inicial da TD clássica com o layout da TD no Excel 2007:

Figura ‎4-16 Exibindo o layout clássico ou layout Excel 2007

4.4.4. Impressão

Sob esta guia você pode definir certas opções de impressão tais como impressão dos botões de

expansão e recolhimento de itens.

4.4.5. Dados

Utilize esta guia para definir opções de dados tais como se os dados devem ou não ser

atualizados na abertura, se os dados devem ser salvos com o arquivo, etc.

A decisão de salvar ou não salvar os dados com o layout de tabela determina como o Excel

manipula os dados, tamanho do arquivo, desempenho da tabela e desempenho de

processamento.

Para salvar os dados com o layout da tabela, deixe este botão selecionado. Feito isso, o Excel

salvará os dados juntamente com a pasta de trabalho. Aqui, existe uma demora no salvamento e

o arquivo inchará. Quanto mais informações de TDs estejam em “cache” e sejam salvas, maior

será o arquivo.

Page 46: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

38

Desenvolvimento inteligente

A vantagem é que com os dados salvos no layout as informações da TD são carregadas mais

rapidamente e liberam memória, pois a mesma somente é utilizada quando algo muda na TD,

como relocação de campos dentro da TD.

Por outro lado, se não utilizamos esta opção o Excel deixa de salvar a informação junto com a

pasta de trabalho. Deste modo, o tamanho da pasta é reduzido se comparado com o salvamento

junto com o layout. O problema é que os dados precisam ser atualizados. Ao atualizar, o Excel

cria uma nova cópia dos dados em “cache”. Este processo é mais lento que o anterior.

4.5. O que é uma tabela unidimensional de campo único?

Uma tabela unidimensional de campo único é a tabela dinâmica mais simples que você

encontrará. Neste tipo de tabela o usuário insere um campo de linha ou de coluna e o campo de

dados. Feito isso, o usuário poderá determinar como os dados são agrupados (soma, contagem,

etc).

4.6. O que é uma tabela bidimensional de campo único?

Uma tabela bidimensional de campo único é a tabela dinâmica que contém ao menos um campo

de linha e um campo de coluna com os dados de ambos os campos sendo cruzados na área de

dados. Este é um dos motivos para se chamar tais tabelas de X-Tab (em inglês X-Tab é lido como

“cross tabulation” ou “tabulação cruzada”, em português).

O grande poder de tabelas dinâmicas é que podemos rapidamente cruzar os mais variados tipos

de informações rapidamente.

4.7. O que é uma tabela unidimensional e bidimensional de múltiplos campos?

Como o nome sugere, se a tabela for unidimensional com múltiplos campos estamos observando

uma tabela que contém mais de um campo da tabela fonte no campo de linha ou no campo de

coluna da tabela dinâmica.

Por outro lado, uma tabela bidimensional com múltiplos campos é aquela que possui múltiplos

campos da tabela fonte tanto no campo de linha quanto no campo de coluna da tabela dinâmica.

Page 47: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

39

Desenvolvimento inteligente

Boa parte das tabelas dinâmicas é feita em cima de tabelas bidimensionais, pois geralmente

queremos cruzar diversos dados simultaneamente de diversos campos de nossa fonte de dados.

Este tipo de tabela envolve um nível de complexidade extremamente elevado e em muitos casos,

você não conseguirá o resultado procurado na primeira tentativa (a menos que você conheça

muito bem os dados, sua estrutura e tenha muita experiência com tabelas dinâmicas). Desta

forma, se você se encontrar em dificuldades em criar uma tabela dinâmica complexa, não entre

em pânico, pois em muitos casos o trabalho é realmente frustrante de início.

4.8. Adicionando campos à tabela dinâmica: método clássico e novo

Para adicionar um campo à tabela dinâmica é bastante simples no Excel 2007. No método normal

para o Excel 2007, tudo que você precisa fazer é selecionar o campo e o Excel fará a sugestão de

onde ele deve ir. Em seguida, você pode utilizar o mouse para arrastar os campos dentro da

janela Lista de Campos para reorganizá-los ou reordená-los.

Page 48: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

40

Desenvolvimento inteligente

No Excel 97-2003, o leitor deve lembrar-se da Figura 4-17 exibida abaixo:

Figura ‎4-17 Janela da lista de campos

Na figura acima, para adicionar um campo à área de filtro (campo de filtro ou de página) tudo que

precisaríamos fazer é selecionar o campo, selecionar a opção na lista conforme mostra a figura e

clicar em Adicionar a. Mas isso mudou no Excel 2007. Ao invés disso, clique com o botão direito5

sobre o campo desejado para obter as mesmas opções:

5 Clicando com o botão esquerdo você obtém opções de filtro.

Page 49: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

41

Desenvolvimento inteligente

Figura ‎4-18 Adicionando campo à TD por clique-direito

4.9. Removendo campos da tabela dinâmica

Para remover um campo da tabela é bastante simples. Simplesmente clique sobre o campo e

segure o botão do mouse. Em seguida, arraste o campo para uma área fora da tabela dinâmica

(utilizando o método clássico conforme já ensinado).

No modo normal, simplesmente desmarque o campo na lista de campos.

Page 50: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

42

Desenvolvimento inteligente

5. Compreendendo as fontes de dados para Tabelas Dinâmicas

Ao criar uma tabela dinâmica, nós temos várias opções de fonte de dados. Compreender tais

fontes de dados é fundamental no momento da criação de sua TD. Utilizar o Excel como fonte de

dados pode não ser a melhor opção quando possuímos uma grande quantidade de registros. Por

outro lado se os dados precisam “viajar” com a planilha, provavelmente o Excel é a melhor opção

de fonte de dados.

Investiremos um tempo agora para conhecer as várias fontes de dados.

5.1. Banco de dados ou lista do Microsoft Office Excel

Antes de tudo, lembre-se que no Excel 2007 não há mais lista. Ao invés disso, nós temos

“tabelas”. Esta nova ferramenta substitui as antigas listas e possui muito mais vantagens do que

as antigas listas. Para maiores informações sobre Tabelas no Excel 2007, veja o curso:

http://www.juliobattisti.com.br/cursos/ex2007tabelas/default.asp

Utilizar o Excel como fonte de dados é, sem dúvida, a forma mais comum e conhecida na criação

de TDs. Neste caso, os dados podem estar localizados:

1. na mesma pasta de trabalho;

2. em uma outra pasta de trabalho

Independentemente da localização acima, os dados devem estar padronizados para o formato de

banco de dados, isto é, com colunas representando campos e as linhas representando registros.

Se a célula ativa está dentro da área de dados e esta área não possui descontinuidade de dados,

o Excel será capaz de determinar a área total de dados que será utilizada como fonte. Por outro

lado, se você deseja determinar a área de forma explicita você pode fazer o seguinte antes de

iniciar o processo de criação da TD:

1. Selecione a área de dados manualmente;

2. Digite o nome da tabela no campo Tabela/Intervalo. Nome da tabela é, na verdade, um

“Nome” (área nomeada);

Page 51: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

43

Desenvolvimento inteligente

3. Crie um nome (que pode ser estático ou dinâmico) e digite o nome no campo

Tabela/Intervalo.

A Figura 5-1 mostra o exemplo utilizando o nome de uma tabela:

Figura ‎5-1 Adicionando campo à TD por clique-direito

Caso o nome inserido seja inválido, o Excel exibirá uma mensagem de erro solicitando a sua

correção para que você possa continuar.

Até usamos dados da mesma pasta de trabalho; porém, podemos também utilizar dados de uma

pasta secundária. Primeira pergunta é: por que eu faria isso? O principal motivo para isso é

reduzir o custo de manutenção dos dados na mesma pasta, pois além do tamanho da pasta de

trabalho há também a questão de velocidade de processamento.

No caso dos dados estarem em uma pasta diferente, o processo de criação da TD é ligeiramente

diferente do processo já visto. Para criar esta TD, siga estes passos:

1. Inicie o processo de criação da TD conforme já mostrado;

2. O padrão “Tabela/Intervalo” já estará selecionado. Clique no botão de referência para abrir

a caixa de seleção de intervalo de dados;

3. Navegue até a pasta de trabalho que contém os dados (use Alt+Tab para alternar entre os

documentos abertos ou outro método de sua preferência);

Page 52: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

44

Desenvolvimento inteligente

4. Selecione os dados.

Note que a fonte de dados será exibida no formato:

[NomeDaPasta.xlsx]NomeDaPlanilha!Intervalo. Por exemplo:

[TD_XL07_Tópico3.3.xlsx]Plan1!MinhaTabela. Neste caso, “MinhaTabela” é o nome do intervalo

que contém os dados no formato de uma tabela.

Alternativamente, você pode inserir o caminho completo da localização de sua pasta de trabalho

contendo os dados, por exemplo: '\Users\Robert

Martim\Desktop\TD_XL07_Tópico3.3.xlsx'!MinhaTabela

NOTA:

Às vezes, nós queremos criar uma TD em cima de dados filtrados. Caso os dados estejam

filtrados o Excel ignorará o filtro e todos os dados serão utilizados. Caso você queira somente os

dados, copie os dados filtrados para outra localidade e utilize-os como fonte para a TD.

5.2. Fonte de dados externos

No caso de fontes externas de dados, nós podemos:

1. Importar os dados para o Excel e utilizá-los diretamente no Excel, atualizando a consulta e

a TD sempre que necessário;

2. Conectar a TD à fonte externa de dados.

Para conectar a TD à fonte externa de dados, siga os passos abaixo:

1. Abrar a caixa de diálogo de criação de TD;

2. Selecione a opção “Fonte de dados externos”;

3. Clique em “Escolher Conexão” para exibir a caixa de “Conexões Existentes”.

Page 53: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

45

Desenvolvimento inteligente

Figura ‎5-2 Conexões existentes de dados

Neste ponto, selecione a conexão desejada e clique em “Abrir”. Alternativamente, clique em

“Procurar Mais…” para localizar outras fontes de dados não listadas. Caso não exista a conexão,

você pode utilizar a guia Dados grupo Obter dados externos. Escolha uma das opções, crie e

salve a sua conexão para uso posterior.

NOTA:

Em alguns casos você pode querer salvar a senha de conexão. Porém, este salvamento não é

recomendado. A razão é que o Excel não somente expõe toda a string de conexão, mas também

o nome de usuário e senha. A Figura 5-3 exibe a string de conexão (cadeia de conexão) para um

servidor SQL. O nome do usuário (sa) e a senha são exibidos:

Page 54: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

46

Desenvolvimento inteligente

Figura ‎5-3 Cadeia de conexão

No caso anterior, nós utilizamos a fonte externa para alimentar a TD diretamente.

5.3. Vários intervalos de consolidação

Para usar esta opção é necessário acessar o antigo Assistente de Tabela Dinâmica. O acesso

direto pelo Excel 2007 não nos permite criar uma TD com este tipo de consolidação.

Antes de tudo, é importante lembrar que os dados devem ser compatíveis com o formato

requerido pela tabela dinâmica. Caso contrário, nós não obteremos o resultado desejado. Uma

vez no formato correto, a criação da TD é mais simples do que pode parecer. Estude a figura

abaixo:

Figura ‎5-4 Intervalos para consolidação

Para criar a TD utilizando os dois intervalos de consolidação, siga os passos abaixo:

Pressione Alt d a (ou Alt d p no Excel 2007 em inglês);

Selecione a opção “Vários Intervalos de Consolidação”;

Clique em “Avancar” para passar para o próximo passo;

Page 55: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

47

Desenvolvimento inteligente

Na segunda etapa do Assistente, você terá as seguintes opções (selecione a opção

padrão e clique em Avançar):

o Crie um único campo de página O Campo de Página é o nome dado no Excel

2003 ao Filtro de Relatório no Excel 2007. Caso tenha dúvida sobre a localização,

veja a Figura 4-1.

o Criarei os campos de página Utilize esta opção para determinar quantos campos

de página devem ter. Você pode determinar entre zero (0) e quatro (4) campos.

Figura ‎5-5 Definindo campo de página

No próximo passo, você deverá selecionar os intervalos de dados. Note que “Centro-

Oeste” e “Nordeste” encontram-se no topo de cada um dos intervalos de consolidação.

Voce não deve selecionar esta área, mas somente a área contendo os cabeçalhos de

campo e dados. Veja Figura 5-6 para exemplo de como selecionar o intervalo.

Clique em Avançar (Figura 5-6). Escolha a nova localidade e clique em “Concluir”

Page 56: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

48

Desenvolvimento inteligente

Figura ‎5-6 Selecionando intervalos de consolidação

Finalmente, defina onde você deseja que a nova TD seja criada (sugiro em uma nova

planilha).

A sua nova tabela dinâmica terá o seguinte formato:

Figura ‎5-7 Tabela dinâmica final

Note que não possuímos os nomes dos campos, mas apenas a consolidação dos mesmos. No

caso do Filtro de Relatório, ao expandi-lo, você verá apenas Item 1 e Item 2 os quais se referem

aos itens Centro-Oeste e Nordeste respectivamente (campo “Região”).

Page 57: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

49

Desenvolvimento inteligente

O nosso próximo passo é mudar os nomes dos campos padrões para o que realmente queremos.

Iniciaremos pelos Filtros de Relatório. Siga os passos abaixo:

Clique no botão de filtro (onde se lê (Tudo)) e escolha “Item 1”

Na barra de fórmula, troque o valor “Item 1” para “Centro-Oeste” e pressione “Enter”

Figura ‎5-8 Trocando o nome do item do Filtro de Relatório

Ao pressionar Enter, o Excel avisará que não existe o item digitado e sugerirá que o item seja

renomeado:

Figura ‎5-9 Campo inexistente, renomear?

Clique “OK” para continuar e renomear o item. Repita os passos e renomeie o “Item 2” para

“Nodeste” bem como “Página 1” para “Região”. Ao final da renomeação, você terá o resultado

como segue:

Page 58: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

50

Desenvolvimento inteligente

Figura ‎5-10 Resultado após renomear o campo (Página1) e Items 1 e 2

Em seguida, nós renomearemos os campos. Na lista de campos, clique no campo que deseja

renomear e selecione a opção “Configurações do Campo…”:

Figura ‎5-11 Renomeando campo via “Lista de campos da tabela dinâmica”

Page 59: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

51

Desenvolvimento inteligente

No caso acima, o campo selecionado é “Linhas” o qual deve ser modificado para “Cidade”. Ao

terminar de renomear todos os campos (exceto o de colunas que não terá efeito algum para este

exemplo), você terá o seguinte cenário:

Figura ‎5-12 Renomeando campo via “Lista de campos da tabela dinâmica”

5.4. Outro relatório de tabela dinâmica ou de gráfico dinamico

Como última opção da lista de fonte de dados nós temos como utilizar outra TD ou Gráfico

Dinâmico (GD). No Excel 2007, você pode criar uma TD/GD utilizando os seguintes métodos:

Selecionar o mesmo intervalo (intervalo exato) de uma TD já existente;

Acionar o Assistente (Alt d a) e selecionar a TD da lista

Figura ‎5-13 Baseando uma nova TD em uma já existente

Page 60: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

52

Desenvolvimento inteligente

O motivo para usar outra TD/GD como fonte de dados? Uma TD/GD baseada em outra usa

menos memória. Simples assim.

Page 61: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

53

Desenvolvimento inteligente

6. Formatação

Neste tópico discutirei a questão de formatação de uma tabela dinâmica. A formatação é um ponto

importante, pois é através dela que nós realçamos pontos dentro da tabela, etc.

Formatação é uma parte complexa, pois cada indivíduo tem gosto diferente. Portanto, este tópico

não estará focado em “como-fazer-a-tabela-mais-bonita-do-planeta”, mas estará focado nas

ferramentas que o leitor precisa para criar a formatação que desejar.

Aqui, não discutirei questões básicas como “onde modificar cor de letra”. O que faremos é

aprender a formatar uma TD. É assumido que o leitor sabe onde formatar texto, linhas, fundos,

etc.

6.1. Estilos de Tabela Dinâmica

O Excel 2007 vem com um número de estilos prontos para aplicação em uma tabela dinâmica.

Além disso, você pode criar a sua própria formatação e/ou estilo. Por questão de praticidade e

rapidez, eu prefiro os estilos prontos aos meus próprios. Não obstante, há empresas que, por

motivos de identidade corporativa, utilizam estilos próprios.

Um estilo visa controlar vários aspectos visuais, entre eles (não somente de uma TD/GD mas de

qualquer outro objeto no Excel):

Fonte: define o tipo de fonte a ser utilizado em toda a TD/GD ou em elementos específicos

do objeto que receberá o estilo. Voce pode definir tamanho, cor, estilo (negrito, itálico,

sublinhado, etc), estilo de fonte, etc

Bordas: define o estilo de bordas para linhas horizontais e verticais utilizadas para

envolver a área onde será aplicada ou elemento que receberá a aplicação

Fundo: determina cores de fundo bem como padrões para o fundo do elemento em

questão

Para aplicar um estilo:

Selecione a TD que receberá o estilo

Selecione um estilo da lista de estilos conforme mostra figura a seguir

Page 62: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

54

Desenvolvimento inteligente

Figura ‎6-1 Estilos de tabela dinâmica

Alternativamente, você pode abrir a caixa de definição de estilos (Estilos de tabela dinâmica

Novo estilo de tabela dinâmica…) onde você poderá definir o estilo para os mais variados

elementos da tabela dinâmica:

Figura ‎6-2 Estilo personalizado para TD

Nesta janela, você possui os seguintes elementos:

Nome: Digite aqui o nome para o seu estilo personalizado

Visualização: Exibe como o seu estilo ficará

Page 63: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

55

Desenvolvimento inteligente

Limpar: Limpa toda a formatação da TD selecionada

Definir como estilo rápido de tabela dinâmica para este documento: define como

estilo padrão para todas as novas TDs para a pasta de trabalho.

Para modificar o estilo criado basta retornar à galeria de estilos e clicar com o botão direito sobre

o estilo personalizado. Para os estilos internos nós podemos defini-lo cmo padrão, copiar etc:

Figura ‎6-3 Atualizando, modificando e duplicando estilos

6.2. Formatando campo

Para formatar um campo é bastante simple. Você pode acessar a configuração do campo:

Direto na tabela: Clique com o botão direito sobre o campo e escolha a opção

“Configurações de campo”.

Na lista de campo (área de campos na TD e não de campos disponíveis): clique com o

botão esquerdo sobre o campo e escolha a opção “Configurações de campo”.

Sob a guia “Opções”, grupo “Campo Ativo”: Clique sobre o campo desejado, selecione

a guia “Opções” grupo “Campo Ativo” e clique em “Configurações de campo”.

As opções de configuração do campo selecionado dependerão do tipo de dado no campo

selecionado.

6.3. Layout do relatório de tabela dinâmica

O Excel oferece três tipos de layouts distintos para a sua tabela dinâmica. Estes podem ser

acessados sob a guia Design (presente na guia de extensibilidade “Ferramentas de Tabela

Dinâmica”).

Page 64: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

56

Desenvolvimento inteligente

Os seguintes layouts estao disponíveis:

Formato compacto: Este layout requer o mínimo de espaço para a sua TD. Figura 6-4

mostra o modelo de formato compacto. Este formato oculta botão de “Lista Suspensa” para

o segundo e subseqüente campos de linha.

Formato de Estrutura de Tópicos: Neste layout, os dados são formatados no modelo de

“estrutura de tópicos”, isto é, os campos são indentados de acordo com a ordem

hierárquica. Figura 6-5 mostra o formato em Estrutura de Tópicos

Formato de tabela: Exibe a TD em um formato de tabela. Figura 6-6 mostra a TD no

formato de tabela.

Figura ‎6-4 Formato compacto

Figura ‎6-5 Estrutura de Tópicos

Page 65: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

57

Desenvolvimento inteligente

Figura ‎6-6 Formato de Tabela

6.4. Preservando formatação

Ao aplicar uma formatação diretamente nos elementos de uma tabela dinâmica, ao atualizarmos a

TD, a formatação é revertida para a formatação padrão aplicada. Para manter o formato aplicado

abra a caixa de opções da tabela dinâmica e marque a opção “Preservar a formatação da célula

ao atualizar”.

É importante notar que elementos que pertencem ao mesmo campo podem ser formatados

simultaneamente desde que eles tenham sido selecionados. Para selecionar tais elementos vai

depender do cursor do mouse:

Figura ‎6-7 Selecionando elementos da tabela dinâmica para formatação

Page 66: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

58

Desenvolvimento inteligente

Com o cursor apontando para baixo, conforme figura acima, os elementos do campo da linha

(Região) serão selecionados como mostra a área sombreada.

O método mais seguro, obviamente, é selecionar o campo diretamente na lista de campos e

formatar por lá.

6.5. Modificando nome de campos

Para modificar o nome de um campo é bastante simples: um duplo-clique sobre o nome do

campo6,7 e a janela de edição do nome do campo é aberta:

Figura ‎6-8 Modificando rótulo do campo da TD

No campo “Nome Personalizado”, defina o nome que você deseja dar ao campo em questão.

6 Caso a área de dados possua mais de um campo o duplo-clique não funcionará. Para modificar o nome do campo clique com o botão

direito sobre o campo e escolha a opção “Configurações de campo”. Utilize este método para modificar campos também. 7 Caso a TD esteja em um formato de relatório, o duplo-clique sobre a área de dados causará a criação de uma planilha nova em forma

de uma tabela-relatório.

Page 67: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

59

Desenvolvimento inteligente

7. Trabalhando com campos

Até o momento vimos o básico e o importante para que possamos trabalhar com tabelas

dinâmicas. Sem este conhecimento básico, passar para o próximo estágio pode ser mais penoso

(como é o caso da formatação).

Neste grande tópico veremos como trabalhar com campos em uma tabela dinâmica.

Primeiramente, investiremos um tempo na construção de tabelas com múltiplos campos em linha

e/ou coluna.

Em seguida movemos para subtotalizações, agrupamentos e campos calculados.

7.1. Trabalhando com campos de linha e de coluna

Trabalhar com campos de linha é bastante simples. Iniciaremos com a adição de um campo de

linha mais o campo de dados da planilha deste tópico:

Figura ‎7-1 Tabela “normal”

Nós temos aqui uma tabela simples unidirecional, isto é, uma TD contendo um campo de linha

mais o campo de dados.

Caso haja uma necessidade de maior detalhamento, nós podemos adicionar mais um campo à

área reservada para as linhas conforme mostra a figura a seguir. Note que a disposição dos dados

é em forma de relatório e, portanto, podemos expandir ou colapsar um determinado item dentro do

campo:

Page 68: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

60

Desenvolvimento inteligente

Figura ‎7-2 Área de linha com múltiplos campos

Conforme já dito a expansão total acima pode ser colapsada/expandida conforme a necessidade,

bastando apenas clicar nos botões de mais (+) e menos (-):

Figura ‎7-3 Expandindo a região sul e colapsando as demais

Agora que possuímos estes dois campos o que mais podemos fazer? Clique com o botão direito

sobre o campo Região e selecione a opção Configurações do campo. A caixa de diálogo do

campo da TD será aberta:

Page 69: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

61

Desenvolvimento inteligente

Figura ‎7-4 Opções de configuração de um campo da TD

Na guia Subtotais e Filtros nós podemos definir o tipo de subtotais que desejamos trabalhar. Por

exemplo, dependendo do tipo de dados, o subtotal automático será Soma ou ContNúm. Por outro

lado, você pode selecionar “Personalizados” e escolher a função para agregar os seus dados.

Clicando na guia Layout e Impressão. Voce terá as seguintes opções:

Figura ‎7-5 Layout do campo da TD

Page 70: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

62

Desenvolvimento inteligente

O que cada uma das opções representa:

Mostra rótulos de item no formato de estrutura de tópicos Exibe os rótulos de item

(não confunda “Item” com “Campo”, “cidade” se transforma em “item” quando estruturada

dentro do campo “região”) em estrutura de tópicos. Campo fica uma linha acima do item

dentro da estrutura da TD:

o Exibir rótulos do próximo campo na mesma coluna O “próximo campo” aqui

se refere ao campo que fornecerá os itens na estrutura de tópicos. Uma coluna é

removida para cada item na estrutura da TD. O formato precisa ser aplicado para

CADA campo que possui um campo de itens.

o Exibir subtotais no início de cada grupo Subtotais serão exibidos cada vez

que um grupo é modificado.

Mostrar rótulo de item no formato de tabela Exibe o rótulo no formato da tabela. O

campo fica na mesma linha que o item dentro da estrutura da TD.

Inserir linha em branco após cada item Novamente, faz exatamente o que está

escrito: insere uma linha em branco após cada item de um grupo;

Inserir quebra de página após cada item Esta opção de impressão permite colocar

quebras de página nos itens do campo selecionado.

Vejamos agora os itens de subtotalização que aparecem na caixa de edição do campo da TD.

7.2. Trabalhando com sumários e subtotalizações: subtotais simples e múltiplos

Ao criar uma tabela dinâmica o Excel adicionará totais por linhas ou colunas (ou ambas se

desejar). O tipo de total poderá variar dependendo do tipo de dado na área de dados. Por

exemplo, se todos os dados forem textos, então os mesmos são contados automaticamente ao

passo que valores numéricos são somados.

É importante salientar que um valor diferente na área de dados determinará o total adicionado

(contagem ou soma). Por exemplo, se existirem 999 valores numéricos e um de texto, a contagem

tem precedência sobre a soma:

Page 71: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

63

Desenvolvimento inteligente

Figura ‎7-6 Configuração do campo da TD

Embora estes dois sumários sejam “padrões”, ainda é possível determinar outros tipos de

sumários. A figura acima mostra algumas das opções de subtotalização. Você pode selecionar

mais de uma função para personalização e o campo de dados será duplicado para exibir a nova

subtotalização.

Observe a figura abaixo:

Figura ‎7-7 Totalização e subtotais personalizados

Nesta figura nós possuímos a soma total por região e a média por região. Porém, note que a

média não é entre as cidades, mas entre todas as observações para o Centro-Oeste, isto é, a

Page 72: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

64

Desenvolvimento inteligente

média é entre toda a soma para a região (R$4.135.308) divida pelo total de observações (648)

para a região.

Finalmente, para remover um subtotal, basta abrir a caixa de configuração do campo da TD e

excluir os tipos de subtotais desejados.

7.2.1. Modificando o tipo de subtotal apresentado (mostrar como % do total, etc)

Os subtotais que adicionamos anteriormente são valores absolutos, porém suponha que você

queira saber os valores relativos, isto é, o que você deseja saber é a proporção de cada valor em

relação ao total.

Continuando com os dados e exemplo do tópico anterior, vejamos o nosso cenário inicial “normal”

(“normal” é a opção inicial de apresentação do sumário):

Figura ‎7-8 Tabela “normal”

A soma total é o nosso 100% e cada uma das somas para as regiões brasileiras representa um

percentual do total. Poderíamos efetuar o cálculo no lado externo da TD, porém este não seria o

cenário ideal:

Page 73: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

65

Desenvolvimento inteligente

Figura ‎7-9 Cálculo percentual no lado externo da TD

Se os valores em linha forem modificados teremos erro no cálculo. Por outro lado, se houver

mudanças na coluna (adição de um campo de linha ou coluna), a TD será movida para a direita

sobrescrevendo os valores (o usuário será alertado antes) calculados.

Como estes cálculos não fazem parte da TD eles são sensíveis a modificações externas. A

solução é adicionar o cálculo diretamente na TD.

Para modificar este campo, abra a configuração de campos para o campo Soma do Total.

Quando a janela abrir clique em Opções para expandir as opções do campo:

Figura ‎7-10 Opções de visualização de dados

Page 74: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

66

Desenvolvimento inteligente

No total são 9 formas diferentes de apresentar os dados (incluindo a forma normal). No exemplo

acima, estamos interessados no percentual do total (% do total). Selecione e aplique esta opção:

Figura ‎7-11 Visualização normal

O exemplo acima pode ser expandido para levar em conta cenários mais complexos. Vejamos

como complicar um pouco a nossa análise.

O nosso conjunto de dados possui um campo de categorias o qual pode ser utilizado em nossa

análise. Suponha que desejamos saber o somatório para cada região dentro de cada categoria.

Também desejamos saber o percentual que cada região representa para cada categoria. O nosso

cenário inicial é mostrado abaixo:

Figura ‎7-12 Definição do problema com múltiplas áreas de dados

Para criar esta tabela inicial siga os passos:

Inicie com um TD em branco;

Page 75: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

67

Desenvolvimento inteligente

Adicione o campo Região à área de linha e o campo Categoria à área de coluna. A TD

terá agora a seguinte visualização:

Figura ‎7-13 Configuração da área de linhas e colunas (exibição clássica)

O próximo passo é adicionar os dados. Aqui, acrescente o campo Total duas vezes na

área de dados. Assim, obteremos a figura inicial deste tópico. Caso o Excel adicione o

campo “Total” como rótulo de “ Valores” da coluna, mova o campo de rótulo para a área

de linha. A figura da direita abaixo mostra como a configuração dos campos deve ficar (a

figura da esquera é a sugestão padrão do Excel):

Figura ‎7-14 Movendo rótulo de coluna para linha

Modifique o rótulo para os campos da área de dados para Soma por categoria e para %

Regional por categoria. A nossa TD agora terá o seguinte layout:

Page 76: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

68

Desenvolvimento inteligente

Figura ‎7-15 Configuração do rótulo de campos

Selecione o campo (efetivamente será um item na área de linha) % Regional por

categoria e acesse a caixa de configuração de campo. Clique em Opções e modifique a

visualização para % da coluna:

Figura ‎7-16 Configuração da área de dados para valores absolutos e percentuais

Os totais acima são para colunas, mas o leitor pode modificar para mostrar os totais por linha, por

exemplo.

7.3. Utilizando data base para comparar valores (gastos de um trimestre base comparados com outro)

Este exemplo pode ser expandido para outros cenários tais como comparações entre anos, entre

meses, etc.

O que desejamos criar neste novo cenário é descrito abaixo:

Calcular a soma total para uma região qualquer;

Page 77: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

69

Desenvolvimento inteligente

Agrupar os dados desta região por trimestre;

Comparar o desempenho dos trimestres seguintes ao trimestre base (neste caso será o

primeiro trimestre, mas o trimestre base pode ser qualquer um).

Para iniciar, vamos criar a parte básica de nossa TD. Siga os passos abaixo:

Adicione uma nova TD utilizando os dados deste tópico;

Adicione os campos Região e Data da Venda à área de linhas;

Adicione o campo de Total à área de dados.

A nossa TD agora terá o seguinte visual (a imagem abaixo mostra apenas parte dos dados):

Figura ‎7-17 Configuração inicial da TD

Agora que já possuímos a parte básica, precisamos agrupar os dados por trimestre e mostrar os

dados apenas para a região que desejamos analisar. Além disso, precisaremos repetir o total para

efetuar a nossa comparação. Para este novo estágio, siga os passos abaixo:

Clique-direito sobre o campo Trimestre e clique sobre Agrupar;

Na nova janela que abrir, selecione a opção Trimestre conforme mostra a figura:

Page 78: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

70

Desenvolvimento inteligente

Figura ‎7-18Agrupando por trimestre

Clique OK para continuar.

A nossa TD deve ter o seguinte formato ao terminar este estágio:

Figura ‎7-19 Dados agrupados

O próximo passo requer a adição do campo Total à área de dados (desde modo teremos os

dados repetidos) e o novo visual da TD será:

Page 79: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

71

Desenvolvimento inteligente

Figura ‎7-20 Repetição do total na área de dados

Modifique os rótulos dos totais para Soma do total e % comparativo (base Trim1).

Para efetuar a última mudança em nossa TD para mostrar o percentual comparativo entre os

trimestres tendo como base o primeiro trimestre, siga os passos abaixo:

Clique-direito sobre o campo % comparativo (base Trim1) e selecione Configurações de

campo;

Na janela que abrir, clique no na guia Mostra valores como;

Selecione a opção % diferença de;

No campo base, selecione o campo Trimestre;

No item base, selecione o item Trim1;

Clique OK para terminar.

A nossa TD finalmente terá o seguinte visual:

Page 80: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

72

Desenvolvimento inteligente

Figura ‎7-21 Comparação percentual entre trimestres e trimestre base

Como o trimestre base é o primeiro, este item estará vazio conforme mostra a figura acima. O

cálculo efetivamente feito pelo Excel é a divisão do segundo (e terceiro) trimestre pelo primeiro

trimestre. Do resultado subtraímos 1. Por exemplo, a diferença para o segundo trimestre é

calculada como segue:

1.640.357,701 3,72%

1.581.582,70

O mesmo cálculo acima é feito para o terceiro trimestre e obtemos -42,25% de diferença em

relação primeiro trimestre. Em outras palavras, as vendas são 42,258% menores do que as

apresentadas no primeiro trimestre.

7.4. Trabalhando com índices (comparação relativa)

Já vimos como efetuar uma comparação absoluta e como efetuar uma comparação percentual. O

nosso próximo objetivo é efetuar uma comparação relativa.

Uma comparação por índice é similar à comparação percentual, porém o Excel nos fornece a

opção de índice para este tipo de comparação e é exatamente isso que faremos.

A grande questão deste tipo de comparação é a interpretação dos resultados. Como estamos

lidando com o cruzamento de dados, isso implicaria que um índice muito elevado em uma linha

indicaria que ele é extremamente importante para o item da coluna.

8 Note que a TD exibe -42.25. Aqui o ponto é usado para separar o decimal. Isso ocorre, pois o meu sistema

está configurado para o inglês britânico onde o decimal é separado com o ponto ao invés de vírgula.

Page 81: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

73

Desenvolvimento inteligente

A figura a seguir mostra o resultado final deste tipo de comparação. Vejamos primeiro o que o

resultado significa antes de criamos a TD utilizando índices:

Figura ‎7-22 Comparação por índice

Todos os itens de nossa tabela possuem um índice bastante similar (indicando uma importância

balanceada). Na linha TELEFONIA nós vemos que o índice para o Sul é de 0,97 ao passo que

para o Centro-Oeste este mesmo índice é de 1,21. Este resultado implica que telefonia tem uma

importância menor nas vendas para o Sul ao passo que a sua importância é maior para o Centro-

Oeste.

O cálculo efetuado pelo Excel para gerar o índice é o seguinte;

_ * _

_ * _

Valor Interserção Total GeralÍndice

Total Coluna Total Linha

Agora que o leitor já compreende como o cálculo é feito e como interpretar o resultado, vejamos

como modificar uma TD:

Clique-direito sobre o campo de dados;

Selecione a opção Configurações do campo de Valor;

Na janela que abrir clique em Mostrar Valores Como;

Selecione a opção Índice dentre as opções para mostrar os dados.

Feito isso, nossa TD de índices está pronta.

Page 82: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

74

Desenvolvimento inteligente

7.5. Trabalhando com campos e itens calculados

Até o momento aprendemos diversas formas de trabalhar com campos em uma tabela dinâmica

manipulando os resultados finais.

Não obstante, os cálculos efetuados até o momento são resultados de funções internas do Excel.

Contudo, além destas mais variadas opções, nós podemos criar nossos campos calculados os

quais são utilizados pela TD.

Vejamos, primeiramente, o que é um campo e item calculado antes de partirmos para a criação

dos mesmos.

7.5.1. O que é um campo ou item calculado?

Antes de tudo, o que é um campo ou item calculado? Um campo calculado não é a mesma coisa

que um item calculado, portanto precisamos defini-los separadamente:

Campo calculado

Um campo calculado nada mais é do que um novo campo. Porém, este novo campo é criado

através de um cálculo entre dois ou mais campos existentes em nossa tabela dinâmica.

Pegue, por exemplo, os campos “Total” e “Qtd Vendida”. Sabemos que ao inserir este dois

campos na área de dados obtermos um somatório dos totais e obteremos um somatório das

quantidades. Supondo que tais somatórios sejam por cada região brasileira, se nós dividirmos o

Total pela Qtd Vendida nós obtemos um retorno médio por unidade vendida por região.

A figura a seguir mostra a configuração inicial de nossa tabela dinâmica:

Page 83: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

75

Desenvolvimento inteligente

Figura ‎7-23 Configuração inicial do problema

Sendo assim, para o Centro-Oeste nós teríamos uma média igual a:

4.135.308$1.354,51

3.053R

Esta é a nossa média por unidade vendida. Obviamente que tal cálculo poderia ser feito em um

novo campo em nossa base de dados, contudo isso é desnecessário. Utilizando um campo

calculado nós podemos rapidamente fazer isso.

Mais adiante, discuto separadamente como isso é feito.

Item calculado

Item calculado refere-se ao cálculo entre itens de uma tabela dinâmica. Imagine o cenário aonde

nós possuímos dois campos em uma linha (digamos o campo Região e o campo Categoria). O

primeiro campo agrupa o segundo, contudo nós desejamos agrupar algumas categorias para

consolidação.

Sendo assim nós poderíamos agrupar as categorias Informática, Telefonia e TVs Plasma e LCD

em uma grande categoria denominada “eletrônicos”. Esta nova categoria nada mais é do que o

nosso item calculado.

Portanto a nossa tabela dinâmica possui a seguinte configuração inicial:

Page 84: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

76

Desenvolvimento inteligente

Figura ‎7-24 Configuração padrão

Esta configuração inicial é o que obteríamos normalmente ao adicionarmos os campos à nossa

TD. Porém, o objetivo, conforme delineado anteriormente, é obter a seguinte TD onde os últimos

três itens do campo Categoria são calculados como um único item:

Figura ‎7-25 Resultado do item calculado

Como podemos ver, agora possuímos os três itens anteriores em um único onde o valor

representa a soma de cada um dos itens determinados.

Agora que o leitor já sabe o que um campo e um item calculados significam, vejamos como criar

tais campos e itens e aplicá-los à nossa TD.

Page 85: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

77

Desenvolvimento inteligente

7.5.2. Criando um campo e/ou item calculado

Voltemos ao exemplo da introdução aos campos calculados. O nosso cenário inicial é dado pela

figura a seguir. O que desejamos saber é média unitária. Nós já possuímos o total das vendas e o

total da quantidade vendida.

Figura ‎7-26 Configuração inicial

Para inserir o campo calculado da média por unidade vendida, siga os passos abaixo:

Ative a tabela dinâmica;

Clique na guia Opções e sob o grupo Ferramentas selecione Fórmulas Campo

Calculado;

A figura abaixo mostra os passos acima visualmente:

Figura ‎7-27 Abrindo a caixa de diálogo do campo calculado

Page 86: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

78

Desenvolvimento inteligente

Ao clicar na opção Campo calculado, a caixa de diálogo do campo calculado será aberta. Aqui,

precisamos determinar a nossa fórmula. A figura abaixo mostra o novo campo já configurado:

Figura ‎7-28 Configurando o campo calculado

Com o campo criado, podemos retornar a nossa TD e adicioná-lo a ela:

Figura ‎7-29 Resultado final de nosso campo calculado

Obtemos, assim, o nosso primeiro campo calculado. O nosso próximo problema é adicionar o item

calculado. Para que isso seja possível, os itens do campo não podem estar agrupados. Caso os

mesmos estejam agrupados (ou os dados venham de uma TD onde os itens estejam agrupados),

é necessário desagrupar os dados antes de continuar.

Para inserir o item calculado agregando os itens discutidos no tópico, siga os passos abaixo:

Ative a tabela dinâmica;

Page 87: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

79

Desenvolvimento inteligente

Clique na guia Opções e sob o grupo Ferramentas selecione Fórmulas Item

Calculado;

O Excel é capaz de reconhecer o campo onde podemos efetuar o cálculo e já abre a janela

sugerindo qual campo utilizar:

Figura ‎7-30 Configurando o item calculado

Tudo que precisamos fazer é adicionar o nome do campo e a fórmula (neste caso uma simples

soma) conforme mostra a figura anterior. Clique OK para terminar.

A nossa TD agora terá o seguinte visual:

Figura ‎7-31 Configuração final da TD após inserção do item calculado

Page 88: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

80

Desenvolvimento inteligente

O qual não é exatamente o que desejamos tendo em vista que agrupamos três itens em um só.

Sendo assim a soma total e para cada região terá os três itens adicionados também, como se os

mesmos fizessem parte de uma nova categoria. O que precisamos fazer é remover estes itens.

Para tanto, clique na seta do menu suspenso9 da categoria e remova os itens consolidados:

Figura ‎7-32 Filtrando os itens

Ao clicar OK, os campos serão filtrados ficando somente a consolidação e os campos não

consolidados. Neste ponto, o recálculo da TD é feito e os valores corretos são mostrados:

9 Caso o menu suspenso da Categoria não esteja visível, clique com o botão direito sobre o menu suspeso para ativar a categoria ou

mude a visualização para modo Tabular (Design Layout do Relatório Mostrar em Formato de Tabela).

Page 89: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

81

Desenvolvimento inteligente

Figura ‎7-33 Configuração final da TD após ocultarmos os campos consolidados

Chegamos ao final da criação de campos e itens calculados. Vejamos agora como removê-los da

lista de campos.

7.5.3. Editando/Excluindo campos/itens calculados

Para remover um campo ou item calculado é bastante simples. Acesse a caixa de diálogo de

criação de itens/campo calculado. Na caixa de combinação Nome selecione o nome do

campo/item e clique em Excluir:

Page 90: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

82

Desenvolvimento inteligente

Figura ‎7-34 Excluindo item calculado

O leitor deve estar atento ao fato de que caso o campo esteja sendo usado o mesmo será

removido da TD. Isso ocorre porque o mesmo não está mais disponível na lista de campo. Caso

um novo campo calculado com o mesmo nome e característica seja criado, o mesmo será

novamente mostrado na TD.

7.5.4. Determinando ordem de cálculo

Se a ordem de cálculo é fator importante em um campo ou item calculado (caso um campo ou

item calculado dependa do resultado de outro), então precisamos determinar a ordem de

resolução do cálculo.

Para determinar a ordem de cálculo vá até Opções Fórmulas Ordem de resolução. Na

janela que abrir, selecione o campo/item e utilize o botão Mover para cima ou Mover para baixo

para determinar a ordem:

Page 91: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

83

Desenvolvimento inteligente

Figura ‎7-35 Determinando ordem de resolução

7.5.5. Determinando ordem de apresentação

Outra questão de ordenação diz respeito à apresentação. O Excel automaticamente sugerirá uma

ordem de apresentação dos itens em uma tabela dinâmica. Não obstante, um item pode ser mais

importante que outro e o que realmente queremos é que o item importante fique em destaque no

topo da lista.

A figura abaixo mostra a ordem padrão (neste caso, alfabética) dos itens do campo Categoria:

Figura ‎7-36 Ordem original

Supondo que o item calculado Outros deva vir no topo da lista de itens, nós podemos facilmente

rearrumar a ordem os itens:

Page 92: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

84

Desenvolvimento inteligente

Figura ‎7-37 Ordem revisada

Para rearrumar a ordem dos itens siga os passos abaixo:

Selecione um item do campo onde ocorrerá a rearrumação;

Clique na Opções Classificar Ordem;

Selecione a opção Manual. Mova o item para o local desejado.

A figura a seguir mostra os passos visualmente:

Figura ‎7-38 Determinando ordem de apresentação

Page 93: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

85

Desenvolvimento inteligente

7.6. Determinando os “Top 10” (os “10 Primeiros”)

Além das facilidades que vimos até o momento, podemos utilizar a ferramenta de “Top 10” ou “os

10 Primeiros” para determinar o grupo que possui os 10 maiores valores em nossa lista.

Embora o nome oficial seja “10 Primeiros”, isso não quer dizer que só possamos filtrar os 10

maiores valores. Na verdade, não somente podemos determinar quantos valores, mas também a

ordem, isto é, poderíamos determinar os 10 menores valores em nossa TD.

Para filtrar a lista para os 10 maiores ou menores valores, siga os passos abaixo:

Clique sobre a alça de filtragem Filtros de Valores 10 Primeiros;

Figura ‎7-39 Determinando os “10 Primeiros”

Na janela que abrir, determine o primeiro maior;

Clique “OK” para terminar:

Page 94: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

86

Desenvolvimento inteligente

Figura ‎7-40 O primeiro maior valor por região da TD10

10

Note que o filtro é aplicado de acordo com o campo selecionado. Neste caso, o campo (item) usado é o de loja.

Page 95: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

87

Desenvolvimento inteligente

8. Salvando a TD como um documento HTM interativo

Este tópico é apenas um comentário sobre o que ocorreu com a interatividade de TD no Excel

2007. Infelizmente, a Microsoft removeu tal opção por motivos de segurança.

Caso o leitor queira utilizar algo similar é preciso utilizar o Excel Services.

Page 96: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

88

Desenvolvimento inteligente

9. Tabulando dados de Pesquisas com TDs

Este tópico funciona como estudo de casos. No estudo de casos, pegamos problemas reais e o

estudamos. O resultado destes estudos não são verdades absolutas neste contexto, mas uma

demonstração de como proceder quando nos deparamos com tais situações.

Neste primeiro estudo tabularemos e analisaremos os resultados de uma pesquisa de opinião. Já

no segundo estudo de casos veremos uma questão recorrendo no fórum de discussão: controle

de estoque.

9.1. Tabulando e analisando dados de uma pesquisa de opinião: cenário 1 – tabulação global

Os dados que utilizaremos neste estudo de caso são oriundos de um banco de dados Access. O

objetivo não é ensinar Access, portanto a criação de consultas no Access não será tratada.

Ao exportar os dados do Access para o Excel teremos algo como mostra a figura abaixo:

Figura ‎9-1 Situação dos dados importados do Access

Note que as respostas são colhidas como VERDADEIRO ou FALSO para todas as respostas

possíveis (de R1 a R6). Como texto é contado, o primeiro passo requer a transformação destes

valores para valores numéricos.

Utilizando a ferramenta de substituição, substitua os valores VERDADEIROS por 1 e os

VALORES FALSOS por 0:

Figura ‎9-2 Conversão para 1s e 0s

Page 97: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

89

Desenvolvimento inteligente

O nosso objetivo agora é contar quantas vezes ocorrem as respostas para as opções R1 a R6.

Uma solução seria aplicar um filtro automático e, utilizando a função SOBTOTAL, somar cada

uma das colunas:

Figura ‎9-3 Contando com a função SUBTOTAL

Não há nada de errado com esta abordagem a não ser o fato de ser lento e passível de erro. Em

momentos como este a solução mais rápida e segura é utilizar uma tabela dinâmica.

No caso da tabulação dos dados, o que desejamos é:

Contar as respostas dadas a cada opção;

Saber o percentual que cada contagem representa para uma pergunta (No caso acima, o

total geral por pergunta é de 48 respostas ao passo que o número de respostas para

opção R1 é 16. Portanto, 16 representa 34,78% do total)

Sendo assim, o novo objetivo é chegar a um resultado igual ao da figura abaixo:

Figura ‎9-4 Resultado final da tabulação da pesquisa

Os campos à direita do campo R6 são campos calculados. Não há necessidade de calculá-los

diretamente na TD, porém para manter a integridade dos cálculos é recomendável que seja feito.

Para o final do curso mostro o motivo para escolher o campo calculado ao invés de calculo

separado.

Page 98: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

90

Desenvolvimento inteligente

Vejamos como chegar ao resultado já mostrado. Cada passo é descrito abaixo seriatim:

1. Inicie criando a sua TD (Clique na célula A1 da planilha de dados e em seguida vá até

Inserir Tabela dinâmica);

2. Adicione o campo Pergunta à área de linha;

3. Adicione os campos R1 a R6 à área de dados.

A primeira parte está pronta e sua TD deve ser algo similar à figura abaixo (o nome do campo de

dados pode ser diferente tendo em vista que eu os renomeei):

Figura ‎9-5 Primeira parte da tabulação da pesquisa11

A primeira parte de nossa TD está pronta. O próximo passo é adicionar um campo para calcular o

total por pergunta e colocar nossa TD em um formato tabular. Seguindo a numeração:

4. Adicione um campo calculado para retornar o total por pergunta. Para tanto, siga os

passos:

a. Clique em um ponto qualquer da TD;

b. Clique em Opções Fórmulas Campo calculado;

c. Na janela que abrir, determine a fórmula como segue (escolha um nome de sua

preferência):

11

O rótulo de valores se encontra na área de linha.

Page 99: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

91

Desenvolvimento inteligente

Figura ‎9-6 Determinando o campo para calcular o total por linha (por pergunta)

5. Adicione este novo campo à área de dados;

Figura ‎9-7 Novo campo adicionado à área de dados

6. Passe o rótulo de valores da área de linha para a área de coluna (por padrao, no Excel

2007, o rótulo já estará na área de rótulos de coluna.

Terminada esta parte, nós teremos a tabulação por cada pergunta e opção de resposta no estilo

“tabular”.

Page 100: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

92

Desenvolvimento inteligente

Figura ‎9-8 Campo Total_Pergunta adicionado

Finalmente, nós precisamos adicionar os percentuais. Como havia dito, isso pode ser feito

manualmente ou através de um campo calculado. Por agora, faremos no estilo campo calculado.

Mais para o final, mostro o motivo para se escolher esta opção ao invés de cálculo externo:

7. Clique em um ponto qualquer da TD e repita o passo 4 acima para adicionar um novo

campo. Adicione um campo chamado PctR1:

Figura ‎9-9 Novo campo adicionado à área de dados

8. Repita o passo 7 para as opções R2 a R6;

9. Adicione os novos campos (PctR1 a PctR6) à área de dados;

10. Formate esta área para percentual mostrando duas casas decimais.

O leitor agora terá a seguinte tabulação das perguntas e devidas respostas:

Page 101: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

93

Desenvolvimento inteligente

Figura ‎9-10 Resultado final da tabulação12

Terminada esta parte, nós teremos a tabulação por cada pergunta e opção de resposta no estilo

tabular.

Podemos agora utilizar tais resultados em gráficos ou fazer um sumário das respostas dadas.

Vejamos agora um segundo cenário em cima da mesma pesquisa, mas utilizando um consulta

diferente no banco de dados.

9.2. Tabulando e analisando dados de uma pesquisa de opinião: cenário 2 – tabulação por cidade

No cenário anterior fizemos a tabulação global, isto é, não nos preocupamos em “quebrar” mais os

dados. Porém, pesquisas são feitas assim: determinadas por sexo, idade, localidade, etc. Todas

estas categorias são eventualmente analisadas individualmente para comparação com a análise

global.

Todas estas comparações são relevantes em uma pesquisa. O intuito deste exemplo não é

comparar com o anterior, mas apenas fazer uma nova análise a qual introduz o elemento “cidade”.

Figura ‎9-11 Dados para tabulação

12

Caso o relatório esteja exibindo “Soma de R1”, etc, acesso o modo clássico de AutoFormato pressionando Alt → F A. Depois, selecione um dos formatos de relatório.

Page 102: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

94

Desenvolvimento inteligente

A figura abaixo mostra o nosso objetivo intermediário. Aqui temos apenas o “hole count”

(contagem de respostas) para cada cidade em questão. O formato final é similar ao anterior em

visual, mas diferente na parte estrutural:

Figura ‎9-12 Formato intermediário da TD

Após a adição dos campos, obter o resultado acima é relativamente simples. Porém, veremos

como o Excel “pivota” a área de dados para mostrar as opções como colunas (campos).

Siga os passos abaixo para montar a parte inicial:

1. Inicie criando a sua TD (Clique na célula A1 da planilha de dados e em seguida vá até

Inserir Tabela dinâmica);

2. Adicione o campo Cidade à área de linhas;

3. Adicione o campo Pergunta à área de linhas;

4. Adicione os campos de opções R1 a R6 à área de dados.

Neste ponto, nós teremos uma TD como segue:

Figura ‎9-13 Primeiro passo completo

Page 103: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

95

Desenvolvimento inteligente

Ao terminarmos o primeiro passo nós teremos os dados em linha, o que causa uma expansão

exagerada da TD para baixo. Se o leitor deseja formatar a TD manualmente, nós precisamos

modificar a disposição dos dados (das opções de resposta). Para tanto, continue como segue:

5. Clique sobre o rótulo Data (“dados” em inglês) o qual representa o campo de dados e os

seis campos das opções e segure o botão do mouse;

6. Usando o modo clássico de exibição, arraste este campo para cima da coluna Total

conforme mostra a figura e solte o botão do mouse;

Figura ‎9-14 Rearranjando o somatório

Note que a pequena figura do mouse mostra a área sendo trabalhada (área de dados em azul).

Uma vez que tenha soltado o campo a TD será redimensionada como segue:

Figura ‎9-15 Resultado do rearranjo

Esta é forma que o Excel utiliza para “pivotar” os dados quando escolhemos uma opção de

formatação automática.

Como o exemplo acima se refere a um formato de relatório em cascata, poderíamos

simplesmente escolher uma das autoformatações disponíveis para rapidamente “pivotar” os

dados.

Page 104: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

96

Desenvolvimento inteligente

A figura abaixo mostra o mesmo resultado acima utilizando o modelo de autoformatação para

relatório 2 (para acessar o modo clássico de AutoFormatação pressione Alt F A):

Figura ‎9-16 “Pivotando” através da autoformatação

Estamos prontos agora para adicionar campos calculados e fazer as comparações conforme

ensinado antes. Caso o leitor queira, é possível pivotar este resultado para o resultado do tópico

anterior. Ao arrastarmos (modo clássico de exibição) o campo Cidade para a área de filtro:

Figura ‎9-17 Pivotando a cidade para a área de filtro

Com os dados na área de filtro nós teremos os totais para as perguntas na área de dados e

podemos rapidamente filtrar o resultado por cidade, evitando, assim, a criação de uma TD extra (a

Page 105: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

97

Desenvolvimento inteligente

TD criada no tópico anterior a este). Nós temos em uma única tabela os dois cenários faltando

apenas adicionarmos os campos calculados do tópico anterior:

Figura ‎9-18 O campo Cidade pivotado para a área de filtro

Ao escolhermos uma cidade, nós obtemos rapidamente a análise para a mesma, facilitando a

comparação com o total:

Figura ‎9-19 Filtro por cidade

O leitor pode agora adicionar os campos calculados e efetuar as comparações conforme desejar.

Page 106: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

98

Desenvolvimento inteligente

10. Tabelas dinâmicas e VBA

Este curso estaria incompleto se não investíssemos parte de nosso tempo no VBA. Enquanto que

a utilização de uma tabela dinâmica não tem segredo algum e o uso de VBA não seja necessário,

existem exceções.

As exceções geralmente ocorrem quando estamos preparando alguma tarefa auto-executável

para um cliente ou para os funcionários da empresa. Neste caso, tais funcionários ou não

possuem o conhecimento ou não tem o tempo para reproduzir a criação de uma TD e precisam

fazê-lo com um clique do mouse.

É neste momento que VBA se torna imprescindível, pois ele nos possibilita maximizar a criação de

TDs quando a tarefa é repetitiva.

Além disso, podemos utilizar objetos de uma TD apenas para extrair certos resumos para uso em

um relatório no Word ou PowerPoint e não queremos perder tempo repetindo a mesma tarefa.

Independentemente de qual seja o uso, acredito que seja de máxima importância estarmos por

dentro de como utilizar o VBA em cima de TDs. E este tópico é exatamente isso.

10.1. Gravando uma macro

Uma das melhores formas de identificação de propriedades de um objeto qualquer é através da

gravação de uma macro. No caso de uma TD isso não é diferente. Vamos iniciar com uma

gravação.

Para gravar uma macro é bastante simples, vá até a guia Desenvolvedor grupo Código

Gravar macro:

Page 107: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

99

Desenvolvimento inteligente

Figura ‎10-1 Janela de definição dos atributos de uma macro

Com a janela de gravação de macros aberta, digite o nome de sua macro, determine se há ou não

uma tecla de atalho, determine o escopo da macro (onde ele deve ser armazenada) e coloque

uma descrição para a mesma.

Muitos usuários de Excel não sabem que é possível visualizar em tempo real a gravação da

macro. Para tanto, abra o VBE antes de iniciar a gravação pressionando Alt+F11. Coloque a

janela do Excel ao lado da janela do VBE conforme mostra a figura:

Figura ‎10-2 Visualizando em tempo real a gravação de macros

Page 108: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

100

Desenvolvimento inteligente

Neste ponto, qualquer modificação efetuada na planilha será imediatamente refletida na janela do

VBE.

Com relação à gravação de macros é basicamente isso que o leitor precisa saber. Uma vez que a

macro tenha sido gravado, o próximo passo é a limpeza de linhas desnecessárias. Note na figura

acima que a rolação da janela também foi gravada, contudo tal comando é irrelevante para o

trabalho em uma TD e o mesmo deve ser removido.

Não entrarei em detalhes sobre limpeza de gravação de macro, pois a limpeza é bastante simples

e deve seguir a lógica e objetivo da macro. Portanto, qualquer tentativa feita aqui apenas se

referiria ao exemplo em questão.

A construção da TD é feita passo-a-passo para que cada elemento seja compreendido

separadamente. Vejamos agora como programar de verdade a nossa TD.

10.2. Adicionando uma nova tabela dinâmica

Através da gravação de uma macro, já deve ser óbvio para o leitor como criar uma tabela

dinâmica via VBA.

Neste exemplo simples utilizaremos um nome dinâmico para determinar a área de dados que

utilizaremos em nossa TD. Se o leitor pulou esta parte do curso, ela deve ser lida antes de

continuar.

Primeiramente, nós desejamos que a TD seja criada na própria pasta de trabalho. Por padrão,

utilizaremos uma nova planilha:

Sub adicionarTD()

Dim wb As Workbook

Set wb = ThisWorkbook

With wb

.PivotCaches.Add(xlDatabase, wb.Sheets("Dados").Range("Dados")). _

CreatePivotTable TableDestination:="", _

TableName:="Minha TD via VBA", _

Defaultversion:=xlPivotTableVersion10

End With

Set wb = Nothing

End Sub

Page 109: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

101

Desenvolvimento inteligente

Neste exemplo, tudo que fazemos é adicionar uma nova TD à pasta de trabalho atual. Porém,

pode haver cenários onde desejamos criar a TD em uma nova pasta de trabalho.

Porém, um erro de execução comum durante este processo é mostrado abaixo:

Figura ‎10-3 Erro em tempo de execução para criação de TD em uma pasta nova

No sub-tópico abaixo, veremos como corrigir tal erro.

10.2.1. Adicionando uma nova tabela dinâmica em uma nova pasta de trabalho

Caso quiséssemos criar uma TD utilizando VBA, o código mais óbvio que utilizaríamos é mostrado

abaixo:

Sub adicionarTD_em_outra_pasta()

Dim wb As Workbook

Dim wbInput As Workbook

Set wb = Application.Workbooks.Add

Set wbInput = ThisWorkbook

With wb

.PivotCaches.Add(xlDatabase, wbInput.Sheets("Dados").Range("Dados")).

_

CreatePivotTable TableDestination:="", _

TableName:="Minha TD via VBA", _

Defaultversion:=xlPivotTableVersion10

End With

Set wb = Nothing

Set wbInput = Nothing

End Sub

Estruturalmente, não há nada de errado com o código, porém ao atingir o bloco With ocorre o erro

mostrado anteriormente.

Page 110: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

102

Desenvolvimento inteligente

Mas antes de entrar no motivo e na solução vejamos o que o código acima faz:

Possuímos dois objetos Workbook. Um objeto é utilizado como a nova pasta que receberá

a TD e o outro é utilizado como a pasta que contém os dados;

O objeto wb é “setado” como uma nova pasta;

O objeto wbInput é “setado” como o objeto que contém a macro e os dados;

No bloco With adicionamos o PivotCache e determinamos a fonte de dados para o

PivotCache (ou Cache se preferir).

Mas o que está ocorrendo no código que gera tal erro? O problema que temos em mão é que há

uma leve demora entre a adição da nova pasta e o tempo de execução do PivotCache. Neste

pequeno intervalo a pasta não está pronta, mas o PivotCache entra na execução gerando o

erro13.

Uma forma de resolver tal questão é simplesmente adicionar uma pequena pausa na execução do

código para termos certeza que o objeto já se encontra na memória e pronto para receber o

PivotCache.

O código revisado abaixo mostra como isso é feito:

Sub adicionarTD_em_outra_pasta()

Dim wb As Workbook

Dim wbInput As Workbook

Set wb = Application.Workbooks.Add

Set wbInput = ThisWorkbook

Application.Wait (Now + TimeValue("00:00:01"))

With wb

.PivotCaches.Add(xlDatabase, wbInput.Sheets("Dados").Range("Dados")).

_

CreatePivotTable TableDestination:="", _

TableName:="Minha TD via VBA", _

Defaultversion:=xlPivotTableVersion10

End With

Set wb = Nothing

Set wbInput = Nothing

End Sub

13

Podemos considerer um bug tendo em vista que tal efeito não deveria efetivamente ocorrer.

Page 111: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

103

Desenvolvimento inteligente

Ao adicionarmos a linha Application.Wait (Now + TimeValue("00:00:01")) nós

adicionamos uma pausa de um segundo na execução do código. Este um segundo é o suficiente

para evitar o erro de execução mostrado anteriormente.

Além deste detalhe, este erro também pode ocorrer caso já exista um TD com o mesmo nome

ativa ou haja um PivotCache na memória.

10.3. Adicionando uma nova tabela dinâmica utilizando os objetos PivotTable e PivotCache

Nos exemplos anteriores nós adicionamos uma tabela dinâmica diretamente a uma planilha ou

pasta nova. Enquanto que não há nada de errado com este método, ele nos deixa com um

pequeno problema em mãos: ele não nos fornece os objetos PivotTable (Tabela Dinâmica) e

PivotCache para manipulação.

Este “pequeno detalhe” pode nos custar caro, principalmente se tudo que desejamos é retornar

informações contidas nos objetos ao invés dos objetos em si.

O código abaixo executa tal operação. Explicações acompanham o código:

Sub adicionarTD_com_PivotTable_e_PivotCache()

' Declaração dos objetos a serem utilizados

Dim wb As Workbook

Dim ptTbl As PivotTable

Dim ptCache As PivotCache

Dim ptRng As Range

' Instanciamento do objeto de destino (esta pasta de trabalho)

' e da área que contém os dados (nome dinâmico "Dados")

Set wb = ThisWorkbook

Set ptRng = Range("Dados")

' Instanciamento dos objetos PivotCache e PivotTable

' Note que a criação ocorre neste momento.

Set ptCache = wb.PivotCaches.Add(xlDatabase, ptRng)

Set ptTbl = ptCache.CreatePivotTable(TableDestination:="", _

TableName:="TD1", Defaultversion:=xlPivotTableVersion10)

' É possível instanciar a Tabela Dinâmica como segue, porém o

' visual será diferente, isto é, as áreas indicativas ficarão

' ocultas.

' Set ptTbl = ptCache.CreatePivotTable("", "TD1")

Page 112: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

104

Desenvolvimento inteligente

' Limpa os objetos da memória

Set wb = Nothing

Set ptRng = Nothing

Set ptCache = Nothing

Set ptTbl = Nothing

End Sub

Ao executar o código acima a área será preparada como segue:

Figura ‎10-4 Área da TD inserida via VBA

Caso o leitor decida pelo segundo método de instanciamento mostrado no código o resultado da

TD será como segue:

Figura ‎10-5 TD sem determinação de destino e versão

Embora o espaço visual seja diferente a funcionalidade é a mesma que a do anterior. Agora que já

temos a TD definida, vejamos como adicionar campos às diferentes áreas de nossa TD.

10.4. Adicionando campos a TD usando VBA: orientação de campos

Este tópico assume que já possuímos um TD em uma determinada planilha e tudo que desejamos

fazer é adicionar os campos em suas determinadas áreas. Este código pode ser juntado ao

anterior para criar todo o processo em um único.

Quando adicionamos os campos, utilizaremos uma das seguintes orientações para os campos:

Page 113: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

105

Desenvolvimento inteligente

xlColumField Adiciona o campo à área de coluna;

xlDataField Adiciona o campo à área de dados;

xlHidden Utilizado para excluir um campo da TD;

xlPageField Adiciona o campo à área de filtro (área de página);

xlRowField Adiciona o campo à área de linha.

A propriedade Orientation determina a orientação do campo dentro de uma TD recebendo como

valor uma das opções acima. Uma outra propriedade que nos interessa é Position. A

propriedade Position determina a posição de um campo em uma determinada área. Por

exemplo:

Figura ‎10-6 Posição de campos dentro de uma determinada área

Na figura acima possuímos dois campos na área de linha (Cidade e Categoria). O campo Cidade

encontra-se na primeira posição ao passo que o campo Categoria encontra-se na segunda

posição.

Além do posicionamento na área de filtro, linha e coluna, nós precisamos nos atentar a área de

dados. Assim como nas outras áreas onde podemos adicionar mais de um campo, nós podemos

adicionar um mesmo campo várias vezes na área de dados.

A diferença é que utilizamos o objeto relativo ao campo (PivotField). O campo pode ser o mesmo,

porém o rótulo do campo deve ser diferente. Na figura anterior, o rótulo é Soma de Total.

O código abaixo mostra como executar tal procedimento. Explicações acompanham o código:

Sub adicionarCampo()

' Declaração dos objetos

Dim ptTbl As PivotTable

Dim ptFld As PivotField

Page 114: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

106

Desenvolvimento inteligente

' Instanciando qual tabela dinâmica utilizar. Assume que já

' existe um TD na planilha de nome "TD" cujo nome é "TD1"

Set ptTbl = ThisWorkbook.Sheets("TD").PivotTables("TD1")

' Com a TD instanciada

With ptTbl

' "Setar" o nome do campo a ser utilizado.

Set ptFld = .PivotFields("Região")

' Com o campo "setado"...

With ptFld

' ... orientar o campo para a área de filtro (área de página)

.Orientation = xlPageField

' ... determinar a posição do campo (somente necessário se

houver

' mais de um campo na área de filtro. É inserido aqui para

' referência)

.Position = 1

' ... Determinar qual campo será usado como filtro.

' Neste caso, filtraremos os dados para o "Sudeste"

.CurrentPage = "Sudeste"

End With

' Remove o objeto da memória para reutilizarmos o objeto

Set ptFld = Nothing

' O mesmo que o campo anterior.

Set ptFld = .PivotFields("Cidade")

With ptFld

.Orientation = xlRowField

.Position = 1

End With

Set ptFld = Nothing

' O mesmo que o campo anterior, porém o campo "Categoria" ficará

' na segunda posição na área de linha

Set ptFld = .PivotFields("Categoria")

With ptFld

.Orientation = xlRowField

.Position = 2

End With

Set ptFld = Nothing

' "Seta" o campo que utilizaremos na área de dados

Set ptFld = .PivotFields("Total")

' Adicionar o campo, como um objeto, à área de dados.

' Define o tipo de sumário como sendo soma utilizando a função

' xlSum

.AddDataField ptFld, "Soma do Total", xlSum

Page 115: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

107

Desenvolvimento inteligente

Set ptFld = Nothing

End With

Set ptTbl = Nothing

End Sub

O nosso próximo passo é remover o campo de uma TD.

10.4.1. Removendo campos de uma TD

Para remover um campo é bastante simples. Na verdade, é tão simples que o leitor poderia gravar

uma macro removendo um campo para descobrir como isso é feito.

Já foram apresentadas as diversas orientações de campos dentro de uma TD. Uma das

orientações é xlHidden.

Seguindo a linha de raciocínio do exemplo anterior, nós podemos resumir a exclusão como segue:

Sub excluirCampo()

Dim ptTbl As PivotTable

Dim ptFld As PivotField

Set ptTbl = ThisWorkbook.Sheets("TD").PivotTables("TD1")

Set ptFld = ptTbl.PivotFields("Categoria")

ptFld.Orientation = xlHidden

Set ptTbl = Nothing

Set ptFld = Nothing

End Sub

Como podemos ver, remover um campo é bastante simples. Vejamos agora como adicionar

campos utilizando uma matriz.

10.4.2. Adicionando campos via matriz (Array)

Quando adicionamos os campos anteriormente tivemos o trabalho de especificar cada campo

individualmente, porém além de trabalhoso é desnecessário.

Um método melhor é utilizar uma matriz juntamente com o método AddFields do objeto

PivotTable.

Sub campoPorMatriz()

Dim ptTbl As PivotTable

Dim ptCache As PivotCache

Page 116: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

108

Desenvolvimento inteligente

Dim ptRng As Range

Set ptRng = Range("dados")

Set ptCache = ThisWorkbook.PivotCaches.Add(xlDatabase, ptRng)

Set ptTbl = ptCache.CreatePivotTable(tabledestination:="", _

Tablename:="TD2", defaultversion:=xlPivotTableVersion10)

With ptTbl

.AddFields RowFields:=Array("Cidade", "Categoria")

' .AddFields RowFields:=Array("Cidade", "Categoria"),

ColumnFields:="Região"

.PivotFields("Total").Orientation = xlDataField

End With

Set ptRng = Nothing

Set ptCache = Nothing

Set ptTbl = Nothing

End Sub

No exemplo acima, temos a matriz para inserção dos campos na área de linha (Array("Cidade",

"Categoria")) além da inserção do campo Total na área de dados sem a necessidade de

utilizarmos o método AddDataField e o objeto relativo ao campo. No mesmo método, podemos

adicionar campos de categoria no mesmo esquema que os campos de linha.

10.5. Copiando valores de uma TD e colando como valores fixos e desconectados

Pode ocorrer cenários onde o leitor deseja apenas os dados de uma TD e não a TD em si. Em

cenários como estes, nós estamos interessados na utilização do sumário retornado pela TD em

um relatório no Word, apresentação do PowerPoint ou até mesmo para utilização em cálculos no

Excel, para evitar problemas com referências absolutas (ou não, dependendo do cenário) no

calculo direto na TD, além de nos livrarmos do PivotCache o qual certamente consumirá tamanho

do arquivo.

Vejamos como efetuar tal cópia. Explicações acompanham o código:

Sub calculoManualAutomatico()

' Declaração dos objetos e variáveis

Dim ptTbl As PivotTable

Dim ptCache As PivotCache

Dim ws As Worksheet

Dim lngLin As Long

' Desabilita a atualização de tela para melhorar o desempenho

Application.ScreenUpdating = False

' Cria o PivotCache

Page 117: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

109

Desenvolvimento inteligente

Set ptCache = ThisWorkbook.PivotCaches.Add(xlDatabase, Range("dados"))

' Cria a Tabela Dinâmica

Set ptTbl = ptCache.CreatePivotTable(Tabledestination:="", _

TableName:="TD1", Defaultversion:=xlPivotTableVersionCurrent)

' Com a TD...

With ptTbl

' Adicionar os campos de linha, coluna e filtro (página)

.AddFields RowFields:="Cidade", _

ColumnFields:="Categoria", PageFields:="Região"

' Adicionar o campo de dados

.PivotFields("Total").Orientation = xlDataField

' Filtrar a região

.PivotFields("Região").CurrentPage = "Sudeste"

' Copiar a TD

.TableRange2.Copy

' Instanciar o objeto "ws" como sendo a planilha ativa

Set ws = ActiveSheet

' Com a planilha

With ws

' Determinar a última linha com dados e somar 5 a ela

lngLin = .Cells(Application.Rows.Count, 1).End(xlUp).Row + 5

' Colar especial o que foi copiado da TD

.Cells(lngLin, 1).PasteSpecial xlPasteValuesAndNumberFormats

End With

' Limpar a TD da área que ocupa atualmente

.TableRange2.Clear

' Com a planilha...

With ws

' Remover linhas e selecionar célula A1

.Rows("1:" & lngLin - 1).Delete Shift:=xlUp

.Cells(1, 1).Select

End With

End With

' Reativar a atualização de tela

Application.ScreenUpdating = True

' Limpar objetos

Set ws = Nothing

Set ptTbl = Nothing

Set ptCache = Nothing

End Sub

Page 118: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

110

Desenvolvimento inteligente

10.6. Cálculo manual ou automático?

Uma questão importante em relação ao uso de VBA na criação de tabelas dinâmicas diz respeito

ao cálculo automático.

Quando adicionamos itens em um local qualquer de nossa planilha via VBA o item aparece

instantaneamente no local determinado. Infelizmente, isso reduz a velocidade de processando do

que estamos fazendo. A solução utilizada é determinar a propriedade ScreenUpdating do

aplicativo para False (Falso) enquanto executamos o código e no final da execução retornamos

para True (Verdadeiro).

Em uma tabela dinâmica o mesmo ocorre, porém nós utilizamos a propriedade ManualUpdate

para determinar a atualização da TD e melhorar o desempenho do código.

Sub calculoManualAutomatico()

' Declaração dos objetos

Dim ptTbl As PivotTable

Dim ptCache As PivotCache

Dim ptFld As PivotField

Set ptCache = ThisWorkbook.PivotCaches.Add(xlDatabase, Range("dados"))

Set ptTbl = ptCache.CreatePivotTable(Tabledestination:="", _

TableName:="TD1", Defaultversion:=xlPivotTableVersionCurrent)

With ptTbl

' Determina atualização manual

.ManualUpdate = True

' .

' .

' .

' Seu código entra aqui

' .

' .

' .

' Determina atualização automática

.ManualUpdate = False

End With

Set ptTbl = Nothing

End Sub

Efetuando a criação da TD nestes moldes, o resultado final será bem mais rápido.

Page 119: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

111

Desenvolvimento inteligente

11. Problemas em potencial

Este último tópico é dedicado à alguns problemas que o leitor poderá encontrar no caminho da

construção de uma tabela dinâmica perfeita.

Muitos usuários não conseguem criar uma tabela dinâmica de impacto por não conhecerem os

dados com os quais trabalha e/ou por não compreender como uma TD analisa e agrupa as

informações. Ao chegar neste tópico acredito que o leitor terá uma visão completa destes pontos

que eu considero básicos, não obstante ainda poderão ocorrer cenários onde o leitor precisará

usar de bom-senso, conhecimento dos dados (e caso não conheça precisará estudá-los) e do

conhecimento adquirido neste curso para rearranjar as informações de modo que consiga o

resultado desejado.

Neste tópico discutirei tais problemas e darei sugestões de como proceder. Lembre-se apenas

que cada caso é um caso. Portanto, use as dicas como guias para solucionar os seus problemas

e não como soluções propriamente ditas.

11.1. Campos nulos (vazios) e em brancos: qual a diferença e como determiná-la?

Um problema comum em tabelas dinâmicas diz respeito a campos nulos. Primeiramente, nós

precisamos distinguir um campo nulo de um campo em branco. A figura abaixo mostra um cenário

simples:

Figura ‎11-1 Nulo ou em branco?

O campo acima é nulo ou está em branco? Apenas olhando a figura não é possível determinar se

o campo é nulo ou se está vazio, porém, ao criamos a nossa tabela dinâmica a diferença

aparecerá:

Page 120: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

112

Desenvolvimento inteligente

Figura ‎11-2 Campos nulos e em branco

Note que quando o campo aparece com a palavra “vazio” ele é, na verdade, nulo. Por outro lado,

quando ele aparece em branco, ele é realmente em branco e não nulo.

Mas como distinguir “em branco” e “nulo” (vazio)? Via de regra, se nada foi feito no campo, ele é

por padrão “nulo”. Por outro lado, se algo foi digitado nele (mesmo que seja uma string em branco)

ele não é nulo – ele é “em branco”.

O leitor poderia criar a seguinte função em VBA para avaliar se o campo é nulo ao não:

Function éNulo(ByVal célula As Range) As Boolean

éNulo = IsNull(célula)

End Function

Porém, ao testar o campo, o resultado será:

Figura ‎11-3 Função para verificação de nulo

Infelizmente, este não é o melhor método para testar se um campo é ou não nulo. No estrito

senso de um banco de dados o campo é nulo e, portanto, precisamos utilizar objetos de um banco

de dados para determinar se um campo é ou não “nulo”.

A figura abaixo mostra o resultado de um loop pelo campo “Letra” para determinar se o valor de

um registro deste campo é ou não nulo:

Page 121: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

113

Desenvolvimento inteligente

Figura ‎11-4 Verificando nulos

Como podemos ver, aqui é registrado corretamente o “status” do campo. O valor “em branco” é,

na verdade, nulo. Por outro lado, ao adicionar um espaço (o qual é “invisível”) e rodarmos

novamente o código o resultado agora já não é mais nulo:

Figura ‎11-5 Verificando nulos

“OK”, você deve estar dizendo, “qual o ponto disso tudo?”.

O ponto que desejo enfatizar é que o leitor precisa estar ciente de tal sutileza para determinar se o

valor é “nulo” (mostrado como “vazio” na TD) porque não há tal informação ou se ele é “em

branco” (mostrado sem texto algum) porque há um “espaço” no registro quando deveria ter, na

verdade, um outro valor.

Isso é crucial! Apenas lembre-se do cenário de eleições: - você é capaz de dizer a diferença entre

“voto em branco” e “voto nulo”? Agora, coloque no contexto de uma TD. Imagine você tabulando

“votações em branco” como se fossem “votações nulas”? Acredito que seja desnecessário dizer

que seria um desastre, pois embora pareçam a mesma coisa, ambos são entidades

completamente distintas!

Page 122: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

114

Desenvolvimento inteligente

Portanto, mais uma vez, vale a pena lembrar a importância de conhecer os dados. Se existe um

campo com algum registro nulo quando você esperava algo diferente, verifique imediatamente a

integridade dos dados para evitar problemas na tabulação.

Finalmente, abaixo se encontra o código que utilizei para determinar se o valor é ou não nulo:

Sub checarNulos()

Dim db As DAO.Database

Dim rs As DAO.Recordset

Set db = OpenDatabase(ThisWorkbook.FullName, False, False, "Excel 8.0")

Set rs = db.OpenRecordset("SELECT * FROM [Dados$]", dbOpenSnapshot)

rs.MoveFirst

Do While Not rs.EOF

Debug.Print "Valor: " & rs.Fields("Letra").Value _

& vbCr & "É nulo?: " & IsNull(rs.Fields("Letra").Value)

Debug.Print vbCr

rs.MoveNext

Loop

rs.Close

db.Close

Set rs = Nothing

Set db = Nothing

End Sub

O leitor pode agora tomar uma decisão mais acertada em relação ao cenário aqui apresentado.

11.2. Descontinuidade nos dados

Seguindo a linha de raciocínio do exemplo anterior, imagine o cenário onde há descontinuidade

nos dados:

Page 123: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

115

Desenvolvimento inteligente

Figura ‎11-6 Dados em formato de relatório

A planilha acima não tem nada de errado, desde que o desejo seja subtotalizar por cômodo.

Porém, note que a informação acima veio de um banco de dados para o Excel o mais provável é

que ela seja de um relatório. Deste modo, o cômodo nada mais é do que um campo em uma outra

tabela (ou na mesma tabela). Na verdade, na própria planilha podemos ver na coluna A o nome

do campo.

O problema que obviamente temos é a quebra na continuidade e a não repetição do dado no

respectivo campo. Observe que a repetição no Excel é necessária, mas em um banco de dados

isso não é. Em banco de dados, a forma correta é relacionar os campos para evitar duplicação de

informações. Este é o motivo para os dados acima fiquem neste formato em um relatório.

Ao chegar neste ponto, o leitor já teve a resposta óbvia: limpar os dados. A primeira coisa que

precisamos fazer é remover as linhas vazias para dar continuidade à lista.

Figura ‎11-7 Dados limpos e prontos para inserção em uma tabela dinâmica

Page 124: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

116

Desenvolvimento inteligente

Com os dados no formato acima, nós estamos prontos para criar a nossa TD, pois eles estão no

formato apropriado. No exemplo abaixo, faço uma simples contagem de quantos produtos estão

destinados a cada cômodo:

Figura ‎11-8 Exemplo simples de uma TD usando os dados limpos

11.3. A função INFODADOSTABELADINÂMICA

A função INFODADOSTABELADINÂMICA tem sua utilidade, porém, pode haver situações onde ela

atrapalha mais do que ajuda. Observe a figura abaixo:

Figura ‎11-9 A função INFODADOSTABELADINÂMICA

Note que a referência à célula é absoluta ($A$3) para o campo que desejamos extrair o valor.

Embora tal referência nos ajude a manter certa integridade dos dados, muitas vezes ela atrapalha.

Caso o leitor queira arrastar a fórmula ou ocorra alguma mudança estrutural na tabela dinâmica, o

resultado final do cálculo manual pode soletrar “d-e-s-a-s-t-r-e”.

Page 125: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

117

Desenvolvimento inteligente

A solução do “problema” é desativar a inserção automática de tal função. A forma mais fácil é

simplesmente:

Acessar as opções do Excel (Botão do Office)

Fórmulas Usar funções GetPivotData para referências de Tabela Dinâmica

Page 126: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

118

Desenvolvimento inteligente

12. Sobre o autor

FORMAÇÃO ACADÊMICA E ESPECIALIZAÇÕES:

Formado e Pós-Graduado em Finanças pela Universidade de Londres, Reino Unido.

Membro da Sociedade Brasileira de Econometria

Microsoft Office Specialist Expert, Microsoft Certified Professional

LINGUAGENS DE PROGRAMAÇÃO E PLATAFORMAS:

Visual Basic, Calculadores Programáveis Casio e Sharp

BDs: MS Access and Lotus Approach

Plataformas: Windows NT, 2000, XP, Vista, Linux Red Hat

EXPERIÊNCIA PROFISSIONAL

abr04- CONSULTOR DE TI INDEPENDENTE

out02-abr04 FAIRCOURT CAPITAL LIMITED (REINO UNIDO)

Diretor TI

fev96-maio02 MELVALE GROUP (REINO UNIDO)

Gerente de Exportação para a África Ocidental

Gerente de TI

OUTRAS ESPECIALIZAÇÕES

Inspeção e regulamentações Nigerianas para importação e exportação

(Nigerian-British Chamber of Commerce & Cotecna International)

Procedimentos de exportação no Reino Unido (The Institute of Export, Reino Unido)

ICC 500 e Incoterms (The Institute of Export, Reino Unido)

OUTRAS ATIVIDADES

Fornece suporte pro bono em TI à entidade de caridade Nigeriana NIDOE (Nigerians in Diaspora Organisation Europe) desde 2001. Participou ativamente na organização da conferência sobre Boa Governança e Responsabilidade Fiscal promovida pelo ONG em Abuja, Nigéria, em novembro de 2003. Foi um dos principais colaboradores na elaboração do relatório final sobre a conferência entregue a presidência da República Nigeriana em maio de 2004.

Autor do livro Excel e VBA na Modelagem Financeira: Uma abordagem prática. Editora Axcel Books, 2005. www.axcel.com.br

Autor principal do livro RibbonX: Customizing the Office 2007 Ribbon, John Wiley & Sons Publishing, 2008.

Page 127: SÉRIES COMO FAZER - Excel 2007 - Tabelas Dinâmicas

Série “Como Fazer” – Excel 2007: Tabelas Dinâmicas

Autor: Robert F. Martim Criado em: 09/04/2009

Publicado: www.juliobattisti.com.br Última edição: 2/7/2009

Contato: [email protected]

119

Desenvolvimento inteligente

Autor do livro: Excel 2007 Avançado, Editora Universo dos Livros, 2008.

Colaborador ativo do fórum Excel Avançado do site www.juliobattisti.com.br, onde divide seu conhecimento e experiência com outros membros do espaço. Webmaster do site www.msofficegurus.com

Colunista dos sites www.linhadecodigo.com.br