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

The following two tabs change content below.

• Bio
• Derniers articles

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

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