Excel táblázatkezelés kezdőknek › 12.2 lecke

Excel tanfolyam · 12.2 lecke · emelt szint

Elnevezett tartományok használata képletekben

A nevek akkor érik meg igazán, amikor a képletekben szerepelnek: a =SZUMHATÖBB(pontszam;osztaly;"9.b";nem;"lány") szinte mondatként olvasható. Ebben a leckében a 9. és a 10. fejezet ismert függvényeit írod át elnevezett tartományokkal, megtanulod a nevek gyors beillesztését, és azt is, mire figyelj, hogy a nevekkel írt képletek ugyanolyan biztonságosak legyenek, mint a hivatkozásokkal írtak.

  • ⏱ kb. 45 perc
  • 🎓 emelt szint
  • 🎒 11. osztálytól

A lecke végére

  • neveket írsz és illesztesz be képletekbe;
  • a feltételes és keresőfüggvényeket nevekkel írod meg;
  • tudod, hogyan viselkedik a név másoláskor;
  • meglévő képletekre alkalmazod a neveket;
  • elkerülöd a nevek használatának tipikus hibáit.

Amit már tudsz

  • a tartományok elnevezését (12.1);
  • a feltételes összesítést (9.4);
  • az FKERES-t és az INDEX + HOL.VAN-t (10.2, 10.7);
  • a relatív és az abszolút hivatkozást (4.4, 4.5).

Név a képletben

  • Beírás: gépeld be a név első betűit a képletben: az Excel felajánlja a hasonló nevű függvényeket és neveket (a neveket címke ikon jelöli). Válaszd ki, és nyomj Tab-ot.
  • Beillesztés listából: Képletek → Definiált nevek → Használat képletben, vagy F3 (Név beillesztése): az összes név listája.
  • Kijelöléssel: ha a képlet írása közben kijelölsz egy elnevezett tartományt, az Excel magától a nevét írja be a cellacím helyett.
  • A név bármely munkalapról használható (munkafüzet hatókörnél, 12.1), munkalapnév nélkül: =SZUM(pontszam) a Statisztika lapon is működik, ha a pontszám a Versenyzők lapon van.

Nevek a függvényekben

Nevek (a Versenyzők lap oszlopai): nev, osztaly, nem, pontszam; az árlista teljes tartománya: arlista; bemeneti cella: hatar.

KérdésHivatkozássalNévvel
összpontszám=SZUM($D$2:$D$61)=SZUM(pontszam)
a 9. b létszáma=DARABTELI($B$2:$B$61;"9.b")=DARABTELI(osztaly;"9.b")
a határt elérők száma=DARABTELI($D$2:$D$61;">="&$H$1)=DARABTELI(pontszam;">="&hatar)
a 9. b-s lányok átlaga=ÁTLAGHATÖBB($D$2:$D$61;$B$2:$B$61;"9.b";$C$2:$C$61;"lány")=ÁTLAGHATÖBB(pontszam;osztaly;"9.b";nem;"lány")
ár a kódból=FKERES(A2;Árlista!$A$2:$C$50;3;HAMIS)=FKERES(A2;arlista;3;HAMIS)
a legjobb neve=INDEX($A$2:$A$61;HOL.VAN(MAX($D$2:$D$61);$D$2:$D$61;0))=INDEX(nev;HOL.VAN(MAX(pontszam);pontszam;0))

✅ Olvasd fel!

A nevekkel írt képlet szinte felolvasható: „az index a névből, ahol a pontszám a pontszámok maximuma”. Ha egy képlet hangosan nehezen olvasható, valószínűleg jobb neveket érdemes adni.

Másolás és rögzítés

🔮 Jósold meg!

Az E2 cellában: =HA(D2>=hatar;"továbbjut";""), ahol a hatar a Beállítások!B1 cella neve. Kitöltöd lefelé az E61-ig. Mi lesz az E10 képlete?

Megoldás

=HA(D10>=hatar;"továbbjut";""). A D2 relatív hivatkozás, ezért lefelé vándorol (4.4), a név viszont mindig ugyanarra a cellára mutat, úgy, mint egy abszolút hivatkozás (4.5). A névvel tehát nem kell dollárjelet írni, és nem lehet elfelejteni.

  • Nevek alkalmazása meglévő képletekre: ha már vannak hivatkozásos képleteid, és utólag nevezel el tartományokat: Képletek → Név megadása nyila → Nevek alkalmazása. Az Excel a megfelelő hivatkozásokat a nevekre cseréli.

Mire figyelj?

  • Egyforma méretű nevek: a feltételes összesítő függvényekben (9.2, 9.4) a neveknek ugyanakkora tartományra kell mutatniuk, ugyanabban a sorban kezdődve. Ellenőrizd a Névkezelőben.
  • Elírt név: ha a név nem létezik, a képlet #NÉV? hibát ad (4.9), mint egy elírt függvénynév.
  • Teljes név egy soros számításban: egy sorhoz tartozó képletben (például „ennek a diáknak a pontszáma × 2”) a cellára hivatkozz (=D2*2), ne az egész névre. Az újabb Excel-verziókban a =pontszam*2 az egész oszlop eredményét „kiönti” a cellák alá, és ha ott már adat van, #KIÖMLÉS! hibát ad (14.8). A név a függvények tartományaként való igazán.

