La fonction SOUS.TOTAL peut-elle être utilisée avec des données filtrées ?

Oui, c'est même l'une de ses principales forces. La fonction SOUS.TOTAL ignore automatiquement les lignes qui sont masquées à cause d'un filtre appliqué à la plage de données.

Comment puis-je ignorer les lignes masquées manuellement avec SOUS.TOTAL ?

Utilisez les codes de fonction de 101 à 111 (par exemple, 109 pour la somme). Ces codes excluent les lignes masquées manuellement du calcul.

Quelle est la différence entre SOUS.TOTAL et SOMME ?

SOMME calcule la somme de toutes les valeurs dans une plage, sans tenir compte des lignes masquées ou filtrées. SOUS.TOTAL, en revanche, peut ignorer les lignes masquées ou filtrées, offrant ainsi plus de flexibilité.

Puis-je utiliser SOUS.TOTAL avec d'autres fonctions comme SI ?

Oui, vous pouvez combiner SOUS.TOTAL avec d'autres fonctions pour créer des calculs plus complexes. Par exemple, vous pouvez utiliser SI pour conditionner le calcul du sous-total en fonction d'une certaine condition.

SOUS.TOTAL peut-elle calculer autre chose que des sommes ?

Absolument. SOUS.TOTAL peut calculer des moyennes, des nombres, des valeurs maximales et minimales, des produits, des écarts types, des variances, et plus encore, en fonction du code de fonction que vous utilisez.

Fonction SOUS.TOTAL Excel : Maîtrisez l'agrégation dynamique de vos données

Qu'est-ce que la fonction SOUS.TOTAL dans Excel ?

La fonction SOUS.TOTAL dans Excel est une fonction d'agrégation qui permet de calculer différents types de totaux (somme, moyenne, minimum, maximum, etc.) sur une plage de cellules, en tenant compte ou non des lignes masquées et/ou filtrées. Elle se distingue des fonctions d'agrégation classiques comme SOMME, MOYENNE, MIN, ou MAX par sa capacité à s'adapter dynamiquement aux données visibles.

Syntaxe de la fonction SOUS.TOTAL

La syntaxe de la fonction SOUS.TOTAL est la suivante :

=SOUS.TOTAL(no_fonction; plage1; [plage2]; ...)

no_fonction : Un nombre (de 1 à 11 ou de 101 à 111) qui spécifie la fonction à utiliser pour le calcul du sous-total. Ce nombre détermine le type d'agrégation à effectuer (somme, moyenne, etc.) et si les lignes masquées doivent être ignorées ou non. Nous détaillerons les différentes options dans la section suivante.
plage1 : La première plage de cellules à prendre en compte dans le calcul du sous-total.
plage2; ... : (Facultatif) Des plages de cellules supplémentaires à inclure dans le calcul. Vous pouvez spécifier jusqu'à 254 plages.

Les différents codes de fonction (no_fonction)

Le premier argument de la fonction SOUS.TOTAL, no_fonction, est crucial car il détermine le type de calcul à effectuer et la façon dont les lignes masquées sont traitées. Voici un tableau récapitulatif des différents codes disponibles :

Code	Fonction	Inclut les lignes masquées manuellement ?	Inclut les lignes filtrées ?
1	MOYENNE	Oui	Non
2	NB	Oui	Non
3	NBVAL	Oui	Non
4	MAX	Oui	Non
5	MIN	Oui	Non
6	PRODUIT	Oui	Non
7	ECARTYPE	Oui	Non
8	ECARTYPEP	Oui	Non
9	SOMME	Oui	Non
10	VAR	Oui	Non
11	VARP	Oui	Non
101	MOYENNE	Non	Non
102	NB	Non	Non
103	NBVAL	Non	Non
104	MAX	Non	Non
105	MIN	Non	Non
106	PRODUIT	Non	Non
107	ECARTYPE	Non	Non
108	ECARTYPEP	Non	Non
109	SOMME	Non	Non
110	VAR	Non	Non
111	VARP	Non	Non

