Pular para o conteúdo principal
InicioTutoriaisPlanilhas

Como criar modelos de dados no Excel: Um guia abrangente

Criamos modelos de dados formatando dados, criando relacionamentos, usando o Power Query e aproveitando o Power Pivot para integração e análise de dados perfeitas.
Actualizado 29 de jul. de 2024  · 13 min leer

Os modelos de dados nos permitem carregar, integrar e analisar dados de várias fontes, facilitando uma visão mais dinâmica e perspicaz dos dados. 

Nas seções do tutorial, exploraremos várias maneiras de criar modelos de dados no Excel, incluindo o uso de tabelas existentes, a importação de novas tabelas e o aproveitamento de ferramentas como Power Query, Power Pivot e PivotCharts.   

Vamos lá!

Como criar um modelo de dados no Excel

Aqui estão as etapas gerais a serem seguidas para criar um modelo de dados no Excel:

  1. Formate seus dados como tabelas.
  2. Crie relacionamentos relações entre as tabelas.
  3. Use o Power Query para carregar e transformar dados.
  4. Use o Power Pivot para modelagem avançada de dados.
  5. Utilize PivotTables e PivotCharts para visualizar seus dados.

Quando usar um modelo de dados no Excel

A criação de um modelo de dados no Excel é particularmente útil em vários cenários comuns, incluindo limpeza de dados, integração e transformação de dados e análise de dados complexos. Aqui estão alguns dos principais benefícios.

  • Facilita a limpeza de dados: Os modelos de dados ajudam a limpar e padronizar diferentes conjuntos de dados para manter a uniformidade e a consistência. Isso é fundamental quando você lida com dados de diferentes fontes, o que exige combinação e pré-processamento. 
  • Realiza ou resolve a integração e a transformação de dados: Com o modelo de dados em vigor, todos os dados disponíveis de qualquer outra fonte podem ser facilmente integrados e transformados, e os usuários de dados podem receber uma visão unificada que é fácil de processar e analisar.
  • Aprimora a visualização de dados: Os modelos de dados dão suporte ao desenvolvimento de visualizações avançadas de dados, como gráficos dinâmicos e relatórios do Power View. 
  • Melhora a segurança e o gerenciamento de dados: Com um modelo de dados, você pode ter acesso controlado a diferentes partes dos seus dados. Você terá mais facilidade para gerenciar permissões e dados dentro do modelo para reduzir os riscos de acesso não autorizado e violações de dados.
  • Simplifica os relatórios de dados: Por meio de modelos de dados, a geração de relatórios de dados pode ser totalmente automatizada, facilitando assim o processo de geração de relatórios e de tomada de decisões. Isso possibilita que os relatórios e painéis sejam ajustados dinamicamente caso haja a inserção de novos dados. Assim, todas as partes interessadas recebem informações atualizadas para seu uso.
  • Lida com grandes conjuntos de dados: Os modelos de dados podem trabalhar com grandes conjuntos de dados no Excel, superando as restrições das planilhas tradicionais do Excel.
  • Permite a análise de dados complexos: O recurso de criar relações entre várias tabelas de dados permite que você faça análises avançadas de dados que talvez não sejam fáceis com as funções regulares do Excel.

Como criar um modelo de dados por meio da importação de dados

Nesta seção, examinaremos o processo de criação de um modelo de dados no Excel, importando conjuntos de dados de amostra. Usaremos dois conjuntos de dados de amostra: Pedido e produto. Criei um repositório público no GitHub chamado Excel-Data-Model para que você possa fazer o download dos conjuntos de dados e acompanhar este tutorial.

A primeira etapa é importar os dados para o Excel. Comece abrindo uma nova pasta de trabalho do Excel e clique na guia Data. Em Get Data (Obter dados), selecione Get Data (Obter dados) > From File (Do arquivo) > From Excel Workbook (Da pasta de trabalho do Excel).

Usando modelos de dados do Excel para importar dados para o Excel

Importação de dados para o Excel. Imagem do autor

Selecione a pasta de trabalhoOrder.xlsx e clique em Importar. Isso abrirá a janela do Navigator: Selecione a tabela Sheet1.

