Comment faire une table de données sur Excel

Construire une table de sensibilité à 1 variable dans Excel

Une table à une variable fait défiler une seule hypothèse et rend un résultat en face de chaque valeur. Elle répond d’un coup à la question « et si la remise montait », là où le calcul à la main oblige à saisir puis relever chaque chiffre. Le modèle de l’onglet Marge tient en cinq données, dont deux seulement vont bouger, la remise en B4 et le volume en B5.

Repérer la cellule d’entrée du modèle

La cellule d’entrée est celle qu’Excel va remplacer par chaque valeur de ta liste, l’une après l’autre, pour relever le résultat à chaque passage. Ici c’est B4, la remise, parce que la marge de B8 en dépend par la chaîne des calculs. Elle porte une valeur en dur et jamais une formule, sans quoi Excel n’aurait rien où écrire.

Placer la formule à droite et au-dessus de la liste

C’est là que presque tout se joue, et c’est aussi la raison pour laquelle cette commande passe pour impossible à réussir du premier coup. Avec des valeurs rangées en colonne, la formule ne va pas dans le coin de la grille, mais une ligne plus haut et une colonne plus à droite. Elle occupe donc B12 quand les remises descendent de A13 à A18.

La logique se retient facilement une fois qu’on la voit. Chaque résultat se lit en face de sa valeur, donc la formule se met en tête de la colonne des résultats, exactement comme un titre au-dessus d’une liste de chiffres.

Ôter la protection avant d’ouvrir la boîte

Le classeur arrive protégé, ce qui empêche d’écraser la formule du modèle d’un clic de trop, et Excel refuse justement de créer une table de données sur une feuille protégée. Son message renvoie lui-même au bon endroit, l’onglet Révision, groupe Protéger, bouton Ôter la protection de la feuille. Aucun mot de passe n’est demandé, et les cases jaunes acceptaient déjà la frappe avant ce geste.

La lecture est immédiate, et c’est tout l’intérêt de la manœuvre. Chaque tranche de cinq points de remise coûte 14 700 € de marge, toujours la même somme, parce que le modèle est linéaire à volume constant. La ligne des 10 % retombe d’ailleurs sur 51 600 €, la marge du modèle, ce qui sert de contrôle rapide quand une grille paraît douteuse.

Monter une table de données Excel à 2 variables

Dès qu’on croise deux hypothèses, la formule quitte le bord et vient occuper la cellule d’angle, en haut à gauche, là où la ligne et la colonne de valeurs se rejoignent. La seconde grille du classeur croise les mêmes remises, toujours en colonne, avec cinq volumes de vente alignés en ligne 22.

Le sens des deux champs de la boîte se déduit du dessin de la grille et non du modèle. « En ligne » désigne les valeurs alignées horizontalement en haut, donc les volumes. Celles qui descendent à gauche, les remises, relèvent au contraire du champ « en colonne ». Les intervertir ne déclenche aucune alerte, la grille se remplit quand même, et les chiffres n’ont plus aucun sens.

Lire la formule TABLE qu’Excel écrit dans la grille

Clique sur n’importe quel résultat et la barre de formule montre {=TABLE(;B4)}, une écriture qu’on ne tape jamais soi-même. Excel la pose au moment où tu valides la boîte, et les accolades disent qu’il s’agit d’une formule matricielle qui tient d’un seul bloc.

Ses deux arguments sont les deux champs de la boîte, dans le même ordre, la cellule d’entrée en ligne puis celle en colonne. Une grille à une seule variable rangée en colonne laisse donc le premier vide, alors que la grille croisée porte les deux et s’écrit {=TABLE(B5;B4)}. Le bloc couvre par ailleurs toute la sélection de départ, valeurs à tester comprises, et pas seulement les cases de résultat.

De là vient le comportement qui surprend le plus. Efface un seul résultat et Excel répond « Impossible de modifier une partie d’une table de données », en laissant la case intacte, alors que le bloc entier s’efface sans un mot. Une grille se refait donc d’un tenant, résultats effacés puis commande relancée, et jamais case par case.

