Táblázatkezelés

Áttekintés

A Microsoft Word után valószínűleg az Excel a világon legtöbbet használt program. Mindkettő nagyon "adja magát", a betanulási görbe igen rövid, azonnal tudunk produktív munkát végezni. Viszont míg a Word esetén a használat során egyre több és több funkciót kihasználunk, az Excelben valahogy megragadunk az alapoknál. Ezen az oldalon összefoglalom, hogy milyen egyéb lehetőségeink vannak.

Táblázatkezelők

De mielőtt belekezdenénk, lássuk, milyen táblázatos lehetőségeink vannak!

  • Microsoft Excel: természetesen ez a táblázatkezelők alfája és ómegája, ahogy a szövegszerkesztők esetén a Microsoft Word. Ezt használjuk, ehhez hasonlítunk, és ezen az oldalon a példákat ennek segítségével, annak angol verziójával mutatom be.
  • Microsoft Excel Online: az Excel online változata, valamivel kisebb tudással, a https://www.office.com/launch/excel oldalon érhető el.
  • Google Spreadsheets: a Google online táblázatkezelő rendszere itt érhető el: https://docs.google.com/spreadsheets/. Közös szerkesztésre kiválóan alkalmas.
  • LibreOffice, OpenOffice: az ingyenes office alkalmazás csomagok is tartalmaznak táblázatkezelőt.

Fájl formátumok

A legfontosabb fájl formátumok az alábbiak:

  • xls: Excel
  • xlsx: újabb verziójú Excel
  • xlt: Excel sablon
  • xltx: újabb verzjú Excel sablon
  • csv: a Comma Separated File (vesszővel elválasztott fájl) rövidítése, mely szöveges formátumban tárolja csak az adatokat (formázásokat, képleteket, diagramokat stb. nem). Az elválasztó karakter nemcsak vessző lehet.

Források

Az alábbi oldalak segítettek elkészíteni ezt az oldalt:

Cellák

Adatok bevitele

Ha megnyitunk egy táblázatkezelő szoftvert, akkor rengeteg cellát látunk. Oda be tudunk írni adatokat: számokat, szöveget egyaránt.

Sorok és oszlopok

Egy táblázat sorokból és oszlopokból áll. A sorokat általában számokkal, az oszlopokat betűkkel jelöljük.

Adatok generálása

Igen hasznos tulajdonsága, melynek használata egyszerű, mégis, sokan nem ismerik. Excelben ha beírunk egy cellába egy értéket, majd annak jobb alsó sarkába visszük a korzort, akkor megjelenik egy kis plusz jel. Ha azt akár le, akár oldalra húzzuk, akkor ugyanazt az értéket beleírja a többi cellába is. Furcsamód nem működik úgy, hogy egyszerre mindkét dimenzióba húzzuk, de az megoldható, hogy először lefelé hozzuk, majd amíg ki van jelölve, akkor jobbra.

Ha egyszerre több cella ki van jelölve, és úgy húzzuk ki, a kijelölésnek megfelelő irányában (tehát ha mondjuk két egymás alatti cella van kijelölve, akkor lefelé), akkor a következőket tapasztaljuk:

  • Ha nem talál logikát a két cella között (pl. szöveg van beleírva), akkor mindegyiket ismételni fogja. Tehát pl. abból, hogy alma és barack, az alma, barack, alma, barack, alma, barack… sorozatot kapjuk.
  • Ha logikát talál a kettő között, akkor folytatja. Pl. a cellákban ez szerepel: 1 és 2, akkor folytatja a sort: 3, 4, 5, 6 stb. Túl nagy intelligenciát persze ne várjunk; az 1, 2, 4, 8 sorozatot már nem tudta értelmesen folytatni, de még ilyen buta állapotban is hasznos szerintem.
  • Ugyanakkor sok esetben meglepően intelligens. A képletekről később lesz szó, előzetesen viszont említek egy példát. Tegyük fel, hogy van egy 10 sorból és 5 oszlopból álló számhalmazunk, és az első oszlop alján létrehozunk egy olyan képletet, amely összeadja az első oszlopban található számokat. Ha ezt jobbra húzva sokszorozzuk, akkor a második oszlop alatti képletet úgy változtatja meg, hogy a második oszlop számjegyeinek az összegét számolja ki stb. Felületesen gondolkodva persze ezt természetesnek vesszük, valójában azonban nagyon nem az!
  • Beszúráskor kibővíti, törléskor szűkíti az intervallumokat. Tehát Ha pl. van egy képlet, ami az A7-es cellában kiszámolja az A1-A5 cellákban található értékek összegét (5 szám összegét), és a 3. és 4. sor közé beszúrunk egy új sort, majd beírunk az üres A4-es cellába egy számot, akkor az A8-ba került képlet átalakul az A1-A6 összegévé.

Formázások

Alap formázások

Alap formázásokat végre tudunk hajtani cellán belül, az Excelben ld. a Home fülön a Font és Alignment részeket. Néhány lehetőség: betűtípus, betűméret, félkövér, dőlt, aláhúzás, cella színe, betű színe, vízszintes igazítás, függőleges igazítás. A celláknak keretet is tudunk rajzolni.

Adatformátum

Az Excel megpróbálja a tartalomból kitalálni, hogy mi van a cellában, és az alapján formázza. Sok esetben előfordul, hogy az így megalkotott formátum nem ugyanaz, amit mi szeretnénk; ez esetben át kell állítanunk kézzel. Megoldás: jelöljük ki a kérdéses cellákat (tipp: Ctrl + A a teljes dokumentumot kijelöli), majd jobb kattintással válasszuk ki a Format Cells… menüpontot. Ott megadhatjuk a formátumot. Pl. ha a cella egy egész számot tartalmaz, és kiválasztjuk a Number típust, akkor megadhatjuk, hogy hány tizedes pontossággal jelenítse meg. Így lehet a 2-ből pl. 2.00.

