17 skyrius · 17.4
Paieška pagal dvi sąlygas vienu metu
VLOOKUP ir XLOOKUP ieško pagal vieną reikšmę (žr. VLOOKUP rodo klaidą #N/A: kaip tai išspręsti ir XLOOKUP: paieška į kairę ir su individualiu pranešimu). Praktikoje dažnai reikia ieškoti pagal du dalykus vienu metu: L dydžio puokštės kainos, kliento tarifo tam tikrais metais, tam tikros spalvos prekės likučio. Tam yra trys geri būdai, ir visi jie veikia paspaudus įprastą Enter. Ketvirtasis būdas, kurį dažnai rasite internete, Euro-Office savaime neveikia.
Pavyzdžiui paimkime gėlių parduotuvės Bloemisterij Het Zonnetje kainoraštį. Ta pati prekė jame nurodyta kelis kartus, skirtingų dydžių:
| A | B | C | |
|---|---|---|---|
| 1 | Product | Dydis | Kaina |
| 2 | Boeket | S | 17,50 |
| 3 | Boeket | M | 24,50 |
| 4 | Boeket | L | 34,50 |
| 5 | Rouwstuk | M | 65,00 |
| 6 | Rouwstuk | L | 89,00 |
| 7 | Bruidsboeket | M | 79,50 |
Ieškoma prekė yra langelyje F2 (Boeket), dydis langelyje G2 (L). Ieškome 34,50.
1 būdas: XLOOKUP su sujungtomis reikšmėmis
=XLOOKUP(F2&"|"&G2;A2:A7&"|"&B2:B7;C2:C7;"nerasta")
Ženklas & sujungia tekstą. F2&"|"&G2 tampa "Boeket|L". A2:A7&"|"&B2:B7 kiekvieną sąrašo eilutę taip pat paverčia tokiu deriniu ("Boeket|S", "Boeket|M" ir t. t.), o XLOOKUP ieško tarp jų. Brūkšnys | tarp reikšmių neleidžia dviem skirtingiems deriniams atsitiktinai sudaryti to paties teksto. Jei derinio nėra, pavyzdžiui, S dydžio laidotuvių kompozicijos (Rouwstuk), gausite "nerasta".

Jei įvedę formulę pažiūrėsite į formulių juostą, prieš A2:A7 ir B2:B7 matysite ženklą @. Jį ten įrašo pati Euro-Office; rezultatas vis tiek teisingas.
2 būdas: FILTER visiems atitikmenims
=FILTER(C2:C7;(A2:A7=F2)*(B2:B7=G2);"nėra")
FILTER grąžina ne tik pirmąją, bet visas eilutes, atitinkančias abi sąlygas. Jei derinys pasitaiko du kartus, abi kainas matysite vieną po kita. Todėl pasirūpinkite, kad po formule būtų laisvos vietos (žr. Sąrašai, kurie atsinaujina patys, su FILTER, UNIQUE ir SORT). Norėdami vietoj „ir“ naudoti „arba“, rašykite pliuso ženklą: (A2:A7="Boeket")+(A2:A7="Rouwstuk").
3 būdas: SUMIFS, kai derinys unikalus
=SUMIFS(C2:C7;A2:A7;F2;B2:B7;G2)
Skamba keistai, nes nieko sudėti nenorite. Tačiau jei kiekvienas derinys pasitaiko tik kartą, suma tiesiog lygi tai vienai kainai. Privalumas: SUMIFS paprasta, greita ir veikia bet kurioje Excel versijoje. Trūkumas: jei derinio nėra, vietoj pranešimo gausite 0 (0,00 €). O jei derinys per klaidą įrašytas du kartus, gausite abiejų kainų sumą.
Ką rasite internete: INDEX ir MATCH su daugyba
Daugelis Excel svetainių siūlo =INDEX(C2:C7;MATCH(1;(A2:A7=F2)*(B2:B7=G2);0)). Euro-Office ši formulė, paspaudus įprastą Enter, grąžina #N/A, net jei derinys egzistuoja. Jei ją užbaigsite klavišais Ctrl+Shift+Enter, formulė gaus riestinius skliaustus {…} ir veiks. Tai lengva pamiršti, todėl verčiau naudokite aukščiau aprašytas XLOOKUP arba FILTER.
Paieška pagal eilutę ir stulpelį
Jei Jūsų kainoraštis yra lentelė, kurioje prekės išdėstytos viena po kita, o dydžiai šalia vienas kito (matrica), naudokite INDEX su dviem MATCH. Kai prekės yra A11:A13, dydžiai S, M ir L yra B10:D10, o kainos B11:D13:
=INDEX(B11:D13;MATCH(F2;A11:A13;0);MATCH(G2;B10:D10;0))
Pirmoji MATCH randa prekės eilutę, antroji dydžio stulpelį. INDEX paima langelį jų susikirtime.
Kurį būdą pasirinkti?
| Situacija | Geriausias pasirinkimas |
|---|---|
| Vienas atsakymas ir aiškus pranešimas, jei derinio nėra | XLOOKUP su sujungtomis reikšmėmis |
| Failas turi veikti ir senoje Excel versijoje | SUMIFS |
| Gali būti keli atitikmenys | FILTER |
| Kainų lentelė su dydžiais kaip stulpelių antraštėmis | INDEX su dviem MATCH |
Dažnos klaidos
- #N/A, nors derinys tikrai yra: dažnai po reikšme būna tarpas ("Boeket ") arba skaičius išsaugotas kaip tekstas. Žr. VLOOKUP rodo klaidą #N/A: kaip tai išspręsti.
- Nevienodo ilgio diapazonai: =XLOOKUP(F2;A2:A7;C2:C50) grąžina #VALUE!. Visi diapazonai turi prasidėti ir baigtis toje pačioje eilutėje.
- Visi stulpeliai (A:A) šiose formulėse sulėtina didelį failą, nes tada Euro-Office sujungia milijoną eilučių. Naudokite fiksuotą diapazoną, pavyzdžiui, A2:A500. Žr. Kaip pagreitinti lėtai veikiančią skaičiuoklę.
- Kolega, naudojantis Excel 2019 ar senesnę versiją, prie XLOOKUP ir FILTER matys #NAME?.
