fbpx

Faire la somme ou la moyenne des N plus grandes valeurs

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.

utilisation de la formule grande valeur sur Excel

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})}

formule matricielle plus Grande.Valeur

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)))}

calculer la moyenne des plus grande valeurs

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.

About Docteur Excel
Tu souffres sur Excel ? Ça se soigne ! Un traitement de choc pour tous tes problèmes de tableur! Docteur Excel t'apprends à manipuler Excel avec une précision chirurgicale pour gagner un temps fou, éblouir ton patron et devenir un pro du tableur.