Quando você tem uma fórmula complexa dependente de múltiplas variáveis ​​e deseja entender como a alteração dessas entradas afeta os resultados de forma eficiente, uma tabela de dados de análise de variações hipotéticas no Excel é uma ferramenta poderosa. Ele permite que você veja todos os resultados possíveis com uma rápida olhada. Aqui está um guia passo a passo sobre como criar uma tabela de dados no Excel. Além disso, discutiremos alguns pontos-chave para o uso eficaz de tabelas de dados e demonstraremos outras operações, como exclusão, edição e recálculo de tabelas de dados.

O que é tabela de dados no Excel?

Crie uma tabela de dados de uma variável

• Tabela de dados orientada a colunas
• Tabela de dados orientada a linhas
• Várias fórmulas para tabela de dados de uma variável

Crie uma tabela de dados de duas variáveis

Pontos-chave usando as tabelas de dados

Outras operações para usar tabelas de dados

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

O que é tabela de dados no Excel?

No Excel, uma tabela de dados é uma das ferramentas de análise de variações hipotéticas que permite experimentar diferentes valores de entrada para fórmulas e observar as alterações na saída da fórmula. Esta ferramenta é extremamente ú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 analisar como diferentes valores de uma variável afetarão uma fórmula. Você pode configurar uma tabela de dados de uma variável em um formato orientado a linhas ou orientado a colunas, dependendo se deseja variar sua entrada em uma linha ou em uma coluna.

Tabela de dados de duas variáveis: esse tipo permite ver o impacto da alteração de 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 de uma coluna.

Crie uma tabela de dados de uma variável

Criar uma tabela de dados de uma variável no Excel é uma habilidade valiosa para analisar como as mudanças em uma única entrada podem impactar vários resultados. Esta seção irá guiá-lo através do processo de criação de tabelas de dados orientadas a colunas, orientadas a linhas e de múltiplas variáveis ​​de fórmula um.

Incrível! Usando guias eficientes no Excel como 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 a colunas

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

Suponha que você esteja considerando um empréstimo de US$ 50,000, que planeja pagar em 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 as várias taxas de juros afetariam o valor que você precisa pagar a cada mês.

Etapa 1: configurar a fórmula base

Para calcular o pagamento, aqui definirei os juros em 5%. Na célula B4, insira a fórmula PMT, ela 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:

Etapa 3: crie a tabela de dados

1. Na célula E2, insira esta fórmula: = B4.
   
   Note: B4 é a célula onde está localizada sua fórmula principal, esta é a fórmula cujos resultados você deseja ver alterados à medida que 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 a E10). Veja a captura de tela:
   
3. Vá para a faixa de opções, clique no Data guia, em seguida, clique Análise do que se analisa > Tabela de dados, veja a captura de tela:
   
4. No Tabela de dados caixa de diálogo, clique no Célula de entrada da coluna caixa (já que estamos usando uma coluna para valores de entrada) e selecione a célula variável referenciada em sua fórmula. Neste exemplo, selecionamos B1 que contém a taxa de juros. Por fim, clique OK botão, veja a captura de tela:
   
5. O Excel preencherá automaticamente as células vazias adjacentes a cada um dos valores das 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) de acordo com sua necessidade. Agora, a tabela de dados orientada a colunas foi criada com sucesso e você pode examinar rapidamente 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 a linhas

A criação de uma tabela de dados orientada a linhas no Excel envolve organizar seus dados de forma que os valores das variáveis ​​sejam listados em uma linha, em vez de em uma coluna. Tomando o exemplo acima como referência, vamos prosseguir com as etapas para concluir a criação de uma tabela de dados orientada a linhas no Excel.

Etapa 1: liste as taxas de juros consecutivas

Coloque os valores das 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:

Etapa 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 a I9). Então clique Data > Análise do que se analisa > Tabela de dados.
3. No Tabela de dados caixa de diálogo, clique no Célula de entrada de linha caixa (já que estamos usando uma linha para valores de entrada) e selecione a célula variável referenciada em sua fórmula. Neste exemplo, selecionamos B1 que contém a taxa de juros. Por fim, clique OK botão, veja a captura de tela:
   
4. Aplique o formato numérico desejado aos resultados (Moeda) de acordo com sua necessidade. Agora, a tabela de dados orientada a linhas é criada, veja a captura de tela:
   

Várias fórmulas para tabela de dados de uma variável

