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
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ék | B: Kód | C: Kategória | D: Ár (Ft) |
|---|---|---|---|
| Kakaós csiga | K01 | pékáru | 290 |
| Pogácsa | K02 | pékáru | 120 |
| Csokoládé | K03 | édesség | 350 |
| Ásványvíz | K04 | ital | 200 |
| Narancslé | K05 | ital | 300 |
| Gumicukor | K06 | édesség | 250 |
Kedvezmény (Árlista lap, F1-től)
| F: Részösszeg legalább | G: Kedvezmény |
|---|---|
| 0 | 0% |
| 2000 | 5% |
| 5000 | 10% |
Rendelés lap (A1-től)
| A: Kód | B: Darab |
|---|---|
| K03 | 6 |
| K01 | 10 |
| K04 | 8 |
| K07 | 2 |
| K02 | 15 |
| K05 | 4 |
Feldolgozás
- 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). - 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). - Kategória (D oszlop): bármelyik módszerrel.
- Tétel összege (F oszlop):
=HA(SZÁM(E2);B2*E2;""), hogy az ismeretlen kódnál ne legyen hiba (8.6). - 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ő.
- 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.
- 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ód | Darab | Termék | Kategória | Egységár | Tétel |
|---|---|---|---|---|---|
| K03 | 6 | Csokoládé | édesség | 350 | 2100 |
| K01 | 10 | Kakaós csiga | pékáru | 290 | 2900 |
| K04 | 8 | Ásványvíz | ital | 200 | 1600 |
| K07 | 2 | ismeretlen kód | ismeretlen kód | ismeretlen kód | (üres) |
| K02 | 15 | Pogácsa | pékáru | 120 | 1800 |
| K05 | 4 | Narancslé | ital | 300 | 1200 |
- 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és | Az á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és | Rá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?
| Hiba | Helyesen | Lecke |
|---|---|---|
| rögzítetlen tábla | Árlista!$B$2:$D$50 | 10.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 XKERES | 10.7 |
| HAHIBA a HAHIÁNYZIK helyett | HAHIÁNYZIK | 10.8 |
| csökkenő sorrendű kedvezménytábla | növekvő sorrend: 0, 2000, 5000 | 10.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.
