Trabalhando com Tabela Dinâmica

Este artigo foi originalmente escrito por João Benito Savastano, diretor da Mondial Informática e publicado tanto na revista Fórum Access quanto no site da própria Mondial. Infelizmente foi tirado do ar a algum tempo, mas por sua riqueza e utilidade, decidi manter o artigo vivo para que todos possam tira proveito. Se o autor deste artigo ou a Mondial Informática se sentirem lesados pela publicação deste artigo, por favor , entrem em contato.

Tomás Vásquez

Este artigo foi orgininalmente publicado
na Revista Forum Access,
de Janeiro e Março de 1999.

Um dos melhores, talvez o melhor, recurso que o MS-Excel apresenta para os seus usuários é a Tabela Dinâmica, que permite a apresentação dos dados de diversas formas, a partir de uma mesma base de informação e o que é mais importante, de um único relatório.

Uma Tabela Dinâmica (ou Pivot Table Report na versão do MS-Excel 97 em inglês), é uma tabela interativa de planilha, utilizada para resumir e analisar dados de uma lista ou tabela já existente. Colunas ou linhas de sumarização ou dados, podem ser invertidos, automaticamente, pelo usuário, criando novas versões de relatório em fração de segundo (isto mesmo, fração de segundo). Um exemplo prático deste tipo de funcionalidade é estruturar um relatório de vendas por Vendedor e por Produto. Caso queira visualizar os dados por Produto e por Vendedor (ou Cliente), será necessário a elaboração de um outro relatório, código adicional, etc. Com a Tabela Dinâmica do MS-Excel basta inverter, com o auxilio do mouse, a posição destas duas colunas, e pronto, o resultado está disponível para consulta.

Pode-se atualizar as informações de um Relatório de Tabela Dinâmica sempre que forem feitas modificações nos dados fonte. Os dados fonte permanecem intactos, independente as alterações de posições de colunas ou linhas realizadas no relatório e a Tabela Dinâmica continua na planilha onde você a criou.

Este recurso oferece uma maleabilidade na apresentação dos dados que não encontramos recursos similares em outros aplicativos, mesmo as outras versões de Planilhas Eletrônicas de Cálculo. Este recurso justifica o significado do nome do aplicativo (Excel, em inglês significa, exceder, sobrepujar, superar, sobressair, primar, etc.).

Vamos a um exemplo prático. Abra o arquivo Vendas.xls que se encontra no disco que acompanha a revista. Observe os dados na planilha OLD. Esta planilha apresenta o resultado das vendas, por vendedor. Aparentemente não existe nada de errado com a planilha, exceto quando você, ou alguém, desejar os resultados por Cliente ou por Produto. Isto significa reorganizar toda a planilha para obter o resultado desejado. E isto demanda tempo e paciência (em muitos casos...).

Você pode resolver estes problemas, criando uma Tabela Dinâmica utilizando o Assistente de Tabela Dinâmica, uma seqüência de 04 (quatro) caixas de diálogo interativas que o guia pelas etapas de localização e recuperação dos dados que você deseja analisar. O Assistente de Tabela Dinâmica também permite que você defina como deseja organizar uma Tabela Dinâmica utilizando rótulos de linha e de coluna, e como deseja apresentar os dados nos campos.

Você pode criar uma Tabela Dinâmica a partir de dados fonte que residem em:

· Uma lista ou um banco de dados do MS-Excel.

· Um banco de dados externo, como uma tabela ou arquivo criado em um aplicativo, ou sistema de gerenciamento de banco de dados externo ao MS-Excel, como por exemplo, MS-SQL Server, MS-Access, Oracle, Sybase etc.

· Vários intervalos de consolidação de dados em planilhas do MS-Excel.

· Outra Tabela Dinâmica na mesma pasta de trabalho.

Uma vez criada a Tabela Dinâmica, você pode organizar, reorganizar e analisar os dados arrastando e soltando os campos na Tabela Dinâmica. Também é possível incluir subtotais, modificar a função de resumo de um campo, alterando a função de soma pela média, valor máximo, mínimo, ou selecionar cálculos personalizados para a Tabela Dinâmica.

Observação: Se você deseja criar uma Tabela Dinâmica utilizando dados externos, utilize o Microsoft Query e os controladores da conectividade de banco de dados (ODBC - Open Database Connectivity) adequados e defina as fontes de dados que deseja utilizar. Neste artigo estaremos apresentando uma conexão com uma base de dados em MS-Access.

Como montar uma tabela dinâmica

