Excel 2016 zsebkönyv 9786155477256, 9786155477263 [PDF]

Zitiervorschau

NPO

Bártfai Barnabás

□□

BBS-INFO Kiadó, 2016.

4

Excel 2016 zsebkönyv

© Bártfai Barnabás, 2016.

Minden jog fenntartva! A könyv vagy annak oldala­ inak másolása, sokszorosítása csak a szerző írásbeli hozzájárulásával történhet. A betűtípus elnevezések, a Microsoft, a Windows, a Windows logo, az Office és az Excel bejegyzett védjegyek. A Microsoft Szoftver Információ telefonszáma: (06-1) 267-46-36 A könyv nagyobb mennyiségben megrendelhető a kiadónál: BBS-INFO Kiadó, 1630 Bp. Pf. 21. Tel.: 407-17-07 A könyv megírásakor a szerző és a kiadó a lehető legnagyobb gondossággal járt el. Ennek ellenére, mint minden könyvben, ebben is előfordulhatnak hibák. Az ezen hibákból eredő esetleges károkért sem a szerző, sem a kiadó semmiféle felelősséggel nem tartozik, de a kiadó szívesen fogadja, ha ezen hibákra felhívják figyelmét.

Papírkönyv: ISBN 978-615-5477-25-6 E-book: ISBN 978-615-5477-26-3

Kiadja a BBS-INFO Kft. 1630 Budapest, Pf. 21. Felelős kiadó: a BBS-INFO Kft. ügyvezetője Nyomdai munkák: Biró Family Nyomda Felelős vezető: Biró Krisztián

Tartalomjegyzék

5

TARTALOMJEGYZÉK 1. A táblázatkezelésről általában...................................... 9 1.1. Milyen feladatot célszerű táblázatkezelővel megoldani?.. 10 1.2. A táblázatok részei............................................................ 12 1.3. Újdonságok az Excel 2016-ban........................................ 15 1.4. Ha nem tudja, hogy mit kellene tenni?........................... 16

2. Alapvető tudnivalók..................................................... 17 2.1. Az Excel indítása................................................................17 2.2. Az Excel 2016 kezdőképemyője...................................... 17 2.3. A program felépítése......................................................... 18 2.4. Az Excel táblázatainak felépítése.................................... 20 2.5. Helyi és lebegő menük használata..................................24 2.6. Billentyűparancsok............................................................ 24 2.7. Cellák és területek azonosítása........................................ 24 2.8. Kilépés az Excelből................... 29

3. Fájlműveletek............................................

30

3.1. A fájl menü.......................................................................... 30 3.2. Táblázat mentése............................................................... 30 3.3. Módosított táblázat mentése a régi megtartása mellett.... 33 3.4. Új táblázat szerkesztésének kezdése...............................34 3.5. Meglévő táblázat betöltése............................................... 35 3.6. Export, import.....................................................................38 3.7. Táblázat nyomtatása......................................................... 39 3.8. Közös használat................................................................. 41

4. Általános funkciók........................................................ 44 4.1. Nagyítás, nézetek.............................................................. 44 4.2. Kurzormozgatás................................................................ 46

Excel 2016 zsebkönyv

6 4.3. 4.4. 4.5. 4.6. 4.7. 4.8. 4.9. 4.10. 4.11. 4.12. 4.13. 4.14. 4.15. 4.16. 4.17. 4.18. 4.19. 4.20. 4.21. 4.22. 4.23.

Hivatkozást tartalmazó cella kijelölése...................... 48 Több cella egyidejű kijelölése....................................... 48 Utolsó művelet visszavonása....................................... 49 Adatbevitel cellába........................................................ 49 Cellák automatikus kitöltése........................................ 51 Cella tartalmának módosítása...................................... 54 Cellatartalom törlése..................................................... 54 Cellatartalom áthelyezése............................................. 55 Cellatartalom másolása................................................. 55 Cellatartalom mozgatása, másolása vágólap segítségével..................................................................... 56 Beillesztés előnézettel.................................................... 57 Adatok átvitele alkalmazások között......................... 58 Formátum másolása...................................................... 61 Sorok, oszlopok, munkalapok beszúrása................... 61 Munkalap átnevezése, mozgatása, másolása............ 62 Sorok, oszlopok, munkalapok törlése......................... 64 Sor- vagy oszloprész beszúrása, törlése..................... 65 Egyetlen cella beszúrása, törlése..................................66 Számolt, vagy átvett adat cellába vitele..................... 67 Cellahivatkozás rögzítése, relatív és abszolút hivatkozások................................................................... 71 Adattípusok.................................................................... 72

5. Formázási lehetőségek.............................................. 76 5.1. Cella tartalmának igazítása és formázása..................... 76 5.2. Irásirány megváltoztatása................................................ 79 5.3. Cellatípusok meghatározása............................................ 79 5.4. Számítási pontosság, kerekítés........................................ 86 5.5. Mezők szélességének és magasságának állítása........... 87 5.6. Cellák összevonása, egyesítése...... ................................. 88 5.7. Sortörés cellán belül.......................................................... 90 5.8. Sorok, oszlopok, munkalapok elrejtése......................... 91 5.9. Beépített stílusok és formátumok.................................... 92 5.9.1. Táblaformázás...................................................... 92 5.9.2. Stílusok................................................................. 95 5.9.3. Feltételes formázás.............................................. 97

Tartalomjegyzék________________________________7 5.10. Adatjelölések................................................................ 100 5.11. Értékgörbék....................................................................104 5.12. Gyorselemzés............................................................... 105

6. Dokumentum-beállítások.......................................... 107 7. Függvények....................................................................114 7.1. 7.2. 7.3. 7.4. 7.5. 7.6.

Függvények bevitelének módszerei..........................115 Gyakrabban alkalmazott függvények...................... 120 Példák a függvényekre................................................ 123 Függvények, képletek értékké alakítása.................. 132 Cellaterület adatainak kontrollálása........................ 132 Hibaüzenetek, hibaelemzés........................................ 133

8. Egyéb műveletek.......................................................... 138 8.1. 8.2. 8.3. 8.4. 8.5. 8.6. 8.7. 8.8. 8.9. 8.10.

Ablaktábla rögzítése.................................................... 138 Cellák és területek elnevezése.................................... 140 Adatok sorba rendezése.............................................. 141 Helyesírás ellenőrzés................................................... 144 Keresés és csere a táblázatban.................................... 145 Táblázatrészek keretezése, vonalazása.................... 146 Táblázatrészek hátterének beállítása......................... 150 Táblázatok védelme, zárolása.................................... 152 Cellába írható adatok korlátozása.............................155 Korrektúra, megjegyzések.......................................... 161

9. Objektumok és ábrák kezelése................................. 162 9.1. 9.2. 9.3. 9.4. 9.5. 9.6. 9.7.

Ábrák beszúrása........................................................... 162 Képek kezelése............................................................. 164 Alakzatok kezelése...................................................... 168 WordArt......................................................................... 171 Szövegdobozok............................................................ 174 Szimbólumok................................................................ 174 Egyenletek......................................................................175

10. Grafikonok, diagramok............................................ 177 10.1. Diagramtípusok........................................................... 177 10.2. Grafikonok, diagramok készítése.............................. 187 10.3. Diagram módosítása.................................................... 190 10.3.1. Törlés....................................................................191

8_______________________ Excel 2016 zsebkönyv 10.3.2. Diagram áthelyezése, átméretezése............... 191 10.3.3. Diagram-összetevők megváltoztatása........... 192 10.3.4. Diagramstílus és szín megváltoztatása............ 192 10.3.5. Adatsorok és kategóriák szűrése.................... 193 10.3.6. Diagram részeinek formázása........................ 193 10.3.7. Adatsor formázása............................................ 195 10.3.8. Adatpont formázása.......................................... 198 10.3.9. Diagramtípus váltása........................................ 198 10.3.10. Diagramelrendezések és feliratok.................. 199 10.3.11. Térhatású objektumok kezelése...................... 205 10.3.12. További diagramrészek formázása................ 206 10.3.13. Diagram kiegészítése........................................207 10.3.14. Forrásadatok megváltoztatása........................ 208 10.4. 3D térképek................................................................... 210

11. Haladó funkciók........................................................ 213 11.1. Adatbázis-táblázatok................................................... 213 11.2. Adatok szűrése............................................................. 214 11.3. Táblázatok átalakítása................................................. 218 11.3.1. Szövegből oszlopok készítése......................... 218 11.3.2. Ismétlődések eltávolítása.................................220 11.3.3. Beolvasás és átalakítás...................................... 221 11.4. Lehetőségelemzés, célértékkeresés............................222 11.5. Solver..............................................................................224 11.6. Tagolás és részösszegszámítás.....................................227 11.7. Kimutatások készítése................................................. 230 11.8. Egy kattintásos előrejelzés.......................................... 235 11.9. Vezérlők és makrók..................................................... 236 11.9.1. Vezérlők.............................................................. 236 11.9.2. Makrók................................................................ 239 11.10. További lehetőségek.................................................... 243

12. Konfigurálás és testreszabás.................................... 245 12.1. 12.2. 12.3. 12.4. 12.5.

Beállítások..................................................................... 245 A gyorselérési eszköztár és a szalagok módosítása.... 249 A gyorselérési eszköztár módosítása....................... 249 A szalagok módosítása................................................ 251 Gyorsbillentyűk és speciális karakterek használata.... 252

A táblázatkezelésről általában

9

1. A táblázatkezelésről általában A számítógéppel megvalósítható feladatok egyik legnagyobb csoportját adatbázis- illetve táb­ lázatkezelő programokkal oldhatjuk meg. A táblá­ zatkezelés és az adatbázis-kezelés feladata sokszor oly nagymértékben hasonlít egymásra, hogy néha nehéz eldönteni, hogy táblázat-, vagy adatbázis­ kezelő programot használjunk. Természetesen a választ sokszor az dönti el, hogy mely programtí­ pus kezelésében vagyunk járatosabbak, bár egyes feladatokat táblázatkezelővel könnyebben megva­ lósíthatunk, míg másokat szinte lehetetlen. Mind­ emellett sajnos az adatbázis és táblázatkezelésnél a programokat profi módon kezelőknek is néha gon­ dot okozhat az, hogy megtalálják az eredményhez vezető megfelelő matematikai algoritmust. Továb­ bi különbség, hogy az adatbázis kezelésnél több­ nyire nem látjuk egyben a teljes adathalmazt, vi­ szont kényelmesebbek a lekérdezések, egyedi illet­ ve pillanatnyi igények szerinti adatkiválogatások,

10

Excel 2016 zsebkönyv

továbbá jobban megváltoztathatók az adatbeviteli és megjelenítési módszerek. Táblázatkezelésnél inkább az előre definiált számolási módszer alap­ ján meghatározott adatmegjelenítés kerül előtérbe. Az adatbázis-kezelőkkel többnyire bármilyen adatstruktúra felírható, a táblázatkezelőkkel viszont többnyire csak a két- esetleg háromdimenziós táb­ lázatok. Az üzleti élet adathalmazainak legnagyobb ré­ szét úgynevezett kétdimenziós adatbázisok képe­ zik, azaz táblázatok formájában is felírhatok. Ez esetben a feldolgozandó adatokat sorokban és osz­ lopokban (esetleg további lapokban) rögzíthetjük. Jellemző, hogy egy-egy adathalmazt egy táblázatra úgy viszünk fel, hogy ha az bővül, akkor ez függő­ legesen lefelé eredményezzen táblázatnövekedést. Ennek megfelelően például ha ismerőseink adatait szeretnénk egy táblázatban rögzíteni, akkor egy­ más mellé (természetesen külön oszlopba) írjuk az egy adott ismerős nevét, címét telefonszámát stb. Újabb ismerős nevét az előző alá kell felvinni úgy, hogy az azonos jellegű adatok azonos oszlopba kerüljenek.

