Une table de données est une plage de cellules dans laquelle vous pouvez modifier les valeurs de certaines cellules et trouver différentes réponses à un problème. Un bon exemple de table de données utilise la fonction PMT avec différents montants de prêt et taux d’intérêt pour calculer le montant abordable sur un prêt hypothécaire immobilier. L’expérimentation de différentes valeurs pour observer la variation correspondante des résultats est une tâche courante dans l’analyse des données.
Dans Microsoft Excel, les tables de données font partie d’une suite de commandes appelées outils d’analyse What-If. Lorsque vous construisez et analysez des tables de données, vous effectuez une analyse de scénario.
L’analyse de scénarios est le processus de modification des valeurs dans les cellules pour voir comment ces modifications affecteront le résultat des formules dans la feuille de calcul. Par exemple, vous pouvez utiliser une table de données pour faire varier le taux d’intérêt et la durée d’un prêt, afin d’évaluer les montants potentiels des paiements mensuels.
Types d’analyse de scénarios
Il existe trois types d’outils d’analyse de scénarios dans Excel : scénarios, tableaux de données et recherche d’objectifs. Les scénarios et les tables de données utilisent des ensembles de valeurs d’entrée pour calculer les résultats possibles. La recherche d’objectifs est distinctement différente, elle utilise un résultat unique et calcule les valeurs d’entrée possibles qui produiraient ce résultat.
À l’instar des scénarios, les tables de données vous aident à explorer un ensemble de résultats possibles. Contrairement aux scénarios, les tables de données affichent tous les résultats dans une table sur une feuille de calcul. L’utilisation de tables de données permet d’examiner facilement une gamme de possibilités en un coup d’œil. Étant donné que vous vous concentrez uniquement sur une ou deux variables, les résultats sont faciles à lire et à partager sous forme tabulaire.
Une table de données ne peut pas prendre en charge plus de deux variables. Si vous souhaitez analyser plus de deux variables, vous devez plutôt utiliser des scénarios. Bien qu’elle soit limitée à une ou deux variables (une pour la cellule d’entrée de ligne et une pour la cellule d’entrée de colonne), une table de données peut inclure autant de valeurs de variables différentes que vous le souhaitez. Un scénario peut avoir un maximum de 32 valeurs différentes, mais vous pouvez créer autant de scénarios que vous le souhaitez.
Pour plus d’informations, consultez l’article Présentation de l’analyse What-If.
Créez des tables de données à une ou deux variables, en fonction du nombre de variables et de formules que vous devez tester.
Tables de données à une variable
Utilisez une table de données à une variable si vous souhaitez voir comment les différentes valeurs d’une variable dans une ou plusieurs formules modifient les résultats de ces formules. Par exemple, vous pouvez utiliser une table de données à une variable pour voir comment les différents taux d’intérêt affectent un paiement hypothécaire mensuel à l’aide de la fonction PMT. Vous entrez les valeurs de variable dans une colonne ou une ligne, et les résultats sont affichés dans une colonne ou une ligne adjacente.
Dans l’illustration suivante, la cellule D2 contient la formule de paiement, =PMT(B3/12,B4,-B5), qui fait référence à la cellule d’entrée B3.
Tables de données à deux variables
Utilisez une table de données à deux variables pour voir comment les différentes valeurs de deux variables dans une formule modifient les résultats de cette formule. Par exemple, vous pouvez utiliser une table de données à deux variables pour voir comment différentes combinaisons de taux d’intérêt et de conditions de prêt affecteront un paiement hypothécaire mensuel.
Dans l’illustration suivante, la cellule C2 contient la formule de paiement, =PMT(B3/12,B4,-B5), qui utilise deux cellules d’entrée, B3 et B4.
Calculs de table de données
Chaque fois qu’une feuille de calcul est recalculée, toutes les tables de données sont également recalculées, même si aucune modification n’a été apportée aux données. Pour accélérer le calcul d’une feuille de calcul qui contient une table de données, vous pouvez modifier les options de calcul pour recalculer automatiquement la feuille de calcul, mais pas les tables de données. Pour plus d’informations, consultez la section Accélérer le calcul dans une feuille de calcul qui contient des tables de données.
Une table de données à une variable contient ses valeurs d’entrée dans une seule colonne (orientée colonne) ou sur une ligne (orientée ligne). Toute formule d’une table de données à une variable ne doit faire référence qu’à un seul cellule d’entrée.
Procédez comme suit :
-
Tapez la liste des valeurs que vous souhaitez remplacer dans la cellule d’entrée( vers le bas d’une colonne ou sur une ligne). Laissez quelques lignes et colonnes vides de chaque côté des valeurs.
-
Effectuez l’une des opérations suivantes :
-
Si la table de données est orientée colonne (vos valeurs de variable se trouvent dans une colonne), tapez la formule dans la cellule une ligne au-dessus et une cellule à droite de la colonne de valeurs. Cette table de données à une variable est orientée colonne et la formule est contenue dans la cellule D2.
-
Si la table de données est orientée ligne (vos valeurs de variable se trouvent dans une ligne), tapez la formule dans la cellule une colonne à gauche de la première valeur et une cellule sous la ligne de valeurs.Si vous souhaitez examiner les effets de différentes valeurs sur d’autres formules, entrez les formules supplémentaires dans les cellules situées sous la première formule.
-
-
Sélectionnez la plage de cellules qui contient les formules et les valeurs que vous souhaitez remplacer. Dans la figure ci-dessus, cette plage est C2 :D5.
-
Sous l’onglet Données , sélectionnez Analyse de scénarios > Table de données (dans le groupe Outils de données ou le groupe Prévision de Excel 2016 ).
-
Effectuez l’une des opérations suivantes :
-
Si la table de données est orientée colonne, entrez le référence de cellule de la cellule d’entrée dans le champ Cellule d’entrée colonne . Dans la figure ci-dessus, la cellule d’entrée est B3.
-
Si la table de données est orientée ligne, entrez la référence de cellule pour la cellule d’entrée dans le champ Cellule d’entrée de ligne .
Remarque : Après avoir créé votre table de données, vous pouvez modifier le format des cellules de résultat. Dans la figure, les cellules de résultat sont mises en forme en tant que devise.
-
Les formules utilisées dans une table de données à une variable doivent faire référence à la même cellule d’entrée.
Procédez comme suit
-
Effectuez l’une des opérations suivantes :
-
Si la table de données est orientée colonne, entrez la nouvelle formule dans une cellule vide à droite d’une formule existante dans la ligne supérieure de la table de données.
-
Si la table de données est orientée ligne, entrez la nouvelle formule dans une cellule vide sous une formule existante dans la première colonne de la table de données.
-
-
Sélectionnez la plage de cellules qui contient la table de données et la nouvelle formule.
-
Sous l’onglet Données , sélectionnez Analyse de scénarios> Table de données (dans le groupe Outils de données ou le groupe Prévision de Excel 2016 ).
-
Effectuez l’une des opérations suivantes :
-
Si la table de données est orientée colonne, entrez la référence de cellule pour la cellule d’entrée dans la zone Cellule d’entrée colonne .
-
Si la table de données est orientée ligne, entrez la référence de cellule pour la cellule d’entrée dans la zone Cellule d’entrée de ligne.
-
Une table de données à deux variables utilise une formule qui contient deux listes de valeurs d’entrée. La formule doit faire référence à deux cellules d’entrée différentes.
Procédez comme suit :
-
Dans une cellule de la feuille de calcul, entrez la formule qui fait référence aux deux cellules d’entrée.
Dans l’exemple suivant, dans lequel les valeurs de départ de formule sont entrées dans les cellules B3, B4 et B5, vous tapez la formule =PMT(B3/12 ;B4,-B5) dans la cellule C2.
-
Tapez une liste de valeurs d’entrée dans la même colonne, sous la formule.
Dans ce cas, tapez les différents taux d’intérêt dans les cellules C3, C4 et C5.
-
Entrez la deuxième liste dans la même ligne que la formule, à sa droite.
Tapez les conditions du prêt (en mois) dans les cellules D2 et E2.
-
Sélectionnez la plage de cellules qui contient la formule (C2), la ligne et la colonne des valeurs (C3 :C5 et D2 :E2) et les cellules dans lesquelles vous voulez les valeurs calculées (D3 :E5).
Dans ce cas, sélectionnez la plage C2 :E5.
-
Sous l’onglet Données , dans le groupe Outils de données ou le groupe Prévisions (dans Excel 2016 ), sélectionnez Analyse de scénarios > Table de données (dans le groupe Outils de données ou le groupe Prévision de Excel 2016 ).
-
Dans le champ Cellule d’entrée de ligne, entrez la référence à la cellule d’entrée pour les valeurs d’entrée dans la ligne.Tapez la cellule B4 dans la zone Cellule d’entrée de ligne.
-
Dans le champ Cellule d’entrée colonne , entrez la référence à la cellule d’entrée pour les valeurs d’entrée dans la colonne.Tapez B3 dans la zone cellule d’entrée colonne .
-
Sélectionnez OK.
Exemple de table de données à deux variables
Une table de données à deux variables peut montrer comment différentes combinaisons de taux d’intérêt et de conditions de prêt affecteront un paiement hypothécaire mensuel. Dans la figure ici, la cellule C2 contient la formule de paiement, =PMT(B3/12,B4,-B5), qui utilise deux cellules d’entrée, B3 et B4.
Lorsque vous définissez cette option de calcul, aucun calcul de table de données ne se produit lorsqu’un recalcul est effectué sur l’ensemble du classeur. Pour recalculer manuellement votre table de données, sélectionnez ses formules, puis appuyez sur F9.
Suivez ces étapes pour améliorer les performances de calcul :
-
Sélectionnez Options > de fichiers > Formules.
-
Dans la section Options de calcul , sélectionnez Automatique.
Conseil : Si vous le souhaitez, sous l’onglet Formules , sélectionnez la flèche options de calcul, puis sélectionnez Automatique.
Vous pouvez utiliser quelques autres outils Excel pour effectuer une analyse de scénario si vous avez des objectifs spécifiques ou des ensembles de données variables plus volumineux.
Valeur cible
Si vous connaissez le résultat à attendre d’une formule, mais que vous ne savez pas précisément quelle valeur d’entrée la formule a besoin pour obtenir ce résultat, utilisez la fonctionnalité Goal-Seek. Consultez l’article Utiliser la recherche d’objectif pour trouver le résultat souhaité en ajustant une valeur d’entrée.
Solveur Excel
Vous pouvez utiliser le complément Solveur Excel pour trouver la valeur optimale pour un ensemble de variables d’entrée. Le solveur fonctionne avec un groupe de cellules (appelées variables de décision, ou simplement cellules variables) qui sont utilisées dans le calcul des formules dans les cellules d’objectif et de contrainte. Le Solveur affine les valeurs des cellules variables de décision pour satisfaire aux limites appliquées aux cellules de contraintes et produire le résultat souhaité pour la cellule objectif. Pour plus d’informations, consultez cet article : Définir et résoudre un problème à l’aide du solveur.
En branchant différents nombres dans une cellule, vous pouvez rapidement trouver différentes réponses à un problème. Un bon exemple est l’utilisation de la fonction PMT avec différents taux d’intérêt et périodes de prêt (en mois) pour déterminer le montant d’un prêt que vous pouvez vous permettre pour une maison ou une voiture. Vous entrez vos nombres dans une plage de cellules appelée table de données.
Ici, la table de données correspond à la plage de cellules B2 :D8. Vous pouvez modifier la valeur en B4, le montant du prêt et les paiements mensuels de la colonne D sont automatiquement mis à jour. En utilisant un taux d’intérêt de 3,75 %, D2 retourne un paiement mensuel de 1 042,01 $ selon la formule suivante : =PMT(C2/12,$B 3 $B$4).
Vous pouvez utiliser une ou deux variables, en fonction du nombre de variables et de formules que vous souhaitez tester.
Utilisez un test à une variable pour voir comment les différentes valeurs d’une variable dans une formule modifient les résultats. Par exemple, vous pouvez modifier le taux d’intérêt d’un paiement hypothécaire mensuel à l’aide de la fonction PMT. Vous entrez les valeurs de variable (taux d’intérêt) dans une colonne ou une ligne, et les résultats sont affichés dans une colonne ou une ligne à proximité.
Dans ce classeur en direct, la cellule D2 contient la formule de paiement =PMT(C2/12,$B$3,$B$4). La cellule B3 est la cellule variable , dans laquelle vous pouvez ajouter une durée différente (nombre de périodes de paiement mensuel). Dans la cellule D2, la fonction PMT intègre le taux d’intérêt de 3,75 %/12, 360 mois et un prêt de 225 000 $, et calcule un paiement mensuel de 1 042,01 $.
Utilisez un test à deux variables pour voir comment les valeurs différentes de deux variables dans une formule modifient les résultats. Par exemple, vous pouvez tester différentes combinaisons de taux d’intérêt et de périodes de paiement mensuel pour calculer un paiement hypothécaire.
Dans ce classeur en direct, la cellule C3 contient la formule de paiement, =PMT($B$3/12,$B$2,B4), qui utilise deux cellules variables, B2 et B3. Dans la cellule C2, la fonction PMT intègre le taux d’intérêt de 3,875 %/12, 360 mois et un prêt de 225 000 $, et calcule un paiement mensuel de 1 058,03 $.
Vous avez besoin d’une aide supplémentaire ?
Vous pouvez toujours poser des questions à un expert de la Communauté technique Excel ou obtenir une assistance dans la Communauté de support.
