Apprendre à utiliser les tableaux structurés Excel : création, manipulations et avantages(1)

Avec ce premier tutoriel d’une série de quatre, je poursuis un triple but :

1. Vous exposer les avantages de l’utilisation des tableaux structurés au sein de vos classeurs Excel ;
2. Vous montrer comment mettre en place et utiliser les tableaux structurés d’Excel ;
3. Vulgariser la mise en place de ces tableaux structurés afin que leur utilisation devienne un réflexe, tant il me semble aberrant, à l’heure actuelle, de rencontrer encore autant d’utilisateurs qui ne les connaissent pas ou qui les utilisent mal.

La version utilisée pour ce tutoriel est la version 2016. Les manipulations habituelles du tableau sont identiques depuis la version 2010. Certains outils ne sont apparus que dans les versions 2013 ou 2016. Ne soyez donc pas étonnés si, utilisant une version antérieure à Excel 2016, vous ne trouviez pas certains outils dont je parle ici (les segments par exemple, apparus avec Excel 2013).

Il se peut également que la dénomination de certains outils ou items de menu soit modifiée de version en version, chose habituelle chez Microsoft.

La version 2007, assez verbeuse, est généralement considérée comme un « coup d’essai ». Vieille de 11 ans à l’heure où j’écris ce tuto, je n’en mentionnerai pas les particularités ici.

Ce tutoriel s’adresse aux utilisateurs de tous niveaux, car je rencontre encore beaucoup trop souvent des personnes qui manipulent Excel depuis de nombreuses années et qui ne sont pas à l’aise avec cet outil, voire qui ne le connaissent tout simplement pas.

De façon résumée, créer et travailler avec un tableau structuré facilite et sécurise la manipulation des données avec Excel.

Avant les tableaux structurés, des manipulations courantes avec Excel telles que l’insertion de lignes, le tri des données ou la création de formules portant sur toutes les données d’une plage pouvaient vite être catastrophiques pour vos données (mauvaise sélection de la plage avant le tri des données, insertion d’une ligne sur une partie seulement des colonnes de la plage…) ou simplement être rendues plus complexes (utilisation de DECALER, mise à jour de la source d’un tableau croisé dynamique…).

Les tableaux structurés ont considérablement simplifié et sécurisé ces opérations. Je détaille plus loin dans ce tutoriel les avantages à travailler avec les tableaux structurés. Je les reprends de façon résumée ici pour fixer le cadre de travail et d’approche de cet outil d’Excel :

1. Les références structurées facilitent la rédaction et la compréhension des formules ;
2. Le tableau structuré grandit lors de l’ajout de nouvelles données ;
3. Le tableau structuré utilise les colonnes formulées pour garantir la pertinence et la justesse des informations stockées dans ledit tableau ;
4. Le tri, l’insertion et la suppression sont sécurisés dans un tableau structuré ;
5. Les mises en forme, y compris les mises en forme conditionnelles, sont propagées sur les nouvelles lignes de données ;
6. Les tableaux croisés dynamiques s’appuyant sur des tableaux structurés sont plus aisés à maintenir.

Un tableau structuré est une table de données. C’est donc une plage, rectangulaire, organisée avec une première ligne d’entête suivie de lignes de données, chaque colonne représentant « un champ » et chaque ligne correspondant à un enregistrement.

On peut imaginer le tableau structuré comme une boite dans laquelle on stockerait des fiches reprenant des informations structurées de la même façon de fiche en fiche.

Dès lors que l’on rencontre des données qui sont organisées selon la définition et le schéma évoqués ci-dessus, il convient d’en modifier les plages en tableaux structurés.

Ce n’est à mon avis pas une option, mais une obligation pour qui veut produire des classeurs professionnels pérennes, évolutifs et surtout fiables. Sous la version 2007, les références structurées propres à ces tableaux étaient assez verbeuses, mais la version 2010 a corrigé le tir et je ne vois aujourd’hui aucune restriction à l’utilisation de cet outil d’Excel.

La création d’un tableau au départ de rien est très simple. Après avoir sélectionné la cellule qui sera celle du coin supérieur gauche du futur tableau, on crée le tableau en utilisant un des deux outils suivants :

• Insertion > Tableaux > Tableau ;
• Accueil > Styles > Mettre sous forme de tableau.

La seconde manière permet de choisir le style de tableau dès sa création.

Quelle que soit la manière choisie, Excel propose de créer le tableau et demande si la sélection comporte une ligne d’entête. Puisque nous partons d’une cellule vide, cocher ou non cette option ne changera rien. Le tableau sera créé, Excel ajoutera une cellule d’entête et la nommera Colonne1.

Il est également possible de créer un tableau en sélectionnant sur base d’une ligne de plusieurs cellules pour créer un tableau de plusieurs colonnes.

Dans ce cas, il sera important de cocher la case Mon tableau comporte des entêtes pour qu’Excel considère la première ligne comme entête.

Si vous oubliez de cocher la case, Excel considèrera la ligne comme une ligne de données et ajoutera une ligne d’entête.

Il n’est pas nécessaire de sélectionner la ligne d’entête entière. Il suffit qu’une cellule de l’entête soit active pour qu’Excel considère toutes les cellules de la ligne comme faisant partie de l’entête.

Pour convertir une plage en tableau structuré, il suffit d’activer une cellule du tableau, puis de procéder comme précédemment. Souvent, dans le cas d’une conversion de plage, la case Mon tableau comporte des entêtes sera cochée, mais il est utile de le vérifier.

La case Mon tableau comporte des entêtes ne sera pas cochée lorsque toutes les données du tableau sont textuelles.

III-E-1. Nommage par défaut▲

III-E-2. Règles de nommage▲

Un nom de tableau doit commencer par une lettre et ne peut contenir que des lettres ou des chiffres ainsi que le caractère de soulignement _ (underscore). Ce nom est unique dans le classeur.

Lors du copier-coller d’un tableau complet, le nouveau tableau porte le nom de l’ancien suffixé d’un numéro d’ordre. Il convient de renommer un tableau copié.

Les noms de tableaux sont considérés « comme des plages nommées », de sorte qu’ils figurent dans la liste déroulante des noms (à gauche de la barre de formule).
Truc : pour accéder à un tableau et le sélectionner, vous pouvez le choisir dans cette liste.

III-E-3. Truc de nommage▲

Pour retrouver rapidement vos tableaux lors de la saisie de formules, il est intéressant de préfixer les noms de vos tableaux en utilisant toujours le même préfixe.

Je propose t_, qui a l’avantage de faire penser à un tableau, mais aussi d’être un préfixe qui n’est pas utilisé par Excel, notamment pour les noms de fonctions.

Ainsi, lors de la saisie d’une formule, la saisie semi-automatique peut vous proposer les noms de tableaux regroupés, ce qui facilitera la saisie durant la rédaction de la formule.

Comme pour le nom d’une fonction lors de la saisie automatique, vous pourrez choisir le tableau souhaité :

• par double-clic ;
• en saisissant le début du nom du tableau jusqu’à n’avoir que lui dans liste, puis TAB ;
• avec les flèches haut/bas puis la touche TAB.

Nous verrons plus loin l’utilisation des références structurées au sein des formules Excel

III-F-1. Intitulés de colonnes uniques▲

III-F-2. Pas de lignes entièrement vides dans le tableau▲

III-F-2-a. Ligne vide▲

Excel gère sans problème des lignes entièrement vides dans le tableau. Un outil pour faire grandir le tableau — et donc préparer des lignes vides pour de nouvelles données — est d’ailleurs proposé sur le ruban et dans le coin inférieur droit de la dernière cellule du tableau.

Des lignes vides peuvent aussi être insérées au milieu du tableau grâce notamment au clic droit dans le tableau.

Cependant, je conseille vivement de ne laisser aucune ligne vide dans un tableau structuré. Les mécanismes de dimensionnement automatique lors de l’ajout de données, la recopie automatique des formules et mises en forme conditionnelles permettent aisément de ne pas devoir prévoir de nouvelles données.

La présence de lignes entièrement vides dans le tableau ne peut que nuire à l’analyse des données (TCD, filtres automatiques, formules…). Par exemple, si je veux compter le nombre de fiches dans mon tableau structuré, je peux utiliser la formule =Lignes(Tableau). Cependant, la fonction compte également les lignes vides, ce qui empêche une analyse correcte.

De plus, comme nous le verrons un peu plus loin, laisser des lignes vides dans le tableau complique la création d’une plage dynamique pour créer des listes déroulantes sur base d’une colonne du tableau.

III-F-2-b. Colonne vide▲

Au vu des mécanismes empêchant qu’une cellule d’intitulé soit vide, une colonne de tableau structuré ne sera jamais entièrement vide puisqu’elle aura toujours un entête. Néanmoins, une colonne vide ne sert pas à grand-chose.

III-F-3. Le tableau est isolé des autres données de la feuille▲

Il est toujours préférable d’isoler le tableau du reste de la feuille, notamment d’autres tableaux éventuellement présents sur la feuille.

Ne collez jamais deux tableaux l’un à côté ou l’un au-dessus de l’autre.
NB : coller deux tableaux ne les fusionne pas !

III-F-4. Idéalement, un seul tableau structuré par feuille▲

On peut accéder très facilement à un tableau structuré via la liste des plages nommées (Zone nom) située à gauche de la barre de formules. Il suffit de dérouler la liste et de cliquer sur le nom du tableau souhaité pour que sa plage de données soit sélectionnée.

Le gestionnaire de noms permet de visualiser la liste de tous les tableaux structurés du classeur. Au passage, remarquez que le nom du tableau fait explicitement référence à la plage de données du tableau. Remarquez également que la portée est bien le classeur et que, contrairement aux plages nommées, le nom du tableau structuré est unique pour le classeur, alors que plusieurs feuilles peuvent avoir des plages nommées de façon identique.

Lorsqu’une cellule du tableau est la cellule active, Excel propose le ruban spécifique du tableau structuré.

La présentation du ruban varie d’une version à l’autre, et certains outils, tels que les segments de tableau, ont été ajoutés à la version 2013.

IV-B-1. Propriétés▲

IV-B-1-a. Nom du tableau▲

Dans ce groupe du ruban, vous pouvez modifier le nom du tableau structuré. Bien entendu, les formules s’appuyant sur ce nom sont modifiées partout dans le classeur.

Ne confondez pas ce contrôle du ruban avec la zone de nom de la barre de formules. La confusion est fréquente en cas de sélection de l’entièreté de la zone de données du tableau structuré.

La modification du nom du tableau structuré n’est pas reportée en VBA !

IV-B-1-b. Redimensionnement▲

Comme son nom l’indique, cet outil permet de redimensionner le tableau. Il ouvre la fenêtre de dialogue permettant de définir une nouvelle zone pour le tableau.

Cet outil est plus ou moins équivalent au coin de redimensionnement du tableau structuré que vous trouvez dans le coin inférieur droit de la dernière cellule de données du tableau. Ce coin permet de redimensionner le tableau sur un axe à la fois.

Dans les faits, ces outils ne sont pas utilisés, Excel redimensionnant le tableau structuré en cours de saisie.

IV-B-2. Outils▲

IV-B-2-a. Tableau croisé dynamique▲

Cet outil, qui permet de créer un tableau croisé dynamique sur base des données du tableau structuré, n’est pas spécifique aux tableaux structurés. Néanmoins, il est intéressant de noter que, utilisé sur base d’un tableau structuré, il prend le tableau comme référence et non la plage classique de données. Cela signifie que lorsque les dimensions du tableau structuré seront modifiées, le TCD s’adaptera à ces nouvelles dimensions lors de son actualisation, sans qu’il soit besoin de modifier la source du TCD.

IV-B-2-b. Supprimer les doublons▲

Cet outil n’est pas spécifique aux tableaux structurés et est disponible également via l’onglet Données du ruban. Néanmoins, son utilisation avec un tableau structuré permet de sélectionner les colonnes de calcul des doublons sur base de leur intitulé, la case Mes données ont des en-têtes étant cochée par défaut.

IV-B-2-c. Convertir en plage▲

Le nom de l’outil est assez explicite. Il faut bien considérer qu’après la conversion, le tableau structuré n’existe plus. Il a été transformé en plage classique. Les références structurées ont été transformées en références classiques et elles ne s’adaptent plus automatiquement aux dimensions du tableau.

Dans les faits, cet outil est rarement utilisé, voire jamais, sauf si on convertit une plage en tableau alors qu’elle ne devrait pas l’être. Lorsque le classeur est en exploitation, cette conversion peut avoir de fâcheuses conséquences, proportionnelles aux avantages dus à l’utilisation des tableaux structurés.

IV-B-2-d. Insérer un segment▲

Un segment est, dans les faits, un filtre « ergonomique ». Il permet un filtrage rapide du tableau sur une ou plusieurs valeurs d’une colonne.

IV-B-2-d-1. Placement du segment▲

Pour utiliser un segment, cliquez sur l’outil puis cochez la colonne souhaitée pour le segment.

IV-B-2-d-2. Utilisation du segment▲

Pour filtrer sur une seule valeur du segment, il suffit de cliquer dessus.

On remarquera que le segment présente les données par ordre alphabétique.

Le segment permet une sélection multiple ou étendue. Pour sélectionner plusieurs éléments, cliquez sur le premier puis CTRL+Clic sur les suivants.

Pour une sélection multiple, vous pouvez aussi utiliser l’outil suivant :

Avec Clic sur une ligne du segment puis Shift+Clic sur une autre, vous sélectionnez une plage contiguë de valeurs.

Le filtre peut être relâché en cliquant sur l’outil « entonnoir » du segment.

Au passage, les aficionados des raccourcis clavier apprécieront le Alt+S et le Alt+C…

Ceux qui connaissent les segments des tableaux croisés dynamiques ne seront pas dépaysés. Il faut cependant noter que le filtre « chronologie » n’existe pas encore sur les tableaux structurés, alors qu’ils existent depuis XL2013 sur les TCD.

IV-B-2-d-3. Utilisation de plusieurs segments▲

Il est possible de positionner plusieurs segments sur un tableau structuré. Dans ce cas, les segments peuvent apporter une info « intéressante », car les éléments d’un segment qui ne sont pas disponibles selon la sélection sur un autre segment sont grisés (mais sélectionnables quand même).

On remarquera que les données non accessibles sont reléguées en bas de liste, par ordre alphabétique.

IV-B-2-d-4. Suppression d’un segment▲

Pour supprimer un segment, il faut cliquer sur l’objet puis presser la touche DEL. Notez que la suppression d’un segment ne supprime pas le filtre posé par ce segment. Notez également que les filtres automatiques et les segments sont synchronisés et peuvent être utilisés en même temps.

IV-B-3. Données de tableau externe▲

IV-B-4. Options de style de tableau▲

IV-B-4-a. Ligne d’entête▲

Cet outil permet d’afficher/masquer la ligne d’entête. Peu utilisé dans les faits.

IV-B-4-b. Ligne de total▲

La ligne de total affiche une ligne permettant une analyse basée sur la fonction SOUS.TOTAL. Cette fonction permet de ne prendre que les lignes filtrées en considération. Elle se place juste sous la dernière ligne de données du tableau. Par défaut lors de la première utilisation de la ligne de total sur un tableau, elle place la fonction SOUS.TOTAL sur la dernière colonne.

Pour rappel, cette fonction permet de ne tenir compte que des lignes filtrées du tableau.

Par défaut, la fonction ne tient compte que des lignes filtrées automatiquement (voir l’aide en ligne sur la fonction SOUS.TOTAL).

Lorsqu’une ligne de total est présente, vous pouvez ajouter une ligne de données dans le bas du tableau :

• en vous plaçant sur la dernière cellule de la dernière ligne de données puis en pressant la touche TAB ;
• par clic droit sur la ligne de total puis Insérer>Lignes du tableau en haut.

Dans les faits, on utilise assez peu cette ligne de total, du moins si l’on veut respecter une règle capitale d’Excel : ne pas mélanger les données et leur analyse/présentation.
J’en profite pour rappeler qu’Excel n’est pas un outil permettant de créer des listings…

IV-B-4-c. Autres outils de ce groupe▲

Les autres outils de ce groupe parlent d’eux-mêmes, et permettent de modifier l’aspect du tableau. Ils sont liés au dernier groupe du ruban Outils de tableau, à savoir le groupe Styles de tableau.

IV-B-5. Styles de tableau▲

Le clic droit dans le tableau est intéressant. Il contient bien entendu les outils habituels du clic droit sur une cellule et je ne reviendrai pas ici sur ces outils génériques censés être connus.

Je vais m’attarder sur deux blocs de commandes spécifiques aux tableaux structurés.

Vos yeux aguerris auront de suite remarqué que le bloc Insérer/Supprimer dans un tableau structuré n’est pas identique au bloc insérer/Supprimer d’une plage classique.

IV-C-1. Insérer, Supprimer, Sélectionner▲

IV-C-1-a. Insérer▲

