Excel táblázatkezelés kezdőknek › 12.7 lecke
Excel tanfolyam · 12.7 lecke · emelt szint
Összetett függvények lépésenkénti felépítése
Az emelt szintű feladatokban gyakran egyetlen cellába kell egy olyan képletet írni, amelyben három-négy függvény ágyazódik egymásba: egy HA, benne egy ÉS, abban egy ÁTLAGHA. Ha egyszerre próbálod beírni, könnyen eltéved a zárójelek és a pontosvesszők között. Ebben a leckében egy biztos módszert tanulsz: szavakkal kezdeni, segédoszlopokkal építkezni, belülről kifelé összevonni, és közben minden lépést ellenőrizni.
- ⏱ kb. 45 perc
- 🎓 emelt szint
- 🎒 11. osztálytól
A lecke végére
- egy összetett feladatot szavakkal lépésekre bontasz;
- segédoszlopokkal építed fel és ellenőrzöd a megoldást;
- belülről kifelé egyetlen képletbe vonod össze;
- a Képletkiértékelővel lépésenként vizsgálsz meg egy képletet;
- felismered a zárójel- és pontosvesszőhibákat.
Amit már tudsz
A négy lépés
- Szavakkal: írd le a feladatot egyszerű lépésekben, mint egy algoritmust: „Számold ki…, nézd meg, hogy…, ha igen, írd ki…”. Minden „számold ki” egy függvény lesz, minden „ha” egy HA, minden „és” egy ÉS.
- Segédoszlopokkal: minden lépés kerüljön külön oszlopba, felirattal. Töltsd ki lefelé, és ellenőrizd néhány soron kézzel.
- Belülről kifelé összevonva: ha minden segédoszlop jó, másold be a belső képletet (az egyenlőségjel nélkül) a rá hivatkozó képletbe, a hivatkozás helyére. Lépésenként, oszlopról oszlopra.
- Ellenőrizve: az összevont képlet minden sorban ugyanazt adja-e, mint a segédoszlopos megoldás? Egy ellenőrző oszlop:
=G2=F2(8.1). Ha a feladat megengedi, a segédoszlopok maradhatnak is.
🎓 Érettségin
Ha a feladat nem tiltja, a segédoszlop teljesen elfogadott, sőt sokszor ez a biztosabb. Ha kifejezetten egyetlen képletet kér („egyetlen képlettel határozza meg…”), akkor is segédoszlopokkal érdemes kezdeni, és a végén összevonni, majd a segédoszlopokat törölni.
Kidolgozott példa
Feladat (a 12.3 listáján, a 12.1 neveivel: osztaly, pontszam): „Az E oszlopban jelenjen meg a »tovább« szó, ha a versenyző pontszáma eléri a saját osztályának átlagát, és legalább 40 pont; különben maradjon üres.”
| Lépés szavakkal | Segédoszlop |
|---|---|
| 1. Mennyi a saját osztályának átlaga? | F2: =ÁTLAGHA(osztaly;B2;pontszam) |
| 2. Eléri az átlagot, ÉS legalább 40? | G2: =ÉS(D2>=F2;D2>=40) |
| 3. Ha igen, „tovább”, különben üres. | H2: =HA(G2;"tovább";"") |
Összevonás belülről kifelé:
- A G2-ben az F2 helyére a képlete:
=ÉS(D2>=ÁTLAGHA(osztaly;B2;pontszam);D2>=40) - A H2-ben a G2 helyére ez:
=HA(ÉS(D2>=ÁTLAGHA(osztaly;B2;pontszam);D2>=40);"tovább";"")
Ellenőrzés: kik jutnak tovább?
Az osztályátlagok: 9.a 35,5; 9.b 43,5; 9.c 40. Továbbjut: Kiss Anna (42 ≥ 35,5 és ≥ 40), Tóth Lili (45 ≥ 43,5), Varga Zsófi (50), Kovács Péter (47 ≥ 40). Nem jut tovább: Nagy Bence (38 < 43,5), Szabó Máté (29), Horváth Dóra (33 < 40), Molnár Ádám (41 < 43,5, bár ≥ 40). Összesen 4 továbbjutó: =DARABTELI(E2:E9;"tovább").
Ellenőrző eszközök
| Eszköz | Hogyan? | Mire jó? |
|---|---|---|
| Képletkiértékelő | Képletek → Képletvizsgálat → Képletkiértékelés → Kiértékelés gomb ismételve | lépésenként megmutatja, mit számol ki az Excel belülről kifelé |
| F9 egy képletrészen | a szerkesztőlécen jelölj ki egy részt (például az ÁTLAGHA(…)-t), nyomj F9-et: megjelenik az értéke. Utána Esc! | egy belső függvény gyors ellenőrzése |
| Színes zárójelek | a szerkesztőlécen az összetartozó zárójelpárok azonos színűek, és a kurzor mellett kiemelődnek | a zárójelhibák megtalálása |
| Függvénysúgó | a képlet írása közben a sárga buborék mutatja, hányadik argumentumnál tartasz (félkövéren) | a pontosvesszők helyének ellenőrzése |
| Elődök mutatása | Képletek → Elődök mutatása (4.9) | melyik cellákból számol a képlet |
🔮 Jósold meg!
A szerkesztőlécen kijelölöd az ÁTLAGHA(osztaly;B2;pontszam) részt, megnyomod az F9-et (megjelenik: 35,5), majd Entert nyomsz Esc helyett. Mi történik a képlettel, ha utána lefelé kitöltöd?
Megoldás
A képletben az ÁTLAGHA helyére végleg a 35,5 szám került: =HA(ÉS(D2>=35,5;D2>=40);…). Kitöltéskor minden sor ezzel a számmal hasonlít, a 9. b-sek és a 9. c-sek is, így rossz eredményt kapsz, hibaüzenet nélkül. Az F9 után mindig Esc-et nyomj (vagy ha már megtörtént, Ctrl + Z).
Kihívás: helyezés és osztályelső
🏅 Építsd fel lépésenként!
A 12.3 listáján, a nevekkel (osztaly, pontszam) dolgozz. Minden feladatot előbb szavakkal, aztán segédoszlopokkal, végül egyetlen képletben oldj meg.
- Helyezés az osztályon belül: „Hányan értek el az osztályában nála többet? Ennél eggyel jobb a helyezése.” Segédoszlop:
=DARABHATÖBB(osztaly;B2;pontszam;">"&D2), majd +1. - Osztályelső: „Ha a pontszáma egyenlő az osztálya legjobb pontszámával, írd ki: osztályelső.” (Tipp: MAXHA függvény az újabb Excelben, vagy a 12.5 AB.MAX-a.)
- Mondat: „Kiss Anna a 9.a osztályban az 1. helyen végzett (42 pont).” (& jel és az 1. feladat eredménye, 11.4.)
- Vond össze a 2. feladatot egyetlen képletbe, és ellenőrizd a Képletkiértékelővel.
Megoldások
- Helyezések: 9.a: Kiss Anna 1., Szabó Máté 2.; 9.b: Varga Zsófi 1., Tóth Lili 2., Molnár Ádám 3., Nagy Bence 4.; 9.c: Kovács Péter 1., Horváth Dóra 2.
- Osztályelsők: Kiss Anna, Varga Zsófi, Kovács Péter. Egy képletben:
=HA(D2=MAXHA(pontszam;osztaly;B2);"osztályelső";""). - Mondat:
=A2&" a "&B2&" osztályban a "&(DARABHATÖBB(osztaly;B2;pontszam;">"&D2)+1)&". helyen végzett ("&D2&" pont)."
Hibavadászat
🐞 1. Peti zárójelei
Peti egyben írta be: =HA(ÉS(D2>=ÁTLAGHA(osztaly;B2;pontszam;D2>=40);"tovább";""). Az Excel hibaüzenetet ad: túl kevés argumentum, vagy hiányzó zárójel.
Mi a megoldás?
Az ÁTLAGHA zárójele nem zárult be a pontszam után, ezért a D2>=40 az ÁTLAGHA negyedik argumentuma lett, és az ÉS-nek csak egy argumentuma maradt, a HA zárójele pedig hiányzik. Helyesen: …ÁTLAGHA(osztaly;B2;pontszam);D2>=40)…. Kattints a zárójelek mellé, és nézd a színeket; vagy építsd fel segédoszlopokkal (1. rész).
🐞 2. Lili „befagyott” átlaga
Lili képlete kitöltés után minden osztályra ugyanazzal a számmal hasonlít: =HA(ÉS(D2>=35,5;D2>=40);"tovább";""). Ő nem írt be 35,5-öt.
Mit tegyen?
Lili ellenőrzéskor F9-et nyomott az ÁTLAGHA részen, majd Entert (3. rész, jóslás): a függvény helyére a kiszámított szám került. Írja vissza a függvényt: ÁTLAGHA(osztaly;B2;pontszam), és legközelebb F9 után Esc-et nyomjon.
Összefoglalás
- Négy lépés: szavakkal → segédoszlopok → belülről kifelé összevonás → ellenőrzés.
- Minden „számold ki” egy függvény, minden „ha” egy HA, minden „és” egy ÉS.
- Összevonás: a belső képletet (= nélkül) a hivatkozás helyére másolod.
- Eszközök: Képletkiértékelő, F9 + Esc, színes zárójelek, függvénysúgó, elődök.
- A segédoszlop érettségin is elfogadott, ha a feladat nem tiltja.
Ellenőrizd magad!
Hol találod a Képletkiértékelőt?
Képletek lap → Képletvizsgálat → Képletkiértékelés.
Mit kell nyomni az F9 után, hogy a képlet ne változzon meg?
Esc-et.
Milyen sorrendben vonod össze a segédoszlopokat?
Belülről kifelé: a legbelső számítást másolod be elsőként a rá hivatkozó képletbe.
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (összetett képletek, egymásba ágyazott függvények, hibakeresés), algoritmikus gondolkodás (probléma lépésekre bontása); kapcsolódás a programozáshoz (dekompozíció, részfeladatok, lépésenkénti nyomkövetés). Az emelt szintű érettségi feladatokban gyakori „egyetlen képlettel” típusú utasítások megoldási stratégiája.
Módszertan
A lecke nem új függvényt, hanem módszert tanít: a dekompozíciót és az ellenőrzést, amely az emelt szintű táblázatkezelési és a programozási feladatoknál egyaránt kulcsfontosságú. A kidolgozott példa minden lépése a kis adatsoron ellenőrizhető. A jóslás az F9 egy kevéssé ismert, veszélyes mellékhatását mutatja meg. Peti zárójelhibája a leggyakoribb szintaktikai hibát elemzi.
Időterv
A négy lépés: 6 perc. Kidolgozott példa: 12 perc. Ellenőrző eszközök és jóslás: 8 perc. Kihívás: 15 perc. Hibavadászat: 4 perc.
Következő lecke: 12.8 Képletmásolás két irányban, vegyes hivatkozással
Egy képlet, egy egész táblázat: a $A1 és az A$1 a szorzótábla és a kereszttáblák kulcsa.