Szöveg körbefuttatása

Ha a tartalom nem fér ki a cellába, akkor kétféle lehetőségünk van:

  • Egy sorosra hagyjuk, melynek következtében ha a tőle jobbra levő cellában van adat, akkor csak annyi látszódik belőle, amekkora a cella. Általában ez az alapértelmezett.
  • Körbe futtatjuk a szöveget, ezáltal megnövelve a sor magasságát. Beállítás Excelben: Home → Alignment → Wrap Text.

Cella méret

Az oszlopok szélességét és a sorok magasságát be tudjuk állítani. Tipp: ha fent két oszlop betűjele közötti elválasztóra duplán kattintunk, akkor a bal oldali oszlop automatikusan átméreteződik akkorára, hogy pont beleférjen a legszélesebb tartalom. Ugyanez működik bal oldalon a sorok számjelével is.

Összevonás

Excelben össze tudunk vonni cellákat: jelöljük ki az összevonandó cellákat, majd Home → Alignment → Merge & Center.

Szöveg iránya

A szöveg általában balról jobbra megy de előfordulhat, hogy más irányt szeretnénk. Excelben jobb kattintás a cellán vagy a kijelölt cellákon → Format Cells… → Alignment fülön az Orientation résznél beállíthatjuk az irány szögét. Pl. 90 fokot beállítva lesz lentről felfelé.

Stílus

Amint azt láttuk, Excelben a cellákat egyesével is lehet formázni. Az egész táblát is lehet egyszerre formázni adott stílust iválasztva: a Home → Styles → Format as Table menüpont kiválasztásával.

Feltételes formázások

Az egyszerűbb feltételes formázásokra is van beépített megoldás Excelben, a Home → Styles → Conditional Formatting menüpontban. Néhány példa:

  • Legnagyobb / legkisebb 10
  • Legnagyobb / legkisebb 10%
  • Átlag alatt felett
  • Nagyobb és/vagy kisebb mint
  • Duplikátumok

Saját feltételt is létre tudunk hozni.

Működés

Beviteli mező

A táblázatkezelő rendszereben felül általában van egy egész oldal szélességű beviteli sáv, ahol "jobban elférünk", és kényelmesebbe tudjuk a cella információkat szerkeszteni. Bizonyos műveleteket csak ott tudunk végrehajtani, pl. lépkedni a kurzorral cellán belül, a nyilak segítségével.

Beszúrás és törlés

Két dolgot különböztessünk meg: oszlopok ill. sorok tartalmának törlése ill. maguknak az oszlopoknak ill. soroknak a törlése. Mindkét esetben ki kell jelölnünk a kérdéses oszlopot ill sort a betűre ill. számra kattintással (egyszerre többet is kijelölhetünk). Az első esetben nyomjuk meg a Delete gombot, a második esetben pedig jobb kattintás után válasszuk ki a Delete-et. Ez utóbbi esetben nem tűnnek el a betűk ill. számok, hanem a teljes tartalom balra ill. felfelé tolódik.

Hasonlóan tudunk oszlopot ill. sort beszúrni: válasszuk ki a beszúrandó oszlop ill. sor utáni sort ill. oszlopot, és jobb kattintással válasszuk ki az Insertet. Tipp: ha több oszlopot ill. sort szeretnénk beszúrni, akkor jelöljünk ki annyi oszlopot ill. sort a beszúrandó helytől jobbra ill. lefelé. A beszúráskor a tőle balra ill. felette levő oszlop ill. sor formázását örökli.

Sorba rendezés

Ez szintén a leggyakrabban használt műveletek közé tartozik, és különösen hasznos, ha a táblázat több oszlopot tartalmaz, amit különböző szempontok szerint lehet sorba rendezni. Tegyük a következőt: jelöljük ki azt az oszlopot, ami szerint a sorba rendezést végre szeretnénk hajtani, majd (Excelben) a Data fülön a Sort & Filter szakaszban válasszuk ki bal oldalon az A és Z betűket tartalmazó ikonok egyikét, függően attól, hogy növekvő vagy csökkenő sorrendbe szeretnénk rendezni. Az Excel rákérdez, hogy csak az adott oszlopot szeretnénk-e rendezni, vagy a többi cellát is rendezze (Expand the selection); ez utóbbi az alapértelmezett, és hagyjuk így. Ezzel a módszerrel pillanatok alatt újra tudjuk rendezni, különböző szempontok szerint.

Szűrés

Cellaértékek alapján tudunk sorokat szűrni. Excelben a Data fülön a Sort & Filteren belül kattintsunk a Filter ikonra. Itt kicsit furcsán működik az Excel, ugyanis az első értékes sort átalakítja szűrőfeltétellé. Emiatt célszerű az első sort előtte megduplázni. Mindegyik oszlopban megjelenik felül a szűrőfeltétel, és a kis lefelé mutató háromszögre kattintva állíthatjuk be az aktuális feltételt. Ki tudjuk egyesével válogatni az értékeket, hogy melyek látszódjanak és melyek ne, de számszerű szűrőket is végre tudunk hajtani, pl. egy bizonyos értéknél legyen nagyobb.

Fontos megjegyezni, hogy a szűrés nem törli az adatokat, és az eredeti sorszámok is megmaradnak. A kiszűrt sorok nem látszódnak, így "hézagos" lesz a sorszámozás. Ha egy szűrőfeltétel aktív, akkor a bal oldali számok kék színűek.

