EXCEL : Insérer des sous-totaux dans une liste de données

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. 

Sous-totaux

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 :

Sous-totaux - Données source

Ensuite, actionner le Menu Données puis Sous-total :

Menu Sous-totaux

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) :

Sous-totaux - Options

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

Share Button
The following two tabs change content below.
Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog. Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.