Janela do navegador de modelos de dados do Excel

A janela Navegador no Excel. Imagem do autor

Clique na opção Carregar para, que abrirá a janela Importar dados. Na caixa de diálogo, selecione Only Create Connection e marque Add this data to the Data Model. Clique em OK.

 Janela Importar dados com modelos de dados do Excel

Janela Importar dados. Imagem do autor

Repita as etapas acima para carregar a folha Product. Quando essas duas tarefas forem concluídas, sua pasta de trabalho do Excel terá a seguinte aparência:

 Consultas de modelos de dados do Excel e painel de conexões no modelo de dados do Excel

Painel Queries & Connections (Consultas e conexões). Imagem do autor

Parabéns! Você importou as duas planilhas para o arquivo do Excel. A próxima etapa é estabelecer relações entre as tabelas importadas. Para fazer isso, vá para a guia Data Tools (Ferramentas de dados ) e clique em Relationships (Relações).

Guia Ferramentas de dados no modelo de dados do Excel

Guia Ferramentas de dados. Imagem do autor 

Você verá uma nova janela.

Janela Gerenciar relacionamentos no modelo de dados do ExcelJanela Gerenciar relacionamentos. Imagem do autor

Clique em Novo para criar um relacionamento. A janela pop-up Criar relacionamento será aberta. Defina a relação conforme mostrado abaixo. 

Janela Criar relacionamento no modelo de dados do ExcelJanela Criar relacionamento. Imagem do autor

Clique em OK. O relacionamento é estabelecido e você pode confirmá-lo na guia Connections (Conexões ).

Painel Queries & Connections (Consultas e conexões) no modelo de dados do ExcelPainel Queries & Connections (Consultas e conexões). Imagem do autor

A próxima etapa é criar a tabela dinâmica. Vá até a guia Inserir e clique em Tabela dinâmica. Em seguida, na caixa de diálogo Tabela dinâmica, selecione Usar o modelo de dados desta pasta de trabalho.

Caixa de diálogo de tabela dinâmica no modelo de dados do ExcelCaixa de diálogo de tabela dinâmica. Imagem do autor

Selecione o local para a tabela dinâmica. Clique em OK. A tabela dinâmica será aberta e você poderá ver os campos em PivotTable Fields.

Campos de tabela dinâmica no modelo de dados do ExcelCampos de tabela dinâmica. Imagem do autor

Para criar a tabela dinâmica, execute a seguinte etapa.

No painel Campos da tabela dinâmica, arraste os campos para as áreas Linhas, Colunas, Valores e Filtros:

  • Fileiras: ProductName
  • Valores: Quantidade

A tabela dinâmica resultante terá a seguinte aparência:

Painel Campos da tabela dinâmica no modelo de dados do Excel

Painel Campos da tabela dinâmica. Imagem do autor

Você pode personalizar a tabela dinâmica e formatá-la conforme necessário.

Como criar um modelo de dados usando o Power Query 

Para esta seção, usaremos os dados armazenados no arquivo do Excel chamado Sample_Data. Esse arquivo também está disponível no mesmo link fornecido na seção anterior. Há duas tabelas na planilha chamada Data, que têm os seguintes registros.

Tabelas do Excel a serem unidas no modelo de dados do ExcelTabelas do Excel a serem unidas. Imagem do autor

Criaremos modelos de dados nessa planilha existente do Excel. Para começar, selecione qualquer célula na tabela Customer ID and Name e, em seguida, selecione Ctrl + T.

Selecionando uma célula em uma tabela no modelo de dados do ExcelSelecionando uma célula. Imagem do autor

Isso abre uma janela pop-up Criar tabela, conforme mostrado abaixo.

Criar tabela pop-up no modelo de dados do Excel

Criar tabela pop-up. Imagem do autor

 Clique em OK e você terá a tabela pronta. 

Tabela dinâmica no modelo de dados do ExcelTabela dinâmica no Excel. Imagem do autor

Agora vamos dar um nome a essa tabela. Vá para a janela contextual Design de tabela e, em Nome da tabela, renomeie a tabela para CustomerPQ. Observe que você também pode dar qualquer outro nome. 

