Une table de données est une plage de cellules dans laquelle vous pouvez modifier les valeurs de certaines cellules et apporter des réponses différentes à un problème. Un bon exemple de table de données utilise la fonction TPM avec différents montants et taux d'intérêt pour calculer le montant abordable d'un emprunt immobilier. Tester différentes valeurs pour observer la variation correspondante dans les 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 What-If'analyse. Lorsque vous construisez et analysez des tables de données, vous analysez les analyses de faits.
L'analyse de what-if est le processus de modification des valeurs dans les cellules pour voir comment ces modifications affectent 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 emprunt afin d'évaluer les montants mensuels potentiels.
Remarque : Vous pouvez effectuer des calculs plus rapides avec des tables de données et des Visual Basic pour Applications données (VBA). Pour plus d'informations, voir Excel What-If de données : calcul plus rapide avec VBA.
Types d'analyse de what-if
Il existe trois types d'outils d'analyse de scénarios dans Excel scénarios,les tables de données et la recherche objectif. Les scénarios et les tables de données utilisent des ensembles de valeurs d'entrée pour calculer les résultats possibles. La valeur objectif est différente, elle utilise un seul résultat et calcule les valeurs d'entrée possibles qui produit ce résultat.
Comme pour les 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 montrent tous les résultats d'une table sur une feuille de calcul. L'utilisation de tables de données permet d'examiner aisé une série de possibilités en un coup d'œil. Étant donné que vous vous concentrez 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, utilisez plutôt des scénarios. Bien qu'elle soit limitée à une ou deux variables (une pour la cellule d'entrée de ligne et l'autre pour la cellule de saisie de colonne), une table de données peut inclure autant de valeurs de variable 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, voir l'article Introduction What-If Analysis.
Créez des tables de données à une variable ou à deux variables, selon le nombre de variables et de formules à tester.
Tables de données à une variable
Utilisez une table de données à une variable si vous voulez 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 mensuel d'emprunt immobilier à l'aide de la fonction PPM. Vous entrez les valeurs variables dans une colonne ou 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 des valeurs différentes 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 d'emprunt affectent le remboursement mensuel d'un emprunt immobilier.
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 les données n'ont pas été modifier. Pour accélérer le calcul d'une feuille de calcul contenant 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 en savoir plus, 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 soit dans une colonne (orientée colonne), soit en travers d'une ligne (orientée ligne). Toute formule dans une table de données à une variable doit faire référence à une seule cellule d’entrée.
Procédez comme suit :
-
Tapez la liste des valeurs que vous voulez remplacer dans la cellule d'entrée, que ce soit dans 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 variables se trouve 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 plusieurs valeurs sur d'autres formules, entrez les formules supplémentaires dans les cellules à droite de la première formule. -
Si la table de données est orientée ligne (vos valeurs variables sont dans une ligne), tapez la formule dans la cellule d'une colonne à gauche de la première valeur et d'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 sous la première formule.
-
-
Sélectionnez la plage de cellules qui contient les formules et les valeurs à remplacer. Dans l'image ci-dessus, cette plage est C2:D5.
-
Sous l'onglet Données, cliquez sur Analyse de >de données (dans le groupe Outils de données ou le groupe prévisionnel 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 de la cellule d'entrée dans le champ de cellule de saisie Colonne. Dans l'image 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 la cellule d'entrée dans le champ de cellule de saisie Ligne.
Remarque : Après avoir créé votre table de données, vous souhaitez peut-être modifier le format des cellules de résultat. Dans l'figure, les cellules de résultat sont formatées 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 de saisie.
Procédez comme suit
-
Vous pouvez :
-
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, cliquez sur Analyse de > données (dans le groupe Outils de données ou groupe Prévisions 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 la cellule d'entrée dans la zone de cellule d'entrée Colonne.
-
Si la table de données est orientée ligne, entrez la référence de la cellule d'entrée dans la zone de la cellule d'entrée 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 la 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 termes 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 de valeurs (C3:C5 et D2:E2) et les cellules dans lesquelles vous souhaitez calculer les valeurs (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 ), cliquez sur Table de données de l'analyse de >de données (dans le groupe Outils de données ou groupe prévisionnel de Excel 2016 ).
-
Dans le champ De cellule d'entrée de ligne, entrez la référence à la cellule d'entrée pour les valeurs d'entrée de la ligne.
Tapez la cellule B4 dans la zone de cellule de saisie ligne. -
Dans le champ De cellule d'entrée de colonne, entrez la référence à la cellule d'entrée pour les valeurs d'entrée de la colonne.
Tapez B3 dans la zone de la cellule d'entrée colonne. -
Cliquez sur OK.
Exemple de table de données à deux variables
Une table de données à deux variables peut vous montrer comment différentes combinaisons de taux d'intérêt et de termes d'emprunt affectent le remboursement mensuel d'un emprunt immobilier. Dans l'chiffre ci-après, 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 n'est effectué lorsqu'un recalcul est effectué sur l'intégralité du manuel. Pour recalculer manuellement votre table de données, sélectionnez ses formules, puis appuyez sur F9.
Pour améliorer les performances du calcul, suivez les étapes suivantes :
-
Cliquez sur Fichier > Options > formules.
-
Dans la section Options de calcul, sous Calculer,cliquez sur Automatique sauf pour les tables de données.
Conseil : Dans l'onglet Formules, cliquez éventuellement sur la flèche des Optionsde calcul, puis sur Automatique sauf dans les tables de données (dans le groupe Calcul).
Vous pouvez utiliser quelques autres outils Excel de base pour effectuer des analyses de cas si vous avez des objectifs spécifiques ou des ensembles plus importants de données variables.
Valeur cible
Si vous connaissez le résultat à attendre d'une formule, mais ne savez pas précisément quelle valeur d'entrée la formule a besoin pour obtenir ce résultat, utilisez la Goal-Seek résultats. Consultez l'article Utiliser la valeur objectif pour trouver le résultat voulu en ajustant une valeur d'entrée.
Excel Solveur
Vous pouvez utiliser le Excel solveur 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 pour calculer les formules dans les cellules 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. En savoir plus dans 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. Par exemple, vous pouvez utiliser la fonction TPM avec différents taux d'intérêt et périodes d'emprunt (en mois) pour déterminer le montant d'un emprunt dont vous pouvez vous permettre l'utilisation 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 est la plage de cellules B2:D8. Vous pouvez modifier automatiquement la valeur de la colonne B4, le montant du prêt et les paiements mensuels dans la colonne D. En utilisant un taux d'intérêt de 3,75 %, D2 renvoie une mensualité de 1 042,01 $ à l'aide de cette formule : =PMT(C2/12;$B$3$B$4).
Vous pouvez utiliser une ou deux variables, selon le nombre de variables et de formules que vous voulez 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 mensuel d'emprunt immobilier à l'aide de la fonction PPM. Vous entrez les valeurs variables (taux d'intérêt) dans une colonne ou ligne et les résultats sont affichés dans une colonne ou une ligne proche.
Dans ce workbook 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 brancher une durée différente (nombre de périodes de remboursement mensuels). Dans la cellule D2, la fonction PMT plug-in le taux d'intérêt 3,75 %/12, 360 mois, et un emprunt de 225 000 $, et calcule un remboursement mensuel de 1 042,01 $.
Utilisez un test à deux variables pour voir comment des 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 le nombre de périodes de remboursement mensuelles pour calculer un paiement d'emprunt immobilier.
Dans ce workbook 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 branche le taux d'intérêt 3,875 %/12, 360 mois et un emprunt de 225 000 $, et calcule un remboursement mensuel de 1 058,03 $.
Vous avez besoin d’une aide supplémentaire ?
Vous pouvez toujours consulter un expert de la communauté technique Excel ou obtenir une assistance dans la communauté Answers.
