Le tableau croisé dynamique (ou TCD) affiche des données structurées pouvant comporter des centaines de milliers de lignes sous une forme synthétique plus facile à lire afin de les analyser et de les explorer. Le TCD est un outil d’audit de données et d’aide à la décision par excellence au même titre que les filtres de données ou les sous-totaux.
La construction d’un TCD EXCEL en VBA peut apparaître à première vue complexe mais suit en réalité quelques étapes rigides et aisées à mettre en application.
Cet article s’appuiera sur l’analyse de la cohérence des écritures de stocks tel que programmé dans ANA-FEC.
Sur le principe, la comptabilisation des stocks et encours et de leurs dépréciations suit un schéma d’écritures relativement simple :
- Stocks de marchandises : 37x D / 6037 C ;
- Stocks d’encours : 34x D / 713x ;
- Dépréciation des stocks de marchandises : 68173x D / 397x C
- …
Le contrôle de la bonne application de ce schéma est aussi simple à mettre en œuvre avec un TCD. Ce contrôle est essentiel pour prévenir toute anomalie de comptabilisation entraînant des écarts fâcheux dans le tableau des flux de trésorerie (TFT) ou la liasse fiscale.
Ici, le TCD résume les écritures mensuelles de comptabilisation des stocks. On observe que chaque mois la comptabilisation des stocks répond au schéma d’écriture standard (avec un solde de colonne nul pour les journaux d’OD ce qui démontre qu’aucun autre compte n’est mouvementé).
Programmation du TCD en VBA :
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="'" & NomOngletFEC & "'!R2C1:R" & NbLignes + 1 & "C32", Version:=6).CreatePivotTable TableDestination:="'" & NomOngletTCDEnCours & "'!" & AdresseTCD, TableName:="Tableau croisé dynamique1", DefaultVersion:=6 Sheets(NomOngletTCDEnCours).Select With activesheet.PivotTables("Tableau croisé dynamique1").PivotFields("aaaamm") .Orientation = xlColumnField .Position = 1 End With With activesheet.PivotTables("Tableau croisé dynamique1").PivotFields("JournalCode") .Orientation = xlRowField .Position = 1 End With With activesheet.PivotTables("Tableau croisé dynamique1").PivotFields("Cpte1") .Orientation = xlRowField .Position = 2 End With With activesheet.PivotTables("Tableau croisé dynamique1").PivotFields("Cpte6") .Orientation = xlRowField .Position = 3 For i = 1 To .PivotItems.Count If .PivotItems(i).Name Like "3*" Or .PivotItems(i).Name Like "603*" Or .PivotItems(i).Name Like "713*" Or .PivotItems(i).Name Like "68173*" Or .PivotItems(i).Name Like "78173*" Then .PivotItems(i).Visible = True Else: .PivotItems(i).Visible = False End If Next i End With activesheet.PivotTables("Tableau croisé dynamique1").AddDataField activesheet.PivotTables("Tableau croisé dynamique1").PivotFields("Solde"), "Somme de Solde", xlSum With activesheet.PivotTables("Tableau croisé dynamique1").PivotFields("Somme de Solde") .NumberFormat = "#,##0.00" End With
Création du TCD (.PivotCaches.Create) à partir d’un tableau Excel (SourceType:=xlDatabase) situé sur la plage de cellules R2C1:R” & NbLignes + 1 & “C32” dans l’onglet dont le nom est stocké dans la variable NomOngletFEC (SourceData:=”‘” & NomOngletFEC & “‘!R2C1:R” & NbLignes + 1 & “C32”). Le TCD est stocké dans l’onglet dont le nom est stocké dans la variable NomOngletTCDEnCours (TableDestination:=”‘” & NomOngletTCDEnCours & “‘!” & AdresseTCD).
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="'" & NomOngletFEC & "'!R2C1:R" & NbLignes + 1 & "C32", Version:=6).CreatePivotTable TableDestination:="'" & NomOngletTCDEnCours & "'!" & AdresseTCD, TableName:="Tableau croisé dynamique1", DefaultVersion:=6
Ajout de champs de données au TCD (.PivotFields) :
With activesheet.PivotTables("Tableau croisé dynamique1").PivotFields("aaaamm") .Orientation = xlColumnField .Position = 1 End With
Les champs peuvent être positionnés en filtre de page (.Orientation = xlPageField), en colonnes (.Orientation = xlColumnField) ou en lignes (.Orientation = xlRowField).
La commande .Position = 1 indique l’ordre de positionnement de chaque champ.
La commande .PivotItems().Visible permet d’inclure ou d’exclure certaines valeurs du TCD.
For i = 1 To .PivotItems.Count If .PivotItems(i).Name Like "3*" Or .PivotItems(i).Name Like "603*" Or .PivotItems(i).Name Like "713*" Or .PivotItems(i).Name Like "68173*" Or .PivotItems(i).Name Like "78173*" Then .PivotItems(i).Visible = True Else: .PivotItems(i).Visible = False End If Next i
Une boucle compteur For… To… Next lit le nom de chaque valeur .PivotItems(i).Name de la première (i = 1) à la dernière (To .PivotItems.Count) et le compare (Like) aux comptes à afficher (“603*”, “713*”…) et en fonction de la correspondance ou non entre le nom est la valeur recherchée les rend visible (.PivotItems(i).Visible = True) ou non (.PivotItems(i).Visible = False). Etant donné le nombre de lignes susceptible d’être traité (jusqu’à un million sous Excel depuis la version 2007), la variable i sera de type Long (dim i as Long).
Ajout de valeurs (.AddDataField) : les données à synthétiser peuvent être additionnées (xlSum), dénombrées (xlCount)…
activesheet.PivotTables("Tableau croisé dynamique1").AddDataField activesheet.PivotTables("Tableau croisé dynamique1").PivotFields("Solde"), "Somme de Solde", xlSum
Formatage de l’affichage des valeurs (.NumberFormat) :
With activesheet.PivotTables("Tableau croisé dynamique1").PivotFields("Somme de Solde") .NumberFormat = "#,##0.00" End With
Le format avec séparateur de milliers et double décimale est représenté ainsi : “#,##0.00”.
Approfondir le sujet : Programmer en VBA, Analyse de données


Derniers articles parBenoît RIVIERE (voir tous)
- Le régime de la TVA sur la marge : 7ème partie – Calcul en ligne de la TVA sur la marge globale - jeudi 30 mars 2023
- Bases d’information publiques utiles à l’évaluation de l’intégrité des tiers (guide de l’AFA) - dimanche 19 mars 2023
- Excel : Obtenir les distances et temps de parcours d’un trajet Google Maps - samedi 18 mars 2023
- Evaluation de biens immobiliers : le prix de l’immobilier en France - samedi 11 mars 2023
- Règlement ANC n° 2022-06 relatif à la modernisation des états financiers : 5ème partie – Impact sur le calcul de la VA CVAE et sur celui de la RSP - dimanche 5 mars 2023