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.