Janela de contexto de design de tabela no modelo de dados do Excel

Na janela de contexto do design da tabela, você pode ver o que está acontecendo. Imagem do autor

Agora essa tabela está definida como CustomerPQ.

Janela de contexto do Table Design no modelo de dados do ExcelJanela de contexto do Table Design. Imagem do autor

Repita o processo para a segunda tabela. Dessa vez, quando a tabela for criada, você deverá definir o nome como PurchasePQ.

Duas tabelas no Excel prontas para o modelo de dados no ExcelDuas tabelas no Excel prontas para o modelo de dados. Imagem do autor

Agora, a tabela ativa é a tabela PurchasePQ. Vá para Data (Dados) e selecione From Table Range( Do intervalo da tabela ). A seguinte planilha será exibida.

Query Editor no modelo de dados do ExcelAbrindo o Power Abrindo o Power Query Editor. Imagem do autor

Para criar uma conexão e adicionar a tabela Purchase PQ ao modelo de dados, clique na guia suspensa Close & Load e selecione Close & Load To...  

o Power Query Editor no modelo de dados do ExcelFechando Fechando o Power Query Editor. Imagem do autor

Isso abrirá uma janela Importar dados, que precisa ser preenchida da seguinte maneira:

Janela Importar dados no modelo de dados do ExcelJanela Importar dados. Imagem do autor 

Depois de concluir as etapas acima, você verá a caixa Queries & Connections (Consultas e conexões ).

Caixa Queries & Connections no modelo de dados do ExcelCaixa Queries & Connections. Imagem do autor

Agora, adicione a tabela CustomerPQ à conexão. Para isso, clique em qualquer célula da tabela, vá para Data (Dados) e selecione From Table Range( Do intervalo da tabela ). A seguinte planilha será exibida.

prontas para o modeloTabelas de dados do ExcelTabelas prontas para o modelo de dados do Excel. Imagem do autor

Repita o processo que você concluiu para a tabela PurchasePQ e preencha a janela Importar dados conforme mostrado abaixo:

Janela Importar dados no modelo de dados do ExcelJanela Importar dados. Imagem do autor 

 Clique em OK. Na guia Queries & Connections (Consultas e conexões ), você verá as duas tabelas.

Painel Consultas e conexões no modelo de dados do ExcelConsultas e conexões. Imagem do autor

Agora estamos prontos para estabelecer relacionamentos. Na guia Dados, vá para a seção Ferramentas de dados e clique em Gerenciar modelo de dados.

Painel Ferramentas de dados no modelo de dados do ExcelFerramentas de dados. Imagem do autor

Isso abrirá o site Power Pivot for Excel.

Modelo de dados do Power Pivot para ExcelPower Pivot para Excel. Imagem do autor

Agora, para criar uma relação entre as duas tabelas, clique na visualização de diagrama.

Painel de visualização no modelo de dados do ExcelPainel de visualização. Imagem do autor

 Isso abrirá uma tela como a mostrada abaixo. 

Visualização de diagrama no modelo de dados do ExcelVisualização de diagrama. Imagem do autor

Você pode ver que há duas tabelas, mas ainda não conectadas. Para criar o relacionamento, vá até a guia Design e clique na guia Criar relacionamento.

Guia Design no modelo de dados do ExcelGuia Design. Imagem do autor

Uma nova janela será aberta. Preencha os detalhes conforme mostrado abaixo:

Criar uma janela de relacionamento no modelo de dados do ExcelCriar uma janela de relacionamento. Imagem do autor

Clique em OK. Você perceberá que o relacionamento já está estabelecido.

Janela mostrando que o relacionamento foi estabelecido no modelo de dados do ExcelJanela mostrando que o relacionamento foi estabelecido. Imagem do autor 

Salve o Power Pivot usando a guia Salvar ou Ctrl + S e feche a guia. O seguinte resultado é exibido.

Ativar a janela pop-up Content no modelo de dados do ExcelAtivar pop-up Content. Imagem do autor

Clique em Enable Content ( Ativar conteúdo ) para criar a conexão. Agora a conexão está estabelecida. Agora você pode usar a tabela dinâmica. Para fazer isso, vá para Inserir, clique na opção Tabela dinâmica e selecione Do modelo de dados.

