Supprimer les sous-totaux d’un TCD

Parfois on génère un TCD, on place ses champs là où on veut et on se retrouve devant ça :

Ça, c’est les sous-totaux de champs qu’Excel place par défaut.
Sauf que parfois, on veut plutôt arriver à ça - la même chose sans les sous-totaux :

Ces sous-totaux, on peut tous les enlever d’un seul coup depuis Excel 2007, via la commande : Outils de Tableau croisé dynamique / Création / Sous-totaux / Ne pas afficher les sous-totaux.

Mais jusqu’à Excel 2003, on devait les enlever un par un. J’en parle ici d’une part parce qu’Excel 2003 est encore assez répandu, d’autre part et surtout parce que c’est l’occasion d’aborder quelques points intéressants en VBA au sujet des TCD.

Si on a quinze champs champs comme ça, cet ajustement est un peu pénible . En plus, ces sous-totaux ne sont visibles que sur les champs de ligne ou de colonne. Donc si on commence par faire le ménage uniquement sur les zones de ligne ou de colonne, et si on déplace ensuite vers ces zones des champs pris ailleurs, il y aura de nouveau du ménage à faire.

Le plus sûr, ce serait de faire le tour de tous les champs dès le début et d’en supprimer les sous-totaux.


Quelques mots sur cette manip de ménage.
(Excel 2003 only.)

Le chemin classique sur un champ précis, c’est d’aller dans la fenêtre des paramètres de champ et d’y sélectionner l’option Aucun des sous-totaux. Voyons ça pas à pas : on ouvre cette fenêtre par un clic droit sur le champ lui-même dans le TCD (la commande “Paramètres de Champ” se trouve dans le menu contextuel), ou bien par ce même bouton situé dans la barre d’outils TCD, ou encore par un double-clic sur l’étiquette du champ. Ensuite, il reste à faire un clic sur la bonne option et un clic sur “OK” ; ou alors un double-clic sur l’option, tout simplement.

Bon, clics simples ou doubles-clics, on s’en sort en ayant cliqué quatre fois. Si on a 25 champs à traiter, ça fait 100 clics. Un peu long, non ?

Plus rapide : supprimer ces sous-totaux au clavier. Facile : il suffit que la cellule active soit sur une ligne de sous-total (par exemple une des cellules entourées sur la première image de ce billet). Et là, on fait “Ctrl -”. C’est un raccourci des plus confortables : les touches “Ctrl“ et ”-“ sont en général aux coins du clavier (sur les clavier d’ordinateur fixe en tout cas), on pourrait faire ça avec des moufles. Si on a 25 champs à traiter, ça va déjà un peu plus vite qu’à la souris.

Cela dit, il y a deux problèmes. D’abord 25 champs c’est à peu près gérable, mais si on en a 50, ou 60, ou plus, ça devient fastidieux. Ensuite et surtout, on ne peut pas effectuer ces manips sur n’importe quel champ : celle à la souris fonctionne sur les champs de page, de ligne, et de colonne ; celle au clavier uniquement sur les champs de ligne et de colonne (pas sur les champs de page). Mais sur un champ masqué (présent dans la base mais non affiché dans le TCD), il n’y a rien à faire. Et sur un champ placé dans la zone de données, non plus (du reste, un champ placé là ne se comporte plus du tout de la même façon, mais c’est une autre histoire).

Bref, il va falloir trouver autre chose. On va aller voir ce qu’on peut faire en VBA. Mais avant, faisons une...



Brêve parenthèse de théorie.

Le sous-total est un attribut du champ de TCD. Il permet d’afficher, pour ce champ, le résultat d’un calcul effectué sur les valeurs affichées dans la zone de données. Ce résultat n’est affiché que s’il y a un autre champ placé dans la même zone (ligne ou colonne), plus loin dans la hiérarchie des positions. Exemple avec la première image de ce billet  : quatre champs sont placés en zone de ligne, un sous-total s’affiche pour chacun des trois premiers ; mais pas pour le quatrième (ça n’aurait pas de sens). Poursuivons.

