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 függvények egymásba ágyazását (5.7);
  • a HA, ÉS, VAGY függvényt (8.2, 8.3);
  • az ÁTLAGHA-t és a DARABHATÖBB-öt (9.3, 9.4);
  • az elnevezett tartományokat (12.2).

A négy lépés

  1. 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.
  2. 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.
  3. 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.
  4. 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 szavakkalSegé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özHogyan?Mire jó?
KépletkiértékelőKépletek → Képletvizsgálat → Képletkiértékelés → Kiértékelés gomb ismételvelépésenként megmutatja, mit számol ki az Excel belülről kifelé
F9 egy képletrészena 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ójeleka szerkesztőlécen az összetartozó zárójelpárok azonos színűek, és a kurzor mellett kiemelődneka 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ásaKé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.

  1. 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.
  2. 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.)
  3. Mondat: „Kiss Anna a 9.a osztályban az 1. helyen végzett (42 pont).” (& jel és az 1. feladat eredménye, 11.4.)
  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.