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):

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:

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):

Aparecerá a seguinte tela:

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:

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

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:

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

Na tela que surgir clique em Novo:

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:

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

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:

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:

[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:

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″]

OUTROS ARTIGOS DE INTERESSE