Excel do Microsoft 365 Excel para a Web Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Mais...Menos
Uma tabela de dados é um intervalo de células em que você pode alterar valores em algumas das células e obter respostas diferentes para um problema. Um bom exemplo de uma tabela de dados emprega a função PMT com valores de empréstimo e taxas de juros diferentes para calcular o valor acessível em um empréstimo hipotecária acessível. Experimentar valores diferentes para observar a variação correspondente nos resultados é uma tarefa comum na análise de dados.
Em Microsoft Excel, as tabelas de dados fazem parte de um pacote de comandos conhecidos como ferramentas What-If de análise. Quando você constrói e analisa tabelas de dados, você está fazendo a análise de what-if. E se a análise for 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 o comprimento do prazo de um empréstimo para avaliar possíveis valores de pagamento mensais. Observação: Você pode realizar cálculos mais rápidos com tabelas de dados e Visual Basic for Applications (VBA). Para obter mais informações, consulte Excel What-If Tabelas de Dados: Cálculo mais rápido com vba.
Tipos de análise de what-if
Há três tipos de ferramentas de análise de what-if em Excel: cenários, tabelasde dados e busca de meta. Cenários e tabelas de dados usam conjuntos de valores de entrada para calcular possíveis resultados. A busca de meta é distintamente diferente, usa um único resultado e calcula os possíveis valores de entrada que produziriam esse resultado.
Como 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 em uma tabela em uma planilha. O uso de tabelas de dados facilita a análise de um intervalo de possibilidades rapidamente. Como você se concentra em apenas uma ou duas variáveis, os resultados são fáceis de ler e compartilhar no formulário 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 ele seja limitado 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 variáveis diferentes você quiser. Um cenário pode ter no máximo 32 valores diferentes, mas você pode criar quantos cenários quiser.
Saiba mais no artigo Introdução What-If Análise.
Crie tabelas de dados de uma variável ou duas variáveis, dependendo do número de variáveis e fórmulas que você precisa testar.
Tabelas de dados de uma variável
Use uma tabela de dados de uma variável se quiser ver como 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 uma variável para ver como taxas de juros diferentes afetam um pagamento de hipoteca mensal usando a função PMT. Insira os valores variáveis em uma coluna ou linha e os resultados serã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.
Tabelas de dados de duas variáveis
Use uma tabela de dados de duas variáveis para ver como 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 combinações diferentes 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, =PMT(B3/12,B4,-B5),que usa duas células de entrada, B3 e B4.
Cálculos da tabela de dados
Sempre que uma planilha é recalculada, quaisquer 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 uma variável contém seus valores de entrada em uma única coluna (orientada para coluna) ou em uma linha (orientada para linha). Qualquer fórmula em uma tabela de dados de uma variável deve se referir a apenas um célula de entrada.
Siga as seguintes etapas:
-
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 cada lado dos valores.
-
Siga um destes procedimentos:
-
Se a tabela de dados for orientada para colunas (seus valores 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 uma variável é orientada para colunas e a fórmula está contida na célula D2.
Se você quiser 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 para linha (seus valores 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 você quiser examinar os efeitos de vários valores em outras fórmulas, insira as fórmulas adicionais nas células abaixo da primeira fórmula.
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.
Na guia Dados, clique em What-If Analysis >Data Table (no grupo Ferramentas de Dados ou no grupo Previsão de Excel 2016 ).
Siga um destes procedimentos:
-
Se a tabela de dados for orientada para colunas, insira o referência de célula para a célula de entrada no campo célula de entrada Column. Na figura acima, a célula de entrada é B3.
-
Se a tabela de dados for orientada para linha, insira a referência de célula para a célula de entrada no campo célula de entrada Linha.
Observação: Depois de criar sua 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 uma variável devem se referir à mesma célula de entrada.
Siga estas etapas
-
Faça um destes:
-
Se a tabela de dados for orientada para 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 para linha, insira a nova fórmula em uma célula vazia abaixo de uma fórmula existente na primeira coluna da tabela de dados.
-
-
Selecione o intervalo de células que contém a tabela de dados e a nova fórmula.
-
Na guia Dados, clique em What-If Analysis> Data Table (no grupo Ferramentas de Dados ou no grupo Previsão de Excel 2016 ).
-
Faça um dos seguintes:
-
Se a tabela de dados for orientada para colunas, insira a referência de célula para a célula de entrada na caixa célula de entrada Column.
-
Se a tabela de dados for orientada para linha, insira a referência de célula para a célula de entrada na caixa de entrada 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 se referir a duas células de entrada diferentes.
Siga as seguintes etapas:
-
Em uma célula na planilha, insira a fórmula que se refere às duas células de entrada.
No exemplo a seguir, no qual os valores de início da fórmula são inseridos nas células B3, B4 e B5, digite a fórmula =PMT(B3/12,B4,-B5) na célula C2.
-
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.
-
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.
-
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.
-
Na guia Dados, no grupo Ferramentas de Dados ou grupo Previsão (em Excel 2016 ), clique em Tabela de Dados de Análise >What-If (no grupo Ferramentas de Dados ou no grupo Previsão de Excel 2016 ).
-
No campo célula de entrada Linha, insira a referência à célula de entrada para os valores de entrada na linha.
Digite a célula B4 na caixa de entrada Linha. -
No campo Célula de entrada Coluna, insira a referência à célula de entrada para os valores de entrada na coluna.
Digite B3 na caixa de entrada Coluna. -
Clique em OK.
Exemplo de uma tabela de dados de duas variáveis
Uma tabela de dados de duas variáveis pode mostrar como combinações diferentes 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, =PMT(B3/12,B4,-B5),que usa duas células de entrada, B3 e B4.
Quando você definir essa opção de cálculo, nenhum cálculo de tabela de dados ocorrerá quando um recálculo for feito em toda a agenda de trabalho. Para recalcular manualmente sua tabela de dados, selecione suas fórmulas e pressione F9.
Siga estas etapas para melhorar o desempenho do cálculo:
-
Clique em Opções > arquivo > Fórmulas.
-
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 eclique em Tabelas de Dados Exceto Automáticas (no grupo Cálculo).
Você pode usar algumas outras ferramentas Excel para executar a análise de what-if se você tiver metas específicas ou conjuntos maiores de dados variáveis.
Atingir Meta
Se você sabe o resultado esperado de uma fórmula, mas não sabe com precisão qual valor de entrada a fórmula precisa para obter esse resultado, use o recurso Goal-Seek de entrada. Consulte o artigo Use Goal Seek para encontrar o resultado que você deseja ajustando um valor de entrada.
Excel Solver
Você pode usar o Excel do Solver para encontrar o valor ideal para um conjunto de variáveis de entrada. O Solver funciona com um grupo de células (chamadas de variáveis de decisão ou simplesmente células variáveis) que são usadas na computação das 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 a uma célula, você pode rapidamente obter respostas diferentes para um problema. Um ótimo exemplo é usar a função PMT com taxas de juros e períodos de empréstimo diferentes (em meses) para descobrir quanto de um empréstimo você pode pagar para uma casa ou um carro. Insira seus números em um intervalo de células chamada tabela de dados.
Aqui, a tabela de dados é o intervalo das 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 US$ 1.042,01 usando esta fórmula: =PMT(C2/12,$B$3,$B$4).
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 valores diferentes de uma variável em uma fórmula alterarão os resultados. Por exemplo, você pode alterar a taxa de juros para um pagamento mensal de hipoteca usando a função PMT. Insira 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 workbook ao vivo, a célula D2 contém a fórmula de pagamento =PMT(C2/12,$B$3,$B$4). A célula B3 é a célula variável, onde você pode conectar um comprimento de termo diferente (número de períodos de pagamento mensais). Na célula D2, a função PMT conecta a taxa de juros 3,75%/12, 360 meses e um empréstimo de US$ 225.000 e calcula um pagamento mensal de US$ 1.042,01.
Use um teste de duas variáveis para ver como 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 número de períodos de pagamento mensais para calcular um pagamento de hipoteca.
Nesta workbook ao vivo, a célula C3 contém a fórmula de pagamento, =PMT($B$3/12,$B$2,B4), que usa duas células variáveis, B2 e B3. Na célula C2, a função PMT conecta a taxa de juros 3,875%/12, 360 meses e um empréstimo de US$ 225.000 e calcula um pagamento mensal de US$ 1.058,03.
Você pode sempre consultar um especialista na Excel Tech Community ou obter suporte na Comunidade de respostas.