Comment extraire les plus petites et plus grandes valeurs avec Excel ? Les fonctions PETITE.VALEUR() et GRANDE.VALEUR()
Dans ce tutoriel, nous allons découvrir deux fonctions d’Excel qui permettent d’établir un classement des plus petites et plus grandes valeurs contenues dans une plage de cellules : il s’agit des fonctions PETITE.VALEUR() et GRANDE.VALEUR().
Restez bien jusqu’à la fin, car nous allons également découvrir comment effectuer cette extraction en tenant compte de critères de filtre des données directement inclus dans ces fonctions.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Introduction
Si vous vous rappelez bien, dans un tutoriel précédent, nous avions eu l’occasion de découvrir la fonction RANG() d’Excel.
Celle-ci permet d’identifier rapidement la position d’une valeur parmi un ensemble de nombres.
=RANG(nombre;référence;ordre)
Cette fonction attend trois arguments :
- Nombre : il s’agit du nombre pour lequel nous souhaitons connaître la position,
- Référence : il s’agit de l’ensemble des données dans lequel se trouve la valeur Nombre et pour lequel nous souhaitons connaître la position
- Ordre : Permet de définir l’ordre d’établissement du classement. En effet, par défaut, le classement s’effectue dans l’ordre décroissant, c’est-à-dire que la plus grande des valeurs va ressortir en première position. Pour inverser cet ordre et donc classer la plus petite des valeurs en première position, il faut donner la valeur « 1 » à l’argument ordre.
=RANG(29;D9:D35;1)
Maintenant, nous allons souhaiter effectuer le traitement inverse, c’est-à-dire que nous voulons connaître la valeur qui arrive à une position donnée.
2. Connaître la plus grande des valeurs
Excel nous propose directement connaître les valeurs situées en n-ième position parmi une série de nombres.
La première de ces formules, qui permet de connaître les valeurs les plus importantes est la fonction GRANDE.VALEUR().
=GRANDE.VALEUR(matrice;k)
Celle-ci attend donc deux arguments :
- Matrice : il s’agit de la matrice dans laquelle se trouvent les données pour lesquelles nous souhaitons extraire les plus grandes valeurs. Il peut s’agir dans le cas le plus simple d’une plage de cellules, ou alors directement d’une matrice saisie entre accolades
- k : est la position de la valeur que nous souhaitons extraire au sein de la matrice
Voici un exemple d’utilisation de la fonction GRANDE.VALEUR() avec une matrice
=GRANDE.VALEUR({1;2;3;4;5};1)
Et plus simplement avec une plage de cellules :
=GRANDE.VALEUR(D9:D25;1)
Ici, nous récupérons l’âge de la personne la plus âgée en donnant la valeur de 1 à l’argument k.
Bien entendu, si notre objectif n’est que connaître que la plus grande valeur de la base, il sera plus simple d’utiliser la fonction MAX() :
=MAX(D9:D25)
Cette fonction est plus simple à utiliser, mais ne permet de récupérer qu’un seul élément.
La force de la fonction GRANDE.VALEUR() est en effet pour permettre d’établir un classement, en faisant varier la valeur de l’argument k:
=GRANDE.VALEUR($D$9:$D$25;I16)
=GRANDE.VALEUR($D$9:$D$25;I17)
=GRANDE.VALEUR($D$9:$D$25;I18)
Ici, pour simplifier la rédaction de la formule, nous passons la référence à la plage des cellules de l’argument matrice en tant que référence absolue, en appuyant sur la touche [F4] du clavier.
Cela permet ensuite de pouvoir étendre facilement la formule sur les cellules du dessous en utilisant la poignée de recopie.
Pour en savoir plus sur les différents types de références, vous pouvons consulter le tutoriel dédié en cliquant ici.
3. Connaître la plus petite des valeurs
Maintenant que nous savons comment utiliser la fonction GRANDE.VALEUR(), voyons comment utiliser la fonction inverse, qui permettra donc de récupérer les valeurs les plus petites contenues dans une base de données : la fonction PETITE.VALEUR() :
=PETITE.VALEUR(matrice;k)
Ces deux fonctions s’utilisent exactement de la même manière.
Nous commençons donc par renseigner les valeurs au sein desquelles nous souhaitons effectuer l’extraction, que celles-ci soient présentées sous la forme d’une matrice, ou d’une plage de cellules.
Puis l’argument k permet de renseigner la position de la valeur à extraire :
=PETITE.VALEUR(D9:D25;1)
Comme nous l’avons dans la partie précédente, nous pourrons ici encore utiliser la fonction MIN() :
=MIN(D9:D25)
Mais ici aussi, nous ne pourrons récupérer que la plus petite des valeurs.
4. Comment extraire les plus petites et plus grandes valeurs en fonction d’un critère
Maintenant que nous savons comment extraire les plus grandes ou plus petites valeurs au sein d’une base de données, voyons comment ajouter un critère dans cette analyse.
Pour cela, revenons sur notre base de données des employés d’une entreprise.
Dans celle-ci, nous allons tout d’abord souhaiter extraire l’âge de la femme la plus jeune.
Pour cela, nous pourrions envisager d’utiliser une colonne supplémentaire, dans laquelle nous insérerions la formule suivante :
=SI(C9="F";D9)
Ici, nous souhaitons ne récupérer dans cette colonne que l’âge des femmes, lesquelles sont identifiées par une lettre « F » dans la colonne « Sexe ».
Dans cette formule, nous utilisons une particularité de la fonction SI() qui prévoit que si le troisième argument valeur_si_faux est omis, alors la fonction SI() retourne comme valeur le booléen FAUX.
De cette manière, lorsqu’il s’agira d’un homme, nous récupérerons systématiquement des FAUX.
Nous pouvons maintenant utiliser la fonction PETITE.VALEUR() sur cette nouvelle colonne :
=PETITE.VALEUR(F9:F25;1)
La femme la plus jeune a donc 26 ans.
Cette solution est donc très simple à mettre en place, mais n’est pas la plus pratique, car elle nécessite d’insérer une colonne dans la feuille de calculs, ce qui ne sera pas toujours possible.
Voyons donc maintenant comment obtenir la même information, en utilisant une unique formule.
Pour cela nous allons devoir construire la matrice utilisée dans la fonction PETITE.VALEUR() afin de récupérer des résultats identiques à ce que nous avons construit dans la colonne « Âge femme ».
Nous avions déjà eu l’occasion d’aborder la notion de construction de matrice à intégrer dans une fonction lorsque nous avions découvert la forme avancée de la fonction SOMMEPROD().
La formule est alors la suivante
=GRANDE.VALEUR((C9:C25="F")*D9:D25;1)
Nous commençons donc rechercher les éléments de la matrice pour lesquels la valeur de la colonne sexe est égale à « F ».
Excel aura donc en mémoire une matrice composée de VRAI et de FAUX.
Puis, nous multiplions cette matrice par les valeurs de la colonne « Âge ».
Lors de cette multiplication, Excel va conserver uniquement les âges de femmes et remplacer les valeurs concernant les âges des hommes par FAUX.
Le mécanisme est identique celui découvert un peu plus tôt avec la colonne « Âge femmes ».