1.1. Milyen feladatot célszerű táblázatkezelővel megoldani? Mivel a táblázatkezelés olyan kétdimenziós adatfeldolgozási folyamat,. amelyben az adatokat sorokban és oszlopokban írjuk fel, ezért a táblázat­

A táblázatkezelésről általában____________________ 11

kezelő programokkal az így leírható, többnyire számolandó adatokat tartalmazó feladatokat cél­ szerű megoldani. Vegyünk most egy igen jellegzetes példát, melyben cégünk különböző bevételei és kiadásai szerepelnek havi bontásban. Hónap

Bevételek rendszeres

eladások

Kiadások bér és jár. anyagktsg.

összesen

Január február március

április május

június július augusztus szeptember október november december

összesen

A táblázatban tehát egyrészt fel kell tüntetnünk a hónapokat és az éves összesítést, másrészt a kü­ lönböző bevételi és kiadási adatokat valamint ezek összesítését is. Természetesen egy valódi célokat szolgáló táblázat ennél több oszlopot tartalmaz, de már ezen a kis táblázaton is jól látszik az, hogy milyen módon lehet felvenni a különböző adato­ kat. A fentiekből észrevehető, hogy a táblázatkeze­ lés is két jól elkülöníthető feladatból áll. Az egyik feladatrész a táblázat elkészítése illetve definiálása (amikor meghatározzuk, hogy a táblázat hogy néz­ zen ki, milyen elemeket, milyen formában tártál-

12______________________ Excel 2016 zsebkönyv

mázzon), a másik pedig a táblázat használata, ada­ tokkal való feltöltése, a benne lévő adatok módosí­ tása. Persze az egész feladatnak semmi értelme nem lenne, ha a felhasználónak a használat során bármit is számolni kellene. A táblázatkezelésnek pont az a lényege, hogy a számítógép a meglévő adatok figyelembe vételével a számolható adatokat állandóan aktuális értéken tartja. így tehát az ös­ szesen sorokat nem nekünk kell állandóan számol­ gatni, hanem azt a gép tölti ki a meglévő adatok alapján. Ehhez persze fontos, hogy a táblázat elké­ szítése során megadjuk a gépnek, hogy az egyes számolható értéket tartalmazó helyekre, miként számoljon ki adatot.

1.2. A táblázatok részei Mint ahogy azt korábban láttuk a táblázatok el­ sősorban oszlopokból és sorokból állnak. (Az adat­ bázis-kezelő programok mezői megfelelnek a táb­ lázatkezelők oszlopainak, míg a rekordjai a sorok­ nak.) A táblázatkezelőben a sorokat számokkal, az oszlopokat az ábécé betűivel azonosítjuk. Ennek megfelelően az oszlopokból és sorokból álló táblá­ zatot úgynevezett cellákra oszthatjuk. Egy cella (amit néha szokás mezőnek is nevezni - nem összetévesztendő az adatbázis-kezelő programok mezőjével) az előző mintán például a január havi anyagköltség, vagy szintén egy cella a jobb alsó sarokban található teljes végösszeg. A cellák azo­

A táblázatkezelésről általában

13

nosítása úgy történik, hogy meghatározzuk melyik betűvel jelzett oszlop hányadik soráról van szó. (A táblázatokban lehetőség van egymás melletti cellák összevonására is, bár az ezekre való hivatkozás a későbbiekben nagyobb figyelmet igényel. Ilyen cellaösszevonásra példa a fejlécben a bevételek és a kiadások felirat.) A cella tartalma igen sokféle lehet, melyek kö­ zül hármat célszerű kiemelni. Az egyik legjelleg­ zetesebb eset, amikor egy cella fejlécet vagy infor­ mációs szöveget tartalmaz. Ez egy előre meghatá­ rozott szöveg, amely arra utal, hogy abban a sor­ ban vagy oszlopban milyen adatok állnak, azok az adatok mire vonatkoznak. Ilyen cella például a feb­ ruár szót tartalmazó cella is, de ilyen cellák állhat­ nak a táblázatban bárhol önállóan is. E cellák tartal­ mát a munka során többnyire nem módosítjuk. A másik jellegzetes cella az adatcella. Ebbe a cellába írja be a felhasználó az adatot (például a március havi rendszeres bevételt). És végül - ami a legfontosabb - a számolt cellák. A táblázatokat többnyire azért készítjük el, hogy a folyamatosan bevitt adatok alapján mindig aktuális képet kap­ junk adatainkról. Ez csak úgy megy, ha a számol­ ható adatokat nem nekünk kell állandóan újraszá­ molni. Ilyen számolt cella lehet az előző példában bármelyik hónap összesen oszlopában található érték, vagy az összesen sor bármelyik cellája. Ezen cellákba tehát nem a felhasználónak kell adatot

14

Excel 2016 zsebkönyv

írni, hanem a táblázat elkészítésekor kell meghatá­ rozni, hogy az adott cella tartalmát a gép hogyan tudja kiszámolni. Ennek megfelelően e cellákba többnyire valamilyen matematikai formulák ke­ rülnek. Természetesen a táblázatban nem a mate­ matikai képlet látszik, hanem annak eredménye. Ily módon tehát a fenti példában a január havi összesen cellába az angol verzióban az =SUM(B3:E3) a magyarban az =SZUM(B3:E3) függvény kerül, mely szerint a cella tartalma a B3 és E3 cellák által határolt területen található nume­ rikus adatok összege. A programok a használat során nem tesznek különbséget a fenti cellák közt, ez csupán a mi eligazodásunkat szolgálja. A cellákba írt adatokat az adatbázis-kezelőkhöz hasonlóan megkülönböztethetjük típus szerint is, ennek megfelelően az adattípus lehet például karak­ teres, numerikus, százalékos, pénznem, dátum, stb. A táblázat kitöltése során figyelemmel kell len­ ni arra a tényre, hogy amíg egy szöveget írunk a képernyőre, addig elegendő volt egy egyszerű kurzor, de a táblázatkezelésnél azt is jelölni kell, hogy az a szöveg, amit éppen beírunk, az melyik cellába kerül. Ennek megfelelően a táblázatkezelő programok egy aktuális cellát is meghatároznak a szokványos kurzoron túl.

A táblázatkezelésről általában

15

1.3. Újdonságok az Excel 2016-ban Bár az Excel 2016 az alapvető kezelési feladatok tekintetében nem hozott nagy változást, mégis sok apró és hasznos dologgal egészült ki. Amit először észreveszünk, az az, hogy megszabadultunk a 2013-as verzió nagybetűs szalagnevitől, új keresési lehetőségeink vannak és megváltoztak egyes sza­ lagok, menük. Mindemellett pedig számos fontos, de elsőre kevésbé szembetűnő változás is történt: • új sablonok kerültek a programba pénzforgalmi és tőzsdei elemzésekhez és naptárkezeléshez, • számos új diagramtípus került a programba, például hierarchiadiagram, vízesés diagram, többszintű gyűrűdiagram, dobozos ábra, 3D térkép, stb., • egyszerűsödött a lekérdezések használata, gyorsan végrehajtható az adatok beolvasása és átalakítása, • az előrejelzés függvény már az exponenciális simítás alapján történő előrejelzést is lehetővé teszi, amely funkció önálló gombbal is előhív­ ható, • egyszerűbbé vált az adatokhoz illő kimutatás készítése (kapcsolatok automatikus, egyéni mértékek létrehozása, szerkesztése és törlése, automatikus időalapú csoportosítás, kimuta­ tásdiagram részletezőgomb, kimutatás mezőlis­ tájában való keresés, intelligens átnevezés, stb.), • megjelent a gyors alakzatformázás funkció,

16

• • • • • •

Excel 2016 zsebkönyv

a beszúrt képek automatikusan elfordulnak a kamera tájolásának megfelelően, lehetőség van az egyenleteket szabadkézi úton is bevinni, szerkeszteni, egyszerűbbé vált a megosztás, bővült a választható megjelenési témák száma, javult az adatvesztés elleni védelem, stb.

Az Excel 2016 megvásárolható dobozos ter­ mékként vagy az Office 365 előfizetéseként is. Ez utóbbi esetén gyakrabban történik frissítés, hibaja­ vítás, ami értelemszerűen teljesen automatikus. Ennek megfelelően idővel előfordulhatnak kisebb eltérések a könyvben leírtakhoz képest. Bár az Excel 2016 elérhető mobil eszközökre is, ezen rendszerek azonban korlátozásokat tartal­ mazhatnak az asztali gépeken vagy laptopokon futó teljesértékű változathoz képest. (Jelen könyv képernyőábrái Windows 10 operációs rendszer alatt futtatott Excel alapján készültek.)

1.4.

Ha nem tudja, hogy mit kellene tenni?

A szalagcímek végén lévő Művelet, vagy Mond­ ja el, mit szeretne tenni mezőbe beírhatjuk az el­ végzendő művelettel kapcsolatos szavakat, mellyel elérhetjük a kívánt funkciókat, illetve segítséget

kaphatunk a beírt funkcióval kapcsolatban.

Alapvető tudnivalók

17

2. Alapvető tudnivalók 2.1.

Az Excel indítása

Az Excelt a Windows operációs rendszerből in­ díthatjuk verziótól függően többnyire a Start menü

-> Minden program -> Microsoft Office -> Microsoft Excel 2016 ponttal, esetleg az Excel 2016 csempe kiválasztásával. (A program indításának feltétele a sikeres telepítés.) Az Excel indítását követően elő­ ször a Microsoft Excel feliratot tartalmazó ábra jelenik meg, majd ez eltűnve egy kezdőképernyő a lehetőségek közti választáshoz. Ekkor megkezd­ hetjük munkánkat.

2.2.

Az Excel 2016 kezdőképernyője

Ez az a képernyő, amellyel legelőször találjuk szembe magunkat az Excel 2016 indítását követő­ en. Itt választhatunk üres munkafüzettel való kez­ dést vagy egyéb sablont is, de a baloldali További munkafüzetek megnyitása sorra kattintva lehető­ ségünk van a régebben megkezdett munkáink foly­

18

Excel 2016 zsebkönyv

tatására vagy a mentett fájlokkal való munka meg­ kezdésére is. Érdekes lehetőség a Bemutató megte­ kintése sablon, amely segítségével megismerhetjük az Excel 2016 újdonságait. Érdemes rászánni pár percet, mert később több időt spórolhatunk meg az így megismert lehetőségek használatával.

2.3. A program felépítése A program a többi Office 2016 programhoz ha­ sonlóan szalagokat tartalmaz, így a különböző tevékenységek kiválasztása úgy történik, hogy a képernyő felső sorában található megfelelő menüre (szalagnévre) állva egyszer megnyomjuk az egér gombját, majd a megjelenő szalagról a kívánt iko­ non kattintunk. A gombok használatáról a prog­ ram tájékoztatást is ad oly módon, hogy az egér­

Alapvető tudnivalók

19

kurzorral gombra állva egy kis idő elteltével meg­ jelenik a gomb funkciója. A szalagokon lévő gombok mérete, illetve cso­ portosítása követi az ablak méretét, így ha nagyobb felbontásban, teljes képernyőn használjuk az Ex­ celt, nagyobb gombokkal és részletesebb kifejtéssel találkozunk annál, mintha kisebb képfelbontással, vagy kisebb ablakmérettel dolgoznánk.

