Calcular vários resultados usando uma tabela de dados

Uma tabela de dados é um intervalo de células nas quais você pode alterar valores em algumas das células e obter diferentes respostas para um problema. Um ótimo exemplo é o uso da função PGTO com diferentes valores de empréstimo e taxas de juros para saber o valor do empréstimo que você pode pagar para uma casa ou um carro. A experimentação com valores alteráveis para produzir diferentes resultados faz parte de uma disciplina conhecida como análise de dados.

Tabelas de dados fazem parte de um conjunto de comandos que se chamam de ferramentas de teste de hipóteses. Quando você as usa, está fazendo um teste de hipóteses.

O teste de hipóteses é o processo de alteração dos valores nas células para ver como essas alterações afetarão o resultado das fórmulas na planilha. Por exemplo, você pode usar uma tabela de dados para variar a taxa de juros e as condições que são usados em um empréstimo para determinar possíveis valores de pagamento mensal.

Tipos de teste de hipóteses    Existem três tipos de ferramentas de teste de hipóteses no Excel: cenários, tabelas de dados e Atingir Meta. Cenários e tabelas de dados usam conjuntos de valores de entrada e determinam os possíveis resultados. Atingir Meta funciona de maneira diferente de cenários e tabelas de dados, pois utiliza um resultado e determina os possíveis valores de entrada que produziram 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.

Para saber mais sobre testes de hipóteses, veja Introdução ao teste de hipóteses.

Noções básicas sobre tabelas de dados

Você pode criar tabelas de dados de variável única ou com duas variáveis, dependendo do número de variáveis e fórmulas que você deseja testar.

Tabelas de dados de variável única    Use uma tabela de dados de variável única se quiser ver como diferentes valores de uma variável em uma ou mais fórmulas irão alterar 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 um pagamento mensal de hipoteca usando a função PGTO. Você insere os valores de variáveis em uma coluna ou linha, e os resultados são exibidos em uma coluna ou linha adjacente.

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 da tabela de dados    As tabelas de dados são recalculadas toda vez que uma planilha é recalculada, mesmo que não haja alteração. 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. Consulte a seção Acelerar o cálculo em uma planilha que contém tabelas de dados.

Criar uma tabela de dados de variável única

Uma tabelas de dados de variável única tem valores de entrada que são listados em colunas (orientadas por colunas) ou em linhas (orientadas por linhas). As fórmulas usadas em uma tabela de dados de variável única deve referir-se somente a uma célula de entrada.

  1. Digite a lista de valores que você deseja substituir na célula de entrada uma coluna para baixo ou em uma linha. Deixe 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 das variáveis estiverem em uma coluna), digite a fórmula na célula uma linha acima e uma célula à direita da coluna de valores. A ilustração da tabela de dados de uma variável mostrada na seção Visão geral é orientada por colunas, e a fórmula está contida na célula D2.Se quiser examinar os efeitos de diversos valores em outras fórmulas, digite as fórmulas adicionais nas células à direita da primeira fórmula.

    • Se a tabela de dados for orientada por linhas (os valores de variáveis estiverem 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 quiser examinar os efeitos de diversos valores em outras fórmulas, digite 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 você deseja substituir. Com base na primeira ilustração na seção Visão geral anterior, esse intervalo é C2:D5.

  4. Na guia Dados, no grupo Ferramentas de Dados ou no grupo Previsão (no Excel 2016), clique em Análise de Hipóteses e em Tabela de Dados.

  5. Siga um destes procedimentos:

    • Se a tabela de dados for orientada por colunas, digite a referência de célula para a célula de entrada na caixa Célula de entrada da coluna. Usando o exemplo mostrado na primeira ilustração, a célula de entrada é B3.

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

      Observação : Depois de criar a sua tabela de dados, é possível alterar o formato das células de resultado. Na ilustração, as essas células de resultado são formatadas como moeda.

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

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

  1. Siga um destes procedimentos:

    • Se a tabela de dados for orientada por colunas (seus valores de variável estão em uma coluna), 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 (seus valores de variável estão em uma linha), digite a nova fórmula em uma célula em branco 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, no grupo Ferramentas de Dados ou no grupo Previsão (no Excel 2016), clique em Análise de Hipóteses e em Tabela de Dados.

  4. Siga um destes procedimentos:

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

Criar uma tabela de dados com duas variáveis

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.

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

    No exemplo a seguir, em que os valores iniciais da fórmula são inseridos nas células B3, B4 e B5, você digita a fórmula =PGTO(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 que a fórmula, à 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 no grupo Previsão (no Excel 2016), clique em Análise de Hipóteses e em Tabela de Dados.

  6. Na caixa Célula de entrada da linha, insira a referência à célula de entrada relativa aos valores de entrada na linha.
    Digite célula B4 na caixa Célula de entrada da linha.

  7. Na caixa Célula de entrada da coluna, insira a referência à célula de entrada relativa aos valores de entrada na coluna.
    Digite B3 na caixa Célula de entrada da coluna.

  8. Clique em OK.

Exemplo    Uma tabela de dados com duas variáveis pode mostrar como combinações diferentes de taxas de juros e condições de empréstimo afetarão o pagamento mensal da hipoteca. Na ilustração a seguinte, a célula C2 contém a fórmula de pagamento, =PGTO(B3/12,B4,-B5), que usa duas células de entrada, B3 e B4.

Tabela de dados com duas variáveis

Acelerar o cálculo em uma planilha que contém tabelas de dados

  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 : Como alternativa, na guia Fórmulas, no grupo Cálculo, clique na seta em Opções de Cálculo e, em seguida, clique em Automático, Exceto Tabelas de Dados.

Observação : Ao selecionar a opção de cálculo, as tabelas de dados são ignoradas quando o restante da pasta de trabalho é recalculada. Para recalcular manualmente sua tabela de dados, selecione as fórmulas e pressione F9.

E depois?

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ê sabe o resultado que quer de uma fórmula, mas não tem certeza do valor de entrada de que a fórmula precisa para obter esse resultado, use o recurso Atingir Meta. Consulte o artigo Usar Atingir Meta para encontrar o resultado desejado ajustando um valor de entrada.

Solver

Você pode usar o suplemento Solver do Excel para encontrar o valor ideal com base em um número de variáveis. 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 do 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 que você deseja para a célula do objetivo. Consulte o artigo Definir e resolver um problema usando o Solver.

Início da página

Expanda suas habilidades
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.

×