Calcular vários resultados usando uma tabela de dados

Observação: Desejamos fornecer o conteúdo da Ajuda mais recente no seu idioma da forma mais rápida possível. Esta página foi traduzida de forma automatizada e pode conter imprecisões ou erros gramaticais. Nosso objetivo é que este conteúdo seja útil para você. As informações foram úteis? Dê sua opinião no final desta página. Aqui está o artigo em inglês para facilitar a referência.

Uma tabela de dados é um intervalo de células no qual você pode alterar valores em alguns em algumas das células e surgirem com respostas diferentes para um problema. Um bom exemplo de uma tabela de dados utiliza a função PGTO com valores de empréstimo diferentes e taxas de juros para calcular o valor acessível sobre um empréstimo de hipoteca casa. Experiências com valores diferentes para observar a variação correspondente nos resultados é uma tarefa comum na análise dos dados.

No Microsoft Excel, tabelas de dados fazem parte de um conjunto de comandos conhecidos como ferramentas de análise de hipóteses. Quando você construir e analisar tabelas de dados, você está fazendo testes de hipóteses.

Testes de hipóteses é o processo de alteração dos valores em células para saber como essas alterações afetarão o resultado de fórmulas na planilha. Por exemplo, você pode usar uma tabela de dados para variar a taxa de juros e a duração do período de um empréstimo — para avaliar possíveis valores de pagamento mensais.

Observação: Você pode executar cálculos mais rápidos com tabelas de dados e o Visual Basic for Applications (VBA). Para obter mais informações, consulte tabelas de dados do Excel hipóteses: cálculo mais rápido com o VBA.

Tipos de hipóteses   

Há três tipos de ferramentas de análise de hipóteses no Excel: cenários, tabelas de dadose atingir meta. Cenários e tabelas de dados usam conjuntos de valores de entrada para calcula os resultados possíveis. Atingir meta nitidamente diferentes, ele usa um único resultado e calcula os valores de entrada possíveis que produzem esse resultado.

Como cenários, tabelas de dados ajudam você a explorar um conjunto de possíveis resultados. Ao contrário de cenários, tabelas de dados mostram todos os resultados em uma tabela em uma planilha. O uso de tabelas de dados facilita o exame geral de um intervalo de possibilidades. Como você se concentra em apenas uma ou duas variáveis, os resultados são fáceis de ler e compartilhar em formulário de tabela.

Uma tabela de dados não pode acomodar mais de duas variáveis. Se você deseja analisar mais de duas variáveis, prefira o uso de cenários. Apesar de estar 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 de coluna), uma tabela de dados pode incluir quantos valores de variáveis diferentes você desejar. Um cenário pode ter no máximo 32 valores diferentes, mas você pode criar quantos cenários desejar.

Saiba mais no artigo Introdução a hipóteses.

Crie um dos tabelas de dados de variável única ou de duas variáveis, dependendo do número de variáveis e fórmulas que você precisa para testar.

Tabelas de dados de variável única   

Use uma tabela de dados de variável única se você quiser ver como os diferentes valores de uma variável em uma ou mais fórmulas mudará os resultados dessas fórmulas. Por exemplo, você pode usar uma tabela de dados de variável única para ver como diferentes taxas de juros afetam o pagamento mensal de hipoteca, usando a função PGTO. Inserir os valores de variáveis em uma coluna ou linha e os resultados são exibidos em uma coluna adjacente ou linha.

Na ilustração a seguir, 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 só variável

Tabelas de dados com duas variáveis   

Use uma tabela de dados de duas variáveis para ver como os diferentes valores de duas variáveis em uma fórmula irão alterar 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 juros e condições de empréstimo irão afetar um pagamento mensal de uma hipoteca.

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

Tabela de dados com duas variáveis

Cálculos de tabela de dados   

Sempre que uma planilha é recalculada, as tabelas de dados também serão recalcular — mesmo que não houve nenhuma alteração aos dados. Para acelerar o cálculo de uma planilha que contém uma tabela de dados, você pode alterar as opções de cálculo para recalcular automaticamente a planilha, mas não as tabelas de dados. Para saber mais, consulte a seção acelerar o cálculo em uma planilha que contém tabelas de dados.

Uma tabela de dados de variável única conter seus valores de entrada em uma única coluna (orientada por colunas), ou em linhas (orientado por linha). Qualquer fórmula em uma tabela de dados de variável única deve fazer referência à apenas uma célula de entrada.

