C'est quoi une table de données dans Excel ?
La table de données est l'outil d'analyse de scénarios qui calcule d'un coup plusieurs résultats d'une même formule, en faisant varier une ou deux valeurs d'entrée. Elle affiche ces résultats dans une grille, ce qu'on appelle aussi une table de sensibilité.
À quoi sert une table de données dans Excel
La table de données te sert à mesurer la sensibilité d'un modèle, c'est-à-dire à voir comment un résultat évolue en changeant une ou deux de ses hypothèses. Tu la laisses parcourir toute une série de valeurs et remplir la grille à ta place, au lieu de tester chaque cas toi-même.
Fait à la main, l'exercice tourne vite à la corvée : tu saisis une valeur, tu relèves le résultat, tu en changes, tu relèves encore, et tu recommences pour chaque hypothèse. La table de données calcule tout d'un coup et pose les résultats côte à côte, prêts à comparer.
C'est l'outil des projections chiffrées, comme la mensualité d'un prêt selon le taux et la durée, ou la marge selon le prix et le volume. Les financiers l'appellent d'ailleurs une analyse de sensibilité, le cœur d'un modèle qu'on présente en réunion.
Comment créer une table de données dans Excel
La table de données affiche d'un coup comment un résultat varie selon une ou deux entrées. Prépare une grille autour de ta formule, puis laisse Excel remplir toutes les combinaisons à ta place.
- 1Construis la grille, place la référence à ta formule dans la cellule d'angle en haut à gauche, puis les valeurs d'entrée à tester le long d'une ligne, d'une colonne, ou des deux.
- 2Sélectionne toute la grille, formule d'angle et valeurs d'entrée comprises, en incluant les cellules vides qui recevront les résultats.
- 3Ouvre Données puis Analyse scénarios puis Table de données pour lancer l'outil.
- 4Renseigne « Cellule d'entrée en ligne » et/ou « Cellule d'entrée en colonne » en pointant les cellules variables d'origine de ton modèle, puis valide, Excel remplit toutes les combinaisons.
- 5Change ensuite n'importe quelle valeur d'entrée : toute la grille se recalcule d'un coup, sans refaire la manipulation.
Quelle différence entre une table de données à une et à deux variables
Tout se joue sur le nombre d'hypothèses que tu fais varier en même temps. Le mécanisme reste identique, seules la disposition de la grille et la place de la formule changent d'un cas à l'autre.
Une variable
Tu ne fais bouger qu'une seule entrée, un taux par exemple, dont tu alignes les valeurs le long d'une colonne ou d'une ligne. La formule à suivre se pose sur le bord de cette liste, et Excel rend un résultat en face de chaque valeur. Rien ne t'oblige à t'arrêter à une seule formule : pose-en d'autres à côté, la mensualité puis les intérêts totaux, et la table remplit chaque colonne pour toutes tes valeurs.
Deux variables
Tu croises deux entrées à la fois, un taux en colonne et une durée en ligne. La formule quitte alors le bord pour la cellule d'angle, en haut à gauche, là où la ligne et la colonne de valeurs se rejoignent. Excel calcule chaque intersection et te rend un tableau à double entrée, une valeur pour chaque paire de taux et de durée.
{=TABLE(ligne;colonne)} entre accolades. C'est une fonction matricielle qu'Excel écrit lui-même dès que tu valides la commande, et qui recalcule tout le bloc en une seule fois.
Tester l'impact du budget pub sur le ROI
Tu as un modèle de calcul du ROI publicitaire : une formule qui prend en entrée le budget mensuel dépensé et qui calcule le nombre de leads générés, le taux de conversion, le revenu et le ROI final. Tu veux montrer à la direction comment le ROI évolue pour des budgets allant de 5 000 à 50 000 euros par paliers de 5 000.
Avec une table de données à une variable, tu listes tes 10 paliers de budget dans une colonne, tu relies la formule de ROI à la table, et Excel calcule instantanément les 10 résultats. Tu vois que le ROI est maximal autour de 25 000 euros et commence à décroître au-delà, ce qui t'aide à justifier ton budget optimal.
Tu peux ensuite transformer ce tableau en graphique pour une présentation visuelle. Chaque fois que tu ajustes les paramètres de ton modèle (coût par clic, taux de conversion), la table de données se recalcule automatiquement.
Pour aller plus loin
Termes liés : Scénario (Gestionnaire de scénarios), Valeur cible, Solveur, Formule
295 termes Excel décryptés, avec exemples et erreurs fréquentes
Questions fréquentes sur la table de données
La valeur cible part d'un objectif et cherche la seule entrée qui y mène, par exemple le taux exact qui ramène une mensualité à 900 euros. La table de données fait l'inverse : elle balaie toute une série d'entrées et te montre le résultat de chacune. L'une vise un point précis, l'autre dresse la carte complète. Sers-toi de la table de données pour explorer, et de la valeur cible pour verrouiller un seuil.
Non, Excel plafonne la table de données à deux variables, une en ligne et une en colonne. Pour tester trois entrées ou plus, tu peux monter plusieurs tables, une par paire de variables, ou passer au gestionnaire de scénarios, qui stocke des jeux de valeurs pour un grand nombre de cellules. Le solveur reste l'option pour un vrai problème d'optimisation.
Excel refuse une cellule d'entrée posée sur un autre onglet que la table de données, et renvoie une erreur de référence non valide au moment de valider. La parade tient en une phrase : construis la table sur la feuille qui porte les cellules variables d'origine, puis ramène le résultat ailleurs si tu en as besoin. La contrainte ne vaut que pour la cellule d'entrée, pas pour les valeurs testées.
Toute la boîte à outils Excel
Formules, exercices, modèles, raccourcis, lexique : tout ce qu’il te faut pour progresser, en accès libre.
Tout voir
