Excel táblázatkezelés kezdőknek › 10.9 mini projekt

Excel tanfolyam · 10.9 mini projekt

Árlista és rendelés összekapcsolása

A 9. b osztály közös büférendelést ad le az osztálykirándulásra. A rendelési lapon csak a termékkódok és a darabszámok szerepelnek, minden más (a termék neve, a kategóriája, az ára) a büfé árlistájában van. A büfé mennyiségi kedvezményt is ad. A feladatod, hogy keresőfüggvényekkel teljes, ellenőrzött rendelési összesítőt készíts, amely akkor is helyesen működik, ha a rendelésben ismeretlen kód szerepel.

  • ⏱ kb. 45 perc
  • 🧩 mini projekt
  • 🎒 10. osztálytól

A projekt végére

  • két munkalap adatait kapcsolod össze kód alapján;
  • jobbra és balra is keresel;
  • pontos és közelítő keresést kombinálsz;
  • célzottan kezeled az ismeretlen kódot;
  • ellenőrzött végösszeget számolsz kedvezménnyel.

Ehhez használod a 10. fejezet tudását

  • 10.1 fogalmak; 10.2 FKERES; 10.3 közelítő keresés;
  • 10.5 HOL.VAN; 10.6 INDEX; 10.7 INDEX + HOL.VAN, XKERES;
  • 10.8 hiányzó találatok; valamint SZUMHA (9.2).

Az adatok

Készíts egy munkafüzetet két lappal (kitalált adatok). Figyeld meg: az árlistában a Termék a Kód előtt (balra) áll!

Árlista lap (A1-től)

A: TermékB: KódC: KategóriaD: Ár (Ft)
Kakaós csigaK01pékáru290
PogácsaK02pékáru120
CsokoládéK03édesség350
ÁsványvízK04ital200
NarancsléK05ital300
GumicukorK06édesség250

Kedvezmény (Árlista lap, F1-től)

F: Részösszeg legalábbG: Kedvezmény
00%
20005%
500010%

Rendelés lap (A1-től)

A: KódB: Darab
K036
K0110
K048
K072
K0215
K054

Feldolgozás

  1. Egységár (Rendelés, E oszlop) FKERES-sel: a kód a B oszlopban, az ár tőle jobbra, ezért a tábla Árlista!$B$2:$D$50, oszlopszám 3, pontos egyezés. Ismeretlen kódnál „ismeretlen kód” (HAHIÁNYZIK, 10.8).
  2. Termék (C oszlop): a név a kód bal oldalán van, ezért FKERES-sel nem megy. INDEX + HOL.VAN: =HAHIÁNYZIK(INDEX(Árlista!$A$2:$A$50;HOL.VAN(A2;Árlista!$B$2:$B$50;0));"ismeretlen kód"), vagy XKERES-sel (10.7).
  3. Kategória (D oszlop): bármelyik módszerrel.
  4. Tétel összege (F oszlop): =HA(SZÁM(E2);B2*E2;""), hogy az ismeretlen kódnál ne legyen hiba (8.6).
  5. Alatta: Részösszeg (SZUM), Kedvezmény % közelítő kereséssel a kedvezménytáblából (FKERES IGAZ-zal, vagy INDEX + HOL.VAN 1-gyel, 10.3), Kedvezmény Ft, Fizetendő.
  6. Ellenőrzés: az ismeretlen kódok száma (DARABTELI), figyelmeztetés a fizetendő mellett, ha nem 0 (10.8), és feltételes formázás az ismeretlen kódokra.
  7. Elemzés: kategóriánkénti összeg SZUMHA-val (9.2), és INDEX + HOL.VAN-nal a legnagyobb tétel terméke (10.6).

Ellenőrzés

Megoldások az ellenőrzéshez
KódDarabTermékKategóriaEgységárTétel
K036Csokoládéédesség3502100
K0110Kakaós csigapékáru2902900
K048Ásványvízital2001600
K072ismeretlen kódismeretlen kódismeretlen kód(üres)
K0215Pogácsapékáru1201800
K054Narancsléital3001200
  • Részösszeg: 9600 Ft; kedvezmény: 10% (a 9600 eléri az 5000-et) = 960 Ft; fizetendő: 8640 Ft.
  • Ismeretlen kódok száma: 1 → a figyelmeztetés megjelenik.
  • Kategóriák: pékáru 4700 Ft, ital 2800 Ft, édesség 2100 Ft (összesen 9600 Ft: egyezik a részösszeggel).
  • Legnagyobb tétel: Kakaós csiga, 2900 Ft.
  • Utolsó lépés: a büfé közli, hogy a K07 a „Kifli” (pékáru, 80 Ft). Vedd fel az árlista végére: a rendelés magától frissül → részösszeg 9760 Ft, kedvezmény 976 Ft, fizetendő 8784 Ft, ismeretlen kód: 0, a figyelmeztetés eltűnik. (Ha nem frissül, a keresési tartomány túl rövid: 10.8, 5. ok.)

