Référence structurée dans les tableaux Excel

L’une des fonctionnalités les plus utiles des tableaux Excel sont les références structurées. Lorsque vous venez de tomber sur une syntaxe spéciale pour référencer des tableaux, cela peut sembler ennuyeux et déroutant, mais après avoir expérimenté un peu, vous verrez sûrement à quel point cette fonctionnalité est utile et intéressante.

Référence structurée Excel

Une référence structurée , ou référence de tableau , est un moyen spécial de référencer des tables et leurs parties qui utilise une combinaison de noms de table et de colonne au lieu d’adresses de cellule.

Cette syntaxe spéciale est requise car les tableaux Excel (par rapport aux plages) sont très puissants et résilients, et les références de cellules normales ne peuvent pas s’ajuster dynamiquement lorsque des données sont ajoutées ou supprimées d’un tableau.

Par exemple, pour additionner les valeurs des cellules B2 : B5, vous utilisez la fonction SOMME avec une référence de plage habituelle :

=SOMME(B2:B5)

Pour additionner les nombres dans la colonne “Ventes” de Tableau1, vous utilisez une référence structurée :

=SOMME(Tableau1[Ventes])

Principales caractéristiques des références structurées

Par rapport aux références de cellule standard, les références de tableau ont un certain nombre de fonctionnalités avancées.

Création facile

Pour ajouter des références structurées à votre formule, il vous suffit de sélectionner les cellules du tableau auxquelles vous souhaitez vous référer. La connaissance d’une syntaxe spéciale n’est pas requise.

Résilient et mis à jour automatiquement

Lorsque vous renommez une colonne, les références sont automatiquement mises à jour avec le nouveau nom et une formule ne se casse pas. De plus, lorsque vous ajoutez de nouvelles lignes au tableau, elles sont immédiatement incluses dans les références existantes et les formules calculent l’ensemble complet des données.

Ainsi, quelles que soient les manipulations que vous faites avec vos tableaux Excel, vous n’avez pas à vous soucier de la mise à jour des références structurées.

Peut être utilisé à l’intérieur et à l’extérieur d’une table

Les références structurées peuvent être utilisées dans les formules à l’intérieur et à l’extérieur d’un tableau Excel, ce qui facilite la localisation des tableaux dans les grands classeurs.

Remplissage automatique des formules (colonnes calculées)

Pour effectuer le même calcul dans chaque ligne du tableau, il suffit de saisir une formule dans une seule cellule. Toutes les autres cellules de cette colonne sont remplies automatiquement.

Comment créer une référence structurée dans Excel

Faire une référence structurée dans Excel est très simple et intuitif.

Si vous travaillez avec une plage, convertissez-la d’abord en tableau Excel. Pour cela, sélectionnez toutes les données et appuyez sur Ctrl + L.

Pour créer une référence structurée, voici ce que vous devez faire :

  1. Commencez à taper une formule comme d’habitude, en commençant par le signe d’égalité (=).
  2. En ce qui concerne la première référence, sélectionnez la cellule ou la plage de cellules correspondante dans votre tableau. Excel sélectionnera le ou les noms de colonne et créera automatiquement une référence structurée appropriée pour vous.
  3. Tapez la parenthèse fermante et appuyez sur Entrée. Si la formule est créée à l’intérieur du tableau, Excel remplit automatiquement toute la colonne avec la même formule.

