Conhecendo o recurso PowerPivot do Microsoft Excel 2010 – Parte II


PARTE II – Importando Base de Dados Access via Instrução SQL

 

 

Agora, vamos importar a mesma tabela pelo método de importação que usa Banco de Dados Access pelo método de importação SQL. Para isso clique na guia Início, grupo Obter Dados Externos, botão De Banco de Dados. Como se trata de um banco de dados feito no Access 2003, use a opção Do Access. Selecione o arquivo Sistema de Vendas.mdb, agora selecione a segunda opção de importação.

 


Em nome amigável de consulta digite tblClientes2. Em Instrução SQL digite a consulta abaixo:

SELECT *

FROM tblClientes

Onde:

SELECT *

Seleciona todos os dados cadastrados na tabela;

FROM tblClientes

Os dados serão retirados da tabela tblClientes do banco de dados em uso.

 

Após digitada a consulta, clique no botão Concluir e assim que a importação de dados estiver terminada, clique no botão Fechar. Como vemos, os resultados são os mesmos.

1 – Vamos supor que o usuário deseja importar apenas a coluna ID_Cliente da tabela tblClientes. Seguindo o primeiro método teremos que usar o botão Visualizar & Filtrar, presente na janela de seleção de tabelas. Basta selecionar a tabela tblClientes e clicar no botão Visualizar & Filtrar. Uma nova janela se abrirá, deixe selecionado apenas o campo ID_Cliente, clique no botão OK e conclua a importação.

 


2 – O mesmo pode ser feito através de uma consulta SQL. Exemplo:

SELECT ID_Cliente

FROM tblClientes

Onde:

SELECT ID_Cliente

Seleciona os dados cadastrados na coluna ID_Cliente;

From tblClientes

Os dados serão retirados da tabela tblClientes do banco de dados em uso.

 


Calculando

 

Há quatro maneiras de calcular valores, baseando-se nos valores de diferentes tabelas de um banco dados:

  1. – Através de uma consulta SQL na hora da importação;
  2. – Importando uma consulta feita no Access;
  3. – Importando algumas colunas de uma tabela e editar como consulta.
  4. -Usando tabelas relacionadas e fórmulas DAX

1 – Através de uma consulta SQL na hora da importação

 

Ainda utilizando a mesma pasta de trabalho, utilizaremos o PowerPivot e o Banco de dados – Sistema de Vendas.mdb, feito em Microsoft Access 2003, para realizar o seguinte exercício das três maneiras citadas acima: mostre, em uma planilha na janela do PowerPivot, o código do cliente que realizou a compra, o preço unitário do produto comprado, a quantidade pedida, e o valor da compra, que será o campo calculado.

  1. Primeiro temos que abrir a janela do PowerPivot. Para isso: vá à guia PowerPivot, grupo Iniciar, botão Janela do PowerPivot.

 

  1. Com a Janela do PowerPivot aberta, clique na guia Início, grupo Obter Dados Externos, botão De Banco de Dados, escolha a opção Do Access. Selecione o banco de dados Sistema de vendas.mdb, e clique em Avançar. Nesse passo, escolha a segunda opção de importação (“Escrever uma consulta que especificará os dados a serem importados”).

 

  1. Em nome amigável digite ValorPedido1. E no campo instrução digite:

 

SELECT PE.ID_Cliente, PR.PrecoUnitario,PE.QtPedido, (PE.QtPedido*PR.PrecoUnitario) as PrecoPedido

From tblProdutos as PR, tblPedidos as PE

Where PR.ID_Produto=PE.ID_Produto

Onde:

SELECT PE.ID_Cliente, PR.PrecoUnitario,PE.QtPedido, (PE.QtPedido*PR.PrecoUnitario) as PrecoPedido

 

Seleciona parte de um conjunto de registros que atendam as condições definidas pelo comando. Aqui, mostra identificação do cliente e quantidade pedida de tblPedidos, preço unitário da tabela tblProdutos e o resultado da multiplicação dos valores presentes nos campos quantidade pedido e preço unitário resultando no preço do pedido, nomeando essa nova coluna de PrecoPedido;

From tblProdutos as PR, tblPedidos as PE

Determina as tabelas de onde serão retiradas as informações e as dá apelidos (PR e PE);

Where PR.ID_Produto = PE.ID_Produto

