52037118 SERIES COMO FAZER Excel 2007 Tabelas Dinamicas

Srie Como Fazer: Excel 2007

Tabelas Dinmicaspor Robert Friedrick Martim

Autor: Publicado: Contato:

Robert F. Martim

www.juliobattisti.com.br [email protected]

Criado em: ltima edio:

09/04/2009 2/7/2009

Srie Como Fazer Excel 2007: Tabelas Dinmicas


Robert F. Martim



09/04/2009 2/7/2009


i

Desenvolvimento inteligente

Nota sobre direitos autoraisEste eBook de autoria de Robert F Martim, sendo comercializado atravs do sitewww.juliobattisti.com.br www.mercadolivre.com.br.

ou

atravs

do

site

de

leiles

Mercado

Livre:

Ao adquirir este eBook voc tem o direito de l-lo na tela do seu computador e de imprimir quantas cpias desejar, desde que sejam para uso pessoal. vetada a distribuio deste eBook, mediante cpia ou quaisquer outros meios de reproduo, para outras pessoas. Se voc recebeu este eBook atravs de e-mail ou via FTP de algum site da Internet, ou atravs de CD de Revista, saiba que voc est com uma cpia pirata, no autorizada. Se for este o seu caso entre, em contato com o autor atravs do e-mail [email protected] ou comunique diretamente ao nosso site atravs do e-mail [email protected]. Ao regularizar a sua cpia, 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!


Robert F. Martim



09/04/2009 2/7/2009

ii



Pr-requisitosPara completar este curso, voc precisa conhecer o ambiente de trabalho Excel 2003 e possuir uma cpia do Excel 2007. Uma verso Trial pode ser baixada do site da Microsoft. Neste mdulo discutirei como utilizar os novos recursos da Tabela Dinmica no Excel 2007.


Robert F. Martim



09/04/2009 2/7/2009


iii


Objetivos deste eBookEste mdulo visa introduo dos novos recursos da Tabela Dinmica do Excel 2007. O trabalho foi desenvolvido a partir da demanda dos usurios do site www.juliobattisti.com.br. O material procura analisar questes pertinentes ao dia-a-dia de seu trabalho. A linguagem utilizada descontrada e com o mnimo de jargo possvel. O objetivo ter um ebook com contedo relevante e de fcil compreenso. Quaisquer dvidas referentes a este mdulo podem ser colocadas diretamente no frum Excel no endereo www.juliobattisti.com.br/forum/ Comentrios e sugestes para melhora do material podem ser enviados diretamente para o autor no endereo [email protected]


Robert F. Martim



09/04/2009 2/7/2009

iv



NDICE ANALTICOIntroduo ....................................................................................................................................1 Bem-vindo a srie Como Fazer...........................................................................................1 Antes de continuar ................................................................................................................1 1. Algumas novidades do Excel 2007 ....................................................................................2 1.1. Lista das novidades e seus benefcios..........................................................................4 1.1.1. Faixa de Opes .............................................................................................. 4 1.1.2. Mais linhas e colunas ....................................................................................... 4 1.1.3. Ordenao simultnea de campos ................................................................... 4 1.1.4. Nova formatao condicional ........................................................................... 5 1.1.5. Novas funes.................................................................................................. 5 1.1.6. Visualizao Dinmica ..................................................................................... 5 1.1.7. Layout da Pgina ............................................................................................. 5 1.1.8. Estilos de formatao ....................................................................................... 5 2. Compreendendo Tabelas Dinmicas .................................................................................7 2.1. TDs, Tabelas e matriciais: como obter resultados iguais ............................................ 12 2.2. Atualizacao de dados ................................................................................................. 16 3. Tabelas dinmicas no Excel 2007: o que h de novo? ................................................... 17 3.1. Acessando a ferramenta de tabela dinmica .............................................................. 17 3.2. Ferramentas de Tabela Dinmica: Tabset de Extensibilidade .................................... 18 3.3. Adicionando suas prprias ferramentas ao Tabset de Extensibilidade ....................... 18 3.4. Lista de campos da tabela dinmica ........................................................................... 20 3.5. Acessando as opes da tabela dinmica .................................................................. 21 4. Compreendendo os elementos de uma Tabela Dinmica .............................................. 24 4.1. Elementos de uma Tabela Dinmica .......................................................................... 24 4.2. Onde devo ir para inserir uma tabela dinmica? ......................................................... 27 4.3. Onde foi parar o Assistente de Tabela Dinmica no Excel 2007? ............................... 28 4.4. Opes da tabela dinmica ........................................................................................ 31 4.4.1. 4.4.2. 4.4.3. 4.4.4. 4.4.5.Autor: Publicado: Contato:

Layout e Formato ........................................................................................... 31 Totais e filtros ................................................................................................. 34 Exibio ......................................................................................................... 35 Impresso ...................................................................................................... 37 Dados ............................................................................................................. 37Robert F. Martim



09/04/2009 2/7/2009


v


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 mltiplos campos? ............... 38 4.8. Adicionando campos tabela dinmica: mtodo clssico e novo ............................... 39 4.9. Removendo campos da tabela dinmica .................................................................... 41 5. Compreendendo as fontes de dados para Tabelas Dinmicas ...................................... 42 5.1. Banco de dados ou lista do Microsoft Office Excel ..................................................... 42 5.2. Fonte de dados externos ............................................................................................ 44 No caso anterior, ns utilizamos a fonte externa para alimentar a TD diretamente. ............ 46 5.3. Vrios intervalos de consolidao............................................................................... 46 5.4. Outro relatrio de tabela dinmica ou de grfico dinamico.......................................... 51 6. Formatao ........................................................................................................................ 53 6.1. Estilos de Tabela Dinmica ........................................................................................ 53 6.2. Formatando campo..................................................................................................... 55 6.3. Layout do relatrio de tabela dinmica ....................................................................... 55 6.4. Preservando formatao ............................................................................................ 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 sumrios e subtotalizaes: subtotais simples e mltiplos ............. 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 (comparao relativa) ........................................................ 72 7.5. Trabalhando com campos e itens calculados ............................................................. 74 7.5.1. 7.5.2. 7.5.3. 7.5.4. 7.5.5. O que um campo ou item calculado? .......................................................... 74 Criando um campo e/ou item calculado .......................................................... 77 Editando/Excluindo campos/itens calculados ................................................. 81 Determinando ordem de clculo ..................................................................... 82 Determinando ordem de apresentao .......................................................... 83

7.6. Determinando os Top 10 (os 10 Primeiros) ............................................................ 85 8. 9. Salvando a TD como um documento HTM interativo ..................................................... 87 Tabulando dados de Pesquisas com TDs ....................................................................... 88 9.1. Tabulando e analisando dados de uma pesquisa de opinio: cenrio 1 tabulao global .......................................................................................................................... 88Autor: Publicado: Contato:Robert F. Martim



09/04/2009 2/7/2009

vi



9.2. Tabulando e analisando dados de uma pesquisa de opinio: cenrio 2 tabulao por cidade ......................................................................................................................... 93 10. Tabelas dinmicas e VBA ................................................................................................. 98 10.1. Gravando uma macro ................................................................................................. 98 10.2. Adicionando uma nova tabela dinmica.................................................................... 100 10.2.1. Adicionando uma nova tabela dinmica em uma nova pasta de trabalho ..... 101 10.3. Adicionando uma nova tabela dinmica utilizando os objetos PivotTable e PivotCache 103 10.4. Adicionando campos a TD usando VBA: orientao de campos............................... 104 10.4.1. 10.4.2. Removendo campos de uma TD .................................................................. 107 Adicionando campos via matriz (Array) ........................................................ 107

10.5. Copiando valores de uma TD e colando como valores fixos e desconectados ......... 108 10.6. Clculo manual ou automtico? ................................................................................ 110 11. Problemas em potencial ................................................................................................. 111 11.1. Campos nulos (vazios) e em brancos: qual a diferena e como determin-la? ......... 111 11.2. Descontinuidade nos dados...................................................................................... 114 11.3. A funo INFODADOSTABELADINMICA .............................................................. 116 12. Sobre o autor ................................................................................................................... 118


Robert F. Martim



09/04/2009 2/7/2009


1


SRIES: COMO FAZER Excel 2007

Tabelas Dinmicaspor Robert Friedrick Martim

IntroduoBem-vindo a srie Como Fazer. Nas sries que sero escritas olharemos aspectos distintos do Excel 2007 de acordo com a demanda do site Jlio Battisti (http://www.juliobattisti.com.br). A inteno principal fornecer ao internauta uma ferramenta que concentre a ateno na soluo de um problema especfico. Neste mdulo veremos como utilizar as novas ferramentas de Tabela Dinmica no Excel 2007 e como aplic-las aos mais variados problemas encontrados no nosso dia-a-dia.

Antes de continuar Sugestes sero sempre bem-vindas e esperamos que o leitor participe pro ativamente no desenvolvimento do material aqui apresentado. Finalmente, todo um esforo foi feito para assegurar que o material aqui apresentado est livre de qualquer erro, mas no improvvel evento de encontrar algo errado, por favor, no deixe de me comunicar, pois terei prazer em revisar o material imediatamente.


Robert F. Martim



09/04/2009 2/7/2009

2



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. No obstante, uma vez que voc aprenda a nova interface, voc comear a ver os benefcios que ela nos traz. O primeiro benefcio que a maioria dos comandos agora fica agrupada no RibbonX (Faixa de Opes) e no enterrados em algum local obscuro do Excel. Alm disso, o RibbonX sensvel ao contexto e ir adicionar novas guias dependendo do que voc esteja fazendo. Como o RibbonX ocupa bastante espao, quanto maior for a resoluo de seu monitor melhor ser o benefcio da Faixa de Opes:

Figura -1 Faixa de Opes em baixa resoluo agrupa ainda mais os itens de um grupo 1

A figura acima mostra a Faixa de Opes com uma resoluo menor. Note que o grupo que usaremos (grupo Estilo) compresso e, portanto, no possvel saber o que h nele at que o mesmo seja expandido. Em uma resoluo maior ns temos uma rea bem mais visvel da Faixa de Opes e o grupo que usaremos exposto:

Figura -2 Faixa de Opes em maior resoluo expande grupos 1

Assim sendo, quanto maior for sua resoluo maior ser o benefcio da Faixa de Opes.


Robert F. Martim



09/04/2009 2/7/2009


3


A ferramenta de formatao condicional pode ser rapidamente acessada na guia Incio no grupo Estilo conforme mostra a figura abaixo:

Figura -3 Acessando a ferramenta de formatao condicional 1

Note a pequena seta para baixo no boto de formatao condicional. Este pequeno boto nos permite expandir as opes disponveis no boto. Este boto, na verdade, funciona como os antigos menus popups os quais expem novos clicveis os abrem novos clicveis ou executam algum tipo de comando:

Figura -4 Expandindo a opo de formatao condicional 1


Robert F. Martim



09/04/2009 2/7/2009

4



Antes de continuar, porm, abro um curto subtpico para discutir algumas novas questes do Excel 2007. Estas novas questes iro lhe ajudar a compreender melhor a nova verso.

1.1. Lista das novidades e seus benefcios Vamos das uma rpida olhada nas novidades do Excel 2007 e seus benefcios (ou no benefcios) para os usurios novos e antigos. Para facilitar a identificao, quebro a lista para que a mesma possa ser encontrada a partir do ndice analtico.

1.1.1.

Faixa de Opes

Faixa de Opes (ou RibbonX) uma das primeiras novidades que qualquer usurio notar. A grande vantagem da Faixa de Opes que ele agrupa tarefas comuns e expe os controles que o usurio precisa para resolver um problema qualquer. A Faixa de Opes sensvel ao contexto e a ela so adicionadas novas guias e controles dependendo do trabalho que voc esteja fazendo. A grande desvantagem da Faixa de Opes o espao que ela ocupa. Portanto, quanto maior a resoluo 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 bilhes de clulas 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 nmero de linhas e colunas utilizando o cdigo abaixo:Sub linhasColunas() Cells(1, 1) = ActiveSheet.Rows.Count Cells(1, 2) = ActiveSheet.Columns.Count End Sub

1.1.3.

Ordenao simultnea de campos

Uma reclamao antiga do Excel era a possibilidade de apenas ordenarmos trs campos, no mximo. bem verdade que podemos ordenar mais de trs campos utilizando um mtodo nada ortodoxo e lento, contudo, o novo Excel nos permite ordenar at 64 campos simultaneamente.Autor: Publicado: Contato:Robert F. Martim



09/04/2009 2/7/2009


5


Provavelmente, voc se perder na ordenao l pelo quinto campo e ter que confiar no resultado final, no obstante, a nova possibilidade extremamente bem-vinda.

1.1.4.

Nova formatao condicional

A nova formatao condicional, o assunto deste curso, est show de bola. Para aqueles que amam formatao condicional e as usam para identificar tudo que tipo de coisa no Excel, a nova interface da FC est nota 1000. No princpio 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 funes

O Excel 2007 agora possui novas funes que resolvem muitos dos problemas que antes tiravam o sono de muitos usurios como o caso de soma condicional.

1.1.6.

Visualizao Dinmica

Permite visualizar o resultado de uma formatao antes que ela seja aplicada. Recurso muito til na hora de darmos o toque final ao nosso trabalho.

1.1.7.

Layout da Pgina

Para os usurios de Word que amam o layout de impresso, agora, ficou muito mais fcil formatar a sua planilha com o Layout da Pgina.

1.1.8.

Estilos de formatao

Com os novos estilos de formatao ficou bastante simples dar aquele toque profissional ao seu trabalho no Excel. Basta selecionar um dos vrios estilos da galeria e aplic-lo. Sem contar que com a Visualizao Dinmica voc ver o resultado antes de aplicar o estilo. Estas so apenas algumas das mais variadas novidades do novo Excel. Infelizmente, o espao aqui curto para discutir cada uma delas, mas acredito que os pontos acima dem ao leitor uma viso geral do que esperar da nova verso.Autor: Publicado: Contato:Robert F. Martim



09/04/2009 2/7/2009

6



Feita as introdues vamos ao que interessa: formatao condicional e validao de dados.


Robert F. Martim



09/04/2009 2/7/2009


7


2.

Compreendendo Tabelas Dinmicas

A principal razo para usarmos Tabelas Dinmicas que elas nos permitem analisar grandes quantidades de dados de forma significativa. Tabelas Dinmicas nos permitem trabalham com uma massa de dados que seria indecifrvel ou insignificativa em sua ausncia. Ns podemos, por exemplo, organizar, subtotalizar, agrupar, etc, de forma simples e rpida atravs de Tabelas Dinmicas. Esta organizao, subtotalizao, agrupamento, etc, no se restringe a dados contidos em uma planilha Excel. Uma tabela dinmica pode utilizar dados de banco de dados externo ou consultas pr-definidas. A importncia desta caracterstica 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 no um banco de dados e no deve ser utilizado para esta finalidade. O nome Tabela Dinmica no faz jus ao nome original em ingls: Pivot Table (ou Tabela Pivotante). A razo para este nome em ingls to singela quanto o nome: quando temos um objeto complexo em nossas mos, como um Cubo Mgico (Cubo de Rubik) ns o giramos de vrias formas analisando-o por diversos ngulos. Com uma tabela dinmica, ns fazemos o mesmo; porm, com uma massa de dados ao invs de um cubo. Suponha, por exemplo, que voc responsvel 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 no tem acesso ao Access, ento importante que voc conhea 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 no causem danos ao seu trabalho. Certamente que gravar os dados no resolve outros problemas ou responde outras perguntas importantes sobre o seu estoque. Se voc conhece os dados, voc ser capaz de construir anlises 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 perecveis. Voc poder criar uma anlise comparativa por setor, produto consumido,Autor: Publicado: Contato:Robert F. Martim



09/04/2009 2/7/2009

8



quem solicitou a baixa, com que freqncia algum 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 atravs de uma tabela dinmica. E se voc acha que o exemplo acima complexo para um simples almoxarifado, imagine uma distribuidora com diversos escritrios espalhados pelo pas? Como conciliar e analisar a distribuio, as vendas por regio e por vendedor? Como saber o percentual de vendas de cada regio em relao 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 regies do pas e analisar cada detalhe? Aqui, se voc alguma vez criou uma consulta circular no Excel (uma consulta que retorna dados dentro da prpria pasta que funciona como a fonte de dados), voc chegar concluso que ela ser incapaz de responder tantas perguntas simultaneamente. Voc precisaria de diversas consultas que analisassem cada situao e depois ficar como louco cruzando tais consultas para chegar ao nvel de detalhe que estou falando. Sem contar a redundncia dos dados, pois voc estaria simplesmente filtrando dados dentro da planilha que j contm os dados! J imaginou o caos? Se seu chefe pedisse uma mdia ponderada no cenrio da distribuidora acima, voc provavelmente iria ficar careca tentando resolver o problema num mar de nmeros. Com tabelas dinmicas talvez voc at arranque uma meia dzia de fios de cabelos, mas certamente no ficar careca por causa disso. E como funciona uma tabela dinmica1?

1

Na maioria dos casos abreviarei Tabela Dinmica para TD.


Robert F. Martim



09/04/2009 2/7/2009


9


A figura abaixo mostra uma tabela simples com alguns dados:

Figura -1 Dados de entrada para uma tabela dinmica 2

Os dados acima podem ser sumarizados em uma tabela dinmica da seguinte forma:

Figura -2 Soma da Venda Total para cada produto 2

Com a pequena tabela dinmica acima podemos facilmente ver os totais para as vendas de cada produto sem a necessidade de criarmos frmulas mirabolantes para somar por produto. Contudo, no temos os detalhes de cada venda. Aqui, podemos fazer algo similar ao que geralmente feito em um relatrio.


Robert F. Martim



09/04/2009 2/7/2009

10



Se o nome do produto o cabealho e sob tal cabealho vem listado cada total de cada venda efetuada temos o seguinte cenrio:

Figura -3 Dados no agrupados em uma TD 2

Aqui, temos no cabealho 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 situaes mais complexas, voc precisar criar filtro ou consulta que rena os dados crticos para anlise e utilizar a tabela resultante para criar a sua tabela dinmica no Excel. A traduo do ingls para o portugus talvez no faa jus ao original em ingls PivotTable. Embora a traduo para Tabela Pivotante possa ser feia acredito que ela faa mais jus ao real significado do que uma Tabela Dinmica realmente seja. Pivotante refere-se a algo que gira sobre um ponto fixo e exatamente isso que os dados fazem dentro de uma tabela dinmica. Os dados giram sobre o eixo da tabela e voc pode gir-los vontade sobre tal eixo.


Robert F. Martim



09/04/2009 2/7/2009


11


Esta aparente simplicidade nos fornece resultados extremamente complexos. Alm disso, se os dados em um formato no resultam no sumrio que voc deseja voc pode simplesmente arrastar o campo para outro local dentro da tabela para rearranjar os dados. Em nossa tabela de dados tambm possumos datas. Datas em tabelas dinmicas tm papel crtico, pois com as datas podemos agrupar dados para anlise por perodos. Por exemplo, poderamos comparar as vendas ms a ms. 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 -4 Agrupamento por ms 2

Com o agrupamento por ms podemos rapidamente identificar que o produto A tem uma constncia maior nas vendas. Que em abril o nico produto vendido foi o produto A e novamente em julho. Estes tipos de respostas no podem ser obtidos diretamente do conjunto de dados, pois o resultado unidirecional2. E se voc no gosta da forma como os resultados so apresentados, pela caracterstica pivotante da tabela dinmica, voc pode simplesmente mover um dos campos para outra rea qualquer ou remov-lo da tabela:

2

Embora seja possvel criar uma frmula matricial ou tabela para representar tal cenrio.


Robert F. Martim



09/04/2009 2/7/2009

12



Figura -5 Agrupamento de todos os produtos 2

Em questo de segundos, voc cria outra apresentao dos dados sem esforo algum. Agora, junte a fora de filtros com consultas bem organizadas com tabelas dinmicas, e voc tem uma ferramenta de anlise de dados extremamente poderosa. Ao longo deste curso, voc notar que no existe uma forma correta de se criar uma tabela dinmica. Tabelas dinmicas so usadas para criar sumrios que nos ajudam a interpretar os dados. Como a tabela final feita depender da necessidade individual, do tipo de anlise sendo feita, e principalmente de sua compreenso 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 introduo ao que possvel fazer com tabelas dinmicas. A seguir apresento os componentes de uma tabela dinmica e o que cada um faz antes de entrarmos na real criao das mesmas.

2.1. TDs, Tabelas e matriciais: como obter resultados iguais Mencionei antes que possvel simular um cenrio de uma tabela dinmica utilizando uma frmula 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. No utilize este mtodo para efetuar clculos complexos, pois alm de trabalhoso consome muito mais de processamento do que uma TD.

3

Caso o leitor no tenha conhecimento sobre matriciais, veja o curso no link: http://www.juliobattisti.com.br/cursos/excelmatric/default.asp


Robert F. Martim



09/04/2009 2/7/2009


13


A primeira coisa que precisamos fazer definir nosso problema:

Figura -6 Configurando o clculo em tabela 2

Na clula C25 ns inserimos a frmula do clculo que desejamos efetuar (neste caso a soma por produto). Aqui, utilizo um formato geral similar a TD para facilitar a visualizao. Disponha os nomes dos produtos em uma coluna nica (vrias linhas por uma coluna) e defina o critrio (neste caso defini como sendo A na clula B23). Utilize o critrio na frmula para a soma baseado no critrio 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 Hipteses Tabela de Dados; Na nova janela que abrir, defina a clula de entrada da coluna como sendo B23; Clique OK.

Figura -7 Argumentos da tabela de dados 2

A figura acima mostra a caixa de entrada para a tabela. Aps clicarmos em OK, a tabela ser preenchida com as somas por produto:Autor: Publicado: Contato:Robert F. Martim



09/04/2009 2/7/2009

14



Figura -8 Resultado do clculo de uma Tabela 2

A tabela utiliza dois argumentos: linha e coluna. Neste caso, temos apenas coluna como argumento da tabela. Os resultados so propagados para as demais clulas e obtemos o que procuramos. Podemos tambm adicionar um somatrio sob a tabela para criar o total geral:

Figura -9 Adicionando total geral tabela 2

Como podemos ver, obtemos exatamente o mesmo resultado que obteramos em uma tabela dinmica. Outra forma de efetuar os mesmos clculos seria utilizando uma funo de banco de dados, uma frmula matricial ou SOMASE. Por exemplo, abaixo utilizo a funo SOMASE:


Robert F. Martim



09/04/2009 2/7/2009


15


Figura -10 Utilizando SOMASE 2

Como as reas so nomeadas, basta seleciona o nome do produto e as reas de dados e arrastar a frmula para baixo. Para a frmula matricial complica um pouco, pois o leitor precisa compreender de multiplicao de matrizes. O efetuar tal clculo faramos: Comparar nome do produto com o critrio (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 dimenso 4xn (4 linhas e n colunas); Multiplicaramos esta matriz de zeros e uns pela matriz contendo os valores totais para obtermos o que desejamos:

Figura -11 Utilizando matrizes em frmula matricial 2

Obtemos mais uma vez o mesmo resultado, mas com um nvel de complexidade muito maior. Sem contar que o cenrio aqui simples. Imagine um cenrio mais complexo com colunas, mltiplas linhas, cabealhos, etc?


Robert F. Martim



09/04/2009 2/7/2009

16



Seria o completo caos tentar resolver tais questes. Sem contar com a imprevisibilidade. E se o seu chefe resolve mudar tudo de posio? Portanto, embora seja possvel no nem um pouco recomendvel fazer algo to complexo utilizando mtodos manuais. O que vejo s vezes a argumentao de que uma TD no retorna no formato desejado. Se no retornar, no tem problema. Analise com a TD, depois copie e cole no formato desejado e mesmo assim ainda ser mais rpido, prtico e consumir menos recursos. No se apegue a detalhes que pouco ajudam na soluo do problema. Ataque o problema de frente e resolva-o. Depois, preocupe-se com questes de formatao e apresentao dos resultados.

2.2. Atualizacao de dados As informaes contidas em uma tabela dinmica no so atualizadas automaticamente, isto , caso ocorra uma mudana na fonte de dados esta mudana no ser refletida em sua TD. Este fato independe da localizao dos dados, ou seja, os dados podem estar na pasta de trabalho ou um servidor SQL remoto d no mesmo. Alm disso, pressionar F9 (recalcular) no recalcula ou atualiza a sua TD. Para atualizar os dados: 1. Clique direito sobre a TD e selecione Atualizar, ou; 2. Clique no boto Atualizar sob a guia Opes. Note que a TD precisa estar ativa para que esta guia esteja visvel, ou; 3. Determine um intervalo para atualizao nos casos de dados externos. Mais adiante no curso veremos como acessar esta opo.


Robert F. Martim



09/04/2009 2/7/2009


17


3.

Tabelas dinmicas no Excel 2007: o que h de novo?

Neste tpico, discutirei as novidades da Tabela Dinmica no Excel 2007. A principal caracterstica que o leitor deve manter em mente diz respeito compatibilidade. Outra questo diz respeito ao acesso da ferramenta.

3.1. Acessando a ferramenta de tabela dinmica 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 -1 Acessando a ferramenta de Tabela Dinmica 3

Como o acesso feito por uma guia diferente da guia de formatao, voc pode adicionar a ferramenta Barra de Ferramentas de Acesso Rpido (BAR). Para tanto, clique com o boto direito do mouse sobre o boto Tabela Dinmica. A opo de adio BAR ser exibida:

Figura -2 Adicionando o boto Tabela Dinmica BAR 3

Uma vez que o boto tenha sido adicionado BAR, o mesmo poder ser acessado rapidamente sem a necessidade de troca de guias:Autor: Publicado: Contato:Robert F. Martim



09/04/2009 2/7/2009

18



Figura -3 Boto Tabela Dinmica adicionado BAR 3

NOTA: A forma como voc clica no boto determina qual elemento inserido. Note que o boto Tabela Dinmica , na verdade, um boto simples e um boto do tipo Split. No caso da figura acima o botao adicionado ao BAR o spliButton (boto split)

3.2. Ferramentas de Tabela Dinmica: Tabset de Extensibilidade No Excel 2003, ns tnhamos uma barra de ferramentas que era sensvel ao contexto do objeto. No Excel 2007, ns temos algo similar denominado tabset de extensibilidade:

Figura -4 Tabset de extensibilidade 3

A figura mostra apenas parte de toda a faixa de opes para o tabset Ferramentas de Tabela Dinmica. O leitor deve estudar atentamente a posio de cada elemento dentro da guia.

3.3. Adicionando suas prprias ferramentas ao Tabset de Extensibilidade Neste tpico cobrirei rapidamente a customizao do TabSet de extensibilidade da Tabela Dinmica. Caso o leitor tenha interesse de aprender mais sobre a customizao da Faixa de


Robert F. Martim



09/04/2009 2/7/2009


19


Opes,

veja

o

curso

sobre

o

assunto

no

link:

http://www.juliobattisti.com.br/cursos/ex2007ribbon/default.asp. A figura abaixo mostra a customizao aplicada:

Figura -5 Customizando o Tabset de Extensibilidade da Tabela Dinmica 3

Como no existe um Object Model (OM) em VBA para lidar com a criao de tais elementos da Faixa de Opes, ns 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 cdigo XML para customizar a Guia conforme exemplo acima:

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 alteraes.


Robert F. Martim



09/04/2009 2/7/2009

20



3.4. Lista de campos da tabela dinmica No Excel 2007 a lista de campos da tabela dinmica est mais inteligente e fcil de usar. Alm do formato padro de visualizao, podemos escolher entre quatro outros modelos diferentes de visualizao conforme mostrado na figura abaixo:

Figura -6 Boto Lista de campos da tabela dinmica 3

Os elementos so descritos abaixo:Nome do elemento Escolha campo Filtro de Relatrio Descrio da funo do elemento Exibe todos os campos disponveis para uso na tabela dinmica. rea onde usamos o campo da seo de pgina da tabela dinmica para filtragem da tabela. Rtulos de Coluna Rtulo de Linha Valores Autor: Publicado: Contato: rea onde usamos o campo que pivotar os dados por coluna. rea onde usamos o campo que pivotar os dados por linha. rea onde usamos o campo de origem dos dadosRobert F. Martim



09/04/2009 2/7/2009


21


Adiar atualizao do Layout

Adia a atualizao do layout at que todos os campos tenham sido inseridos e o boto Atualizar tenha sido clicado. Note que desmarcar esta opo desfaz o layout criado com a opo marcada.

3.5. Acessando as opes da tabela dinmica A caixa de dilogo de opes da tabela dinmica mudou bastante, contendo agora um formato que agrupa funes similares.

Figura -7 Acessando as opes da tabela dinmica 3

Abaixo descrevo rapidamente os mtodos de acesso caixa de dilogo.


Robert F. Martim



09/04/2009 2/7/2009

22



Mtodo 1 1. Clique em qualquer local de sua tabela dinmica 2. Quando a guia de extensibilidade for ativada, seleciona a guia Opes 3. No primeiro grupo (canto esquerdo) chamado Opes clique sobre o boto de mesmo nome Mtodo 2 1. Siga os mesmo passos do mtodo 1, mas clique sobre a pequena seta do boto Opes (no clique sobre a palavra). Um menu ser expandido conforme Figura 3-8


Mtodo 3 1. Clique com o boto direito sobre a tabela dinmica; 2. Selecione a opo Opes da tabela dinmica


Robert F. Martim



09/04/2009 2/7/2009


23




Robert F. Martim



09/04/2009 2/7/2009

24



4.

Compreendendo os elementos de uma Tabela Dinmica

Este curto tpico para introduzi-lo aos elementos que compem uma tabela dinmica. Voc precisar estudar estes elementos para compreender melhor como eles funcionam. As explicaes so basicamente o que voc pode encontrar no prprio Ajuda do Excel e a melhor forma de realmente aprend-los testar diferentes combinaes 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 informaes e alguns campos ao invs de um banco de dados ou planilha enorme e com diversos campos. Isso facilitar a sua compreenso e visualizao do que ocorre quando voc cruza os dados. Com um banco de dados ou planilha muito grande e muitos campos, certamente causar confuso para o mais inexperiente, pois a tendncia entrar em pnico quando no se consegue fazer o sumrio desejado. Isso muitos vezes tem a ver com o prprio formato dos dados (como eles esto 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 Dinmica A figura a seguir mostra os componentes da tabela dinmica apresentada anteriormente:

Figura -1 Componentes da tabela dinmica 4

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.


Robert F. Martim



09/04/2009 2/7/2009


25


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 dinmica. No exemplo acima, nomeProduto e Meses so os campos da tabela dinmica. Meses o campo de linha (rtulo de linha) ao passo que nomeProduto o campo de pgina (Filtro Relatrio). Campos podem ser os mesmos contidos na tabela fonte ou podem ser campos criados pelo usurio 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 disponveis para os mesmos (discutirei isso mais adiante no curso). Observe que o ponto de interseo entre um item de linha e um item de coluna o sumrio de dados para o cruzamento entre os campos de linha e coluna para este item. Campo de linha (Rtulos de Linha) Um campo de linha refere-se ao campo na rea de linha da tabela dinmica. fcil confundir um campo de linha com um item de linha. Campos de linha referem-se aos campos utilizados como cabealhos para os itens de linha (os campos que possuem a seta de campo suspenso). Campo de coluna (Rtulos de Coluna) Um campo de coluna refere-se ao campo que mostra os itens na vertical. Novamente, fcil 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 tendncia olhar uma tabela dinmica e chamar um item de coluna de campo de coluna. Campos de coluna possuem uma seta de campo suspenso. Campo de filtro (Filtro Relatrio) Campo de filtro refere-se ao campo na parte superior da tabela dinmica. 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 no precisaAutor: Publicado: Contato:Robert F. Martim



09/04/2009 2/7/2009

26



necessariamente receber um valor numrico. Este campo ir agrupar os dados de diversas formas. Voc pode somar, contar, retirar mdia e desvios padres, etc deste campo. Ou ainda criar os seus prprios 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 -1 Expandindo itens utilizando a seta suspensa 4

Ao expandir RL (Rtulos de Linha, o qual contm a agrupamento dos meses), voc pode selecionar os meses que devem aparecer em sua tabela dinmica.

4

Caso o layout do relatrio esteja no Formato Compacto (Design Layout Layout do Relatrio Mostrar em Formato Compacto) voce precisa clicar com o botao direito do mouse sobre a seta suspensa para visualizar os campos filtrveis.


Robert F. Martim



09/04/2009 2/7/2009


27


4.2. Onde devo ir para inserir uma tabela dinmica? Para inserir uma tabela dinmica 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 cabealhos (nomes de campos), no possua registros nulos e seja contnua. Caso o seu conjunto de dados possua registros nulos, selecione a rea que contm os dados e siga as instrues abaixo para iniciar o Assistente de Tabela Dinmica: V at a guia Inserir grupo Tabelas boto split Tabela Dinmica Tipo: Tabela Dinmica: Cria uma tabela dinmica; Grfico Dinmico: Cria um grfico dinmico.

Uma nova janela ser aberta. Nesta janela, selecione uma das opes 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 -2 Primeiro passo para criar a Tabela Dinmica 4


Robert F. Martim



09/04/2009 2/7/2009

28



Determine se a tabela dinmica deve ser inserida em uma nova planilha ou na planilha ativa. Caso escolha a planilha ativa assegure-se que no h informaes no caminho da tabela. Embora um aviso seja emitido possvel substituir dados por erro. Geralmente, tenho preferncia por uma planilha nova.

4.3. Onde foi parar o Assistente de Tabela Dinmica no Excel 2007? Para todos aqueles que j usaram TDs no Excel 97-2003 a figura do Assistente certamente bem-vinda por vrias razes. Por exemplo,voc pode determinar a consolidao de vrias reas de dados, pode criar uma TD ou GD, etc. Porm, no Excel 2007 quando ns inciamos o processo no h mais tal Assistente. No 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 seqncia as teclas Alt d a. Note que esta no a mesma seqncia de acelerao no Excel 97-2003.

Figura -3 Primeiro passo para criar a Tabela Dinmica usando o Assistente 4


Robert F. Martim



09/04/2009 2/7/2009


29


Nesta primeira etapa, selecione uma das opes para a fonte de dados e para tipo (Tabela ou Grfico Dinmico) 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; Vrios intervalos de consolidao: Especifica reas mltiplas de consolidao; Outro relatrio de tabela dinmica ou de grfico dinmico: Determina se a fonte provm de uma Tabela ou Grfico Dinmico j existente no documento em questo. Este mtodo evita redundncia de consultas e minimiza o uso de memria. Somente ser possvel utilizar tal opo se existir uma Tabela ou Grfico Dinmico disponvel no documento (se efetivamente existir um PivotCache). Tipo de relatrio: Tabela Dinmica: Cria uma tabela dinmica; Relatrio de Grfico Dinmico: Cria um grfico dinmico.

3. Clique em Avanar para continuar ou Concluir para terminar. Sugiro Avanar para se beneficiar do Assistente, principalmente se o leitor for iniciante. Assumindo que o leitor clicou em Avanar, o prximo passo requer a seleo da rea que contm os dados na planilha. Caso a clula 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 Segundo passo do Assistente de TD 4


Robert F. Martim



09/04/2009 2/7/2009

30



Clique em avanar para determinar o local de sada da TD. Caso j exista alguma tabela dinmica 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 No, o Excel para avana para o ltimo passo:

Figura -5 ltimo passo do Assistente de TD 4

Determine se a tabela dinmica deve ser inserida em uma nova planilha ou na planilha ativa. Caso escolha a planilha ativa assegure-se que no h informaes no caminho da tabela. Embora um aviso seja emitido possvel substituirmos dados por engano. Geralmente, tenho preferncia por uma planilha nova. Este o ltimo passo do Assistente. O Assistente de layout deve ser acessado a partir das opes da TD. Este assunto coberto adiante.


Robert F. Martim



09/04/2009 2/7/2009


31


4.4. Opes da tabela dinmica Vamos investir agora um tempo na anlise das opes de uma tabela dinmica.


Como podemos ver acima, as opes so separadas em cinco grupos: 1) Formato e Layout; 2) Totais e Filtros; 3) Exibicao; 4) Impresso e 5) Dados. Deste modo, quebrarei cada um destes grupos em tpicos para que possamos analis-los separadamente e maximizar a compreenso de cada item dentro de cada grupo. Iniciarei pelas opes de formatao.

