Excel táblázatkezelés kezdőknek › 10.2 lecke

Excel tanfolyam · 10.2 lecke

FKERES pontos egyezéssel

A rendelésben csak a termékkód áll, az árat egy külön árlistából kell kikeresni. Kézzel minden sornál ugyanaz a mozdulat: megkeresni a kódot, leolvasni az árat. Az FKERES ezt teszi helyetted, egyetlen képlettel, akárhány sorra. Ez a táblázatkezelés egyik leghasznosabb és legtöbbet használt függvénye, érettségin is szinte biztosan találkozol vele.

  • ⏱ kb. 45 perc
  • 🎓 haladóbb
  • 🎒 10. osztálytól

A lecke végére

  • megnevezed és beállítod az FKERES négy argumentumát;
  • pontos egyezéssel keresel egy másik táblában;
  • helyesen számolod az oszlopszámot;
  • rögzíted a táblát, és másik munkalapról is keresel;
  • ismered az FKERES korlátait.

Amit már tudsz

  • a keresés fogalmait (10.1);
  • az abszolút hivatkozást (4.5);
  • a hivatkozást másik munkalapra (4.7);
  • a logikai értékeket (8.1).

Az FKERES szerkezete

ArgumentumJelentés
keresési_értékamit keresel (általában egy cella a sorodban, például a kód)
táblaa keresési tábla; az első oszlopában keres, ezért a kulcsoszloppal kell kezdődnie
oszlop_száma tábla hányadik oszlopából adja vissza az értéket (1 = maga a kulcsoszlop)
tartományban_keresHAMIS (vagy 0) = pontos egyezés; IGAZ (vagy elhagyva) = közelítő egyezés (10.3)

Mondd ki szavakkal (10.1): „Keresd meg az A2 értékét az F2:H20 tábla első oszlopában, és add vissza ugyanabból a sorból a tábla 3. oszlopának értékét, pontos egyezéssel.” Ha nincs ilyen kód, az eredmény #HIÁNYZIK (10.8).

Az oszlopszám

🔮 Jósold meg!

Az árlista a B2:D20 tartományban van: B = Kód, C = Termék, D = Ár. Az árat szeretnéd. Melyik oszlopszám a helyes: 3 vagy 4 (mert a D a munkalap 4. oszlopa)? És mit ad a 4?

Megoldás

3: az oszlopszámot a tábla első oszlopától számoljuk, nem a munkalap A oszlopától. B = 1, C = 2, D = 3. A 4 a táblán kívülre mutat, ezért #HIV! hibát ad (4.9). Ha a táblát módosítod (például egy oszlopot beszúrsz), az oszlopszám nem frissül magától: ilyenkor ellenőrizd! (A 10.7-ben ezt a HOL.VAN is kiszámolhatja.)

Rögzítés és másik lap

  1. Írd meg a képletet az első rendelési sorban, és a táblát rögzítsd: $F$2:$H$20 (F4, 4.5). A keresési érték (A2) maradjon relatív, mert soronként változik.
  2. Töltsd ki lefelé (4.4): minden sor a saját kódját keresi ugyanabban a táblában.
  3. Ha az árlista másik munkalapon van, a Tábla mezőbe kattintás után egyszerűen jelöld ki ott: =FKERES(A2;Árlista!$A$2:$C$20;3;HAMIS) (4.7).
  4. Ha az árlista bővülhet, adhatsz nagyobb tartományt (Árlista!$A$2:$C$500) vagy teljes oszlopokat (Árlista!$A:$C): az üres sorok nem zavarják a pontos keresést.

✅ Ellenőrzés

Szúrj ki két-három sort, és nézd meg kézzel az árlistában: tényleg ennyibe kerül? Az árlista alján (vagy egy külön cellában) a =DARABTELI(Árlista!$A$2:$A$20;A2) megmutatja, hányszor szerepel egy kód (egynél többször nem szabad, 10.1).

Korlátok

  • Csak jobbra: az FKERES mindig a tábla első oszlopában keres, és csak tőle jobbra lévő oszlopból ad vissza. Ha a név a kód bal oldalán áll, nem tudja visszaadni: erre az INDEX + HOL.VAN vagy az XKERES a megoldás (10.7).
  • Az első találat: ismétlődő kulcsnál mindig az elsőt adja vissza (10.1).
  • Rögzített oszlopszám: oszlop beszúrásakor elcsúszhat (2. rész).

Kihívás: rendelés és tanulói adatok

