Tabela de dados no Excel: Crie tabelas de dados de uma e duas variáveis

Quando você tem uma fórmula complexa dependente de várias variáveis e deseja entender como alterar essas entradas impacta os resultados de forma eficiente, uma tabela de dados de análise de hipóteses no Excel é uma ferramenta poderosa. Ela permite que você veja todos os resultados possíveis com um rápido olhar. Aqui está um guia passo a passo sobre como criar uma tabela de dados no Excel. Além disso, discutiremos alguns pontos-chave para usar tabelas de dados de forma eficaz e demonstraremos outras operações, como excluir, editar e recalcular tabelas de dados.

O que é uma tabela de dados no Excel?

Criar tabela de dados de uma variável

• Tabela de dados orientada por coluna
• Tabela de dados orientada por linha
• Múltiplas fórmulas para tabela de dados de uma variável

Criar tabela de dados de duas variáveis

Pontos-chave ao usar tabelas de dados

Outras operações ao usar tabelas de dados

• Excluir uma tabela de dados
• Editar resultado da tabela de dados
• Recalcular tabela de dados manualmente

O que é uma tabela de dados no Excel?

No Excel, uma tabela de dados é uma das ferramentas de Análise de Hipóteses que permite experimentar diferentes valores de entrada para fórmulas e observar as mudanças na saída da fórmula. Essa ferramenta é incrivelmente útil para explorar vários cenários e realizar análises de sensibilidade, especialmente quando uma fórmula depende de múltiplas variáveis.

Existem dois tipos de tabelas de dados no Excel:

Tabela de Dados de Uma Variável: Isso permite que você analise como diferentes valores de uma variável impactam uma fórmula. Você pode configurar uma tabela de dados de uma variável em formato orientado por linha ou coluna, dependendo se deseja variar sua entrada ao longo de uma linha ou descendo uma coluna.

Tabela de Dados de Duas Variáveis: Este tipo permite que você veja o impacto de alterar duas variáveis diferentes no resultado de uma fórmula. Em uma tabela de dados de duas variáveis, você varia os valores ao longo de uma linha e uma coluna.

Criar tabela de dados de uma variável

Criar uma tabela de dados de uma variável no Excel é uma habilidade valiosa para analisar como mudanças em uma única entrada podem impactar vários resultados. Esta seção irá guiá-lo pelo processo de criação de tabelas de dados orientadas por coluna, linha e múltiplas fórmulas de uma variável.

Incrível! Usar Abas Eficientes no Excel como no Chrome, Edge, Firefox e Safari!
Economize 50% do seu tempo e reduza milhares de cliques do mouse para você todos os dias!

---

Tabela de dados orientada por coluna

Uma tabela de dados orientada por coluna é útil quando você deseja testar como diferentes valores de uma única variável impactam a saída de uma fórmula, com os valores da variável listados em uma coluna. Vamos considerar um exemplo financeiro simples:

Suponha que você esteja considerando um empréstimo de $50.000, que planeja pagar ao longo de um período de 3 anos (equivalente a 36 meses). Para avaliar qual pagamento mensal seria acessível com base no seu salário, você está interessado em explorar como várias taxas de juros afetariam o valor que precisa pagar mensalmente.

Passo 1: Configure a fórmula base

Para calcular o pagamento, aqui, vou definir o juro como 5%. Em uma célula B4, insira a fórmula PMT, que calcula o pagamento mensal com base na taxa de juros, número de períodos e valor do empréstimo. Veja a captura de tela:

= -PMT($B$1/12, $B$2*12, $B$3)

Passo 2: Liste as taxas de juros em uma coluna

Em uma coluna, liste as diferentes taxas de juros que deseja testar. Por exemplo, liste valores de 4% a 11% em incrementos de 1% em uma coluna e deixe pelo menos uma coluna em branco à direita para os resultados. Veja a captura de tela:

Passo 3: Crie a tabela de dados

1. Na célula E2, insira esta fórmula: =B4.
   
   Nota: B4 é a célula onde sua fórmula principal está localizada, esta é a fórmula cujos resultados você deseja ver mudar conforme varia a taxa de juros.
   
2. Selecione o intervalo que inclui sua fórmula, lista de taxas de juros e células adjacentes para os resultados (por exemplo, selecione D2 até E10). Veja a captura de tela:
   
3. Vá para a faixa de opções, clique na aba Dados , depois clique em Análise de Hipóteses > Tabela de Dados, veja a captura de tela:
   Tabela de Dados"/>
4. Na Tabela de Dados caixa de diálogo, clique na caixa Célula de Entrada da Coluna (já que estamos usando uma coluna para valores de entrada) e selecione a célula variável referenciada na sua fórmula. Neste exemplo, selecionamos B1, que contém a taxa de juros. Por fim, clique no botão OK , veja a captura de tela:
   
5. O Excel preencherá automaticamente as células vazias adjacentes a cada um dos seus valores variáveis (diferentes taxas de juros) com os resultados correspondentes. Veja a captura de tela:
   
