Dicas de Excel

Excel 2020: Faça 60 análises de variações hipotéticas com uma análise de sensibilidade

A busca de metas 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 prazos.

You have a three-variable model for car payment. You want to do a sensitivity analysis with Principal from $21K to $30K and Term from 36 to 72 months.

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.


The formula calculating payment has to be the top-left corner of the sensitivity analysis. Below that, enter 21000 to 30000 in 1000 unit increments. To the right of the top left cell, enter 36, 48, 54, 60, 66, 72.

Na guia Dados, selecione Análise de variações hipotéticas, Tabela de dados ….

On the Data tab, under What-If Analysis, choose Data Table.

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.

The data table dialog asks for a Row Input Cell and a Column Input cell. Since you have monthly terms across the top row, those should get plugged in to B2. The loan principal along the left column should be plugged in to B1.

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.

The results of the sensitivity analysis range from a low of $341 a month to $902 a month. In this screen shot, a color scale marks the highest numbers in red.

Nota: Eu formatei os resultados da tabela para não ter decimais e usei Início, Formatação condicional, Escala de cores para adicionar o sombreado vermelho / amarelo / verde.

Aqui está a grande parte: esta mesa é “viva”. 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.

Change the numbers in the top row and left column and the data recalculates. In this image, with months from 48 to 63 and prices from 23K to 24K, the monthly car payments range from $418 to $553.

Dica: Você pode construir modelos muito mais complexos e ainda usar uma tabela de dados. No meu podcast 2141 “ O asteróide Bennu atingiu a Terra ” 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.

Foto do título: rawpixel.com / Unsplash

Conteúdo original publicado no MrExcel.

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *