Les tableaux structurés dans Excel

Cet outil, pourtant sorti en 2007, continue d'être parfois mal compris. Donc voici une série d'astuces pour remettre les pendules à l'heure.

Utilisation de base   ↑↑↑

Un tableau est généralement constitué d'une colonne avec une clef primaire (pas indispensable, mais recommandée), d'une ligne d'en-têtes et de données. Dans les données, on distingue les données saisies manuellement et les données calculées. Il y a d'autres nuances, mais ce sera pour une prochaine fois ?.

Pour mettre des données sous forme de tableau, il suffit de se positionner sur vos données, puis de cliquer ici (menu Accueil) en choisissant la couleur que vous préférez.

Une fois le tableau créé, vous avez énormément d'avantages...

"Mettre sous forme de tableau" ne fait pas que mettre en forme, mais ce bouton ajoute un objet tableau à la feuille   ↑↑↑

C'est l'une des choses les plus importantes à comprendre au départ. L'intitulé de ce bouton ne représente qu'une toute petite partie de ce que les tableaux peuvent faire. Les fonctionnalités vont bien au delà de la mise en forme.

Les avantages les plus faciles à comprendre   ↑↑↑

• Les formules, formats et validations vont se recopier automatiquement vers le bas lors de l'ajout d'une nouvelle ligne. Donc, si vous ajoutez une nouvelle ligne, hop, tout est prêt pour la saisie.

• Vous pouvez colorier une ligne sur deux en fonction de vos paramètres.

• Le déplacement vers le bas va transformer les intitulés de colonnes (A,B,C, etc.) en nom de colonnes.

• Les formules sont plus lisibles, une fois que l'on a compris que :
  • @ veut dire sur la même ligne, par exemple =[@[ID Produit]]. Exemple ici en colonne E.

• Les tableaux peuvent être nommés, ce qui facilitent encore la lecture.
  Au lieu d'avoir =SOMME(D2:D4) on va avoir =SOMME(T_Transactions[Quantité]).
  Pour nommer le tableau, ne pas confondre la zone du nom du tableau (onglet Création de tableau) avec la zone de nom (à gauche de la barre de formule)

• Les [ ] indiquent les noms de colonnes.
  Par exemple : =SOMME(T_Transactions[Quantité]) veut dire faire la somme de la colonne Quantité du tableau T_Transactions

• Un tableau croisé dynamique basé sur une table va automatiquement prendre en compte les nouvelles lignes.

Les astuces de pro   ↑↑↑

• CTRL+L (Tableau = ListObject) permet d'ajouter directement un tableau à la plage. Un bon réflexe est de donner tout de suite un nom intelligent à votre tableau. Ma nomenclature est d'utiliser T_ pour le début de ce nom. N'oubliez pas de cocher "Mon tableau comporte des en-têtes".

• CTRL+MAJ+L, une fois, puis deux fois : ajouter ou supprimer des filtres.
  Ultra-pratique pour enlever tous les filtres actifs en une seconde.
  C'est l'équivalent de cette fonction :

• CTRL+Espace sélectionne la colonne de données directement.
  Une deuxième fois permet de sélectionner la colonne de données + l'en-tête.
  Une troisième fois la colonne de la feuille au complet.
  CTRL + Espace fonctionne aussi pendant l'édition d'une formule, pas juste pour une sélection de plage.
• Un nom basé sur une colonne d'un tableau s'ajuste automatiquement à la taille du tableau.
  On peut taper le nom directement dans la zone de nom et valider par Entrée.
  Ou alors, Formules-Gestionnaire de Noms-Nouveau..., entrer le nom et choisir la colonne du tableau.
  A quoi ca sert ? Deux exemples en dessous...

• Les noms définis sur une colonne sont très pratiques pour les listes de validations basées sur d'autres feuilles.
  Sur la même feuille, pas besoin de passer par un nom défini, on peut directement faire référence à la colonne du tableau.

• Les noms définis figent la colonne.
  Si on recopie vers la droite avec la poignée de recopie, le nom d'une formule ne change pas, alors que la référence à la colonne du tableau va changer, car considéré comme non figée.
  Voir l'exemple complet plus bas pour bien comprendre.
• On peut aussi faire référence à ces noms dans VBA sans grand risque pour le code si le nom de la colonne du tableau change. Le code va ressembler à ceci : Range("NomColonneTableau").

Autres avantages avec Modern Excel   ↑↑↑

• Dans une formule, si vous faites référence directement à une colonne, la formule est automatiquement propagée.
  Par exemple, =T_Dépenses[Montant] va se propager vers le bas. Donc l'ajout de nouvelles lignes va être automatiquement prise en compte. On pourra aussi faire référence à cette plage avec un # à la fin. Très pratique pour séparer la saisie des données du reporting avec des listes.

