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

La principale différence est que SOUS.TOTAL peut ignorer les lignes masquées ou filtrées, tandis que SOMME additionne toutes les valeurs de la plage, qu'elles soient visibles ou non. SOUS.TOTAL est donc plus flexible pour les calculs dynamiques.

Comment faire la moyenne sans les zéros avec SOUS.TOTAL ?

Vous pouvez utiliser SOUS.TOTAL avec le numéro de fonction 101 (MOYENNE, excluant les lignes masquées) après avoir filtré les zéros de votre plage de données. Cela calculera la moyenne des valeurs visibles.

SOUS.TOTAL fonctionne-t-il avec les tableaux croisés dynamiques ?

Non, SOUS.TOTAL n'est pas directement utilisé dans les tableaux croisés dynamiques. Les tableaux croisés dynamiques ont leurs propres fonctions d'agrégation intégrées pour calculer les sous-totaux et les totaux.

Quel numéro de fonction utiliser pour compter le nombre de cellules non vides avec SOUS.TOTAL ?

Utilisez le numéro de fonction 103 (NBVAL) pour compter le nombre de cellules non vides dans une plage, en ignorant les lignes filtrées. Cela vous donnera le nombre d'éléments visibles.

Puis-je utiliser SOUS.TOTAL avec plusieurs plages de cellules ?

Oui, vous pouvez spécifier jusqu'à 254 plages de cellules dans la formule SOUS.TOTAL. Cela vous permet d'effectuer des calculs sur des plages non contiguës.

Formule SOUS.TOTAL Excel : Comment l'utiliser pour des calculs dynamiques ?

Comprendre la Formule SOUS.TOTAL Excel

La formule SOUS.TOTAL est conçue pour effectuer des opérations arithmétiques (somme, moyenne, etc.) sur une plage de cellules, en ignorant les lignes masquées ou filtrées. C'est sa principale différence avec les fonctions classiques comme SOMME, MOYENNE, etc., qui calculent sur toutes les cellules de la plage, qu'elles soient visibles ou non.

Syntaxe de la Formule

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

=SOUS.TOTAL(numéro_fonction;référence1;[référence2];...)

