Goal Seek permite encontrar o conjunto de entradas que levam a um resultado específico. Às vezes, você deseja ver muitos resultados diferentes de várias combinações de entradas. Desde que você tenha apenas duas células de entrada para alterar, o recurso Tabela de dados fará uma análise de sensibilidade.
Usando o exemplo de pagamento de empréstimo, digamos que você deseja calcular o preço de vários saldos de principal e de vários termos.
Certifique-se de que a fórmula que você deseja modelar está no canto superior esquerdo de um intervalo. Coloque vários valores para uma variável na coluna esquerda e vários valores para outra variável na parte superior.
Na guia Dados, selecione Análise de variações hipotéticas, Tabela de dados….
Você tem valores na linha superior da tabela de entrada. Você deseja que o Excel conecte esses valores a uma determinada célula de entrada. Especifique essa célula de entrada para Célula de entrada de linha.
Você tem valores ao longo da coluna esquerda. Você quer esses plugados em outra célula de entrada. Especifique essa célula para a Célula de entrada da coluna.
Quando você clica em OK, o Excel repete a fórmula na coluna superior esquerda para todas as combinações da linha superior e coluna esquerda. Na imagem abaixo, você vê 60 pagamentos de empréstimos diferentes, com base em vários dados.
Nota
Eu formatei os resultados da tabela para não ter decimais e usei Home, Formatação Condicional, Escala de Cores para adicionar o sombreado vermelho / amarelo / verde.
Aqui está a grande parte: esta mesa é "ao vivo". Se você alterar as células de entrada ao longo da coluna esquerda ou linha superior, os valores na tabela serão recalculados. Abaixo, os valores à esquerda estão focados na faixa de $ 23K a $ 24K.
Dica
Você pode construir modelos muito mais complexos e ainda usar uma tabela de dados. No meu podcast 2141 "Will Asteroid Bennu Strike the Earth" no YouTube, eu tinha um modelo com 100K NORM.INV e 100K VLOOKUP. Essas 200.000 fórmulas foram enviadas para uma função SUM que as resumiu. Usei uma tabela de dados para executar essas 200.001 fórmulas 100 vezes. A coisa toda recalcula em cerca de 11 segundos.
Agradecimentos a Owen W. Green por sugerir esta técnica de tabelas.
