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
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!B5lefelé másolva=Szeptember!B6lesz, 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: Á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.
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!B5a 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.
⚠️ 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.
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?
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.
