Formule de SOUS-TOTAL Conditionnel – Excel et Google Sheets

Ce tutoriel montre comment calculer un sous-total conditionnel, en exécutant la fonction SOUS.TOTAL, uniquement sur les cellules qui répondent à certains critères.

Fonction SOUS.TOTAL

La fonction SOUS-TOTAL est utilisée pour effectuer divers calculs sur une plage de données (décompte, somme, moyenne, etc.). Si vous n’êtes pas familier avec cette fonction, vous vous demandez peut-être pourquoi vous n’utiliseriez pas simplement les fonctions NB, SOMME ou MOYENNE… Il y a au moins deux bonnes raisons :

1. Vous pouvez créer un tableau qui répertorie les options SOUS.TOTAL (1,2,3,4, etc.) et copier une seule formule vers le bas pour créer des données récapitulatives. (Cela peut être un gain de temps particulièrement important si vous essayez de calculer un SOUS-TOTAL conditionnel comme nous le démontrons dans cet article).
2. La fonction SOUS.TOTAL peut être utilisée pour calculer uniquement les lignes visibles (filtrées).

Nous allons nous concentrer sur la deuxième implémentation de la fonction SOUS.TOTAL.

Dans cet exemple, nous utiliserons la fonction pour compter (NBVAL) les lignes visibles en définissant l’argument SOUS.TOTAL no_fonction à 3 (Une liste complète des fonctions possibles se trouve ici)

=SOUS.TOTAL(3;$D$2:$D$14)

Remarquez comment les résultats changent lorsque nous filtrons manuellement les lignes.

SOUS-TOTAL Conditionnel « SI »

Pour créer un « Sous-Total Si », nous allons utiliser une combinaison de SOMMEPROD, SOUS.TOTAL, DECALER, LIGNE et MIN dans une formule de tableau. Grâce à cette combinaison, nous pouvons essentiellement créer une fonction générique « SOUS.TOTAL SI ». Voyons un exemple.

Nous avons une liste de membres et leur statut de présence pour chaque événement :

Supposons que l’on nous demande de compter le nombre de membres qui ont assisté à un événement de façon dynamique car nous filtrons manuellement la liste comme suit :

Pour ce faire, nous pouvons utiliser cette formule :

=SOMMEPROD((<plage de valeurs>=<critères>)*(SOUS.TOTAL(3;DECALER(<première cellule de la plage>;LIGNE(<plage de valeurs>)-MIN(LIGNE(<plage de valeurs>));0))))

=SOMMEPROD((D2:D14="Participant")*(SOUS.TOTAL(3;DECALER(D2;LIGNE(D2:D14)-MIN(LIGNE(D2:D14));0))))

Lorsque vous utilisez Excel 2019 et les versions antérieures, vous devez saisir la formule de tableau en appuyant sur CTRL + MAJ + ENTRÉE pour indiquer à Excel que vous saisissez une formule de tableau. Vous saurez que la formule a été saisie correctement en tant que formule de tableau lorsque des accolades apparaissent autour de la formule (voir image ci-dessus).

Comment fonctionne la formule ?

La formule fonctionne en multipliant deux tableaux à l’intérieur de SOMMEPROD, où le premier tableau traite de nos critères et le second filtre uniquement les lignes visibles :

=SOMMEPROD(<réseau de critères>*<réseau de visibilité>)

Le Tableau de Critères

Le tableau des critères évalue chaque ligne de notre plage de valeurs (statut « Présent » dans cet exemple) et génère un tableau comme celui-ci :

=(<variété des valeurs>=<critères>)

=(D2:D14="Présent")

Sortie :

{VRAI ; FAUX ; FAUX ; VRAI ; FAUX ; VRAI ; VRAI ; VRAI ; FAUX ; FAUX ; VRAI ; FAUX ; VRAI ; VRAI}

Notez que la sortie dans le premier tableau de notre formule ignore si la ligne est visible ou non, c’est là que notre deuxième tableau intervient.

Le Tableau de Visibilité

En utilisant SOUS.TOTAL pour exclure les lignes non visibles de notre plage, nous pouvons générer notre tableau de visibilité. Cependant, SOUS.TOTAL seul renvoie une seule valeur, alors que SOMMEPROD attend un tableau de valeurs. Pour contourner ce problème, nous utilisons DECALER pour transmettre une ligne à la fois. Cette technique nécessite d’alimenter DECALER avec un tableau qui contient un seul nombre à la fois. Le deuxième tableau ressemble à ceci :

=SOUS.TOTAL(3;DECALER(<première cellule de la plage>;LIGNE(<valeur de la plage>)-MIN(LIGNE(<valeur de la plage>));0))

=SOUS-TOTAL(3;DECALER(D2;LIGNE(D2:D14)-MIN(LIGNE(D2:D14));0))

Sortie :

{1;1;0;0;1;1}

Assemblage des Deux :

=SOMMEPROD({VRAI ; VRAI ; FAUX ; FAUX ; VRAI ; VRAI} * {1 ; 1 ; 0 ; 0 ; 1 ; 1})

= 4

SOUS-TOTAL Conditionnel avec Plusieurs Critères

Pour ajouter plusieurs critères, il suffit de les regrouper dans le SOMMEPROD comme suit :

=SOMMEPROD((<valeur de la plage 1>=<critère 1>)*(<valeur de la plage 2>=<critère 2>)*(SOUS.TOTAL(3,DECALER(<première cellule de la plage>,LIGNE(<valeur de la plage>)-MIN(LIGNE(<valeur de la plage>)),0))))

=SOMMEPROD((E2:E14="Attended")*(B2:B14=2019)*(SOUS.TOTAL(3,DECALER(E2,LIGNE(E2:E14)-MIN(LIGNE(E2:E14)),0))))

SOUS-TOTAL Conditionnel dans Google Sheets

La création d’une formule SOUS.TOTAL Contionnel fonctionne exactement de la même manière dans Google Sheets et dans Excel. Sauf que, lorsque vous utilisez CTRL + MAJ + ENTRÉE pour saisir la formule de tableau, Google Sheets ajoute la fonction ARRAYFORMULA à la formule (vous pouvez également ajouter cette fonction manuellement).