Excel táblázatkezelés kezdőknek › 12.8 lecke
Excel tanfolyam · 12.8 lecke · emelt szint
Képletmásolás két irányban, vegyes hivatkozással
A 4.6-ban a vegyes hivatkozással szorzótáblát készítettünk: egyetlen képlet, amelyet jobbra és lefelé is másolhatunk. Az emelt szintű feladatokban ugyanez a technika kereszttáblákban jelenik meg: osztályonként és nemenként, hónaponként és kategóriánként kell összesíteni, és a javítók azt is nézik, hogy a táblázatot egyetlen, helyesen másolható képlettel töltötted-e ki. Ebben a leckében a vegyes hivatkozást a feltételes összesítő függvényekkel és az elnevezett tartományokkal kapcsolod össze.
- ⏱ kb. 45 perc
- 🎓 emelt szint
- 🎒 11. osztálytól
A lecke végére
- megfogalmazod a vegyes hivatkozás szabályát kereszttáblákra;
- egyetlen képlettel kitöltesz egy kereszttáblát;
- a neveket és a vegyes hivatkozást együtt használod;
- a kereszttáblát sor- és oszlopösszeggel ellenőrzöd;
- felismered a rossz helyre tett dollárjeleket.
Amit már tudsz
A szabály
| Hivatkozás | Másoláskor | F4 lenyomására |
|---|---|---|
F2 | sor és oszlop is változik | 1. állapot |
$F$2 | semmi sem változik | 2. állapot |
F$2 | csak az oszlop változik, a sor rögzített | 3. állapot |
$F2 | csak a sor változik, az oszlop rögzített | 4. állapot |
✅ Kereszttábla-szabály
- A sorfejléc (a bal oldali oszlopban, például az osztályok) minden sorban mást mutat, de mindig ugyanabban az oszlopban van: az oszlopot rögzítsd →
$F2. - Az oszlopfejléc (a felső sorban, például a nemek) minden oszlopban mást mutat, de mindig ugyanabban a sorban van: a sort rögzítsd →
G$1. - Az adattartományok soha nem mozdulhatnak:
$B$2:$B$9vagy egy név (12.2).
Kereszttábla egy képlettel
A 12.3 listája (Név, Osztály, Nem, Pontszám) mellett, a nevekkel (osztaly, nem, pontszam, 12.2):
- A sorfejlécek az F2:F4-ben: 9.a, 9.b, 9.c. Az oszlopfejlécek a G1:H1-ben: lány, fiú.
- A G2 cellába egyetlen képlet:
=SZUMHATÖBB(pontszam;osztaly;$F2;nem;G$1). - Másold jobbra (H2), majd a G2:H2-t lefelé (G4:H4-ig).
- Ugyanígy készíthetsz darabszám-táblát (
=DARABHATÖBB(osztaly;$F2;nem;G$1)) és átlagtáblát (=HAHIBA(ÁTLAGHATÖBB(pontszam;osztaly;$F2;nem;G$1);"–"), 8.6).
| Összeg | lány | fiú | Összesen |
|---|---|---|---|
| 9.a | 42 | 29 | 71 |
| 9.b | 95 | 79 | 174 |
| 9.c | 33 | 47 | 80 |
| Összesen | 170 | 155 | 325 |
🔮 Jósold meg!
Lili dollárjelek nélkül írta a G2-be: =SZUMHATÖBB(pontszam;osztaly;F2;nem;G1), és ugyanígy másolta. Mi lesz a H4 cella képlete, és mit mutat?
Megoldás
A H4-ben: =SZUMHATÖBB(pontszam;osztaly;G4;nem;H3). A G4 és a H3 a kereszttábla egy-egy eredménycellája (nem osztály- és nemnév): olyan osztály és nem nincs, ezért 0. A G2 kivételével szinte minden cella rossz eredményt ad, hibaüzenet nélkül. A neveknek köszönhetően az adattartományok nem csúsztak el, de a fejléc-hivatkozások igen. A sorfejlécnél az oszlopot, az oszlopfejlécnél a sort kell rögzíteni (1. rész).
💡 Nevek és vegyes hivatkozás együtt
A nevek (12.2) a tartományokat rögzítik, így ott nem kell dollárjel. A fejléccellákra viszont továbbra is vegyes hivatkozás kell. Nevek nélkül a képlet: =SZUMHATÖBB($D$2:$D$9;$B$2:$B$9;$F2;$C$2:$C$9;G$1), ugyanazt adja, de nehezebb olvasni.
Ellenőrzés
- Sor- és oszlopösszegek: az I oszlopban
=SZUM(G2:H2), az 5. sorban=SZUM(G2:G4). A sorösszegeknek egyezniük kell az osztályok összpontszámával (=SZUMHA(osztaly;F2;pontszam)), az oszlopösszegeknek a nemekével. - Végösszeg: a kereszttábla jobb alsó sarka (I5) egyezzen meg a teljes pontszámmal:
=I5=SZUM(pontszam)→ IGAZ (325). - Szúrópróba: kattints egy belső cellára (például H3), és nézd meg a színes kereteket: a sorfejléc a saját sorában (F3), az oszlopfejléc a saját oszlopában (H1) van-e.
Kihívás: három kereszttábla
📊 Egy képlet, egy tábla
- A 12.3 listája mellé készítsd el a fenti összeg kereszttáblát egyetlen, jobbra és lefelé másolt képlettel, sor- és oszlopösszegekkel.
- Alá ugyanígy a darabszám (DARABHATÖBB) és az átlag (ÁTLAGHATÖBB, hiba esetén „–”) kereszttáblát.
- Ellenőrizd: a darabszám-tábla végösszege 8, az összegtábláé 325.
- Bővítsd egy oszloppal: „Összesen” átlag osztályonként (ÁTLAGHA az F oszlop fejlécére, csak a sort változtatva).
- Bónusz: a 4.6 szorzótáblájának mintájára készíts egy „kedvezményes ár” táblát: a sorokban árak (500, 1000, 1500 Ft), az oszlopokban kedvezmények (5%, 10%, 20%), a képlet:
=$A2*(1-B$1).
Megoldások
- Darabszám: 9.a: 1 lány, 1 fiú; 9.b: 2, 2; 9.c: 1, 1; összesen 8.
- Átlag: 9.a: 42 / 29; 9.b: 47,5 / 39,5; 9.c: 33 / 47.
- Osztályátlag: 9.a 35,5; 9.b 43,5; 9.c 40 (
=ÁTLAGHA(osztaly;$F2;pontszam)). - Kedvezményes ár: 500 Ft: 475 / 450 / 400; 1000 Ft: 950 / 900 / 800; 1500 Ft: 1425 / 1350 / 1200.
Hibavadászat
🐞 1. Peti egyforma sorai
Peti képlete: =SZUMHATÖBB(pontszam;osztaly;$F$2;nem;G$1). Másolás után mindhárom sorban ugyanaz áll: 42 és 29.
Mi a megoldás?
A sorfejlécet teljesen rögzítette ($F$2), így minden sor a 9.a-ra számol. A sorfejlécnél csak az oszlopot kell rögzíteni, a sornak változnia kell: $F2 (1. rész).
🐞 2. Lili felcserélt dollárjelei
Lili képlete: =SZUMHATÖBB(pontszam;osztaly;F$2;nem;$G1). A G2 helyes (42), a többi cellában 0 vagy furcsa számok.
Mit tegyen?
Lili éppen fordítva rögzített: a sorfejlécnél a sort, az oszlopfejlécnél az oszlopot. Jobbra másolva az F$2-ből G$2 lesz (egy eredménycella), lefelé a $G1-ből $G2 (szintén eredménycella). Helyesen: $F2 és G$1. Emlékeztető: „a dollár a fejléc irányába mutat”: a bal oldali fejlécnél az oszlop elé, a felső fejlécnél a sor elé.
Összefoglalás
- Vegyes hivatkozás: $F2 (oszlop rögzített), F$2 (sor rögzített); F4-gyel váltogatható.
- Kereszttábla: a sorfejléc $F2, az oszlopfejléc G$1, az adattartomány rögzített (név vagy $).
- Egyetlen képlet tölti ki az egész táblát, jobbra és lefelé másolva.
- Ellenőrzés: sor- és oszlopösszegek, végösszeg a teljes összeggel, szúrópróba a színes keretekkel.
- A nevek a tartományokat, a vegyes hivatkozás a fejléceket rögzíti.
Ellenőrizd magad!
Milyen hivatkozást kap a kereszttábla bal oldali sorfejléce?
Rögzített oszlopú vegyes hivatkozást, például $F2.
Hányszor kell az F4-et megnyomni, hogy F2-ből F$2 legyen?
Kétszer (F2 → $F$2 → F$2).
Hogyan ellenőrzöd egy összeg-kereszttábla helyességét?
A végösszegnek egyeznie kell a teljes lista összegével, a sor- és oszlopösszegeknek a csoportösszegekkel.
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (cellahivatkozások: relatív, abszolút, vegyes; képletek másolása), adatok összesítése két szempont szerint; kapcsolódás a matematikához (kétdimenziós táblázatok, mátrix). Az emelt szintű érettségi feladatok jellegzetes eleme: kereszttábla kitöltése egyetlen, helyesen másolható képlettel.
Módszertan
A lecke a 4.6 szorzótáblás vegyes hivatkozását a feltételes összesítő függvényekkel és a nevekkel kapcsolja össze, emelt szintű alkalmazásban. A „dollár a fejléc irányába” szabály könnyen megjegyezhető. Az ellenőrzés (sor-, oszlop- és végösszeg) a kereszttábla belső konzisztenciáját vizsgálja. A két hibavadászat a két tipikus rossz rögzítést mutatja meg.
Időterv
A szabály: 6 perc. Kereszttábla egy képlettel és jóslás: 12 perc. Ellenőrzés: 5 perc. Kihívás: 16 perc. Hibavadászat: 4 perc.
Következő lecke: 12.9 Mini projekt: versenyeredmények összetett elemzése
Az emelt szintű eszközök együtt: nevek, AB függvények, összetett képletek és kereszttábla egy versenyértékelésben.
