Comment utiliser la fonction SUBTOTAL dans Microsoft Excel ?

Il est facile d’obtenir des sous-totaux pour des groupes d’éléments liés dans Microsoft Excel. En fait, vous avez deux façons de le faire. Vous pouvez utiliser la fonction SOUS-TOTAL ou la fonction Sous-total, selon ce qui vous convient le mieux. Nous allons vous montrer comment procéder.

Table des matières

• Qu’est-ce qu’un sous-total dans Excel ?
• Utiliser la fonction SOUS-TOTAL d’Excel
  • Remarques sur la fonction SOUS-TOTAL
• Utilisation de la fonction de sous-total

Qu’est-ce qu’un sous-total dans Excel ?

Comme nous l’avons mentionné, la fonction SOUS-TOTAL d’Excel vous permet de regrouper des éléments identiques ou apparentés dans une liste et d’utiliser une fonction pour calculer les valeurs. Par exemple, vous pouvez l’utiliser pour additionner les ventes par mois ou les notes moyennes par étudiant. Vous pouvez également additionner les stocks par produit ou compter le nombre de factures dues la semaine prochaine.

En utilisant la fonction SOUS-TOTAL, en créant une formule ou en utilisant la fonction Sous-total, vous pouvez obtenir les calculs dont vous avez besoin en quelques étapes seulement.

Utiliser la fonction SOUS-TOTAL d’Excel

Vous pouvez créer une formule à l’aide de la fonction SOUS-TOTAL d’Excel, avec la possibilité d’inclure ou d’exclure les lignes que vous avez masquées.

La syntaxe de la formule est SUBTOTAL(numéro de fonction, référence 1, référence 2,…) où les deux premiers arguments sont obligatoires. Vous pouvez utiliser des références de cellules supplémentaires ou des plages nommées pour les arguments restants, si nécessaire.

L’argument numéro_fonction vous permet d’insérer l’une des 11 fonctions en utilisant son numéro correspondant. Les 11 premières incluent les lignes cachées, tandis que les 11 secondes les excluent. Il suffit donc d’insérer le numéro correspondant à la fonction dont vous avez besoin et à la façon dont vous souhaitez traiter les lignes cachées.

À titre d’exemple de la fonction SUBTOTAL, nous allons additionner le total des ventes dans la plage de cellules B2 à B4 en utilisant cette formule :

=SUBTOTAL(9,B2:B4)

Formule de base du sous-total dans Excel

Pour le prochain exemple, nous avons caché les lignes 4 et 5. En utilisant cette première formule, nous pouvons obtenir notre somme en utilisant le nombre 9 comme premier argument pour inclure les données cachées.

=SUBTOTAL(9,B2:B6)

Fonction SUBTOTAL incluant les lignes cachées

Maintenant, nous allons exclure ces lignes cachées de notre total, en utilisant le nombre 109 comme premier argument.

=SOUS-TOTAL(109,B2:B6)

Fonction SUBTOTAL excluant les lignes cachées

Une fois que vous avez vos sous-totaux, vous pouvez utiliser la fonction SUBTOTAL une fois de plus pour obtenir un total général en bas de page. Vous pouvez également utiliser la fonction SUM pour additionner les sous-totaux.

Pour un autre exemple, nous allons utiliser la fonction moyenne pour inclure les lignes cachées 3 et 4 avec cette formule :

=SOUS-TOTAL(1,C2:C6)

Fonction SOUS-TOTAL incluant les lignes cachées

Ensuite, nous exclurons les lignes cachées à l’aide de cette formule :

=SOUS-TOTAL(101,C2:C6)

Fonction SOUS-TOTAL excluant les lignes cachées

Remarques sur la fonction SOUS-TOTAL

Voici quelques points à garder à l’esprit lorsque vous utilisez la fonction SOUS-TOTAL dans Excel :

