A feladat megoldása során az Excel 2010 használata a javasolt, de a segédlet a 2003-as verzióhoz és Calchoz is használható. A feladat elvégzése során a következőket fogjuk gyakorolni:
Külső adatok importálása. Szöveg, logikai és egyszerű statisztikai függvények használata. Relatív és abszolút hivatkozások alkalmazása. Összetett logikai feltételek, egybeágyazott függvények felépítése. Konstans adatok, névkezelő használata. Adatok sorba rendezése. Diagram készítése, cellák formázása.
A feladat megoldása hozzávetőlegesen 80 percet vesz igénybe.
Ebben a feladatban folytatjuk az Excel fontos függvényeinek megismerését. A megoldás során egy céges nyilvántartással dolgozunk, és az itt felmerülő kérdésekre válaszolunk.
A Fájl/Megnyitás parancs segítségével nyissuk meg a Nyers.xlsx nevű fájlt. Soha ne dupla kattintással nyissuk meg a táblázatokat, ha olyan környezetben dolgozunk, ahol nem tudjuk, milyen program van az adott kiterjesztésű fájlhoz rendelve. A megnyitás után egy olyan két munkalapból álló dokumentumot kapunk, amiből a dolgozók adatai hiányoznak. Az Adatok/Külső adatok átvétele/Szövegből paranccsal olvassuk be a dolgozókat tartalmazó Adatok.csv fájlt. Ha nézőkével belenézünk a csv-be, akkor látjuk, hogy a különböző oszlopokba kerülendő adatok pontosvesszővel vannak elválasztva, ezért a Szövegbeolvasó varázsló első lépésében válasszuk a Tagoltat, a másodikban pedig a Pontosvesszővel elválasztottat. Csak azokat az adatokat tároljuk számként, amelyekkel számolunk, a harmadik oszlopban lévő személyi számmal nem számolunk, ezért a harmadik lépésben állítsuk át a formátumát szövegre.
A befejezés gombra kattintás után állítsuk be, hogy a Munka1 munkalap A2-es cellájától kezdve töltse be az adatokat, mert a táblázat első sorában a fejlécek vannak. Ha ezzel végeztünk a Munka1 munkalapot nevezzük át Adatoknak.
Családi pótlékot most – az egyszerűség kedvéért – csak hölgyek kapnak abban az esetben, ha legalább egy gyerekkel rendelkeznek és az éves bruttó jövedelmük nem éri el a 1500000 Ftot. A családi pótlék összege egy gyerek esetében 15000 Ft, több gyerek esetében ez az összeg gyerekenként 1500 Ft-tal növekedik. Egyéb esetben az érték 0 Ft. A feltételek ellenőrzéséhez a HA() függvényt fogjuk használni. A táblázatból hiányzik az egyes dolgozók gyermekeinek a száma. A C oszlopra állva a jobb egérgombbal megjelenő menü beszúrás parancsával a lakhely és a személyi szám közé szúrjunk be egy új oszlopot, ahova a hiányzó értékeket betölthetjük. Az oszlop felirata legyen „Gyerekek száma”. A számokat a VÉLETLEN.KÖZÖTT() függvénnyel állítjuk elő. A C2es cellába írjuk be, hogy =VÉLETLEN.KÖZÖTT(0;5). Ennek segítségével egy nulla és öt közé eső véletlen számot fog előállítani úgy, hogy a nullát és az ötöt is generálhatja. A képletet a kitöltőfülre való dupla kattintással másoljuk le a C oszlop többi cellájába is. A családi pótlék kiszámolásához meg kell határoznunk a dolgozók nemét, amit a személyi szám első jegyének lecsípésével állapíthatunk meg. Személyi szám első jegye 1 2 3 4
Születési évszáma 19XX 19XX 20XX 20XX
Neme férfi nő férfi nő
A férfiak személyi számának az első számjegye páratlan, a nőknek páros. Az azonosító első jegyét a BAL() függvény segítségével tudjuk levágni, majd a PÁROSE() függvénnyel ellenőrizhetjük, hogy páros-e. Ezzel meg is van a logikai vizsgálatunk első fele, már el tudjuk dönteni, hogy az adott személy nő-e: PÁROSE(BAL(személyi szám)). A vizsgálat második felében azt kell megnéznünk, hogy az adott dolgozónak van-e gyereke. Ezt egy egyszerű reláció segítségével dönthetjük el: gyerekek száma > 0. Az éves jövedelmet úgy kapjuk meg, hogy a havit megszorozzuk 12-vel. Ezt alkalmazva a harmadik kritériumot is elő tudjuk állítani: havi alapfizetés * 12 < 1500000. Az előző három feltételnek egyszerre kell teljesülnie, hogy az adott dolgozó kapjon családi pótlékot. A feltételeket az ÉS() függvény segítségével tudjuk összekapcsolni:
ÉS(PÁROSE(BAL(személyi szám)); gyerekek száma > 0; havi alapfizetés * 12 < 1500000). Ha a feltétel igaz, akkor egy gyereknél 15000 Ft-ot, többnél pedig gyerekenként plusz 1500 Ft-ot kell adni, tehát 15000+(gyerekek száma - 1)*1500 Ft-ot, ellenkező esetben 0 Ft a juttatás. Az előbbiek alapján el is készült a függvényünk: HA(ÉS(PÁROSE(BAL(személyi szám)); gyerekek száma > 0; havi alapfizetés * 12 < 1500000); 15000+(gyerekek száma 1)*1500;0). Az első emberhez tartozó személyi szám a D2, gyerekek száma C2, alapfizetése pedig a E2 cellában van. Az Excel-feladatok megoldásakor tilos a konstans adatokat a képletbe fix módon beírni, ehelyett hivatkozni kell rájuk. Ennek oka az, hogy ha a konstans adat megváltozik, akkor a nem hivatkozással előállított képlet nem tudja követni a változásokat. A konstans adatok a konstansok munkalapon helyezkednek el.
Helyettesítsük be a képletbe a cellahivatkozásokat. A beillesztésnél figyelni kell arra, hogy a másolásnál módosulandó celláknál relatív, a konstans adatoknál pedig abszolút hivatkozást használjunk. A cellákat rögzíteni a dollár jelekkel tudjuk, amit a legegyszerűbben az F4-es billentyű segítségével érhetünk el. Relatív hivatkozás
A1
Abszolút hivatkozás
$A$1
Vegyes hivatkozás (rögzített oszlop)
$A1
Vegyes hivatkozás (rögzített sor)
A$1
HA(ÉS(PÁROSE(BAL(D2)); C2 > 0; E2 * 12 < 1500000); 15000+(C2 - 1)*1500;0) Az első emberhez tartozó családi pótlékhoz az E2 cellába =HA(ÉS(PÁROSE(BAL(D2)); C2 > 0; E2 * 12 < Konstansok!$B$3);Konstansok!$B$1+(C2 - 1)*Konstansok!$B$2;0) képletet kell írni. Az összetett függvény előállításához használhatjuk a függvényvarázslót is. Az E2-be írt függvényt másoljuk át az oszlop többi cellájába is.
Azok a dolgozók, akik nem Győrben laknak utazási hozzájárulást kapnak. Az utazási hozzájárulás összege 5000 Ft és ez is a Konstansok munkalapon van. Az egyes cellákat, blokkokat el tudjuk látni egyedi névvel. A cellák átnevezését a Név mező segítségével vagy a Képletek/Definiált nevek/Névkezelő menüpont alól tudjuk elvégezni. A Név mezővel csak az új cellaneveket tudjuk megadni a Névkezelővel a meglévő neveket módosítani, törölni is tudjuk. Ha a Név mezőbe egy nem új, hanem egy már létező cellanevet írunk, akkor az Excel kijelöli az adott névvel ellátott cellát, blokkot. Nevezzük át a Konstansok munkalapon a B4-es cellában lévő utazási hozzájárulást utazásnak. Az AZONOS() függvénnyel lehet leellenőrizni, hogy az egyes szövegek egyformák-e. A függvény első paraméterének adjuk meg a lakhelyet tartalmazó cella hivatkozását, a másodiknak pedig a „Győr” szöveget. Figyelni kell arra, hogy a szöveget idézőjelbe kell tenni. A hozzájárulás kiszámolásához megint a HA() függvényt kell alkalmazni, ha az előzőekben leírt feltétel nem teljesül, akkor meg kell kapnia a dolgozónak az utazásnak elnevezett összeget, ha pedig teljesül, akkor 0 Ft-ot kell neki adni. A G2 cellába a következő függvényt kell írni: =HA(AZONOS(B2;"Győr");0;utazás), majd végig kell másolni az egész oszlopba.
A havi összes bruttó jövedelem a havi fizetés, a családi pótlék és az utazás hozzájárulás öszszege. Ezt a SZUM() függvénnyel számolhatjuk ki a H2 cellába: =SZUM(E2:G2).
Most csak 2 db adókulcsot használunk. Ha valakinek az éves jövedelme nem éri el a 1700000 Ft-ot, akkor a jövedelme 20%-át fizeti be adóként. Különben az adó 1700000 Ft-ig 20% plusz a 1700000 Ft-on felüli rész 30%-a. A %-ok és a sávhatár szintén szerepel a konstansok munkalapon. Nevezzük át ezeket a mezőket, a 20%-ot alsónak, a 30%-ot felsőnek, a 1700000 Ft-ot pedig sávhatárnak. A számoláshoz itt is a HA() függvényt kell alkalmazni. A logikai vizsgálat során azt kell megnézni, hogy az éves bruttó jövedelem eléri-e a sávhatárt, itt figyelni kell arra, hogy a cellába a havi bruttó szerepel, ezért azt meg kell szorozni 12-vel. Ha túllépi a sávhatárt akkor (sávhatár*alsó+(havi bruttó*12-sávhatár)*felső)/12 Ft-ot kell adónak befizetni. A 12-es
osztó azért van a képlet végén, mert a cellába a havi adót számoljuk ki és a számolás során a sávhatár miatt az évessel dolgoztunk. I2 cellába az =HA(H2*12>sávhatár;(sávhatár*alsó+(H2*12-sávhatár)*felső)/12;H2*alsó) függvényt írjuk, majd másoljuk le az I oszlop többi cellájába.
A havi összes bruttó jövedelem és a havi adó különbsége. Képletet nem csak egyenlőség jellel, hanem összeadás és kivonás művelettel is lehet kezdeni. Ezt kipróbálva a J2 cellába a +H2-I2 képletet kell írni. Figyeljük meg, hogy az enter lenyomása után a szerkesztőlécben az =+H2-I2 képlet fog megjelenni.
Rendezzük az adatokat összes nettó jövedelem szerint csökkenő sorrendbe. A rendezés általános menete: Álljunk bele a rendezni kívánt blokk egyik cellájába (vagy jelöljük ki az egész táblázatot). Ezután válasszuk a Kezdőlap menüszalag Rendezés és szűrés menüjének egyéni sorrend… parancsát, majd az Oszlop listában állítsuk be, hogy mely oszlop alapján szeretnénk rendezni, a Sorrend listában pedig a csökkenő sorrendet. A Rendezés párbeszédablakban az Újabb szint nyomógombbal további rendezési szempontok hozhatók létre, amelyek akkor lépnek életbe, ha a felettük álló szempont(ok) alapján holtverseny áll elő. Amennyiben az aktív cella a nettó jövedelem oszlopában van, a rendezés elvégezhető a Rendezés méret szerint (csökkenő) parancs kiválasztásával is. Érdekesség, hogy Excel 2010-ben nem csak a cellaértékek, hanem színek szerint is lehet rendezni.
Hozzunk létre egy új munkalapot Statisztika néven és számoljuk ki rá a nettó jövedelmek átlagát, minimumát és maximumát. A számolás elvégzése előtt az összes nettó jövedelem oszlopot a fejléc nélkül nevezzük át nettónak. Majd a Statisztika lap A oszlopába soroljuk fel az „Átlag”, „Minimum”, „Maximum” feliratokat és melléjük a B oszlopba írjuk be az =ÁTLAG(nettó), =MIN(nettó), =MAX(nettó) függvényeket.
Ábrázoljuk oszlopdiagramon a statisztika eredményeit. A diagramok általános használata: Jelöljük ki az ábrázolni kívánt adatoszlopokat/sorokat jelen esetben az A1:B3 blokkot. Ezután válasszuk a Beszúrás menüszalag Diagramok csoportjának Oszlop menüjéből a megfelelő elemet. A kész diagram kiválasztás után mozgatható és átméretezhető.
Formázzuk meg a pénzadatokat tartalmazó oszlopokat úgy, hogy a számértékek Ft formátumban jelenjenek meg (pl. 32 456 Ft), és ne legyenek tizedesek, a százalék adatok pedig %os alakban látszódjanak (Kezdőlap menüszalag Szám csoport). Állítsuk be megfelelően az oszlopok szélességét.
Végezetül mentsük el a munkafüzetet a táblázatkezelő saját formátumában fizetések néven. Gratulálunk! Ezzel elérkeztünk a példa végéhez.
© Boros Norbert, Kallós Gábor (Széchenyi István Egyetem), 2012. Minden jog fenntartva