Adat validálás

Ha el szeretnénk érni, hogy egy cella csak bizonyos értékeket tartalmazhasson, akkor Excelben tegyük a következőt: jelöljük ki a cellát (vagy tartományt), válasszuk ki a Data → Data Validation menüpontot, és oda írjuk be a feltételt.

Sorok és oszlopok rögzítése

Ha a táblázat tartalma nem fér ki a képernyőre, akkor görgetni kell. Ezzel viszont nem látjuk pl. az első sort, ami esetleg fejléc információt tartalmaz, vagy az első oszlopot, amely az adott sorra vonatkozó leglényegesebb adatot tartalmazza. Általában célszerű lerögzíteni ezeket, hogy görgetés esetén is a helyükön maradjanak. Tetszőleges számú felső sort ill. bal oldali oszlopot rögzíthetünk. Az Excelben View → Freeze Panes → itt választhatunk az első sor, az első oszlop között, valamint az aktuális cellától balra ill. felette levő sorokra ill. oszlopokra. Ez utóbbival lehet pl. két oszlopot és három sort rögzíteni, ha a C4-es mezőben állunk.

Több fül

A táblázatkezelők képesek több lapot kezelni. Általában alul látható a fülek nevei, amit meg tudunk változtatni, és ott tudunk újakat létrehozni.

Transzponálás

Excelben ha kijelölünk cellákat, majd jobb egérgombbal egy másik cellára kattintunk, akkor számos beszúrási lehetőség közül választhatunk. Az egyik a transzponált (Transpose), ami azt jelenti, hogy felcseréli az x és y koordinátákat.

Adatelemzés

Az Excelbe építettek statisztikai adatelemző komponenseket is. Ezt viszont - valószínűleg összetettségénél fogva - alapból nem látjuk. Tegyük a következőt: File → Options → Add-ins → alul Manage: Excel Add-ins, mellette Go… → itt jelöljük be az Analysis ToolPak-ot → OK. A Data fülön megjelenik egy Analysis rész, és azon belül egy Data Analysis. Ott számos adatelemző lehetőséget látunk. Pl. az egyik leggyakoribb a regressziós elemzés (Regression). Ehhez meg kell adni az X és az Y adatsort. Eredményül kiszámolja a korrelációt és még számos egyéb értéket.

Diagramok

A diagramok készítése az Excel leglátványosabb elemei. Ebből lássunk párat!

Általános elvek

A diagramok felépítésénél vegyük figyelembe a következőket:

  • Ki kell jelölni a táblázatnak azt a részét, amiről diagramot szeretnénk készíteni.
  • A cella értékek számok legyenek.
  • Célszerű az oszlopok fölé oszlopnevet, a soroktól balra pedig sor neveket adni.
  • A diagramon elkészítésekor az oszlopokon belüli értékeket szorosabbnak veszi, mint a sorokban. Ez egyébként logikus: általában egy oszlopban azonos természetű dolgok szerepelnek, pl. egyik oszlop az áru neve, egy másik az eladási mennyiség stb., míg a sorokban egy adott dologra vonatkozó, de különböző jellegű dolgok szerepelnek, pl. az, hogy "alma" és 5.

A példákban a következő inputot használjuk:

input.png

Itt egy képzeletbeli elnagyolt gyümölcs eladási statisztikákat látunk. Ebből szeretnénk diagramokat készíteni. Például azt szeretnénk illusztrálni, hogy hogyan változott az alma eladása az évek során, összehasonlítva a barack és a cseresznye eladásokkal. De alapvetően a tendencia az gyümölcs eladások év szerinti változása.

Ha másképp nincs jelezve, az A1:D5 tartomány legyen kijelölve. A diagramokat az Insert → Charts menüpont alatt érjük el.

Oszlop

Az oszlopdiagramok az egyik leggyakoribb diagram típusok, ugyanis látványosak, és segítségükkel két dimenziós adatot is jól tudunk illusztrálni. Egy dimenzió esetén egyszerű oszlopokról van szó (próbáljuk ki pl. úgy, hogy kijelöljük a B2:B6 intervallumot), a két dimenziós eset az érdekes. Logikailag a következő esetek lehetségesek:

  • Egymásra helyezett oszlopok: amikor az összeget szeretnénk hangsúlyozni; jelene setben azt, hogy melyik évben összesen hány gyömölcsöt adtunk el.
  • Egymás mellé helyezett oszlopok: amikor az összeg nem érdekes, viszont össze szeretnénk hasonlítani az egyes gyümölcsök éves eladását és az egymáshoz viszonytott eladását is.
  • 100%-os egymásra helyezett oszlopok: amikor kizárólag az éven belüli arány az érdekes, semmi más.

FOrmáját tekintve a következők lehetnek:

  • Álló vagy fekvő.
  • 2 dimenziós vagy 3 dimenziós: ez plusz információt nem ad hozzá, de nagyon látványos.

Az alábbi diagramon egy egymás mellé helyezett, álló, 3D-ben ábrázolt oszlop diagramot látunk:

barchart.png

Vonal

A vonaldiagramok elsősorban trendek illusztrálására alkalmasak. Néhány lehetőség:

  • Külön ábrázolt, egymásra helyezett vagy az összes 100%-ra nyújtott
  • Csak vonal vagy terület megrajzolása is
  • 2 dimenziós vagy 3 dimenziós
  • Az értékek jelölése vagy elhagyása

Az alábbi példa egy tipikus vonaldiagramot ábrázol:

linechart.png

Torta

