Moyenne des N valeurs les plus grandes

Calculer la moyenne sans condition est une opération très facile à effectuer et nous l’avons déjà expliquée en utilisant la fonction Moyenne dans l’article nommé : Calculer la moyenne des données filtrées ou non-filtrées en utilisant la fonction MOYENNE

Aujourd’hui nous allons voir dans cette formation Excel comment calculer la moyenne des N valeurs les plus grandes (par exemple la moyenne des cinq valeurs les plus grandes ou la moyenne des dix valeurs les plus grandes) et ceci en utilisant la fonction Moyenne.

Voyons ces deux exemples, et découvrons ensemble comment les choses se dérouleront dans le reste de l’article :

Calculer la moyenne des cinq valeurs les plus grandes

Dans l’exemple suivant, nous avons dix nombres non triés dans la plage de cellules A1:A10 :

• Téléchargez le classeur exemple ici : Moyenne des N valeurs les plus grandes
• Ouvrez le classeur téléchargé et sélectionnez une cellule vide, par exemple C2
• Entrez la formule suivante : =MOYENNE(GRANDE.VALEUR(A1:A10; {1;2;3;4;5}))
• Et appuyez sur Ctrl+Maj+Entrée puisqu’il s’agit d’une formule matricielle.
• Excel affiche 362

Calculer la moyenne des 10 valeurs les plus grandes

• Passez maintenant à la deuxième feuille du classeur ouvert.
• Entrez la même formule précédente mais cette fois en remplaçant la plage de cellules A1:A10 par A1:A20, et {1;2;3;4;5} par LIGNE(1:10).
• Votre formule s’écrira comme ça : =MOYENNE(GRANDE.VALEUR(A1:A20;LIGNE(1:10)))
• Composez ensuite Ctrl+Maj+Entrée.
• Excel vous affiche 3221,2

Explication des formules utilisées:

Comment trouver les cinq valeurs les plus grandes dans une plage de cellules ?

Pour calculer donc la moyenne des cinq valeurs les plus grandes dans une plage de cellules, Excel suit la procédure suivante :

• Il doit d’abord localiser les cinq grandes valeurs puis en calculer la moyenne.

C’est pour cela que nous avons utilisé la fonction GRANDE.VALEUR et nous l’avons intégrée dans la fonction MOYENNE.

Comment utiliser la fonction GRANDE.VALEUR ?

La fonction GRANDE.VALEUR est une fonction qui permet de trouver la valeur la plus élevée dans une plage de cellule.

Sa syntaxe s’écrit comme ça : GRANDE.VALEUR(matrice,k)

• Matrice : La plage de cellules dans laquelle vous cherchez la k-ième plus grande valeur.
• K : représente la position de la valeur recherchée en partant de la valeur la plus grande.

Exemple

• Sélectionnez la première feuille du classeur téléchargé, et entrez cette formule dans la cellule D1

=GRANDE.VALEUR(A1:A10;2)

• Ici, on demande à Excel de nous renvoyer la deuxième valeur la plus grande dans la plage de cellules A1:A10 ; ce qui donne comme résultat 550.

Revenons maintenant à notre exemple de début et cherchons les cinq valeurs les plus grandes en utilisant GRANDE.VALEUR :

• On doit apporter une petite modification au deuxième argument de la fonction GRANDE.VALEUR pour obtenir le résultat cherché, et ceci en entrant une matrice écrite de la façon suivante : {1;2;3;4;5}.
• Dans ce cas Excel va renvoyer le résultat sous forme de tableau de cinq lignes et d’une colonne.

Tout d'abord, suivez l'exemple suivant pour tester l’utilisation de la matrice {1;2;3;4;5} :

• Sélectionnez cinq cellules verticalement D1:D5, et tapez dans la barre de formule : ={1;2;3;4;5} puis appuyez sur Ctrl+Maj+Entrée

• Vous obtenez donc un tableau contenant des chiffres de 1 à 5.

