Imaginez que vous ayez un ensemble de données dans Google Sheets et que vous souhaitiez faire la somme des n valeurs maximales par groupe. Grâce à une formule puissante impliquant Query et un tableau de comptage, cela devient très facile.
Un exemple pratique
Prenons l’exemple des notes obtenues par deux étudiants dans cinq matières différentes. Voici à quoi ressemble notre ensemble de données :
Somme des valeurs maximales n dans une plage triée
Le premier cas que nous allons aborder est celui d’un ensemble de données triées. Dans cet exemple, nous supposons que les noms des étudiants sont triés dans la colonne A et que nous voulons également trier les notes de manière décroissante dans la colonne B.
Pour ce faire, vous devez trier les données comme suit : sélectionnez le tableau A2:C11, puis allez dans le menu Données -> Trier la plage. Effectuez le tri selon les paramètres suivants :
Supposons que nous voulions trouver les 3 notes les plus élevées de chaque étudiant. Pour cela, nous utiliserons la fonction Query en combinaison avec une colonne virtuelle qui contient un compteur.
Voici la formule pour générer cette colonne virtuelle :
ARRAYFORMULA(if(len(A2:A),COUNTIFS(A2:A,A2:A,ROW(A2:A),"<=ROW(A2:A))))Cette formule génère un tableau de quatre colonnes contenant le nom de l’étudiant, la note sur 100, la matière et la colonne « Helper » en tant que quatrième colonne.
Ensuite, utilisez l’opérateur de comparaison <= pour limiter le nombre de lignes à trois dans chaque groupe. Cela est possible grâce au compteur de la quatrième colonne.
Voici la formule finale pour la somme des 3 valeurs maximales par groupe :
=Query(Query({A2:C,ARRAYFORMULA(if(len(A2:A),COUNTIFS(A2:A,A2:A,ROW(A2:A),"<=ROW(A2:A))))},"Select * where Col4 <=3"),"Select Col1, Sum(Col2) where Col1 is not null group by Col1")Voici à quoi cela ressemble :
Somme des valeurs maximales n dans une plage non triée
Supposons maintenant que nous ayons un ensemble de données non triées ou que nous importions des données à l’aide de Importrange et que celles-ci soient mises à jour fréquemment. Il ne serait pas judicieux de trier manuellement les données à chaque fois.
Nous avons donc besoin d’une formule « prête à l’emploi » pour faire la somme des valeurs maximales n par groupe dans Google Sheets, indépendamment de l’ordre des données.
Vous pouvez utiliser la formule finale mentionnée précédemment avec quelques modifications pour un ensemble de données non triées.
Utilisez la formule SORT ci-dessous pour remplacer le tableau A2:C dans la formule finale :
sort(A2:C,1,true,2,false)Ensuite, utilisez la formule SORT ci-dessous pour remplacer le tableau A2:A dans le COUNTIFS (uniquement les deux premières références) :
sort(A2:A,1,true)Voici la formule finale pour la somme des 3 valeurs maximales par groupe dans un ensemble de données non triées :
=Query(Query({sort(A2:C,1,true,2,false),ARRAYFORMULA(if(len(A2:A),COUNTIFS(sort(A2:A,1,true),sort(A2:A,1,true),ROW(A2:A),"<=ROW(A2:A))))},"Select * where Col4 <=3"),"Select Col1, Sum(Col2) where Col1 is not null group by Col1")Voici ce que cela donne :
Comme vous pouvez le constater, cette méthode est très efficace pour faire la somme des valeurs maximales n par groupe dans Google Sheets. Elle peut être appliquée à de nombreux cas pratiques, tels que le calcul des scores les plus élevés de chaque joueur, le calcul des ventes maximales réalisées par chaque commercial, ou encore l’extraction et la somme des meilleures notes obtenues dans trois matières par tous les étudiants.
Si vous souhaitez en savoir plus sur les astuces et les fonctionnalités avancées de Google Sheets, n’hésitez pas à visiter Crawlan.com.
