marrakeshh - Fotolia
Excel : comment extraire les 3 plus grandes valeurs d'un tableau avec une simple formule
S'il est simple de trouver la plus grande (ou plus petite) valeur, il est plus difficile de trouver les deux ou trois plus importantes (ou plus faibles). C'est là que les fonctions LARGE et SMALL vous aideront.
La plupart des utilisateurs d'Excel n'ont aucun problème pour faire la somme ou la moyenne d'une colonne de nombres. Les fonctions SUM() et AVERAGE() sont parmi les plus célèbres du tableur de Microsoft.
Il en est presque de même lorsqu'il s'agit de trouver la valeur numérique la plus grande ou la plus petite, même si les fonctions =MAX() et =MIN() sont un tout petit moins connues.
A titre d'exemple, nous allons illustrer nos formules sur le tableau de valeur suivant :
Si nous cherchons le plus gros chiffre d'affaires (colonne « revenue ») et le plus petit, il suffit d'injecter les valeurs de la colonne E dans ces deux formules.
Soit : =MAX(E2:E19) et =MIN (E2:E19).
Jusqu'ici, rien de bien sorcier.
Ceci dit, les choses deviennent beaucoup moins triviales lorsque l'on s'intéresse non plus à la plus grande valeur, mais aux deux plus grandes.
Si vous n'avez sous la main que les célèbres fonctions SUM, AVERAGE, MAX, MIN, COUNT et IF, vous pouvez essayer une formule du type =MAX(IF(E2:E19=MAX(E2:E19),0,E2:E19)).
Certes. Cela fonctionne.
Mais comment allez-vous faire pour trouver la troisième plus grande valeur ? Cette solution de contournement n'est pas des plus élégantes et surtout elle montre rapidement ses limites.
Alors, comment faire ?
Les fonctions LARGE et SMALL entrent en jeu
Et bien, utilisez la fonction =LARGE() !
Cette fonction d'Excel se présente sous la forme =LARGE(array,k). Le premier paramètre renvoie à la source des données à explorer. Le deuxième indique la position de la valeur (la k-ième) à renvoyer en résultat. En clair, la fonction donnera la plus grande valeur si k=1, la deuxième plus grande si k=2, la troisième plus grande si k=3, etc.
Pour paramétrer cette formule, vous avez en pratique deux options.
La première consiste à passer par une « colonne d'aide » avec les chiffres 1, 2, 3, ..., k, comme indiqué dans la colonne K6:K8 de la Figure 3 (ci-dessous).
Les colonnes d'aide simplifient les formules complexes ou les opérations difficiles. Vous pouvez les utiliser pour trier, exécuter des recherches à l'aide de critères multiples ou conjointement avec des formules qui renvoient les valeurs des lignes répondant à des conditions spécifiques.
Dans notre exemple, la colonne d'aide est utilisée pour incrémenter la valeur de k. Dans la case J6, on aura donc la formule =LARGE ($E$2:$E$19,K6). Ce qui retournera la plus grande valeur de E2:E19 puisque k=1.
Il n'y aucune différence avec la fonction MAX, me direz-vous... et vous aurez raison. Sauf que...
Sauf qu'il y aura une différence de taille lorsque vous copierez la formule de J6 aux cases J7 et J8. En incrémentant k, la formule renvoie dans ces deux cases les deuxième et troisième valeurs les plus importantes de la plage du chiffre d'affaires.
On utilisera exactement la même méthode avec la fonction qui permet d'extraire les valeurs les plus petites : =SMALL().
Éviter la colonne d'aide
La colonne d'aide est bien pratique, mais il se peut que vous souhaitiez vous en passer.
Pourquoi pas. C'est tout à fait possible.
Dans cette deuxième option, pour que l'argument k soit automatiquement incrémenté lorsque vous dupliquez la formule vers le bas, il vous faudra utilisez ROW(1:1) en paramètre.
Évidemment, 1:1 fait référence à la ligne 1. ROW(1:1) vaut donc 1. Il passera à 2:2 lorsque vous copierez la formule vers le bas (et k sera donc égal à 2).
En conclusion, bien que LARGE et SMALL ne figurent pas parmi les fonctions les plus connues d'Excel, ce sont deux très bonnes solutions pour extraire et utilisez les N valeurs les plus grandes et les plus petites dans une plage de données.
Bill Jelen, également connu sous le nom de « MrExcel », est consultant Microsoft Excel depuis 25 ans. Il est l'auteur du site MrExcel.com.
