Calcular vários resultados usando uma tabela de dados

No Microsoft Excel, as tabelas de dados fazem parte de um conjunto de comandos conhecidos como ferramentas de análise de hipóteses. Quando você constrói e analisa tabelas de dados, você está realizando uma análise de hipóteses.

A análise de hipóteses é o processo de alterar os valores nas células para ver como essas mudanças afetarão o resultado das fórmulas na planilha. Por exemplo, você pode usar uma tabela de dados para variar a taxa de juros e o prazo de um empréstimo — para avaliar os possíveis valores de pagamento mensal.

Tipos de análise de hipóteses    

Existem três tipos de ferramentas de análise de hipóteses no Excelcenários, tabelas de dados e Busca de Objetivo. Cenários e tabelas de dados utilizam conjuntos de valores de entrada para calcular resultados possíveis. A Busca de Objetivo é claramente diferente: ela utiliza um único resultado e calcula os valores de entrada possíveis que produziram esse resultado.

Assim como os cenários, as tabelas de dados ajudam você a explorar um conjunto de resultados possíveis. Ao contrário dos cenários, as tabelas de dados mostram todos os resultados em uma única tabela em uma única planilha. Usar tabelas de dados facilita examinar uma variedade de possibilidades de relance. Como você se concentra em apenas uma ou duas variáveis, os resultados são fáceis de ler e compartilhar em formato de tabela.

Uma tabela de dados não pode comportar mais de duas variáveis. Se você quiser analisar mais de duas variáveis, deve usar cenários. Embora seja limitada a apenas uma ou duas variáveis (uma para a célula de entrada da linha e outra para a célula de entrada da coluna), uma tabela de dados pode incluir quantos valores diferentes de variáveis você desejar. Um cenário pode ter no máximo 32 valores diferentes, mas você pode criar quantos cenários desejar.

Saiba mais no artigo, Introdução à Análise de Hipóteses.

Crie tabelas de dados de uma variável ou de duas variáveis, dependendo do número de variáveis e fórmulas que você precisa testar.

Tabelas de dados de uma variável    

Use uma tabela de dados de uma variável se quiser ver como diferentes valores de uma variável em uma ou mais fórmulas irão alterar os resultados dessas fórmulas. Por exemplo, você pode usar uma tabela de dados de uma variável para ver como diferentes taxas de juros afetam o pagamento mensal de uma hipoteca usando a função PMT. Você insere os valores da variável em uma coluna ou linha, e os resultados são exibidos em uma coluna ou linha adjacente.

Na ilustração a seguir, a célula D2 contém a fórmula de pagamento, =PMT(B3/12,B4,-B5), que faz referência à célula de entrada B3.

Tabelas de dados de duas variáveis    

Use uma tabela de dados de duas variáveis para ver como diferentes valores de duas variáveis em uma fórmula irão alterar os resultados dessa fórmula. Por exemplo, você pode usar uma tabela de dados de duas variáveis para ver como diferentes combinações de taxas de juros e prazos de empréstimo afetarão o pagamento mensal de uma hipoteca.

Na ilustração a seguir, a célula C2 contém a fórmula de pagamento, =PMT(B3/12,B4,-B5), que utiliza duas células de entrada, B3 e B4.

 

Cálculos em tabelas de dados    

Sempre que uma planilha for recalculada, todas as tabelas de dados também serão recalculadas—mesmo que não haja nenhuma alteração nos dados. Para acelerar o cálculo de uma planilha que contém uma tabela de dados, você pode alterar as opções de Cálculo para recalcular automaticamente a planilha, mas não as tabelas de dados. Para saber mais, consulte a seção Acelerar o cálculo em uma planilha que contém tabelas de dados.

Uma tabela de dados de uma variável contém seus valores de entrada em uma única coluna (orientada por coluna) ou ao longo de uma linha (orientada por linha). Qualquer fórmula em uma tabela de dados de uma variável deve se referir a apenas uma célula de entrada célula de entrada.

Siga as seguintes etapas:

1. Digite a lista de valores que deseja substituir na célula de entrada—seja em uma coluna ou em uma linha. Deixe algumas linhas e colunas vazias em ambos os lados dos valores.

2. Siga um destes procedimentos:

   • Se a tabela de dados for orientada por coluna (seus valores de variável estiverem em uma coluna), digite a fórmula na célula uma linha acima e uma coluna à direita da coluna de valores. Esta tabela de dados de uma variável é orientada por coluna, e a fórmula está contida na célula D2.
     
     
     
     Se você quiser examinar os efeitos de vários valores em outras fórmulas, insira as fórmulas adicionais nas células à direita da primeira fórmula.

   • Se a tabela de dados for orientada por linha (seus valores de variável estiverem em uma linha), digite a fórmula na célula uma coluna à esquerda do primeiro valor e uma linha abaixo da linha de valores.
     
     Se você quiser examinar os efeitos de vários valores em outras fórmulas, insira as fórmulas adicionais nas células abaixo da primeira fórmula.

3. Selecione o intervalo de células que contém as fórmulas e os valores que você deseja substituir. Na figura acima, esse intervalo é C2:D5.

