Como fazer uma tabela de dados para análise “e se” no Excel

O Microsoft Excel oferece vários recursos para analisar cenários “e se”. Uma dessas ferramentas é a folha de dados. Com ele, você pode experimentar diferentes valores para ver resultados diferentes.

Essa é uma maneira útil de ver coisas como empréstimos, investimentos, marcações de produtos e assim por diante, com diferentes variáveis. Por exemplo, você pode inserir várias taxas de juros diferentes para um empréstimo para ver qual será seu pagamento em cada taxa, tudo em um só lugar.

Criar uma tabela de dados com uma variável

As tabelas de dados no Excel podem incluir uma ou duas entradas diferentes. Então vamos começar com uma tabela de dados com uma variável. Calculamos o valor futuro de nosso investimento usando a função VF e constantes para a taxa de juros anual, o número de pagamentos e o valor do pagamento.

Aqui veremos qual será o valor futuro de nossos investimentos em diferentes taxas de juros.

Você pode inserir seus valores em uma coluna ou linha, mas o posicionamento afeta onde você deve colocar sua fórmula. Se seus valores estiverem em uma coluna, coloque a fórmula uma célula acima e à direita. Se seus valores estiverem em uma linha, coloque a fórmula uma célula para baixo e para a esquerda.

Em nosso exemplo, estamos usando uma tabela de dados orientada a colunas. Como você pode ver na captura de tela abaixo, movemos nossa fórmula para a coluna D e listamos nossas taxas de juros na coluna C.

Selecione as células que contêm a fórmula, os valores e os resultados esperados. Para nós é de C2 a D6.

Clique na guia Dados, clique na seta suspensa Análise de variações hipotéticas e selecione Tabela de dados.

Na janela Data Table que se abre, insira a referência da célula para a variável a ser modificada e de acordo com suas configurações. Em nosso exemplo, estamos inserindo uma referência à célula B3 para a alteração da taxa de juros no campo Célula de entrada da coluna. Novamente, estamos usando uma tabela de dados baseada em colunas.

Clique em OK e você verá os resultados. Agora temos o valor futuro de nosso investimento com cada taxa de juros inserida.

Crie uma tabela de dados com duas variáveis

Você pode adicionar duas variáveis ​​à tabela de dados para uma análise mais detalhada. Usando o mesmo exemplo, não apenas queremos obter o valor futuro de nossos investimentos com taxas de juros diferentes, mas também queremos ver valores futuros diferentes alterando o número de pagamentos.

Como você está usando duas variáveis, você usará uma tabela de dados orientada a colunas e linhas. Portanto, coloque a fórmula diretamente acima da coluna de valor e à esquerda da linha de valor, conforme mostrado abaixo.

Agora selecione todas as células contendo fórmula, valores e resultados esperados. Para nós é de C2 a F6.

Clique na guia Dados e selecione Análise de variações hipotéticas > Tabela de dados. Quando a janela aparecer, insira as referências de célula de entrada de linha e coluna.

Usando nosso exemplo, inserimos B3 para a célula de entrada de coluna que é para nossa taxa de juros e B4 para a célula de entrada de linha que é para nosso valor de pagamento.

Clique em OK e você deverá ver toda a saída de suas variáveis. Aqui temos o valor futuro do nosso investimento usando diferentes taxas de juros e diferentes números de pagamentos, todos exibidos em uma tabela bonita e organizada.

Na próxima vez que você quiser analisar dados em uma planilha com números ou valores diferentes, considere usar uma planilha de dados. Não apenas os cálculos são feitos para você, mas o resultado fornece uma tabela estruturada para facilitar a análise.