Google Sheets prend en charge les subtotals avec conditions, mais cette fonctionnalité est moins connue qu’elle ne devrait l’être, car elle nécessite une solution de contournement.
Pour effectuer des subtotals, nous pouvons utiliser la fonction SOUSTOTAUX dans Google Sheets. Cependant, elle n’accepte pas les conditions.
Il n’existe pas de fonction SOUSTOTAUX SI dédiée ou de fonctionnalité similaire, mais vous pouvez utiliser la fonction SOUSTOTAUX en conjonction avec d’autres fonctions qui prennent en charge les conditions, telles que NB.SI, SOMME.SI ou REQUETE.
Apprendre à utiliser la fonction SOUSTOTAUX avec des conditions est important car c’est la seule fonction dans Google Sheets qui peut fonctionner avec des données visibles uniquement.
Comment Utiliser le Subtotal avec Conditions dans Google Sheets
La fonction SOUSTOTAUX utilise des codes de fonction pour effectuer une agrégation. Par exemple, elle utilise 109 pour SOMME et 103 pour NBVAL. Nous utiliserons ces deux codes dans cet exemple.
Pour en savoir plus sur tous les codes, vous pouvez vous référer au tutorial suivant: Google Sheets Function Numbers: A Comprehensive Guide.
Comme vous le savez peut-être, lorsque vous avez des lignes masquées, vous pouvez utiliser une formule SOUSTOTAUX dans Google Sheets pour totaliser uniquement les lignes visibles. Par exemple, la formule suivante totalisera la plage C2:C5, en excluant les valeurs des lignes masquées:
=SOUSTOTAUX(109,C2:C5)
Maintenant, supposons que vous souhaitiez totaliser la plage C2:C5 correspondant à « Apple » dans B2:B5. En d’autres termes, vous souhaitez utiliser la fonction SOUSTOTAUX avec une condition.
Les formules SOMME.SI ou SOMME.SI.ENS suivantes renverront le compte conditionnel, y compris les lignes visibles et masquées:
=SOMME.SI.ENS(C2:C5,B2:B5,"Apple")
=SOMME.SI(B2:B5,"Apple",C2:C5)
Voici une solution de contournement pour utiliser les subtotals avec conditions dans Google Sheets:
Subtotal avec Conditions en Utilisant une Colonne d’Aide dans Google Sheets
Dans la cellule D2, insérez la formule suivante et copiez-la dans les cellules D3, D4 et D5:
=SOUSTOTAUX(103,B2)
Cette plage peut être appelée la plage de la colonne d’aide.
Que font ces formules (plage de la colonne d’aide)?
La fonction SOUSTOTAUX avec le code 103 renvoie le compte de chaque cellule. Elle renvoie 1 si la cellule a une valeur, sinon 0. Lorsque vous masquez une ligne, la formule renverra 0, indépendamment de la présence ou non d’une valeur.
Nous utiliserons cette fonctionnalité dans la formule SOMME.SI.ENS suivante:
=SOMME.SI.ENS(C2:C5,B2:B5,"Apple",D2:D5,1)
Syntaxe:
SOMME.SI.ENS(plage_somme; plage_critère1; critère1; [plage_critère2; ...]; [critère2; ...])Où:
plage_sommeest C2:C5plage_critère1est B2:B5critère1est « Apple »plage_critère2est D2:D5critère2est 1
Cette formule SOMME.SI.ENS calcule le total de « Apple » dans les lignes visibles.
La formule dans la cellule C7 est une formule SOMME.SI.ENS qui effectue le subtotal avec la condition dans Google Sheets.
Au lieu de SOMME.SI, j’ai utilisé la fonction SOMME.SI.ENS car nous voulons vérifier deux conditions:
- La valeur dans la colonne B est « Apple ».
- La valeur dans la colonne D est supérieure à 0.
La colonne D est notre nouvelle colonne avec quelques formules SOUSTOTAUX. Lorsque vous masquez des lignes ou appliquez un filtre, la valeur de la ligne masquée dans la colonne D devient zéro. Ainsi, la formule SOMME.SI.ENS exclut ces lignes dans la somme.
Subtotal avec Conditions Sans Utiliser de Colonne d’Aide dans Google Sheets
Je sais que la plupart d’entre vous n’aiment pas utiliser une plage de colonne d’aide. Moi non plus.
Nous pouvons effectuer des subtotals avec conditions sans utiliser de plage de colonne d’aide dans Google Sheets. Ce que vous voulez faire, c’est utiliser la fonction SOUSTOTAUX avec MAP.
Nous pouvons développer la formule D1 par elle-même à l’aide de la fonction MAP.
=MAP(B2:B5, LAMBDA(ligne, SOUSTOTAUX(103,ligne)))
Remplacez plage_critère2 dans la formule, c’est-à-dire D2:D5, par cette formule. Elle sera la suivante:
=SOMME.SI.ENS(C2:C5,B2:B5,"Apple",MAP(B2:B5, LAMBDA(ligne, SOUSTOTAUX(103,ligne))),1)
Conclusion
Nous avons vu comment utiliser les subtotals avec conditions dans Google Sheets. Si vous souhaitez explorer les possibilités des subtotals avec conditions plus en détail, il existe quelques tutoriels supplémentaires disponibles. Les voici:
- Comment Omettre les Valeurs Masquées ou Filtrées dans une Somme
- SOMME.SI Excluant les Lignes Masquées dans Google Sheets
- Manipulation de Lignes Masquées dans Google Sheets avec une Colonne d’Aide Virtuelle
- Vlookup Ignorant les Lignes Masquées dans Google Sheets
- Insérer des Nombres Séquentiels en Ignorant les Lignes Masquées | Filtrées dans Google Sheets
- NBSI Excluant les Lignes Masquées dans Google Sheets
