Fonction SUBTOTAL dans Excel

Introduction

La fonction SUBTOTAL d'Excel est un outil puissant pour calculer des sous-totaux dans une liste ou une base de données. Contrairement à la fonction SOMME, SUBTOTAL a la capacité d'ignorer les lignes masquées et/ou d'autres sous-totaux, offrant ainsi une flexibilité accrue dans l'analyse des données.

Syntaxe

=SUBTOTAL(fonction_num;réf1;[réf2];...)

• fonction_num: Un nombre de 1 à 11 (inclut les lignes masquées manuellement) ou de 101 à 111 (exclut les lignes masquées manuellement) spécifiant la fonction à utiliser pour le sous-total. Les options courantes incluent :
  • 1 ou 101 : MOYENNE
  • 2 ou 102 : NB
  • 3 ou 103 : NBVAL
  • 4 ou 104 : MAX
  • 5 ou 105 : MIN
  • 6 ou 106 : PRODUIT
  • 7 ou 107 : ECARTYPE
  • 8 ou 108 : ECARTYPEP
  • 9 ou 109 : SOMME
  • 10 ou 110 : VAR
  • 11 ou 111 : VARP
• réf1: La première plage ou référence à laquelle appliquer le sous-total.
• réf2, ... (facultatif): Plages ou références supplémentaires à inclure dans le sous-total.

Fonctionnement

La fonction SUBTOTAL calcule un sous-total pour une plage de cellules en utilisant la fonction spécifiée par l'argument fonction_num. La principale caractéristique de SUBTOTAL est sa capacité à ignorer les lignes masquées (si fonction_num est entre 101 et 111) et les autres sous-totaux présents dans la plage.

Cas d'utilisation

1. Rapports financiers : Calculer des sous-totaux pour différentes catégories de dépenses dans un état des revenus, en ignorant les lignes masquées pour se concentrer sur des segments spécifiques.
2. Gestion des ventes : Déterminer le total des ventes par région, en excluant les lignes masquées correspondant aux ventes annulées ou en attente.
3. Analyse RH : Calculer le nombre moyen d'employés par département, en excluant les employés temporairement absents (masqués dans la feuille de calcul).
4. Suivi de projet : Calculer le coût total des tâches achevées (celles qui ne sont pas masquées) dans un plan de projet.

Bonnes pratiques

• Utilisez les codes de fonction corrects : Assurez-vous de choisir le code de fonction approprié pour le type de calcul que vous souhaitez effectuer.
• Tenez compte des lignes masquées : Décidez si vous souhaitez inclure ou exclure les lignes masquées dans le calcul en utilisant les codes de fonction appropriés (1-11 ou 101-111).
• Évitez de nicher SUBTOTALs : Bien que SUBTOTAL puisse ignorer d'autres SUBTOTALs, il est préférable d'éviter de les imbriquer pour plus de clarté.
• Nommez vos plages: Utilisez des noms de plages pour rendre vos formules plus lisibles et plus faciles à maintenir.

Combinaisons

• SUBTOTAL et FILTRE : Utilisez SUBTOTAL avec la fonction FILTRE pour calculer des sous-totaux basés sur des critères spécifiques. Par exemple, calculer le total des ventes pour un produit spécifique.
• SUBTOTAL et SI : Utilisez SUBTOTAL avec la fonction SI pour calculer des sous-totaux conditionnels. Par exemple, calculer la somme des salaires uniquement pour les employés à temps plein.
• SUBTOTAL et INDEX/EQUIV : Utilisez SUBTOTAL avec INDEX/EQUIV pour rechercher dynamiquement la plage à sous-totaliser.