À titre d’exemple, additionnons les chiffres des ventes pour 3 mois dans chaque ligne de notre exemple de table, nommée VentesMois. Pour cela, nous tapons =SOMME( dans E2, sélectionnez B2:D2, tapez la parenthèse fermante et appuyez sur Entrée :

En conséquence, toute la colonne E est automatiquement remplie avec cette formule :

=SOMME(VentesMois[@[Janvier]:[Mars]])

Bien que la formule soit la même, les données sont calculées individuellement dans chaque ligne. 

Si vous entrez une formule en dehors du tableau et que cette formule ne nécessite qu’une plage de cellules, voici un moyen plus rapide de créer une référence structurée :

  1. Après la parenthèse ouvrante, commencez à taper le nom du tableau. Lorsque vous tapez la première lettre, Excel affiche tous les noms correspondants. Si nécessaire, tapez quelques lettres supplémentaires pour affiner la liste.
  2. Utilisez les touches fléchées pour sélectionner le nom du tableau dans la liste.
  3. Double-cliquez sur le nom sélectionné ou appuyez sur la touche Tab pour l’ajouter à votre formule.
  4. Tapez la parenthèse fermante et appuyez sur Entrée.

Par exemple, pour trouver le plus grand nombre dans notre exemple de table, nous commençons à taper la formule MAX, après la parenthèse ouvrante tapez “ve”, sélectionnez la table ventes_par_moisdans la liste, et appuyez sur Tab ou double-cliquez sur le nom.

Comme résultat, nous avons cette formule :

=MAX(Ventes_par_Mois)

Syntaxe de référence structurée

Comme déjà mentionné, vous n’avez pas besoin de connaître la syntaxe des références structurées pour les inclure dans vos formules, mais cela vous aidera à comprendre ce que chaque formule fait réellement.

Généralement, une référence structurée est représentée par une chaîne qui commence par un nom du tableau et se termine par un spécificateur de colonne.

A titre d’exemple, décomposons la formule suivante qui additionne les totaux des colonnes Sud et Nord dans le tableau nommé Régions :

=SOMME(Régions[[#Totaux];[Sud]:[Nord]])

La référence comprend trois composants :

  1. Nom du tableau
  2. Spécificateur d’article
  3. Spécificateurs de colonne

Pour voir quelles cellules sont réellement calculées, sélectionnez la cellule de formule et cliquez n’importe où dans la barre de formule. Excel mettra en surbrillance les cellules de tableau référencées :

Nom de la table

Le nom de la table fait uniquement référence aux données de la table , sans ligne d’en-tête ni lignes de total. Il peut s’agir d’un nom de table par défaut comme Table1 ou d’un nom personnalisé comme Regions .

Si votre formule se trouve dans la table à laquelle elle fait référence, le nom de la table est généralement omis car il est implicite.

Spécificateur de colonne

Le spécificateur de colonne fait référence aux données de la colonne correspondante, sans la ligne d’en-tête ni la ligne de total. Un spécificateur de colonne est représenté par le nom de la colonne entre crochets, par exemple [Sud].

Pour faire référence à plusieurs colonnes contiguës, utilisez l’opérateur de plage comme [[Sud] :[Est]].

Spécificateur d’article

Pour faire référence à des parties spécifiques d’un tableau, vous pouvez utiliser l’un des spécificateurs suivants.

Spécificateur d’article Fait référence à
[#Tout] Le tableau entier, y compris les données du tableau, les en-têtes de colonne et la ligne de total.
[#Données] Les lignes de données.
[#Entêtes] La ligne d’en-tête (en-têtes de colonne).
[#Totaux] La ligne totale. S’il n’y a pas de ligne totale, elle renvoie null.
[@Nom de colonne] La ligne actuelle, c’est-à-dire la même ligne que la formule.

Veuillez noter que le signe dièse (#) est utilisé avec tous les spécificateurs d’éléments, à l’exception de la ligne actuelle. Pour faire référence aux cellules de la même ligne où vous entrez la formule, Excel utilise le caractère @ suivi du nom de la colonne.

Par exemple, pour ajouter des nombres dans les colonnes Sud et Ouest de la ligne actuelle, vous utiliserez cette formule :

=SOMME(Régions[@[Sud]];[@[ Nord]])

Si les noms de colonne contiennent des espaces, des signes de ponctuation ou des caractères spéciaux, un ensemble supplémentaire de crochets autour du nom de colonne apparaît :

=SOMME(Régions[@[vente Sud]];Régions[@Ouest])

Opérateurs de référence structurés

Les opérateurs suivants vous permettent de combiner différents spécificateurs et d’ajouter encore plus de flexibilité à vos références structurées.

Opérateur de plage (deux-points)

Comme pour les références de plage normales, vous utilisez deux-points (:) pour faire référence à deux ou plusieurs colonnes adjacentes dans une table.

Par exemple, la formule ci-dessous additionne les nombres dans toutes les colonnes entre Sud et Est .

=SOMME(Régions[@[Sud]:[Ouest]])

Opérateur union (virgule)

Pour faire référence à des colonnes non adjacentes, séparez les spécificateurs de colonne par des virgules.

Par exemple, voici comment additionner les lignes de données dans les colonnes Sud et Ouest .

=SOMME(Régions[@Sud];Régions[@Ouest])

Opérateur d’intersection (espace)

Il est utilisé pour faire référence à une cellule à l’intersection d’une ligne et d’une colonne spécifiques.

Par exemple, pour renvoyer une valeur à l’intersection de la ligne Total et de la colonne Ouest , utilisez cette référence :

=Régions[#Totaux] Régions[[#Tout];[Ouest]]

Veuillez noter que le spécificateur [#Tout] est requis dans ce cas car le spécificateur de colonne n’inclut pas la ligne de total. Sans cela, la formule renverrait #NULL!.

Règles de syntaxe des références de table

Pour modifier ou créer manuellement des références structurées, veuillez suivre ces instructions :

  1. Mettez les spécificateurs entre parenthèses

Tous les spécificateurs de colonnes et d’éléments spéciaux doivent être placés entre [crochets].

Un spécificateur qui contient d’autres spécificateurs doit être entouré de crochets extérieurs. Par exemple, Régions[@[Sud]:[Ouest]]).

  1. Séparez les spécificateurs internes par des virgules

Si un spécificateur contient deux spécificateurs internes ou plus, ces spécificateurs internes doivent être séparés par des virgules.

Par exemple, pour renvoyer l’en-tête de la colonne Sud , vous tapez une virgule entre [#En-têtes] et [Sud] et placez toute cette construction dans un ensemble supplémentaire de crochets :

=Régions[[#En-têtes];[Sud]]

  1. N’utilisez pas de guillemets autour des en-têtes de colonne

Dans les références de table, les en-têtes de colonne ne nécessitent pas de guillemets, qu’il s’agisse de texte, de nombres ou de dates.

  1. Utilisez un guillemet simple pour certains caractères spéciaux dans les en-têtes de colonne

Dans les références structurées, certains caractères tels que les crochets gauche et droit, le signe dièse (#) et le guillemet simple (‘) ont une signification particulière. Si l’un des caractères ci-dessus est inclus dans un en-tête de colonne, un guillemet simple doit être utilisé avant ce caractère dans un spécificateur de colonne.

Par exemple, pour l’en-tête de colonne “article #”, le spécificateur est

[article ‘#].

Voici une syntaxe =Régions[[#En-têtes];[Sud ‘#]]

  1. Utilisez des espaces pour rendre les références structurées plus lisibles

Pour améliorer la lisibilité de vos références de table, vous pouvez insérer des espaces entre les spécificateurs. Normalement, il est considéré comme une bonne pratique d’utiliser des espaces après les point virgules. Par example:

=MOYENNE(Régions[@[Sud ]];Régions[@Nord];Régions[@Ouest])

Références des tableaux Excel – exemples de formules

Pour mieux comprendre les références structurées dans Excel, passons en revue quelques exemples de formules supplémentaires. Nous essaierons de les garder simples, significatifs et utiles.

Trouver le nombre de lignes et de colonnes dans un tableau Excel

Pour obtenir le nombre total de colonnes et de lignes, utilisez les fonctions Colonnes et lignes, qui ne nécessitent que le nom de la table :

Colonnes (Nom du tableau)

Lignes (Nom du tableau)

Par exemple, pour trouver le nombre de colonnes et de lignes de données dans la table nommée Ventes , utilisez ces formules :

=COLONNES(Ventes)

=LIGNES(Ventes)

Pour inclure les lignes d’en- tête et de total dans le décompte, utilisez le spécificateur [#Tout] :

=Lignes(Ventes[#Tout])

La capture d’écran ci-dessous montre toutes les formules en action :

Compter les blancs et les non-blancs dans une colonne

Lorsque vous comptez quelque chose dans une colonne spécifique, assurez-vous de sortir le résultat en dehors du tableau, sinon vous risquez de vous retrouver avec des références circulaires et des résultats erronés.

Pour compter les blancs dans une colonne :

  1. Vide(nom de la table[ nom de la colonne])

Pour compter les cellules non vides dans une colonne :

NBVal (nom de la table[ nom de la colonne])

Par exemple, pour savoir combien de cellules de la colonne Janvier sont vides et combien contiennent des données, utilisez ces formules :

vides : =NB.VIDE(vente_mois[Janvier])

Non vides : =NBVAL(vente_mois[Janvier])

Pour compter les cellules non vides dans les lignes visibles d’un tableau filtré, utilisez la fonction SOUS-TOTAL avec numéro_fonction défini sur 103 :

= SOUS-TOTAL(103;Vente[Janvier])

Somme dans un tableau Excel

Le moyen le plus rapide d’additionner des nombres dans un tableau Excel consiste à activer l’ option Ligne Total. Pour ce faire, cliquez avec le bouton droit sur n’importe quelle cellule du tableau, pointez sur Tableau et cliquez sur Ligne total . La ligne de total apparaîtra immédiatement à la fin de votre tableau.

Parfois, Excel peut supposer que vous ne souhaitez totaliser que la dernière colonne et laisser les autres cellules de la ligne Total vides. Pour résoudre ce problème, sélectionnez une cellule vide dans la ligne Total, cliquez sur la flèche qui apparaît à côté de la cellule, puis sélectionnez la fonction SOMME dans la liste :

Cela insérera une formule SOUS-TOTAL qui additionne les valeurs uniquement dans les lignes visibles , en ignorant les lignes filtrées :

= SOUS.TOTAL(109;Vente[Janvier])

Veuillez noter que cette formule ne fonctionne que dans la ligne Total . Si vous essayez de l’insérer manuellement dans une ligne de données, cela créera une référence circulaire et renverra 0 comme résultat. Une formule SOMME avec une référence structurée ne fonctionnera pas non plus pour la même raison :

Donc, si vous voulez les totaux à l’intérieur du tableau , vous devez soit activer la ligne Total, soit utiliser une référence de plage normale telle que :

=SOMME(B2:B5)

En dehors du tableau , la formule SOMME avec une référence structurée fonctionne très bien :

=SOMME(Vente[Janvier])

Veuillez noter que contrairement à SOUS-TOTAL, la fonction SOMME additionne les valeurs de toutes les lignes, visibles et masquées.

Références structurées relatives et absolues dans Excel

Par défaut, les références structurées Excel se comportent de la manière suivante :

  • Les références à plusieurs colonnes sont absolues et ne changent pas lorsque les formules sont copiées.
  • Les références de colonne unique sont relatives et changent lorsqu’elles sont glissées d’une colonne à l’autre. Lorsqu’ils sont copiés/collés via une commande ou des raccourcis correspondants (Ctrl+C et Ctrl+V), ils ne changent pas.

Dans les situations où vous avez besoin d’une combinaison de références de table relatives et absolues, il n’y a aucun moyen de copier la formule et de conserver les références de table correctes. Faire glisser la formule modifiera les références à des colonnes uniques, et les raccourcis copier/coller rendront toutes les références statiques. Mais il existe quelques astuces simples pour se déplacer!

Référence structurée absolue à une seule colonne

Pour rendre absolue une référence de colonne unique, répétez le nom de la colonne pour la transformer formellement en une référence de plage.

Référence de colonne relative (par défaut)

tableau[colonne]

Référence de colonne absolue

tableau[[colonne]: [colonne]]

Pour créer une référence absolue pour la ligne actuelle , préfixez l’identifiant de la colonne par le symbole @ :

tableau[@[colonne]:[colonne]]

Pour voir comment les références de table relatives et absolues fonctionnent dans la pratique, veuillez considérer l’exemple suivant.

Supposons que vous souhaitiez additionner les chiffres de vente d’un produit spécifique pendant 3 mois. Pour cela, nous entrons le nom du produit cible dans une cellule (F2 dans notre cas) et utilisons la fonction SOMME.SI pour obtenir le total des ventes de janvier :

=SOMME.SI(ventes2[Articles];$F$2;ventes2[Janvier])

Le problème est que lorsque nous faisons glisser la formule vers la droite pour calculer les totaux des deux autres mois, la référence [Item] change et la formule se brise :

Pour résoudre ce problème, rendez la référence [Articles] absolue, mais conservez [Janvier] relative :

=SOMME.SI(ventes2[[Articles]:[Articles]];$F$2;ventes2[Janvier])

Maintenant, vous pouvez faire glisser la formule modifiée vers d’autres colonnes et cela fonctionne parfaitement :

Référence structurée relative à plusieurs colonnes

Dans les tableaux Excel, les références structurées à plusieurs colonnes sont absolues par nature et restent inchangées lorsqu’elles sont copiées dans d’autres cellules.

Pour moi, ce comportement est très raisonnable. Mais si vous devez rendre une référence de plage structurée relative, préfixez chaque spécificateur de colonne avec le nom de la table et supprimez les crochets extérieurs comme indiqué ci-dessous.

Référence de plage absolue (par défaut)

tableau[[colonne1]:[colonne2]]

Référence de plage relative

tableau[colonne1]:tableau[colonne2]

Pour faire référence à la ligne actuelle dans le tableau , utilisez le symbole @ :

@ [colonne1]:[colonne2]

Par exemple, la formule ci-dessous avec une référence structurée absolue additionne les nombres dans la ligne actuelle des colonnes Janvier et Février. Lorsqu’il est copié dans une autre colonne, il additionnera toujours Janvier et Février .

=SOMME(Tableau10[@ [Janvier]:[Février]])

Si vous souhaitez que la référence change en fonction d’une position relative de la colonne dans laquelle la formule est copiée, rendez-la relative :

=SOMME(Tableau10[@Février]:Tableau10[@Mars]])

Veuillez noter la transformation de la formule dans la colonne F (le nom du tableau est omis car la formule se trouve à l’intérieur du tableau) :

C’est ainsi que vous créez des références de table dans Excel.

 

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x