Si vous utilisez Google Sheets, vous pouvez vous demander comment calculer la moyenne des n plus petites valeurs dans une plage donnée. De plus, vous souhaiterez peut-être exclure les valeurs égales à zéro pour obtenir des résultats plus précis. Dans cet article, je vais vous expliquer comment réaliser ces calculs en utilisant différentes formules et fonctions de Google Sheets.
Comment sélectionner les plus petites valeurs
Dans Google Sheets, vous pouvez utiliser les fonctions SORTN ou SMALL pour sélectionner les n plus petites valeurs d’une plage donnée. Ces fonctions vous permettent de trier les valeurs et d’obtenir les n premières valeurs de la liste triée. Ainsi, vous pouvez facilement trouver les plus petites valeurs qui vous intéressent.
Comment exclure les valeurs égales à zéro
Maintenant, vous vous demandez peut-être comment exclure les valeurs égales à zéro de vos calculs. Pour cela, nous avons deux options : QUERY et FILTER. Personnellement, je préfère utiliser FILTER car elle est plus simple et efficace.
Les différentes formules
Dans cet article, je vous présenterai quatre formules différentes pour calculer la moyenne des plus petites valeurs, selon que vous souhaitez inclure ou exclure les valeurs égales à zéro.
- SMALL (inclut 0)
- SORTN (inclut 0)
- SMALL + FILTER (exclut 0)
- SORTN + FILTER (exclut 0)
De plus, je vous montrerai comment transformer ces formules en formules matricielles à l’aide de Lambdas. Les formules matricielles vous permettent d’appliquer une formule à une plage de cellules, au lieu de devoir écrire la formule pour chaque cellule individuellement.
Comment utiliser les formules
Pour vous montrer comment utiliser ces formules, je vais prendre l’exemple de notes d’étudiants réparties dans différentes colonnes. Supposons que les notes de la première année soient dans la première ligne, les notes de la deuxième année dans la deuxième ligne, etc.
Si vos données sont disposées horizontalement, vous pouvez utiliser les deux formules suivantes pour obtenir les n plus petites valeurs, y compris zéro :
-
Formule SMALL :
=ArrayFormula(iferror(small(B2:J2,sequence(5)))) -
Formule SORTN :
=sortn(transpose(B2:J2),5)
Si vos données sont disposées verticalement, vous devrez effectuer une légère modification dans les formules ci-dessus.
Pour obtenir la moyenne des plus petites valeurs, vous pouvez envelopper ces formules avec la fonction AVERAGE :
-
Formule SMALL :
=ArrayFormula(average(small(B2:J2,sequence(5)))) -
Formule SORTN :
=average(sortn(transpose(B2:J2),5))
Si vous souhaitez exclure les valeurs égales à zéro, vous pouvez utiliser les mêmes formules mais en ajoutant une fonction FILTER pour filtrer les valeurs supérieures à zéro :
-
Formule SMALL + FILTER :
=ArrayFormula(average(small(filter(B2:J2,B2:J2>0),sequence(5)))) -
Formule SORTN + FILTER :
=average(sortn(transpose(filter(B2:J2,B2:J2>0)),5))
Utilisation des formules matricielles
Si vous souhaitez appliquer ces formules à plusieurs lignes de données, vous pouvez utiliser des formules matricielles. Cela vous permettra d’obtenir la moyenne des plus petites valeurs pour chaque ligne de données individuellement.
Vous pouvez utiliser les fonctions BYROW ou MAP de Google Sheets pour obtenir ce résultat. Ces fonctions vous permettent d’appliquer une formule à une plage de cellules spécifiée. Voici comment les utiliser :
-
Formule SMALL + FILTER (formule matricielle) :
=byrow(B2:J7,lambda(r,average(sortn(transpose(filter(r,r>0)),5)))) -
Formule SORTN + FILTER (formule matricielle) :
=byrow(B2:J7,lambda(r,average(sortn(transpose(filter(r,r>0)),5))))
En utilisant ces formules matricielles, vous pouvez calculer la moyenne des plus petites valeurs pour chaque ligne de données en une seule étape.
Conclusion
Maintenant, vous savez comment calculer la moyenne des plus petites valeurs dans Google Sheets, en incluant ou excluant les zéros. Vous avez également appris comment utiliser des formules matricielles pour appliquer ces calculs à plusieurs lignes de données. N’hésitez pas à expérimenter avec ces formules et à les adapter à vos propres besoins. Pour plus d’informations sur Google Sheets et d’autres astuces de marketing en ligne, consultez Crawlan.com.
