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

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 exemple de feuille de calcul, sélectionnez la cellule E2

2. Dans la barre de formule, entrez =C2*D2 , puis 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 une cellule du tableau pour afficher l’onglet Création de tableau sur 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.

Respectez les règles suivantes pour les noms de tableau :

• Utilisez des caractères valides  Commencez toujours par une lettre, un trait de soulignement (_) ou une barre oblique inverse (\). Utilisez ensuite des caractères alphanumériques, des points et des caractères de soulignement pour le reste du nom. Vous ne pouvez pas nommer un tableau « C », « c », « R » ou « r », car ces lettres servent déjà de raccourcis pour sélectionner la colonne ou la ligne de la cellule active lorsque vous les entrez dans la zone Nom ou Accéder à.

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

• N’utilisez pas d’espace pour séparer les mots  Le nom ne doit contenir aucun espace. Vous pouvez utiliser le caractère de soulignement (_) et le point ().) comme séparateur. Par exemple, VentesDépt, Taxe_Ventes ou Premier.Trimestre.

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

• Utiliser des noms de table uniques Les noms en double ne sont pas autorisés. Excel ne fait pas la distinction entre les caractères majuscules et minuscules dans les noms. Par conséquent, si vous entrez « Sales » mais que vous avez déjà un autre nom appelé « SALES » dans le même classeur, vous serez invité à choisir un nom unique.

• Utiliser un identificateur d’objet  Si vous envisagez d’avoir une combinaison de tables, de tableaux croisés dynamiques et de graphiques, il est judicieux de préfixer vos noms avec le type d’objet. Par exemple : tbl_Sales pour une table de ventes, pt_Sales pour un tableau croisé dynamique des ventes et chrt_Sales pour un graphique des ventes, ou ptchrt_Sales pour un graphique croisé dynamique des ventes. Cela conserve tous vos noms dans une liste triée 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, il est utile de comprendre la syntaxe de référence structurée. Examinons 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 :    DeptSales est un nom de table personnalisé. Il fait référence aux données du tableau, sans ligne d’en-tête ou de total. Vous pouvez utiliser un nom de tableau par défaut, tel que Tableau1, ou le remplacer par un nom personnalisé.

• Spécificateur    de colonne :[Sales Amount] et [Commission Amount] sont des spécificateurs de colonne qui utilisent les noms des colonnes qu’ils représentent. Ils font référence aux données de colonne, sans en-tête de colonne ou ligne de total. Placez toujours les spécificateurs entre crochets, comme illustré.

• Spécificateur d’élément :    [#Totals] et [#Data] sont des spécificateurs d’éléments spéciaux qui font référence à des parties spécifiques de la table, telles que la ligne totale.

• 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 :    (DeptSales[[[#Totals],[Sales Amount]] et DeptSales[[[#Data],[Commission Amount]] sont des références structurées, représentées par une chaîne qui commence par le nom de la 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 :

• Placez les spécificateurs entre crochets    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. Par exemple : =DeptSales[[Sales Person] :[Region]]

• 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 (/)

• À l’enseigne (@)

• Barre oblique inverse (\)

• Point d’exclamation ( !)

• Parenthèse gauche (()

• Parenthèse droite ())

• Signe de pourcentage (%)

• ? (point d’interrogation)

• Backtick (')

• Point-virgule (;)

• Tilde (~)

• Trait de soulignement (_)

• 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 qui ont besoin d’un caractère d’échappement (') dans la formule :

• Crochet ouvrant ([)

• Crochet fermant (])

• Signe dièse (#)

• Apostrophe (')

• À l’enseigne (@)

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.

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.

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 :

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.

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 s’il faut générer des références structurées pour les tables dans les semi-sélections    Par défaut, lorsque vous créez une formule, la sélection d’une plage de cellules dans un tableau permet de semi-sélectionner les cellules et d’entrer automatiquement une référence structurée au lieu 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 ce comportement en sélectionnant ou en désactivant la zone Utiliser les noms de table dans les formules case activée de > la boîte de dialogue Options > > Formules > Utilisation des formules.

• Utiliser des classeurs contenant des liens externes vers des tableaux Excel dans d’autres classeurs    Si un classeur contient un lien externe vers un tableau Excel dans un autre classeur, ce classeur source lié doit être ouvert dans Excel pour éviter les erreurs #REF ! dans le classeur de destination qui contient les liens. Si vous ouvrez d’abord le classeur de destination et que les erreurs #REF ! s’affichent, elles seront résolues si vous ouvrez ensuite le classeur source. Si vous ouvrez d’abord le classeur source, aucun code d’erreur ne devrait apparaître.

• 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 colonnes    Vous pouvez activer et désactiver les en-têtes de colonne de table à partir de l’onglet Création de tableau > Ligne d’en-tête. Si vous désactivez les en-têtes de colonnes de table, les références structurées qui utilisent des noms de colonnes ne sont pas affectées et vous pouvez toujours les utiliser dans les formules. Les références structurées qui font directement référence aux en-têtes de tableau (par exemple , =DeptSales[[[#Headers],[%Commission]]) entraînent des #REF.

• Ajouter ou supprimer des colonnes dans un tableau    Étant donné que les plages de données de table changent souvent, les références de cellule pour les 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 et si vous ajoutez par la suite une ligne de données, la référence des cellules s’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 : La copie d’une référence structurée et le remplissage d’une référence structurée ne sont pas la même chose. Lorsque vous copiez, toutes les références structurées restent les mêmes, tandis que lorsque vous remplissez une formule, les références structurées complètes ajustent les spécificateurs de colonne comme une série, comme résumé dans le tableau suivant.

Vous avez besoin d’une aide supplémentaire ?

Vous pouvez toujours poser des questions à un expert de la Communauté technique Excel ou obtenir une assistance dans la Communauté de support.

Rubriques connexes

Vue d’ensemble des tableaux
ExcelVidéo : Créer et mettre en forme un tableau
ExcelTotal des données d’un tableau
ExcelMettre en forme un tableau
ExcelRedimensionner une table en ajoutant ou en supprimant des lignes et des colonnes
Filtrer les données d’une plage ou d’une table
Convertir un tableau en plage
Problèmes de
compatibilité des tables ExcelExporter un tableau Excel vers SharePoint
Vue d’ensemble des formules dans Excel