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.