Estabelece uma igualdade que liga as duas tabelas, neste caso a igualdade é do campo ID_Produto.

 

  1. Clique em Concluir. Assim que a importação for concluída, clique em Fechar. Note que além das colunas do banco de dados, aparece outra coluna com o nome dado na consulta, que é resultado da multiplicação da coluna de preço unitário e unidades pedidas.

 

2 – Importando uma consulta feita no Access

 

  1. Utilizando o segundo método, o procedimento começa no Access. Abra o banco de dados que estamos utilizando pelo Access. Crie uma nova consulta, pela guia Criar, grupo Consultas, botão Design da Consulta. Na caixa de dialogo Mostrar Tabelas, selecione as tabelas que serão utilizadas para a consulta (tblProdutos e tblPedidos).

 

  1. Agora exiba os campos: ID_Cliente (da tabela tblPedidos), QtPedido (também da tabela tblPedidos) e PrecoUnitario (da tabela tblPdodutos). No quarto Nome de Campo digite a fórmula abaixo:

 

PrecoPedido: [QtPedido]*[PrecoUnitario]

Onde:

PrecoPedido

é o nome da coluna que comportara os resultados da consulta;

[QtPedido]*[PrecoUnitario]

multiplicação entre os valores quantidade pedido (tblPedidos) e preço unitário (tblProdutos).

 

  1. Salve a consulta com o nome ValorPedido2.

 

  1. Volte a janela do PowerPivot. Siga o mesmo caminho para a importação de dados do Access e selecione o mesmo banco de dados que foi usado anteriormente, agora usando a primeira opção de importação. Ao invés de selecionar uma ou várias tabelas, basta clicar na consulta ValorPedido2 e concluir a importação.

 

  1. Note que os dados exibidos são idênticos aos dados exibidos pelo método usado anteriormente.

 

3 – Importando algumas colunas de uma tabela e editar como consulta.

 

  1. O terceiro método começa com a importação de uma tabela manualmente. Neste caso importaremos a tabela tblPedidos, por esta possuir mais dados que serão visualizados pela consulta.

 

  1. Escolha o banco de dados Sistema de Vendas.mdb, selecione a primeira opção de importação e siga os seguintes passos de importação:

 

  1. Escolha apenas a tabela tblPedidos, em Visualizar & Filtrar selecione apenas os campos ID_Cliente e QtPedido.

 

  1. Como você pode ver, apenas os campos selecionados são exibidos. Mudaremos isso acessando a guia Design, grupo Propriedades, botão Propriedades da Tabela. Na caixa de gialogo Editar Propriedades da Tabela, no campo Alterar para, selecione Editor de Consulta e digite a consulta abaixo:

 

SELECT [tblPedidos].[ID_CLIENTE],[tblPedidos].[QtPedido], [tblProdutos].[PrecoUnitario], [tblPedidos].[QtPedido]*[tblProdutos].[PrecoUnitario]

FROM [tblPedidos], [tblProdutos]

WHERE [tblPedidos].[ID_Produto]=[tblProdutos].[ID_Produto]

Onde:

SELECT [tblPedidos].[ID_CLIENTE],[tblPedidos].[QtPedido], [tblProdutos].[PrecoUnitario], [tblPedidos].[QtPedido]*[tblProdutos].[PrecoUnitario]

Esta consulta é semelhante a primeira consulta, porém está não usa apelidos para as tabelas, referenciando-as com seu todo o seu nome antes de seus campos;

FROM [tblPedidos], [tblProdutos]

Determina as tabelas de onde serão retiradas as informações;

WHERE [tblPedidos].[ID_Produto] = [tblProdutos].[ID_Produto]

Estabelece uma igualdade que liga as duas tabelas, neste caso a igualdade é do campo ID_Produto, também sem a utilização de apelidos.

Através deste método, obtemos o mesmo resultado.

 

4 – Usando tabelas relacionadas e fórmulas DAX

 

  1. Para realizar cálculos entre planilhas o PowerPivot necessita que essas planilhas estejam relacionadas entre si.

 

  1. Abra uma nova pasta de trabalho no Excel 2010 e abra a janela do PowerPivot. Clique na guia Inicio
    da janela do PowerPivot,
    grupo Obter dados externos botão De banco de Dados, opção
    Do access. Selecione o banco de dados Sistema de Vendas, clique no botão Avançar, selecione a primeira opção de importação (“Selecionar itens…”) e depois clique em Avançar, selecione apenas tblPedidos e tblProdutos, clique no botão
    Concluir.

 


 

  1. Para criar uma relação clique na guia Design, botão Criar Relação. Crie uma relação entre a planilha tblPedidos e a planilha tblProdutos usando campos em comum entre elas (no caso o campo ID_Produto).

 


 

  1. No conjunto de fórmulas oferecidas pelo PowerPivot (fórmulas DAX) existe uma fórmula chamada “Related“, essa fórmula é capaz de retornar o valor de uma coluna de uma outra planilha desde que as planilhas estejam relacionadas. Com isso é possível fazer cálculos entre planilhas no PowerPivot.

