Como criar relacionamentos entre várias tabelas usando o modelo de dados no Excel
O Excel é uma ferramenta poderosa para análise de dados e automação subsequente ao lidar com grandes conjuntos de dados. Você pode estar gastando muito tempo analisando toneladas de dados usando VLOOKUP, INDEX-MATCH, SUMIF, etc.
Graças ao modelo de dados do Excel, você pode economizar um tempo precioso por meio de relatórios de dados automáticos. Descubra como é fácil atribuir um relacionamento entre duas tabelas usando o Modelo de Dados e uma ilustração de tal relacionamento em uma tabela dinâmica na seção a seguir.
Os requisitos básicos
Você precisará do Power Pivot e do Power Query (Get & Transform) para realizar várias tarefas durante a criação do Modelo de Dados do Excel . Veja como você pode obter esses recursos em sua pasta de trabalho do Excel:
Como obter o Power Pivot
1. Excel 2010: você precisará baixar o suplemento Power Pivot da Microsoft e, em seguida, instalá-lo para o programa Excel em seu computador.
2. Excel 2013: a edição Office Professional Plus do Excel 2013 inclui Power Pivot. Mas, você precisa ativá-lo antes do primeiro uso. Aqui está como:
- Clique em Arquivo na faixa de opções de uma pasta de trabalho do Excel.
- Em seguida, clique em Opções para abrir as Opções do Excel .
- Agora, clique em Add-ins .
- Selecione COM Add-ins clicando no menu suspenso da caixa Gerenciar .
- Clique em Ir e marque a caixa de seleção do Microsoft Power Pivot para Excel .
3. Excel 2016 e posterior: Você encontrará o menu Power Pivot na faixa de opções .
Como obter o Power Query (obter e transformar)
1. Excel 2010: Você pode baixar o suplemento Power Query da Microsoft . Após a instalação, o Power Query aparecerá na Faixa de Opções .
2. Excel 2013: Você precisa ativar o Power Query seguindo as mesmas etapas que você acabou de fazer para tornar o Power Pivot funcional no Excel 2013.
3. Excel 2016 e mais tarde: Você pode encontrar Poder consulta (Get & Transform), indo para a guia dados na fita de Excel.
Criar modelo de dados importando dados para a pasta de trabalho do Excel
Para este tutorial, você pode obter dados de amostra pré-formatados da Microsoft:
Download : Amostra de dados do aluno (somente dados) | Dados de amostra do aluno (modelo completo)
Você pode importar um banco de dados com várias tabelas relacionadas de muitas fontes, como pastas de trabalho do Excel, Microsoft Access, sites, SQL Server, etc. Em seguida, você precisa formatar o conjunto de dados para que o Excel possa utilizá-lo. Aqui estão as etapas que você pode tentar:
1. No Excel 2016 e edições posteriores, clique na guia Dados e selecione Nova Consulta .
2. Você encontrará várias maneiras de importar dados de fontes externas ou internas. Escolha aquele que mais lhe convier.
3. Se estiver usando a edição Excel 2013, clique em Power Query na Faixa de Opções e selecione Obter Dados Externos para escolher os dados para importação.
4. Você verá a caixa do Navegador onde você precisa escolher quais tabelas você precisa importar. Clique na caixa de seleção para Selecionar vários itens para escolher várias tabelas para importação.
5. Clique em Carregar para concluir o processo de importação.
6. O Excel criará um modelo de dados para você usando essas tabelas. Você pode ver os cabeçalhos das colunas da tabela nas listas de Campos da Tabela Dinâmica .
Você também pode utilizar funções do Power Pivot como colunas calculadas, KPIs, hierarquias, campos calculados e conjuntos de dados filtrados do Modelo de Dados do Excel. Para isso, você precisará gerar o Modelo de Dados a partir de uma única tabela. Você pode tentar estas etapas:
1. Formate seus dados em um modelo tabular selecionando todas as células que contêm dados e clique em Ctrl + T.
2. Agora, selecione a tabela inteira e, em seguida, clique na guia Energia Pivot na fita.
3. Na seção Tabelas , clique em Adicionar ao modelo de dados .
O Excel criará relacionamentos de tabela entre os dados relacionados do Modelo de Dados. Para isso, deve haver relacionamentos de chave primária e estrangeira nas tabelas importadas.
O Excel utiliza as informações de relacionamento da tabela importada como base para gerar conexões entre as tabelas em um Modelo de Dados.
Construir relacionamentos entre as tabelas no modelo de dados
Agora que você tem um Modelo de Dados em sua pasta de trabalho do Excel, precisará definir relacionamentos entre as tabelas para criar relatórios significativos. Você precisa atribuir um identificador de campo exclusivo ou chave primária a cada tabela, como ID do Semestre, Número da Classe, ID do Aluno, etc.
O recurso Exibição de Diagrama do Power Pivot permitirá que você arraste e solte esses campos para construir um relacionamento. Siga estas etapas para criar links de tabelas no Modelo de Dados do Excel:
1. Na faixa de opções da pasta de trabalho do Excel, clique no menu Power Pivot .
2. Agora, clique em Gerenciar na seção Modelo de Dados . Você verá o editor Power Pivot conforme mostrado abaixo:
3. Clique no botão Exibir Diagrama localizado na seção Exibir da guia Início do Power Pivot. Você verá os cabeçalhos das colunas da tabela agrupados de acordo com o nome da tabela.
4. Agora você poderá arrastar e soltar o identificador de campo exclusivo de uma tabela para outra. A seguir está o esquema de relacionamento entre as quatro tabelas do Modelo de Dados Excel:
O seguinte descreve a ligação entre as tabelas:
- Alunos de mesa | ID do aluno para notas da tabela | Identidade estudantil
- Semestres de mesa | ID do semestre para notas da tabela | Semestre
- Classes de mesa | Número da classe para notas da tabela | ID de classe
5. Você pode criar relacionamentos escolhendo um par de colunas de valor exclusivo. Se houver duplicatas, você verá o seguinte erro:
6. Você notará Estrela (*) de um lado e Um (1) do outro na Visualização do Diagrama dos relacionamentos. Ele define que existe uma relação um-para-muitos entre as tabelas.
7. No editor do Power Pivot, clique na guia Design e selecione Gerenciar Relacionamentos para saber quais campos fazem as conexões.
Gerar uma tabela dinâmica usando o modelo de dados do Excel
Agora você pode criar uma Tabela Dinâmica ou Gráfico Dinâmico para visualizar seus dados do Modelo de Dados do Excel. Uma pasta de trabalho do Excel pode conter apenas um Modelo de Dados, mas você pode continuar atualizando as tabelas.
Como os dados mudam com o tempo, você pode continuar a usar o mesmo modelo e economizar tempo ao trabalhar com o mesmo conjunto de dados. Você perceberá mais economia de tempo ao trabalhar com dados em milhares de linhas e colunas. Para criar um relatório baseado em tabela dinâmica, siga estas etapas:
1. No editor Power Pivot, clique na guia Home .
2. Na faixa de opções , clique em Tabela Dinâmica .
3. Escolha qualquer um entre Nova Planilha ou Planilha Existente.
4. Selecione OK . O Excel adicionará uma Tabela Dinâmica que mostrará o painel Lista de Campos à direita.
A seguir está uma visão holística de uma tabela dinâmica criada utilizando o Modelo de Dados do Excel para os dados de amostra do aluno usados neste tutorial. Você também pode criar tabelas dinâmicas ou gráficos profissionais de big data usando a ferramenta Excel Data Model.
Transforme conjuntos de dados complexos em relatórios simples usando o modelo de dados do Excel
O modelo de dados do Excel utiliza os benefícios de criar relacionamentos entre tabelas para produzir tabelas dinâmicas ou gráficos significativos para fins de relatório de dados.
Você pode atualizar continuamente a pasta de trabalho existente e publicar relatórios sobre os dados atualizados. Você não precisa editar fórmulas ou investir tempo em rolar por milhares de colunas e linhas cada vez que os dados de origem são atualizados.
