RECHERCHEV: recherche le nième élément (sans colonnes d’assistance)
Dans cette série, Mastering VLOOKUP, nous avons couvert beaucoup de terrain. Mais ce post est l’un des plus diaboliques de tous. VLOOKUP, de par sa conception, est destiné à renvoyer la première correspondance trouvée. Mais, si nous voulons le 2e, 3e ou nième RECHERCHEV, ce n’est pas si facile. Souvent, les gens ont recours à des colonnes d’aide, mais ce n’est vraiment pas nécessaire (et loin d’être aussi amusant!)
Regardez la capture d’écran ci-dessous. Nos données sont triées par ordre d’âge, mais comment pourrions-nous trouver l’âge du 3e Dave dans la liste?
La formule dans la cellule E6 est:
{=VLOOKUP(SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),CHOOSE({1,2},
ROW(A2:A8),B2:B8),2,0)} Tout d’abord – c’est une formule matricielle. Ne tapez pas le {} au début et à la fin, mais entrez la formule avec Ctrl + Maj + Entrée et Excel les ajoutera automatiquement.
Deuxièmement – oui, cette formule fait peur! Mais restez avec moi, je ferai de mon mieux pour l’expliquer.
La clé de cette formule fonctionne correctement dans la fonction SMALL avec la fonction ROW. Construisons la formule petit à petit.
Fonction IF
{=VLOOKUP(SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),CHOOSE({1,2},
ROW(A2:A8),B2:B8),2,0)} La section en surbrillance rouge est presque une fonction IF standard. Cependant, il travaille sur une plage de cellules plutôt que sur une seule cellule. Si une cellule dans A2-A8 est égale à la valeur de recherche, Dave dans notre exemple, le numéro de ligne de cette cellule est renvoyé. Si le nom n’est pas Dave, FALSE est renvoyé. Le nom Dave est dans les rangées 3, 4 et 8, donc la fonction IF renverrait un tableau de {FALSE, 3, 4, FALSE, FALSE, FALSE, 8}.
SMALL Fonction
{=VLOOKUP(SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),CHOOSE({1,2},
ROW(A2:A8),B2:B8),2,0)} Nous avons encapsulé le résultat de la fonction IF dans la fonction SMALL. La cellule E4 est la nième valeur que nous recherchons. Dans notre exemple, E4 est 3, car nous recherchons la 3ème recherche. Cela recherchera maintenant la troisième plus petite valeur du tableau de fonctions IF renvoyé ci-dessus. Le troisième plus petit résultat du tableau est 8; {FALSE, 3,4, FALSE, FALSE, FALSE,8}.
Ceci termine le premier argument de la fonction RECHERCHEV; la valeur de recherche est 8. Même si nous recherchons le 3ème RECHERCHEV de Dave, le 3ème Dave est dans la 8ème ligne. Donc, nous recherchons 8. Espérons que ce n’est pas trop déroutant.
CHOISIR la fonction
La fonction CHOOSE fonctionne de la même manière que celle que nous avons rencontrée lors d’une RECHERCHEV vers la gauche. Cependant, nous utiliserons le numéro de ligne comme colonne de recherche.
Dans la fonction CHOOSE, les nombres dans {} déterminent quelle colonne est laquelle.
{=VLOOKUP(SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),CHOOSE({1,2},
ROW(A2:A8),B2:B8),2,0)} La colonne 1, la colonne de recherche, est simplement le numéro de ligne. Cela renverrait un tableau de {2, 3, 4, 5, 6, 7, 8}. Notre valeur de recherche est 8, donc notre RECHERCHEV sera en mesure de trouver une valeur.
{=VLOOKUP(SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),CHOOSE({1,2},
ROW(A2:A8),B2:B8),2,0)}
Fonction RECHERCHEV {=VLOOKUP(SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),CHOOSE({1,2}, ROW(A2:A8),B2:B8),2,0)}
La fonction RECHERCHEV est utilisée pour effectuer une correspondance exacte de la ligne numéro 8 et renvoyer la valeur de la colonne 2 (cellules B2-B8).
Le résultat
Le résultat de notre formule est 47. Nous pouvons adapter cette formule pour trouver la nième recherche à partir de n’importe quel ensemble de données.
Cela a été difficile d’y aller pendant un moment, mais j’espère que vous avez réussi.
Existe-t-il un moyen plus efficace?
Oui il y a. Une fois que nous avons obtenu le numéro de ligne d’une valeur correspondante, il serait probablement plus facile d’appliquer une formule INDEX plutôt qu’une RECHERCHEV. Mais le fait est que VLOOKUP est une option possible. Si RECHERCHEV est l’une des rares fonctions que vous connaissez, alors il l’utilise.
Caractères d’espacement
Nous pourrions / devrions utiliser des caractères d’espacement dans cette formule. Bien qu’il soit très peu probable de renvoyer un résultat incorrect avec notre exemple, cela pourrait être possible avec d’autres ensembles de données, il est donc préférable d’utiliser des caractères d’espacement. Compte tenu de la complexité de l’exemple, j’ai décidé de les laisser de côté. Si nous les voulions, la formule serait:
{=VLOOKUP(E3&"^"&SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),
CHOOSE({1,2},A2:A8&"^"&ROW(A2:A8),B2:B8),2,0)} Colonnes d’assistance
Si cet exemple semble trop complexe, l’option la plus simple est simplement d’utiliser une colonne d’aide.
La formule en B2 est:
=A2&"^"&COUNTIF($A$2:A2,A2)
Copiez cette formule en B8. Alors la formule en F6 pourrait être:
=VLOOKUP(F3&"^"&F4,B2:C8,2,0)
Facile.
N’oubliez pas:
Si vous avez trouvé cet article utile ou si vous avez une meilleure approche, veuillez laisser un commentaire ci-dessous.
Avez-vous besoin d’aide pour l’adapter à vos besoins?
Je suppose que les exemples de cet article ne correspondaient pas exactement à votre situation. Nous utilisons tous Excel différemment, il est donc impossible d’écrire un article qui répondra aux besoins de chacun. En prenant le temps de comprendre les techniques et principes de cet article (et ailleurs sur ce site) vous devriez pouvoir l’adapter à vos besoins.
Mais si vous avez encore du mal :
Et ensuite?
N’y allez pas encore, il y a beaucoup plus à apprendre sur comment-supprimer. Consultez les derniers articles:
