Excel táblázatkezelés kezdőknek › 10.3 lecke
Excel tanfolyam · 10.3 lecke
FKERES közelítő egyezéssel és rendezett táblával
A 8.4-ben négy egymásba ágyazott HA kellett ahhoz, hogy egy pontszámból jegy legyen. A 8.5-ben megírtuk a határtáblázatot. Most jön a jutalom: egyetlen rövid FKERES ugyanezt elvégzi, akárhány kategória van. A közelítő egyezés nem a pontosan egyező értéket keresi, hanem azt a sávot, amelybe a szám beleesik. Ehhez egy dolog kell: a tábla legyen jól felépítve és rendezve.
- ⏱ kb. 45 perc
- 🎓 haladóbb
- 🎒 10. osztálytól
A lecke végére
- közelítő egyezéssel keresel határtáblázatban;
- tudod, miért kell a táblát növekvő sorrendbe rendezni;
- kezeled az első határ alatti értéket;
- alsó határos táblát készítesz a szabály szövegéből;
- összeveted a megoldást az egymásba ágyazott HA-val.
Amit már tudsz
A közelítő egyezés
| H: Alsó határ | I: Jegy | J: Megnevezés |
|---|---|---|
| 0 | 1 | elégtelen |
| 25 | 2 | elégséges |
| 40 | 3 | közepes |
| 55 | 4 | jó |
| 70 | 5 | jeles |
- Jegy:
=FKERES(B2;$H$2:$J$6;2;IGAZ) - Megnevezés:
=FKERES(B2;$H$2:$J$6;3;IGAZ) - A negyedik argumentum
IGAZ(vagy1, vagy elhagyva): közelítő egyezés.
Hogyan „gondolkodik”? Végigmegy a kulcsoszlopon, és azt a sort választja, amelyben a legnagyobb olyan érték áll, amely még nem nagyobb a keresettnél. A 63 pontnál: 0 ✓, 25 ✓, 40 ✓, 55 ✓, 70 ✗ → az 55-ös sor → 4-es, „jó”. Ez pontosan az alsó határos lánc logikája (8.5).
💡 Az első határ alatt
Ha a keresett érték kisebb a tábla első értékénél (például −5 vagy egy 0 helyett 10-zel kezdődő tábla esetén a 7 pont), nincs megfelelő sor, és #HIÁNYZIK lesz az eredmény. Ezért a határtáblázat mindig a legkisebb lehetséges értékkel kezdődjön (itt 0).
Miért kell rendezni?
🔮 Jósold meg!
Lili a határtáblázatot „jegy szerint visszafelé” írta: 70 → 5, 55 → 4, 40 → 3, 25 → 2, 0 → 1. A közelítő FKERES milyen jegyet ad a 63 pontra?
Megoldás
Valószínűleg rossz eredményt (vagy #HIÁNYZIK-ot), hibaüzenet nélkül. A közelítő keresés feltételezi, hogy a kulcsoszlop növekvő sorrendű, és ennek megfelelően, „ugrásokkal” keres (a gyors bináris keresés elvén). Fordított sorrendben a feltételezés hamis, és az eredmény kiszámíthatatlan. Szabály: közelítő egyezésnél a kulcsoszlop mindig növekvő sorrendben. Rendezd a táblát a határ szerint (7.2).
⚠️ Pontos egyezésnél nem kell rendezni
A pontos egyezés (HAMIS) a tábla sorrendjétől függetlenül működik (10.2). A rendezés csak a közelítő egyezéshez kötelező.
A határok a szabályból
A közelítő FKERES mindig a „legalább” logikát követi: egy sor akkor illik, ha a keresett érték eléri az alsó határt. A szabály szövegéből így készíts táblát:
| Szabály | Alsó határok a táblában |
|---|---|
| „Aki 50 pontot elért, de 67-et nem, az közepes.” | 50 → közepes, 67 → a következő jegy (a felső határ a következő sor alsó határa lesz) |
| „25–39 pont: elégséges” (egész pontszámok) | 25 → elégséges, 40 → a következő |
| „1000 Ft-ig nincs kedvezmény, 1000 Ft felett 5%” (egész forint) | 0 → 0%, 1001 → 5% (mert az 1000 még a „-ig” sávba tartozik, 8.5) |
✅ Teszteld a határokat!
Ugyanúgy, mint a 8.4-ben: próbáld ki pontosan a határértékeket (24, 25, 39, 40, 70) és a legkisebb, illetve legnagyobb lehetséges értéket (0, 100). Ha valamelyik rossz sávba kerül, a tábla alsó határai hibásak.
FKERES vagy HA?
| Egymásba ágyazott HA (8.4) | Közelítő FKERES | |
|---|---|---|
| Képlet hossza | a kategóriákkal nő | mindig rövid |
| Határok módosítása | a képletben vagy hivatkozott cellákban | csak a táblázatban |
| Új kategória | új HA a képletbe | új sor a táblázatba (és a tartomány bővítése) |
| Hibalehetőség | zárójelek, sorrend | rendezetlen tábla, hiányzó legkisebb határ |
Két-három kategóriánál mindkettő jó. Négy-öt vagy több kategóriánál a táblázatos megoldás áttekinthetőbb és könnyebben karbantartható.
Kihívás: jegyek és kedvezménysávok
📝 1. A 8.4 pontszámlistája újra
- Vedd elő a 8.4-ben készült pontszámlistát, és mellé a határtáblázatot (0, 25, 40, 55, 70 → 1–5, megnevezéssel).
- Számold ki új oszlopokban a jegyet és a megnevezést közelítő FKERES-sel.
- Hasonlítsd össze a 8.4-es egymásba ágyazott HA eredményével: minden sorban egyezik? (Tipp: egy ellenőrző oszlop:
=C2=E2, 8.1.) - Az iskola új jegyhatárokat vezet be (30, 45, 60, 80): írd át csak a táblát. Melyik megoldást volt könnyebb frissíteni?
🏷️ 2. Kedvezménysávok
- A büfé szabálya: „1000 Ft-ig nincs kedvezmény; 1000 Ft felett 3000 Ft-ig 5%; 3000 Ft felett 10%” (egész forintokban).
- Készíts határtáblázatot (vigyázz a „felett” szóra, 3. rész), és közelítő FKERES-sel számold ki tíz vásárlás kedvezményét és a fizetendő összeget.
- Teszteld: 999, 1000, 1001, 3000, 3001 Ft.
Hibavadászat
🐞 1. Peti felső határai
Peti a határtáblázatba a sávok felső határát írta: 24 → 1, 39 → 2, 54 → 3, 69 → 4, 100 → 5. Most a 30 pontos dolgozat 1-est kap, a 60 pontos 3-ast.
Mi a megoldás?
A közelítő FKERES az alsó határokkal dolgozik (1. rész): a 30-nál a legnagyobb, még nem nagyobb érték a 24, ezért az 1-es sor jön. Peti írja át a táblát alsó határokra: 0 → 1, 25 → 2, 40 → 3, 55 → 4, 70 → 5.
🐞 2. Lili hiányzó legkisebb határa
Lili határtáblázata így kezdődik: 25 → 2, 40 → 3… (az elégtelent kihagyta, „mert az úgyis az alja”). A 20 pontos dolgozatoknál #HIÁNYZIK jelenik meg.
Mit tegyen?
A 20 kisebb a tábla legkisebb értékénél, ezért nincs megfelelő sor (1. rész). Lili vegye fel a táblázat elejére a 0 → 1 sort is, hogy minden lehetséges érték találjon sávot.
Összefoglalás
- =FKERES(érték;tábla;oszlop;IGAZ): a legnagyobb, még nem nagyobb kulcs sorát adja vissza.
- A kulcsoszlop növekvő sorrendben, alsó határokkal, a legkisebb lehetséges értékkel kezdve.
- Rendezetlen tábla: hibaüzenet nélküli rossz eredmény; az első határ alatt: #HIÁNYZIK.
- A „felett” határ egész számoknál eggyel nagyobb alsó határt jelent.
- Sok kategóriánál a határtáblázat + FKERES áttekinthetőbb, mint a HA-lánc.
Ellenőrizd magad!
Mit ad a közelítő FKERES a 47 pontra a fenti határtáblán?
3-ast (közepes): a 40-es sort.
Miért kell a táblát növekvő sorrendbe rendezni?
Mert a közelítő keresés ezt feltételezi; különben kiszámíthatatlan eredményt ad.
Mit kell a határtábla első sorába írni?
A legkisebb lehetséges értéket (például 0).
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (keresőfüggvények, feltételtől függő számítások); kapcsolódás a programozáshoz (rendezett adatban való keresés, a bináris keresés elve) és a matematikához (intervallumok). Az NKP-okostankönyvre épülő emelt szóbeli tétel segédtáblás osztályzási feladatának megoldási módja.
Módszertan
A lecke a 8.4 és a 8.5 munkáját „jutalmazza”: ugyanaz a probléma egy rövidebb, karbantarthatóbb megoldással. A jóslás a rendezés szükségességét tapasztalati úton mutatja meg, és megemlíti a mögötte álló bináris keresést. A szabályból táblázat típusú gyakorlat a határok pontos értelmezését (8.5) ismétli, a két megoldás összevetése a megfelelő eszköz megválasztására nevel.
Időterv
A közelítő egyezés: 8 perc. Miért kell rendezni és jóslás: 6 perc. A határok a szabályból: 7 perc. FKERES vagy HA: 4 perc. Kihívás: 16 perc. Hibavadászat: 4 perc.
Következő lecke: 10.4 VKERES és vízszintes adatok
Ha a táblázat soraiban vannak a kulcsok: a VKERES, az FKERES „fekvő” testvére.
