Ceci est le premier article de la série en cinq parties sur l'analyse des données dans Excel. Dans cette section, je vais vous montrer comment créer et utiliser un tableau de données à une variable dans Excel.
Autres articles de la série Analyse de données :
- Tableau de données à deux variables dans Excel.
- Gestionnaire de scénarios dans Excel.
- Recherche d'objectif dans Excel.
- Solveur Excel.
Regarder la vidéo - Tableau de données à une variable dans Excel
Une table de données variable dans Excel est la plus adaptée dans les situations où vous souhaitez voir comment le résultat final change lorsque vous modifiez l'une des variables d'entrée. Si vous souhaitez modifier deux variables, utilisez une table de données à deux variables ou le gestionnaire de scénarios.
Quand utiliser une table de données 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.
La valeur du paiement mensuel est calculée sur la base de ces trois variables (elle 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 quel nombre de paiements mensuels convient à votre état et comment le paiement mensuel varierait en fonction de celui-ci. Par exemple, vous voudrez peut-être conserver la durée de votre prêt (nombre de paiements mensuels) de sorte que le montant de votre paiement mensuel ne dépasse pas 1 000 $.
Un moyen rapide consiste à modifier les données de cette table et la formule serait automatiquement mise à jour. Cependant, si vous souhaitez tester si pour de nombreux nombres de paiements mensuels différents, un tableau de données à une variable dans Excel serait la solution.
Configuration d'un tableau de données à une variable dans Excel
Voici les étapes pour configurer un tableau de données à une variable dans Excel :
- Dans une colonne, ayez toutes les différentes valeurs du nombre de paiements mensuels que vous souhaitez tester. Dans cet exemple, nous testons 72, 84, 96… 240.
- Tapez =B4 dans la cellule E1, qui se trouve une ligne au-dessus des valeurs de la colonne adjacente à droite. Il s'agit d'une construction qui doit être suivie lorsque vous travaillez avec une table de données variables dans Excel. Assurez-vous également que la valeur de la cellule E1 dépend du nombre de paiements mensuels. Cela ne fonctionnera pas si vous entrez manuellement la valeur dans la cellule E1.
Dans ce cas, la cellule E1 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 dans la table de données à une variable.
- Sélectionnez les données (D1:E16). Accédez à 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, reportez-vous à la cellule B3 dans le champ Cellule de saisie de la colonne. Étant donné que nous utilisons un tableau de données à une variable, laissez la cellule d'entrée de ligne vide. De plus, nous avons choisi l'option de cellule d'entrée de colonne car les données sont structurées dans une colonne.
- Cliquez sur OK. Dès que vous cliquez sur OK, il remplira automatiquement les cellules adjacentes aux points de données variables (nombre de mensualités).
Par exemple, si vous vouliez calculer le nombre de paiements mensuels que vous devriez utiliser pour que le paiement mensuel soit inférieur à 1 000 $. Vous pouvez facilement sélectionner 144 mois (12 ans).
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.
- Vous pouvez faire le même calcul en organisant les données horizontalement. La seule différence serait que vous devrez utiliser la cellule d'entrée de ligne dans la boîte de dialogue du tableau de données (au lieu de la cellule d'entrée de colonne).
