Parfois, nous pouvons avoir besoin de trouver la Nème occurrence d’une valeur correspondante dans un tableau dans Google Sheets. Est-ce possible ?
Oui ! La méthode la plus simple consiste à utiliser une combinaison de filtre et d’index.
Cependant, cette méthode ne permet pas d’utiliser les fonctionnalités intégrées de XLOOKUP, telles que l’utilisation de jokers ou la recherche depuis le haut ou le bas.
Supposons qu’il y ait des noms de pays dans la colonne A et des chiffres dans la colonne B.
Nous pouvons utiliser la formule de combinaison INDEX et FILTER suivante pour trouver la troisième occurrence du nom de pays « USA » dans la colonne A et renvoyer la valeur correspondante de la colonne B.
=index(filter(B:B,A:A="USA"),3)
Nous ne pouvons pas utiliser « US* » avec la combinaison ci-dessus si nous ne sommes pas sûrs si la colonne A contient « US » ou « USA ».
Mais REGEXMATCH peut venir à notre rescousse.
=index(filter(B:B,regexmatch(A:A,"US")),3)
Cependant, les autres inconvénients (recherche depuis le début ou la fin) subsistent. De plus, cela peut ne pas prendre en charge des clés de recherche multiples, par exemple « UK » et « USA », en une seule fois.
Apprenons donc comment utiliser XLOOKUP pour trouver la Nème occurrence d’une valeur dans Google Sheets.
Nous avons trois ensembles de formules.
- Recherche depuis la première occurrence jusqu’à la dernière occurrence.
- Recherche depuis la dernière occurrence jusqu’à la première occurrence.
- Jokers dans la Nème occurrence de XLOOKUP.
Vous ne vous concentrez que sur le premier ensemble de formules. Les autres seront faciles à apprendre car elles sont très similaires.
1. Correspondance Nth avec XLOOKUP et recherche de la première à la dernière occurrence
Nos données d’exemple se trouvent dans les colonnes A à C, et les critères se trouvent dans la plage de cellules E3:E4.
Veuillez noter qu’il n’est pas nécessaire d’utiliser des données triées pour trouver la Nème occurrence d’une valeur avec XLOOKUP dans Google Sheets.
Nous avons deux critères à rechercher dans la fonction XLOOKUP. Ce sont « UK » et « USA » dans la plage de cellules E3:E4 (verticalement).
Ainsi, les formules suivantes renverront deux valeurs verticalement. Vous pouvez contrôler le nombre de critères en modifiant E3:E4 dans la formule XLOOKUP ci-dessous.
Formule F3 (1ère occurrence) :
=ArrayFormula(xlookup(E3:E4,B2:B,C2:C,"",0,1))
C’est une formule régulière qui renvoie 5 et 100. Veuillez vous référer à l’image ci-dessus.
Formule G3 (2ème occurrence) :
=ArrayFormula(xlookup(E3:E4, filter(B2:B, countifs(B2:B,B2:B,row(B2:B))<=2),filter(C2:C, countifs(B2:B,B2:B,row(B2:B))<=2),"",0,1))
Elle renvoie 10 et 110.
Formule H3 (3ème occurrence) :
=ArrayFormula(xlookup(E3:E4, filter(B2:B, countifs(B2:B,B2:B,row(B2:B))<=3),filter(C2:C, countifs(B2:B,B2:B,row(B2:B))<=3),"",0,1))
Elle renvoie 15 et 120.
Si vous comparez les formules G3 et H3, vous verrez que la seule différence réside dans les parties surlignées. Le nombre 2 dans la formule G3 devient 3 dans la formule H3.
Modifiez ces parties pour obtenir la Nème correspondance de XLOOKUP depuis la première occurrence jusqu’à la dernière occurrence dans Google Sheets.
Structure de la formule de correspondance Nth avec XLOOKUP
Dans l’exemple ci-dessus, prenons la formule G3 pour l’apprentissage, car F3 est un XLOOKUP régulier.
En ce qui concerne H3, ce n’est pas différent de G3. Seule la valeur de Nth varie dans les deux cas.
Apprenons donc pas à pas la formule G3.
Syntaxe : XLOOKUP(clé_recherche, plage_recherche, plage_résultat, [valeur_manquante], [mode_correspondance], [mode_recherche])
-
Clé_recherche :
E3:E4 -
Plage_recherche :
La formule suivante de comptage continu génère le nombre de comptage continu des occurrences des éléments dans le champ des critères XLOOKUP, qui est la colonne B.
=ArrayFormula(countifs(B2:B,B2:B,row(B2:B),"<="&row(B2:B)))
Je l’ai entrée dans la cellule D2 pour votre référence (nous l’utiliserons dans notre formule XLOOKUP de la 2ème occurrence elle-même).
Le résultat du comptage continu avec la plage B2:B est donné dans l’image ci-dessus.
Dans la formule G3, le Nème valeur est égal à 2. Donc, filtrez B2:B pour que le nombre d’occurrences soit égal à 2 pour obtenir la plage_recherche pour XLOOKUP.
=filter(B2:B, countifs(B2:B,B2:B,row(B2:B),"<="&row(B2:B))=2)
- Plage_résultat :
Pour obtenir la plage_résultat, filtrez C2:C pour que le nombre d’occurrences soit égal à 2.
=filter(C2:C, countifs(B2:B,B2:B,row(B2:B),"<="&row(B2:B))=2)
-
Valeur_manquante :
« » -
Mode_correspondance :
0 -
Mode_recherche :
1
Voilà comment nous utilisons XLOOKUP pour trouver la Nème occurrence d’une valeur depuis la première occurrence jusqu’à la dernière occurrence dans Google Sheets.
2. Correspondance Nth avec XLOOKUP et recherche de la dernière à la première occurrence
Voici les modifications que vous devez apporter aux formules F3, G3 et H3 pour obtenir la Nème correspondance avec XLOOKUP de la dernière occurrence à la première occurrence.
Dans la formule F3 régulière, remplacez 1 dans la dernière partie par -1.
Dans les deux autres formules, remplacez « <= » dans la formule de comptage continu par « >= ». Cela signifie que nous avons besoin d’une formule de comptage continu inversée.
Le remplacement de 1 par -1 n’est pas indispensable ici en raison des données filtrées. Première et dernière ne font aucune différence.
3. Jokers
L’astérisque est l’un des jokers les plus courants. Comment l’utiliser dans la correspondance Nème avec XLOOKUP dans Google Sheets ?
La réponse est la même que pour votre formule régulière.
Par exemple, pour rechercher « US » au lieu de « USA » avec XLOOKUP, vous pouvez utiliser la formule F3 suivante.
=ArrayFormula(xlookup(E3:E4&"*",B2:B,C2:C,"",2,1))
Remarque : Les critères doivent être « US » dans E3:E4. Ainsi, cela correspondra à la fois à « US » et « USA » dans la colonne B.
J’ai ajouté un astérisque avec la partie critères et remplacé 0 par 2 dans le match_mode.
Cela s’applique aux formules de correspondance 2, 3 et Nème occurrence de XLOOKUP.
C’est tout. Merci de votre attention. Profitez-en !
Liens : Crawlan.com