Important :

Les codes de 1 à 11 incluent les lignes masquées manuellement.
Les codes de 101 à 111 excluent les lignes masquées manuellement.
Tous les codes excluent les lignes filtrées.

Exemples pratiques d'utilisation de la fonction SOUS.TOTAL

Pour bien comprendre la puissance de la fonction SOUS.TOTAL, examinons quelques exemples pratiques.

Exemple 1 : Calculer la somme des ventes en ignorant les lignes masquées

Supposons que vous ayez un tableau de ventes avec les colonnes suivantes : "Date", "Produit", "Quantité", et "Prix unitaire". Vous souhaitez calculer la somme totale des ventes, mais vous avez masqué certaines lignes manuellement (par exemple, les ventes annulées).

Pour ce faire, vous pouvez utiliser la fonction SOUS.TOTAL avec le code 109 (SOMME, excluant les lignes masquées).

La formule serait la suivante :

=SOUS.TOTAL(109; D2:D100)

Où D2:D100 est la plage de cellules contenant les montants des ventes.

Explication :

Cette formule calculera la somme de toutes les valeurs dans la plage D2:D100, à l'exception des valeurs situées sur les lignes masquées manuellement. Les lignes filtrées seront également exclues du calcul.

Exemple 2 : Calculer la moyenne des notes en ignorant les notes nulles

Imaginez que vous ayez une liste de notes d'étudiants et que certaines notes soient nulles (par exemple, si l'étudiant était absent à l'examen). Vous souhaitez calculer la moyenne des notes, mais vous voulez ignorer les notes nulles pour obtenir une moyenne plus représentative.

Bien que SOUS.TOTAL n'ait pas de fonction intégrée pour ignorer les valeurs nulles directement, vous pouvez combiner la fonction avec un filtre pour masquer les lignes contenant des notes nulles. Ensuite, vous pouvez utiliser SOUS.TOTAL avec le code 101 (MOYENNE, excluant les lignes masquées) pour calculer la moyenne des notes restantes.

Filtrer les données : Sélectionnez votre plage de données (par exemple, A1:B10, où la colonne B contient les notes). Allez dans l'onglet "Données" et cliquez sur "Filtrer". Un petit bouton de filtre apparaîtra dans l'en-tête de chaque colonne. Cliquez sur le bouton de filtre de la colonne "Notes" et décochez la case correspondant à la valeur 0.
Calculer la moyenne avec SOUS.TOTAL : Utilisez la formule suivante dans une cellule vide :

=SOUS.TOTAL(101; B2:B10)

Explication :

Cette formule calculera la moyenne des notes visibles dans la plage B2:B10, c'est-à-dire les notes qui n'ont pas été filtrées (les notes non nulles). La fonction SOUS.TOTAL ignorera automatiquement les lignes masquées par le filtre.

Exemple 3 : Créer un tableau de sous-totaux dynamiques

L'une des applications les plus intéressantes de la fonction SOUS.TOTAL est la création de tableaux de sous-totaux dynamiques. Cela permet de calculer des sous-totaux par catégorie, par région, ou par tout autre critère, et de les mettre à jour automatiquement lorsque les données sont filtrées ou modifiées.