A tortadiagramok - egyszerűségük lévén - igen népszerűek, azonban tudunk kell a hátrányairól is. Egyrészt leginkább csak egy dimenziós adatok ábrázolására alkalmas, másrészt összehasonlításnál gyengén teljesít. Akkor érdemes használni, amikor kifejezetten az arányok nagyságrendjét szeretnénk illusztrálni

Az alábbi diagram elkészítéséhez a B1:D2 intervallumot jelöljük ki:

piechart.png

A fánk diagram a torta diagram egy újabb változata, melynek a közepe üres. E korlátozottan alkalmas 2 dimenziós értékek illusztrálására is.

Fatérkép

A fatérképek (treemap; az Excelben a hierarchy chart alatt érhető el) önmagukban csak egy dimenziót képesek ábrázolni. Általában az az erősségük, hogy jól alábonthatóak, az Excelben viszont pont az alábontás hiányzik. Az elemeket mozaikszerűen rendezi el, egy téglalapot több téglalapra bontva.

Az alábbi fatérképet más adatokkal rajzoltam, hogy kicsit látványosabb legyen:

treemap.png

Doboz

Az Excelben a hisztogram alatt helyezkedik el. Segítségével áttekintés kapunk a terjedelemről, azaz minimum és maximum értékekről és a mediánról. Al alábbi ábra elkészítéséhez a B1:D5 intervallum legyen kijelölve, mert az évek bezavarnak:

box.png

Furcsamód a gyümölcsnevek is elvesztek…

Létezik egy ehhez nagyon hasonló diagram típus: a hegedű diagram (vioplot). A doboz diagrammal az a probléma, hogy elfedi a sűrűsödéseket, tehát pl. ugyanúgy illusztrálja azt, ha van 10 4-es, 10 5-ös és 1-6,os, mint azt, hogy van 14-14 4-es és 6-os, és 2 5-ös. A hegedű diagram ez utóbbi esetben a 4-est és a 6-ost szélesebbre veszi. Amiatt hegedű diagram a neve, mert általában az eredmény hegedűre hasonlít. Sajnos az Excelben ilyen diagram típus nincs.

A diagramok tulajdonságai

Miután elkészítettük a diagramot, számos lehetőségünk van az utólagos beállításokra. Néhányat említek:

  • Be tudjuk állítani a címét, a jelmagyarázatot, a koordinátákat.
  • Be tudjuk állítani a színeket, mintákat stb.
  • Ki tudunk szűrni belőle adatsorokat (pl. egy gyümölcsre vonatkozó értékeket).
  • Át tudjuk állítani a diagram típusát: Excelben jobb kattints: Change Chart Type.
  • Az átalakításnál lehetőségünk van transzponálni.

Képletek

A képletek az Excel talán legnagyobb potenciállal rendelkező területe, ugyanakkor az átlag Excel felhasználó számára ez alig ismert. Ebben a fejezetben megkarcoljuk ennek az óriási területnek a felszínét.

Áttekintés

Néhány fő szempont a képletek során:

  • Ha egy cella tartalma egyenlőség jellel kezdődik, akkor azt az Excel képletként értelmezi.
  • A képletek csupa nagybetűsek.
  • A paramétereket zárójelben kell megadni a függvénynév után.
  • A paramétereket vesszővel választjuk el.
  • Paraméterként cella hivatkozást is megadhatunk. Pl. a B5 a B oszlop ötödik sora cellájának a tartalmát jelenti.
  • Ha megadunk két cellát, és közé kettőspontot (:) teszünk, akkor az a teljes szakaszt jelenti. Pl. A1:A5 az A1, A2, A3, A4 és A5-ös cellákat.
  • A magyar nyelvű Excelben a függvényeket is lefordították. (Ezt egyébként hibának gondolom, mert pontosan kell megadni a függvényneveket, és ha csak angol nyelvű dokumentációt találunk egy specifikus problémára, akkor nehéz átfordítani.)
  • Az Excel tartalmaz egy képletszerkesztőt, mégpedig felül, az egysoros beviteli mezőtől balra, az fx ikonra kattintva. Ott beírhatjuk a függvény nevét, és a paramétereket a táblázatból választhatjuk ki.

Alap műveletek

Az alap műveleteket közvetlenül is beírhatjuk. Pl.:

  • =2+3: összeadja a 2-t és a 3-at, és a cella értéke az eredmény lesz. Figyeljük meg, hogy felül a beviteli mezőben a képlet marad.
  • =A1+A2: összeadja az A1 és A2 cella értékét.
  • =A1-A2: kivonja A1 értékéből A2 értékét.
  • =A1*A2: összeszorozza az A1 és A2 cella értékét.
  • =A1/A2: elosztja A1 értékét A2 értékével.
  • =A1^A2: A1 értékét A2 értéke hatványra emeli.

Ha csak ezt írjuk be: 2+3, akkor nem számol ki semmit.

SUM

A függvények "helló világa" az összeadás, azaz a SUM. Hozzunk létre egy olyan dokumentumot, melyben az A1, …, A5 cellákban 1, 2, 3, 4 és 5 szerepel, és állítsuk a kurzort az A7-es cellába. Írjuk be a következőt:

=SUM(A1:A5)

Eredményül a 15-öt kell kapnunk.

Ha véletlenül kettőspont helyett vesszőt írunk (=SUM(A1,A5)), akkor az A1 és A5-ös cella tartalmának összege lesz az eredmény, azaz 6.

Lássuk ugyanezt képletszerkesztővel! Egy másik cellát (pl. A8) kiválasztva kattintsunk az fx-re! Felül a keresőbe írjuk be azt, hogy SUM, majd kattintsunk a Go-ra, a listából válasszuk ki ezt a függvényt, majd kattintsunk az OK-ra. Most meg kell adnunk a paramétereket, amit megtehetünk a táblázat megfelelő celláinak kijelölésével. A végén az OK-ra kattintva elvileg a fenti képletet kapjuk.

