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
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és | Hivatkozással | Né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*2az 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
- A 12.1-ben elnevezett versenylistához készíts egy külön
Statisztikamunkalapot. - 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.
- 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).
- Írd át a
hatarértékét a Beállítások lapon: frissül minden? - Egy régebbi, hivatkozásos képleteket tartalmazó lapon próbáld ki a Nevek alkalmazása parancsot. Melyik képletek változtak meg?
- 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.
