Calcular vários resultados utilizando uma tabela de dados

Descrição Geral

No Microsoft Excel, as tabelas de dados fazem parte de um conjunto de comandos conhecido como ferramentas de análise de hipóteses. 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 alteração dos valores em 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 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. Utilizar tabelas de dados facilita a verificação rápida de 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 conter mais do que duas variáveis. Se quiser analisar mais do que duas variáveis, deve utilizar em alternativa os 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 os cenários que quiser.

Saiba mais no artigo Introdução à Análise de Hipóteses.

Informações básicas da tabela de dados

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 PGTO. 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, =PGTO(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, =PGTO(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 deCá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.

Criar uma tabela de dados de uma variável

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 seus 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 seus 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 valores que pretende substituir. Na figura acima, este intervalo é C2:D5.

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

5. Siga 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 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 sua tabela de dados, poderá querer alterar o formato das células de resultado. Na figura, as células de resultado são formatadas como moeda.

Adicionar uma fórmula a uma tabela de dados de uma variável

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, selecioneTabela de Dadosde Análise> de Hipóteses (no grupo Ferramentas de Dados ou Grupo de previsão de Excel 2016).

4. Efetue os 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 de 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.

Criar uma tabela de dados de duas variáveis

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 =PGTO(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), selecione Tabela de Dados de Análise > de Hipóteses (no grupo Ferramentas de Dados ou Grupo de previsão de 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 célula B4 na caixa da 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 Célula de entrada da coluna.
8. Selecione 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, =PGTO(B3/12;B4,-B5), que utiliza duas células de entrada, B3 e B4.

Acelerar o cálculo numa folha de cálculo que contém tabelas de dados

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 sua tabela de dados, selecione as respetivas fórmulas e, em seguida, prima F9.

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

1. SelecioneOpções>de Ficheiro>Fórmulas.

2. Na secção Opções de cálculo, selecione Automático.

   Sugestão

   Opcionalmente, no separador Fórmulas, selecione a seta em Opções de Cálculo e, em seguida, selecione Automático.

O que se segue?

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 Procurar Objetivo. Consulte o artigo Utilizar Procurar Objetivo para encontrar o resultado pretendido ao ajustar um valor de entrada.

Solucionador do Excel

Pode utilizar o suplemento Solucionador do Excel para encontrar o valor ideal para um conjunto de variáveis de entrada. O Solucionador funciona com um grupo de células, denominadas variáveis de decisão ou simplesmente células de variáveis e que são utilizadas no cálculo de fórmulas nas células de objetivo e de 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 Solucionador.

Ao ligar números diferentes a uma célula, pode rapidamente obter respostas diferentes para um problema. Um grande exemplo é utilizar a função PGTO com taxas de juro e períodos de empréstimo diferentes (em meses) para descobrir quanto de um empréstimo pode pagar por uma casa ou um carro. Introduza os seus números num intervalo de células denominado tabela de dados.

Aqui, a tabela de dados é o intervalo de células B2:D8. Pode alterar o valor em B4, o valor do empréstimo e os pagamentos mensais na coluna D são atualizados automaticamente. Utilizando uma taxa de juro de 3,75%, D2 devolve um pagamento mensal de $1042,01 utilizando esta fórmula: =PGTO(C2/12,$B$3,$B$4).

Pode utilizar uma ou duas variáveis, consoante o número de variáveis e fórmulas que pretende testar.

Utilize um teste de uma variável para ver como diferentes valores de uma variável numa fórmula irão alterar os resultados. Por exemplo, pode alterar a taxa de juro de um pagamento mensal de hipoteca utilizando a função PGTO. Introduza os valores das variáveis (as taxas de juro) numa coluna ou linha e os resultados são apresentados numa coluna ou linha próxima.

Neste livro dinâmico, a célula D2 contém a fórmula de pagamento =PMT(C2/12,$B$3,$B$4). A célula B3 é a célula variável, onde pode ligar um comprimento de termo diferente (número de períodos de pagamento mensais). Na célula D2, a função PGTO liga a taxa de juro 3,75%/12, 360 meses e um empréstimo de $225 000 e calcula um pagamento mensal de $1042,01.

Utilize um teste de duas variáveis para ver como diferentes valores de duas variáveis numa fórmula irão alterar os resultados. Por exemplo, pode testar diferentes combinações de taxas de juro e o número de períodos de pagamento mensais para calcular um pagamento de hipoteca.

Neste livro dinâmico, a célula C3 contém a fórmula de pagamento = PMT($B$3/12,$B$2,B4), que utiliza duas células variáveis, B2 e B3. Na célula C2, a função PGTO liga a taxa de juro 3,875%/12, 360 meses e um empréstimo de $225 000 e calcula um pagamento mensal de $1058,03.