MODELO DE DADOS NO EXCEL

você não sabe que precisa dele, mas precisa

Modelo de Dados no Excel

(você pode baixar aqui o arquivo básico de Modelo de Dados no Excel para praticar junto com o tutorial)

O Excel 2013 passou a contar com um recurso de nome pomposo, mas que na realidade é algo bem simples e que nos possibilita eliminar o uso do PROCV quando desejamos relacionar diversas planilhas.

Veja as planilhas abaixo. As 3 estão na mesma pasta de trabalho (arquivo):

20130719-1

Note que a planilha Fluxo possui colunas com códigos (banco e lançamento). Se formos, por exemplo, montar uma tabela dinâmica usando a planilha Fluxo como base, veríamos apenas os códigos ao invés das descrições, o que dificultaria e em muito nossa análise.

A solução mais típica seria colocar na planilha Fluxo colunas adicionais para trazermos o Nome do banco, Nome do Lançamento,  Tipo e Sigla. Seria algo assim:

20130719-2

Imagine o trabalho de ficar copiando para baixo sempre que novas linhas surgirem. Isso sem contar no aumento do tamanho do arquivo, afinal colocamos 4 colunas a mais.

Só que no Excel 2013 isto agora torna-se desnecessário, porque o Modelo de Dados no Excel serve exatamente para isto.

Nele indicamos as colunas que se relacionam nas planilhas, e quando formos montar uma tabela dinâmica, ele faz o relacionamento sem precisarmos criar centenas e centenas de PROCV.

Etapa 1 – Criar as Tabelas

Para cada uma das 3 planilhas vamos transformá-las em Tabelas.

Para fazê-lo, vá na planilha Bancos, clique em qualquer célula da área preenchida e em seguida clique em Inserir>Tabela (você pode saber mais sobre este recurso aqui):

20130719-3

Aparecerá a seguinte tela:

20130719-4

Ele indicará o intervalo completo da tabela. Mantenha exatamente o que ele mostrar. Clique em OK e a tabela está criada.

Note uma coisa:  Clique no menu Design (canto superior direito) e à esquerda você verá  nome da tabela criada. Vamos renomear para tbBancos.

Basta escrever no campo Nome da Tabela e dar um enter:

20130719-5

Agora crie tabelas nas planilhas Lançamentos e Fluxo. Chame a tabela de Lançamentos como tbLancamentos e o Fluxo de tbFluxo:

20130719-6

 

Com isto, finalizamos a 1ª etapa do trabalho.

Aproveite e salve seu arquivo.

Etapa 2 – Criar as relações que servirão para o Modelo de Dados no Excel

Agora vamos criar as relações, que servem de substitutas aos diversos PROCV que normalmente faríamos.

Vamos basicamente indicar quais as colunas nas 3 planilhas que são relacionadas, isto é, aquelas que têm dados em comum.

Temos 2 conjuntos:

20130719-7

Para criarmos as relações, clicamos em Dados>Relações:

20130719-8

Na tela que surgir clique em Novo:

20130719-9

 

Vamos relacionar as colunas comuns, conforme a imagem acima. Veja que a Coluna (externo) corresponde à tabela de cadastro, e a Coluna Relacionada (principal) é a tabela que usaremos para montar o relatório.

Depois dê OK e faça a mesma coisa com os campos de Código de Lançamento. Veja:

20130719-10

Depois  dê OK e teremos as duas relações feitas:

20130719-11

Com isto terminamos a 3ª etapa no processo de criação do Modelo de Dados no Excel.

 Etapa 3 – Montando a tabela dinâmica

Uma fez feitos os relacionamentos, agora vamos criar nosso relatório.

Vá em uma das planilhas com tabela, clique dentro da tabela e vá em Inserir>Tabela Dinâmica:

20130719-12

 

Note que apareceu o nome da tabela. Você precisa selecionar o ítem Adicionar estes dados ao Modelo de Dados.

Depois, dê OK e ele criará uma tabela dinâmica:

20130719-13

No menu que aparece do lado direito, clique no ítem chamado TUDO (veja imagem acima). Ele irá mostrar as 3 tabelas que temos no arquivo.

Agora, você pode arrastar os campos de qualquer uma das tabelas para a sua tabela dinâmica, porque os dados foram relacionados. Faça como indicado acima.

Veja que o campo Bancos vem da tabela tbBancos, a Descrição vem de tbLançamentos e Valor vem da tbFluxo. Veja o resultado:

20130719-14

 

Com este método, não precisamos mais de inúmeros PROCV, e no dia em que aparecer ítens novos, eles serão automaticamente adicionados ao relatório.

Licença Creative Commons

OUTROS ARTIGOS DE INTERESSE

Como calcular o VPL – Valor Presente Líquido

COMO CALCULAR O VPL O que é o VPL - Valor Presente Líquido Neste artigo você aprenderá a calcular o VPL. Valor Presente líquido é um dos cálculos mais comuns em finanças. Com ele podemos determinar valores de empresas, saber o valor de retorno de um projeto dentre...

ler mais

Função Valor Futuro (VF) no Excel

O QUE É E COMO UTILIZAR A FUNÇÃO VALOR FUTURO (VF) NO EXCEL A função valor futuro, como o próprio nome diz, calcula um determinado valor monetário em uma data futura, aplicando-se uma taxa de juros qualquer. Para fins de exemplo, imagine que você pretenda aplicar...

ler mais
Fabio Vianna
Siga-Me

Fabio Vianna

Modela planilhas deste 1994 e ministra treinamentos desde 1997.
Especialista em Modelagem Profissional de Planilhas, incluindo Dashboards no Excel.
Fabio Vianna
Siga-Me

Últimos posts por Fabio Vianna (exibir todos)