La formule SUMIF est utile pour effectuer des sommes conditionnelles dans Google Sheets. Mais savez-vous comment utiliser la SUMIF pour exclure les lignes masquées dans Google Sheets ?
La fonction SUBTOTAL de Google Sheets peut vous aider à traiter les lignes masquées. Cependant, elle n’accepte pas de critères ou de conditions.
Je propose donc une combinaison de SUMIF et de SUBTOTAL.
Note : Dans ce contexte, les lignes masquées sont les lignes masquées par filtrage, regroupement des lignes ou masquage manuel.
Comment additionner une colonne en fonction de conditions et exclure les lignes masquées
Dans Excel, j’ai vu des utilisateurs utiliser une combinaison de SUMPRODUCT, SUBTOTAL, ROW et OFFSET pour effectuer cette tâche. Cependant, cette combinaison peut ne pas fonctionner comme prévu dans Google Sheets.
J’ai inclus la formule Excel mentionnée dans mon article intitulé « Fonction SUBTOTAL avec conditions dans Excel et Google Sheets ».
Vous y trouverez l’utilisation des fonctions COUNTIF, AVERAGEIF, MINIFS, MAXIFS et SUMIF dans les lignes visibles sans colonne d’assistance dans Excel.
L’approche de la colonne d’assistance dans Google Sheets est également détaillée dans cet article. J’espère que vous trouverez le temps de le consulter.
La fonction SUMIF qui traite les lignes masquées – Comment ça marche ?
Le but de la SUMIF est de faire la somme d’une plage en fonction d’une condition. Cependant, elle ajoutera les valeurs des lignes masquées si la condition est remplie.
Malheureusement, il n’existe aucune fonction intégrée dans Google Sheets ou Excel pour traiter les lignes masquées en fonction d’une condition.
SUBTOTAL est la seule fonction qui traite correctement les lignes masquées. Cependant, il n’existe pas de fonction SUBTOTALIF pour appliquer des conditions.
Alors quelle est la solution finale ?
Nous pouvons utiliser une combinaison de SUMIF et de SUBTOTAL pour exclure les lignes masquées.
Il existe deux méthodes que nous pouvons adopter. Dans l’une d’entre elles, nous utilisons une colonne d’assistance, tandis que dans l’autre, nous n’en utilisons pas.
Dans la méthode sans colonne d’assistance, il existe deux approches : l’une est dynamique et l’autre est non dynamique.
SUMIFS en excluant les lignes masquées dans Google Sheets en utilisant une colonne d’assistance
La colonne d’assistance est une colonne supplémentaire utilisée à des fins de calcul.
Je pense que SUMIFS serait préférable à utiliser avec SUBTOTAL et plus facile à comprendre. C’est pourquoi je vais l’utiliser ici.
Commençons par expliquer comment appliquer une somme conditionnelle en excluant les lignes masquées.
Notre exemple de données réelles est réparti sur la plage B2:E7. La colonne A est notre colonne d’assistance, dont je parlerai plus tard.
Dans cet exemple, je veux faire la somme de la valeur des ventes d’un des articles, c’est-à-dire « Combinaison », dans la colonne B.
Cela signifie que je souhaite obtenir la somme de la colonne E si les valeurs de la colonne B sont égales à « Combinaison ».
Nous pouvons facilement le faire avec la fonction SUMIF. Mais nous voulons quelque chose de plus !
=SUMIF(B2:B7, »Combinaison »,E2:E7)
Lorsque nous masquons une ligne contenant « Combinaison », la valeur de la ligne masquée ne doit pas être prise en compte dans le total.
Étapes
Voici les étapes à suivre pour utiliser la SUMIF en excluant les lignes masquées dans Google Sheets.
Dans la colonne d’assistance A, dans la cellule A2, appliquez la formule Subtotal ci-dessous et copiez-la jusqu’à la cellule A7.
=subtotal(109,E2)
Le SUBTOTAL ci-dessus est la clé. Savez-vous pourquoi ?
Il extrait toutes les valeurs de la colonne E vers la colonne A.
Lorsque vous masquez des lignes dans la plage, la formule SUBTOTAL dans cette ligne retourne zéro au lieu de la valeur réelle.
Grâce à cette fonctionnalité de SUBTOTAL, nous pouvons exclure les valeurs des lignes masquées de notre total avec une formule comme celle-ci.
=SUMIFS(E2:E7,A2:A7, »>0″,B2:B7, »Combinaison »)
Cela renverra la somme de la plage de cellules E2:E7 si B2:B7 = « Combinaison » et A2:A7 > 0.
Lorsque vous masquez une ligne, la valeur dans la cellule correspondante de la colonne A passe à 0 (zéro).
Ainsi, la formule SUMIFS exclura cette ligne du total.
Maintenant, masquez une ligne contenant l’article « Combinaison » et observez la magie.
SUMIFS en excluant les lignes masquées sans utiliser de colonne d’assistance
Nous avons déjà appris à utiliser la SUMIF en excluant les lignes masquées dans Google Sheets avec une colonne d’assistance ci-dessus. Maintenant, nous pouvons le faire avec une colonne d’assistance virtuelle.
Veuillez supprimer la colonne A. Ainsi, la colonne des critères (Article) deviendra A et la colonne des valeurs (Montant) deviendra D (veuillez vous référer à l’image ci-dessous).
1. Approche non dynamique
Il s’agit de notre approche non dynamique pour utiliser la SUMIF en excluant les lignes masquées dans Google Sheets, sans utiliser de colonne d’assistance.
=sumifs(D2:D7,{subtotal(109,D2);subtotal(109,D3);subtotal(109,D4);subtotal(109,D5);subtotal(109,D6);subtotal(109,D7)}, »>0″,A2:A7, »Combinaison »)
Cette formule semble compliquée, n’est-ce pas ? Mais en réalité, ce n’est pas le cas !
La formule entre les accolades est une colonne d’assistance virtuelle.
Étapes (Colonne d’assistance virtuelle)
Tout d’abord, nous avons besoin d’une colonne d’assistance contenant la formule SUBTOTAL, utilisons E2:E7 à cette fin. Nous utilisons cette colonne à des fins temporaires et pouvons la supprimer ultérieurement.
Veuillez vous référer à la colonne E. J’ai copié la formule E2 dans les cellules E3:E7.
Maintenant, appuyez sur les touches de raccourci Ctrl+~ pour afficher les formules dans la colonne d’assistance.
Supprimez le signe « = » des formules dans la colonne d’assistance.
Encore une fois, appuyez sur les touches de raccourci Ctrl+~.
Maintenant, ce que nous voulons faire, c’est utiliser la formule suivante dans n’importe quelle cellule vide.
=join(« ; »,E2:E7)
Copiez sa sortie et collez-la dans la même cellule sous forme de valeur. Vous pouvez utiliser Ctrl+C pour copier et Alt+E+S+V pour coller en tant que valeur.
Ça ressemblera à ceci.
subtotal(109,D2);subtotal(109,D3);subtotal(109,D4);subtotal(109,D5);subtotal(109,D6);subtotal(109,D7)
Placez simplement des accolades ouvrantes et fermantes autour du texte ci-dessus pour en faire un tableau.
{subtotal(109,D2);subtotal(109,D3);subtotal(109,D4);subtotal(109,D5);subtotal(109,D6);subtotal(109,D7)}
Vous pouvez maintenant supprimer la colonne d’assistance E.
2. Approche dynamique en utilisant la fonction BYROW
L’approche non dynamique ci-dessus présente deux inconvénients principaux.
- Elle prend beaucoup de temps pour créer la colonne d’assistance virtuelle.
- Elle est utile uniquement lorsque le nombre de lignes dans les données source est faible, disons <15.
Nous pouvons le surmonter en utilisant la fonction BYROW, une fonction auxiliaire LAMBDA, dans Google Sheets.
=byrow(D2:D7,lambda(helper,subtotal(109,helper)))
Nous pouvons remplacer la formule de la colonne d’assistance virtuelle par cette formule BYROW dans la SUMIFS, et le tour est joué !
=sumifs(D2:D7,byrow(D2:D7,lambda(helper,subtotal(109,helper))), »>0″,A2:A7, »Combinaison »)
La formule ci-dessus est ma formule suggérée pour utiliser la SUMIF en excluant les lignes masquées dans Google Sheets.
