Formule SUBTOTAL() dans Excel : Guide de calcul complet

Les utilisateurs d'Excel ont souvent besoin de méthodes flexibles pour résumer des données qui répondent à des filtres ou à des regroupements. Les fonctions traditionnelles telles que SUM(), AVERAGE() ou COUNT() calculent les résultats sur la base de toutes les cellules d'une plage, même si certaines lignes sont masquées ou filtrées. La fonction SUBTOTAL() offre une alternative dynamique qui ajuste automatiquement ses calculs en fonction de ce qui est actuellement visible dans votre feuille de calcul.

Si vous êtes novice en Excel et que vous souhaitez acquérir des bases solides avant d'explorer des fonctions avancées telles que SUBTOTAL(), notrecours d' introduction à Excel couvre les compétences essentielles, notamment la navigation dans la feuille de calcul, les formules de base et les techniques de mise en forme des données, qui vous prépareront à un travail plus sophistiqué sur Excel.

Qu'est-ce que la fonction SUBTOTAL() dans Excel ?

La fonction SUBTOTAL() calcule des valeurs agrégées telles que la somme, la moyenne, le nombre et d'autres opérations statistiques sur un ensemble de données. Ce qui la différencie des fonctions Excel standard, c'est son fonctionnement dynamique, qui modifie le résultat en fonction de ce qui est visible ou filtré. La fonction peut ignorer à la fois les lignes masquées manuellement et les lignes masquées par des filtres, selon le numéro de fonction que vous spécifiez.

Lorsque vous appliquez un filtre à vos données ou que vous masquez manuellement certaines lignes, SUBTOTAL() ajuste automatiquement ses calculs pour n'inclure que les cellules visibles. Ce comportement en fait un excellent choix pour créer des lignes de résumé dans des ensembles de données filtrées, construire des tableaux de bord interactifs ou générer des rapports qui doivent être mis à jour en fonction des sélections de l'utilisateur.

Cette fonction prend en charge les mêmes opérations statistiques que les fonctions standard d'Excel, notamment la somme, la moyenne, le décompte, le maximum, le minimum et plusieurs autres, mais elle les regroupe en une seule fonction contextuelle qui s'adapte à l'état actuel de vos données.

Syntaxe et opérations supportées

La fonction SUBTOTAL() suit une structure syntaxique simple :

=SUBTOTAL(function_num, ref1, [ref2], ...)

Le premier paramètre, function_num, détermine à la fois le type de calcul et la manière dont la fonction traite les lignes cachées. Les autres paramètres (ref1, ref2, etc.) sont les plages de cellules que vous souhaitez inclure dans le calcul. Vous pouvez spécifier jusqu'à 254 plages différentes.

Les numéros de fonction se répartissent en deux catégories distinctes selon la manière dont ils traitent les lignes cachées manuellement :

Les numéros de fonction 1 à 11 incluent dans leurs calculs les cellules des lignes masquées manuellement, tandis que les numéros de fonction 101 à 111 les excluent. Cependant, les deux gammes ignorent toujours les lignes cachées par les filtres.

Par exemple, SUBTOTAL(9, A2:A10) calcule la somme de A2:A10 en incluant toutes les lignes masquées manuellement, tandis que SUBTOTAL(109, A2:A10) exclut les lignes masquées manuellement du calcul de la somme. Dans les deux cas, les lignes filtrées sont automatiquement exclues du résultat.

Exemple de fonction SUBTOTAL() en action

Voyons comment fonctionne SUBTOTAL() à l'aide d'un exemple pratique utilisant les données de vente d'un magasin d'électronique et de meubles.

Ensemble de données non filtrées. Image par l'auteur. 

Notre échantillon de données contient sept produits répartis dans deux catégories : quatre produits électroniques (ordinateur portable, téléphone, tablette, écran) et trois produits d'ameublement (bureau, chaise, lampe). En l'absence de filtres, la formule SUBTOTAL(109,C2:C8) et la formule régulière SUM(C2:C8) donnent toutes deux le même résultat : 3525 (le total de toutes les ventes).

Ensemble de données filtré. Image par l'auteur. 

Nous avons maintenant appliqué un filtre pour n'afficher que les produits électroniques, en masquant les trois rangées de meubles. Remarquez la différence essentielle dans les résultats de nos calculs :

• Formule SOUS-TOTAL montre 3000 - ajustement automatique pour additionner uniquement les lignes visibles de l'électronique
• La formule SUM normale affiche toujours 3525 - en continuant d'inclure les éléments cachés du mobilier

Cette comparaison côte à côte démontre l'avantage principal de SUBTOTAL(): il répond de manière dynamique aux changements de filtres et de visibilité des données, alors que les fonctions traditionnelles telles que SUM() restent statiques. Lorsque vous filtrez des données ou masquez des lignes, SUBTOTAL() se recalcule instantanément pour ne refléter que ce qui est actuellement visible, ce qui est essentiel pour les tableaux de bord réactifs et les rapports interactifs.

Comment SUBTOTAL() fonctionne avec des données cachées et filtrées

La fonction SUBTOTAL() traite les différents types de données cachées de manière spécifique. Il est donc essentiel de comprendre ces comportements lors de l'élaboration de vos formules.

SUBTOTAL() avec des lignes filtrées

Lorsque vous appliquez un filtre à votre ensemble de données, SUBTOTAL() exclut toujours les lignes filtrées de ses calculs, quel que soit le numéro de fonction utilisé. Ce comportement est automatique et cohérent pour tous les numéros de fonction (de 1 à 11 et de 101 à 111).

Par exemple, si vous disposez de données sur les ventes de janvier à décembre et que vous appliquez un filtre pour n'afficher que les mois du premier trimestre (janvier, février, mars), SUBTOTAL(9, B2:B13) ne calculera la somme que pour ces trois mois visibles, en ignorant les données du deuxième trimestre et du quatrième trimestre qui ont été filtrées.

SUBTOTAL() avec des lignes cachées manuellement

Les lignes masquées manuellement sont traitées différemment selon le numéro de fonction que vous avez choisi. Lorsque vous cliquez avec le bouton droit de la souris sur les numéros de ligne et que vous sélectionnez "Masquer", vous masquez manuellement ces lignes.

L'utilisation des numéros de fonction 1 à 11 permet d'inclure ces lignes masquées manuellement dans les calculs. L'utilisation des numéros de fonction 101-111 les exclut. Vous pouvez ainsi déterminer si les données cachées doivent avoir un impact sur vos résultats.

SUBTOTAL() avec plages horizontales et verticales

La fonction SUBTOTAL() fonctionne mieux avec des plages de données verticales (colonnes de données). Bien qu'il puisse gérer des plages horizontales, son comportement de masquage est optimisé pour les opérations basées sur les lignes, car les fonctions de filtrage et de masquage des lignes d'Excel fonctionnent verticalement.

Lorsque vous imbriquez des formules SUBTOTAL() dans le même intervalle, la fonction saute automatiquement les autres résultats SUBTOTAL() afin d'éviter un double comptage. Cette fonction est particulièrement utile lorsque vous créez des résumés hiérarchiques ou des sous-totaux au sein de groupes, car elle permet de s'assurer que vos totaux reflètent avec précision chaque groupe distinct, sans duplication.

Utilisations avancées de la fonction SUBTOTAL()

Créer un résumé dynamique dans une liste déroulante avec SUBTOTAL()

Vous pouvez combiner SUBTOTAL() avec la fonction IF() pour créer des résumés interactifs qui changent en fonction de la sélection de l'utilisateur. Cette approche utilise une liste déroulante pour permettre aux utilisateurs de passer d'un type de calcul à l'autre.

Pour créer un résumé déroulant dynamique, commencez par créer une liste déroulante dans la cellule D1 avec des options telles que "Sum", "Average", "Count" et "Max".

Ensuite, utilisez la formule suivante dans une autre cellule (par exemple, D2) pour ajuster dynamiquement le calcul en fonction de la sélection de l'utilisateur :

=IF(D1="Sum",SUBTOTAL(109,A2:A10),
  IF(D1="Average",SUBTOTAL(101,A2:A10),
  IF(D1="Count",SUBTOTAL(103,A2:A10),
  IF(D1="Max",SUBTOTAL(104,A2:A10),"Select Option"))))

Cette formule évalue la sélection déroulante et applique la fonction SUBTOTAL() correspondante. Les utilisateurs peuvent passer d'un type de calcul à l'autre sans avoir à modifier les formules, ce qui rend les rapports plus interactifs et plus conviviaux.

SUBTOTAL() dans les tableaux et les schémas Excel

Les tableaux Excel utilisent automatiquement SUBTOTAL() dans la fonction Total des lignes. Lorsque vous convertissez votre plage de données en tableau (Ctrl+T) et que vous activez la ligne de total, Excel insère par défaut SUBTOTAL(109, …) pour les calculs de somme. Ce Total Row se met à jour dynamiquement lorsque des lignes sont filtrées, ajustant instantanément les résultats pour qu'ils ne reflètent que les données visibles.

Vous pouvez modifier le type de calcul en cliquant sur la flèche déroulante de n'importe quelle cellule de la ligne Total et en sélectionnant différentes options telles que Compte, Moyenne, Max ou Min. Excel met automatiquement à jour le numéro de fonction tout en conservant la structure SUBTOTAL().

La fonction de contour (Données > Sous-total) s'appuie également sur les formules SUBTOTAL(). Lorsque vous regroupez des données et insérez des sous-totaux, Excel place automatiquement les fonctions SUBTOTAL() à chaque rupture de groupe. Cela permet de créer des résumés hiérarchiques qui réagissent correctement lorsque vous développez ou réduisez les niveaux de contour, puisque la fonction ignore les résultats de sous-totaux imbriqués.

Pièges et limites courants

Comprendre les erreurs courantes et les limites de SUBTOTAL() vous permet d'éviter les erreurs de calcul et de choisir la bonne approche pour vos besoins d'analyse de données.

• Erreur #VALUE ! avec des numéros de fonction non valides : L'erreur la plus fréquente est l'utilisation d'un numéro de fonction non valide. SUBTOTAL() n'accepte que les numéros de fonction 1-11 et 101-111. L'utilisation de nombres tels que 12, 50 ou 200 renvoie une erreur #VALUE ! Vérifiez toujours que votre numéro de fonction correspond à une opération valide parmi les gammes prises en charge.

• Comportement des colonnes cachées par rapport aux lignes cachées : Alors que SUBTOTAL() réagit aux lignes cachées en fonction du numéro de fonction que vous avez choisi, il ne se comporte pas de la même manière avec les colonnes cachées. La fonction inclut les données des colonnes masquées dans tous les calculs, que vous utilisiez les numéros de fonction 1-11 ou 101-111. Cette limitation signifie que vous avez besoin d'autres approches lorsque vous travaillez avec des ensembles de données où la visibilité des colonnes affecte votre analyse.

• Les références 3D ne sont pas prises en charge : Contrairement à de nombreuses fonctions Excel, SUBTOTAL() ne peut pas référencer des plages sur plusieurs feuilles de calcul. Les formules telles que SUBTOTAL(109, Sheet1:Sheet3!A1:A10) renvoient des erreurs. Pour contourner ce problème, vous pouvez d'abord calculer des sommes ou d'autres agrégats séparément sur chaque feuille à l'aide de fonctions standard telles que SUM(), puis consolider ces résultats intermédiaires avec un seul SOUS-TOTAL sur une feuille récapitulative.

• Les valeurs d'erreur restent dans les calculs : SUBTOTAL() n'ignore pas les cellules contenant des valeurs d'erreur telles que #N/A, #DIV/0 ! ou #VALUE ! Ces erreurs se propagent dans vos calculs de sous-totaux et peuvent invalider l'ensemble des résultats. Nettoyez vos données des erreurs avant d'appliquer SUBTOTAL(), ou envisagez d'utiliser la fonction AGGREGATE() qui peut ignorer les valeurs d'erreur.

• L'orientation verticale des données est la plus efficace : Bien que SUBTOTAL() puisse gérer des plages horizontales, sa conception est optimisée pour les structures de données verticales. Les fonctions de filtrage, de tri et de masquage d'Excel fonctionnent ligne par ligne, ce qui rend les dispositions verticales plus compatibles avec le comportement prévu de la fonction.

Subtotal() vs. AGGREGATE()

