Macros-commandes VBA/Création de Tableau croisé

**Création de Tableau croisé**
Leçon : Macros-commandes VBA

Chapitre n^o 12
Chap. préc. :	Création de Graphique
Chap. suiv. :	Lectures et écritures

En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Création de Tableau croisé
Macros-commandes VBA/Création de Tableau croisé », n'a pu être restituée correctement ci-dessus.

Définitions

Qu’est-ce qu’un tableau croisé dynamique ?

Un tableau croisé dynamique (appelé aussi sous l’abréviation « TCD ») permet grâce à l’utilisation de certains tableurs, de synthétiser une base de données brutes. Le TCD est un outil très utile pour analyser des bases de données très fournies puisqu’il en facilite leurs analyses et leurs comparaisons.

De plus, c’est un outil totalement modulable et personnalisable. Sa particularité est qu’il est dynamique. On entend par dynamique, le fait que le tableau soit modulable en fonction des besoins d'analyse mais aussi par le fait qu’il prenne en compte toutes les modifications apportées au tableau brut, cela permet ainsi d’actualiser les données du TCD. En effet, on peut choisir les données que l’on veut faire apparaître en colonne et en ligne même après avoir généré un premier TCD. L'utilisateur a le choix de la fonction de regroupement, il peut également décider des champs à utiliser pour réaliser son analyse.

Grâce au TCD il est également possible d'afficher plusieurs fonctions (des moyennes, des sommes, etc.).

Exemple

Afin de comprendre l’utilité du TCD, prenons l’exemple d’une entreprise :

Une entreprise X dans le secteur de la téléphonie veut savoir combien de contrats clients ont été résiliés au cours de l’année N et pour quels motifs.

Au cours de l’année les salariés de l’entreprise X regroupent dans un tableau les différentes résiliations de la manière suivante :

Base de données de l'entreprise X

En fin d’année, le chef de service veut analyser les motifs de résiliations les plus courants. Son but : comprendre la raisons de la résiliation des clients et mettre en œuvre une solution pour y remédier. Les membres de son service réalisent alors le TCD suivant :

TCD de l'entrerprise X

Ainsi, le chef de service peut en conclure que le motif de résiliation qui revient le plus souvent est le prix. Il pourra alors appliquer une solution pour réduire les prix et conserver ses clients.

Qu’est-ce qu’une macro ?

Une macro ou macro-commande permet d'enregistrer une suite d'instructions écrites les unes après les autres. L'enregistrement d'une macro donne naissance à un code qui se fait en langage VBA (Visual Basic for Applications). Le langage VBA est un dérivé du langage Visual Basic, celui-ci a été spécialement développé pour les applications du pack Office (Word, Excel, PowerPoint).

Lorsque l’on exécute la macro, le tableur va interpréter les différentes instructions sauvegardées dans le code de la macro à exécuter. Cette exécution permettra de reproduire l'intégralité des commandes enregistrées. Chaque étape sera répétée à l'identique à chaque exécution de la macro.

La macro a pour finalité d'automatiser des tâches afin de les rendre beaucoup plus rapides que si elles avaient été effectuées manuellement.

Dans le cadre d'un projet d'entreprise, elles peuvent aussi avoir un intérêt lors de tests répétitifs de contrôle de qualité des données et/ou de tests de non-régression.

Son rôle est aussi de faciliter la vie de l'utilisateur. La macro est souvent utilisée lorsqu'une tâche est répétitive, ce qui permet à l'utilisateur de travailler beaucoup plus rapidement et efficacement sans erreurs manuelles.

Comment actualiser avec une macro un TCD créé manuellement?

Renseignement des données

Pour créer un TCD, il faut dans un premier temps créer une base de données dans un tableur pouvant faire appel au TCD.

Cette base de données devra alors contenir des titres en colonnes et/ou en ligne, puis devra être alimentée selon l'envie de l’utilisateur qui crée le tableau.

Il est primordial que chaque colonne possède une en-tête si l’on veut qu’elles figurent dans le TCD.

Remarque

Un TCD ne peut être créé s'il n'y a pas d'en-tête dans les colonnes.

Exemple

On peut reprendre l’exemple de l’entreprise X (voir « qu’est-ce qu’un tableau croisé dynamique ») qui regroupait en colonne les rubriques :

« Nom »
« Date d’inscription »
« Date de résiliation »
« Motifs de résiliation ».

Cette entreprise a ainsi alimenté son tableau au fur et à mesure de l’année.

Création manuelle du TCD

Lorsque le moment de l’analyse arrive, l’individu va créer manuellement son TCD.

La démarche est alors simple :

Sélectionner les données
Cliquer sur l'onglet « insertion »
Cliquer sur « tableau croisé dynamique »
Une boîte de dialogue s'affiche (Annexe 1)
Sélectionner "nouvelle feuille de calcul" ou "feuille de calcul existante"
Cliquer sur "Ok"

Conseil

Lors de la sélection des données, il est conseillé de sélectionner les colonnes afin que la sélection s'effectue jusqu'en bas. Cela évite de devoir redéfinir la source du TCD à chaque nouvelle saisie dans la base de données.

L'étape suivante est de définir les rubriques du TCD, c’est-à-dire, savoir ce que l’on va mettre en colonne, en ligne, et ce que l’on veut mettre en avant (une somme, un nombre, un pourcentage etc.).

Le choix peut être plus ou moins complexe, mais celui-ci est essentiel. Il faut que les résultats qui ressortent du TCD soient clairs et précis, afin que l’on puisse facilement les analyser. Il ne faut surtout pas que le TCD soit plus complexe que le tableau brut. Les résultats qui en ressortent doivent être évidents et "sauter aux yeux".

Enregistrement de la macro : Actualiser le TCD automatiquement

Afin d’actualiser automatiquement le TCD, nous allons faire appel à une macro VBA. Pour que cela soit plus simple à réaliser, nous avons décidé de faire appel à l’enregistreur. Cette manipulation est simple, il suffit de faire l’actualisation du TCD manuellement tout en enregistrant une macro. Ainsi, l’enregistreur écrira tout seul la macro dans Visual Basic.

1ère étape : L’enregistreur

Cliquer sur l'onglet « Affichage » ou sur l'onglet "Développeur"
Cliquer sur « enregistrer une macro », une boite de dialogue va alors s'ouvrir (annexe 2) avec les rubriques suivantes :
- Nom de la macro, ex : « ActualiserTCD »
- Touche de raccourci
- Enregistrer la macro dans
- Description, ex : "Actualiser automatiquement le TCD"
- OK
Effectuer l'action d'actualisation : Faire un clic droit sur le TCD et cliquer sur « Actualiser les données »
Se rendre à nouveau sur l'onglet "Affichage" ou "Développeur"
Cliquer sur « Arrêter l’enregistrement »

2e étape : Analyse du langage Visual Basic

Cliquer sur « Macro »
Cliquer sur la macro crée : « ActualiserTCD » ;
Cliquer sur « Modifier » ;

Ou passer par Visual Basic dans "outil"

La macro suivante apparaît :

Sub ActualiserTCD()
'
' ActualiserTCD Macro
'
    Sheets("Feuil2").Select
    Range("G7").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").RefreshTable
End Sub

Il est important d’examiner les actions qui ont été enregistrées par Excel.

C’est d’autant plus important lorsque la macro ne s’exécute pas comme on le souhaite. On peut rapidement voir où se trouve l’erreur d’écriture.

De plus, analyser la macro permet également d’apporter des modifications. En effet, lors de l’enregistrement, on a tendance à vouloir aller trop vite, et on clique sur des boutons qu’il ne faut pas. Toutes ces manipulations inutiles peuvent être supprimées.

Remarque

Afin de désactiver la carte vidéo et améliorer les performances, il faut rajouter :

En début de macro : Application.ScreenUpdating = False
En fin de macro : Application.ScreenUpdating = True

La macro finale sera donc :

Sub ActualiserTCD()
'
' ActualiserTCD Macro
'
Application.ScreenUpdating = False
    Sheets("Feuil2").Select
    Range("G7").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").RefreshTable
Application.ScreenUpdating = True
End Sub

Création d’un bouton et affectation de la macro « Actualiser le TCD »

Pour finir, nous allons créer un bouton qui permettra à l’utilisateur d’actualiser le TCD uniquement en cliquant dessus.

La démarche est alors la suivante :

Insérer une forme ou une image en cliquant sur l'onglet insertion, ex : un rectangle
Modifier la mise en forme et ajouter un texte s'il s'agit d'une forme, ex de texte : « Cliquer ici pour actualiser le TCD »
Faire un clic droit sur la forme et cliquer sur « Affecter une macro »
Une boîte de dialogue va s'ouvrir (Annexe 3), sélectionner la macro « ActualiserTCD » et cliquer sur OK

Comment créer automatiquement un TCD à l’aide d’une macro ?

Test avec enregistrement

Démarche

Pour automatiser la création du TCD, on peut passer par la méthode de l’enregistreur.

La démarche est la suivante :

1ere étape : Création de la macro

Activation de l’enregistreur
Nommer la macro : « CREERTCD » ; « OK »
Sélection des données : sélectionner les colonnes afin que cela prenne en compte toutes les lignes en cas d’ajout de données
Insertion du TCD : « Insertion » ; « Tableau croisé dynamique »
Choisir d’imposer ou non les champs du TCD à l’utilisateur :
- Si oui, choisir les rubriques à mettre dans « Etiquettes de colonnes » ; « Etiquettes de lignes » , « Filtre du rapport » ; « Valeurs »
- Si non, passer à l’étape suivante
Actualiser le TCD : Clic droit ; « Actualiser » (cette étape permet de prendre en compte les lignes supplémentaires entre la saisie des données du tableau et la création du TCD)
Choix des champs du TCD
Arrêter l’enregistreur

2e étape : Exécution de la macro

Il y a une erreur qui survient lorsque l’on utilise l’enregistreur pour automatiser la création d’un TCD. En effet, si l’on exécute la macro, une boîte de dialogue s’ouvre (annexe 4).

Cette dernière a pour rôle d’indiquer lorsqu’il y a une erreur d’écriture dans la macro.

3e étape : Analyse de l’écriture dans Visual Basic

Lorsqu’il y a une erreur, il est nécessaire d’examiner ce qu'Excel a enregistré. En reprenant l’exemple de l’entreprise X, le code suivant apparaît sous Visual Basic :

Sub CREERTCD()
'
' CREERTCD Macro
'
' Sélection des données
    Sheets("BASE DE DONNEES").Select
    Columns("A:D").Select

' Création du TCD
    Sheets.Add
      ActiveWorkbook.Worksheets("ACTUALISER TCD").PivotTables( _
        "Tableau croisé dynamique7").PivotCache.CreatePivotTable TableDestination:= _
        "Feuil1!L3C1", TableName:="Tableau croisé dynamique1", DefaultVersion:= _
        xlPivotTableVersion12
    Sheets("Feuil1").Select
    Cells(3, 1).Select

' Actualiser
    Range("C9").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique4").PivotCache.Refresh

End Sub

Le problème vient de cette partie de l’écriture :

    ActiveWorkbook.Worksheets("ACTUALISER TCD").PivotTables( _
        "Tableau croisé dynamique7").PivotCache.CreatePivotTable TableDestination:= _
        "Feuil1!L3C1", TableName:="Tableau croisé dynamique1", DefaultVersion:= _
        xlPivotTableVersion12

Le problème est que nous spécifions la feuille de destination et également la plage du tableau croisé dynamique dans l’écriture. La conséquence de ce type d’écriture est que la macro n’est pas dynamique. Ce qui explique que la macro ne puisse s’exécuter.

4e étape : Résolution du problème Pour résoudre le problème, il faut supprimer quelques lignes :

Il faut effacer la valeur qui se trouvait dans : TableDestination

Cela revient à : TableDestination = ""

Supprimer les lignes :

Sheets.add ; Sheets (« sheetname »).Select ; Cells (x,y).Select

5e étape : L’écriture finale pour automatiser la création d’un TCD

Sans contrainte des champs :

Sub CREERTCD()
'
' CREERTCD Macro
'
' Sélection des données
    Sheets("BASE DE DONNEES").Select
    Columns("A:D").Select