Dans un tableau structuré, on ne peut insérer que des lignes entières ou des colonnes entières. Il n’est pas possible de « casser » des lignes ou des colonnes du tableau par une insertion partielle. De ce fait, il découle qu’il n’est pas nécessaire de sélectionner les lignes entières ou les colonnes entières pour insérer, Excel comprenant qu’il doit considérer l’entièreté des lignes/colonnes du tableau et RIEN QUE du tableau.

Ainsi, si je souhaite insérer deux lignes dans mon tableau, je sélectionne deux cellules au-dessus desquelles je souhaite insérer mes lignes et Excel fait le travail pour moi sans risque de casse, ni dans le tableau ni ailleurs sur la feuille.

Il en va de même pour les colonnes, bien entendu.

IV-C-1-b. Supprimer▲

La suppression fonctionne de la même manière, et l’outil supprimera les lignes entières des cellules sélectionnées uniquement pour les colonnes du tableau, ne supprimant rien hors de celui-ci.

Ici aussi, il en va de même pour les colonnes.

IV-C-1-c. Sélectionner▲

L’outil permet de sélectionner :

• les données des colonnes des cellules actives ;
• les données + l’entête + le total des colonnes des cellules actives ;
• les lignes entières des cellules actives.

IV-C-2. Table▲

IV-D-1. Préambule▲

Dans des classeurs un peu complexes, savoir vers quoi pointe une formule relève parfois de la gageure. En effet, dans l’exemple suivant, il n’est pas simple de savoir que Feuil1!B2 :B9 pointe vers la colonne Prénom d’une plage de données ou d’un tableau structuré.

Il me semble que l’utilisation d’une référence structurée permet de clarifier la formule et de la rendre plus lisible. En effet, t_Contacts[Prénom] dit clairement que l’on pointe vers la colonne Prénom du tableau structuré nommé t_Contacts (du moins, dès que l’on a compris le principe de nommer d’abord le tableau puis la colonne de celui-ci que l’on souhaite utiliser… 😉 )

De plus, comme nous allons le voir par la suite, nous n’aurons pas à nous préoccuper de l’emplacement du tableau structuré dans le classeur, surtout si nous utilisons l’astuce que j’ai énoncée et qui consiste à préfixer chaque nom de tableau par t_.

IV-D-2. Définition et syntaxe▲

IV-D-2-a. Définition▲

Une référence structurée est une référence qui utilise la structure d’un tableau structuré.

IV-D-2-b. Syntaxe générique d’une référence structurée utilisée à l’extérieur du tableau▲

IV-D-2-b-1. Zones d’un tableau structuré▲

Un tableau structuré est constitué de sept zones qui peuvent toutes être identifiées grâce à des références structurées. L’exemple ci-dessus s’appuie sur le tableau structuré nommé t_Contacts et je reprends pour chaque zone la référence structurée correspondante lorsque l’on est à l’extérieur du tableau :