Természetesen nem kötelező cellára hivatkozni, pl. az =SUM(2,3) összeadja a két számot, és az összeget, azaz 5-öt ad eredményül.

Lássunk még egy példát! A B1 cellába írjuk ezt: 2*A1. Megjelenik az A1 cella értékének a kétszerese, azaz a 2. Most húzzuk le a B1 cellát B5-ig. Megjelenik a 2, 4, 6, 8, 10 sorozat. Tehát a második sorban A1 helyett A2-t írt stb.! Valamint húzzuk jobbra eggyel az A7-es cellát; a B7-ben 30 jelenik meg, azaz a B1:B5 összege!

Az Excelben a Formulas fülön tudunk listából kiválasztani.

A függvények bemutatásakor az egyszerűsítés érdekében a legtöbb esetben eltekintek a cella elején az egyenlőségjel használatától.

Alap műveletek

Amint azt láthattuk az előző példában, a szorzást képlet nélkül is meg tudtuk adni. Ez igaz a többi alapműveletre is; tehát az =SUM(A1,A2) és az =A1+A2 ekvivalens.

Matematika

Lássuk a legfontosabb matematikai függvényeket!

  • Az alábbi függvények vesszővel elválasztott értékeket, cellákat ill. kettősponttal elválasztott intervallumokat is kaphatnak paraméterül:
    • SUM: a paraméterül kapott értékeket összeadja. Pl. a =SUM(A1:A5) összeadja az A1, A2, A3, A4 és A5 cella tartalmát.
    • PRODUCT: a paraméterül kapott értékeket összeszorozza. Pl. a =PRODUCT(A1:A5) összeszorozza az A1, A2, A3, A4 és A5 cella tartalmát.
    • SUMPRODUCT: tetszőleges számú intervallumot adhatunk paraméterül. Az eredmény az egyes elempárok (ill. elem n-esek) szorzatainak összege. Pl. =SUMPRODUCT(A1:A3, B1:B3) eredménye A1*B1 + A2*B2 + A3*B3 lesz.
    • AVERAGE: a paraméterül kapott értékek átlagát számolja ki.
    • MAX: a legnagyobb elemmel tér vissza.
    • MIN: a legkisebb elemmel tér vissza.
  • Az alábbi függvények paraméter száma fix, nem kaphatnak intervallumot:
    • SIN, COS és a többi trigonometrikus függvény: a megadott érték szinuszát, koszinuszát stb. számolja ki. Ezzel kapocslatos konstans a PI(). Tehát ki kell tenni a zárójelet, de ennek nincs paramétere, a pi értékét adja vissza. Pl. =SIN(PI()/2). Alapértelmezésben radiánban számol. Konverziós függvények: DEGREES (pl. =DEGREES(3.14)) és RADIANS (pl. RADIANS(180)).
    • LOG, LOG10, LN: az első kettő a tízes, a harmadik az e alapú logaritmus. Kapcsolódó függvény: E, ami egy paramétert vár, és a természetes alap (e) hatványát adja vissza. Pl. =E(1) magát az e konstanst. Hatványozni a POWER függvénnyel tudunk, pl. =POWER(3, 4) eredménye 81.
    • MOD: osztás maradéka, pl. =MOD(8, 3) eredménye 2.
    • ROMAN, ARABIC: számokat konvertál egyikből a másikba, pl. =ROMAN(123), =ARABIC("CXXIII").

Még igen sok matematikai és statisztikai függvény van. Ennek a leírásnak nem célja ezek részletes ismertetése; a Formulas fülön érdemes nézegetni a lehetőségeket.

Logika

A feltételkezelés alapvető fontosságú minden programozási nyelvben. Az Excelben is van feltételkezelés, mely tartalmaz logikai műveleteket is.

  • IF(feltétel, igaz ág, hamis ág): kiértékeli a feltételt, melynek eredménye logikai kell, hogy legyen, és az eredménytől függően lesz az IF eredménye a megfelelő ág. Pl. =IF(MOD(A1, 2) = 1, "páratlan", "páros").
  • NOT, AND, OR, XOR: logikai tagadás, logikai és, logikai vagy, logikai kizáró vagy. Példa: =IF(AND(NOT(MOD(G21,2)=1), G21>0),"páros pozitív","nem páros pozitív").
  • TRUE, FALSE: igaz és hamis konstansok.
  • SUMIF(feltétel intervallum, feltétel, összegzés intervallum): összegzés bizonyos feltétel esetén. Pl. =SUMIF(A1:A5, ">4", B1:B5): összeadja B1-től B5-ig azon cella értékeit, melyhez az A1-A5 cella értéke nagyobb mint 4.

Szöveg

Stringek kezelésére is vannak beépített függvények, pl.

  • LEN(str): a string hosszát adja vissza, pl. =LEN(D1)
  • UCASE(str): nagybetűsít.
  • LCASE(str): kisbetűsít.
  • LEFT(str), n: az első N karakterrel tér vissza.
  • RIGHT(str, n): az utolsó N karakterrel tér vissza.
  • TRIM(str): törli az elejéről és a végéről a szóközöket és tabulátorokat. Van külön Ltrim és Rtrim is.
  • REPLACE(eredeti, mettől, meddig, mire): alstringet már stringre cserél.

Dátum

