Valeurs maximales et minimales avec LARGE et SMALL

MAX vous donne la valeur unique la plus grande et MIN la plus petite. Mais que faire si vous voulez la 2e valeur la plus élevée ou la 3e valeur la plus basse ?

C’est exactement le rôle de LARGE et SMALL. Elles vous permettent d’extraire la n-ième valeur la plus grande ou la n-ième valeur la plus petite d’une plage, ce qui constitue la base des rapports sur les premiers ou derniers N éléments.

LARGE(range, k) renvoie la k-ième plus grande valeur. Le deuxième argument k indique la position : 1 correspond à la plus grande valeur, 2 à la deuxième plus grande, et ainsi de suite.

Ainsi, LARGE(A2:A20,1) est égal à MAX(A2:A20), tandis que LARGE(A2:A20,2) renvoie la deuxième valeur la plus élevée.

SMALL(range, k) fonctionne comme LARGE en partant du bas. SMALL(A2:A20,1) est égal à MIN(A2:A20), et SMALL(A2:A20,3) renvoie la troisième plus petite valeur.

Utilisez SMALL pour faire ressortir les moins bons résultats, les options les moins coûteuses ou les dates les plus anciennes d’un ensemble de données.

Pour répertorier les trois premières valeurs, empilez trois formules LARGE avec k = 1, 2 et 3. Une méthode plus propre consiste à utiliser une cellule contenant un nombre auxiliaire afin de pouvoir recopier la formule vers le bas.

Si la cellule D2 contient 1, D3 contient 2 et D4 contient 3, cette formule unique, recopiée vers le bas, produit la liste complète des trois premières valeurs.

Vous pouvez générer automatiquement k avec la fonction ROW et éviter ainsi une colonne auxiliaire. ROW()-1 produit 1 sur la première ligne, 2 sur la suivante, et ainsi de suite.

Recopiée vers le bas, cette formule crée une liste classée sans configuration supplémentaire. Ajustez le décalage en fonction de l’emplacement de début de votre liste.

Dans les versions modernes d’Excel et de Google Sheets, vous pouvez déployer les cinq premières valeurs avec une seule formule en fournissant SEQUENCE comme argument k. SEQUENCE(5) produit 1, 2, 3, 4 et 5.

LARGE renvoie ensuite cinq valeurs à la fois et les déploie automatiquement vers le bas. Une seule formule pour obtenir une liste complète classée.

Une question fréquente en entreprise est la suivante : quelle contribution mes trois principaux clients apportent-ils ? Entourez LARGE de SUM avec une SEQUENCE (ou une constante matricielle) pour totaliser les premières valeurs.

La formule ci-dessous additionne les trois plus grandes valeurs de la plage et vous donne leur contribution combinée dans une seule cellule.

Si k est inférieur à 1 ou supérieur au nombre de nombres présents dans la plage, LARGE et SMALL renvoient l’erreur #NUM!.

Par exemple, demander la 30e plus grande valeur dans une liste qui ne contient que 19 nombres échoue. Dans vos rapports, évitez ce problème en entourant l’appel de IFERROR.

Une valeur seule est moins utile que de savoir qui l’a obtenue. Combinez LARGE avec INDEX et MATCH pour récupérer le nom associé à la valeur la plus élevée.

Cette formule trouve le chiffre d’affaires le plus élevé, localise sa ligne et renvoie le nom correspondant de la colonne A ; votre rapport affiche ainsi « Meilleure vendeuse : Maria. »

Comme les autres fonctions statistiques, LARGE et SMALL ne comptent que les nombres. Le texte et les cellules vides de la plage sont ignorés ; ils n’influencent donc pas la valeur classée en n-ième position.

Vous pouvez ainsi les appliquer sans risque à une colonne qui comprend une ligne d’en-tête. Faites simplement attention aux nombres accidentellement stockés sous forme de texte : ils sont ignorés et peuvent modifier la valeur renvoyée par LARGE ou SMALL.

Vous avez les ventes mensuelles dans B2:B20. La direction souhaite connaître les trois meilleurs et les trois moins bons mois.

• Les trois meilleurs : =LARGE($B$2:$B$20,{1,2,3})
• Les trois moins bons : =SMALL($B$2:$B$20,{1,2,3})

Placez chaque formule dans une cellule capable de déployer son résultat : vous obtenez instantanément une synthèse des valeurs les plus hautes et les plus basses pour alimenter votre tableau de bord.

Testez votre compréhension de LARGE et SMALL.

Vous pouvez maintenant aller au-delà de MAX et MIN :

• LARGE(range,k) renvoie la k-ième plus grande valeur ; SMALL(range,k), la k-ième plus petite.
• k = 1 correspond à MAX ou MIN ; augmentez k pour obtenir les valeurs suivantes.
• Fournissez SEQUENCE ou un tableau tel que {1,2,3} pour créer ou additionner une liste des premiers N éléments avec une seule formule.
• Associez ces fonctions à INDEX-MATCH pour libeller la valeur et entourez-les de IFERROR pour gérer les valeurs de k hors plage.