Aujourd’hui nous allons voir une astuce qui permet de calculer la somme ou la moyenne des N plus grandes valeurs d’une plage de cellules.
Calculer la N-ième plus grande valeur d’une plage
Tout d’abord nous avons besoin d’accéder à la N-ième plus grande valeur de la plage de cellules Nous allons pour cela utiliser la fonction GRANDE.VALEUR qui s’utilise de la manière suivante:
=GRANDE.VALEUR(plage de cellules; N)
Cette formule renvoie alors la N-ième plus grande valeur. Dans l’exemple ci-dessous, la formule =GRANDE.VALEUR(A1:A15;2) renvoie donc la 2e plus grande valeur, c’est à dire 18.
Calculer une liste des N plus grandes valeurs
Nous allons maintenant améliorer la formule pour renvoyer non pas une seule valeur mais la liste des N plus grandes valeurs. Comme on souhaite avoir comme résultat une liste, on va passer par une formule matricielle. Pour obtenir la liste des N plus grandes valeurs, on va remplacer dans la formule ci-dessus N par une liste des nombres entiers de 1 à N: {1;2; … ;N} Ainsi la formule pour renvoyer les 3 plus grandes valeurs devient
{=GRANDE.VALEUR(A1:A15;{1;2;3})}
On voit que le vecteur renvoyé est bien de taille 3 et renvoie dans l’ordre la plus grande, puis la 2e plus grande valeur, etc. Le problème de cette formule est qu’il faut écrire manuellement la liste des entiers de 1 à N. Comment faire pour calculer automatiquement cette liste? Nous allons utiliser une astuce concernant le nom des lignes du tableau Excel, que l’on peut nommer par leur numéro. Nous allons alors utiliser la fonction LIGNE:
{=LIGNE(1:12)} revient au même que d’écrire {1;2;3; … ;12}
La formule ci -dessous devient alors {=GRANDE.VALEUR(A5:A19;LIGNE(1:3))} , plus besoin d’écrire à la main tous les chiffres!
Effectuer un calcul sur les N plus grandes valeurs
Maintenant que l’on sait calculer la matrice des N plus grandes valeurs, il est facile de faire un calcul sur cette plage, par exemple la somme:
{=SOMME(GRANDE.VALEUR(A5:A19;LIGNE(1:3)))}
ou la moyenne:
{=MOYENNE(GRANDE.VALEUR(A5:A19;LIGNE(1:3)))}
Dans cet exemple, le calcul a été fait avec N=3 mais vous pouvez le réaliser avec n’importe quelle valeur. Attention, ce sont des formules matricielles, à valider avec Ctrl + Maj + Entrée si vous utilisez la formule finale sans stocker l’étape de calcul intermédiaire dans le classeur.