💡 Próbáld ki a határokat is!

Írd át a darabszámokat úgy, hogy a részösszeg pontosan 5000 Ft legyen: 10% kedvezményt kell kapnod („legalább”). 4999 Ft-nál 5%-ot. Ha nem így van, nézd meg a kedvezménytábla alsó határait és a közelítő keresés beállítását (10.3).

Önellenőrzés és értékelés

✅ Önellenőrző lista

  • Az egységár, a termék és a kategória minden ismert kódnál helyes, a táblák rögzítettek.
  • A terméknevet balra kereséssel (INDEX + HOL.VAN vagy XKERES) kaptam meg.
  • Minden pontos keresésben szerepel a HAMIS vagy a 0 (XKERES-nél ez az alapérték).
  • Az ismeretlen kódot HAHIÁNYZIK-kal (vagy XKERES-szel) kezeltem, nem HAHIBA-val.
  • A tétel összege ismeretlen kódnál üres, nem hiba.
  • A kedvezmény közelítő kereséssel jön, a határokon is helyes.
  • A figyelmeztetés jelzi, ha a végösszeg hiányos.
  • A K07 felvétele után minden magától frissült.
Szempont⭐ Elindultam⭐⭐ Sikerült⭐⭐⭐ Profi vagyok
KeresésAz ár megvan.Ár, név, kategória, kedvezmény.Balra és közelítő keresés is, rögzített, bővíthető tartományokkal.
Hibakezelés#HIÁNYZIK látszik.„ismeretlen kód” üzenet.Célzott kezelés, kiemelés, figyelmeztetés a végösszegnél.
EllenőrzésRánéztem.A megoldásokkal egyezik.Határesetek és a kategóriaösszeg ellenőrzése is.

Hibavadászat: Peti rendelési összesítője

🐞 Peti beszámolója

„Az árat =FKERES(A2;Árlista!B2:D7;3) képlettel kerestem ki, és lemásoltam. A termék nevét =FKERES(A2;Árlista!$A$2:$D$7;1;HAMIS) képlettel próbáltam, de nem működött. Az ismeretlen kódot HAHIBA-val oldottam meg. A kedvezménytáblát így írtam: 5000 → 10%, 2000 → 5%, 0 → 0%. A tétel összegében =B2*E2 áll. A K07 felvétele után semmi nem változott.”

Hány hibát találtál? Mit tanácsolnál Petinek?
HibaHelyesenLecke
rögzítetlen táblaÁrlista!$B$2:$D$5010.2
hiányzó HAMIS (közelítő keresés)…;3;HAMIS)10.2
FKERES balra (a név a kód előtt van)INDEX + HOL.VAN vagy XKERES10.7
HAHIBA a HAHIÁNYZIK helyettHAHIÁNYZIK10.8
csökkenő sorrendű kedvezménytáblanövekvő sorrend: 0, 2000, 500010.3
tétel összege hibát ad ismeretlen kódnál=HA(SZÁM(E2);B2*E2;"")8.6
túl rövid tartomány (B2:D7)bővebb tartomány (például B2:D50)10.8

Hét hibát találhattál. Ha mindet megtaláltad, a keresőfüggvények mestere vagy!

Mit tudsz már? A 10. fejezet összefoglalása

  • Két táblát kapcsolsz össze egy kulcs alapján, pontos és közelítő egyezéssel.
  • FKERES-t és VKERES-t használsz, és ismered a korlátaikat.
  • HOL.VAN-nal helyet, INDEX-szel értéket keresel; együtt bármelyik irányban.
  • XKERES-sel egyszerűbben keresel, ha a verzió engedi.
  • Felderíted és célzottan kezeled a hiányzó találatokat.
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (keresőfüggvények, több munkalap, hibakezelés), Adatbázis-kezelés (táblák kapcsolata kulcs alapján, mint egy lekérdezés). Az NKP-okostankönyvre épülő emelt szóbeli tétel keresőfüggvényes elemének komplex alkalmazása; az érettségi táblázatkezelési feladatainak jellegzetes szerkezete (több lap, segédtábla, kedvezménysáv).

Módszertan

A mini projekt adatszerkezete tudatosan úgy készült, hogy a diáknak mindhárom keresési helyzettel (jobbra, balra, közelítő) találkoznia kelljen. Az ismeretlen kód és a K07 utólagos felvétele a dinamikus, bővíthető megoldás értékét mutatja meg. A megadott megoldások és a határesetek önálló ellenőrzést tesznek lehetővé. Peti beszámolója a fejezet hét tipikus hibáját ismétli.

Időterv (45 perc)

Az adatok: 7 perc. Feldolgozás: 22 perc. Ellenőrzés és határok: 7 perc. Hibavadászat: 6 perc. Önellenőrzés: 3 perc.

Következő fejezet: 11.1 BAL, JOBB és KÖZÉP

Kezdődik a 11. fejezet: szöveg-, dátum- és időfüggvények az importált adatok feldolgozásához.