XLOOKUP funkcija: kaip naudoti ir kuo ji geresnė už VLOOKUP

VLOOKUP ilgai buvo pirmoji funkcija, kurios išmokdavo kiekvienas, dirbantis su Excel, ir ji vis dar dažnai sutinkama senesniuose failuose. Naujesnėse Excel versijose atsirado XLOOKUP, kuri tą patį daro paprasčiau ir saugiau.

XLOOKUP sintaksė

=XLOOKUP(ko_ieškoti;kur_ieškoti;ką_grąžinti;[jei_nerasta];[atitikmens_tipas];[paieškos_kryptis])

Privalomi tik pirmi trys argumentai:

Stulpeliai kur_ieškoti ir ką_grąžinti turi būti vienodo ilgio, kitaip formulė grąžins klaidą #VALUE!.

Ta pati užduotis dviem būdais

Užduotis: prie užsakymų pridėti prekės kainą iš kainoraščio pagal prekės kodą. Lape „Kainos“ kodai yra A stulpelyje, o kainos C stulpelyje.

=VLOOKUP(A2;Kainos!A:C;3;FALSE)
=XLOOKUP(A2;Kainos!A:A;Kainos!C:C;"Nerasta")

VLOOKUP reikia nurodyti visą lentelę ir stulpelio, kuriame yra atsakymas, numerį. XLOOKUP nurodote, kur ieškoti ir ką grąžinti.

Kodėl VLOOKUP kartais grąžina ne tą reikšmę

1. Artimiausias atitikmuo pagal nutylėjimą. Jei paskutinio argumento nenurodote arba parašote TRUE, VLOOKUP ieško ne tikslaus atitikmens, o artimiausios mažesnės reikšmės ir tikisi, kad pirmas stulpelis surikiuotas. Nesurikiuotoje lentelėje gausite kitos prekės kainą be jokio klaidos pranešimo. Tai pavojingiausia VLOOKUP savybė, nes klaidos nesimato. XLOOKUP pagal nutylėjimą ieško tikslaus atitikmens.
2. Stulpelio numeris nesikeičia kartu su lentele. Įterpus naują stulpelį kainoraštyje tarp A ir C, formulė ir toliau grąžins trečią stulpelį, kuriame dabar yra kažkas kita. XLOOKUP nurodo patį stulpelį, todėl įterpimas jai netrukdo.
3. Ieško tik pirmame stulpelyje ir tik į dešinę. Jei kodas yra C stulpelyje, o pavadinimas A, VLOOKUP pavadinimo negrąžins. Tenka pertvarkyti lentelę arba naudoti INDEX ir MATCH.

Ką XLOOKUP moka papildomai

• Reikšmė, kai nerasta. Ketvirtas argumentas pakeičia #N/A klaidą jūsų tekstu arba nuliu, todėl formulės nereikia apgaubti IFERROR funkcija. Tai svarbu, nes IFERROR paslepia ir tas klaidas, kurias norėtumėte pamatyti.
• Paieška nuo galo. Šeštas argumentas -1 randa paskutinį atitikmenį, pavyzdžiui, naujausią prekės kainą kainų istorijoje, kurioje ta pati prekė kartojasi: =XLOOKUP(A2;Istorija!A:A;Istorija!C:C;"Nėra";0;-1)
• Keli stulpeliai iš karto. =XLOOKUP(A2;Kainos!A:A;Kainos!B:D) vienu kartu grąžina tris stulpelius.
• Intervalai. Kai nuolaidą reikia rasti pagal užsakymo sumą, penktas argumentas -1 grąžina tikslų atitikmenį arba artimiausią mažesnį.

Formulė =XLOOKUP(B2;Nuolaidos!A:A;Nuolaidos!B:B;0;-1) 800 € užsakymui grąžins 3 %.

Kada XLOOKUP netinka

XLOOKUP yra Microsoft 365, Excel 2021, Excel 2024 ir Excel naršyklėje. Excel 2019 ir senesnėse versijose šios funkcijos nėra: formulės ten neveiks, o perskaičiavus lapą langeliuose atsiras klaida #NAME?.

Todėl prieš rašydami XLOOKUP į failą, kurį siunčiate klientams ar tiekėjams, pasiteiraukite, kokią Excel versiją jie naudoja. Jei nežinote, saugiausia rinktis INDEX ir MATCH:

=INDEX(Kainos!C:C;MATCH(A2;Kainos!A:A;0))

Šis derinys veikia visose versijose, gali ieškoti į kairę ir nesugenda įterpus stulpelį. Svarbu nepamiršti nulio MATCH funkcijos gale: be jo MATCH taip pat ieško artimiausio atitikmens.

Google Sheets XLOOKUP taip pat turi ir ji veikia taip pat kaip Excel, todėl tokią formulę galima perkelti iš vieno įrankio į kitą.

Kaip INDEX ir MATCH veikia, kaip ieškoti pagal dvi sąlygas ir eilutės ir stulpelio sankirtoje, plačiau aprašiau straipsnyje INDEX ir MATCH funkcija Excel.

XLOOKUP pagal kelias sąlygas

Kai reikšmė priklauso nuo dviejų stulpelių, pavyzdžiui, prekės ir tiekėjo, sąlygas galima sudauginti:

=XLOOKUP(1;(Kainos!A:A=G2)*(Kainos!B:B=H2);Kainos!C:C;"Nerasta")

Kiekviena sąlyga grąžina TRUE arba FALSE, o daugyba palieka vienetą tik ten, kur abi teisingos. Dideliuose failuose vietoje viso stulpelio nurodykite intervalą, pavyzdžiui A2:A5000, kad formulė veiktų greičiau.

Patarimai, kurie galioja abiem funkcijoms

• Naudokite lenteles, o ne visus stulpelius. Pavertus kainoraštį Excel lentele (Ctrl+T), formulė atrodo taip: =XLOOKUP([@Kodas];Kainos[Kodas];Kainos[Kaina];"Nerasta"). Ją lengviau perskaityti, o naujos kainoraščio eilutės įtraukiamos automatiškai.
• Patikrinkite tarpus ir formatą. Dažniausia priežastis, kodėl reikšmė „nerandama“, nors ji aiškiai yra, yra tarpas kodo gale arba kodas, vienoje lentelėje išsaugotas kaip tekstas, o kitoje kaip skaičius. TRIM funkcija ir vienodas stulpelio formatas išsprendžia daugumą tokių atvejų. Kaip atpažinti tekstu išsaugotus skaičius, aprašiau straipsnyje kodėl Excel nesumuoja skaičių ir neatpažįsta datų.
• Nekurkite tūkstančių paieškos formulių kas savaitę keičiamiems duomenims. Tokie failai ima lėtėti ir lūžti. Jei sujungimas kartojasi reguliariai, jį verta perkelti į Power Query. Kaip tai padaryti, rašiau straipsnyje kaip sujungti kelis Excel failus į vieną lentelę.

Trumpai