Calcular vários resultados utilizando uma tabela de dados

Uma tabela de dados é uma gama de células em que pode alterar valores em algumas das células e encontrar respostas diferentes para um problema. Um bom exemplo de uma tabela de dados emprega a função pmt com diferentes montantes de empréstimo e taxas de juro para calcular o valor acessível de um empréstimo hipotecário. Experimentar com valores diferentes para observar a variação correspondente nos resultados é uma tarefa comum na análise de dados.

No Microsoft Excel, as tabelas de dados fazem parte de um conjunto de comandos conhecidos como ferramentas de análise What-If. Quando constrói e analisa tabelas de dados, está a fazer análises "e se".

E se a análise é o processo de alteração dos valores nas células para ver como essas mudanças afetarão 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 os potenciais montantes mensais de pagamento.

Nota: Pode realizar cálculos mais rápidos com tabelas de dados e Base Visual para Aplicações (VBA). Para obter mais informações, consulte tabelas de dados What-If Excel: Cálculo mais rápido com vba.

Tipos de análise do que se    

Existem três tipos de ferramentas de análise do "e se" no Excel: cenários, tabelasde dados e procura de objetivos. Cenários e tabelas de dados utilizam conjuntos de valores de entrada para calcular possíveis resultados. A procura de objetivos é distintamente diferente, usa um único resultado e calcula possíveis valores de entrada que produziriam esse resultado.

Tal como os 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 facilita a análise de uma série de possibilidades num ápice. 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, em vez disso deve utilizar cenários. Embora esteja limitado 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 variáveis diferentes que desejar. 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.

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

Tabelas de dados de uma variável    

Use uma tabela de dados de uma variável se quiser ver como diferentes valores de uma variável em uma ou mais fórmulas mudarão os resultados dessas fórmulas. Por exemplo, você pode usar uma tabela de dados de uma variável para ver como diferentes taxas de juro afetam um pagamento mensal de hipotecas usando a função PMT. Introduza os valores variáveis numa coluna ou numa linha e os resultados são apresentados numa coluna ou linha adjacente.

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

Tabela de dados com uma variável

Tabelas de dados de duas variáveis    

Use uma tabela de dados de duas variáveis para ver como diferentes valores de duas variáveis numa fórmula mudarão os resultados dessa fórmula. Por exemplo, você pode usar uma tabela de dados de duas variáveis para ver como diferentes combinações de taxas de juro e condições de empréstimo afetarão um pagamento mensal de hipoteca.

Na seguinte ilustração, 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.

Tabela de dados com duas variáveis
 

Cálculos de quadros de dados    

Sempre que uma folha de cálculo recalcula, quaisquer tabelas de dados também recalcularão — mesmo que não tenha havido alteração nos dados. Para acelerar o cálculo de uma folha de cálculo que contenha 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 seus valores de entrada, quer numa única coluna (orientada para colunas), quer através de uma linha (orientada para a linha). Qualquer fórmula numa tabela de dados de uma variável deve referir-se a apenas um célula de entrada.

Siga estes passos:

  1. Digite a lista de valores que pretende substituir na célula de entrada — quer numa coluna quer através de uma linha. Deixe algumas linhas e colunas vazias em ambos os lados dos valores.

  2. Siga um dos seguintes procedimentos:

    • Se a tabela de dados for orientada para colunas (os seus valores variáveis estão numa coluna), digite 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.


      Tabela de dados com uma variável
      Se quiser examinar os efeitos de vários valores noutras fórmulas, insira as fórmulas adicionais nas células à direita da primeira fórmula.

    • Se a tabela de dados for orientada para a linha (os seus valores variáveis estão em linha), digite a fórmula na coluna da célula uma à 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, insira as fórmulas adicionais em células abaixo da primeira fórmula.

  3. Selecione a gama de células que contém as fórmulas e valores que pretende substituir. Na figura acima, esta gama é C2:D5.

  4. No separador Dados, clique em "Análise de E-Se Analysis >Data Table" (no grupo ferramentas de dados ou no grupo de previsão de Excel 2016 ). 

  5. Siga um dos seguintes procedimentos:

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

    • Se a tabela de dados estiver orientada para a linha, introduza a referência celular para a célula de entrada no campo de células de entrada Row.

      Nota: Depois de criar a sua tabela de dados, pode 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 devem referir-se à mesma célula de entrada.

