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 vegyes hivatkozást és a szorzótáblát (4.6);
  • a kategóriatáblát (9.2) és a TÖBB függvényeket (9.4);
  • az elnevezett tartományokat (12.2);
  • a 12.3 példalistáját (12.3).

A szabály

HivatkozásMásoláskorF4 lenyomására
F2sor és oszlop is változik1. állapot
$F$2semmi sem változik2. állapot
F$2csak az oszlop változik, a sor rögzített3. állapot
$F2csak a sor változik, az oszlop rögzített4. á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$9 vagy 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):

  1. A sorfejlécek az F2:F4-ben: 9.a, 9.b, 9.c. Az oszlopfejlécek a G1:H1-ben: lány, fiú.
  2. A G2 cellába egyetlen képlet: =SZUMHATÖBB(pontszam;osztaly;$F2;nem;G$1).
  3. Másold jobbra (H2), majd a G2:H2-t lefelé (G4:H4-ig).
  4. 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).
ÖsszeglányfiúÖsszesen
9.a422971
9.b9579174
9.c334780
Összesen170155325

🔮 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

  1. 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.
  2. Alá ugyanígy a darabszám (DARABHATÖBB) és az átlag (ÁTLAGHATÖBB, hiba esetén „–”) kereszttáblát.
  3. Ellenőrizd: a darabszám-tábla végösszege 8, az összegtábláé 325.
  4. Bővítsd egy oszloppal: „Összesen” átlag osztályonként (ÁTLAGHA az F oszlop fejlécére, csak a sort változtatva).
  5. 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.