C'est quoi une référence structurée Excel ?
Une référence structurée désigne des cellules par le nom du tableau et le nom d'une de ses colonnes, comme Ventes[Montant], au lieu d'une adresse comme D2:D500. Elle n'existe que dans une plage convertie en tableau Excel avec le raccourci Ctrl+T, et elle continue de désigner la même colonne quand le tableau s'agrandit.
À quoi ressemble une référence structurée dans une formule
Une référence structurée s'écrit avec le nom du tableau, puis le nom d'une colonne entre crochets, comme Ventes[Montant]. Elle remplace l'adresse que tu aurais tapée, D2:D500, par quelque chose qui se lit à voix haute. Ainsi, =SOMME(Ventes[Montant]) additionne toute la colonne Montant du tableau Ventes, sans que tu aies à savoir où elle commence ni où elle finit.
Une seconde forme apparaît quand la formule est écrite dans une colonne du tableau lui-même. L'arobase de [@Montant] désigne la valeur de la colonne Montant sur la ligne courante, celle où se trouve la formule. Le nom du tableau devient inutile, ce qui donne des calculs très courts, du genre =[@Prix]*[@Quantité].
Excel réserve enfin quelques mots-clés précédés d'un dièse pour viser une zone précise. Ventes[#En-têtes] pointe la ligne de titres, Ventes[#Totaux] la ligne des totaux, et Ventes[#Tout] reprend le tableau entier, en-têtes et totaux compris.
Ventes[Montant] se relit sans effort, Tableau1[Montant] ne dit rien à personne.
Pourquoi Excel écrit une référence structurée à la place de A2
Excel bascule en référence structurée dès que la cellule que tu pointes appartient à un tableau. Tu tapes =SOMME(, tu sélectionnes la colonne à la souris, et c'est Ventes[Montant] qui s'inscrit au lieu de l'adresse attendue. Rien n'est cassé, c'est le comportement normal d'une plage convertie en tableau.
La surprise vient surtout quand tu ignores que le fichier contient un tableau, parce que quelqu'un d'autre l'a créé avant toi. Beaucoup de gens y voient un bug et retapent l'adresse à la main, ce qui fonctionne d'ailleurs très bien.
Si cette écriture te gêne vraiment, tu peux la couper. Ouvre Fichier > Options > Formules, puis décoche la case « Utiliser les noms de tableaux dans les formules ». Excel repointe alors les cellules en adresses classiques, et les formules déjà écrites, elles, gardent leur référence structurée.
Quelle différence entre une référence structurée et une plage comme A2:A50
Une référence structurée suit le tableau, alors qu'une plage comme A2:A50 reste clouée sur des coordonnées. Ajoute trois lignes en bas du tableau et Ventes[Montant] les intègre aussitôt, quand A2:A50 continue de s'arrêter à la ligne 50. C'est la première raison pour laquelle un fichier de suivi finit par afficher des totaux faux.
Le nom protège aussi tes formules des déménagements de colonnes. Insère une colonne au milieu du tableau et la référence continue de viser Montant, là où une adresse aurait glissé d'un cran sans prévenir.
Reste le confort de lecture, qui n'est pas un détail sur un fichier partagé. La formule =SOMME(Ventes[Montant])/NBVAL(Ventes[Commande]) se comprend en une seconde, six mois plus tard, par quelqu'un qui n'a jamais ouvert le classeur.
Pourquoi une référence structurée ne s'écrit pas avec des dollars
Une référence structurée n'accepte pas le signe dollar, tout simplement parce qu'elle ne désigne pas des coordonnées. Ventes[Montant] vise la colonne entière du tableau, donc la recopier vers le bas ne la décale jamais d'une ligne. La touche F4, qui fige les références classiques, n'a rien à faire ici.
Il existe malgré tout un cas où elle bouge, et c'est la recopie vers la droite. Ventes[Montant] devient alors Ventes[Quantité], la colonne voisine, quand tu la tires par la poignée de recopie. Au copier-coller, en revanche, elle ne bouge pas, là où une adresse classique aurait glissé.
Pour la clouer sur place, écris le nom de la colonne deux fois, sous la forme Ventes[[Montant]:[Montant]]. Cette syntaxe joue le rôle des dollars dans le monde des tableaux, et c'est elle qu'on appelle une référence structurée absolue.
Quand une référence structurée ne fonctionne plus
Une référence structurée vit dans le classeur de son tableau, et nulle part ailleurs. D'une feuille à l'autre elle circule sans problème, sans même que tu aies à citer le nom de la feuille. En revanche, d'un classeur à l'autre elle n'existe pas, et Excel repasse à une adresse externe complète.
Elle disparaît aussi le jour où le tableau redevient une plage ordinaire. Va dans Création de tableau, clique sur Convertir en plage, et chaque Ventes[Montant] de tes formules est réécrit en adresse figée, du genre Feuil1!$D$2:$D$500. Le calcul reste juste, mais tu perds la lisibilité et l'extension automatique.
Attention enfin aux colonnes supprimées. Une colonne effacée renvoie #REF! dans toutes les formules qui la citaient, alors qu'une colonne simplement renommée met ces mêmes formules à jour toute seule.
Ventes[[#Cette ligne];[Montant]]. Le raccourci [@Montant] est arrivé avec Excel 2010, et l'ancienne écriture reste acceptée dans les fichiers d'époque.
Analyser les performances de campagnes
Tu gères un tableau « Campagnes » avec les colonnes Canal, Budget, Clics, Conversions et CA. Chaque mois, tu ajoutes de nouvelles lignes pour les campagnes lancées. Tes formules de synthèse doivent toujours couvrir l'ensemble des données.
Avec les références structurées, ta formule de coût par conversion s'écrit =SOMME(Campagnes[Budget])/SOMME(Campagnes[Conversions]). Quand tu ajoutes 15 nouvelles campagnes en fin de mois, les totaux se recalculent automatiquement. Pas besoin de vérifier si ta plage va jusqu'à la bonne ligne.
Dans la colonne ROI, la formule =[@CA]/[@Budget] fait le calcul ligne par ligne, et elle se recopie toute seule sur chaque campagne ajoutée. Tu n'as plus une seule plage à surveiller dans ce fichier.
Pour aller plus loin
Termes liés : Tableau Excel, Plage nommée, Formule, Référence absolue
Questions fréquentes sur la référence structurée
Le plus simple, c'est de ne pas l'écrire toi-même. Commence ta formule, puis sélectionne à la souris la colonne ou la cellule visée dans ton tableau, et Excel inscrit la référence structurée à ta place. Si tu préfères taper, la syntaxe est le nom du tableau suivi du nom de la colonne entre crochets, comme dans =SOMME(Ventes[Montant]). Pour la ligne en cours, écris [@Montant], sans nom de tableau devant.
Oui, et c'est même plus sûr qu'une plage figée. Tu écris =RECHERCHEV(A1;Produits;3;FAUX) avec le nom du tableau, et la table de recherche s'étend d'elle-même quand de nouvelles lignes arrivent, donc tu n'as plus de plage à corriger. Évite en revanche Produits[#Tout] dans une RECHERCHEV, il embarque la ligne d'en-têtes dans la zone de recherche, qui peut alors ressortir comme un faux résultat. Avec RECHERCHEX, tu vas plus loin en nommant chaque colonne, comme dans =RECHERCHEX(A1;Produits[Référence];Produits[Prix]).
Oui, c'est la seule condition à remplir. Une référence structurée n'existe que dans une plage convertie en tableau, ce que tu fais en cliquant dans tes données puis en appuyant sur Ctrl+T. Tant que ta plage reste une simple zone de cellules, Excel n'a aucun nom de tableau ni aucun nom de colonne à te proposer, et tes formules continuent d'utiliser des adresses. Une fois la conversion faite, les références structurées apparaissent d'elles-mêmes dès que tu pointes une cellule du tableau.
Du mot à la pratique
Tu sais maintenant ce que le mot veut dire. Reste à s’en servir : la formule, le fichier, et le cours qui remet tout dans l’ordre.
Tout le lexiqueDes modèles Excel prêts à télécharger
Toutes les formules Excel, expliquées
Tout le cours Excel en PDF
