9.1 lecke · 1/2 · kb. 6 perc
Az FKERES függvény
Az FKERES (VLOOKUP) az Excel egyik legtöbbet használt függvénye: egy listából kikeresi a hozzád tartozó sort, és onnan hoz át egy adatot. Ebben a leckében megnézed a két üzemmódját (közelítő és pontos egyezés), és azt, hogy mikor kapsz #HIÁNYZIK hibát.
Hogyan működik az FKERES?
Van egy keresett értéked (például egy pontszám vagy egy ügyfélkód) és egy táblázatod, amelyben ehhez kapcsolódó adatok állnak. Az FKERES a táblázat első oszlopában felülről lefelé keresi a megfelelő sort, majd abból a sorból a megadott sorszámú oszlop tartalmát adja vissza.
FKERES(keresési_érték; tábla; oszlop_szám; [tartományban_keres])- keresési_érték: amit keresel, általában egy cellahivatkozás (B2).
- tábla: a keresőtáblázat tartománya, fejléc nélkül. Az első oszlopa a keresőoszlop.
- oszlop_szám: hányadik oszlopból kéred az eredményt, a tábla első oszlopától számolva (az első oszlop az 1).
- tartományban_keres: elhagyható. Ha elhagyod, vagy IGAZ, akkor közelítő egyezést kapsz. Ha HAMIS (vagy 0), akkor pontos egyezést.
Figyelem
Az oszlop_szám a tábla oszlopait számolja, nem a munkalapét. Ha a tábla az E oszlopban kezdődik, akkor az E oszlop az 1-es, a H oszlop a 4-es.
Közelítő egyezés: a ponthatárok példája
A hallgatók dolgozatot írtak, a pontszámok már megvannak. Az értékelést egy ponthatár-táblázat alapján kell megadnod. Ez tipikus közelítő keresés: a 240 pont nem szerepel a táblázatban, de tudjuk, melyik sávba esik.
| A | B | C | |
|---|---|---|---|
| 1 | név | pontszám | értékelés |
| 2 | Ambrus Regina | 240 | közepes |
| 3 | Árva Elvira | 310 | jeles |
| 4 | Bagi Mihály | 102 | elégtelen |
| 5 | Bakos Zsuzsanna | 257 | közepes |
| 6 | Benkő Kornél | 215 | elégséges |
A hallgatók listája: a C oszlopot az FKERES tölti ki.
| E | F | G | H | |
|---|---|---|---|---|
| 1 | alsó határ | felső határ | teljesítmény | értékelés |
| 2 | 0 | 198 | 60% | elégtelen |
| 3 | 199 | 231 | 70% | elégséges |
| 4 | 232 | 264 | 80% | közepes |
| 5 | 265 | 297 | 90% | jó |
| 6 | 298 | 330 | 100% | jeles |
A ponthatárok táblázata ugyanazon a munkalapon. A kijelölt E2:H6 a keresőtábla, a fejléc nem része.
A C2 cella képlete:
Mit csinál a függvény? Az E oszlopban azt a legnagyobb számot keresi, amely még kisebb vagy egyenlő, mint 240. Ez a 232. A 232-es sor 4. oszlopában a „közepes” áll, ez lesz az eredmény. Ugyanígy a 310-hez a 298-as sor tartozik (jeles), a 215-höz a 199-es (elégséges).
A felső határ oszlopra a függvénynek nincs is szüksége: a sávot az alsó határok egyértelműen kijelölik. Az embereknek viszont segít olvasni a táblázatot.
A közelítő egyezés feltétele
A tábla első oszlopában az értékeknek növekvő sorrendben kell állniuk. Ha nincsenek rendezve, az FKERES sokszor hibaüzenet nélkül, csendben ad rossz eredményt. Ez a legveszélyesebb hiba, mert észre sem veszed.
Pontos egyezés: az adóslista példája
Most egy ügyféllistád van kódokkal, és egy külön adóslistád. A tartozásokat át szeretnéd vezetni az ügyféllistára. Itt nincs értelme „közelítőleg” keresni: a T-150-es kódhoz pontosan a T-150-es sor kell.
| A | B | C | |
|---|---|---|---|
| 1 | kód | név | tartozás |
| 2 | T-150 | Jámbor Dóra | 793 092 |
| 3 | H-198 | Hajnal Farkas | #HIÁNYZIK |
| 4 | F-289 | Koltai Paula | 1 235 331 |
| 5 | C-756 | Gémes Mária | #HIÁNYZIK |
Az ügyféllista. A C2 képlete lefelé másolva tölti ki az oszlopot.
| G | H | I | |
|---|---|---|---|
| 1 | kód | név | tartozás |
| 2 | C-473 | Hegyi Károly | 546 880 |
| 3 | T-150 | Jámbor Dóra | 793 092 |
| 4 | F-289 | Koltai Paula | 1 235 331 |
| 5 | M-875 | Virág Botond | 466 015 |
Az adóslista (G2:I5). A kódok itt nincsenek sorrendben, pontos egyezésnél erre nincs is szükség.
A negyedik argumentum, a HAMIS kéri a pontos egyezést. Helyette 0-t is írhatsz: =FKERES(A2;$G$2:$I$5;3;0) ugyanezt adja. Ha az első oszlopban több azonos érték is van, az FKERES mindig a legfelső találatot használja.
Pontos egyezésnél a kis- és nagybetű nem számít: a „t-150” is megtalálja a „T-150” sort.
Tipp
Ha a keresőtáblát táblázattá alakítottad (Ctrl+T) és elnevezted, például „adósok” néven, akkor a tartomány helyére a táblázat nevét is írhatod: =FKERES(A2;adósok;3;HAMIS). A táblázat neve automatikusan a fejléc nélküli adatterületet jelenti, és új sorok hozzáadásakor is követi a táblázatot.
Ha nincs találat: #HIÁNYZIK
A fenti példában Hajnal Farkasnak és Gémes Máriának nincs tartozása, ezért a kódjuk nem szerepel az adóslistán. Pontos egyezésnél ilyenkor az eredmény #HIÁNYZIK. Ez nem a képlet hibája: azt jelzi, hogy a keresett érték nincs meg az első oszlopban.
Közelítő egyezésnél akkor kapsz #HIÁNYZIK hibát, ha a keresett érték kisebb, mint az első oszlop legkisebb értéke. A ponthatár-táblázatban egy -5 pontos dolgozat adna ilyen eredményt, hiszen az első alsó határ 0.
Gyakori okok, ha „biztosan ott van”, mégsem találja:
- az egyik oldalon szövegként tárolt szám áll (a „150” szöveg nem egyenlő a 150 számmal);
- felesleges szóköz van a kód végén („T-150 ” nem egyenlő a „T-150” értékkel);
- a tábla tartománya nem fedi le az összes sort.
A #HIÁNYZIK eltüntetéséről a következő leckében, a HAHIBA függvénynél lesz szó.
Két szabály, amit mindig tarts be
A keresőoszlop legyen az első oszlop
Az FKERES csak a tábla első oszlopában keres, és csak tőle jobbra tud adatot visszaadni. Ha a kód a H oszlopban állna, a név pedig a G-ben, akkor a $G$2:$I$5 tartománnyal nem működne: a tábla tartományát úgy kell megadnod, hogy a kódoszlopnál kezdődjön. Balra keresni az FKERES nem tud, erre az XKERES vagy az INDEX és HOL.VAN páros való.
Ha az oszlop_szám nagyobb, mint a tábla oszlopainak száma, #HIV! hibát kapsz, ha 1-nél kisebb, #ÉRTÉK! hibát.
A tábla tartománya legyen abszolút
A képletet lefelé másolod, a keresett érték (B2, B3, B4…) változik, a keresőtábla viszont ugyanaz marad. Ezért a tartomány abszolút hivatkozás: $E$2:$H$6. Beíráskor a tartomány kijelölése után nyomd meg az F4 billentyűt, és az Excel beteszi a dollárjeleket.
Figyelem
Ha elfelejted a dollárjeleket, a C3 cellában már E3:H7 lesz a tábla, és az első sávot (0 pont) elveszíted. Az eredmény egy darabig jónak tűnik, aztán valamelyik sorban hibás vagy #HIÁNYZIK lesz.
Összefoglalás
- Az FKERES a tábla első oszlopában keres, és a megadott sorszámú oszlopból ad vissza adatot ugyanabból a sorból.
- Közelítő egyezés (alapértelmezés): a legnagyobb, a keresett értéknél kisebb vagy azzal egyenlő értéket találja meg, az első oszlopnak növekvő sorrendben kell lennie.
- Pontos egyezés: a negyedik argumentum HAMIS vagy 0, a sorrend nem számít.
- Ha nincs találat, az eredmény #HIÁNYZIK.
- A tábla tartománya legyen abszolút (F4), és a keresőoszlop legyen az első oszlopa.
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
- Ponthatárok Érdemjegy és szöveges értékelés pontszám alapján, FKERES közelítő egyezéssel.
- Tartozások Ügyfelek tartozása az Adóslista lapról, FKERES pontos egyezéssel és HAHIBA-val.