Excel táblázatkezelés kezdőknek › 4.3 lecke

Excel tanfolyam · 4.3 lecke

Számítás cellák adataival

Egy jó táblázat olyan, mint egy gép: fent vannak a kapcsolói (a bemeneti adatok), és ha valamelyiket átállítod, lent azonnal megjelenik az új eredmény. Ebben a leckében megtanulod úgy felépíteni a számolásaidat, hogy minden változás magától végigfusson a táblázaton, és sosem kelljen képleteket egyenként javítgatni.

  • ⏱ kb. 45 perc
  • 🎯 kezdőknek
  • 🎒 8. osztálytól

A lecke végére

  • szétválasztod a bemeneti és a számoló cellákat;
  • a képletekbe állandók helyett hivatkozásokat írsz;
  • felismered a „varázsszámokat”, és kiküszöbölöd őket;
  • hivatkozási láncot építesz;
  • „mi lenne, ha” kérdésekre válaszolsz a bemenetek változtatásával.

Amit már tudsz

  • a cellahivatkozást a képletben (2.1);
  • a képlet részeit (4.1) és a műveleti sorrendet (4.2);
  • a cellastílusokat (3.1).

Bemenet és számolás

📖 Fogalomtár

Bemeneti cella
Olyan cella, amelybe te írsz adatot (létszám, ár, százalék). Ezt lehet változtatni.
Számoló cella
Képletet tartalmaz, amely más cellákból számol. Ehhez nem nyúlunk kézzel.
Paraméter
Olyan bemenet, amely sok számításban szerepel (például az áfa vagy a létszám). Egyetlen cellában legyen, felirattal.

✅ Egy jó számolótábla szabályai

  1. A bemenetek egy jól látható helyen, felirattal állnak (például a lap tetején vagy oldalt).
  2. A bemeneti cellák más színt kapnak, például a Kezdőlap → Cellastílusok → Bevitel stílust (3.1).
  3. A képletekben csak hivatkozások vannak, beírt számok nem.
  4. Minden paraméter egyszer szerepel a táblázatban.

A varázsszámok

„Varázsszámnak” hívjuk az olyan állandót, amelyet valaki a képletbe írt, és senki sem tudja, mit jelent: =B2*1,27, =D5/26. Ma még tudod, hogy az 1,27 az áfa és a 26 a létszám, de egy hónap múlva vagy egy társad számára ez rejtély, és ha változik, minden képletet meg kell keresni.

🔮 Jósold meg!

Egy árlistában tíz képlet így számol: =B2*1,27, =B3*1,27… A tizenegyedik sorban valaki ezt írta: =B12*(1+F1), ahol az F1-ben 27% áll. Az áfa 18%-ra változik. Melyik képletek számolnak helyesen, ha csak az F1-et javítod?

Megoldás

Csak a tizenegyedik! A többi tíz képletbe az 1,27 „be van égetve”, azokat egyenként kellene átírni (és könnyű kihagyni egyet). Ha minden képlet az F1-re hivatkozna, egyetlen javítás elég lenne. (Hogy az F1-re mutató képletet hogyan lehet lemásolni úgy, hogy az F1 ne csússzon el, az a 4.5 lecke titka: a dollárjel.)

Hivatkozási lánc

Egy képlet hivatkozhat egy másik képletes cellára is, az pedig egy harmadikra. Így láncok épülnek: a bemenet → részeredmény → végeredmény. Ha a lánc elején változik valami, az Excel az egész láncot újraszámolja.

CellaTartalomSzerep
B226bemenet: létszám
B3130000bemenet: busz összesen
B42500bemenet: belépő fejenként
B6=B3/B2részeredmény: busz fejenként (5000)
B7=B6+B4végeredmény: fejenkénti költség (7500)
B8=B7*B2ellenőrzés: összes költség (195 000)

💡 „Mi lenne, ha…?”

Jól felépített táblázatban kísérletezhetsz: mi lenne, ha 30-an mennénk? Ha drágább a busz? Csak a sárga cellákat írod át, és rögtön látod az eredményt. Ezt hívják modellezésnek.

Kihívás: kirándulási kalkulátor

