Ha kérdésed van, ide írhatsz. Visszatérés a nyitólapra.

A gyakorlat témája: Táblázatkezelési alapismeretek – helyiségkimutatás.

A gyakorlat folyamán megismerkedünk a Microsoft Office irodai programcsomag Excel 97 táblázatkezelő alkalmazásával. A mérnöki munka során gyakran van szükség táblázatosan megjeleníteni adatokat, azokkal műveleteket végezni, ezek eredményeit grafikusan bemutatni, esetenként bizonyos problémákra optimális megoldást keresni. Ezekre, és más feladatokra alkalmasa eszköz egy táblázatkezelő rendszer.

Készítsünk egy egyszerű helyiségkönyvet egy nyaraló helyiségeiről! A táblázatos kimutatás tartalmazza a helyiségek neveit, geometriai adatait, nyílásainak összméretét, alapterületét, megvilágítását, és vakolt felületeinek területét. Az alábbi alaprajzról leolvashatók a szükséges méretek.

Az Excel természetesen nem az egyetlen (bár a legelterjedtebb) táblázatkezelő. Hasonló szolgáltatásokat kínál pl. a StarOffice integrált irodai programcsomag táblázatkezelő része, a StarCalc. Ez több platformon, (Windows mellett pl. Linux-on) működik, és teljesen ingyenes – tehát (legálisan) használható munkára is. (Oktatási célokra az Excel is ingyenesen használható.) A feladat (kisebb különbségekkel) mindkét programban szinte azonos módon végezhető el.

Kezdjünk egy új táblázatot! Excel-ben a File • New (Fájl • Új dokumentum) parancs menüjében választhatjuk ki, milyen sablon-dokumentumot akarunk az új táblázathoz használni. StarCalc-ban a File • New > Spreadsheet parancs nyit új, üres számolótáblát, míg a sablonok a File • New > From Template parancs paneljén keresztül érhetők el.

A dokumentumablak először egy semmitmondó nevet kap (DocumentX, ill. Untitled), ezt File • Save As… parancs segítségével módosíthatjuk, és ugyanitt menthetjük el a fájt a kívánt helyre.

Az program-, és a dokumentum-ablakban megtalálhatóak a Windows világában szokásos ablakelemek, (címsor, ablakszegély, menüsor, min-max és ikonkapcsolók, görgetősávok, üzenetsor), és a táblázat kezelését segítő speciális eszközök (pl. az oszlop-, és sor-fejlécek, szerkesztőléc…).

A táblázat munkaterülete kisebb részekre, ún. cellákra oszlik. Egy-egy cella tartalma minősülhet számnak, vagy szövegnek. Erre nézve nem perdöntő a látvány: szóközzel kezdve számjegyekből álló szöveget is írhatunk, és fordítva: számot is megjeleníthetünk szövegként (l. később). Gyakori probléma a tizedes-jel hibás alkalmazása, ha ugyanis az adott gépen a tizedesjel pl. ".", akkor az 1,2 két, vesszővel elválasztott számnak, végső soron pedig szövegnek minősül. Érdemes ezért megjegyezni, hogy a formázás nélküli cellákban a szöveg balra, a szám jobbra igazítva jelenik meg.

A cella (része) lehet szöveg (pl.:"Összesen:"), konstans (pl.:"123"), képlet (pl.:"=5*953", ill. "=4*A1"), és függvény (pl.:"=MAX(5,9,4/9)", ill. "=SUM(D3:E4)"). Mint látható, ez utóbbiakban a változó (argumentum) lehet valamely cellára, (vagy tartományra) vonatkozó hivatkozás is (l. később).

 •> Hivatkozások

Egy cellára való hivatkozás lehet pl.: A1, $A1, A$1, vagy $A$1 alakú, mind a négyféle jelölés azt jelenti, hogy az A jelű oszlop 1. sorában lévő cella értékére hivatkozunk (azzal akarunk számolni). Ha egy cellába az "=4*A1" képletet írjuk, ott az A1 cella mindenkori értékének 4-szerese jelenik meg (feltéve persze, hogy a A1 cellában számértékként értelmezhető beírás van.)

