9.2 lecke · 2/2 · kb. 6 perc
HAHIBA, VKERES, XKERES és hasznos trükkök
Az FKERES alapjai után jönnek a gyakorlati fogások: hogyan rejtsd el a #HIÁNYZIK hibát, hogyan másold a képletet oldalra is, hogyan keress két feltétel alapján, és mit tud a modern XKERES.
Hibák eltüntetése: HAHIBA
Az előző lecke adóslistás példájában a nem tartozó ügyfeleknél #HIÁNYZIK jelent meg. Ez pontos, de csúnya, és ha az oszlopot összeadnád, a SZUM is hibát adna. A HAHIBA (IFERROR) függvény ezt orvosolja.
HAHIBA(érték; érték_hiba_esetén)Az első argumentum az a számítás, amit el akarsz végezni. Ha ez hibát ad, a függvény a második argumentumot adja vissza helyette, egyébként az első eredményét.
A "" egy nulla hosszúságú szöveg: a cella üresnek látszik. Ha számolni szeretnél az oszloppal, írj inkább 0-t:
| A | B | C | |
|---|---|---|---|
| 1 | kód | név | tartozás |
| 2 | T-150 | Jámbor Dóra | 793 092 |
| 3 | H-198 | Hajnal Farkas | 0 |
| 4 | F-289 | Koltai Paula | 1 235 331 |
| 5 | C-756 | Gémes Mária | 0 |
A HAHIBA a #HIÁNYZIK helyére 0-t írt, így az oszlop már összeadható.
Figyelem
A HAHIBA minden hibát elnyel, azt is, amit egy elgépelés okoz (például rossz oszlopszám miatti #HIV!). Először írd meg és ellenőrizd az FKERES-t magában, és csak a végén csomagold HAHIBA-ba.
Oldalra másolható FKERES: az OSZLOP() trükk
Készletértéket számolsz. A Készlet munkalapon telephelyenként látod a darabszámokat, a terméklistán pedig az árakat. A terméklista C:F oszlopába a telephelyenkénti készletértéket kell kiszámolni: darabszám szorozva az árral.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | cikkszám | osztály | össz. | Budaörs | Soroksár | Fehérvár | Csepel |
| 2 | K-101 | I. | 137 | 15 | 68 | 27 | 27 |
| 3 | K-102 | I. | 187 | 79 | 96 | 12 | 0 |
| 4 | K-103 | A | 248 | 82 | 97 | 5 | 64 |
A Készlet munkalap: a darabszámok az A2:G4 tartományban. A Budaörs a tábla 4. oszlopa.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | cikkszám | ár | Budaörs | Soroksár | Fehérvár | Csepel |
| 2 | K-101 | 2 699 | 40 485 | 183 532 | 72 873 | 72 873 |
| 3 | K-102 | 699 | 55 221 | 67 104 | 8 388 | 0 |
| 4 | K-103 | 2 799 | 229 518 | 271 503 | 13 995 | 179 136 |
A terméklista a kész készletértékekkel. Itt is a C oszlop a Budaörs.
A C2 képlete a Budaörs oszlophoz így indulna: =FKERES($A2;Készlet!$A$2:$G$4;4;HAMIS)*$B2. A cikkszám és az ár oszlopát rögzítetted, hogy a képlet jobbra másolható legyen. A gond a 4-es: Soroksárnál már 5 kellene, Fehérvárnál 6, Csepelnél 7.
Az argumentum nélküli OSZLOP (COLUMN) függvény annak az oszlopnak a számát adja, amelyben a képlet áll (A = 1, B = 2, C = 3…). A képlet a C oszlopban áll (3), a hozzá tartozó darabszám a Készlet tábla 4. oszlopában. Soroksárnál ez 4 és 5, Fehérvárnál 5 és 6: a különbség mindig 1. Tehát az oszlopszám helyére OSZLOP()+1 kerül.
Ellenőrzés: C2 = 15 × 2 699 = 40 485, D2 = 68 × 2 699 = 183 532. A képletet most már jobbra és lefelé is másolhatod.
Biztosabb megoldás: HOL.VAN
Az OSZLOP() trükk elromlik, ha valaki beszúr egy oszlopot. Biztosabb, ha az oszlopszámot a fejléc alapján keresed meg a HOL.VAN (MATCH) függvénnyel: =FKERES($A2;Készlet!$A$2:$G$4;HOL.VAN(C$1;Készlet!$A$1:$G$1;0);HAMIS)*$B2. A HOL.VAN megmondja, hányadik a „Budaörs” a Készlet lap fejlécében (a 4.), így a két táblázat oszlopainak sorrendje sem számít.
Keresés két feltétel alapján: összefűzött kulcs
Az FKERES egyetlen értéket keres. Mi van, ha a terméket és a kiszerelést együtt kell egyeztetni? Készíts a keresőtábla elejére egy segédoszlopot, amely a két adatot az & operátorral egy kulccsá fűzi.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | kulcs | termék | kiszerelés | ár |
| 2 | liszt|1 kg | liszt | 1 kg | 349 |
| 3 | liszt|5 kg | liszt | 5 kg | 1 590 |
| 4 | cukor|1 kg | cukor | 1 kg | 459 |
| 5 | cukor|5 kg | cukor | 5 kg | 2 190 |
Az A2 képlete: =B2&"|"&C2, lefelé másolva.
Ha a keresett termék az F2, a kiszerelés a G2 cellában áll („cukor” és „5 kg”), a keresésnél ugyanígy fűzöd össze őket:
Az eredmény 2 190. A „|” elválasztó azért kell, hogy két különböző pár ne adhasson azonos kulcsot (elválasztó nélkül az „ab” és „c”, illetve az „a” és „bc” is „abc” lenne).
Vízszintes táblázat: VKERES
A VKERES (HLOOKUP) az FKERES fektetett változata: a tábla első sorában keres, és a megadott sorszámú sorból ad eredményt.
VKERES(keresési_érték; tábla; sor_szám; [tartományban_keres])| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | vásárlás | 0 | 50 000 | 100 000 | 200 000 |
| 2 | kedvezmény | 0% | 3% | 5% | 8% |
| 3 | |||||
| 4 | vevő | összeg | kedvezmény | ||
| 5 | Szőke Petra | 120 000 | 5% | ||
| 6 | Hegyi Félix | 42 000 | 0% |
A kedvezménysávok vízszintesen, a B1:E2 tartományban.
Közelítő egyezés történik: a 120 000-nél kisebb vagy egyenlő legnagyobb sávhatár a 100 000, alatta 5% áll. A 42 000-hez a 0-s sáv tartozik. Itt is igaz: közelítő keresésnél az első sor legyen növekvő sorrendben, pontos egyezéshez pedig írj HAMIS-t a negyedik argumentumba.
A modern megoldás: XKERES
A Microsoft 365-ben és az Excel 2021-es és újabb változataiban elérhető az XKERES (XLOOKUP). Egyszerre váltja ki az FKERES-t és a VKERES-t, és a buktatóik nagy részét is.
XKERES(keresési_érték; keresési_tömb; visszaadandó_tömb; [ha_nincs_találat])Nem egy táblát és egy oszlopszámot adsz meg, hanem két oszlopot: azt, amelyikben keresni kell, és azt, amelyikből az eredmény jön. Az adóslistás példa XKERES-sel:
- Alapból pontos egyezést keres, nem kell HAMIS.
- A ha_nincs_találat argumentum helyettesíti a HAHIBA-t, és csak a „nincs találat” esetet kezeli, a valódi hibákat nem rejti el.
- Balra is keres: a visszaadandó oszlop lehet a keresőoszlop előtt is.
- Nincs oszlopszám, így egy beszúrt oszlop sem rontja el.
Van még két elhagyható argumentuma. Az ötödik az egyezési mód: a -1 azt jelenti, hogy „pontos egyezés, vagy ha nincs, a következő kisebb”. Ezzel a ponthatáros feladat is megoldható, és a táblának rendezettnek sem kell lennie:
Jó tudni: INDEX és HOL.VAN
Régebbi Excelben az XKERES helyett az INDEX és a HOL.VAN (MATCH) párosát használják. A HOL.VAN megmondja, hányadik sorban van a találat, az INDEX pedig kiveszi az adott sort a visszaadandó oszlopból: =INDEX($I$2:$I$5;HOL.VAN(A2;$G$2:$G$5;0)). Ez is tud balra keresni. Ha a munkafüzetet régebbi Excel-verzióval is megnyitják, ez a biztonságos választás, mert ott az XKERES nem működik.
Összefoglalás
- A HAHIBA(számítás; helyettesítő) a hibát egy általad megadott értékre cseréli, de minden hibát elrejt, ezért csak a végén használd.
- Az argumentum nélküli OSZLOP() a képlet oszlopszámát adja, így az FKERES oszlopszáma másoláskor magától léptethető. Biztosabb a HOL.VAN a fejléc alapján.
- Két feltételhez fűzd össze a kulcsot az
&operátorral, elválasztó karakterrel. - A VKERES a tábla első sorában keres, vízszintes táblázatokhoz.
- Az XKERES alapból pontos egyezéssel keres, balra is tud, és beépített „nincs találat” értéke van (Microsoft 365, Excel 2021+).
Gyakorlás
Töltsd le a gyakorló munkafüzetet, és oldd meg a megadott munkalapok feladatait. A sárga cellákba kerülnek a képletek. Ha elakadsz, nézd meg a megoldást.
Keresés táblázatban: gyakorló feladatok
- Készletérték Készletérték telephelyenként egyetlen lemásolható FKERES képlettel (OSZLOP()+1 trükk).
- Órabérek Órabér a kategória, státusz és szint összefűzött kulcsával keresve a Bértábla lapon.
- Bértábla Segédtábla: a keresési kulcs összefűzése három oszlopból.
- Szélerősség Szélerősség neve és fokozata vízszintes segédtáblából: VKERES és XKERES.
Ez volt a modul utolsó leckéje
Ellenőrizd a tudásodat a modul rövid kvízével: 6 kérdés, azonnali visszajelzéssel.