Este é o segundo artigo da série de cinco partes sobre Análise de Dados no Excel. Nesta seção, mostrarei como usar a Tabela de dados de duas variáveis no Excel.
Outros artigos desta série:
- Uma tabela de dados variáveis no Excel.
- Gerenciador de cenários no Excel.
- Atingir Meta no Excel.
- Excel Solver.
Assistir ao vídeo - Tabela de dados de duas variáveis no Excel
A tabela de dados de duas variáveis é mais adequada em situações em que você deseja ver como o resultado final muda quando duas das variáveis de entrada mudam simultaneamente (em comparação com a Tabela de dados de uma variável onde apenas uma das variáveis de entrada muda).
Se você deseja analisar dados quando mais de 2 variáveis mudam, o gerenciador de cenários é o caminho a percorrer.
Quando usar a tabela de dados de duas variáveis no Excel
Suponha que você tenha um conjunto de dados conforme mostrado abaixo:
No conjunto de dados acima, temos o valor do empréstimo, a taxa de juros e o número de pagamentos mensais. Com base nessas 3 variáveis de entrada, o pagamento mensal é calculado (está em vermelho porque é uma saída de dinheiro). A seguinte fórmula é usada para calcular o Pagamento Mensal:
= PMT (B2 / 12, B3, B1)
Agora você pode querer fazer uma análise para ver qual deve ser a combinação ideal de valor do empréstimo e número de pagamento mensal para atender às suas necessidades. Por exemplo, você pode querer manter o Pagamento Mensal em $ 500 ou menos e analisar o que a combinação de Montante do Empréstimo e Posse pode lhe dar isso.
Em tal situação, uma tabela de dados de duas variáveis deve ser usada.
Configurando Tabela de Dados de Duas Variáveis no Excel
Aqui estão as etapas para configurar uma tabela de dados de duas variáveis no Excel:
- Em uma coluna, tenha todos os valores diferentes que você deseja testar para Número de pagamentos mensais. Neste exemplo, estamos testando 72, 84, 96… 240. Ao mesmo tempo, tenha os diferentes valores do montante do empréstimo na linha logo acima dos valores da coluna (começando uma célula à direita) conforme mostrado na figura abaixo.
- Digite = B4 na célula D1, que está uma linha acima dos valores da coluna. Esta é uma construção que precisa ser seguida quando você trabalha com duas tabelas de dados variáveis. Além disso, certifique-se de que o valor na célula D1 depende de ambas as variáveis (número de pagamentos mensais e valor do empréstimo). Não funcionará se você inserir manualmente o valor na célula D1.
Nesse caso, a célula D1 se refere à célula B4, que tem um valor calculado por meio de uma fórmula que usa as células B1, B2 e B3.
- Agora os dados estão todos configurados para serem usados para um cálculo de tabela de dados variáveis.
- Selecione os dados (D1: J16). Vá para a guia Dados -> Ferramentas de dados -> Análise de hipóteses -> Tabela de dados
- Na caixa de diálogo Tabela de dados, use as seguintes referências:
- Célula de entrada de linha: $ B $ 1
- Célula de entrada de coluna: $ B $ 3
- Clique OK. Assim que você clicar em OK, ele preencherá instantaneamente todas as células vazias no intervalo de dados selecionado. Ele fornece rapidamente uma visão dos Pagamentos mensais para várias combinações de valor do empréstimo e Número de pagamentos mensais.
Por exemplo, se você deseja identificar as combinações de Valor do empréstimo e Número do pagamento mensal que resultariam em um pagamento mensal de menos de $ 500 por mês, você pode simplesmente usar este método de tabela de 2 dados variáveis.
Observação:
- Depois de calcular os valores usando a tabela de dados, ele não pode ser desfeito usando Control + Z. No entanto, você pode selecionar manualmente todos os valores e excluí-los.
- Você não pode excluir / modificar nenhuma célula em todo o conjunto de valores calculados. Como se trata de uma matriz, você terá que deletar todos os valores.
