Criar um modelo de dados no Excel

Um Modelo de Dados permite-lhe integrar dados de várias tabelas, criando efetivamente uma origem de dados relacional dentro de um livro do Excel. No Excel, os Modelos de Dados são utilizados de forma transparente, fornecendo dados tabulares utilizados em Tabelas Dinâmicas e Gráficos Dinâmicos. Um Modelo de Dados é visualizado como uma coleção de tabelas numa Lista de Campos e, na maioria das vezes, nunca saberá que está lá.

Antes de poder começar a trabalhar com o Modelo de Dados, tem de obter alguns dados. Para isso, iremos utilizar a experiência Get & Transform (Power Query), pelo que poderá querer recuar um pouco e ver um vídeo ou seguir o nosso guia de aprendizagem em Obter & Transformação e Power Pivot.

Onde está o Power Pivot?

Excel 2016 & Excel do Microsoft 365 – o Power Pivot está incluído no Friso.

Onde está Get & Transform (Power Query)?

Excel 2016 & Excel do Microsoft 365 – a opção Obter transformação & (Power Query) foi integrada com o Excel no separador Dados.

Introdução

Primeiro, tem de obter alguns dados.

No Excel 2016 e no Excel do Microsoft 365, utilize a > Obter & Transformar Dados > Obter Dados para importar dados de várias origens de dados externas, como um ficheiro de texto, um livro do Excel, um site, o Microsoft Access, o SQL Server ou outra base de dados relacional que contenha várias tabelas relacionadas.
O Excel solicitará que você selecione uma tabela. Se quiser obter múltiplas tabelas a partir da mesma origem de dados, selecione a opção Ativar seleção de múltiplas tabelas . Quando seleciona múltiplas tabelas, o Excel cria automaticamente um Modelo de Dados.

Observação: Para estes exemplos, estamos a utilizar um livro do Excel com detalhes fictícios de estudantes sobre turmas e notas. Pode transferir o nosso livro de exemplo do Modelo de Dados de Estudante e acompanhar. Também pode transferir uma versão com um Modelo de Dados concluído..
Selecione uma ou mais tabelas e, em seguida, clique em Carregar.

Se precisar de editar os dados de origem, pode escolher a opção Editar . Para obter mais detalhes, consulte: Introdução ao Editor de Consultas (Power Query).

Tem agora um Modelo de Dados que contém todas as tabelas que importou e estas serão apresentadas na Lista de Campos da Tabela Dinâmica.

Observações:

Os modelos são criados de modo implícito quando você importa duas ou mais tabelas simultaneamente no Excel.
Os modelos são criados de modo explícito quando você usa o suplemento Power Pivot para importar dados. No suplemento, o modelo é representado num esquema com separadores semelhante ao Excel, onde cada separador contém dados tabulares. Veja Obter dados com o suplemento Power Pivotpara saber as noções básicas da importação de dados com uma base de dados do SQL Server.
Um modelo pode conter uma única tabela. Para criar um modelo com base em apenas uma tabela, selecione a tabela e clique em Adicionar ao Modelo de Dados no Power Pivot. Você pode fazer isso se quiser usar os recursos do Power Pivot, como conjunto de dados filtrados, colunas calculadas, campos calculados, KPIs e hierarquias.
As relações de tabelas poderão ser criadas automaticamente se você importar tabelas relacionadas que tenham relações de chave primária e chave estrangeira. Geralmente o Excel pode usar as informações de relações importadas como base para relações de tabelas no Modelo de Dados.
Para obter sugestões sobre como reduzir o tamanho de um modelo de dados, veja Criar um Modelo de Dados com eficiência de memória com o Excel e o Power Pivot.
Para mais exploração, consulte Tutorial: Importar Dados para o Excel e Criar um Modelo de Dados.

Dica: Como pode saber se o seu livro tem um Modelo de Dados? Aceda a Power Pivot > Gerir. Se vir dados semelhantes a uma folha de cálculo, existe um modelo. Veja: Saiba que origens de dados são utilizadas num modelo de dados de livro para saber mais.

Criar Relações entre as tabelas

