Papírgyűjtés

Egy középiskola néhány osztálya benevezett, az egy hétig tartó papírgyűjtési akcióba. A következő eredmények születtek, melyeket egy Excel táblázatban gyűjtöttünk össze. A táblázat feltöltési és formázási sorrendje megegyezik, a dokumentumbeli sorrendjével, azt célszerű betartani.

A táblázatban a hét napjait húzással kell beírni. Az üresen maradt E3 azt jelenti, hogy az osztály nem közölte a gyűjtött mennyiséget. (Itt nincs adat, nem úgy, mint ahol tudjuk, hogy nem gyűjtöttek semmit, ott 0 található. Ez az átlagszámításnál jelent különbséget, az átlag függvény ugyanis az üres cellákat teljesen figyelmen kívül hagyja, abban a sorban csak 4-el fog osztani.)

Ha a fenti értékeket beírtuk, akkor a továbbiakban szinte minden adat függvénnyel számított, kivétel az egy osztály illetve egy nap teljesítményének keresésénél, az osztály illetve a nap neve – ezt lásd később.

Összesítsük a G oszlopban az osztályok teljesítményét. G2 tartalma:

=szum(b2:f2)

Az oszlop további értékeit húzás segítségével töltsük fel.

H oszlopban számítsuk ki az osztályok napi átlagteljesítményét. H2 tartalma:

=átlag(b2:f2)

Az oszlop további értékeit húzás segítségével töltsük fel.

Az átlagértékeket számként, két tizedes jegy feltüntetésével formázzuk. Az I oszlopban adjuk meg a legnagyobb napi teljesítményt osztályonként. Használjuk a max függvényt. Az I2 tartalma:

=max(b2:f2)

Az oszlop további értékeit húzás segítségével töltsük fel.

Majd a J oszlopban a legkisebb napi teljesítményeket. Használjuk a min függvényt. A J2 tartalma:

=min(b2:f2)

Az oszlop további értékeit húzás segítségével töltsük fel.

Összesítsük az iskola teljesítményét naponként, számítsuk ki, hogy naponta mennyi volt az osztályok átlagteljesítménye (használjuk a fentiekben leírt szum és átlag függvényeket, és húzással töltsük fel a C10:G10 és C11:F10 cellákat). A teljesítményértékeket számként, 2 tizedes értékre formázzuk. Számítsuk ki, hogy a héten az összes osztály, összesen mennyi papírt gyűjtött. A számított mezők háttérszíne legyen 25%-os szürke.

Minősítsük az osztályokat aszerint, hogy az átlagosnál nagyobb, vagy kisebb volt a heti teljesítményük. A szükséges függvényt a K2 cellába kell írni, majd húzással minden osztályra érvényesíteni. Ez példa a függvények egymásba ágyazásának lehetőségére is. Ha húzással szeretnénk az osztályok minősítését elkészíteni, akkor figyeljünk az abszolút koordináták alkalmazására. A függvény:

=ha(g2>átlag(g$2:g$8);”Szorgalmas”;”Nem szorgalmas”)

Keressük ki, hogy melyik volt a legtöbb papírt gyűjtött osztály. Keressük meg, mennyi volt a legtöbb, amit egy napon gyűjtöttek az osztályok. Tegyük lehetővé, hogy bármely osztály heti eredményét megkérdezhessük. Tegyük lehetővé, hogy bármely nap összteljesítményét lekérdezhessük. Hirdessünk versenyeredményt: ki volt a győztes, és kérdezzük le, hogy melyik osztály lett az utolsó. Ezek magyarázó szövegeit tüntessük fel a feladatlapon. Mindig az A oszlopban, a B és C oszlopokkal egyesíteni nem kell, ahhoz, hogy látható legyen a D és E oszlop tartalma, az A, B és C oszlopokat a szükséges minimális méretnél szélesebbre formázzuk.

Lássuk a szükséges függvényeket.

A legtöbbet gyűjtött érték meghatározása, az E14-es cella:

=max(g2:g8)

A legkevesebb gyűjtött érték meghatározása, az E15-ös cella:

=min(g2:g8)

A D16-os cellába beírt osztály eredményének meghatározása függőlegesen elhelyezkedő értékek közötti kereséssel, az E16-os cella:

=fkeres(d16;a2:g8;7;hamis)

Helyes működését úgy ellenőrizhetjük, hogy a D16-os cellát másik osztály nevével töltjük fel.

A D17-es cellába beírt nap eredményének meghatározása vízszintesen elhelyezkedő értékek közötti kereséssel, az E17-es cella:

=vkeres(d17;b1:f10;10;hamis)

Helyes működését úgy ellenőrizhetjük, hogy a D17-es cellát másik nap nevével töltjük fel.

A győztes osztály meghatározása, a B19-es cella:

=index(a2:g8;hol.van(e14;g2:g8;0);1)

Az utolsó osztály meghatározása, a B20-as cella:

=index(a2:g8;hol.van(e15;g2:g8;0);1)

Vegyük észre, hogy az utóbbi két kereső függvény, csak a keresési értékekben különböznek, viszont működésük feltétele, hogy az E14 és E15 már fel legyen töltve, a legjobb illetve a leggyengébb eredménnyel. A versenyeredmények helyes meghatározásának ellenőrzésére módosítsunk az alapadatokon, írjunk be az átlagosnál jóval nagyobb értéket és figyeljük meg, hogy módosul-e a győztes osztály neve. Ugyanezt, csak ellentétes értelemben az utolsó helyezett osztály adatainak módosításával is megtehetjük, és akkor megváltozik az utolsó helyezett is.

Befejezésként formázzuk a munkalapot a következőképpen: ehhez először szúrjunk be két sort a tábla elejére. Figyeljünk arra, hogy beszúrással a relatív hivatkozások 2-vel eltolódnak (növekednek), tehát az eddig beírt képletek cellahivatkozásai már nem fognak megegyezni a fentebb leírtakkal. Ezért a formázást csak akkor célszerű megkezdeni, ha már meggyőződtél a táblázat függvényeinek helyes működéséről.

A lap minden cellája 10-es betűméretű, Arial (vagy Ariel CE) típusú legyen. A lap tájolása legyen fekvő (File/Oldalbeállítás…), a Papírgyűjtés szó középre (A1:K1) igazított cellaegyesítéssel, félkövér és alá van húzva. A fejléc és oldalléc szövegek félkövéren írottak, a fejlécek és az osztályok neve középre igazított. A B2:J10 tartomány középre igazított. A D18:D19 tartomány jobbra igazított. Az A3:K10 tartomány teljesen rácsozott, külső kerete vastag, de úgy, hogy a G12;A13:F13 tartományt is körbeveszi. Az A11:G11 tartomány belső rácsvonalai hiányzanak. A rácsvonalak végleges kialakításához használjuk a Formátum/Cellák…/Szegély párbeszéd-panelt.