Análise Avançada de Dados - Modelo de Dados
O modelo de dados está disponível no Excel 2013 e em versões posteriores. No Excel, você pode usar o Modelo de Dados para integrar dados de várias tabelas na pasta de trabalho atual e / ou dos dados importados e / ou das fontes de dados conectadas à pasta de trabalho por meio de conexões de dados.
Com um modelo de dados, você pode criar relacionamentos entre as tabelas. O modelo de dados é usado de forma transparente em relatórios de Tabela Dinâmica, Gráfico Dinâmico, PowerPivot e Power View.
Criação de modelo de dados durante a importação de dados
Quando você importa dados de bancos de dados relacionais como o banco de dados Microsoft Access, que contém várias tabelas relacionadas, o Modelo de Dados é criado automaticamente se você importar mais de uma tabela de uma vez.
Você pode opcionalmente adicionar tabelas ao Modelo de Dados, ao importar dados das seguintes fontes de dados -
- Bancos de dados relacionais, uma tabela por vez
- Arquivos de Texto
- Pastas de trabalho do Excel
Por exemplo, enquanto você está importando dados de uma pasta de trabalho do Excel, você pode observar a opção Add this data to the Data Model, com uma caixa de seleção ativada.
Se você deseja adicionar os dados que está importando ao Modelo de Dados, marque a caixa.
Criação de modelo de dados a partir de tabelas do Excel
Você pode criar o Modelo de Dados a partir de tabelas do Excel usando comandos PowerPivot. Você aprenderá o PowerPivot em detalhes nos capítulos posteriores.
Todos os comandos do Modelo de Dados estão disponíveis na guia PowerPivot na Faixa de Opções. Você pode adicionar tabelas do Excel ao Modelo de Dados com esses comandos.
Considere a seguinte pasta de trabalho de dados de vendas, na qual você tem a planilha Catálogo de Produtos que contém Produto, ID do Produto e Preço. Você tem quatro planilhas de vendas em 4 regiões - Leste, Norte, Sul e Oeste.
Cada uma dessas quatro planilhas contém o número de unidades vendidas e o valor total para cada um dos produtos em cada mês. Você precisa calcular o valor total de cada um dos produtos em cada região e o valor total das vendas em cada região.
As etapas a seguir permitem que você chegue aos resultados desejados -
- Comece criando o modelo de dados.
- Clique na planilha do Catálogo de Produtos.
- Clique na guia POWERPIVOT na faixa de opções.
- Clique em Adicionar ao modelo de dados. A caixa de diálogo Criar Tabela é exibida.
- Selecione o intervalo da tabela.
- Marque a caixa Minha tabela tem cabeçalhos. Clique OK.
Uma nova janela - PowerPivot para Excel - <seu nome de arquivo do Excel> é exibida.
A seguinte mensagem aparece no centro da janela em branco -
A tabela Product Backlog que você adicionou ao Modelo de Dados aparece como uma folha na janela do PowerPivot. Cada linha da tabela é um registro e você pode voltar e avançar os registros usando os botões de seta para a esquerda e para a direita na parte inferior da janela.
- Clique na guia Tabela Vinculada na janela do PowerPivot.
- Clique em Ir para a tabela do Excel.
A janela de dados do Excel é exibida.
- Clique na guia da planilha - Leste.
- Clique na guia POWERPIVOT na faixa de opções.
- Clique em Adicionar ao modelo de dados.
Outra folha aparece na janela do PowerPivot exibindo a tabela Leste.
Repita para as planilhas - Norte, Sul e Oeste. Ao todo, você adicionou cinco tabelas ao Modelo de Dados. A janela do PowerPivot tem a seguinte aparência -
Criação de relacionamentos entre tabelas
Se quiser fazer cálculos nas tabelas, você deve primeiro definir as relações entre elas.
Clique na guia Início na Faixa de Opções na janela do PowerPivot. Como você pode observar, as tabelas são exibidas na Visualização de Dados.
Clique em Exibir diagrama.
As tabelas aparecem na Visualização de Diagrama. Como você pode observar, algumas das tabelas podem estar fora da área de exibição e todos os campos nas tabelas podem não estar visíveis.
- Redimensione cada tabela para mostrar todos os campos dessa tabela.
- Arraste e organize as tabelas para que todas sejam exibidas.
- Na tabela Leste, clique em ID do produto.
- Clique na guia Design na Faixa de Opções.
- Clique em Criar relacionamento. A caixa de diálogo Criar Relacionamento é exibida.
Na caixa sob Tabela, o Leste é exibido. Na caixa abaixo da coluna, a ID do produto é exibida.
- Na caixa em Tabela de pesquisa relacionada, selecione Catálogo de produtos.
- A ID do produto aparece na caixa em Coluna de pesquisa relacionada.
- Clique no botão Criar.
Aparece a linha que representa o relacionamento entre as tabelas Leste e Backlog do produto.
- Repita os mesmos passos para as tabelas - Norte, Sul e Oeste. Linhas de relacionamento aparecem.
Resumindo os dados nas tabelas no modelo de dados
Agora, você está pronto para resumir os dados de vendas de cada um dos produtos em cada região em apenas algumas etapas.
- Clique na guia Página inicial.
- Clique em Tabela Dinâmica.
- Selecione PivotTable na lista suspensa.
A caixa de diálogo Criar Tabela Dinâmica é exibida na janela de tabelas do Excel. Selecione Nova planilha.
Em uma nova planilha, uma tabela dinâmica vazia é exibida. Como você pode observar, a Lista de Campos contém todas as tabelas do Modelo de Dados com todos os campos exibidos.
Selecione a ID do produto na Tabela 1 (Catálogo de produtos).
Selecione Valor total nas outras quatro tabelas.
Para cada um dos campos em ∑ Valores, altere o Nome personalizado em Configurações do campo de valor para exibir os nomes das regiões como rótulos de coluna.
A soma do valor total será substituída pelo rótulo que você fornecer. A tabela dinâmica com valores resumidos de todas as tabelas de dados mostra os resultados necessários.
Adicionando dados ao modelo de dados
Você pode adicionar uma nova tabela de dados ao Modelo de Dados ou novas linhas de dados às tabelas existentes no Modelo de Dados.
Adicione uma nova tabela de dados ao Modelo de Dados com as seguintes etapas.
Clique na guia DADOS na faixa de opções.
Clique em Conexões existentes no grupo Obter dados externos. A caixa de diálogo Conexões existentes é exibida.
Clique na guia Tabelas. Os nomes de todas as tabelas da pasta de trabalho serão exibidos.
Clique no nome da tabela que deseja adicionar ao Modelo de Dados.
Clique no botão Abrir. A caixa de diálogo Importar dados é exibida.
Como você sabe, ao importar a tabela de dados, ela é automaticamente adicionada ao Modelo de Dados. A tabela recém-adicionada aparece na janela do PowerPivot.
Adicione novas linhas de dados às tabelas existentes no Modelo de Dados.
Atualize a conexão de dados. Novas linhas de dados da fonte de dados são adicionadas ao Modelo de Dados.