* Kérdés, észrevétel  y Vissza   ? 2002.10.09.

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.

 • Táblázatkezelő (Spreadsheet) programok  

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 dokumentum 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.

A 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. Ezek közül (pl. formázáskor) egyszerre több is kijelölhető, (a [Ctrl] lenyomása mellett) akár – mint a fönti képen – egymástól függetlenül is. Mindenképpen van azonban egy, ún. aktív cella – ennek címe (a fönti képnél A1) jelenik meg menüsor alatti név-mezőben. Ha pl. szöveget írunk, az mindig az aktív cellába kerül (bár [Ctrl]+[Enter] hatására az összes kijelölt cellába bevihető.).

Egy-egy cella tartalma vagy szám (ami jelenthet pl. időt, dátumot is), vagy szöveg; azaz minden, amit a program nem tud számként értelmezni, szövegnek minősül. A cella-tartalom lehet statikus, pl. egy konstans szám vagy szöveg, illetve képlet (mint négyzetgyök 2), és lehet dinamikus, ahol a cellatartalom pl. más cellák tartalmától függően változhat.

A cella megjelenése tartalmától (szinte) függetlenül állítható: mint később látni fogjuk, a szám-megjelenítési formátum olyan szabadságot ad, hogy akár azt is beállíthatjuk, hogy egy cella értkétől függően más-más szöveg jelenjen meg (pl. 1-nél "elégtelen", 5-nél "jeles"). A lényeg, hogy ettől még a cella tartalma szám marad, s így numerikus műveletekben használható marad! Ugyanilyen módon áll elő pl. az alábbi példákban a "1 000 Ft", vagy a 1,23m² formátum – a cella tartalma ott is 1000, ill. 1,23 kell maradjon (különben szám helyett szöveg lesz s mint tudjuk egy szöveggel nem lehet pl. szorozni).

 " Cellahivatkozások  

Egy táblázat tartalmának dinamikussá tétele általában igényli, hogy egy cella értéke más cella (ill. cellák) értékének függvényében változzon (pl. mindig a szomszéd cellában álló szám 25%-át jelenítse meg, mint ÁFA-t).

Egy másik cella tartalmára való hivatkozás lehet: A1, $A1, A$1, vagy $A$1 formátumú. Mind a négyféle jelölés azt jelenti, hogy az A nevű oszlop 1. sorában lévő cella értékére hivatkozunk (azzal akarunk számolni).

Ha pl. a B1 cellába a "=A1*0,25" képletet írjuk, ott a A1 cella épp aktuális értékének 25%-a jelenik meg (feltéve persze, hogy az A1 cellában számként értelmezhető beírás van.)

E példában az A1 hivatkozás relatív hivatkozást jelent, mert az A1 cellának az épp aktuális (B1) cellához viszonyított sor-és oszloptávolságát jelzi. Így válik lehetővé, hogy a B1 cella tartalmának más cellákba való lemásolása után azokban a cellákban ne A1 értéke szerepeljen, hanem minden cellában a tőle ugyanolyan sor- és oszloptávolságra (azaz mellette lévő) cella értéke.

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.

A vegyes hivatkozásra legegyszerűbb példa talán egy szorzótábla: mivel itt minden cellában az oszlop-, illetve a sor első számának szorzata kell szerepeljen, a szorzat egyik tagjánál a sor, a másiknál az oszlop jele elé kell $ jelet írnunk.

Abszolút hivatkozást célszerű használnunk, ha első példát továbbfejlesztve a számot, amellyel az A oszlopban szereplő minden értéket be kell szorozzuk majd, beírjuk pl. a C1 cellába, majd a B oszlop képleteiben a konkrét érték (25%) helyett mindenhol e cellára hivatkozunk – ekkor természetesen abszolút hivatkozással!

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. Erre majd a gyakorlat folyamán látunk példát.

 • Cellák elnevezése  

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 az aktív cella címét jelző név mezőre kattintunk, és oda beírjuk az aktív cellának (vagy kijelölt tartománynak) szánt nevet. A cellákba írt 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 pl. 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. E:E), vagy függőleges tartományt (pl. E1:E99) nevezünk el pl. Netto-nak, és az e tartományba eső cellákra hivatkozunk pl. az F 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. Nagyon 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!

 • Helyiségkönyv  

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 Word 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 beí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 alábbi ábra szerint, ezeket is félkövér betűtípussal emeljük ki, 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 – től E10 celláiba. A nappaliban pl. 2 db. 90x2,40 méretű belső nyílás, 1 db. 75x2,10 méretű, és 2 db. 90x2,10 méretű 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 írjuk, (mint az az alábbi ábra parancsbeviteli sorában is látható) az E5 cellába pedig a “=2*1.1*2.3+0.9*2.3” képletet. A képletek bevitele 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özbensQ 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.