Jim a décrit une situation où il a une liste de noms d’employés et de leurs salaires. Il veut déterminer qui sont les cinq employés les mieux payés. Il utilise la fonction LARGE pour identifier les cinq plus gros salaires, puis essaie d’utiliser RECHERCHEV pour renvoyer les noms appartenant à ces salaires. Cela fonctionne bien à moins qu’il y ait des doublons dans les cinq premiers salaires (les gens reçoivent le même salaire). S’il y en a, alors RECHERCHEV renvoie uniquement le nom du premier employé à ce salaire.

Pour renvoyer tous les noms propres, vous pouvez faire plusieurs choses.

Une méthode serait de contourner complètement l’utilisation d’une formule. Au lieu de cela, vous pouvez utiliser la fonction de filtre automatique dans Excel:

  1. Sélectionnez n’importe quelle cellule de votre tableau de données.

  2. Choisissez Données | Filtre | Filtre automatique. Excel ajoute des flèches déroulantes à droite de chaque en-tête de colonne dans le tableau.

  3. Utilisez la liste déroulante en haut de la colonne des salaires pour choisir Top 10. Excel affiche la boîte de dialogue Top 10 AutoFilter. (Voir la figure 1.)

  4. Réglez la commande centrale de 10 à 5.

  5. Cliquez sur OK. Excel affiche les cinq premiers salaires de la liste.

Lorsque vous suivez ces étapes, vous pouvez en fait vous retrouver avec plus de cinq enregistrements visibles, en particulier s’il existe des liens dans les salaires des employés. Le filtre identifie les cinq premiers salaires, puis affiche tous les enregistrements dont les salaires correspondent à ceux-ci.

Si vous ne souhaitez pas utiliser le filtre automatique, une autre option consiste simplement à vous assurer qu’il existe quelque chose d’unique dans chacun des enregistrements de votre liste d’employés. Par exemple, si les noms des employés sont dans la colonne B et les salaires dans la colonne C, vous pouvez utiliser la formule suivante dans la colonne A pour rendre chaque enregistrement unique:

=C2+ROW()/100000000

Cela ajoutera le numéro de ligne divisé par 100 000 000 et créera une valeur unique. Si vous avez (par exemple) des salaires identiques de 98 765,43 dans les lignes 2 et 49 dans la colonne A, ils seront:

98765.43000002 98765.43000049

Le grand nombre (100 000 000) est tel que si vous aviez un nombre identique à la ligne 65536, vous obtiendrez:

98765.43065536

Et même dans ce cas, la valeur arrondie à 2 décimales serait toujours le nombre réel. Si LARGE et VLOOKUP sont effectués avec les valeurs «non uniques» de la colonne A, vous retournerez les salaires les plus élevés (et leurs personnes associées), en fonction de la position de la personne dans la liste.

Une troisième approche consiste à utiliser les fonctions RANK et COUNTIF pour renvoyer un «classement» unique pour chaque valeur de la liste des salaires. Si les salaires se situent dans la plage B1: B50, saisissez ce qui suit dans la cellule C1 et copiez-la dans la plage:

=RANK(B1,$B$1:$B$50)+COUNTIF($B$1:B1,B1)-1

Vous pouvez désormais utiliser INDEX sur les valeurs de classement pour renvoyer le nom associé à chaque salaire.

Enfin, une quatrième approche consiste à créer une macro qui peut renvoyer les informations souhaitées. Il existe de nombreuses manières d’implémenter une macro; ce qui suit n’est que l’un d’entre eux:

Function VLIndex(vValue, rngAll As Range, _   iCol As Integer, lIndex As Long)

Dim x As Long     Dim lCount As Long     Dim vArray() As Variant     Dim rng As Range     On Error GoTo errhandler

Set rng = Intersect(rngAll, rngAll.Columns(1))

ReDim vArray(1 To rng.Rows.Count)

lCount = 0     For x = 1 To rng.Rows.Count         If rng.Cells(x).Value = vValue Then             lCount = lCount + 1             vArray(lCount) = rng.Cells(x).Offset(0, iCol).Value         End If     Next x

ReDim Preserve vArray(1 To lCount)

If lCount = 0 Then         VLIndex = CVErr(xlErrNA)

ElseIf lIndex > lCount Then         VLIndex = CVErr(xlErrNum)

Else         VLIndex = vArray(lIndex)

End If errhandler:

If Err.Number <> 0 Then VLIndex = CVErr(xlErrValue)

End Function

Les paramètres passés à cette fonction définie par l’utilisateur sont la valeur, la plage de cellules à rechercher, le « décalage » de cette plage pour la recherche (le nombre de colonnes à droite est positif, à gauche est négatif) et le numéro du duplicata (1 est la première valeur, 2 la seconde, et ainsi de suite).

Pour l’utiliser, par exemple, supposons que A1: B1 contient des en-têtes de colonne, A2: A100 contient les salaires et B2: B100 contient les noms des employés.

Dans la cellule E2, vous pouvez saisir ce qui suit pour déterminer le salaire le plus élevé du tableau:

=LARGE($A$2:$A$100,ROW()-1)

Dans la cellule F2, vous pouvez entrer la formule suivante pour déterminer si la ligne a des doublons et garder une trace de la « valeur » actuelle de ce doublon:

=IF(E2=E1,1+F1,1)

Dans la cellule G2, vous pouvez utiliser la formule suivante, qui appelle la fonction définie par l’utilisateur:

=VLIndex(E2,$A$2:$A$100,1,F2)

Copiez les cellules E2: G2 à E3: G6, et vous aurez (dans la colonne G) les noms des employés ayant les cinq plus gros salaires.

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (3077) s’applique à Microsoft Excel 97, 2000, 2002 et 2003.