Utilisation de références structurées avec des tableaux Excel

Important :  Cet article a été traduit automatiquement, voir l’avertissement. Vous pouvez consulter la version en anglais de cet article ici.

Lorsque vous créez un tableau Excel, Excel affecte un nom pour la table et pour chaque en-tête de colonne dans la table. Lorsque vous ajoutez des formules à un tableau Excel, les noms peuvent apparaître automatiquement lorsque vous entrez la formule et sélectionnez les références de cellule dans la table au lieu de taper manuellement les. Voici un exemple de rôle Excel :

Au lieu d’utiliser des références de cellule explicites

Excel utilise les noms des tableaux et des colonnes

=SOMME(C2:C7)

=SOMME(VentesDépt[Montant ventes])

Cette combinaison de noms de tableau et de colonne est appelée une référence structurée. Les noms des références structurées sont modifiés dès que vous ajoutez ou supprimez des données du tableau.

Les références structurées apparaissent également lorsque vous créez une formule en dehors d’un tableau Excel qui fait référence aux données du tableau. Les références peuvent faciliter la recherche de tableaux dans un classeur volumineux.

Pour inclure des références structurées dans votre formule, cliquez sur les cellules du tableau à référencer au lieu de taper leur référence de cellule dans la formule. Dans cet exemple, vous allez entrer une formule qui utilise automatiquement des références structurées pour calculer le montant d’une commission de ventes.

Ventes Personne

Région

Montant des ventes

% commission

Montant de la Commission

Jean

Nord

260

10 %

Robert

Sud

660

15 %

Michelle

Est

940

15 %

Eric

Ouest

410

12 %

Sylvie

Nord

800

15 %

Alexandre

Sud

900

15 %

  1. Copiez les données d’exemple dans le tableau ci-dessus, y compris les en-têtes de colonnes et collez-le dans la cellule A1 d’une feuille de calcul Excel.

  2. Pour créer la table, sélectionnez n’importe quelle cellule dans la plage de données, puis appuyez sur Ctrl + T.

  3. Vérifiez que la case à cocher Mon tableau comporte des en-têtes est activée, puis cliquez sur OK.

  4. Cliquez sur la cellule E2, tapez un signe égal (=), puis cliquez sur la cellule C2.

    Dans la barre de formule, la référence structurée [@[Montant des ventes]] s’affiche après le signe égal.

  5. Tapez un astérisque (*) juste après le crochet fermant, puis cliquez sur la cellule D2.

    Dans la barre de formule, la référence structurée [@[% commission]] s’affiche après l’astérisque.

  6. Appuyez sur entrée.

    Excel crée automatiquement une colonne calculée et recopie la formule dans la colonne entière, en l’ajustant pour chaque ligne.

Que se passe-t’il lorsque j’utilise des références de cellule explicites ?

Si vous entrez des références de cellule explicites dans une colonne calculée, le calcul de la formule peut être plus difficile à déterminer.

  1. Dans votre feuille de calcul exemple, cliquez sur la cellule E2

  2. Dans la barre de formule, entrez = C2 * D2 et appuyez sur entrée.

Vous pouvez remarquer que lorsqu’Excel copie la formule dans la colonne, le programme n’utilise pas de références structurées. Si, par exemple, vous ajoutez une colonne entre les colonnes existantes C et D, vous devez réviser la formule.

Comment modifier le nom d’un tableau ?

Dès que vous créez un tableau, Excel crée un nom de tableau par défaut (Tableau1, Tableau2 etc.). Vous pouvez modifier ce nom pour le rendre plus explicite.

  1. Sélectionnez n’importe quelle cellule du tableau pour afficher les Outils de tableau > onglet Création dans le ruban.

  2. Tapez le nom souhaité dans la zone Nom de la Table, puis appuyez sur entrée.

Dans nos données d’exemple, nous avons utilisé le nom VentesDépt.

