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 What-If ferramentas de análise. Ao construir e analisar tabelas de dados, você está fazendo a análise de e se.

A análise de e-se é o processo de alteração dos valores nas células para ver como essas alterações 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 comprimento do prazo de um empréstimo, para avaliar possíveis valores de pagamento mensal.

Tipos de análise what-if    

Há três tipos de ferramentas de análise what-if no Excel: cenários, tabelas de dados e busca de metas. Cenários e tabelas de dados usam conjuntos de valores de entrada para calcular possíveis resultados. A busca por metas é distintamente diferente, usa um único resultado e calcula possíveis valores de entrada que produziriam esse resultado.

Como cenários, tabelas de dados ajudam você a explorar um conjunto de possíveis resultados. Ao contrário dos cenários, as tabelas de dados mostram todos os resultados em uma tabela em uma planilha. O uso de tabelas de dados facilita a análise de uma série de possibilidades rapidamente. Como você se concentra em apenas uma ou duas variáveis, os resultados são fáceis de ler e compartilhar no formulário tabular.

Uma tabela de dados não pode acomodar mais de duas variáveis. Se você quiser analisar mais de duas variáveis, em vez disso, use cenários. Embora seja limitado 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 tantos valores variáveis diferentes quanto você quiser. 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 What-If.

Crie tabelas de dados de uma variável ou 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 valores diferentes de uma variável em uma ou mais fórmulas alterarão 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 um pagamento mensal de hipoteca usando a função PMT. Insira os valores de 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 se refere à célula de entrada B3.

Tabelas de dados de duas variáveis    

Use uma tabela de dados de duas variáveis para ver como valores diferentes de duas variáveis em uma fórmula alterarão 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 condições de empréstimo afetarão um pagamento mensal de hipoteca.

Na ilustração a seguir, 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.

 

Cálculos da tabela de dados    

Sempre que uma planilha é recalculada, todas as tabelas de dados também serão recalculadas, mesmo que não tenha havido 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, confira 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 a colunas) ou em uma linha (orientada a linhas). Qualquer fórmula em uma tabela de dados de uma variável deve se referir a apenas uma célula de entrada.

Siga as seguintes etapas:

1. Digite a lista de valores que você 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 para coluna (seus valores variáveis estão em uma coluna), digite a fórmula na célula uma linha acima e uma célula à direita da coluna de valores. Essa tabela de dados de uma variável é orientada a colunas 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 para linhas (seus valores variáveis estão em uma linha), digite a fórmula na célula uma coluna à esquerda do primeiro valor e uma célula 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 , clique em Análise de E-Se > Tabela de Dados (no grupo Ferramentas de Dados ou grupo Previsão de Excel 2016 ).

5. Siga um destes procedimentos:

   • Se a tabela de dados estiver orientada à coluna, insira o referência de célula para a célula de entrada no campo célula de entrada Coluna . Na figura acima, a célula de entrada é B3.

   • Se a tabela de dados for orientada a linhas, insira a referência de célula para a célula de entrada no campo célula de entrada Linha .

     Observação: Depois de criar sua tabela de dados, talvez você queira 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. Faça um destes:

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

   • Se a tabela de dados for orientada a linhas, 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 , clique em Análise de E-Se > Tabela de Dados (no grupo Ferramentas de Dados ou grupo Previsão de Excel 2016 ).

4. Faça qualquer um dos seguintes procedimentos:

   • Se a tabela de dados for orientada à coluna, insira a referência de célula para a célula de entrada na caixa de entrada Coluna .

   • Se a tabela de dados for orientada a linhas, insira a referência de célula para a célula de entrada na caixa de entrada Linha .

Uma tabela de dados de duas variáveis usa 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 na planilha, insira a fórmula que se refere às duas células de entrada.

   No exemplo a seguir, no qual 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.

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

3. Insira a segunda lista na mesma linha que a fórmula à direita.

   Digite os termos 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), a linha e a coluna de valores (C3:C5 e D2:E2) e as células nas quais você deseja os valores calculados (D3:E5).

   Nesse caso, selecione o intervalo C2:E5.

5. Na guia Dados, no grupo Ferramentas de Dados ou no grupo Previsão (em Excel 2016 ), clique em Tabela de Dados >análise de e-se (no grupo Ferramentas de Dados ou no grupo Previsão de Excel 2016 ). 

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

7. No campo célula de entrada Coluna , insira a referência à célula de entrada para os valores de entrada na coluna.
   Digite B3 na caixa de entrada Coluna .

8. Clique em 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 termos de empréstimo afetarão um pagamento mensal de 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 essa opção de cálculo, nenhum cálculo de tabela de dados ocorre quando um recalculação é feito em toda a pasta de trabalho. Para recalcular manualmente sua tabela de dados, selecione suas fórmulas e pressione F9.

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

1. Clique emOpções > de Arquivo > Fórmulas.

2. Na seção Opções de cálculo , em Calcular, clique em Automático, exceto para tabelas de dados.

   Dica: Opcionalmente, na guia Fórmulas , clique na seta em Opções de Cálculo e clique em Tabelas automáticas de dados exceto (no grupo Cálculo ).

Você pode usar algumas outras ferramentas do Excel para executar a análise de e se você tiver metas específicas ou conjuntos maiores de dados variáveis.

Atingir Meta

Se você sabe o resultado esperado de uma fórmula, mas não sabe exatamente qual valor de entrada a fórmula precisa para obter esse resultado, use o recurso Goal-Seek. Consulte o artigo Usar Busca de Metas para localizar o resultado desejado ajustando um valor de entrada.

Solucionador do Excel

Você pode usar o suplemento do Excel Solver para encontrar o valor ideal para um conjunto de variáveis de entrada. O solucionador funciona com um grupo de células (chamadas de variáveis de decisão ou simplesmente células variáveis) que são usadas na computação das fórmulas nas células objetivas 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.