Painel de tabela dinâmica no modelo de dados do ExcelPainel de tabela dinâmica. Imagem do autor

Você verá uma janela pop-up como esta:

Tabela dinâmica da guia Modelo de dados no modelo de dados do ExcelTabela dinâmica da guia Modelo de dados. Imagem do autor

Clique em New Worksheet ( Nova planilha ) e clique em OK. Uma nova planilha chamada Sheet1 é criada.

Planilha de tabela dinâmica no ExcelPlanilha de tabela dinâmica. Imagem do autor 

Em PivotTable Fields, você pode encontrar as tabelas.

Rótulos em campos de tabela dinâmica no modelo de dados do ExcelRótulos em campos de tabela dinâmica. Imagem do autor

Selecione Customer Name na tabela CustomerPQ. Em seguida, em PurchasePQ, selecione Purchase. Os campos da tabela dinâmica terão a seguinte aparência:

Selecionando itens nos campos da tabela dinâmica no Excel com nosso modelo de dados do ExcelSelecionando itens nos campos da tabela dinâmica. Imagem do autor 

Isso gerará a tabela dinâmica dentro da planilha, que terá a aparência abaixo:

Saída de tabela dinâmica no modelo de dados do ExcelSaída de tabela dinâmica. Imagem do autor

Bingo! Dessa forma, você pode usar o Power Query para criar o modelo de dados e usar o Power Pivot para criar relatórios resumidos.

Como criar um modelo de dados diretamente no Power Pivot

Nesta seção, você usará o Power Pivot para criar um modelo de dados e preparar um relatório resumido. 

Para começar, vá para a planilha chamada Power Pivot nos dados que discutimos acima e crie as tabelas para nossos dois registros.

Registros a serem usados no modelo de dados do ExcelRegistros a serem usados. Imagem do autor 

Já vimos como criar tabelas nas seções anteriores, portanto, siga essas etapas para criar as duas tabelas.

A única diferença seria que definiremos novos nomes para essas tabelas, para que possamos diferenciar a seção anterior: 

  • Defina o nome da primeira tabela como CustomerPP.
  • Defina o nome da segunda tabela como PurchasePP.

Duas tabelas usadas para o modelo de dados do Excel

Dados Tabelas dos registros. Imagem do autor

As duas tabelas são criadas e a próxima etapa é adicionar essas tabelas ao modelo de dados. Para fazer isso, acesse Power Pivot e selecione Add to Data Model.

Adicionar ao modelo de dados do Excel em PowerPivot no Excel

Adicionar ao modelo de dados em PowerPivot. Imagem do autor

Isso abrirá uma janela pop-up do site Power Pivot for Excel com a seguinte aparência.

Pop-up do Power Pivot para Excel com modelo de dados do Excel

Pop-up do Power Pivot para Excel. Imagem do autor 

 

Salve e feche essa janela. Repita o mesmo processo para a tabela CustomerPP para que você a adicione ao modelo de dados.

Pop-up do Power Pivot para Excel com modelo de dados do Excel

Pop-up do Power Pivot para Excel. Imagem do autor

Depois que essas tabelas forem adicionadas ao modelo de dados, você poderá estabelecer a relação entre elas usando o Power Pivot. Para fazer isso, clique em Diagram View.

Visualização de diagrama no modelo de dados do Excel

Visualização do diagrama. Imagem do autor

Em seguida, vá para a seção Design e clique em Criar relacionamento.

Criar uma guia Relacionamento no modelo de dados do Excel

Crie uma guia Relacionamento. Imagem do autor

Uma nova janela será aberta. Preencha os detalhes conforme mostrado abaixo:

Se você quiser criar uma relação entre duas tabelas usando modelos de dados no Excel, deverá usar o recurso de modelos de dados.

Crie uma relação entre duas tabelas. Imagem do autor

Clique em OK. Você perceberá que o relacionamento está estabelecido.

 Os relacionamentos são estabelecidos no modelo de dados do Excel

Os relacionamentos são estabelecidos. Imagem do autor