🎓 Érettségin

Ha a feladat kéri, hogy „a számításokban a pontszam névre hivatkozzon”, akkor a megfelelő képletekben a nevet kell használni, nem a cellatartományt. A javítók ezt a képletben ellenőrzik.

Kihívás: statisztikai lap nevekkel

📊 Minden képlet nevekkel

  1. A 12.1-ben elnevezett versenylistához készíts egy külön Statisztika munkalapot.
  2. Nevekkel számold ki: a versenyzők számát, az összpontszámot, az átlagot, a legjobb és a leggyengébb eredményt, és a legjobb versenyző nevét.
  3. Osztályonként (9.a, 9.b, 9.c, egy oszlopban) a létszámot, az átlagot és a határt elérők számát (DARABTELI, ÁTLAGHA, DARABHATÖBB nevekkel).
  4. Írd át a hatar értékét a Beállítások lapon: frissül minden?
  5. Egy régebbi, hivatkozásos képleteket tartalmazó lapon próbáld ki a Nevek alkalmazása parancsot. Melyik képletek változtak meg?
  6. Bónusz: hasonlítsd össze egy osztálytársad megoldásával: melyik képletet volt könnyebb megérteni, a nevest vagy a hivatkozásost?

Hibavadászat

🐞 1. Peti eltérő nevei

Peti képlete: =SZUMHATÖBB(pontszam;osztaly;"9.b"). Az eredmény #ÉRTÉK!. A Névkezelőben: pontszam = D2:D61, osztaly = B2:B60.

Mi a megoldás?

A két név különböző méretű tartományra mutat (4. rész), ezért a SZUMHATÖBB nem tudja párba állítani a sorokat (9.4). Peti javítsa az osztaly hivatkozását B2:B61-re a Névkezelőben.

🐞 2. Lili ismeretlen neve

Lili képlete: =ÁTLAG(pontszám), az eredmény #NÉV?. A Névkezelőben a név így szerepel: pontszam.

Mit tegyen?

A „pontszám” (ékezettel) és a „pontszam” (ékezet nélkül) két különböző név (12.1): a képletben használt név nem létezik. Lili használja a beillesztési listát (F3) vagy az automatikus kiegészítést, így nem írhatja el a nevet.

Összefoglalás

  • Név beírása: automatikus kiegészítés (Tab), F3 (Név beillesztése), vagy kijelöléssel.
  • A név bármely lapról, lapnév nélkül használható (munkafüzet hatókörnél).
  • Másoláskor a név nem vándorol: úgy viselkedik, mint egy abszolút hivatkozás.
  • Nevek alkalmazása: a meglévő képletek hivatkozásait nevekre cseréli.
  • Figyelj: egyforma méretű nevek, elírt név (#NÉV?), egész név egy soros képletben (#KIÖMLÉS!).

Ellenőrizd magad!

Hogyan illeszted be gyorsan egy név nevét a képletbe?

F3-mal (Név beillesztése) vagy a gépelés közbeni automatikus kiegészítéssel.

Kell-e dollárjelet tenni egy név elé, ha a képletet másolod?

Nem, a név mindig ugyanarra a tartományra mutat.

Mit jelez a #NÉV? hiba egy névvel írt képletben?

Hogy a képletben szereplő név nem létezik (például el van írva).

👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (képletek és függvények paraméterezése, cellahivatkozások). A digitális kultúra emelt szintű érettségi új követelménye (képletekben tartományokra nevük segítségével hivatkozni) közvetlen gyakorlása; kapcsolódás a programozáshoz (beszédes változónevek, olvasható kód).

Módszertan

A lecke a már ismert függvényeket „fordítja le” nevekre, így az új tudás a meglévőre épül, a táblázatos összevetés a két írásmód egyenértékűségét mutatja meg. A jóslás a név abszolút viselkedését tapasztalati úton mutatja be. A „mire figyelj” rész a három jellegzetes hibát (méreteltérés, elírás, kiömlés) előre jelzi. A beszédes nevek és a „felolvasható képlet” a programozásból ismert olvashatósági elvet viszi át a táblázatkezelésre.

Időterv

Név a képletben: 6 perc. Nevek a függvényekben: 10 perc. Másolás és jóslás: 6 perc. Mire figyelj: 5 perc. Kihívás: 14 perc. Hibavadászat: 4 perc.

Következő lecke: 12.3 Adatbázisfüggvények: lista és feltételtartomány

Az emelt szint másik különlegessége: az AB függvények, a 7.4 feltételtartományára építve.