A táblázatkezelőben a fenti példában a A1 alakú hivatkozás relatív hivatkozást jelent, mert a A1 cellának az épp aktuális cellához viszonyított sor-és oszloptávolságát jelzi. Ennek az a jelentősége, hogy ha egy oszlopban pl. mindig az előző két oszlop szorzata kell szerepeljen, elég az első cella képletét beírni (pl.C1-be beírva: "=A1*B1"), majd a cellát lemásolni a többi sorba, hiszen mindegyik cella a tőle ugyanolyan sor- és oszloptávolságra lévő cellákra fog hivatkozni (l. később: terület számítása).

Nem mindig felel azonban meg céljainknak a relatív hivatkozás. Ha egy cella lemásolása után azt szeretnénk, hogy a másolás a hivatkozott cella során vagy oszlopán, esetleg egyiken se változtasson, akkor a rögzíteni kívánt sorszám, és/vagy oszlopnév elé “$” jelet kell tenni. Az ily módon jelölt hivatkozások abszolút hivatkozások (pl. $A$1), vagy vegyes hivatkozások (pl. A$1, $A1), lesznek.

Bizonyos műveletek igénylik, hogy ne csak egy cellára, hanem összefüggő tartományra hivatkozzunk (pl. ha egy számoszlop összegére, vagy átlagára vagyunk kíváncsiak). Ezt a tartomány bal felső és jobb alsó cellájának megadásával tehetjük meg, közéjük “:” jelet téve (pl. E3:G7). A tartomány határainál is használható bármely hivatkozás-típus.

 •> Cellák elnevezése

Excel-ben a celláknak, tartományoknak nevet is adhatunk (ill. később törölhetjük, átnevezhetjük, vagy más cellákhoz rendelhetjük a megadott nevet) az Insert • Name > Define (Beszúrás • Név > Név megadása) parancs panelén.

E névadást egyszerűbben is megtehetjük, ha a szerkesztősor előtti, aktív cella címét jelző mezőre kattintunk, és oda beírjuk az aktív cellának (vagy a kijelölt tartománynak) szánt nevet. A képletekben ezután használhatjuk az általunk adott neveket is, és ezen hivatkozások talán jobban hasonlítanak a megszokott változónév használathoz.

Ezek a nevek természetesen mindig a hozzájuk rendelt cellákra (tartományokra) vonatkoznak. Ha tehát a fönti példában az A1 cellát pl. ÁFA-nak nevezzük, az "=ÁFA" képlet(rész) abszolút hivatkozásként működik. De ha pl. egy oszlopot (pl. B:B), vagy függőleges tartományt (pl. B1:B99) nevezünk el Netto-nak, és az e tartományba eső cellákra hivatkozunk a C oszlop mellettük lévő celláiban ("=Netto*ÁFA"), a képlet az adott oszlopnak a képlet sorába eső cellájára fog hivatkozni – a $A1 típusú vegyes hivatkozásnak megfelelően. Fontos kivétel ezalól a (később ismertetésre kerülő) tartományra vonatkozó függvények (pl. összeg, átlag) esete: ezek (pl.:"=SUM(Netto)") ilyenkor a teljes tartományra vonatkoznak!

Az A1 cellába írjuk be: "A hallgató neve:", mellé a B1 cellába pedig a nevünket. Alá az A2 cellába azt hogy "Dátum:" utána a B2 cellát egyenlőre hagyjuk üresen. Itt is használhatjuk a szövegszerkesztőnél megismert karakterformázási lehetőségeket, alakítsuk pl. félkövér betűkké az eddig leírt cellák tartalmát: jelöljük ki a módosítani kívánt cellákat (Ctrl+egérkattintással több, nem összefüggő cellát is ki lehet jelölni), majd kattintsunk a B (bold – félkövér) ikonra. Azt tapasztaljuk, hogy a "hallgató neve:" szöveg nem fér el a cellában, a végét letakarja a B1 cellába írt név. Ezt az A és B oszlopnév határoló vonalára vitt egérrel, majd az mellékelt ábra szerint megváltozott egérkurzor megjelenésekor módosíthatjuk: lenyomott bal egérgombbal megnövelhetjük az A oszlop szélességét a kívánt méretre.

Ezután írjuk be az A3 cellától kezdődően az I4 celláig a táblázatunk fejlécét az ábra szerint (ezeket is félkövér betűtípussal), majd az A5 cellától lefelé az A10 celláig írjuk be az alaprajz szerinti helyiségek neveit, és X ill. Y irányú méreteit:

Az utolsó adatcsoport, amit még be kell vinnünk, a nyílászárók területe. Ugyancsak az alaprajzi ábráról olvashatjuk le és írhatjuk be helyiségenként külön a belső és külön a külső nyílásterületeket a táblázatunk D5:E10 celláiba. A nappaliban pl. 2 db. 90x2,40 méretű belső nyílás, 1 db. 75x2,10-es, és 2 db. 90x2,10-es belső ajtó, míg 2 db. 1.10x2.30 méretű ablak és 1 db. 90x2,30 méretű külső ajtó van. Így a D5 cellába a "=2*0.9*2.4+0.75*2.1+2*0.9*2.1" képletet, az E5 cellába pedig a "=2*1.1*2.3+0.9*2.3" képletet kell írjuk. A képletek bevitele (Enter) után a cellákban már a kiszámolt érték jelenik meg. Hasonlóan adjuk meg a többi helyiség nyílásainak a méretét is.

Miután az alaprajzi adatokat bevittük, a táblázat többi adatát már a meglévőek alapján számítani tudjuk. A nappali területe, amely az F5 cellába kerül, az X és Y méretek szorzata lesz, amely értékek a B5 és a C5 cellákban találhatók. Tehát az F5 cellába a “=B5*C5” képletet írjuk be. Ennek bevitele után a cellában megjelenő érték a kiszámított eredmény lesz, vagyis 16,7796. Amennyiben az F5 cellát lemásoljuk az F6, F7, ... F10 cellákba, akkor a korábban ismertetett relatív hivatkozás miatt azokba rendre “=B6*C6”, “=B7*C7”, ... “=B10*C10” kerül, vagyis a megjelenő eredmények helyesen a többi helyiség alapterületét adják meg.

Az F5 cellára állva, annak lemásolását többféle módon is megtehetjük. A többi programból már ismerjük az Edit / Copy - Edit / Paste menüpárt, mely a vágólap (Clipboard) igénybevételével végzi el a másolást. A Paste végrehajtása előtt jelöljük ki az F6:F10 tartományot, hogy egy műveletben másolja be a képleteket a kívánt cellákba.

Ugyanezt valamivel gyorsabban is elvégezhetjük az Edit legördülő menü kihagyásával, ha az F5 cellán állva a jobb oldali egérgombbal kattintunk, aminek következtében megjelenik egy ún. pop-up menü a leggyakrabban használt menük listájával. Ebben megtalálható mind a Copy, mind a Paste.

Van egy még hatékonyabb módja a másolásnak, az ún. auto-fill funkció, amelyhez a másolni kívánt cella jobb alsó sarkára kell az egérkurzorral állni, és a fekete kereszt alakúvá váló kurzorral a bal oldali egérgombot lenyomva tartva a kívánt celláig húzni azt. A húzás irányába kijelölődnek a szomszédos cellák, amelyekbe az egérgomb felemelése után bekerül a kiinduló cella tartalma.

Az előbbiek alapján a G5 cellába a nappali helyiség kerületét kiszámító “=2*(B5+C5)” képlet, a H5 cellába pedig a bevilágítási arányt megadó “=E5/F5”, (külső nyílások területe/alapterület) képlete kerül. A vakolt felületek kiszámításához szükséges a helyiségek magassága is. Ehhez nem adtunk a helyiségekhez tartozó “belmagasság” oszlopot, mivel ennél a feladatnál azonosan 2.50 m belmagasságúak a helyiségek. Írjuk ezt az értéket be az I3 cellába. Miután ez az érték így nem szerepel minden helyiség sorában, ezért ügyelni kell arra, hogy abszolút hivatkozással hivatkozzunk rá, ezáltal másoláskor ne változzon az I3 cella sora, ill. oszlopa.

Hasonlóan az előzőekhez beírhatnánk az I5 cellába a nappali helyiség vakolt felületeinek területét a “=F5+G5*$I$3-D5-E5” képlettel (alapterület+kerület  belmagasság-belső nyílások-külső nyílások), de itt mutatjuk be a már fentebb ismertett nevek használatát.

Az I3 cellának “belmagasság” nevet adva az előbbi képletben a “$I$3” helyett használható ez a név, mint változó.

