Les tableaux structurés

Création d'un tableau structuré

Il y a 2 possibilités équivalentes pour créer un tableau structuré :

• AccueilMettre sous forme de tableau
• InsertionTableau

Il faut ensuite préciser si le tableau comporte une ligne d'en-tête :

Les données prennent alors une apparence différente :

Un onglet supplémentaire apparaît également dans le bandeau supérieur : "Outils de tableau : création".

Cet onglet permet notamment de donner un nom plus explicite au tableau ("Tableau1" remplacé ici par "TableVenteFruits"), ou encore d'en modifier la mise en forme.

Fonctionnalités et syntaxe des formules

Filtre et tri

Ces fonctionnalités sont disponibles sans recourir au tableau structuré, mais sont néanmoins ajoutées automatiquement et très utiles.

Un avantage par rapport à la fonctionnalité classique est qu'il n'y a pas de risque de travailler sur une plage partielle à cause de ligne(s) vide(s). Tout ce qui est mis en forme par le tableau structuré est utilisé lors d'un tri ou d'un filtre.

Syntaxe des formules

L'une des fonctionnalités les plus intéressantes apportées par le tableau structuré est certainement la structure particulière des formules qu'il permet. En effet, chaque colonne du tableau fonctionne comme une plage nommée qui prend le nom de son en-tête, ce qui rend les formules nettement plus claires.

Par exemple, si l'on souhaite calculer le total de chaque ligne en colonne E (montant * quantité), on écrit en l'absence de tableau structuré :

=C2*D2

Avec le tableau structuré, on peut écrire :

=[@[Nombre d''unités achetées]]*[@[Prix par unité]]

Cette syntaxe est automatique si la colonne E appartient déjà au tableau structuré : ce qui est le cas dès que l'on saisit le titre de l'en-tête. Lors de la rédaction de la formule, la sélection des cellules C2 et D2 aboutie directement à l'écriture ci-dessus.

Une fois la formule étendue à toute la plage, cette colonne devient une "colonne calculée". C'est-à-dire que chaque ajout de ligne étend automatiquement cette fonction, ce qui apporte un confort non négligeable dans la gestion de données. A noter qu'il est possible de désactiver une colonne calculée, par exemple pour appliquer des formules différentes d'une ligne à l'autre.

Le tableau structuré offre aussi la possibilité d'ajouter une ligne des totaux (soit depuis l'onglet "Création""Ligne des totaux", soit via un clic droit"Table""Ligne des totaux"). Un menu déroulant sur chaque cellule du total permet de choisir l'opération à afficher : moyenne, nombre, somme, etc.

Dans le détail de la formule, le @ désigne la donnée de la même ligne que celle qui contient la formule. En l'absence de ce symbole, cela désigne la totalité de la colonne. Dans le cas où le nom de colonne est composé d'au moins un caractère spécial (tabulation, saut de ligne, ,, ., :, etc), une paire de crochets supplémentaire est nécessaire autour de la référence structurée. De même, les caractères [, ], # et ' nécessitent d'être précédés d'une apostrophe (caractère d'échappement), par exemple :

[@NombreUnitésAchetées]

Devient :

[@[Nombre d''unités achetées]]

Comme pour une référence de plage classique, préciser le nom du tableau n'est pas nécessaire lorsque la formule est écrite au sein de ce même tableau. En dehors, il faut précéder la référence de plage du nom de tableau :

=SOMME(TableVenteFruits[[Nombre d''unités achetées]])

Ci-dessous une liste de références avec la plage correspondante pour chaque :

L'avantage de ces références est qu'elles sont dynamiques. Tout ajout/suppression de ligne ou déplacement est automatiquement intégré, de sorte que les formules et tableaux croisés qui y font référence n'ont pas besoin d'être corrigés.

En revanche, l'utilisation du $ pour différencier les références fixes des références relatives disparaît avec cette nouvelle syntaxe. La variation ou non de la référence dépend de la manière dont est étendue la formule : les références ne varient pas en cas de copié-collé de la formule, mais évoluent lorsque l'on étend la formule par extension depuis le coin inférieur droit de la plage.

Tableau croisé dynamique

