Ceci est le deuxième article de la série en cinq parties sur l'analyse des données dans Excel. Dans cette section, je vais vous montrer comment utiliser le tableau de données à deux variables dans Excel.
Autres articles de cette série :
- Un tableau de données variables dans Excel.
- Gestionnaire de scénarios dans Excel.
- Recherche d'objectif dans Excel.
- Solveur Excel.
Regarder la vidéo - Tableau de données à deux variables dans Excel
La table de données à deux variables convient mieux aux situations où vous souhaitez voir comment le résultat final change lorsque deux des variables d'entrée changent simultanément (par rapport à une table de données à variable où une seule des variables d'entrée change).
Si vous souhaitez analyser des données lorsque plus de 2 variables changent, le gestionnaire de scénarios est la solution.
Quand utiliser le tableau de données à deux variables dans Excel
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous :
Dans l'ensemble de données ci-dessus, nous avons le montant du prêt, le taux d'intérêt et le nombre de paiements mensuels. Sur la base de ces 3 variables d'entrée, le paiement mensuel est calculé (il est en rouge car il s'agit d'une sortie d'argent). La formule suivante est utilisée pour calculer le paiement mensuel :
=PMT(B2/12,B3,B1)
Maintenant, vous voudrez peut-être faire une analyse pour voir quelle devrait être la combinaison idéale du montant du prêt et du nombre de paiements mensuels pour répondre à vos besoins. Par exemple, vous souhaiterez peut-être maintenir le paiement mensuel à 500 $ ou moins et analyser quelle combinaison Montant du prêt et Durée du prêt peut vous donner cela.
Dans une telle situation, un tableau de données à deux variables doit être utilisé.
Configuration de deux tableaux de données variables dans Excel
Voici les étapes pour configurer un tableau de données à deux variables dans Excel :
- Dans une colonne, placez toutes les différentes valeurs que vous souhaitez tester pour le nombre de paiements mensuels. Dans cet exemple, nous testons 72, 84, 96… 240. En même temps, placez les différentes valeurs de montant de prêt dans la ligne juste au-dessus des valeurs de colonne (en commençant une cellule à droite) comme indiqué dans l'image ci-dessous.
- Tapez =B4 dans la cellule D1, qui se trouve une ligne au-dessus des valeurs de la colonne. Il s'agit d'une construction qui doit être suivie lorsque vous travaillez avec deux tables de données variables. Assurez-vous également que la valeur de la cellule D1 dépend des deux variables (nombre de paiements mensuels et montant du prêt). Cela ne fonctionnera pas si vous entrez manuellement la valeur dans la cellule D1.
Dans ce cas, la cellule D1 fait référence à la cellule B4, qui a une valeur calculée à l'aide d'une formule qui utilise les cellules B1, B2 et B3.
- Maintenant, les données sont toutes définies pour être utilisées pour un calcul de table de données à deux variables.
- Sélectionnez les données (D1:J16). Allez dans l'onglet Données -> Outils de données -> Et si analyse -> Table de données
- Dans la boîte de dialogue Table de données, utilisez les références suivantes :
- Cellule d'entrée de ligne : $B$1
- Cellule d'entrée de la colonne : $B$3
- Cliquez sur OK. Dès que vous cliquez sur OK, il remplit instantanément toutes les cellules vides de la plage de données sélectionnée. Il vous donne rapidement une vue des paiements mensuels pour diverses combinaisons de montant de prêt et de nombre de paiements mensuels.
Par exemple, si vous souhaitez identifier les combinaisons du montant du prêt et du nombre de paiements mensuels qui entraîneraient un paiement mensuel inférieur à 500 $ par mois, vous pouvez simplement utiliser cette méthode de tableau à 2 données variables.
Noter:
- Une fois que vous avez calculé les valeurs à l'aide de la table de données, cela ne peut pas être annulé à l'aide de Ctrl + Z. Vous pouvez cependant sélectionner manuellement toutes les valeurs et les supprimer.
- Vous ne pouvez pas supprimer/modifier une cellule dans l'ensemble des valeurs calculées. Comme il s'agit d'un tableau, vous devrez supprimer toutes les valeurs.
