Calcular vários resultados utilizando uma tabela de dados

Nota: Queremos fornecer-lhe os conteúdos de ajuda mais recentes o mais rapidamente possível e no seu idioma. Esta página foi traduzida automaticamente e pode conter erros gramaticais ou imprecisões. O nosso objetivo é que estes conteúdos lhe sejam úteis. Pode informar-nos se as informações foram úteis no final desta página? Eis o artigo em inglês para referência.

Uma tabela de dados é um intervalo de células no qual pode alterar valores em algumas das células e surgir com diferentes respostas a um problema. Um bom exemplo de uma tabela de dados emprega a função PGTO com diferentes montantes de financiamento e taxas de juro para calcular o montante acessível por um empréstimo de hipoteca de casa. Fazer experiências 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 conhecido como ferramentas de análise de hipóteses. Ao construir e analisar tabelas de dados, está a efetuar uma análise de hipóteses.

A análise de hipóteses é 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 folha de cálculo. Por exemplo, pode utilizar uma tabela de dados para variar a taxa de juros e o período de vigência de um empréstimo – para avaliar os potenciais valores de pagamento mensal.

Nota: Pode efetuar cálculos mais rápidos com tabelas de dados e VBA (Visual Basic for Applications). Para obter mais informações, consulte o artigo tabelas de dados do Excel: cálculo mais rápido com VBA.

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 dadose metas-busca. Os cenários e as tabelas de dados utilizam conjuntos de valores de entrada para calcular possíveis resultados. O objetivo-buscar é distinto de forma diferente, utiliza um único resultado e calcula possíveis valores de entrada que produziram esse resultado.

Como cenários, as tabelas de dados ajudam-no a explorar um conjunto de resultados possíveis. Ao contrário dos cenários, as tabelas de dados mostram-lhe todos os resultados de uma tabela numa folha de cálculo. Ao utilizar tabelas de dados, é mais fácil analisar 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 pretender analisar mais do que duas variáveis, deve utilizar cenários. Embora se limite 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 o número de valores de variável diferentes que quiser. Um cenário pode ter um máximo de 32 valores diferentes, mas pode criar o número de cenários que quiser.

Saiba mais no artigo, introdução à análise de hipóteses.

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

Tabelas de dados com uma variável    

Utilize uma tabela de dados com uma variável se pretender ver como os 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 com uma variável para ver como as diferentes tarifas de juro afetam um pagamento mensal de hipoteca através da função PGTO. Introduzir os valores de variável 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.

Tabela de dados com uma variável

Tabelas de dados com duas variáveis    

Utilize uma tabela de dados com duas variáveis para ver como os 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 com duas variáveis para ver como as combinações diferentes de taxas de juro e as condições de empréstimo irão afetar o pagamento mensal de uma 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.

Tabela de dados com duas variáveis
 

Cálculos de tabelas de dados    

Sempre que uma folha de cálculo for recalculada, as tabelas de dados também serão recalculadas, mesmo que não tenha sido alterada para os 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 com uma variável contém os seus valores de entrada numa única coluna (orientada por colunas) ou numa linha (orientada por linha). Qualquer fórmula numa tabela de dados de variável única tem de referir-se apenas a um célula de entrada.

Siga estes passos:

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

  2. Siga um dos seguintes procedimentos:

    • Se a tabela de dados estiver orientada por colunas (os seus valores de variável 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 com uma variável é orientada por colunas e a fórmula está contida na célula D2.


      Tabela de dados com uma variável
      Se pretender analisar 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 por linha (os seus valores de variável 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 pretender analisar 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 >dedados (no grupo ferramentas de dados ou no grupo previsão de Excel 2016 ). 

  5. Siga um dos seguintes procedimentos:

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

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

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

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

Siga estes passos

  1. Efetue um dos seguintes procedimentos:

    • Se a tabela de dados estiver orientada por 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 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 > de dados (no grupo ferramentas de dados ou no grupo previsão de Excel 2016 ).

  4. Siga um dos seguintes procedimentos:

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

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

Uma tabela de dados com duas variáveis utiliza uma fórmula que contém duas listas de valores de entrada. A fórmula tem de 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 referente à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 tarifas de juros nas células C3, C4 e C5.

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

    Introduza as condições 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 em que pretende os valores calculados (D3: E5).

    Neste caso, selecione o intervalo C2: e5.

  5. No separador dados , no grupo ferramentas de dados ou em grupo previsão (no Excel 2016 ), clique emtabela de dados da análise de hipóteses >(no grupo ferramentas de dados ou grupo previsão do Excel 2016 ). 

  6. No campo célula de entrada da 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 da coluna.
    Escreva B3 na caixa célula de entrada da coluna .

  8. Clique em OK.

Exemplo de uma tabela de dados com duas variáveis

Uma tabela de dados com duas variáveis pode mostrar como as combinações diferentes de taxas de juro e as condições de empréstimo irão afetar um pagamento mensal de hipoteca. Na figura, 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.

Tabela de dados com duas variáveis

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

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

  1. Siga um dos seguintes procedimentos:

    • No Excel 2007, clique no botão do Microsoft Office Imagem do botão do Office , clique em Opções do Excele, em seguida, clique na categoria fórmulas .

    • Em todas as outras versões, clique em Opções de _GT_ de 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 sobre as opções de cálculoe, em seguida, clique em automático exceto tabelas de dados (no grupo cálculo ).

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

Atingir Objetivo

Se souber que o resultado é esperado a partir de uma fórmula, mas não sabe exatamente o valor de entrada que a fórmula precisa para obter esse resultado, utilize a funcionalidade de meta-procurar. Consulte o artigo utilizar o objetivo procurar para encontrar o resultado que pretende ao ajustar um valor de entrada.

Solver do Excel

Pode utilizar o suplemento Solver do Excel 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 variáveis) utilizadas em calcular as 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 utilizando o Solver.

Ao associar números diferentes a uma célula, pode surgir rapidamente com respostas diferentes a um problema. Um excelente exemplo é utilizar a função PGTO com tarifas de juro e períodos de empréstimo diferentes (em meses) para descobrir quanto de um empréstimo pode arcar com 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 montante do empréstimo e os pagamentos mensais na coluna D automaticamente atualizados. Ao utilizar uma taxa de juros de 3,75%, D2 devolve um pagamento mensal de $1042.01 com esta fórmula: = PGTO (C2/12, $B $3 $B $4).

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

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 os diferentes valores de uma variável numa fórmula irão alterar os resultados. Por exemplo, pode alterar a taxa de juros de um pagamento mensal da hipoteca utilizando a função PGTO. Introduza os valores de variável (as tarifas de juros) numa coluna ou linha e os resultados são apresentados numa coluna ou linha próxima.

Neste livro em directo, a célula D2 contém a fórmula de pagamento =PGTO (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 é ligada à taxa de juros 3,75%/12, 360 meses e um empréstimo de $225000 e calcula o pagamento mensal de $1042.01.

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

Neste livro em directo, a célula C3 contém a fórmula de pagamento, =pgto ($B $3/12, $B $2, B4), que utiliza duas células de variáveis, B2 e B3. Na célula C2, a função PGTO é ligada à taxa de juros 3.875%/12, 360 meses e um empréstimo de $225000 e calcula o pagamento mensal de $1058.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.

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.

×