É fácil criar uma tabela dinâmica a partir daqui, como fizemos na seção anterior. O resultado final deve ser semelhante a este.

Resumo da tabela dinâmica com o modelo de dados do Excel

Resumo da tabela dinâmica. Imagem do autor

Seguindo as etapas acima, você pode usar o Power Pivot para criar o modelo de dados e criar uma análise resumida.

Como criar um modelo de dados com PivotTable ou PivotChart

Depois que você criar a tabela dinâmica, será fácil inserir um gráfico dinâmico. Com a tabela dinâmica (Sheet 1 ) selecionada, vá para a guiaInserir.

Resumo da tabela dinâmica em um modelo de dados do Excel

Resumo da tabela dinâmica. Imagem do autor

Clique em PivotChart (Gráfico dinâmico)selecione o tipo de gráfico e clique em OK.

Usando modelos de dados no Excel com as opções Inserir gráfico

Opções de inserção de gráfico. Imagem do autor

Isso criará o seguinte gráfico dinâmico.

Usando modelos de dados no Excel com gráficos dinâmicos

PivotChart visualization. Imagem do autor

Você pode personalizar o gráfico dinâmico:

  • Ajustar os elementos do gráfico, como títulos, legendas e rótulos de eixo, e
  • Formatar o gráfico para melhorar sua aparência e legibilidade.

Ótimo, você usou a tabela dinâmica para criar sua primeira visualização.

Perguntas e problemas comuns sobre o modelo de dados do Excel

Aqui estão algumas das perguntas e problemas comumente associados aos modelos de dados do Excel:

O que devo fazer se um problema de modelo de dados estiver impedindo o Microsoft Excel de abrir uma pasta de trabalho?

Esse problema pode ocorrer devido à corrupção no modelo de dados ou a problemas de compatibilidade. Aqui estão as etapas para resolver o problema:

  1. Abra no modo de segurança: Tente abrir o Excel no modo de segurança, mantendo pressionada a tecla Ctrl ao iniciar o Excel. Às vezes, isso pode contornar o problema.
  2. Verifique se há atualizações: Para garantir que o Excel esteja atualizado, acesse File > Account > Update Options.
  3. Escritório de reparos: Use a ferramenta de reparo do Office. Acesse Painel de Controle > Programas e Recursos, selecione a instalação do Office e escolha Alterar > Reparar.
  4. Desativar suplementos: Acesse File > Options > Add-ins e desative todos os add-ins. Reinicie o Excel e tente abrir a pasta de trabalho novamente.
  5. Extração de dados: Se você não conseguir abrir a pasta de trabalho, tente usar uma ferramenta de recuperação de dados ou entre em contato com o suporte da Microsoft para obter assistência avançada.

Como posso solucionar erros no modelo de dados?

Para solucionar erros no modelo de dados:

  1. Verifique os tipos de dados: Certifique-se de que os tipos de dados nas colunas usadas para relacionamentos sejam consistentes.
  2. Validação de relacionamentos: Verifique se todos os relacionamentos estão definidos corretamente.
  3. Revisão dos cálculos: Verifique se há erros nas colunas e medidas calculadas.
  4. Atualizar dados: Certifique-se de que todas as conexões de dados estejam atualizadas.
  5. Mensagens de erro: Examine todas as mensagens de erro na janela do Power Pivot para ver se há problemas específicos.

Seguindo essas etapas, você pode gerenciar e solucionar problemas de forma eficaz no seu modelo de dados no Excel, garantindo uma análise de dados tranquila e eficiente. O Suporte da Microsoft é outro recurso útil para a solução de problemas. 

Considerações finais

Obrigado por você ter concluído o tutorial! Você aprendeu um conceito importante em sua jornada para dominar a análise de dados usando o Excel. A criação de modelos de dados aprimorará significativamente os recursos de análise de dados para você e sua organização. Também aumenta a sua eficiência no gerenciamento de diferentes tabelas e registros de dados. 

