Képletek ellenőrzése és gyakori hibák az Excelben

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

Excel tanfolyam · 4.9 lecke

Képletek ellenőrzése és gyakori hibák

#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

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;
  • módszeresen nyomozod ki a hibákat.

Amit már tudsz

  • a #HIV! hibát (2.5);
  • a #NÉV? és a #ÉRTÉK! hibát (1.2, 2.1);
  • a színes hivatkozáskereteket (4.1).

A hibaértékek

Magyarul (angolul)JelentésGyakori okLecke
#HIV! (#REF!)érvénytelen hivatkozástörölt sor, oszlop, lap; a munkalapról „lelógó” másolás2.5, 4.4
#ÉRTÉK! (#VALUE!)rossz adattípusszöveggel szoroz, összead („12 db”)2.1
#ZÉRÓOSZTÓ! (#DIV/0!)nullával osztásaz osztó üres cella vagy 04.3, 4.8
#NÉV? (#NAME?)ismeretlen névelírt függvénynév, angol függvénynév, idézőjel nélküli szöveg1.2, 5. fejezet
#HIÁNYZIK (#N/A)nem található értéka keresőfüggvény nem talál (10. fejezet)10.
#SZÁM! (#NUM!)érvénytelen számtúl nagy eredmény, lehetetlen művelet–
#NULLA! (#NULL!)nincs közös részké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 el3.3

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.

Nyomozóeszközök

A Képletek lap Képletvizsgálat csoportja:
EszközMire jó?
Elődök mutatásakék nyilakkal mutatja, mely cellákból számol a kijelölt képlet
Utódok mutatásamely képletek használják a kijelölt cellát (ha törölnéd, mi romlana el?)
Nyilak eltávolításaeltünteti a nyilakat
Képletek megjelenítéseeredmények helyett képletek (1.3, 4.4)
Hibaellenőrzésvé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.

Nyomozási módszer

✅ Öt lépés

  1. Olvasd el a hibaértéket (1. rész): már ez is irányt mutat.
  2. Nézd meg a képletet az F2-vel: a színes keretek jó helyre mutatnak (4.1)?
  3. Ellenőrizd a hivatkozott cellákat: számok-e (jobbra igazodnak?), nem üresek-e (2.8, 4.3)?
  4. Kövesd vissza az Elődök mutatásával vagy a Képletkiértékelővel.
  5. Mondd ki szavakkal, mit kellene számolnia, és hasonlítsd össze egy fejben vagy számológéppel számolt eredménnyel (4.1).

Kihívás: hibás munkafüzet

🕵️ Javítsd ki a társad munkáját!

  1. 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).
  2. 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.
  3. 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.
  4. Írjátok fel, melyik hibát melyik eszközzel találtátok meg.

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.

Időterv

Hibaértékek: 9 perc. Körkörös hivatkozás és jóslás: 5 perc. Nyomozóeszközök: 8 perc. Módszer: 4 perc. Páros kihívás: 15 perc. Hibavadászat: 4 perc.

Következő lecke: 4.10 Mini projekt: bevásárlás kedvezményekkel

A 4. fejezet zárása: egy bevásárlólista paraméterlappal, abszolút és vegyes hivatkozással, százalékokkal és ellenőrzött képletekkel.

Százalékszámítás az Excelben: kedvezmény, változás és részarány

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

Excel tanfolyam · 4.8 lecke

Százalékszámítás: kedvezmény és változás

„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

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;
  • helyesen számolsz százalékos változást;
  • felismered a százalékos „trükköket”.

Amit már tudsz

  • a százalékformátumot és az alapképleteket (3.6);
  • a műveleti sorrendet (4.2);
  • az abszolút hivatkozást (4.5);
  • a százalékszámítást matematikából.

A három alapfeladat

📖 Fogalomtár (matematikából)