Le tableau structuré se prête bien comme source d'un tableau croisé dynamique, puisque la référence est redimensionnée automatiquement à l'ajout/suppression de lignes et de colonnes. L'option tableau croisé dynamique est d'ailleurs proposée directement depuis l'onglet "création" du tableau structuré.

Dédoublonnage

La fonctionnalité de suppression des doublons, déjà présente dans l'onglet "Données" est aussi proposée dans l'onglet "Création" du tableau structuré. Celle-ci permet de supprimer les lignes qui comportent des redondances au niveau d'une ou plusieurs colonnes. Dans le cas de plusieurs colonnes, seules les lignes qui sont strictement identiques sur tous les champs sélectionnés sont supprimées.

Divers

La suppression ou l'ajout de lignes n'affecte pas les données hors du tableau, et ne créé donc ni décalage ni perte de données sur une feuille où se trouve un tableau structuré.

A partir d'Excel 2013, il est possible d'utiliser des segments sur les tableaux structurés. Ceux-ci permettent de filtrer les données d'un champ.

Utilisation dans VBA

Les références structurées offrent une souplesse appréciable dans VBA, puisqu'on ne s'occupe ni du positionnement du tableau au sein du classeur, ni même du nombre d'entrées qu'il contient. A noter cependant que la modification du nom d'un champ dans Excel ne modifie pas le code VBA qui y fait explicitement référence.

Détail des structures

Il y a 2 possibilités pour faire référence à un tableau structuré et ses sous-ensembles dans VBA : faire référence à l'objet "ListObject" ou aux références structurés sous forme de plage nommée Range("TableauMachin[Truc]").

Concernant la syntaxe ListObject, il est possible (comme pour une feuille par exemple) de faire référence au tableau par le biais de son index (1, 2, etc) ou de son nom ("TableVenteFruits", etc).

Le tableau ci-dessous reprend les références énoncées précédemment, et leur syntaxe correspondante dans VBA (à noter que des syntaxes dérivées sont possibles) :

Exemples d'utilisation

Sub Exemples()

    With Sheets(1).ListObjects(1)
        'Nom du tableau structuré
        MsgBox "Nom tableau : " & .Name 'TableVenteFruits
        'Nom de la 1ère colonne du tableau
        MsgBox "Première colonne : " & .ListColumns(1).Name 'Produit
        'Adresse de la plage "Produit"
        MsgBox "Plage produits : " & .ListColumns("Produit").DataBodyRange.Address '$A$2:$A$19
        'Nombre de lignes du tableau
        MsgBox "Nombre de lignes du tableau : " & .DataBodyRange.Count '18
        'N° de ligne de l'en-tête
        MsgBox "Indice de ligne d'en-tête : " & .HeaderRowRange.Row '1
        'N° de colonne de l'en-tête "Produit"
        MsgBox "Indice de colonne produit : " & .HeaderRowRange.Find("Produit", LookAt:=xlWhole).Column '1
        'Adresse de plage du corps du tableau
        MsgBox "Plage de données du tableau : " & .DataBodyRange.Address '$A$2:$E$19
        'N° de ligne de la ligne des totaux
        MsgBox "Indice de ligne des totaux : " & .TotalsRowRange.Row '20
        'Adresse de plage du tableau
        MsgBox "Plage contenant le tableau : " & .Range.Address '$A$1:$A$20
        'Ajout d'une colonne en 3ème position, nommée "Unité"
        .ListColumns.Add(3).Name = "Unité"
        'Remplissage de la colonne ajoutée
        For Each Elem In .ListColumns("Unité").DataBodyRange
            Elem.Value = "Cagette"
        Next Elem
        'Insertion d'une ligne en fin de tableau
        .ListRows.Add
        .DataBodyRange.Rows(.DataBodyRange.Rows.Count) = Array("Pomme", "France", "Cagette", 17, 14.55)
        'Modification d'une valeur (prix des pommes de la ligne tout juste ajoutée)
        .ListColumns("Prix par unité").DataBodyRange(19) = 15.07
        'Tri sur la provenance
        .DataBodyRange.Sort key1:=.ListColumns("Provenance").DataBodyRange, Header:=xlYes, Order1:=xlAscending
    End With

End Sub

Divers

Un tableau structuré a au moins un enregistrement (vide), c'est-à-dire que ListObject(1).ListRows.Count renvoie 0, mais Range("TableVenteFruits").Rows.Count renvoie 1.