Para montar a sua tabela dinâmica, basta selecionar qualquer célula que contenha os dados da sua tabela e utilizar o Assistente de tabela dinâmica do MS-Excel.

1. Na pasta de trabalho Vendas.xls selecione a planilha DadosDeVendas. Nesta planilha você encontra uma base de dados de vendas, com 7 campos de dados.

2. Para iniciar o processo de montagem da Tabela Dinâmica deve-se inicialmente, manter ativa a Planilha ou Pasta de trabalho que contém a tabela de dados que desejamos utilizar para a criação da Tabela Dinâmica. Um detalhe importante: remova todos as fórmulas de soma, os subtotais e filtros da tabela antes de criar uma Tabela Dinâmica.

3. No menu Dados selecione o comando Relatório da tabela dinâmica e você terá a Caixa de Diálogo apresentada na Figura 1.

 


Figura 1 - Primeira etapa do Assistente de Tabela Dinâmica do MS-Excel.
 

Neste passo você deve selecionar a origem dos dados. Temos as quatro alternativas já citadas no artigo. Neste exemplo inicial, vamos utilizar uma base de dados oriunda de uma planilha do MS-Excel.

4. Um clique no botão Avançar > da caixa de diálogo, e você terá a próxima etapa, representada pela Figura 2.

 


Figura 2 - Segunda etapa do Assistente de Tabela Dinâmica do MS-Excel.
 

Esta etapa permite especificar o intervalo de Banco de Dados ou lista do MS-Excel que você deseja usar.

5. Em Intervalo você digita o nome da Planilha e/ou intervalo de células ou seleciona o intervalo na Planilha. Se a tabela de origem estiver em outra Pasta de trabalho, digite o nome da Pasta de Trabalho usando a seguinte sintaxe:

[Pasta de Trabalho]nome da planilha!intervalo

Se você selecionar uma célula na tabela antes de iniciar o comando Relatório da tabela dinâmica, o MS-Excel inserirá o intervalo automaticamente.

No botão Procurar…, o MS-Excel exibe a caixa de diálogo Procurar para que você possa selecionar a Pasta de Trabalho que contém a tabela desejada.

6. Após selecionar a pasta de trabalho, digite o nome da Planilha e o intervalo desejado. Este procedimento é necessário para que o MS-Excel saiba onde a sua pasta de trabalho está arquivada para realizar posteriormente o trabalho de atualização de dados mesmo que a pasta de trabalho com a origem dos dados esteja fechada.

Lembramos que a Caixa de Diálogo da Etapa 2, representada pela Figura 2, que foi detalhada, refere-se a escolha de Banco de Dados ou lista do MS-Excel feita na primeira etapa. Caso tenha escolhido outra opção, esta etapa será diferente. Observe que o MS-Excel apresentou automaticamente o nome Banco_de_dados nesta etapa. Ele detectou o nome a partir da célula selecionada na planilha. Este nome de área já havia sido criado anteriormente, para facilitar o trabalho de manutenção. Todas as vezes que a base de dados for alterada no número de linhas, é mais conveniente alterar o range da área do que alterar todas as fórmulas que dependam deste intervalo para apresentar o valor correto.

7. Um clique no botão Avançar > da caixa de diálogo, e você terá a próxima etapa, representada pela Figura 3,

Figura 3 - Terceira etapa do Assistente de Tabela Dinâmica do MS-Excel.

Esta etapa permite projetar o layout da Tabela Dinâmica. O lado direito da caixa de diálogo contém botões que representam os campos na tabela de origem.

A figura na caixa de diálogo representa a área dinâmica, com seções para os campos de Linha, campos de Coluna e campos de Página assim como uma área de dados para os dados resumidos Dados.

Na parte superior da caixa de diálogo temos um pequeno descritivo do significado de cada área da Tabela Dinâmica.

Obs.: Quando você selecionar a opção Vários intervalos de consolidação na primeira etapa, os nomes de campo apresentados nesta etapa serão genéricos.

8 Arraste os campos relacionados no lado direito da tela para as posições indicadas conforme a Figura 4, deste modo teremos a mesma estrutura de Tabela Dinâmica.

 


Figura 4 - Definição do posicionamento dos campos na Tabela Dinâmica.

 

9 Um clique no botão Avançar > da caixa de diálogo, e você terá a próxima etapa, representada pela Figura 5,

 


Figura 5 - Quarta etapa do Assistentede Tabela Dinâmica do MS-Excel.

Esta etapa permite especificar onde aplicar a nova Tabela Dinâmica se em uma nova planilha ou em uma planilha já existente.

