O modelo de dados do Excel (Power Pivot) permite que você conecte um grande conjunto de dados detalhados de dados reais a um orçamento de nível superior usando tabelas de junção.
Os orçamentos são feitos no nível superior - receita por linha de produto, por região, por mês. Os reais se acumulam lentamente ao longo do tempo - fatura por fatura, item de linha por item de linha. Comparar o pequeno arquivo de orçamento com os volumosos dados reais foi uma dor para sempre. Adoro este truque de Rob Collie, também conhecido como PowerPivotPro.com.
Para configurar o exemplo, você tem uma tabela de orçamento de 54 linhas: uma linha por mês por região por produto.
O arquivo de fatura está no nível de detalhe: 422 linhas neste ano.
Não há VLOOKUP no mundo que permitirá que você combine esses dois conjuntos de dados. Mas, graças ao Power Pivot (também conhecido como Modelo de Dados no Excel 2013+), isso se torna fácil.
Você precisa criar pequenas tabelas que eu chamo de “juntadores” para vincular os dois conjuntos de dados maiores. No meu caso, Produto, Região e Data são comuns entre as duas tabelas. A tabela de produtos é uma pequena mesa de quatro células. Idem para a região. Crie cada um deles copiando dados de uma tabela e usando Remover Duplicatas.
A tabela de calendário à direita foi realmente mais difícil de criar. Os dados de orçamento têm uma linha por mês, sempre caindo no final do mês. Os dados da fatura mostram datas diárias, geralmente dias de semana. Portanto, tive que copiar o campo Data de ambos os conjuntos de dados em uma única coluna e, em seguida, remover duplicatas para ter certeza de que todas as datas eram representadas. Em seguida, costumava =TEXT(J4,"YYYY-MM")criar uma coluna Mês a partir das datas diárias.
Se você não tiver o suplemento Power Pivot completo, será necessário criar uma tabela dinâmica a partir da tabela Orçamento e marcar a caixa Adicionar esses dados ao modelo de dados.
Conforme discutido na dica anterior, conforme você adiciona campos à tabela dinâmica, você terá que definir seis relacionamentos. Embora você pudesse fazer isso com seis visitas à caixa de diálogo Criar relacionamento, ativei meu suplemento Power Pivot e usei a visualização do diagrama para definir os seis relacionamentos.
Aqui está a chave para fazer todo esse trabalho: você pode usar os campos numéricos de Orçamento e Real. Mas se você quiser mostrar Região, Produto ou Mês na tabela dinâmica, eles devem vir das tabelas de junção!
Aqui está uma tabela dinâmica com dados provenientes de cinco tabelas. A coluna A está vindo do marcador de região. A linha 2 está vindo do joiner do Agenda. A segmentação de produtos é proveniente da junção de produtos. Os números do orçamento vêm da tabela Orçamento e os números reais vêm da tabela Fatura.
Isso funciona porque as tabelas do joiner aplicam filtros à tabela de orçamento e real. É uma bela técnica e mostra que o Power Pivot não é apenas para big data.
Assistir vídeo
- Você tem um pequeno conjunto de dados de orçamento de cima para baixo
- Você deseja comparar com um conjunto de dados reais de baixo para cima
- Os dados reais podem vir de um registro de fatura
- O modelo de dados permitirá que você compare esses conjuntos de dados de tamanhos diferentes
- Faça ambos os conjuntos de dados em uma tabela Ctrl + T
- Para cada campo de texto que você deseja relatar, crie uma tabela de junção
- Copie os valores e remova duplicatas
- Para datas, você pode incluir datas de ambas as tabelas e converter para o final do mês
- Faça os juntadores serem tabelas Ctrl + T
- Opcional, mas útil, nomear todas as cinco tabelas
- Crie uma tabela dinâmica em Orçamento e escolha o Modelo de Dados
- Crie uma tabela dinâmica usando orçamento e real das tabelas originais
- Todos os outros campos devem vir das tabelas do joiner
- Adicionar fatiadores por produto
- Crie três relacionamentos de Orçamento a Joiners
- Crie três relacionamentos de Real para Joiners
- Amanhã: como construir relacionamentos é mais fácil com Power Pivot e fórmulas DAX
Transcrição de vídeo
Aprenda Excel com o podcast, episódio 2016 - Orçamento de cima para baixo versus reais de baixo para cima!
Ei, estou enviando um podcast para este livro inteiro, clique no “i” no canto superior direito e siga a lista de reprodução.
Ei, vou interromper isso, aqui é Bill Jelen de 15 minutos a partir de agora. Percebo agora que este é um podcast incrivelmente longo, e você está tentado apenas a clicar nele, mas deixe-me resumir isso. Se você está no Excel 2013 e já teve uma pequena tabela de orçamento e uma grande tabela de reais e precisa mapeá-los juntos, essa é uma nova habilidade incrível que temos no Excel 2013, que muitas pessoas não explicaram , e provavelmente você não sabe sobre isso. Se este é você, você está em 2013 e precisa mapear esses dois conjuntos de dados, com calma, talvez hoje, talvez amanhã, talvez adicionar à lista de observação, vale a pena, é uma técnica incrível.
Certo, aqui está o que temos, do lado esquerdo temos um orçamento, esse orçamento, é feito no nível superior, top-down, certo para cada linha de produto, para cada região, para cada mês, há um orçamento . Não há muitos registros aqui, conte até 55, no lado direito estamos tentando comparar isso com os reais. Os reais vêm de um registro de fatura, então temos Região, Produto e Receita, mas são faturas individuais, muito mais dados aqui, já estamos na metade do ano e eu já tenho 423 registros. Tudo bem, então como você mapeia esses 55 para esses 423? Pode ser difícil de fazer com VLOOKUP, você teria que resumir primeiro, mas, felizmente, no Excel 2013, o modelo de dados torna isso muito, muito fácil. O que precisamos para permitir que esta grande mesa se comunique com esta pequena mesa são intermediários, eu os chamo de juntadores.Pequenas tabelas, Produto, Região e Calendário, vamos juntar o orçamento a essas três tabelas, vamos juntar o real a essas três tabelas e, milagrosamente, a tabela dinâmica funcionará. Tudo bem, então é assim que fazemos isso.
Primeiro preciso criar os juntadores, então pego este campo Produto da coluna A e o copio para a coluna F e, em seguida, Dados, Remover Duplicatas, clique em OK e ficamos com uma pequena tabela, 1 título 3 linhas. Mesma coisa para Região, pegue as regiões, Ctrl + C, vá para a coluna G, Colar, Remover Duplicatas, clique em OK, 3 linhas 1 cabeçalho, certo. Agora, para as datas, as datas não são iguais, essas são as datas de término do mês, elas são armazenadas como datas de término do mês e esses são os dias da semana. Vou pegar as duas listas, Ctrl + C a segunda lista e colá-la aqui, Ctrl + V, então vou pegar a lista mais curta, copiá-la e colá-la abaixo, certo. E é realmente irritante que, embora estejam armazenados como datas, eles estão aparecendo como meses, e Remover Duplicados não os verá iguais.Portanto, antes de usar Remover Duplicatas, preciso alterá-lo para uma data curta. Escolha esses dados, Dados, Remover Duplicados, clique em OK e, em seguida, classifique um pouco aqui para que funcionem.
Tudo bem, agora não quero relatar por data diária, então vou adicionar uma coluna aqui, uma coluna de pesquisa que diz Mês, e isso será igual a EOMONTH daquela data,, 0, o que nos levará a o fim do mês. Ele irá formatar isso como uma data abreviada e copiar, tudo bem. Agora, precisamos fazer cada um deles em uma tabela Ctrl + T, então a partir daqui Ctrl + T, Minha tabela tem cabeçalhos, lindo. Os pequenos, não percebe que são cabeçalhos aí em cima, então temos que ter certeza de marcar isso e Ctrl + T, tudo bem, e eles chamam essas tabelas de Tabela1, Tabela2, Tabela3, nomes muito chatos, certo? Vou renomeá-los e chamá-los de BudTable, ProdTable, RegTable, meu CalTable e, em seguida, ActTable, certo.
Começamos da primeira tabela e, por falar nisso, não vamos usar o PowerPivot hoje, vamos fazer tudo isso com o modelo de dados. Então, Excel 2013 ou mais recente, você tem este Insert, PivotTable, vamos marcar a caixa para “Adicionar esses dados ao Modelo de Dados”, clicar em OK e obteremos nossa lista de campos com o botão mágico Todos, que permite me escolher entre todas as cinco tabelas na pasta de trabalho, real, orçamento, calendário, produto, região. Certo, então os números virão da tabela Orçamento, colocarei o orçamento lá, e da tabela Real colocarei o real lá, mas aqui está o restante da tabela dinâmica. Quaisquer outros campos de texto que vamos colocar na área da linha ou na área da coluna ou como segmentação de dados, eles têm que vir dos associadores, eles têm que vir dessas tabelas entre as tabelas.
Tudo bem, então, da tabela Calendar, pegaremos esse campo Mês e colocá-lo no topo, vamos ignorar outras relações agora. Estarei criando os relacionamentos, mas quero criá-los todos de uma vez. E a tabela Região, coloque as regiões ao lado. Eu poderia colocar os produtos de lado, mas na verdade vou usar a tabela Produto como um divisor, então Analisar, Inserir Fatiador, novamente você deve ir para Todos se ainda não usou a tabela Produto. Então vá para Todos e você verá que o Produto está disponível para ser criado como fatiador dos produtos, assim. Tudo bem agora, neste ponto não criamos relacionamentos, então todos esses números estão errados. E os relacionamentos que temos que criar, temos que criar 3 tabelas desta pequena tabela de orçamento, uma para os produtos, uma para as regiões, uma para o calendário,são 3 relacionamentos. E então temos que criar relacionamentos da tabela Real para a região do Produto no Calendário, então um total de 6 tabelas. E sim, isso definitivamente seria mais fácil se tivéssemos o PowerPivot, mas não temos ou vamos supor que não temos.
Então, vou usar a maneira antiga, o diálogo Criar aqui, onde temos a tabela Orçamento à esquerda, e vamos usar o campo Região e relacionar isso à tabela Região, o campo Região . Tudo bem, 1/6 foi criado. Vou escolher Criar, novamente na tabela de Orçamento vamos para o Produto e, em seguida, vincularemos isso à tabela Produto, ao Produto, clique em OK. Na tabela Orçamento, no campo Data, vamos para a tabela Calendário e no campo Destino, clique em OK, estamos na metade do caminho, certo. Da tabela Reais, vamos Região, para a tabela Região, clique em OK, da tabela Reais para o Produto, e da tabela Reais para o Calendário. Na verdade, vou pegar os Valores e fazê-los ir para o lado, certo. Design, Layout de Relatório, Mostrar em Forma Tabular para obter uma visão que eu prefiro, Repetir Todos os Rótulos de Item, certo,isso é absolutamente incrível! Agora temos esta pequena tabela minúscula, uns 50 registros nesta tabela de centenas de registros, e criamos uma única tabela dinâmica graças ao modelo de dados. Para cada um em que podemos ver o orçamento, podemos ver a receita, é dividida por região, é dividida por mês e é dividida por produto.
Agora, esse conceito veio de Rob Collie, que executa o Power Pivot Pro, e Rob criou muitos livros por aí, seu último é “Power Pivot e Power BI”. Acho que este estava realmente no livro “Power Pivot Alchemy”, é o que eu vi e disse “Bem, isso, embora eu não tenha milhões de linhas para relatar através do Power Pivot, este é um que iria fizeram uma ENORME diferença na minha vida, tendo dois conjuntos de dados de tamanhos incompatíveis e precisando relatar de ambos. ” Bem, este exemplo e muitos outros estão neste livro, eventualmente terei o podcast do livro inteiro, que parece que vai demorar dois meses e meio. Mas você pode pegar o livro inteiro hoje, ao mesmo tempo, ir lá, comprar o livro, $ 10 pelo e-book, $ 25 pelo livro impresso, e você pode ter todas essas dicas de uma vez.
Tudo bem, um episódio muito longo aqui: temos um pequeno orçamento de cima para baixo e um baixo para cima Real, eles são de tamanhos diferentes, mas usando o modelo de dados no Excel 2013 … E, a propósito, se você estiver em 2010, você poderia , em teoria, faça isso obtendo o suplemento Power Pivot e execute todas essas etapas em 2010. Transforme ambos os conjuntos de dados em uma tabela Ctrl + T e, em seguida, junte suas tabelas para qualquer coisa que você deseja relatar, no rótulo de linha, rótulo de coluna ou segmentação de dados, portanto, copie esses valores e remova duplicatas para as datas. Na verdade, peguei valores de ambas as tabelas, porque havia alguns valores exclusivos em cada uma, e usei o EOMONTH para chegar lá, fazer com que essas tabelas joiner sejam tabelas controladas. É opcional, mas nomeei todas as 5 tabelas, porque é mais fácil quando você está configurando essas relações, em vez de ser chamado de Tabela 1,Tabela2, Tabela3.
Então, comece a partir da tabela Orçamento, Inserir, Tabela Dinâmica, marque a caixa para Modelo de Dados e crie uma tabela Dinâmica usando Orçamento e Real. Todo o resto vem das tabelas de junção, então Região e Mês na área de linha e coluna, segmentações de dados vieram da tabela de Produtos. E então tivemos que criar 3 relacionamentos do orçamento para os marceneiros, 3 relacionamentos do real para os marceneiros, e temos uma tabela dinâmica incrível. Agora, amanhã, vamos dar uma olhada em como usar a guia Power Pivot e criar alguns cálculos adicionais. Então tudo isso é possível, é quando queremos inserir um campo calculado, é quando você tem que pagar os $ 2 extras por mês para obter a versão Pro Plus do Office 365.
Bem, ei, obrigado a Rob Collie do Power Pivot Pro por esta dica, e obrigado pela visita, nos vemos na próxima vez para outro netcast do!
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2016.xlsx
