Calcular vários resultados utilizando uma tabela de dados

No Microsoft Excel, as tabelas de dados fazem parte de um conjunto de comandos conhecido como ferramentas de análise What-If. Quando constrói e analisa tabelas de dados, está a fazer uma análise de hipóteses.

A análise de hipóteses é o processo de alterar os valores nas células para ver como essas alterações irão afetar o resultado das fórmulas na folha de cálculo. Por exemplo, pode utilizar uma tabela de dados para variar a taxa de juro e a duração do prazo de um empréstimo para avaliar potenciais montantes de pagamento mensais.

Tipos de análise de hipóteses    

Existem três tipos de ferramentas de análise de hipóteses no Excel: cenários, tabelas de dados e procura de objetivos. Os cenários e as tabelas de dados utilizam conjuntos de valores de entrada para calcular possíveis resultados. A procura de objetivos é distintamente diferente, utiliza um único resultado e calcula os valores de entrada possíveis que produziriam esse resultado.

À semelhança dos cenários, as tabelas de dados ajudam-no a explorar um conjunto de possíveis resultados. Ao contrário dos cenários, as tabelas de dados mostram-lhe todos os resultados numa tabela numa folha de cálculo. A utilização de tabelas de dados torna mais fácil examinar rapidamente um intervalo de possibilidades. Como se concentra apenas numa ou duas variáveis, os resultados são fáceis de ler e partilhar em forma de tabela.

Uma tabela de dados não pode acomodar mais do que duas variáveis. Se quiser analisar mais de duas variáveis, deve utilizar cenários. Embora esteja limitada a apenas uma ou duas variáveis (uma para a célula de entrada de linha e outra para a célula de entrada da coluna), uma tabela de dados pode incluir os valores de variáveis diferentes que quiser. Um cenário pode ter um máximo de 32 valores diferentes, mas pode criar quantos cenários quiser.

Saiba mais no artigo Introdução à Análise de What-If.

Crie tabelas de dados de uma variável ou de duas variáveis, consoante o número de variáveis e fórmulas que precisa de testar.

Tabelas de dados de uma variável    

Utilize uma tabela de dados de uma variável se quiser ver como diferentes valores de uma variável numa ou mais fórmulas irão alterar os resultados dessas fórmulas. Por exemplo, pode utilizar uma tabela de dados de uma variável para ver como as diferentes taxas de juro afetam um pagamento de hipoteca mensal através da função PMT. Introduza os valores das variáveis numa coluna ou linha e os resultados são apresentados numa coluna ou linha adjacente.

Na ilustração seguinte, a célula D2 contém a fórmula de pagamento = PMT(B3/12;B4,-B5), que se refere à célula de entrada B3.

Tabelas de dados de duas variáveis    

Utilize uma tabela de dados de duas variáveis para ver como diferentes valores de duas variáveis numa fórmula irão alterar os resultados dessa fórmula. Por exemplo, pode utilizar uma tabela de dados de duas variáveis para ver como diferentes combinações de taxas de juro e termos de empréstimo afetarão um pagamento mensal de hipoteca.

Na ilustração seguinte, a célula C2 contém a fórmula de pagamento = PMT(B3/12;B4,-B5), que utiliza duas células de entrada, B3 e B4.

 

Cálculos da tabela de dados    

Sempre que uma folha de cálculo é recalculada, as tabelas de dados também serão recalculadas, mesmo que não tenha havido alterações aos dados. Para acelerar o cálculo de uma folha de cálculo que contém uma tabela de dados, pode alterar as opções de Cálculo para recalcular automaticamente a folha de cálculo, mas não as tabelas de dados. Para saber mais, consulte a secção Acelerar o cálculo numa folha de cálculo que contém tabelas de dados.

Uma tabela de dados de uma variável contém os respetivos valores de entrada numa única coluna (orientada para colunas) ou numa linha (orientada para linhas). Qualquer fórmula numa tabela de dados de uma variável tem de fazer referência a apenas uma célula de entrada.

Siga estes passos:

1. Escreva a lista de valores que pretende substituir na célula de entrada, seja numa coluna ou numa linha. Deixe algumas linhas e colunas vazias em ambos os lados dos valores.

2. Execute um dos seguintes passos:

   • Se a tabela de dados for orientada para colunas (os valores das variáveis estão numa coluna), escreva a fórmula na célula uma linha acima e uma célula à direita da coluna de valores. Esta tabela de dados de uma variável é orientada para colunas e a fórmula está contida na célula D2.
     
     
     
     Se quiser examinar os efeitos de vários valores noutras fórmulas, introduza as fórmulas adicionais nas células à direita da primeira fórmula.

   • Se a tabela de dados for orientada para linhas (os valores das variáveis estão numa linha), escreva a fórmula na célula uma coluna à esquerda do primeiro valor e uma célula abaixo da linha de valores.
     
     Se quiser examinar os efeitos de vários valores noutras fórmulas, introduza as fórmulas adicionais nas células abaixo da primeira fórmula.

3. Selecione o intervalo de células que contém as fórmulas e os valores que pretende substituir. Na figura acima, este intervalo é C2:D5.

4. No separador Dados , clique em Análise de Hipóteses > Tabela de Dados (no grupo Ferramentas de Dados ou grupo Previsão de Excel 2016 ).

