Comment utiliser SOUS.TOTAL sur Excel pour des calculs dynamiques ?

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

La fonction SOUS.TOTAL d'Excel est conçue pour calculer des sous-totaux dans une liste ou une base de données. Sa particularité réside dans sa capacité à ignorer les lignes masquées par un filtre ou par un masquage manuel. Cela en fait un outil idéal pour obtenir des résultats précis et dynamiques lorsque vous travaillez avec des ensembles de données volumineux et que vous avez besoin de filtrer l'information.

Syntaxe de la fonction SOUS.TOTAL

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

=SOUS.TOTAL(no_fonction;réf1;[réf2];...)

Où :

• no_fonction : Un nombre compris entre 1 et 11 (ou 101 et 111) qui spécifie la fonction à utiliser pour le calcul du sous-total. Nous détaillerons ces options plus loin.
• réf1 : La première plage de cellules à inclure dans le sous-total.
• réf2;... : (Facultatif) Des plages de cellules supplémentaires à inclure dans le sous-total, jusqu'à 254 plages.

Les différents codes de fonction (no_fonction)

Le paramètre no_fonction est crucial car il détermine le type de calcul effectué par SOUS.TOTAL. Voici les options disponibles :

Comme vous pouvez le constater, les nombres de 1 à 11 incluent les lignes masquées manuellement, tandis que les nombres de 101 à 111 les excluent. Toutes les options excluent les lignes filtrées.

Comment utiliser SOUS.TOTAL dans Excel : Exemples pratiques

Exemple 1 : Calculer la somme des ventes filtrées

Imaginez que vous avez une feuille de calcul avec une colonne "Ventes" et une colonne "Région". Vous souhaitez calculer la somme des ventes pour une région spécifique après avoir appliqué un filtre.

1. Créez votre tableau de données avec les colonnes "Région" et "Ventes". Remplissez-le avec des données fictives.
2. Appliquez un filtre sur la colonne "Région" pour afficher uniquement les ventes de la région souhaitée (par exemple, "Nord").
3. Utilisez la fonction SOUS.TOTAL pour calculer la somme des ventes visibles. Dans une cellule vide, entrez la formule suivante : =SOUS.TOTAL(9;B2:B100) (en supposant que la colonne "Ventes" commence à la cellule B2 et se termine à la cellule B100).

La fonction SOUS.TOTAL affichera uniquement la somme des ventes de la région "Nord", car elle ignore les lignes masquées par le filtre. Si vous changez le filtre, le résultat de la fonction SOUS.TOTAL se mettra à jour automatiquement.

Exemple 2 : Calculer la moyenne des notes filtrées

Supposons que vous ayez une feuille de calcul avec une colonne "Nom", une colonne "Classe", et une colonne "Note". Vous souhaitez calculer la moyenne des notes pour une classe spécifique.

1. Créez votre tableau de données avec les colonnes "Nom", "Classe", et "Note".
2. Appliquez un filtre sur la colonne "Classe" pour afficher uniquement les notes de la classe souhaitée (par exemple, "Terminale").
3. Utilisez la fonction SOUS.TOTAL avec le code de fonction 1 (pour la moyenne) : =SOUS.TOTAL(1;C2:C100) (en supposant que la colonne "Note" commence à la cellule C2 et se termine à la cellule C100).

Exemple 3 : Compter le nombre d'éléments visibles après un filtre

Vous avez une liste de produits et vous voulez savoir combien de produits d'une certaine catégorie sont affichés après avoir appliqué un filtre.

1. Créez votre tableau de données avec les colonnes "Produit" et "Catégorie".
2. Appliquez un filtre sur la colonne "Catégorie".
3. Utilisez la fonction SOUS.TOTAL avec le code de fonction 3 (pour NBVAL, qui compte les cellules non vides) : =SOUS.TOTAL(3;A2:A100) (en supposant que la colonne "Produit" commence à la cellule A2 et se termine à la cellule A100).

SOUS.TOTAL vs SOMME : Quelle est la différence ?