6. Aplique o formato numérico desejado aos resultados (Moeda) conforme necessário. Agora, a tabela de dados orientada por coluna foi criada com sucesso, e você pode rapidamente examinar os resultados para avaliar quais valores de pagamento mensal seriam acessíveis para você quando a taxa de juros mudar. Veja a captura de tela:
   

Tabela de dados orientada por linha

Criar uma tabela de dados orientada por linha no Excel envolve organizar seus dados de modo que os valores variáveis sejam listados ao longo de uma linha, em vez de descendo uma coluna. Tomando o exemplo acima como referência, vamos prosseguir com os passos para completar a criação de uma tabela de dados orientada por linha no Excel.

Passo 1: Liste as taxas de juros em uma linha

Coloque os valores variáveis (taxas de juros) em uma linha, garantindo que haja pelo menos uma coluna vazia à esquerda para a fórmula e uma linha vazia abaixo para os resultados, veja a captura de tela:

Passo 2: Crie a tabela de dados

1. Na célula A9, insira esta fórmula: =B4.
   
2. Selecione o intervalo que inclui sua fórmula, lista de taxas de juros e células adjacentes para os resultados (por exemplo, selecione A8 até I9). Depois, clique em Dados > Análise de Hipóteses > Tabela de Dados.
3. Na Tabela de Dados caixa de diálogo, clique na caixa Célula de Entrada da Linha (já que estamos usando uma linha para valores de entrada), e selecione a célula variável referenciada na sua fórmula. Neste exemplo, selecionamos B1, que contém a taxa de juros. Por fim, clique no botão OK , veja a captura de tela:
   
4. Aplique o formato numérico desejado aos resultados (Moeda) conforme necessário. Agora, a tabela de dados orientada por linha foi criada, veja a captura de tela:
   

Múltiplas fórmulas para tabela de dados de uma variável

Criar uma tabela de dados de uma variável com múltiplas fórmulas no Excel permite que você veja como alterar uma única entrada afeta várias fórmulas diferentes de uma só vez. No exemplo acima, e se você quiser ver a mudança nas taxas de juros tanto nos pagamentos quanto nos juros totais? Aqui está como configurá-la:

Passo 1: Adicione uma nova fórmula para calcular os juros totais

Na célula B5, insira a seguinte fórmula para calcular os juros totais:

=B4*B2*12-B3

Passo 2: Organize os dados da tabela de origem

Em uma coluna, liste as diferentes taxas de juros que deseja testar, e deixe pelo menos duas colunas em branco à direita para os resultados. Veja a captura de tela:

Passo 3: Crie a tabela de dados:

1. Na célula E2, insira esta fórmula: =B4 para criar uma referência ao cálculo de pagamento nos dados originais.
2. Na célula F2, insira esta fórmula: =B5 para criar uma referência aos juros totais nos dados originais.
   
3. Selecione o intervalo que inclui suas fórmulas, lista de taxas de juros e células adjacentes para o resultado (por exemplo, selecione D2 até F10). Depois, clique em Dados > Análise de Hipóteses > Tabela de Dados.
4. Na Tabela de Dados caixa de diálogo, clique na caixa Célula de Entrada da Coluna (já que estamos usando uma coluna para valores de entrada), e selecione a célula variável referenciada na sua fórmula. Neste exemplo, selecionamos B1, que contém a taxa de juros. Por fim, clique no botão OK , veja a captura de tela:
   
5. Aplique o formato numérico desejado aos resultados (Moeda) conforme necessário. E você pode ver os resultados para cada fórmula com base nos valores variáveis.
   

Desbloqueie a Magia do Excel com o Kutools AI

• Execução Inteligente: Realize operações de células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
• Fórmulas Personalizadas: Gere fórmulas sob medida para otimizar seus fluxos de trabalho.
• Codificação VBA: Escreva e implemente código VBA sem esforço.
• Interpretação de Fórmulas: Compreenda fórmulas complexas com facilidade.
• Tradução de Texto: Supere barreiras linguísticas dentro de suas planilhas.

Potencialize suas capacidades no Excel com ferramentas alimentadas por IA. Baixe Agora e experimente uma eficiência como nunca antes!

Criar tabela de dados de duas variáveis

Uma tabela de dados de duas variáveis no Excel exibe o impacto de diferentes combinações de dois conjuntos de valores variáveis no resultado de uma fórmula, demonstrando como alterações em duas entradas de uma fórmula afetam simultaneamente seu resultado.

Aqui, fiz um esboço simples para ajudar você a entender melhor a aparência e a estrutura de uma tabela de dados de duas variáveis.

Baseando-se no exemplo para criar uma tabela de dados de uma variável, vamos agora aprender como criar uma tabela de dados de duas variáveis no Excel.

No conjunto de dados abaixo, temos a taxa de juros, o prazo do empréstimo e o valor do empréstimo, e calculamos o pagamento mensal usando esta fórmula: =-PMT($B$1/12, $B$2*12, $B$3) Além disso, focaremos em duas variáveis-chave dos nossos dados: taxa de juros e valor do empréstimo, para observar como mudanças em ambos os fatores afetam simultaneamente os valores dos pagamentos.