🚌 Mennyibe kerül fejenként?

  1. Építsd fel a fenti kalkulátort egy új munkalapon: a bemenetek felirattal (A oszlop) és értékkel (B oszlop), sárga Bevitel stílussal.
  2. Bővítsd még két bemenettel: ebéd fejenként és kísérő tanárok száma (a tanárok buszjegyét a diákok fizetik, a belépőjüket és ebédjüket nem).
  3. Írd át a képleteket úgy, hogy a busz ára a diákok között oszoljon el, a többi költség fejenként adódjon hozzá.
  4. Formázd az eredményeket forintra, 0 tizedesjegyre (3.6).
  5. Válaszolj „mi lenne, ha” kérdésekre: mennyi a fejenkénti költség 20, 26 és 32 diák esetén? Írd fel az eredményeket.
  6. Ellenőrzés: a B8 (összes költség) egyezik a bemenetekből fejben számolt összeggel?

Hibavadászat

🐞 1. Peti beégetett létszáma

Peti a kirándulási táblázat öt képletébe közvetlenül beírta a létszámot: =B3/26, =B5*26… Jött egy új diák, Peti négy képletet kijavított, egyet elfelejtett. A végösszeg most nem stimmel.

Mi a megoldás?

A létszám egy varázsszám lett (2. rész). Peti vegyen fel egy Létszám bemeneti cellát (például B2), és mind az öt képletben erre hivatkozzon. Legközelebb csak a B2-t kell átírnia, és minden képlet helyes marad.

🐞 2. Lili hibaüzenete

Lili képlete: =B3/B12. Az eredmény #ZÉRÓOSZTÓ! lett, pedig a létszám a B2-ben szépen ott van.

Mit tegyen?

A képlet egy üres cellára (B12) hivatkozik, amely 0-nak számít, nullával pedig nem lehet osztani (2.2). Lili nyomja meg az F2-t: a színes keretek (4.1) azonnal megmutatják, hogy a hivatkozás rossz helyre mutat. Javítsa B2-re, legjobb kattintással, nem gépeléssel.

Összefoglalás

  • Bemeneti cellák: felirattal, egy helyen, kiemelő stílussal.
  • Számoló cellák: csak hivatkozások, „varázsszám” nélkül.
  • Egy paraméter egyszer szerepeljen a táblázatban.
  • Hivatkozási lánc: bemenet → részeredmény → végeredmény; minden végigfut.
  • „Mi lenne, ha”: a bemenetek átírásával modellezel.

Ellenőrizd magad!

Mi a baj a =B2*1,27 képlettel?

Az 1,27 varázsszám: ha az áfa változik, minden ilyen képletet egyenként kell javítani.

Mit jelent a #ZÉRÓOSZTÓ! hiba?

A képlet nullával (vagy üres cellával) próbál osztani.

Miért jó, ha a bemeneti cellák más színűek?

Mert azonnal látszik, mit lehet átírni, és mit nem szabad (a képleteket).

👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 7–8. évfolyam: Táblázatkezelés (képletek cellahivatkozásokkal, táblázat tervezése, egyszerű modellezés); kapcsolódás a matematikához (szöveges feladatok, arányos osztás) és a pénzügyi tudatossághoz.

Módszertan

A lecke a „hogyan” kérdésről a „hogyan érdemes” kérdésre tereli a figyelmet: a táblázattervezés alapelve a bemenetek és a számítások szétválasztása, amely a programozásban a konstansok és változók használatának felel meg. A jóslás bemutatja a beégetett állandók hátrányát, és előkészíti az abszolút hivatkozást (4.5). A kirándulási kalkulátor valódi modellezési feladat „mi lenne, ha” kérdésekkel.

Időterv

Bemenet és számolás: 8 perc. Varázsszámok és jóslás: 8 perc. Hivatkozási lánc: 7 perc. Kihívás: 17 perc. Hibavadászat: 5 perc.

Következő lecke: 4.4 Relatív hivatkozás és képletmásolás

Egy képlet, sok sor: hogyan „gondolkodik” a relatív hivatkozás, és mikor működik tökéletesen.