4.4.1. Layout e Formato A nossa primeira parada diz respeito s opes de layout e formatao. Todas as opes so tratadas separadamente:Autor: Publicado: Contato:Robert F. Martim



09/04/2009 2/7/2009

32



Mesclar rtulos Mesclar rtulos nada mais do que um mesclado de clulas. Observe o a figura abaixo:

Figura -7 Mesclando clulas 4

Note que as clulas selecionadas so mescladas quando esta opo ativada. A vantagem de ter as clulas mescladas que ao selecionar o espao j selecionamos o valor ao invs de uma clula. Na formatao padro, ns devemos especificamente selecionar a clula que contm o valor ao invs da rea que contm o valor. Abaixo e acima Modifica a disposio dos campos dentro da tabela. Observe as duas figuras a seguir

Figura -8 Disposio Abaixo e Acima 4

A figura acima mostra a disposio Abaixo e Acima ao passo que a figura abaixo mostra a disposio para o layout Acima e Abaixo:Autor: Publicado: Contato:Robert F. Martim



09/04/2009 2/7/2009


33


Figura -9 Disposio Acima e Abaixo 4

Para valores de erro, mostrar Utilize esta opo para mostrar um erro por voc determinado ao invs dos erros internos como #NM!, #VALOR!, etc; Para clulas vazias, mostrar Quando no h informao para uma linha/coluna o Excel deixar em branco o ponto onde um valor seria esperado. A figura na prxima pgina mostra isso ocorrendo:

Figura -10 Clulas vazias 4

Utilize esta opo para determinar um valor para as clulas em branco. Apenas lembre-se de manter consistncia, isto , se voc est somando utilizando o valor zero. Note tambm que ao inserir o valor 0 a TD pode ser interpretada como sendo o valor zero ao invs de no h dados.


Robert F. Martim



09/04/2009 2/7/2009

34



Figura -11 Clulas vazias preenchidas com zeros 4

4.4.2. Totais e filtros Sob esta tabulao, o leitor poder inserir totais por linhas e colunas, alm de poder permitir mltiplos filtros por campo e permitir a utilizao de listas personalizadas na classificao 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 -12 Total geral para colunas e linhas 4

Outra forma de ativar/desativar totais acessar a guia Design (da guia de extensibilidade) e sob o grupo Layout boto split Totais Gerais selecione a o total desejado.


Robert F. Martim



09/04/2009 2/7/2009


35


4.4.3. Exibio Na parte de exibio, ns podemos executar vrios comandos que nos permitem modificar o mtodo de exibio da tabela dinmica. Mostrar botes de expandir/recolher Esta opo permite a alternncia entre a visualizao e no visualizao do boto de expandir/recolher.

Figura -13 Botes de expandir/recolher 4

A expanso para itens pode ocorrer atravs deste boto ou pelo duplo-clique (caso o boto no esteja visvel). Outra opo acessar a TD e quando a guia de extensibilidade for ativada, v at a guia Opes grupo Campo Ativo Expandir Campo Inteiro:

Figura -14 Expandir/recolher Campo Inteiro 4

Legendas de campos e lista suspensa de filtrosAutor: Publicado: Contato:Robert F. Martim



09/04/2009 2/7/2009

36



Esta opo permite a alternncia entre a visualizao e no visualizao do boto de expandir/recolher.

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

A exibio destes elementos til para impresso final de um relatrio de tabela dinmica. Layout clssico Exibe a TD no layout clssico, isto , no layout pr-Excel 2007. Neste caso, note que em caso de campos passveis de extenso, o item oculto ser exibido em outra coluna dentro da rea de rtulo de linha. A vantagem da utilizao deste layout que podemos arrastar os campos para dentro/fora de uma tabela dinmica. Utilize a tabela abaixo como guia dos smbolos de arraste do mouse: Ao arrastarmos um campo, caso a rea em azul seja o topo da pequena representao 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 representao 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 representao da TD, isso indica que o campo ser inserido na rea do campo de coluna;


Robert F. Martim



09/04/2009 2/7/2009


37


Ao arrastarmos um campo, caso a rea em azul seja a rea central da pequena representao 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 tambm o layout inicial da TD clssica com o layout da TD no Excel 2007:

Figura -16 Exibindo o layout clssico ou layout Excel 2007 4

4.4.4. Impresso Sob esta guia voc pode definir certas opes de impresso tais como impresso dos botes de expanso e recolhimento de itens.