• Vous pouvez charger des tables dans Power Query en un clic.
  Power Query est un outil d'ETL (extraction, transformation et chargement/load).
  Une fois sur un tableau, vous pouvez charger les données directement dans Power Query pour les transformer (Données - À partir d'un tableau ou d'une plage).

• Exemples d'utilisation de tableau chargés dans Power Query :
  • Comme paramètres, par exemple pour stocker un chemin d'accès.
  • Comme source de données
  • Voire comme outil avancé pour conserver les données saisies dans Excel lors d'une liaison avec des données externes. Un cas très pratique et très utile.
• Gestion des boîtes à cocher.
  Cette fonctionnalité est encore en développement, mais si vous avez insider, vous avez peut être déjà du la voir passer. Pour le moment, la recopie vers le bas des checkbox n'est pas fonctionnelle lors de l'ajout de nouvelles lignes (merci à Nicolas Brabant pour me l'avoir fait remarquer).

Utilisation des segments   ↑↑↑

Évidemment, on peut ajouter des segments à un tableau, ce qui simplifie considérablement le filtrage et la construction de tableaux de bord.

Astuce 1 : Identifier facilement les lignes visibles

Ajouter une colonne avec sous.total ou agregat pour identifier si la colonne a été filtrée ou pas, ce qui permet ensuite de récupérer les données via la fonction FILTRE.

Ici, en colonne H, on a =SOUS.TOTAL(3;[@[ID Détail transaction]])

Sur une autre feuille, on peut reprendre tout ce qui est visible :
=FILTRE(T_Transactions[[ID Produit]:[Quantité]];T_Transactions[Filtre])

Astuce 2 : Garder en permanence tous vos segments à l'écran

• Ajouter les segments sur une autre feuille.
• Affichage, Nouvelle fenêtre.
• Affichage, décocher ce qui ne vous intéresse pas (quadrillage, barre de formule, en-têtes)
• Lancer PowerToys.
• Windows+CTRL+T pour garder la fenêtre toujours à l'écran.

Alternativement à PowerToys, on peut aussi déplacer la nouvelle fenêtre sur un autre écran.

Pour tout faire en VBA, avec un simple bouton, je vous invite à vous procurer le logiciel super-segments disponible avec ce séminaire.

Vidéo et démonstration complète ici

Ce qu'il manque      ↑↑↑

Des comportements différents en fonction de la manière de recopier des formules.

Pour "figer" une colonne, on va devoir "répéter" la colonne .
Par exemple : =SOMME(T_Transactions[[Montant]:[Montant]]).
Honnêtement, si MS pouvait trouver une alternative, ou même simplement appliquer la touche de raccourci F4 à cette approche, ce serait vraiment chouette. Par manque de connaissance, paresse et difficulté de manipulation, on ne recours que rarement à cette option, or, elle permet de solidifier et sécuriser l'ensemble de l'application.

Vous pouvez tester la différence de comportement avec une formule de type =SOMME(T_Transactions[Quantité]) :

• Copier et coller la cellule. (CTRL+D fonctionne aussi de la même manière, merci à Nicolas Brabant pour l'astuce).
• Recopier vers la droite avec la poignée de recopie.

Soit le tableau suivant :

Dans les lignes trois et quatre, on voit ce qui se passe si on fige la référence, alors que la ligne 2 retourne la valeur 4 qui ne correspond à rien.

On peut aussi utiliser un nom dans une formule (donc une alternative à la syntaxe précédente pour figer une colonne).

PS : dans certains cas, il peut être utile de ne pas figer la recopie vers la droite, mais basé sur mon expérience, c'est assez rare.

Gestion des formules appartenant à une colonne (i.e. colonnes calculées).

• Lorsqu'on ajoute une formule dans une colonne, il arrive parfois que celle-ci n'appartienne plus ensuite à cette colonne. L'ajout d'une nouvelle ligne indique si la colonne a toujours une formule ou si celle-ci a été "oubliée" par excel. Idéalement, on pourrait s'attendre à une meilleure façon de connaitre cette présence de formule. 30 secondes pour mieux comprendre la propagation des formules sur une colonne.
• De la même manière, pouvoir faire "oublier" une formule à une colonne pourrait aussi être très pratique dans certains cas. Un copier coller en valeur semble résoudre le problème dans lamajorité des cas, mais j'ai déjà eu des cas inexplicables un peu difficile. Démo en 30 secondes.
• Pour aller plus loin, désactiver manuellement une fois (CTRL+Z) ou de manière permanente la recopie des formules (Fichier-options-vérification-options de correction automatique, mise en forme automatique au cours de la frappe, Formules de remplissage dans les colonnes pour créer des colonnes calculées), une approche plus complète ici.

Gestion des formats

• Une situation similaire se pose sur les formats. Il faut en effet ajouter le format à l'ensemble de la colonne pour que celui-ci soit changé. Voir ici pour bien comprendre.
• Une meilleure prise en charge des mises en forme conditionnelle. On ne va pas se mentir, c'est l'une des plus grosses faiblesses des tableaux.
• Une amélioration des calculs sur des grosses tables. Ici, j'apporterais une nuance. En effet, les goulots d'étranglement ne sont pas forcément liés à des gros tableaux, mais ils peuvent venir d'ailleurs.

Gestion de la protection

• C'est une demande récurrente faites à Microsoft. A moins que cette demande ne remonte dans les priorités, je ne vois pas trop quand ils vont l'adresser.

Vos formulaires en un clic avec FormXL Pro   ↑↑↑

Si vous avez aimé ce post et que vous voulez aller encore plus loin avec les tables, je vous invite à regarder mon application spécialisée sur l'ajout automatique (en un clic) de formulaires dans vos tables de données. Cette application a été ajoutée avec succès sur des tables de plusieurs dizaines de colonnes. Idéal pour ajouter, supprimer ou consulter des lignes. L'ajout d'une clef unique (par ex., numéro de facture automatique), les formats de date, les couleurs de colonnes, les validations de données liées à plusieurs tables et même les listes en cascade sont prises en compte.

Démonstration et lien ici.

Co-développement d'applications   ↑↑↑

Pour rappel, Excel n'est pas un gestionnaire de données, mais

• Si l'informatique n'a pas le temps de vous développer votre application
• Si la demande est trop complexe et nécessite un prototype
• Si vous n'avez que peu d'utilisateurs en simultanée

Excel est une solution idéale pour créer un prototype fonctionnel haute fidélité. Les tableaux vous offrent la structure et la rigueur nécessaire pour mener à bien cette aventure.

Prototypage immédiat et fonctionnel de haute fidélité :

• prototype : cela rassurera l'informatique, car ils pourront ensuite se baser sur ce prototype pour créer une solution à plus grande échelle. Cela reflète aussi une certaine humilité par rapport à la portée de l'application. Ceci dit, certains de mes "prototypes" sont utilisés depuis des années et permettent toujours de réaliser d'énormes gains de productivité.
• immédiat : comme on se base sur Excel, toute sa puissance est disponible (tableau bien sur, formules, validation, formulaire, Power Query, PowerPivot, modèle de données, DAX, VBA, etc.). Pour 80 % de mes projets, je développe à la vitesse de ma pensée (et du clavier). Les 20% restants sont des projets très complexes qui peuvent demander une réflexion plus importante en amont.
• fonctionnel : comme vous utilisez Excel (et que vous co-développez avec moi 🙂 ) l'application sera fonctionnelle très rapidement.
• haute fidélité : l'application reflétera parfaitement vos besoins et n'aura pas de perte de traduction entre l'utilisateur final et le développement.

Le VBA et les tableaux structurés      ↑↑↑

Pour bien terminer, voici quelques concepts et codes indispensables pour gérer les tableaux structurés dans VBA et prendre un bon départ. Il vaut cependant mieux avoir des bonnes bases en VBA pour commencer.

Tout d'abord, l'objet auquel on fait référence est un listobject. On peut y faire référence directement avec listobjects("NomDeLaTable") ou bien en utilisant le numéro du listobject dans la feuille, ou encore avec une intersection avec une cellule.

Ensuite, ce listobject a plusieurs propriétés et méthodes (actions). Voici celles que j'utilise le plus (définitions directement tirées de l'aide d'excel).

• Range : "Renvoie un objet Range qui représente la plage à laquelle l’objet de liste spécifié dans la liste s’applique."
• DataBodyRange : "renvoie un objet Range qui représente la plage de valeurs, à l’exception de la ligne d’en-tête dans une table. En lecture seule."
• HeaderRowRange : Renvoie un objet Range qui représente la plage de la ligne d’en-tête d’une liste. Type de données Range en lecture seule.
• ListRows : Renvoie un objet ListRows qui représente toutes les lignes de données de l’objet ListObject . En lecture seule.

ListRows est intéressant pour l'ajout d'une nouvelle ligne. Toujours de l'aide d'excel :

Set myNewRow = Worksheets(1).ListObject(0).ListRows.Add

Par contre, cette méthode peut être (très) lente, et donc je préfère augmenter la taille du DataBodyRange avec un resize. La syntaxe est plus lourde, mais c'est beaucoup plus rapide.

Pour aller beaucoup plus loin, vous pouvez revoir ce séminaire très riche sur le sujet.

En cadeau, voici le code discuté dans ce séminaire. Après dézippage, ne pas oublier de débloquer le fichier (clic droit, (plus d'options - Windows 11), propriétés, cocher débloquer en bas à droite).

Et je m'arrête là ! Je pense que si vous maitrisez déjà tout ce que j'ai raconté dans ce post, vous pouvez aller très loin. Avec les outils en plus que j'ai développés (FormXL Pro, Super-Segments, Gestion de listes), basés sur des dizaines d'années d'expérience, j'espère que vous pourrez aller encore plus loin !