Külön kategóriát képeznek a dátum függvények. Néhány példa:

  • DATE(év, hónap, nap): létrehoz egy dátumot, pl. =DATE(2020,5,15).
  • TODAY(): az aktuális napot adja vissza.
  • WEEKDAY(dátum): megadja, hogy a hét hányadik napja a paraméterül kapott érték. Pl. =WEEKDAY(TODAY()). A hetet vasárnappal kezdi, melynek sorszáma 1.
  • NOW(): visszaadja az aktuális pillanatot perc pontosan.

Cellainformáció

Talán a legnagyobb ereje azoknak a függvényeknek van, amelyek cella információkat használunk fel. Néhány példa:

  • LOOPUP(mit, keresési intervallum[, eredmény intervallum]): rákeres egy értékre, az első találattal tér vissza. Pl. a =LOOKUP(3, A1:A5, B1:B5) megkeresi az első 3-ast az A1-A5 tartományban, és a találathoz tartozó B1-B5 értékkel tér vissza. Ha pl. az A2 cella az első 3-as (az A1-ben nem 3-as van), a B2 cellában pedig az áll, hogy alma, akkor a függvény eredménye alma lesz.
  • TRANSPOSE(intervallum): transzponálja az értékeket. Ezt trükkösen kell beírni: először is ki kell jelölni egy akkora területet, amekkora az eredmény lesz, majd beírni a függvényt, és utána megnyomni a Ctrl + Shift + Enter kombinációt. Pl. az =TRANSPOSE(A1:B3) előtt jelöljünk ki egy 3 széles és 2 magas tartományt, utána árjuk be a fenti függvényt, majd az említett kombinációt. Az eredmény úgy fog látszódni, mintha kapcsos zárójelben lenne.
  • COLUMN(cella): visszaadja a cellához tartozó oszlop sorszámát.
  • ROW(cella): visszaadja a cellához tartozó sor sorszámát.

VBA makrók

Előfordulhat, hogy valami nem megoldható (vagy nem egyszerűen megoldható) függvényekkel; ez esetben használhatóak a makrók.

Makrók engedélyezése

Biztonsági okokból a makrók futtatása Excelben meg van nehezítve.

  • A makrókat a Developer fülön érjük el, az viszont alapból nem látszik. Tegyük a következőt: File → Options → Customize Ribbon → jobb oldalon kattintsuk be a Developert.
  • A Developer fülön található a Macro Settings. Ha nem fut a makró, ott tudjuk engedélyezni.

Makró készítése

Makrót alapvetően kétféleképpen tudunk készíteni:

  • Rögzítéssel: Developer → Record Macro, majd "megjegyzi" a végrehajtott lépéseket.
  • Fejlesztéssel: a programot magunknak kell megírnunk. Ez esetben az Alt + F11 kombinációval nyissuk meg a makró szerkesztőt, ott hozzunk létre egy új modult (pl. Insert → Module, vagy ugyanezt a context menüből), és oda írhatjuk a makrót.

Ez utóbbit fogjuk alkalmazni. Másoljuk be az alábbi kódot!

Sub Sign()

    For Each Cell In Range("A1:A5")
        If Cell.Value > 0 Then
            Cell.Offset(0, 1).Value = "Positive"
        ElseIf Cell.Value < 0 Then
            Cell.Offset(0, 1).Value = "Negative"
        Else
            Cell.Offset(0, 1).Value = "Zero"
        End If
    Next Cell

End Sub

Makró futtatása

A makrókat többféleképpen futtathatjuk. Lépjünk vissza az Excel táblázathoz (a Visual Basic szerkesztő nyitva maradhat). Az alábbiak ekvivalensek:

  • Nyissuk meg a Developers → Macros ablakot, ott válasszuk ki az imént létrehozott makrót, és futtassuk le (Run).
  • Ugyanitt ne a a Run-t válasszuk, hanem az Options…-t, ott állítsunk be egy karaktert, majd Ctrl + a beálltott karakterrel le tudjuk futtatni.
  • Hozzunk létre egy nyomógombot a következőképpen: Developer → Insert → itt válasszuk ki a bal felső ikont → rajzoljunk egy nyomógombot → állítsuk be a futtatandó makrót → adjunk nevet neki. Majd erre a nyomógombra kattintva is le tudjuk futtatni a makrót.

A szerkesztőbe egy debuggert is beépítettek, ahova breakpointokoat tudunk beszúrni, egyesével lépkedni stb.

A VBA áttekintése

Ebben a szakaszban áttekintjük a VBA programozási nyelv alapjait, majd az alábbiakban kitérünk a részletekre.

  • A VBA a Visual Basic for Applications rövidítése.
  • Ezzel tipikusan rövid, pár soros programokat szoktunk készíteni, nagy méretű alkalmazásokat nem.
  • A szokásos alapvető programozási struktúrák (változók, függvények, feltételkezelés, ciklus stb.) meg vannak, de a nagyobb logikai egységek kezelésére itt nincs szükség.
  • Típusos nyelv, a változónak meg kell adni a típusát. A számok előjelesek.
  • Megjegyzéseket a ' karakterrel tudunk beszúrni.

Szubrutin és függvény

A szubrutin és a függvény hasonló fogalmak, de nem teljesen ugyanazok. Szintaktikai eltérések mellett lényeges különbség, hogy a függvény teljesen úgy viselkedik mint a beépített Excel függvények, pl. lehet visszatérési értéke, míg a szubrutinnak nincs. Szubrutinnak Excelből történő futtatás során nem tudunk paramétert átadni (lehet paraméterlistája, de csak másik szubrutinból vagy függvényből hívhatjuk), és azt globálisan futtatjuk, nem tudjuk cellához kötni.

A szubrutin felépítése:

Sub name(param1 As Type1, param2 As Type2, ...)
    command1
    command2
    command3
End Sub

Szubrutinra már láttunk fent példát.

A függvény felépítése