4.4.5. Dados Utilize esta guia para definir opes de dados tais como se os dados devem ou no ser atualizados na abertura, se os dados devem ser salvos com o arquivo, etc. A deciso de salvar ou no 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 boto 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 informaes de TDs estejam em cache e sejam salvas, maior ser o arquivo.


Robert F. Martim



09/04/2009 2/7/2009

38



A vantagem que com os dados salvos no layout as informaes da TD so carregadas mais rapidamente e liberam memria, pois a mesma somente utilizada quando algo muda na TD, como relocao de campos dentro da TD. Por outro lado, se no utilizamos esta opo o Excel deixa de salvar a informao 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 cpia 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 dinmica mais simples que voc encontrar. Neste tipo de tabela o usurio insere um campo de linha ou de coluna e o campo de dados. Feito isso, o usurio poder determinar como os dados so agrupados (soma, contagem, etc).

4.6. O que uma tabela bidimensional de campo nico? Uma tabela bidimensional de campo nico a tabela dinmica que contm 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 ingls X-Tab lido como cross tabulation ou tabulao cruzada, em portugus). O grande poder de tabelas dinmicas que podemos rapidamente cruzar os mais variados tipos de informaes rapidamente.

4.7. O que uma tabela unidimensional e bidimensional de mltiplos campos? Como o nome sugere, se a tabela for unidimensional com mltiplos campos estamos observando uma tabela que contm mais de um campo da tabela fonte no campo de linha ou no campo de coluna da tabela dinmica. Por outro lado, uma tabela bidimensional com mltiplos campos aquela que possui mltiplos campos da tabela fonte tanto no campo de linha quanto no campo de coluna da tabela dinmica.


Robert F. Martim



09/04/2009 2/7/2009


39


Boa parte das tabelas dinmicas 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 nvel de complexidade extremamente elevado e em muitos casos, voc no conseguir o resultado procurado na primeira tentativa (a menos que voc conhea muito bem os dados, sua estrutura e tenha muita experincia com tabelas dinmicas). Desta forma, se voc se encontrar em dificuldades em criar uma tabela dinmica complexa, no entre em pnico, pois em muitos casos o trabalho realmente frustrante de incio.

4.8. Adicionando campos tabela dinmica: mtodo clssico e novo Para adicionar um campo tabela dinmica bastante simples no Excel 2007. No mtodo normal para o Excel 2007, tudo que voc precisa fazer selecionar o campo e o Excel far a sugesto 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.


Robert F. Martim



09/04/2009 2/7/2009

40



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

Figura -17 Janela da lista de campos 4

Na figura acima, para adicionar um campo rea de filtro (campo de filtro ou de pgina) tudo que precisaramos fazer selecionar o campo, selecionar a opo na lista conforme mostra a figura e clicar em Adicionar a. Mas isso mudou no Excel 2007. Ao invs disso, clique com o boto direito5 sobre o campo desejado para obter as mesmas opes:

5

Clicando com o boto esquerdo voc obtm opes de filtro.


Robert F. Martim



09/04/2009 2/7/2009


41


Figura -18 Adicionando campo TD por clique-direito 4

4.9. Removendo campos da tabela dinmica Para remover um campo da tabela bastante simples. Simplesmente clique sobre o campo e segure o boto do mouse. Em seguida, arraste o campo para uma rea fora da tabela dinmica (utilizando o mtodo clssico conforme j ensinado). No modo normal, simplesmente desmarque o campo na lista de campos.


Robert F. Martim



09/04/2009 2/7/2009

42



5.

Compreendendo as fontes de dados para Tabelas Dinmicas

Ao criar uma tabela dinmica, ns temos vrias opes de fonte de dados. Compreender tais fontes de dados fundamental no momento da criao de sua TD. Utilizar o Excel como fonte de dados pode no ser a melhor opo quando possumos uma grande quantidade de registros. Por outro lado se os dados precisam viajar com a planilha, provavelmente o Excel a melhor opo de fonte de dados. Investiremos um tempo agora para conhecer as vrias fontes de dados.

5.1. Banco de dados ou lista do Microsoft Office Excel Antes de tudo, lembre-se que no Excel 2007 no h mais lista. Ao invs disso, ns temos tabelas. Esta nova ferramenta substitui as antigas listas e possui muito mais vantagens do que as antigas listas. Para maiores informaes 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 dvida, a forma mais comum e conhecida na criao de TDs. Neste caso, os dados podem estar localizados: 1. na mesma pasta de trabalho; 2. em uma outra pasta de trabalho Independentemente da localizao 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 clula ativa est dentro da rea de dados e esta rea no 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 criao 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);


Robert F. Martim



09/04/2009 2/7/2009


43


3. Crie um nome (que pode ser esttico ou dinmico) e digite o nome no campo Tabela/Intervalo. A Figura 5-1 mostra o exemplo utilizando o nome de uma tabela:

Figura -1 Adicionando campo TD por clique-direito 5

Caso o nome inserido seja invlido, o Excel exibir uma mensagem de erro solicitando a sua correo para que voc possa continuar. At usamos dados da mesma pasta de trabalho; porm, podemos tambm utilizar dados de uma pasta secundria. Primeira pergunta : por que eu faria isso? O principal motivo para isso reduzir o custo de manuteno dos dados na mesma pasta, pois alm do tamanho da pasta de trabalho h tambm a questo de velocidade de processamento. No caso dos dados estarem em uma pasta diferente, o processo de criao da TD ligeiramente diferente do processo j visto. Para criar esta TD, siga estes passos: 1. Inicie o processo de criao da TD conforme j mostrado; 2. O padro Tabela/Intervalo j estar selecionado. Clique no boto de referncia para abrir a caixa de seleo de intervalo de dados; 3. Navegue at a pasta de trabalho que contm os dados (use Alt+Tab para alternar entre os documentos abertos ou outro mtodo de sua preferncia);Autor: Publicado: Contato:Robert F. Martim



09/04/2009 2/7/2009

44



4. Selecione os dados. Note que a fonte de dados ser exibida Por no formato: exemplo:

[NomeDaPasta.xlsx]NomeDaPlanilha!Intervalo.

