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 What-If de análise. Ao construir e analisar tabelas de dados, está a fazer análises de que se deve.

A análise de que se deve fazer é o processo de alteração dos valores nas células para ver como essas alterações irão afetar o resultado das fórmulas na mesma. Por exemplo, pode utilizar uma tabela de dados para variar a taxa de juros e o comprimento de prazo de um empréstimo para avaliar potenciais montantes de pagamentos mensais.

Tipos de análise de e-se    

Existem três tipos de ferramentas de análise de hipóteses no Excel: cenários, tabelasde 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 é diferente, utiliza um único resultado e calcula possíveis valores de entrada que produzam esse resultado.

Tal como em 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 só, numa única ficha. Utilizar tabelas de dados torna mais fácil examinar um intervalo de possibilidades de relance. 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 do que duas variáveis, deve utilizar cenários. Apesar de estar limitada a apenas uma ou duas variáveis (uma para a célula de entrada de linha e uma para a célula de entrada de coluna), uma tabela de dados pode incluir quantos valores de variáveis diferentes 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 What-If Dados.

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

Tabelas de dados com uma variável    

Utilize uma tabela de dados de uma variável se quiser ver como os valores diferentes 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 o pagamento de um empréstimo mensal utilizando a função PGTO. Introduza os valores de variáveis numa coluna ou linha e o resultado é apresentado numa coluna ou linha adjacente.

Na seguinte ilustração, 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 os diferentes valores de duas variáveis numa fórmula irão alterar os resultados da fórmula. Por exemplo, pode utilizar uma tabela de dados de duas variáveis para ver como as diferentes combinações de taxas de juro e termos de empréstimo afetam o pagamento mensal da hipoteca.

Na seguinte ilustração, 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 ficha é recalculada, todas as tabelas de dados também serão recalculadas, mesmo que não tenha sido efetivamente efetivamente alterações aos dados. Para acelerar o cálculo de uma ficha de cálculo que contém uma tabela de dados, pode alterar as opções de Cálculo para recalcular automaticamente a mesma, mas não as tabelas de dados. Para saber mais, consulte a secção Acelerar cálculo numa ficha de cálculo que contenha tabelas de dados.

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

Siga estes passos:

1. Escreva a lista de valores que pretende substituir na célula de entrada: uma coluna abaixo ou uma linha para a frente. Deixe algumas linhas e colunas vazias em ambos os lados dos valores.

2. Siga um dos seguintes passos:

   • Se a tabela de dados estiver orientada para colunas (os valores de variáveis estiverem numa coluna), escreva a fórmula na célula uma linha acima e numa célula à direita da coluna de valores. Esta tabela de dados com 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 estiver orientada para linhas (os valores de variáveis estiverem numa linha), escreva a fórmula na célula uma coluna à esquerda do primeiro valor e numa 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, clique em Análise de E->Tabela de Dados (no grupo Ferramentas de Dados ou grupo Previsão do Excel 2016 ).  

5. Siga um dos seguintes passos:

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

   • Se a tabela de dados estiver orientada por linhas, introduza a referência da célula de entrada no campo de célula de entrada linha.

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

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

Siga estes passos

1. Eis um dos dois:

   • Se a tabela de dados estiver 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 estiver orientada por linhas, introduza a nova fórmula numa célula vazia por baixo 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 E-> Tabela de Dados (no grupo Ferramentas de Dados ou grupo Previsão de Excel 2016 ).

4. Eis um dos seguintes procedimentos:

   • Se a tabela de dados estiver orientada para colunas, introduza a referência da célula de entrada na caixa Célula de introdução de coluna.

   • Se a tabela de dados estiver orientada por linhas, introduza a referência da 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 mesma, introduza a fórmula que se refere às duas células de entrada.

   No exemplo seguinte, no qual os valores iniciais da fórmula são introduzidos nas células B3, B4 e B5, escreve 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 da fórmula, à direita.

   Escreva os termos do 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 Tabela de Dados da Análise de Que Se >(no grupo Ferramentas de Dados ou grupo Previsão do Excel 2016 ).  

6. No campo célula de entrada 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 Célula de entrada da coluna, 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. Clique em OK.

Exemplo de uma tabela de dados de duas variáveis

Uma tabela de dados de duas variáveis pode mostrar como as diferentes combinações de taxas de juro e termos de empréstimo afetam o pagamento mensal do empréstimo. 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.

Quando define esta opção de cálculo, não ocorrem cálculos da tabela de dados quando um novo cálculo é feito 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 do cálculo:

1. Clique em Opções > Ficheiro >Fórmulas.

2. Na secção Opções de cálculo, em Calcular, clique em Automático, exceto 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 de Excel para efetuar uma análise de e-se se tiver objetivos específicos ou conjuntos maiores de dados variáveis.

Atingir Objetivo

Se sabe o resultado esperado de uma fórmula, mas não sabe exatamente qual o valor de entrada de que a fórmula precisa para obter esse resultado, utilize a funcionalidade Goal-Seek fórmula. Consulte o artigo Utilizar a procura de objetivo para encontrar o resultado que pretende ajustando um valor de entrada.

Excel Solver

Pode utilizar o Excel Solver para encontrar o valor ideal para um conjunto de variáveis de entrada. O Solver trabalha com um grupo de células (denominadas variáveis de decisão ou simplesmente células de variáveis) utilizadas na computação das 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 Solver.