Cum se utilizează funcția XLOOKUP în Excel

Ce este funcția Xlookup în Excel ? Cum se utilizează Xlookup în Excel ? Să aflăm cu LuckyTemplates.com!

Dacă utilizați în mod regulat Excel, probabil că sunteți destul de familiarizat cu funcțiile de căutare. Dacă nu știți, trebuie doar să o înțelegeți ca pe o funcție Excel folosită pentru a căuta date sau valori specifice pe rânduri sau coloane dintr-o foaie de calcul.

Această funcție este utilă în special atunci când trebuie să procesați o mulțime de date într-o foaie de calcul Excel. În Excel, aveți mai multe tipuri de funcții LOOKUP. XLOOKUP este unul dintre ele. Deci, ce este funcția XLOOKUP, ce este formula XLOOKUP și cum se utilizează? Să aflăm cu LuckyTemplates.com!

Lucruri de știut despre funcția XLOOKUP în Excel

• Ce este XLOOKUP?
  • Cum se accesează funcția XLOOKUP
  • Sintaxa funcției XLOOKUP
• Avantajele și dezavantajele funcției XLOOKUP în Excel
  • Avantajele funcției XLOOKUP
  • Dezavantajele funcției XLOOKUP
• Cum să utilizați funcția XLOOKUP
  • Nu există numere suplimentare de index de coloană
  • Potrivire exactă în mod implicit
  • XLOOKUP poate fi găsit în stânga
  • Utilizați XLOOKUP pentru a căuta intervale
  • XLOOKUP înlocuiește funcția HLOOKUP
  • XLOOKUP poate fi vizualizat de jos în sus

Ce este funcția XLOOKUP în Excel?

În esență, XLOOKUP înlocuiește vechile funcții precum HLOOKUP, VLOOKUP și LOOKUP. Acceptă căutarea datelor atât pe verticală, cât și pe orizontală.

Cu alte cuvinte, funcția XLOOKUP din Excel vă ajută să găsiți rapid valori în setul de date furnizat, atât pe orizontală, cât și pe verticală. Apoi returnează valoarea corespunzătoare în funcție de diferite rânduri și coloane.

Există multe modalități de a maximiza utilizarea funcției XLOOKUP.

Cum se accesează funcția XLOOKUP

În prezent, XLOOKUP este disponibil numai pentru utilizatorii programului Insiders. Oricine se alătură programului Insider poate accesa cele mai recente funcții Excel pe măsură ce acestea devin disponibile. Microsoft va implementa în curând această funcție pentru toți utilizatorii Office 365. Din păcate, XLOOKUP nu este disponibil pentru utilizatorii care rulează versiunile Office 2010, 2013, 2016 și 2019.

Dacă nu utilizați suita Microsoft 365 pe computer, poate fi necesar să faceți upgrade la Microsoft 365 pentru a obține acces la funcționalitatea XLOOKUP. Și dacă utilizați Microsoft 365, veți vedea opțiunea activată în Excel. XLOOKUP este disponibil și pe Office 365 Online.

Sintaxa funcției XLOOKUP

Sintaxa funcției CĂUTARE XL este similară cu sintaxa funcțiilor CĂUTARE V și CĂUTARE HL. Dacă le-ați folosit înainte, veți găsi că folosirea XLOOKUP este mai convenabilă. Iată sintaxa pentru funcția XLOOKUP în Excel:

= CĂUTARE XL ( valoare_căutare , matrice_căutare , matrice_returnată , [dacă_nu_găsită], [mod_potrivire], [mod_căutare])

Funcția XLOOKUP acceptă până la 6 argumente și aici sunt valorile acestora.

1. lookup_value (obligatoriu): valoarea pe care doriți să o găsiți.

2. lookup_array (obligatoriu): matricea în care doriți să căutați valoarea de căutare.

3. return_array (obligatoriu): Matrice de unde doriți să preluați și să returnați valori atunci când este găsită valoarea de căutare.

4. [if_not_found] (opțional): Când nu este găsită nicio potrivire, aceasta este valoarea returnată.

