Excel táblázatkezelés kezdőknek › 14.1 projekt
Excel tanfolyam · 14.1 projekt
Kirándulás költségtervezése
Tavasszal kétnapos osztálykirándulásra mentek: 28 diák és 2 kísérő tanár. Három ajánlat van az asztalon: Pécs, Eger és Szeged. Az osztályfőnök három kérdésre vár választ: melyik a legolcsóbb, mennyit fizessen egy diák, és belefér-e a szülőkkel megbeszélt 32 000 Ft-os felső határba? A 13.8-ban egy lezárt esemény költségeit számoltad el. Most előre tervezel: modellt építesz, három változatot hasonlítasz össze, és adatokkal alátámasztott javaslatot írsz.
- ⏱ kb. 45 perc + bővítés
- 🧩 önálló projekt
- 🧠 Kompetenciák: modellezés, összehasonlítás, döntés adatok alapján
- 🎒 11. osztálytól · Táblázatkezelés
A projekt végére
- paraméterlapot építesz elnevezett bemeneti cellákkal;
- egyetlen, két irányba másolható képlettel számolsz különböző elszámolású tételeket;
- tartalékkal, támogatással és felfelé kerekítéssel kiszámolod a befizetendő összeget;
- három változatot hasonlítasz össze, és diagrammal mutatod meg a különbséget;
- adatokkal alátámasztott javaslatot írsz.
Ehhez használod
🧭 Így dolgozz a 14. fejezetben
A 14. fejezet projektjeiben kevesebb a kész képlet, és több a saját döntés. Minden lépésnél előbb próbáld meg önállóan. Ha elakadsz, nyisd le a 💡 Tipp részt. A kész képleteket és eredményeket csak az ellenőrzésnél nézd meg.
🔐 Kitalált ajánlatok
Az árak kitaláltak, de valószerűek. Valódi tervezésnél kérjétek el írásban az ajánlatokat, és nézzétek meg, mit tartalmaz az ár (például reggelit, idegenforgalmi adót), és hogy bruttó ár-e.
Paraméterek: a modell kezelőpultja
Új munkafüzet kirandulas néven, az első munkalap neve legyen Paraméterek (2.6). Minden szám, ami a tervezés során változhat, ide kerül, és csak ide.
| Cella | A: Paraméter | B: Érték | A B cella neve |
|---|---|---|---|
| 2. sor | Diákok száma | 28 | diak |
| 3. sor | Kísérő tanárok | 2 | kiserok |
| 4. sor | Létszám összesen | képlet: =diak+kiserok | letszam |
| 5. sor | Éjszakák száma | 1 | ejszaka |
| 6. sor | Tartalék | 10% | tartalek |
| 7. sor | Osztálypénz-támogatás | 60 000 Ft | tamogatas |
| 8. sor | Felső határ egy diákra | 32 000 Ft | hatar |
- Az A1:B1 fejléc:
Paraméter,Érték. Gépeld be a táblázat sorait. - Nevezd el a B2:B8 cellákat a Névmezővel a táblázat utolsó oszlopa szerint (12.1). Ellenőrizd a Névkezelőben (Ctrl + F3): hét név, mindegyik munkafüzet hatókörű.
- A B4 cella maga is képlet: a létszámot az Excel számolja, nem te.
- Formázás: B6 százalék, B7:B8 pénznem, ezres tagolással (3.6). A bemeneti cellák kapjanak Bevitel stílust (4.3), a B4 ne, mert az számított érték.
💡 Miért külön lap, és miért nevek?
Ha két diák lemond, vagy kétéjszakás lesz az út, egyetlen cellát írsz át, és minden számítás követi. A =C3*letszam*ejszaka képletből első ránézésre kiderül, mit számol. Ha még nem tanultad a neveket, ugyanez abszolút hivatkozással: =C3*Paraméterek!$B$4*Paraméterek!$B$5 (4.5, 4.7).
Ajánlatok
Új munkalap: Kalkuláció. Gépeld be a három ajánlat egységárait az A1 cellától. A B oszlop azt mondja meg, hogyan számolódik a tétel: fix = egy összeg az egész csoportra; fő = fejenként egyszer; éj = fejenként és éjszakánként.
| A: Tétel | B: Elszámolás | C: Pécs | D: Eger | E: Szeged |
|---|---|---|---|---|
| Különbusz (oda-vissza) | fix | 280 000 | 240 000 | 300 000 |
| Szállás | éj | 6 500 | 7 200 | 5 900 |
| Vacsora és reggeli | éj | 3 800 | 3 500 | 4 000 |
| Ebéd (2 alkalom) | fő | 4 400 | 4 000 | 4 600 |
| Belépők, programok | fő | 3 900 | 5 200 | 4 500 |
| Idegenvezetés | fix | 30 000 | 25 000 | 0 |
🔮 Jósold meg!
Csak az egységárakra nézve: melyik úticél lesz összesen a legolcsóbb? Írd fel a tippedet egy cellába, a számítás végén visszatérünk rá. (Egernek a legolcsóbb a busza, de a legdrágább a szállása és a belépője…)
Kalkuláció
- Tételek költsége. A G1:I1 cellákba:
Pécs,Eger,Szeged. A G2 cellába egyetlen képletet írj, amely a B oszlop alapján dönt:fixesetén az egységár,főesetén egységár × létszám,éjesetén egységár × létszám × éjszakák. Ezt a képletet másold a teljes G2:I7 tartományra. - Összesítő sorok a 9. sortól, az A oszlopban felirattal: 9. Költség összesen; 10. Tartalékkal együtt; 11. Támogatás után; 12. Egy diákra (pontos); 13. Befizetés egy diákra; 14. Belefér?; 15. Fix költségek aránya.
- Ki fizet kiért? A kísérő tanárok költségét is a diákok fizetik. Ezért a tételeknél a
letszamszerepel, a 12. sorban viszont adiak-kal osztasz. - Befizetés: 100 forintra felfelé kerekítve (lásd az új eszközt lent). Belefér?: „belefér” vagy „túllépés” a
hataralapján. A felső határ még belefér! - Fix költségek aránya: a
fixtételek összege osztva az összes költséggel, a B oszlop alapján, százalékformátumban. - Legolcsóbb úticél: a K1 cellába felirat, a K2 cellába egy képlet, amely kiírja annak a városnak a nevét, ahol a befizetés a legkisebb.
🆕 Új eszköz: KEREKÍTÉS.FEL (ROUNDUP)
Ugyanúgy működik, mint a KEREKÍTÉS (5.5), de mindig felfelé kerekít: =KEREKÍTÉS.FEL(szám;számjegyek). Párja a KEREKÍTÉS.LE (ROUNDDOWN), amely mindig lefelé kerekít.
A számjegyek száma negatív is lehet: ilyenkor a tizedesvesszőtől balra kerekít. A -1 tízesekre, a -2 százasokra, a -3 ezresekre kerekít.
=KEREKÍTÉS(31721,43;-2)→ 31 700=KEREKÍTÉS.FEL(31721,43;-2)→ 31 800
💡 Tipp az 1. lépéshez
Két HA-t kell egymásba ágyaznod (8.4): ha fix → …, különben ha fő → …, különben → …. A B oszlopra mutató hivatkozás oszlopát rögzítsd ($B2), a sorát ne (4.6). Így jobbra másolva is a B oszlopot nézi, lefelé másolva pedig a saját sorát. Az egységárra mutató hivatkozás maradjon teljesen relatív: jobbra másolva C → D → E lesz belőle.
💡 Tipp az 5–6. lépéshez
Az 5. lépéshez: a SZUMHA összegtartománya a G2:G7 legyen, a feltételtartomány a $B$2:$B$7 (9.2). A 6. lépéshez: a MIN megkeresi a legkisebb befizetést, a HOL.VAN megmondja, hányadik a 13. sorban, az INDEX pedig kiveszi az ugyanannyiadik városnevet az 1. sorból (10.7).
🔮 Jósold meg!
Szegednél az egy diákra jutó pontos összeg 32 035,71 Ft. Mit adna erre a sima KEREKÍTÉS(…;-2), és mi lenne vele a gond, ha ennyit szednétek be?
Megoldás
A KEREKÍTÉS 32 000 Ft-ot adna, és a „Belefér?” sor azt mutatná: belefér. Csakhogy 28 × 32 000 = 896 000 Ft, a kirándulás pedig 897 000 Ft-ba kerül: 1 000 Ft hiányozna. Ha pénzt szedtek be, mindig felfelé kerekítetek, a fölösleg pedig tartalék marad. A KEREKÍTÉS.FEL itt 32 100 Ft-ot ad, ami már túllépés.
Döntés
- Diagram. Készíts halmozott oszlopdiagramot a költségszerkezetről. Jelöld ki az A1:A7 tartományt, majd Ctrl-lal a G1:I7-et (6.6). A vízszintes tengelyen az úticélok legyenek, az oszlopok szeletei a tételek. Ha fordítva jelenik meg, használd a Sor/oszlop váltása gombot. Adj neki címet és jelmagyarázatot (6.7).
- Indokold a választást: miért halmozott oszlop, és miért nem három kördiagram? (Tipp: egyszerre akarod összehasonlítani az egészeket és a részeiket, 6.12.)
- Kiemelés: a „túllépés” cellák legyenek pirosak (8.7).
- Javaslat egy szövegdobozban a diagram mellett (6.11), 3–4 mondatban: melyik úticélt javaslod, mennyi a befizetés, miért ezt, és mi a kockázata.
💡 Mi lenne, ha…? Két diák lemond
Írd át a diak cellát 26-ra. Előbb tippelj: csökken vagy nő az egy diákra jutó befizetés? Melyik úticél fér bele így a határba?
Mi történt, és miért?
Mindhárom úticél túllépés lett: Eger 32 500 Ft, Pécs és Szeged 32 900 Ft. A busz és az idegenvezetés fix költség: ugyanannyiba kerül, de kevesebben osztoznak rajta. Ugyanígy a kísérők költségén is kevesebb diák osztozik. Ezért drágább fejenként, ha kevesebben mennek. Ez fontos érv a javaslatodhoz: Egernél a legkisebb a fix költségek aránya, ezért a lemondásokra is ez a legkevésbé érzékeny. Hogy mennyi támogatás kellene ahhoz, hogy 26 fővel is beleférjetek, azt a 14.11-ben a Célérték kereséssel számolod ki egy lépésben.
🚀 Bővítés: ha az iskola fizeti a kísérőket
Állítsd vissza a 28 diákot, a kísérők számát pedig írd át 0-ra. Mennyi lesz így a befizetés? Miért elég ehhez egyetlen paramétert átírni?
Ellenőrzés és értékelés
Megoldások az ellenőrzéshez
A G2 képlete: =HA($B2="fix";C2;HA($B2="fő";C2*letszam;C2*letszam*ejszaka)), a G2:I7 tartományra másolva.
| Sor | Képlet (G oszlop) | Pécs | Eger | Szeged |
|---|---|---|---|---|
| Különbusz | fix | 280 000 | 240 000 | 300 000 |
| Szállás | éj | 195 000 | 216 000 | 177 000 |
| Vacsora és reggeli | éj | 114 000 | 105 000 | 120 000 |
| Ebéd | fő | 132 000 | 120 000 | 138 000 |
| Belépők, programok | fő | 117 000 | 156 000 | 135 000 |
| Idegenvezetés | fix | 30 000 | 25 000 | 0 |
| 9. Összesen | =SZUM(G2:G7) | 868 000 | 862 000 | 870 000 |
| 10. Tartalékkal | =G9*(1+tartalek) | 954 800 | 948 200 | 957 000 |
| 11. Támogatás után | =G10-tamogatas | 894 800 | 888 200 | 897 000 |
| 12. Egy diákra | =G11/diak | 31 957,14 | 31 721,43 | 32 035,71 |
| 13. Befizetés | =KEREKÍTÉS.FEL(G12;-2) | 32 000 | 31 800 | 32 100 |
| 14. Belefér? | =HA(G13<=hatar;"belefér";"túllépés") | belefér | belefér | túllépés |
| 15. Fix arány | =SZUMHA($B$2:$B$7;"fix";G2:G7)/G9 | 35,7% | 30,7% | 34,5% |
- Legolcsóbb úticél (K2):
=INDEX(G1:I1;HOL.VAN(MIN(G13:I13);G13:I13;0))→ Eger. Bevált a jóslásod? - Pécs pontosan a határon van (32 000 Ft): a
<=miatt belefér, de semmi mozgástere nincs. - Bővítés (0 kísérő): Pécs 30 500 Ft, Eger 30 200 Ft, Szeged 30 600 Ft, mind belefér.
- Egy jó javaslat például: „Egert javaslom: 31 800 Ft a befizetés, ez a legolcsóbb, és 200 Ft-tal a határ alatt van. A fix költségek aránya itt a legkisebb (30,7%), ezért egy-két lemondás itt emeli a legkevésbé az árat. Kockázat: két lemondásnál már ez is 32 500 Ft lenne, ezért érdemes előre rögzíteni a lemondás feltételeit.”
✅ Önellenőrző lista
- Minden változó szám a Paraméterek lapon van, a Kalkuláció lapon csak egységárak és képletek vannak.
- A G2:I7 tartományban egyetlen, végigmásolt képlet van (Képletek lap → Képletek megjelenítése, 4.9).
- A tartalékot szorzással számoltam, nem a 10% hozzáadásával.
- A befizetés KEREKÍTÉS.FEL-lel készült, és a felső határ még „belefér”.
- A fix arány SZUMHA-val, a legolcsóbb úticél INDEX–HOL.VAN-nal számolódik.
- A diák- és a kísérőszám átírása után minden frissül, a diagram is.
- A diagramon az úticélok a tengelyen, a tételek a szeletek; van címe és jelmagyarázata.
- A javaslat számokkal érvel, és kockázatot is megnevez.
| Szempont | ⭐ Elindultam | ⭐⭐ Sikerült | ⭐⭐⭐ Profi vagyok |
|---|---|---|---|
| Modell | Kiszámoltam az összegeket. | Paraméterlap, nevek, egy másolt HA-s képlet. | Bármely paraméter átírására helyesen frissül minden. |
| Számítások | Megvan az egy főre jutó összeg. | Tartalék, támogatás, felfelé kerekítés, belefér? | Fix arány és legolcsóbb úticél képlettel. |
| Döntés | Van diagram. | Indokolt diagramtípus, javaslat. | A javaslat a „mi lenne, ha” eredményére is épít. |
Hibavadászat: Lili kalkulációja
🐞 Lili beszámolója
„A szállást így számoltam: =7200*30. A busz és az idegenvezetés képletét külön írtam meg, a többi sorba a =C3*letszam képletet másoltam, az éjszakát kihagytam, mert most úgyis csak egy van. A jobbra másolt képleteimben B3 szerepel, dollárjel nélkül. A tartalékot így adtam hozzá: =G9+10%. A befizetést a =KEREKÍTÉS(G12;-2) adja. A Belefér? sorban: =HA(G13<hatar;"belefér";"túllépés"). Pécsnél túllépés jött ki, pedig pont 32 000 Ft.”
Hány hibát találtál? Mit tanácsolnál Lilinek?
| Hiba | Helyesen | Lecke |
|---|---|---|
| „varázsszámok” a képletben (7200, 30) | hivatkozás az egységárra és a letszam névre | 4.3 |
| az éjszaka kimaradt a képletből | a modell minden paramétert használjon, különben kétéjszakás útnál hibás lesz | 4.3 |
tételenként külön képlet, B3 dollárjel nélkül | egyetlen HA-s képlet $B2-vel, végigmásolva | 8.4, 4.6 |
=G9+10% csak 0,1-et ad hozzá | =G9*(1+tartalek) | 4.8 |
| sima kerekítés pénz beszedésénél | KEREKÍTÉS.FEL(G12;-2) | 14.1 |
< a határnál | <=, mert a határ még belefér | 8.5 |
Hat hibát találhattál. A legalattomosabb a második: most még jó eredményt ad, és csak akkor derül ki, amikor megváltozik egy paraméter.
Mit gyakoroltál?
- Modellt építettél: a bemenetek egy helyen, elnevezve, a számítás képletekben.
- Egyetlen, vegyes hivatkozású HA-s képlettel háromféle elszámolást kezeltél.
- Tartalékot, támogatást és felfelé kerekítést alkalmaztál pénzügyi helyzetben.
- Különbséget tettél fix és változó költség között, és ezt érvként használtad.
- Adatokból, diagrammal alátámasztott javaslatot fogalmaztál meg.
Ellenőrizd magad!
Miért KEREKÍTÉS.FEL-t használunk a befizetésnél?
Mert lefelé kerekítve kevesebb pénz gyűlne össze, mint amennyibe a kirándulás kerül. Felfelé kerekítve a különbözet tartalék marad.
Miért $B2 és nem $B$2 a HA-s képletben?
Jobbra másolva a B oszlopnál kell maradnia (ezért $B), lefelé másolva viszont minden sornak a saját elszámolási módját kell néznie (ezért relatív a sor).
Miért nő az egy diákra jutó összeg, ha kevesebben mennek?
A fix költségeken (busz, idegenvezetés) és a kísérők költségén kevesebb diák osztozik.
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 9–12. évfolyam: Táblázatkezelés (problémamegoldás táblázatkezelővel, modellezés, függvények, diagramok, adatok alapján hozott döntés). Kapcsolódás a pénzügyi és gazdasági neveléshez (költségvetés, tartalék, fix és változó költség), a matematikához (százalékszámítás, kerekítés) és az osztályfőnöki órához (kirándulás tervezése).
Módszertan
A 14. fejezet nyitó projektje tudatosan csökkenti a támogatást (scaffolding fading): a képletek nem a lépéseknél, hanem lenyitható tippekben és a megoldásban szerepelnek. A két jóslás (legolcsóbb úticél, kerekítés) a „jósolj – figyeld meg – magyarázd meg” logikát követi. A „mi lenne, ha” kérdés a modellszemléletet és a fix költség fogalmát építi, és előkészíti a 14.11 Célérték keresését. A javaslatírás az adatalapú érvelést gyakoroltatja. A KEREKÍTÉS.FEL új eszköz, de az 5.5-ben tanult KEREKÍTÉS közvetlen kiterjesztése. Differenciálás: nevek helyett abszolút hivatkozással is megoldható; a bővítés a gyorsabbaknak szól. Lili beszámolója hat tipikus modellezési hibát mutat be.
Időterv (45 perc)
Paraméterek: 6 perc. Ajánlatok és jóslás: 5 perc. Kalkuláció: 16 perc. Döntés: diagram és javaslat, 10 perc. Ellenőrzés és hibavadászat: 8 perc. A bővítés és a javaslat kidolgozása házi feladatnak is adható.
Következő lecke: 14.2 Energiafogyasztás és megtakarítás
Háztartási gépek fogyasztása, éves költség és megtérülés: mikor éri meg egy takarékosabb hűtő?
