Calculer plusieurs résultats à l’aide d’une table de données

Dans Microsoft Excel, les tables de données font partie d’une suite de commandes appelées outils d’analyse d’évaluation de scénarios. Lorsque vous construisez et analysez des tables de données, vous effectuez une analyse d’évaluation de scénarios.

L’analyse d’évaluation de scénarios est un processus consistant à modifier des valeurs dans des cellules afin de voir l’incidence de ces modifications sur 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 d’évaluation de scénarios    

Il existe trois types d’outils d’analyse d’évaluation de scénarios dans Excel : scénarios, tables 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 un tableau 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 contenir plus de deux variables. Si vous voulez analyser plus de deux variables, vous pouvez utiliser la fonctionnalité 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 ne peut pas contenir plus 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 d’évaluation de scénarios.

Créer 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    

Utiliser 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 différents taux d’intérêt affectent un paiement hypothécaire mensuel à l’aide de la fonction VPM. 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, =VPM(B3/12,B4,-B5), qui fait référence à la cellule d’entrée B3.

Tables de données à deux variables    

Utiliser 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, =VPM(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 :

1. Taper la liste des valeurs que vous souhaitez remplacer dans la cellule d’entrée (vers le bas d’une colonne ou sur une ligne). Laisser quelques lignes et colonnes vides de chaque côté des valeurs.

2. 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 vous souhaitez examiner les effets de différentes valeurs sur d’autres formules, entrer les formules supplémentaires dans les cellules situées à droite de la première formule.

   • Si la table de données est orientée ligne (vos valeurs de variable se trouvent dans une ligne), taper 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, entrer les formules supplémentaires dans les cellules situées sous la première formule.

3. Sélectionner la cellule ou la plage de cellules qui contient les valeurs, les formats de cellule ou les formules que vous souhaitez remplacer. Dans la figure ci-dessus, cette plage est C2:D5.

4. Sous l’onglet Données, sélectionner Analyse de scénarios > Table de données (dans le groupe Outils de données ou le groupe Prévision de Excel 2016).

5. Effectuez l’une des opérations suivantes :

   • Si la table de données est orientée colonne, entrer 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, entrer 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

1. Effectuer l’une des opérations suivantes :

   • Si la table de données est orientée colonne, entrer 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, entrer la nouvelle formule dans une cellule vide sous une formule existante dans la première colonne de la table de données.

2. Sélectionner la plage de cellules qui contient la table de données et la nouvelle formule.

3. 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).

4. Effectuer l’une des opérations suivantes :

   • Si la table de données est orientée colonne, entrer 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, entrer 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 :

1. Dans une cellule de la feuille de calcul, entrer 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 =VPM(B3/12 ;B4,-B5) dans la cellule C2.

2. Taper une liste de valeurs d’entrée dans la même colonne, sous la formule.

   Dans ce cas, taper les différents taux d’intérêt dans les cellules C3, C4 et C5.

3. Entrer la deuxième liste dans la même ligne que la formule, à sa droite.

   Taper les conditions du prêt (en mois) dans les cellules D2 et E2.

4. Sélectionner 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.

5. Sous l’onglet Données, dans le groupe Outils de données ou le groupe Prévisions (dans Excel 2016), sélectionner Analyse de scénarios > Table de données (dans le groupe Outils de données ou le groupe Prévision de Excel 2016).

6. Dans le champ Cellule d’entrée de ligne, entrer la référence à la cellule d’entrée pour les valeurs d’entrée dans la ligne.
   Taper la cellule B4 dans la zone Cellule d’entrée de ligne.

7. 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.
   Taper B3 dans la zone Cellule d’entrée colonne.

8. 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, =VPM(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électionner ses formules, puis appuyer sur F9.

Suivre ces étapes pour améliorer les performances de calcul :

1. Sélectionner Fichier > Options > Formules.

2. Dans la section Options de calcul , sélectionner 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 que vous souhaitez obtenir d’une formule, mais n’êtes pas sûr de la valeur d’entrée dont la formule a besoin pour produire ce résultat, vous pouvez utiliser la fonctionnalité Valeur cible. Voir l’article Utiliser la valeur cible pour trouver le résultat souhaité en ajustant une valeur d’entrée.

Solutionneur Excel

Vous pouvez utiliser le complément Solutionneur Excel pour trouver la valeur optimale pour un ensemble de variables d’entrée. Le Solutionneur utilise un groupe de cellules, appelées variables de décision ou simplement cellules variables, qui interviennent dans le calcul des formules des cellules objectif et de contraintes. 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, consulter cet article : Définir et résoudre un problème à l’aide du solutionneur.