Noul XLOOKUP de la Excel va înlocui VLOOKUP, oferind un înlocuitor puternic pentru una dintre cele mai populare funcții ale Excel. Această nouă funcție rezolvă unele dintre limitările VLOOKUP și are funcționalități suplimentare. Iată ce trebuie să știți.
Cuprins
Ce este XLOOKUP?
Noua funcție XLOOKUP are soluții pentru unele dintre cele mai mari limitări ale VLOOKUP. În plus, înlocuiește și HLOOKUP. De exemplu, XLOOKUP se poate uita în stânga sa, implicit la o potrivire exactă și vă permite să specificați un interval de celule în loc de un număr de coloană. VLOOKUP nu este atât de ușor de utilizat sau la fel de versatil. Vă vom arăta cum funcționează totul.
Pentru moment, XLOOKUP este disponibil doar pentru utilizatorii din programul Insiders. Oricine poate alăturați-vă programului Insiders pentru a accesa cele mai noi funcții Excel de îndată ce acestea devin disponibile. Microsoft va începe în curând să-l lanseze tuturor utilizatorilor Office 365.
Cum să utilizați funcția XLOOKUP
Să intrăm direct cu un exemplu de XLOOKUP în acțiune. Luați exemplul de date de mai jos. Dorim să returnăm departamentul din coloana F pentru fiecare ID din coloana A.
Acesta este un exemplu clasic de căutare a potrivirii exacte. Funcția XLOOKUP necesită doar trei informații.
Imaginea de mai jos arată XLOOKUP cu șase argumente, dar numai primele trei sunt necesare pentru o potrivire exactă. Deci, să ne concentrăm asupra lor:
Lookup_value: ceea ce cauți.
Lookup_array: Unde să căutați.
Return_array: intervalul care conține valoarea de returnat.
Următoarea formulă va funcționa pentru acest exemplu: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)
Să explorăm acum câteva avantaje pe care le are XLOOKUP față de VLOOKUP aici.
Nu mai există numărul de index al coloanei
Cel de-al treilea argument infam al LOOKUP a fost acela de a specifica numărul coloanei informațiilor de returnat dintr-o matrice de tabel. Aceasta nu mai este o problemă, deoarece XLOOKUP vă permite să selectați intervalul din care să reveniți (coloana F în acest exemplu).
Și nu uitați, XLOOKUP poate vizualiza datele din stânga celulei selectate, spre deosebire de VLOOKUP. Mai multe despre asta mai jos.
De asemenea, nu mai aveți problema unei formule rupte atunci când sunt inserate coloane noi. Dacă acest lucru s-a întâmplat în foaia de calcul, intervalul de returnare s-ar ajusta automat.
Potrivirea exactă este implicită
A fost întotdeauna confuz când ați învățat CĂUTARE V de ce a trebuit să specificați o potrivire exactă.
Din fericire, XLOOKUP are o potrivire exactă – motivul mult mai comun pentru a utiliza o formulă de căutare). Acest lucru reduce nevoia de a răspunde celui de-al cincilea argument și asigură mai puține greșeli de către utilizatorii noi în formulă.
Deci, pe scurt, XLOOKUP pune mai puține întrebări decât VLOOKUP, este mai ușor de utilizat și este, de asemenea, mai durabil.
XLOOKUP poate privi spre stânga
Posibilitatea de a selecta un interval de căutare face ca XLOOKUP să fie mai versatil decât VLOOKUP. Cu XLOOKUP, ordinea coloanelor din tabel nu contează.
CĂUTARE V a fost constrânsă prin căutarea în coloana cea mai din stânga a unui tabel și apoi revenirea de la un număr specificat de coloane la dreapta.
În exemplul de mai jos, trebuie să căutăm un ID (coloana E) și să returnăm numele persoanei (coloana D).
Următoarea formulă poate realiza acest lucru: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)
Ce să faci dacă nu ai găsit
Utilizatorii funcțiilor de căutare sunt foarte familiarizați cu mesajul de eroare #N/A care îi întâmpină atunci când funcția lor VLOOKUP sau MATCH nu găsește ceea ce are nevoie. Și adesea există un motiv logic pentru asta.
Prin urmare, utilizatorii caută rapid cum să ascundă această eroare, deoarece nu este corectă sau utilă. Și, desigur, există modalități de a face acest lucru.
XLOOKUP vine cu propriul argument încorporat „dacă nu a fost găsit” pentru a gestiona astfel de erori. Să-l vedem în acțiune cu exemplul anterior, dar cu un ID greșit.
Următoarea formulă va afișa textul „ID incorect” în loc de mesajul de eroare: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,”ID incorect”)
Utilizarea XLOOKUP pentru o căutare a intervalului
Deși nu este la fel de comună ca potrivirea exactă, o utilizare foarte eficientă a unei formule de căutare este căutarea unei valori în intervale. Luați următorul exemplu. Dorim să returnăm reducerea în funcție de suma cheltuită.
De data aceasta nu căutăm o valoare anume. Trebuie să știm unde valorile din coloana B se încadrează în intervalele din coloana E. Aceasta va determina reducerea câștigată.
XLOOKUP are un al cincilea argument opțional (rețineți că este implicit potrivirea exactă) numit modul potrivire.
Puteți vedea că XLOOKUP are capacități mai mari cu potriviri aproximative decât cea a VLOOKUP.
Există opțiunea de a găsi cea mai apropiată potrivire mai mică decât (-1) sau cea mai apropiată mai mare decât (1) valoarea căutată. Există, de asemenea, o opțiune de a utiliza caractere joker (2), cum ar fi ? sau *. Această setare nu este activată în mod implicit, așa cum a fost cu VLOOKUP.
Formula din acest exemplu returnează cea mai apropiată valoare mai mică decât valoarea căutată dacă nu este găsită o potrivire exactă: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)
Cu toate acestea, există o greșeală în celula C7 în care este returnată eroarea #N/A (argumentul „dacă nu a fost găsit” nu a fost folosit). Acest lucru ar fi trebuit să returneze o reducere de 0%, deoarece cheltuirea 64 nu atinge criteriile pentru nicio reducere.
Un alt avantaj al funcției XLOOKUP este că nu necesită ca intervalul de căutare să fie în ordine crescătoare, așa cum o face VLOOKUP.
Introduceți un rând nou în partea de jos a tabelului de căutare și apoi deschideți formula. Extindeți intervalul utilizat făcând clic și trăgând colțurile.
Formula corectează imediat eroarea. Nu este o problemă să aveți „0” în partea de jos a intervalului.
Personal, aș sorta în continuare tabelul după coloana de căutare. A avea „0” în partea de jos m-ar înnebuni. Dar faptul că formula nu s-a rupt este genial.
XLOOKUP Înlocuiește și funcția HLOOKUP
După cum am menționat, funcția XLOOKUP este, de asemenea, aici pentru a înlocui HLOOKUP. O funcție pentru a înlocui două. Excelent!
Funcția HLOOKUP este căutarea orizontală, utilizată pentru căutarea de-a lungul rândurilor.
Nu la fel de cunoscut ca și fratele său VLOOKUP, dar util pentru exemple precum mai jos, unde anteturile sunt în coloana A, iar datele sunt de-a lungul rândurilor 4 și 5.
XLOOKUP poate privi în ambele direcții – în jos coloane și, de asemenea, de-a lungul rândurilor. Nu mai avem nevoie de două funcții diferite.
În acest exemplu, formula este utilizată pentru a returna valoarea vânzărilor aferentă numelui din celula A2. Se uită de-a lungul rândului 4 pentru a găsi numele și returnează valoarea din rândul 5: =XLOOKUP(A2,B4:E4,B5:E5)
XLOOKUP poate privi de jos în sus
De obicei, trebuie să căutați o listă pentru a găsi prima (de multe ori numai) apariție a unei valori. XLOOKUP are un al șaselea argument numit mod de căutare. Acest lucru ne permite să comutăm căutarea pentru a începe din partea de jos și să căutăm o listă pentru a găsi în schimb ultima apariție a unei valori.
În exemplul de mai jos, am dori să găsim nivelul stocului pentru fiecare produs în coloana A.
Tabelul de căutare este în ordinea datei și există mai multe verificări de stoc pentru fiecare produs. Dorim să returnăm nivelul stocului de la ultima dată când a fost verificat (ultima apariție a ID-ului produsului).
Al șaselea argument al funcției XLOOKUP oferă patru opțiuni. Suntem interesați să folosim opțiunea „Căutare de la ultimul până la primul”.
Formula completată este afișată aici: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)
În această formulă, al patrulea și al cincilea argument au fost ignorate. Este opțional și am dorit ca implicită o potrivire exactă.
Ridica
Funcția XLOOKUP este urmașul așteptat cu nerăbdare atât la funcțiile CĂUTARE V, cât și CĂUTARE HL.
O varietate de exemple au fost folosite în acest articol pentru a demonstra avantajele XLOOKUP. Unul dintre acestea este că XLOOKUP poate fi folosit pe foi, registre de lucru și, de asemenea, cu tabele. Exemplele au fost păstrate simple în articol pentru a ne ajuta înțelegerea.
Din cauza matrice dinamice fiind introduse în Excel în curând, poate returna și un interval de valori. Acesta este cu siguranță ceva care merită explorat mai departe.
Zilele CĂUTARE V sunt numărate. XLOOKUP este aici și va fi în curând formula de căutare de facto.