5. [match_mode] (opțional): Acest argument vă permite să specificați tipul de potrivire pe care îl căutați. Există diferite valori pentru a-l specifica:

• 0 - Caută o potrivire exactă, iar valoarea trebuie să se potrivească exact cu valoarea din lookup_array. Această valoare este, de asemenea, setată ca implicită atunci când nu este menționată.
• -1 - Caută o potrivire exactă și, odată găsită, va reveni la următoarea valoare mai mică.
• 1 - Caută o potrivire exactă și când este găsită returnează următoarea valoare mai mare.
• 2 - Efectuați potrivirea parțială utilizând metacaracterele unde *, ? și ~ are o semnificație specială.

6. [search_mode] (opțional): Folosit pentru a specifica modul de căutare al XLOOKUP în lookup_array. Există valori diferite pentru a specifica același lucru:

• 1 - Efectuați o căutare începând de la primul articol. Această valoare este setată ca implicită atunci când nu este specificat nimic.
• -1 - Efectuează o căutare inversă pornind de la ultimul element.
• 2 - Efectuați o căutare binară în lookup_array unde datele trebuie sortate în ordine crescătoare. Dacă datele nu sunt sortate, acestea pot produce erori sau rezultate greșite.
• -2 - Efectuați o căutare binară în lookup_array unde datele trebuie sortate în ordine descrescătoare. Dacă datele nu sunt sortate, acestea pot produce erori sau rezultate greșite.

Avantajele și dezavantajele funcției XLOOKUP în Excel

XLOOKUP este încă o funcție avantajoasă în comparație cu VLOOKUP și INDEX/ATCH. Dar are și propriile sale dezavantaje.

Avantajele funcției XLOOKUP

• Maxilarul funcționează atât pe verticală, cât și pe orizontală.
• Sunt necesare doar 3 argumente, în loc de 4 ca în VLOOKUP și INDEX MATCH.
• Întotdeauna implicit la o potrivire exactă.
• Căutările parțiale pot fi efectuate folosind metacaractere.
• Căutările pot fi efectuate în ordine descrescătoare.
• Utilizați o funcție în loc de două în INDEX MATCH.

Dezavantajele funcției XLOOKUP

• Argumentele opționale pot părea complicate pentru începători.
• Poate consuma mai mult timp atunci când selectați două intervale și aveți prea multe celule în foaia de calcul.
• Returnează o eroare când caută și returnează o matrice care nu are aceeași lungime.
• Rețineți atât intervalul de căutare, cât și intervalul de returnare.

Beneficiile utilizării funcției XLOOKUP Excel

• XLOOKUP face cele mai utilizate formule din Excel mai simple și mai puțin predispuse la erori . Trebuie doar să scrieți =XLOOKUP (date de găsit, listă, listă de rezultate) și veți obține răspunsul sau #N/A dacă valoarea nu este găsită.
• Rezultatele implicite ale căutării sunt exacte . Unul dintre dezavantajele VLOOKUP este că trebuie să menționați FALSE ca ultimul parametru pentru a obține rezultatul corect. XLOOKUP rezolvă acest lucru, efectuând o potrivire exactă în mod implicit. Puteți utiliza parametrul modului de potrivire pentru a modifica comportamentul de căutare, dacă doriți.
• Al patrulea parametru acceptă cazul în care valoarea nu este găsită . În majoritatea situațiilor de afaceri, veți fi forțat să „împachetați” formula de căutare cu IFERROR sau IFNA pentru a elimina erorile. XLOOKUP oferă un al 4-lea parametru, astfel încât să puteți cunoaște rezultatul implicit dorit dacă valoarea nu este găsită.
• XLOOKUP oferă parametri de selecție pentru a găsi situații speciale . Puteți căuta de sus sau de jos, după caractere speciale și opțiuni mai rapide pentru a găsi date filtrate.

Cum să utilizați funcția XLOOKUP

Aruncă o privire la exemplul de mai jos pentru a înțelege cum funcționează XLOOKUP. În acest exemplu, trebuie să returnăm partea din coloana F pentru fiecare ID din coloana A.