' Création du TCD
    ActiveWorkbook.Worksheets("ACTUALISER TCD").PivotTables( _
        "Tableau croisé dynamique7").PivotCache.CreatePivotTable TableDestination:= _
        "", TableName:="Tableau croisé dynamique4", DefaultVersion:= _
        xlPivotTableVersion12

' Actualiser
    Range("C9").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique4").PivotCache.Refresh

End Sub

Remarque

Il est possible de modifier les intitulés des colonnes uniquement lorsque les champs ne sont pas définis dans la macro.

Avec contrainte des champs :

Sub CREERTCDCHAMPS()
'
' CREERTCDCHAMPS Macro
'
' Sélection des données
    Sheets("BASE DE DONNEES").Select
    Columns("A:D").Select

' Création du TCD
    ActiveWorkbook.Worksheets("ACTUALISER TCD").PivotTables( _
        "Tableau croisé dynamique7").PivotCache.CreatePivotTable TableDestination:= _
        "", TableName:="Tableau croisé dynamique3", DefaultVersion:= _
        xlPivotTableVersion12

' Choix des champs
    ActiveSheet.PivotTables("Tableau croisé dynamique3").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique3").PivotFields("Nom client"), _
        "Nombre de Nom client", xlCount
    With ActiveSheet.PivotTables("Tableau croisé dynamique3").PivotFields( _
        "Motif de résiliation")
        .Orientation = xlRowField
        .Position = 1
    End With

' Filtrer
    With ActiveSheet.PivotTables("Tableau croisé dynamique3").PivotFields( _
        "Motif de résiliation")
        .PivotItems("(blank)").Visible = False
    End With

' Actualiser
    Range("B5").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique3").PivotCache.Refresh

End Sub

6e étape : Création d'un bouton

Créer un bouton : Insertion d'une forme
Ajouter un texte : clic droit ; "modifier le texte" ; exemple : "CREER UN TCD"
Affecter la macro : clic droit ; "affecter une macro" ; choisir la macro ; "OK"

Avantages

La macro est générée automatiquement par l’enregistreur
Méthode simple lorsque l’on ne connaît pas le langage pour créer un TCD
Permet de se familiariser avec le langage VBA
Les champs en lignes et en colonnes ne sont pas imposés

Inconvénients

La macro peut seulement faire les actions que l’on a enregistré
Lorsqu’il y a une erreur, la macro ne fonctionne pas du tout
Il faut parfois revenir sur l’écriture
Il n’est pas évident de comprendre le problème de l’écriture lorsqu’il y a une erreur

Test avec langage Visual Basic

Démarches

Notre objectif est ici de créer un tableau croisé dynamique via le code VBA.

Cette macro permettra de créer un TCD avec des champs définis.
Elle impose donc à l'utilisateur de programmer dans la macro les champs voulus dans le TCD.

Exemple

Pour cela, nous remplissons un tableau de base avec toutes les données, nommé "Adhérents" dans la feuille "Données".

Ensuite, nous créons une nouvelle feuille, nommée "TCD automatique" afin d'y faire apparaître le TCD.

Explications des champs de l’en-tête de colonne "réglé" :

"O" pour réglé
"N" pour non-réglé

Tableau de base "Adhérents"

La démarche de notre programmation est la suivante :

1. Dimensionner les variables utilisées dans le code (phase facultative mais recommandée pour limiter les erreurs).
2. Affecter le TCD à la feuille "TCD automatique" afin de programmer que toutes les actualisations se feront sur cette feuille.
3. Supprimer tous les TCD existants de la feuille par une boucle For Each afin de rafraîchir proprement notre TCD.
4. Ajouter le TCD sur la feuille "TCD automatique" en respectant le code ci-dessous.

Attention : ce code impose à l'utilisateur de nommer son tableau de base. Ici, nous l'avons nommé "Adhérents". Nous indiquons l'emplacement du TCD; ici en cellule B5 et nous décidons de nommer notre TCD "TCD_Adhérents".

Voici ci-dessous ce que nous obtenons :

TCD Adhérents vide