Pour ce faire, vous pouvez utiliser la fonction SOUS.TOTAL en combinaison avec la fonction SOUS.TOTAL.SI (si vous utilisez une version récente d'Excel) ou avec des fonctions de recherche et de référence (comme INDEX et EQUIV) pour créer une formule qui calcule le sous-total approprié en fonction de la catégorie sélectionnée.

(Malheureusement, la fonction SOUS.TOTAL.SI n'existe pas. On utilisera donc des tableaux croisés dynamiques ou des formules plus complexes combinant SOMME.SI.ENS et SOUS.TOTAL pour obtenir un résultat similaire. Cet exemple est donc théorique et nécessite une adaptation).

Alternatives à la fonction SOUS.TOTAL

Bien que la fonction SOUS.TOTAL soit très utile, il existe d'autres méthodes pour obtenir des résultats similaires, en particulier :

Tableaux croisés dynamiques : Les tableaux croisés dynamiques sont un outil puissant pour l'analyse et la synthèse de données. Ils permettent de créer des tableaux de sous-totaux, des moyennes, et d'autres agrégations de manière interactive et flexible. Ils sont particulièrement adaptés aux analyses complexes et aux explorations de données.
Fonctions SOMME.SI, MOYENNE.SI, etc. : Ces fonctions permettent de calculer des sommes, des moyennes, et d'autres agrégations en fonction de critères spécifiques. Elles sont utiles lorsque vous souhaitez effectuer des calculs conditionnels basés sur des valeurs ou des plages de cellules.
Filtres automatiques : Les filtres automatiques permettent de masquer temporairement les lignes qui ne répondent pas à certains critères. Vous pouvez ensuite utiliser les fonctions d'agrégation classiques (SOMME, MOYENNE, etc.) pour calculer les totaux sur les lignes visibles. Cependant, cette méthode n'est pas aussi dynamique que la fonction SOUS.TOTAL car les résultats ne se mettent pas à jour automatiquement lorsque les filtres sont modifiés.

Erreurs courantes à éviter avec la fonction SOUS.TOTAL

Voici quelques erreurs courantes à éviter lors de l'utilisation de la fonction SOUS.TOTAL :

Utiliser le mauvais code de fonction : Il est crucial de choisir le code de fonction approprié en fonction du type de calcul que vous souhaitez effectuer et de la façon dont vous souhaitez traiter les lignes masquées. Consultez le tableau des codes de fonction pour vous assurer de faire le bon choix.
Inclure des lignes de sous-totaux dans la plage : Si vous avez déjà des lignes de sous-totaux calculées avec la fonction SOUS.TOTAL dans votre plage de données, assurez-vous de ne pas les inclure dans la plage de la fonction SOUS.TOTAL principale. Sinon, vous risquez de compter les sous-totaux deux fois.
Oublier d'actualiser les calculs après avoir modifié les filtres : Si vous utilisez des filtres en combinaison avec la fonction SOUS.TOTAL, assurez-vous que les calculs sont actualisés après avoir modifié les filtres. Dans certains cas, Excel peut ne pas actualiser automatiquement les calculs et vous devrez peut-être appuyer sur la touche F9 pour forcer l'actualisation.

Conseils et astuces pour optimiser l'utilisation de SOUS.TOTAL

Utilisez des noms de plage : Au lieu d'utiliser des références de cellules directes (comme A1:B10), vous pouvez attribuer des noms aux plages de cellules. Cela rend vos formules plus lisibles et plus faciles à maintenir. Par exemple, vous pouvez nommer la plage contenant les montants des ventes "Ventes" et utiliser la formule =SOUS.TOTAL(109; Ventes).
Combinez SOUS.TOTAL avec d'autres fonctions : La fonction SOUS.TOTAL peut être combinée avec d'autres fonctions Excel pour effectuer des calculs plus complexes. Par exemple, vous pouvez utiliser la fonction SI pour conditionner le calcul du sous-total en fonction d'une certaine condition.
Utilisez les raccourcis clavier : Pour masquer ou afficher rapidement des lignes, vous pouvez utiliser les raccourcis clavier suivants :
- Masquer une ligne : Ctrl + 9
- Afficher une ligne masquée : Ctrl + Maj + 9

Conclusion

La fonction SOUS.TOTAL est un outil précieux pour l'analyse et la gestion de données dans Excel. Sa capacité à s'adapter dynamiquement aux lignes masquées et filtrées en fait un allié de choix pour calculer des totaux partiels, des moyennes, et d'autres agrégations de manière flexible et efficace. En maîtrisant les différents codes de fonction et en comprenant les erreurs à éviter, vous pourrez exploiter pleinement le potentiel de SOUS.TOTAL et améliorer significativement votre productivité dans Excel.