Table de Données, Plusieurs Résultats pour un Calcul

Dans Microsoft Excel, les tables de données font partie d’une suite de commandes appelée outils d’analyse de scénarios. Lors de la création et de l’analyse de tables de données, vous effectuez une analyse de scénarios.

Dans le cas d’une analyse de scénarios, le processus de modification des valeurs de la cellule s’applique aux formules dans la feuille de calcul. Par exemple, vous pouvez utiliser une table de données pour différencier le taux d’intérêt et la durée d’un emprunt, afin d’évaluer les montants de vos paiements mensuels potentiels.

Types d’analyses de scénarios

Il existe trois types d’outils d’analyse de scénarios dans Excel : les scénarios, les tables de données et la recherche de buts. Les tables de scénarios et de données utilisent des ensembles de valeurs d’entrée pour calculer les résultats possibles. Objectif-la recherche est différente, elle utilise un résultat unique et calcule les valeurs d’entrée possibles qui génèrent ce résultat.

Comme dans les scénarios, les tables de données vous permettent d’explorer une série de résultats possibles. Contrairement aux scénarios, les tables de données vous montrent tous les résultats d’une table dans une feuille de calcul. L’utilisation de tables de données permet d’examiner facilement une gamme de possibilités en un clin d’œil. Étant donné que vous vous focalisez sur une ou deux variables, vous pouvez facilement lire et partager les résultats sous forme tabulaire.

Une table de données ne peut pas accueillir plus de deux variables. Si vous souhaitez analyser plus de deux variables, il est préférable d’utiliser des scénarios. Même s’il est limité à une ou deux variables (une pour la cellule d’entrée en ligne et une pour la cellule d’entrée en colonne), une table de données peut inclure autant de valeurs de variables différentes que vous le souhaitez. Un scénario peut avoir au maximum 32 valeurs différentes, mais vous pouvez créer autant de scénarios que vous le souhaitez.

Pour plus d’informations, consultez l’article Introduction à l’analyse de scénarios.

Créez une table 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 la façon dont les différentes valeurs d’une variable dans une ou plusieurs formules changent les résultats de ces formules. Par exemple, vous pouvez utiliser une table de données à une variable pour visualiser la façon dont les différents taux d’intérêt affectent le remboursement d’un emprunt mensuel en utilisant la fonction VPM. Vous entrez les valeurs des variables 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 remboursement = 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 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 visualiser la façon dont les combinaisons de taux d’intérêt et de conditions de prêts peuvent affecter le remboursement d’un emprunt mensuel.

Dans l’illustration suivante, la cellule C2 contient la formule de remboursement = VPM (B3/12 ; B4 ;-B5)qui utilise deux cellules d’entrée, B3 et B4.

Calculs de tables de données

À chaque recalcul de la feuille de calcul, les tables de données sont également recalculées, même en cas de modification des 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, reportez-vous à la section accélérer le calcul dans une feuille de calcul contenant 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). Les formules d’une table de données à une variable doivent faire référence à une seule cellule d’entrée.

Procédez comme suit :

1. Tapez la liste de valeurs que vous souhaitez remplacer dans la cellule d’entrée, soit au-dessous d’une colonne ou d’une ligne. Laissez quelques lignes et colonnes vides de chaque côté des valeurs.
2. Effectuez l’une des opérations suivantes :
   • S’il s’agit d’une table de données orientée en colonne (les valeurs de ses variables se trouvent dans une colonne), tapez la formule dans la cellule d’une ligne au-dessus et une cellule à droite de la colonne de valeurs. Cette table de données à une variable est orientée en colonne et la formule est incluse dans la cellule D2.Si vous voulez examiner les effets de diverses valeurs dans d’autres formules, entrez les formules supplémentaires dans les cellules à droite de la première formule.
   • S’il s’agit d’une table de données orientée ligne (les valeurs de ses variables se trouvent dans une ligne), tapez la formule dans la cellule d’une colonne à gauche de la première valeur et dans une cellule située en dessous de la ligne de valeurs.Si vous voulez examiner les effets de diverses valeurs dans d’autres formules, entrez les formules supplémentaires dans les cellules en dessous de la première formule.
3. Sélectionnez la plage de cellules qui contient les formules et les valeurs que vous souhaitez remplacer. Dans l’illustration ci-dessus, il s’agit de C2 : D5.
4. Sous l’ onglet données , cliquez sur 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 :
   • S’il s’agit d’une table de données orientée en colonne, entrez la référence de cellule de la cellule d’entrée dans le champ cellule d’entrée en colonne . Dans l’illustration ci-dessus, la cellule d’entrée est B3.
   • S’il s’agit d’une table de données orientée ligne, entrez la référence de cellule de la cellule d’entrée dans le champ cellule d’entrée en ligne .

     Remarque : Après avoir créé votre table de données, vous souhaiterez peut-être modifier le format des cellules de résultat. Dans la figure, les cellules de résultat sont mises en forme en tant que devises.

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. Effectuez l’une des opérations suivantes :
   • S’il s’agit d’une table de données orientée en colonne, entrez la nouvelle formule dans une cellule vide située à droite de la formule existante dans la ligne supérieure de la table de données.
   • S’il s’agit d’une table de données 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.
2. Sélectionnez la plage de cellules qui contient la table de données et la nouvelle formule.
3. Sous l’onglet données , cliquez sur analyse de scénarios> table de données (dans le groupe outils de données ou le groupe prévision de Excel 2016 ).
4. Effectuez l’une des opérations suivantes :
   • S’il s’agit d’une table de données orientée en colonne, entrez la référence de cellule de la cellule d’entrée dans la zone cellule d’entrée en colonne .
   • S’il s’agit d’une table de données orientée ligne, entrez la référence de cellule de la cellule d’entrée dans la zone cellule d’entrée en 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, entrez la formule faisant référence aux deux cellules d’entrée.Dans l’exemple suivant, dans lequel les valeurs de départ de la formule sont tapées dans les cellules B3, B4 et B5, tapez la formule = VPM (B3/12 ; B4 ;-B5) dans la cellule C2.
2. 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.
3. Entrez la seconde liste dans la même ligne que la formule, à sa droite.Tapez les modalités du prêt (en mois) dans les cellules D2 et E2.
4. Sélectionnez la plage de cellules qui contient la formule (C2), la ligne et la colonne de valeurs (C3 : C5 et D2 : E2) et les cellules dans lesquelles vous voulez obtenir les valeurs calculées (D3 : E5).Dans ce cas, sélectionnez la plage C2 : E5.
5. Dans l’onglet données , dans le groupe outils de données ou prévision (dans Excel 2016 ), cliquez sur 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 en ligne , entrez la référence de 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 en ligne .
7. Dans le champ cellule d’entrée en colonne , entrez la référence de la cellule d’entrée pour les valeurs d’entrée dans la colonne.
   Tapez B3 dans la zone cellule d’entrée en colonne .
8. Cliquez sur OK.

Exemple de table de données à deux variables

Une table de données à deux variables peut montrer la façon dont différentes combinaisons de taux d’intérêt et de conditions de prêts peuvent affecter une mensualité d’emprunt. Dans la figure ci-dessous, 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électionnez ses formules, puis appuyez sur F9.

Pour améliorer les performances de calcul, procédez comme suit :

1. Effectuez l’une des opérations suivantes :
   • Dans Excel 2007, cliquez sur le bouton Microsoft Office  , sur Options Excel, puis sur la catégorie formules .
   • Dans les autres versions, cliquez sur options de > de fichier > formules.
2. Dans la section mode de calcul , sous calculer, cliquez sur automatique sauf dans les tableaux de données.

   Conseil : Dans l’onglet formules , vous pouvez également cliquer sur la flèche des options de calcul, puis cliquer sur automatique sauf les tables de données (dans le groupe calcul ).

Vous pouvez utiliser d’autres outils Excel pour effectuer des analyses de scénarios si vous avez des buts spécifiques ou des ensembles de données variables de grande taille.

Valeur cible

Si vous connaissez le résultat que vous souhaitez obtenir d’une formule, mais que vous ne connaissez pas précisément la valeur d’entrée dont la formule a besoin pour obtenir ce résultat, utilisez la fonctionnalité valeur cible. Consultez l’article utiliser la fonctionnalité valeur cible pour rechercher le résultat souhaité en ajustant une valeur d’entrée.

Solveur Excel

Vous pouvez utiliser le complément Excel Solver pour rechercher la valeur optimale d’un ensemble de variables d’entrée. Le solveur fonctionne avec un groupe de cellules (appelés variables de décision ou simplement des cellules variables) qui sont utilisés dans le calcul des formules dans les cellules objectif et 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.