Cet attribut est réglé sur une des trois options suivantes : (je passe très vite dessus)
- Automatique : c’est l’option par défaut et, dans la plupart des cas, disons que cela correspond à une somme.
- Personnalisés : onze fonctions de calcul sont disponibles, les plus couramment utilisées étant sans doute la somme, le nombre de valeurs numériques (Nbval), le nombre de valeurs (Nb), la moyenne, le minimum et le maximum. Notons qu’il est possible d’en sélectionner plusieurs et donc d’afficher plusieurs sous-totaux pour un même champ !
- Aucun : c’est l’option qui permet de n’afficher aucun sous-total pour le champ, comme on la vu plus haut. C’est ce qu’on veut faire ici pour tous les champs.

Cette question des options de sous-totaux est un vaste sujet à lui tout seul. J’y consacrerai un autre billet. Contentons-nous ici de ce court aperçu.



Revenons à la pratique et voyons ce qu’on peut faire en VBA.

Il s’agit de trouver un moyen de spécifier qu’on ne veut Aucun sous-total pour chacun des champs du TCD. On va se servir d’une boucle pour parcourir les champs du TCD.


Préparatifs

Côté variables, on aura essentiellement besoin de deux choses :
- une variable “champ de TCD” (PivotField) pour la boucle - appelons-la PF ;
- una variable “TCD” (PivotTable) pour désigner le TCD lui-même - appelons-la PT. Ça, ce n’est pas indispensable mais c’est commode : désigner un TCD de façon explicite est assez long, et on devra le faire plusieurs fois. Donc utiliser une variable pour ça et la définir dès que possible, ça allègera l’histoire.

Poussons plus loin les préparatifs autour de cette dernière variable : on pourrait repérer le TCD d’après son nom, par exemple de cette manière :
   Set PT = ActiveSheet.PivotTables("Tableau croisé dynamique1")
Mais on peut faire mieux : repérer le TCD positionné sur la cellule active :
   Set PT = ActiveCell.PivotTable
Comme ça, on peut cibler un TCD sans le nommer dans le code VBA (ça évite une référence en dur) et même s’il y en a plusieurs sur la même feuille. Il suffit de faire en sorte que la cellule active soit occupée par un TCD, c’est à dire de sélectionner une cellule du TCD !

Attention, si on sélectionne une autre cellule de la feuille, c’est à dire s’il n’y a pas de TCD sur la cellule active, alors la commande précédente générera une erreur. Pour éviter ce problème, on va le contourner avec une ligne de gestion d’erreur et une commande GoTo renvoyant à une étiquette placée en fin de macro. Ce type d’astuce n’est pas ce qu’il y a de plus propre dans un code VBA mais ici c’est vraiment simple et pratique. De toute façon, la macro ne sera pas très longue et n’a de sens que si on travaille réellement sur un TCD !

Ce sera donc ceci :

On Error GoTo Fin

Set PT = ActiveCell.PivotTable

On Error GoTo 0

Et la fin de la macro sera donc comme cela :

Fin:

On Error GoTo 0

End sub

La boucle

La boucle ressemblera à quelque chose comme ça :

Dim PT As PivotTable, PF As PivotField

On Error GoTo Fin

Set PT = ActiveCell.PivotTable

On Error GoTo 0

For Each PF In PT.PivotFields

