fbpx

TABELA DE DADOS NO EXCEL

Simule até 2 variáveis simultaneamente

Tabela de Dados no Excel

O que é uma Tabela de Dados no Excel?

OK, você fez umas projeções financeiras (ou definiu um cenário X em qualquer planilha sua).

Mas tem dúvidas acerca de como algumas variáveis possam influenciar as suas projeções.

É para isto que serve o recurso de Tabela de Dados No Excel: ele permite que você simule até 2 variáveis simultaneamente (não confunda com o recurso Tabela – saiba mais sobre ele aqui).

Imagine uma planilha como a abaixo (clique para ver maior):

Temos uma projeção financeira bem simples de 2 produtos; neste exemplo o preço unitário de janeiro será corrigido ao longo dos meses deste ano.

Mas não temos certeza de que estes preços sejam os melhores. Temos algumas opções para definir os preços.

Imagine que tenhamos estas opções:

tabela de dados no Excel

Queremos combinar todas essas opções para analisar como ficaria nossa Receita no ano. Imagine termos 30 linhas de Produtos para fazermos as simulações, depois resumirmos os dados e somente ao final de todo este processo poderíamos definir a melhor combinação.

Com o recurso da Tabela de Dados no Excel, isso se torna algo extremamente simples.

Como criar uma Tabela de Dados no Excel

A primeira coisa a fazer é criar uma matriz com os preços que queremos simular. No exemplo, colocaremos os preços do Produto A na linha e os Preços do Produto B na coluna. Veja:

Precisamos, por exemplo, colocar o preço do Produto A – 1,50, na célula C8, e do produto B na C13. Em seguida, digitarmos na tabela acima a receita total no ano.

Depois, deixar os 1,50 no Produto A, e colocar o valor de R$9,95 na célula C13. Copiar a receita total para a respectiva célula da tabela acima e daí por diante.

Que tal?

Com a Tabela de Dados no Excel ficará muito mais simples:

ETAPA 1: Vamos informar na célula B2 da tabela onde faremos a simulação qual informação deverá ser preenchida nela. Como queremos a receita total do ano, vamos colocar nela a referência desta célula:

Note que NÃO É PARA DIGITAR A RECEITA, e sim VINCULAR ESTA CÉLULA À CÉLULA DA RECEITA (em nosso exemplo ela está na célula O4 de nossa planilha de projeções financeiras.

ETAPA 2: Vamos fazer uma ‘gambiarra’ para resolver uma limitação do recurso. Somente podemos usar variáveis que estejam na mesma planilha da tabela de dados no Excel. Como temos 2 planilhas, uma com a projeção financeira e outra com a tabela, vamos fazer um ajuste.

Na planilha com a tabela de dados vamos inserri as células dos preços para que eles sejam utilizados na planilha de projeções. É como se as premissas de preço dos produtos A e B passassem a ser informadas na planilha de simulação. Veja:

Por enquanto são somente 2 valores digitados nas células.

Agora vamos informar na planilha de projeções que os valores de preço dos dois produtos virão das células acima:

O Produto A:

E o Produto B:

Agora estamos prontos para a próxima etapa.

ETAPA 3: inserir a Tabela de Dados no Excel

Vamos selecionar toda a área de dados que temos:

Em seguida clicamos em DADOS > TESTE DE HIPÓTESES > TABELA DE DADOS:

Irá surgir esta tela:

Temos duas entradas: Célula de entrada da linha e Célula de entrada da coluna.

Na 1ª vamos informar qual célula das projeções deverá ser alterada a partir dos valores indicados na LINHA desta tabela. Como inserimos ali os dados de preço do PRODUTO A, vamos informar neste campo que ele deve simular os valores na célula C15 (lembre de nossa ‘gambiarra’).

Para a coluna, vamos informar a célula C16, já que na coluna temos os preços relativos ao PRODUTO B:

Agora é só dar um OK que a tabela de dados no Excel irá simular essas variáveis e apresentará o seguinte resultado:

A leitura que fazemos é a seguinte: Se o produto A tiver um preço unitário de R$1,45 em janeiro e o Produto B tiver um preço unitário de R$10,50 em janeiro nossa receita total no ano será de R$213.986,67. Seria a mesma coisa se digitássemos esses valores nas células (clique para ver maior):

Note que é bem mais simples criar uma tabela como esta do que digitar os valores individualmente.

Além disto, se alterarmos qualquer variável, nossa tabela de dados no Excel irá ser atualizada. Veja o que acontece se, por exemplo, mudarmos o volume de vendas do Produto A para 1.600 em janeiro:

Apesar da tabela de dados no Excel estar limitada a até 2 variáveis ela ainda é extremamente útil e poderosa quando precisarmos simular cenários em nossas projeções.

 

Licença Creative Commons

OUTROS ARTIGOS DE INTERESSE

Como Criar Bullet Chart no Excel

Como Criar Bullet Chart no Excel

O que é um Bullet Chart O Bullet Chart foi criado por Stephen Few por volta de 2005 com o objetivo de ser a solução PROFISISONAL para as aberrações chamadas de Gráficos de Velocímetro. O Bullet Chart e capaz de apresentar de forma mais clara valores e suas relações...

ler mais

Aprenda a fazer um Gráfico Circular no Excel

GRÁFICO CIRCULAR NO EXCEL Neste artigo você aprenderá a fazer um gráfico circular no Excel, que pode ser inserido em apresentações e em infográficos. Ao final do artigo você poderá baixar a planilha com elke Não recomendamos sua inserção em Dashboard pois ele é menos...

ler mais

Limpeza de Dados no Excel – PARTE 1

LIMPEZA DE DADOS NO EXCEL - IMPORTANDO, EXCLUINDO E PROMOVENDO LINHAS Nesta primeira parte da sequência vamos apresentar a ferramenta de PowerQuery (ou Obter e Transformar dados no Excel 2016). No Excel 2010 e 2013 você precisará instalá-lo. Siga as instruções que...

ler mais
Siga-Me

Nossas Redes Sociais

Facebook
Linkedin
Instagram
Youtube