Modelo de dados do Excel (sumário)
- Introdução ao modelo de dados no Excel
- Como criar um modelo de dados no Excel?
Introdução ao modelo de dados no Excel
O recurso de modelo de dados do Excel facilita a criação de relacionamentos entre relatórios fáceis e seus conjuntos de dados em segundo plano. Isso facilita muito a análise de dados. Permite a integração de dados de uma infinidade de tabelas espalhadas por várias planilhas, simplesmente construindo relacionamentos entre as colunas correspondentes. Ele funciona completamente nos bastidores e simplifica bastante os recursos de relatórios, como Tabela Dinâmica etc.
Em nosso artigo, tentaremos mostrar como criar uma tabela dinâmica a partir de duas tabelas, empregando o recurso Modelo de Dados, estabelecendo um relacionamento entre dois objetos de tabela e criando uma Tabela Dinâmica.
Como criar um modelo de dados no Excel?
Vamos entender como criar o modelo de dados no Excel com alguns exemplos.
Você pode baixar este modelo de modelo de dados do Excel aqui - modelo de modelo de dados do ExcelExemplo 1
- Temos uma lista de produtos e um código de prateleira para cada produto. Precisamos de uma tabela onde tenhamos a descrição das prateleiras junto com os códigos das prateleiras. Então, como incorporamos as descrições de prateleiras contra cada código de prateleira? Talvez muitos de nós recorram a usar o VLOOKUP aqui, mas removeremos completamente a necessidade de usar o VLOOKUP aqui usando o Modelo de Dados do Excel.
- A tabela à esquerda é a tabela de dados e a tabela à direita é a tabela de pesquisa. Como podemos ver pelos dados, é possível criar um relacionamento com base em colunas comuns.
- Agora, o modelo de dados é compatível apenas com objetos de tabela. Portanto, às vezes pode ser necessário converter conjuntos de dados em objetos de tabela. Para fazer isso, siga as etapas abaixo.
- Clique com o botão esquerdo do mouse em qualquer lugar do conjunto de dados.
- Clique na guia Inserir e navegue até Tabela no grupo Tabelas ou simplesmente pressione Ctrl + T.
- Desmarque ou marque a opção Minha tabela com a opção Cabeçalho. No nosso exemplo, ele realmente tem um cabeçalho. Clique OK.
- Ainda focados na nova tabela, precisamos fornecer um nome que seja significativo na caixa Nome (à esquerda da barra de fórmulas).
Em nosso exemplo, nomeamos a tabela Pessoal.
- Agora também precisamos fazer o mesmo processo para a tabela de pesquisa e denominar Shelf Code.
Criando um relacionamento
Então, primeiro, vamos para a guia Dados e, em seguida, selecionamos Relacionamentos no subgrupo Ferramentas de Dados. Depois de clicar na opção Relacionamentos, no começo, já que não há relacionamento, não teremos nada.
Primeiro, clicaremos em Novo para criar um relacionamento. Agora, precisaremos fornecer os nomes das tabelas principal e de pesquisa na lista suspensa e também mencionar a coluna que é comum entre as duas tabelas, para que possamos estabelecer o relacionamento entre as duas tabelas, na lista suspensa. de colunas.
- Agora, a tabela principal é a tabela que possui os dados. É a tabela de dados primária - Tabela5. Por outro lado, a tabela Relacionada é a tabela que possui os dados de pesquisa - é a nossa tabela de pesquisa ShelfCodesTable. A tabela principal é a que é analisada com base na tabela de pesquisa que contém dados de pesquisa que tornarão os dados relatados no final mais significativos.
- Portanto, a coluna comum entre as duas tabelas é a coluna Código da prateleira. É isso que usamos para estabelecer o relacionamento entre as duas tabelas. Chegando às colunas, a Coluna (estrangeira) é aquela que se refere à tabela de dados em que pode haver valores duplicados. Por outro lado, a coluna relacionada (principal) refere-se à coluna na tabela de pesquisa em que temos valores únicos. Estamos simplesmente configurando o campo para pesquisar valores da tabela de pesquisa na tabela de dados.
- Depois que configurássemos isso, o Excel criaria um relacionamento entre os dois nos bastidores. Ele integra os dados e cria um modelo de dados com base na coluna comum. Isso não apenas esclarece os requisitos de memória, mas também é muito mais rápido do que usar o VLOOKUP em grandes pastas de trabalho. Depois de definir o modelo de dados, o Excel trataria esses objetos como tabelas de modelo de dados em vez de uma tabela de planilha.
- Agora, para ver o que o Excel está fazendo, podemos clicar em Gerenciar Modelos de Dados em Dados -> Ferramentas de Dados.
- Também podemos obter a representação esquemática do modelo de dados alterando a visualização. Vamos clicar na opção Visualizar. Isso abrirá as opções de exibição. Em seguida, selecionaremos a exibição de diagrama. Em seguida, veremos a representação diagramática, mostrando as duas tabelas e a relação entre elas, ou seja, a coluna comum - Código da prateleira.
- O diagrama acima mostra um relacionamento um para muitos entre os valores exclusivos da tabela de pesquisa e a tabela de dados com valores duplicados.
- Agora teremos que criar uma tabela dinâmica. Para fazer isso, iremos para a guia Inserir e, em seguida, clique na opção Tabela Dinâmica.
Na caixa de diálogo Criar tabela dinâmica da tabela dinâmica, selecionaremos a fonte como "Usar o modelo de dados desta pasta de trabalho".
- Isso criará a tabela dinâmica e podemos ver que as duas tabelas de origem estão disponíveis na seção de origem.
- Agora, criaremos uma tabela dinâmica que mostra a contagem de cada pessoa que arquivou itens.
- Selecionaremos Pessoal na seção Linhas da Tabela 5 (tabela de dados), seguida por Descrição (tabela de pesquisa).
- Agora, arrastaremos o Código da prateleira da Tabela 5 para a seção Valores.
- Agora, adicionaremos Meses da Tabela 5 à seção Linhas.
- Ou podemos adicionar os meses como um filtro e adicioná-lo à seção Filtros.
Exemplo 2
- Agora temos o Sr. Basu executando uma fábrica chamada Basu Corporation. Basu está tentando estimar a receita para 2019 com base nos dados de 2018.
- Temos uma tabela em que temos a receita para 2018 e a receita subsequente em diferentes níveis incrementais.
- Portanto, temos a receita para 2018 - US $ 1, 5 milhão e o crescimento mínimo esperado para o ano seguinte é de 12%. Basu quer uma tabela que mostre a receita em diferentes níveis incrementais.
- Criaremos a tabela a seguir para as projeções em diferentes níveis incrementais para 2019.
- Agora, forneceremos à primeira linha Receita uma referência à receita mínima estimada para 2019, ou seja, US $ 1, 68 M.
- Depois de usar a fórmula, a resposta é mostrada abaixo.
- Agora, selecionaremos a tabela inteira, ou seja, D2: E12 e, em seguida, iremos para Dados -> Previsão -> Análise de variações hipotéticas -> Tabela de dados.
- Isso abrirá a caixa de diálogo Tabela de dados. Aqui, inseriremos a porcentagem mínima de incremento da célula B4 na célula de entrada da coluna. A razão para isso é que nossas porcentagens de crescimento estimadas projetadas na tabela são organizadas de forma colunar.
- Depois de clicar em OK, a análise de variações hipotéticas preencherá automaticamente a tabela com a receita projetada nas diferentes porcentagens incrementais.
Exemplo 3
- Agora, suponha que tenhamos o mesmo cenário acima, exceto que agora também temos outro eixo a considerar. Suponhamos que, além de mostrar a receita projetada para 2019 com base nos dados de 2018 e a taxa de crescimento mínima esperada, agora também tenhamos a taxa de desconto estimada.
- Primeiro, teremos uma tabela mostrada abaixo.
- Agora, referiremos a receita mínima projetada para 2019, ou seja, da célula B5 à célula D8.
- Agora, selecionaremos a tabela inteira, ou seja, D8: J18 e, em seguida, iremos para Dados -> Previsão -> Análise de variações hipotéticas -> Tabela de dados.
- Isso abrirá a caixa de diálogo Tabela de dados. Aqui, inseriremos a porcentagem mínima de incremento da célula B3 na célula de entrada da coluna. A razão para isso é que nossas porcentagens de crescimento estimadas projetadas na tabela são organizadas de forma colunar. Agora, também inseriremos adicionalmente a porcentagem de desconto mínimo da célula B4 na célula de entrada de linha. A razão para isso é que nossas porcentagens de desconto projetadas na tabela são organizadas em linhas.
- Clique OK. Isso fará com que a análise de variações hipotéticas preencha automaticamente a tabela com a receita projetada nas diferentes porcentagens incrementais de acordo com as porcentagens de desconto.
Lembretes sobre o Modelo de Dados no Excel
- Após o cálculo bem-sucedido dos valores da tabela de dados, um simples Desfazer, ou seja, Ctrl + Z não funcionará. No entanto, é possível excluir manualmente os valores da tabela.
- Não é possível excluir uma única célula da tabela. Ele é descrito como uma matriz internamente no Excel, portanto, teremos que excluir todos os valores.
- Precisamos selecionar corretamente a célula de entrada de linha e a célula de entrada de coluna.
- A tabela de dados, diferentemente da tabela dinâmica, não precisa ser atualizada sempre.
- Usando o Modelo de Dados no Excel, podemos não apenas melhorar o desempenho, mas também facilitar os requisitos de memória em planilhas grandes.
- Os Modelos de Dados também tornam nossa análise muito mais simples em comparação ao uso de várias fórmulas complicadas em toda a pasta de trabalho.
Artigos recomendados
Este é um guia para o Modelo de Dados no Excel. Aqui discutimos como criar um modelo de dados no Excel, além de exemplos práticos e modelo de excel para download. Você também pode consultar nossos outros artigos sugeridos -
- Barra de fórmulas no Excel
- Imprimir linhas de grade no Excel
- Janela de inspeção no Excel
- SUMIFS do Excel com datas