Tableaux structurés Excel – fauconnier.developpez.com

Les tableaux structurés vous aident à gérer et analyser vos données de façon sûre, simple et pérenne.

Apparues très timidement dans la version 2003, les listes de données permettaient à une plage de saisie de s’agrandir dès l’ajout de nouvelles données. L’accès à l’outil était caché dans un sous-menu et, de ce fait, les listes étaient peu connues et donc peu utilisées. La version 2007 a mis au jour cet outil dans une version rafraîchie quoiqu’un peu verbeuse, mais l’apparition des tableaux structurés justifiait à elle seule le passage à cette nouvelle version. L’engouement des utilisateurs fut cependant unanime et la version 2010 exposa un outil mature et efficace. L’outil acquérait de nouvelles fonctionnalités en 2013 et 2016 et il est aujourd’hui un élément incontournable d’une utilisation professionnelle d’Excel.

Dans ce premier tutoriel, vous vous familiariserez avec la création et la manipulation des tableaux structurés dans l’environnement Excel. 29 commentaires Donner une note à l´article (5) 
Article lu   fois.
Profil ProSite personnel
Publié le – Mis à jour le 7 janvier 2019 
Public visé : tout le monde
Version PDF Version hors-ligne
ePub, Azw et Mobi
Avec ce premier tutoriel d’une série de quatre, je poursuis un triple but :
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 :
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 :
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.
Par défaut, le tableau structuré est nommé Tableau suffixé d’un numéro d’ordre. Ce n’est évidemment pas pratique et il convient de renommer chaque tableau créé.
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.
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é :
Nous verrons plus loin l’utilisation des références structurées au sein des formules Excel
Cela peut sembler couler de source, mais, dans un tableau structuré, les noms de colonne sont uniques et obligatoires. Pas question en effet d’avoir deux colonnes qui auraient Nom comme intitulé, pas question non plus d’avoir une colonne qui n’a pas de nom.
Si vous nommez une colonne à l’identique d’une autre, Excel suffixera ce nom avec un numéro d’ordre. Si vous supprimez l’intitulé d’une colonne, Excel la renommera Colonne suivi d’un numéro d’ordre.
Si vous renommez une colonne d’un tableau structuré, les formules Excel s’appuyant sur cette colonne sont adaptées.
La modification du nom d’une colonne d’un tableau structuré n’est pas reportée en VBA !
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.
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.
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 !
La règle de base est de ne placer qu’un tableau par feuille. Le placer en A1 n’est cependant pas obligatoire.
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.
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 !
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.
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.
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.
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.
Un segment est, dans les faits, un filtre « ergonomique ». Il permet un filtrage rapide du tableau sur une ou plusieurs valeurs d’une colonne.
Pour utiliser un segment, cliquez sur l’outil puis cochez la colonne souhaitée pour le 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.
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.
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.
L’outil d’export permet d’envoyer un tableau à :
Ces outils sont liés à des données Externes et ne sont par voie de conséquence disponibles que lorsque le tableau est lié à une source externe de données.
Cet outil permet d’afficher/masquer la ligne d’entête. Peu utilisé dans les faits.
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 :
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…
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.
Vous pourrez, grâce à la galerie des styles de tableau, modifier l’aspect esthétique (ou pas) de vos tableaux structurés. Notez que ces styles sont bien entendu liés au thème utilisé dans votre classeur.
Les outils affichés lorsque la galerie est ouverte permettent :
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.
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.
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.
L’outil permet de sélectionner :
Les outils de table permettent :
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_.
Une référence structurée est une référence qui utilise la structure 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 :
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.
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 :
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.
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.
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 !!
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).
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.
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.
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 :
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.
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.
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.
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.
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.
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.
PowerPivot et PowerQuery, qui permettent tout deux d’assembler des données de diverses sources pour les traiter avant de les mettre à disposition de l’analyse, par exemple au travers d’un TCD, sont plus stables pour les données EXCEL qui leur sont fournies en tableau structuré. Ici aussi, on appréciera de ne pas devoir redéfinir les données alimentant ces outils, puisque le tableau structuré les présente de manière dynamique.
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.
Nous l’avons vu, le tableau structuré s’ajuste à l’ajout et à la suppression de lignes (et de colonnes). Les formules qui s’appuient sur un tableau structuré, qu’elles utilisent les références classiques ou structurées, s’adaptent donc elles aussi aux modifications des dimensions du tableau structuré, de sorte qu’il n’est presque plus nécessaire de devoir recourir à la fonction DECALER pour créer des plages de données dynamiques.
La fonction DECALER reste une fonction très intéressante et utile d’Excel dans d’autres cas d’utilisation.
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.
Quelques raccourcis clavier intéressants, ou pas. Mis à part CTRL+L, il faut être dans le tableau.
CTRL+L : Créer un tableau structuré (anciennement Liste, d’où le L) ;
CTRL+A
CTRL+Espace :
SHIFT+Espace : dans une cellule de données ou plusieurs cellules de données contiguës en colonne, sélectionne les lignes entières du tableau des cellules sélectionnées, un second SHIFT+Espace sélectionne les lignes entières de la feuille) ;
CTRL + + Ajoute une ligne si une seule cellule saisie en colonne et ajoute une colonne si deux ou plusieurs cellules saisies en colonnes ;
CTRL + – Supprime une ligne ou une colonne dans les mêmes conditions que CTRL + +
Quelques autres raccourcis un peu tirés par les cheveux sont expliqués dans mon billet de blog : https://www.developpez.net/forums/blogs/27262-pierre-fauconnier/b4695/excel-quelques-raccourcis-selection-tables-donnees/
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 :
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.
L’outil de mise en forme conditionnelle n’accepte pas les références structurées lorsque vous créez une MFC formulée. Une référence classique ne sera adaptée à l’ajout/suppression de valeurs dans les plages utilisées pour la référence structurée que si cette plage se trouve sur la même feuille. Les règles de bonne conception vous obligeant à placer cette plage sur une autre feuille, les plages renseignées dans la formule de MFC ne seront pas dynamiques.
L’outil de validation n’accepte pas non plus les références structurées. Lors de l’utilisation d’une référence structurée, Excel affiche un message d’erreur.
Il est possible de remplacer la référence structurée par une référence classique, mais celle-ci s’adaptera à la référence structurée uniquement si cette dernière pointe vers une plage de la même feuille. Si, selon les règles d’une bonne conception de classeur, votre référence de validation pointe vers une autre feuille, la référence classique ne sera pas dynamique, et vous devrez l’adapter à chaque ajout/suppression de données dans la plage qui alimente la validation (liste déroulante, formule personnalisée…).
Les critères formulés acceptent les références structurées. Si vous saisissez le nom du tableau source dans l’outil de filtre avancé, Excel le reconnaît mais le transforme en références classiques. Ces références s’adaptent aux nouvelles lignes lors de l’utilisation ultérieure de l’outil de filtre avancé. Par contre, Excel n’accepte pas l’extraction vers un autre tableau structuré.
Dans la mesure où l’outil doit être relancé lors d’une mise à jour de l’extraction, cette limitation a dans les faits peu de conséquences.
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.
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.
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.
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.
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 :
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.
Il n’est pas nécessaire de posséder Access pour pouvoir importer une table Access dans Excel. Le moteur de données permettant l’importation est normalement installé par défaut.
Merci de me signaler en commentaires les problèmes éventuellement rencontrés lors des manipulations d’importation.
Vous allez :
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.
Notez que malgré sa liaison aux données, vous pouvez supprimer, ajouter, modifier des données au sein du tableau. Toutefois, ces données modifiées seront remplacées par celle de la BD source lors de la prochaine mise à jour.
Notez également que la protection de la feuille empêchera la mise à jour du tableau.
Notez enfin qu’Excel ne crée pas de tableau structuré ni de liaison directe avec un fichier TXT.
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 :
On pourrait, par exemple, remplacer les propriétés Tableau et Contact par une commande SQL :
L’utilisation des tableaux structurés n’est, à mon avis, pas une option, mais bien une obligation pour un travail professionnel avec Excel. Les quelques inconvénients liés à la copie de formules ou aux outils qui n’acceptent pas les tableaux structurés, et qui peuvent alors être palliés assez facilement, ne sont rien en rapport à la fiabilité, la souplesse et l’évolutivité que les tableaux structurés offrent en échange de bien peu de contraintes.
Dans le tutoriel sur les tableaux structurés en VBA, nous verrons que là aussi, cet outil apporte une simplification extraordinaire du code, l’utilisation des ListObjects (tableaux structurés en VBA) et des références structurées permettant de créer du code qui ne devra être adapté ni à la taille du tableau après ajout ou suppression de données, ni à son déplacement sur une autre feuille, par exemple.
Le troisième tutoriel permettra de mettre en place un modèle de conception pour l’accès aux données selon le modèle CRUD (Create, Read, Update, Delete :>> ajout, lecture, modification et suppression de données). Le quatrième tutoriel fera la jonction entre formulaire lié aux données et tableaux structurés.
Je remercie vivement :
Qwazerty, Philippe Tulliez et 78Chris pour leurs avis techniques ;
dourouc05 et f-leb pour la relecture orthographique.
Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d’auteur. Copyright © 2018 Pierre Fauconnier. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l’ensemble de son contenu : textes, documents, images, etc. sans l’autorisation expresse de l’auteur. Sinon vous encourez selon la loi jusqu’à trois ans de prison et jusqu’à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à Developpez LLC.
© 2000-2022 – www.developpez.com

source

A propos de l'auteur

Backlink pro

Ajouter un commentaire