A szalagokon belül a funkciók csoportosítva vannak, s többnyire minden csoport jobb alsó sar­ kában található egy íi jel, amire rákattintva megje­ leníthető az adott csoporthoz tartozó párbeszédpa­ nel. (Ez lényegében ugyanaz, mint amiket a koráb­ bi verziókban a különböző menüpontok kiválasz­ tásával elérhettünk.) A program számos szolgáltatása úgy működik, hogy az ikonra állva megmutatja annak leendő eredményét, de azt csak a kattintással aktivizálja, így lehetőségünk van végignézni a lehetséges al­ ternatívákat és könnyebben tudunk dönteni anél­

20______________________ Excel 2016 zsebkönyv

kül, hogy bármilyen műveletet is végrehajtanánk. (Ez azonban nem minden funkciónál van így.) További hasznos dolog a helyi menü, ami a jobb egérgombbal való kattintással érhető el. Itt mindig megtaláljuk az adott helyen elvégezhető legfonto­ sabb funkciókat, úgyhogy érdemes gyakran hasz­ nálni. Az Excel 2016-os helyi menüi ráadásul jóval több mindent tartalmaznak, mint a 2003-as vagy korábbi verziók menüi, mivel tartalomtól és pozí­ ciótól függően a fontosabb szolgáltatások helyi eszköztárát is megjelenítik. Az Excel bizonytalan használata esetén élhe­ tünk a súgó szolgáltatásaival, amely a többi Windows-os programhoz hasonlóan itt is segít, ha valamit nem ismerünk. A helpet az FI billentyűvel érhetjük el. További segítségkérési lehetőség a szalagcímek végén lévő „Mondja el, mit szeretne tenni" mező, amelybe az elvégzendő művelettel kapcsolatos szavakat és kifejezéseket írhat be, mellyel elérhet­ jük a kívánt funkciókat, illetve segítséget kapha­ tunk a beírt funkcióval kapcsolatban.

2.4. Az Excel táblázatainak felépítése Az Excel táblázatainak felépítése szintén sor és oszlopszerkezetű, annyi kiegészítéssel, hogy a program egyszerre több ilyen táblázattal képes

Alapvető tudnivalók

21

dolgozni olymódon, hogy minden táblázatot külön munkalapra helyez, de ezeket egy állományban tárolja. Természetesen a munkalapok között is felállíthatunk függvényeket, hivatkozhatunk más munkalapra. További lehetőség, hogy egyszerre több állománnyal is dolgozhatunk, mintha több­ ször nyitnánk meg az Excelt.

Az Excel ablakának jobb felső részében a Win­ dows szabályainak megfelelően a programablak méretét megváltoztató elemeket találjuk. Ez alatt helyezkedik el a menüsor, majd alatta az aktuális menü szalagja. A nyomógombok alatti sorban megtalálható egyrészt a cellaazonosító, ahol jelzi

22

Excel 2016 zsebkönyv

az Excel az aktuális cella azonosítóját (az ábrán pl. C8), a függvénygombot, s mellette a szerkesztőléc­ ben az aktuális cellatartalmat. (Számolt cella esetén a táblázatban az eredményt látjuk, míg ezen a ré­ szen az azt meghatározó matematikai formulát.) Az ablak felső részén láthatjuk az oszlopok azonosítására szolgáló betűket, bal szélén pedig a sorok azonosítását végző számokat. A jobb oldalon és a jobb alsó szélén a gördítősávok helyezkednek el, amelyeket használni a Windows szabályai sze­ rint tudjuk. Az ablak bal alsó részén a különböző munkala­ pok azonosítására szolgáló füleket találjuk. A kí­ vánt munkalapra úgy tudunk átváltani, hogy az adott feliratú fülre kattintunk. Amennyiben a használni kívánt fül nem látszik, úgy az előttük látható nyilakat használva lépkedhetünk a látható fülek között. Vigyázzunk azonban arra, hogy ha véletlenül kattintottunk rá valamely fülre, akkor a táblázatunk eltűnhet. Ilyenkor nem kell megijedni, az nem törlődött ki, hiszen az eredeti táblázatot tartalmazó fülön kattintva (Pl. Munkai) visszahoz­ hatjuk táblázatunkat. Az ablak legalsó sorában egy olyan státuszsort találunk, amely egyrészt megmutatja a program állapotát (Kész, Táblázat újraszámolása, stb.), más­ részt a különböző billentyűk helyzetét (pl. Scroll Lock). A státuszsor jobb szélén a megjelenítésre és nagyításra vonatkozóan találunk lehetőségeket.

23

Alapvető tudnivalók

Bizonyos esetekben az ablak jobb vagy bal szé­ lén megjelenik egy munkaablak is, amelyen az aktuális tevékenységhez kapcsolódó lehetőségeket találjuk. Ez az ablakrész természetesen bezárható, így nem foglal helyet a táblázatból. Az Office 2016 a 2007-es verzióhoz hasonlóan szintén nem menüpontokat és eszköztárakat tar­ talmaz, hanem a menüpontokkal szalagokat hív­ hatunk elő, amelyekben ikonok formájában találjuk meg a kívánt funkciót. A megjelenő szalagok az éppen használt lehetőségtől függenek és a menü­ pontokkal választhatók ki. Alapesetben a Kezdőlap szalag jelenik meg, amely az alapvető funkciókat tartalmazza.

A szalagok megjelenítési módját megváltoztat­ hatjuk, ha kattintunk a jobb felső sarokban lévő HU gombra. Ekkor megjelenik egy ;•••••- Menüszalag automatikus elrejtése elrejtése. Megjelenítéséhez kattintson lista, melyből í ' Ámenüszslag az alkalmazás tetejére. választhatunk, Í - Lapfülek megjelenítése Csak a menüszalagfülek megjelenítése. A. parancsok hogy a menü­ i megjelenítéséhez kattintson az egyik fülre. szalag legyen-e |iiüsto.: lapfülek és parancsok megjelenítése t------ , £ menüszalagfülek és a parancsok megjelenítése automatikusan mindig. elrejtve, csak a szalagnevek jelenjenek meg, vagy a normál módon minden látszódjon. wctí

24

2.5.

Excel 2016 zsebkönyv

Helyi és lebegő menük használata

Egyes szövegformázások esetén a kijelölés után halványan megjelenik egy lebegő eszköztár, amely segítségével szintén elvégezhetjük a legfontosabb formázásokat. További hasznos lehetőség a jobb egérgombbal előhívható helyi menük használata, amely tartalma mindig az adott helyen elvégezhető lehetőségektől függ, így nagy valószínűséggel megtaláljuk rajta a nekünk szükséges tevékenység ikonját is.

2.6.

Billentyűparancsok

Az Excelben a billentyűk használatával minden művelet elvégezhető egér nélkül is. Ha az Alt gombot lenyomjuk, úgy a szalagokon megjelennek azok a betűk, amelyeket le kell ütni ahhoz, hogy az adott funkciót érvényesítsük, vagy az adott szalag­ ra álljunk.

2.7. Cellák és területek azonosítása A cellák azonosítása úgy történik, hogy megha­ tározzuk melyik betűvel jelzett oszlop hányadik

Alapvető tudnivalók____________________________ 25

soráról van szó. Eszerint a bal felső cellát Al-el, a harmadik oszlop negyedik sorában található cellát C4-el azonosítjuk. Az azonosítás az X, Y, Z után AA, AB, AC-vel folytatódik. -

AF123 AE

/« AF

AG

AH

Al_________________

124

Az Excel 2013-ban viszonylag nagyméretű táb­ lákkal is dolgozhatunk, használhatunk három be­ tűből álló oszlopazonosítókat (XFD-ig összesen 16.384 oszlopot), illetve több mint 1.000.000 sort. Ha tehát egy cellát kell azonosítanunk, nincs különösebb problémánk, hiszen a cellát a sor és oszlopazonosítójával meghatározhatjuk. Egy cella azonosítása tehát az oszlop és a sor azonosítójának egymás után írásával történik. Pl.: B2, D18, AF123. Több cella azonosítása esetén a legegyszerűbb módszer az, amikor a terület bal felső és jobb alsó cellájának kettősponttal elválasztott azonosítóját alkalmazzuk. Önálló cellák felsorolása pon­ tosvesszővel, cellametszéspontok kijelölése szóköz­ zel történik. A

2 4

B

C

D

26

Excel 2016 zsebkönyv

Háromdimenziós táblázat esetén a munkalapok összevonása a munkalap-elnevezések közé tett hasonló jelekkel oldható meg. Terület azonosítása tehát annak bal felső és jobb alsó cellájának megadásával valósul meg. Pl.: B2:C4, D5:AC123, B2:B500. Sorok azonosítása a kezdő és a záró sor sorszá­ mának kettősponttal való elválasztásával is leírha­ tó. Pl.: 2:10, 1:100, 5:5. Oszlop azonosítása a kezdő és a záró oszlop be­ tűjelének kettősponttal való elválasztásával is defi­ niálható. Pl.: B:D, A:AA, C:C. A következő ábrák a különböző cellák és táblá­ zatterületek megadásának módját szemléltetik:

27

Alapvető tudnivalók ABC 1 2 3 4 5

—

....

B:B (Egy teljes oszlopot magába foglaló cellatartomány)

Ez utóbbi azonosító alkalmazása igen ritka, csak akkor alkalmazható, ha átkapcsolunk e stílusra. A formula hasznos lehet makrók alkalmazása során illetve speciális hivatkozások készítésénél. Pl.:

Relatív hivatkozás a három sorral feljebb és ugyanabban az oszlopban lévő cellára S[1 ]O[3] Relatív hivatkozás az egy sorral lejjebb és három oszloppal jobbra lévő cellára S3O4 Abszolút hivatkozás a harmadik sorban és a negyedik (D) oszlopban lévő cellára S[-1] Relatív hivatkozás az aktív cella fölötti teljes sorra O[1] Relatív hivatkozás az aktív cella utáni oszlopra S Abszolút hivatkozás az aktuális sorra O Abszolút hivatkozás az aktuális oszlopra S[-3]O

29

Alapvető tudnivalók

Kilépés az Excelből

2.8.

Az Excel programból kilépni vagy a Windows lehetőségei szerint, vagy a Fájl menü Bezárás me­ nüpontjával tudunk. Amennyiben kilépés előtt táblázatunkat nem mentettük el, úgy a gép a men­ tés szükségességére figyelmeztet minket. Ez eset­ ben a Menti a Munkafüzet változásait? kérdésre Mentés válasz esetén mentést végez, Nincs mentés válasz esetén mentés nélkül kilép, Mégse válasz esetén pedig nem lép ki a táblázatkezelőből. X :

i Microsoft Excel

|

