Calcular vários resultados usando uma tabela de dados

Uma tabela de dados é um intervalo de células que mostra como a alteração de uma ou duas variáveis nas suas fórmulas (fórmula: uma sequência de valores, referências de células, nomes, funções ou operadores em uma célula que juntos produzem um novo valor. Uma fórmula sempre começa com um sinal de igual (=).) irá afetar os resultados dessas fórmulas. Tabelas de dados fornecem um atalho para o cálculo de vários resultados em uma operação e uma maneira de exibir e comparar os resultados de todas as diferentes variações na sua planilha.

Neste artigo


Visão geral

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 obter informações sobre ferramentas de teste de hipóteses, siga os links na seção Consulte Também.

Noções básicas sobre tabelas de dados

Você pode criar tabelas de dados de variável única ou de duas variáveis, dependendo do número de variáveis e fórmulas que 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, =PGTO(B3/12,B4,-B5), que faz referência à célula de entrada B3.

Tabela de dados de variável única

Uma tabela de dados de variável única

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, =PGTO(B3/12,B4,-B5), que usa duas células de entrada, B3 e B4.

Tabela de dados com duas variáveis

Uma tabela de dados de duas variáveis

Cálculos de 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

Início da página Início da página

Criar uma tabela de dados de variável única

Uma tabela de dados de uma variável tem valores de entrada que estão listados em uma coluna (orientados por coluna) ou em uma linha (orientados por linha). As fórmulas usadas em uma tabela de dados de uma variável devem fazer referência a apenas uma célula de entrada (célula de entrada: a célula na qual cada valor de entrada de uma tabela de dados é substituído. Qualquer célula de uma planilha pode ser a célula de entrada. Embora a célula de entrada não precise fazer parte da tabela de dados, as fórmulas das tabelas de dados devem se referir à 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 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. A ilustração a 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 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 quiser analisar os efeitos de diversos valores em outras fórmulas, digite as fórmulas adicionais nas células abaixo da primeira fórmula.

  1. 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.
  2. Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, clique em Tabela de Dados.
  1. Siga um destes procedimentos:

 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.

Início da página Início da página

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 (os valores de variáveis 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 (os valores de variáveis 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, clique em Teste de Hipóteses e, em seguida, clique em Tabela de Dados.
  1. 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.

Início da página Início da página

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, digite a fórmula =PGTO(B3/12,B4,-B5) na célula C2.

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

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

  1. 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.

  1. 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.

  1. Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, clique em Tabela de Dados.
  1. 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.

  1. 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.

  1. Clique em OK.

Exemplo    Uma tabela de dados de duas variáveis pode mostrar como diferentes combinações de taxas de juros e condições de empréstimo irão afetar o pagamento mensal de uma hipoteca. Na ilustração a seguir, 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


Início da página Início da página

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

  1. Clique no Botão Microsoft Office Imagem do botão, clique em Opções do Excel, e, em seguida, clique na categoria Fórmulas.
  1. 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 essa opção de cálculo, as tabelas de dados são ignoradas quando o restante da pasta de trabalho é recalculada. Para recalcular manualmente a tabela de dados, selecione suas fórmulas e pressione F9.

Início da página Início da página

 
 
Aplica-se a:
Excel 2007