Pour sommer les n plus grandes valeurs en fonction d’un critère dans Google Sheets, vous devez d’abord savoir comment les trouver.
Si vous êtes un utilisateur régulier de Google Docs Sheets, vous avez probablement déjà pensé aux solutions utilisant les fonctions MAX, LARGE ou MAXIFS. Cependant, ces solutions ne vous aideront pas de manière ordinaire.
Personnellement, je préfère utiliser la formule QUERY pour accomplir ce genre de tâches. Une autre formule efficace est la combinaison de FILTER et SORTN.
Query :
Vous pouvez utiliser la clause « Order by » dans une requête pour trier les données par ordre décroissant (pour trouver les n plus grandes valeurs) ou par ordre croissant (pour trouver les n plus petites valeurs).
Voir notre article sur l’ordre de tri personnalisé dans Google Sheets Query pour plus d’informations.
La clause « Limit » dans une requête permet de retourner n valeurs. En utilisant la clause « Where », nous pouvons appliquer des conditions.
SORTN + Filter :
La fonction SORTN nous aide à trier les données par ordre décroissant (pour trouver les n plus grandes valeurs) ou par ordre croissant (pour trouver les n plus petites valeurs). La variable ‘n’ dans SORTN indique le nombre de valeurs à prendre en compte.
Il n’est pas possible d’utiliser des conditions avec SORTN. Pour cela, nous pouvons utiliser la fonction FILTER en combinaison avec SORTN.
Voici un exemple très basique qui vous donnera une idée claire des formules que j’utilise.
Formule Query pour sommer les n plus grandes valeurs en fonction d’un critère
Problème : Sommez les deux plus grandes quantités de vente pour le fruit « Pomme ».
Suivez les instructions étape par étape ci-dessous pour comprendre comment la formule se développe. Dans cet exemple, le critère/condition se trouve dans la cellule D2 et la formule dans la cellule E2.
Etape n°1 : Utilisation de la clause « Order by ».
La formule dans la cellule E2 qui trie la colonne B par ordre décroissant.
=query(A2:B,"Select * order by B desc")Cette formule placera les valeurs les plus élevées en tête de liste.
Etape n°2 : Utilisation de la clause « Where ».
Modifiez cette formule comme suit.
=query(A2:B,"Select B where A='Pomme' order by B desc")Ou
=query(A2:B,"Select B where A='"&D2&"' order by B desc")Vous obtenez ainsi une colonne contenant les valeurs les plus élevées pour le fruit « Pomme ».
Etape n°3 : Utilisation de la clause « Limit ».
Nous voulons additionner les deux valeurs les plus élevées. Modifiez la formule pour inclure la clause « Limit » et enveloppez l’ensemble de la formule avec la fonction SUM.
=sum(query(A2:B,"Select B where A='Pomme' order by B desc limit 2"))La formule ci-dessus est la formule finale pour sommer les n plus grandes valeurs en fonction d’un critère dans Google Sheets. Ici, « Limit 2 » détermine la valeur de n.
J’ai utilisé le mot « critères » mais j’ai utilisé « critère » dans la formule. Donc, si vous souhaitez inclure deux conditions, modifiez la requête comme suit.
Problème : Sommez les deux plus grandes quantités de vente des fruits « Pomme » ou « Orange ».
=sum(query(A2:B,"Select B where A='Pomme' or A='Orange' order by B desc limit 2"))Dans cette formule, les quantités de vente les plus élevées sont trouvées pour les articles « Pomme » et « Orange ». La formule ignore les autres valeurs (fruits) dans la colonne A et trouve les deux valeurs les plus élevées indépendamment du fruit « Pomme » ou « Orange ».
Remarque :
Si vous recherchez une formule pour additionner les deux valeurs les plus élevées de chaque article, c’est-à-dire les deux valeurs les plus élevées pour « Pomme » et les deux valeurs les plus élevées pour « Orange », vous pouvez consulter mon tutoriel intitulé « Somme des deux valeurs les plus élevées par groupe ». Vous trouverez le lien à la fin de cet article.
SORTN + Filter pour sommer les n plus grandes valeurs en fonction de conditions
Il y a une autre manière de sommer les n plus grandes valeurs en fonction de conditions dans Google Sheets. Voici comment faire :
Ici aussi, le critère « Pomme » se trouve en D2 et la formule que je vais utiliser se trouve en E2.
Dans un premier temps, filtrez l’ensemble de données pour le fruit « Pomme ».
=filter(A2:B,A2:A="Pomme")Modifiez cette formule pour ne retourner que la colonne des valeurs.
=filter(B2:B,A2:A="Pomme")Dans l’étape suivante, nous pouvons TRIER les données par ordre décroissant et limiter le résultat à n. Ici, n vaut deux.
=sortn(filter(B2:B,A2:A="Pomme"),2,0,1,false)Une autre formule qui utilise les fonctions ARRAY_CONSTRAIN et SORT, équivalente à celle ci-dessus.
=array_constrain(sort(filter(B2:B,A2:A="Pomme"),1,false),2,1)Enveloppez la formule avec SUM.
=sum(sortn(filter(B2:B,A2:A="Pomme"),2,0,1,false))Ou
=sum(array_constrain(sort(filter(B2:B,A2:A="Pomme"),1,false),2,1))J’ai expliqué (j’espère bien) la formule ci-dessus de manière détaillée dans ma vidéo tutoriel ci-dessous.
Puis-je utiliser plusieurs critères ici ?
Oui ! Dans les deux formules ci-dessus, SORTN + Filter ou Array_Constrain + SORT + Filter, nous pouvons ajouter plusieurs critères. Il suffit de modifier la formule du filtre en conséquence.
=sum(sortn(filter(B2:B,(A2:A="Pomme")+(A2:A="Orange")),2,0,1,false))Ici encore, prenez en compte la note donnée en caractères gras ci-dessus.
En conclusion
Le but de ce tutoriel est de vous familiariser avec la manière de sommer les n plus grandes valeurs en fonction d’un critère dans Google Sheets. Pour ce faire, j’ai fourni deux formules et les ai détaillées autant que possible. Si vous avez des doutes, n’hésitez pas à les poser dans les commentaires.
Ressources supplémentaires :
- Comment sommer, calculer la moyenne, compter, trouver le maximum et le minimum dans Google Sheets Query.
- Trouver les n plus grandes valeurs d’une ligne et retourner les en-têtes dans Google Sheets.
- Comment mettre en évidence la valeur maximale d’une ligne dans Google Sheets.
- Sommer les n plus grandes valeurs par groupe dans Google Sheets.
Pour plus d’informations, visitez Crawlan.com.
