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 do Budget 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: 1 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 na tabela Orçamento e selecionar a caixa de seleção 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.