5. Ajouter les champs, une fois le TCD crée. L'utilisateur programme les champs qu’il veut insérer dans son TCD : en ligne, en colonne et en valeur.
Ici il s'agit du champ "Catégorie" en ligne, "Réglé" en colonne et "Cotisation" en valeur.

Ces noms de champs doivent correspondre aux entêtes de colonne du tableau de base.

Sub create_TCD()

'Dimension des variables 

Dim wshTCD      As Worksheet
Dim PvtTCD      As PivotTable
    
   'Affectation du TCD à la feuille "TCD automatique"
    Set wshTCD = Worksheets("TCD automatique")
    
   'Suppression de tous les TCD existants dans la feuille
    For Each PvtTCD In wshTCD.PivotTables
        PvtTCD.TableRange2.Clear
    Next PvtTCD
    
   'Ajout d'un TCD sur la feuille "TCD automatique"
    Set PvtTCD = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Adhérents") _
                .CreatePivotTable(tabledestination:=wshTCD.Range("B5"), TableName:="TCD_Adhérents")
    
   'Ajout des champs au TCD
       With PvtTCD        

        'Ajout d'une Ligne
        With .PivotFields("Catégorie")
            .Orientation = xlRowField
            .Position = 1
        End With
        
        'Ajout d'une Colonne
        With .PivotFields("Réglé")
            .Orientation = xlColumnField
            .Position = 1
        End With
        
        'Ajout d'une Valeur Cotisation
        With .PivotFields("Cotisation")
            .Orientation = xlDataField
        End With
    End With

End Sub

Voici le résultat attendu :

TCD Adhérents

Remarque

L’utilisateur doit penser à nommer son tableau de base afin qu’il soit repris pour créer le TCD.
Cette macro permet de créer un TCD avec des champs définis. Elle impose donc à l'utilisateur de programmer dans la macro les champs voulus dans le TCD.

Avantages

Les avantages de la création d'un TCD automatique avec le langage Visual Basic :

Automatisation des tâches répétitives.
Suppression des erreurs humaines dès lors que le programme est débogué et qu’il n'y a pas de modification de la structure des données de base.
Une fois créé, c’est un gain de temps pour l'utilisateur.
Simplification de la transmission des connaissances à d'autres utilisateurs.
Harmonisation de la structure d'un TCD pour un ensemble d'utilisateurs. Le TCD "officiel" ne pourra pas prendre une autre forme que celui programmé.

Inconvénients

Les inconvénients de la création d'un TCD automatique avec le langage Visual Basic :

Nécessite une connaissance technique d'Excel et VBA pour développer le programme et le déboguer.
Demande un investissement en temps au départ pour mettre en œuvre la solution finalisée.
Demande une transmission des savoirs lors des changements d'organisation du travail.

Comment peut-on paramétrer un TCD ?

Appliquer une fonction

Cette macro applique une fonction de synthèse dans un champ défini.

Elle permet de choisir un type de calcul (somme, nombre, moyenne,...) que l’on souhaite utiliser pour résumer les données du champ sélectionné de manière automatique (par macro).

Dans l'exemple ci-dessous elle modifie la fonction de synthèse du champ "Cotisations", afin d'afficher la moyenne et de la renommer "Cotisation moyenne".

Sub ModifierFonctionDuTCD()

    'Définition des variables
    Dim pvtTCD As PivotTable
    
    'Affectation du TCD sur lequel on souhaite travailler
    Set pvtTCD = ActiveSheet.PivotTables("TCD_Adhérents")

    'Remplacement de la fonction "Somme" affectée au champ "Cotisation" par la fonction "Moyenne"
    With pvtTCD.PivotFields("Somme de Cotisation")
    
        .Function = xlAverage
        .NumberFormat = "# ##0 €"
        .Caption = "Cotisation Moyenne"
        
    End With
    

End Sub

Voici les autres constantes disponibles pour la propriété Function, que l’on peut appliquer de la même manière que xlAverage ci-dessus :

xlAverage - Moyenne
xlCountNums - Nb
xlMin - Min
xlStDev - Ecartype
xlSum - Somme
xlVar - Var
xlCount - Nombre
xlMax - Max
xlProduct - Produit

