fbpx

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

[irp posts=”2597″ name=”I – Função INFODADOSTABELADINAMICA”]

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.

[et_bloom_inline optin_id=”optin_10″]

Licença Creative Commons

OUTROS ARTIGOS DE INTERESSE

quatro motivos para voce parar de usar o procv

quatro motivos para voce parar de usar o procv

Já são 40 anos de idade O Excel 1.0 foi criado em 1982. São quase 40 anos de existência. Apesar de ter começado minha carreira usando o Excel 5.0 tenho certeza de que a função PROCV existia nela (pelo menos no 5.0 tinha!). Então estamos hoje, no séc. XXI obcecados em...

ler mais
Como utilizar a Função Filtro

Como utilizar a Função Filtro

Certamente você no mínimo já ouviu falar do recurso de FILTRO do Excel. Nele você consegue mostrar somente linhas de uma planilha que atendem a um ou mais critérios. Além disso você poderia usar também o filtro avançado, cuja principal diferença é a possibilidade de...

ler mais

Função PROCX

Como usar a função PROCX Recentemente a Microsoft incluiu uma nova função em seu arsenal de funções de procura a referência, a função PROCX. Além de servir como um substituto para o famoso PROCV, ela também utiliza a nova funcionalidade, que é apresentar a resposta em...

ler mais
Siga-Me

Nossas Redes Sociais

Facebook
Linkedin
Instagram
Youtube