Dans le post précédent de cette série, Mastering VLOOKUP, nous avons appris comment rechercher le 2ème, 3ème, 4ème ou nième élément d’un ensemble de données. Aujourd’hui, nous allons développer cela en examinant comment renvoyer toutes les valeurs correspondantes dans l’ensemble de données. De plus, nous combinerons cela avec des correspondances partielles, afin de pouvoir créer une fonction de recherche puissante et complète.
RECHERCHEV est génial lorsque vous avez des données uniques, mais cela ne se produit pas toujours. Revenons à un exemple utilisé dans un article précédent, regardez la capture d’écran ci-dessous.
Il y a 4 personnes différentes appelées Paul qui travaillent pour votre entreprise. La dernière fois que nous avons utilisé cet exemple, nous connaissions le nom de famille de Paul. Imaginez que cette fois, quelqu’un a demandé le numéro de téléphone de Paul, mais ils ne se souviennent pas de son nom de famille. Quel Paul? Un RECHERCHEV de base en lui-même serait inutile car seul le premier numéro de téléphone de Paul, Paul Daniels, serait retourné. Nous devons être en mesure de répertorier tous les éléments correspondants.
Nous pouvons utiliser la formule introduite dans le post précédent pour renvoyer une liste complète de tous les Paul.
La cellule G7 comprend la formule suivante:
{=IFERROR(VLOOKUP(SMALL(IF(IF(ISERROR(SEARCH($F$3,$A$2:$A$8&" "&$B$2:$B$8)),
FALSE,TRUE),ROW($A$2:$A$8)),$E7),CHOOSE({1,2},ROW($A$2:$A$8),
$C$2:$C$8),2,0),"")} Cette formule a été copiée dans G8-G11.
Décomposons cette formule petit à petit.
{=IFERROR(VLOOKUP(SMALL(IF(IF(ISERROR(SEARCH($F$3,$A$2:$A$8&" "&$B$2:$B$8)),
FALSE,TRUE),ROW($A$2:$A$8)),$E7),CHOOSE({1,2},ROW($A$2:$A$8),
$C$2:$C$8),2,0),"")} N’oubliez pas qu’il s’agit d’une formule matricielle, vous devez donc utiliser Ctrl + Maj + Entrée pour utiliser la formule.
Fonction RECHERCHE
{=IFERROR(VLOOKUP(SMALL(IF(IF(ISERROR(SEARCH($F$3,$A$2:$A$8&" "&$B$2:$B$8)),
FALSE,TRUE),ROW($A$2:$A$8)),$E7),CHOOSE({1,2},ROW($A$2:$A$8),
$C$2:$C$8),2,0),"")} La fonction RECHERCHE est utilisée pour rechercher un morceau de texte dans un autre morceau de texte. La principale différence entre SEARCH et FIND est que SEARCH n’est pas sensible à la casse. La cellule F3 contient la valeur de recherche; “Paul”.
Les cellules A2-A8 sont combinées avec les cellules B2-B8 pour créer un nom complet. Le tableau des noms {Paul Daniels, Jack Daniels, Paul Newman, Paul McCartney, Jeff Daniels, Paul Simon, Anthony Daniels}. Le résultat de la fonction SEARCH est la position de la chaîne correspondante. Si aucune correspondance n’est trouvée, #VALUE! l’erreur est renvoyée. Lors de l’utilisation de “Paul” comme exemple, il a renvoyé le résultat est {1, #VALUE !, 1, 1, #VALUE !, 1, #VALUE!}
Fonction ISERROR
{=IFERROR(VLOOKUP(SMALL(IF(IF(ISERROR(SEARCH($F$3,$A$2:$A$8&" "&$B$2:$B$8)),
FALSE,TRUE),ROW($A$2:$A$8)),$E7),CHOOSE({1,2},ROW($A$2:$A$8),
$C$2:$C$8),2,0),"")} La fonction ISERROR entoure le résultat renvoyé de la fonction SEARCH. Cette fonction transforme toute erreur en TRUE et toute non-ERREUR en FALSE. Le résultat renvoyé est maintenant converti en {FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, TRUE}
Fonction IF
{=IFERROR(VLOOKUP(SMALL(IF(IF(ISERROR(SEARCH($F$3,$A$2:$A$8&" "&$B$2:$B$8)),
FALSE,TRUE),ROW($A$2:$A$8)),$E7),CHOOSE({1,2},ROW($A$2:$A$8),
$C$2:$C$8),2,0),"")} La fonction double IF est utilisée pour inverser les valeurs TRUE / FALSE, puis si TRUE récupère le numéro de ROW.
La fonction IF entourant ISERROR renverra {TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, FALSE}. Alors IF entourant ce IF retournera {2, FALSE, 4, 5, FALSE, 7, FALSE}
SMALL Fonction
{=IFERROR(VLOOKUP(SMALL(IF(IF(ISERROR(SEARCH($F$3,$A$2:$A$8&" "&$B$2:$B$8)),
FALSE,TRUE),ROW($A$2:$A$8)),$E7),CHOOSE({1,2},ROW($A$2:$A$8),
$C$2:$C$8),2,0),"")} La fonction SMALL est utilisée pour renvoyer la nième recherche, basée sur la valeur dans les cellules E7-E11.
Le reste de la formule
{=IFERROR(VLOOKUP(SMALL(IF(IF(ISERROR(SEARCH($F$3,$A$2:$A$8&" "&$B$2:$B$8)),
FALSE,TRUE),ROW($A$2:$A$8)),$E7),CHOOSE({1,2},ROW($A$2:$A$8),
$C$2:$C$8),2,0),"")} La section rouge ci-dessus a été expliquée dans le post précédent, veuillez donc vous y référer pour obtenir une description plus complète.
Fonction IFERROR
La fonction IFERROR est utilisée pour renvoyer une cellule vide pour toutes les valeurs qui ne correspondent pas.
{=IFERROR(VLOOKUP(SMALL(IF(IF(ISERROR(SEARCH($F$3,$A$2:$A$8&" "&$B$2:$B$8)),
FALSE,TRUE),ROW($A$2:$A$8)),$E7),CHOOSE({1,2},ROW($A$2:$A$8),
$C$2:$C$8),2,0),"")} Le résultat
Une fois copié à partir de la cellule G7-G11, VLOOKUP renverra une liste de tous les éléments correspondants.
Renvoyer le nom complet
Nous avons peut-être trouvé les numéros de téléphone de chaque Paul, mais nous devons également récupérer le nom complet. La formule dans F7 est similaire à G7 (avec les différences soulignées ci-dessous). Notre formule renverra le prénom et le nom séparés par un espace.
=IFERROR(VLOOKUP(SMALL(IF(IF(ISERROR(SEARCH($F$3,$A$2:$A$8&" "&$B$2:$B$8)),
FALSE,TRUE),ROW($A$2:$A$8)),$E7),CHOOSE({1,2},ROW($A$2:$A$8),
$A$2:$A$8&" "&$B$2:$B$8),2,0),"") Retour des correspondances partielles
Comme nous avons utilisé la fonction SEARCH, elle est capable de renvoyer des correspondances partielles. Si nous recherchons «Dan», il fournira une liste de tous les résultats correspondants.
Cette formule peut également fonctionner avec des caractères génériques. Si nous recherchons "paul * m" il y a 3 résultats correspondants
Autres articles de la série Mastering VLOOKUP
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, vous devriez:
- Lisez d'autres blogs ou regardez des vidéos YouTube sur le même sujet. Vous en bénéficierez beaucoup plus en découvrant vos propres solutions.
- Demandez le «Excel Ninja» dans votre bureau. C'est incroyable ce que les autres savent.
- Posez une question dans un forum comme Monsieur Excel, ou la Communauté Microsoft Answers. N'oubliez pas que les personnes présentes sur ces forums donnent généralement leur temps gratuitement. Veillez donc à rédiger votre question, assurez-vous qu'elle est claire et concise. Répertoriez toutes les choses que vous avez essayées et fournissez des captures d'écran, des segments de code et des exemples de classeurs.
- Utilisez Excel Rescue, qui est mon partenaire de conseil. Ils aident en fournissant des solutions aux petits problèmes Excel.
Et ensuite?
N'y allez pas encore, il y a beaucoup plus à apprendre sur comment-supprimer. Consultez les derniers articles:
sauvegarder