O próximo passo consiste em criar relações entre as suas tabelas, para que possa extrair dados de qualquer uma delas. Cada tabela tem de ter uma chave primária ou um identificador de campo exclusivo, como o ID de Estudante ou o Número da turma. A forma mais fácil é arrastar e largar esses campos para os ligar na Vista de Diagrama do Power Pivot.

Aceda a Power Pivot > Gerir.
No separador Base , selecione Vista de Diagrama.
Todas as tabelas importadas serão apresentadas e poderá querer demorar algum tempo a redimensioná-las consoante o número de campos que cada uma tem.
Em seguida, arraste o campo de chave primária de uma tabela para a seguinte. O exemplo seguinte é a Vista de Diagrama das nossas tabelas de estudantes:

Criámos as seguintes ligações:
- tbl_Students | ID de Estudante > tbl_Grades | ID do Estudante
  
  Por outras palavras, arraste o campo ID de Estudante da tabela Estudantes para o campo ID de Estudante na tabela Notas.
- tbl_Semesters | ID do semestre > tbl_Grades | Semestre
- tbl_Classes | Número de Classe > tbl_Grades | Número da Classe
Observações:
- Os nomes de campo não têm de ser os mesmos para criar uma relação, mas têm de ser do mesmo tipo de dados.
- Os conectores na Vista de Diagrama têm um "1" de um lado e um "*" do outro. Isto significa que existe uma relação um-para-muitos entre as tabelas e que determina a forma como os dados são utilizados nas tabelas dinâmicas. Veja: Relações entre tabelas num Modelo de Dados para saber mais.
- Os conectores indicam apenas que existe uma relação entre tabelas. Na verdade, não lhe mostrarão quais os campos que estão ligados entre si. Para ver as ligações, aceda a Power Pivot > Gerir > Estrutura > Relações > Gerir Relações. No Excel, pode aceder a Relações de > de Dados.

Utilizar um Modelo de Dados para criar uma Tabela Dinâmica ou um Gráfico Dinâmico

Um livro do Excel pode conter apenas um Modelo de Dados, mas esse modelo pode conter várias tabelas que podem ser utilizadas repetidamente em todo o livro. Pode adicionar mais tabelas a um Modelo de Dados existente em qualquer altura.

No Power Pivot, aceda a Gerir.
No separador Base , selecione Tabela Dinâmica.
Selecione onde pretende colocar a tabela dinâmica: uma nova folha de cálculo ou a localização atual.
Clique em OK e o Excel adicionará uma tabela dinâmica vazia com o painel Lista de Campos apresentado à direita.

Em seguida, crie uma Tabela Dinâmica ou crie um Gráfico Dinâmico. Se já tiver criado relações entre as tabelas, pode utilizar qualquer um dos respetivos campos na Tabela Dinâmica. Já criámos relações no livro de exemplo Modelo de Dados de Estudante.

Adicionar dados existentes e não relacionados a um Modelo de Dados

Suponha que importou ou copiou muitos dados que pretende utilizar num modelo, mas não os adicionou ao Modelo de Dados. Enviar os novos dados para um modelo é mais fácil do que você imagina.

Comece por selecionar qualquer célula dentro dos dados que pretende adicionar ao modelo. Pode ser qualquer intervalo de dados, mas os dados formatados como uma tabela do Excel são os melhores.
Use uma destas abordagens para adicionar seus dados:
Clique em Power Pivot > Adicionar ao Modelo de Dados.
Clique em Inserir > Tabela dinâmica e marque a opção Adicionar estes dados ao Modelo de Dados na caixa de diálogo Criar Tabela Dinâmica.

O intervalo ou tabela é agora adicionado ao modelo como uma tabela vinculada. Para saber mais sobre como trabalhar com tabelas vinculadas em um modelo, consulte Adicionar dados usando tabelas vinculadas do Excel no Power Pivot.

Adicionar dados a uma tabela de Power Pivot

No Power Pivot, não é possível adicionar uma linha a uma tabela digitando diretamente uma nova linha como em uma planilha do Excel. No entanto, pode adicionar linhas ao copiar e colar ou atualizar os dados de origem e atualizar o modelo do Power Pivot.