Uma das coisas que mais listo no PowerPivot para Excel é a capacidade de adicionar tabelas de pesquisa a seus conjuntos de dados. Na maioria das vezes, os dados com os quais você está trabalhando não têm todos os campos necessários para sua análise. Por exemplo, você pode ter um campo de data, mas precisa agrupar seus dados por trimestre. Você pode escrever uma fórmula, mas é mais fácil criar uma tabela de pesquisa simples no ambiente do PowerPivot.
Você também pode usar essa tabela de consulta para outro agrupamento, como o nome do mês e a primeira / segunda metade do ano. Em termos de data warehousing, você está criando uma tabela de dimensão de data. Neste artigo, fornecerei algumas tabelas de dimensão de exemplo para aprimorar seu projeto do PowerPivot para Excel.
Nova tabela de dimensão de texto (pesquisa)
Vamos considerar uma tabela com dados do pedido. Suponha que a tabela tenha campos para cliente, data do pedido, total do pedido e tipo de pedido. Vamos nos concentrar no campo do tipo de pedido. Suponha que o campo do tipo de pedido inclua valores como:
- Netbooks
- Desktops
- Monitores
- Projetores
- Impressoras
- Scanners
- Câmeras digitais
- Câmeras Digitais SLR
- Câmeras de filme
- Filmadoras
- Telefones de Escritório
- Telefones inteligentes
- PDAs
- Acessórios para Celulares
Na realidade, você teria códigos para isso, mas para manter esse exemplo simples, assuma que esses são os valores reais na tabela de pedidos. Usando o PowerPivot para Excel, você poderia facilmente agrupar seus pedidos por tipo de pedido.
E se você quisesse um agrupamento diferente? Por exemplo, suponha que você precise de um agrupamento de categorias como computadores, câmeras e telefones. A tabela de pedidos não possui um campo de categoria, mas você pode facilmente criá-la como uma tabela de consulta no PowerPivot para Excel. A tabela de pesquisa de amostra completa está abaixo tabela 1 .
Aqui estão os passos:
- Passo 1: Você precisa de uma lista distinta do campo de tipo para sua tabela de pesquisa. Este será o seu campo de pesquisa. A partir do seu conjunto de dados, crie uma lista distinta de valores no campo do tipo de pedido. Digite a lista distinta de tipos em uma pasta de trabalho do Excel. Etiquetar a coluna Tipo .
- Passo 2: Na coluna ao lado da sua coluna de pesquisa (Tipo), adicione o novo campo no qual você deseja agrupar. No nosso exemplo, adicione uma coluna com um rótulo chamado Categoria .
- Etapa 3: Para cada valor em sua lista distinta de valores (tipos neste exemplo), adicione o valor correspondente Categoria valores. Em nosso exemplo simples, insira Computadores , Câmeras ou Telefones no Categoria coluna.
- Passo 4: Copie o Tipo e Categoria tabela de dados em sua área de transferência.
- Passo 5: Abra a pasta de trabalho do Excel com os dados do pedido no PowerPivot para Excel. Inicie a janela do PowerPivot. Clique em Colar, o que trará sua nova tabela de consulta. Dê um nome à mesa e certifique-se de verificar Use a primeira linha como cabeçalhos de coluna. Clique Está bem. Você criou uma tabela de pesquisa no PowerPivot.
- Passo 6: Crie uma relação entre o Tipo campo no Ordem mesa e o Categoria campo na tabela de pesquisa. Clique no desenhar fita e escolha Criar Relacionamento. Faça as seleções no Criar Relacionamento diálogo e clique Crio.
Ao criar uma Tabela Dinâmica no Excel com base nos dados do PowerPivot, você poderá agrupar pelo seu novo Categoria campo. Lembre-se de que o PowerPivot para Excel oferece suporte apenas a Internins Joins. Se você tem um tipo de pedido ausente de sua tabela de consulta, todos os registros correspondentes para esse tipo serão ausentes de qualquer tabela dinâmica com base nos dados do PowerPivot. Você precisará verificar isso de tempos em tempos.
Tabela Dimensão de Data (Lookup)
A tabela de consulta de data provavelmente será necessária na maioria dos projetos do PowerPivot para Excel. A maioria dos conjuntos de dados tem algum tipo de campo (s) de data. Existem funções para calcular o ano e o mês.
No entanto, se você precisar do texto real do mês ou do trimestre, precisará escrever uma fórmula complexa. É muito mais fácil incluir uma tabela de dimensão de data (pesquisa) e associá-la ao número do mês no seu conjunto de dados principal. Você precisará adicionar uma coluna à sua tabela de pedidos para representar o número do mês no campo de data do pedido. A fórmula DAX para mês no nosso exemplo é = MÊS (data do pedido). Isso retornará um número entre 1 e 12 para cada registro. Nossa tabela de dimensões fornecerá valores alternativos, vinculados ao número do mês. Isso fornecerá flexibilidade em sua análise. A tabela de dimensão de data de amostra completa está abaixo mesa 2 .
A dimensão de data ou a tabela de consulta incluirá 12 registros. A coluna do mês terá os valores de 1 a 12. Outras colunas incluirão texto do mês abreviado, texto do mês completo, trimestre, etc. Aqui estão os passos:
- Passo 1: Copie a tabela da Tabela 2 abaixo e cole no PowerPivot. Você poderia criar essa tabela no Excel, mas estou economizando seu tempo. Você deve poder colar diretamente dos dados selecionados abaixo se estiver usando o Internet Explorer ou o Edge. O PowerPivot seleciona a formatação da tabela no meu teste. Se você estiver usando outro navegador, talvez seja necessário colar primeiro no Excel e copiá-lo do Excel para obter a formatação da tabela.
- Passo 2: Abra a pasta de trabalho do Excel com os dados do pedido no PowerPivot para Excel. Inicie a janela do PowerPivot. Clique Colar que trará sua tabela de pesquisa copiada da tabela abaixo ou do Excel. Dê um nome à mesa e certifique-se de verificar Use a primeira linha como cabeçalhos de coluna. Clique Está bem. Você criou uma tabela de consulta de data no PowerPivot.
- etapa 3: Criar uma relação entre o Mês campo no Ordem mesa e o MonthNumber campo na tabela de pesquisa. Clique no desenhar fita e escolha Criar Relacionamento. Faça as seleções no Criar Relacionamento diálogo e clique Crio.
Novamente, com a adição de uma dimensão de data, você poderá agrupar os dados em sua Tabela Dinâmica usando qualquer um dos diferentes valores da tabela de consulta de datas. Agrupar por trimestre ou o nome do mês será um piscar de olhos.
Tabelas de dimensão de amostra (pesquisa)
tabela 1
| Tipo | Categoria |
| Netbooks | Computador |
| Desktops | Computador |
| Monitores | Computador |
| Projetores e telas | Computador |
| Impressoras, Scanners e Fax | Computador |
| Configuração e serviço de computadores | Computador |
| Acessórios para Computadores | Computador |
| Câmeras digitais | Câmera |
| Câmeras Digitais SLR | Câmera |
| Câmeras de filme | Câmera |
| Filmadoras | Câmera |
| Câmeras e Filmadoras Acessórios | Câmera |
| Telefones Domésticos e de Escritório | telefone |
| Telefones de tela de toque | telefone |
| Smartphones e PDAs | telefone |
mesa 2
| MonthNumber | MonthTextShort | MonthTextFull | Trimestre | Semestre |
| 1 | Jan | janeiro | Q1 | H1 |
| 2 | Fevereiro | fevereiro | Q1 | H1 |
| 3 | Mar | Março | Q1 | H1 |
| 4 | Abril | abril | Q2 | H1 |
| 5 | Maio | Maio | Q2 | H1 |
| 6 | Junho | Junho | Q2 | H1 |
| 7 | Julho | Julho | Q3 | H2 |
| 8 | Agosto | agosto | Q3 | H2 |
| 9 | Set | setembro | Q3 | H2 |
| 10 | Out | Outubro | Q4 | H2 |
| 11 | Nov | novembro | Q4 | H2 |
| 12 | Dez | dezembro | Q4 | H2 |