[TD_XL07_Tpico3.3.xlsx]Plan1!MinhaTabela. Neste caso, MinhaTabela o nome do intervalo que contm os dados no formato de uma tabela. Alternativamente, voc pode inserir o caminho completo da localizao de sua pasta de trabalho contendo os dados, por exemplo: '\Users\Robert

Martim\Desktop\TD_XL07_Tpico3.3.xlsx'!MinhaTabela NOTA: s vezes, ns queremos criar uma TD em cima de dados filtrados. Caso os dados estejam filtrados o Excel ignorar o filtro e todos os dados sero 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, ns podemos: 1. Importar os dados para o Excel e utiliz-los diretamente no Excel, atualizando a consulta e a TD sempre que necessrio; 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 dilogo de criao de TD; 2. Selecione a opo Fonte de dados externos; 3. Clique em Escolher Conexo para exibir a caixa de Conexes Existentes.


Robert F. Martim



09/04/2009 2/7/2009


45


Figura -2 Conexes existentes de dados 5

Neste ponto, selecione a conexo desejada e clique em Abrir. Alternativamente, clique em Procurar Mais para localizar outras fontes de dados no listadas. Caso no exista a conexo, voc pode utilizar a guia Dados grupo Obter dados externos. Escolha uma das opes, crie e salve a sua conexo para uso posterior. NOTA: Em alguns casos voc pode querer salvar a senha de conexo. Porm, este salvamento no recomendado. A razo que o Excel no somente expe toda a string de conexo, mas tambm o nome de usurio e senha. A Figura 5-3 exibe a string de conexo (cadeia de conexo) para um servidor SQL. O nome do usurio (sa) e a senha so exibidos:


Robert F. Martim



09/04/2009 2/7/2009

46



Figura -3 Cadeia de conexo 5

No caso anterior, ns utilizamos a fonte externa para alimentar a TD diretamente. 5.3. Vrios intervalos de consolidao

Para usar esta opo necessrio acessar o antigo Assistente de Tabela Dinmica. O acesso direto pelo Excel 2007 no nos permite criar uma TD com este tipo de consolidao. Antes de tudo, importante lembrar que os dados devem ser compatveis com o formato requerido pela tabela dinmica. Caso contrrio, ns no obteremos o resultado desejado. Uma vez no formato correto, a criao da TD mais simples do que pode parecer. Estude a figura abaixo:

Figura -4 Intervalos para consolidao 5

Para criar a TD utilizando os dois intervalos de consolidao, siga os passos abaixo: Pressione Alt d a (ou Alt d p no Excel 2007 em ingls); Selecione a opo Vrios Intervalos de Consolidao; Clique em Avancar para passar para o prximo passo;Robert F. Martim




09/04/2009 2/7/2009


47


Na segunda etapa do Assistente, voc ter as seguintes opes (selecione a opo padro e clique em Avanar): o Crie um nico campo de pgina O Campo de Pgina o nome dado no Excel 2003 ao Filtro de Relatrio no Excel 2007. Caso tenha dvida sobre a localizao, veja a Figura 4-1. o Criarei os campos de pgina Utilize esta opo para determinar quantos campos de pgina devem ter. Voc pode determinar entre zero (0) e quatro (4) campos.

Figura -5 Definindo campo de pgina 5

No prximo passo, voc dever selecionar os intervalos de dados. Note que CentroOeste e Nordeste encontram-se no topo de cada um dos intervalos de consolidao. Voce no deve selecionar esta rea, mas somente a rea contendo os cabealhos de campo e dados. Veja Figura 5-6 para exemplo de como selecionar o intervalo.

Clique em Avanar (Figura 5-6). Escolha a nova localidade e clique em Concluir


Robert F. Martim



09/04/2009 2/7/2009

48



Figura -6 Selecionando intervalos de consolidao 5

Finalmente, defina onde voc deseja que a nova TD seja criada (sugiro em uma nova planilha).

A sua nova tabela dinmica ter o seguinte formato:

Figura -7 Tabela dinmica final 5

Note que no possumos os nomes dos campos, mas apenas a consolidao dos mesmos. No caso do Filtro de Relatrio, ao expandi-lo, voc ver apenas Item 1 e Item 2 os quais se referem aos itens Centro-Oeste e Nordeste respectivamente (campo Regio).


Robert F. Martim



09/04/2009 2/7/2009


49


O nosso prximo passo mudar os nomes dos campos padres para o que realmente queremos. Iniciaremos pelos Filtros de Relatrio. Siga os passos abaixo: Clique no boto de filtro (onde se l (Tudo)) e escolha Item 1 Na barra de frmula, troque o valor Item 1 para Centro-Oeste e pressione Enter

Figura -8 Trocando o nome do item do Filtro de Relatrio 5

Ao pressionar Enter, o Excel avisar que no existe o item digitado e sugerir que o item seja renomeado:

Figura -9 Campo inexistente, renomear? 5

Clique OK para continuar e renomear o item. Repita os passos e renomeie o Item 2 para Nodeste bem como Pgina 1 para Regio. Ao final da renomeao, voc ter o resultado como segue:


Robert F. Martim



09/04/2009 2/7/2009

50



Figura -10 Resultado aps renomear o campo (Pgina1) e Items 1 e 2 5

Em seguida, ns renomearemos os campos. Na lista de campos, clique no campo que deseja renomear e selecione a opo Configuraes do Campo:

Figura -11 Renomeando campo via Lista de campos da tabela dinmica 5


Robert F. Martim



09/04/2009 2/7/2009


51


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 no ter efeito algum para este exemplo), voc ter o seguinte cenrio:

Figura -12 Renomeando campo via Lista de campos da tabela dinmica 5

5.4. Outro relatrio de tabela dinmica ou de grfico dinamico Como ltima opo da lista de fonte de dados ns temos como utilizar outra TD ou Grfico Dinmico (GD). No Excel 2007, voc pode criar uma TD/GD utilizando os seguintes mtodos: Selecionar o mesmo intervalo (intervalo exato) de uma TD j existente; Acionar o Assistente (Alt d a) e selecionar a TD da lista

Figura -13 Baseando uma nova TD em uma j existente 5 Autor: Publicado: Contato:Robert F. Martim



09/04/2009 2/7/2009

52



O motivo para usar outra TD/GD como fonte de dados? Uma TD/GD baseada em outra usa menos memria. Simples assim.


Robert F. Martim



09/04/2009 2/7/2009


53


6.

Formatao