''' instructions de suppression de sous-totaux

Next

Je reviens maintenant sur cette parenthèse faite plus haut : les champs qu’on place en zone de page, de ligne ou de colonne peuvent avoir des sous-totaux, de même que ceux qui sont cachés, mais pas ceux qui sont en zone de données ! Autrement dit, on ne peut pas parcourir tous les PT.PivotFields : on doit exclure de notre boucle les champs de données.

On pourrait imaginer de parcourir quand-même l’ensemble des PT.PivotFields et, pour chacun, de vérifier s’il est placé en zone de données ou non. Mais cette vérification serait acrobatique, notamment parce qu’un même champ peut être placé simultanément en zone de données et dans une autre zone. (Je l’ai précisé tout à l’heure : un champ en zone de données ne se comporte plus de la même façon que dans les autres zones. Ce sera sans doute aussi l’objet d’un billet sur les TCD.)


La boucle autour de la boucle

On va donc faire autrement. On va parcourir non pas l’ensemble des champs du TCD mais un groupe de sous-ensembles comprenant les champs de page, les champs de ligne, les champs de colonne et les champs cachés. Ce groupe de sous-ensembles est facile à définir avec la fonction Array qui renvoie un tableau :

Array(PT.PageFields, PT.RowFields, PT.ColumnFields, PT.HiddenFields)

Seulement voilà : cet Array ne contient pas des champs de TCD mais des sous-ensembles de champs de TCD, ce qui n’est pas la même chose : on ne peut pas le parcourir avec la variable PF ! Il va donc nous falloir créer une boucle autour de la boucle déjà esquissée, pour parcourir cet Array à l’aide d’une nouvelle variable qui désignera un sous-ensemble de champs. Cette variable-ci pourrait être de type PivotFields (avec un ‘s’, il s’agit d’une collection) mais il y a encore un brin de complication : si une zone ne contient aucun champ, le sous-ensemble correspondant ne sera pas une collection de champs mais rien (Nothing) : on est donc obligé de déclarer la variable avec le type Variant. Appelons-là PFs - puisque c’est quand-même de ça qu’il s’agit.

À partir de là, la boucle qui existait déjà va parcourir non pas les champs du TCD mais ceux du sous-ensemble PFs qu’on vient de définir.

On arrive donc à quelque chose comme ceci :
Dim PFs as Variant
For Each PFs In Array(PT.PageFields, PT.RowFields, PT.ColumnFields, PT.HiddenFields)
For Each PF In PFs
''' instructions de suppression des sous-totaux
Next
Next

Voilà, c’est tout pour les questions de boucle !
Maintenant, il nous reste à voir ce que sont exactement...


Les instructions de suppression de sous-totaux

Si on lance l’enregistreur de macros avant de supprimer manuellement les sous-totaux du champ “région” du TCD de la première image de ce billet, on va voir ceci :
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("région"). _
Subtotals = Array(False, False, False, False, False, False, False, False, _
False, False, False, False)

Qu’est-ce que c’est, cette longue série de False ?

Il y en a exactement douze et c’est l’occasion de reparler de la parenthèse théorique faite plus haut. On y a vu que les sous-totaux Personalisés pouvaient faire appel à onze fonctions différentes et que plusieurs d’entre elles pouvaient être affichées simultanément. Ici il y a un paramètre de plus : c’est le numéro 1 et il correspond à l’option Automatique.

L’instruction qui supprime tous les sous-totaux d’un champ PF peut donc être tout simplement ceci :

PF.Subtotals = Array(False, False, False, False, False, False, _

False, False, False, False, False, False)

Mais on peut faire mieux.

Il se trouve que l’option de sous-total Automatique, lorsqu’on la sélectionne, supprime tous les autres sous-totaux qui sont éventuellement affichés (Min, Moyenne et Max par exemple). L’astuce consiste à utiliser cette priorité de l’option numéro 1 en l’affichant, pour que cela supprime automatiquement les autres :

PF.Subtotals(1) = True

et de supprimer ensuite uniquement cette option-là :

PF.Subtotals(1) = False

Et voilà !


Il ne reste plus qu’à assembler tout ça pour le mettre en musique.


Sub SuppressionSousTotauxTCD()

Dim PT As PivotTable, PFs As Variant, PF As PivotField

' définition du TCD
On Error GoTo Fin
Set PT = ActiveCell.PivotTable
On Error GoTo 0

' boucles
For Each PFs In Array(PT.PageFields, PT.RowFields, PT.ColumnFields, PT.HiddenFields)
For Each PF In PFs
    ' instructions
    PF.Subtotals(1) = True
    PF.Subtotals(1) = False
Next
Next

Fin:
On Error GoTo 0
End Sub


Comme d’habitude, avoir cette macro à portée de doigts (par exemple dans un classeur de macros personnelles accessible via une barre d’outils) permet de supprimer tous les sous-totaux d’un TCD en une fraction de seconde.