A la découverte des tableaux structurés 1/2

Première partie d'un topo sur cette fonctionnalité incontournable introduits dans Excel 2007 sous le nom de Tableau (pas vraiment top comme nom d'où le "structuré" que l'on ajoute souvent 😁).

Présentation de cette fonctionnalité

Principe de fonctionnement

Un tableau structuré est une plage de données convertie en un objet Table.
Contrairement à une plage classique, le tableau devient un objet avec des propriétés propres, comme :

• Un nom unique (ex. : TableClients).
• Des colonnes nommées.
• Une taille dynamique c'est à dire qui s’ajuste automatiquement en cas d'ajout de lignes et de colonnes.
• Des références structurées dans les formules faisant référence à ses éléments (cellule, colonnes sans le titre, avec le titre, le titre de colonne…).
• Un style visuel intégré, paramétrable et automatique.

Il s'agit d'un tableau ayant une structure de type "Base de données", un tableau à 1 dimension, c'est à dire avec seulement des entêtes de colonne (un seul niveau d'entête). C'est le type de tableau que l'on utilise pour faire des tableaux croisés dynamiques, par exemple.

Avantages

• Principalement leur capacité à se mettre à jour dynamiquement.
  • Mise à jour des formules (internes et externes) quand on ajoute des lignes à la fin de celui-ci. Ainsi on n'a plus à "prévoir" des lignes "futures" en sélectionnant des lignes/colonnes vides en plus, d'utiliser des colonnes/lignes entières…
    • Exemple : Le tableau fait actuellement 850 lignes (commence en ligne 1), plus besoin de formule comme SOMME(D2 : D50000) ou SOMME(D:D) pour être sûr que les nouvelles lignes soient prisent en compte.
  • Recopie automatique des formules déjà présentes dans les colonnes en cas d'ajout de lignes (pas de "trous" dans la colonne de résultats).
  • Recopie automatique des formules créées dans toute la colonne que ce soit vers le haut ou vers le bas (pas besoin de partir d'une extrémité puis de glisser la formule jusqu'à l'autre extrémité du tableau).
  • Mise en forme automatique pour les nouvelles lignes et colonnes.
• Formule plus lisible, évitant l'erreur de sélectionner une mauvaise plage (plage incomplète, voir une autre plage que celle ciblée).
• Mise en forme rapide via des styles de tableaux prédéfinis et personnalisables.
• Affichage des titres de colonne en continue (le figeage de la première ligne du tableau est ainsi inutile).
  • Nécessite d'avoir sélectionné au moins une cellule du tableau structuré.
  • Les noms des colonnes apparaissent à la place des lettres désignant la référence des colonnes (ici les lettres de "A" à "F").
    

Inconvénients

• Syntaxe de formules plus longues.
• Pas de fusion de cellules autorisée.
• Limitation de présentation/structure pour le tableau (un seul niveau d'entête…).
• Incompatibilité avec certains outils Excel (Sous-totaux, Affichages personnalisés…).

Bonnes pratiques à adopter

Le tableau de départ :

• Ne doit pas avoir de lignes ou de colonnes entièrement vides. Il est donc inutile et contre-productif d'anticiper un agrandissement du tableau en laissant des lignes ou de colonnes entièrement vides !
• Ne doit pas avoir de cellules fusionnées. C'est un simple "conteneur", par un outil de présentation.
• Ne pas contenir de lignes/colonnes de synthèse intermédiaires (comme des sous-totaux par exemple).

Manipulation des tableaux structurés

Création

On se place dans le tableau (les lignes et colonnes entièrement vides délimiterons le tableau) puis :

• Ruban Insertion >Tableau.
• Ruban Accueil > Mettre sous forme de tableau.
• Raccourci clavier Ctrl + l (lire "L" en minuscule).
• Ruban PowerPivot > Ajouter au modèle de données.

Si le tableau comporte déjà en 1ère ligne des étiquettes de colonne, vérifier bien qu'est coché l'option "Mon tableau comporte des en-têtes" dans la fenêtre pop-up "Créer un tableau" (elle permet de contrôler la plage pris en compte et de paramétrer l'existence d'une ligne de titre si nécessaire).

Avant

Après

Chaque nom de colonne doit être unique dans le tableau :

• S'il n'y a pas de nom, Excel utilisera alors comme nom le mot "Colonne" suivi d'un suffixe numérique (chiffre/nombre) => ici la colonne D dans les images précédentes.
• Si le nom est déjà utilisé dans une colonne (doublon) => ici les colonne B et C dans les images précédentes, Excel ajoutera un suffixe numérique (chiffre/nombre) à l'un des titres pour les différentier.

Excel applique automatiquement :

• L'outil Filtre, les listes au niveau des titres de colonnes (CTRL + MAJ + L ou la commande Filtrer du ruban Données ou la case à coché Bouton de filtre du ruban Création de tableau permettent d'activer/désactiver cet outil).
  
  1. L'icône au niveau du titre de colonne indique l'application d'un tri croissant (↑) et d'un filtre (Y).
  2. L'icône Trier de A à Z est active indiquant le tri.
  3. Indicateurs qu'un filtre appliqué (n° de ligne en couleur, n° de ligne manquants, commande Effacer le filtre de … active, …).
• Le style de tableau par défaut et active les filtres automatiques (flèche à droite des titres de colonnes).

Commandes disponibles pour manipuler les tableaux

Quand on sélectionne une ou plusieurs cellules du tableau cela génère l'apparition de nouvelles options dans le menu contextuel (clic droit) et d'un ruban outils Création de tableau (à droite).

Commandes du ruban Outils Création de tableau

Ruban apparaissant quand on clique dans tableau structuré.

1. Consulter ou modifier le nom du tableau.
   • Les caractères [ ] { } " ; ' ne sont pas acceptés dans les noms des tableaux structurés.
   • Le nom ne doit pas commencer par un chiffre et ne doit pas contenir d'espace.
   • Le nom ne doit pas être déjà utilisé (voir les noms existants dans l'outil Gestionnaire de noms (ruban Formules)).
   • Le nom ne doit pas correspondre à une cellule ("XFD1" ou "XFD$1" ne sont pas accepté car il s'agit de la 1ère cellule de la dernière colonne de la feuille, "XFE1" lui est accepté donc pas de "Tab5" mais plus "Tab_5" ou "Tabl5").
   • Le nom ne peut être "C", "c", "R", "r", "L", "l".
   • Le nom est limité à 255 caractères.
   • Les caractères "[ ] # ' @" seront automatiquement préfixés du caractère d'échappement apostrophe dans les références structurées (exemple : la colonne "a#b" générera la référence "TabVentes[a'#b]").
   • Excel ne fait pas de distinction de casse donc "Vts", "VTS" sont un même nom pour lui.
2. Redéfinir la plage pris en compte par le tableau (en plus grande ou plus petite).
3. Créer un tableau croisé dynamique à partir des données du tableau (Équivalent à Insertion > Tableau croisé dynamique).
4. Supprimer les lignes qui dupliquées ou partiellement dupliquées (Équivalent à Données > Supprimer les doublons).
5. Annuler la transformation du tableau en tableau structuré (toutes les références dans les formules utilisant le tableau seront converties en référence classique du type A1:A...).
6. Insère un segment (interface graphique utilisateur de filtrage des lignes du tableau).
   • La liaison/connexion entre segment n'est pas disponible (un même segment ne peut filtres plusieurs tableaux).
   • Les segments de type chronologie ne sont pas disponibles.
     
7. Exportation vers SharePoint / Visio.
8. Permet de mettre à jour le tableau quand celui-ci est généré automatiquement (requête PowerQuery, PowerPivot…).
9. Propriétés (formatage, mise à jour) du tableau quand celui-ci est généré automatiquement (requête PowerQuery, PowerPivot…).
10. Pour ouvrir le fichier directement dans Excel online via le navigateur Web (seulement pour les fichiers en ligne : SharePoint, OneDrive).
11. Déconnecte le tableau qui ne pourra plus être mise à jour automatiquement.
12. Choix des éléments à formater avec le style sélectionné :
    • Ligne Total : Permet d'ajouter en dernière ligne une ligne de synthèse.
    • Bouton de filtre : Permet d'activer ou de désactiver l'outil Filtre (Liste déroulante au niveau des titres de colonnes).
13. Choix du formatage/style automatique à utiliser.

Commandes du menu contextuel lié

Un clic droit sur une cellule de tableau structuré fait apparaitre ou modifie les commandes suivantes par rapport au menu contextuel des cellules classiques.

1. Permet d'insérer une ou plusieurs lignes/colonnes positionnées avant la sélection (en haut/à gauche) et une ligne en dessous si l'on est en dernière ligne.
   • Le nombre inséré dépend de la sélection (par exemple, la sélection de 3 cellules superposées générera 3 lignes en cas d'insertion de ligne mais 1 colonne en cas d'insertion de colonne).
   • Les commandes classiques d'insertion de lignes/colonne peuvent être utilisées dans le cas uniquement où leur activation n'impacte qu'un seul tableau structuré (commandes grisées si plusieurs tableaux impactés).
2. Permet de supprimer une ou plusieurs lignes/colonnes correspondantes à la sélection.
   • Le nombre supprimé dépend de la sélection (par exemple, la sélection de 3 cellules superposées supprimera 3 lignes en cas de suppression de ligne mais 1 colonne en cas de suppression de colonne).
   • Les commandes classiques de suppression de lignes/colonne peuvent être utilisées dans le cas uniquement où leur activation n'impacte qu'un seul tableau structuré (commandes grisées si plusieurs tableaux impactés).
   • La ligne de titre de colonne (1ère ligne) ne peut être supprimée (commande grisée).
3. Permet de sélectionner une ou plusieurs colonnes (selon la sélection) avec ou sans son titre ou une ou plusieurs lignes (selon la sélection).
4. Permet :
   • D'afficher ou de masquer une ligne de synthèse. La commande Ligne des totaux est aussi présente dans les choix des éléments à formater avec le style sélectionné dans le ruban Création de tableau (cf. n°12).
     • Pour chaque colonne, on pourra soit saisir une valeur/texte (ex : Moyenne) soit sélectionner une fonction de calcul.
       • Aucune : Laisser la cellule vide.
       • De Moyenne à Var : Permet d'utiliser les fonctions de calculs classique de la fonction SOUS.TOTAL (codes fonction de 101 à 105, 107, 109, 110).
       • Autres fonctions… : Permet de choisir la fonction voulue mais nécessitera de sélectionner la/les plages à utiliser).
     • Les saisies et fonctions sélectionnées seront conservées en cas de masquage et de réaffichage de cette ligne de synthèse.
     • Le masquage temporaire de cette ligne de synthèse sera nécessaire pour ajouter facilement des lignes à la fin du tableau.
   • D'annuler la transformation du tableau en tableau structuré (toutes les références dans les formules utilisant le tableau seront converties en référence classique du type A1:A...).
   • De définir un texte de remplacement (accessibilité utilisateur handicapé).

Agrandir le tableau

Il suffit d'ajouter/saisir les lignes et les colonnes à la fin du tableau (tout en bas pour les lignes, tout à droite pour les colonnes) en s'assurant de ne pas laisser de lignes/colonnes vides.

Déplacer un tableau

Il suffit de le glisser après sélection en se plaçant sur la bordure de sélection ou d'utiliser les commandes Couper-Coller.

Copier un tableau

Il suffit d'utiliser les commandes Copier-Coller. Dans tous les cas le tableau sera renommé par ajout d'un suffixe.

Renommer le tableau ou certaines des colonnes

Il suffit de saisir le nouveau nom en s'assurant qu'il soit unique (sinon Excel ajoutera un nombre en suffixe au nom). Toutes les formules seront mises à jour (sauf les références générées avec les fonctions INDIRECT bien évidemment).

Formatage du tableau

Ce type de tableau peut être mis en forme normalement mais possède des spécificités pour leur formatage.

• Il utilise un style de tableau permettant de formatter automatiquement :
  • La 1ère et dernière ligne/colonne du tableau.
  • Une ligne/colonne sur 2 ou sur 3...
  • Certaines cellules spécifiques.
• Lors d'un formatage manuel, si ce formatage s'applique sur une colonne entière, cela définit le formatage "global" de la colonne. Ainsi c'est ce formatage "global" qui sera utilisé quand on ajoutera de nouvelles lignes.
  • Ce formatage "colonne" (global) est modifiable en formattant toute la colonne en UNE FOIS mais on perdra les autres personnalisations.
• Si le formatage de toutes les cellules de la colonne est fait en plusieurs fois, le formatage des cellules est bien modifié mais pas celui de la colonne (global) et les nouvelles lignes n'utiliseront pas ce nouveau formatage.

Création d'un style de tableau

Via le ruban outils Création de tableau > Nouveau style de tableau... en bas de la galerie des Styles de tableau.

Il suffit de donner un nom au nouveau style puis de sélectionner un des Éléments de tableau dans la liste puis de paramétrer son formatage via le bouton Format.

• Les éléments personnalisés affichent un nom en gras.
• Le bouton Effacer permet d'annuler toutes les personnalisations de l'élément sélectionné.
• Taille de rayure (1 à 9) indique le nombre de lignes/colonnes utilisé par les bandes colorées.
• L'ordre de la liste des éléments de tableau correspond à l'ordre d'application/à la priorité du formatage (plus l'élément est bas dans la liste et plus son formatage est prioritaire).
• Le style est créé pour ce classeur et peut être définit comme style par défaut via la case à cocher Toujours utiliser ce style de tableau pour ce document.
• Pour l'utiliser dans plusieurs documents il faut utiliser un modèle de classeur.
• Pour copier un style de tableau dans un autre classeur, il suffit de copier un tableau utilisant ce style dans le classeur de destination puis supprimer le tableau si nécessaire.

Menu contextuel pour les vignettes des styles de tableau

1. Appliquer le style en écrasant toutes personnalisations/tous formatage directe.
2. Appliquer le style en conservant toutes personnalisations/tous formatage directe.
3. Pour modifier un style de tableau en réaffichant la boîte de dialogue Style de tableau.
4. Pour dupliquer un style de tableau en réaffichant la boîte de dialogue Style de tableau avec un autre nom de style.
5. Pour supprimer un style de tableau : clic droit sur la vignette puis Supprimer (tous tableau utilisant ce style basculera sur le style par défaut).
6. Pour définit ce style comme style par défaut, activer la case à cocher Toujours utiliser ce style de tableau pour ce document.
7. Pour ajouter toute la galerie à la barre d'outils d'accès rapide.

Ordre d'application des formatages

D'abord le formatage par le style du tableau puis le formatage direct puis le formatage par mises en forme conditionnelle (donc le plus prioritaire).

Pour le formatage via un style de tableau : L'ordre de priorité/d'application est donnée par l'ordre des éléments dans la fenêtre "Modifier le style de tableau" (en haut "Tableau entier", le moins prioritaire à "Dernière cellule du total", le plus prioritaire).

Ainsi certains formatages peuvent se masquer partiellement voire totalement.
Par exemple si l'on applique un style aux éléments 1ère rangée de lignes et 2ème rangée de lignes, le formatage des éléments 1ère rangée de colonnes et 2ème rangée de colonnes ne sera plus visible.

Application d'un style de tableau

Il suffit de cliquer sur la vignette du style désiré dans la galerie des Styles de tableau. Via les cases à cocher du groupe Options de style de tableau, on peut choisir quels éléments seront mise en forme.

Suite de l'article le mois prochain.

Merci pour votre attention bienveillante.