La fonction AGGREGATE() est une alternative améliorée à SUBTOTAL(), avec des capacités supplémentaires de traitement des erreurs et davantage d'opérations statistiques. Alors que SUBTOTAL() propose 11 opérations de base, AGGREGATE() offre 19 fonctions différentes, dont le calcul des centiles, des quartiles et de la médiane.

Le principal avantage de AGGREGATE() réside dans ses capacités de traitement des erreurs. Contrairement à SUBTOTAL(), qui inclut les valeurs d'erreur dans les calculs, AGGREGATE() peut automatiquement ignorer les cellules contenant des erreurs comme #N/A, #DIV/0! ou #VALUE!. Il est donc particulièrement utile pour les ensembles de données incomplètes ou problématiques.

AGGREGATE() offre également un contrôle plus granulaire sur les éléments à ignorer. Vous pouvez le configurer pour qu'il ignore les lignes cachées, les sous-totaux imbriqués, les valeurs d'erreur ou toute combinaison de ces éléments à l'aide de son paramètre options.

Conseils de pro pour l'utilisation de SUBTOTAL()

Triez toujours en premier

Lorsque vous utilisez la fonction intégrée Données > Sous-total d'Excel, triez d'abord vos données en fonction de la colonne de regroupement. Cela permet de s'assurer que tous les enregistrements liés apparaissent ensemble, créant ainsi des ruptures de groupe nettes pour vos calculs de sous-totaux. Les données non triées produisent des sous-totaux dispersés qui ne fournissent pas de résumés significatifs.

Utilisez des étiquettes dans la première rangée de colonnes

Placez des en-têtes descriptifs dans la première ligne de votre plage de données avant d'appliquer des sous-totaux. Excel utilise ces étiquettes pour créer des descriptions significatives des sous-totaux et faciliter l'interprétation de vos rapports. Des en-têtes de colonne clairs facilitent également la sélection des plages pour les formules manuelles SUBTOTAL().

Soyez attentif à l'emplacement du résumé (au-dessus ou au-dessous)

L'outil de sous-total d'Excel place par défaut les lignes de résumé sous chaque groupe, mais vous pouvez choisir de les placer au-dessus. Tenez compte de l'utilisation prévue de votre rapport lorsque vous faites ce choix. Les lignes de résumé situées au-dessus des groupes conviennent bien aux tableaux de bord, tandis que les lignes de résumé situées au-dessous des groupes s'alignent sur les formats comptables traditionnels.

Masquer ou filtrer des lignes pour un contrôle précis

Comprendre la différence entre le filtrage et le masquage manuel des lignes pour obtenir les résultats escomptés. Utilisez des filtres lorsque vous souhaitez que SUBTOTAL() ignore temporairement certaines données. Utilisez le masquage manuel (clic droit > Masquer) si vous souhaitez des exclusions plus permanentes, puis choisissez les numéros de fonction 101-111 pour respecter l'état masqué.

Cette approche vous permet d'exercer un contrôle à plusieurs niveaux : filtre pour les vues de données temporaires, masquage manuel pour les exclusions semi-permanentes et sélection de numéros de fonction appropriés pour honorer vos intentions de masquage.

Conclusion

Apprendre à utiliser efficacement SUBTOTAL() transforme votre approche de l'analyse des données dans Excel. Au lieu de créer des calculs statiques qui s'interrompent lorsque les données changent, vous pouvez élaborer des formules résilientes qui s'adaptent automatiquement aux actions de l'utilisateur et aux modifications des données. Cette compétence devient particulièrement précieuse lorsque vous travaillez avec de grands ensembles de données ou que vous créez des rapports que plusieurs utilisateurs filtreront et manipuleront au fil du temps.

Pour développer une expertise complète d'Excel au-delà des fonctions individuelles telles que SUBTOTAL(), notrecursus de compétences Excel Fundamentals propose un parcours de formation structuré de 16 heures qui vous emmène de la préparation des données aux techniques d'analyse et de visualisation avancées. Pour les lecteurs prêts à explorer immédiatement les applications analytiques, notre cours Analyse de données dans Excel enseigne la maîtrise des tableaux croisés dynamiques et des fonctions logiques avancées pour une meilleure compréhension.