Téléchargez l'exemple de classeur
Ce tutoriel montrera comment calculer le "sous-total si", en ne comptant que les lignes visibles avec des critères.
Fonction SOUS-TOTAL
La fonction SOUS-TOTAL permet d'effectuer divers calculs sur une plage de données (compte, somme, moyenne, etc.). Plus important encore, il peut être utilisé pour calculer uniquement sur les lignes visibles (filtrées). Dans cet exemple, nous utiliserons la fonction pour compter (COUNTA) les lignes visibles en définissant l'argument SUBTOTAL function_num sur 3 (une liste complète des fonctions possibles peut être trouvée ici.)
=SOUS-TOTAL(3,$D$2:$D$14)
Remarquez comment les résultats changent lorsque nous filtrons manuellement les lignes.
SOUS-TOTAL SI
Pour créer un « Sous-total si », nous utiliserons une combinaison de SOMMEPROD, SOUS-TOTAL, DÉCALAGE, LIGNE et MIN dans une formule matricielle. En utilisant cette combinaison, nous pouvons essentiellement créer une fonction générique « SOUS-TOTAL SI ». Parcourons un exemple.
Nous avons une liste des membres et leur statut de présence pour chaque événement :
Supposons qu'on nous demande de compter le nombre de membres qui ont assisté à un événement de manière dynamique alors que nous filtrons manuellement la liste comme suit :
Pour ce faire, nous pouvons utiliser cette formule :
=SOMMEPROD((=)*(SOUS-TOTAL(3,OFFSET(,LIGNE()-MIN(LIGNE()),0))))
=SUMPRODUCT((D2:D14="Attendu")*(SOUS-TOTAL(3,OFFSET(D2,ROW(D2:D14)-MIN(ROW(D2:D14)),0))))
Lorsque vous utilisez Excel 2022 et versions antérieures, vous devez entrer la formule matricielle en appuyant sur CTRL + MAJ + ENTRÉE pour dire à Excel que vous entrez une formule matricielle. Vous saurez que la formule a été saisie correctement en tant que formule matricielle lorsque des accolades apparaissent autour de la formule (voir l'image ci-dessus).
Comment fonctionne la formule ?
La formule fonctionne en multipliant deux tableaux à l'intérieur de SUMPRODUCT, où le premier tableau traite de nos critères et le second tableau filtre uniquement les lignes visibles :
=SOMMEPROD (*)
Le tableau des critères
Le tableau de critères évalue chaque ligne de notre plage de valeurs (État « Attendu » dans cet exemple) et génère un tableau comme celui-ci :
=(=)
=(D2:D14="Attention")
Sortir:
{VRAI; FAUX; FAUX; VRAI; FAUX; TURE; TURE ; TURE ; FAUX; FAUX; VRAI; FAUX; VRAI}
Notez que la sortie du premier tableau de notre formule ignore si la ligne est visible ou non, c'est là que notre deuxième tableau intervient pour vous aider.
Le tableau de visibilité
En utilisant SUBTOTAL pour exclure les lignes non visibles de notre plage, nous pouvons générer notre tableau de visibilité. Cependant, SUBTOTAL seul renverra une valeur unique, tandis que SUMPRODUCT attend un tableau de valeurs. Pour contourner ce problème, nous utilisons OFFSET pour passer une ligne à la fois. Cette technique nécessite de fournir à OFFSET un tableau contenant un nombre à la fois. Le deuxième tableau ressemble à ceci :
=SOUS-TOTAL(3,OFFSET(,LIGNE()-MIN(LIGNE()),0))
=SOUS-TOTAL(3,DECALAGE(D2,LIGNE(D2:D14)-MIN(LIGNE(D2:D14)),0))
Sortir:
{1;1;0;0;1;1}
Assembler les deux :
=SOMMEPROD({VRAI; VRAI; FAUX; FAUX; VRAI; VRAI} * {1; 1; 0; 0; 1; 1})
= 4
SOUS-TOTAL SI avec plusieurs critères
Pour ajouter plusieurs critères, il suffit de plusieurs autres critères ensemble dans le SUMPRODUCT comme ceci :
=SOMMEPROD((=)*(=)*(SOUS-TOTAL(3,OFFSET(,LIGNE()-MIN(LIGNE()),0))))
=SUMPRODUCT((E2:E14="Attendu")*(B2:B14=2019)*(SOUS-TOTAL(3,OFFSET(E2,ROW(E2:E14)-MIN(ROW(E2:E14)),0)) ))
SOUS-TOTAL SI dans Google Sheets
La fonction SOUS-TOTAL SI fonctionne exactement de la même manière dans Google Sheets que dans Excel :
