Excel kezdőknek és haladóknak

Az Excel egy nagyon fontos részéhez értünk. Aki nem találkozott még vele, vagy nem gyakorlott a használatában, annak a megfelelő hivatkozások elsajátítása jelentős előrelépést fog eredményezni az Excel használatában. 

Adva van egy zöldséges üzlet, ahol a készlet értékét szeretnénk kiszámolni:

Valószínűleg mindenkinek egyértelmű, hogy az érték oszlopban összeszorozzuk a mennyiséget az egységárral. Így a D2-es cellába (alma sor-Érték oszlop) a következő képlet kerül: =B2*C2. Mivel az összes alatta lévő értéket is ugyanígy számoljuk ki, így egyszerűen átmásoljuk a D2-es cellát a D3, D4, D5 és D6-os cellákba. Ennek megfelelően pl. a D4-es cellába az alábbi képlet került: =B4*C4. Észrevehetjük, hogy az eredeti, másolt D2-es cellához képest 2 sort mentünk lefelé, és így a D4-es cellába jutottunk. Az eredeti képletünkben szereplő képletek is úgy változtak, hogy az oszlop hivatkozások (betűk) változatlanok maradtak, a sor hivatkozások (számok) pedig 2-vel nőttek. 

Az ilyen fajta cellahivatkozásokat nevezzük relatív hivatkozásnak. Ez azt jelenti, hogy ha képletet másolunk valamely cellából, akkor a beillesztett cellába kerülő képelt sor és oszlop hivatkozása annyival fog változni, amennyivel az eredeti cellához képest elmozdultunk mind vízszintesen mint függőlegesen. Hogy ezt jobban megértsük, jöjjön a következő példa:

Az előző táblázatot kiegészítjük 2 új oszloppal: egy új egységárral és egy új értékkel. A mennyiség változatlan maradt, viszont szeretnénk kiszámolni mindkét egységárral a készlet értékét. 

Hibás megoldás relatív hivatkozással

Az E2-es cellában az előzőnek megfelelően beírtuk az =B2*C2 képletet. Ha most ezt a képletet átmásoljuk mind a két oszlopba, a fenti táblázatban látható képleteket kapjuk. A fent leírt viselkedés alapján nem lepődünk meg azon, hogy az Új érték oszlopba rossz képlet került. Hiszen mi történt? Az eredeti cellában lévő képletet átmásoltuk egy a cellától jobbra lévő oszlopba. Így ahogyan azt vártuk, a képlet is úgy változott meg, hogy az oszlop hivatkozás is eggyel odébb tolódott - B-ből C lett, C-ből D lett. A relatív hivatkozások így működnek, a másolt képletben szereplő oszlop és sor hivatkozások annak megfelelően változnak a beillesztés után, amennyi sorral és oszloppal eltér a másolt és beillesztett sor egymástól.

Helyes megoldás relatív hivatkozással (nem másolással készült a képlet!!!)

Mi a megoldás, hogy másolás után is helyes képletet kapjuk? Használjunk abszolút hivatkozásokat. Ez azt jelenti, hogy a másolandó képletben azokat az oszlop és/vagy sor azonosítókat rögzítsük, amelyeket nem szeretnénk, ha a másolás során "elvándoroljanak". A fenti példában a mennyiséget szeretnénk változatlanul hagyni, amely értékek a B oszlopban vannak, így az eredeti képletben a mennyiségre vonatkozó hivatkozás (B2) oszlopát rögzítenünk kell. Ezt a rögzítést az oszlop vagy sor azonosító, vagy mindkettő elé írt $ jellel érhetjük el. Ha a képletben ráállunk az adott hivatkozásra (pl. B2) és megnyomjuk az F4 billentyűt, láthatjuk, hogy az Excel a hivatkozást az alábbiakra cseréli: $B$2, B$2,$B2,B2. Az F4 billenytyű használata csak egy könnyebbség, a $ jelet kézzel is kiírhatjuk az oszlop vagy sor azonosító, vagy mindkettő elé.

Hogyan jöjjünk rá, hogy milyen módon adjuk meg a hivatkozásunkat? Amíg nem vagyunk gyakorlottak a dologban, az alábbi módszert javaslom: számoljuk ki a képletet  először az E2 majd az F5 cellára. Amennyiben a feladat lehetővé teszi, a két kiszámolt cella lehetőség szerint ne legyen se egy sorban se egy oszlopban. Így az első cellára az =B2*C2, a másodikra a B5*D5 képletet kapjuk. Most megnézzük, hogy melyik sor és/vagy oszlop hivatkozás nem változott a két képletben: csak a B maradt változatlan, így ezt kell rögzítenünk. Ennek megfelelően az eredeti =B2*C2 képletet átírjuk =$B2*C2 formátumúvá, akkor másolás után az F5-ös cellába is a most már helyes =$B5*D5 képletet kapjuk. 

