Blog ENI : Toute la veille numérique !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
💥 1 livre papier acheté 
= la version en ligne automatiquement offerte. Cliquez ici
  1. Livres et vidéos
  2. Analysez efficacement vos données
  3. Tableaux croisés et VBA
Extrait - Analysez efficacement vos données à l'aide des tableaux croisés dynamiques (3e édition)
Extraits du livre
Analysez efficacement vos données à l'aide des tableaux croisés dynamiques (3e édition)
2 avis
Revenir à la page d'achat du livre

Tableaux croisés et VBA

Introduction

Ce chapitre n’a pas pour vocation de faire de vous un développeur VBA sur Excel. Par contre, si vous devez automatiser des tâches pour des personnes qui ne maîtrisent pas Excel, les macros et les procédures VBA pourront vous rendre service.

Vous disposez de deux méthodes pour concevoir des macros :

  • Dans un premier temps enregistrer la macro puis la modifier dans un deuxième temps.

  • Écrire directement la macro en Visual Basic.

La première méthode permet de laisser Excel écrire le texte de la macro, ce qui évite bien sûr les erreurs de syntaxe et vous évite d’avoir à maîtriser le langage Visual Basic.

La deuxième méthode nécessite que vous maîtrisiez le langage Visual Basic.

Nous allons tout d’abord mettre en pratique la première méthode.

 Ouvrez le classeur Mesures.xlsx.

 Chaque ligne de la feuille Liste contient le nombre de mesures effectuées par une personne sur un site industriel. Les feuilles ListeSiteB et ListeSiteC contiennent les mêmes informations pour deux autres sites.

images/C10-01.PNG

Trois plages dynamiques ont été créées, chacune relative à la feuille d’un site.

Pour vous familiariser avec la syntaxe VBA relative aux tableaux croisés dynamiques, nous allons créer un tableau croisé tout en laissant Excel enregistrer nos actions....

Créer un tableau croisé à l’aide d’une macro

Activer l’onglet Développeur

Si vous n’avez pas encore travaillé avec l’onglet Développeur, activez-le :

 Effectuez un clic droit sur le ruban puis cliquez sur l’option Personnaliser le ruban.

 Dans la catégorie Personnaliser le ruban, dans la liste Onglets principaux, activez la case à cocher Développeur, puis cliquez sur OK.

images/C10-02.PNG

L’onglet Développeur a été ajouté dans le ruban à droite de l’onglet Affichage.

Enregistrer la macro

Lors de l’enregistrement d’une macro, Excel transcrit toutes nos actions en langage VBA. Si vous commettez une erreur lors de l’enregistrement, cette erreur sera mémorisée et reproduite à chaque exécution de la macro.

Soyez donc vigilant lors de la phase d’enregistrement, ne cherchez pas à brûler les étapes.

 Pour que la macro puisse enregistrer le positionnement du curseur dans la bonne feuille, cliquez tout d’abord dans une cellule de la feuille Liste.

 Dans l’onglet Développeur, groupe Code, cliquez sur le bouton Enregistrer une macro.

 Saisissez le nom de la macro dans la boîte de dialogue puis validez en cliquant sur OK.

Le raccourci-clavier peut vous permettre plus tard d’exécuter rapidement la macro. La description permet d’expliquer ce que réalise la macro.

images/C10-03.PNG

À partir de maintenant, Excel enregistre toutes vos actions. Vous pouvez remarquer dans le coin inférieur gauche de votre écran le bouton d’arrêt de l’enregistrement.

images/C10-04.PNG

 Cliquez dans la cellule A3 de la feuille TABCROIS.

 Dans l’onglet Insertion, groupe Tableaux, cliquez...

Modifier la plage source à l’aide d’une macro

 Pour que la macro puisse enregistrer le positionnement du curseur dans le tableau croisé dynamique, cliquez tout d’abord dans une cellule d’une autre feuille que la feuille TABCROIS.

 Dans l’onglet Développeur, groupe Code, cliquez sur Enregistrer une macro.

 Saisissez le nom de la macro ModifSource dans la boîte de dialogue puis validez.

À partir de maintenant, Excel enregistre toutes vos actions.

 Sélectionnez la feuille TABCROIS puis cliquez sur une cellule du tableau croisé.

 Dans l’onglet Outils de tableau croisé dynamique - Analyse, groupe Données, cliquez sur le bouton Changer la source de données, puis saisissez le nom de la plage dynamique correspondant au deuxième site :

images/C10-09.PNG

 Validez par OK.

 Cliquez en G1 pour sortir du tableau croisé.

 Arrêtez l’enregistrement.

Le listing de la macro est détaillé ci-dessous :


Sub ModifSource() 
' ModifSource Macro 
' 
' 
    Sheets("TABCROIS").Select 
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotSelect 
"AGENT[All]", _ 
       xlLabelOnly + xlFirstRow, True 
    Range("A9").Select 
    ActiveSheet.PivotTables("Tableau croisé...

Lier les filtres de plusieurs tableaux croisés

Lorsque vous avez construit plusieurs tableaux croisés à partir d’une même source de données et que ces tableaux sont filtrés sur le même champ, il est possible de synchroniser les filtres de tous les tableaux.

L’objectif est que la modification du filtre du premier tableau croisé se répercute automatiquement sur les autres.

Le classeur Incidents.xlsx va nous permettre de mettre en pratique cette technique. Celui-ci recense les pannes survenues dans une usine. Le champ COUT MO correspond au coût de la main-d’œuvre.

images/C10-12.PNG

Trois tableaux croisés ont été construits :

1. Durées totales mensuelles des arrêts par service (TCD1)

2. Coût total mensuel des pièces par service (TCD2)

3. Coût total mensuel de la main d’œuvre par service (TCD3)

Chaque tableau croisé utilise la fonction somme et comporte comme filtre le type de panne.

 Ces trois tableaux croisés dynamiques ont été élaborés dans la feuille TCD.

images/C10-13.PNG

 Chaque tableau a été renommé : TCD1, TCD2 et TCD3. (Clic droit sur le tableau croisé, Options du tableau croisé dynamique...).

 Les nombres du premier tableau ont été formatés au format horaire dépassant les 24 heures (sans les secondes).

images/C10-14.PNG

Notre procédure doit...

Compléments

Si vous devez réaliser d’autres macros, utilisez toujours pour débuter le mode enregistrement, ceci vous permettra d’obtenir simplement les syntaxes Visual Basic des différentes fonctionnalités des tableaux croisés. Vous pourrez ainsi obtenir et modifier le code pour filtrer, regrouper, modifier les formats…

Nous vous présentons ci-après quelques codes.

Ne pas afficher la liste des champs


  ActiveWorkbook.ShowPivotTableFieldList = False 
 

Formater les valeurs du tableau en euro


    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _ 
        "Somme de MONTANT") 
        .NumberFormat = "# ##0,00 €" 
    End With
 

Actualiser un tableau croisé


    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
 

Actualiser tous les tableaux croisés


    ActiveWorkbook.RefreshAll
 

Supprimer les totaux des lignes


    ActiveSheet.PivotTables("Tableau croisé dynamique1").ColumnGrand = False
 

Supprimer les totaux des colonnes


    ActiveSheet.PivotTables("Tableau croisé dynamique1").RowGrand = False
 

Afficher le total des colonnes et masquer le total des lignes


    With ActiveSheet.PivotTables("Tableau croisé dynamique1") 
  ...