A táblázat Vizsga 2 oszlopában a pótvizsga (vagy javítóvizsga) eredménye található, a Vizsga 3 oszlopban a második pótvizsga eredménye. Ha egy hallgatónál nem szerepel jegy, akkor nem sikerült megszereznie az aláírást a tárgyból. A statisztika készítésénél minden esetben csak az utoljára megszerzett jegy számít.
A statisztikákat egy új munkafüzet külön lapjain készítse el, hogy az adatok lecserélésével más tárgyak átlagait is könnyen ki lehessen vele számítani.
Ha Internet Explorert használ, akkor a fenti linkre kattintva a fájl a böngésző ablakában jelenik meg. Ilyenkor nincsenek eszköztárak, így a fájl tartalmát meg lehet nézni, de dolgozni nem nagyon lehet. Kattintson a jobb egérgombbal a linkre, majd válassza a Cél mentése másként (Save target as) menüpontot. A mentési hely megadása után letölti a fájlt, majd a megjelenő ablakban a megnyitásra kattintva most már külön ablakban jelenik meg a fájl.
Megoldási stratégiánk a következő: először egyszerűsítsük a feladatot, amennyire lehet, majd ha ennek már sikerült megtalálni a megoldását, akkor ezt általánosítva próbálunk eljutni az eredeti feladat megoldásához.
Előszőr határozzuk meg az összes hallgató számát, akik egy konkrét félévben felvették a tárgyakat. Jó tesztalany mindjárt az első félév (1990/91/1), mert csak egy tárgy lett meghirdetve, és ránézéssel is megszámolható: az erdemény 10 kell legyen. Ezt a korábban tanultak alapján a darabteli (countif) függvénnyel határozhatjuk meg. A feladatkiírás szerint a statisztikákat egy új munkafüzetben kell létrehozni, ezért
- Nyisson meg egy új munkafüzetet, és mentse el ugyanoda, ahova a a FelevesEredmenyek.xls fájlt mentette.
- A munka1 nevű fület nevezze át a következőre: Hallgatók száma.
- Az A1 cellában kezdje el írni a képletet, majd amikor a tartományt kell megadni, váltson át a FelevesEredmenyek.xls fájlra, és jelölje ki a teljes A oszlopot. A kritérium az "1990/91/1" szöveg lehet.
Következő lépésben ezt próbáljuk kiterjeszteni az összes félévre. A képletet mozgassa át a B1 cellába, az A1 cellába
írja be az 1990/91/1 szöveget, és módosítsa a képletet, hogy a konstans szöveg helyett erre a szövegre mutasson.
Az eredmény:
=DARABTELI('[FelevesEredmenyek.xls]Féléves eredmények'!$A:$A;A1). Természetesen az eredmény ugyanúgy 10
kell maradjon.
Ha az A2, A3, ... cellákba begépeljük a félévneveket, akkor az előbbi képlet végigmásolásával megkapjuk az összes félév hallgatói létszámát. A baj csak az, hogy 35-ször legépelni a félévneveket elég unalmas. Sajnos az első 2 vagy 4 elem megadása alapján ilyen sorozatot az Excel már nem tölt ki automatikusan, ezért inkább szövegkezelő függvényekkel (ld. Office súgó) összerakjuk elemeiből.
Szúrjunk be egy új oszlopot a bal szélen, ahol egy 1990, 1990, 1991, 1991, 1992, 1992, 1993, ...
alakú listát készítünk. Sajnos az Excel auto-kitöltője ezt sem hajlandó létrehozni, ezért például létrehozhatjuk úgy, hogy
az A1 cellába beírjuk az 1990 kiinduló értéket, az A2 cella képlete =A1, az A3-é =A2+1, majd az utóbbi kettőt másoljuk le,
ameddig szükséges.- Most már elkezdhetjük összerakni a félév szövegét: a B1 cellában a =A1&"/"&A1+1 képlet összefűzi az 1990 és az 1991 szöveget, és közé ír egy perjelet. Az eredmény: 1990/1991. Az & jel a szövegösszefűzés operátor, vagy használható az összefűz függvény: =ÖSSZEFŰZ(A1;"/";A1+1)
- A végére még oda kellene tenni a félév számát (1 vagy 2).
Ezt előállíthatjuk egy külön oszlopban: szúrjon be egy új oszlopot az A és B oszlop közé,
írja be az első két cellába az 1-et és 2-t, majd másolja végig, de az automatikus kitöltés opcióiból válassza a Cellák másolását.- Másik lehetőség, hogy a ha() függvénnyel állítjuk elő: ha az aktuális évszám egyenlő a következővel, akkor őszi, ha nem, akkor tavaszi félév: =ha(A1=A2;1;2)
- A második évszámból csak az utolsó két számjegyet szoktuk kiírni. A bal(szöveg;szám) függvény a szöveg első-, a jobb(szöveg;szám) a szöveg utolsó megadott számú karakterét adja eredményül.
Így a félévet előállító képlet végül így néz ki a C1 cellában:
=A1&"/"&JOBB(A1+1;2)&"/"&B1. Most már végigmásolhatjuk
a D oszlopban a darabteli() függvényt, és megvan a tárgyakat felvett hallgatók száma félévenként.
Az eredeti feladat megoldásához már csak egyet kellene tennünk: a számlálást tárgyanként kellene elvégezni. Ez azért nehéz, mert így két kritériumunk van: egy a Félév, egy pedig a Tárgy oszlopra vonatkozóan, a darabteli() függvény viszont csak egy oszlopra vonatkozó kritériumot enged meg. Ilyenkor a következő trükk használható: a FelevesEredmenyek.xls listájában készítünk egy segédoszlopot, ahol összefűzzük a Félév és a Tárgynév szövegét (esetleg valami elválasztó jellel), majd a Statisztika.xls-ben lévő darabteli függvényt módosítjuk úgy, hogy a tartománya ez az új oszlop legyen, a kritériuma pedig a Tárgynév és a Félév ugyanolyan összefűzése.
- Az összefűzéshez a FelevesEredmenyek.xls fájlban a G2 cellába írjuk beezt a képletet:
=A2&" - "&B2, aminek eredménye pl. 1990/91/1 - Bevezetés az informatikába 1. lesz, majd ezt másoljuk végig. A másolás vonszolással elég lassú több, mint 9000 sor esetén, viszont a másolósarokra duplán kattintva az Excel automatikusan végigküldi a képletet. - A Statisztikak.xls fájlban szúrjon be egy új sor legfelülre, majd a darabteli() oszlopának fejlécébe (D1 cella) másolja be az egyik tárgynevet.
- Alatta a darabteli függvényt módosítsa az alábbiak szerint:
=DARABTELI('[FelevesEredmenyek.xls]Féléves eredmények'!$G:$G;$C2&" - "&D$1). A kritériumban a vegyes hivatkozás használata miatt ha a mellette lévő oszlop fejlécébe (E1 cella) beírja a másik tárgy nevét, akkor a képlet jobbra, és lefelé is másolható.
A feladat megoldható segédoszlop nélkül is az ab.darab() függvénnyel, ld. Súgó.
- Egy új oszlopban számítsa ki minden sorhoz az utolsó vizsgaeredményt.
- Készítsen táblázatot, amelynek soraiban az egyes félévek, oszlopaiban a tárgyak szerepelnek, és határozza meg a sor- és oszlopfejlécek alapján az átlagokat. Mivel nincsen feltételes átlag függvény, használja a szumha()/darabteli() képletet.
Az előzőhöz hasonlóan használja a darabteli() függvényt.
A tárgyat mindenkinek újra fel kell vennie, aki nem szerzett jegyet, vagy a végső vizsgajegye elégtelen, így őket kell összeszámolni félévenként és tárgyanként.
Legegyszerűbb, ha előszőr megszámoljuk, hogy az egyes hallgatók az egyes tárgyakat hányszor vették fel, majd az így kapott lista alapján megszámoljuk, hogy az egyes tárgyaknál hányszor van 1, 2, etc.
- Előszőr létre kell hozni a hallgatók listáját. Most szerencsére nem kell leszűrni a különböző értékeket (erre az Office 2007 beépített parancsot ad, de a korábbi Excelekben kicsit körülményesen, az Irányított szűrővel lehet ezt megkapni) ez lehet a számok listája 1-től a legnagyobb előforduló hallgato_id-ig. A legnagyobb hallgato_id-t megadja a max() függvény, vagy a teljes oszlop kijelölése után az állapotsor (legalul) jobb oldalán megjelenő összegen jobb-klikk és Max. Mivel a legnagyobb előforduló érték több ezres, a lehúzásos módszer elég lassú. Helyette be kell írni az A2 cellába 1-et, majd alá =A2+1 képletet. Ezt kijelölve másolás, majd a shift gombot nyomva tartva a Page Down gombbal elég gyorsan el lehet érni a végéig, végül beillesztés.
- Listánk most nyilván olyan hallgato_id-ket is tartalmaz, amelyek nem szerepelnek az eredmények között. Ezek kiszűréséhez az fkeres() függvénnyel írhatunk egy tetszőleges jelet a szomszédos oszlopba, vagy a darabteli() függvénnyel megszámolhatjuk, hogy hányszor szerepelnek az eredmények között. Az Autoszűrőt bekapcsolva leszűrhetjük az eredményeknél nem szereplő id-ket, majd az összes ilyen sort letörölhetjük. Sajnos a kieső sorok hatására a szépen egyesével növekvő sorozatunk tele lesz #REF! hibával, mert hivatkozott sorokat kitöröltünk. Így a törlés visszavonása és a szűrés kikapcsolása után előszőr "be kell égetnünk" a sorszámokat. Jelölje ki a sorszámokat tartalmazó oszlopot, másolás, majd ugyanide illessze vissza: a Szerkesztés (vagy jobb klikk)> Irányított beillesztés (Edit> Paste Special) ablakban válassza az Értéket (Value). Ellenőrizze, hogy a képletek tényleg lecserélődtek az eredményekre, és végezze el újra a szűrést és törlést.
- Az egyes tárgyakból előforduló darabszámokat a darabteli() függvénnyel közvetlenül most sem tudjuk meghatározni, mert két oszlopban van a kritérium (pl. tárgy=BevInf 2. és hallgato_id=3532). Ilyenkor a korábban már használt trükkhöz lehet folyamodni. A darabteli() képletét végigküldve elég lassan számol, így érdemes kikapcsolni az automatikus számolást (Eszközök> Beállítások> Számolás fül, Tools> Options> Calculation). Kézi számítást ezután az F9 gomb megnyomásával lehet kérni.
- Az így elkészült táblázat alapján a darabteli() függvénnyel egyszerűen megszámolható, hogy az egyes tárgyakat hányan vették fel egyszer, kétszer, etc. A legnagyobb előforduló darabszám az oszlop kijelölése után megnézhető az állapotsoron, vagy az Autoszűrő bekapcsolásával az oszlop szűrőlistájában.
- Ellenőrizze a számítást úgy, hogy közvetlenül meghatározza, hány hallgató vette fel a két tárgyat, és ezeket összehasonlítja az előbbi eredmények összegeivel.