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

  • az FKERES-t pontos egyezéssel (10.2);
  • az egymásba ágyazott HA-kat (8.4);
  • a határtáblázatot és a hézagmentes kategóriákat (8.5);
  • a rendezést (7.2).

A közelítő egyezés

H: Alsó határI: JegyJ: Megnevezés
01elégtelen
252elégséges
403közepes
554jó
705jeles
  • Jegy: =FKERES(B2;$H$2:$J$6;2;IGAZ)
  • Megnevezés: =FKERES(B2;$H$2:$J$6;3;IGAZ)
  • A negyedik argumentum IGAZ (vagy 1, 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).

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ályAlsó 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 hosszaa kategóriákkal nőmindig rövid
Határok módosításaa képletben vagy hivatkozott cellákbancsak 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égzárójelek, sorrendrendezetlen 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

  1. 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).
  2. Számold ki új oszlopokban a jegyet és a megnevezést közelítő FKERES-sel.
  3. 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.)
  4. 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

  1. 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).
  2. 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.
  3. 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.