Function name(param1 As Type1, param2 As Type2, ...)
    command1
    command2
    name = result
End Sub

A paraméter lista típusait ld. lejjebb. Az eredményt úgy adjuk vissza, hogy értékül adjuk a függvény nevének. Példa a téglalap területének kiszámolására:

Function area(length As Integer, width As Integer)
    area = length * width
End Function

Excelből ugyanúgy használhatjuk mintha beépített függvény lenne:

=area(A1,A2)

Változók, típusok és műveletek

Változókat a következő módon tudunk létrehozni:

Dim name As Type

Konstansokat hasonlóan:

Const name As Type = value

Értéket az egyenlőségjel segítségéve tudunk adni. Változónak nem lehet egyből értéket adni, konstansnak kötelező, ami egyébként valóban csak konstans (statikusan kiértékelhető) lehet. Pl. egy változó aktuális értékét nem kaphatja értékül.

A típus az alábbi lehet (mind előjeles):

  • Numerikus típusok
    • Byte
    • Integer (16 bites)
    • Long (32 bites)
    • Single (kb. a floatnak felel meg)
    • Double
    • Currency
    • Decimal
  • Nem numerikus típusok
    • String
    • Date
    • Boolean
    • Object
    • Variant: ez bármelyik típust helyettesíti

Műveletek:

  • Aritmetikai műveletek: +, -, *, /, %, ^
  • Logikai műveletek: =, <>, <, >, <=, >=, AND, OR, NOT, XOR
  • Összefűzés: &

Íme egy szándékosan túlegyszerűsített példa:

Function valtozok()
    Const a As Integer = 2
    Const b As Integer = 3
    Dim result As Integer
    result = a + b
    valtozok = result
End Function

Feltételkezelés

Az if struktúra általános felépítése:

If (condition1) Then
    Statements
ElseIf (condition2) Then
    Statements
Else
    Statements
End If

Példa:

Function elojel(number As Integer)
    If number < 0 Then
        elojel = "negatív"
    ElseIf number = 0 Then
        elojel = "nulla"
    Else
        elojel = "pozitív"
    End If
End Function

A case szerkezet az alábbi:

Select Case expression
    Case expressionlist1
        Statements
    Case expressionlist2
        Statements
    Case Else
        Statements
End Select

Példa:

Function nap(day As Integer)
    Select Case day
        Case 1
            nap = "hétfő"
        Case 2
            nap = "kedd"
        Case 3
            nap = "szerda"
        Case 4
            nap = "csütörtök"
        Case 5
            nap = "péntek"
        Case 6
            nap = "szombat"
        Case 7
            nap = "vasárnap"
        Case Else
            nap = "ismeretlen"
    End Select
End Function

Ciklusok

For ciklus

For counter = start To end [Step stepcount]
    Statements
Next

Példa:

Function faktorialis(number As Integer)
    Dim result As Integer
    result = 1
    For counter = 1 To number
        result = result * counter
    Next
    faktorialis = result
End Function

Kilépés a for ciklusból: Exit For.

For each

For Each element In Group
    Statements
Next

A Group lehet pl. tömb, ld. lejjebb. Pl.:

Function gyumolcsok()
    fruits = Array("alma", "barack", "mandarin")
    Dim fruitnames As Variant
    For Each Item In fruits
        fruitnames = fruitnames & Item & ", "
    Next
    gyumolcsok = fruitnames
End Function

Elöltesztelős while ciklus, amíg igaz

Do While condition
    Statements
Loop

Példa:

Function doWhileLoop(number As Integer)
    Dim result As Integer
    Dim counter As Integer
    result = 0
    counter = 0
    Do While counter <= number
        result = result + counter
        counter = counter + 1
    Loop
    doWhileLoop = result
End Function

Ennek egy régebbi szintaxisa:

While condition
    Statements
Wend

Pl.:

Function whileWend(number As Integer)
    Dim result As Integer
    Dim counter As Integer
    result = 0
    counter = 0
    While counter <= number
        result = result + counter
        counter = counter + 1
    Wend
    whileWend = result
End Function

Hátultesztelős while ciklus, amíg igaz

Do
    Statements
Loop While condition

Itt tehát egyszer mindenképpen lefut. Pl.:

Function doLoopWhile(number As Integer)
    Dim result As Integer
    Dim counter As Integer
    result = 0
    counter = 0
    Do
        result = result + counter
        counter = counter + 1
    Loop While counter <= number
    doLoopWhile = result
End Function

Elöltesztelős while ciklus, amíg hamis

Do Until condition
    Statements
Loop

Példa:

Function doUntilLoop(number As Integer)
    Dim result As Integer
    Dim counter As Integer
    result = 0
    counter = 0
    Do Until counter > number
        result = result + counter
        counter = counter + 1
    Loop
    doUntilLoop = result
End Function

Hátultesztelős while ciklus, amíg hamis

Do
    Statements
Loop Until condition

Példa:

Function doLoopUntil(number As Integer)
    Dim result As Integer
    Dim counter As Integer
    result = 0
    counter = 0
    Do
        result = result + counter
        counter = counter + 1
    Loop Until counter > number
    doLoopUntil = result
End Function

Ciklus megszakítása

Mindegyik esetben az Exit Do utasítással tudjuk megszakítani a ciklust.

String

Létezik String típus, és a fent megismert string műveletek itt is használhatóak. Pl.:

Function stringExample(str As String)
    stringExample = Ucase(str)
End Function

Dátum

Dátumkezelésre is vannak beépített függvények, pl:

Function dateExample()
   dateExample = "" & Date
End Function