Neste tpico discutirei a questo de formatao de uma tabela dinmica. A formatao um ponto importante, pois atravs dela que ns realamos pontos dentro da tabela, etc. Formatao uma parte complexa, pois cada indivduo tem gosto diferente. Portanto, este tpico no estar focado em como-fazer-a-tabela-mais-bonita-do-planeta, mas estar focado nas ferramentas que o leitor precisa para criar a formatao que desejar. Aqui, no discutirei questes bsicas 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 Dinmica O Excel 2007 vem com um nmero de estilos prontos para aplicao em uma tabela dinmica. Alm disso, voc pode criar a sua prpria formatao e/ou estilo. Por questo de praticidade e rapidez, eu prefiro os estilos prontos aos meus prprios. No obstante, h empresas que, por motivos de identidade corporativa, utilizam estilos prprios. Um estilo visa controlar vrios aspectos visuais, entre eles (no 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 especficos do objeto que receber o estilo. Voce pode definir tamanho, cor, estilo (negrito, itlico, 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 aplicao Fundo: determina cores de fundo bem como padres para o fundo do elemento em questo Para aplicar um estilo: Selecione a TD que receber o estilo Selecione um estilo da lista de estilos conforme mostra figura a seguirRobert F. Martim




09/04/2009 2/7/2009

54



Figura -1 Estilos de tabela dinmica 6

Alternativamente, voc pode abrir a caixa de definio de estilos (Estilos de tabela dinmica Novo estilo de tabela dinmica) onde voc poder definir o estilo para os mais variados elementos da tabela dinmica:

Figura -2 Estilo personalizado para TD 6

Nesta janela, voc possui os seguintes elementos: Nome: Digite aqui o nome para o seu estilo personalizado Visualizao: Exibe como o seu estilo ficarRobert F. Martim




09/04/2009 2/7/2009


55


Limpar: Limpa toda a formatao da TD selecionada Definir como estilo rpido de tabela dinmica para este documento: define como estilo padro para todas as novas TDs para a pasta de trabalho.

Para modificar o estilo criado basta retornar galeria de estilos e clicar com o boto direito sobre o estilo personalizado. Para os estilos internos ns podemos defini-lo cmo padro, copiar etc:

Figura -3 Atualizando, modificando e duplicando estilos 6

6.2. Formatando campo Para formatar um campo bastante simple. Voc pode acessar a configurao do campo: Direto na tabela: Clique com o boto direito sobre o campo e escolha a opo Configuraes de campo. Na lista de campo (rea de campos na TD e no de campos disponveis): clique com o boto esquerdo sobre o campo e escolha a opo Configuraes de campo. Sob a guia Opes, grupo Campo Ativo: Clique sobre o campo desejado, selecione a guia Opes grupo Campo Ativo e clique em Configuraes de campo. As opes de configurao do campo selecionado dependero do tipo de dado no campo selecionado.

6.3. Layout do relatrio de tabela dinmica O Excel oferece trs tipos de layouts distintos para a sua tabela dinmica. Estes podem ser acessados sob a guia Design (presente na guia de extensibilidade Ferramentas de Tabela Dinmica).


Robert F. Martim



09/04/2009 2/7/2009

56



Os seguintes layouts estao disponveis: Formato compacto: Este layout requer o mnimo de espao para a sua TD. Figura 6-4 mostra o modelo de formato compacto. Este formato oculta boto de Lista Suspensa para o segundo e subseqente campos de linha. Formato de Estrutura de Tpicos: Neste layout, os dados so formatados no modelo de estrutura de tpicos, isto , os campos so indentados de acordo com a ordem hierrquica. Figura 6-5 mostra o formato em Estrutura de Tpicos Formato de tabela: Exibe a TD em um formato de tabela. Figura 6-6 mostra a TD no formato de tabela.

Figura -4 Formato compacto 6

Figura -5 Estrutura de Tpicos 6 Autor: Publicado: Contato:Robert F. Martim



09/04/2009 2/7/2009


57


Figura -6 Formato de Tabela 6

6.4. Preservando formatao Ao aplicar uma formatao diretamente nos elementos de uma tabela dinmica, ao atualizarmos a TD, a formatao revertida para a formatao padro aplicada. Para manter o formato aplicado abra a caixa de opes da tabela dinmica e marque a opo Preservar a formatao da clula 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 -7 Selecionando elementos da tabela dinmica para formatao 6


Robert F. Martim



09/04/2009 2/7/2009

58



Com o cursor apontando para baixo, conforme figura acima, os elementos do campo da linha (Regio) sero selecionados como mostra a rea sombreada. O mtodo 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 edio do nome do campo aberta:

Figura -8 Modificando rtulo do campo da TD 6

No campo Nome Personalizado, defina o nome que voc deseja dar ao campo em questo.

6

Caso a rea de dados possua mais de um campo o duplo-clique no funcionar. Para modificar o nome do campo clique com o boto direito sobre o campo e escolha a opo Configuraes de campo. Utilize este mtodo para modificar campos tambm. 7 Caso a TD esteja em um formato de relatrio, o duplo-clique sobre a rea de dados causar a criao de uma planilha nova em forma de uma tabela-relatrio.


Robert F. Martim



09/04/2009 2/7/2009


59


7.

Trabalhando com campos

At o momento vimos o bsico e o importante para que possamos trabalhar com tabelas dinmicas. Sem este conhecimento bsico, passar para o prximo estgio pode ser mais penoso (como o caso da formatao). Neste grande tpico veremos como trabalhar com campos em uma tabela dinmica. Primeiramente, investiremos um tempo na construo de tabelas com mltiplos campos em linha e/ou coluna. Em seguida movemos para subtotalizaes, agrupamentos e campos calculados.

7.1. Trabalhando com campos de linha e de coluna Trabalhar com campos de linha bastante simples. Iniciaremos com a adio de um campo de linha mais o campo de dados da planilha deste tpico:

Figura -1 Tabela normal 7

Ns 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, ns podemos adicionar mais um campo rea reservada para as linhas conforme mostra a figura a seguir. Note que a disposio dos dados em forma de relatrio e, portanto, podemos expandir ou colapsar um determinado item dentro do campo:


Robert F. Martim



09/04/2009 2/7/2009

60



Figura -2 rea de linha com mltiplos campos 7

Conforme j dito a expanso total acima pode ser colapsada/expandida conforme a necessidade, bastando apenas clicar nos botes de mais (+) e menos (-):

Figura -3 Expandindo a regio sul e colapsando as demais 7

Agora que possumos estes dois campos o que mais podemos fazer? Clique com o boto direito sobre o campo Regio e selecione a opo Configuraes do campo. A caixa de dilogo do campo da TD ser aberta:Autor: Publicado: Contato:Robert F. Martim



09/04/2009 2/7/2009


61


Figura -4 Opes de configurao de um campo da TD 7

Na guia Subtotais e Filtros ns podemos definir o tipo de subtotais que desejamos trabalhar. Por exemplo, dependendo do tipo de dados, o subtotal automtico ser Soma ou ContNm. Por outro lado, voc pode selecionar Personalizados e escolher a funo para agregar os seus dados. Clicando na guia Layout e Impresso. Voce ter as seguintes opes:

Figura -5 Layout do campo da TD 7


Robert F. Martim



09/04/2009 2/7/2009

62



O que cada uma das opes representa: Mostra rtulos de item no formato de estrutura de tpicos Exibe os rtulos de item (no confunda Item com Campo, cidade se transforma em item quando estruturada dentro do campo regio) em estrutura de tpicos. Campo fica uma linha acima do item dentro da estrutura da TD: o Exibir rtulos do prximo campo na mesma coluna O prximo campo aqui se refere ao campo que fornecer os itens na estrutura de tpicos. 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 incio de cada grupo Subtotais sero exibidos cada vez que um grupo modificado. Mostrar rtulo de item no formato de tabela Exibe o rtulo no formato da tabela. O campo fica na mesma linha que o item dentro da estrutura da TD. Inserir linha em branco aps cada item Novamente, faz exatamente o que est escrito: insere uma linha em branco aps cada item de um grupo; Inserir quebra de pgina aps cada item Esta opo de impresso permite colocar quebras de pgina nos itens do campo selecionado. Vejamos agora os itens de subtotalizao que aparecem na caixa de edio do campo da TD.

7.2. Trabalhando com sumrios e subtotalizaes: subtotais simples e mltiplos Ao criar uma tabela dinmica 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, ento os mesmos so contados automaticamente ao passo que valores numricos so somados. importante salientar que um valor diferente na rea de dados determinar o total adicionado (contagem ou soma). Por exemplo, se existirem 999 valores numricos e um de texto, a contagem tem precedncia sobre a soma:


Robert F. Martim



09/04/2009 2/7/2009


63


Figura -6 Configurao do campo da TD 7

Embora estes dois sumrios sejam padres, ainda possvel determinar outros tipos de sumrios. A figura acima mostra algumas das opes de subtotalizao. Voc pode selecionar mais de uma funo para personalizao e o campo de dados ser duplicado para exibir a nova subtotalizao. Observe a figura abaixo:

Figura -7 Totalizao e subtotais personalizados 7

Nesta figura ns possumos a soma total por regio e a mdia por regio. Porm, note que a mdia no entre as cidades, mas entre todas as observaes para o Centro-Oeste, isto , aAutor: Publicado: Contato:Robert F. Martim

www.juliobattisti.com.br rm@msoffic

Documents

52037118 SERIES COMO FAZER Excel 2007 Tabelas Dinamicas