Leírva bonyolultnak tűnhet a dolog, a gyakorlatban viszont egy kis gyakorlás után már egyszerű: megnézzük, hogy átmásoláskor van-e olyan része a képletnek, amely esetében nem szeretnénk ha megváltozna az oszlop, a sor, vagy az egész cella, és a másolandó képletet ennek megfelelően módosítjuk.

Fontos: az abszolút hivatkozások használata ($ jel) semennyiben sem befolyásolja egy adott cellába írt képlet viselkedését, az kizárólag másoláskor mondja meg az Excelnek, hogy az adott sort, oszlopot esetleg mindkettőt a másolás során hagyja változatlanul, ne módosítsa a sor, oszlop vagy mindkettő változásának megfelelően.

Feladat

A feladat mindig ugyanaz lesz: megadom a másolandó cellában található képletet, és megadom, hogy melyik cellába kell átmásolni. Nektek azt a képletet kell megadnotok, amelyet az Excel a másolás után a célcellába beír. Próbáljátok meg lehetőleg Excel használat nélkül megválaszolni a kérdéseket, mert bár így lényegesen nehezebb, viszont sokkal jobban megértitek.

Segítség: a feladathoz úgy kezdjetek hozzá, hogy nézzétek meg, hogy hány sorral és oszloppal tér el a kezdő és cél cella egymástól, és a képletben szereplő esetleges abszolút hivatkozások segítségével határozzátok meg a célcellába kerülő képletet. A megoldás Excellel le tudjátok ellenőrizni. Csak írjátok be az adott képletet a kezdő cellába, majd másoljátok át a cél cellába, és nézzétek meg az eredményt. A képleteket külön munkalapra (fülecskék alul) másoljátok, különben körkörös hivatkozásokat kaptok!!!

Példa:

1. sor: Láthatjuk, hogy a kezdő cellához képest (C3) 2 oszloppal és 2 sorral mozdultunk el, hogy eljussunk a cél cellába (E5). Mivel a célcellában lévő képletben semmi sem volt rögzítve (nincs $ jel), ezért minden sor és oszlop azonosítót ennek megfelelően fog az Excel változtatni. Így lesz az A-ból C, a B-ből D, a 3-ból 5.

2. sor: +1-gyel és +3-mal mozdultunk el. A kezdő képletben az A rögzítve van, így ez marad, a többi az előbbinek megfelelően változik: B-ből C lesz, 2-ből 4 lesz.

3. sor: +1-gyel és +1-gyel mozdultunk el. A1 és a szorzás második tagjából az A rögzítve van, így azok változatlanok maradnak, a többi az előbbinek megfelelően változik: B-ből C lesz, 4-ből 5 lesz.

Megoldandó (megoldás lejjebb):

Ha a fenti táblázat kitöltése sikerült, akkor a mellékletben található feladatokat már könnyen meg tudjátok oldani. 

http://www.streamfile.com/myid/9IvBYtU6o4Ft 

A 3. file-ban a bemutatkozó bejegyzésben már megismert példa található. Ennek megoldásához a fentieken túlmenően még egy pici ötlet is kell. Valószínűleg ott más képlettel számítanátok az első és utolsó sorhoz tartozó értéket. Itt azt nézzétek meg, hogy az utolsó sorba írt képlettel (szum függvény) nem számolható-e az első sor is. Ha igen, akkor a fentebb ajánlott módszerrel tegyétek egymás mellé a két képletet, és már látszik is, hogy mit lehet rögzíteni. Sok sikert a gyakorláshoz. Kérést e-mailben vagy kommentben is feltehettek. A kommentet a többiek is látják, és talán ők is tanulhatnak belőle.

Megoldás:

Összefoglalva: 

A sorok/oszlopok rögzítése az eredeti képlet eredményét nem befolyásolja, csak másoláskor változik tőle a célcellába kerülő képlet.

Rögzített sor, oszlop vagy mindkettő  (pl. A$2 -> C$2, $A2 -> $A4, $A$2 -> $A$2) a másolás során nem módosul.

