Excel offre une panoplie complète de fonctions facilitant l’analyse de données. Une de celles-ci consiste à insérer des sous-totaux automatiquement dans des listes de données en fonction de critères prédéfinis.
Les sous-totaux peuvent prendre la forme d’additions, de dénombrements… L’insertion de sous-totaux fonctionne de la même manière que les requêtes SQL regroupement (GROUP BY).
Pour ce faire, sélectionner les données y compris les entêtes (la présence d’entêtes de données est obligatoire comme pour la mise en place de filtres ou de tableaux croisés dynamiques), par exemple ici avec un FEC :
Ensuite, actionner le Menu Données puis Sous-total :
La fenêtre Sous-total apparaît. Dans cet exemple, l’on souhaite insérer un sous-total pour les champs Débit et Crédit pour chaque écriture (champ EcritureNum) :
D’autres fonctions sont disponibles : Nombre, Moyenne, Max, Min… (cf tableau récapitulatif en bas de cet article).
Après validation de ces paramètres, Excel ajoute les sous-totaux ainsi qu’un total général.
Les sous-totaux ajoutés utilisent la fonction SOUS.TOTAL (Subtotal en VBA), ainsi dans la cellule J5 :
=SOUS.TOTAL(9;J2:J4)
En regard des entêtes de lignes, Excel ajoute des – qui permettent de masquer les lignes de données pour n’afficher que les lignes de sous-totaux (cf mode plan).
Cette fonction Excel peut faire l’objet d’une programmation en VBA. L’insertion de sous-totaux en VBA se programme avec la commande Subtotal. Notre exemple précédent se programme ainsi :
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(10, 11), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Equivalence entre le menu Données et la commande VBA :
| VBA | Fenêtre Sous-Total | Commentaires |
| GroupBy:=1 | A chaque changement de : EcritureNum | A changement (rupture) de numéro d’écriture (:=1) |
| Function:=xlSum | Utiliser la fonction : Somme | Les autres fonctions XlConsolidationFunction : cf ci-dessous |
| TotalList:=Array(10,11) | Ajouter un sous-total à : Débit, Crédit | |
| Replace:=True | Case Remplacer les sous-totaux existants cochée | |
| PageBreaks:=False | Case Saut de page entre les groupes décochée | |
| SummaryBelowData:=True | Case Synthèse sous les données cochée | Ajoute un sous-total général |
Liste des fonctions de sous-totalisation et valeurs XlConsolidationFunction :
| Nom | Valeur | Description |
|---|---|---|
| xlAverage | -4106 | Moyenne |
| xlCount | -4112 | Nombre |
| xlCountNums | -4113 | Nombre de valeurs numériques |
| xlDistinctCount | 111 | Nombre de valeurs distinctes |
| xlMax | -4136 | Maximum |
| xlMin | -4139 | Minimum |
| xlProduct | -4149 | Produit (multiplication) |
| xlStDev | -4155 | Écart-type sur la base d’un échantillon |
| xlStDevP | -4156 | Écart-type sur la base du remplissage entier |
| xlSum | -4157 | Somme |
| xlUnknown | 1000 | Aucune fonction de sous-total indiquée |
| xlVar | -4164 | Variation sur la base d’un exemple |
| xlVarP | -4165 | Variation sur la base du remplissage entier |
Les sous-totaux sont un des outils d’analyse de données à disposition de l’utilisateur d’Excel.
Approfondir le sujet : Programmer en VBA / Découvrir la série d’articles Maîtriser Excel / Analyse de données
Derniers articles parBenoît RIVIERE (voir tous)
- Nouveautés de l’interpréteur de formules de calcul (v1.1) - dimanche 3 novembre 2024
- Dématérialisation de la facturation : nouvelles mentions obligatoires - lundi 28 octobre 2024
- Interpréteur de formules de calcul en Python - dimanche 13 octobre 2024
- Les données de la facturation électronique - mercredi 9 octobre 2024
- VBA/SQL vs Power Query : deux solutions complémentaires - mercredi 2 octobre 2024