Réparer un tableau de sensibilité qui renvoie des résultats faux

Une grille fausse ne vient presque jamais de la formule du modèle, mais du cadrage de la table ou du choix de la cellule d’entrée. Trois causes couvrent la quasi-totalité des cas, et les deux premières passent sans le moindre message.

Une formule posée une colonne trop à gauche

Avec des valeurs rangées en colonne, la formule va en B12, et la même formule posée en A12, dans le coin, remplit toute la colonne de résultats de zéros. Excel accepte pourtant la commande sans un mot, écrit sa formule matricielle comme d’habitude, et rien à l’écran ne dit que la grille ne calcule plus rien. Une colonne de zéros est donc le symptôme à connaître, et la réparation tient à décaler la formule d’une colonne vers la droite avant de relancer.

Une cellule d’entrée qui n’alimente pas la formule

Quand toute la colonne de résultats affiche la même valeur, la cellule d’entrée désignée n’est pas celle dont la formule dépend. Excel y injecte bien chaque remise, seulement le calcul ne passe pas par elle, donc le résultat ne bouge pas d’une ligne à l’autre. La fiche de la cellule d’entrée détaille ce cas et la façon de remonter la chaîne des références.

Une entrée pointée sur un autre onglet

Excel veut la cellule d’entrée sur la feuille qui porte la table, et il refuse la création quand elle pointe ailleurs, comme le signale la fiche de la cellule d’entrée. C’est la raison pour laquelle le classeur d’exemple tient tout entier sur la feuille Marge. Construis la grille là où vivent les hypothèses, quitte à ramener ensuite le résultat sur la feuille de présentation par une simple référence.

Trouver l’hypothèse qui pèse le plus sur la marge

Une grille à deux entrées sert d’abord à arbitrer, et sa lecture se fait par lignes et par colonnes plutôt que case par case. À 6 000 unités, passer de 0 % à 25 % de remise fait tomber la marge de 81 000 € à 7 500 €, soit 73 500 € de perdus. Doubler le volume de 4 000 à 8 000 unités, au taux de remise du modèle, n’en rapporte que 64 400 €.

La remise pèse donc plus lourd que le volume sur cet éventail, et une remise commerciale ne se rattrape pas en vendant davantage. La ligne des 25 % le dit autrement, puisqu’elle reste négative jusqu’à 5 000 unités et ne repasse au-dessus de zéro qu’à partir de 6 000. Les trois cases négatives du coin inférieur gauche dessinent ainsi la zone où le contrat se signe à perte.

Pour rendre cette zone visible d’un coup d’œil, une mise en forme conditionnelle posée sur les résultats fait mieux que n’importe quel commentaire. Une échelle de couleurs sur B23:F28 suffit, et elle suit la grille à chaque recalcul puisqu’elle porte sur les cellules et non sur des valeurs figées.

Pourquoi le classeur ralentit après un tableau de sensibilité

Une table de données se recalcule avec le reste de la feuille, et chaque case demande à Excel un passage complet du modèle. Une grille de trente cases relance donc trente calculs à chaque frappe, ce qui reste imperceptible ici mais se sent dès qu’un modèle financier compte des milliers de formules et plusieurs grilles.

Excel prévoit pour cela un troisième mode, qui recalcule tout sauf les tables de données, et il se choisit dans le menu Options de calcul. Le classeur reprend alors sa vivacité, et les grilles se mettent à jour quand tu appuies sur F9. La fiche de la table de données nomme le réglage, et celle du mode de calcul décrit les deux autres.

La grille suit en revanche le modèle sans qu’on lui demande rien. Remplace le prix de vente de B2 par 55 € et les deux tables se réécrivent d’elles-mêmes, la première ligne passant de 81 000 € à 117 000 €. Rien n’oblige donc à refaire la manipulation à chaque hypothèse nouvelle.

Pour aller plus loin sur les autres outils de la même famille, la leçon sur l’analyse de scénarios reprend la valeur cible, le gestionnaire de scénarios et le solveur, chacun sur son terrain.