Százalékalap
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).
KeresettMatekExcel (B2: alap, C2: láb, D2: érték)
százalékértékalap × 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

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%.

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:
SorB (szavazat)C (részarány)
2: kirándulás12=B2/$B$6 → 40%
3: mozi9=B3/$B$6 → 30%
4: múzeum6=B4/$B$6 → 20%
5: bowling3=B5/$B$6 → 10%
6: összesen30100%

✅ Ellenőrzés

A részarányok összege mindig 100% (a kerekítés miatt néha 99% vagy 101% látszik, 3.5).

Kihívás: árfigyelő és szavazás

📈 1. Árfigyelő

  1. 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).
  2. 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.
  3. 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?
  4. Melyik hónapban volt a legnagyobb drágulás? A negatív változásokat formázd pirosra (3.8).

🗳️ 2. Osztályszavazás

  1. Írd fel egy kitalált osztályszavazás eredményét (legalább négy lehetőség).
  2. Számold ki az összes szavazatot (képlettel), majd minden lehetőség részarányát abszolút hivatkozással.
  3. Ellenőrizd, hogy a részarányok összege 100%-e.

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.

Hivatkozás másik munkalapra: paraméterlap és 3D-hivatkozás az Excelben

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

Excel tanfolyam · 4.7 lecke

Hivatkozás másik munkalapra

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

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);
  • a bemeneti cellákat (4.3);
  • az abszolút hivatkozást (4.5).

Ismétlés: a lapnév és a felkiáltójel

  • Forma: =Lapnév!Cella, például =Szeptember!B5.
  • 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).

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: ÁfaB1: 27%
A2: KedvezményB2: 20%
A3: Euró árfolyamB3: 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.

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).

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.

Kihívás: négyhavi zsebpénzfüzet

📅 Beállítások, havi lapok, összesítő

  1. Nyisd meg a 2.6-ban készült zsebpénz-munkafüzetet (Szeptember, Október, November, Összesítő).
  2. 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).
  3. Minden havi lapon a maradékot számold így: =Beállítások!$B$1-B5 (ahol B5 a havi összes kiadás).
  4. Az Összesítő lapon a három hónap kiadását egy 3D-hivatkozással add össze: =SZUM(Szeptember:November!B5).
  5. 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!
  6. Változtasd meg a havi keretet a Beállítások lapon: minden havi maradék frissült?

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.

Időterv

Ismétlés: 5 perc. Paraméterlap: 8 perc. 3D-hivatkozás és jóslás: 10 perc. Külső hivatkozás: 5 perc. Kihívás: 13 perc. Hibavadászat: 4 perc.

Következő lecke: 4.8 Százalékszámítás: kedvezmény és változás

Százalékos képletek a gyakorlatban: kedvezmény, felár, változás, és melyik cellát kell közben rögzíteni.

Vegyes hivatkozás sorokra és oszlopokra az Excelben

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

Excel tanfolyam · 4.6 lecke

Vegyes hivatkozás sorokra és oszlopokra

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

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.

Amit már tudsz

  • a relatív hivatkozást (4.4);
  • az abszolút hivatkozást és az F4-et (4.5);
  • a szorzótábla-nyomozást (2.4).

Mi a vegyes hivatkozás?

📖 Fogalomtár

Vegyes hivatkozás
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ásLefelé másolvaJobbra másolva
A2 (relatív)A3B2
$A$2 (abszolút)$A$2$A$2
$A2 (vegyes)$A3$A2
A$2 (vegyes)A$2B$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).

A szorzótábla végre

  1. Az A2:A11 tartományba 1-től 10-ig (2.4), a B1:K1 tartományba szintén 1-től 10-ig.
  2. A B2 cellába az alapképlet: =A2*B1.
  3. 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.
  4. 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!

Kihívások

🏷️ 1. Kedvezménytábla

  1. Az A3:A8 tartományba írj termékárakat (például 2000, 4500, 8000, 12 000, 15 000, 25 000 Ft).
  2. A B2:E2 tartományba kedvezményeket: 10%, 15%, 20%, 25%.
  3. 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.
  4. Formázd az árakat forintra (3.6), a fejlécet kiemelten (3.1).

