17. nodaļa · 17.4
Meklēšana pēc diviem nosacījumiem vienlaikus
VLOOKUP un XLOOKUP meklē pēc vienas vērtības (skatiet VLOOKUP atgriež #N/A: kā to novērst un XLOOKUP: meklēšana pa kreisi un ar pielāgotu paziņojumu). Praksē bieži jāmeklē pēc divām lietām vienlaikus: pušķa cena izmērā L, klienta tarifs noteiktā gadā, produkta krājumi noteiktā krāsā. Tam ir trīs labi veidi, un visi darbojas ar parasto Enter. Ceturtais veids, ko bieži redzat internetā, Euro-Office bez papildu darbībām nedarbojas.
Kā piemēru izmantosim ziedu veikala Bloemisterij Het Zonnetje cenrādi. Viens un tas pats produkts tajā ir vairākas reizes, dažādos izmēros:
| A | B | C | |
|---|---|---|---|
| 1 | Produkts | Izmērs | Cena |
| 2 | Pušķis | S | 17,50 |
| 3 | Pušķis | M | 24,50 |
| 4 | Pušķis | L | 34,50 |
| 5 | Sēru vainags | M | 65,00 |
| 6 | Sēru vainags | L | 89,00 |
| 7 | Līgavas pušķis | M | 79,50 |
Meklētais produkts ir šūnā F2 (Pušķis), izmērs šūnā G2 (L). Mēs meklējam 34,50.
1. veids: XLOOKUP ar apvienotām vērtībām
=XLOOKUP(F2&"|"&G2;A2:A7&"|"&B2:B7;C2:C7;"nav atrasts")
Zīme & savieno tekstu. F2&"|"&G2 kļūst par "Pušķis|L". A2:A7&"|"&B2:B7 arī no katras saraksta rindas izveido šādu kombināciju ("Pušķis|S", "Pušķis|M" un tā tālāk), un XLOOKUP meklē tajās. Vertikālā svītra | starp vērtībām novērš to, ka divas dažādas kombinācijas nejauši veido vienādu tekstu. Ja kombinācija neeksistē, piemēram, sēru vainags izmērā S, Jūs saņemat "nav atrasts".

Ja pēc ievadīšanas paskatāties formulu joslā, pirms A2:A7 un B2:B7 ir redzama zīme @. To Euro-Office pievieno pats; rezultāts ir pareizs.
2. veids: FILTER visām atbilstībām
=FILTER(C2:C7;(A2:A7=F2)*(B2:B7=G2);"nav")
FILTER atgriež ne tikai pirmo, bet visas rindas, kas atbilst abiem nosacījumiem. Ja kombinācija sastopama divas reizes, Jūs redzat abas cenas vienu zem otras. Tāpēc parūpējieties, lai zem formulas būtu brīva vieta (skatiet Saraksti, kas atjaunojas paši, ar FILTER, UNIQUE un SORT). Lai “vai” vietā izmantotu “un”, izmantojiet plusa zīmi: (A2:A7="Pušķis")+(A2:A7="Sēru vainags").
3. veids: SUMIFS, ja kombinācija ir unikāla
=SUMIFS(C2:C7;A2:A7;F2;B2:B7;G2)
Izklausās dīvaini, jo Jūs taču neko nevēlaties saskaitīt. Taču, ja katra kombinācija ir sastopama tikai vienu reizi, summa ir tieši šī viena cena. Priekšrocība: SUMIFS ir vienkārša, ātra un pieejama jebkurā Excel versijā. Trūkums: ja kombinācija neeksistē, Jūs saņemat 0 (0,00 €), nevis paziņojumu. Un, ja kombinācija nejauši ir sarakstā divas reizes, abas cenas tiek saskaitītas.
Ko atradīsiet internetā: INDEX un MATCH ar reizināšanu
Daudzas Excel vietnes piedāvā =INDEX(C2:C7;MATCH(1;(A2:A7=F2)*(B2:B7=G2);0)). Euro-Office šī formula ar parasto Enter atgriež #N/A, pat ja kombinācija eksistē. Ja to noslēdzat ar Ctrl+Shift+Enter, formula saņem figūriekavas {…} un darbojas. To ir viegli aizmirst, tāpēc labāk izmantojiet iepriekš aprakstīto XLOOKUP vai FILTER.
Meklēšana pēc rindas un kolonnas
Ja Jūsu cenrādis ir tabula, kurā produkti ir viens zem otra un izmēri blakus cits citam (matrica), izmantojiet INDEX ar diviem MATCH. Ja produkti ir diapazonā A11:A13, izmēri S, M un L diapazonā B10:D10 un cenas diapazonā B11:D13:
=INDEX(B11:D13;MATCH(F2;A11:A13;0);MATCH(G2;B10:D10;0))
Pirmā MATCH atrod produkta rindu, otrā atrod izmēra kolonnu. INDEX paņem šūnu krustpunktā.
Kuru izvēlēties?
| Situācija | Labākā izvēle |
|---|---|
| Viena atbilde ar saprotamu paziņojumu, ja tā neeksistē | XLOOKUP ar apvienotām vērtībām |
| Failam jādarbojas arī vecā Excel versijā | SUMIFS |
| Var būt vairākas atbilstības | FILTER |
| Cenu tabula ar izmēriem kā kolonnu virsrakstiem | INDEX ar diviem MATCH |
Biežākās kļūdas
- #N/A, lai gan kombinācija tiešām ir sarakstā: bieži aiz vērtības ir atstarpe ("Pušķis ") vai skaitlis ir saglabāts kā teksts. Skatiet VLOOKUP atgriež #N/A: kā to novērst.
- Dažāda garuma diapazoni: =XLOOKUP(F2;A2:A7;C2:C50) atgriež #VALUE!. Visiem diapazoniem jāsākas un jābeidzas vienā un tajā pašā rindā.
- Veselas kolonnas (A:A) šajās formulās padara lielu failu lēnu, jo Euro-Office tad apvieno miljonu rindu. Izmantojiet fiksētu diapazonu, piemēram, A2:A500. Skatiet Lēnas izklājlapas darba paātrināšana.
- Kolēģis, kuram ir Excel 2019 vai vecāka versija, XLOOKUP un FILTER vietā redz #NAME?.