Acesta este un exemplu care caută rezultate exacte, dar funcția XLOOKUP necesită doar trei tipuri de informații.

Imaginea de mai jos arată XLOOKUP cu 5 argumente, dar primele trei argumente sunt necesare pentru rezultate corecte. Deci, să ne concentrăm asupra lor:

• Lookup_value : Valoare de căutare
• Lookup_array : interval de căutare
• Return_array : Interval care conține valoarea de returnat

În acest exemplu, vom folosi formula:

=XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Acum să explorăm câteva dintre avantajele XLOOKUP față de VLOOKUP.

Nu există numere suplimentare de index de coloană

Cel de-al treilea argument al CĂUTARE V este acela de a specifica numărul de coloane de informații de returnat din intervalul tabelului. Aceasta nu mai este o problemă cu XLOOKUP deoarece funcția permite utilizatorului să selecteze intervalul de returnare. (coloana F în acest exemplu).

Și nu uitați, XLOOKUP poate vedea datele rămase ale celulei selectate, spre deosebire de VLOOKUP.

De asemenea, nu veți întâlni erori de formulă atunci când adăugați coloane noi. Dacă acest lucru se întâmplă în foaia de calcul, intervalul de returnare va fi ajustat automat.

Potrivire exactă în mod implicit

Când folosește VLOOKUP, utilizatorul trebuie să specifice o potrivire exactă, dacă dorește. Dar cu XLOOKUP, implicit este o potrivire exactă. Acest lucru reduce al patrulea argument și asigură mai puține greșeli pentru noii utilizatori. Pe scurt, XLOOKUP necesită mai puține întrebări decât VLOOKUP și este ușor de utilizat.

XLOOKUP poate fi găsit în stânga

Posibilitatea de a selecta intervale de căutare oferă XLOOKUP mai multă flexibilitate decât VLOOKUP. Cu XLOOKUP, ordinea coloanelor din tabel nu contează.

CĂUTARE V este limitată prin căutarea în coloana cea mai din stânga a tabelului și apoi revenirea de la un număr specificat de coloane din dreapta.

În exemplul de mai jos, trebuie să căutăm ID-ul (coloana E) și să returnăm numele persoanei (coloana D).

Utilizați formula de mai jos:

=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Utilizați XLOOKUP pentru a căuta intervale

Deși nu este la fel de comună ca potrivirea exactă, puteți utiliza o formulă de căutare pentru a găsi o valoare într-un interval. De exemplu, dorim să returnăm o reducere în funcție de suma cheltuită.

De data aceasta nu căutăm o anumită valoare și trebuie să știm în ce interval se încadrează valorile din coloana B în coloana E pentru a determina reducerea.

XLOOKUP are un al patrulea argument opțional (rețineți că implicit este potrivirea absolută) numit match_mode.

Puteți vedea că XLOOKUP poate găsi potriviri aproximative mai bune decât VLOOKUP.

Există o opțiune de a găsi cea mai apropiată potrivire mai mică de (-1) sau cea mai apropiată potrivire mai mare de (1) valoarea de căutare. Există, de asemenea, o opțiune de utilizare a metacaractere (2), cum ar fi ? sau *. Această setare nu este activată în mod implicit, așa cum este cu VLOOKUP.

Formula din acest exemplu returnează cea mai apropiată valoare mai mică decât valoarea de căutare dacă nu se găsește o potrivire exactă:

=XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

Totuși, a apărut o eroare în celula C7, ar fi trebuit returnat reducerea de 0% deoarece a cheltuit doar 64, care nu îndeplineau criteriile pentru a primi reducerea.

Un alt avantaj al funcției XLOOKUP este că nu necesită un interval de căutare în ordine crescătoare, cum ar fi VLOOKUP.

Introduceți un rând nou în partea de jos a tabelului de căutare și apoi deschideți formula. Extindeți domeniul de utilizare făcând clic și trăgând colțurile.

Formula corectează imediat eroarea. Nu are nicio problemă să aibă 0 la sfârșitul tabelului interval.

XLOOKUP înlocuiește funcția HLOOKUP

