Como fazer uma tabela de dados para análise hipotética no Excel

Logotipo do Microsoft Excel em um fundo verde

O Microsoft Excel oferece alguns recursos para realizar análises em cenários hipotéticos. Uma dessas ferramentas é uma Tabela de Dados. Com ele, você pode experimentar valores diferentes para ver vários resultados.

Esta é uma maneira útil de ver coisas como empréstimos , investimentos, aumentos 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 seria o seu pagamento com cada taxa, tudo em um só lugar.

Crie uma tabela de dados de uma variável

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

Dados de valor futuro para a tabela de dados no Excel

Aqui veremos qual seria o valor futuro do nosso investimento com 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 consecutivos, coloque a fórmula uma célula abaixo e à esquerda.

Para 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.

Fórmula e colocação de valor

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

Células selecionadas para a tabela de dados

Vá para a guia Dados, clique na seta suspensa Análise de variações hipotéticas e escolha “Tabela de dados”.

Tabela de dados na guia Dados

Na caixa Tabela de dados que é aberta, insira a referência da célula para a variável alterada e de acordo com sua configuração. Para nosso exemplo, inserimos a referência de 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.

Referência de célula de entrada de coluna

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

Tabela de dados de uma variável preenchida no Excel

Crie uma tabela de dados de duas variáveis

Você pode querer adicionar duas variáveis ​​à sua tabela de dados para uma análise mais detalhada . Utilizando o nosso mesmo exemplo, não queremos apenas o valor futuro do nosso investimento com taxas de juro diferentes, mas também gostaríamos de ver valores futuros diferentes alterando o número de pagamentos.

Recomendado  Como criar um cronograma de amortização usando modelos do Excel

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 valores e à esquerda da linha de valores conforme mostrado abaixo.

Fórmula e colocação de valor

Agora, selecione todas as células que contêm a fórmula , os valores e os resultados esperados. Para nós, é C2 a F6.

Células selecionadas para a tabela de dados

Vá para a guia Dados e selecione Análise de variações hipotéticas > Tabela de dados. Quando a caixa aparecer, insira as referências das células de entrada da linha e da coluna.

Usando nosso exemplo, inserimos B3 para a célula de entrada da coluna, que é para a nossa taxa de juros, e B4 para a célula de entrada da linha, que é para o nosso número de pagamentos.

Referência de célula de entrada de coluna

Clique em “OK” e você deverá ver todas as saídas de suas variáveis. Aqui, temos o valor futuro do nosso investimento utilizando diferentes taxas de juros e diversos números de pagamentos, todos exibidos em uma tabela bonita e organizada.

Tabela de dados de duas variáveis ​​​​concluída no Excel

Na próxima vez que você quiser analisar dados em sua planilha com números ou quantidades variadas, considere usar uma Tabela de Dados. Não apenas os cálculos são realizados para você, mas o resultado fornece uma tabela estruturada para fácil análise.