Course
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:
- Formate seus dados como tabelas.
- Crie relacionamentos relações entre as tabelas.
- Use o Power Query para carregar e transformar dados.
- Use o Power Pivot para modelagem avançada de dados.
- 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).
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
.
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. 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:
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. Imagem do autor
Você verá uma nova janela.
Janela 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. 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). 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. 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. 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. 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. 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. Imagem do autor
Isso abre uma janela pop-up Criar tabela, conforme mostrado abaixo.
Criar tabela pop-up. Imagem do autor
Clique em OK e você terá a tabela pronta.
Tabela 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.
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. 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. 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.
Abrindo 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...
Fechando Fechando o Power Query Editor. Imagem do autor
Isso abrirá uma janela Importar dados, que precisa ser preenchida da seguinte maneira:
Janela Importar dados. Imagem do autor
Depois de concluir as etapas acima, você verá a caixa Queries & Connections (Consultas e conexões ).
Caixa 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.
Tabelas 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. Imagem do autor
Clique em OK. Na guia Queries & Connections (Consultas e conexões ), você verá as duas tabelas.
Consultas 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.
Ferramentas de dados. Imagem do autor
Isso abrirá o site Power Pivot for Excel
.
Power 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. Imagem do autor
Isso abrirá uma tela como a mostrada abaixo.
Visualizaçã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. Imagem do autor
Uma nova janela será aberta. Preencha os detalhes conforme mostrado abaixo:
Criar 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. Imagem do autor
Salve o Power Pivot usando a guia Salvar ou Ctrl + S e feche a guia. O seguinte resultado é exibido.
Ativar 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. Imagem do autor
Você verá uma janela pop-up como esta:
Tabela 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. Imagem do autor
Em PivotTable Fields, você pode encontrar as tabelas.
Ró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. Imagem do autor
Isso gerará a tabela dinâmica dentro da planilha, que terá a aparência abaixo:
Saí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. 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
.
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 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. 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. 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 do diagrama. Imagem do autor
Em seguida, vá para a seção Design e clique em Criar relacionamento.
Crie uma guia Relacionamento. Imagem do autor
Uma nova janela será aberta. Preencha os detalhes conforme mostrado abaixo:
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. 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. 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. Imagem do autor
Clique em PivotChart (Gráfico dinâmico)selecione o tipo de gráfico e clique em OK.
Opções de inserção de gráfico. Imagem do autor
Isso criará o seguinte gráfico dinâmico.
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:
- 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.
- Verifique se há atualizações: Para garantir que o Excel esteja atualizado, acesse File > Account > Update Options.
- 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.
- Desativar suplementos: Acesse File > Options > Add-ins e desative todos os add-ins. Reinicie o Excel e tente abrir a pasta de trabalho novamente.
- 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:
- Verifique os tipos de dados: Certifique-se de que os tipos de dados nas colunas usadas para relacionamentos sejam consistentes.
- Validação de relacionamentos: Verifique se todos os relacionamentos estão definidos corretamente.
- Revisão dos cálculos: Verifique se há erros nas colunas e medidas calculadas.
- Atualizar dados: Certifique-se de que todas as conexões de dados estejam atualizadas.
- 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.
Aprenda Excel com a DataCamp
Course
Introduction to Power Query in Excel
Course
Power Pivot in Excel
blog
Como exportar dados do Power BI para o Excel
Eugenia Anello
6 min
blog
Como fazer a transição do Excel para o Power BI
Eugenia Anello
11 min
blog
O que são modelos semânticos do Power BI?
tutorial
Como criar tabelas de datas no Power BI Tutorial
tutorial
Tutorial do Power BI para iniciantes
DataCamp Team
16 min
tutorial