Na janela do PowerPivot na Planilha tblPedidos clique na célula Adicionar coluna e digite a fórmula:

 

=RELATED(tblProdutos[PrecoUnitario])*tblPedidos[QtPedido]

Onde:

=RELATED(tblProdutos[PrecoUnitario])

É a fórmula DAX em si. Busca o valor em uma tabela relacionada (tblProdutos) e traz para a planilha desejada

*tblPedidos[QtPedido]

 

Multiplica o valor encontrado na outra planilha pelo valor desejado.

 

  1. Renomeie o campo calculado para facilitar a identificação. Para isso dê um duplo clique no nome que deseja alterar, digite o nome desejado (no caso “Valor do Pedido”) e aperte ENTER.

Observe o resultado. O PowerPivot calcula todas as linhas automaticamente sem a necessidade de aplicar a fórmula em todas as linhas.

 


 

  1. Ainda é possível usar algumas das fórmulas do Excel para criar campos no PowerPivot. No nosso exemplo vamos usar a função SE (IF) para verificar se a empresa cumpriu uma determinada meta de compra.

Clique no Adicionar Campo
da planilha tblPedidos e digite uma nova fórmula:

=IF(tblPedidos[Valor do Pedido]<10000,”Não atingiu a meta”, “Atingiu a meta”)

Onde:

=IF(tblPedidos[Valor do Pedido]<10000,

Faz o teste lógico com a coluna desejada

“Não atingiu a meta”

Mostra o resultado se o teste lógico for verdadeiro

“Atingiu a meta”

Mostra o resultado se o teste lógico for falso

 

  1. Renomeie o campo calculado para “Metas”

Repare que no PowerPivot a função SE funciona um pouco diferente do que no Excel, já que os parâmetros da formula são separados por “,” e não por “;“.

Pressione a tecla ENTER e observe o resultado.


 

Criando Tabelas Dinâmicas no Excel

 

Usando os dados importados no PowerPivot é possível criar Tabelas Dinâmicas ou Gráficos no Excel 2010


 

1 – Usando a planilha tblClientes importada anteriormente vamos criar uma tabela dinâmica. Para isso na guia Início,grupo Relatórios, botão
Tabela Dinâmica. Criaremos uma tabela dinamica no Excel vinculada com o banco de dados importado na janela do power pivot, escolha a opção Tabela Dinâmica, será mostrada uma caixa de dialogo com duas opções:

  • Nova Planilha, na qual é criada uma nova planilha na pasta de trabalho para colocar os dados do PowerPivot

 

  • Planilha existente, onde é possível escolher qualquer planilha já criada na pasta de trabalho aberta.

 


 

2 – Escolha a opção Planilha Existente, a seguir selecione a célula A1 na planilha Tabela dinâmica, clique em OK.


 

3 – Será aberto o Assistente de Criação de Tabelas Dinâmicas do Excel mostrando todas as colunas importadas do PowerPivot, selecione todos os campos da tabela tblClientes.

 


 

4 – Clique em qualquer célula da tabela dinâmica. Será aberta a guia Ferramentas de tabela dinâmica. Clique na guia Design.


 

5 – Esta tabela não precisa se subtotais pois não trabalha com valores numéricos que devem ser somados ou contados, então vamos retirar a opção de subtotais, botão subtotais opção Não Mostrar Subtotais e no botão Totais Gerais escolher a opção Desativado Para Linhas e Colunas. No botão Layout de Relatório escolha a opção Mostrar em Formato de Tabela.

 

Repare que depois dessas configurações a tabela ficou mais fácil de ser analisada. Ainda é possível congelar a primeira linha e aplicar as opções de filtro em qualquer uma das colunas da tabela para facilitar ainda mais a visualização dos dados.


 

Há como calcular sub-totais baseando o que se deve soar de acordo com o que temos em comum entre diferentes linhas, como nomes, códigos, cidades, etc.:

6 – Abra a janela do PowerPivot e exporte para o excel em forma de tabela dinamica a planilha ValorPedido1 para a planilha Tabela Dinâmica 2

 

7 – Para trabalhar com sub-totais temos que montar a tabela indicando quais valores serão usados para a soma, no nosso caso os campos somados serão: QtPedido e PrecoPedido que deverão ser colocados no campo Valores, os campos ID_Cliente e PrecoUnitario devem ficar em rótulos de linha.

8 – Clique em qualquer célula da tabela dinâmica. Será aberta a guia Ferramentas de Tabela Dinâmica. Clique na guia Design.

9 – Nesta tabela podemos trabalhar com subtotais e total geral temos valores numéricos que podem ser somados ou contados, então vamos selecionar grupo Layout, botão Subtotais opção Mostrar Todos os Subtotais
no final de cada grupo e no botão Totais Gerais escolher a opção Ativado para Linhas e Colunas. No botão Layout de relatório escolha a opção Mostrar em Formato de Tabela.

Segmentação de Dados

 

É possivel criar segmentação de dados para filtrar os dados de uma tabela ou de um gráfico dinâmico que se encontra no Excel.

1 – Na planilha Tabela Dinâmica
selecione qualquer campo da tabela dinâmica.Clique na guia Inserir, grupo Filtro, botão Segmentação de Dados.

2 – Será exibida uma caixa de diálogo na qual poderão ser escolhidos os campos que servirão de filtro da tabela, todas as planilhas que estão no power pivot são mostradas com os seus campos à serem selecionados para realizar a filtragem.

3 – Cheque o campo Cargo
da tabela tblCliente e clique no botão OK. Na planilha aparecerá um quadro como esse:


4 – Nesse quadro é possível filtrar todos os dados da planilha Tabela dinâmica pelo cargo, pode-se usar mais de uma segmentação em uma tabela para conseguir informações ainda mais especificas.

 

Escolha um ou mais cargos e observe as alterações na tabela.

 

Trabalhando Segmentação com Chaves Estrangeiras

 

Na planilha Tabela Dinâmica 2 observe que os dados são mostrados a partir do ID_Cliente, supondo que a pessoa que deseja consultar essa tabela não conheça as empresas pelo seus respectivos ID’s podemos utilizar a segmentação de dados usando campos de outras tabelas. Para isso siga os passos abaixo:

 

  1. Para começar selecione qualquer campo da tabela dinâmica da Planilha Tabela dinâmica 2. A lista de campos do PowerPivot aparecerá.

 

  1. Para facilitar a filtragem vamos checar o campo Empresa de tblClientes. Isso resultará em um aviso do Excel: “A relação pode ser necessária”.

 

  1. Clique no botão Criar.


 

  1. Ao clicar em Criar
    o Excel checa a necessidade de criar uma relação entre as planilhas tblClientes e ValorPedido1, e cria a relação automaticamente.

 

  1. Clique em Fechar, para finalizar o assistente de criação de relacionamentos.

 

  1. Repare que o campo Empresa foi colocado automaticamente em Rótulos de Linha, como queremos utilizar o campo como uma segmentação de dados arraste-o para Segmentações de Dados Verticais.

 

Será criada uma segmentação com todos os nomes de empresas. Selecione alguns nomes e observe o resultado.

 

Trabalhando com Gráficos Dinâmicos

 

  1. Importe para o PowerPivot a query qryPedidosCompleta.

 

  1. Na janela do PowerPivot na guia início,botão Tabela dinâmica escolha a opção Gráfico dinâmico, a seguir escolha a opção Planilha existente e escolha a planilha Gráfico Dinâmico.

 

  1. Na lista de campos do PowerPivot escolha os campos Empresa e Total da planilha qryPedidosCompleta.

 

  1. Observe que há muitas empresas o que dificulta a visualização dos dados como um todo, para facilitar a visualização desses dados siga o procedimento abaixo:

 

  1. Insira uma segmentação de dados. Checando o campo Empresa da planilha qryPedidosCompleta, observe que nada acontece se a segmentação foi criada com alguma célula que não faz parte do gráfico dinâmico selecionado, isso ocorre pois nenhuma conexão foi feita entre essa segmentação e alguma fonte de dados.

 

  1. Clique na segmentação criada à pouco, em Ferramentas de Segmentação de Dados
    clique no botão Conexões de Tabela Dinâmica.

 

  1. Selecione a planilha que você deseja filtrar os dados (nesse caso planilha Dados para plan 3 Gráfico 1).

 

  1. Selecione uma ou mais empresas e observe o resultado.


 

Até a próxima!

MAC

 

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s