5. Siga um dos seguintes procedimentos:

   • Se a tabela de dados for orientada para colunas, introduza o referência da célula da célula de entrada no campo Coluna da célula de entrada . Na figura acima, a célula de entrada é B3.

   • Se a tabela de dados for orientada para linhas, introduza a referência de célula para a célula de entrada no campo Célula de entrada de linha.

     Nota: Depois de criar a tabela de dados, poderá querer alterar o formato das células de resultado. Na figura, as células resultantes são formatadas como moeda.

As fórmulas utilizadas numa tabela de dados de uma variável têm de fazer referência à mesma célula de entrada.

Siga estes passos

1. Efetue um destes passos:

   • Se a tabela de dados for orientada para colunas, introduza a nova fórmula numa célula em branco à direita de uma fórmula existente na linha superior da tabela de dados.

   • Se a tabela de dados for orientada para linhas, introduza a nova fórmula numa célula vazia abaixo de uma fórmula existente na primeira coluna da tabela de dados.

2. Selecione o intervalo de células que contém a tabela de dados e a nova fórmula.

3. No separador Dados , clique em Análise de Hipóteses > Tabela de Dados (no grupo Ferramentas de Dados ou grupo Previsão do Excel 2016 ).

4. Efetue um dos seguintes procedimentos:

   • Se a tabela de dados for orientada para colunas, introduza a referência de célula para a célula de entrada na caixa Célula de entrada coluna.

   • Se a tabela de dados for orientada para linhas, introduza a referência de célula para a célula de entrada na caixa Célula de entrada de linha.

Uma tabela de dados de duas variáveis utiliza uma fórmula que contém duas listas de valores de entrada. A fórmula tem de fazer referência a duas células de entrada diferentes.

Siga estes passos:

1. Numa célula na folha de cálculo, introduza a fórmula que se refere às duas células de entrada.

   No exemplo seguinte, em que os valores iniciais da fórmula são introduzidos nas células B3, B4 e B5, escreva a fórmula =PMT(B3/12;B4,-B5) na célula C2.

2. Escreva uma lista de valores de entrada na mesma coluna, abaixo da fórmula.

   Neste caso, escreva as diferentes taxas de juro nas células C3, C4 e C5.

3. Introduza a segunda lista na mesma linha que a fórmula, à direita.

   Escreva os termos de empréstimo (em meses) nas células D2 e E2.

4. Selecione o intervalo de células que contém a fórmula (C2), a linha e a coluna de valores (C3:C5 e D2:E2) e as células nas quais pretende os valores calculados (D3:E5).

   Neste caso, selecione o intervalo C2:E5.

5. No separador Dados , no grupo Ferramentas de Dados ou grupo Previsão (no Excel 2016 ), clique em Análise de Hipóteses > Tabela de Dados (no grupo Ferramentas de Dados ou grupo Previsão do Excel 2016 ).

6. No campo Célula de entrada de linha, introduza a referência à célula de entrada para os valores de entrada na linha.
   Escreva a célula B4 na caixa Célula de entrada da linha.

7. No campo Coluna da célula de entrada , introduza a referência à célula de entrada para os valores de entrada na coluna.
   Escreva B3 na caixa Coluna na célula de entrada .

8. Clique em OK.

Exemplo de uma tabela de dados de duas variáveis

Uma tabela de dados de duas variáveis pode mostrar como diferentes combinações de taxas de juro e termos de empréstimo afetarão um pagamento mensal da hipoteca. Na figura aqui, a célula C2 contém a fórmula de pagamento , =PMT(B3/12;B4,-B5), que utiliza duas células de entrada, B3 e B4.

Quando define esta opção de cálculo, não ocorrem cálculos de tabelas de dados quando é feito um novo cálculo em todo o livro. Para recalcular manualmente a tabela de dados, selecione as respetivas fórmulas e, em seguida, prima F9.

Siga estes passos para melhorar o desempenho dos cálculos:

1. Clique em Opções do > de Ficheiros > Fórmulas.

2. Na secção Opções de cálculo , em Calcular, clique em Automático, exceto em tabelas de dados.

   Sugestão: Opcionalmente, no separador Fórmulas , clique na seta em Opções de Cálculo e, em seguida, clique em Automático Exceto Tabelas de Dados (no grupo Cálculo ).

Pode utilizar outras ferramentas do Excel para efetuar análises de hipóteses se tiver objetivos específicos ou conjuntos maiores de dados variáveis.

Atingir Objetivo

Se souber o resultado esperado de uma fórmula, mas não souber exatamente qual o valor de entrada de que a fórmula precisa para obter esse resultado, utilize a funcionalidade Goal-Seek. Veja o artigo Utilizar Objetivo Procurar para encontrar o resultado pretendido ao ajustar um valor de entrada.

Excel Solver

Pode utilizar o suplemento Solver do Excel para encontrar o valor ideal para um conjunto de variáveis de entrada. O Solver funciona com um grupo de células (denominadas variáveis de decisão ou simplesmente células variáveis) que são utilizadas na computação das fórmulas nas células de objetivo e restrição. O Solver ajusta os valores nas células de variáveis de decisão para satisfazer os limites em células de restrição e produzir o resultado pretendido para a célula de objetivo. Saiba mais neste artigo: Definir e resolver um problema com o Solver.