Voici la suite de l'article A la découverte des tableaux structurés 1/2.
Syntaxes des références structurées pour les formules de calculs
Par défaut, quand c'est possible, Excel va utiliser un type de référence spécifique pour faire référence à une cellule sur la même ligne, à une ou des colonnes du tableau ou à tout le tableau.
Référence à une cellule sur la même ligne
Pour calculer un montant (Quantité * Prix), au lieu d'avoir le classique :
= E2 * F2Nous obtiendrons :
Si la formule est dans le tableau structuré :
= [@Qt]*[@Prix]
Si la formule est à lʼextérieur du tableau :
= Tableau5[@Qt]*Tableau5[@Prix]- Le "@" indique d'utiliser la valeur correspondante de la ligne du tableau (intersection implicite).
- "Tableau5" est le nom du tableau.
- Dans le tableau, cette formule sera automatiquement recopiée dans toute la colonne. On aura donc la même formule pour toutes les cellules du tableau mais renvoyant un résultat différent en fonction de la ligne (grâce au "@").
- En cas de recopie horizontal (on reste sur la même ligne) de cette formule, Excel décalera les références des colonnes [Qt] et [Prix] comme il le ferait pour la version avec des références standards (par exemple E2 * F2).
Référence aux données d'une colonne (sans le titre de colonne)
Pour calculer la somme des montants, au lieu de :
= SOMME(G2:G4)Nous obtiendrons :
= SOMME(Tableau5[Mt])- [Mt] : Nom de la colonne.
- En cas de recopie horizontal (reste sur la même ligne) de cette formule, Excel décalera la référence de colonne [Mt] comme il le ferait pour la version avec des références standards (G2:G4 devient H2:H4).
Référence aux données de plusieurs colonnes (sans les titres)
Pour compter le nombre de valeurs saisies entre les colonnes "Design" et "Colonne3", au lieu d'avoir :
= NBVAL(B2:D4)Nous obtiendrons :
= NBVAL(Tableau5[[Desig]:[Colonne3]])- [Desig]:[Colonne3] : Toutes les colonnes entre la colonne [Desig] et la colonne [Colonne3].
- Attention : En cas de recopie horizontal (reste sur la même ligne) de cette formule, Excel NE décalera PAS ces références de colonne.
Remarque :
- Le fait qu'Excel ne fasse pas de décalage des références dans cas (plage de colonne), nous donne la syntaxe à employer si l'on veut faire référence à une colonne en mode "absolue", c'est à dire sans qu'Excel ne décale cette référence en cas de recopie horizontale (ce que l'on fait en joutant des "$" aux référence standard).
On veut bloquer la référence à la colonne Qt comme on le ferait en ajoutant de $ a la référence pour la passer en absolue (= $E2 * F2) :
= Tableau5[@[Qt]:[Qt]]*Tableau5[@Prix]Autres références structurées possibles
Référence à une colonne avec son titre :
= NBVAL(Tableau5[[#Tout];[Desig]])
Référence à plusieurs colonnes avec leurs titres :
= NBVAL(Tableau5[[#Tout];[Desig]:[Matière]])
Référence à tout le tableau sans les titres :
= NBVAL(Tableau5)
Référence à tout le tableau avec les titres :
= NBVAL(Tableau5[#Tout])
Référence à un titre dʼune colonne :
= NBVAL(Tableau5[[#En-têtes];[Desig2]])
Référence à tous les titres des colonnes du tableau :
= NBVAL(Tableau5[#En-têtes])
Référence à 2 valeurs de la ligne de synthèse :
= NBVAL(Tableau1[[#Totaux];[Qt]:[Prix]])
Référence à toutes les valeurs de la ligne de synthèse :
= NBVAL(Tableau5[#Totaux])Référence mixte
Il est tout à fait possible d'utiliser une référence pour une plage mélangeant une référence standard et une référence structurée.
Référence mixte générant une plage dynamique ("accordéon")
= NB.SI(B$2:[@Ref];[@Ref])- Ici B$2 : [@Ref] représente une plage s'agrandissant d'une ligne à chaque ligne, partant de B2 (début des données de la colonne) jusqu'à la ligne "en cours".
Intérêts de ces nouvelles syntaxes par rapport aux références classiques (type L1C2)
- Des formules plus lisibles.
- Dans une colonne du tableau structuré, automatisation de la recopie et de la mise à jour (gain de temps et cohérence assurée)
- Les formules se recopient automatiquement, peu importe où elle est générée ou modifiée.
- Les formules prennent en comptes les nouvelles données ajoutées et se limitent strictement à la plage de données réellement utilisée.
Remarque :
- On peut forcer la personnalisation d'une formule dans une colonne du tableau structuré par rapport aux autres formules de la colonne ainsi :
- Modifier la formule, valider => Excel recopie automatiquement la formule (écrase les anciennes formules dans toutes les autres cellules de la colonne).
- Utiliser la balise active "Options de correction automatique" pour annuler cette recopie en utilisant "Annuler la colonne calculée" (Arrêter la création automatique de colonnes calculés permet de désactiver cette recopie automatique dans Excel => ré-activable via les options de Correction automatique>Mise en forme automatiques au cours de la frappe).
- Dans ce cas, Excel affichera un triangle vert en haut à gauche de la cellule indiquant que la formule de cette cellule est incohérente par rapport aux autres formules de la colonne.
En plaçant le pointeur de souris sur cet indicateur, Excel proposera d'Ignorer l'erreur (faire disparaitre l'indicateur vert) ou de Restaurer la formule (la regénérer pour qu'elle soit cohérente par rapport aux autres formule).
- Attention : En personnalisant une formule, la recopie automatique est désactivée pour toute la colonne.
- Dans ce cas (recopie désactivée pour la colonne) si l'on modifie une formule de la colonne, la balise active "Options de correction automatique" permet de recopier cette formule sur toute la colonne (réactivation de la recopie automatique).
- Dans ce cas (recopie désactivée pour la colonne) si l'on modifie une formule de la colonne, la balise active "Options de correction automatique" permet de recopier cette formule sur toute la colonne (réactivation de la recopie automatique).
Liaison entre classeurs
- Une formule utilisant une référence structurée pointant vers un autre classeur renverra l'erreur #REF! tant que le classeur cible n'est pas ouvert.
Exemples de mise en œuvre spécifiques
Utilisation dans les listes déroulantes générées par l'outil Validation de données
- On ne peut pas utiliser une formule utilisant des références structurées comme source pour les valeurs d'une liste (par exemple =TabVentes[Desig]).
- La formule =INDIRECT("TabVentes[Desig]") est utilisable mais n'est pas dynamique en cas de renommage du tableau ou des colonnes utilisées.
- La meilleure solution est d'utiliser un nom comme conteneur intermédiaire (via le ruban Formules>Définir un nom).
- Le nom sera accepté par l'outil Validation (Astuce : utiliser le raccourcis F3 pour afficher la liste des noms générés quand on complète la boîte de dialogue Validation de données).
- La plage utilisée par le nom sera mise à jour dynamiquement en cas d'ajout/suppression d'éléments dans le tableau structuré et en cascade, la liste affichée par l'outil de validation sera elle aussi mise à jour.
Utilisation dans les graphiques
- Même si les références structurées ne sont pas conservées mais converties en références classiques (remarquer dans la barre de formule les références de la fonction SERIE correspondante), le graphique sera bien dynamique en cas d'ajout de lignes.
Utilisation avec les compléments PowerPivot et PowerQuery
- Pour utiliser des données dans PowerPivot ou PowerQuery, lors de l'importation des données, Excel devra convertir la plage en tableau structurée.
- Quand PowerQuery renvoie des données dans Excel sous forme de tableau, il s'agira de tableau structuré.
Implémentation en VBA
Les tableaux structurés sont des objets appelés ListObjects, voici quelques exemples de syntaxe pouvant être utile :
' Collection Tableaux / Objet Tableau
' ===================================
' Création
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "TabVentes"
' ou
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes)
tbl.Name = "TabVentes" ' On renomme le tableau
tbl.DisplayName = "TabVentes" ' Par précaution on le modifie aussi
' Manipulations diverses
Set tbl = ActiveSheet.ListObjects("TabVentes") ' Accès au tableau
'tbl.Refresh ' Actualisation
tbl.Resize Range("A1:G4") ' Redimensionnement
tbl.Resize tbl.Range.Resize(tbl.Range.Rows.Count, tbl.Range.Columns.Count + 2) ' Redimensionnement
'tbl.Unlist ' Convertion en plage
'tbl.Delete ' Supprimer le tableau
' Test d'exitance du tableau
On Error Resume Next
Set tblA = ActiveSheet.ListObjects("TabVentesA") ' Attention d'avoir bien déclaré tblA
On Error GoTo 0 ' Réinitialisation gestion d'erreur
If tblA Is Nothing Then MsgBox "Pas de TabVentesA"
' Manipulations des éléments du tableau
' =====================================
tbl.ListRows.Add 2 ' Ajoute une ligne apres la 1ère ligne de données (position 2)
tbl.ListRows.Add ' Etendre le tableau d'une ligne
' Ecrire en ligne 2 en colonne "Desig" (solutions équivalentes)
tbl.ListRows(2).Range.Columns(tbl.ListColumns("Desig").Index).Value = "Chaise"
tbl.ListColumns("Desig").DataBodyRange.Rows(2).Value = "Chaise" ' DataBodyRange=zone des données
tbl.ListColumns(2).DataBodyRange.Rows(2).Value = "Chaise"
tbl.ListColumns(2).Range.Rows(3).Value = "Chaise"
tbl.DataBodyRange.Cells(2, 2).Value = "Chaise"
tbl.Range(3, 2).Value = "Chaise"
' Ajouter une colonne calculée
Dim col As ListColumn
Set col = tbl.ListColumns.Add(Position:=4) ' Création et position
col.Name = "Total" ' Nommage
col.DataBodyRange.Formula = "=[@Qt]*[@Prix]" ' Ajout de la formule
' Suppression
tbl.ListRows(2).Delete ' Supprimer la 2ème ligne
tbl.ListColumns(5).Delete ' Supprimer la 5ème colonne
tbl.ListColumns("Test").Delete ' Supprimer la colonne "Test"
' Informations
Debug.Print tbl.ListColumns.Count ' Nombre de colonnes
Debug.Print tbl.ListRows.Count ' Nombre de lignes (sans le titre)
' Divers
tbl.ShowTotals = True ' Activation de la ligne de synthèse
' Mise en forme
tbl.HeaderRowRange.Interior.Color = vbGreen ' Ligne de titre en fond vert
tbl.DataBodyRange.Interior.Color = RGB(300, 0, 300) ' Lignes de données avec un fond
tbl.TotalsRowRange.Interior.Color = vbYellow ' Ligne de synthèse en fond jauneMerci pour votre attention bienveillante.