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

Quando tem uma fórmula complexa que depende de múltiplas variáveis e pretende compreender de forma eficiente como as alterações nessas entradas afetam os resultados, a tabela de dados da Análise de Hipóteses no Excel é uma ferramenta poderosa. Permite visualizar todos os resultados possíveis com um simples olhar. Eis um guia passo a passo para criar uma tabela de dados no Excel. Além disso, abordaremos pontos essenciais para utilizar tabelas de dados de forma eficaz e demonstraremos outras operações, como eliminar, editar e recalcular essas tabelas.

O que é uma tabela de dados no Excel?

Criar tabela de dados com uma variável

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

Criar tabela de dados com duas variáveis

Pontos-chave na utilização de tabelas de dados

Outras operações na utilização de tabelas de dados

• Eliminar uma tabela de dados
• Editar resultado da tabela de dados
• Recalcular manualmente a tabela de dados

O que é uma tabela de dados no Excel?

No Excel, uma tabela de dados é uma das ferramentas de Análise de hipóteses que lhe permite experimentar diferentes valores de entrada nas fórmulas e observar as alterações no respetivo resultado. 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 com uma variável: Permite-lhe analisar como valores diferentes de uma única variável afetam uma fórmula. Pode configurar uma tabela de dados com uma variável em formato orientado por linha ou por coluna, consoante pretenda que a entrada varie ao longo de uma linha ou de uma coluna.

Tabela de dados com duas variáveis: Este tipo permite-lhe observar o impacto da alteração simultânea de duas variáveis diferentes no resultado de uma fórmula. Numa tabela de dados com duas variáveis, os valores variam ao mesmo tempo ao longo de uma linha e de uma coluna.

Criar tabela de dados com uma variável

Criar uma tabela de dados com uma variável no Excel é uma competência valiosa para analisar como as alterações numa única entrada influenciam diversos resultados. Esta secção orienta-o passo a passo na criação de tabelas de dados orientadas por coluna, orientadas por linha e com múltiplas fórmulas.

Espantoso! Utilize o eficiente Tabs no Excel tal como no Chrome, Edge, Firefox e Safari!
Poupe 50 % do seu tempo e reduza milhares de cliques do rato todos os dias!

---

Tabela de dados orientada por coluna

Uma tabela de dados orientada por coluna é útil quando pretende testar como diferentes valores de uma única variável afetam o resultado de uma fórmula, com os valores dessa variável listados numa coluna. Considere o seguinte exemplo financeiro:

Imagine que está a considerar um empréstimo de $50,000, que planeia reembolsar num período de 3 anos (equivalente a 36 meses). Para avaliar qual o pagamento mensal acessível com base no seu salário, pretende explorar como diferentes taxas de juro afetariam o montante que teria de pagar mensalmente.

Passo 1: Configurar a fórmula base

Para calcular o pagamento, defina aqui a taxa de juro como 5 %. Na célula B4, introduza a fórmula PGTO, que calcula o pagamento mensal com base na taxa de juro, no número de períodos e no montante do empréstimo. Veja a captura de ecrã:

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

Passo 2: Listar as taxas de juro numa coluna

Numa coluna, liste as diferentes taxas de juro que pretende testar — por exemplo, de 4 % a 11 %, em incrementos de 1 %. Deixe pelo menos uma coluna em branco à direita para os resultados. Veja a captura de ecrã:

Passo 3: Criar a tabela de dados

1. Na célula E2, introduza esta fórmula:=B4.
   
   Nota: B4 é a célula onde se encontra a sua fórmula principal, ou seja, a fórmula cujos resultados pretende observar a variar conforme altera a taxa de juro.
   
2. Selecione o intervalo que inclui a sua fórmula, a lista de taxas de juro e as células adjacentes destinadas aos resultados (por exemplo, o intervalo D2:E10). Veja a imagem:
   
3. Aceda ao Friso, clique no separador Dados, e depois clique em Análise de hipóteses>Tabela de dados, veja a captura de ecrã:
   Tabela de dados"/>
4. Na caixa de diálogo Tabela de dados, clique na caixa Célula de entrada da coluna (uma vez que estamos a utilizar uma coluna para os valores de entrada) e selecione a célula variável referenciada na sua fórmula. Neste exemplo, selecionamos a célula B1, que contém a taxa de juro. Por fim, clique no botão OK. Veja a captura de ecrã:
   
5. O Excel preencherá automaticamente as células vazias adjacentes a cada um dos seus valores variáveis (diferentes taxas de juro) com os respetivos resultados. Veja a captura de ecrã:
   
6. Aplique o formato numérico pretendido aos resultados (Moeda) de acordo com as suas necessidades. Agora, a tabela de dados orientada por coluna foi criada com sucesso e permite-lhe consultar rapidamente os resultados para avaliar quais os montantes mensais de pagamento que seriam acessíveis para si quando a taxa de juro mudar. Veja a captura de ecrã:
   