10 Definidos os padrões solicitados nesta tela, clique no botão Concluir nesta caixa de diálogo e você terá o resultado apresentado na Figura 6:

Figura 6 - Resultado final da Tabela Dinâmica.

Deste modo você obtêm a Tabela Dinâmica com todas as informações resumidas e concentradas no modo solicitado.

Observe os campos de quebra de página Data, Supervisor Cliente e Cidade. Eles apresentam a mesma funcionalidade que os botões drop-down do AutoFiltro, teste-os.

Você também poderá redefinir a distribuição dos campos pela Tabela Dinâmica simplesmente arrastando o botão cinza indicativo do campo para o local desejado, invertendo posições de botões de campo de coluna com linha ou então de página. Deste modo podemos obter resultados diferentes com a mesma Tabela Dinâmica.

A posição padrão de apresentação das informações dos campos de Dados da Tabela Dinâmica é na posição vertical, ou seja, um abaixo do outro. Caso você queira apresentar os dados de previsto e Realizado, um ao lado do outro, na posição horizontal, basta arrastar o campo Dados um pouco para a direita, conforme apresentado na Figura 7. Observe o ícone que será exibido temporariamente, a direita do ponteiro do mouse. É uma pequena tabela que indica a posição vertical ou horizontal dos dados.

 


Figura 7 - Nova apresentação de dados com a Tabela Dinâmica

Se você observou, quando a Tabela Dinâmica foi criada, uma nova barra de ferramentas surgiu na tela. É a barra de ferramentas Tabela dinâmica. A descrição dos botões que compõem esta Barra é a seguinte:

 

Botão Descrição
Abre uma lista com alguns comandos da barra de ferramentas Tabela dinâmica, a saber:

Assistente: Inicia o Assistente da tabela dinâmica, que irá orientá-lo durante a criação ou modificação de uma tabela dinâmica.

Atualizar dados: Atualiza os dados de uma tabela dinâmica quando os dados de origem são alterados.

Selecionar: Habilita as ferramentas de seleção de dados da tabela dinâmica

Fórmulas: Permite a inserção de fórmulas e campos calculados na tabela dinâmica

Campo: Dependendo da célula que estiver ativa na sua tabela dinâmica, altera os cálculos de subtotal e de itens para um campo ou modifica as propriedades da área de dados.

Opções: Exibe a caixa de diálogo Opções da tabela dinâmica, onde você define as opções avançadas de formatação, layout, gerenciamento de memória e dados externos da sua tabela dinâmica.

Ativa o Assistente, que irá guiá-lo pelas etapas necessárias para criar ou modificar uma Tabela dinâmica.
Campo da Tabela Dinâmica: Define as funções de resumo usadas para calcular os valores no campo de dados selecionado. Você também pode criar um cálculo personalizado, ou alterar o nome do campo de dados.

Insere um subtotal para um campo de linha ou um campo de coluna em uma Tabela dinâmica existente. Você também pode ocultar itens para que eles não sejam exibidos na Tabela dinâmica, renomear o campo, ou alterar a orientação do campo (de linha para coluna, por exemplo).

Mostrar páginas: Copia cada página de um campo de página para uma nova planilha da pasta de trabalho atual. Você pode então passar para uma planilha diferente para imprimir ou para aprofundar a análise dos dados.
Desagrupar: Separa um conjunto de itens agrupados. Cada ocorrência do grupo será substituída pelos itens nele contidos.
Agrupar: Agrupa itens em uma Tabela dinâmica por categoria para criar um item único a partir de múltiplos itens. Este recurso poderá ser útil, por exemplo, quando você quiser agrupar meses em trimestres para fins de análise, elaboração de gráficos ou impressão.
Ocultar detalhe: Oculta os dados de detalhe retraindo um item externo de um campo de linha ou de coluna em uma Tabela dinâmica. Este recurso poderá ser útil quando você quiser isolar segmentos de dados para análise, impressão ou elaboração de gráficos.
Mostrar detalhe: Exibe os dados de detalhe que foram ocultados com o subcomando Ocultar detalhe, expandindo um campo de linha ou um campo de coluna em uma Tabela dinâmica. Este comando é equivalente a um clique duplo sobre um item cujo detalhe foi ocultado.
Atualizar dados: Atualiza os dados de uma Tabela dinâmica depois que os dados fonte forem alterados. O MS-Excel extrai o intervalo de dados fonte especificado originalmente e depois substitui os dados da Tabela dinâmica.

Use este comando se os dados na tabela ou no arquivo de banco de dados de origem forem alterados e você quiser refletir as alterações no conjunto de resultados atual.

