Blog

Postado em em 13 de junho de 2019

Tabela de Dados no Excel, como usar?

Nessa publicação vamos mostrar como utilizar a Tabela de Dados no Excel. Poucas pessoas conhecem essa ferramenta que pode poupar muito tempo em análises!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Para baixar a planilha utilizada nessa aula clique aqui!

O que é a Tabela de Dados?

A tabela de dados tem um comportamento similar ao Gerenciador de Cenários, no entanto com essa ferramenta podemos construir uma tabela com os valores variáveis da nossa tabela para que possamos ter uma análise mais completa entre esses dois valores.  Neste caso, vamos utilizar a seguinte tabela.

Valores base
Valores base

Temos que os dois valores em laranja são variáveis, ou seja, eles podem ser modificados. E à medida que esses valores são modificados temos uma alteração no faturamento e lucro bruto dos nossos produtos.

Quando utilizar essa ferramenta da guia Dados?

Vamos utilizar justamente para verificar diferentes combinações dos nossos dados variáveis e com isso poderemos verificar como o lucro bruto e o faturamento se comportam em cada uma dessas combinações. Inicialmente, vamos preencher uma tabela manualmente com esses dados e em seguida vamos utilizar a ferramenta Tabela de Dados para automatizar esse processo.

Lucro e faturamento
Lucro e faturamento

Como utilizar a Tabela de Dados?

Inicialmente, podemos pensar que, para obter os resultados de faturamento e lucro bruto para os preços unitários mostrados, precisaríamos apenas alterar na primeira tabela e copiar os resultados para cada uma das colunas. Isso pode ser feito, no entanto, temos uma forma para automatizar esse processo e torná-lo mais rápido.

O primeiro passo para preenchermos essa tabela menor é selecionar os dados tanto de preço unitário quanto de faturamento e lucro bruto.

Selecionando valores
Selecionando valores

Vale ressaltar que o valor das células F4 e F5 são apenas cópias dos valores das células C5 e C6, pois são nessas células que temos as fórmulas para calcular o faturamento (C4*C2) e o lucro bruto ((C2-C3)*C4). Portanto, o valor da célula F4 é =C5 e o valor da célula F5 é =C6.

Feito isso, basta ir até a guia Dados, em seguida selecionar a opção Teste de Hipóteses e por fim selecionar a opção Tabela de Dados.

Ferramenta do Teste de Hipóteses
Ferramenta do Teste de Hipóteses

Ao clicar em Tabela de Dados uma nova janela será aberta e irá solicitar ao usuário se os dados que estão variando estão em linha ou em coluna. Podemos ver em nossa tabela que os valores que estão variando (preço unitário) estão dispostos em uma linha. Logo, vamos utilizar a opção de Entrada da linha. Vamos selecionar a célula C2 que é a célula que está variando dentro do nosso cálculo.

Configuração da Tabela de Dados
Configuração da Tabela de Dados

Colocada a célula C2 dentro do campo de entrada da linha podemos pressionar OK e então toda a tabela será preenchida com as informações de lucro bruto e faturamento para todos os valores que desejamos, que foram colocados na parte superior da tabela.

Tabela de Dados preenchida
Tabela de Dados preenchida

Desta forma, o usuário não tem que preencher coluna por coluna e dado por dado até completar toda a tabela, essa ferramenta permite automatizar esse processo e facilitá-lo, principalmente quando temos uma quantidade grande de valores para testar.

Neste exemplo, só fizemos a variação de preço unitário, no entanto podemos explorar um pouco mais essa ferramenta e utilizar duas variações simultâneas, ou seja, podemos fazer tanto uma variação de preço unitário quanto de custo unitário.

Para fazermos essa variação dupla só podemos calcular uma variável de cada vez, ou seja, vamos calcular apenas o lucro bruto. Desta forma, vamos obter o lucro bruto para diferentes valores de preço e custo unitário.

Tabela vazia
Tabela vazia

O procedimento é exatamente o mesmo, vamos iniciar selecionando os dados variáveis e a nossa fórmula.

Tabela de Dados selecionada
Tabela de Dados selecionada

Em seguida, vamos até a ferramenta Tabela de Dados.

Ferramenta Tabela de Dados
Ferramenta Tabela de Dados

Na janela que será aberta vamos preencher tanto a entrada de linha quanto a entrada de coluna, pois agora teremos os dois valores variando para encontrarmos os diferentes valores de lucro bruto para essas combinações.

Opções da Tabela de Dados
Opções da Tabela de Dados

Na entrada de linha selecionamos a célula C3, pois em nossa tabela podemos ver que o preço unitário está variando em linha enquanto o custo unitário está variando em coluna, que se encontra na célula C2. Por fim, basta pressionar OK para que todo o cálculo seja feito.

Tabela de Dados preenchida
Tabela de Dados preenchida

É possível observar que toda a tabela foi preenchida com pequenos passos. Agora é possível analisar os diferentes dados das diferentes combinações de preço unitário com custo unitário para encontrar o lucro bruto.

Desta forma, o usuário não precisa ficar testando uma infinidade de valores até encontrar um valor próximo ao que procura. Pode simplesmente utilizar essa ferramenta para facilitar sua análise.

Vale lembrar que o mesmo pode ser feito para o faturamento, basta copiar a tabela e trocar a fórmula dentro da célula azul para a fórmula do faturamento.

Para mais conteúdos sobre Teste de Hipóteses, acesse:
https://www.hashtagtreinamentos.com/gerenciador-de-cenarios-no-excel/
https://www.hashtagtreinamentos.com/planilha-de-previsao-no-excel/



Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?