Azonban bizonyos feltételek mellett nem csak abszolút hivatkozású celláknak adható név. Ha egy teljes oszlopot/sort kijelölünk (ezt a megfelelő oszlop nevére vagy sor számára kattintva tehetjük meg) annak is adhatunk nevet. Ekkor, ha egyértelműen az adott oszlop/sor csak egy cellájára hivatkozunk (vagyis nem ún, blokkműveletet kívánunk végrehajtani, mint pl összegzés, átlagszámítás, stb. - ld. később), akkor az adott cellára is hivatkozhatunk a cella oszlopának/ sorának nevével. Adjunk a B, C, D, E, F és G oszlopoknak rendre “x”, “y”, “belső_nyílások”, “külső_nyílások”, “alapterület” és “kerület” neveket.

Ekkor az I5 cellába a fent írt “=F5+G5*$I$3-D5-E5” képlet helyett írhatjuk azt is, hogy “=alapterület+kerület*belmagasság-belső_nyílások-külső_nyílások”, ami ugyan hosszabb, mint ha cella hivatkozásokkal tennénk ugyanezt, viszont jóval érthetőbb, olvashatóbb a képlet tartalma, ami fontos szempont, ha mások, vagy később akár mi magunk megnézni, módosítani szeretnénk táblázatunkat. Hasonlóképpen átírhatjuk a már elkészített terület, kerület és bevilágítás oszlopok 5 sorában a cellahivatkozásokat a megadott változónevekre. Ezután az F5:I5 cellablokot kijelölve, és a jobb alsó sarkánál fogva lehúzva a 10 sorig (auto-fill), lemásolhatjuk a tartalmukat, megkapva az összes helyiségre a kívánt adatokat.

A képletekben, kifejezésekben használhatunk függvényeket is a konstansok és változók mellett. Az Excel táblázatkezelő rendszert az igen nagy számú beépített függvény teszi alkalmassá, hogy az élet különböző területein jól használható legyen. A gyakran használt matematikai függvények mellett számos gazdasági, statisztikai, logikai, adatbáziskezelő, dátum- és szövegműveleteket végző függvénnyel dolgozhatunk. A függvényeket az Insert / fx Funktion... menüre, (vagy az eszközsorban található  fx  ikonra) kattintva, majd a megjelenő Paste Funktion nevű párbeszédablakból választhatjuk ki, ahol ABC sorrendbe akár az öszes (All), akár alkalmazási területenként külön kategóriákba csoportosítva találhatók. Amennyiben ismerjük a használni kívánt függvény szintaxisát (pontos nevét, az utána zárójelek közé kerülő paraméterei számát, sorrendjét és típusát), akkor közvetlenül a képletbe is beírhatjuk. Például a B2 cellába a mindenkori dátum kell hogy szerepeljen. A Paste Funktion párbeszédablakból a Date&Time (dátum és idő) kategória Now (most) függvényét kiválasztva, majd a paramáterekre vonatkozó ablak (ennek a függvénynek nincs paramétere) jóváhagyása után a parancsbeviteli sorba beírja szintaktikailag helyesen a függvényt: “=NOW()”, és az aktív cellában (B2) megjelenik a függvény értéke, vagyis az aktuális időpont. Ezt a kívánt formára a Format Cells párbeszédablakban állíthatjuk be (a párbeszédablak vagy a menüsor Format / Cells..., vagy a jobb egérgomb Format Cells... kiválasztásával jeleníthető meg). A cellákban az adatok megjelenését vagy a meglévő sablonformátumok alkalmazásával (jelen esetben a Format Cells párbeszédablak, Number fül, Date kategória Type mezőjében szereplő listából választhatunk), vagy egyedi beállítással alakíthatjuk ki (jelen esetben a Format Cells párbeszédablak, Number fül, Custom kategória Type mező listájából választhatunk, vagy bővíthetjük ezt a listát a beviteli sorban újabb formátumot megadva). A mellékelt ábra a magyar Windows nyelvi környezetben megadható formát mutatja: az “éééé.hh.nn.” azt jelenti, hogy a dátumban az évszám 4 számjeggyel, a hónap és a nap kettővel jelenjen meg és közöttük legyen pont (angol Windows nyelvi beállítások esetén ugyanez “yyyy.mm. dd.” formátum megadással érhető el).

