Un tableau de données variables dans Excel

Qu'est-ce qu'un tableau de données à une variable dans Excel?

Une table de données variable dans Excel signifie en changeant une variable avec plusieurs options et obtenir les résultats pour plusieurs scénarios.

Comment créer un tableau de données à une variable dans Excel? (avec exemples)

Vous pouvez télécharger ce modèle Excel de table de données à une variable ici - Modèle Excel de table de données à une variable

Exemple 1

Vous prenez un prêt de Rs. 2, 50 000 pour 2 ans. Vous êtes en discussion avec l'agent de crédit concernant le taux d'intérêt.

Vous devez analyser à différents taux d'intérêt le paiement mensuel que vous devez payer pour compenser le prêt. Aux fins du calcul, prenez le taux de base de 12% par an.

  • Étape 1: Calculez l'EMI mensuelle à l'aide de la fonction PMT.

  • Étape 2: Créez maintenant un tableau de scénario comme indiqué dans l'image ci-dessous.

  • Étape 3: Sélectionnez la cellule E9 et donnez un lien vers la cellule B6 (Montant EMI). Maintenant, la cellule E9 montrant l'EMI mensuel.

  • Étape 4: Sélectionnez la plage de D9 à E22.

  • Étape 5: Cliquez sur l'onglet Données, puis sur  What-if-Analysis and Data Table

  • Étape 6: La boîte de dialogue Table de données apparaîtra. Dans la cellule d'entrée de colonne , sélectionnez la cellule B5 (qui contient le taux d'intérêt de base du prêt).

Une table de données variables dans Excel, nous ignorons toujours la cellule d'entrée ROW ou la cellule d'entrée Column. Cela dépend de la structure de notre table. Si nos taux d'intérêt différents dans notre tableau de scénario sont verticaux, nous ignorons la cellule d'entrée de ligne et si les taux d'intérêt de notre tableau de scénario sont horizontaux, nous ignorons la cellule d'entrée de colonne. Dans cet exemple, j'ai ignoré la cellule d'entrée Row, car les différents taux d'intérêt du tableau de scénario sont de manière verticale.

  • Étape 7: Cliquez maintenant sur le bouton OK pour créer différents scénarios.

Maintenant, la plage E10: E22 montre de nouvelles valeurs. D'après le tableau, il est très clair que l'EMI mensuel à un taux d'intérêt de 12,5% sera de 11 827 INR et l'EMI mensuel à un taux d'intérêt de 13,5% serait de 11 944 INR et ainsi de suite.

C'est ainsi que fonctionne une table de données variables dans Excel. Vous pouvez également le montrer dans un graphique.

Exemple # 2

Supposons que vous soyez directeur des ventes d'une entreprise. De la direction, vous avez reçu un objectif de vente mensuel de 1, 70, 00 USD de votre équipe. Le tableau ci-dessous montre l'objectif de vente pour 6 membres. Vous devez analyser quel devrait être leur niveau d'efficacité pour atteindre l'objectif de 1,7 lakh USD en un mois.

L'objectif global de vos équipes est de 2,04 lakhs. Vous ne savez pas quel pourcentage d'efficacité ils doivent apporter à la table pour atteindre l'objectif fixé par la direction.

Votre équipe peut donner un niveau d'efficacité maximal de 90% et vous avez calculé le revenu total à un niveau d'efficacité de 90%.

Avec un niveau d'efficacité de 90%, votre équipe peut atteindre un revenu total de 1,83 lakh USD en un mois. Vous avez besoin de quel devrait être le niveau d'efficacité pour atteindre l'objectif de revenus donné par la direction.

Créez un tableau de scénario comme indiqué dans l'image ci-dessous.

  • Étape 1: Sous le tableau, vous devez créer un fichier Excel.

Ce tableau montre à différents niveaux d'efficacité quels seront les revenus?

  • Étape 2: Sélectionnez la cellule H3 et donnez un lien vers la cellule B11 (à 90% de la cellule de revenu de niveau d'efficacité). Maintenant, la cellule H3 montre les revenus de niveau d'efficacité de 90%.

  • Étape 3: Sélectionnez la plage de G3 à H12

  • Étape 4: Recherchez maintenant le tableau de données dans la section Et si l'analyse.

  • Étape 5: Une fois que vous avez cliqué sur le tableau de données, nous devons donner un lien dans la cellule d'entrée de colonne , sélectionnez la cellule B10 (qui contient le pourcentage d'efficacité).

Pour la cellule d'entrée Colonne, j'ai donné le lien vers la cellule B10 car sur la base des différents niveaux d'efficacité, nous allons créer les scénarios. Maintenant, le tableau de données comprend qu'à 90% les revenus seront de 1,83 lakh USD. De même, il créera des scénarios pour 100%, 95%, 90%, 85%, 80% et ainsi de suite.

  • Étape 6: Cliquez sur OK pour créer différents scénarios.

Maintenant, la gamme G3: H12 montrant des scénarios. La direction a donné l'objectif de 1,70 lakh USD pour ce mois. Afin de réaliser autant de revenus, votre équipe doit fonctionner à un niveau d'efficacité d'au moins 85%.

De cette manière, vous pouvez utiliser la table de données pour créer différentes analyses et choisir un scénario approprié pour atteindre les objectifs.

Choses à retenir

  • Une fois les scénarios créés, nous ne pouvons pas annuler l'action. Cela restera le même.
  • Nous ne pouvons même pas modifier les valeurs de cellule car cela devient une formule matricielle Excel et vous devez tout supprimer pas un par un.
  • Dans une table de données variables, laissez toujours de côté la cellule d'entrée Row si le scénario doit être affiché sous la forme verticale. Si le scénario doit être affiché sous forme horizontale, quittez la cellule d'entrée Colonne.
  • Vous pouvez modifier la valeur dans la base de données principale pour voir les résultats en temps réel à différents types d'alternatives.