La principale différence entre SOUS.TOTAL et SOMME réside dans la façon dont elles gèrent les lignes masquées. La fonction SOMME additionne toutes les valeurs d'une plage, qu'elles soient visibles ou masquées. En revanche, la fonction SOUS.TOTAL, lorsqu'elle est utilisée avec les codes de fonction 1 à 11, ignore les lignes masquées par un filtre. Si vous utilisez les codes 101 à 111, elle ignorera aussi les lignes masquées manuellement.

Cela signifie que SOUS.TOTAL est particulièrement utile lorsque vous travaillez avec des données filtrées et que vous souhaitez obtenir un résultat qui reflète uniquement les valeurs visibles. SOMME est plus appropriée lorsque vous avez besoin d'additionner toutes les valeurs, quelle que soit leur visibilité.

Astuces et bonnes pratiques pour utiliser SOUS.TOTAL

• Choisissez le bon code de fonction : Assurez-vous de sélectionner le code de fonction approprié en fonction du type de calcul que vous souhaitez effectuer (somme, moyenne, nombre, etc.).
• Utilisez les références de cellules correctement : Vérifiez que les références de cellules dans la fonction SOUS.TOTAL correspondent à la plage de données que vous souhaitez analyser.
• Comprenez la différence entre les codes 1-11 et 101-111 : Gardez à l'esprit que les codes 1 à 11 incluent les lignes masquées manuellement, tandis que les codes 101 à 111 les excluent. Choisissez le code approprié en fonction de vos besoins.
• Combinez SOUS.TOTAL avec d'autres fonctions : Vous pouvez combiner SOUS.TOTAL avec d'autres fonctions Excel pour créer des formules plus complexes et puissantes. Par exemple, vous pouvez utiliser SI et SOUS.TOTAL pour effectuer des calculs conditionnels sur des données filtrées.
• Utilisez les tableaux structurés : Convertir vos plages de données en tableaux structurés (Insertion > Tableau) permet à Excel de gérer automatiquement les références de cellules lorsque vous ajoutez ou supprimez des lignes. Cela simplifie l'utilisation de SOUS.TOTAL et réduit le risque d'erreurs.

Erreurs courantes à éviter avec SOUS.TOTAL

• Oublier de spécifier le code de fonction : Si vous ne spécifiez pas le code de fonction, Excel renverra une erreur.
• Utiliser le mauvais code de fonction : Utiliser un code de fonction incorrect entraînera un résultat incorrect.
• Inclure des cellules vides dans la plage de données : Si votre plage de données contient des cellules vides, cela peut affecter le résultat du calcul, en particulier pour les fonctions de moyenne et de nombre.
• Ne pas tenir compte des lignes masquées : Si vous utilisez les codes de fonction 1 à 11, assurez-vous de comprendre que les lignes masquées manuellement seront incluses dans le calcul. Si vous ne voulez pas inclure ces lignes, utilisez les codes 101 à 111.

Alternatives à SOUS.TOTAL

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

• Tableaux croisés dynamiques : Les tableaux croisés dynamiques sont un excellent moyen d'analyser et de résumer de grandes quantités de données. Ils vous permettent de regrouper et de filtrer les données, et d'effectuer des calculs (somme, moyenne, etc.) sur les groupes résultants.
• Fonctions SOMME.SI, MOYENNE.SI, NB.SI : Ces fonctions vous permettent d'effectuer des calculs conditionnels sur une plage de cellules. Par exemple, SOMME.SI vous permet d'additionner uniquement les valeurs qui répondent à un certain critère.
• Power Query : Power Query est un outil d'extraction, de transformation et de chargement de données (ETL) intégré à Excel. Il vous permet d'importer des données à partir de différentes sources, de les nettoyer et de les transformer, et de les charger dans une feuille de calcul. Vous pouvez ensuite utiliser des formules Excel ou des tableaux croisés dynamiques pour analyser les données.

Conclusion

La fonction SOUS.TOTAL d'Excel est un outil précieux pour effectuer des calculs dynamiques sur des données filtrées. En comprenant sa syntaxe, ses différents codes de fonction et ses bonnes pratiques d'utilisation, vous pouvez l'utiliser pour optimiser vos feuilles de calcul et gagner en efficacité. N'hésitez pas à expérimenter avec les exemples présentés dans cet article et à explorer les alternatives pour trouver la méthode la plus adaptée à vos besoins.