Cum se utilizează funcția XLOOKUP în Excel 2016

Excel 2016 pentru abonații Office 365 pe Windows și Mac acceptă acum o nouă funcție XLOOKUP, prezentată ca un înlocuitor considerabil mai simplu și mai versatil pentru funcția de căutare verticală foarte populară (dar adesea defăimată), VLOOKUP (nu știu ce X din XLOOKUP înseamnă; extensiv, poate?).

Pentru cei dintre voi care nu sunt încă familiarizați cu CĂUTARE V (considerată a treia funcție cea mai folosită imediat după SUMĂ și MEDIE), această funcție caută vertical după rând în coloana din stânga a unui tabel de căutare desemnat de sus în jos până când găsește o valoare într-un coloană de căutare desemnată printr-un număr de compensare care se potrivește sau îl depășește pe cel pe care îl căutați. Deși extrem de utilă pentru a localiza anumite elemente într-o listă lungă sau o coloană a unui tabel de date din foaia de lucru, funcția CĂUTARE V are câteva limitări care nu sunt împărtășite de această nouă funcție de căutare, ca XLOOKUP:

• În mod implicit, găsirea potrivirilor exacte pentru valoarea dvs. de căutare în intervalul de căutare
• Poate căuta atât pe verticală (după rând), cât și pe orizontală (după coloană) într-un tabel, înlocuind astfel necesitatea utilizării funcției HLOOKUP atunci când căutați orizontal după coloană
• Poate căuta la stânga sau la dreapta, astfel încât intervalul de căutare din tabelul dvs. de căutare să nu fie localizat într-o coloană din stânga celei desemnate ca interval de returnare pentru ca funcția să funcționeze
• Când se utilizează potrivirea exactă implicită, funcționează chiar și atunci când valorile din intervalul de căutare nu sunt sortate într-o anumită ordine
• Poate căuta din rândul de jos până în sus în intervalul matricei de căutare, folosind un argument opțional pentru modul de căutare

Funcția XLOOKUP are cinci argumente posibile, dintre care primele trei sunt obligatorii și ultimele două opționale, folosind următoarea sintaxă:

CĂUTARE XL( valoare_căutare , matrice_căutare , matrice_return ,[ mod_potrivire ],[ mod_căutare ])

Argumentul necesar search_value desemnează valoarea sau elementul pe care îl căutați. Argumentul matricei de căutare obligatorie desemnează intervalul de celule care trebuie căutat pentru această valoare de căutare, iar argumentul return_array desemnează intervalul de celule care conțin valoarea pe care doriți să o returnați atunci când Excel găsește o potrivire exactă.

* Rețineți că atunci când desemnați argumentele lookup_array și return_array în funcția dvs. XLOOKUP, ambele intervale trebuie să fie de lungime egală, altfel Excel va returna #VALOR! eroare la formula ta. Acesta este mai mult motivul pentru care utilizați nume de intervale sau nume de coloane ale unui tabel de date desemnat atunci când definiți aceste argumente, mai degrabă decât să le subliniați sau să introduceți referințele celulelor lor .

Argumentul opțional match_mode poate conține oricare dintre următoarele patru valori:

• 0 pentru o potrivire exactă (implicit, la fel ca atunci când nu este desemnat niciun argument match_mode )
• -1 pentru potrivirea exactă sau următoarea valoare mai mică
• 1 pentru potrivirea exactă sau valoarea următoare mai mare
• 2 pentru potrivirea parțială folosind caractere joker unite la referința de celulă în argumentul lookup_value

Argumentul opțional search_mode poate conține oricare dintre următoarele patru valori:

• 1 pentru a căuta de la primul până la ultimul, adică de sus în jos (prestabilit, la fel ca atunci când nu este desemnat niciun argument search_mode )
• -1 pentru a căuta de la ultimul până la primul, adică de jos în sus
• 2 pentru o căutare binară în ordine crescătoare
• -2 pentru căutare binară în ordine descrescătoare

Cel mai bun mod de a înțelege puterea și versatilitatea noii funcție XLOOKUP este să o vedeți în acțiune într-o foaie de lucru Excel. În figura următoare, am o foaie de lucru cu un tabel simplu de date privind vânzările din 2019 aranjat pe țară. Pentru a utiliza XLOOKUP pentru a returna vânzările totale din acest tabel în celula E4 în funcție de țara pe care o introduceți în celula D4 a foii de lucru, parcurgeți acești pași:

Poziționați cursorul celulei în celula E4 a foii de lucru

Faceți clic pe opțiunea Căutare și referință din fila Formule, urmată de XLOOKUP în partea de jos a meniului derulant pentru a deschide caseta de dialog Argumente ale funcției.

Faceți clic pe celula D4 din foaia de lucru pentru a introduce referința celulei acesteia în caseta de text pentru argumentul Lookup_value.

Apăsați Tab pentru a selecta caseta de text pentru argumentul Lookup_array, apoi faceți clic pe celula A4 și țineți apăsată Shift în timp ce apăsați Ctrl-săgeată în jos pentru a selecta A4:A8 ca interval de căutare (deoarece intervalul A3:B8 este definit ca un tabel de date Excel, Tabelul 1[Țara] apare în caseta de text în locul intervalului A4:A8).

