Une feuille de calcul contenant des milliers de lignes peut rapidement devenir illisible sans résumés structurels. Pour une organisation efficace des données dans Excel, la fonctionnalité Sous-total s'impose comme l'un des outils les plus puissants et les plus percutants disponibles.
Que vous soyez comptable en train d'équilibrer des grands livres, responsable commercial analysant les performances régionales ou marketeur suivant les indicateurs de campagne, apprendre comment ajouter des sous-totaux dans Excel peut vous faire gagner des heures de travail manuel.
Dans ce guide, nous explorerons quatre méthodes distinctes pour créer des sous-totaux dans Excel :
- L'outil Sous-total intégré (La méthode la plus rapide).
- La fonction SOUS.TOTAL d'Excel (Pour des calculs dynamiques).
- Le Tableau croisé dynamique (L'alternative moderne).
- L'automatisation avec Python (Pour le traitement par lots).
Que sont les sous-totaux dans Excel ?
Les sous-totaux sont des calculs récapitulatifs insérés entre des groupes de données connexes dans une feuille de calcul. Ils vous permettent de :
- Diviser de grands ensembles de données non organisés en catégories logiques et lisibles
- Visualiser en un coup d'œil les sommes, moyennes, nombres et autres agrégations
- Développer ou réduire des groupes pour vous concentrer sur des sections spécifiques
- Conserver à la fois les données détaillées et les données récapitulatives sur une seule feuille
Méthode 1 : Utiliser l'outil Sous-total intégré
La fonctionnalité Sous-total dans Excel est le moyen le plus rapide d'ajouter des sous-totaux pour la plupart des besoins de reporting de base. Elle insère automatiquement des lignes de résumé et crée une vue hiérarchique, vous permettant de réduire et de développer vos données pour différents niveaux de détail.
Étape 1 : Trier vos données
Étant donné que les sous-totaux regroupent les données en fonction des changements de valeur dans une colonne spécifique, vous devez d'abord trier cette colonne.
- Cliquez sur la colonne par laquelle vous souhaitez effectuer le regroupement (par exemple, "Région").
- Allez dans l'onglet Données et cliquez sur Trier de A à Z ou Trier de Z à A.
Étape 2 : Ouvrir l'outil Sous-total
- Sélectionnez n'importe quelle cellule de votre ensemble de données
- Accédez à nouveau à l'onglet Données et cliquez sur Sous-total (dans le groupe Plan)
Étape 3 : Configurer les paramètres de sous-total
Une boîte de dialogue Sous-total apparaîtra avec trois options cruciales :
- À chaque changement de : Sélectionnez la colonne par laquelle vous avez trié (par exemple, "Région")
- Utiliser la fonction : Choisissez votre calcul — Somme, Nombre, Moyenne, Max, Min, etc.
- Ajouter un sous-total à : Cochez la case de la ou des colonnes numériques que vous souhaitez résumer
Cliquez sur OK. Excel insérera immédiatement une ligne "Total" après chaque groupe, ainsi qu'une ligne "Total général" au bas de l'ensemble de données.
Conseil d'expert : Travailler avec les niveaux de plan
Regardez sur le bord gauche de la feuille de calcul, vous verrez des boutons de plan (1, 2, 3). Vous pouvez :
- Cliquer sur 1 pour afficher uniquement le total général
- Cliquer sur 2 pour afficher les sous-totaux de groupe et le total général
- Cliquer sur 3 pour afficher toutes les lignes de détail ainsi que les sous-totaux
- Utiliser les boutons + / - pour développer ou réduire des groupes individuels
⚠️ Remarque : Le bouton Sous-total de l'onglet Données ne fonctionne pas dans les tableaux Excel formatés. Utilisez la fonction SOUS.TOTAL dans une ligne de total (Création de tableau → Ligne de total) ou convertissez d'abord le tableau en plage.
Méthode 2 : Utiliser la fonction SOUS.TOTAL d'Excel
Alors que l'outil intégré est statique, la fonction SOUS.TOTAL est une formule dynamique qui se recalcule en temps réel à mesure que vos données changent. Elle est largement utilisée par les analystes financiers qui créent des modèles réutilisés mois après mois.
La syntaxe :
=SOUS.TOTAL(no_fonction, réf1, [réf2], ...)
-
no_fonction (Requis) : Un code numérique qui indique à Excel exactement quelle opération mathématique effectuer.
- Codes de base (incluent les lignes masquées manuellement) : 1 = MOYENNE, 2 = NB, 4 = MAX, 5 = MIN, 9 = SOMME (le plus couramment utilisé)
- Ajoutez 100 au code de base pour ignorer les lignes masquées manuellement (par exemple, 109 = SOMME qui exclut les lignes masquées manuellement)
- réf1 (Requis) : La plage de cellules pour le sous-total
- [réf2] (Optionnel) : Plages supplémentaires que vous pouvez ajouter si vos données ne sont pas contiguës.
Exemple de formule de sous-total
Si vous avez des données dans les cellules E2:E10 :
- Pour sommer des valeurs dans une liste filtrée, utilisez =SOUS.TOTAL(9, E2:E10).
- Pour compter des entrées, utilisez =SOUS.TOTAL(2, E2:E10).
Pourquoi utiliser SOUS.TOTAL au lieu de SOMME ?
L'avantage clé est que SOUS.TOTAL ignore automatiquement les autres résultats de SOUS.TOTAL au sein de la plage, évitant ainsi le double comptage. Elle respecte également les filtres Excel : lorsque vous filtrez des données, seules les lignes visibles sont incluses dans le calcul.
Méthode 3 : L'alternative moderne - Tableaux croisés dynamiques
Les tableaux croisés dynamiques sont essentiellement des moteurs de synthèse dynamiques qui créent un tout nouveau tableau (distinct de vos données brutes) pour afficher des résumés. Ils sont particulièrement utiles lorsque vous avez besoin de plus de flexibilité que ce que l'outil Sous-total offre.
Comment créer un tableau croisé dynamique dans Excel :
- Sélectionnez votre ensemble de données complet.
- Allez dans l'onglet Insertion et cliquez sur Tableau croisé dynamique.
- Faites glisser votre catégorie (par exemple, "Région") dans le champ Lignes.
- Faites glisser votre valeur numérique (par exemple, "Revenu") dans le champ Valeurs.
Excel utilise par défaut "Somme" pour les champs numériques et "Nombre" pour les champs texte.
Personnaliser le comportement des sous-totaux
Par défaut, les tableaux croisés dynamiques affichent un sous-total pour chaque champ de ligne de niveau supérieur. Cependant, vous avez un contrôle granulaire :
- Faites un clic droit sur l'étiquette de ligne (par exemple, "Région").
- Sélectionnez Paramètres de champ.
- Sous "Sous-totaux", vous pouvez choisir :
- Automatique : Affiche les sous-totaux par défaut.
- Personnalisé : Permet de sélectionner des fonctions spécifiques (par exemple, afficher à la fois la Somme et la Moyenne pour le même champ).
- Aucun : Supprime complètement les sous-totaux si vous ne voulez que le total général.
✅ Avantage : Contrairement à l'outil Sous-total traditionnel, les tableaux croisés dynamiques ne nécessitent pas de trier les données dans Excel au préalable. Ils se rafraîchissent également automatiquement lorsque vos données sources sont mises à jour, et vous pouvez facilement ajouter plusieurs couches de regroupement et types de calcul sans réécrire de formules.
Méthode 4 : Ajouter des sous-totaux par programmation avec Python
Pour les utilisateurs qui ont besoin d'automatiser la génération de sous-totaux, par exemple dans le traitement par lots ou au sein d'un pipeline de données, la bibliothèque Free Spire.XLS for Python offre un moyen puissant d'insérer des sous-totaux par programmation.
Cette méthode reproduit entièrement le comportement de l'outil Sous-total intégré (Méthode 1) via du code, vous permettant de traiter des centaines de fichiers sans ouvrir Excel manuellement.
Prérequis
Tout d'abord, assurez-vous que Python est installé (3.7 ou supérieur). Ouvrez votre terminal ou invite de commande et exécutez :
pip install Spire.Xls.Free
Code Python pour insérer des sous-totaux dans Excel
Le script suivant charge un classeur Excel, applique des sous-totaux à une plage de données spécifiée et enregistre le résultat sous un nouveau fichier :
from spire.xls import *
from spire.xls.common import *
# Créer un objet Workbook et charger le fichier Excel
workbook = Workbook()
workbook.LoadFromFile("Regional.xlsx")
sheet = workbook.Worksheets[0]
# Définir la plage de données pour le calcul du sous-total
# La plage couvre de B2 à E10 (lignes de données uniquement, hors en-tête)
data_range = sheet.Range["B2:E10"]
# Appeler la méthode Subtotal avec tous les paramètres
sheet.Subtotal(data_range, 0, [3], SubtotalTypes.Sum, True, False, True)
# Enregistrer le fichier résultat
workbook.SaveToFile("AddSubtotals.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Résultat : Dans la plage B2:E10, nous appliquons un sous-total de type Somme regroupé par la première colonne ("Région") sur la quatrième colonne ("Unités vendues").
Détail des paramètres
La méthode Subtotal() accepte sept paramètres qui vous donnent un contrôle total sur le résultat :
| Paramètre | Type | Description |
|---|---|---|
| range | IXLSRange | La plage de cellules contenant les données sources pour le sous-total. |
| groupByIndex | int | Index de colonne basé sur 0 au sein de la plage sélectionnée utilisé pour regrouper les données. |
| totalFields | int[] | Une liste d'index de colonnes basés sur 0 (au sein de la plage) indiquant pour quels champs calculer les sous-totaux. |
| subtotalType | SubtotalTypes | Type de calcul : Somme, Moyenne, Nombre, Max, Min, etc. |
| replace | bool | Indique s'il faut remplacer les sous-totaux existants dans la plage. |
| addPageBreak | bool | Indique s'il faut insérer un saut de page après chaque groupe (utile pour l'impression). |
| addsummaryBelowData | bool | Indique s'il faut placer la ligne de sous-total sous les détails du groupe. |
⚠️ Remarque : Tout comme dans l'interface Excel, vos données sources doivent être triées par la colonne de regroupement avant d'appeler Subtotal(). Des données non triées généreront une ligne de sous-total à chaque changement de valeur, créant de nombreux petits groupes involontaires.
À LIRE ÉGALEMENT : Grouper ou dégrouper des lignes et des colonnes dans Excel avec Python
Foire aux questions (FAQ)
Q : Puis-je ajouter plusieurs sous-totaux à la même colonne ?
Oui, vous pouvez appliquer plus d'un calcul (par exemple, à la fois la Somme et la Moyenne) à la même colonne :
- Insérez le premier sous-total comme d'habitude
- Ouvrez à nouveau la boîte de dialogue Sous-total
- Choisissez une fonction différente
- Décochez "Remplacer les sous-totaux actuels"
- Cliquez sur OK
En Python, vous appelleriez la méthode Subtotal() deux fois avec replace=False lors du deuxième appel.
Q : Quel est le raccourci pour les sous-totaux dans Excel ?
Il n'y a pas de raccourci clavier direct, mais vous pouvez utiliser Alt + A + B pour ouvrir la boîte de dialogue Sous-total.
Q : Comment supprimer les sous-totaux ?
Si vous faites une erreur ou souhaitez revenir aux données brutes, retournez dans Données > Sous-total. Dans la boîte de dialogue, cliquez sur le bouton Supprimer tout en bas à gauche. Cela supprime instantanément toutes les lignes de sous-total et les symboles de plan, restaurant vos données dans leur état trié d'origine.
Q : Puis-je copier uniquement les sous-totaux ?
Oui. Si vous cliquez sur le bouton de plan 2 pour afficher uniquement les totaux, vous pouvez appuyer sur Alt + ; (point-virgule) pour sélectionner uniquement les cellules visibles, puis copier/coller vers une nouvelle feuille.
Réflexions finales
Apprendre comment ajouter des sous-totaux dans Excel est une compétence incontournable pour l'analyse de données, et la bonne méthode dépend entièrement de votre flux de travail. Pour une analyse rapide et ponctuelle, l'outil Sous-total intégré fonctionne le mieux. Alors que la fonction SOUS.TOTAL prend en charge des calculs dynamiques et conviviaux pour les filtres, et que les tableaux croisés dynamiques excellent dans les rapports flexibles et multidimensionnels,
Lorsque vous avez besoin d'automatiser des tâches répétitives et de traiter des fichiers à grande échelle, Python avec Free Spire.XLS est une option idéale. Maîtriser ces quatre méthodes vous rendra nettement plus rapide et plus confiant lorsque vous travaillerez avec de grands ensembles de données.
Voir aussi
- Compter les lignes dans les fichiers Excel — Avec ou sans les ouvrir
- Comment supprimer des colonnes en double dans Excel : 4 meilleures méthodes
- Comment insérer des formules dans Excel : Six méthodes simples
- Python : Créer ou utiliser des tableaux croisés dynamiques dans Excel
- C#/VB.NET : Grouper ou dégrouper des lignes et des colonnes dans Excel