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

Função SES

FUNÇÃO SES Se você usa a função SE com diversas condições (escrevemos mais sobre isto aqui), temos uma nova função a partir do Excel 2016. Ela é a função SES. Basicamente a função SES condensa dezenas de SE dentro de uma única função, eliminando a necessidade de se...

ler mais

Como Importar o Facebook para o Excel

Como importar o Facebook para o Excel Já pensou em analisar seu feed do Facebook no Excel, quem sabe usando uma tabela dinâmica, filtros, ou simplesmente ter um histórico das besteiras coisas legais que você escreve? Existe um método relativamente simples para...

ler mais

Listas Expansíveis

LISTAS EXPANSÍVEIS ONDE PODEMOS USAR LISTAS EXPANSÍVEIS É muito comum termos listas diversas no Excel e precisarmos aplicá-las em outros locais, como em uma validação de dados, em um formulário e outras situações. Porém, se simplesmente selecionamos um intervalo,...

ler mais
Comentários
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)

Optimization WordPress Plugins & Solutions by W3 EDGE