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:

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.

OUTROS ARTIGOS DE INTERESSE