A kezdő és cél cella közötti vízszintes távolság a nem rögzített oszlop hivatkozást (betűk), a függőleges távolság a nem rögzített sor hivatkozást (szám) változtatja az elmozdulással megegyező mértékkel.

A mostani bejegyzéssel lezárjuk a számformátumokat. Láthattuk, hogy az Excel a cellákba bevitt adatban megpróbál valamilyen általa ismert számformátumot felfedezni (dátum, százalék, pénznem, ezres tagolás stb.), és ha ez sikerül, akkor a cella formátumát ennek megfelelően állítja be, valamint ennek megfelelően átalakítja a bevitt adatot. Dátum esetében kiszámolja az 1900.01.01-óta (egész pontosan 1900.01.00 !!!-óta, mivel az 1900.01.01-et 1-é konvertálja) eltelt napok számát, százalék esetében a bevitt értéket elosztja 100-zal (a cellába 4%-ot adunk meg, akkor az Excel 0,04-et tárol el), ezres elválasztó esetén beállítja formátumot és kitörli az ezres elválasztó karaktereket stb. A fentieket könnyen beláthatjuk, ha a cella számformátumát másikra változtatjuk.

Dátum formátum esetében a fenti elv miatt könnyedén kiszámolhatjuk 2 adott dátum között eltelt napok számát, mivel a 2 dátum egyszerű kivonásával máris megvan az eredmény. Ne lepődjünk meg azon, ha sok esetben az Excel az alábbihoz hasonló eredményt ad:

Ebben az esetben a végeredményt is dátum formátumban adta meg az Excel, ha visszaalakítjuk normál számformátummá, már látható is a jó eredmény:

A dátum formátumnál még nem ejtettem szót az idő formátumról. Mivel tudjuk, hogy a dátum egy adott időpont óta eltelt napok számát jelenti, így könnyen rájöhetünk, hogy az idő nem lesz más, mint az eltelt tört napok száma. Vagyis a 2012.12.13 10:00 nem más, mint az eltelt napok száma (41.253) + az eltelt tört napok száma, vagyis 1/24*10 (0,416666667). Tehát egy óra nem más, mint 1/24, egy perc pedig 1/24/60. 

Nem foglalkoztam még egy izgalmas résszel, az egyéni számformátumokkal. Sokszor szembesülünk azzal, hogy egy cellába beírt érték mellé oda szeretnénk írni a mértékegységet is. Itt egyéni számformátum nélkül 2 lehetőségünk van:

1. a szám mellé odaírjuk a mértékegységet is, de innentől kezdve a cellába írt adat már szöveg formátumban lesz, így további műveletet nem tudunk végezni vele.

2. az egyik cellába beírjuk (kiszámoljuk) az értéket, a mellette lévőbe pedig beírjuk a mértékegységet. Sok esetben ez maga az elvárt megoldás, de mindenképpen alkalmas a célunk elérésére. Okozhat azonban nehézséget a cellák formázásakor (keret, háttér stb.), illetve egyszerűen csak szeretnénk egy cellában látni az értéket a mértékegységgel úgy, hogy a cellával továbbra is tudjunk műveleteket végezni.

A fentiekre ad megoldást az egyéni számformátum. Az Excel súgójában kimerítő leírás van az egyéni számformátumról, különböző lehetőségeiről (negatív számok más formátumban stb.), úgyhogy inkább azt írom le, hogy én hogyan szoktam csinálni.

Tegyük fel, hogy Ft/db mértékegységet szeretném az érték mellé kiírni úgy, hogy megmarad az ezres csoportosítás is tizedes jegyek nélkül. Ha kiválasztjuk az Egyéni számformátumot, akkor láthatjuk, hogy sok létező számformátum fel van már sorolva. Én ezek közül szoktam kiválasztani azt, amelyik legközelebb áll ahhoz, amit én szeretnék megadni. Jelen esetben ez az ezres határolóval ellátott, tizedes jegy nélküli számformátum: # ##0 Ez után szeretném megadni a Ft/db kifejezést Ez nagyon egyszerű: # ##0" Ft/db" 

Figyelem: a kívánt szöveget idézőjelbe kell tenni, és KÖZVETLENÜL a számformátum után kell írni!!! A szám és a szöveg közé kerülő szünet jelet már az idézőjelbe kell beleírni. Ez a leggyakoribb hiba, ami miatt nem működik az egyéni számformátum, vagyis hogy a szünetjelet a számformátum után, de még az idézőjelen kívülre tesszük.Ha jól csináltuk, a cellában az érték így fog megjelenni: 13 546 Ft/kg

