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)
- VBA/SQL vs Power Query : deux solutions complémentaires - mercredi 2 octobre 2024
- L’IA dans les cabinets comptables : cas concrets - jeudi 26 septembre 2024
- EXCEL : insérer une image ou un logo dans une cellule - lundi 16 septembre 2024
- Lancer l’exécution d’un script Python à partir d’une macro VBA - lundi 9 septembre 2024
- Open Data : quoi de neuf ? - lundi 2 septembre 2024