A criação de uma tabela de dados de uma variável com múltiplas fórmulas no Excel permite ver como a alteração de uma única entrada afeta várias fórmulas diferentes ao mesmo tempo. No exemplo acima, e se você quiser ver a mudança nas taxas de juros tanto nos reembolsos quanto nos juros totais? Veja como configurá-lo:

Etapa 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

Etapa 2: organizar os dados de origem da tabela de dados

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:

Etapa 3: Crie a tabela de dados:

1. Na célula E2, insira esta fórmula: = B4 para criar uma referência ao cálculo do reembolso nos dados originais.
2. Na célula F2, insira esta fórmula: = B5 para criar uma referência ao interesse total 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 a F10). Então clique Data > Análise do que se analisa > Tabela de dados.
4. No Tabela de dados caixa de diálogo, clique no Célula de entrada de coluna caixa (já que estamos usando uma coluna para valores de entrada) e selecione a célula variável referenciada em sua fórmula. Neste exemplo, selecionamos B1 que contém a taxa de juros. Por fim, clique OK botão, veja a captura de tela:
   
5. Aplique o formato numérico desejado aos resultados (Moeda) de acordo com sua necessidade. E você pode ver os resultados de cada fórmula com base nos valores variáveis ​​das variáveis.
   

Crie uma 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 de variáveis ​​no resultado de uma fórmula, demonstrando como alterações em duas entradas de uma fórmula afetam simultaneamente seu resultado.

Aqui, desenhei um esboço simples para ajudá-lo a entender melhor a aparência e a estrutura de uma tabela de dados de duas variáveis.

Com base no exemplo de criação de 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) também. Aqui, iremos concentrar-nos em duas variáveis-chave dos nossos dados: taxa de juro e montante do empréstimo, para observar como as alterações em ambos estes factores afectam simultaneamente os montantes de reembolso.

Etapa 1: configurar as duas variáveis ​​variáveis

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 em uma célula à direita da célula da fórmula), veja a captura de tela:
   

Etapa 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. Nesse caso, inserirei esta fórmula: = B4 na célula E2. Veja a captura de tela:
   
2. Selecione o intervalo que inclui os valores do empréstimo, as taxas de juros, a célula da fórmula e as células onde os resultados serão exibidos.
   
3. Então clique Data > Análise do que se analisa > Tabela de dados. Na caixa de diálogo Tabela de dados:
   • No Célula de entrada de linha caixa, selecione a referência de célula para a célula de entrada para os valores das variáveis ​​na linha (neste exemplo, B3 contém o valor do empréstimo).
   • No Célula de entrada da colunaNa caixa l, selecione a referência da célula para a célula de entrada para os valores das variáveis ​​na coluna (B1 contém a taxa de juros).
   • E então, clique OK Botão.
     
4. Agora, o Excel preencherá a tabela de dados com os resultados de 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éstimos e taxas de juros afetam o pagamento mensal, tornando-o uma ferramenta valiosa para planejamento e análise financeira.
   
5. Por fim, você deve aplicar o formato numérico desejado aos resultados (Moeda) de acordo com sua necessidade.
   

Pontos-chave usando as 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 de fórmula no conjunto de dados de origem e quaisquer alterações nesta célula de fórmula atualizarão automaticamente a saída.
• Depois que os valores forem calculados usando a tabela de dados, eles não poderão 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 da tabela não podem ser alteradas ou excluídas.

Outras operações para usar tabelas de dados

Depois de criar uma tabela de dados, você poderá precisar de operações adicionais para gerenciá-la com eficiência, incluindo a exclusão da tabela de dados, a modificação de seus resultados e a execução de recálculos manuais.

Excluir uma tabela de dados

Como os resultados da tabela de dados são calculados com uma fórmula de matriz, não é possível 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 pressione Apagar tecla no teclado.

Editar resultado da tabela de dados

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

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

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 vários valores de variáveis ​​​​e fórmulas complexas estiver causando lentidão na sua pasta de trabalho do Excel.

Para evitar que o Excel execute cálculos automaticamente 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. Faça o seguinte:

Vou ao Fórmulas Guia e, em seguida, clique em Opções de Cálculo > Automático, exceto para tabelas de dados, veja a captura de tela:

Depois de modificar essa configuração, ao recalcular toda a sua pasta de trabalho, o Excel não atualizará mais automaticamente os cálculos nas suas tabelas de dados.

Se 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 pressionar F9 chave.

Seguindo as etapas descritas neste guia e mantendo os pontos principais em mente, você pode utilizar tabelas de dados com eficiência 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!