Tableau de données (analyse de sensibilité)
Le tableau de données calcule automatiquement plusieurs résultats d'une formule en faisant varier une ou deux variables d'entrée, créant ainsi une table de sensibilité. Tu l'insères via Données > Analyse de scénarios > Table de données. Il est parfait pour afficher comment un résultat (bénéfice, mensualité) évolue selon différentes hypothèses.
À quoi sert un tableau de données (analyse de sensibilité) dans Excel ?
Le tableau de données te permet de voir d'un coup d'oeil comment un résultat évolue quand tu fais varier une ou deux hypothèses. Au lieu de modifier manuellement une cellule, noter le résultat, remettre une autre valeur, re-noter, etc., le tableau de données calcule automatiquement tous les résultats pour toutes les valeurs que tu veux tester, et les affiche dans un tableau compact.
Tu vas l'utiliser chaque fois que tu veux tester la sensibilité d'un modèle à ses paramètres : comment évolue la mensualité d'un prêt selon le taux et la durée, comment varie la marge brute selon le prix de vente et le volume, ou quel est l'impact de différents taux de conversion sur ton chiffre d'affaires prévisionnel. C'est l'outil parfait pour préparer des scénarios chiffrés à présenter en réunion.
Tableau de données (analyse de sensibilité) : exemples concrets
Exemple 1 : Responsable marketing : tester l'impact du budget pub sur le ROI
Tu es responsable marketing dans une startup SaaS. 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 un tableau de données à une variable, tu listes tes 10 paliers de budget dans une colonne, tu relies la formule de ROI au tableau, 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), le tableau de données se recalcule automatiquement.
Exemple 2 : Acheteur : comparer les conditions de paiement fournisseurs
Tu es acheteur dans une entreprise industrielle. Tu négocies un contrat avec un fournisseur et tu veux comparer l'impact financier de différentes combinaisons de remise commerciale (de 0 % à 15 %) et de délai de paiement (30, 60, 90 jours). Ta formule calcule le coût net ajusté en intégrant le coût du financement lié au délai de paiement.
Avec un tableau de données à deux variables, tu places les pourcentages de remise en ligne et les délais de paiement en colonne. Excel remplit la grille avec le coût net pour chaque combinaison. Tu obtiens une matrice complète qui montre, par exemple, qu'une remise de 8 % à 60 jours revient moins cher qu'une remise de 12 % à 90 jours à cause du coût du financement.
Cette matrice te sert directement en négociation : tu peux montrer au fournisseur que certaines combinaisons sont équivalentes et lui proposer celle qui l'arrange le mieux tout en restant dans ton budget.
- #1 Pour un tableau à une variable, place tes valeurs de test dans une colonne et la formule une ligne au-dessus et une colonne à droite. Sélectionne tout le bloc puis va dans Données > Analyse de scénarios > Table de données.
- #2 Pour un tableau à deux variables, place les valeurs d'une variable en ligne (première ligne du bloc) et celles de l'autre en colonne (première colonne). La formule va dans la cellule à l'intersection (coin supérieur gauche).
- #3 Les tableaux de données se recalculent à chaque modification du classeur, ce qui peut ralentir Excel sur des fichiers lourds. Si c'est le cas, passe le calcul en mode "Automatique sauf les tables de données" dans Formules > Options de calcul.
Tu ne peux pas supprimer ou modifier individuellement les cellules calculées à l'intérieur d'un tableau de données. Si tu dois changer une valeur, supprime tout le bloc de résultats d'un coup (sélectionne les cellules calculées, puis Suppr) et relance la table de données.
Les erreurs courantes avec le tableau de données (analyse de sensibilité)
✕ Confondre la cellule d'entrée en ligne et la cellule d'entrée en colonne
Dans un tableau de données à deux variables, Excel te demande deux cellules d'entrée : une pour la ligne et une pour la colonne. Si tu inverses les deux, les résultats sont mathématiquement corrects mais les axes de ta matrice sont inversés. Le résultat qui devrait correspondre à une remise de 10 % et un délai de 30 jours correspond en réalité à l'inverse.
L'erreur passe facilement inaperçue parce que les chiffres dans la grille ont l'air plausibles. Tu ne la détectes que si tu vérifies manuellement un résultat en modifiant les cellules d'entrée une par une.
Solution : Retiens cette règle : la cellule d'entrée en ligne correspond aux valeurs placées dans la première ligne du tableau, et la cellule d'entrée en colonne correspond aux valeurs dans la première colonne. Vérifie toujours un résultat du tableau en entrant manuellement les valeurs correspondantes dans ton modèle.
✕ Placer la formule au mauvais endroit dans le bloc du tableau
Pour un tableau à une variable, la formule doit être positionnée dans la cellule adjacente à la première valeur de test (une ligne au-dessus si tes valeurs sont en colonne, ou une colonne à gauche si tes valeurs sont en ligne). Si tu places la formule ailleurs, Excel ne comprend pas la structure et remplit le tableau avec des résultats identiques ou des erreurs.
C'est une erreur de positionnement qui ne génère aucun message d'erreur. Excel calcule quelque chose, mais ce n'est pas ce que tu attends. Tu te retrouves avec un tableau où tous les résultats sont les mêmes, ce qui devrait t'alerter.
Solution : Suis la disposition standard : pour un tableau à une variable verticale, tes valeurs de test vont dans la colonne A (par exemple A3:A12), et ta formule va dans la cellule B2 (une ligne au-dessus de la première valeur, une colonne à droite). Sélectionne ensuite tout le bloc A2:B12 avant de lancer la table de données.
Questions fréquentes sur le tableau de données (analyse de sensibilité)
Prépare d'abord ton modèle avec une formule qui dépend d'une cellule d'entrée. Liste les valeurs que tu veux tester dans une colonne ou une ligne, et place la formule à l'intersection. Sélectionne tout le bloc (valeurs de test + cellule de formule + zone de résultats vide), puis va dans Données > Analyse de scénarios > Table de données. Indique la cellule d'entrée correspondante et valide. Excel remplit instantanément tous les résultats.
La valeur cible répond à une question unique : "Quelle valeur d'entrée me donne ce résultat précis ?". Le tableau de données répond à une question plus large : "Comment évolue mon résultat pour toute une série de valeurs d'entrée ?". La valeur cible cherche un point précis, le tableau de données cartographie toute la courbe. Les deux sont complémentaires : utilise le tableau de données pour explorer, et la valeur cible pour trouver un seuil exact.
Non, Excel limite le tableau de données à deux variables maximum (une en ligne, une en colonne). Si tu dois analyser l'impact de trois variables ou plus, tu peux créer plusieurs tableaux de données (un par paire de variables), ou utiliser le Gestionnaire de scénarios qui permet de stocker des jeux complets de valeurs pour autant de cellules que tu veux. Le Solveur est une autre option pour les problèmes multivariables.
Termes liés
Envie d'aller plus loin ?
Explore les 293 termes de notre lexique ou découvre nos formules Excel.