Dátumot is írhatunk egyéni formátumban, pl. nem akarjuk megjelenteni a napot, csak az évet és hónapot. Ezt a következő formátum megadásával érhetjük el: éééé.hh A pontos kódokat nem írom le, azokat megtaláljátok a súgóban. Lehet rövid vagy hosszú dátumformátum, idő perc nélkül stb. Egy dolgot azonban ne felejtsetek el: attól, hogy a cellában lévő érték formátumát, megjelenítési módját változtatjátok, attól még a cellába írt érték nem változik meg!!!

A cellák formázásával nem foglalkoznék többet, a többi formázási lehetőség elég egyértelmű. Egy témánál fogunk még visszatérni a formázásra, mégpedig a feltételes formázás kapcsán, ami egy igen érdekes, sok lehetőséget rejtő téma, előtte viszont át kell vennünk a cellahivatkozási módokat (relatív, abszolút), úgyhogy ez fog következni.

Még mielőtt mélyebben belemegyünk az Excel rejtelmeibe, előrebocsátanék pár dolgot.

Ez a blog nem egy online tanfolyam akar lenni. Természetesen igyekszem egy fajta logika mentén végigmenni a különböző témákon, a témaválasztás viszont nem lesz teljes körű. Elsősorban konkrét eseteket szeretnék bemutatni, olyanokat, melyek az évek alatt nekem is fejtörést okoztak. Ha kérdésetek van, tegyétek fel nyugodtan, válaszolok rá, ha tudok.

Remélem, hogy ti is hasznosnak találjátok majd ezeket a bejegyzéseket, és bátran osszátok meg őket.

Számformátumok folytatás

Azért érdemes egy kicsit többet elidőzni ezen a látszólag nem annyira lényeges témán, mivel tapasztalatom szerint igen sok bosszúság tudnak okozni a különböző formátumú cellák, és fontos megérteni ezzel kapcsolatban az Excel működését.

Biztos sokan tapasztaltátok már - elsősorban valahonnan máshonnan átmásolt értékek esetében -, hogy az Excel néha kiszámíthatatlanul működik, és nem vesz minden cellát figyelembe.

A fenti táblázat összegére az Excel 4 milliót mond. A számokat pontosan úgy vittem be, ahogyan az látszik, új, formázatlan munkafüzetet használtam. A hiba okát a következő táblázat jobban megmutatja:

Amint az látszik, az első két sort jobbra, az utolsó hármat balra igazította az Excel. Ez máris megmagyarázza a hibát: a teljesen formázatlan valamint a szünetekkel határolt, és tizedesvesszővel megadott szöveget számként értelmezte, a többit viszont meghagyta szövegként. Ezt könnyen felismerhetjük, ha egy formázatlan táblázatban megnézzük, hogy az adott szöveget automatikusan merre igazítja az Excel. A számokat ugyanis jobbra, a szövegeket (még a számnak látszóakat is) pedig balra. A fenti példában a SPACE-t felismerte ezres határolónak, a  VESSZŐT pedig tizedes jelölőnek, és a megadott szöveget automatikusan számmá konvertálta. 

Figyelem: különböző rendszer és Excel beállításoktól függően más karaktereket ismerhet fel az Excel, úgyhogy korántsem biztos, hogy mindenki gépén ugyanezt a szöveget fogja számként felismerni.

Olyan is előfordulhat, hogy máshonnan átmásolt szöveget nem konvertál automatikusan, ha viszont minden cellát egyesével szerkesztünk (F2-es gomb), utána már számként jeleníti meg (bal oldalra igazítottból jobbra igazított lesz). Sok sornál ez nyilván igen kényelmetlen, itt nem marad más, mint a függvény használat (ÉRTÉK függvény).

Ugyanez az automatikus konvertálás történik meg akkor is ha pl. dátumot adunk meg a cellában (2012.12.11), vagy százalékos értéket adunk meg (15%) stb. 

FONTOS: Az Excel minden látszat ellenére 2 mezőtípust ismer: az értéket és a szöveget. Minden megadott inputot először megpróbál számmá alakítani. Dátum esetében ez azt jelenti, hogy a megadott dátumnál kiszámolja, hogy 1900.01.01 és az adott dátum között hány nap telt el, és azt az értéket tárolja el, a százalékosan megadott mezőnél pedig az adott értéket elosztja százzal, és úgy tárolja el. Ezeket könnyen beláthatjuk, ha a dátumot illetve százalékot tartalmazó cella formátumát rendes számmá változtatjuk át, így a fenti dátumból 41.154, a megadott százalékból pedig 0,15 lett. Az Excel tehát megpróbálja valamelyik előre definiált számformátumot felismerni az inputban, és ha ez sikerül, átkonvertálja értékké, és beállítja a felismert formátumot. Ha ez nem sikerül, akkor meghagyja szöveg formátumban.