Siga estes passos

  1. Faça qualquer um destes:

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

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

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

  3. No separador Dados, clique em "Análise de E-Se Analysis > Data Table" (no grupo ferramentas de dados ou no grupo de previsão de Excel 2016 ).

  4. Siga um dos seguintes procedimentos:

    • Se a tabela de dados estiver orientada para a coluna, introduza a referência celular para a célula de entrada na caixa de entrada da Coluna.

    • Se a tabela de dados estiver orientada para a linha, introduza a referência celular para a célula de entrada na caixa de entrada Row.

Uma tabela de dados de duas variáveis usa uma fórmula que contém duas listas de valores de entrada. A fórmula deve referir-se 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 , no qual os valores de partida da fórmula são introduzidos nas células B3, B4 e B5, digita a fórmula =PMT (B3/12,B4,-B5) na célula C2.

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

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

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

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

  4. Selecione a gama de células que contém a fórmula (C2), tanto a linha como 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 no grupo de previsão (em Excel 2016 ), clique em What-If Analysis >Data Table (no grupo ferramentas de dados ou grupo de previsão de Excel 2016 ). 

  6. No campo de células de entrada Row, introduza a referência à célula de entrada para os valores de entrada na linha.
    Digite a célula B4 na caixa de entrada row.

  7. No campo de células de entrada coluna, introduza a referência à célula de entrada para os valores de entrada na coluna.
    Tipo B3 na caixa de células 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 diferentes combinações de taxas de juro e condições de empréstimo afetarão um pagamento mensal de hipotecas. 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.

Tabela de dados com duas variáveis

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

Siga estes passos para melhorar o desempenho do cálculo:

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

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

    Sugestão: Opcionalmente, no separador Fórmulas, clique na seta nas Opções de Cálculoe, em seguida, clique em Tabelas automáticas exceto dados (no grupo de cálculo).

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

Atingir Objetivo

Se sabe o resultado que se espera de uma fórmula, mas não sabe exatamente qual o valor de entrada que a fórmula precisa para obter esse resultado, use a funcionalidade Goal-Seek. Consulte o artigo Use Goal Procure encontrar o resultado que deseja ajustando um valor de entrada.

Excel Solver

Pode utilizar o add-in Excel Solver para encontrar o valor ideal para um conjunto de variáveis de entrada. O Solver trabalha com um grupo de células (chamadas variáveis de decisão, ou simplesmente células variáveis) que são usadas na computação das fórmulas nas células objetivas 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: Defina e resolva um problema utilizando o Solver.

Ao ligar números diferentes a uma célula, pode rapidamente encontrar respostas diferentes para um problema. Um grande exemplo é usar a função pmt com diferentes taxas de juro e períodos de empréstimo (em meses) para descobrir quanto de um empréstimo você pode pagar por uma casa ou um carro. Introduzes os teus números numa série de células chamadas tabela de dados.

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

Este intervalo de células, B2:D8, é uma tabela de dados

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

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

Neste livro ao vivo, 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 período diferente (número de períodos de pagamento mensais). No telemóvel D2, a função PMT liga a taxa de juro de 3,75%/12, 360 meses e um empréstimo de $225.000, e calcula um pagamento mensal de $1.042,01.

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

Neste livro ao vivo, 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. No telemóvel C2, a função PMT liga a taxa de juro de 3,875%/12, 360 meses e um empréstimo de $225.000, e calcula um pagamento mensal de $1.058,03.

Precisa de mais ajuda?

Pode sempre perguntar a um especialista na Comunidade Tecnológica do Excel, obter suporte na Comunidade de Respostas ou sugerir uma nova funcionalidade ou melhoria no User Voice do Excel.

Precisa de mais ajuda?

Aumente os seus conhecimentos do Office
Explore as formações
Seja o primeiro a obter novas funcionalidades
Adira ao Office Insider

As informações foram úteis?

Obrigado pelos seus comentários!

Obrigado pelo seu feedback! Parece que poderá ser benéfico reencaminhá-lo para um dos nossos agentes de suporte do Office.

×