Utilisez les règles suivantes pour les noms de table :

  • Utilisez des caractères valides  Commencent toujours un nom par une lettre, un caractère de soulignement (_) ou une barre oblique inversée (\). Utiliser des lettres, des nombres, des points et caractères pour le reste du nom de soulignement. Vous ne pouvez pas utiliser « C », « c », « R » ou « r » pour le nom, car ils sont déjà désignés comme un raccourci pour la sélection de la colonne ou ligne de la cellule active lorsque vous les entrez dans la zone nom ou Atteindre .

  • N’utilisez des références de cellule  Les noms ne peuvent pas être identique à une référence de cellule, comme Z$ 100 ou R1C1.

  • N’utilisez pas un espace pour séparer les mots  Espaces ne peuvent pas être utilisés dans le nom. Vous pouvez utiliser le caractère de soulignement (_) et la période (.) comme séparateurs de mots. Par exemple, VentesDépt, premier.trimestre ou Taxe_Ventes.

  • Utilisez au maximum 255 caractères Un nom de tableau accepte un maximum de 255 caractères.

  • Utilisez des noms de tableau uniques Les noms en double ne sont pas autorisés. Excel n’est pas sensible à la casse. Ainsi, si vous entrez « Ventes » alors qu’un autre tableau du même classeur est déjà nommé « VENTES », vous serez invité à choisir un nom unique.

  • Utiliser un identificateur d’objet  Si vous prévoyez d’avoir une combinaison de graphiques, des tableaux croisés dynamiques et des tableaux, il est recommandé de préfixe vos noms avec le type d’objet. Par exemple : tbl_Sales pour une table des ventes, pt_Sales d’un tableau croisé dynamique de ventes et chrt_Sales pour un graphique des ventes ou ptchrt_Sales pour un graphique croisé dynamique vente. Cela permet de conserver toutes vos noms dans une liste dans le Gestionnaire de noms.

Règles de syntaxe des références structurées

Vous pouvez également entrer ou modifier des références structurées manuellement dans la formule, mais pour ce faire, vous aidera à comprendre la syntaxe de la référence structurée. Passons l’exemple de formule suivant :