Ha nem akarjuk, hogy egy általunk megadott inputot az Excel automatikusan értékké konvertáljon (pl. 0-val kezdődő numerikus kód esetén), akkor vagy előre szöveg formátumúra kell állítanunk a cellát, vagy a szöveg előtt egy aposztrófot kell megadni. Ez a szöveg megjelenítését nem befolyásolja, de jelzi az Excelnek, hogy ne akarjon konvertálni.

A következő bejegyzésben még foglalkozom egy kicsit a formátumokkal, elsősorban az egyéni számformátumokkal (pl. db, kg stb.)

Várom a visszajelzéseket és a kérdéseket.

Az alábbi táblázat az előző példát mutatja más számokkal.

Látszólag valami hiba van az Excellel... 

Nyilván sokan tudjátok az okot, de fontos felhívni a figyelmet a következőre.

Az Excel a cellákban tárol számokat, képletek eredményét 9 tizedes jegyen tárolja, megjeleníteni azonban az általunk meghatározott módon fogja. A fenti "hiba" magyarázata is ez: a második oszlopban minden cella 0,499999999 értéket tartalmaz, melyet az Excel az általunk megadott 0 tizedesjeggyel jelenít meg. A cella értéke ettől függetlenül nem lett 0, hanem maradt az eredeti 0,499999999-es érték, melyeket ha összeadunk már érthetőek a 3. oszlopban található értékek. Az alábbi táblázat a tényleges  tizedes jeggyel mutatja az értékeket.

Egy táblázat elkészítésénél kiemelt figyelmet kell fordítanunk a fentiekre. Abban az esetben, ha a cellák megadott vagy számított értékei felvehetnek tört értéket, úgy el kell döntenünk mit kezdünk velük. Abban az esetben, ha úgy döntünk, hogy pontos értékkel számolunk, figyelnünk kell rá, hogy amennyiben a megjelenítéskor elrejtjük a tizedes törteket, úgy a végeredmény kézzel történő ellenőrzésekor előfordulhatnak a példában szereplő esetek. Abban az esetben, ha a táblázatot nem saját használatra készítjük, és várhatóan nyomtatva adjuk át, úgy vagy kerüljük el a fenti "hibát" kerekítéssel, vagy legyünk tisztában azzal, hogy ilyen "hiba" előfordulhat, és magyarázzuk meg az okát, ha rákérdeznek.

A kerekítésre függvényt használhatunk. Akik gyakorlottak a függvények használatában, azok már biztosan találkoztak velük, akik még nem, azoknak most megemlítem a használható függvényeket, a függvényekről általánosságban viszont majd csak a későbbiek folyamán fogok írni.

=KEREKÍTÉS(kerekítendő szám;tizedesjegyek száma)

=KEREK.FEL(kerekítendő szám;tizedesjegyek száma)

=KEREK.LE(kerekítendő szám;tizedesjegyek száma)

Bemutatkozásként egy rövid példát mutatnék, hogy többek között milyen bejegyzésekre számíthattok ezen a blogon.

A feladat

Adva van egy táblázat, melyben az egyik oszlopban havi értékesítési adatok vannak. A feladat, hogy egy új oszlopban kiszámoljuk minden hónapban az addigi halmozott (kumulált) értékesített mennyiséget. Azt gondolom, hogy minden Excel felhasználó könnyedén megoldja ezt a problémát, úgyhogy nehezítsünk egy kicsit a dolgon: a halmozott értékeket tartalmazó oszlopnak csak az első hónaphoz tartozó sorába lehet képletet írni, a többi cellát másolással kell kitölteni természetesen úgy, hogy minden sorban a megfelelő értéket mutassa.

Akinek a fenti feladat megoldása triviális volt, annak nem biztos, hogy sok újat fogok tudni mutatni. Akinek azonban nem sikerült megoldani, az sok érdekes apró trükkel találkozhat a blogon.

Kérlek benneteket, hogy ha megoldottátok a példát, magát a megoldást ne írjátok le, hadd maradjon gondolkodni való a többieknek is!