Diretamente o modelo de dados internos do Excel: dummies

Você pode usar uma combinação de tabelas dinâmicas e conexões de dados do Excel para interagir diretamente com o Modelo de Dados Interno, sem o suplemento Power Pivot. Isso é útil se você estiver usando versões do Excel que não são fornecidas com o suplemento Power Pivot, como quando você está usando o Microsoft Office, edição Home ou Small Business. Todo o livro de trabalho do Excel 2013 e 2016 vem com um modelo de dados interno.

Imagine que você tenha a tabela de Transações que você vê aqui e em outra planilha você possui uma tabela de Empregados que contém informações sobre os funcionários.

Este quadro mostra transações por número de funcionário.

Esta tabela fornece informações sobre funcionários: primeiro nome, sobrenome e cargo.

Você precisa criar uma análise que mostre vendas por título de trabalho. Isso normalmente seria difícil dado o fato de que as vendas e o cargo estão em duas tabelas separadas. Mas com o Modelo de Dados Internos, você pode seguir estas simples etapas:

1. Clique dentro da tabela de dados Transações e comece uma nova tabela dinâmica selecionando Inserir ➪ Tabela dinâmica da Faixa de opções.
2. Na caixa de diálogo Criar tabela dinâmica, selecione a opção Adicionar esses dados à modelo de dados.

   

   Quando você cria uma nova tabela dinâmica da tabela Transações, certifique-se de selecionar Adicionar esses dados ao modelo de dados.

3. Clique dentro da tabela de dados do Empregado e comece uma nova tabela dinâmica.

   Novamente, certifique-se de selecionar a opção Adicionar esses dados à modelo de dados, conforme mostrado.

   Observe que as caixas de diálogo Criar tabela dinâmica estão referenciando intervalos nomeados. Ou seja, cada tabela recebeu um nome específico. Quando você está adicionando dados ao Modelo de Dados Internos, é uma prática recomendada para nomear as tabelas de dados. Desta forma, você pode facilmente reconhecer suas tabelas no Modelo de Dados Interno. Se você não nomear suas tabelas, o Modelo de Dados Interno as mostra como Range1, Range2 e assim por diante.

   

   Crie uma nova tabela dinâmica da tabela Funcionários e selecione Adicionar estes dados ao modelo de dados.
4. Para dar um nome à tabela de dados, basta destacar todos os dados na tabela e, em seguida, selecione Fórmulas → Definir comando Nome da Faixa de opções. Na caixa de diálogo, digite um nome para a tabela. Repita para todas as outras tabelas.
5. Depois que ambas as tabelas foram adicionadas ao modelo de dados interno, abra a lista Campos dinâmicos e escolha o seletor ALL. Este passo mostra ambos os intervalos na lista de campos.

   

   Selecione TODOS na lista Campos dinâmicos para ver ambas as tabelas no modelo de dados interno.
6. Crie a tabela dinâmica como normal.Neste caso, Job_Title é colocado na área da Linha e Sales_Amount vai para a área de Valores.

   Como você pode ver aqui, o Excel imediatamente reconhece que você está usando duas tabelas do Modelo de Dados Interno e solicita que você crie uma relação entre eles. Você tem a opção de permitir que o Excel autentifique os relacionamentos entre suas tabelas ou clique no botão Criar. Sempre crie as relações você mesmo, para evitar qualquer possibilidade de o Excel ter errado.

   

   Quando o Excel o solicitar, escolha criar a relação entre as duas tabelas.
7. Clique no botão Criar.

   Excel abre a caixa de diálogo Criar relacionamento, mostrada aqui. Lá, você seleciona as tabelas e os campos que definem o relacionamento. Você pode ver que a tabela Transações tem um campo Sales_Rep. Está relacionado à tabela Employees através do campo Employee_Number.

Crie o relacionamento apropriado usando as listas suspensas Tabela e Coluna.

Depois de criar o relacionamento, você possui uma única tabela dinâmica que efetivamente usa dados de ambas as tabelas para criar a análise que você precisa. A figura a seguir ilustra que, usando o modelo de dados internos do Excel, você alcançou o objetivo de exibir vendas por título de trabalho.

Você alcançou seu objetivo de mostrar vendas por título de trabalho.

Você vê que o menu suspenso inferior direito é denominado Coluna relacionada (primária). O termo primário significa que o Modelo de Dados Interno usa este campo da tabela associada como a chave primária.

Uma chave primária é um campo que contém apenas valores únicos não nulos (sem duplicatas ou espaços em branco). Os campos de chave primária são necessários no modelo de dados para evitar erros de agregação e duplicações. Todo relacionamento que você criar deve ter um campo designado como a chave primária.

A tabela Empregados deve ter todos os valores exclusivos no campo Employee_Number, sem valores em branco ou nulos. Esta é a única maneira que o Excel pode garantir a integridade dos dados ao juntar várias tabelas.

Depois de atribuir tabelas ao Modelo de Dados Interno, você precisará ajustar as relações entre as tabelas. Para fazer alterações nos relacionamentos em um Modelo de Dados Interno, clique na guia Dados na Faixa de opções e selecione o comando Relacionamentos. A caixa de diálogo Gerenciar relações, mostrada aqui, é aberta.

A caixa de diálogo Gerenciar relacionamentos permite que você faça alterações nos relacionamentos no modelo de dados interno.

Aqui, você encontrará os seguintes comandos:

• Novo: Crie uma nova relação entre duas tabelas no Modelo de Dados Interno.
• Editar: Alterar o relacionamento selecionado.
• Ativar: Aplicar o relacionamento selecionado, informando o Excel para considerar o relacionamento ao agregar e analisar os dados no Modelo de Dados Internos.
• Desativar: Desative o relacionamento selecionado, informando o Excel para ignorar o relacionamento ao agregar e analisar os dados no Modelo de Dados Internos.
• Excluir: Remover o relacionamento selecionado.