Calcular vários resultados usando uma tabela de dados

Uma tabela de dados é um intervalo de células no qual você pode alterar valores em algumas das células e se surgem com diferentes respostas para um problema. Um bom exemplo de uma tabela de dados emprega a função PGTO com valores de empréstimo e taxas de juros diferentes para calcular a quantia acessível em um empréstimo de hipoteca de casa. Testar 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 pacote de comandos conhecido como ferramentas de análise de hipóteses. Ao construir e analisar tabelas de dados, você está fazendo um análise hipotética.

O análise de hipóteses é o processo de alteração dos valores nas células para ver 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 de um empréstimo – para avaliar valores de pagamento mensal possíveis.

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

Tipos de análise 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 Use conjuntos de valores de entrada para calcular possíveis resultados. Atingir meta é diferentemente diferente, ele usa um único resultado e calcula possíveis valores de entrada que produzem esse resultado.

Assim como os cenários, as tabelas de dados ajudam você a explorar um conjunto de resultados possíveis. Ao contrário dos cenários, as tabelas de dados mostram todos os resultados de uma tabela em uma planilha. Usar tabelas de dados facilita a análise de uma série de possibilidades rapidamente. Como você se concentra em apenas uma ou duas variáveis, é fácil ler e compartilhar resultados no formato tabular.

Uma tabela de dados não pode acomodar mais de duas variáveis. Se você quiser analisar mais de duas variáveis, use cenários. Embora seja limitado a apenas uma ou duas variáveis (uma para a célula de entrada da linha e outra para a célula de entrada da coluna), uma tabela de dados pode incluir quantos valores variáveis diferentes desejar. Um cenário pode ter um máximo de 32 valores diferentes, mas você pode criar quantos cenários desejar.

Saiba mais no artigo introdução ao analisador de hipóteses.

Crie 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 testar.

Tabelas de dados de variável única    

Use uma tabela de dados de variável única se desejar ver como os valores diferentes de uma variável em uma ou mais fórmulas alterarão os resultados dessas fórmulas. Por exemplo, você pode usar uma tabela de dados de variável única para ver como as diferentes tarifas de juros afetam um pagamento mensal da hipoteca usando a função PGTO. Você insere os valores variáveis em uma coluna ou linha, e os resultados são exibidos em uma coluna ou linha adjacentes.

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

Tabelas de dados de duas variáveis    

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

Data table with two variables
 

Cálculos da tabela de dados    

Sempre que uma planilha é recalculada, todas as tabelas de dados também serão recalculadas, mesmo se não houver nenhuma alteração nos 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, confira a seção acelerar o cálculo em uma planilha que contém tabelas de dados.

Uma tabela de dados de variável única contém os valores de entrada em uma única coluna (orientada por colunas) ou em uma linha (orientada por linha). Qualquer fórmula em uma tabela de dados de variável única deve fazer referência a apenas um célula de entrada.

Siga as seguintes etapas:

  1. Digite a lista de valores que você deseja substituir na célula de entrada, uma coluna ou em uma linha abaixo. Deixe algumas linhas e colunas vazias nos dois lados dos valores.

  2. Siga um destes procedimentos:

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

      Tabela de dados com uma só variável

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

    • Se a tabela de dados for orientada por linha (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 desejar examinar os efeitos de vários valores em outras fórmulas, insira 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. Na figura acima, este intervalo é C2: D5.

  4. Na guia dados , clique em análise de hipóteses >tabela de dados (no grupo ferramentas de dados ou no grupo previsão de Excel 2016 ). 

  5. Siga um destes procedimentos:

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

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

Siga estas etapas

  1. Siga um destes:

    • Se a tabela de dados for orientada por colunas, insira 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 no grupo 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 com 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 faz referência às duas células de entrada.

    No exemplo a seguir, no qual 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 as diferentes taxas de juros nas células C3, C4 e C5.

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

    Digite os termos do 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 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 previsão (em Excel 2016 ), clique em análise de hipóteses >tabela de dados (no grupo ferramentas de dados ou no grupo previsão de Excel 2016 ). 

  6. No campo célula de entrada da 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 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 de duas variáveis pode mostrar como diferentes combinações de taxas de juros e termos de empréstimo afetarão um pagamento mensal de hipoteca. Na figura aqui, 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.

Data table with two variables

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

Siga estas etapas para melhorar o desempenho do 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 Excele, em seguida, clique na categoria fórmulas .

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

Você pode usar algumas outras ferramentas do Excel para executar o análise hipotética se tiver metas específicas ou conjuntos maiores de dados variáveis.

Atingir Meta

Se você souber o resultado de uma fórmula, mas não souber exatamente o valor de entrada que a fórmula precisa para obter esse resultado, use o recurso de meta-Seek. Confira o artigo usar atingir meta para encontrar o resultado desejado ajustando um valor de entrada.

Solver do Excel

Você pode usar o suplemento Solver do Excel para encontrar o valor ideal para um conjunto de variáveis de entrada. O Solver funciona com um grupo de células (chamadas variáveis de decisão ou simplesmente células variáveis) que são usadas em calcular as fórmulas nas células de objetivo e restrição. O Solver ajusta os valores nas células variáveis de decisão para satisfazer aos limites sobre células de restrição e produzir o resultado que você deseja para a célula objetiva. Saiba mais neste artigo: definir e resolver um problema usando o Solver.

Ao conectar números diferentes em uma célula, você pode se aprofundar com diferentes respostas para um problema. Um ótimo exemplo é usar a função PGTO com diferentes taxas de juros e períodos de empréstimo (em meses) para descobrir quanto de um empréstimo você pode pagar por uma casa ou um carro. Você insere seus números em um intervalo de células chamado tabela de dados.

Aqui, a tabela de dados é o intervalo de células B2: D8. Você pode alterar o valor em B4, o valor do empréstimo, e os pagamentos mensais na coluna D são atualizados automaticamente. Usando uma taxa de juros de 3,75%, D2 retorna um pagamento mensal de $1042.01 usando esta fórmula: = PGTO (C2/12, $B $3 $B $4).

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

Você pode usar uma ou duas variáveis, dependendo do número de variáveis e fórmulas que deseja testar.

Use um teste de uma variável para ver como os valores diferentes de uma variável em uma fórmula alterarão os resultados. Por exemplo, você pode alterar a taxa de juros de um pagamento mensal da hipoteca usando a função PGTO. Você insere os valores variáveis (as taxas de juros) em uma coluna ou linha, e os resultados são exibidos em uma coluna ou linha próxima.

Nesta pasta de trabalho ativa, 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 você pode conectar um período diferente (número de períodos de pagamento mensal). Na célula D2, a função PGTO conecta-se à tarifa de juros 3,75%/12, 360 meses e um empréstimo $225000 e calcula um pagamento mensal $1042.01.

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

Nesta pasta de trabalho ativa, a célula C3 contém a fórmula de pagamento, =pgto ($B $3/12 $B $2, B4), que usa duas células variáveis, B2 e B3. Na célula C2, a função PGTO se conecta à taxa de juros 3.875%/12, 360 meses e um empréstimo $225000 e calcula um pagamento mensal $1058.03.

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.

Observação:  Esta página foi traduzida automaticamente e pode apresentar erros gramaticais ou imprecisões. Nosso objetivo é que este conteúdo seja útil para você. Você pode nos dizer se as informações foram úteis? Use o artigo em inglês como referência.​

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.

×