1111 Budapest, Bertalan Lajos utca 7-9., BME L épület
A tavalyi évhez hasonlóan idén ősszel is indítunk egy blog sorozatot, amelynek témája a Microsoft Excel. Az első néhány bejegyzésben a legismertebb függvényeket tekintjük át egy-egy, a logisztikához valamilyen szinten kapcsolódó példán keresztül. A későbbiekben rátérünk a táblázatokra, azok formázására és kezelésére. Kimutatásokat készítünk egy tábla adataiból, illetve több tábla összekapcsolását követően. Végezetül néhány bejegyzésben bemutatjuk a VBA alkalmazását, hogyan lehet a mindennapi feladatokat automatizálni, egyszerűsíteni.
Térjünk is rá első bejegyzésünkre, amelyben a legalapvetőbb függvényekkel, a SZUM és az ÁTLAG felhasználásával oldunk meg egy feladatot.
Kiinduló adatok
A fenti kiinduló táblázatra fogjuk építeni a mai példánkat: minden sor egy adott napon adott termék értékesített mennyiségét (liter) és az abból származó bevételt (Euró) tartalmazza. A fájl letölthető az alábbi linkről: Excel_01
Navigáció
Egy megnyitott Excel fájl, tehát egy munkafüzet (workbook) több munkalapból (worksheet) áll. Egy-egy ilyen munkalap celláin egérrel vagy a billentyűzet nyilaival navigálhatunk. Amennyiben egyszerre több cellát szeretnénk kijelölni, úgy vagy az egér bal gombjának nyomvatartásával jelölhetünk ki egy területet, vagy a SHIFT lenyomása közben a nyilak segítségével növeljük lépésről lépésre a kijelölt tartományt (range).
Ha a fenti táblázat egy cellájára kattintunk, akkor a CTRL+nyíl segítségével odaugorhatunk az adott sor/oszlop első/utolsó cellájára. Például a C10-es cellában állva megnyomva a CTRL+jobb nyíl gombot, a kijelölés a 10-es sor legutolsó – adatot is tartalmazó – cellájára ugrik.
Amennyiben a SHIFT a kijelölés, a CTRL az ugrás gyorsbillentyűje, akkor a kettő együtt kijelöli az összes cellát a kiinduló és az ugrást követő cella között. Például a C10-es cellában ha lenyomjuk a CTRL+SHIFT+lefele nyilat, akkor a C oszlop utolsó foglalt elemére ugrunk (C16) és közben minden cellát kijelölünk (C10:C16).
További – kijelöléshez használható – billentyű kombinációk: CTRL+SPACE (szóköz) kijelöli a kiinduló cellát tartalmazó teljes oszlopot, a SHIFT+SPACE pedig a teljes sort. A kettő együttes használatával, tehát a CTRL+SHIFT+SPACE kombinációval a cellát is tartalmazó teljes táblázat kerül kiválasztásra (A1:E16), a CTRL+A-val pedig a teljes munkalap.
Egyenlet
Az Excel célja, hogy a táblázatokban tárolt adatokat manipuláljuk, azokból számításokat és elemzéseket végezzünk. Ennek alapvető eszközei az egyenletek, amelyeket a cellákba vagy a szerkesztőlécbe kell beírni. Minden egyenlet egy egyenlőségjellel kezdődik, azt követően pedig használhatóak a beépített függvények, logikai operátorok és aritmetikai műveletek.
Első feladatként számítsuk ki az értékesített mennyiség és az értékesítésből származó költség alapján az egyes termékek fajlagos (literenkénti) árait. Ehhez az F2-es cellába kezdjük el gépelni az alábbi képletet (formula):
=E2/D2
Az E2 és a D2 két cellahivatkozás, amelyeket be is gépelhetünk, de billentyűzet/egér segítségével ki is jelölhetjük. Ez a képlet elosztja a bevételt a mennyiséggel, így megadva a fajlagos árat [€/liter] mértékegységben. Ahhoz, hogy az egész oszlopban érvényesüljön a képlet és ne csak az F2-es cellában, le kell másolnunk a képletet a többi cellára is. Ennek négy opciója létezik:
- Egérrel megfogjuk az F2-es cella jobb alsó sarkában lévő zöld négyzetet és ezt a fogantyút lehúzzuk az F16-os celláig.
- Egérrel kétszer rákattintunk a kijelölt F2-es cella zöld kis négyzetére és az Excel automatikusan „lehúzza” a képletet az F16-os celláig.
- Az F2-es cellát másoljuk (pl. CTRL+C billentyűkombinációval), kijelöljük a többi cellát (F3:F16) és beillesztjük (CTRL+V).
- F2-es cellával együtt kijelöljük az F2:F16 tartományt (akár egérrel, akár a gyorsbillentyűk segítségével), majd megnyomjuk a CTRL+D gombot.
Amint láthatjuk, ahogy lemásoltuk a képletet, úgy a képletben lévő cellahivatkozások is elmozdultak: például az F10-es cellába kattintva a képlet =E10/D10-re változott. Mivel nem mindig szeretnénk, hogy ez megtörténjen, így a relatív cellahivatkozás (amikor képlettel együtt mozog a kijelölés) megszüntethető és abszolúttá tehető, amiről egy későbbi bejegyzésben lesz szó.
SZUM függvény
A SZUM függvény (angolban SUM) összegzi a kijelölt cellák és/vagy tartományok értékeit.
=SZUM(szám1; [szám2]; …)
A fenti szintaxis megegyezik az Excel által alkalmazott formátummal, ahol a szögletes zárójelben lévő paraméterek opcionálisak. Tehát a SZUM függvény esetén legalább egy számot vagy tartományt ki kell jelölni, de pontosvessző utáni további számok, tartományok megadása már nem kötelező.
A függvény segítségével összegezzük az egy hét alatt értékesített összes mennyiséget. Ehhez írjuk be egy tetszőleges cellába az alábbi képletet:
=SZUM(D2:D16)
Ha ezt a képletet az előbb bemutatott módszerrel egyel jobbra másoljuk, akkor a bevételt összegezzük, hiszen a relatív cellahivatkozások miatt a képlet az alábbiak szerint változik meg:
=SZUM(E2:E16)
ÁTLAG függvény
Az ÁTLAG függvény (angolban AVERAGE) kiszámítja a kijelölt számok átlagát. Szintaxisa megegyezik a SZUM függvényével:
=ÁTLAG(szám1; [szám2]; …)
Ezek alapján az egyszerre értékesített átlagos mennyiség és – a képlet jobbra másolásával – az adott termékek értékesítéséből származó átlagos bevétel az alábbi képlettel számítható:
=ÁTLAG(D2:D16)
A fenti számítások eredményei az alábbi ábrán láthatóak:
A következő bejegyzésben folytatjuk az egyszerűbb függvények bemutatását, illetve megismerjük a függvények egymásba ágyazásának lehetőségét.
