1111 Budapest, Bertalan Lajos utca 7-9., BME L épület

Excel a gyakorlatban I.

Excel a gyakorlatban

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

Excel példa 1 - 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:

  1. 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.
  2. 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.
  3. 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).
  4. 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:

Excel példa 1 - Megoldás

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.

Tetszett az oldal? Oszd meg másokkal is!
English