Funcția XLOOKUP este exclusivă pentru programul privilegiat al Office 365. Funcția LOOKUP are multe funcționalități care depășesc multe dintre punctele slabe ale funcției VLOOKUP și HLOOKUP, dar, din păcate, nu ne este disponibilă deocamdată. Dar nu vă faceți griji, putem crea o funcție XLOOKUP care funcționează exact la fel ca viitoarea funcție XLOOKUP MS Excel. Îi vom adăuga funcționalități unul câte unul.
Codul VBA al funcției XLOOKUP
Funcția de căutare UDF de mai jos va rezolva multe probleme. Copiați-l sau descărcați suplimentul xl de mai jos, fișierul de mai jos.
Funcție XLOOKUP (lk ca variantă, lCol ca gamă, rCol ca gamă) XLOOKUP = WorksheetFunction.Index (rCol, WorksheetFunction.Match (lk, lCol, 0)) Funcție finală
Explicaţie:
Codul de mai sus este doar INDEX-MATCH de bază utilizat în VBA. Acest lucru simplifică o mulțime de lucruri cu care se confruntă un utilizator nou. Dacă rezolvă complexitatea funcției INDEX-MATCH și folosește doar trei argumente. Puteți să-l copiați în fișierul Excel sau să descărcați fișierul .xlam de mai jos și să îl instalați ca un supliment Excel. Dacă nu știți cum să creați și să utilizați un program de completare, faceți clic aici, acesta vă va ajuta.
Supliment XLOOKUP
să vedem cum funcționează pe foaia de lucru Excel.
Sintaxa XLOOKUP
| = XLOOKUP (lookup_value, lookup_array, result_array) |
valoare_cercare: Aceasta este valoarea pe care doriți să o căutați în lookup_array.
lookup_array: Este un interval unidimensional în care doriți să căutați valoare_cercare.
result_array: Este, de asemenea, un interval unidimensional. Acesta este intervalul din care doriți să recuperați valoarea.
Să vedem această acțiune XLOOKUP în acțiune.
Exemple XLOOKUP:
Aici am un tabel de date în excel. Să explorăm câteva funcționalități folosind acest tabel de date.
Funcționalitate 1. Corect Căutați în partea stângă și dreapta a valorii de căutare.
După cum știm, funcția Excel VLOOKUP nu poate prelua valori din stânga valorii de căutare. Pentru aceasta, trebuie să utilizați combinația complexă INDEX-MATCH. Dar nu mai.
Presupunând că trebuie să preluăm toate informațiile disponibile în tabelul unor numere de rulare. În acest caz, va trebui să regăsiți și regiunea, care se află în stânga coloanei cu numărul ruloului.
Scrieți această formulă, I2:
| = XLOOKUP (H2, $ B $ 2: $ B $ 14, $ A $ 2: $ A $ 14) |
Obținem rezultatul la nord pentru numărul 112. Copiați sau trageți în jos formula din celulele de mai jos pentru a le completa cu regiunile respective.
Cum functioneazã?
Mecanismul este simplu. Această funcție caută valoare_cercare în lookup_array și returnează indexul unei prime potriviri exacte. Apoi folosește acel index pentru a extrage valoarea rezultat_array. Această funcție funcționează perfect cu intervale denumite.
În mod similar, utilizați această formulă pentru a extrage valoarea din fiecare coloană.
Funcționalitate 2. Exact Orizontală Căutați deasupra și dedesubtul valorii de căutare.
XLOOKUP funcționează și ca o funcție HLOOKUP exactă. Funcția HLOOKUP are aceeași limitare ca și VLOOKUP. Nu poate prelua valoarea de deasupra valorii de căutare. Dar XLOOKUP nu funcționează doar ca HLOOKUP, ci depășește această slăbiciune. Să vedem cum.
Ipotetic, dacă doriți să comparați două înregistrări. Înregistrarea de căutare pe care o aveți deja. Înregistrarea cu care doriți să comparați este deasupra intervalului de căutare. Utilizați această formulă în acest caz.
| = XLOOKUP (H7, $ A $ 9: $ E $ 9, $ A $ 2: $ E $ 2) |
trageți în jos formula și aveți întreaga înregistrare a rândului de comparare.
Funcționalitate 3. Nu este nevoie de numărul coloanei și de potrivirea exactă implicită.
Când utilizați funcția VLOOKUP, trebuie să spuneți numărul coloanei din care doriți să preluați valorile. Pentru asta, trebuie să numeri coloanele sau să folosești câteva trucuri, să iei ajutorul altor funcții. Cu acest UDF XLOOKUP, nu este nevoie să faceți asta.
Dacă utilizați VLOOKUP doar pentru a prelua o anumită valoare dintr-o coloană sau pentru a verifica dacă există o valoare în coloană, aceasta este cea mai bună soluție ca la mine.
Funcționalitate 4. Înlocuiește funcția INDEX-MATCH, VLOOKUP, HLOOKUP
Pentru sarcini simple, funcția noastră XLOOKUP înlocuiește funcțiile menționate mai sus.
Limitări ale XLOOKUP:
Când vine vorba de formule complexe, cum ar fi VLOOKUP cu Dynamic Col Index, unde VLOOKUP identifică coloana de căutare cu anteturi, acest XLOOKUP va eșua.
O altă limitare este că, dacă trebuie să căutați mai multe coloane sau rânduri aleatorii din tabel, această funcție va fi inutilă, deoarece trebuie să scrieți această formulă din nou și din nou. Acest lucru poate fi depășit folosind intervale denumite.
Deocamdată, nu am adăugat funcționalitatea aproximativă, deci, desigur, nu puteți obține potrivirea aproximativă. Vom adăuga asta prea curând.
Dacă funcția XLOOKUP nu reușește să găsească valoarea de căutare, va returna eroarea #VALUE nu # N / A.
Deci, băieți, așa folosiți XLOOKUP pentru a extrage, căuta și valida valori în tabelele Excel. Puteți utiliza această funcție definită de utilizator pentru o căutare fără probleme în partea stângă sau în sus a valorii de căutare. Dacă aveți în continuare vreo îndoială sau orice cerință specifică legată de această funcție sau interogare EXCEL 2010/2013/2016/2019/365 sau VBA, întrebați-o în secțiunea de comentarii de mai jos. Cu siguranță veți primi un răspuns.
Creați funcția VBA pentru a returna matricea | Pentru a returna o matrice din funcția definită de utilizator, trebuie să o declarăm atunci când numim UDF.
Matrice în Excel Formul | Aflați ce matrice sunt în Excel.
Cum se creează o funcție definită de utilizator prin VBA | Aflați cum să creați funcții definite de utilizator în Excel
Utilizarea unei funcții definite de utilizator (UDF) dintr-un alt registru de lucru utilizând VBA în Microsoft Excel | Utilizați funcția definită de utilizator într-un alt registru de lucru Excel
Returnează valorile de eroare de la funcțiile definite de utilizator utilizând VBA în Microsoft Excel | Aflați cum puteți returna valorile de eroare dintr-o funcție definită de utilizator
Articole populare:
Împărțiți foaia Excel în mai multe fișiere pe baza coloanei folosind VBA | Acest cod VBA împarte baza de foi Excel pe valori unice într-o coloană specificată. Descărcați fișierul de lucru.
Dezactivați mesajele de avertizare folosind VBA în Microsoft Excel 2016 | Pentru a dezactiva mesajele de avertizare care întrerup codul VBA care rulează, folosim clasa Application.
Adăugați și salvați un registru de lucru nou folosind VBA în Microsoft Excel 2016 | Pentru a adăuga și a salva registre de lucru folosind VBA, folosim clasa Workbooks. Workbooks.Add adaugă cu ușurință un nou registru de lucru, totuși …