Modelo de dados em Excel

O que é o modelo de dados no Excel?

O modelo de dados no Excel é um tipo de tabela de dados em que duas ou mais de duas tabelas estão em relação entre si por meio de uma série comum ou mais de dados, em tabelas de modelo de dados e dados de várias outras planilhas ou fontes se unem para formar um único tabela que pode ter acesso aos dados de todas as tabelas.

Explicação

• Ele permite a integração de dados de várias tabelas criando relacionamentos com base em uma coluna comum.
• Os modelos de dados são usados ​​de forma transparente, fornecendo dados tabulares que podem ser usados ​​em Tabela Dinâmica no Excel e Gráficos Dinâmicos no Excel. Ele integra as tabelas, permitindo análises abrangentes usando tabelas dinâmicas, Power Pivot e Power View no Excel.
• O modelo de dados permite carregar dados na memória do Excel.
• Ele é salvo na memória onde não podemos vê-lo diretamente. Então, o Excel pode ser instruído a relacionar os dados entre si usando uma coluna comum. A parte 'Modelo' do Modelo de Dados refere-se a como todas as tabelas se relacionam entre si.
• O Modelo de Dados pode acessar todas as informações de que precisa, mesmo quando as informações estão em várias tabelas. Após a criação do Modelo de Dados, o Excel tem os dados disponíveis em sua memória. Com os dados em sua memória, eles podem ser acessados ​​de várias maneiras.

Exemplos

Você pode baixar este modelo de modelo de dados em Excel aqui - modelo de modelo de dados em Excel

Exemplo 1

Se tivermos três conjuntos de dados relacionados ao vendedor: o primeiro contendo informações de receita, um segundo contendo a receita do vendedor e o terceiro contendo as despesas do vendedor.

Para conectar esses três conjuntos de dados e fazer um relacionamento com eles, fazemos um Modelo de Dados com as seguintes etapas:

• Converta os conjuntos de dados em objetos Tabela:

Não podemos criar um relacionamento com conjuntos de dados comuns. O Modelo de Dados funciona apenas com objetos Tabelas do Excel. Para fazer isso:

• Passo 1 - Clique em qualquer lugar dentro do conjunto de dados e, em seguida, clique na guia 'Inserir' e, em seguida, clique em 'Tabela' no grupo 'Tabelas'.

• Passo 2 - Marque ou desmarque a opção: 'Minha mesa tem cabeçalhos' e clique em OK.

• Passo 3 - Com a nova tabela selecionada, insira o nome da Tabela em 'Nome da Tabela' no grupo 'Ferramentas'.

• Passo 4 - Agora podemos ver que o primeiro conjunto de dados é convertido para o objeto 'Tabela'. Ao repetir essas etapas para os outros dois conjuntos de dados, vemos que eles também são convertidos em objetos 'Tabela', conforme abaixo:

Adicionando os objetos 'Tabela' ao Modelo de Dados: Via Conexões ou Relacionamentos.

Via Conexões

• Selecione uma tabela e clique na guia 'Dados' e depois clique em 'Conexões'.

• Na caixa de diálogo resultante, há um ícone 'Adicionar'. Expanda a lista suspensa de 'Adicionar' e clique em 'Adicionar ao modelo de dados'.

• Clique em 'Tabelas' na caixa de diálogo exibida e, a seguir, selecione uma das tabelas e clique em 'Abrir'.

Ao fazer isso, um modelo de dados de pasta de trabalho seria criado com uma tabela e uma caixa de diálogo aparecerá da seguinte maneira:

Portanto, se repetirmos essas etapas também para as outras duas tabelas, o Modelo de Dados agora conterá todas as três tabelas.

Agora podemos ver que todas as três tabelas aparecem nas Conexões da pasta de trabalho.

Por meio de relacionamentos

Crie o relacionamento: Uma vez que ambos os conjuntos de dados são objetos Tabela, podemos criar um relacionamento entre eles. Para fazer isso:

• Clique na guia 'Dados' e depois em 'Relacionamentos'.

• Veremos uma caixa de diálogo vazia porque não há conexões atuais.

• Clique em 'Novo' e outra caixa de diálogo aparecerá.

• Expanda os menus suspensos 'Tabela' e 'Tabela relacionada': A caixa de diálogo 'Criar relacionamento' aparece para escolher as tabelas e colunas a serem usadas para um relacionamento. Na expansão de 'Tabelas', selecione o conjunto de dados que desejamos analisar de alguma forma, e em 'Tabela Relacionada', selecione o conjunto de dados que possui os valores de pesquisa.
• A tabela de pesquisa no Excel é a tabela menor no caso de um para muitos relacionamentos e não contém valores repetidos na coluna comum. Na expansão de 'Coluna (Estrangeira)', selecione a coluna comum na tabela principal, em 'Coluna Relacionada (Primária)', selecione a coluna comum na tabela relacionada.

• Com todas essas quatro configurações selecionadas, clique em 'OK'. Uma caixa de diálogo aparece como segue clicando em 'OK'.

Se repetirmos essas etapas para relacionar outras duas tabelas: Tabela de receita com tabela de despesas, elas também serão relacionadas no modelo de dados da seguinte forma:

O Excel agora cria o relacionamento nos bastidores, combinando dados no Modelo de Dados com base em uma coluna comum: ID do Vendedor (neste caso).

Exemplo # 2

Agora, digamos que no exemplo acima desejamos criar uma Tabela Dinâmica que avalia ou analisa os objetos Tabela:

• Clique em 'Inserir' -> 'Tabela Dinâmica'.

• Na caixa de diálogo exibida, clique na opção que diz: 'Usar uma fonte de dados externa' e, a seguir, clique em 'Escolher conexão'.

• Clique em 'Tabelas' na caixa de diálogo resultante e selecione o modelo de dados da pasta de trabalho contendo três tabelas e clique em 'Abrir'.

• Selecione a opção 'Nova planilha' no local e clique em 'OK'.

• O painel Campos da Tabela Dinâmica exibirá objetos de tabela.

• Agora, as alterações na Tabela Dinâmica podem ser feitas em conformidade, de modo a analisar os objetos da tabela conforme necessário.

Por exemplo, neste caso, se desejarmos encontrar a receita total ou receita de um determinado vendedor, então uma Tabela Dinâmica é criada da seguinte maneira:

Isso é de grande ajuda no caso de um modelo / mesa que contém um grande número de observações.

Portanto, podemos ver que a Tabela Dinâmica usa instantaneamente o Modelo de Dados (escolhendo-o ao escolher a conexão) na memória do Excel para mostrar as relações entre as tabelas.

Coisas para lembrar

• Usando o modelo de dados, podemos analisar dados de várias tabelas ao mesmo tempo.
• Ao criar relacionamentos com o Modelo de Dados, superamos a necessidade de usar VLOOKUP, SUMIF, função INDEX e fórmulas MATCH, pois não precisamos obter todas as colunas em uma única tabela.
• Quando os conjuntos de dados são importados para o Excel de fontes externas, os modelos são criados implicitamente.
• Relacionamentos de tabela podem ser criados automaticamente se importarmos tabelas relacionadas que possuem relacionamentos de chave primária e estrangeira.
• Ao criar relacionamentos, as colunas que estamos conectando em tabelas devem ter o mesmo tipo de dados.
• Com as tabelas dinâmicas criadas com o modelo de dados, podemos adicionar segmentações também e dividir as tabelas dinâmicas em qualquer campo que desejarmos.
• A vantagem do Modelo de Dados sobre as funções LOOKUP () é que ele requer substancialmente menos memória.
• O Excel 2013 suporta apenas um para um ou um para vários relacionamentos, ou seja, uma das tabelas não deve ter valores duplicados na coluna à qual estamos vinculando.