Não tem Power Pivot? Não importa. A maior parte do Power Pivot é integrado ao Excel 2013 e ainda mais no Excel 2016. Hoje, nossa dica de Ash é juntar tabelas em uma tabela dinâmica.
Todas as quartas-feiras, durante sete semanas, estou apresentando uma das dicas favoritas de Ash Sharma. Ash é gerente de produto da equipe do Excel. Sua equipe traz para você tabelas dinâmicas e muitas outras coisas boas. Hoje, o recurso favorito de Ash é juntar vários conjuntos de dados usando Relacionamentos e o Modelo de Dados.
Digamos que seu departamento de TI forneça o conjunto de dados mostrado nas colunas A: D. Existem campos para cliente e mercado. Você precisa combinar certos mercados em regiões. Cada cliente pertence a um setor. Região e Setor não estão nos dados originais, mas você tem tabelas de pesquisa para fornecer essas informações.
Normalmente, você nivelaria os dados usando PROCV para extrair dados das tabelas laranja e amarela para a tabela azul. Mas como o campo-chave não está no lado esquerdo de cada tabela, você terá que alternar para INDEX e MATCH ou reorganizar as tabelas de pesquisa.
A partir do Excel 2013, você pode deixar as tabelas de pesquisa onde estão e combiná-las no próprio relatório de tabela dinâmica.
Para que essa técnica funcione, todas as três tabelas devem ser formatadas como uma tabela. Selecione uma célula em cada conjunto de dados e escolha Home, Format as Table ou pressione Ctrl + T. As três tabelas serão inicialmente chamadas de Table1, Table2 e Table3. Eu uso a guia Design de Ferramentas de Tabela da Faixa de Opções e renomeio cada tabela. Eu também mudo a cor de cada mesa. Neste exemplo, a tabela azul é chamada de Dados. A tabela laranja é RegionTable. A tabela amarela é SectorTable.
Nota
Alguns dirão que você deve usar nomes geeks como Fact, TblSector e TblRegion. Se alguém incomodar você assim, roube o protetor de bolso e diga que você prefere nomes que soem ingleses.
Para renomear uma tabela, digite um novo nome na caixa do lado esquerdo da guia Design das Ferramentas de Tabela. Os nomes das tabelas não devem ter espaços.
Depois de definir as três tabelas, vá até a guia Dados e clique em Relacionamentos.
Na caixa de diálogo Gerenciar relacionamentos, clique em Novo. Na caixa de diálogo Criar Relacionamento, especifique se o campo Cliente da tabela Dados está relacionado ao Campo Cliente da Tabela Setorial. Clique OK.
Defina outro novo relacionamento entre o campo Market nos campos Data e RegionTable. Depois de definir os dois relacionamentos, você os verá na caixa de diálogo Gerenciar relacionamentos.
Parabéns: você acabou de criar um modelo de dados em sua pasta de trabalho. É hora de construir uma tabela dinâmica.
Selecione a célula em branco onde deseja que a tabela dinâmica apareça. Por padrão, a caixa de diálogo Criar tabela dinâmica escolherá Usar o modelo de dados desta pasta de trabalho. O local da tabela dinâmica será padronizado para a célula que você escolheu. Clique OK.
A lista Campos da tabela dinâmica listará todas as três tabelas. Use o triângulo à esquerda de uma tabela para expandir o nome da tabela e mostrar os campos.
Expanda a tabela de dados. Selecione o campo Receita. Ele se moverá automaticamente para a área Valores. Expanda o SectorTable. Escolha o campo Setor. Ele se moverá para a área de Linhas. Expanda o RegionTable. Arraste o campo Região para a área Colunas. Agora você terá uma tabela dinâmica resumindo os dados das três tabelas.
Nota
Em cada livro que escrevi antes de hoje, uso uma técnica diferente para construir este relatório. Após definir as três tabelas, escolho a célula A1 e Inserir, Tabela Dinâmica. Eu marquei a caixa para Adicionar esses dados ao modelo de dados. Na lista Campos da Tabela Dinâmica, selecione Todos no topo da lista. Escolha campos para o relatório e, a seguir, defina os relacionamentos após o fato. A técnica descrita acima parece mais suave e na verdade envolve um pouco de planejamento futuro. As pessoas que usam Option Explicit em seu código VBA definitivamente gostariam deste método.
As relações no modelo de dados fazem o Excel parecer mais com o Access ou com o SQL Server, mas com todas as vantagens do Excel.
Adoro pedir à equipe do Excel seus recursos favoritos. Cada quarta-feira, vou compartilhar uma de suas respostas. Obrigado a Ash Sharma por fornecer esta ideia.
Excel Pensamento do Dia
Pedi conselhos aos meus amigos do Excel Master sobre o Excel. O pensamento de hoje para ponderar:
"Não procure se você está em um relacionamento"
John Michaloudis