Selecionar rótulo: Seleciona a área de rótulo da linha selecionada.
Selecionar dados: Seleciona a área de dados da linha selecionada.
Selecionar rótulo e dados: Seleciona a área de rótulo e dados da linha selecionada.

11 Para obter um novo relatório, clique sobre o campo Supervisor, que está no campo superior esquerdo da planilha e arraste até o lado esquerdo do campo Produto. Ao soltar o botão esquerdo do mouse você terá o relatório reorganizado, conforme apresentado na Figura 8.

 


Figura 8 - Novo relatório, com dados de Supervisor e Produto apresentado pela Tabela Dinâmica.

12. Você pode experimentar o tipo de agrupamento que desejar. Arraste o campo Supervisor imediatamente a direita do campo Produto… pronto você tem um novo tipo de relatório em mãos e sem muito trabalho para chegar ao resultado desejado. Qualquer um dos campos indicados em cinza na Tabela Dinâmica, exceto o campo Dados pode ser trocado de posição entre as áreas de Página, Linha e Coluna. Estas áreas foram indicadas inicialmente no Assistente de Tabela Dinâmica, representado na Figura 3 e 4.

13.Vamos organizar um outro tipo de relatório. Devolva os campos de Supervisor e Produto para a área de Página. Traga para a aérea de Linha da Tabela Dinâmica o campo Data. Podemos criar algumas visões de agrupamento de dados, mesmo aquelas que não estavam previstas na base de dados. Selecione qualquer célula do campo de data, e clique no botão Agrupar da Barra de Ferramentas Tabela dinâmica, ele é representado por uma seta para a direita. Clicando neste botão você terá a caixa de diálogo representada na Figura 9:

 


Figura 9 - Caixa de Diálogo de Agrupamento de dados do tipo Data e Hora

Nesta Caixa você pode agrupar itens de data ou de hora em um Relatório de Tabela Dinâmica em períodos específicos. O MS-Excel seleciona automaticamente os menores e maiores valores entre os itens. Você pode selecionar outros itens ou criar seus próprios intervalos se for necessário.

Os itens selecionados devem estar em um formato de data ou hora válido para o MS-Excel. Na caixa Iniciar em você define o primeiro item que se deseja no grupo e em Finalizar em você define o último item que se deseja no grupo.

Na caixa de listagem Por selecione um ou mais períodos de tempo para os itens no grupo.

Em Número de dias você define o número de dias que se deseja em um intervalo (por exemplo, períodos de 10 dias). Esta opção só se torna disponível ao selecionar-se a opção "Dias", na caixa de listagem Por.

14. Para o nosso caso, selecione o agrupamento por Meses e observe o resultado no relatório.

15. Repita o procedimento, selecionando o agrupamento por Meses e Anos e observe a diferença.

16. Para retirar o Agrupamento realizado, selecione antes uma célula qualquer que tenha o indicativo de mês ou ano e clique no botão de Desagrupar a barra de ferramentas. Seu relatório voltou a exibir os dados por data de venda e não mais agrupados.

Você também pode solicitar a classificação dos dados e outros tipos de agrupamento de informações. O botão de Agrupar também pode ser utilizado para campos de texto. Você pode solicitar atualização dos dados em relação a origem, clicando no botão Atualizar dados na Barra de Ferramentas.

Interagindo com a Tabela Dinâmica

O que permite o dinamismo neste tipo de tabela é o fato de que os campos podem estar em orientações diferentes, permitindo assim uma melhor organização dos dados, conforme a necessidade e o tipo de análise que será efetuada , através de variadas visualizações dos dados,.

Pode-se por exemplo, criar subtotais para determinados campos, classificados, classificar dados, criação de campos calculados, enfim, existe uma enorme flexibilidade em se obter uma visão dos dados, que permitirá uma análise mais favorável e compreensiva.

Reagrupe novamente os campos da tabela dinâmica, de tal modo que o campo Supervisor desça para o campo de Linha e mantenha dos demais campos em página.

Se você quiser saber qual foi o Supervisor que mais vendeu, basta selecionar qualquer uma das células que tenha o resultado de Realizado e clique no botão de Classificação decrescente na Barra de Ferramentas Padrão e observe o resultado. Este mesmo procedimento pode ser feito com o botão de Classificação ascendente ou com qualquer outro campo da tabela dinâmica.

Alterando o tipo de cálculo dos dados.