La fonction fonctionne pour les colonnes ou les plages verticales, et non pour les lignes ou les plages horizontales.
Si vous avez des sous-totaux imbriqués dans la zone de l'argument de référence, Excel les ignore dans le résultat du sous-total.
Lorsque vous utilisez un filtre, les données filtrées sont exclues du résultat du sous-total.

Utilisation de la fonction de sous-total

Une autre façon d’utiliser la fonction SOUS-TOTAL dans Excel est d’utiliser la fonction Sous-total. De cette façon, vous pouvez ajouter automatiquement des calculs et regrouper les éléments en même temps. Excel utilise la fonction SOUS-TOTAL pour accomplir cette tâche à votre place. Prenons quelques exemples.

En utilisant la fonction Sous-total pour nos ventes par mois, nous pouvons regrouper les données par mois et additionner chaque groupe avec un grand total en bas.

Sélectionnez toutes les lignes que vous souhaitez regrouper et sous-toter. Allez dans l’onglet Données et choisissez « Sous-total » dans le menu déroulant du schéma.

Sous-total dans le menu Outline

Lorsque la boîte de sous-total s’ouvre, choisissez comment vous voulez ajouter les sous-totaux.

À chaque changement : Sélectionnez la colonne que vous souhaitez utiliser pour le regroupement. Ici, nous avons choisi Mois pour qu'un nouveau groupe soit créé lorsque le mois change dans la feuille.
Utiliser la fonction : Sélectionnez la fonction que vous souhaitez utiliser, comme la somme, la moyenne, le minimum, le maximum ou une autre option. Dans notre exemple, nous avons sélectionné Somme.
Ajouter le sous-total à : Cochez la case correspondant à l'endroit où vous voulez que le sous-total soit calculé. Dans notre exemple, nous avons choisi Ventes.

Vous pouvez également cocher les cases en bas de page pour les éléments supplémentaires de votre choix. Cliquez sur « OK ».

Paramètres du sous-total pour la fonction Somme

Vous verrez alors vos données se mettre à jour pour regrouper et sous-toter les lignes et créer un grand total en bas. Utilisez les boutons plus, moins et numériques pour réduire ou développer les groupes afin de les visualiser plus facilement.

Sous-total pour la fonction Somme

Comme autre exemple, nous allons utiliser les notes des élèves. Nous allons utiliser les fonctions Sous-total et Groupe pour afficher la note moyenne de chaque élève. Voici la procédure à suivre.

Sélectionnez les lignes, allez dans Données, et choisissez « Subtotal » dans le menu déroulant de la ligne.

Dans la zone Subtotal, nous choisirons Student dans la liste déroulante de modification et Average dans la liste des fonctions. Nous cocherons ensuite la case pour Grade. Cliquez sur « OK ».

Paramètres de sous-total pour la fonction Moyenne

Nous avons maintenant nos lignes regroupées par étudiant avec une note moyenne pour chacun d’entre eux et une moyenne générale en bas. Là encore, vous pouvez utiliser les boutons de gauche pour réduire et développer les groupes.

Sous-total pour la fonction Moyenne

Si vous décidez de dégrouper les lignes après avoir utilisé la fonction Sous-total, les lignes redeviennent normales. Toutefois, celles contenant la fonction SOUS-TOTAL utilisée par Excel restent pour que vous puissiez continuer à les utiliser ou simplement les supprimer si vous le préférez.

Moyenne du sous-total pour les lignes non groupées

La fonction Sous-total peut devenir complexe si vous prévoyez d’utiliser de nombreux groupes. Toutefois, ces exemples de base devraient vous aider à démarrer si vous êtes intéressé par cette fonction.

Il convient de noter que cette fonction ne permet pas d’ajouter un sous-total de tableau Excel. Si vos données se trouvent dans un tableau, vous pouvez soit insérer la formule Excel pour SOUS-TOTAL comme décrit précédemment, soit convertir votre tableau en plage de cellules pour utiliser cette fonction. Si vous choisissez cette dernière option, vous perdrez la fonctionnalité de tableau.