#HIV!, #ÉRTÉK!, #ZÉRÓOSZTÓ!: a fejezet során már sok hibaüzenettel találkoztál. Ezek nem ellenségek, hanem nyomok: megmondják, mi romlott el. Ebben a leckében összegyűjtjük őket, megismered az Excel „nyomozóeszközeit”, és kapsz egy módszert, amellyel bármelyik hibás képletet kinyomozhatod.
⏱ kb. 45 perc
🎯 kezdőknek
🎒 8. osztálytól
Kép helye (16:9)Munkalap hibaértékekkel (#HIV!, #ÉRTÉK!, #ZÉRÓOSZTÓ!), az Elődök mutatása kék nyilaival, fölötte a Képletek lap Képletvizsgálat csoportja.Javasolt alternatív szöveg: „Hibaértékek és képletvizsgálati eszközök”
A lecke végére
felismered és megérted a gyakori hibaértékeket;
tudod, mi a körkörös hivatkozás;
az Elődök és az Utódok mutatásával feltérképezed a képleteket;
a Képletkiértékelővel lépésenként megnézed a számolást;
törölt sor, oszlop, lap; a munkalapról „lelógó” másolás
2.5, 4.4
#ÉRTÉK! (#VALUE!)
rossz adattípus
szöveggel szoroz, összead („12 db”)
2.1
#ZÉRÓOSZTÓ! (#DIV/0!)
nullával osztás
az osztó üres cella vagy 0
4.3, 4.8
#NÉV? (#NAME?)
ismeretlen név
elírt függvénynév, angol függvénynév, idézőjel nélküli szöveg
1.2, 5. fejezet
#HIÁNYZIK (#N/A)
nem található érték
a keresőfüggvény nem talál (10. fejezet)
10.
#SZÁM! (#NUM!)
érvénytelen szám
túl nagy eredmény, lehetetlen művelet
–
#NULLA! (#NULL!)
nincs közös rész
két tartomány között kettőspont vagy pontosvessző helyett szóköz
–
#####
nem hiba!
a szám vagy dátum nem fér el
3.3
2
Körkörös hivatkozás
🔮 Jósold meg!
Az A5 cellába ezt írod: =A1+A2+A3+A4+A5. Mi történik?
Megoldás
Az Excel figyelmeztet: körkörös hivatkozás. A képlet önmagára hivatkozik (az A5 az A5-öt is összeadná), vagyis a saját eredményétől függ, ezért nem tudja kiszámolni (gyakran 0-t mutat). Az állapotsoron is megjelenik a „Körkörös hivatkozás” felirat a cella címével. Javítás: a képlet ne tartalmazza a saját celláját: =A1+A2+A3+A4.
3
Nyomozóeszközök
A Képletek lap Képletvizsgálat csoportja:
Eszköz
Mire jó?
Elődök mutatása
kék nyilakkal mutatja, mely cellákból számol a kijelölt képlet
Utódok mutatása
mely képletek használják a kijelölt cellát (ha törölnéd, mi romlana el?)
Nyilak eltávolítása
eltünteti a nyilakat
Képletek megjelenítése
eredmények helyett képletek (1.3, 4.4)
Hibaellenőrzés
végigmegy a munkalap hibás vagy gyanús képletein (a zöld háromszögek, 2.8)
Képletkiértékelő
lépésenként mutatja, hogyan számol a képlet: minden kattintásra egy részeredmény
💡 A hiba forrásánál
Ha egy hibás cella egy másik hibás cellára hivatkozik, a hiba „továbbterjed”. Az Elődök mutatása és egy piros nyíl visszavezet a hiba első előfordulásához: azt kell javítani, a többi magától rendbe jön.
4
Nyomozási módszer
✅ Öt lépés
Olvasd el a hibaértéket (1. rész): már ez is irányt mutat.
Nézd meg a képletet az F2-vel: a színes keretek jó helyre mutatnak (4.1)?
Ellenőrizd a hivatkozott cellákat: számok-e (jobbra igazodnak?), nem üresek-e (2.8, 4.3)?
Kövesd vissza az Elődök mutatásával vagy a Képletkiértékelővel.
Mondd ki szavakkal, mit kellene számolnia, és hasonlítsd össze egy fejben vagy számológéppel számolt eredménnyel (4.1).
5
Kihívás: hibás munkafüzet
🕵️ Javítsd ki a társad munkáját!
Párban dolgozzatok. Mindketten készítsetek egy kis, 6–8 soros számolótáblát (például a 4.3 kirándulási kalkulátorát vagy a 4.5 büféjét).
Rejtsetek el benne négy hibát: egy hiányzó dollárjelet, egy szövegként beírt számot, egy elírt függvénynevet és egy körkörös hivatkozást.
Cseréljetek gépet. Az öt lépés és a nyomozóeszközök segítségével keressétek meg és javítsátok ki a hibákat.
Írjátok fel, melyik hibát melyik eszközzel találtátok meg.
6
Hibavadászat
🐞 1. Peti #NÉV? hibája
Peti képlete: =SZMU(B2:B8), és #NÉV? jelent meg.
Mi a megoldás?
Elírta a függvény nevét: SZUM helyett SZMU. Gépelés közben az Excel listát ajánl a függvénynevekről: válassza ki onnan (Tab), így nem írhatja el. A helyes függvénynév beíráskor nagybetűssé alakul; ha kisbetűs marad, gyanús (5. fejezet).
🐞 2. Lili körkörös összege
Lili az összeget a B9-be tette ezzel a képlettel: =SZUM(B2:B9). Az Excel körkörös hivatkozásra figyelmeztetett, és 0-t mutat.
Mit tegyen?
A tartományba a saját cellája (B9) is beletartozik (2. rész). Javítsa: =SZUM(B2:B8). Az állapotsor „Körkörös hivatkozás” felirata megmutatja, melyik cellában van a gond.
Összefoglalás
Hibaértékek: #HIV!, #ÉRTÉK!, #ZÉRÓOSZTÓ!, #NÉV?, #HIÁNYZIK, #SZÁM!, #NULLA!; a ##### nem hiba.
Körkörös hivatkozás: a képlet a saját cellájára hivatkozik.
Nyomozóeszközök: Elődök és Utódok mutatása, Képletkiértékelő, Hibaellenőrzés.
A továbbterjedő hibát a forrásánál javítsd.
Öt lépés: hibaérték, képlet, hivatkozott cellák, visszakövetés, ellenőrző számolás.
Ellenőrizd magad!
Mit jelent a #ZÉRÓOSZTÓ!?
A képlet nullával vagy üres cellával próbál osztani.
Melyik eszköz mutatja meg, mely cellákból számol egy képlet?
Az Elődök mutatása.
Hiba-e a #####?
Nem, csak nem fér el a szám vagy a dátum.
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 7–8. évfolyam: Táblázatkezelés (hibaértékek, képletek ellenőrzése, hibakeresés); kapcsolódás a programozáshoz (hibakeresés, debugging, lépésenkénti végrehajtás).
Módszertan
A lecke a fejezetben eddig szétszórtan előforduló hibaüzeneteket rendszerezi, és a hibát nyomként, nem kudarcként mutatja be. A Képletkiértékelő a programozás lépésenkénti végrehajtásához hasonló. Az öt lépéses módszer átvihető stratégia. A páros hibakereső feladatban a diákok egymás számára alkotnak hibákat, ami a megértés mély szintjét igényli.
„Most minden 20%-kal olcsóbb, és a pénztárnál még további 10%!” Akkor ez 30% kedvezmény? A 3.6-ban megtanultad a százalékformátumot és az alapképleteket. Most a matematikaórán tanult fogalmakkal (alap, érték, láb) rendszerezzük a százalékszámítást, lebuktatunk néhány reklámtrükköt, és a dollárjelet is bevetjük.
⏱ kb. 45 perc
🎯 kezdőknek
🎒 8. osztálytól
Kép helye (16:9)Árfigyelő táblázat hónapokkal, árakkal és százalékos változással, a negatív változások pirosak; mellette egy kétlépcsős kedvezmény számítása.Javasolt alternatív szöveg: „Százalékos változás és kedvezmény az Excelben”
A lecke végére
Excel-képletté alakítod a százalékszámítás három alapfeladatát;
egymás utáni kedvezményeket és változásokat számolsz;
részarányt számolsz az összeshez abszolút hivatkozással;
Az egész, amelynek a százalékát vesszük (például az eredeti ár).
Százalékláb
Hány százalék (például 20%).
Százalékérték
Az alap ennyi százaléka (például a kedvezmény forintban).
Keresett
Matek
Excel (B2: alap, C2: láb, D2: érték)
százalékérték
alap × láb
=B2*C2
százalékláb
érték ÷ alap
=D2/B2 (százalékformátum)
százalékalap
érték ÷ láb
=D2/C2
2
Egymás utáni százalékok
🔮 Jósold meg!
(a) Egy 10 000 Ft-os cipő árából előbb 20%-ot, a pénztárnál még további 10%-ot engednek. Hány százalék összesen a kedvezmény? (b) Egy 1000 Ft-os jegy ára januárban 10%-kal nő, februárban 10%-kal csökken. Mennyi lesz februárban?
Megoldás
(a) 28%, nem 30%! A második kedvezmény már a csökkentett árból (8000 Ft) jár: 8000 × 0,9 = 7200 Ft, ami 10 000-hez képest 72%, vagyis 28% kedvezmény. Excelben: =B2*(1-C2)*(1-D2). (b) 990 Ft, nem 1000! 1000 × 1,1 = 1100, majd 1100 × 0,9 = 990. A csökkenés nagyobb alapból indul. A százalékok tehát nem adódnak össze, hanem szorzódnak: (1 + p)·(1 − q).
💡 Százalékos változás újra
A változás mindig a régi értékhez viszonyít: =(új-régi)/régi (3.6). Ezért a 990-ről vissza 1000-re emelés már nem 10%, hanem kb. 1,01%.
3
Részarány az összeshez
Gyakori kérdés: egy tétel hány százaléka az összesnek? Például mekkora részt tesz ki az élelmiszer a havi kiadásból, vagy hány százalék szavazott egy javaslatra. A képlet: rész ÷ egész, ahol az egész (az összeg) minden sorban ugyanaz a cella, ezért rögzíteni kell:
Sor
B (szavazat)
C (részarány)
2: kirándulás
12
=B2/$B$6 → 40%
3: mozi
9
=B3/$B$6 → 30%
4: múzeum
6
=B4/$B$6 → 20%
5: bowling
3
=B5/$B$6 → 10%
6: összesen
30
100%
✅ Ellenőrzés
A részarányok összege mindig 100% (a kerekítés miatt néha 99% vagy 101% látszik, 3.5).
4
Kihívás: árfigyelő és szavazás
📈 1. Árfigyelő
Válassz egy kitalált terméket (például egy füzetcsomag), és írd fel az árát hat hónapon át (A oszlop: hónap, B oszlop: ár; a hónapokat töltsd ki a kitöltőfogantyúval, 2.4).
A C oszlopban számold a havi változást az előző hónaphoz képest: =(B3-B2)/B2, százalékformátumban, egy tizedesjeggyel.
A D oszlopban a változást az első hónaphoz képest: =(B3-$B$2)/$B$2. Miért kell itt a dollárjel?
Melyik hónapban volt a legnagyobb drágulás? A negatív változásokat formázd pirosra (3.8).
🗳️ 2. Osztályszavazás
Írd fel egy kitalált osztályszavazás eredményét (legalább négy lehetőség).
Számold ki az összes szavazatot (képlettel), majd minden lehetőség részarányát abszolút hivatkozással.
Ellenőrizd, hogy a részarányok összege 100%-e.
5
Hibavadászat
🐞 1. Peti összeadott kedvezményei
Peti a kétszeres kedvezményt így számolta: =B2*(1-(C2+D2)), vagyis 20% + 10% = 30%. Az eredmény 7000 Ft lett, de a pénztárban 7200 Ft-ot kellett fizetni.
Mi a megoldás?
Az egymás utáni kedvezmények szorzódnak (2. rész): =B2*(1-C2)*(1-D2) = 7200 Ft. A 30% csak akkor igaz, ha a két kedvezményt egyszerre, az eredeti árból adják (és ezt a hirdetés ritkán mondja!).
🐞 2. Lili hibaüzenetes részarányai
Lili a részarányt így írta: =B2/B6, és lemásolta. Az első sor jó, a többiben #ZÉRÓOSZTÓ! és furcsa értékek jelentek meg.
Mit tegyen?
A B6 (az összesen) másoláskor B7-re, B8-ra lépett, amelyek üresek, és nullával nem lehet osztani. Az egésznek a helyén kell maradnia: =B2/$B$6 (3. rész, 4.5).
Összefoglalás
Érték = alap × láb; láb = érték ÷ alap; alap = érték ÷ láb.
Egymás utáni százalékok szorzódnak: =ár*(1-p)*(1-q).
Változás: =(új-régi)/régi, mindig a régihez viszonyítva.
Részarány: =rész/$összes$, az összeg cellája rögzítve.
A részarányok összege 100%.
Ellenőrizd magad!
Mennyi egy 5000 Ft-os termék ára 10%, majd 20% kedvezmény után?
5000 × 0,9 × 0,8 = 3600 Ft.
A 30 fős osztályból 12-en szavaztak a kirándulásra. Hány százalék ez?
40%.
Miért kell a $ a =B2/$B$6 képletben?
Mert az összes (B6) minden sorban ugyanaz, másoláskor sem léphet el.
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 7–8. évfolyam: Táblázatkezelés (százalékos képletek, abszolút hivatkozás); szoros kapcsolódás a matematikához (százalékszámítás alapfogalmai, egymás utáni százalékos változás) és a pénzügyi, fogyasztói tudatossághoz (kedvezmények értelmezése).
Módszertan
A lecke a matematikaórai fogalmakat (alap, érték, láb) Excel-képletekre fordítja, így a két tantárgy egymást erősíti. A két jóslás tipikus téveszméket (a százalékok összeadhatók; a +p és −p kioltja egymást) cáfol meg számolással. A részarány számítása az abszolút hivatkozás jellegzetes, gyakori alkalmazása, és előkészíti a diagramokat (6. fejezet).
Időterv
A három alapfeladat: 8 perc. Egymás utáni százalékok és jóslás: 10 perc. Részarány: 7 perc. Kihívás: 16 perc. Hibavadászat: 4 perc.
Következő lecke: 4.9 Képletek ellenőrzése és gyakori hibák
#HIV!, #ÉRTÉK!, #ZÉRÓOSZTÓ!, #NÉV?: mit jelentenek a hibaértékek, és hogyan nyomozd ki őket.
A 2.6-ban már hivatkoztál egy másik munkalap egyetlen cellájára. Most továbblépünk: egy külön „Beállítások” lapra gyűjtöd a közös adatokat, egyetlen képlettel összeadod több munkalap ugyanazon celláját, és megismered, mire figyelj, ha egy egészen másik fájlra hivatkozol.
⏱ kb. 45 perc
🎓 haladóbb
🎒 8. osztálytól
Kép helye (16:9)Munkafüzet Beállítások, Szeptember, Október, November és Összesítő lapfülekkel; az Összesítő lapon a =SZUM(Szeptember:November!B5) képlet.Javasolt alternatív szöveg: „Paraméterlap és 3D-hivatkozás több munkalapon”
A lecke végére
magabiztosan hivatkozol másik munkalapra;
paraméterlapot készítesz abszolút hivatkozásokkal;
3D-hivatkozással több lap azonos celláját összegzed;
tudod, mi történik a hivatkozásokkal átnevezéskor, törléskor, áthelyezéskor;
ismered a külső hivatkozás kockázatait.
Amit már tudsz
a munkalapok kezelését és a lapok közötti hivatkozást (2.6);
Ha a lapnévben szóköz vagy írásjel van, a név aposztrófok közé kerül: ='Őszi szünet'!B5.
Legbiztosabb kattintással: = jel, kattintás a lapfülre, kattintás a cellára, Enter (2.6).
Hivatkozásokat is másolhatsz: a =Szeptember!B5 lefelé másolva =Szeptember!B6 lesz, a relatív szabály itt is érvényes (4.4).
2
Paraméterlap
Ha egy munkafüzetben sok lap használja ugyanazokat a közös adatokat (áfa, árfolyam, létszám, dátum), gyűjtsd őket egyetlen Beállítások lapra (4.3), és mindenhonnan arra hivatkozz:
Beállítások lap
Érték
A1: Áfa
B1: 27%
A2: Kedvezmény
B2: 20%
A3: Euró árfolyam
B3: 400
Egy másik lapon: =B2*(1+Beállítások!$B$1). A dollárjel itt is kell, ha a képletet lemásolod (4.5): a lapnév nem mozdul, de a cellacím a lapon belül igen.
💡 Miért jó?
Ha az áfa változik, egyetlen helyen kell átírni, és a munkafüzet összes lapja frissül. A Beállítások lapot színes füllel és az első helyen tartva mindenki azonnal megtalálja.
3
3D-hivatkozás
📖 Fogalomtár
3D-hivatkozás
Egymás melletti munkalapok azonos cellájára (vagy tartományára) mutató hivatkozás: Szeptember:November!B5 a Szeptember, az Október és a November lap B5 celláját jelenti.
Akkor működik jól, ha a lapok azonos szerkezetűek (a havi összeg mindegyiken a B5-ben van). Használat: =SZUM(Szeptember:November!B5) (a SZUM függvényről az 5. fejezetben tanulsz). Kattintással: =SZUM( → kattints az első lap fülére → Shift + kattintás az utolsó lap fülére → kattints a B5-re → ) és Enter.
🔮 Jósold meg!
Az Összesítő lapon: =SZUM(Szeptember:November!B5). (a) Az Október és a November közé beszúrsz egy új lapot (például „Őszi szünet”), amelynek a B5-jében is van egy szám. Bekerül az összegbe? (b) A December lapot a November mögé húzod. Bekerül?
Megoldás
(a) Igen: a 3D-hivatkozás minden olyan lapot tartalmaz, amely az első és az utolsó lap között áll. (b) Nem: a December a tartomány utolsó lapja (November) mögött van. A 3D-hivatkozásnál a lapok sorrendje számít! Ha a decembert is bele akarod venni, írd át: =SZUM(Szeptember:December!B5).
4
Külső hivatkozás másik munkafüzetre
Egy képlet egy másik fájl cellájára is mutathat. Ha mindkét munkafüzet nyitva van, kattintással is elkészül, és ilyen lesz: =[zsebpenz.xlsx]Szeptember!$B$5.
⚠️ Törékeny kapcsolat
Ha a forrásfájlt átnevezed, áthelyezed vagy törlöd, a hivatkozás megszakad (mint a Word körlevelénél, Word 11.2).
Megnyitáskor az Excel figyelmeztethet, hogy a munkafüzet külső hivatkozásokat tartalmaz; ismeretlen fájlnál ezt ne frissítsd gondolkodás nélkül.
Ha a munkafüzetet elküldöd valakinek, a forrásfájl nélkül a hivatkozott adatok nem frissülnek.
Ezért iskolai feladatoknál és érettségin a külső hivatkozást kerüld, hacsak a feladat kifejezetten nem kéri: másold át az adatokat egy lapra.
5
Kihívás: négyhavi zsebpénzfüzet
📅 Beállítások, havi lapok, összesítő
Nyisd meg a 2.6-ban készült zsebpénz-munkafüzetet (Szeptember, Október, November, Összesítő).
Szúrj be az első helyre egy Beállítások lapot: „Havi keret” és egy kitalált összeg (például 12 000 Ft).
Minden havi lapon a maradékot számold így: =Beállítások!$B$1-B5 (ahol B5 a havi összes kiadás).
Az Összesítő lapon a három hónap kiadását egy 3D-hivatkozással add össze: =SZUM(Szeptember:November!B5).
Másold le a November lapot, nevezd el December-nek, és húzd a November mögé. Mi változik az összesítőben? Javítsd a képletet!
Változtasd meg a havi keretet a Beállítások lapon: minden havi maradék frissült?
6
Hibavadászat
🐞 1. Peti aposztróf nélküli lapneve
Peti beírta: =Őszi szünet!B5. Az Excel hibát jelzett, és nem fogadta el a képletet.
Mi a megoldás?
A szóköz miatt a lapnevet aposztrófok közé kell tenni: ='Őszi szünet'!B5. A legegyszerűbb, ha Peti kattintással készíti a hivatkozást: az Excel maga teszi ki az aposztrófokat (1. rész).
🐞 2. Lili kimaradt decembere
Lili az Összesítő lapon =SZUM(Szeptember:November!B5) képlettel összegzett, aztán a december lapját a lapfülek végére tette. A decemberi kiadások nem jelentek meg az összegben.
Mit tegyen?
A 3D-hivatkozás csak az első és az utolsó megnevezett lap közötti lapokat veszi bele (lásd a jóslást). Lili írja át a képletet: =SZUM(Szeptember:December!B5), vagy tegye a december lapját a november elé. Tipp: sokan két üres „határlapot” (például „Eleje” és „Vége”) használnak, és mindig ezek közé húzzák a havi lapokat.
Összefoglalás
Másik lap: =Lapnév!Cella; szóköznél aposztróf.
Paraméterlap: a közös adatok egy helyen, a hivatkozásban dollárjellel.
3D-hivatkozás: Első:Utolsó!Cella; az első és az utolsó lap közötti összes lapra érvényes.
A lapok sorrendje a 3D-hivatkozásnál számít.
Külső hivatkozás más fájlra: törékeny, iskolai feladatokban kerüld.
Ellenőrizd magad!
Hogyan hivatkozol a Bevétel lap C3 cellájára rögzítve?
=Bevétel!$C$3
Mely lapokat foglalja magába a Január:Március!B2 hivatkozás?
A Január és a Március lapot és mindazokat, amelyek közöttük állnak.
Miért veszélyes a külső hivatkozás?
Mert a forrásfájl átnevezésével, áthelyezésével vagy törlésével megszakad.
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 7–8. évfolyam (haladóbb szint): Táblázatkezelés (munkalapok közötti hivatkozás, 3D-hivatkozás, külső hivatkozás), Adatkezelés (munkafüzet szerkezetének tervezése).
Módszertan
A lecke a 2.6 egyszerű lapközi hivatkozását rendszerszintű tervezéssé bővíti: a paraméterlap a 4.3 bemeneti cella elvét és a 4.5 abszolút hivatkozását kapcsolja össze. A 3D-hivatkozásról szóló jóslás a lapsorrend szerepét fedezteti fel. A külső hivatkozás bemutatása inkább óvatosságra nevel, és a Word körlevél adatforrásának tapasztalatára épít.
Emlékszel a 2.4 szorzótábla-nyomozására? Egy képlettel, jobbra és lefelé kitöltve sehogy sem akart sikerülni. Most végre megoldjuk: a vegyes hivatkozással a hivatkozás egyik fele rögzített, a másik lép. Így egyetlen képlettel elkészül a teljes 10 × 10-es szorzótábla.
⏱ kb. 45 perc
🎓 haladóbb
🎒 8. osztálytól
Kép helye (16:9)Kitöltött 10 × 10-es szorzótábla, a B2 cella vegyes hivatkozásos képlete a szerkesztőlécen, a fejlécsor és a fejlécoszlop kiemelve.Javasolt alternatív szöveg: „Szorzótábla egyetlen vegyes hivatkozásos képlettel”
A lecke végére
megkülönbözteted a $A2 és az A$2 hivatkozást;
eldöntöd, melyik részt kell rögzíteni;
egyetlen képlettel kitöltesz egy kétirányú táblázatot;
szorzó-, százalék- és összeadótáblát készítesz;
a három hivatkozástípus közül tudatosan választasz.
Olyan hivatkozás, amelynek csak az egyik fele rögzített.
$A2
Az oszlop rögzítve, a sor lép: jobbra másolva is az A oszlopban marad, lefelé másolva a sorszám nő.
A$2
A sor rögzítve, az oszlop lép: lefelé másolva is a 2. sorban marad, jobbra másolva az oszlopbetű lép.
Hivatkozás
Lefelé másolva
Jobbra másolva
A2 (relatív)
A3
B2
$A$2 (abszolút)
$A$2
$A$2
$A2 (vegyes)
$A3
$A2
A$2 (vegyes)
A$2
B$2
2
A szabály
✅ Honnan veszed az adatot?
Ha az adat egy fejlécoszlopban van (a bal szélen, lefelé sorakozva), annak az oszlopát rögzítsd: $A2.
Ha az adat egy fejlécsorban van (fent, jobbra sorakozva), annak a sorát rögzítsd: B$1.
Ahogy a 4.5-ben: előbb írd meg az alapképletet a bal felső cellába, és csak utána tedd ki a dollárjeleket (az F4 második és harmadik nyomása).
3
A szorzótábla végre
Az A2:A11 tartományba 1-től 10-ig (2.4), a B1:K1 tartományba szintén 1-től 10-ig.
A B2 cellába az alapképlet: =A2*B1.
Gondold végig: az A2 egy fejlécoszlopból jön → az oszlopát rögzítem: $A2. A B1 egy fejlécsorból jön → a sorát rögzítem: B$1. A végleges képlet: =$A2*B$1.
Töltsd ki jobbra a K2-ig, majd az egész sort lefelé a 11. sorig (Gyorsabb módszer: jelöld ki a B2:K11 tartományt, írd be a képletet, és Ctrl + Enter-rel zárd le: az Excel minden kijelölt cellába beírja, a hivatkozásokat cellánként igazítva.)
🔮 Jósold meg!
A B2-ben: =$A2*B$1. Mi lesz a képlet az E7 cellában, és mennyi az eredménye?
Megoldás
=$A7*E$1: az A oszlop maradt, a sor 7 lett; az 1. sor maradt, az oszlop E lett. Az A7-ben 6, az E1-ben 4 áll, így az eredmény 24 (6 × 4). Ellenőrizd a szorzótábla többi celláját is a Képletek megjelenítésével!
4
Kihívások
🏷️ 1. Kedvezménytábla
Az A3:A8 tartományba írj termékárakat (például 2000, 4500, 8000, 12 000, 15 000, 25 000 Ft).
A B2:E2 tartományba kedvezményeket: 10%, 15%, 20%, 25%.
A B3-ba egyetlen képlet, amely a kedvezményes árat számolja: =$A3*(1-B$2). Töltsd ki a teljes B3:E8 tartományt.
Formázd az árakat forintra (3.6), a fejlécet kiemelten (3.1).
➕ 2. Összeadótábla
Egy üres területen (például a G2-től) készíts összeadótáblát: a bal oszlopba és a felső sorba tizedes törtek kerüljenek (például 0,25-től 2,5-ig 0,25-ös lépésközzel, 2.4).
Egyetlen vegyes hivatkozásos képlettel töltsd ki: a két szám összegét mutassa, két tizedesjeggyel.
📱 3. Lili két telefonja (4.5)
A percdíjas táblázatot bővítsd két „telefonnal”: az F1 és a G1 cellában két különböző percdíj legyen, a B oszlopban a percek. Írj egy képletet a C2-be, amelyet jobbra (D2) és lefelé is kitöltve mindkét telefon napi költségét kiszámolja.
5
Hibavadászat
🐞 1. Peti csupa egyes szorzótáblája
Peti „biztos, ami biztos” alapon mindkét hivatkozást teljesen rögzítette: =$A$2*$B$1. A szorzótábla minden cellájában 1 áll.
Mi a megoldás?
Ha mindent rögzít, minden cella ugyanazt az 1 × 1-et számolja. A szorzótáblában mindkét tényezőnek egy irányban lépnie kell: a sor tényezője lefelé, az oszlopé jobbra. Helyesen: =$A2*B$1 (2. rész).
🐞 2. Lili fordított dollárjelei
Lili így írta: =A$2*$B1. Néhány cella jó, a többiben furcsa számok vannak.
Mit tegyen?
Lili éppen fordítva rögzített: a bal oldali fejlécoszlopnál a sort, a felső fejlécsornál az oszlopot. Így a képlet jobbra másolva a fejlécsor helyett a táblázat belsejébe mutat. Kérdezze meg mindkét hivatkozásnál: „Honnan jön az adat? Oszlopból → az oszlop elé $; sorból → a sor elé $.” Helyesen: =$A2*B$1.
Összefoglalás
$A2: az oszlop rögzített; A$2: a sor rögzített.
Fejlécoszlopból vett adat: oszlop elé $; fejlécsorból vett adat: sor elé $.
Kétirányú táblázat (szorzó-, kedvezmény-, összeadótábla) egyetlen képlettel.
Előbb az alapképlet, aztán az F4 (2. és 3. nyomás).
Ellenőrzés: Képletek megjelenítése és egy-két cella szúrópróbája.
Ellenőrizd magad!
Mi lesz a C3-ban lévő =$A3*C$2 képletből az F5-ben?
=$A5*F$2
Melyik rész rögzített a B$4 hivatkozásban?
A sor (4).
Miért nem jó a szorzótáblához a =$A$2*$B$1?
Mert egyik tényező sem lép, minden cellában ugyanaz az eredmény.
A lecke a 2.4 nyitott kérdését zárja le, így a diák a vegyes hivatkozást egy régóta várt megoldásként éli meg. Az „oszlopból jön → oszlop elé $, sorból jön → sor elé $” szabály egyszerű döntési eszköz. A három kihívás fokozatosan nehezedik, az utolsó a 4.5 Lili-esetét is megoldja. Peti és Lili hibái a két leggyakoribb tévutat mutatják be.
További gyakorlás
Vegyes hivatkozásos gyakorló feladatok (összeadótábla, százalékos tábla) Kolman Krisztián digitális kultúra tananyagában is találhatók: Táblázatkezelés – Excel (Szombathelyi Premontrei Szent Norbert Gimnázium).
Időterv
Mi a vegyes hivatkozás: 7 perc. A szabály: 5 perc. A szorzótábla és jóslás: 10 perc. Kihívások: 18 perc. Hibavadászat: 5 perc.
Következő lecke: 4.7 Hivatkozás másik munkalapra
Összesítő lap több havi lapból: lapok közötti hivatkozások, akár dollárjellel is.
A büfé árlistájánál (3.6) és a szorzótáblánál (2.4) elakadtunk: a képlet lemásolásakor az áfa cellája vagy a fejléc is „elvándorolt”. Most megtanulod a megoldást: egy kis dollárjellel megmondhatod az Excelnek, hogy egy hivatkozás maradjon a helyén. Ez az Excel egyik legfontosabb fogása.
⏱ kb. 45 perc
🎯 kezdőknek
🎒 8. osztálytól
Kép helye (16:9)Büfé árlista: a szerkesztőlécen a bruttó ár dollárjeles képlete, a munkalapon kék kerettel kiemelve az F1 áfa cella, a képlet lemásolva az összes termékre.Javasolt alternatív szöveg: „Abszolút hivatkozás az áfa cellájára”
A lecke végére
megmagyarázod, mit jelent az abszolút hivatkozás;
dollárjelet teszel a hivatkozásba gépelve és az F4-gyel;
eldöntöd, melyik hivatkozást kell rögzíteni;
áfás, percdíjas és valutaváltó táblázatot készítesz;
saját feladatot találsz ki az abszolút hivatkozásra.
az áfás képletet (3.6) és az egyéni formátumot (3.8).
1
Mi az abszolút hivatkozás?
📖 Fogalomtár
Abszolút hivatkozás
Olyan hivatkozás, amely másoláskor sem változik: mindig ugyanarra a cellára mutat. Jele: dollárjel az oszlop betűje és a sor száma előtt: $F$1.
A $ jelentése
„Ez a rész rögzítve van.” A $F rögzíti az oszlopot, a $1 a sort; a kettő együtt az egész cellát.
💡 A 4.4 hasonlata folytatva
„A tőled balra ülő padtárs füzete” relatív; „a tanári asztalon lévő füzet” abszolút. Akárhonnan kéred, mindig ugyanaz a füzet: ez a $F$1. (A $ jelnek itt semmi köze a dollárhoz, csak egy erre a célra választott jel.)
2
Az F4 billentyű
A dollárjeleket begépelheted (AltGr + É a magyar billentyűzeten), de gyorsabb az F4: szerkesztés közben a kurzort tedd a hivatkozásra (vagy épp azután kattints a cellára), és nyomd meg. Minden nyomásra a következő formára vált:
F4-nyomás
Forma
Jelentés
1.
$F$1
teljesen rögzítve (abszolút)
2.
F$1
csak a sor rögzítve (vegyes, 4.6)
3.
$F1
csak az oszlop rögzítve (vegyes, 4.6)
4.
F1
újra relatív
💡 Laptopon
Sok laptopon az F-billentyűk más funkciót is kapnak (hangerő, fényerő). Ha az F4 nem működik, próbáld az Fn + F4 kombinációt.
3
Mikor kell a dollárjel?
✅ Két lépésben
Előbb írd meg az alapképletet az első sorra, dollárjel nélkül, és ellenőrizd, hogy jó-e az eredmény.
Kérdezd meg minden hivatkozásnál: „Másoláskor ennek is lépnie kell, vagy a helyén kell maradnia?” Amelyiknek a helyén kell maradnia (egy közös paraméter: áfa, árfolyam, díj), az kap dollárjelet.
🔮 Jósold meg!
A C2-ben: =B2*(1+$F$1). Lemásolod a C3:C10 tartományba. Mi lesz a C7 képlete? És mi történne, ha a $ jelek nélkül másolnád?
Megoldás
A C7-ben: =B7*(1+$F$1): a B lépett, az F1 maradt. Dollárjel nélkül a C7-ben =B7*(1+F6) állna, az F6 pedig üres, így az áfa 0 lenne: a bruttó ár egyenlő a nettóval, hibaüzenet nélkül (3.6 büfé). Ezért kell mindig ellenőrizni egy középső sort is!
4
Kihívások
🥪 1. A büfé befejezése
Nyisd meg a 3.6-ban készült büfé-árlistát (vagy készíts újat: Termék, Nettó ár, Bruttó ár, Akciós ár; az F1-ben az áfa: 27%, az F2-ben a kedvezmény: 20%).
Írd át a Bruttó ár képletét: =B2*(1+$F$1), és töltsd ki lefelé.
Az Akciós ár: =C2*(1-$F$2), kitöltve.
Állítsd az áfát 18%-ra, a kedvezményt 25%-ra: frissült minden sor?
📱 2. Percdíjas telefonszámla
Az E1-be: „Percdíj:”, az F1-be egy kitalált díj, például 18 (formátum: 0" Ft/perc", 3.8).
Az A oszlopba egy hét napjai, a B-be a beszélgetett percek (formátum: 0" perc").
A C oszlopban a napi költség: =B2*$F$1, forint formátumban, egy tizedesjeggyel. Töltsd ki, és add össze.
💶 3. Valutaváltó
Az F1-be írd: „1 euró =”, a G1-be egy kitalált árfolyamot (például 400 Ft; a valódi árfolyam naponta változik).
Az A oszlopban forintárak, a B oszlopban számold ki euróban: =A2/$G$1, két tizedesjeggyel, € formátumban (3.6).
Változtasd meg az árfolyamot: mi történik?
💡 4. A te feladatod
Találj ki egy saját témájú feladatot, amelyben egy közös paraméter (egy cella) sok sor számításában szerepel, és csak abszolút hivatkozással oldható meg jól. Például: kilométerdíjas taxi, darabár egy osztályrendelésnél, pontszám-szorzó egy versenyen. Formázd meg a táblázatot, és cserélj feladatot a padtársaddal!
5
Hibavadászat
🐞 1. Peti egyforma árai
Peti a bruttó árat így írta: =$B$2*(1+F1), és lemásolta. Minden sor az első termék árából számol, és a második sortól kezdve már az áfa sem stimmel.
Mi a gond?
Peti a rossz hivatkozást rögzítette! A nettó árnak (B2) soronként lépnie kellene, az áfának (F1) pedig a helyén kellene maradnia. Helyesen: =B2*(1+$F$1). Mindig kérdezd meg minden hivatkozásnál: lépjen vagy maradjon? (3. rész)
🐞 2. Lili kétszeres F4-e
Lili a percdíjas táblázatban kétszer nyomta meg az F4-et, így a képlete =B2*F$1 lett. Lefelé másolva tökéletesen működik. Amikor azonban a táblázatot egy oszloppal jobbra is kibővítette (második telefon), ott rossz eredményt kapott.
Mi történt?
Az F$1 csak a sort rögzíti (vegyes hivatkozás): lefelé másolva marad az 1. sorban, jobbra másolva viszont az F-ből G lesz. Ha egy paraméternek minden irányban a helyén kell maradnia, a teljes $F$1 kell. A vegyes hivatkozások hasznos oldalát a 4.6 mutatja meg.
Összefoglalás
Abszolút hivatkozás: $F$1, másoláskor sem változik.
F4: $F$1 → F$1 → $F1 → F1 (laptopon Fn + F4).
Előbb az alapképlet, utána a dollárjel.
Kérdezd minden hivatkozásnál: lépjen vagy maradjon?
Közös paraméterhez (áfa, díj, árfolyam) abszolút hivatkozás kell.
Ellenőrizd magad!
Mi lesz a C2-ben lévő =B2*$E$1 képletből a C9-ben?
=B9*$E$1
Hányszor kell megnyomni az F4-et, hogy az A1-ből $A$1 legyen?
Egyszer.
Melyik cellát kell rögzíteni a =B2/G1 képletben, ha G1 az árfolyam?
A G1-et: =B2/$G$1
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 7–8. évfolyam: Táblázatkezelés (abszolút hivatkozás, képletek másolása); kapcsolódás a matematikához (egyenes arányosság, százalékszámítás) és a pénzügyi tudatossághoz (áfa, percdíj, árfolyam).
Módszertan
A lecke a 2.4, a 3.6 és a 4.3–4.4 korábban „nyitva hagyott” problémáit oldja meg, így az abszolút hivatkozás válaszként érkezik egy már átélt nehézségre. A „lépjen vagy maradjon” kérdés egyszerű döntési szabályt ad. A saját feladat megalkotása a megértés legmagasabb szintű ellenőrzése. Lili esete a vegyes hivatkozás (4.6) motivációja.
További gyakorlás
Hasonló típusú (nettó-bruttó, percdíjas számla, saját feladat) gyakorló feladatok Kolman Krisztián digitális kultúra tananyagában is találhatók: Táblázatkezelés – Excel (Szombathelyi Premontrei Szent Norbert Gimnázium).
Időterv
Mi az abszolút hivatkozás: 6 perc. Az F4: 5 perc. Mikor kell és jóslás: 8 perc. Kihívások: 21 perc. Hibavadászat: 5 perc.
Következő lecke: 4.6 Vegyes hivatkozás sorokra és oszlopokra
Csak a sor vagy csak az oszlop rögzítve: így készül el végre egyetlen képlettel a teljes szorzótábla.
A 2.3-ban és a 2.4-ben már láttad, hogy egy képlet másolásakor a hivatkozások „vándorolnak”. Most megérted, miért, és megtanulod előre megmondani, mi lesz egy képletből, akárhová másolod. Ez a tudás a 4.5 és a 4.6 lecke (dollárjel) alapja.
⏱ kb. 45 perc
🎯 kezdőknek
🎒 8. osztálytól
Kép helye (16:9)Kitöltőfogantyúval lemásolt képletek a Képletek megjelenítése nézetben: =A2*B2, =A3*B3, =A4*B4 egymás alatt, a hivatkozások soronként lépnek.Javasolt alternatív szöveg: „Relatív hivatkozások lemásolt képletekben”
A lecke végére
megmagyarázod, hogyan működik a relatív hivatkozás;
előre megmondod, mi lesz a képletből másolás után;
egy képlettel egy egész oszlopot kitöltesz;
a Képletek megjelenítésével ellenőrzöd a másolt képleteket;
Az alapértelmezett hivatkozás (például A2). Az Excel nem a cella „lakcímét” jegyzi meg, hanem azt, hogy a képlethez képest hol van: „két oszloppal balra, ugyanebben a sorban”.
Képletmásolás
Másoláskor az Excel ezt az útleírást viszi át az új helyre, ezért a hivatkozás annyi oszlopot és sort lép, amennyit a képlet.
💡 Hasonlat
Ha azt mondod egy osztálytársadnak: „a tőled balra ülő padtársad füzetét kérem”, akkor ez bárhol ülve működik, csak mindig más füzetet jelent. Ilyen a relatív hivatkozás. Ha viszont azt mondod: „a tanári asztalon lévő füzetet kérem”, az mindenhonnan ugyanaz. Ez lesz az abszolút hivatkozás (4.5).
2
Másolási irányok
Másolás iránya
Mi változik?
=A2*B2 a C2-ből →
lefelé 1 sorral
a sorszámok nőnek
C3: =A3*B3
jobbra 1 oszloppal
az oszlopbetűk lépnek
D2: =B2*C2
lefelé 3 sorral
sorszámok +3
C5: =A5*B5
felfelé 1 sorral
sorszámok −1
C1: =A1*B1
🔮 Jósold meg!
A C2-ben ez áll: =A2+B2. (a) Átmásolod az E4-be. Mi lesz a képlet? (b) Átmásolod a B2-be. Mi történik?
Megoldás
(a)=C4+D4: két oszloppal jobbra és két sorral lejjebb került, a hivatkozások is ennyit léptek. (b)=#HIV!+A2 lesz: az A2-től egy oszloppal balra már nincs oszlop, a hivatkozás „leesik” a munkalapról, ezért #HIV! hiba keletkezik (2.5).
3
Ellenőrzés
Képletek lap → Képletek megjelenítése: minden cellában a képletet látod, így egy pillantással ellenőrizheted, hogy a lemásolt képletek soronként a saját sorukra mutatnak-e (1.3).
Szúrópróba: kattints a kitöltött oszlop egy középső és az utolsó cellájára, és nézd meg a szerkesztőlécet, illetve az F2 színes kereteit (4.1).
4
A relatív hivatkozás határa
⚠️ Amikor valaminek a helyén kell maradnia
Ha minden sor ugyanarra az egy cellára hivatkozna (az áfára a 3.6 büféjében, a fejlécre a 2.4 szorzótáblájában), a relatív hivatkozás itt cserben hagy: másoláskor ez a hivatkozás is „elvándorol”, egy üres cellára mutat, és 0 vagy hibás eredményt ad. Erre való a dollárjel, amelyet a 4.5-ben tanulsz meg.
5
Kihívás: egy képlet, sok sor
📐 1. Téglalapok
Készíts táblázatot: a (cm), b (cm), Kerület (cm), Terület (cm²), legalább nyolc téglalappal (két tizedesjegyes oldalhosszakkal is).
Írd be az első sorba a képleteket: kerület =2*(A2+B2), terület =A2*B2.
Töltsd ki mindkét oszlopot egy mozdulattal: jelöld ki a C2:D2 tartományt, és kattints duplán a kitöltőfogantyúra (2.4).
Formázd az eredményeket két tizedesjegyre (3.5), és ellenőrizd a Képletek megjelenítésével.
🥐 2. Pékáru cukortartalma
Készíts táblázatot: Termék, Tömeg (g), Cukor (g), Cukortartalom, legalább hat kitalált termékkel (például kakaós csiga: 90 g, 18 g cukor).
A cukortartalom: =C2/B2, százalékformátumban, két tizedesjeggyel (3.6). Töltsd ki.
A tömeg és a cukor oszlopa jelenjen meg így: „90 g” (egyéni formátum, 3.8).
Melyik termékben a legnagyobb a cukortartalom? (Egyelőre nézd meg szemmel; az 5. fejezetben a MAX függvény kiszámolja.)
6
Hibavadászat
🐞 1. Peti elcsúszott másolata
Peti a terület képletét (=A2*B2, a C2-ben) átmásolta a szomszédos D2-be is, a kerülethez, hogy ott „majd átírja”. Elfelejtette, és most a D2-ben =B2*C2 áll: a kerület oszlopában értelmetlen számok vannak.
Mi a gond?
A D2 egy oszloppal jobbra van, ezért a hivatkozások is egy oszloppal jobbra léptek (2. rész): a b oldalt a területtel szorozza. Peti a kerülethez ne másolja a terület képletét, hanem írja meg külön: =2*(A2+B2). Másolni csak azonos szerkezetű számítást érdemes, azonos irányban.
🐞 2. Lili „ugyanaz” eredménye
Lili a cukortartalom képletét az első sorba helyesen beírta, majd a kitöltés helyett a cella eredményét másolta le értékként (2.3). Most minden termék cukortartalma ugyanannyi.
Mit tegyen?
Értékként beillesztve az Excel csak a számot vitte át, a képletet nem, így nem is tud soronként számolni. Lili a teljes beillesztést (vagy a kitöltőfogantyút) használja, és ellenőrizze a szerkesztőlécen, hogy minden sorban képlet áll.
Összefoglalás
A relatív hivatkozás a képlethez viszonyított helyet jegyzi meg.
Másoláskor a hivatkozások annyi sort és oszlopot lépnek, amennyit a képlet.
Ha egy cellának a helyén kell maradnia, a relatív hivatkozás nem elég: jön a dollárjel (4.5).
Ellenőrizd magad!
Mi lesz a B5-ben lévő =A5*2 képletből a B9-ben?
=A9*2
Mi lesz a C3-ban lévő =A3-B3 képletből az F3-ban?
=D3-E3
Miért kap #HIV! hibát a =A1 képlet, ha egy sorral feljebb másolod?
Mert az 1. sor fölött nincs sor, a hivatkozás kiesne a munkalapról.
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 7–8. évfolyam: Táblázatkezelés (relatív hivatkozás, képletek másolása); kapcsolódás a matematikához (téglalap kerülete és területe, arány, százalék) és a biológiához (táplálkozás, cukortartalom).
Módszertan
A lecke a 2.3 és 2.4 tapasztalataiból fogalmi megértést épít: a relatív hivatkozást „útleírásként” értelmezzük, amelyet a padtárs-hasonlat tesz szemléletessé, és amely egyben előkészíti az abszolút hivatkozás hasonlatát. A jóslás két irányban (átlósan és a munkalapról kifelé) is próbára teszi a modellt. A relatív hivatkozás határa tudatosan motiválja a 4.5 leckét.
További gyakorlás
Hasonló felépítésű, gyakorló feladatok a relatív, abszolút és vegyes hivatkozásokhoz Kolman Krisztián (Szombathelyi Premontrei Szent Norbert Gimnázium) digitális kultúra tananyagában is találhatók: Táblázatkezelés – Excel.
Időterv
Hogyan gondolkodik: 7 perc. Másolási irányok és jóslás: 9 perc. Ellenőrzés: 4 perc. A határ: 4 perc. Kihívás: 17 perc. Hibavadászat: 4 perc.
Következő lecke: 4.5 Abszolút hivatkozás: a dollárjel szerepe
A $ jellel egy cella a helyén marad: így készül el végre a büfé áfás árlistája.
A legjobb felhasználói élmény érdekében technológiákat, például sütiket használunk, hogy tároljuk és/vagy hozzáférjünk az eszköz információihoz. Ezek elfogadása lehetővé teszi az oldalon belüli viselkedésed vagy egyedi azonosítók adatainak feldolgozását. Ha nem fogadod el vagy vonod vissza a hozzájárulásodat, bizonyos funkciók korlátozottak lehetnek.
Funkcionális
Always active
A technikai tárolás vagy hozzáférés szigorúan szükséges az előfizető vagy felhasználó által kifejezetten kért konkrét szolgáltatás igénybevételének lehetővé tételéhez, vagy kizárólag az elektronikus hírközlő hálózaton keresztül történő kommunikáció továbbításához.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statisztika
A technikai tárolás vagy hozzáférés kizárólag statisztikai célokra történik.The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
A technikai tárolás vagy hozzáférés szükséges a felhasználói profilok létrehozásához reklámküldés céljából, vagy a felhasználók követéséhez egy vagy több weboldalon marketing célokból.