1. Zone de données du tableau (Bleu) : simplement le nom du tableau => t_Contacts (le nom du tableau pointe vers sa zone de données, à l’exclusion des lignes d’entête et de total) ;
2. L’entièreté du tableau (Noir) ; le nom du tableau suivi de [#Tout] => t_Contacts[#Tout] ;
3. L’entête du tableau (Rouge) ; le nom du tableau suivi de [#En-Têtes] => t_Contacts[#En-têtes] ;
4. La ligne de total (Vert) : le nom du tableau suivi de [#Totaux] => t_Contacts[#Totaux] ;
5. La zone de données d’une colonne (Orange) : le nom du tableau suivi du nom de la colonne entre crochets => t_Contacts[Prénom] ;
6. La colonne complète du tableau (Jaune) : Un peu moins intuitif => t_Contacts[[#Tout];[Prénom]] ;
7. Une ligne complète du tableau lorsque l’on est sur la même ligne hors tableau(Rose) : Le nom du tableau suivi de [@] => t_Contacts[@].

Ces références structurées peuvent être utilisées dans n’importe quelle formule saisie dans une cellule, à la place des références classiques.

Lorsque le nom du tableau est utilisé seul, seule la zone de données est prise en compte. Lorsque la colonne est précisée seule derrière le nom du tableau, seules les données de cette colonne sont prises en compte.

Cela signifie que, dans ces deux cas, les entêtes et totaux ne sont pas repris dans la référence.

Il est également possible d’utiliser les références structurées pour définir une plage constituée de colonnes contiguës. Dans ce cas, on utilise le signe : comme pour une référence classique entre les noms des colonnes (pour rappel, [NomDeLaColonne]), et on encadre le tout avec des crochets :

=NBVAL(t_Contacts[[Prénom]:[Nom]])

Il n’existe pas de syntaxe particulière pour référencer deux colonnes non contiguës d’un tableau structuré. Pour les fonctions qui acceptent plusieurs arguments, il faudra passer chaque colonne en argument : =NBVAL(t_Contacts[ID];t_Contacts[Nom]). Pour les autres cas, il faudra passer par une plage nommée.

IV-D-2-c. Syntaxe de la référence structurée à l’intérieur du tableau▲

Les possibilités sont réduites, car il faut éviter les références circulaires. Il n’est possible d’utiliser que certaines références :

1. Une référence vers une cellule de la même ligne : [@ suivi du nom de la colonne => [@Salaire] ;
2. Une référence à une autre colonne : Le nom de la colonne entre crochets => [Salaire]

Voici un exemple d’une colonne formulée d’un tableau qui utilise les deux références ci-dessus pour calculer l’écart à la moyenne du salaire de chaque membre du personnel.

On notera qu’il n’existe pas de référence structurée pour une ligne de tableau placée sur une autre ligne de la feuille que celle de la cellule qui utilise la formule, que la formule soit dans ou hors du tableau.

Il n’est donc pas possible d’utiliser une référence structurée pour pointer vers une ligne particulière d’un tableau structuré. C’est normal dans la mesure où, sauf cas très spécifique (lignes déroulantes en cascade, par exemple), l’ordre des données dans un tableau structuré n’a aucune importance et ne peut en aucun cas influencer les résultats de formules Excel.

IV-D-3. Saisie▲

IV-D-3-a. Saisie « souris » d’une référence structurée▲

Par défaut, la sélection par pointage transforme une référence classique en référence structurée lorsque c’est possible. La sélection à la souris est simple et assez intuitive dans la majorité des cas.

Sélection d’une partie de colonne => pas de référence structurée.

Sélection de toutes les données d’une colonne => référence structurée.

Sélection, hors du tableau, d’une cellule du tableau sur une autre ligne que la ligne active : pas de référence structurée.

Sélection, hors du tableau, d’une cellule du tableau sur la même ligne que la cellule active => référence structurée.

Sélection de deux colonnes contiguës partielles => pas de référence structurée.

Sélection de toutes les lignes de données de deux colonnes contiguës => référence structurée.

Sélection, à l’intérieur du tableau, d’une cellule d’une autre ligne => pas de référence structurée.

Sélection, à l’intérieur du tableau, d’une cellule de la même ligne => référence structurée.

Sélection de toute la colonne du tableau, en ce compris les cellules d’entête et de total => référence structurée.

Sélection de x colonnes contiguës complètes => référence structurée.

Sélection d’une partie des données du tableau => pas de référence structurée.

Sélection de toutes les données du tableau => référence structurée.

La sélection des données d’une colonne peut aussi s’effectuer par clic sur l’entête de la colonne, dès l’apparition de la flèche noire.

Un second clic sélectionnera en plus les données d’entête et de total.

Il est possible de sélectionner les données de tout le tableau par un clic lors de l’apparition de la flèche noire dans le coin supérieur gauche de la première cellule d’entête.

Un second clic à cet endroit sélectionnera en plus les lignes d’entête et de total.

L’utilisation des références structurées lors de la sélection par pointage peut être désactivée dans les options d’Excel. Elles seront toujours utilisables à la saisie manuelle, mais la sélection par souris utilisera les références classiques.

IV-D-3-b. Saisie manuelle d’une référence structurée▲

Les noms des tableaux apparaissent lors de la saisie manuelle d’une formule, à l’instar des noms de fonctions. Vous bénéficiez donc de la saisie semi-automatique. En cela, les tableaux structurés se comportent comme des plages nommées.

Vous comprenez ici qu’il est intéressant de préfixer vos noms de tableaux, de manière à les avoir tous regroupés lors de la saisie du préfixe. Ici, on remarque que le tableau Tableau7, non préfixé, ne se retrouve pas dans la liste des tableaux préfixés.

Derrière le nom du tableau, on pourra saisir le crochet, pour qu’Excel continue à nous aider dans la saisie de la référence.

Vous remarquerez l’utilisation du # pour les parties spécifiques du tableau, ainsi que le signe @ qui permet de pointer vers la même ligne du tableau.

La liste des colonnes s’adapte à la saisie semi-automatique.

N’oubliez pas de fermer le crochet !!

IV-D-4. Copier-coller de références structurées▲

IV-D-4-a. Généralités▲

Vous avez probablement remarqué que l’on ne parle pas du signe $ avec les références structurées, ce qui signifie que l’on ne détermine pas qu’une référence structurée est absolue ou relative comme on devait le faire pour des références classiques (voir mon tuto sur le sujet : https://fauconnier.developpez.com/excel/bases/references/ )

Dès lors, comment pratiquer avec les références structurées ? Les concepteurs d’Excel ont un peu bouleversé nos habitudes de travail à ce sujet, et si j’en suis personnellement chagriné, je ne peux que constater et essayer de me plier à ce qui nous est imposé.

Avec les références classiques, c’est le concepteur de la formule qui décidait, en plaçant judicieusement le signe $, qu’une référence était absolue ou relative lors de la recopie. Avec les références structurées, c’est la personne qui copie qui décide ! Enfin, pas tout à fait, mais presque, car c’est la technique employée pour le copier-coller qui va déterminer si les références structurées sont relatives ou absolues. Et, sauf conception spécifique de la référence, le caractère absolu ou relatif de la référence s’appliquera à toutes les références structurées utilisées dans la formule.

Les références structurées pointant vers une colonne sont toujours absolues lors d’une recopie verticale. Les références structurées utilisant le signe @ sont toujours relatives lors d’une recopie verticale.

Le caractère relatif ou absolu de la référence structurée n’est donc déterminant que lors d’une recopie latérale d’une formule.

Les références structurées seront considérées comme relatives lors d’un copier-coller par poignée de recopie.

Les références structurées seront considérées comme absolues lors d’un copier-coller par les outils habituels de copier-coller (ruban, clic droit, raccourcis clavier).

IV-D-4-b. Recopie en utilisant la poignée de recopie▲

La poignée de recopie permet une recopie latérale d’une formule. Dans le cas de l’utilisation d’une référence structurée, cette façon de procéder va déterminer que la référence structurée est relative ! Les colonnes structurées vont donc glisser latéralement lors de la recopie.

IV-D-4-c. Copier-coller par les outils, le clic droit ou les raccourcis▲

Le copier-coller latéral par les outils du ruban (groupe Presse-papiers), par clic droit ou par les raccourcis clavier (CTRL+C puis ENTER ou CTRL+V) va déterminer que la référence est absolue ! Les colonnes référencées après collage seront identiques aux colonnes d’origine.

IV-D-4-d. Et si on a une formule avec une colonne absolue et une colonne relative ?▲

Si, lors du copier-coller, une colonne est absolue (elle ne doit donc pas bouger) et une colonne est relative (elle doit glisser), il va falloir utiliser une des solutions suivantes :

• utiliser les références classiques et placer judicieusement les $ ;
• référencer la colonne absolue en précisant qu’elle va de telle colonne à telle colonne (en fait, doubler son nom).

Le copier-coller via les outils, les raccourcis ou le clic droit considèrera toujours les références structurées comme absolues lors d’un copier-coller latéral, de sorte qu’in fine, le concepteur de la formule n’est jamais certain de la façon dont sera interprétée sa formule lors d’un copier-coller.

Comme illustré ci-dessus, la saisie d’une formule utilisant des références structurées est souvent facilitée. Vous n’avez pas besoin de savoir où commence et où finit la plage structurée du tableau que vous voulez utiliser, notamment par clic pour la sélection de la colonne.

Comme nous l’avons vu plus haut, nous pouvons sélectionner une partie du tableau par saisie manuelle dans une formule. Dans la mesure où, péché capital oblige, on ne traite jamais les données du tableau sur la même feuille que lui (à part la ligne de total qui, dans les faits, n’est pas très utile), saisir des formules utilisant une référence structurée pour un tableau se trouvant sur une autre feuille est d’une simplicité enfantine.

Vous remarquez que durant la saisie, je ne me suis à aucun moment préoccupé de savoir où se trouvait mon tableau, quelle taille il avait, où se trouvait la colonne, etc. La saisie a été réalisée à l’aveugle.

Lors de notre survol des outils du tableau structuré, vous avez vu qu’Excel proposait de redimensionner le tableau.

Dans les faits, l’adaptation de la taille du tableau s’effectue de manière automatique, lors de l’ajout ou du retrait de lignes/colonnes.

V-B-1. Sans ligne de total▲

En saisissant une donnée à la suite du tableau, c’est-à-dire sur la ligne qui suit directement la dernière ligne de données, le tableau est automatiquement étendu à cette nouvelle ligne.

Excel propose d’annuler le développement automatique du tableau, mais il ne faut jamais utiliser cette option.

Cette extension est automatique aussi après collage de données à la suite de la dernière ligne du tableau.

Il en va de même pour les colonnes ajoutées directement à droite de la dernière colonne du tableau.

Il est donc important de toujours isoler le tableau du reste de la feuille par des cellules vides tout autour de lui. Bien entendu, si le tableau commence en A1, il n’est pas nécessaire d’insérer une ligne au-dessus ni une colonne à sa gauche.

Avec une plage classique, la référence à une colonne de données ne s’étend pas lors de l’ajout de données à la suite de la plage. Excel détectera ce problème et vous en informera, vous proposant même d’étendre la formule aux nouvelles saisies.

Mais, si vous acceptez de mettre à jour la formule…, ce sera ponctuel. De nouveaux ajouts vous imposeront de mettre votre formule à jour.

Pour pallier ce problème, il était parfois de coutume de prendre toute la colonne. C’était notamment le cas avec des RECHERCHEV et autres fonctions. Inutile de dire que c’est une méthode que je ne peux en aucun cas avaliser. La bonne méthode aurait consisté à passer par une plage dynamique. Encore fallait-il savoir comment faire et respecter les règles de la mise en œuvre de la solution.

Avec un tableau structuré, ce n’est pas pareil. La référence va s’étendre aux nouvelles lignes du tableau, même en utilisant des références classiques, puisque le tableau va s’étendre automatiquement aux nouvelles données saisies à la suite de la dernière ligne du tableau.

Cela sécurise évidemment les formules qui travaillent sur les colonnes de vos tableaux structurés, car vous n’avez plus à vous préoccuper de l’ajout de données. Excel prend cela en charge à votre place.

La référence structurée, qui utilise explicitement la colonne du tableau, s’adapter évidemment aussi aux ajouts ou retraits de lignes au sein du tableau structuré.

Vous aurez remarqué au passage que les références structurées ne connaissent pas la notion de référence absolue ou relative (voir mon tuto sur ces notions si elles ne sont pas claires pour vous.

V-D-1. Une formule saisie se propage à toute une colonne▲

Les règles capitales d’Excel veulent que dans un tableau, on utilise la même formule dans toute la colonne d’un tableau. La raison en est que, si la règle de gestion est modifiée et amène à une modification de la formule, on ne doive la modifier qu’une fois. C’est une règle capitale que je vois bien trop souvent bafouée dans les tableaux que je rencontre sur le forum ou chez mes clients.

Excel intègre cette règle capitale dans les tableaux structurés. Lors de la création d’une colonne formulée (vide au départ, donc), Excel va propager la formule dans toute la colonne du tableau.

Ici aussi, Excel permet de déroger à cette règle, mais je vous conseille vivement de n’en rien faire.

Lors de la modification de la formule, Excel adaptera automatiquement la formule sur toute la colonne.

Attention !!!

Excel adaptera la formule si vous avez respecté la règle ! Pour vous informer que la formule a été propagée, Excel affiche la balise active « éclair ». C’est le signe qui vous indique que la colonne est bien formulée.

Imaginons que vous ayez saisi à la main un des écarts à la moyenne (ne rigolez pas, je rencontre très régulièrement ce cas !). Excel vous en informe par le triangle vert ET la balise active (qui, au passage, permet de rétablir la colonne formulée).

Mais si vous n’en avez pas tenu compte et que vous modifiez la formule ailleurs dans la colonne, Excel ne veut plus jouer. Vous n’avez pas respecté les règles, Excel ne peut plus vous aider. Il va simplement (et trop discrètement) vous informer qu’il ne peut plus considérer la colonne comme formulée, grâce à la balise active « f(x) », mais que vous pouvez rétablir la situation.

Vous ne pouvez pas déroger à cette règle d’Excel, surtout dans des classeurs collaboratifs. Une colonne d’un tableau structuré qui contient une formule doit toujours être formulée, c’est-à-dire utiliser la même formule dans toute la colonne.

Vous courrez à la catastrophe en ne respectant pas cette règle.

V-D-2. Extension de la formule aux nouvelles lignes du tableau▲

Au-delà de la fiabilité de vos calculs qu’apporte la colonne formulée, l’ajout de lignes au tableau, que ce soit par insertion ou ajout en bas de tableau, va propager les formules des colonnes formulées aux nouvelles lignes du tableau.

V-D-3. Tableau vide, mais formule mémorisée▲

Une astuce intéressante (mais peu visible et qui peut vite être « écrasée »).

Les tableaux structurés permettent (enfin) de concevoir ses classeurs de façon professionnelle. Vous allez pouvoir ajouter des données de test et vérifier vos formules, vos graphiques, vos TCD et autres. Puis, au moment de passer en production, vous allez bien entendu supprimer les données de vos tableaux, c’est-à-dire en supprimer toutes les lignes.

Votre tableau est donc maintenant « vide », et Excel met à votre disposition une première ligne vide (sans données et… sans formules).

Vide ? Pas si sûr. À la saisie d’une donnée sur la ligne, Excel va ressusciter les formules des colonnes formulées.

Attention. Il ne faut donc pas vider le contenu de vos lignes, mais bien les supprimer.

Faites attention également qu’Excel n’indique nulle part qu’une formule a été mémorisée pour une colonne. On peut considérer que c’est dommage, mais c’est ainsi.

Dès lors, pour supprimer une formule dans une cellule, il convient de vider le contenu de toute la cellule.

Dans une plage classique, le tri pouvait poser problème en fonction de la sélection active au moment du tri, parfois sans qu’Excel ne fasse la moindre remarque. L’image suivante montre le résultat d’un tri croissant sur une partie du tableau. Avouez que les jeux de mots sont beaucoup moins amusants. Remarquez surtout que ce tri malencontreux a fait passer des données d’une ligne sur l’autre, et a donc mélangé les informations. Sauf à s’en rendre compte de suite, c’est la catastrophe !

Dans un tableau structuré, Excel ne tient aucun compte de la sélection et va faire glisser les lignes entières du tableau. L’illustration suivante montre que ce sont bien les lignes entières du tableau qui ont été triées sur base de la colonne de la cellule active. Ouf ! Carine Hainat et Julie Hénas peuvent se reprendre un petit ballon.

Dans une plage classique, l’insertion de données sur une sélection malheureuse pouvait donner des résultats problématiques, créant un « trou » en décalant vers la droite…

Ou vers le bas…

Un tableau structuré résout le problème, car Excel fait, à nouveau, bouger les lignes entières en les décalant vers le bas.

Vous remarquerez que les items du clic droit ne sont pas identiques pour la plage classique et le tableau structuré.

Le fonctionnement pour la suppression de lignes est identique, de sorte que je n’ai pas besoin de l’illustrer ici.

Autre trouvaille intéressante, si une MFC (Mise en Forme Conditionnelle) a été placée sur toute une colonne d’un tableau structuré, elle se propage aux nouvelles lignes du tableau.

V-H-1. Tableau croisé dynamique▲

Sur base d’une plage classique, l’actualisation du TCD ne portait que sur les données de la plage source renseignée. L’ajout de lignes dans la source n’était pas pris en compte, et il fallait adapter la source du TCD soit manuellement, soit par programmation.

Le tableau structuré n’impose pas cette contrainte, et l’actualisation du TCD prend en compte tout le tableau, et donc aussi les nouvelles données.

V-H-2. PowerPivot et PowerQuery▲

Comme je l’ai déjà dit plus haut, je n’utilise quasiment jamais la ligne de total, préférant séparer les données de leur analyse.

Toutefois, l’outil étant présent, il peut être intéressant de savoir que lorsque vous paramétrez la ligne de total, c’est-à-dire lorsque vous en personnalisez les formules, celles-ci restent mémorisées même si vous masquez la ligne et sont toujours présentes lorsque vous affichez à nouveau la ligne de total.

V-J-1. Tableau structuré et colonnes sont dynamiques▲

V-J-2. Créer une plage dynamique sur base d’un tableau▲

Il est néanmoins des situations où il est intéressant de pouvoir créer une plage dynamique sur base de tout ou partie d’un tableau structuré (tout le tableau, une colonne, plusieurs colonnes contiguës), notamment parce que certains outils d’Excel n’ont pas intégré les références structurées.

Pour créer une plage nommée dynamique sur base d’une colonne d’un tableau structuré, il suffit de sélectionner la colonne (souvent, uniquement les données), puis de créer la plage nommée.

Et voilà. Adieu DECALER (pour les plages dynamiques basées sur un tableau structuré…).

Depuis toujours, Excel permet de figer les volets, c’est-à-dire de laisser visibles de façon permanentes x lignes et y colonnes en haut et à gauche de la feuille. Lors de la manipulation de grandes plages de données, ces possibilités sont évidemment pratiques pour « savoir où on est ».

C’est très pratique puisque, comme illustré dans la copie d’écran ci-dessous, cela permet de garder un œil sur les premières ligne et colonne de la plage.

Avec l’arrivée des tableaux structurés, Excel a automatisé le gel de la ligne d’entête du tableau, où que le tableau se trouve sur la feuille, tant que la cellule active fait partie du tableau. Cela est réalisé par un basculement de l’affichage des entêtes de colonnes qui remplace les lettres classiques par les intitulés du tableau.

Ainsi, si l’on déroule vers le bas dans le tableau suivant, qui commence en ligne 4, les intitulés du tableau remplaceront les lettres B à G dès que la ligne 4 sera rendue invisible par le défilement, pour autant que la cellule active soit dans le tableau et qu’il y ait au moins une ligne du tableau visible.

Notez que, depuis la version Excel 2010, les outils de filtre sont présents sur la ligne d’entête.

Notez bien que ce basculement des entêtes n’est actif que si la cellule active fait partie du tableau et pour autant qu’au moins une ligne du tableau soit affichée.

Dans l’illustration suivante, la cellule active est hors tableau et les intitulés du tableau n’ont pas basculé sur les entêtes de la feuille.

Dans l’illustration suivante, la cellule active fait partie du tableau mais il n’y a plus de lignes du tableau visibles. On récupère donc les entêtes classiques.

Les tableaux structurés ont vu le jour officiellement avec la version 2007, même si les listes de données existaient déjà dans la version 2003 de façon très discrète.

Il en découle que seuls les fichiers XLSX, XSLM et XSLB supportent les tableaux structurés. Vous pouvez placer un tableau structuré dans un XLS, mais il sera remplacé à l’enregistrement par une plage de données classique et ses références structurées seront transformées en références classiques. Bien entendu, Excel vous avertira des risques encourus.

Vous remarquez ici que les références structurées ont été remplacées par des références classiques :

• =[@Salaire]-MOYENNE([Salaire]) ==> =Feuil1!$D2-MOYENNE(Feuil1!$D$2:$D$10)
• =MOYENNE(t_Contacts[Salaire]) ==> =MOYENNE(Feuil1!$D$2:$D$10)

Sauf à modifier des données de cellules existantes du tableau structuré, la protection de la feuille, quelles que soient les permissions cochées (même l’ajout/suppression de lignes ou de colonnes) ne permettent pas de manipuler le tableau structuré. Les outils d’ajout et de modification sont grisés.

C’est pour moi une absurdité, mais je conçois qu’il était malaisé de permettre les modifications puisqu’elles impactent peu ou prou le reste de la feuille.

On devra donc considérer qu’un tableau structuré ne pourra être verrouillé, ce qui rendra impossible le verrouillage des formules.

VII-C-1. Outils▲

VII-C-2. Solutions▲

VII-C-2-a. Plages nommées▲

La solution que je préconise pour la MFC comme pour la validation est de créer des plages dynamiques s’appuyant sur le tableau ou sur ses colonnes. Le cas typique de cette mise en place est la liste déroulante de validation.

Je vais illustrer ce propos en ajoutant une colonne Service au tableau structuré des contacts et en permettant la saisie du service sur base d’une liste déroulante utilisant les valeurs d’un tableau t_Services.

Les précisions données ici valent pour la version 2016. Ces outils sont régulièrement adaptés par les concepteurs d’Excel, de sorte qu’il est possible qu’une nouvelle version permette des manipulations plus aisées, notamment pour la création de listes déroulantes de validation.

VII-C-2-a-1. Création du tableau des services▲

La création du tableau des services n’est pas un problème. Pour le confort de l’utilisateur, il est préférable de trier les services par ordre croissant.

VII-C-2-a-2. Création de la plage dynamique.▲

La plage dynamique se crée elle aussi très facilement. Il suffit de sélectionner les données de la colonne Service du tableau t_Services et de créer la plage en lui attribuant un nom dans la zone des noms. N’oubliez pas de presser la touche ENTER pour valider votre choix.

Pas de panique, Excel n’affichera pas ce nom, car t_Services, créé antérieurement, est prioritaire.

VII-C-2-a-3. Utilisation au sein du tableau des contacts▲

Dans le tableau des contacts, après avoir créé si elle n’existe pas puis sélectionné la colonne de données Service, il suffit de créer une validation s’appuyant sur une liste, puis de saisir le nom L_Services ou de le sélectionner (F3).

Excel expose alors les données dans la liste déroulante et il suffira d’y effectuer son choix. La liste sera adaptée lors de l’ajout, le retrait ou le tri des données dans le tableau des services.

VII-C-2-b. Utilisation de la fonction INDIRECT▲

Bien que je préfère les plages nommées, il est également possible de contourner l’obstacle en utilisant la fonction INDIRECT. Cette fonction utilise un argument de type texte pour pointer vers une référence par ricochet. L’argument doit donc représenter une référence valide, c’est-à-dire :

• une adresse de plage ;
• le nom d’une plage nommée ;
• une référence structurée.

Excel a lié les sources de données externes aux tableaux structurés. Cela signifie que lorsque vous importez des données grâce aux outils d’importation et de liaison de données, le résultat produit dans Excel EST un tableau structuré.

Le présent tutoriel n’a pas pour but d’expliquer en détail les possibilités de liaisons à des données externes, mais d’illustrer ces liaisons dans le cadre de l’utilisation des tableaux structurés.

L’onglet Données du ruban présente un premier groupe d’outils permettant d’importer des données depuis des sources (fichier texte, base Access, données du Web, SQL) dont la diversité dépend en gros des pilotes d’accès aux données présents sur votre ordinateur.

Pour illustrer ce propos, nous allons importer une table Access dans Excel.

Vous allez :

• préciser le fichier Access dont vous voulez récupérer des données en Excel ;
• si plusieurs sources de données existent (tables, requêtes), choisir la source à lier à Excel ;
• Préciser sous quelle forme vous allez réaliser l’importation.

Après avoir cliqué sur OK, Excel importe les données de la source et crée une liaison qui permettra la mise à jour des données. On remarque bien qu’Excel a créé un tableau structuré, que vous allez bien entendu renommer, comme pour un tableau structuré normal.

Pour mettre les données à jour, il suffit d’actualiser par clic droit, par le ruban Données ainsi que par ALT+F5. Par défaut, les colonnes formulées sont gardées après la mise à jour.

Vous pouvez accéder aux propriétés de la liaison par l’onglet Données ou par l’onglet spécifique du tableau lorsqu’il est actif. Vous pourrez ainsi automatiser la mise à jour, notamment pour qu’il y ait un rafraîchissement des données à l’ouverture.

L’onglet Définition donne accès aux données techniques de la liaison :

• chaine de connexion ;
• requête SQL.

On pourrait, par exemple, remplacer les propriétés Tableau et Contact par une commande SQL :