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)
- Les données de la facturation électronique - mercredi 9 octobre 2024
- VBA/SQL vs Power Query : deux solutions complémentaires - mercredi 2 octobre 2024
- L’IA dans les cabinets comptables : cas concrets - jeudi 26 septembre 2024
- EXCEL : insérer une image ou un logo dans une cellule - lundi 16 septembre 2024
- Lancer l’exécution d’un script Python à partir d’une macro VBA - lundi 9 septembre 2024