=SOMME(VentesDépt[[#Totaux],[Montant des ventes]],VentesDépt[[#Données],[Montant de la commission]])

Cette formule contient les composants de référence structurée suivants :

  • Nom de la table :   VentesDépt est un nom de table personnalisée. Il fait référence aux données de tableau, sans les lignes d’en-tête ou le total. Vous pouvez utiliser un nom de tableau par défaut, tel que Table1, modifier ou pour utiliser un nom personnalisé.

  • Spécificateur de colonne :   [Montant des ventes]et[Montant de la commission] sont des spécificateurs de colonnes qui utilisent les noms des colonnes qu’elles représentent. Elles font référence aux données de colonne, sans n’importe quelle ligne d’en-tête ou le total de colonne. Placez toujours les spécificateurs entre crochets, comme indiqué.

  • Spécificateur d’élément :   [#Totals] et [#Data] sont des spécificateurs d’éléments spéciaux qui renvoient à certaines parties du tableau, par exemple la ligne total.

  • Spécificateur de tableau :   [[#Totaux],[Montant des ventes]] et [[#Données],[Montant de la commission]] sont des spécificateurs de tableaux qui représentent les parties externes de la référence structurée. Les références externes se trouvent à la suite du nom du tableau et doivent figurer entre crochets.

  • Référence structurée :   (VentesDépt [[#Totals], [montant des ventes]] et VentesDépt [[#Data], [montant de la Commission]] sont des références structurées, représentées par une chaîne commençant par le nom de table et se termine par le spécificateur de colonne.

Pour créer ou modifier manuellement des références structurées, utilisez les règles de syntaxe suivantes :

  • Spécificateurs entre crochets utilisation    Toutes les tables, colonnes et les spécificateurs d’éléments spéciaux besoin d’être entre crochets ([]). Un spécificateur qui contient d’autres spécificateurs nécessite externes crochets interne correspondance entre crochets des spécificateurs d’autres. Par exemple : = VentesDépt [[vendeur] : [Région]]

  • Les en-têtes de colonnes sont des chaînes de caractères    Toutefois, ils ne nécessitent pas de guillemets lorsqu’ils sont utilisés dans une référence structurée. Les nombres ou les dates, par exemple 2014 ou 01/01/2014, sont également considérés comme des chaînes de caractères. Vous ne pouvez pas utiliser des expressions avec des en-têtes de colonne. Par exemple, l’expression SynthèseExerciceVentesDépt[[2014]:[2012]] est incorrecte.

Placez les en-têtes de colonne avec des caractères spéciaux entre crochets    S’il comporte des caractères spéciaux, l’en-tête complet doit figurer entre crochets, ce qui oblige à utiliser des doubles crochets dans un spécificateur de colonne. Par exemple : =SynthèseExerciceVentesDépt[[Montant total €]]

Voici la liste des caractères spéciaux nécessitant des crochets supplémentaires dans la formule :

  • Tab

  • Saut de ligne

  • Retour chariot

  • Virgule (,)

  • Deux-points (:)

  • Point (.)

  • Crochet ouvrant ([)

  • Crochet fermant (])

  • Signe dièse (#)

  • Apostrophe (')

  • Guillemet (")

  • Accolade ouvrante ({)

  • Accolade fermante (})

  • Symbole dollar ($)

  • Accent circonflexe (^)

  • Esperluette (&)

  • Astérisque (*)

  • Signe plus (+)

  • Signe égal (=)

  • Signe moins (-)

  • Signe supérieur à (>)

  • Signe inférieur à (<)

  • Symbole de division (/)

  • Utilisez un caractère d’échappement à la place d’un caractère spécial dans les en-têtes de colonne    Certains caractères ont une signification particulière et obligent à utiliser une apostrophe (') comme caractère d’échappement. Par exemple : =SynthèseExerciceVentesDépt['#ArticlesB]

Voici la liste des caractères spéciaux nécessitant un caractère d’échappement (') dans la formule :

  • Crochet ouvrant ([)

  • Crochet fermant (])

  • Signe dièse (#)

  • Apostrophe (')

Utilisez des espaces pour améliorer la lisibilité dans une référence structurée    Vous pouvez utiliser des espaces pour améliorer la lisibilité d’une référence structurée. Par exemple : =VentesDépt[ [Vendeur]:[Région] ] ou =VentesDépt[[#En-têtes], [#Données], [% commission]]

Il est recommandé d’utiliser un espace :

  • Après le premier crochet ouvrant ([)

  • Avant le dernier crochet fermant (])

  • Après une virgule

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 représente la plage de cellules :

=VentesDépt[[Vendeur]:[Région]]

Toutes les cellules dans deux colonnes voisines ou plus

: (colonne) opérateur de plage

A2:B7

=VentesDépt[Montant des ventes],VentesDépt[Montant de la commission]

Une combinaison de deux colonnes ou plus

, (virgule) opérateur d’union

C2:C7, E2:E7

=VentesDépt[[Vendeur]:[Montant des ventes]] VentesDépt[[Région]:[% commission]]

L’intersection de deux colonnes ou plus

 (espace) opérateur d’intersection

B2:C7

Spécificateurs d’éléments spéciaux

Pour faire référence à certaines parties d’un tableau, par exemple la ligne de total uniquement, vous pouvez utiliser l’un des spécificateurs d’élément spéciaux suivants dans vos références structurées.

Ce spécificateur d’élément spécial :

Fait référence à :

#Tout

Tout le tableau, y compris les en-têtes de colonne, les données et les totaux (le cas échéant).

#Données

Uniquement les lignes de données.

#Headers

Uniquement l’en-tête de colonne.

#Totaux

Uniquement la ligne des totaux. Si cette ligne n’existe pas, le résultat renvoyé est vide.

#Cette ligne

ou

@

ou

@[Nom de colonne]

Uniquement les cellules dans la même ligne que la formule. Il n’est pas possible de combiner ces spécificateurs avec d’autres spécificateurs d’éléments spéciaux. Utilisez-les 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.

Excel modifie automatiquement les spécificateurs #Cette ligne en spécificateurs @ plus courts dans les tableaux contenant plusieurs lignes de données. Si votre tableau comporte une seule ligne, Excel ne remplace pas le spécificateur #Cette ligne, ce qui peut provoquer des résultats de calcul inattendus lorsque vous ajoutez des lignes. Pour éviter les problèmes de calcul, veillez à entrer plusieurs lignes dans votre tableau avant d’entrer des formules de références structurées.

Qualification de références structurées dans des colonnes calculées

Lorsque vous créez une colonne calculée, vous utilisez souvent 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 Montant de la commission, 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

=[Montant des ventes]*[% commission]

Multiplie les valeurs correspondantes de la ligne en cours.

Pleinement qualifiée

=VentesDépt [Montant des ventes]*VentesDépt[% commission]

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.

Exemples d’utilisation de références structurées

Voici quelques exemples d’utilisation des références structurées.

Cette référence structurée :

Fait référence à :

qui représente la plage de cellules :

=VentesDépt[[#Tout],[Montant des ventes]]

Toutes les cellules de la colonne Montant des ventes.

C1:C8

=VentesDépt[[#En-têtes],[% commission]]

En-tête de la colonne % commission.

D1

=VentesDépt[[#Totaux],[Région]]

Total de la colonne Région. Si cette ligne n’existe pas, le résultat renvoyé est vide.

B8

=VentesDépt[[#Tout],[Montant des ventes]:[% commission]]

Toutes les cellules des colonnes Montant des ventes et % commission.

C1:D8

=VentesDépt[[#Données],[% commission]:[Montant de la commission]]

Uniquement les données des colonnes % commission et Montant de la commission.

D2:E7

=VentesDépt[[#En-têtes],[Région]:[Montant de la commission]]

Uniquement les en-têtes des colonnes comprises entre Région et Montant de la commission.

B1:E1

=VentesDépt[[#Totaux],[Montant des ventes]:[Montant de la commission]]

Totaux des colonnes Montant des ventes et Montant de la commission. Si la ligne Totaux n’existe pas, le résultat renvoyé est vide.

C8:E8

=VentesDépt[[#En-têtes],[#Données],[% commission]]

Uniquement l’en-tête et les données de la colonne % commission.

D1:D7

=VentesDépt[[#Cette ligne], [Montant de la commission]]

ou

=VentesDépt[@Montant de la commission]

La cellule à l’intersection de la ligne actuelle et la colonne de la Commission. Si utilisé dans la même ligne qu’un en-tête ou de la ligne total, cela renverra une #VALUE ! erreur.

Si vous entrez la forme longue de cette référence structurée (#Cette ligne) dans un tableau comportant plusieurs lignes de données, Excel la remplace automatiquement par la forme raccourcie (@). Les deux formes fonctionnent indifféremment.

E5 (si la ligne active est la ligne 5)

Stratégies d’utilisation des références structurées

Prenez en compte les points suivants lorsque vous utilisez des références structurées.

  • Utiliser la saisie semi-automatique de formule    Lors de la saisie de références structurées, la fonctionnalité de saisie semi-automatique des formules peut se révéler très utile pour garantir que vous utilisez la syntaxe correcte. Pour en savoir plus, voir Utiliser la saisie semi-automatique des formules.

  • Décider si vous souhaitez utiliser des références structurées pour les tableaux dans des sélections    Par défaut, lorsque vous créez une formule, en cliquant sur une cellule de plage au sein d’une semi-sélection table les cellules et entre automatiquement une référence structurée au lieu de la plage de cellules dans la formule. Cette semi-sélection facilite entrer une référence structurée. Vous pouvez activer ce comportement ou désactiver en activant ou désactivant la case à cocher utiliser des noms de table dans les formules dans le fichier > Options > formules > boîte de dialogue avec les formules.

  • Classeurs utilisation avec des liens externes vers des tableaux Excel dans d’autres classeurs    Si un classeur contient un lien externe à un tableau Excel dans un autre classeur, ce classeur source liée doit être ouvert dans Excel afin d’éviter #REF ! erreurs dans le classeur de destination qui contient les liens. Si vous ouvrez d’abord le classeur de destination et #REF ! erreurs s’affichent, ils seront résolus si vous ouvrez le classeur source. Si vous ouvrez le classeur source tout d’abord, vous ne devez voir aucun codes d’erreur.

  • Convertir une plage en tableau et inversement    Lorsque vous convertissez un tableau en plage de cellules, toutes les références aux cellules sont remplacées par leur référence absolue de type A1 équivalente. Lorsque vous convertissez une plage de cellules en tableau, Excel ne transforme pas automatiquement les références de cellules en références structurées.

  • Désactiver les en-têtes de colonne    Vous pouvez activer ou désactiver les en-têtes de colonnes et désactivation de l’onglet Création de tableau > Ligne d’en-tête. Si vous désactivez les en-têtes de colonnes, des références structurées qui utilisent des noms de colonne ne sont pas affectées, et vous pouvez toujours les utiliser dans les formules. Références qui font directement réfèrent aux en-têtes structurées (par exemple, = VentesDépt [[#Headers], [% Commission]]) créent #REF.

  • Ajouter ou supprimer des colonnes et lignes dans la table    Étant donné que les plages de données de tableau changent souvent, références de cellule des références structurées sont automatiquement ajustent. Par exemple, si vous utilisez un nom de table dans une formule pour compter toutes les cellules de données dans une table et que vous ajoutez une ligne de données, la référence de cellule ajuste automatiquement.

  • Modifier le 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éplacer, copier et remplir des 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.

    Remarque : Copier une référence structurée et effectuer un remplissage d’une référence structurée n’est pas la même chose. Lorsque vous copiez, toutes les références structurées restent identiques. En revanche, lorsque remplissez une formule, les références structurées pleinement qualifiées ajustent 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 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.

Vous avez besoin d’une aide supplémentaire ?

Vous pouvez toujours consulter un expert de la communauté technique Excel, obtenir une assistance dans la communauté Answers ou suggérer une nouvelle fonctionnalité ou une amélioration sur le forum Excel User Voice.

Rubriques connexes

Vue d’ensemble des tableaux Excel
vidéo : créer et mettre en forme un tableau Excel
totaliser les données d’un tableau Excel
mettre en forme un tableau Excel
redimensionner un tableau en ajoutant ou en supprimant des lignes et des colonnes
Filtrer des données dans une plage ou le tableau
convertir un tableau en plage
les problèmes de compatibilité des tableaux Excel
Exporter un tableau Excel dans SharePoint
une vue d’ensemble des formules dans Excel

Remarque : Avertissement traduction automatique : cet article a été traduit par un ordinateur, sans intervention humaine. Microsoft propose cette traduction automatique pour offrir aux personnes ne maîtrisant pas l’anglais l’accès au contenu relatif aux produits, services et technologies Microsoft. Comme cet article a été traduit automatiquement, il risque de contenir des erreurs de grammaire, de syntaxe ou de terminologie.

Développez vos compétences
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×