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.

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

  • a bemeneti cellákat és a paraméterlapot (4.3, 4.7);
  • a vegyes hivatkozást (4.6) és az egymásba ágyazott HA-t (8.4);
  • a SZUMHA-t (9.2), az INDEX-et és a HOL.VAN-t (10.7);
  • az elnevezett tartományokat (12.1, 12.2) és a halmozott oszlopdiagramot (6.2).

🧭 Í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.

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.

CellaA: ParaméterB: ÉrtékA B cella neve
2. sorDiákok száma28diak
3. sorKísérő tanárok2kiserok
4. sorLétszám összesenképlet: =diak+kiserokletszam
5. sorÉjszakák száma1ejszaka
6. sorTartalék10%tartalek
7. sorOsztálypénz-támogatás60 000 Fttamogatas
8. sorFelső határ egy diákra32 000 Fthatar
  1. Az A1:B1 fejléc: Paraméter, Érték. Gépeld be a táblázat sorait.
  2. 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ű.
  3. A B4 cella maga is képlet: a létszámot az Excel számolja, nem te.
  4. 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ételB: ElszámolásC: PécsD: EgerE: Szeged
Különbusz (oda-vissza)fix280 000240 000300 000
Szálláséj6 5007 2005 900
Vacsora és reggeliéj3 8003 5004 000
Ebéd (2 alkalom)fő4 4004 0004 600
Belépők, programokfő3 9005 2004 500
Idegenvezetésfix30 00025 0000

🔮 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ó

  1. 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: fix esetén az egységár, fő esetén egységár × létszám, éj esetén egységár × létszám × éjszakák. Ezt a képletet másold a teljes G2:I7 tartományra.
  2. Ö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.
  3. 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 letszam szerepel, a 12. sorban viszont a diak-kal osztasz.
  4. Befizetés: 100 forintra felfelé kerekítve (lásd az új eszközt lent). Belefér?: „belefér” vagy „túllépés” a hatar alapján. A felső határ még belefér!
  5. Fix költségek aránya: a fix tételek összege osztva az összes költséggel, a B oszlop alapján, százalékformátumban.
  6. 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

  1. 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).
  2. 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.)
  3. Kiemelés: a „túllépés” cellák legyenek pirosak (8.7).
  4. 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.

SorKéplet (G oszlop)PécsEgerSzeged
Különbuszfix280 000240 000300 000
Szálláséj195 000216 000177 000
Vacsora és reggeliéj114 000105 000120 000
Ebédfő132 000120 000138 000
Belépők, programokfő117 000156 000135 000
Idegenvezetésfix30 00025 0000
9. Összesen=SZUM(G2:G7)868 000862 000870 000
10. Tartalékkal=G9*(1+tartalek)954 800948 200957 000
11. Támogatás után=G10-tamogatas894 800888 200897 000
12. Egy diákra=G11/diak31 957,1431 721,4332 035,71
13. Befizetés=KEREKÍTÉS.FEL(G12;-2)32 00031 80032 100
14. Belefér?=HA(G13<=hatar;"belefér";"túllépés")beleférbelefértúllépés
15. Fix arány=SZUMHA($B$2:$B$7;"fix";G2:G7)/G935,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
ModellKiszá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ásokMegvan 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ésVan 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?
HibaHelyesenLecke
„varázsszámok” a képletben (7200, 30)hivatkozás az egységárra és a letszam névre4.3
az éjszaka kimaradt a képletbőla modell minden paramétert használjon, különben kétéjszakás útnál hibás lesz4.3
tételenként külön képlet, B3 dollárjel nélkülegyetlen HA-s képlet $B2-vel, végigmásolva8.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élKEREKÍTÉS.FEL(G12;-2)14.1
< a határnál<=, mert a határ még belefér8.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ő?