Quando criamos uma tabela dinâmica, a função Soma é aplicada como padrão para os dados que possuem conteúdo numérico e a função de Cont.Valores para outros tipos de dados. Entretanto, é possível alterar a função de cálculo para uma outra categoria, como por exemplo Média.

Para tanto, basta selecionar o campo de Dados que se deseja alterar e definir a nova função desejada.

Suponhamos agora que queiramos saber a quantidade de itens vendidos por cada Supervisor, ao invés do valor vendido. Temos que alterar a função do campo Realizado de Soma para Cont.Valores da seguinte forma:

1 Selecione uma célula que faça referencia ao campo Realizado.

2 Clique no botão Campo da tabela dinâmica, o terceiro da barra de ferramentas de Tabela Dinâmica.

3 Na Caixa de Diálogo representada na Figura 10, na lista Resumir por selecione a função Cont.Valores (a segunda função da lista) .

 


Figura 10 - Caixa de Diálogo de Campo da tabela dinâmica

Clique no botão OK da Caixa de Diálogo e observe que os dados de Realizado agora apresentam o número de vendas realizadas por Supervisor e não mais os resultados do total financeiro vendido.

Na Figura 10 é apresentado o botão Número… Você pode utilizar para formatar o número do modo que desejar.

Não se esqueça de devolver a função Soma para o campo Realizado para podermos continuar com o exercício.

Criando um campo calculado

Outro recurso disponível ao trabalharmos com uma tabela dinâmica é a possibilidade de criação de campos calculados. Um campo calculado é o resultado de uma operação que é feita com base nos valores de outros campos da tabela, através da aplicação de fórmulas. Normalmente este tipo de informação não está presente na base de dados e necessitamos desta informação no relatório.

Nas versões anteriores, era necessário criar um campo na base de origem e efetuar o cálculo. Com o recurso de campo calculado ganha-se mais performance, pelo fato de não haver a necessidade de armazenamento deste campo, pois o mesmo é reflexo do valor de campos já existentes, ganhando também mais espaço em disco.

Para o nosso exemplo, gostaríamos de apresentar o Desempenho de vendas de cada Supervisor, apresentando um resultado percentual de quanto cada Supervisor conseguiu cumprir da sua meta.

1 Na barra de ferramentas Tabela Dinâmica, clique no botão Tabela Dinâmica e, em seguida clique na opção Fórmula e escolha Campo Calculado. A Caixa de Diálogo Inserir campo calculado será exibida, conforme figura 11:

 


Figura 11 - Caixa de Diálogo de Campo Calculado

onde:

Nome Nome do campo calculado que está sendo criado

Fórmula Expressão que determina o conteúdo do novo campo

Campos Campos existentes na tabela, que poderão ser utilizados na construção da fórmula

Inserir campo Insere o campo selecionado na fórmula

Adicionar Adiciona o novo campo criado à lista de campos

Excluir Exclui o campo selecionado da tabela

2 Nomeie o novo campo como Desempenho

3 No campo Formula, digite a seguinte expressão: =Realizado/Previsto

Para escrever a fórmula você pode utilizar o botão Inserir Campo. Com isto minimizamos a possibilidade de erro na montagem da fórmula.

4 Clique no botão OK e observe o resultado.

5 Salve o arquivo para poder continuar o trabalho.

Como você pode Ter observado, o recurso de Tabela Dinâmica do MS-Excel é muito prático e apresenta um volume muito grande de recursos. Neste artigo estamos apresentando somente alguns dos muitos efeitos pirotécnicos que a Tabela Dinâmica pode oferecer.

Microsoft Query

Outra possibilidade que o MS-Excel nos oferece para elaborar uma tabela dinâmica é buscar as informações diretamente na base de dados, sem a necessidade de copiarmos as informações para a planilha para depois gerar a tabela dinâmica. Para realizar esta tarefa podemos utilizar o MS-Query.

O MS-Query é um aplicativo visualizador de banco de dados, que proporciona um fácil acesso a vários formatos de bancos de dados, podendo ser usado como um programa independente ou como um suplemento para o MS-Office. Quando usado como um programa independente, você pode exibir e manipular arquivos de banco de dados na janela Microsoft Query.

A tabela abaixo lista alguns dos formatos compatíveis com o MS-Query, que dependem diretamente dos controladores ODBC instalados.

Tipo de banco de Dados Versões

Access 1.0, 1.1, 2.0, 7.0 e 8.0
Dbase III, IV e 5.0
Excel 3.0, 4.0, 5.0, 7.0 e 8.0
FoxPro 2.0, 2.5 e 2.6
Paradox 3.x, 4.x e 5.x
SQL Server 1.1, 4.2, 6.0, 6.5 e Sybase 4.2
ODBC ODS Gateway  
Texto  