➕ 2. Összeadótábla

  1. 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).
  2. 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.

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.
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 7–8. évfolyam (haladóbb szint): Táblázatkezelés (vegyes hivatkozás, kétdimenziós táblázatok); kapcsolódás a matematikához (szorzótábla, összeadás tizedes törtekkel, százalékszámítás).

Módszertan

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.

Abszolút hivatkozás: a dollárjel szerepe az Excelben

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

Excel tanfolyam · 4.5 lecke

Abszolút hivatkozás: a dollárjel szerepe

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

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.

Amit már tudsz

  • a relatív hivatkozást és határát (4.4);
  • a bemeneti cellákat és a varázsszámokat (4.3);
  • az áfás képletet (3.6) és az egyéni formátumot (3.8).

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.)

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ásFormaJelentés
1.$F$1teljesen rögzítve (abszolút)
2.F$1csak a sor rögzítve (vegyes, 4.6)
3.$F1csak 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.

Mikor kell a dollárjel?

✅ Két lépésben

  1. Előbb írd meg az alapképletet az első sorra, dollárjel nélkül, és ellenőrizd, hogy jó-e az eredmény.
  2. 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!

Kihívások

🥪 1. A büfé befejezése

  1. 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%).
  2. Írd át a Bruttó ár képletét: =B2*(1+$F$1), és töltsd ki lefelé.
  3. Az Akciós ár: =C2*(1-$F$2), kitöltve.
  4. Állítsd az áfát 18%-ra, a kedvezményt 25%-ra: frissült minden sor?

📱 2. Percdíjas telefonszámla

  1. Az E1-be: „Percdíj:”, az F1-be egy kitalált díj, például 18 (formátum: 0" Ft/perc", 3.8).
  2. Az A oszlopba egy hét napjai, a B-be a beszélgetett percek (formátum: 0" perc").
  3. 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ó

  1. 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).
  2. Az A oszlopban forintárak, a B oszlopban számold ki euróban: =A2/$G$1, két tizedesjeggyel, € formátumban (3.6).
  3. 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!

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.

Relatív hivatkozás és képletmásolás az Excelben

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

Excel tanfolyam · 4.4 lecke

Relatív hivatkozás és képletmásolás

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

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;
  • felismered, mikor nem elég a relatív hivatkozás.

Amit már tudsz

  • a képletek másolását és kivágását (2.3);
  • a kitöltőfogantyút és a szorzótábla-nyomozást (2.4);
  • a bemeneti és számoló cellákat (4.3).

Hogyan gondolkodik a relatív hivatkozás?

📖 Fogalomtár

Relatív hivatkozás
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).

Másolási irányok

Másolás irányaMi változik?=A2*B2 a C2-ből →
lefelé 1 sorrala sorszámok nőnekC3: =A3*B3
jobbra 1 oszloppalaz oszlopbetűk lépnekD2: =B2*C2
lefelé 3 sorralsorszámok +3C5: =A5*B5
felfelé 1 sorralsorszámok −1C1: =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).

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).

A relatív hivatkozás határa

Kihívás: egy képlet, sok sor

📐 1. Téglalapok

  1. 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).
  2. Írd be az első sorba a képleteket: kerület =2*(A2+B2), terület =A2*B2.
  3. 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).
  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

  1. 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).
  2. A cukortartalom: =C2/B2, százalékformátumban, két tizedesjeggyel (3.6). Töltsd ki.
  3. A tömeg és a cukor oszlopa jelenjen meg így: „90 g” (egyéni formátum, 3.8).
  4. Melyik termékben a legnagyobb a cukortartalom? (Egyelőre nézd meg szemmel; az 5. fejezetben a MAX függvény kiszámolja.)

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 a hivatkozás „leesik” a munkalapról: #HIV!.
  • Ellenőrzés: Képletek megjelenítése, szúrópróba, F2.
  • 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.