🛒 1. Rendelés az árlistából

  1. Készíts egy Árlista munkalapot (Kód, Termék, Ár) legalább 8 kitalált termékkel, és egy Rendelés lapot (Kód, Darab) 10 tétellel.
  2. A Rendelés lapon FKERES-sel keresd ki a termék nevét (2. oszlop) és az árát (3. oszlop).
  3. Számold ki a tételenkénti összeget (darab × ár) és a végösszeget.
  4. Írj át egy árat az árlistában: frissül a rendelés?
  5. Írj a rendelésbe egy nem létező kódot: mit látsz? (A szép kezelésről a 10.8 szól.)

🎓 2. Tanulókódból név

  1. Egy Diákok lapon: Tanulókód, Név, Osztály. Egy Dolgozat lapon csak Tanulókód és Pontszám.
  2. A Dolgozat lapon FKERES-sel írd ki minden tanulókód mellé a nevet és az osztályt.
  3. Számold ki DARABTELI-vel, hogy minden tanulókód pontosan egyszer szerepel-e a Diákok lapon (10.1).

Hibavadászat

🐞 1. Peti „majdnem jó” árai

Peti képlete: =FKERES(A2;Árlista!$A$2:$C$20;3). A legtöbb ár jónak tűnik, de néhány sorban egy másik termék ára jelent meg, és egy nem létező kódhoz is kapott árat.

Mi a megoldás?

A negyedik argumentum elmaradt, ezért az FKERES közelítő egyezéssel keresett (1. rész), ami csak növekvő sorrendű táblán értelmes, és a „legközelebbi kisebb” kódot adja, ha nem talál pontosat (10.3). Pontos kereséshez mindig írd ki: …;3;HAMIS). Ez az egyik leggyakoribb FKERES-hiba, mert hibaüzenet nélkül rossz eredményt ad.

🐞 2. Lili lefelé romló képlete

Lili a =FKERES(A2;F2:H20;3;HAMIS) képletet lefelé töltötte ki. Az első sorok jók, lejjebb egyre több #HIÁNYZIK jelenik meg, pedig a kódok szerepelnek az árlistában.

Mit tegyen?

A tábla nincs rögzítve, ezért lefelé csúszik: a 10. sorban már F11:H29, és az árlista első kilenc sora kimaradt belőle (4.4). Lili rögzítse a táblát: $F$2:$H$20 (3. rész).

Összefoglalás

  • =FKERES(keresési_érték;tábla;oszlop_szám;HAMIS): pontos keresés a tábla első oszlopában.
  • Az oszlopszámot a tábla első oszlopától számold.
  • A táblát rögzítsd ($), a keresési érték relatív maradjon.
  • A negyedik argumentumot (HAMIS) mindig írd ki pontos kereséshez.
  • Korlátok: csak jobbra ad vissza, az első találatot adja, az oszlopszám nem frissül magától.

Ellenőrizd magad!

Mit jelent a HAMIS az FKERES negyedik argumentumában?

Pontos egyezést.

A tábla C2:F30. Hányas oszlopszám adja az E oszlopot?

3 (C = 1, D = 2, E = 3).

Miért kell a táblát rögzíteni?

Mert kitöltéskor különben lefelé csúszik, és a felső sorok kimaradnak belőle.

👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (keresőfüggvények használata, több munkalap használata, cellahivatkozások), Adatbázis-kezelés (táblakapcsolat kulcs alapján). Az NKP-okostankönyvre épülő emelt szóbeli tétel keresőfüggvényes feladatának egyik megoldási módja; az érettségi feladatok egyik leggyakoribb függvénye.

Módszertan

A lecke a 10.1 „mondatba foglalt” keresését az FKERES argumentumaira képezi le, színes anatómiával. A jóslás az oszlopszám leggyakoribb félreértését teszi tudatossá. A hibavadászat két, hibaüzenet nélküli vagy félrevezető hibát dolgoz fel: a hiányzó negyedik argumentumot és a rögzítetlen táblát. A korlátok tudatosítása motiválja az INDEX + HOL.VAN és az XKERES megismerését (10.7).

Időterv

Az FKERES szerkezete: 8 perc. Az oszlopszám és jóslás: 6 perc. Rögzítés és másik lap: 8 perc. Korlátok: 3 perc. Kihívás: 16 perc. Hibavadászat: 4 perc.

Következő lecke: 10.3 FKERES közelítő egyezéssel és rendezett táblával

Pontszámból jegy, összegből kedvezménysáv: az FKERES közelítő egyezéssel, határtáblázattal.