Passo 1: Configure as duas variáveis que estão mudando

1. Em uma coluna, liste as diferentes taxas de juros que deseja testar. Veja a captura de tela:
   
2. Em uma linha, insira os diferentes valores do valor do empréstimo logo acima dos valores da coluna (começando de uma célula à direita da célula da fórmula), veja a captura de tela:
   

Passo 2: Crie a tabela de dados

1. Coloque sua fórmula na interseção da linha e da coluna onde você listou os valores das variáveis. Neste caso, inserirei esta fórmula: =B4 na célula E2. Veja a captura de tela:
   
2. Selecione o intervalo que inclui seus valores de empréstimo, taxas de juros, a célula da fórmula e as células onde os resultados serão exibidos.
   
3. Depois, clique em Dados > Análise de Hipóteses > Tabela de Dados. Na caixa de diálogo Tabela de Dados:
   • Na caixa Célula de Entrada da Linha, selecione a referência da célula para a célula de entrada dos valores variáveis na linha (neste exemplo, B3 contém o valor do empréstimo).
   • Na caixa Célula de Entrada da Coluna, selecione a referência da célula para a célula de entrada dos valores variáveis na coluna (B1 contém a taxa de juros).
   • E então, clique no botão OK Botão.
     
4. Agora, o Excel preencherá a tabela de dados com os resultados para cada combinação de valor do empréstimo e taxa de juros. Ele fornece uma visão direta de como diferentes combinações de valores de empréstimo e taxas de juros afetam o pagamento mensal, tornando-se uma ferramenta valiosa para planejamento e análise financeira.
   
5. Por fim, você deve aplicar o formato numérico desejado aos resultados (Moeda) conforme necessário.
   

Pontos-chave ao usar tabelas de dados

• A tabela de dados recém-criada deve estar na mesma planilha que os dados originais.
• A saída da tabela de dados depende da célula da fórmula no conjunto de dados de origem, e qualquer alteração nesta célula da fórmula atualizará automaticamente a saída.
• Uma vez que os valores tenham sido calculados usando a tabela de dados, eles não podem ser desfeitos com Ctrl + Z. No entanto, você pode selecionar manualmente todos os valores e excluí-los.
• A tabela de dados gera fórmulas de matriz, portanto, células individuais dentro da tabela não podem ser alteradas ou excluídas.

Outras operações ao usar tabelas de dados

Uma vez que você tenha criado uma tabela de dados, pode precisar de operações adicionais para gerenciá-la de forma eficaz, incluindo excluir a tabela de dados, modificar seus resultados e realizar recalculações manuais.

Excluir uma tabela de dados

Como os resultados da tabela de dados são calculados com uma fórmula de matriz, você não pode excluir um valor individual da tabela de dados. No entanto, você só pode excluir toda a tabela de dados.

Selecione todas as células da tabela de dados ou apenas as células com os resultados, e depois pressione a tecla Delete no teclado.

Editar resultado da tabela de dados

De fato, não é possível editar células individuais dentro de uma tabela de dados diretamente, pois elas contêm fórmulas de matriz que o Excel gera automaticamente.

Para fazer alterações, normalmente você precisaria excluir a tabela de dados existente e depois criar uma nova com as modificações desejadas. Isso envolve ajustar sua fórmula base ou os valores de entrada e depois configurar novamente a tabela de dados para refletir essas mudanças.

Recalcular tabela de dados manualmente

Normalmente, o Excel recalcula todas as fórmulas em todas as pastas de trabalho abertas sempre que uma alteração é feita. Se uma grande tabela de dados contendo numerosos valores variáveis e fórmulas complexas estiver causando lentidão na sua pasta de trabalho do Excel.

Para evitar que o Excel execute automaticamente cálculos para todas as tabelas de dados sempre que uma alteração for feita na pasta de trabalho, você pode alternar o modo de cálculo de Automático para Manual. Por favor, faça o seguinte:

Vá para a aba Fórmulas , e depois clique em Opções de Cálculo > Automático Exceto para Tabelas de Dados, veja a captura de tela:

Uma vez que você modifique essa configuração, ao recalcular sua pasta de trabalho inteira, o Excel não atualizará automaticamente os cálculos em suas tabelas de dados.

Se você precisar atualizar sua tabela de dados manualmente, basta selecionar as células onde os resultados (células contendo as fórmulas TABLE()) são exibidos, e depois pressionar a tecla F9.

Seguindo os passos descritos neste guia e mantendo os pontos-chave em mente, você pode utilizar tabelas de dados de forma eficiente para suas necessidades de análise de dados. Se você estiver interessado em explorar mais dicas e truques do Excel, nosso site oferece milhares de tutoriais, por favor clique aqui para acessá-los. Obrigado por ler, e esperamos fornecer mais informações úteis no futuro!