Regardez la vidéo - Recherchez la deuxième, la troisième ou la énième valeur correspondante
Lorsqu'il s'agit de rechercher des données dans Excel, j'utilise souvent deux fonctions étonnantes - RECHERCHEV et INDEX (principalement en conjonction avec la fonction MATCH).
Cependant, ces formules sont conçues pour rechercher uniquement la première instance de la valeur de recherche.
Mais que se passe-t-il si vous souhaitez rechercher la deuxième, la troisième, la quatrième ou la Nième valeur.
Eh bien, c'est faisable avec un peu de travail supplémentaire.
Dans ce didacticiel, je vais vous montrer différentes manières (avec des exemples) de rechercher la deuxième ou la Nième valeur dans Excel.
Rechercher la deuxième, la troisième ou la énième valeur dans Excel
Dans ce didacticiel, je vais couvrir deux manières de rechercher la deuxième ou la Nième valeur dans Excel :
- Utilisation d'une colonne d'aide.
- Utilisation de formules matricielles.
Commençons et plongeons dedans.
Utilisation de la colonne d'assistance
Supposons que vous soyez un coordinateur de formation dans une organisation et que vous disposiez d'un ensemble de données comme indiqué ci-dessous. Vous voulez lister toutes les formations devant le nom d'un employé.
Dans l'ensemble de données ci-dessus, les employés ont reçu une formation sur différents outils Microsoft Office (Excel, PowerPoint et Word).
Maintenant, vous pouvez utiliser la fonction RECHERCHEV ou le combo INDEX/MATCH pour trouver la formation qu'un employé a terminée. Cependant, il ne retournera que la première instance correspondante.
Par exemple, dans le cas de John, il a suivi les trois formations, mais lorsque je recherche son nom avec RECHERCHEV ou INDEX/MATCH, il renverra toujours 'Excel', qui est la première formation pour son nom dans la liste .
Pour ce faire, nous pouvons utiliser une colonne d'aide et y créer des valeurs de recherche uniques.
Voici les étapes :
- Insérez une colonne avant la colonne qui répertorie la formation.
- Dans la cellule B2, entrez la formule suivante :
=A2&COUNTIF($A$2:$A2,A2)
- Dans la cellule F2, entrez la formule suivante et copiez-collez pour toutes les autres cellules :
=IFNA(RECHERCHEV($E2&COLONNES($F$1:F1),$B$2:$C$14,2,0),"")
La formule ci-dessus renverrait la formation pour chaque employé dans l'ordre dans lequel il apparaît sur la liste. S'il n'y a pas de formation répertoriée pour un employé, il renvoie un blanc.
Comment fonctionne cette formule ?
La formule COUNTIF dans la colonne d'aide rend le nom de chaque employé unique en y ajoutant un numéro. Par exemple, la première instance de John devient John1, la deuxième instance devient John2 et ainsi de suite.
La formule RECHERCHEV utilise désormais ces noms d'employés uniques pour trouver la formation correspondante.
Notez que $E2&COLUMNS($F$1:F1) est la valeur de recherche dans la formule. Cela ajouterait un numéro au nom de l'employé en fonction du numéro de colonne. Par exemple, lorsque cette formule est utilisée dans la cellule F2, la valeur de recherche devient « John1 ». Dans la cellule G2, il devient « John2 » et ainsi de suite.
Utilisation de la formule matricielle
Si vous ne souhaitez pas modifier l'ensemble de données d'origine en ajoutant des colonnes d'aide, vous pouvez également utiliser une formule matricielle pour rechercher la deuxième, la troisième ou la nième valeur.
Supposons que vous ayez le même ensemble de données que celui illustré ci-dessous :
Voici la formule qui renverra la valeur de recherche correcte :
=SIERREUR(INDICE($B$2:$B$14,PETIT(SI($A$2:$A$14=$D2, LIGNE($A$2:$A$14)-1,""),COLONNES($E$1 :E1))),"")
Copiez cette formule et collez-la dans la cellule E2.
Notez qu'il s'agit d'une formule matricielle et que vous devez utiliser Contrôle + Maj + Entrée (maintenez les touches Contrôle et Maj enfoncées et appuyez sur la touche Entrée), au lieu d'appuyer uniquement sur la touche Entrée.
Cliquez ici pour télécharger le fichier exemple.
Comment fonctionne cette formule ?
Décomposons cette formule en plusieurs parties et voyons comment elle fonctionne.
$A$2 :$A$14=$D2
La partie ci-dessus de la formule compare chaque cellule de A2:A14 avec la valeur de D2. Dans cet ensemble de données, il vérifie si une cellule contient ou non le nom « Jean ».
Il renvoie un tableau de VRAI à FAUX. Si la cellule porte le nom « John », ce serait Vrai, sinon ce serait Faux.
Vous trouverez ci-dessous le tableau que vous obtiendriez dans cet exemple :
{VRAI;FAUX;FAUX;FAUX;FAUX;FAUX;VRAI;FAUX;FAUX;FAUX;VRAI;FAUX;FAUX}
Notez qu'il a TRUE en 1ère, 7ème et 111ème position, car c'est là que le nom John apparaît dans l'ensemble de données.
SI($A$2:$A$14=$D2, LIGNE($A$2:$A$14)-1,””)
La formule SI ci-dessus utilise le tableau de TRUE et FALSE et remplace TRUE par la position de son occurrence dans la liste (donnée par ROW($A$2:$A$14)-1) et FALSE par "" (espaces). Voici le tableau résultant que vous obtenez avec cette formule SI :
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Notez que 1, 7 et 11 sont la position d'occurrence de John dans la liste.
PETIT(SI($A$2:$A$14=$D2, LIGNE($A$2:$A$14)-1,””),COLONNES($E$1:E1))
La fonction SMALL sélectionne maintenant le premier plus petit, deuxième plus petit, troisième plus petit nombre de ce tableau. Notez qu'il utilise la fonction COLUMNS pour générer le numéro de colonne. Dans la cellule E2, la fonction COLONNES renvoie 1 et la fonction PETIT renvoie 1. Dans la cellule F2, la fonction COLONNES renvoie 2 et la fonction PETIT renvoie 7.
INDEX($B$2:$B$14,PETIT(SI($A$2:$A$14=$D2, LIGNE($A$2:$A$14)-1,””),COLONNES($E$1:E1) ))
La fonction INDEX renvoie désormais la valeur de la liste dans la colonne B en fonction de la position renvoyée par la fonction SMALL. Par conséquent, dans la cellule E2, il renvoie « Excel », qui est le premier élément de B2:B14. Dans la cellule F2, il renvoie PowerPoint, qui est le 7e élément de la liste.
Comme il existe des cas où il n'y a qu'une ou deux formations pour certains employés, la fonction INDEX renverrait une erreur. La fonction SIERREUR est utilisée pour renvoyer un blanc à la place de l'erreur.
Notez que dans ces exemples, j'ai utilisé des références de plage. Cependant, dans des exemples pratiques, il est avantageux de convertir les données en un tableau Excel. En convertissant en un tableau Excel, vous pouvez utiliser des références structurées, ce qui facilite la création de formules. En outre, un tableau Excel peut automatiquement tenir compte de tous les nouveaux éléments de formation ajoutés à la liste (vous n'avez donc pas à ajuster les formules à chaque fois).
Que faites-vous lorsque vous devez rechercher la deuxième, la troisième ou la Nième valeur ? Je suis sûr qu'il y a plus de façons de le faire. Si vous utilisez quelque chose de plus simple que celui répertorié ici, partagez-le avec nous tous dans la section commentaires.
Cliquez ici pour télécharger le fichier exemple.
