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

Une table de données est une plage de cellules dans laquelle vous pouvez modifier les valeurs d’un certain nombre de certaines cellules et faire apparaître des réponses différentes à un problème. Le bon exemple de table de données utilise la fonction VPM avec différents montants d’emprunt et taux d’intérêt pour calculer le montant abordable sur un prêt immobilier. L’expérimentation de valeurs différentes pour observer la variance correspondante dans les résultats est une tâche courante lors de l' analyse des données.

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.

Remarque : Vous pouvez effectuer des calculs plus rapides avec les tables de données et Visual Basic pour applications (VBA). Pour plus d’informations, reportez-vous à la rubrique table de données scénarios Excel : calcul plus rapide avec VBA.

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éeset 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.

Table de données à une variable

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.

Data table with two variables
 

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.

      Table de données à une variable

      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.

Data table with two variables

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 Image du bouton 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.

En connectant différents numéros dans une cellule, vous pouvez rapidement faire apparaître des réponses différentes à un problème. Par exemple, l’utilisation de la fonction VPM avec différents taux d’intérêt et périodes de crédits (en mois) vous permet de déterminer le nombre de crédits que vous pouvez vous offrir pour une maison ou une voiture. 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 de la cellule B4, le montant de l’emprunt et les échéances mensuelles dans la colonne D, puis mettre à jour automatiquement. À l’aide d’un taux d’intérêt de 3,75%, D2 renvoie un paiement mensuel d' $1 042,01 à l’aide de la formule suivante : = VPM (C2/12, $B $3 $B $4).

La plage de cellules B2:D8 est une table de données

Vous pouvez utiliser une ou deux variables, suivant le nombre de variables et de formules que vous voulez tester.

Utilisez un test à une variable pour vérifier la manière dont les différentes valeurs d’une variable dans une formule changent les résultats. Par exemple, vous pouvez modifier le taux d’intérêt d’un remboursement mensuel d’un prêt hypothécaire à l’aide de la fonction VPM. Entrez les valeurs des variables (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 actif, la cellule D2 contient la formule de remboursement =VPM (C2/12, $B $3, $B $4). La cellule B3 est la cellule variable dans laquelle vous pouvez brancher une longueur de terme différente (nombre de périodes de remboursement mensuelles). Dans la cellule D2, la fonction VPM se connecte au taux d’intérêt 3,75%/12, 360 mois et un emprunt $225 000 et calcule un paiement mensuel de $1 042,01.

Utilisez un test à deux variables pour voir comment différentes valeurs 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 le nombre de périodes de remboursement mensuelles pour calculer le remboursement d’un emprunt hypothécaire.

Dans ce classeur actif, la cellule C3 contient la formule de paiement, =vpm ($B $3/12, $B $2, B4), qui utilise deux cellules variables, B2 et B3. Dans la cellule C2, la fonction VPM se connecte au taux d’intérêt 3.875 de 12, de 360 mois et un emprunt $225 000, et calcule un paiement mensuel de $1 058,03.

Vous avez besoin d’une aide supplémentaire ?

Vous pouvez toujours consulter un expert de la communauté technique Excel, obtenir une assistance dans la communauté Answers ou suggérer une nouvelle fonctionnalité ou une amélioration sur le forum Excel User Voice.

Développez vos compétences dans Office
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×