Para aprender mais, considere explorar as seguintes fontes:   

  • Introdução ao Excel: Um guia completo para você começar a usar o Excel, abrangendo as funcionalidades e os recursos básicos.
  • Análise de dados no Excel: Aprenda várias técnicas e ferramentas de análise de dados disponíveis no Excel para aprimorar suas habilidades analíticas.
  • Fundamentos do Excel: Um curso que abrange os conceitos e operações fundamentais do Excel para que você tenha uma base sólida.
  • Visualização de dados no Excel: Explore como criar visualizações impactantes e interativas usando as ferramentas internas do Excel.
  • Introdução ao Power Query no Excel: Entenda como usar o Power Query para importar, limpar e transformar dados de forma eficiente no Excel.

Além disso, você aprenderá Como obter uma certificação do Microsoft Excel para validar suas habilidades.

Perguntas frequentes

O que é um modelo de dados no Excel e como ele é útil?

Com um modelo de dados no Excel, é possível unir dados de várias fontes em uma única fonte de dados. Isso permite que você não apenas realize análises complexas usando tabelas dinâmicas e gráficos dinâmicos, mas também que faça cálculos e relacionamentos de muitos para muitos sem redundância nos dados.

Posso atualizar meu modelo de dados se meus dados de origem mudarem e como faço isso?

Sim, você pode atualizar facilmente seu modelo de dados se os dados de origem forem alterados. Para fazer isso, tudo o que você precisa fazer é acessar a guia Data e clicar em Refresh All. Isso atualizará os dados em seu modelo e quaisquer tabelas dinâmicas e gráficos dinâmicos associados.

Posso usar dados de bancos de dados externos em meu modelo de dados do Excel?

Sim, você pode usar dados de bancos de dados externos. Acesse Data > Get Data e selecione a fonte apropriada. Em seguida, siga as instruções para se conectar ao banco de dados, selecione as tabelas de que você precisa e adicione-as ao modelo de dados.

Como faço para remover uma tabela do modelo de dados?

Para remover uma tabela do modelo de dados, vá paraDados > Gerenciar modelo de dados para abrir a janela do Power Pivot. Selecione a tabela que você deseja remover, clique com o botão direito do mouse sobre ela e selecione Delete.

Quais são os benefícios de usar o Power Query para modelagem de dados no Excel?

Os benefícios de usar o Power Query para modelagem de dados no Excel incluem transformações de dados, automação, integração com várias fontes, interfaces intuitivas e fáceis de usar e escalabilidade.

Temas

Aprenda Excel com a DataCamp

Course

Data Preparation in Excel

3 hr
25.7K
Understand how to prepare Excel data through logical functions, nested formulas, lookup functions, and PivotTables.
See DetailsRight Arrow
Start Course
Ver maisRight Arrow
Relacionado

blog

Como exportar dados do Power BI para o Excel

Você gostaria de poder ver os dados por trás de suas visualizações do Power BI? Aqui está nosso guia para você exportar dados do Power BI para o Excel.
Eugenia Anello's photo

Eugenia Anello

6 min

blog

Como fazer a transição do Excel para o Power BI

Interessado em aprender o Power BI, mas não sabe por onde começar? Leia este guia para obter aprendizado passo a passo e recursos para continuar praticando o que você aprendeu
Eugenia Anello's photo

Eugenia Anello

11 min

blog

O que são modelos semânticos do Power BI?

Saiba mais sobre modelos semânticos no Power BI, seus componentes, modos e práticas recomendadas para criá-los e gerenciá-los.
Joleen Bothma's photo

Joleen Bothma

7 min

tutorial

Como criar tabelas de datas no Power BI Tutorial

Saiba como criar tabelas de datas no Power BI com este tutorial visual passo a passo.
Kafaru Simileoluwa's photo

Kafaru Simileoluwa

12 min

tutorial

Tutorial do Power BI para iniciantes

Aprenda os conceitos básicos do Power BI e como criar um relatório básico com este tutorial passo a passo.
DataCamp Team's photo

DataCamp Team

16 min

tutorial

Tutorial de execução de scripts Python no Power BI

Descubra as diferentes maneiras de usar o Python para otimizar a análise, a visualização e a modelagem de dados no Power BI.
Joleen Bothma's photo

Joleen Bothma

9 min

See MoreSee More