E42-101 – Segédletek III.
Excel alapok
Excel alapok Áttekintés
A Microsoft Excel egy táblázatkezelő program, amelyet adatbevitelre, számításokra, elemzésekre, adatbázis-kezelésre (korlátozottan!) és adatok grafikus megjelenítésére terveztek és hoztak létre. A Worddel együtt az Office programcsomag része. Az excel-állományok is hierarchikusan szervezettek: munkafüzet, munkalap, cella. Munkafüzet (workbook)
Az excel-munkafüzet a Windows felől nézve egy fájl, logikailag egyenlő a worddokumentummal. Az adatok, a számítási képletek, eljárások és az eredmények tárolódnak ebben a fájlban. Az állományok kezelésére ugyanazok a műveletek állnak rendelkezésre, mint a Word esetében. Munkalap (sheet)
A munkafüzet lapokra oszlik. A lapok között megkülönböztethetünk munkalapokat és ábralapokat. A munkalapokra visszük be az adatokat és a kiértékelő algoritmusokat, itt találhatjuk meg a numerikus végeredményeket is. Az ábrák (diagramok, grafikonok) a munkalapok adatait fölhasználva grafikusan mutatják be az eredményeket. Minden munkalapot névvel azonosítunk, amely megnevezés a tábla alján egy kis fülön olvasható. Ez a név bármikor megváltoztatható a név fölötti jobb egérgomb kattintásával feljövő menüből. Újabb munkalapokat is hasonló módon illeszthetünk be a munkafüzetünkbe. A munkalapok közül a fülre való kattintással választhatjuk ki a használni kívántat. Cella
A munkalap oszlopokból és sorokból áll. Az oszlopok és sorok metszéspontjában a táblázat atomi egysége a cella (adatmező, box stb.) található. Ezekbe a mezőkbe írhatók be az eredeti adatok és azok a számítást végző kifejezések, amelyek a végeredményeket szolgáltatják. Mozgás a munkalapon
Az Excel egy szabványos windows-ablakban fut. Kezelőfelülete szinte megegyezik Wordével, csak az adatterület különbözik, mert itt egy besraffozott üres „lepedőt” kapunk az üres (szabványos méretű) papírlap(ok) helyett. Cella azonosítása
Mivel szükséges tudnunk megnevezni azt a részt, ahol valamilyen adatot eltároltunk, ezért a mezőket azonosítani kell tudnunk. Egy cellára való hivatkozáshoz az Excelben megadjuk azt, hogy melyik oszlop és melyik sor metszéspontjában helyezkedik el. Az Excel alaphelyzetben az oszlopokat az angol ABC betűivel (balról jobbra haladva), a sorokat a természetes számokkal (felülről lefele haladva) jelöljük. Ebben az esetben negyedik oszlop nyolcadik sorát a D8 karaktersorozattal azonosítjuk. A másik lehetséges beállítás esetén a sorokat és oszlopokat is számozzuk. Ezt a módot az Eszközök Beállítások… menüpontokon keresztül felnyíló űrlap Általános fülén az S1O1 hivatkozási stílus jelölőmezőbe elhelyezett jellel. Ebben az esetben negyedik oszlop nyolcadik sorát a S8O4 karaktersorozattal azonosítjuk. Bármelyik cellának adhatunk tetszőleges nevet is – és a későbbiekben ezzel hivatkozhatunk rá. Egy cella névadásakor első lépésként kiválasztjuk az adatcellát, és a Névmezőbe kattintva beírjuk a nevet.
E42-101 – Segédletek III.
Excel alapok
Főmenü Gyorssúgó
Eszköztárak
Aktív cella
Szerkesztőléc
Név mező Munkalap Üres táblázat
Munkalapok megnevezése
1. ábra: Excel képernyő Táblázati tartomány azonosítása
Vannak olyan műveletek, amikor nem csak egyetlen cellában lévő adatra kell hivatkoznunk, hanem több cellában lévőre. Ha ezek a mezők nem egymás mellett helyezkednek el, akkor a cellák azonosítóit pontosvesszővel elválasztva soroljuk fel. (Angol nyelvű Windows és Excel esetén a listaelválasztó elem a vessző.) Az Excel megengedi derékszögű négyszög alakú táblázati részre való egyszerűbb hivatkozást. Ilyen esetben a bal felső és jobb alsó cella azonosítóját kell megadni köztük egy kettősponttal vagy két ponttal történő szeparátorral. Tehát például a C4 és D8 cellákkal határolt téglalap alakú területen lévő tíz cellát nem csak a C4;C5;C6;C7;C8;D4;D5;D6;D7;D8 listával, hanem a C4:D8 vagy C4..D8 jelsorozattal is azonosíthatjuk. Természetesen itt is használható a logikai név is. A cella-tartomány kijelölése után a Névmezőbe kattintva beírjuk a nevet, ami a későbbiekben már használható. Aktuális cella kiválasztása
A mezőt, amelybe szeretnénk valamilyen karaktersort bevinni a legegyszerűbb módon úgy választhatjuk ki, hogy rákattintunk az egérkurzorral. Természetesen használhatjuk a kurzormozgatókat is: – esete válogatja, hogy mikor melyik a gyorsabb.
DBCE
2
E42-101 – Segédletek III.
Excel alapok
Mozgás a táblázatban
Ha a táblázatunk olyan nagy, hogy a képernyőn már nem fér el, akkor nagy hasznát vehetjük a következő billentyűkombinációk: • mozgás a táblázati sor első, értéket tartalmazó oszlopába; • mozgás a táblázati sor utolsó, értéket tartalmazó oszlopába; • mozgás a táblázat elejére (bal felső, értéket tartalmazó cella); • mozgás a táblázat végére (jobb alsó, értéket tartalmazó cella); • mozgás az adattömb első oszlopába, üres sor esetén az űrlap legelső (A) oszlopába; • mozgás az adattömb utolsó oszlopába, üres sor esetén az űrlap az űrlap legutolsó (IV) oszlopába; • mozgás az adattömb első sorába, üres oszlop esetén az űrlap az űrlap legelső (1) sorába; • mozgás az adattömb utolsó sorába, üres oszlop esetén az űrlap az űrlap legutolsó (65536) sorába. Ha a kurzormozgató billentyűket a shifttel együtt használjuk, akkor a kurzor nem csak elmozdul a kívánt helyre, hanem a startpozíciótól kezdve a cellák ki is jelölődnek. Teljes oszlop vagy sor kijelöléséhez elegendő az oszlop vagy sor fejlécére kattintani. Ugyancsak a mozgást segíti a Szerkesztés Ugrás… menüpont kiválasztása, illetve az vagy billentyűk lenyomása után megjelenő ablak. A Hivatkozás mezőben meg kell adni a cella azonosítóját vagy nevét. A név kiválasztható a Hova listából is. Ha egy névvel ellátott mezőt szeretnénk aktívvá tenni, akkor a leggyorsabb elérési mód az, hogy a Névmező melletti jelre kattintva a legördülő listából kiválaszthatjuk a kívánt helyet.
R V GR GR GE GD GC GB
Gg
Munka a cellákban Adatbevitel
A kívánt cellát kiválasztva elkezdhetjük az adat begépelését. Az adatbevitel végét vagy oly módon jelezzük, hogy egy másik cellát teszünk aktuálissá, vagy lenyomjuk az billentyűt. Ez utóbbi esetben – a beállítástól függően – az aktuális alatti vagy mellette jobbra lévő cellába kerül a vezérlés. Az Excel automatikusan felismeri az adat formátumát: szám (numerikus adat), szöveg (címke, karaktersorozat), dátum. Ha mégis rosszul dönt, akkor ezt a Formátum Cellák… után felnyíló űrlap Szám fülén a Kategóriában helyesbíthetünk. Egyedül a képlet bevitelének kezdetét kell jeleznünk, mégpedig a lenyomásával.
L
=
Adatsorozat bevitele
Gyakran előfordul, hogy valamilyen sorozatot kell elhelyeznünk a táblázatunkban. Ehhez az Excel rendelkezik megfelelő eljárásokkal, nekünk csupán a sorozat első elemét kell elhelyezni egy mezőben. Utána ezt a mezőt aktuálissá téve válasszuk ki Szerkesztés Kitöltés Sorozatok… menüpontot, és a felnyíló ablakban ki kell választani adatsorozat elhelyezkedését, típusát, és meg kell adni a sorozat lépésközét és végértékét. Számtani sorozatot gyorsabban is megadhatunk, ha pár értéket beírva, majd azokat kijelölve tovább húzzuk a kívánt celláig.
Adatmódosítás
Az adat bevitele közben a Szerkesztőlécben is látható az aktuális mező tartalma. Ha már egy adatot tartalmazó cellára kattintunk, és elkezdünk gépelni, akkor a régi adat elveszik, felülírja az új. Ha nem szeretnénk a karaktersorozatot elveszíteni, csak javítanunk kell benne, akkor
3
E42-101 – Segédletek III.
Excel alapok
a mezőre kattintva, és az -t lenyomva vagy a Szerkesztőlécbe kattintva kerülünk szerkesztő üzemmódba. Ebben az esetben a nem a mezők között mozgatja a cellakurzort, hanem cellában mozgatja a karakterkurzort. A cella tartalmát a leütésével lehet törölni. Ha rossz cellában kezdtük meg az adatbevitelt vagy rossz cella módosítását kezdeményeztük, akkor a tevékenységet az lenyomásával következmények nélkül visszaléphetünk.
Adatfeldolgozás
Az Excel csak egy táblázógép lenne, ha nem volna képes származtatott adatok előállítására. Az Excel mezőibe azonban nem csak elsődleges adatokat vihetünk be, hanem kifejezéseket is beírhatunk. Ezzel a cella értékét más mezőktől és/vagy eseményektől tehetjük függővé. Fontos fogalom: a cella értékét és nem tartalmát! A cella tartalma a kifejezés, ami előállítja a cella értékét. A cella tartalmi a szerkesztőlécben látszik, a cella értéke pedig a táblázatban. A kifejezéseket a lenyomásával kezdjük. Ez jelzi az Excel számára, hogy nem egy normál szöveg helyezkedik el a cellában, amit csak meg kell jeleníteni, hanem egy utasítás, amit végre kell hajtani. Egy kifejezés tartalmazhat konstans értéket, műveleti jeleket, függvényeket és utalásokat más cellákra. A numerikus operátorok: + (összeadás), - (kivonás), * (szorzás), / (osztás) és ^ (hatványozás). Ha a cella kifejezést tartalmaz, akkor a táblázati lepedőn lévő cella és a Szerkesztőléc tartalma eltér. A Szerkesztőlécben mező tartalma (a képlet), a táblázati mezőben a kifejezéssel kiszámolt érték látható. Az Excel több mint háromszáz beépített függvényt tartalmaz. Ezek a függvények begépelhetők a táblázati mezőbe, de a függvényvarázsló segítségével is beszúrhatók (Beszúrás Függvény…). Minden függvényt egy névvel azonosítunk, és a számítások elvégzéséhez meg kell adnunk az argumentumait. Ezeket az értékeket a név mögött zárójelek között kell elhelyezni. Ha a paraméterek száma több mint egy, akkor az elemek listaként helyezendők el, vagyis pontosvesszővel elválasztandók. Vannak olyan függvények is, amelyek nulla paraméteresek. Ebben az esetben is elhelyezendők a zárójelek, de köztük nem lenhet semmi. Például a =MAX(C4:D8) vagy a =MAX(adatok) illetve a =MAX(C4;C5;C6;C7;C8;D4;D5;D6;D7;D8) megkeresi a megadott vagy megnevezett cellahalmazban található legnagyobb értéket; a =PI() előállítja a π értékét. Függvény paramétereként függvény-hivatkozás is megadható.
Nevezéktan
A kifejezések és függvények fölhasználják más cellák értékeit a végeredmény kiszámításához. Ezek a cellahivatkozások (referenciák) fölfoghatók mutatókként, amelyek azonosítják az Excel számára a helyet, ahol a kívánt érték megtalálható. Ezek a referenciák lehetnek relatívak vagy abszolútak.
2. ábra: Cellahivatkozások egy kifejezésben
Az 2. ábra bal oldalán egy relatív, jobb oldalán egy abszolút cellahivatkozás látható. Mindkét esetben a B5-ös mezőre hivatkozunk, a második esetben azonban az oszlop- és sorjelzet elé egy $ jelet helyeztünk el. A $-t vagy manuálisan kell begépelnünk, vagy a hivatkozásba moz4
E42-101 – Segédletek III.
Excel alapok
gatva a kurzort az -t nyomogatva állíthatjuk be a megfelelő típusú hivatkozást beállítani. Az -t lenyomva nem találunk különbséget a két megoldás között, mindkét képlet a -17,7778-at adja végeredményül. Eltérést akkor tapasztalunk, ha a táblázat első helyére beírt képletet arrébb másoljuk. Célszerű másolni, hiszen a ugyanazt a képletet kell beírnunk – a két skála közötti konverziós képlet független a hőmérséklettől. Ha újra (és újra) beírnánk a kifejezést, növekedne a tévesztés bekövetkezésének valószínűsége.
3. ábra: Cellahivatkozások másolt kifejezésben
A 3. ábra az átmásolt képleteket mutatja. Látható, hogy a relatív hivatkozás esetében a képlet módosult, mégpedig annyival megnövelve a sorhivatkozás értékét, ahány sorral a képlet az eredeti helyzetéhez viszonyítva lejjebb került. Így minden Farenheit értékhez helyes Celsius került. Az abszolút hivatkozás esetében viszont a hivatkozás „sértetlen”, a lejjebb került kifejezés visszamutat az eredeti cellára. Emiatt abszolút – nem változtathatja meg az Excel a hivatkozás. A cellák közötti képletmásolás többféleképpen is megvalósítható. Használható a Wordnél megszokott – billentyűsorozat. Vagy a másolandó mezőn állva és kijelölve a kitöltendő mezőket a vagy lefele vagy jobbra másolhatunk. Ugyanez a funkció a menüből is elérhető: Szerkesztés Kitöltés Lefelé | Jobbra | Fel | Balra. De talán a legegyszerűbb mód, ha a képletet tartalmazó aktív cella körüli jelölő-négyzet jobb alsó sarkában található „szemölcsöt” megfogva húzzuk megfelelő irányba a képletet. Természetesen a relatív hivatkozás nem csak lefele, hanem felfele és az oszlopok között jobbra és balra mozogva is helyesen működik. Az abszolút hivatkozásnál sem kell mindig az oszlop- és sorhivatkozás előtt szerepelnie a $-nak, csak azon rész előtt, amelyet védeni szeretnénk a változás ellen. A 4. ábrán egy négyjegyű sinus-táblát készítettünk. A táblázat első oszlopa tartalmazza fokokat, az első sora a perceket. Egy fok és percérték metszéspontjában kiolvasható a sinus-érték. A számítást a SIN függvény végzi el. De mivel a szögfüggvények radiánban mért értékeket várnak, a táblázat megfelelő helyéről kiemelt, és kiszámított fokot a RADIÁN függvénynyel radiánba számoltatjuk át. Egy szögértéket fokrészét MINDIG az első oszlopból, a percrészét MINDIG az első sorból kell kiemelni. Ezt valósítjuk meg azzal, hogy a hivatkozásnál a fokrész esetében az oszlop- a percrész esetében a sorhivatkozást védjük a $-ral.
5
E42-101 – Segédletek III.
Excel alapok
4. ábra: Helyesen alkalmazott cellahivatkozás
Bonyolultabb – sok cellahivatkozást tartalmazó - képletek megértését nehezíti, hogy a mezőhivatkozások nem „beszédesek”, egy B17 pointer nem mond semmit arról, hogy a mezőben milyen értéket helyeztünk el. Sokkal beszédesebbek a kifejezéseink, ha megfelelően megválasztott mező- és tartománynevekkel megmutatjuk, hogy milyen jellegű adat található a hivatkozott helyen. Az 5. ábrán ugyanazt a példát oldottuk meg kétféle módon. A számításhoz használt képlet: a−
b d1,3
h=e , ahol h a magasság, d1,3 a mellmagassági átmérő, a és b a fafajra jellemző paraméterek.
5. ábra: Kifejezés cellahivatkozásokkal és nevekkel
A megfelelő neveket a résztáblázat „fejléceiből” lehet megszereztetni a programmal. A megfelelő részt kijelölve a Beszúrás Név Létrehozás… menüpontokon végighaladva kiválaszthatjuk azt, hogy a fejléc a kijelölt tartomány melyik részén helyezkedik el. Az így megadott név mindig abszolút hivatkozásként kerül be az Excel nyilvántartásába. Ha ez a számításinkhoz nem jó, akkor a Beszúrás Név Név megadása… menüpontokon végighaladva állítható be a kívánt érték. A 6. ábra mutatja az így meghívott segédablakot.
6
E42-101 – Segédletek III.
Excel alapok
6. ábra: Táblázati tartomány nevének és paramétereinek beállítása
A beállításnál nem szabad megfeledkezni arról, hogy az Excel esetében mindig először ki kell választani az objektumot és utána kell dolgozni. Esetünkben az a és b paraméterek abszolút hivatkozásúk kellenek, hogy legyenek, hiszen minden cellában ugyanazt a konstanst kell használnunk. Az átmérő esetében viszont relatív hivatkozást kell majd használnunk. Tehát ki kell választanunk a d1_3 nevet, majd a Hivatkozás mezőben be kell állítani a helyes értéket a $ jelek kitörlésével vagy az használatával.
7