Dátumkezelés esetén érdemes tájékozódni a lehetőségekről. Egy picit összetettebb példa, ami kiszámolja két dátum között eltelt napok számát:

Function dateDiffExample(date1 As String, date2 As String)
    dateDiffExample = DateDiff("d", CDate(date1), CDate(date2))
End Function

Paraméterként a dátumot adhatjuk meg ilyen formában: "2020-05-14".

Idő kezelésére hasonló módon a Time függvénycsalád használható.

Tömb

Fent már láthattunk egy példát tömbre, amikor felsoroltuk az elemeit. Egyesével is megadhatjuk pl.:

Function arrayExample()
    Dim fruits(3)
    fruits(0) = "alma"
    fruits(1) = "körte"
    fruits(2) = "szilva"
    fruits(3) = "barack"
    arrayExample = fruits(0) & ", " & fruits(1) & ", " & fruits(2) & ", " & fruits(3)
End Function

Az a furcsa állapot áll fenn, hogy a tömbök indexelése 0-tól indul, viszont n-ig tart, nem n-1-ig. A tömb típusa bármi lehet.

Több dimenziós tömböt a következőképpen tudunk kezelni: Dim multidim(2,3) As Variant, majd a hivatkozás multidim(1, 0). A VBA számos tömb kezelő függvényt bitztisít; szükség esetén tájékozódjunk a specifikációban.

Tartomány

A Range tartomány típus. Erre példa:

Function rangeExample(myRange As Range)
    Dim result As Integer
    result = 0
    For Each Cell In myRange
        result = result + Cell.Value
    Next
    rangeExample = result
End Function

Hívása pl.: =rangeExample(A1:A5).

Bemenet és kimenet

Az InputBox()-szal tudunk beolvasni, MsgBox()-szal pedig felugró ablakban kiírni, pl.:

Sub inputOutputExample()
    Dim width As Integer
    Dim length As Integer
    width = InputBox("Szélesség:")
    length = InputBox("Hosszúság:")
    MsgBox("Terület = " & width * length)
End Sub

Az Excel objektumok elérése

Az összes Excel objektumot (lapokat, cellákat stb.) el lehet érni VBA-ból, és minden tulajdonságát be lehet állítani. Néhány példa:

  • ActiveSheet, Worksheet(1), Worksheet("Sheet1"): munkalapra hivatkozás.
    • Rows: sorok elérése.
    • Columns: oszlopok elérése.
    • Cells: cellák elérése.
  • WorksheetFunction: a munkalapra vonatkozó függvények gyűjteménye
    • CountIf(range, condition): a kijelölt tartományon belül található elemekre végrehajt egy ellenőrzést.
  • Selection: az aktuális kijelölést adja vissza.
  • Range: egy tartományra hivatkozik.
  • Cell: egy cellára hivtkozik.
    • Value: a cella értékét lehet ennek segítségével kiolvasni
    • Offset: egy másik cella kiválasztása, relatív távolságra az aktuális cellától.
    • Interior: háttér színét lehet ezen keresztül beállítani

A következő példa a kijelölt cellák tartalmát összeadja, és beleírja az E1-es cellába:

Sub sumSelection()
    Dim result As Integer
    result = 0
    For Each cell In Selection
        result = result + cell.Value
    Next
    Cells(1, 5).Value = result
End Sub

Először ki kell jelölni az összeadni kívánt értékeket, majd le kell futtatni a makrót.

Makróként le kell futtatni, majd kijelölni egy tartományt.

Excel tippek és trükkök

Ebben a szakaszban gyakran előforduló problémákat és megoldásait sorolom fel.

Sorok és oszlopok rögzítése

Kattintsunk abba a cellába, melytől balra levő oszlopokat ill. felette levő sorokat szeretnénk rögzíteni, majd View → Window → Freeze Panges → Greeze Fanes.

Teljes sor formázása

Bal oldalon kattintsunk a megfelelő sort vagy sorokat jelölő számokra. Ezt követően minden formázás az összes cellára érvényes.

Rendezés adott oszlop alapján

Jelöljük ki az oszlopot, majd Data → Sort & Filter → a megfelelő AZ ikonra kattintsunk. A felugró ablakban felteszi a kérdést, hogy csak az adott oszlopot rendezze-e, vagy az oszlop alapján a teljes táblázatot. Ez utóbbi az alapértelmezett, és mi ezt szeretnénk, így hagyjuk az alapértelmezetten.

Duplikátumok kijelölése

Home → Styles → Conditional Formatting → Highlight Cells Rules → Duplicate Values…

Duplikátumok törlése

Data → Data Tools → Remove Duplicates

Hőtérkép készítése

Home → Styles → Coditional Formatting → Color Scales → itt válasszunk ki egy tetszőleges színskálát.

Cella tartomány körberajzolása

Jelöljük ki a tartományt, majd Home → Font → alul középen válasszuk ki a Borders lenyíló menüt, és ott válasszuk ki a megfelelőt.

Véletlen számok generálása adott tartományban

=RANDBETWEEN(1,10)

Kulcshoz tartozó érték keresése

Tegyük fel, hogy a kulcsok az A2:A6 cellákban, míg a hozzájuk tartozó értékeke a B2:B6 cellákban vannak. Pl. az alma kulcshoz tartozó (első) érték megkeresése: =LOOKUP("alma", A2:A6,B2:B6).

Kulcs-érték párok tortadiagramként történő ábrázolása

Tegyük fel, hogy az A oszlop tartalmazza a kulcsokat, a B az értékeket. Az első sor fejléc. Jelöljük ki a megfelelő tartományt (pl. 5 értékes elem esetén az A1:B6-ot), majd Insert → Charts → válasszuk ki a megfelelő tortadiagramot.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License