Apăsați Tab pentru a selecta caseta text argument Return_array, apoi faceți clic pe celula B4 și țineți apăsată Shift în timp ce apăsați Ctrl-săgeată în jos pentru a selecta B4:B8 ca interval care conține valorile care trebuie returnate pe baza rezultatelor căutării (care apare ca Tabelul 1[Vânzări totale] în caseta de text).

Faceți clic pe OK pentru a introduce formula XLOOKUP în celula E4.

Crearea unei formule cu XLOOKUP în celula E4 care returnează vânzări în funcție de țara introdusă în celula D4.

Excel introduce formula XLOOKUP în celula E4 a foii de lucru și returnează 4900 ca rezultat, deoarece Costa Rica este introdusă în prezent în celula de căutare D4 și, după cum puteți vedea în tabelul de vânzări din 2019, aceasta este într-adevăr vânzările totale realizate pentru această țară.

Deoarece XLOOKUP funcționează de la dreapta la stânga la fel de bine ca și de la stânga la dreapta, puteți utiliza această funcție la fel de bine pentru a returna țara din acest tabel de vânzări pe baza unei anumite cifre de vânzări. Figura următoare vă arată cum faceți acest lucru. De data aceasta, creați formula XLOOKUP în celula D4 și desemnați valoarea introdusă în celula E4 (11.000, în acest caz) ca argument search_value.

În plus, introduceți -1 ca argument match_mode pentru a suprascrie potrivirea exactă implicită a funcției, astfel încât Excel să returneze țara cu o potrivire exactă la valoarea vânzărilor introdusă în celula de căutare E4 sau cea cu vânzările totale imediat mai mici (Mexic cu 10.000 USD în acest caz, deoarece nu există nicio țară în acest tabel cu 11.000 USD din vânzări totale). Fără a desemna un argument match_mode pentru această formulă, Excel ar returna #NA ca rezultat, deoarece nu există o potrivire exactă la 11.000 USD în acest tabel de vânzări.

Crearea unei formule cu XLOOKUP în celula D4 care returnează țara pe baza vânzărilor introduse în celula E4

Deoarece funcția XLOOKUP este la fel de confortabilă în căutarea orizontală după coloană, precum și în verticală după rând, o puteți utiliza pentru a crea o formulă care efectuează o căutare bidirecțională (înlocuind nevoia de a crea o formulă care să combină funcțiile INDEX și MATCH ca în trecut). Următoarea figură, care conține tabelul programului de producție 2019 pentru numerele de piesă, de la AB-100 la AB-103 pentru lunile aprilie până în decembrie, vă arată cum se face acest lucru.

Crearea unei formule cu funcții XLOOKUP imbricate pentru a returna numărul de unități produse pentru o piesă într-o anumită lună

În celula B12, am creat următoarea formulă:

=XLOOKUP(căutare_parte,$A$3:$A$6,XLOOKUP(dată_căutare,$B$2:$J$2,$B$3:$J$6))

Această formulă începe prin definirea unei funcții XLOOKUP care caută vertical, după rând, o potrivire exactă cu intrarea piesei făcută în celula numită part_lookup (celula B10, în acest caz) în intervalul de celule $A$3:$A$6 din tabelul de producție . Rețineți, totuși, că argumentul return_array pentru această funcție originală LOOKUP este ea însăși o a doua funcție XLOOKUP.

Această a doua funcție XLOOKUP imbricată caută în intervalul de celule $B$2:$J$2 orizontal după coloană pentru o potrivire exactă cu data introdusă în celula numită date_lookup (celula B11, în acest caz). Argumentul return_array pentru această a doua funcție XLOOKUP imbricată este $B$3:$J$6, intervalul de celule al tuturor valorilor de producție din tabel.

Modul în care funcționează această formulă este că Excel calculează mai întâi rezultatul celei de-a doua funcții XLOOKUP imbricate, efectuând o căutare orizontală care, în acest caz, returnează matricea din intervalul de celule D3: D6 din coloana Jun-19 (cu valorile: 438, 153, 306 și 779) ca rezultat. Acest rezultat, la rândul său, devine argumentul return_array pentru funcția originală XLOOKUP care efectuează o căutare verticală pe rând pentru o potrivire exactă cu intrarea numărului de piesă făcută în celula B11 (denumită part_lookup). Deoarece, în acest exemplu, această celulă part_lookup conține AB-102, formula returnează doar valoarea de producție Jun-19, 306, din rezultatul celei de-a doua, următoarea funcție XLOOKUP.

Iată-l! O primă privire la XLOOKUP, o nouă funcție de căutare puternică, versatilă și destul de ușor de utilizat, care poate face nu numai căutările cu o singură valoare efectuate de funcțiile CĂUTARE V și HLOOKUP, ci și căutări de valori bidirecționale efectuate prin combinarea Funcțiile INDEX și MATCH, de asemenea.

* Din păcate, funcția XLOOKUP nu este compatibilă cu versiunile anterioare ale Microsoft Excel, care acceptă doar funcțiile CĂUTARE V și HLOOKUP sau compatibilă cu versiunile actuale care nu o includ încă ca una dintre funcțiile lor de căutare, cum ar fi Excel 2019 și Excel Online . Aceasta înseamnă că dacă partajați un registru de lucru care conține formule XLOOKUP cu colegii de muncă sau clienții care folosesc o versiune de Excel care nu include această nouă funcție de căutare, toate aceste formule vor returna #NUME? valorile de eroare atunci când își deschid foaia de lucru.