...

Remarque

Cette macro ne pourra être utilisée qu'une fois. Après, elle demandera une modification du code VBA.
En effet, la "Somme de Cotisation" va être modifiée par "Cotisation Moyenne".
Donc si l’on répète la macro il ne trouvera pas la "Somme de Cotisation" puisque cette fonction aura été modifiée.
Il faudra la remplacée par "Cotisation Moyenne" et indiquer la fonction que l’on souhaite appliquée.
À noter que la syntaxe à une très grande importance. Une "faute de frappe" empêchera la macro de fonctionner.

Paramétrer les sous-totaux

Nous avons la possibilité de paramétrer les sous-totaux de notre TCD. Cela permettra à l'utilisateur de figer ce mode de calcul et de l'automatiser.

Nous pouvons tout d’abord enlever les sous-totaux de l’ensemble des champs du TCD :

'
' Cette procédure parcourt l’ensemble des champs d'un TCD afin de leur enlever les sous-totaux s'ils existent
'
 Sub EnleverSousTotauxDuTCD()
 
    Dim pvtTCD As PivotTable
    Dim pvfTCD As PivotField
    
    ThisWorkbook.Sheets("TCD automatique").Select    
    Set pvtTCD = ActiveSheet.PivotTables("TCD_Adhérents")
    For Each pvfTCD In pvtTCD.PivotFields  
        If pvfTCD.Subtotals(1) Then pvfTCD.Subtotals(1) = False
    Next pvfTCD
    
 End Sub

Nous pouvons aussi décider d'appliquer des sous-totaux qu’à certains champs définis :

'
'Cette procédure paramètre les sous-totaux : n'applique des sous-totaux qu'aux champs définis
'
 Sub InitialiserSousTotauxDuTCD()
 
    Dim pvtTCD As PivotTable
    Dim pvfTCD As PivotField
    
    ThisWorkbook.Sheets("TCD automatique").Select
    Set pvtTCD = ActiveSheet.PivotTables("TCD_Adhérents")
    For Each pvfTCD In pvtTCD.PivotFields
        'Seuls les champs "Age" et "Catégorie" auront l’attribut subtotals activé
        Select Case pvfTCD.Name
            Case "Age", "Catégorie"
                pvfTCD.Subtotals(1) = True
            Case Else
                pvfTCD.Subtotals(1) = False
        End Select
    Next pvfTCD
    
 End Sub

Comment extraire une valeur d'un TCD ?

Cette méthode permet à l'utilisateur de repérer rapidement dans le TCD une valeur spécifique.
C'est très utile lorsque l'utilisateur répète toujours une même tâche et que le TCD est assez conséquent.

Exemple

Par exemple ici, dans l'exemple 2 (voir ci-dessous), l'employé doit calculer chaque mois le montant des cotisations de catégorie "E" non réglées.
Il n'aura donc qu’à lancer cette macro chaque mois afin de voir apparaître par un message (MsgBox) le montant recherché.

Sub ExtraireValeurDuTCD()

    Dim pvtTCD As PivotTable
    Dim rngPlage As Range
    
    ' Défini le TCD sur lequel on souhaite récupérer la valeur
    Set pvtTCD = ActiveSheet.PivotTables("TCD_Adhérents")   
    ' Exemple 1 : Cette procédure affiche la valeur de la synthèse "Cotisations" pour l'élément "A" du champ "Catégorie" grâce à la méthode GetPivotData.
    ' Récupération de la valeur du champs "Cotisation" pour la ligne "Catégorie A" =>> Récupère le total de la ligne.
        Set rngPlage = pvtTCD.GetPivotData("Cotisation", "Catégorie", "A")
    MsgBox rngPlage.Value
   
    ' Exemple 2: Récupération de la valeur du champs "Cotisation" pour la ligne "Catégorie E" de la colonne "Réglé N" =>> Récupère la valeur de la jonction ligne et colonne.
    Set rngPlage = pvtTCD.GetPivotData("Cotisation", "Catégorie", "E", "Réglé", "N")
    MsgBox rngPlage.Value

End Sub