Tabela de dados orientada por linha

Criar uma tabela de dados orientada por linhas no Excel significa organizar os seus dados de forma que os valores das variáveis sejam dispostos ao longo de uma linha, em vez de numa coluna. Tendo o exemplo acima como referência, vamos avançar com os passos necessários para concluir a criação da sua tabela de dados orientada por linhas no Excel.

Passo 1: Listar as taxas de juro numa linha

Coloque os valores das variáveis (taxas de juro) numa linha, garantindo que existe pelo menos uma coluna vazia à esquerda para a fórmula e uma linha vazia abaixo para os resultados, conforme ilustrado na captura de ecrã:

Passo 2: Criar a tabela de dados

1. Na célula A9, introduza esta fórmula: =B4.
   
2. Selecione o intervalo que inclui a sua fórmula, a lista de taxas de juro e as células adjacentes para os resultados (por exemplo, selecione A8 a I9). Em seguida, clique em Dados > Análise de hipóteses > Tabela de dados.
3. Na caixa de diálogo Tabela de dados, clique na caixa Célula de entrada da linha (uma vez que estamos a utilizar uma linha para os valores de entrada) e selecione a célula variável referenciada na sua fórmula. Neste exemplo, selecionamos a célula B1, que contém a taxa de juro. Por fim, clique no botão OK. Veja a captura de ecrã:
   
4. Aplique o formato numérico pretendido aos resultados (Moeda) conforme as suas necessidades. A tabela de dados orientada por linha já foi criada — veja a captura de ecrã:
   

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

Criar uma tabela de dados com uma variável e múltiplas fórmulas no Excel permite-lhe observar como a alteração de um único valor de entrada afeta, em simultâneo, várias fórmulas distintas. No exemplo anterior, e se quiser analisar o impacto das variações nas taxas de juro tanto nas prestações mensais como no total de juros pagos? Eis como configurá-la:

Passo 1: Adicionar uma nova fórmula para calcular o total de juros

Na célula B5, introduza a seguinte fórmula para calcular o total de juros:

=B4*B2*12-B3

Passo 2: Organizar a Dados de Origem da tabela de dados

Numa coluna, liste as diferentes taxas de juro que pretende testar e deixe pelo menos duas colunas em branco à direita para os resultados. Veja a captura de ecrã:

Passo 3: Criar a tabela de dados:

1. Na célula E2, introduza esta fórmula: =B4 para criar uma referência ao cálculo do reembolso nos dados originais.
2. Na célula F2, introduza esta fórmula: =B5 para criar uma referência ao juro total nos dados originais.
   
3. Selecione o intervalo que inclui as suas fórmulas, a lista de taxas de juro e as células adjacentes para os resultados (por exemplo, selecione D2 a F10). Em seguida, clique em Dados > Análise de hipóteses > Tabela de dados.
4. Na caixa de diálogo Tabela de dados, clique na caixa Célula de entrada da coluna (uma vez que estamos a utilizar uma coluna para os valores de entrada) e selecione a célula variável referenciada na sua fórmula. Neste exemplo, selecionamos a célula B1, que contém a taxa de juro. Por fim, clique no botão OK. Veja a captura de ecrã:
   
5. Aplique o formato numérico pretendido aos resultados (Moeda), conforme as suas necessidades, e veja os resultados de cada fórmula com base nos diferentes valores da variável.
   

Desbloqueie a Magia do Excel com KUTOOLS AI

• Execução Inteligente: Execute operações em células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
• Fórmulas personalizadas: Crie fórmulas personalizadas para simplificar os seus fluxos de trabalho.
• Programação VBA: Escreva e implemente código VBA com facilidade.
• Interpretação de Fórmulas: Compreenda facilmente fórmulas complexas.
• Tradução de Texto: Elimine barreiras linguísticas nas suas folhas de cálculo.

Potencie as capacidades do Excel com ferramentas potenciadas por IA.Transferir Agorae experimente uma eficiência nunca antes alcançada!

Criar tabela de dados com duas variáveis

Uma tabela de dados com duas variáveis no Excel mostra como diferentes combinações de dois conjuntos de valores afetam o resultado de uma fórmula, revelando claramente o impacto de alterações simultâneas em ambas as entradas.

Aqui, elaborei um esboço simples para o ajudar a compreender melhor o aspeto e a estrutura de uma tabela de dados com duas variáveis.

Partindo do exemplo usado para criar uma tabela de dados com uma variável, vamos agora aprender a criar uma tabela de dados com duas variáveis no Excel.