Menti a(zj Munkafüzed változtatásait? A Nincs mentés gombra kattintva a program ideiglenesen megőrzi a fájl aktuális példányát. További információ

Mentés

: Nincs mentés

Mégse

Excel 2016 zsebkönyv

30

3. Fájlműveletek 3.1.

A fájl menü

Az Office 2016 Fájl menüjének kiválasz­ tásakor minden más eltűnik a képernyőről. Ha tehát a Fájl menüt választjuk, de ott mégsem kívánunk semmiféle műveletet végezni, úgy abból a táblázathoz visszatérni a felső sorban lévő nyíllal lehet.

©

3.2.

Táblázat mentése

Elkészült táblázatunkat háttértárolón eltárolni úgy tudjuk, hogy a bal felső sa­ rokban lévő lemez gombra kattintunk. A mentés kérése egyébiránt történhet a Ctrl-S billentyű­ kombinációk alkalmazásával vagy a Fájl menü Men­ tés menüpontjának választásával is. Amennyiben táblázatunk még nem volt el­ mentve (emiatt neve sem volt), úgy először meg kell adnunk egy mentési helyet és egy fájlnevet.

31

Fájlműveletek

A mentés helyének kiválasztása során először el kell döntenünk, hogy a számítógép helyi meghaj­ tóira, vagy a Microsoft által biztosított, felhőben lévő OneDrive tárhelyre történjen-e a mentés. Ha ezt kattintással kiválasztottuk, úgy megjelennek az adott helyen legutóbb használt mappák, illetve van egy Tallózás gomb is, amelyre kattintva is tudunk mentési helyet választani. Akár erre a gombra, akár a fenti mappák valamelyikére kattintunk, meg­ jelenik a mentéskor használatos párbeszédpanel, ahol megadhatjuk az állomány leendő nevét. Munksfihct I - Excel

Wormkie

Mentés másként

M,

OneDrive

Men,és

MeMés

Nyoenhtfa Megomíj

[Q Era9^0 4< Heiyhouáadasa

Dokumentumok Dokumentumok

: : 0,.

Asztal Tallózás

ínxxtíbi

Seztrfa

A megadott névnek a szokásos állománynévvel kapcsolatos szabályoknak kell megfelelnie. (Az .XLSX kiterjesztés megadása nem szükséges, azt a gép automatikusan generálja.) A nevet a fájlnév utáni sorba kell begépelni, bár kiválaszthatjuk a meglévő állományokat tartalmazó listáról is, de ez esetben egy régi táblázat felülírása történhet meg.

32

Excel 2016 zsebkönyv

Az állománynév megadását követően értelemsze­ rűen a Mentés gombon kell kattintani. GR Mentés mélWn! t Rendezet •

> E-'.9éj> » Ú| meppe

h OneOtive

| Videók i VtftualBoxVMt....

Né-

Modontit

8 ':wt«gjdn Qt; Bártfai fleinabat * Adam Brown 0-5teOxtor 0rí«ayiijrJa 8.’.': BBS-libri-AdategyeSetés-ntw 8-: egjrtjJa 8b kimutató Egyéni Cffke-sabtanok

Ooktunentumc

’

tó AdaVonaaok

*

HaegéebtteKt Mmta

ceon-atott d


[JT) 4

j .‘.".J ikonkészletek

»

200

E

340 ÜJ1200 í

50

|

120

E

900

• (s3

Üi szabály...

:

Szabályok törlése

0

►

Szabályok kejelése...

P

O

Színátmenetes kitöltés

!hsh hB e egyszínű kitöltés

E! E E EEE .További szabályok...

A másik kiemelési lehetőség a cellák hátterének színezése annak tartalmától függően. Ekkor a cellahátterek egy adott színskála különböző színeit veszik fel a szerint, hogy a cellában lévő érték mekkora a többihez képest. »

”ú8 i’í

®

Feltetetes

Beszúrás • S*™b • Formázás

Cellást Húsok

formázás-táblázatként •

í«Éj Formátum -

i.

Szám

Ilik

|

jE' IT] *

zV Szerkesztés

Celiakijelolési szabályok

.•Tar;

legfekó/legalsó értékek szabályai

Ár

1

900

fjt8

Új szabály...

Szabályok {őrlése

Si|

• J HUSI ( h a b a; Jovábbi szabályok...

Szabályok kezelése...

.

Rendezés Kér ’ és szűrés * kijr

Excel 2016 zsebkönyv

102

Az ilyen jelölés készítéséhez első lépésben jelöl­ jük ki azt a cellatartományt, amit meg kívánunk jelölni, majd válasszuk a Kezdőlap szalag Feltéte­ les formázás ikonját. A Színskálák sornál kattint­ sunk arra a típusra, amelyikkel a jelölést meg kí­ vánjuk tenni. Sok esetben előfordul, hogy a felajánlott szín­ skála nem felel meg igényeinknek, így azt egyedi­ leg kell meghatározni. Ha ilyet szeretnénk, így a További szabályok sort kell választanunk, majd a megjelenő panelen kiválasztani a színskála típusát, majd a különböző értékekhez tartozó színeket. Új formázási szabály

X

?

Szabálytípus kiválasztása: Az összes cella formázása az értékűk alapján

Csak az adott tartalmú cellák formázása

Csak. a sorrend elején vagy végén lévő értékek formázása

•- Csak az átlag feletti vagy alatti értékek formázása Csak az egyedi vagy az ismétlődő értékek formázása »- A formázandó cellák kijelölése képlettel

________

:

Szabály leírásának szerkesztése:___________________________________________ __________

:

Az összes cella formázása az értékük alapján:

Fgrmátumstilus:

■ skála három színből ? v

Típus:

Minimum Legkisebb érték

Érték:

\ {legkisebb élték)

Középpont V: i Percentills

íjfeí i 50

Maximum ívj: :Legnagyobb érték

[yjj

ffi&p

&

j{Legnagyobb érték)

A panelen ezek beállítására számos lehetőség kínálkozik, hiszen ugyanez a panel szolgál a többi feltételes formázás egyedivé tételére is.

Formázási lehetőségek

103

A harmadik kiemelési lehetőség a különféle ikonok alkalmazása a cellán belül. ’ 1

*

IS?

Feltételes Formázás CelUstilusok formézis*'táblázatként-

i COP

£.e Ha kijelölési szabályok

i

1 Ár

*

4

• Ik'd Ársávok

200 340

i 5] Színskálák

1200

120

IjJ, Legfelső/legalsó értékek szabálya:

’

Ikonkészletek

2400 900

ű

Új szabály...

fje

Szabályok törlése

• rsfcilyck kezelése...

©0Q i» h

/ f X

r

Minősítitek

☆

☆

• »C(5O

íit

zi di 4 p_szá% 25%í^ 15%-**

Nézzünk egy egyszerű összegzést és feltételvizsgá­ latot:

124

Excel 2016 zsebkönyv

Feltételes összegzés:

Az persze előfordulhat, hogy nem tudjuk kita­ lálni a megfelelő matematikai formulát, ami a kí­ vánt adat előállításához szükséges lehet. Ebben sajnos a gép nem tud segíteni. E feladatokhoz nem árt némi jártasság a matematika tudományában.

125

Függvények

A következőkben nézzünk meg néhány példát, amely gyakran előfordulhat, és könnyen megold­ hatjuk a legegyszerűbb függvényekkel. Adósávok számítása során fontos lehet egy adott sávba eső összeg kiszámítása: 123 456,00 HUF

1 Összeg: 2 3 Alsó határ 4 Felső határ:

5 6 Alsó határ alatti rész: 7 Felső határ feletti rész: 8 Beleeső összeg:

100 000,00 HUF 200 000,00 HUF-----------------------------=MIN(B3;B1)

0.00 23 456,00

HUfL

MAY/nma m' hr!AX(B 1-B4,0)

=MIN(MAX(B1 -B3;0);B4-B3)

Adott összegek kiszámítására fontos lehet a kerekí­ tés használata. Erre mutat példát a mellékelt táblá­ zat, ahol a számok esetében a megjelenített tizedes­ jegyek számát a gombbal lecsökkentettük nullá-

1 2 3 4

A B Első szám: 1 = Második szám: 2^=5Si A két szám összege: Helyes összeg: __ ^3

=B1+B2

=KEREKÍTÉS(B1;0)+KEREKÍTÉS(B2;0)

126_______________ ______ Excel 2016 zsebkönyv

Útiköltség számítása egy egyszerű példával:

Fizetésemelés számítása egy érdekes eredményt hozó példával (százalékszámítás): =B4*(1+B$1+C4)^

I

2! 3

Emelés:

Név

4 .Hát Izsák 5 -Trab Antal 6 Hü Jenő 7 Víz Elek

Jelenlegi bér 53000

8 L

9 Átlag

10 II Átlagos egyéni emelés: 12 Átl.emelés bértömegre vetítve

^~=ÁTLAG(C4:C7)+B1

A táblázatban a BI, C4-C9 valamint Dll és D12 cellák százalék típusra lettek beállítva (így értelem­ szerűen a bennük lévő adatok 0,18 0,04 -0,06

Függvények

127

stb.). Az emelt bér képlete azért tartalmaz 1+... formulát, mert értelemszerűen nem csak az emelést kell megadni, hanem az eredeti bért plusz az eme­ lést. Az 1 alatt ez esetben a 100%-ot értjük. Ugyan­ ez vonatkozik az 1 kivonására a D12 cellában is. A képletek ...B$l.. része az egyszerű áthelyezés lehe­ tősége miatt tartalmazza az 1-es sor rögzítését, hiszen így ha a D4 cella tartalmát másoljuk le a többibe, nem változik meg a Bl-re való hivatkozás. A Bértömegre vetített emelés pedig azért lett na­ gyobb az átlagos egyéni emelésnél, mert a maga­ sabb bérnél nagyobb mértékű volt az emelés, mint az alacsonyabbnál. Készítsünk egy táblázatot, melynek A és B osz­ lopában nevek szerepelnek. (Az A oszlop rendezve van.) A C oszlopban jelöltessük meg csillaggal azon sorokat, amelyik mellett lévő, B oszlopban lévő név szerepel az A oszlopban.

r=HA(DARABTELI(A$2:A$99;B8)>0;"*";"")

Excel 2016 zsebkönyv

128

Vigyük fel az adatokat az A és a B oszlopokba. (Az első sort hagyjuk meg fejlécnek.) írjuk be a C2 cellába az =HA(DARABTELI(A$2:A$99;B2)>0;"*";"") függvényt. (A függvény lényege, hogy csillagot tesz ha igaz, hogy a darab­ teli függvény nullánál több olyan elemet talál az A2:A99 területen, amely megegyezik B2-vel. A $ jeleket azért tettük ki, hogy másolásnál az A2:A99 terület rögzítve maradjon. Az A$2:A$99 helyére gépelhetünk A:A-t is, de ekkor a fejlécben lévővel azonos cella mellé is kiteszi a csillagot.) Ha bevit­ tük a függvényt, jelöljük ki a C2-es cellát, majd tegyük vágólapra. Ezt követően jelöljük ki a C3 és az alatti cellákat, majd illesszük be a vágólap tar­ talmát. A következő példában végeztessünk egy olyan kereséses számolást, ahol egy listából kell kiválasz­ tani egy adott értékhez tartozó adatot, mellyel műveletet végzünk. A

B

CD

kedvezmény

1 2

cégnév

Alex Kft.

20%

3

Csavar Rt.

25%

4

Eztvedd Kft.

25%;

5

Kő Bt.

10%

6 7

Rekesz Bt. Trab Antal

15% 10%

E

Vevőnév: Eztvedd Kft. Összeg:

15000

Fizetendő

ff25ö

=E2*(1-FKERES(E1;A2:B99;2)) j

A kedvezmény oszlopban lévő adatok: 0,2 0,25 0,25 0,1 0,15. és 0,1 százalékos típusban kerülnek megjelenítésre. A cégnév oszlopban lévő adatok

Függvények

129

ábécé sorrendben rendezve vannak. A táblázat kitöltése során a felhasználó az El cellába beírja a vevő nevét, az E2 cellába pedig a teljes összeget. Ennek hatására az Excel megkeresi a vevőnévvel azonos nevet az A oszlopban, majd a hozzá tartozó százalékértékkel visszatérve kiszámítja a fizetendő összeget. Ehhez az E4 cellába az =E2*(1-FKERES(E1;A2:B7;2)) vagy az =E2*(1-KUTAT(E1;A2: A99;B2:B99)) függvényt kell írni. A függvény ke­ reső része az El cellában lévő értéket megkeresi az A2:A99 területen (ahol a cégnevek vannak), majd a hozzá tartozó B2:B99 területen lévő értéket adja. Ez az érték egy százalékérték, ami valószínűleg 0% és 100% között, azaz 0 és 1 között lesz. Ezt levonva 1ből (azaz a termék árának 100%-ából) megkapjuk azt a számot amivel az E2 cellában található termék árát meg kell szorozni.

Egy mai dátumtól függő kamattal növelt érték kiszá­ mítása, feltételvizsgálattal: =HA(C3' Alakzatok v dobhatjuk táblázataink ..C3 Lig) JgjSmsrtArt kinézetének színvonalát, Képek Online képek Képernyőkép • ha azokba különböző Ábrák képeket, ábrákat is beil­ lesztünk. Fájlban eltárolt képek, ábrák importálása esetén kattintsunk a Beszúrás szalag Ábrák mezőjében a Képek ikonra, válasszuk ki a kívánt képfájlt, majd kattintsunk a Beszúrás gombon. (Ha legördítjük a beszúrás gombot, úgy választhatjuk a Csatolás fájlhoz funkciót - ekkor nem kerül eltárolásra az Excel fájlban a kép, csak egy hivatkozás rá -, illetve a Beszúrás és csatolás, amikor ugyan eltárolódik a kép az Excel fájlban, de annak megváltozása ese­ tén az Excelben lévő kép is módosul.)

Objektumok és ábrák kezelése

163

ClipArt beillesztéséhez kattintsunk a Beszúrás szalag Ábrák mezőjében az Online képek ikonra, melynek hatására megjelenik egy ablak, ahol ke­ reshetünk Clip- üy Alakzatok , w Aruház Artot. Legutóbb használt alakzatok Alakzat beszú­ O\\COO A A-3 rása esetén a Be­ z4? AA t > ☆ Vonalak szúrás szalag Ábrák mezőjében j\\\T.XX Z Á T,A A Téglalapok az Alakzatok iDDOOODOaD ikont kell legördí­ Egyszerű alakzatok teni, majd a be­ ÍÜO A |\ Z7Z3OÖ0©® ® szúrandó formán ©feoonrt/ooeg kattintani, végül húzással az alak­ [){}C3< > Nyilak zat táblázatbeli Hz* A •ö' ’

Nincsenek finom elek

Térbgli forgatás

2,5 pont

5 pont

Képeffektusok gombbal előhívható menüben pedig számos kép­ hatást választhatunk ki (árnyék, tükröződés, ra­ gyogás, élsimítás, fazettázás, térhatású forgatás). Ha több képet is helyeztünk a táblázatra, úgy az Előbbre hozás és Hátrébb küldés gombokkal ha­ tározhatjuk meg azok sorrendjét. A Kijelöléspanel gombbal egy munkaablakot nyithatunk meg, amelyben kattintással kiválasztha­ tunk minden képet, így azokat is, amelyek esetleg takarásban vannak. Az Igazítása gombbal legördíthető listáról több kép együttes kijelölése esetén (shift + kattintás) azokat egymáshoz igazíthatjuk, illetve a rácshoz illesztés funkció bekapcsolásával a kép mozgatása csak a cellarácsok széleire történhet.

Objektumok és ábrák kezelése

167

A Forgatás gombot legördítve lehetőségünk van derékszögű forgatások, illetve tükrözések ki­ választására. A Körülvágás gombra való kattintás hatására megjelenő jelek húzásával lehetőségünk van a kép széleiből levágni, illetve a képen azt túlhúzva ah­ hoz területet hozzáadni. A gomb legördítésével akár alakzatra vagy adott méretarányra is engedi a képet körülvágni az Excel. A képet pontos értékekre is méretezhetjük a Magasság és a Szélesség mezőknél beírt vagy kiválasztott értékekkel. A fényerő és kontrasztértékeket megváltoztat­ hatjuk közvetlenül is a Képeszközök Formátum szalagjának Korrekciók gombjával.

Van lehető­ Nőtt Fo'métum Q Mondj* irt tenni... ségünk arra is, Q9Q hogy a kép egy EMvolftandö Megdőlt* Minden mádos&ei Mcdoittsck adott színű részét (hátte­ rét) átlátszóvá tegyük. Ehhez a Háttér eltávo­ lítása gombra kell kattintani, majd a megjelenő részt körülhatárolni, esetleg az így megjelenő szalag elemeinek segítségével bőví­ teni vagy szűkíteni a megmaradó területeket.

168

Excel 2016 zsebkönyv

A képjellemzők rengeteg lehetőséget kínálnak, ezek pontos hatásait megismerni csak sok-sok próbálgatással és :>íi- 2gyakorlással tudjuk. Stílus Kitöltés Körvonal I ; Érdemes használni a mstmmLJ i jobb egérgombbal Másolás elérhető lehetősége­ • Beillesztés beállításai; ket is, hiszen a helyi © menüben is megta­ $£] Szöveg szerkesztése J* sabb lehetőségek. Elöfehozas ► Az elérhető % Hatrakuld£.s > Hivatkozás... funkciók viszont M a kró^hozzárendel és... eltérhetnek ' attól Beállítás alapértelmezett alakzatként függően, hogy mi­ $ [j Méret és tulajdonságok... lyen képet impor­ Alakzat formázása... táltunk, illetve mi annak forrása, és miként lett beillesztve.

■

9.3.

íq

Alakzatok kezelése

A korábban említett módon beszúrt alakzatok ugyanolyan könnyedén kezelhetők, mint a képek. A kiválasztást kattintással, a mozgatást az alakzat húzásával, az átméretezést a sarkainál és széleinél lévő jelek húzásával lehet megvalósítani. Forgatást az alakzat felett lévő kör alakú nyíl húzásával tu­ dunk végezni. Ha az alakzattal bármiféle formázást vagy mó­ dosítást kívánunk végrehajtani, úgy a Rajzeszkö-

Objektumok és ábrák kezelése__________________ 169

zök Formátum szalagját kell választani. Ez egy opcionális szalag, csak akkor jelenik meg, ha alak­ zatra kattintunk. H *»• „non a. ;. v>ao •i •S ZV < i

BOÖj

j:2 « liií. Árfolyam Frfü-H

Többsz-ntü gyúnistagram Ifik HiMtcgrarn

Válassza ki a diagranrtlpust és a tengelyt az adatsorhoz:

|»H Oubwrcoibfi

Hif- yT‘

Kombináltak

Ok

Mégse

Azt is megtehetjük, hogy a különböző adatsorok más-más diagramtípussal legyenek ábrázolva. Ekkor a panel alsó részében kell az adott sorozatnál a diagramtípus elemet legördíteni, majd a kívánt típust kiválasztani.

10.3.10. Diagramelrendezések és feliratok Alapesetben az elkészített diagram nem tartal­ maz semmiféle címet és tengelyfeliratot, ezeket

200

Excel 2016 zsebkönyv

nekünk kell utólag megadni. Ezt többféle módon is megoldhatjuk. DIAGRAM-ÖSSZETEVŐK A legegyszerűbb Pl Tengelyek megoldás, ha a diagram­ EJ Tengelyeiknek EJ Diagramcím ra, majd a mellette meg­ EJ Adatfeliratok T jelenő + jelre kattintunk, EJ Adattábla majd a megjelenő segéd­ Pl Rácsvonalak pj Jelmagyarázat panelen a kívánt feliratok előtti négyzetet bejelöl­ _____ jük. Az így bekapcsolt ^7 Ifflí u feliratokat aztán már rá­ Kész kattintás után tetszés sze­ Diag ram-összetevő ozzáadasa * elrendezés rint átírhatjuk. ► itt Tengelyek Másik lehetőség, hogy ► Idb Tengelyeinek válasszuk a Diagramesz­ ► S Diag rámám közök Tervezés szalagját, ► fii Adatfeliratok ahol a Diagramelrendezé­ la ► Adattábla sek mezőben meg­ ái Hiba sávok jelennek az ehhez szüksé­ ► ges funkciók. A Diagram­ j iá Rácsvonalak 1 RSI összetevő hozzáadása )!ÍJ ► Jelmagyarázat gombot legördítve szintén yonaíak lehetőség van különféle 1 ! ► | :--zÁ Trendvonal feliratok és egyéb diag­ ( |tN Rozitrv/negst? v eltérés » ramelemek hozzáadására illetve kikapcsolására, melyek aztán a kiválasztá­ sukat követően szintén átírhatók. Problémát elsősorban a tengelyek okozhatnak, mivel ha a forrásadat kijelöléskor nem jelöltük ki a

201

Grafikonok és diagramok

diagram adataival együtt a tengelyek feliratait is, úgy azok csak sima számok lesznek. Ez elsősorban a vízszintes tengely esetén jelent hiányosságot. Természetesen a vízszintes tengely feliratai utólag is meghatározhatók, ha a helyi menü, vagy a Diag­ rameszközök Tervezés szalagján az Adatok kijelö­ lése gombra, majd a megjelenő panelen a jobbolda­ li lista feletti Szerkesztés gombra kattintunk, végül pedig a Tengelyfeliratok panelen kijelöljük vagy meghatározzuk a tengelyfeliratok helyét a táblá­ zatban. : Adatforrás krválesztása Diagram jdíttartomanya;

«Munka2!SB$£SD$8

[iP, Sor/osztop yanása

Vízszintes ftategóna) tengely feliratai

Jfimagyararat (adatsor) j

Hozzáadás

0

Adatsort

0 0

7/Szjrkesztés

X EHávglifás

C? Sjerkesztés

Adatsor?

0 0

1 2

Adatsor)

0

3

0

4

0

5

Rejtett és üres cellák

?

Ten geíyfeí iratok

X

Tengely felirattartománya: =Munka2!SES2:SES8

|§sj OK

= 2013; 2014; 20...

"]

Mégse

?

A több adatsorból álló diagramok esetében az adatsorok nevei is hasonlóan szerkeszthetők, vagy adhatók meg, csak ekkor az Adatforrás kiválasztá­ sa panelen kattintással ki kell választani a kívánt

202

Excel 2016 zsebkönyv

adatsort, majd az e fölött lévő baloldali feletti Szerkesztés gombra kattintva megadni az adatsor­ neveket. Duplán kattintva Tengely formázása a tengelyre a jobbol­ Tengely beáfirtasai Szöveg beállítások dali munkaablakban O ű IB ill rengeteg további lengety beátMásai formázási és beállí­ Határok tási lehetőség nyílik Minimum 0,0 Automat meg. Lehetőség van Maximum 7°.° a tengely megjelení­ Egytég Fd lépték 105 tésének és skálázá­ Kis lépték ; 2.° sának a módosításá­ Vízszintes tengely metszéspontja ra is, így akár loga­ • Automatikus ritmikus beosztású Éjen értéknél ao tengelyeket is létre Legnagyobb értéknél tudunk hozni. (A Nagyságrend Nincs beállításoknál termé­ L...Í Logaritmikus skála 10 szetesen külön-kü­ {. J Értékek fordított sorrendben lön szabályozhatjuk b Osztásközök a vízszintes, illetve a b Feliratok függőleges tengely osztását.) A tengelyekhez hasonlóan a diagram rácsvona­ lait is ki- illetve bekapcsolhatjuk, valamint megha­ tározhatjuk annak részletességét, sűrűségét is. Eh­ hez a Diagrameszközök Tervezés szalagján a Diag­ ram-összetevő hozzáadása gombot legördítve a Rácsvonalak gomb kívánt elemén kattintva lehet a szükséges rácsvonalat kiválasztani.

Grafikonok és diagramok

203

Egy diagramnál fontos lehet tudni, hogy melyik elem mit akar ábrázolni. Az adatsorokat az esetek többségében színekkel különböztetjük meg, de használhatunk más Jelmagyarázat formázása ’x kitöltést is. Mivel ezek Jelmagyarázat beádfLásai Szfivegbeátlítások azonosítására a jelO dl magyarázat szolgál, * Jefmagyarázat beáflftásai meg kell tudnunk ha­ Jelmagyarázat helye tározni annak helyze­ Q Fent >• lent tét is, amit a dupla fial oldalon kattintás után megje­ Jobb gidaion jobbra fent lenő munkaablakban i/í tie fedje a diagramot a jelmagyarázat tudunk módosítani.

Az Excel lehetőséget biztosít arra is, hogy a diag­ ramban magukat az adatokat is megjelenítsük, ezért a Diagrameszközök Tervezés szalagján a Diagram-összetevő hozzáadása gombot legördítve az Adattábla P gomb kívánt Diagram-összetevő Kész Más hiílilo i ti I í 0I hozzáadása ’ el rend ezés * színek » elemének ki­ a T engelyek ► llj) Tengelyeírnek választásával * & ll Diagramom bekapcsolhat­ E C D ifli Adatfeliratok juk, illetve AdtUU. hto N.-Ki fői tJibftsavok » | 8/.UX meghatároz­ Bátsvonaiek [fíh Jelmagyaraza --telekkel hatjuk az dl- Jelmagyaiazat ► tfi' adattábla life ^elmagyarázó1 -jelek nélkül 'tk Trendvonai ► megjeleníté­ További adattál’- -beállítások,.. sét.

Excel 2016 zsebkönyv

204

Lehetőség van arra is, hogy a diagramra adat­ feliratokat helyezzünk, így a diagramról nem csak az arányokat, hanem a konkrét értékeket is le tud­ junk olvasni.

Az adat­ feliratok megjeleníté­ sét a Diag­

ram-

H

™T~í dk-

Diagram-összetevő Kész hozzáadásaelrendezés’

Mi

Tengelyek

Idb

Tengelyeírnek

£

Diagramom

Más színek’

ÍÍS

► ► ►

összetevő ifi, * isfc Adattábla hozzáadása » iá Hibasávofc Középen gomb Adat­ i£ Racsvcnafak » ► Jelmagyarázat feliratok B^lú! a végén funkciójánál tel fjjQj fielüi az alapnal w Trendvonal • lehet kivá­ Pe.ifiiv.-'ne.ijahv e iéra > lasztani, de Kívül 3 végén a legördülő fflfh ^datbuborek lista lehető­ További adatíeHat-beáHitások... ségei diag­ ramtípustól függően eltérőek lehetnek. A diagramfeliratok formázása és tartalma ter­ mészetesen utólag is módosítható. Ha meg kíván­ juk változtatni, kattintsunk rá és gépeléssel módo­ sítsuk a szöveget. Dupla kattintás esetén megjele­ níthető a jobboldali munkaablak is, ahol számos megjelenítésbeli tulajdonság módosítható a színtől kezdve az áttetszőségen át a típusáig. (Például előfordulhat, hogy az adatfeliratok olyan további beállítást is igényelnek, hogy azt nem értékként, édatfeliratok

Grafikonok és diagramok

205

hanem százalékként kívánjuk megjeleníteni. Ekkor a dupla kattintást követően a Tengely beállításai rész Szám funkcióját, majd a legördülő listáról a százalék kategóriát kell választani.) A szöveg formázására több lehetőség is kínál­ kozik, használhatjuk a látványos WordArt stílusok szolgáltatásait a Diagrameszközök Formátum sza­ lagjáról, azok minden előnyével (árnyék, tükrözés, kitöltés, stb.), de visszaváltva a Kezdőlap szalagra a Betűtípus mezőben lévő funkciók (betűtípus váltás, betűméret váltás, színe­ - — J, feJívA. SÜSS® „/‘ÍSfcSiWS, *,* zések, vastag betű, dőlt , íih'hi Kész Más betű, stb.) is működnek. elrendezés- színekTovábbi lehetőség, ha jj | | £3 j fc az előre összeállított fiDLOn fflpl iir,i elrendezés minták közül választunk egyet. Ehhez lífL-it . UíILül jfc a Diagrameszközök Ter­ vezés szalagján a Kész ielrendezés gombját kell irtfkn legördíteni, majd a fel­ ajánlott elrendezésmin­ ták közül a szimpatiku­ son kattintani.

10.3.11. Térhatású objektumok kezelése A különböző 3D-s objektumok elforgatásához, dőlésszögének megváltoztatásához a diagram jobb egérgombbal előhívható helyi menüjéből válasszuk

206

Excel 2016 zsebkönyv

a Térbeli forgatás menüpontot, majd a munkaabla­ kon állítsuk be a kívánt adatokat. Gyakori még a kördiagramok cikkelyeinek szét­ húzása, illetve egyes szeletek kiemelése. Ezt igen egyszerűen megtehetjük. Ha a teljes tortát jelöljük ki, majd húzással elmozdítjuk valamely szeletet, úgy az Excel széthúzza a teljes diagramot, míg ha csak egy szeletet jelölünk ki a mozgatás előtt, úgy csak az az egy szelet kerül ki-húzásra.

10.3.12. További diagramrészek formázása Mivel egy diagram számos részből épül fel, ezen részek külön-külön formázhatok. A diagram legfontosabb eleme az adatsorok te­ rülete, amely formázásáról a korábbiakban már részletesen szóltunk. A rajzterület természetesen a szélének húzásával áthelyezhető, illetve a sarkain lévő jelek húzásával átméretezhető. Egy diagram esetében többféle Kitöltés háttér is előfordulhat, ugyanis Körvonal ’ lehet háttere az egész diagramke­ retnek, illetve egyes diagramtípu­ O Effektusok' sok esetén a falaknak és a padló­ nak is. Ezen elemek formázása a kijelölésüket kö­ vetően az adatsorok formázásánál leírtak szerint valósul meg, tehát a Kitöltés gombbal beállíthatunk háttérszínt és mintát, a Körvonal gombbal szegélyt, az Effektusok gombbal pedig árnyékot, ragyogást, fazettázást, stb.

Grafikonok és diagramok_______________________207

Ennek megfelelően beilleszthetünk akár fájlban lévő képet a hátsó falnak, anyagmintát a padlónak, színátmenetet az oldalfalnak, és szép háromdi­ menziós megjelenítést az adatsoroknak. Feliratok esetében a formázás történhet a Diag­ rameszközök Formátum szalagján a WordArt stí­ lusok szolgáltatásait használva, illetve a Kezdőlap szalagon a Betűtípus mezőben lévő funkciók al­ kalmazásával is.

10.3.13. Diagram kiegészítése A diagramra lehetőségünk van további akár nem odaillő elemeket is helyezni. Újabb szövege­ ket a Diagrameszközök Formátum szalagján az Alakzatok beszúrása mezőben lévő Szövegdoboz gombra kattintva, majd a dobozt megrajzolva tu­ dunk begépelni abba szöveget. Ugyanilyen módon beszúrhatunk tetszőleges rajzi alakzatot is a kívánt alakzat gombjával. Az Excel olyan további kiegészítéseket is kínál a diagramokhoz, mint például a trendvonal, amely megjeleníti az adatok várható alakulását. A trend­ vonalat felvenni a Diagrameszközök Tervezés szalagján lévő Diagram-összetevő hozzáadása gomb legördítése után a Trendvonal gombot, majd a kívánt trendvonalat választva lehet. Ugyanilyen módon lehetőség van a hibasávok megjelenítésére, illetve típustól függően egyéb vonalak, eltérések ábrázolására is.

208_____________________ Excel 2016 zsebkönyv

10.3.14. Forrásadatok megváltoztatása Előfordulhat, hogy egy diagram alapját képező cellacsoport új helyre kerül, megváltozik, vagy esetleg utólag vettük észre, hogy a diagram nem is azokról az adatokról készült, amiről szerettük vol­ na. Az esetek többségében a táblázat módosulását a diagramok is követik, de ha netán mégsem lenne megfelelő a diagram adattartalma, úgy a jobb egérgombbal előhívható helyi menüből vagy a Diagrameszközök Tervezés szalagjáról válasszuk az Adatok kijelölése menüpontot vagy gombot, ahol megtehetjük a kívánt módosításokat.

Gyakran előfordul, hogy a táblázatban a diag­ ramot alkotó mezők nem közvetlenül egymás mel­ lett vagy alatt helyezkednek el, hanem egymástól távolabb, s köztük egyéb, a diagramba felvenni nem kívánt adatok is vannak. Ez esetben a diagra­ mot úgy kell elkészíteni, hogy az adatsorok csak a kívánt adatokat tartalmazzák. Ez történhet a teljes

Grafikonok és diagramok______________________ 209

terület kijelölésével elkészített diagramból adatso­ rok eltávolításával, vagy egy szűkebb diagram új adatsorral való bővítésével. A gyakorlatban az eltávolítás úgy valósítható meg, hogy a diagram jobb egérgombbal előhívható helyi menüjéből kiválasztott Adatok kijelölése me­ nüpont paneljén kattintsunk a felesleges adatsorra, majd az Eltávolítás gombra. Új adatsor felvételekor kattintsunk a Hozzá­ adás gombon, majd az Adatsor nevéhez adjuk meg a nevet, vagy annak helyét, az Adatsor értékeihez pedig adjuk meg, vagy jelöljük ki az adatsor cella­ tartományát. Természetesen meglévő adatsorokat is módo­ síthatunk, ha például nem megfelelően került elhe­ lyezve, vagy nem jó a neve. Ekkor kattintsunk a javítandó adatsorra, majd a Szerkesztés gombra. A megjelenő panelen módosíthatjuk az adott adatsor nevének és értékeinek hivatkozását tartalmazó mezőket. Ha netán fordítva helyezkedne el a táblázatunk, tehát az oszlopok és a sorok felcserélve találhatók, úgy a Sor/oszlop váltása gombbal ezen könnyedén segíthetünk. Ha a tengelyfeliratot tartalmazó tartomány a helytelen, úgy kattintsunk a Tengely feliratai alatti Szerkesztés gombra, majd adjuk meg, vagy jelöl­ jük ki a kívánt tartományt.

210_____________________ Excel 2016 zsebkönyv

További lehetőség, az a korábban már ismerte­ tett módszer, hogy a diagramon, majd a mellette megjelenő T gombon kattintunk, majd az így megjelenő listán tetszés szerint ki-, illetve bejelöl­ jük a megjeleníteni kívánt adatsort és adatpontot, amelyet aztán az Alkalmaz gombra való kattintás­ sal tudunk a diagramra is érvényessé tenni.

10.4. 3D térképek Az Excel 2016 újdonsága a 3D-s térképek, me­ lyek használata során a legelső alkalommal enge­ délyezni kell az adatelemzési bővítményt. 3D-s térképeket A B C Forgalom Dátum persze nem lehet 1 Ország 25 000C 2015.01.01 2 Magyarország akármilyen adattarto­ 3 Németország 34 000€ 2015.01.01 12 000€ 2015.06.03 mányból előállítani, 4 Ausztria 8 000€ 2015.01.01 5 Belgium használatához ugyanis 6 Kína 36 000€ 2015.01.01 szükség van egy olyan 7 Magyarország 10 000€ 2015.04.02 8 Németország 20 000 € 2016.01.01 mezőre, amely földrajzi 9 Ausztria 20 000 € 2016.01.01 12 000C 2016.01.01 helyeket azonosít (Vá­ 10 Belgium Kína - € 2016.01.01 rosnév, országnév, 11 koordináta, stb.). Ezen túl fontos az is, hogy a for­ rásadatokat tartalmazó terület fejlécekkel rendel­ kezzen. A készítés jelentősen eltér a többi di­ agramtól, kezdéshez válasszuk a Beszú­ 3D rás szalag 3D térkép gombját, melynek térkép hatására megnyílik egy külön ablak. Itt az első feladatunk a mezők hozzárendelése a térkép­

IMI

Grafikonok és diagramok

211

hez a jobboldali panelen. Kattintsunk a Hely alatti négyzetben a + gombon és válasszuk azt a mező­ nevet, amely a földrajzi helyet azonosítja a térké­ pen. Ha ez nem egyértelmű gördítsük le a típusvá­ lasztó gombot is és határozzuk meg, hogy hely országnév, településnév, stb-e. Ezt követően a Ma­ gasság (Méret, Érték) négyzetben is kattintsunk a + gombon és válasszuk azt a mezőnevet, amely az adott helyekhez tartalmazó adatokat tartalmazza. Természetesen további adatokat és kategóriákat is beállíthatunk, illetve a mezőket húzással is felve­ hetjük.

Azt, hogy az adatok miként jelenjenek meg a térképen az Adatok alatti ® » ÍW * *•' gombokra való kattintással határozhatjuk meg. A térképet mozgatni az egérrel és a rajta lévő gombokkal is tudjuk. A térkép megjelenése a szalagon megvál­

212_____________________ Excel 2016 zsebkönyv

toztatható, sőt a Témák gombbal akár domborzati térképet is kérhetünk. A Képernyőkép rögzítése gombbal a térkép aktuális állapota vágólapra kerül, amely tartalmát az Excelben például a Kezdőlap szalag Beillesztés gombjával is beszúr­ hatjuk a táblázatba. A 3D térképeket nem szüksé­ ges külön menteni, a térkép abban az állapotban marad, amiben az ablak bezárásakor volt. A 3D térképekkel videóbemutatókat is készíthe­ tünk, amikkel az adatok időbeli változását is szem­ léltethetjük. Ehhez szükségünk van egy olyan me­ zőre a táblázatban, amely dátum-, vagy időértéket tartalmaz, illetve az adatsoroknak ismétlődniük kell úgy, hogy a különböző időpontokban más-más értéket tartalmazzanak. Ha ilyenkor a jobboldali panelen az Idő négyzetben felvesszük a hozzá tar­ tozó mezőt, úgy teljesképernyős nézetben, alul megjelenik egy lejátszósáv is, amely elején lévő lejátszógombbal elindíthatjuk a bemutatót. Az időben animált bemutatót el is menthetjük egy videofájlba, ha kattintunk a Video létrehozása gombra. Lehetőség van több bemutatójelentet is készíte­ ni különböző adatokból, ha kattintunk az Új jelenet gombra. Jeleneteket a baloldali listában a jobb felső sarkukban lévő X gombbal törölhetünk.

Haladó funkciók

213

11. Haladó funkciók

11.1. Adatbázis-táblázatok Az Excel programban lehetőségünk van adat­ bázis-kezeléshez hasonló feladatok elvégzésére is. Ezekkel a listáknak nevezett táblázatokkal vé­ gezhetünk adatbázis-műveleteket, rendezéseket, összegzéseket, hiszen a listák felépítése az adatbá­ zisokhoz hasonlóan rendezett, mezőkből és rekor­ dokból áll. Ezen fogalmakkal főként az adatbázis­ kezelés során találkozhatunk, de Excelre vetítve ezek a következőkként értelmeződnek: Rekordnak nevezzük az egy egységet leíró kü­ lönböző jellemzőket. (Pl. egy rekord az adatállo­ mányban egy ember neve, a hozzátartozó irányító­ számmal, városnévvel, illetve utca, házszámmal, telefonszámmal.) Excelben tehát egy sor. Mező alatt az adatbázis összes elemének egy­ azon jellemző adatát értjük. Excelben ez tehát egy oszlop.

214_____________________ Excel 2016 zsebkönyv

E szerint Excelben tehát a lista oszlopai az adat­ bázis mezői, a lista oszlopfeliratai az adatbázis me­ zőnevei, a lista sorai az adatbázis rekordjai lesznek. Például: B

A

c

D

E

B Br BESS®

B

1

Km? '

?

Cic Imre

1234

Budapest

3

Csőt Ányos

8000

Székesfehérvár Budapest u 99.

4

Hát Izsák

1999

Budapest

Zab u. 5.

5

Kerti Virág

1357

Budapest

Föld u. 3

Láda u. 23

1234567

2345673

Mezte Lenke

2336

Dunavarsány

Pécsi út 45.

Trab Antal

4400

Nyíregyháza

Fau. 3.

398765

8. Wines Eszter

1345

Budapest

Duna u. 93.

4012345

6

345678

Amennyiben egy adatállománnyal folyamato­ san dolgozunk, a rekordok számát szaporítjuk, esetleg csökkentjük. Ezzel az adatállomány na­ gyobbá illetve kisebbé válik. A mezők számának megváltoztatása nem jellemző feladat. A mezőneveknek mindig a lista-adatbázis leg­ felső sorában kell állniuk (ezeket célszerű valami­ lyen módon kiemelni, pl. vastag betűvel vagy táb­ lázatvonallal). Bár az Excel elvileg automatikusan felismeri a lista-adatbázist, mégis kerüljük az üres sorok alkalmazását (az ugyanis elválasztja a listát), illetve egy lapon csak egy listát tároljunk.

11.2. Adatok szűrése A szűrés használatával lehetőségünk van arra, hogy a táblázatban csak egy adott feltételnek meg­

Haladó funkciók

215

felelő adatok jelenjenek meg, a többi ideiglenesen elrejtésre kerül. így a kiválogatni kívánt sorok könnyedén megtekinthetők, javíthatók, vagy azok­ kal összesítések és egyéb műveletek végezhetők. Adatok szűréséhez olyan táblázatrészre van szükségünk, amely megfelel a listák követelménye­ inek. Szétszórtan elhelyezkedő, vagy vízszintesen egymás mellett lévő adatokat szűrni nem lehet. Szintén fontos, hogy a legfelső sorban egy azonosí­ tó is szerepeljen, amely az oszlop tartalmára utal. (pl. dátum, cégnév, mennyiség, stb.)

Szűréshez a legpraktikusabb, ha táblaformázást végzünk (ehhez jelöljük ki a formázandó cellatar­ tományt, majd gördítsük le a Kezdőlap szalag Formázás táblázatként (vagy a Beszúrás szalag Táblázat) ikonját és válasszuk ki a kívánt módot), ekkor ugyanis automatikusan beállításra kerülnek a fejlécek, amelyeken bekapcsolásra kerülnek a szűrők is. A másik lehetőség, ha nem kívánunk táblaformázást vé­ Rendezés Keresés és gezni, csupán egyszerű szű­ és szűrés” kijelölés’ Rendezés (A-Z) rést szeretnénk az adataink­ i ? | Rendezés (Z-A.) ból. Ekkor egyszerűen álljunk csak rá a szűrendő adatokat ; Egyéni sorrend.,, tartalmazó táblázatrészre, ’f’ Szűrő majd válasszuk a Kezdőlap i ’’K Szűf ok törlése szalag végén lévő Rendezés Trs újból alkalmaz és szűrés ikonjának Szűrő

P

Excel 2016 zsebkönyv

216

pontját, vagy az Adatok szalag, Szűrő ikonját. A funkció kiválasztásának hatására az oszlopok felső sorában lévő azonosítók mellett kis legördítő nyi­ lak jelennek meg. A kívánt mezőnév melletti kis nyilat legördítve kiválaszthatjuk az adatbázis egy vagy több rekord­ ját (így a bejelölt rekordok fognak csak megjelenni D c A B a listában), 1 1 ■■Hl- hm - MH - ■MnffT megadhatunk Láda u. 23 2 Rendezés íA-Zl Budapest u. 99 további szűrési 4 A. i Rendezés sZ-A) Zab u 5 £ ► Földu 3 Rendezés szín szerint feltételeket, e- £ Pécsi út 45 7 * Sj^t, ,< rlr.Se Fa u. 3 setleg törölhet­ 6 Duna u 93 c Szövegszürők • jük a szűrést. í p A tartalom­ 11. Keresés . M(A Összes kijelölései 1 i Budapest tól függő Szö­ 1. j-Isid Kaposvár z

veg szűrés, Számszűrés, Dátumszűrés, stb. pontokkal,

í;

■1í

i—Isfl Nyíregyháza

•~0 Székesfehérvár

2 2 2 2

I

™

I

Mégse

egyedi feltételeket is megadhatunk, így lényegében bármilyen feltétel megfogalmazható. AutoSzűrő beállítása

?

X

A megjelenítés fettételei;

Tartozás j nagyobb mint ® És

|

fvj

......A

1000Cf

O Vagy 13 Ll '

TAB

A ? bármely egyetlen karakter jelölésére használható A * bármilyen karafcterfánc jelölésére használható [

OK

|

:

Mégse

Haladó funkciók

217

A szűrt lista már csak az általunk megadott fel­ tételnek eleget tevő sorokat fogja tartalmazni. Szű­ rőt több oszlopra is beállíthatunk, ilyenkor csak azok a sorok fognak megjelenni, amelyek minden szűrő, minden feltételének eleget tesznek. A szűrt adatokat tartalmazó cellákkal azonban bánjunk óvatosan, mert a rájuk irányuló műveletek eredménye adott esetben megtévesztő lehet. A kijelölés hatására az alsó státuszsorban lévő auto­ matikus összesítések csak a szűrt rekordokra vo­ natkoznak, de a különböző függvények, amelyek a szűrt sorokból dolgoznak, például azokat összeg­ zik, továbbra is a teljes lista adatait veszik alapul. Hasznos lehet a szűrést a vágólap-funkciókkal is kombinálni, hiszen ha egy szűrt listát teszünk vágólapra, úgy a vágólaptartalom beillesztésekor már csak a szűrt adatok kerülnek át az új helyre. Szűrés kikapcsolásához ismételten válasszuk a Kezdőlap szalag Rendezés és szűrés ikonjának Szűrő pontját, vagy az Adatok szalag, Szűrő esetleg Szűrők törlése ikonját. Másik helyen lévő, újabb szűrés egy táblázatban csak akkor végezhető, ha az előző szűrőt bezártuk.

Ha nem csak el kívánjuk tüntetni a nemkívána­ tos adatokat a táblázatból, hanem a bizonyos felté­ teleknek eleget tevő adatokat ki is kívánjuk gyűjte­ ni egy új helyre, úgy e célra az irányított szűrés a legjobb megoldás. Az irányított szűrés lényegében egy feltételes másolás, ahol egy előre megadott

Excel 2016 zsebkönyv

218

szempontrendszer szerint végezzük el a megfelelő sorok másolását. Irányított szűréshez az adattar­ tományon kívül szükségünk van egy szűrőtarto­ mányra is, amely a táblázat azon része, ahova a szűrőfeltételeket felvittük az adatbázis függvé­ nyeknél használatos módon. Szűrés indításához az Adatok szalag Rendezés és szűrés mezőjének Speciális ikonját kell válasz­ tani, majd a panelen megadni az eredeti adattar­ tományt, a szűrőtartományt, illetve ha más helyre kívánjuk az eredményt másolni, úgy a Hova má­ solja sorban a céltartomány kezdeti celláját is. 2 3 4 5 6 7 8

Cicimre Csőt Ányos Hátizsák Kerti Virág Mezle Lenke Trao Antii Wince Eszter

1234 8300 1990 1357 7400 4400 1345

Budapest Lada u. 23 Széketfehervá’ Budapest u Budapest Zab u 5. Budapest FoWu 3 Kaposvár Ny'egyhaza Fa u 3 Budapest

0-tUF 20 000 HUF 23 00ÖHUF 1 0C0 HVJF 5 000MUF 10 000HUF 14 000MJF

1234567

Város Tartozás Budapest >5000

2345678 345678 358765 4012345

O Melyben uuije U*> helyit aátolje l»telaiton>4n)r

MunkatiM>t'.-1FSá íít

Sjtiótartamxv Hunul ijMjt ÜJ2 Í3L mqvj «4toi|*

Mu*>*sl SAS'd

fa

Q Cisker egyedi rekordok meglelenítie

11.3. Táblázatok átalakítása 11.3.1.Szövegből oszlopok készítése Ha valamilyen más helyről (pl. szö­ vegszerkesztőből, internetről) importá­ lunk adatokat, úgy könnyen előfordul, Szövegből hogy azok nem táblázatszerűén helyez­ oszlopok kednek el, aminek kézi átalakítása igen körülményes feladat lenne. Ennek automatizálásé

Haladó funkciók

219

ra szolgál az Adatok szalag Szövegből oszlopok ikonja, amelyre a konvertálandó cellák kijelölését követően rákattintva lehetőségünk van a kívánt átalakításokra. ?

Szövegdaraboló varázsló -1. lépés a 3-ból

X

A Szöveg varázsló megállapítása szerint az adat kötött szélességű.

Ha ez igaz, tépjen tovább, egyébként válassza a megfelelő adattípust.

Az eredeti adat típusa Válassza az adat típusát legjobban meghatározó fájttípust: ®íjagottj - Pontosvessző, tabulator vagy mis betű határolja el az egyes mezőket

O £>* széles

- A mezőhatirok közötti területet szóközök töltik ki.

Kijelölt adatok megtekintése: : lmxe/Budapest/Lád« u. 22 it Ányos/Székesfehérvár/Budapest u. 99. 4_ iát Izsák/Budapest/Zeb u. 5. á. íarti Virág/Budapest/Föld u. 3 :• Lenke/Kaposvir/Pácsi út 45.

I WM.

|

Szövegdaraboló varázsló - 2. lépés a 3-ból

?

X

Ezen a képernyőn kiválaszthatja az egyes adatok határolóit. A szövegre gyakorolt hatását megtekintheti az alábbi képen.

Határoló jelek 0 Iabulátor

f~l

Pontosvessző

□ Egymást közvetlenül kővető határolók egynek számítanak

□ VíSSZfl

Szövegjeiólő:

□Szójöz 0 Egyéb:

*

1j

Megtekintés

iudapest -áda u. 23 Zsófc Ányos Széicesí ehér vár iudapest u. 99. iát Izsák Budapest Zab u. S. Budapest (érti Vlzág föld u. 3 fezre Lenke >éeai út 45.

Mégse

< Vissza

|

Tavább>"l

Befejezet

220

Excel 2016 zsebkönyv

A feladat nem bonyolult, de a bemeneti adatok­ tól függően eltérő módon kell eljárnunk, így min­ dig figyeljünk az átalakítandó adatok elhelyez­ kedésére és tagolására. Ha mindent jól csináltunk, a végeredmény a kívánt cellákba szervezett lista lesz. Hasznos funkció ez például akkor is, ha pdf fájlban, szövegként kapunk egy táblázatot, amit ha Excelbe másolunk, az összes adat egyetlen oszlop­ ba kerül.

11.3.2.Ismétlődések eltávolítása További problémaforrás az adatbázis jellegű táblázatoknál, hogy adott esetben redundanciákat, azonos adatokat tartalmaznak, amely aztán prob­ lémákat okozhatnak a munka során. Az ilyenek megszüntetéséhez jelöljük ki az ada­ tokat tartalmazó tartományt, majd válasszuk az Adatok szalag Ismétlődések eltávolítása ikonját.

Ha a lista adatokat is tartalmaz, úgy fontos el­ dönteni, hogy minden ismétlődő elemet ki aka­

221

Haladó funkciók

runk törölni, vagy csak azokat, amelyek esetében a mellettük lévő adatok is azonosak. Ennek értelmé­ ben, ha az eltávolítás panelen minden oszlop előtt meghagyjuk a pipát, akkor csak azok kerülnek eltávolításra, ahol minden adat megegyezik, míg ha csak a legelső marad kijelölve, úgy a gép a többi oszlopot nem veszi figyelembe, és ennek meg­ felelően minden felesleges sort eltávolít, amelyeik esetében egynél többször szerepel az első oszlop­ ban lévő tétel.

11.3.3. Beolvasás és átalakítás Ha külső adatbázisból szeretnénk az excelbe adatokat beolvasni, úgy a legpraktikusabb mód­ szer, ha az Adatok szalag Beolvasás és átalakítás mezőjében legördítjük az Új lekérdezés gombot, majd kiválasztjuk a megfelelő adatbázist. (Ezt ko­ rábban csak a Power Query bővítménnyel volt elérhető.)

SOt Swvrr Ar-slyós Servkev adattwzrccxM

CrjKle-wiotbííisbál

222

Excel 2016 zsebkönyv

11.4. Lehetőségelemzés, célértékkeresés A célértékkeresés egy olyan funkció, amely se­ gítségével úgy tudjuk megváltoztatni a táblázatban lévő adatokat, hogy a végeredmény egy általunk előre megadott állapot vagy érték legyen. Ha ez a módszer nem lenne, úgy ilyen esetekben addig kellene átírogatni a Lehetőségelemzés Előrejelzési ” munkalap bemeneti adatokat tartalmazó cellákat, Esetvizsgáló... amíg ki nem jön a Célértékkeresés. megfelelő végered­ Adattábla... mény, ami sokszor . .. i : lv ' i ...... V csak nagyon hosszas próbálkozásuk után születne meg, és akkor sem biztos, hogy pontosan. Ilyen jellegű problémák megoldásához vá­ lasszuk az Adatok szalag Lehetőségelemzés gomb­ jának kívánt funkcióját. A Célértékkeresés sor segítségével megtalálhatjuk azt a bemeneti adatot, amely a kívánt eredményt adja a függvényt tartal­ mazó cellában. A megjelenő panelen a Célcellához a függvényt tartalmazó, adott értéket elérni kívánt cella azono­ sítóját, a Célértékhez azt az értéket, amit el szeret­ nénk érni, a Módosuló cellához pedig annak a cél-

d?

223

Haladó funkciók

Iának az azonosítóját kell gépelnünk, amely adattal amúgy kísérletezgetnénk ahhoz, hogy a célértéket elérjük. A Célcella és Módosuló cella tartalma a táblázaton való kattintással is meghatározható. Nézzük meg mindezt egy példával: Van egy táblázatunk, amely kiszámolja, hogy adott összegű kölcsönnél, adott kamatláb mellett, havi vissza­ fizetési gyakorisággal mekkora lesz a törlesztő­ részletünk. c =-RESZLET(B3/12;B4*12;B2) A

1 2 3 4 5 6

Kölcsön összege: Kamatláb: Évek száma: Havi törlesztőrészlet:

7 Teljes visszafizetett összeg:

B

10 000 000 HUF 9,80%/ 20,U 95 181 HUlM

22 843 400 H&s,

=B5*B4*12

j

Ezt a táblázatot felhasználva ki szeretnénk szá­ molni, hogy mekkora összegű hitelt tudunk felvenni akkor, ha 9,8%-os kamat mellett húsz évre havi 50.000 forintnyi törlesztőrészletet tudunk vállalni. (A feladat természetesen megoldható a táblázat átalakításával és más függvények alkalmazásával is, de itt most a célértékkeresés bemutatása a cél.) A megoldáshoz tehát válasszuk az Adatok sza­ lag Lehetőségelemzés funkciójának Célértékkere­ sés sorát, majd a megjelenő panelen írjuk be a Cél­ cellához a B5-öt, a Célértékhez az 50000-et, a Mó­ dosuló cellához pedig a B2-t, végül kattintsunk az

224

OK gombon. Ha jól csináltuk az Excel 5.253.158 HUF-ra módosítja a B2-es cella értékét, míg a teljes visszafizetett összeg 12.000.000 HUF lesz.

Excel 2016 zsebkönyv Célérték keresése

?

X

Célcella: Célérték;

Módosuló cella; OK

|

Mégse

11.5. Solver A Solver szintén a végeredmény megtalálásá­ nak az adatok kézi módosításoktól mentes felada­ tára nyújt megoldást. Segítségével bonyolult, optimalizációs lehetőségeket tudunk megoldani úgy, hogy az optimális megoldás érdekében a Solver több bemeneti paramétert is változtat. A Solvert azonban nem érjük el automatikusan, hiszen a külön telepítendő bővítmények közt találjuk. Tele­ pítéséhez a Fájl menü Beállítások pont Bővítmé­ nyek funkcióját kell kiválasztani, majd az Ugrás gombra kattintva kipipálni a Solver bővítmény előtti négyzetet. A Solver gombja az Adatok szalag végén talál­ ható, paneljén az adatokat a célértékkereséshez hasonlóan kell megadni. Célcellához a kívánt ered­ ményt tartalmazó cella azonosítóját, a módosuló cellákhoz pedig azoknak a celláknak az azonosítóit kell gépelnünk, amely adatokkal amúgy kísérletez­ getnénk ahhoz, hogy a célértéket elérjük. A Célér-

Haladó funkciók

225

ték beállítása és a Változócellák módosításával tartalma a táblázaton való kattintással is meghatá­ rozható. A Cél sorban itt nem csak egyenlőség adható meg, hanem minimum és maximum érték is. A Solver tehát jóval több mindent nyújt a célér­ tékkereséshez képest, hiszen több módosuló cella is megadható, definiálhatunk korlátozó feltétele­ ket, beállíthatunk módszereket, stb. Ennek meg­ felelően kellő gyakorlattal komplexebb problémák megoldására is jól alkalmazható. Vegyünk egy példát, ahol egy kisvállalkozás termelési struktúráját kellene meghatározni úgy, hogy a profit a lehető legnagyobb legyen, miköz­ ben korlátozott a raktár-, és munkaerő-kapacitása, valamint az anyagköltségre sem költhet többet ha­ vonta egy megadott összegnél. Fontos az is, hogy az adott termékekből csak egész számú darabot gyárthat. Az adatokat vegyük fel a táblázatba az ábrának megfelelően, de az F3:F7 cellákat ne töltsük még ki. A G3...G7 cellákban lévő értékeket az E3...E7 és F3...F7 cellák szorzatával kaptuk, a BIO, CIO, D10 és E10 cellákba pedig a szorzatösszeg függvénnyel készítettük el a képleteket. A végső nyereség a B12 cellába kerül, ezt az ér­ téket kell majd maximalizálni a Solver segítségé­ vel.

Excel 2016 zsebkönyv

226 8

A

:......... C

0

F......

£

G

Indítsuk el a Solvert az Adatok szalagról, majd töltsük ki a paramétereket. A Sctver paraméterei

X

Célérték beállítása: Cél:

®Mai

OMm

O írtét!*

yáltozóceHák módosításéval: S'i

SFSMFS7 Vonatkozó korlátozások:

SB$lt>-AM »«»nj*-«h.-akt*' ' : kb« rekeere >uvrv1

M6p
wHr» BqiaMK

y: 4 'etuunettNgnfcncddéw virtmóA'- wk •*x igénybexe»á rrvretel nt én yi S