Modelo de Dados no Excel - você não sabe que precisa dele, mas precisa
você não sabe que precisa dele, mas precisa
(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.
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):
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.
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:
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.