Molnár Mátyás
Bevezetés az Excel 2010 használatába Csak a lényeg – érthetően!
Microsoft Excel 2010
Tartalomjegyzék A TÁBLÁZATKEZELÉS ALAPJAI
1
AZ EXCEL PROGRAMABLAK FELÉPÍTÉSE GYORSELÉRÉSI ESZKÖZTÁR ÁLLAPOTSOR AZ EXCEL MUNKAFÜZET ADATBEVITEL A BACKSTAGE NÉZET KÉPERNYŐ NÉZETEK
1 5 6 7 7 14 19
TÁBLÁZATOK SZERKESZTÉSE
21
CELLATARTALOM SZERKESZTÉSE SZERKESZTŐ MŰVELETEK VISSZAVONÁSA SOROK, OSZLOPOK BESZÚRÁSA, TÖRLÉSE MÁSOLÁS ÁTHELYEZÉS
21 22 22 24 27
KÉPLET ÉS FÜGGVÉNYHASZNÁLAT ALAPJAI
29
MATEMATIKAI MŰVELETEK VÉGREHAJTÁSI SORRENDJE CELLA- ÉS TARTOMÁNYHIVATKOZÁSOK A FÜGGVÉNY HASZNÁLAT ALAPJAI HIBAÉRTÉKEK JELENTÉSE
29 31 37 42
MUNKALAPOK KEZELÉSE
43
MUNKALAPOK SZERKESZTÉSE HIVATKOZÁS MÁSIK MUNKALAPON LÉVŐ ADATRA
43 48
TÁBLÁZATOK FORMÁZÁSA
49
FORMÁZÁS A MINIPULT ÉS A HELYI MENÜK PARANCSAIVAL A CELLÁK FORMÁZÁSA PÁRBESZÉDPANEL FORMÁZÁS CELLASTÍLUSOKKAL FORMÁZÁS DOKUMENTUM TÉMÁKKAL BETŰTÍPUSOK CELLÁK EGYESÍTÉSE CELLATARTALOM IGAZÍTÁSA SZÁMFORMÁTUMOK CELLÁK SZEGÉLYEZÉSE ÉS MINTÁZÁSA CELLÁK KITÖLTÉSE SZÍNNEL OSZLOPSZÉLESSÉG BEÁLLÍTÁSA SORMAGASSÁG BEÁLLÍTÁSA
49 49 50 51 52 54 54 55 59 60 60 61
OLDALBEÁLLÍTÁSOK ÉS NYOMTATÁS
63
OLDALBEÁLLÍTÁSOK ÉLŐFEJ ÉS ÉLŐLÁB A MUNKAFÜZET NYOMTATÁSA
63 67 69
TARTALOMJEGYZÉK
71
CELLA- ÉS TARTOMÁNYHIVATKOZÁSOK A hivatkozás a munkalap celláját vagy cellatartományát azonosítja, és közli az Excellel, hol keresse a képletben használni kívánt adatokat. Hivatkozhatunk ugyanazon munkafüzet más munkalapjainak celláira, más munkafüzetben lévő adatra. A más munkafüzetek celláira történő hivatkozást külső hivatkozásnak (csatolásnak) hívjuk. Az Excel az új fájlformátumban az oszlopokra betűkkel (A-tól XFD-ig, összesen 16 384 oszlop), a sorokra számmal hivatkozik (1-től 1 048 576-ig). Ezeket a betűket és számokat sor- és oszlopazonosítónak hívjuk. Cellahivatkozáskor először az oszlop betűjét, majd a sor számát adjuk meg. A B10 például a B oszlop és az 10. sor metszéspontjában található. Cellatartományra hivatkozáskor adjuk meg a tartomány bal felső sarkában lévő cella hivatkozását, egy kettőspontot (:), majd a tartomány jobb alsó sarkában lévő hivatkozást.
A hivatkozás tárgya
Hivatkozás
Az A oszlop 10. sorában lévő cella
A10
Az A oszlop 10-20. sorának cellatartománya
A10:A20
A B-E oszlop 15. sorának cellatartománya
B15:E15
Az 5. sor minden cellája
5:5
Az 5-10. sor minden cellája
5:10
A H oszlop valamennyi cellája
H:H
A H-J oszlop valamennyi cellája
H:J
Az A-E oszlop 10-20. sorának cellatartománya
A10:E20
Relatív hivatkozás Amikor képletet hozunk létre, a cellákra vagy tartományokra vonatkozó hivatkozások általában a képletet tartalmazó cellához viszonyítva (relatíve) határozzák meg az adat helyét. A következő példában a D2 cellában lévő képlet ugyanabban a sorban a képlettől balra két cellával lévő értékhez hozzáadja a képlettől egy sorral lejjebb és balra kettő cellával lévő értéket – ezt relatív hivatkozásnak nevezzük. Ez most a B2 és B3 cellát jelenti.
Ha relatív hivatkozást tartalmazó képletet másolunk, az Excel a hivatkozásokon relatíve nem változtat. A másolat is ugyanabban a sorban a képlettől balra két cellával lévő értékhez hozzáadja a képlettől egy sorral lejjebb és balra kettő cellával lévő értéket – ezért másolat – ugyanazt a műveletet végzi, relatíve ugyanonnan vett adatokkal.
Microsoft Excel 2010
Igaz, hogy emiatt viszont az oszlop és/vagy a sorazonosító értékén változtatnia kell. Így lett a B2 hivatkozásból B3, és a B3-ból pedig B4. A program hajlandó a hivatkozásokat úgy is megmutatni nekünk, ahogy azt előbb szavakkal leírtuk (konkrét cellahivatkozások nélkül). Ehhez be kell kapcsolnunk az S1O1 hivatkozási stílusú megjelenítést (Fájl, Beállítások, Képletek parancs S1O1 hivatkozási stílus jelölőnégyzetet). Íme, a B2 cella képlete! Ne tessék megrémülni, pontosan az van odaírva, amit előbb „emberi” nyelven elmondtunk. Az első hivatkozás S betűje (Sor) mögött nincs szám, ez azt jelenti, hogy ugyanabban a sorban van az adat, mint a képlet. Az O betű (Oszlop) mögött szögletes zárójelben -2 látható, azaz balra két oszloppal van adat. Ez csak így leírva tűnik bonyolultnak, ha már értjük, akkor ez egyszerű lesz!
Az oszlopazonosító betűjeleket sorszámokra cserélte a program – ennek később még szerepe lesz! Jól látható, hogy a másolt képlet betűre megegyezik az eredeti képlettel – persze, hiszen azért másolat. Vigyázat! Az S1O1 hivatkozási stílus beállítás az aktuális munkafüzetre (fájlra) vonatkozik. Ha ezt fájlt mentjük, s elküldjük egy kollégánknak, akkor azt így kapja meg. Ha Ő még nem ismeri ezt megjelenítési módot (mert nem ebből a jegyzetből tanult), akkor valószínűleg a „frászt” hozzuk ezzel rá. Ha már értjük a relatív hivatkozás működését, akkor kapcsoljuk ki az S1O1 hivatkozási stílus jelölőnégyzetet, s így mentsük a fájlt.
Abszolút hivatkozás Ha a másolt képletnek ugyanabból a cellából kell venni az adatot, mint az eredeti képletnek, akkor használjunk abszolút hivatkozást. Az abszolút hivatkozás jele a $ jel. Ha a $ jelet mind az oszlop-, mind a sorazonosító elé kitesszük, akkor ez a hivatkozás a másolás után nem változik.
A B1 cella képletét bárhová másoljuk, az a második szorzó tényzőt mindig a D5 cellából veszi (mindig a D5-re hivatkozik).
Microsoft Excel 2010
Az S1O1 hivatkozási stílussal megjelenítve a D1 cellára történő abszolút hivatkozás így néz ki: S1O4 – első sor 4. oszlop – ezért vannak ebben a megjelenítési módban oszlopszámok az oszlopazonosító betűk helyett.
Vegyes hivatkozás Lehetőségünk van csak az oszlopazonosítót, vagy csak a sorazonosítót rögzíteni a hivatkozásban. A hivatkozás rögzített részét a program másoláskor nem változtatja meg. $A1 vagy A$1
A következő példában hitelek kamatait számítjuk ki 100.000-től 500.000-ig 2%-tól 8%-ig terjedő éves kamatokkal. A B3 cellába olyan képletet írunk, amely a B3:F9 tartományba másolva mindig az A oszlopból veszi az első szorzótényezőt, abból a sorából, ahová a másolt képlet kerül. A második szorzótényezőt abból az oszlopból veszi a képlet, ahová másoljuk, de mindig a második sorból. Ezért a helyes képlet: =$A3*B$2.
A B3 cella képletét akárhová másoljuk az első szorzó tényezőt mindig az A oszlopból veszi, annak abból a sorából ahová a képletet másoltuk. A második szorzó tényezőt abból az oszlopból veszi, amelyik oszlopba másoltuk, de mindig az első sorból.
Váltás a relatív és az abszolút hivatkozás között Ha képletet hoztunk létre és a relatív hivatkozást abszolútra kívánjuk változtatni (vagy fordítva), jelöljük ki a képletet tartalmazó cellát. A szerkesztőlécen jelöljük ki a megváltoztatni kívánt hivatkozást és azután nyomjuk meg az F4 funkcióbillentyűt. Valahányszor az F4 billentyűt lenyomjuk, az Excel átkapcsol a kombinációk között: abszolút oszlop és abszolút sor (például $C$1); relatív oszlop és abszolút sor (C$1); abszolút oszlop és relatív sor ($C1); valamint relatív oszlop és relatív sor (C1). Ha például egy képletben a $A$1 címet jelöljük ki és megnyomjuk az F4 billentyűt, a hivatkozás A$1 lesz. Nyomjuk le ismét az F4 billentyűt, ettől a hivatkozás $A1 lesz és így tovább.