Para o nosso exercício usaremos o arquivo base.mdb, que se encontra no disquete fornecido junto com a revista. Este arquivo contém 5 tabelas de dados. As tabelas apresentam as seguintes informações:

Tabela Descritivo

Cliente Tabela dos Clientes da Empresa

Pedidos Tabela com a relação de pedidos realizados pelos Clientes

Produtos Tabela com todos os Produtos comercializados pela Empresa

Funcionarios Tabela com os nomes dos Funcionários do Depto de Vendas. Esta informação será útil se você desejar acumular informações para pagamento de comissão.

Fornecedores Tabela com informações sobre os seus Fornecedores. Esta informação será útil se você desejar saber como esta a sua distribuição de vendas por Fornecedor, etc.

A Figura 12 apresenta todos os campos das tabelas com os seus respectivos relacionamentos:

 


Figura 12 - Tabelas do Banco de Dados Base.mdb e seus relacionamentos
 

Vamos iniciar a montagem de uma nova tabela dinâmica, com dados oriundos de uma base de dados em MS-Access (arquivo Base.mdb).

1 Inicialmente abra uma nova pasta de trabalho, e no menu Dados selecione, Relatório da Tabela dinâmica. Nesta etapa, selecione a opção dos dados, Origem de dados externos (a segunda opção da lista), para ativarmos o MS-Query para buscar os dados em um arquivo no formato MDB.

Clique no botão Avançar para ativar a etapa 2 do Assistente da tabela dinâmica e você terá a Caixa de Diálogo indicada na Figura 12,

 


Figura 12 - Assistente da Tabela Dinâmica para dados externos ao MS-Excel

Clique no botão Obter dados… para ativar o MS-Query e iniciar a pesquisa, e teremos a Caixa de Diálogo indicada na Figura 13,

 


Figura 13 - MS-Query. Indique a origem dos dados

2 Na Caixa de Diálogo Escolher a origem de dados, selecione a opção <Nova origem de dados>. Vamos utilizar este recurso para criar um nome próprio de acesso, facilitando a identificação e posterior uso, do que simplesmente indicar que vamos acessar um Banco de dados MS-Access. Em seguida, dê um clique no botão OK. A caixa de diálogo Criar nova origem de dados será exibida, conforme apresenta a Figura 14.

Figura 14 - Caixa de Diálogo Criar nova origem de dados do MS-Query

3 Na caixa de diálogo Criar nova origem de dados, clique no campo identificado com o número 1 e digite um nome para a fonte de dados que estamos criando. Para esse exemplo, digite “Desempenho de Vendas Mondial”.

4 Após termos definido um nome para a base de dados, a caixa identificada com o número 2 foi habilitada. Nela, poderemos definir qual o driver que utilizaremos para acessar a nossa base de dados. Nesse exemplo, selecione na lista a opção “Driver para o Microsoft Access (*.mdb)”.

5 No item 3, dê um clique no botão Conectar, e o MS-Query irá apresentar a caixa de diálogo ODBC Microsoft Access 97 Setup onde podemos definir os parâmetros relativos à base de dados utilizada.

6 Clique no botão Select e direcione para a sua pasta de trabalho, e aponte para o arquivo Base.mdb. Uma vez selecionado o arquivo, dê um clique no botão OK. O resultado da indicação do arquivo de dados está sendo apresentado na Figura 15.

Figura 15 - Selecionando o arquivo de dados da pesquisa

7 uma vez que você já selecionou o arquivo desejado, clique no botão OK da Caixa de Diálogo ODBC Microsoft Access 97 Setup, e teremos novamente a Caixa de Diálogo Escolher a origem de dados (a Caixa de Diálogo inicial), indicada na Figura 16. Isto significa que a nossa base de dados foi selecionada corretamente.

 


Figura 16 - Caixa de Diálogo Escolher aorigem de dados

Observe que a Caixa de Diálogo já trouxe selecionada a opção “Desempenho de Vendas Mondial”.

8 Mantenha a opção “Desempenho de Vendas Mondial” selecionada e clique no botão OK da Caixa de Diálogo Escolher a origem de dados. A Caixa de Diálogo Assistente de Consulta - escolher colunas (Figura 17) será exibida para que você possa determinar quais campos (ou colunas) das tabelas de dados você utilizará para na sua consulta.

Figura 17 - Caixa de Diálogo para escolher os campos (ou colunas) de dados da pesquisa

