17. fejezet · 17.4
Keresés egyszerre két feltétel alapján
Az FKERES és az XKERES egyetlen érték alapján keres (lásd: FKERES #N/A hibát ad: így oldja meg és XKERES: keresés balra is, saját üzenettel). A gyakorlatban azonban sokszor két dologra kell egyszerre keresni: egy L méretű csokor árára, egy ügyfél adott évi díjára vagy egy termék adott színű készletére. Erre három jó módszer létezik, és mindegyik egyszerűen az Enter billentyűvel működik. Egy negyedik módszer, amellyel az interneten gyakran találkozhat, az Euro-Office-ban nem működik minden további nélkül.
Példaként a Bloemisterij Het Zonnetje árlistáját használjuk. Ugyanaz a termék többször, különböző méretekben szerepel benne:
| A | B | C | |
|---|---|---|---|
| 1 | Termék | Méret | Ár |
| 2 | Csokor | S | 17,50 |
| 3 | Csokor | M | 24,50 |
| 4 | Csokor | L | 34,50 |
| 5 | Koszorú | M | 65,00 |
| 6 | Koszorú | L | 89,00 |
| 7 | Menyasszonyi csokor | M | 79,50 |
A keresett termék az F2 cellában van (Csokor), a méret a G2 cellában (L). A 34,50 értéket keressük.
1. módszer: XKERES összefűzött értékekkel
=XKERES(F2&"|"&G2;A2:A7&"|"&B2:B7;C2:C7;"nem található")
Az & jel összefűzi a szövegeket. Az F2&"|"&G2 eredménye „Csokor|L”. Az A2:A7&"|"&B2:B7 a lista minden sorából ugyanilyen kombinációt készít („Csokor|S”, „Csokor|M” és így tovább), az XKERES pedig ezek között keres. A közéjük tett | függőleges vonal megakadályozza, hogy két különböző kombináció véletlenül ugyanazt a szöveget adja. Ha a kombináció nem létezik, például S méretű koszorú, akkor a „nem található” szöveget kapja eredményül.

Ha a beírás után megnézi a szerkesztőlécet, egy @ jelet lát az A2:A7 és a B2:B7 előtt. Ezt az Euro-Office maga teszi oda, az eredmény ettől még helyes.
2. módszer: SZŰRŐ az összes találathoz
=SZŰRŐ(C2:C7;(A2:A7=F2)*(B2:B7=G2);"nincs")
A SZŰRŐ nem csak az első, hanem az összes olyan sort visszaadja, amely mindkét feltételnek megfelel. Ha egy kombináció kétszer szerepel, mindkét árat egymás alatt látja. Ezért ügyeljen arra, hogy a képlet alatt legyen szabad hely (lásd: Önmagukat frissítő listák a SZŰRŐ, EGYEDI és SORBA.RENDEZ függvénnyel). Ha „és” helyett „vagy” feltételt szeretne, használjon pluszjelet: (A2:A7="Csokor")+(A2:A7="Koszorú").
3. módszer: SZUMHATÖBB, ha a kombináció egyedi
=SZUMHATÖBB(C2:C7;A2:A7;F2;B2:B7;G2)
Elsőre furcsán hangzik, hiszen nem szeretne semmit összeadni. Ha azonban minden kombináció csak egyszer fordul elő, az összeg éppen az az egy ár. Az előnye: a SZUMHATÖBB egyszerű, gyors, és minden Excel-verzió ismeri. A hátránya: ha a kombináció nem létezik, üzenet helyett 0 (0,00 €) az eredmény. Ha pedig a kombináció véletlenül kétszer szerepel, a két ár összegét kapja.
Amit az interneten talál: INDEX és HOL.VAN szorzással
Sok Excel-oldal ezt a képletet javasolja: =INDEX(C2:C7;HOL.VAN(1;(A2:A7=F2)*(B2:B7=G2);0)). Az Euro-Office-ban ez a képlet egyszerű Enter esetén #N/A hibát ad, akkor is, ha a kombináció létezik. Ha a Ctrl+Shift+Enter billentyűkombinációval zárja le, a képlet kapcsos zárójelek {…} közé kerül, és így már működik. Ezt azonban könnyű elfelejteni, ezért inkább a fenti XKERES vagy SZŰRŐ függvényt használja.
Keresés sor és oszlop alapján
Ha az árlistája olyan táblázat, amelyben a termékek egymás alatt, a méretek pedig egymás mellett szerepelnek (mátrix), akkor az INDEX függvényt két HOL.VAN függvénnyel használja. Ha a termékek az A11:A13 tartományban, az S, M és L méretek a B10:D10 tartományban, az árak pedig a B11:D13 tartományban vannak:
=INDEX(B11:D13;HOL.VAN(F2;A11:A13;0);HOL.VAN(G2;B10:D10;0))
Az első HOL.VAN a termék sorát keresi meg, a második a méret oszlopát. Az INDEX a kettő metszéspontjában lévő cellát adja vissza.
Melyiket válassza?
| Helyzet | Legjobb választás |
|---|---|
| Egyetlen eredmény, rendezett üzenettel, ha nem létezik | XKERES összefűzött értékekkel |
| A fájlnak régebbi Excelben is működnie kell | SZUMHATÖBB |
| Több találat is lehetséges | SZŰRŐ |
| Ártábla, amelyben a méretek az oszlopfejlécek | INDEX két HOL.VAN függvénnyel |
Buktatók
- #N/A, pedig a kombináció valóban szerepel: gyakran egy érték mögötti szóköz („Csokor ”) vagy szövegként tárolt szám okozza. Lásd: FKERES #N/A hibát ad: így oldja meg.
- Eltérő hosszúságú tartományok: az =XKERES(F2;A2:A7;C2:C50) eredménye #VALUE!. Ügyeljen arra, hogy minden tartomány ugyanabban a sorban kezdődjön és végződjön.
- Teljes oszlopok (A:A) használata ezekben a képletekben lelassítja a nagy fájlokat, mert az Euro-Office ilyenkor egymillió sort fűz össze. Használjon rögzített tartományt, például A2:A500. Lásd: Lassú táblázat felgyorsítása.
- Ha egy kollégája Excel 2019-et vagy régebbi verziót használ, az XKERES és a SZŰRŐ függvénynél #NAME? hibát lát.
