Editions ENI Livres | Vidéos | e-Formations
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.
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.
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.
À 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.
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 :
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.
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.
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).
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")
...
