rezumat
Funcția Excel XLOOKUP este un înlocuitor modern și flexibil pentru funcții mai vechi, cum ar fi VLOOKUP, HLOOKUP și LOOKUP. XLOOKUP acceptă potrivirea aproximativă și exactă, metacaracterele (*?) Pentru potrivirile parțiale și căutările în intervale verticale sau orizontale.
Scop
Căutați valori în interval sau matriceValoare returnată
Valoare (valori) de potrivire din matrice returnatăSintaxă
= XLOOKUP (căutare, căutare_array, return_array, (not_found), (match_mode), (search_mode))Argumente
- căutare - Valoarea căutării.
- lookup_array - Matricea sau intervalul de căutat.
- return_array - Matricea sau intervalul de returnat.
- not_found - (opțional) Valoare de returnat dacă nu a fost găsită nicio potrivire.
- match_mode - (opțional) 0 = potrivire exactă (implicit), -1 = potrivire exactă sau următoarea cea mai mică, 1 = potrivire exactă sau următoare mai mare, 2 = potrivire cu metacaracter.
- căutare_mod - (opțional) 1 = căutare din primul (implicit), -1 = căutare din ultimul, 2 = căutare binară crescătoare, -2 = căutare binară descendentă.
Versiune
Excel 365Note de utilizare
XLOOKUP este un înlocuitor modern pentru funcția VLOOKUP. Este o funcție flexibilă și versatilă care poate fi utilizată într-o mare varietate de situații.
XLOOKUP poate găsi valori în intervale verticale sau orizontale, poate efectua potriviri aproximative și exacte și acceptă metacaractere (*?) Pentru potriviri parțiale. În plus, XLOOKUP poate căuta date începând de la prima valoare sau ultima valoare (a se vedea mai jos tipul de potrivire și detaliile modului de căutare). În comparație cu funcțiile mai vechi, cum ar fi VLOOKUP, HLOOKUP și LOOKUP, XLOOKUP oferă mai multe avantaje cheie.
Mesajul nu a fost găsit
Când XLOOKUP nu poate găsi o potrivire, returnează eroarea # N / A, ca și alte funcții de potrivire din Excel. Spre deosebire de celelalte funcții de potrivire, XLOOKUP acceptă un argument opțional numit not_found care poate fi utilizat pentru a suprascrie eroarea # N / A atunci când altfel ar apărea. Valorile tipice pentru not_found pot fi „Not found”, „No match”, „No result” etc. Atunci când furnizați o valoare pentru not_found, includeți textul între ghilimele duble („”).
Notă: Aveți grijă dacă furnizați un șir gol ("") pentru not_found. Dacă nu se găsește nicio potrivire, XLOOKUP nu va afișa nimic în loc de # N / A. Dacă doriți să vedeți eroarea # N / A când nu se găsește o potrivire, omiteți argumentul în întregime.
Tipul de potrivire
În mod implicit, XLOOKUP va efectua o potrivire exactă. Comportamentul de potrivire este controlat de un argument opțional numit match_type, care are următoarele opțiuni:
| Tipul de potrivire | Comportament |
|---|---|
| 0 (implicit) | Potrivire perfecta. Va reveni # N / A dacă nu se potrivește. |
| -1 | Potrivire exactă sau următorul articol mai mic. |
| 1 | Potrivire exactă sau următorul articol mai mare. |
| 2 | Potrivire cu wildcard (*,?, ~) |
Mod de căutare
În mod implicit, XLOOKUP va începe să se potrivească de la prima valoare a datelor. Comportamentul căutării este controlat de un argument opțional numit search_mode , care oferă următoarele opțiuni:
| Mod de căutare | Comportament |
|---|---|
| 1 (implicit) | Căutați din prima valoare |
| -1 | Căutare din ultima valoare (invers) |
| 2 | Valorile binare ale căutării sunt sortate în ordine crescătoare |
| -2 | Valorile binare ale căutării sunt sortate în ordine descrescătoare |
Căutările binare sunt foarte rapide, dar datele trebuie să fie sortate după cum este necesar. Dacă datele nu sunt sortate corect, o căutare binară poate întoarce rezultate nevalide care arată perfect normal.
Exemplul # 1 - potrivirea exactă de bază
În mod implicit, XLOOKUP va efectua o potrivire exactă. În exemplul de mai jos, XLOOKUP este folosit pentru a prelua vânzările pe baza unei potriviri exacte din film. Formula din H5 este:
=XLOOKUP(H4,B5:B9,E5:E9)
Explicații mai detaliate aici.
Exemplul # 2 - potrivire aproximativă de bază
Pentru a activa o potrivire aproximativă, furnizați o valoare pentru argumentul „match_mode”. În exemplul de mai jos, XLOOKUP este utilizat pentru a calcula o reducere bazată pe cantitate, care necesită o potrivire aproximativă. Formula din F5 furnizează -1 pentru match_mode pentru a activa potrivirea aproximativă cu un comportament „potrivire exactă sau următorul cel mai mic”:
=XLOOKUP(E5,B5:B9,C5:C9,,-1)
Explicații mai detaliate aici.
Exemplul # 3 - valori multiple
XLOOKUP poate returna mai multe valori în același timp pentru aceeași potrivire. Exemplul de mai jos arată cum XLOOKUP poate fi configurat pentru a returna trei valori de potrivire cu o singură formulă. Formula din C5 este:
=XLOOKUP(B5,B8:B15,C8:E15)
Observați că matricea de returnare (C8: E15) include 3 coloane: Prima, Ultima, Departamentul. Toate cele trei valori sunt returnate și se revarsă în intervalul C5: E5.
Exemplul # 4 - căutare bidirecțională
XLOOKUP poate fi folosit pentru a efectua o căutare bidirecțională, cuibărind un XLOOKUP în altul. În exemplul de mai jos, XLOOKUP „interior” recuperează un rând întreg (toate valorile pentru Glass), care este predat XLOOKUP „exterior” ca matrice de întoarcere. XLOOKUP exterior găsește grupul corespunzător (B) și returnează valoarea corespunzătoare (17.25) ca rezultat final.
=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))
Mai multe detalii aici.
Exemplul # 5 - mesajul nu a fost găsit
La fel ca alte funcții de căutare, dacă XLOOKUP nu găsește o valoare, returnează eroarea # N / A. Pentru a afișa un mesaj personalizat în loc de # N / A, furnizați o valoare pentru argumentul opțional „not found”, inclus între ghilimele duble („”). De exemplu, pentru a afișa „Nu s-a găsit” când nu se găsește niciun film care să se potrivească, pe baza foii de lucru de mai jos, utilizați:
=XLOOKUP(H4,B5:B9,E5:E9,"Not found")
Puteți personaliza acest mesaj după cum doriți: „Fără potrivire”, „Filmul nu a fost găsit” etc.
Exemplul # 6 - criterii complexe
Cu capacitatea de a gestiona matrici în mod nativ, XLOOKUP poate fi utilizat cu criterii complexe. În exemplul de mai jos, XLOOKUP se potrivește cu prima înregistrare în care: contul începe cu „x”, iar regiunea este „est”, iar luna nu este aprilie:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)
Detalii: (1) exemplu simplu, (2) exemplu mai complex.
Beneficii XLOOKUP
XLOOKUP oferă câteva avantaje importante, mai ales în comparație cu VLOOKUP:
- XLOOKUP poate căuta date în dreapta sau în stânga valorilor de căutare
- XLOOKUP poate returna mai multe rezultate (exemplul nr. 3 de mai sus)
- XLOOKUP implicit la o potrivire exactă (VLOOKUP implicit la aproximare)
- XLOOKUP poate funcționa cu date verticale și orizontale
- XLOOKUP poate efectua o căutare inversă (de la ultimul la primul)
- XLOOKUP poate returna rânduri sau coloane întregi, nu doar o valoare
- XLOOKUP poate funcționa cu matrice în mod nativ pentru a aplica criterii complexe
Note
- XLOOKUP poate funcționa atât cu tablouri verticale, cât și cu orizontale.
- XLOOKUP va returna # N / A dacă valoarea de căutare nu este găsită.
- Matrice_căutare trebuie să aibă o dimensiune compatibilă cu return_array argumentul, în caz contrar XLOOKUP va reveni #VALUE!
- Dacă XLOOKUP este utilizat între registrele de lucru, ambele registre de lucru trebuie să fie deschise, altfel XLOOKUP va returna #REF !.
- La fel ca funcția INDEX, XLOOKUP returnează o referință ca rezultat.
Videoclipuri asemănătoare
Exemplu de bază XLOOKUP În acest videoclip, vom configura funcția XLOOKUP cu un exemplu de bază. Potrivind numele orașului, vom prelua țara și populația. 
Potrivire aproximativă de bază XLOOKUP În acest videoclip, vom configura funcția XLOOKUP pentru a efectua o potrivire aproximativă pentru a calcula o reducere bazată pe cantitate. 
XLOOKUP cu logică booleană În acest videoclip vom analiza cum se utilizează funcția XLOOKUP cu logică booleană pentru a aplica mai multe criterii. 
XLOOKUP cu valori de căutare multiple În acest videoclip, vom configura XLOOKUP pentru a returna mai multe valori într-o matrice dinamică, oferind o gamă de valori de căutare în loc de o singură valoare de căutare.