După cum sa menționat, funcția XLOOKUP poate înlocui și funcția HLOOKUP. O funcție poate înlocui două funcții, ce ar putea fi mai bun?

Funcția HLOOKUP caută în sus orizontal, folosită pentru a căuta după rând.

Această funcție nu este la fel de renumită ca VLOOKUP, dar este utilă în cazurile în care titlul este în coloana A și datele sunt în rândurile 4 și 5, ca în exemplul de mai jos.

XLOOKUP poate căuta în ambele direcții, în jos coloane și de-a lungul rândurilor.

În acest exemplu, se folosește o formulă pentru a returna valoarea vânzărilor asociată cu numele din coloana A2. Se uită la rândul 4 pentru nume și returnează valoarea de la rândul 5:

=XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP poate fi vizualizat de jos în sus

De obicei, trebuie să căutați de sus în jos într-o listă pentru a găsi prima apariție a unei valori. XLOOKUP are un al cincilea argument numit search_mode. Acest argument permite redirecționarea căutării pentru a începe din partea de jos a unei liste pentru a găsi ultima prezență a unei valori.

În exemplul de mai jos, trebuie să găsim nivelul de inventar al fiecărui produs în coloana A.

Tabelul de căutare este aranjat în ordinea datei. Dorim să returnăm nivelul de inventar de la ultima verificare (ultima apariție a ID-ului produsului).

Al cincilea argument al funcției XLOOKUP oferă patru opțiuni. Aici vom folosi opțiunea Căutare de la ultimul până la primul .

Formula folosită în acest exemplu:

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)

În această formulă, al patrulea argument este omis. Este opțional și dorim ca implicit să fie o potrivire exactă.

Funcția CĂUTARE XL este succesorul așteptat atât pentru funcțiile CĂUTARE V, cât și pentru CĂUTARE HL. O serie de exemple au fost folosite în acest articol pentru a demonstra avantajele funcției XLOOKUP.

XLOOKUP este o îmbunătățire mare când vine vorba de funcțiile de căutare. Singura limitare a funcției XLOOKUP este că nu este compatibilă cu versiunea inversă. Dacă partajați foi de calcul cu versiuni mai vechi ale Microsoft Office Suite, este posibil să primiți erori.

Cu toate acestea, puteți accesa XLOOKUP din Office 365 Online. XLOOKUP este încă în dezvoltare, așa că va dura ceva timp pentru ca utilizatorii Excel să îl adopte.

Vă doresc succes!

Diferența dintre XLOOKUP și VLOOKUP

De ce este XLOOKUP mai bun decât VLOOKUP?

Deși VLOOKUP are propriile sale utilizări, are caracteristici mai limitate decât XLOOKUP. Acest lucru face ca XLOOKUP să fie cea mai populară alegere atunci când trebuie să procesați cantități mari de date. Drept urmare, rulează mai repede și are mai puține erori.

• XLOOKUP nu limitează intervalul de date : XLOOKUP este mai flexibil decât VLOOKUP. VLOOKUP poate căuta valori situate numai în coloana din stânga a unui tabel și poate returna valori din coloana corespunzătoare din dreapta. Între timp, XLOOKUP necesită doar pași mai simpli și poate returna valori pentru orice coloană în orice direcție.
• Rezultatul implicit al XLOOKUP este cea mai precisă valoare .
• În mod implicit, funcția CĂUTARE V din Excel găsește întotdeauna rezultate aproximative . Cu toate acestea, dacă aveți nevoie de rezultate exacte, ar trebui să introduceți FALSE în argumentul range_lookup.
• Funcția XLOOKUP caută o valoare de căutare într-un anumit interval și întotdeauna implicit la valoarea exactă. Dacă nu puteți găsi unul, puteți edita argumentul pentru a găsi o potrivire aproximativă care este mai mică sau mai mare decât valoarea de căutare.

Alte motive:

• XLOOKUP poate gestiona inserarea sau ștergerea coloanei.
• XLOOKUP implementează căutări verticale și orizontale.
• XLOOKUP oferă potriviri aproximative mai inteligente.
• XLOOKUP are nevoie de mai puține referințe de celule.