Uma tabela de dados variáveis ​​no Excel

O que é Tabela de dados de uma variável no Excel?

Uma tabela de dados de variável no Excel significa alterar uma variável junto com várias opções e obter os resultados para vários cenários.

Como criar uma tabela de dados de uma variável no Excel? (com exemplos)

Você pode baixar este modelo de tabela de dados de uma variável aqui - modelo de tabela de dados de uma variável em Excel

Exemplo 1

Você está tomando um empréstimo de Rs. 2, 50.000 por 2 anos. Você está discutindo com o oficial de crédito a respeito da taxa de juros.

Você precisa analisar, a diferentes taxas de juros, qual é o pagamento mensal que você precisa pagar para compensar o empréstimo. Para efeito de cálculo, use a taxa básica de 12% ao ano.

  • Passo 1: Calcule o EMI mensal usando a função PMT.

  • Etapa 2: Agora crie uma tabela de cenários conforme mostrado na imagem abaixo.

  • Passo 3: Selecione a célula E9 e dê um link para a célula B6 (quantidade EMI). Agora a célula E9 mostrando o EMI mensal.

  • Etapa 4: Selecione o intervalo de D9 a E22.

  • Etapa 5: Clique na guia Dados e  em Análise de hipóteses e Tabela de dados

  • Etapa 6: A caixa de diálogo Tabela de dados aparecerá. Na célula de entrada da coluna , selecione a célula B5 (que contém a taxa básica de juros do empréstimo).

Uma tabela de dados variáveis ​​no Excel, sempre ignoramos a célula de entrada ROW ou a célula de entrada de coluna. Depende da estrutura da nossa mesa. Se as diferentes taxas de juros da nossa tabela de cenários forem verticais, ignoramos a célula de entrada de linha e, se as taxas de juros da nossa tabela de cenários forem horizontais, ignoramos a célula de entrada da coluna. Neste exemplo, ignorei a célula de entrada de linha porque as diferentes taxas de juros da tabela de cenários estão em uma forma vertical.

  • Passo 7: Agora clique no botão OK para criar diferentes cenários.

Agora o intervalo E10: E22 mostrando alguns novos valores. A partir da tabela, é muito claro que @ taxa de juros de 12,5% EMI mensal seria 11.827 INR e @ taxa de juros de 13,5% EMI mensal seria 11.944 INR e assim por diante.

É assim que funciona uma tabela de dados variáveis ​​no Excel. Você também pode mostrar isso em um gráfico.

Exemplo # 2

Suponha que você seja o gerente de vendas de uma empresa. Da gerência, você recebeu da sua equipe uma meta de vendas mensal de 1,70,00 USD. A tabela abaixo mostra a meta de vendas para 6 membros. Você precisa analisar qual deve ser o nível de eficiência deles para atingir a meta de 1,7 lakh USD em um mês.

A meta geral de suas equipes é 2,04 lakhs. Você não tem certeza de qual porcentagem de eficiência eles precisam trazer para a mesa para atingir a meta dada pela gerência.

Sua equipe pode fornecer um nível de eficiência máximo de 90% e você calculou a receita total com um nível de eficiência de 90%.

Com um nível de eficiência de 90%, sua equipe pode atingir a receita total de 1,83 lakh USD em um mês. Você precisa qual deve ser o nível de eficiência para atingir a meta de receita dada pela administração.

Crie uma tabela de cenários conforme mostrado na imagem abaixo.

  • Etapa 1: Abaixo da tabela, você precisa criar um arquivo excel.

Esta tabela mostra em diferentes níveis de eficiência qual será a receita?

  • Passo 2: Selecione a célula H3 e dê um link para a célula B11 (célula de receita com nível de eficiência de 90%). Agora a célula H3 mostra a receita com nível de eficiência de 90%.

  • Etapa 3: Selecione o intervalo de G3 a H12

  • Passo 4: Agora encontre a Tabela de Dados na seção Análise What if.

  • Passo 5: Depois de clicar na Tabela de Dados, precisamos fornecer um link na Célula de Entrada da Coluna , selecione a célula B10 (que contém a porcentagem de eficiência).

Para a célula de entrada da coluna, forneci o link para a célula B10 porque, com base nos diferentes níveis de eficiência, iremos criar os cenários. Agora, a tabela de dados indica que a receita de 90% será de 1,83 lakh USD. Da mesma forma, ele criará cenários para 100%, 95%, 90%, 85%, 80% e assim por diante.

  • Etapa 6: Clique em OK para criar diferentes cenários.

Agora o intervalo G3: H12 mostrando cenários. A administração deu a meta de 1,70 lakh USD para este mês. Para obter essa receita, sua equipe precisa ter um nível de eficiência de pelo menos 85%.

Desta forma, você pode usar a Tabela de dados para criar análises diferentes e escolher um cenário adequado para atingir os objetivos.

Coisas para lembrar

  • Depois de criar os cenários, não podemos desfazer a ação. Ele permanecerá o mesmo.
  • Não podemos nem mesmo modificar os valores das células, porque se torna uma fórmula de matriz do Excel e você precisa excluir tudo, não um por um.
  • Em uma tabela de dados variáveis, sempre deixe de fora a célula de entrada Linha se o cenário for mostrado na forma vertical. Se o cenário for mostrado na forma horizontal, deixe a célula de entrada Coluna.
  • Você pode alterar o valor no banco de dados principal para ver os resultados em tempo real em diferentes tipos de alternativas.