Alors, en utilisant la fonction GRANDE.VALEUR de la façon suivante : =GRANDE.VALEUR(A1:A10; {1;2;3;4;5}) et dans la même plage de cellules D1:D5, Excel va chercher les cinq grandes valeurs dans la plage de cellules A1:A10 et va les afficher triées de la première grande valeur à la  cinquième grande valeur.

• Dans la cellule D1, Excel affichera la première grande valeur.
• Dans la cellule D2, il affichera la deuxième grande valeur…
• Et ainsi de suite jusqu’à la cinquième grande valeur.

Sélectionnez donc cinq cellules verticalement comme vous l’avez fait avant (D1:D5) et entrez la formule précédente puis tapez Ctrl+Maj+Entrée

Remarquez donc qu’Excel affiche les nombres trouvés et les trie du plus grand au plus petit comme nous l’avons mentionné.

Il ne reste à ce stade qu’introduire la fonction MOYENNE pour calculer la moyenne de ces valeurs trouvées.

• Entrez enfin la formule suivante =MOYENNE(GRANDE.VALEUR(A1:A10; {1;2;3;4;5}))
• Et eppuyez sur Ctrl+Maj+Entrée
• Voilà donc le résultat souhaité.

Passons à présent à la deuxième formule pour Calculer la moyenne des 10 valeurs les plus grandes

La formule utilisée était =MOYENNE(GRANDE.VALEUR(A1:A20;LIGNE(1:10)))

C’est pareil à la première formule, à l’exception d’une petite différence, outre la référence de la plage de cellules, qui est l’intégration de la fonction LIGNE à la place de la matrice {1;2;3;4;5}.

En effet, dans cet exemple nous voulons calculer la moyenne des dix plus grandes valeurs, alors nous devons utiliser la matrice {1;2;3;4;5;6;7;8;9;10} . Cette écriture pareille trop longue. (N'est-ce pas !!)

Alors pour éviter cette façon d’écrire la matrice et surtout si le nombre des valeurs recherchées est élevé, on a recouru à l’utilisation de la fonction LIGNE

Comment utiliser la fonction LIGNE ?

La fonction LIGNE renvoie le numéro de ligne d’une référence.

• Sa syntaxe est : LIGNE([référence])

Référence : peut référencer une cellule, une plage de cellules ou une matrice (tableau).

Exemple

• Sélectionnez la feuille 2 du classeur téléchargé.
• Tapez dans la cellule D2 la formule suivante : =LIGNE(A2)

• Excel renvoie donc le numéro de la cellule référencée qui est 2.

Sélectionnez maintenant la plage de cellules D1:D10 et entrez la formule suivante dans la barre de formule =LIGNE(1:10) pour connaître ce qu'elle va donner.

Puis tapez Ctrl+Maj+Entrée

Excel affiche donc un tableau vertical de dix chiffres consécutifs de 1 à 10.

Ce tableau, sera utilisé comme deuxième argument de la fonction GRANDE.VALEUR pour chercher les 10 valeurs les plus élevées dans la plage de cellules A1:A20

• Sélectionnez la plage de cellules D1:D10 et tapez dans la barre de formule la formule suivante : =GRANDE.VALEUR(A1:A20;LIGNE(1:10))
• Appuyez ensuite sur Ctrl+Maj+Entrée
• Voici le résultat affiché

• A l’instar du premier exemple des 5 valeurs les plus grandes, introduisez à présent la fonction MOYENNE et tapez dans la cellule C3 :

=MOYENNE(GRANDE.VALEUR(A1:A20;LIGNE(1:10)))

• Et voici le résultat donné.

Note : dans la barre de formule sélectionnez la partie LIGNE(1:10) et tapez F9, vous découvrez donc les valeurs renvoyées par la fonction LIGNE et qui sont utilisées dans cette formule.

PARTAGEZ PARTAGEZ PARTAGEZ