VBA, EXCEL : créer un tableau croisé dynamique

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.

ANA-FEC - TCD Analyse cohérence des stocks

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 :

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).

Ajout de champs de données au TCD (.PivotFields) :

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.

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)…

Formatage de l’affichage des valeurs (.NumberFormat) :

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

Share Button
The following two tabs change content below.
Benoît RIVIERE
Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog. Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.