Les références structurées rendent le travail avec les données de tableau plus facile et plus intuitif lorsque vous utilisez des formules qui font référence à un tableau, que ce soit des parties du tableau ou le tableau entier. Elles sont particulièrement utiles du fait que les plages des tableaux changent souvent : les références des cellules des références structurées s’ajustent automatiquement. Cela réduit au minimum la réécriture de formules lorsque des cellules et des colonnes sont ajoutées et supprimées dans un tableau ou lorsque des données externes sont actualisées.
| Cette référence structurée est plus facile à comprendre : |
Que cette référence de cellule : |
| =SOMME(DéptVentes[MntVentes]) |
=SOMME(C2:C7) |
Contenu de cet article
Exemple : tableau Service commercial
L’exemple suivant, repris au long de cet article, concerne un tableau qui répertorie les ventes d’un service de six employés, avec les dernières ventes et commissions.
Tableau d’un service commercial
Tableau complet (A1:E8)
Données du tableau (A2:E7)
Colonne et en-tête de colonne (D1:D8)
Colonne calculée (E1:E8)
Ligne Totaux (A8:E8)
Haut de la page
Composants d’une référence structurée
Pour utiliser efficacement des tableaux et des références structurées, vous devez comprendre comment créer la syntaxe des références structurées lorsque vous créez des formules. Les composants d’une référence structurée sont illustrés dans l’exemple suivant ; il s’agit d’une formule qui effectue les totaux des ventes et des commissions :
Le nom d’un tableau est un nom significatif que vous attribuez pour faire référence aux données réelles du tableau (à l’exclusion de la ligne de l’en-tête et celle des totaux, si elles existent).
Un spécificateur de colonne, dérivé de l’en-tête de la colonne et figurant entre crochets, fait référence aux données de la colonne (à l’exclusion de l’en-tête de colonne et du total, s’ils existent).
Un spécificateur d’élément spécial constitue un moyen de faire référence à certaines parties du tableau, telles que la ligne Totaux.
Le spécificateur du tableau est la partie externe de la référence structurée qui figure entre crochets à la suite du nom du tableau.
Une référence structurée est la chaîne de caractères complète qui commence par le nom du tableau et se termine par le spécificateur du tableau.
Haut de la page
Noms des tableaux et spécificateurs de colonnes
Chaque fois que vous insérez un tableau, Excel crée un nom de tableau par défaut (Table1, Table2 et ainsi de suite) au niveau de l’ensemble ou de l’étendue du classeur. Vous modifiez facilement ce nom afin qu’il soit plus significatif. Par exemple, pour modifier Table1 et lui donner le nom VentesDépt, vous utilisez la boîte de dialogue Modifier le nom. Dans le groupe Propriétés de l’onglet Création, modifiez le nom du tableau dans la zone Nom du tableau.
Le nom d’un tableau fait référence à l’ensemble des données du tableau à l’exception des lignes d’en-tête et de total. Dans l’exemple de tableau du service commercial, le nom du tableau, VentesDépt, fait référence à la plage de cellules A2:E7.
Similaires aux noms des tableaux, les spécificateurs de colonnes représentent des références à la colonne complète de données à l’exception de l’en-tête. Dans l’exemple de tableau Service commercial , le spécificateur de colonne [Région] fait référence à la plage de cellules B2:B8 ; le spécificateur de colonne [ComPct] fait référence à la plage de cellules D2:D7.
Haut de la page
Opérateurs de référence
Pour plus de souplesse dans la spécification des plages de cellules, vous pouvez employer les opérateurs de référence suivants pour combiner des spécificateurs de colonnes.
| Cette référence structurée : |
Fait référence à : |
En utilisant le : |
qui, dans l’Exemple, représente la plage de cellules : |
| =VentesDépt[[Représentant]:[Région]] |
Toutes les cellules dans deux colonnes voisines ou plus |
: (colonne) opérateur de plage |
A2:B7 |
| =VentesDépt[MntVentes],VentesDépt[MntCom] |
Une combinaison de deux colonnes ou plus |
, (virgule) opérateur d’union |
C2:C7, E2:E7 |
| =VentesDépt[[Repr]:[MntVente]] VenteDépt[[Région]:[PctCom]] |
L’intersection de deux colonnes ou plus |
(espace) opérateur d’intersection |
B2:C7 |
Haut de la page
Spécificateurs d’éléments spéciaux
Pour plus de commodité, vous pouvez également utiliser des éléments spéciaux pour faire référence à diverses parties d’un tableau (ex. la ligne Totaux), pour faciliter la référence à ces parties dans les formules. Les spécificateurs d’éléments spéciaux utilisables dans une référence structurée sont indiqués ci-dessous :
| Ce spécificateur d’élément spécial : |
Fait référence à : |
qui, dans l’Exemple, représente la plage de cellules : |
| =VentesDépt[#Tout] |
Tout le tableau, y compris les en-têtes des colonnes, les données et les totaux (le cas échéant). |
A1:E8 |
| =VentesDépt[#Données] |
Uniquement les données. |
A2:E7 |
| =VentesDépt[#En-têtes] |
Uniquement l’en-tête de colonne |
A1:E1 |
| =VentesDépt[#Totaux] |
Uniquement la ligne des totaux. Si cette ligne n’existe pas, le résultat renvoyé est vide. |
A8:E8 |
| =VentesDépt[#Cette ligne] |
Uniquement la partie des colonnes de la ligne en cours. Il n’est pas possible de combiner le spécificateur #ThisRow avec d’autres spécificateurs d’éléments spéciaux. Utilisez-le pour imposer une intersection implicite comme référence ou pour remplacer une intersection implicite et faire référence à des valeurs individuelles d’une colonne. Pour d’autres exemples, voir Exemples d’utilisation de références structurées. |
A5:E5 (si la ligne 5 est la ligne active) |
Haut de la page
Qualification de références structurées dans des colonnes calculées
Lorsque vous créez une colonne calculée, vous utilisez couramment une référence structurée pour créer la formule. Cette référence peut être non qualifiée ou pleinement qualifiée. Par exemple, pour créer la colonne calculée baptisée ComAmt, qui calcule le montant des commissions en dollars, vous pouvez utiliser les formules suivantes :
| Type de référence structurée |
Exemple |
Commentaire |
| Non qualifiée |
=[MntVentes]*[PctCom] |
Multiplie les valeurs correspondantes de la ligne en cours. |
| Pleinement qualifiée |
=VentesDépt[MntVentes]*VentesDépt[PctCom] |
Multiplie les valeurs correspondantes de chaque ligne pour les deux colonnes. |
Règle générale : si vous utilisez des références structurées dans un tableau, comme par exemple lorsque vous créez une colonne calculée, vous pouvez utiliser une référence structurée non qualifiée ; cependant, si vous utilisez la référence structurée en dehors du tableau, vous devez utiliser une référence pleinement qualifiée.
Haut de la page
Exemples d’utilisation de références structurées
Il existe de nombreuses façons d’utiliser ces éléments spéciaux et de les combiner avec des noms de tableaux et des références de colonnes. Ce qui suit le démontre :
| Cette référence structurée : |
Fait référence à : |
qui, dans l’Exemple, représente la plage de cellules : |
| =VentesDépt[[#Tout],[MntVentes]] |
Toutes les cellules de la colonne MntVentes. |
C1:C8 |
| =VentesDépt[[#En-têtes],[PctCom]] |
En-tête de la colonne PctCom. |
C1 |
| =VentesDépt[[#Totaux],[Région]] |
Total de la colonne Région. Si cette ligne n’existe pas, le résultat renvoyé est vide. |
B8 |
| =DeptSales[[#All],[SaleAmt]:[ComPct]] |
Toutes les cellules de MntVentes et de PctCom. |
C1:D8 |
| =VentesDépt[[#Données],[PctCom]:[MntCom]] |
Uniquement les données des colonnes PctCom et MntCom. |
D2:E7 |
| =VentesDépt[[#En-têtes],[Région]:[MntCom]] |
Uniquement les en-têtes des colonnes comprises entre Région et PctCom MntCom. |
B1:E1 |
| =VentesDépt[[#Totaux],[MntVentes]:[MntCom]] |
Totaux des colonnes comprises entre MntVentes et MntCom. Si la ligne Totaux n’existe pas, le résultat renvoyé est vide. |
C8:E8 |
| =VentesDépt[[#En-têtes],[#Données],[PctCom]] |
Uniquement l’en-tête et les données de la colonne PctCom. |
D1:D7 |
| =VentesDépt[[#Cette ligne], [MntCom]] |
Cellule à l’intersection de la ligne active et de la colonne MntCom. |
E5 (si la ligne active est la ligne 5) |
Haut de la page
Utilisation des références structurées
Prenez en compte les points suivants lorsque vous utilisez des références structurées.
Utilisation de la Saisie semi-automatique de formules
Vous constaterez peut-être que l’utilisation de la Saisie semi-automatique des formules est très utile lorsque vous tapez des références structurées pour garantir que vous utilisez la syntaxe correcte.
Pour plus d’informations, voir Utiliser la saisie semi-automatique de formule.
Décider d’utiliser des références structurées pour les tableaux lorsque vous utilisez la fonction de semi-sélection
Par défaut, lors de la création d’une formule, lorsque vous cliquez sur une plage de cellules dans un tableau, vous effectuez une semi-sélection des cellules et définissez automatiquement une référence structurée à la place de la plage de cellules dans la formule. Cette semi-sélection facilite la saisie d’une référence structurée. Vous pouvez activer ou désactiver cette fonction : activez ou désactivez la case à cocher Utiliser les noms de tableaux dans les formules dans la section Manipulation de formules de la catégorie Formules de la boîte de dialogue Options Excel.
Conversion d’une plage en tableau et inversement
Lorsque vous convertissez un tableau en plage de cellules, toutes les références aux cellules deviennent des cellules équivalentes de type A1. Lorsque vous convertissez un tableau en plage de cellules, Excel ne transforme pas automatiquement les références des plages de cellules en noms de tableau et en références de colonnes équivalents.
Désactivation d’en-têtes de colonnes
Si vous désactivez les en-têtes de colonnes (dans le groupe Styles de tableau de l’onglet Création, désélectionnez Ligne d’en-tête), les références structurées qui utilisent ces en-têtes ne sont pas affectées : vous pouvez toujours les utiliser dans des formules.
Ajout ou suppression de colonnes dans un tableau
Du fait que les plages des tableaux changent souvent, les références des cellules des références structurées s’ajustent automatiquement. Par exemple, si vous utilisez le nom d’un tableau dans une formule pour compter toutes les cellules de données du tableau Service commercial (ex. =NBVAL(VentesDépt) dans l’exemple de tableau Service commercial, la valeur renvoyée est 30 parce que la plage des données est A2:E7. Si vous ajoutez par la suite une ligne de données, la référence des cellules s’ajuste automatiquement à A2:E8 : la nouvelle valeur renvoyée est alors 35.
Modification du nom d’un tableau ou d’une colonne
Si vous renommez un tableau ou une colonne, Excel modifie automatiquement l’utilisation de ce tableau et de l’en-tête de colonne dans toutes les références structurées du classeur.
Déplacement, copie et recopie de références structurées
Toutes les références structurées restent identiques lorsque vous copiez ou déplacez une formule qui utilise une telle référence.
Lorsque vous rédigez une formule, les références structurées pleinement qualifiées peuvent ajuster les spécificateurs de colonnes tels qu’une série comme le récapitule le tableau suivant.
| Si le sens de recopie est : |
Et si, pendant la recopie, vous appuyez sur : |
Alors : |
| Vers le haut ou vers le bas |
Rien |
L’ajustement des spécificateurs de colonnes n’a pas lieu. |
| Vers le haut ou vers le bas |
Ctrl |
Les spécificateurs de colonnes s’ajustent comme une série. |
| Vers la droite ou vers la gauche |
Aucune |
Les spécificateurs de colonnes s’ajustent comme une série. |
| Vers la droite ou vers la gauche |
Ctrl |
L’ajustement des spécificateurs de colonnes n’a pas lieu. |
| Vers le haut, vers le bas, à droite ou à gauche |
Maj |
Au lieu de remplacer les valeurs dans les cellules actives, celles-ci sont déplacées et des spécificateurs de colonnes sont insérés. |
Haut de la page
Règles de syntaxe des références structurées
La liste ci-dessous répertorie les règles de syntaxe que vous devez connaître lorsque vous créez et modifiez des références structurées.
Remarque Les noms des tableaux respectent les mêmes règles que les noms définis.Pour plus d’informations, voir Définir et utiliser des noms dans les formules.
Utilisation des crochets dans les spécificateurs
Tous les spécificateurs de tableaux, de colonnes et d’éléments spéciaux doivent figurer entre crochets ([ ]). Un spécificateur qui en contient d’autres exige des crochets externes.
Exemple =VentesDépt[ [Repr]:[Région] ]
Les en-têtes de colonnes sont des chaînes de caractères
Tous les en-têtes de colonnes sont des chaînes de caractères. Cependant, ils ne nécessitent pas de guillemets dans les références structurées. Si un en-tête de colonne contient des nombres ou des dates (ex. 2004 ou 1/1/2004), ceux-ci sont néanmoins considérés comme des chaînes de caractères. Du fait qu’il s’agit de chaînes de caractères, vous ne pouvez pas utiliser des expressions entre crochets.
Exemple =SynthèseExerciceVentesDépt[[2004]:[2002]]
Caractères spéciaux dans les en-têtes de colonnes
Si un en-tête de colonne d’un tableau contient un des caractères spéciaux suivants, l’en-tête complet doit figurer entre crochets. Cela signifie que des doubles crochets sont indispensables dans un spécificateur de colonne qui contient les caractères spéciaux suivants : espace, tabulation, saut de ligne, retour chariot, virgule (,), points superposés (:), point (.), crochet ouvrant ([), crochet fermant (]), symbole livre sterling (#), apostrophe (’), guillemet ("), accolade ouvrante ({), accolade fermante (}), symbole dollar ($), accent circonflexe (^), et commercial (&), astérisque (*), signe plus (+), signe égal (=), signe moins (-), symbole "supérieur à" (>), symbole "inférieur à " (<), et barre de division (/).
Exemple =SynthèseExerciceVentesDépt[[Montant$total]]
Il existe une seule exception, dans le cas où le seul caractère spécial utilisé est un espace.
Exemple =VentesDépt[Montant total]
Caractères spéciaux dans des en-têtes de colonnes qui nécessitent l’utilisation du caractère d’échappement
Les caractères suivants ont une signification particulière et exigent d’utiliser une apostrophe (’) comme caractère d’échappement : crochet ouvrant ([), crochet fermant (]), symbole livre sterling (#) et apostrophe (’).
Exemple =SynthèseExerciceVentesDépt[’#ArticlesB]
Utilisation de l’espace pour améliorer la lisibilité dans une référence structurée
Utilisez des espaces pour améliorer la lisibilité d’une référence structurée :
- un espace après le premier crochet ouvrant ([) et avant le dernier crochet fermant (]) ;
- un espace après une virgule.
Exemple =VentesDépt[ [Repr]:[Région] ]
Exemple =VentesDépt[[#En-têtes], [#Données], [PctCom]]
Haut de la page