9 Para exibir os campos de cada tabela disponível, dê um clique no sinal de mais que está a esquerda do nome da mesma e os campos serão exibidos. Selecione o campo desejado para a consulta e então, clique no botão > para adicionar o campo na Lista Colunas em sua consulta. Você pode transferir o nome do campo dando um duplo clique sobre o mesmo. Observe que ao selecionar o nome, ele deixa de fazer parte da relação Tabelas e colunas disponíveis.

Os campos que serão selecionados de cada tabela são:

Tabela Campo Descritivo
Pedidos DataPedido Apresenta a data do pedido.
  QtPedido Apresenta a quantidade que o Cliente solicitou no Pedido.
Clientes Empresa Apresenta o nome da Empresa que solicitou o Pedido.
  Estado Apresenta o Estado da Empresa. Útil para efeito de estatística de distribuição de venda por Estado.
Produtos Categoria Apresenta a Categoria do Produto solicitado.
  Produto Apresenta o nome do Produto solicitado.
  PrecoUnitario Apresenta o Preço Unitário do Produto solicitado.
Fornecedores NomeFornec Apresenta o Nome do Fornecedor. Útil para efeito de estatística para apresentar o perfil dos seus Fornecedores.
Funcionarios NomeFunc Apresenta o Nome do Funcionário que realizou a Venda. Informação útil para levantamento de dados para pagamento de comissão.

Alguns aplicativos como o MS-Access usam o termo tabela para o local onde armazenam os dados, outros aplicativos como o MS-Excel e aplicativos baseados em texto usam o termo arquivo, o MS-Query, recupera os dados como tabelas.

10 Clique no botão Avançar para apresentar para a próxima etapa da criação da consulta. O MS-Query exibe a janela Assistente de consulta - filtrar dados, conforme a Figura 18.

Figura 18 - Assistente de Consulta para filtrar dados

11 Aqui podemos definir critérios para filtrar os dados conforme a necessidade. Para isso, basta selecionar o campo de coluna na qual desejamos aplicar o filtro e, em seguida, definir os critérios através das caixas de combinação exibidas do lado direito da janela. Neste ponto de nosso exemplo, não iremos definir nenhum tipo de critério de seleção de dados, portanto, clique sobre o botão Avançar para prosseguir.

12 Na Caixa de Diálogo Assistente de consulta - ordem de classificação (Figura 19) podemos definir os critérios para classificação dos dados que estamos extraindo da base de dados. Podemos estabelecer até três chaves de classificação para os dados. Em nosso exemplo, estaremos classificando os dados pelo nome da Empresa e, em seguida pelo nome do Produto, caso tenhamos mais de um registro da mesma empresa.

Figura 19 - Caixa de Diálogo para definir a ordem de classificação dos dados

13 Clique no botão Avançar para a etapa final do Query Wizard.

Figura 20 - Etapa final do Assistente de consulta

onde:

Retornar dados ao Microsoft Excel Retorna os dados conforme a configuração efetuada na consulta para a planilha do MS-Excel.

Exibir dados ou editar consulta no Microsoft Query Permite a visualização dos dados ou a edição dos parâmetros da consulta através do MS-Query.

Salvar consulta - Salva as configurações da consulta criada.

14 Para o nosso exercício selecione a opção Exibir dados ou editar consulta no Microsoft Query e clique no botão Concluir. Vamos apresentar a tela do MS-Query (Figura 21) e aprender a incluir um novo campo de dados, para posteriormente finalizar a consulta.

Figura 21 - Tela de trabalho do MS-Query com a pesquisa montada

15 Nossa base de dados não apresenta o campo com o valor total de venda de cada produto (apresenta o Preço Unitário e a Quantidade Pedida). No MS-Query podemos adicionar um campo apresentando esta informação. No Menu Registros selecione a opção Adicionar coluna… (Figura 22) para adicionarmos um novo campo de dados (calculado).

Figura 22 - Caixa de Diálogo Adicionar coluna do MS-Query

Em Campo devemos indicar qual a operação matemática que desejamos realizar. No nosso exercício será a seguinte fórmula: Pedidos.QtPedido*Produtos.PrecoUnitario

Em Cabeçalho da Coluna digitamos Total do Pedido. Uma vez feita as indicações na Caixa de Diálogo, clique no botão Adicionar e o MS-Query incluirá o campo no final da tabela.