4. Na guia Dados, selecione Análise de Hipóteses Tabela de Dados (no grupo Ferramentas de Dados ou no grupo Previsão de Excel 2016).

5. Siga um destes procedimentos:

   • Se a tabela de dados for orientada por coluna, insira o referência de célula da célula de entrada no campo Célula de entrada da coluna. Na figura acima, a célula de entrada é B3.

   • Se a tabela de dados for orientada por linha, insira a referência da célula para a célula de entrada no campo Célula de entrada da linha.

     Observação: Depois de criar sua tabela de dados, você pode querer alterar o formato das células de resultado. Na figura, as células de resultado são formatadas como moeda.

As fórmulas usadas em uma tabela de dados de uma variável devem se referir à mesma célula de entrada.

Siga estas etapas

1. Execute uma destas opções:

   • Se a tabela de dados for orientada por coluna, insira a nova fórmula em uma célula vazia à direita de uma fórmula existente na linha superior da tabela de dados.

   • Se a tabela de dados for orientada por linha, insira a nova fórmula em uma célula vazia abaixo de uma fórmula existente na primeira coluna da tabela de dados.

2. Selecione o intervalo de células que contém a tabela de dados e a nova fórmula.

3. Na guia Dados, selecione Análise de Hipóteses > Tabela de Dados (no grupo Ferramentas de Dados ou no grupo Previsão de Excel 2016).

4. Faça uma das seguintes ações:

   • Se a tabela de dados for orientada por coluna, insira a referência da célula de entrada no campo Célula de entrada da coluna.

   • Se a tabela de dados for orientada por linha, insira a referência da célula para a célula de entrada na caixa Célula de entrada da linha.

Uma tabela de dados de duas variáveis utiliza uma fórmula que contém duas listas de valores de entrada. A fórmula deve se referir a duas células de entrada diferentes.

Siga as seguintes etapas:

1. Em uma célula da planilha, insira a fórmula que faz referência às duas células de entrada.

   No exemplo a seguir — em que os valores iniciais da fórmula são inseridos nas células B3, B4 e B5 — você digita a fórmula =PMT(B3/12,B4,-B5) na célula C2.

2. Digite uma lista de valores de entrada na mesma coluna, abaixo da fórmula.

   Neste caso, digite as diferentes taxas de juros nas células C3, C4 e C5.

3. Insira a segunda lista na mesma linha da fórmula—à sua direita.

   Digite os prazos do empréstimo (em meses) nas células D2 e E2.

4. Selecione o intervalo de células que contém a fórmula (C2), tanto a linha quanto a coluna de valores (C3:C5 e D2:E2), e as células nas quais você deseja os valores calculados (D3:E5).

   Neste caso, selecione o intervalo C2:E5.

5. Na guia Dados, no grupo Ferramentas de Dados ou Previsão (em Excel 2016), selecione Análise de Hipóteses >Tabela de Dados (no grupoFerramentas de Dados ou Previsão de Excel 2016).

6. No campo Célula de entrada da linha, insira a referência da célula de entrada para os valores na linha.
   Digite a célula B4 no campo Célula de entrada da linha.

7. No campo Célula de entrada da coluna, insira a referência da célula de entrada para os valores na coluna.
   Digite B3 no campo Célula de entrada da coluna.

8. Selecione OK.

Exemplo de uma tabela de dados de duas variáveis

Uma tabela de dados de duas variáveis pode mostrar como diferentes combinações de taxas de juros e prazos de empréstimo afetarão o pagamento mensal de uma hipoteca. Na figura aqui, a célula C2 contém a fórmula de pagamento, =PMT(B3/12,B4,-B5), que usa duas células de entrada, B3 e B4.

Quando você define esta opção de cálculo, nenhum cálculo de tabela de dados ocorre quando é feito um recálculo em toda a pasta de trabalho. Para recalcular manualmente sua tabela de dados, selecione suas fórmulas e depois pressione F9.

Siga estas etapas para melhorar o desempenho do cálculo:

1. Selecione Arquivo > Opções > Fórmulas.

2. Na seção Opções de cálculo, selecione Automático.

   Dica: Opcionalmente, na guia Fórmulas, selecione a seta em Opções de Cálculo, depois selecione Automático.

Você pode usar algumas outras ferramentas do Excel para realizar análises de hipóteses se tiver objetivos específicos ou conjuntos maiores de dados variáveis.

Atingir Meta

Se você souber o resultado esperado de uma fórmula, mas não souber exatamente qual valor de entrada a fórmula precisa para obter esse resultado, use o recurso Atingir Meta. Consulte o artigo Usar o Atingir Meta para encontrar o resultado desejado ajustando um valor de entrada.

Solucionador do Excel

Você pode usar o suplemento Solver do Excel para encontrar o valor ideal para um conjunto de variáveis de entrada. O Solver trabalha com um grupo de células (chamadas de variáveis de decisão, ou simplesmente células variáveis) que são usadas no cálculo das fórmulas nas células de objetivo e de restrição. O Solver ajusta os valores nas células variáveis de decisão para satisfazer aos limites sobre células de restrição e produzir o resultado que você deseja para a célula objetiva. Saiba mais neste artigo: Definir e resolver um problema usando o Solver.