On en a vu une dans le billet du 22 janvier dernier : on parlait des sous-totaux de champs (de lignes par exemple), qu’on ne peut supprimer qu’un après l’autre - jusqu’à Excel 2003. Avec une macro VBA, on a alors vu comment les supprimer tous d’un seul coup.
Ici on va en voir une autre, à propos des fonctions d’agrégation des champs de données.
Mise en situation.
Parfois on génère un TCD, on place ses champs de données et on se retrouve devant ça :
Le choix que fait Excel est très simple :
- si, dans la table qui sert de base, toutes les cellules de la colonne en question contiennent des valeurs numériques, alors Excel va faire une Somme lorsqu’on placera le champ en zone de données ;
- dans tous les autres cas, ce sera un comptage (fonction Nombre - cela s’appelle Compte à partir d’Excel 2007 mais je l’appelle ici Nombre tout bêtement parce que j’ai créé cette macro sous Excel 2003). Concrètement, il s’agira d’un comptage de valeurs, doublons compris et hors cellules vides, c’est à dire d’un comptage du nombre de cellules non-vides dans la colonne correspondante de la base.
On se trouve dans l’un de ces autres cas par exemple lorsqu’on place en zone de données un champ contenant du texte. Là, de toute manière, on ne peut rien faire d’autre que compter ! Mais c’est aussi le cas s’il y a dans la colonne la moindre cellule vide : une cellule vide, ce n’est pas une valeur numérique, donc il n’y a pas que des valeurs numériques, donc Excel fait un comptage. Et c’est même le cas avec des dates. (Ce qui n’est pas bête de la part d’Excel : même si les dates sont techniquement des valeurs numériques, on veut éventuellement les compter, pas les additionner !)
Dans le cas illustré ci-dessus, on a presque uniquement des données numériques : des quantités, des montants. Je dis presque uniquement, parce que j’ai glissé dans la base quelques cellules vides, d’où l’utilisation de la fonction d’agrégation Nombre.
Mais on veut additionner ces valeurs, et pas les compter, n’est-ce pas ? Ce à quoi on veut arriver, c’est évidemment ceci :
Comment rectifier le tir ?
À la main, ce n’est pas très difficile. Lorsqu’on est positionné sur une cellule du champ (c’est à dire lorsque la cellule active est occupée par ce champ), on ouvre la fenêtre des paramètres de champ et on va modifier la fonction affichée dans la liste “Synthèse par”, pour passer de Nombre à Somme :
Au clavier, il y a plusieurs chemins. Pas de “raccourci clavier” à proprement parler mais une série de Alt, de Tab, de flèches et de Entrée. (Je ne détaille pas ça ici.)
Souris ou clavier, ce n’est pas spécialement fastidieux. Là où ça le devient, c’est dans les situations où on a placé en zone de données 10 champs, ou 20, ou plus, et que la plupart sont agrégés en Nombre. S’il faut les rectifier un après l’autre, c’est pénible !
Une solution en VBA.
Lançons l’enregistreur de macro avant de faire l’opération décrite ci-dessus, pour voir.
Ca va donner ça :
Sub Macro1()
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"Nombre de quantité").Function = xlSum
End Sub
Petite parenthèse à propos de l’enregistreur de macros :
- pardon aux vrais puristes qui voient ça comme un truc d’amateur et qui ne l’utilisent jamais ;
- clin d’oeil aux vrais-faux puristes qui disent que c‘est un truc d’amateur et qui prétendent ne jamais l’utiliser...
Qu’est-ce que ça illustre ?
Pour un champ de TCD placé en zone de données, Function est la propriété qui définit la fonction utilisée par Excel pour agréger les données de la base à un croisement de critères.
Il y a un large éventail de fonctions disponibles :
- les plus couramment utilisées sont celles qu’Excel choisit par défaut lors de la construction du TCD : xlSum (Somme) et xlCount (Nombre) ;
- l’image ci-dessus montre une partie des autres fonctions disponibles, moyenne, minimum et maximum entre autres. Je ne vais pas les lister ici, ce n’est pas l’objet de ce billet. J’aurai l’occasion d’en parler dans un prochain billet dédié aux paramètres de champs.
On pourrait imaginer de faire comme ce qu’on a fait pour supprimer les sous-totaux de tous les champs : parcourir l’ensemble de tous les champs du TCD, et donc travailler en VBA sur chacun. Mais on se heurterait à trois problèmes :
1. d’abord, pour les champs qui ne seraient pas en zone de données, c’est inutile de vouloir spécifier une fonction d’agrégation - du reste, essayer de faire ça en VBA provoquerait une erreur.
2. ensuite, un champ peut être placé plus d’une fois en zone de données ; ça arrive qu’on le fasse pour utiliser plusieurs fonctions sur une même colonne de la base de données (minimum, moyenne et maximum par exemple). Je l’ai signalé dans un précédent billet : dans la zone de données, un champ de TCD ne se comporte plus de la même façon que dans les autres zones.
3. last but not least : on ne veut pas forcément faire des additions sur tous les champs qu’on a placés en zone de données. Il y a notamment les champs qui correspondent à du texte dans la base données. Si on les places en zone de données, c’est pour faire des comptages, pas des additions !
Il vaut donc mieux cibler les champs sur lequels on agréger les données par Somme. Astuce toute simple : on peut se servir de la plage sélectionnée dans la feuille !
L’idée est alors de parcourir, l’une après l’autre, les cellules de cette plage sélectionnée, de repérer le champ de TCD qui s’y trouve, et de travailler dessus !
Allons-y !
Parcourir les cellules de la plage sélectionnée, c’est facile :
Dim c As Range
For Each c In Selection
' instructions
Next c
Le champ de TCD qui se trouve sur une cellule, c’est : c.PivotField
Donc la manipulation à effectuer consiste en une seule instruction :
c.PivotField.Function = xlSum
Assemblons tout cela :
Dim c As Range
For Each c In Selection
c.PivotField.Function = xlSum
Next c
Voilà pout le socle de la macro.
Maintenant, comme on l’a signalé plus haut, cela va provoquer une erreur si le champ en question se trouve non pas dans la zone de données mais dans une autre zone : on ne peut pas définir la propriété Function sur autre chose qu’un champ de données ! (Et, du reste, il y aura aussi une erreur si la cellule n’est même pas sur un TCD.)
On va contourner le problème en entourant l’instruction centrale ainsi :
- juste avant, dire que, en cas d’erreur, il faut continuer à la ligne suivante :
On Error Resume Next
- juste après, revenir au mode de gestion normal des erreurs :
On Error GoTo 0
Et une dernière chose, à fois pour la vitesse et pour la cosmétique de l’opération : on entoure le tout de commandes pour désactiver/réactiver le rafraichissement écran. On fait ça en manipulant la propriété Application.ScreenUpdating.
Et hop, tout ça ensemble :
Sub FonctionSommeChampsData_TCD()
' Remplace par xlSum la fonction d’agrégation
' des champs de TCD de la PLAGE SELECTIONNEE
Application.ScreenUpdating = False
Dim c As Range
For Each c In Selection
On Error Resume Next
c.PivotField.Function = xlSum
On Error GoTo 0
Next c
Application.ScreenUpdating = True
End Sub
Limpide !
RépondreSupprimer