16 Com os dados já todos estruturados, podemos retornar para o MS-Excel. No Menu Arquivo selecione a opção Retornar dados ao Microsoft Excel. Deste modo retornamos para o Assistente da tabela dinâmica, na etapa 2 de 4. Clique no botão Avançar > para acessarmos a etapa 3 de 4 do Assistente. Nesta etapa vamos distrubuir os campos pelas áreas de Página, Linha e Dados conforme indicado na Figura 23. Lembre-se que o campo PrecoUnitario não será exibido no relatório porque apresentará a soma dos preços unitários dos pedidos indicados em cada linha, gerando uma informação desnecessária para o nosso relatório.

Figura 23 - Etapa 3 do Assistente da tabela dinâmica para distribuição dos campos pelo relatório

Em contrapartida, o campo com o valor total dos pedidos será muito útil porque apresenta o valor total de cada produto.

Dê um duplo clique sobre o campo Pedidos.QdPedido*Produtos.PrecoUnitario e será apresentada a Caixa de Diálogo (Figura 24) do Campo da tabela dinâmica. Troque o nome do campo para Total do Pedido.

Figura 24 - Campo da tabela dinâmica

Dê um clique no botão Número… e formate o campo com separador de milhar e com 2 casas decimais. Ao selecionar o formato desejado clique no botão Ok para fechar a Caixa de Diálogo de Formatar células. Com este procedimento você retorna para a Caixa de Diálogo do Campo. Clique no botão OK para fechar a caixa de diálogo e retornar para o Assistente da tabela dinâmica. Não esqueça de formatar o campo de QtPedido. Se desejar altere o seu nome.

Como você já conhece os procedimentos, faça o ajuste que você desejar na tabela dinâmica e finaliza a sua montagem.

17 Com a tabela dinâmica finalizada, ajuste a apresentação da área de Dados, apresentando as informações na horizontal. Deste modo você obterá um resultado próximo do indicado na Figura 25.

Figura 25 - Resultado da montagem da tabela dinâmica

Como resultado final você pode incluir um campo calculado apresentando o valor de comissão que será pago para cada pedido.

Os demais procedimentos você já conhece. Arraste os campos nas suas posições e consulte o resultado gerado. Calssifique a informação do modo que você desejar. Gere outros tipos de totalizadores, como por exemplo, média vendida, quantidade de pedidos, etc.

Pode-se obter os seguintes relatórios:

  • Venda por Estado,
  • Por Empresa,
  • Por Categoria de Produto e Empresa,
  • Por Funcionário,
  • Por Fornecedor de Produtos,
  • Por Período de Vendas (com sazonalidade),
  • e muitos outros..

18 Você está com dúvidas a respeito de algum dado apresentado? Você não concorda com o valor total apresentado na categoria Bebida? Não tem problema, você não precisa voltar para a sua aplicação em MS-Access para saber quais são os registros que geraram a informação do volume de vendas de Bebidas. Basta você dar um duplo clique sobre o campo de Soma de QtPedido, e o MS-Excel apresentará o recurso de drilldown, isto é, será criada uma nova planilha na pasta de trabalho ativa, com todos os registros da sua base de dados que geraram a informação desejada. Basta consultar. Como você pode observar, os recursos apresentados pela Tabela Dinâmica do MS-Excel são muito grandes e oferecem uma maleabilidade muito grande para obter os resultados desejados. Basta ter conhecimento da base de dados que estamos trabalhando e um pouco de criatividade para elaborarmos os melhores relatórios que podem ser feitos. Lembramos que todos estes recursos estão disponívies aos usuários sem a necessidade de recursos de macro programação. A utilização do VBA (Visual Basic for Applications) que acompanha o MS-Excel torna estas atividades ainda mais fáceis de serem executadas, porém não são indispensáveis para o seu uso no dia-a-dia. Na home page da Mondial Informática (http://www.mondial.com.br/) você pode fazer download de um Pasta de Trabalho que apresenta um pequeno sistema com segurança, entrada de dados e relatório estruturado com a Tabela Dinâmica. Pode ser um bom exemplo do que pode ser feito com o produto. E isto tudo sendo realizado rapidamente a baixo custo para a sua Empresa. Para aqueles que desejam este tipo de produtividade, utilizar o MS-Excel será um passo fácil neste sentido.

Para aqueles que não utilizam o MS-Excel desejamos boa sorte.
João Benito Savastano (benito@mondial.com.br) é Diretor de Tecnologia e Paulo Marcos Misael (info@mondial.com.br) é Analista de Sistemas da Mondial Informática S/C Ltda, Empresa Solution Provider Partner Microsoft, especializada em Treinamento e Desenvolvimento na Plataforma Office/VBA.