Siga as seguintes etapas:

  1. Digite a lista de valores que você deseja substituir na célula de entrada — para baixo uma coluna ou linha. Deixe a algumas linhas e colunas vazias em ambos os lados dos valores.

  2. Siga um destes procedimentos:

    • Se a tabela de dados for orientada por colunas (os valores de variáveis estão em uma 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 variável única é orientada por colunas, e a fórmula contida na célula D2.

      Tabela de dados com uma só variável

      Se desejar examinar os efeitos dos vários valores em outras fórmulas, insira as fórmulas adicionais em células à direita da primeira fórmula.

    • Se a tabela de dados for orientada por linhas (os valores de variáveis estão em uma linha), digite a fórmula na célula uma coluna à esquerda do primeiro valor e uma célula abaixo da linha de valores.

      Se desejar examinar os efeitos dos vários valores em outras fórmulas, insira as fórmulas adicionais em células abaixo da primeira fórmula.

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

  4. Na guia dados, clique em hipóteses >  Tabela de dados (no grupo Ferramentas de dados ou grupo de previsão de Excel 2016 ).

  5. Siga um destes procedimentos:

    • Se a tabela de dados for orientada por colunas, digite o referência de célula para a 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 for orientada por linhas, insira a referência de célula para a célula de entrada no campo célula de entrada da linha.

      Observação: Depois de criar a tabela de dados, talvez você queira alterar o formato das células de resultado. Na figura, as células de resultado são formatadas como moeda.

As Fórmulas usadas em uma tabela de dados de variável única devem fazer referência à mesma célula de entrada.

Siga estes procedimentos

  1. Siga um destes procedimentos:

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

    • Se a tabela de dados for orientada por linhas, insira a nova fórmula em uma 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. Na guia dados, clique em Análise de hipóteses > Tabela de dados (no grupo Ferramentas de dados ou grupo de previsão de Excel 2016 ).

  4. Siga um destes procedimentos:

    • Se a tabela de dados for orientada por colunas, insira 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 for orientada por linhas, insira a referência de célula para a célula de entrada na caixa célula de entrada da linha.

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 fazer referência a duas células de entrada diferentes.

Siga as seguintes etapas:

  1. Em uma célula da planilha, insira a fórmula que se refere às duas células de entrada.

    No exemplo a seguir, no qual os valores iniciais de fórmula são inseridos nas células B3, B4 e B5, você digita a fórmula =PMT(B3/12,B4,-B5) na célula C2.

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

    Nesse caso, digite diferentes taxas de juros nas células C3, C4 e C5.

  3. Insira a segunda lista na mesma linha da fórmula — à sua direita.

    Digite 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), tanto a linha quanto a coluna de valores (C3:C5 e D2:E2), e as células nas quais você deseja os valores calculados (D3:E5).

    Nesse caso, selecione o intervalo C2:E5.

  5. Na guia dados, no grupo Ferramentas de dados ou grupo de previsão (em Excel 2016 ), clique em hipóteses >  Tabela de dados (no grupo Ferramentas de dados ou grupo de previsão de Excel 2016 ).

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

  7. No campo da célula de entrada da coluna, insira a referência à célula de entrada para os valores de entrada na coluna.
    Digite 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 diferentes combinações de taxas de juros e termos de empréstimo afetarão um pagamento de hipoteca mensal. Na figura a seguir, a célula C2 contém a fórmula de pagamento, =PMT(B3/12,B4,-B5), que usa duas células de entrada, B3 e B4.

Tabela de dados com duas variáveis

Quando você definir essa opção de cálculo, sem cálculos de tabela de dados ocorrem quando um recálculo é feito na pasta de trabalho inteira. Para recalcular manualmente sua tabela de dados, selecione suas fórmulas e pressione F9.

Siga estas etapas para melhorar o desempenho de cálculo:

  1. Siga um destes procedimentos:

    • No Excel 2007, clique no Botão do Microsoft Office imagem do botão office , clique em Opções do Excel e clique na categoria Fórmulas.

    • Em todas as outras versões, clique em arquivo > Opções > fórmulas.

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

    Dica: Opcionalmente, na guia fórmulas, clique na seta em Opções de cálculo, clique em Automático, exceto tabelas de dados (no grupo cálculo ).

Você pode usar outras ferramentas do Excel para executar análises hipotéticas se tem metas específicas ou conjuntos maiores de dados de variáveis.

Atingir Meta

Se você souber o resultado esperado de uma fórmula, mas não souber precisamente quais valor de entrada a fórmula deve obter esse resultado, use o recurso atingir meta. Consulte o artigo Atingir meta de uso para encontrar o resultado desejado ajustando um valor de entrada.

Solver

Você pode usar o suplemento Solver do Excel para localizar o valor ideal para um conjunto de variáveis de entrada. O Solver trabalha com um grupo de células (chamado variáveis de decisão ou células variáveis simplesmente) que são usados na computação as fórmulas nas células objetivo e de restrição. O Solver ajusta os valores nas células variáveis de decisão para satisfazer os limites nas células de restrição e produzir o resultado desejado para a célula de objetivo. Saiba mais neste artigo: definir e resolver um problema usando o Solver.

Precisa de mais ajuda?

Você pode sempre consultar um especialista na Excel Tech Community, obter suporte na Comunidade de respostas ou sugerir um novo recurso ou melhoria no User Voice do Excel.

Expanda suas habilidades no Office
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

Obrigado por seus comentários!

Agradecemos pelos seus comentários! Parece que pode ser útil conectar você a um de nossos agentes de suporte do Office.

×