numéro_fonction : C'est un nombre (de 1 à 11 ou de 101 à 111) qui spécifie la fonction à utiliser pour le sous-total. Nous verrons plus en détail les différentes options disponibles.
référence1 : C'est la première plage de cellules sur laquelle effectuer le calcul.
[référence2];... (facultatif) : Ce sont des plages de cellules supplémentaires (jusqu'à 254) sur lesquelles effectuer le calcul.

Les Différents Numéros de Fonction

Le paramètre numéro_fonction est crucial car il détermine le type de calcul effectué par la formule SOUS.TOTAL. Voici un tableau récapitulatif des options disponibles :

Numéro	Fonction	Inclut les valeurs masquées manuellement ?	Inclut les valeurs 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.S	Oui	Non
8	ECARTYPE.P	Oui	Non
9	SOMME	Oui	Non
10	VAR.S	Oui	Non
11	VAR.P	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.S	Non	Non
108	ECARTYPE.P	Non	Non
109	SOMME	Non	Non
110	VAR.S	Non	Non
111	VAR.P	Non	Non

Important :

Les numéros de fonction de 1 à 11 incluent les valeurs masquées manuellement (par clic droit -> Masquer). Les numéros de fonction de 101 à 111 les excluent.
Dans tous les cas, les valeurs filtrées sont exclues du calcul.

Exemples Pratiques d'Utilisation de la Formule SOUS.TOTAL

Pour bien comprendre comment fonctionne SOUS.TOTAL, voici quelques exemples concrets.

Exemple 1 : Calculer la somme des ventes filtrées

Imaginons un tableau de ventes avec les colonnes suivantes :

Colonne A : Date
Colonne B : Produit
Colonne C : Montant des ventes

Vous souhaitez calculer la somme des ventes pour un produit spécifique, après avoir filtré le tableau sur ce produit. Voici comment faire :

Appliquez un filtre sur la colonne B (Produit) pour afficher uniquement les ventes du produit souhaité.
Dans une cellule vide, entrez la formule suivante :

=SOUS.TOTAL(9;C2:C100) (en supposant que vos données de ventes commencent à la ligne 2 et se terminent à la ligne 100)

Cette formule va calculer la somme (fonction 9) des valeurs dans la plage C2:C100, en ignorant les lignes filtrées.

Exemple 2 : Calculer la moyenne des notes sans les zéros

Vous avez une liste de notes dans une colonne, et vous voulez calculer la moyenne, mais sans prendre en compte les notes égales à zéro. Vous pouvez masquer les lignes contenant les zéros et utiliser la fonction SOUS.TOTAL avec le numéro de fonction 101 (MOYENNE, excluant les lignes masquées).

Sélectionnez les cellules contenant les notes.
Cliquez sur l'onglet "Données" puis sur "Filtrer".
Cliquez sur la flèche du filtre dans l'en-tête de la colonne des notes.
Décochez la case "0" dans la liste des valeurs.
Dans une cellule vide, entrez la formule suivante :

=SOUS.TOTAL(101;A2:A50) (en supposant que vos notes sont dans la colonne A, de la ligne 2 à la ligne 50)

Cette formule calculera la moyenne des notes visibles, en excluant les zéros que vous avez filtrés.

Exemple 3 : Compter le nombre de produits différents dans une liste filtrée

Supposons que vous ayez une liste de produits dans une colonne et que vous souhaitiez compter le nombre de produits différents affichés après avoir appliqué des filtres. Vous pouvez utiliser une combinaison de SOUS.TOTAL et NBVAL pour y parvenir.

Assurez-vous que votre liste de produits est dans une colonne (par exemple, la colonne A).
Appliquez les filtres nécessaires pour afficher les produits que vous souhaitez compter.
Dans une colonne vide (par exemple, la colonne B), entrez la valeur "1" à côté de chaque produit dans la liste filtrée. Vous pouvez le faire manuellement ou utiliser une formule si vous avez des critères spécifiques pour déterminer si une ligne doit être comptée.
Dans une cellule vide, entrez la formule suivante :

=SOUS.TOTAL(103;B2:B100) (en supposant que vous avez entré les "1" dans la colonne B, de la ligne 2 à la ligne 100)

Cette formule utilise la fonction NBVAL (fonction 103) pour compter le nombre de cellules non vides dans la plage B2:B100, en ignorant les lignes filtrées. Cela vous donnera le nombre de produits différents affichés.

Bonnes Pratiques et Astuces pour Utiliser SOUS.TOTAL

Utilisez des tableaux structurés : Convertir vos plages de données en tableaux Excel (Insertion > Tableau) permet de rendre vos formules plus robustes et plus faciles à comprendre. Les références de colonnes seront automatiquement ajustées lorsque vous ajoutez ou supprimez des lignes.
Nommez vos plages de cellules : Donner des noms significatifs à vos plages de cellules (Formules > Définir un nom) améliore la lisibilité de vos formules et facilite leur maintenance.
Vérifiez les numéros de fonction : Assurez-vous d'utiliser le bon numéro de fonction en fonction de vos besoins. Une erreur dans ce paramètre peut entraîner des résultats incorrects.
Combinez SOUS.TOTAL avec d'autres formules : N'hésitez pas à combiner SOUS.TOTAL avec d'autres fonctions Excel pour réaliser des calculs plus complexes. Par exemple, vous pouvez l'utiliser avec SI pour effectuer des calculs conditionnels.
Soyez attentif aux lignes masquées : Rappelez-vous que les numéros de fonction de 1 à 11 incluent les lignes masquées manuellement, tandis que ceux de 101 à 111 les excluent. Choisissez la bonne option en fonction de vos besoins.

Erreurs Courantes à Éviter

Oublier de spécifier le numéro de fonction : Si vous ne spécifiez pas le numéro de fonction, Excel renverra une erreur.
Utiliser le mauvais numéro de fonction : Utiliser un numéro de fonction incorrect peut entraîner des résultats inattendus.
Ne pas tenir compte des lignes masquées : Oublier que certains numéros de fonction incluent les lignes masquées peut conduire à des erreurs dans vos calculs.
Confondre SOUS.TOTAL avec SOMME : SOUS.TOTAL est plus polyvalent que SOMME car il tient compte des filtres et des lignes masquées. Utilisez SOUS.TOTAL lorsque vous avez besoin de cette fonctionnalité.

Alternatives à la Formule SOUS.TOTAL

Bien que SOUS.TOTAL soit un outil puissant, il existe d'autres méthodes pour effectuer des calculs dynamiques dans Excel.

Tableaux Croisés Dynamiques : Les tableaux croisés dynamiques sont une excellente alternative pour analyser et agréger des données. Ils offrent une grande flexibilité et permettent de créer des rapports interactifs.
Fonctions SOMME.SI, MOYENNE.SI, etc. : Ces fonctions permettent d'effectuer des calculs conditionnels sur des plages de données. Elles sont utiles lorsque vous avez des critères spécifiques à prendre en compte.
Power Query : Power Query est un outil d'extraction, de transformation et de chargement de données (ETL) intégré à Excel. Il permet d'importer des données provenant de différentes sources et de les transformer pour les analyser.

Conclusion

La formule SOUS.TOTAL est un atout précieux dans la boîte à outils de tout utilisateur d'Excel. Sa capacité à effectuer des calculs dynamiques en tenant compte des filtres et des lignes masquées en fait un outil indispensable pour l'analyse de données. En comprenant sa syntaxe, ses différentes fonctions et en suivant les bonnes pratiques, vous pouvez exploiter pleinement son potentiel et optimiser vos feuilles de calcul. N'hésitez pas à expérimenter avec les exemples fournis et à explorer les alternatives mentionnées pour trouver la solution la plus adaptée à vos besoins.