Kaip naudoti XLOOKUP su keliais kriterijais Excel lentelėse

Naudojant XLOOKUP funkciją Excel’e, lentelių sujungimas dažniausiai atliekamas pagal vieną stulpelį, pavyzdžiui, ID. Tačiau realiuose duomenyse dažnai pasitaiko situacijų, kai vien tik ID nepakanka.

Tokiais atvejais XLOOKUP gali grąžinti neteisingą rezultatą, nors formulė parašyta teisingai. Problema slypi ne formulėje, o pasirinktoje paieškos logikoje.

Jei dar tik mokaisi pagrindinės XLOOKUP logikos, pirmiausia gali perskaityti straipsnį: kaip sujungti Excel lenteles pagal ID naudojant XLOOKUP .

1. Kodėl vieno lookup kriterijaus nepakanka

Įsivaizduok, kad turi lentelę, kurioje tas pats ID pasikartoja kelis kartus, bet su skirtingomis datomis, miestais, produktais ar reikšmėmis.

Pavyzdžiui:

• tas pats klientas gali turėti kelis užsakymus
• tas pats ID gali būti susijęs su skirtingais miestais
• tas pats produktas gali turėti skirtingas kainas skirtingomis dienomis
• tas pats darbuotojas gali būti susijęs su keliomis operacijomis

Jei XLOOKUP ieško tik pagal ID, jis grąžins pirmą rastą reikšmę, kuri nebūtinai bus ta, kurios tau reikia.

Tokiu atveju reikia ne vieno, o kelių kriterijų. Tai leidžia tiksliau identifikuoti eilutę, kurią iš tikrųjų nori rasti.

Apie tai, kodėl verta tikrinti XLOOKUP rezultatus, plačiau rašome straipsnyje: kaip patikrinti, ar XLOOKUP rezultatai teisingi .

2. Sprendimas: keli kriterijai su XLOOKUP

Vienas paprasčiausių būdų – sujungti kelis kriterijus į vieną bendrą paieškos reikšmę.

Pavyzdžiui, jei turi:

• ID – pirmas kriterijus
• Miestą arba datą – antras kriterijus

Gali juos sujungti į vieną paieškos logiką:

Tokiu būdu XLOOKUP ieškos ne tik pagal ID, bet pagal ID ir antrą kriterijų kartu.

Tai leidžia tiksliai identifikuoti reikiamą eilutę ir išvengti situacijos, kai XLOOKUP grąžina pirmą rastą, bet ne tą rezultatą.

3. Praktinis pavyzdys: lentelių sujungimas pagal ID ir miestą

Tarkime, kad kairėje lentelėje turime klientų sąrašą, o dešinėje – pardavimų informaciją.

Problema ta, kad tas pats ID gali pasikartoti daugiau nei vieną kartą. Pavyzdžiui, klientas K001 gali būti susijęs ir su Vilniumi, ir su Kaunu. Jei XLOOKUP ieškotų tik pagal ID, rezultatas nebūtų pakankamai tikslus.

Tokiu atveju galima sujungti du kriterijus – ID ir miestą.

Šioje formulėje:

• [@ID]&[@Miestas] – sujungia ID ir miestą kairėje lentelėje
• Pardavimai[ID]&Pardavimai[Miestas] – sujungia ID ir miestą pardavimų lentelėje
• Pardavimai[Suma] – nurodo, kokią reikšmę reikia grąžinti
• „” – nurodo, kad jei reikšmė nerandama, langelis lieka tuščias

Pavyzdžiui:

• K001 + Vilnius → 1200
• K001 + Kaunas → 3500

Tai leidžia tiksliai sujungti lenteles net tada, kai vieno kriterijaus nepakanka.

XLOOKUP lentelių sujungimas pagal du kriterijus: ID ir miestą.

4. Trumpa video pamoka

Žemiau pateiktoje trumpoje pamokoje parodyta, kaip XLOOKUP formulėje sujungti du kriterijus ir pagal juos prijungti reikšmę iš kitos lentelės.

5. Alternatyva: FILTER funkcija

Jei nori daugiau lankstumo, gali naudoti FILTER funkciją. Ji leidžia grąžinti visas eilutes, kurios atitinka kelis kriterijus.

Pavyzdžiui:

Šis metodas ypač naudingas, kai:

• gali būti keli atitikmenys
• reikia matyti daugiau nei vieną rezultatą
• nori ne tik vienos reikšmės, bet platesnio rezultato

XLOOKUP dažniausiai patogus, kai nori grąžinti vieną konkretų rezultatą. FILTER labiau tinka tada, kai reikia matyti visas eilutes, atitinkančias pasirinktą logiką.

6. Dažniausios klaidos

Dirbant su keliais kriterijais, dažnai pasitaiko šios problemos:

• sujungiant kriterijus paliekami tarpai
• vienas kriterijus yra tekstas, o kitas – skaičius
• datos ar miestai skirtingose lentelėse įrašyti nevienodai
• kriterijai sujungiami neteisinga tvarka

Jei XLOOKUP grąžina #N/A, nors viskas atrodo teisingai, labai tikėtina, kad problema slypi duomenų paruošime.

Plačiau apie dažniausias XLOOKUP problemas skaityk straipsnyje: XLOOKUP neveikia? 3 dažniausios priežastys ir kaip jas išspręsti .

Kodėl tai svarbu

Vieno kriterijaus paieška tinka paprastoms lentelėms. Tačiau realiuose duomenyse dažnai reikia tikslesnės logikos.

Naudojant kelis kriterijus:

• sumažėja klaidų tikimybė
• gaunami tikslesni rezultatai
• galima dirbti su sudėtingesnėmis lentelėmis

Tai yra vienas iš svarbiausių žingsnių pereinant nuo paprasto Excel naudojimo prie pažangesnio darbo su duomenimis.

Mini kursas apie Excel lentelių jungimą su XLOOKUP

Paruošėme trumpą mini kursą, kuriame aiškiai parodome:

• kaip teisingai sujungti lenteles su XLOOKUP
• kada užtenka vieno kriterijaus, o kada reikia daugiau
• kaip išvengti dažniausių klaidų
• kaip dirbti su realiais duomenimis