Természetesen nem csak dátumok, hanem számértékeket tartalmazó cellák megjelenési formátuma is hasonlóan állítható be. Jelöljük ki a helyiségek X, Y méreteit és a kerületét tartalmazó cellákat (a  Ctrl   billentyűt lenyomva tartva több nem összefüggő tartomány is kijelölhető), ezután a Format Cells párbeszédablak, Number fül, Custom kategória Type mezőjébe íjuk be: “0.00\ \m”, majd ugyanígy a belső nyílások, külső nyílások, a terület és a vakolat celláihoz a “0.00\ \m2” formátumot rendeljük hozzá. Itt a 0.00 a kiírandó tizedesjegyek számát jelenti, az \ \m ill. a \ \m2 helyközzel a szám után irandó szöveget jelenti. A bevilágítási értékekre százalékos formában van szükségünk, ezért azokat is állísuk be egy tizedes pontossággal: Jelöljük ki a cellákat és a Format Cells párbeszédablak, Number fül, Percentage kategórián belül állítsuk be a kívánt tizedesértéket.

Az A11 cellába írjuk be: Összesen:, a G11 cellába, hogy Átlag:, majd a D11, E11, F11 és I11 cellákba számíttassuk ki a SUM (összegző) függvény segítségével a felettük lévő tartományok összegét, a H11 cellában pedig az AVERAGE (álag) függvénnyel az átlagos bevilágítást. Az átlag függvény a korábban leírt módon használható, az összegző függvény azonban külön   S   ikonban is megtalálható az eszköz-sorban, mivel arra gyakan van szükség táblázatok készítése során.

Hogy a logikai függvények használata is szerepeljen a gyakorlatban, írjuk be a H1 cellába, hogy határidő:, és az I1 cellába pedig a gyakorlat beadási határidejét 99/12/20 alakban. Nevezzük el az I1 cellát “határidő”-nek, és a a B2 cellát, amelybe korábban a NOW függvénnyel az aktuális dátumot írtuk be, “mai_nap”-nak. A C2 cellában - attól függően, hogy a “mai_nap” nevű cella tartalma nagyobb-e vagy sem a “határidő” nevű cella tartalmánál - jelenjen meg vagy az hogy “máris késő!”, vagy az hogy “jó lesz sietni!”. Feltételtől függő cella tartalom megjelenítésére az IF logikai függvény ad lehetőséget. Szintaktikája: IF(fetétel,képlet igaz feltétel esetén,képlet hamis feltétel esetén). A feltétel egy logikai állítás kell legyen, pl. $B$2>$I$1. Feladatunk esetén a C2 cellába a következő képlet kell beírjuk: “=IF(mai_nap>határidő,"máris késô!","jó lesz sietni!")”. Miután az aktuális dátum még nem nagyobb a “határidő” nevű cellába beírt dátumnál, ezért a "jó lesz sietni!". szöveg jelenik meg. Próbáljunk meg a “határidő” nevű cellába az aktuális dátumnál korábbi időpontot beírni, pl. 99/10/10-et, ekkor a "máris késô!" szöveg fog megjelenni a C2 cellában.

Amikor nagyjából elkészültünk egy táblázattal, azt izlésünk szerint csinosíthatjuk, jobban használhatóbbá tehetjük néhány további formázási utasítással. Pl. jobban is néz ki, és jobban is olvasható, ha a B3, D3 és F3 cellákba írt szövegek a velük szomszédos cellákkal összevont terület közepére kerülnek. Ezt a Format Cells párbeszédablak, Aligment fül, Horizontal mezőjében a Center Across Selection kiválasztásával érhetjük el. További formázási lehetőség a táblázat celláinak bekeretezése. A Format Cells párbeszédablak, Border fül kiválasztása után számos vonaltípus, vonalvastagság közül választva megadhatjuk, hogy a kijelölt tartomány határoló és közbenső cellát hogyan kívánjuk keretezni. Ugyanezen párbeszédablak Patterns fülére kattintva pedig a kijelölt cellák mintázatát, színét adhatjuk meg úgy, ahogy azt a hangsúlyozni kívánt részek vagy izlésünk igényli.

A cellákba írt szövegek, adatok színét is megváltoztathatjuk a Format Cells párbeszédablak Font fül, Color mezője, vagy az eszközsor Font Color ikonja segítségével.

A helyiségkimutatás gyakorlat egy lehetséges végeredményét mutatja a fönti ábra.