No conjunto de dados abaixo, temos a taxa de juro, o prazo do empréstimo e o respetivo montante, e calculámos a prestação mensal utilizando a seguinte fórmula:=-PMT($B$1/12; $B$2*12; $B$3). Agora, vamos focar-nos em duas variáveis-chave dos nossos dados — a taxa de juro e o montante do empréstimo — para analisar como as suas alterações simultâneas impactam os valores das prestações.

Passo 1: Configurar as duas variáveis em mudança

1. Numa coluna, liste as diferentes taxas de juro que pretende testar. Veja a captura de ecrã:
   
2. Numa linha, introduza os diferentes valores do montante do empréstimo logo acima dos valores da coluna (começando numa célula à direita da célula da fórmula), veja a captura de ecrã:
   

Passo 2: Criar a tabela de dados

1. Coloque a sua fórmula na interseção entre a linha e a coluna onde listou os valores das variáveis. Neste caso, introduzirei esta fórmula: =B4 na célula E2. Veja a captura de ecrã:
   
2. Selecione o intervalo que inclui os montantes do empréstimo, as taxas de juro, a célula com a fórmula e as células onde os resultados serão exibidos.
   
3. Em seguida, 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 de linha, selecione a referência da célula que contém os valores variáveis na linha (neste exemplo, B3 contém o montante do empréstimo).
   • Na caixa Célula de entrada de coluna, selecione a referência da célula que contém os valores variáveis na coluna (B1 contém a taxa de juro).
   • Em seguida, clique no botão OK.
     
4. Agora, o Excel preencherá a tabela de dados com os resultados para cada combinação de montante do empréstimo e taxa de juro, oferecendo uma visão clara de como essas variáveis impactam o pagamento mensal — uma ferramenta essencial para planeamento e análise financeira.
   
5. Por fim, aplique o formato numérico pretendido aos resultados (Moeda), de acordo com as suas necessidades.
   

Pontos-chave na utilização de tabelas de dados

• A tabela de dados recém-criada deve estar na mesma folha de cálculo que os dados originais.
• O resultado da tabela de dados depende da célula com a fórmula definida nos Dados de Origem, e quaisquer alterações nessa célula atualizam automaticamente o resultado.
• Assim que os valores forem calculados utilizando a tabela de dados, não é possível anulá-los com Ctrl + Z. No entanto, pode selecionar manualmente todos os valores e eliminá-los.
• A tabela de dados gera fórmulas de matriz, pelo que não é possível alterar nem eliminar células individuais dentro da tabela.

Outras operações na utilização de tabelas de dados

Após criar uma tabela de dados, poderá precisar de realizar operações adicionais para a gerir de forma eficaz, como eliminar a tabela, modificar os seus resultados ou executar recálculos manuais.

Eliminar uma tabela de dados

Como os resultados da tabela de dados são calculados com uma fórmula de matriz, não é possível eliminar um valor individual da tabela. Contudo, pode eliminar a tabela de dados completa.

Selecione todas as células da tabela de dados ou apenas as células com os resultados e, em seguida, prima a tecla Eliminar no teclado.

Editar resultado da tabela de dados

De facto, não é possível editar diretamente células individuais numa tabela de dados, uma vez que estas contêm fórmulas de matriz geradas automaticamente pelo Excel.

Para efetuar alterações, normalmente terá de eliminar a tabela de dados existente e criar uma nova com as modificações pretendidas — o que implica ajustar a sua fórmula base ou os valores de entrada e, em seguida, configurar novamente a tabela de dados para refletir essas alterações.

Recalcular manualmente a tabela de dados

Normalmente, o Excel recalcula todas as fórmulas em todos os livros abertos sempre que é feita uma alteração. Se uma tabela de dados extensa — com numerosos valores variáveis e fórmulas complexas — estiver a tornar a sua pasta de trabalho do Excel mais lenta...

Para evitar que o Excel execute automaticamente cálculos em todas as tabelas de dados sempre que forem feitas alterações no livro, pode mudar o modo de cálculo de Automático para Manual. Siga estes passos:

Aceda ao separador Fórmulas, clique em Opções de Cálculo>Automático Exceto Tabelas de Dados, conforme ilustrado na captura de ecrã:

Assim que modificar esta definição, o Excel deixará de atualizar automaticamente os cálculos nas suas tabelas de dados ao recalcular todo o livro.

Se precisar de atualizar manualmente a sua tabela de dados, basta selecionar as células onde os resultados (células que contêm as fórmulas TABLE()) são apresentados e, em seguida, premir a tecla F9.

Ao seguir os passos descritos neste guia e considerar os pontos-chave mencionados, conseguirá utilizar as tabelas de dados de forma eficaz para as suas necessidades na Análise de Dados. Se quiser explorar mais dicas e truques do Excel, o nosso site oferece milhares de tutoriais;clique aqui para aceder a eles. Obrigado pela leitura — esperamos continuar a fornecer-lhe informações úteis no futuro!