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)
- Excel : collecter des données juridiques sur les entreprises avec l’API SIRENE – 1ère partie : base SIREN (entreprises) - lundi 15 avril 2024
- Obtenir les données financières de sociétés au format Excel - mardi 2 avril 2024
- Robotic Process Automation (RPA) et Python : piloter des applications web - dimanche 24 mars 2024
- Initiation à la programmation en Basic : nouveau compilateur FreeBASIC - lundi 18 mars 2024
- Retrogaming : (re)jouer aux jeux vidéo d’antan - lundi 11 mars 2024