14.Cellaformátum. XVII. Az Excel XP Igazítás Igazítás ikonokkal

XVII. Az Excel XP

14.Cellaformátum ¾ „Formátum”, „Cellák…” ½ ¾ Helyi menü, „Cellaformázás…” ½ A cellaformátum egy nagyon összetett fogalom, többféle formázás is tartozik bele. Ezek egy részét korábban már tanultuk, más része viszont abszolút újdonságnak számít. Amit már tanultunk, azt egy kicsit gyorsabban, helyenként csak utalásokkal fogjuk megbeszélni. A cellaformázást csak kész üzemmódban lehet végrehajtani (ez alól csak a karakterformázás a kivétel), és ha vannak kijelölt területek, akkor az összes kijelölt cellára végrehajtódik a formázás. A cellaformátum a következő témákat takarja: ¾ számformátum, ¾ igazítás, ¾ betűtípus, ¾ szegély, ¾ mintázat, ¾ védelem. A cellaformázást a „Formátum” menüből, illetve a helyi menüből tudjuk elindítani, vagy a Ctrl-1 billentyű kombinációval (de az egyes számot most az írógépszerű billentyűzeten kell megnyomni). Igazság szerint a formázó eszközsorból is elérhető lehetőségek döntő részét nem a menüből fogjuk majd beállítani. Ennek az oka nyilvánvaló: ikonnal formázni sokkal egyszerűbb. Néha azonban kénytelenek leszünk menüből formázni, ugyanis bizonyos lehetőségek csak ott találhatóak meg. Itt is igaz ugyanis az, ami a Windows alatt majd minden programban: menüből jóval több lehetőségünk van, mint valamilyen gyorsabb elérési lehetőséggel, például a formázó eszközsorral, vagy a helyi menükkel. Természetesen célszerű okokból formázó eszközsor lehetőségeit is itt, a cellaformázással párhuzamosan fogjuk megbeszélni. A helyi menük egyébként véleményem szerint ebben a programban jóval korlátozottabb mértékben használhatók, mint a Word programban, jóformán csak a cellaformázás miatt fogjuk nagyritkán elindítani.

14.1

Igazítás

Az igazításokat valószínűleg a formázó eszközsorral hajtjuk majd végre, ugyanis minden fontosat megtalálunk ott is. Néha azonban olyan speciális igazítási effektusokat is akarunk majd alkalmazni a táblázatunkban, melyek csak a cellaformázásban vannak meg. Előbb azonban nézzük meg a formázó eszközsor lehetőségeit.

14.1.1 Igazítás ikonokkal ¾

balra;

¾

középre;

¾

jobbra;

¾

cellaegyesítés.

Itt a már megtanult igazításon kívül találunk tehát egy „Cellák egyesítésével” (korábbi is. Ezt az ikont majd elsősorban fejlécekverziókban „Oszlopok között középre”) ikont hez fogjuk használni. Hasonló hatást lehet vele elérni, mint a Word programban a táblázatok 416. Ábra: celláinak egyesítésével, és aztán az egyesített cellában történő középre igazítással. Arra ügyelA legfőbb igazítások jünk, hogy amennyiben egy kijelölt tartomány több cellájában is van adat, csak a bal felső cella tartalma marad meg, amire egy hibaüzenet is figyelmeztet. Ez a funkció többszörös kijelöléssel a 97-es verzióban nem működik! Nézzünk konkrét példát, úgy egyszerűbb elmagyarázni. Mint az ábrán is látszik, három oszlop között középen van az első sor tartalma. Ennek eléréséhez a következőket kell végrehajtani: ki kell jelölni azokat a cellákat, melyek között középre akarjuk 417. Ábra: A1-C3 tartomány cellák állítani az egyik cella tartalmát. Ezek után csak rá kell kattintani a ikonra, és kész egyesítésével, majd függőlegesen középre igazítva is a kívánt forma. Újra a ikonra kattintva, ismét felosztásra kerülnek a cellák. A korábbi verziók „oszlopok között középre” igazítását megszüntetni viszont csak menüből lehet! Függőleges középre igazítás több sor között Ha egymás felett lévő cellákat egyesítünk, akkor azok között ugyanúgy középre igazíthatunk szöveget, de csak menüből. Hogy lehet újra normál igazítást kérni az Excel 97 verzióban? Jelöljük ki a kérdéses cellát, majd a „Formátum” menü „Tartalom törlése” pontjából a „Formátumot” opciót kell alkalmazni. Ezzel azonban minden más formázást is törlünk!

265

PC Suli XP alapokon 14.1.2 Igazítás menüvel ¾ „Formátum”, „Cellák…”, „Igazítás” regiszter ½ ¾ Helyi menü, „Cellaformázás…”, „Igazítás” regiszter ½ Az igazítással kapcsolatos plusz lehetőségeket itt találjuk majd meg. Ha a „Cella formázása” ablakot elindítottuk, és az „Igazítás” regiszterfülre kattintunk, akkor a lehetőségek három nagy csoportba vannak bontva. n „A szöveg igazítása” A függőleges igazítási lehetőségeknek akkor van jelentősége, ha a cella magassága nagyobb, mint a benne található betűk nagysága, ikonvagy függőlegesen több cellát „egyesítettünk” például a nal. n „A szöveg elhelyezése” Véleményem szerint a „Sortöréssel több sorba”, és a „Lekicsinyítve, hogy beférjen” egy próba után érthetővé válik. Ehhez gépeljünk egy cellába annyi karaktert, hogy ne férjen be, majd az adatbevitel lezárása után alkalmazzuk a lehetőségeket. ikonnnak. A „Cellák egyesítésével” megfelel a n „Elforgatás” A 97-es Excel verzió óta már nemcsak az írás irányát lehet megadni, hanem tetszőleges szögben döntve is lehetséges az adat megjelenítése. A kis piros jelet kell csak a skálán húzni, vagy a számmezőn kell beállítani a kívánt értéket. Amennyiben azonban szegélye is van a cellának, annak függőleges vonalai is adott szöggel elforgatva jelennek meg. Ezenkívül lehetséges a betűk egymás alá történő el418. Ábra: „Igazítás” menüből helyezése is a „Szöveg” feliratú mezőre történő kattintással. n „Jobbról balra” Ennek csak akkor van jelentősége, ha egy cellába mondjuk arab, vagy héber (azaz jobbról balra írandó) szöveget is írunk a mi szokásainknak megfelelően balról jobbra írandó szöveg mellé. Sortörés billentyűzetről Ha azt szeretnénk, hogy a sortörések ott legyenek, ahol mi akarjuk, akkor a szöveget úgy gépeljük be, hogy a sortörések helyén Alt - Enter billentyűkombinációt adunk meg. Ez esetben a „Sortöréssel több sorba” jelölőnégyzet automatikusan kipipálásra kerül.

113. Feladat: Készítsük el az ábrán lévő táblázatot! A szegélyeket pillanatnyilag még hagyjuk ki. Megjegyzem, ez a feladat a program méltatlan a program tudásához, de néha ilyesmire is fogjuk azért használni az Excelt. Megoldás: Írjuk be A1 cellába „Jelenléti ív”, majd Enter. A2 cellába „Név”, majd két Tabulátor. C2 cellába „Kovács Pál”, Tabulátor, „Szabó Imre”, Tabulátor, „Szalay József”, Tabulátor, „Csanaki Elemér, Enter. Vigyük a cellamutatót az A3 cellába, és gépeljük be „09.06” majd zárjuk le az adatbevitelt a ikonra kattintással. Az autokitöltőt húzzuk le szeptember 17-ig az egér jobb gombjával, majd a megjelenő menüből válasszuk a „Kitöltés munkanapokkal” opciót. Meglepetésünkre 419. Ábra: A következő feladat tovább jelennek meg a napok, mint kértük (mert annyi darab jelenik meg, mint ameddig lehúztuk). Semmi baj, az egér valamelyik gombjával az autokitöltőt most toljuk fel annyira, hogy szeptember 17. még megmaradjon. ikonnal. Fogjuk meg az Most kattintsunkB3 cellába, majd gépeljük be „hétfő”, azután az adatbevitelt most is zárjuk le a autokitöltőt, és húzzuk le szeptember 17-ig, de most is a jobb gombbal. A menüből ez esetbe is a „Kitöltés munkanapokkal” opciót válasszuk. A felesleges napokat szüntessük meg. A 2. sor magasságát a 2. és a 3. sor címe közé állva, az egeret lefelé húzva, állítsuk 48 pontra. Most jelöljük ki a 3-tól a 12. sorig a táblázatot, majd válasszuk ki a „Formátum”, abból a „Sor” menüt, azon belül pedig a

266

XVII. Az Excel XP „Magasság…” opciót, és adjunk meg 30-as értéket. Kattintsunk a

ikonra, a szövegek középre igazításához, valamint a

ikonra a félkövérre állításához. Jelöljük ki A2-B2 tartományt, és egyesítsük a cellákat a állítsuk félkövérre a C2-F2 tartományt. Jelöljük ki A1-F1 tartományt, és egyesítsük a cellákat itt is a félkövérre a

ikonnal, majd a

ikonnal formázzuk ezt is félkövérre. Szintén

ikonnal. Állítsuk át a betű méretet 20 pontosra, és a jellegét

ikonnal.

Jelöljük ki az egész táblázatot, majd válasszuk a „Formátum” menüből a „Cellák…” pontot. Lapozzunk az „Igazítás regiszterre, és válasszuk a „Függőlegesen” legördülő listából a „Középen” elemet. Végül még mindig megtartva a kijelölést válasszuk ki a „Formátum” menüből az „Oszlop”, majd a „Legszélesebb kijelölt” pontot. Ezzel elkészültünk.

14.2

Betűtípus

Tekintsük meg most is két csoportra osztva lehetőségeinket.

14.2.1 Betűk formázása ikonokkal Most ismét csak felsorolom az ikonokat, mert a szövegszerkesztés kapcsán már mindent megtanultunk korábban (XIV. fejezet 2.11.1.): ¾

betűtípus;

¾

betű méret;

¾

félkövér, dőlt, aláhúzott;

¾

betűszín.

14.2.2 Betűk formázása menüből ¾ „Formátum”, „Cellák…”, „Betűtípus” regiszter ½ ¾ Helyi menü, „Cellaformázás…”, „Betűtípus” regiszter ½ A „Cellák formázása” ablaknak ezt a regiszterét nagy valószínűséggel nem, vagy csak nagyon ritkán fogjuk használni. Azért nem lapozunk majd erre a regiszterre, mivel a formázó eszközsorral gyakorlatilag szinte mindent meg lehet valósítani. Magáról a párbeszédablakról sem kívánok bővebben szólni, mivel az eddig tanultak alapján minden teljesen világos. n „Normál font” Talán csak azt kell külön megemlítenem, hogy ezzel a jelölőnégyzettel kiválasztott betűtípus lesz az alapértelmezett (a formázás nélküli cellákban ez fog megjelenni). Két fontos kapcsolódó tudnivalóról azonban említést kell tennem: 1. Ha valakinek csak a „Betűtípus” regiszterfül jelenik meg, akkor a „Cellák formázása” ablakot nem „Kész”, hanem „Beírás”, esetleg „Szerkesztés” üzemmódban indította. 2. Ha szükségünk van rá, akkor egy cellán belül is be lehet állítani eltérő karakterformákat. Ehhez csak ki kell jelölni a formázandó részt a szerkesztőlécen, és úgy formázni. Természetesen, ha ilyenkor a „Cellák formázása” ablakot elindítjuk, akkor a „Betűtípus” lesz csak elérhető! 420. Ábra: „Betűtípus” menüből Befejezésül néhány, a színekkel kapcsolatos gondolat. ¾ Mind ikonból, mind párbeszédablakból állítható a betűk színe. Minthogy azonban fekete-fehér nyomtatón közel sem úgy jelenik meg a lapon a betűk színe, mint ahogy azt szeretnénk, átgondoltan használjuk! ¾ Ha egy színt kiválasztottunk, akkor ugyanazért a színért legközelebb nem kell már lenyitni a ikonhoz tartozó legördülő listát, mert a kis ikonra kattintva a legutóbban használt színt kapjuk. Amíg mást nem választunk, a piros szín tartozik az ikonhoz. Hogy lehet a betűszínt megszüntetni? Ha valamilyen betűszínt kapcsolunk egy cellához, és meg szeretnénk szüntetni a hozzárendelést, akkor az elérhető színek közül az „Automatikus” opciót kell majd választanunk!

267

PC Suli XP alapokon 14.3

Szegély

Nagy valószínűséggel a szegélyek miatt is csak ritkán indítjuk a menüt, hiszen ikonnal szinte minden fontos megvalósítható. Lássuk a lehetőségeket előbb ikonnal, azután menüből.

14.3.1 Szegélyek ikonnal ¾

Most mindössze egy ikonról kell beszélni: szegélyek

Az ikonhoz tartozó legördülő listát lenyitva ugyan más tartalom jelenik meg, mint amit a Word programnál megbeszéltünk, én mégis úgy hiszem, hogy részletesebb magyarázatot felesleges hozzáfűzni.

14.3.2 Szegélyek menüből

421. Ábra: Szegélyek ikonnal

¾ „Formátum”, „Cellák…”, „Szegély” regiszter ½ ¾ Helyi menü, „Cellaformázás…”, „Szegély” regiszter ½ Menüből formázva három igazi plussz lehetőségünk van: ¾ színes szegélyeket húzhatunk; ¾ a cellákat átlósan is áthúzhatjuk; ¾ több „Stílus” közül választhatunk. Ha mégis a „Cellák formázása” ablakból szegélyezünk, akkor a következőket kell tudnunk. n „Vonal” Előbb mindig ezen a részen adjuk meg, hogy milyen legyen a „Stílus” és a „Szín”. n „Szegély” Itt adjuk meg utólag, hogy a szegély mire vonatkozzon. n „Elhelyezés” Itt lehetséges menüből a három legfontosabb lehetőség gyors elérése. A „Belül” csak több cella előzetes kijelölése esetén választható. Hogy lehet a felesleges szegélyeket eltüntetni? Egyszerűen kattintsunk a

ikonra a szegélyek

lenyitása után!

422. Ábra: „Szegélyek” menüből 114. Feladat: Az előző feladatban készített táblázatot lássuk el szegélyekkel. Az aláírásoknak szánt helyek közül egy átlós vonallal húzzuk ki Csanaki Elemér második hetét. Megoldás: Jelöljük ki az A1-F12 tartományt, majd a szegélyek

eszköz segítségével válasszuk ki a „Minden szegély”

Most jelöljük ki az A1-F1, A2-F2, A2-B12, A3-F3 tartományokat, majd a szegélyek körbe”

opciót.

eszközből válasszuk a „Vastag

opciót.

Jelöljük ki F8-F12 tartományt, majd a „Formátum” menüből indítsuk a „Cellák…” pontot. Lapozzunk a „Szegély” regiszterikonjaira. hez. Kattintsunk a két átlós szegély

14.4

Mintázat

Mintázat alatt a cella különféle sraffozását, pontozását és hátterének színezését egyaránt kell érteni. A mintázattal kapcsolatban saját tapasztalatom alapján azt szeretném tanácsolni, hogy a sraffozásokat lehetőleg ne használjuk, mert egy ilyen mintázattal ellátott cella tartalma kinyomtatva nem jól látható. Ezek után lássuk a lehetőségeket.

14.4.1 Mintázat ikonnal Ha nagyon ragaszkodunk valamilyen kiemeléshez, akkor javaslom, hogy csak a szürke, vagy a sárga kitöltőszínt alkalmazzuk (nem véletlenül az utóbbi az alapértelmezés), mert az ilyen színű cella tartalma színesben tényleg feltűnő, és fekete-fehérben kinyomtatva is jól látható marad. ¾ mintázat

268

XVII. Az Excel XP Ha az ikonhoz tartozó lista lenyitása után valamilyen más színt választottunk, akkor ebben az esetben is a legutóbb használt elem kapcsolódik a következőkben az ikonhoz. A formázó eszközsoron azonban sraffozások nem elérhetőek, innét csak a háttérszínt lehet állítani.

14.4.2 Mintázat menüből n n

¾ „Formátum”, „Cellák…”, „Mintázat” regiszter ½ ¾ Helyi menü, „Cellaformázás…”, „Mintázat” regiszter ½ „Szín” Itt adható meg a cella háttérszíne. „Mintázat” Itt pedig a „Mintázat” választható. Alkalmazhatunk különféle sraffozásokat és pontozásokat, melyeknek ráadásul a színe is beállítható. Hogy lehet a felesleges mintázatot és cellaszínt eltüntetni?

Szegélyek ikonra!

14.5

lenyitása után kattintsunk a

Számformátum

Azt az adatok bevitelekor megtárgyaltuk, hogy az Excel az egyes adattípusokhoz (szám, dátum, idő) eleve rendel valamilyen számformátumot. Ha azonban ettől eltérő alakra van szükségünk, akkor a most ismertetésre kerülő eljárások valamelyikét kell alkalmaznunk. 423. Ábra: „Mintázat” menüből

14.5.1 Számformátum ikonnal

A következőkben egy kicsit részletesebben ismertetem az ikonok hatását, mint a többi formázó ikonnál, mert valamivel összetettebbek a lehetőségek is. 3. pénznem: ¾ ezres tagolás; ¾ a Windows „Területi és nyelvi beállítások”-ban megadott pénznemet szúrja be a szám mögé; ¾ két tizedes jegyet ír a szám mögé (ha nincs tizedes, két nullát). 4. százalék: ¾ a cellában lévő számérték százszorosát mutatja az Excel; ¾ a szám mögé a „%” jelet teszi a program; ¾ a számot egészre kerekíti. 5. ezres csoport: ¾ ezres tagolás; ¾ a pénznem és százalékjelet kitörli (ha van); ¾ két tizedes jegyet ír a szám mögé (ha nincs tizedes, két nullát). tizedeshelyek növelése: 6. ¾ a tizedeshelyek számát növeli a program (ez egy képlet esetében az eredmény nagyobb pontosságú kijelzését jelenti). tizedeshelyek csökkentése: 7. ¾ a tizedeshelyek számát csökkenti az Excel (ez egy képlet esetében az eredmény kerekített kijelzését jelenti). Tizedesek viselkedése Ha egy képlet eredménye sok tizedes után sem ad pontos eredményt (pl., mert két irracionális szám hányadosáról van szó), akkor a tizedesek számának növelésével pontosíthatjuk a kijelzést. Ha csökkentjük a tizedesek számát, akkor ugyan a program a matematika kerekítési szabályainak megfelelően kevesebb tizedesjellel jeleníti meg a számot, de változatlanul a pontos értékét tárolja. A tizedesek számát a következőkben megtárgyalásra kerülő speciális számformátumok segítségével is meg lehet adni.

14.5.2 Számformátum menüből ¾ „Formátum”, „Cellák…”, „Szám” regiszter ½ ¾ Helyi menü, „Cellaformázás…”, „Szám” regiszter ½ A „Cellák formázása” ablak elindítása után kattintsunk a „Szám” regiszterfülre. A regiszterfül kiválasztásával egy cella tartalmának alakítására sokkal többféle lehetőséghez jutunk, mintha ikonnal formáznánk.

269

PC Suli XP alapokon n

„Kategória” Minden kategóriában nagyon sok lehetőséget találunk, de ha egyik sem jó, akkor az „Egyéni” kategóriát választva a „Formátumkód” mezőben mi magunk is hozhatunk létre új formátumokat. Mivel ez viszont eléggé bonyolult, ezt a témát csak a kiegészítő információk között tárgyaljuk, ott sem teljes részletességében. Arra külön felhívom a figyelmet, hogy a „Pénzek” kategóriát választva akár cellánként eltérő pénznemeket is megjeleníthetünk. Szám egyéni formátumkód

A formátumkód felépítése attól is függ, hogy milyen típusú adatra akarjuk vonatkoztatni. Ha a „Szám” kategóriában hozunk létre egy új kódot, a kód négy részből állhat. Az első rész a pozitív számok, a második a negatív számok megjelenési formáját határozza meg, a harmadik a 0 érték helyén megjelenő cella képet írja le, a negyedik pedig akkor jut érvényre, ha a cellába nem számot, hanem szöveget viszünk be. A négy kódrészletet pontosvesszővel kell elválasztani. Az első három kódrészletben a következő jelek szerepelhetnek (a negyedikben csak az utolsó kettő): ¾

kódelem #

¾

0

¾

szóköz _ „szöveg” [szín]

¾ ¾ ¾

424. Ábra: „Számformátum” menüből hatása csak akkor jelenik meg az adott helyértéken szám, ha az nem nulla; az adott helyértéken az úgynevezett értéktelen nulla is megjelenik (az egyes helyértékre mindig ezt kell írni, hogy az egynél kisebb számok nulla egész valahány tized, század, és így tovább formában jelenjenek meg); ezres tagoláshoz rakjuk az ezresek közé; a mögé írt karaktert nem jeleníti meg, de a karakter szélességének megfelelő helyet üresen hagy a program; az idézőjelbe írt szöveg jelenik meg; a zárójelbe írt színnel jelenik meg a szám (például fekete, kék, piros, cián, sárga).

Példa szám formátumkódra Például a # ##0,00" méter";[piros]# ##0,00" méter hiány";"nincs készleten" kód hatására a cellákban a következőket látjuk: ¾ ¾ ¾

cella tartalma 13345 -1524 0

cella képe 13 345,00 méter; -1 524,00 méter hiány (piros színnel); nincs készleten. Értéktelen nulla fogalma

Egy nulla akkor értékes, ha elhagyva új számot kapunk. Például a 10 (tíz) számban a nulla értékes, mert elhagyva más számot, az 1-et (egy) kapjuk. Ezzel szemben az 15,10 (tizenöt egész tíz század) nulláját elhagyva a kapott szám 15,1 (tizenöt egész egy tized) pontosan ugyan annyit ér. A gyakorlatban annyit elég megjegyezni, hogy az egyes helyértéken mindig 0 kódot kell megadni, a tizedesjel mögött pedig annyi 0 kódot, ahány tizedes pontosságot megkövetelünk. A tízes, százas, ezres, stb. helyértékeken csak akkor szerepeltessünk 0 kódot, ha vezető nullákat akarunk alkalmazni. Vezető nulla értelmezése Biztos láttunk már olyan bizonylatot, amely 001 alakban jeleníti meg az 1 (egyes) számot. Ennek a célja a visszaélések elkerülése (persze ilyenkor a szám mögött is szokott valami lenni, de ez már nem gond számunkra). Nos, az ilyen nullákat szokás vezető nulláknak nevezni. Dátum egyéni formátumkód Dátum és idő kategóriában a kódoláshoz a következő lehetőségeink vannak: kódelem hatása cella tartalom cella képe éééé az év kiírt évszázaddal 1996.07.08 1996 ¾ éé az év évszázad nélkül 1996.07.08 96 ¾ hhhh a hónap teljes neve 1996.07.08 július ¾ hhh a hónap rövidítése 1996.07.08 júl. ¾ hh a hónap két számjeggyel 1996.07.08 07 ¾ h a hónap egy számjeggyel 1996.07.08 7 ¾ nnnn a hét napja kiírva 1996.07.08 hétfő ¾ nnn a hét napja rövidítve 1996.07.08 h ¾ nn a hónap napja két jeggyel 1996.07.08 08 ¾ n a hónap napja egy jeggyel 1996.07.08 8 A kódolással ennél részletesebben felesleges megismerkednünk, valószínűleg már ezekre sem lesz szükségünk. Azért tárgyaltuk mégis, mert ennek ismeretében a beépített kódok értelmezése sokkal könnyebb. Ne felejtsük, hogy egy dátum, vagy idő az Excel számára szám. Csak azért látjuk dátumként, vagy időként, mert a cellaformátumában az van meghatározva. Erről korábban is tettem már említést annak kapcsán, hogy ha egy cellába beírunk egy dátumot, majd azt valamilyen módon kitöröljük, alapesetben csak a cella tartalma törlődik, formátuma nem. Ez annyit jelent, hogy ha ezek után egy számot írunk be a cellába, az dátumként fog megjelenni. Ha számként akarjuk látni, akkor a cellaformátumát át kell állítani! ¾

Euró pénznem megadása A „Számformátum” regiszteren egyszerűen válasszuk a „Pénznem” kategóriát, majd a „Pénznem” legördülő listából az Eurót. Persze amennyiben majd az Euró lesz már a hivatalos magyar fizetőeszköz, akkor azt a „Területi és nyelvi beállítások” segítségével kell megadnunk, mert akkor a azt kapjuk. Az Euró jelét, mint szöveget, az AltGr-U billentyűkombinációval tudjuk majd bevinni a „Területi és nyelvi beállítások” ablakba.

270

ikonnal is

XVII. Az Excel XP Feltételes formázás Ha egy cella megjelenését a benne lévő adatoktól szeretnénk függővé tenni, akkor használjuk a feltételes formázást, amit a „Formátum” menüben találunk. Ez egy kicsit hasonlít ugyan a számformátumra, de a pozitív, negatív, nulla tartományok helyett mi adhatunk meg intervallumokat, valamint a formázás számformátum helyett a következőkre vonatkozik (sajnos itt számformátumot nem is adhatunk meg): ¾ betűtípus, ¾ szegély, ¾ mintázat. A feltétel megadása jelenti talán az egyetlen, némi magyarázatot is igénylő feladatot. Elsőként azt kell kiválasztani, hogy az mire vonatkozzon. A lehetőségek: ¾ „A cella értéke”, Akkor válasszuk, ha konstansokkal, vagy olyan értékekkel akarjuk a cella mindenkori értékét össze hasonlítani, mely valamely cellában már kiszámolásra került. ¾ „A képlet értéke”. Akkor válasszuk, ha a feltétel egy képlettel számítható ki, és azt itt, a formázásnál akarjuk megadni. Ha a „Cella értéke” feltételt választjuk, akkor nekünk kell megadni az úgynevezett logikai operátort is. Ez a következők valamelyike lehet: ¾ a következők között van; ¾ nincs a következők között; ¾ egyenlő; ¾ nem egyenlő; ¾ nagyobb, mint; ¾ kisebb, mint; ¾ nagyobb, vagy egyenlő; 425. Ábra: A feltételes formázás párbeszédablaka ¾ kisebb, vagy egyenlő. A logikai operátortól függően azután egy, vagy két számot kell még megadni. Ha a „Képlet értéke” feltételt választjuk, akkor egy olyan logikai feltételt kell megadnunk, melynek értéke csak logikai „IGAZ”, és „HAMIS” lehet. A feltétel – mivel valójában ez is egy képlet – mindig egyenlőségjellel kell, hogy kezdődjön. Egy komoly megkötésünk van: a feltétel csak az adott munkalap valamely cellájára vonatkozhat. Ha több feltételt is meg szeretnénk határozni, akkor a „Bővítés>>” gombot kell alkalmaznunk (több feltétel esetén a formátum az első teljesült feltétel szerinti lesz). Ha pedig éppen ellenkezőleg, olyan feltételünk is van, amit törölni szeretnénk, akkor kattintsunk „Törlés…” gombra, majd az ennek hatására megjelenő ablakban válasszuk ki a törlendő feltételt.

115. Feladat: Készítsük el az ábrán látható valutaátváltási táblázatot. Az árfolyamok 2004. márciusiak. A forint ellenértékeket természetesen az Excel programmal számoltassuk ki! Mentsük el „Valutaváltás” néven! 426. Ábra: Megoldás: Egy feltétel törlése Kezdjük a feliratokkal: A1 cellába gépeljük be „Valuta átváltások”, majd nyomjunk két Enter-t. A3-ba gépeljük be „09.6”, majd kattintsunk a ikonra. A cella autokitöltőjét az egér jobb gombjával húzzuk le szeptember 17-ig, majd a felengedés után válasszuk ki a „Kitöltés munkanapokkal” opciót, azután toljuk fel szeptember 17-ig (ha végezzük, érthető lesz, miért van erre szükség). Most B2-be gépeljük be „Valuták” E2-be „Forint”, A14-be „Árfolyamok”, A15, A16, A17 cellákba a megfelelő valutanemek neve „/ Ft”-ot, B15, B16 (A15 pél427. Ábra: Feladat a számformátumokra dául „€/Ft”), B17 cellákba pedig a valuták árfolyamát. Az Euró jelét, mint szöveget, az AltGr-U billentyűkombinációval tudjuk bevinni. A „$” jelet a billentyűzeten is megtaláljuk. Adjunk meg B3-D12 tartományba tetszőleges számadatokat. Adjuk meg a képleteket: Most kattintsunk E3-ba, majd gépeljünk egy „=” jelet. Kattintsunk B3 cellára, majd gépeljünk egy „*” jelet. Most kattintsunk a B15 cellába, majd nyomjuk meg az F4 funkció billentyűt (hiszen az árfolyam fix adatként megadott paraméter), majd kattintsunk a ikonra. Fogjuk meg a E3 cella autokitöltőjét, és húzzuk le a E12 celláig, vagy kattintsunk rá duplát. Kattintsunk F3-ba, majd gépeljünk egy „=” jelet. Kattintsunk C3 cellára, majd gépeljünk egy „*” jelet. Most a B16 cellába katikonra. tintsunk, majd nyomjuk meg az F4 funkció billentyűt (mert ez az árfolyam is fix paraméter), majd kattintsunk a Fogjuk meg a F3 cella autokitöltőjét, és húzzuk le a F12 celláig, vagy kattintsunk rá duplát.

271

PC Suli XP alapokon Kattintsunk G3-ba, majd gépeljünk egy „=” jelet. Kattintsunk D3 cellára, majd gépeljünk egy „*” jelet. Azután a B17 cellába ikonra. Fogjuk meg a G3 cella autokitöltőjét, kattintsunk, majd nyomjuk meg az F4 funkció billentyűt, végül kattintsunk a és húzzuk le a G12 celláig, vagy kattintsunk rá duplát. Formázzuk meg a táblázatot: Jelöljük ki A1-G1, B2-D2, E2-G2, A14-B14 tartományokat (akár egyszerre), majd kattintsunk a

ikonra.

Jelöljük ki A3-A12 tartományt, majd indítsuk el a Ctrl-1 billentyűkombinációval a „Cellák formázása” ablakot, azután lapozzunk a „Szám” regiszterhez. Most válasszuk ki a „Dátum” kategóriából a hónapnév és dátum variációt (március 14.). Jelöljük ki B3-B12 tartományt, majd indítsuk a „Cellák formázása” ablakot. Most is válasszuk a „Szám” regisztert, majd a „Pénznem” kategóriából valamelyik Eurót (a különbség az egyes Eurók között csak abban van, hogy az „€” szimbólum hol jelenik meg). Hasonlóan formázzuk meg a „$” és az „CHF” valutákat is. Jelöljük ki E3-G12, valamint B15-B17 tartományokat. Kattintsunk a

, majd kétszer a

ikonra.

Oszlopszélességek: Jelöljük ki a teljes táblázatot, majd válasszuk a „Formátum” menüből az „Oszlop”, majd a „Legszélesebb kijelölt” opciót. Szegélyek és kitöltés: Jelöljük ki A1-G12, és A14-B17 tartományokat, majd a szegélyek eszköz

lenyitása után válasszuk a mindenhol vékony

szegélyt. Most egyszerre jelöljük ki a következőket: A1-G1, A2, B2-D2, E2-G2, A3-A12, B3-D12, E3-E12, A14-B14, A15-A17, , és a körbe vastag szegélyt alkalmazzuk, majd kattintsunk a ikonra. B15-B17. Nyissuk le a szegélyek eszközt

14.6

Védelem

¾ „Formátum”, „Cellák…”, „Védelem” regiszter ½ ¾ Helyi menü, „Cellaformázás…”, „Védelem” regiszter ½ Az Excelben megvalósítható védelem leginkább egy múzeum védelméhez hasonlítva érthető meg. A múzeum minden helyiségében van egy úgynevezett térérzékelő, ami az adott helyiségben észleli csak a mozgásokat (ezek azok a kis fehér dobozkák, melyek általában a helyiségek egyik sarkában a plafon alatt láthatóak, és mozgásunkra egy kis piros LED villog rajtuk). A térérzékelők külön-külön ki-be kapcsolhatók. Az Excelben ennek a cellánként ki-be kapcsolható „zárolt”-ság felel meg. Alap esetben minden cella zárolt. Akkor hogyan tudunk mégis a cellákba írni? Azért, mert nincs bekapcsolva a munkalap védelme. Ez megfelel az egész múzeumi riasztórendszer főkapcsolójának. Ha ez a főkapcsoló nincs felkapcsolva, a látogatók szabadon mozoghatnak minden helységben. Természetesen felmerül a kérdés: mire jó mindez a gyakorlatban? Ha egy Excel táblázatot előre elkészítünk, beleértve a képletek megadását is, a munka hátralévő részét (a forrásadatok beírását) már jóformán akárki el tudná végezni. Természetesen ekkor a táblázat már kitöltött részét azért joggal félti a táblázat alkotója. Hogy ne lehessen ezeknek a 428. Ábra: „Védelem” celláknak a tartalmát megváltoztatni, be kell kapcsolni a munkalap védelmét. Azoknak a celláknak a tartalma azonban, melyekbe a forrásadatokat várjuk, ekkor is változtatható kell, hogy legyen. Ezért a védelem beállításakor a következőket kell tennünk: ¾ Miután elkészültünk a táblázattal (a képletekkel és a formázással egyaránt) jelöljük ki az összes olyan cellát, ahová később még adatokat kell majd bevinni! ¾ Hívjuk be a cellaformázást a Ctrl-1 billentyűkombinációval, és lapozzunk a „Védelem” regiszterfülre! ¾ Vegyük le a pipát a „Zárolt” jelölőnégyzetről! ¾ Kapcsoljuk be a munkalap védelmet az „Eszközök” menü „Védelem” pontjából a „Lapvédelem…” menüponttal, de előbb vegyük le a pipát a „Zárolt cellák kijelölése” elemről, majd nyomjuk meg az OK gombot. Ha az ekkor kapott újabb ablakban jelszót is megadunk, a védelem kikapcsolása is csak a helyes jelszó meg429. Ábra: A jelszó megadása adása után lesz lehetséges.

272

XVII. Az Excel XP Amennyiben ezt a folyamatot végrehajtottuk, akkor csak azokba a cellákba lehet adatot bevinni, melyeknél a zárolást kikapcsoltuk (más cellát ki sem tudunk jelölni). Ha levettük a pipát a „Zárolt cellák kijelölése” elemről, akkor el tudjuk kezdeni az adatbevitelt, de hibaüzenetet kapunk. Így nem le430. Ábra: Ha zárolt cellába adatot akarunk bevinni, hibaüzenetet kapunk het a táblázatnak az előre megadott részeit elrontani. Ha a védelmet ki akarjuk kapcsolni, ugyanazokat a menüpontokat kell kiválasztani, mint a védelem bekapcsolásakor, azaz: „Eszközök” menü „Védelem” pontjából a „Lapvédelem feloldása…” Ezzel a védelemnek az átlag felhasználó által is használt részét megbeszéltük. Még egy fontos dologra kell a figyelmet felhívni: összekapcsolt munkalapoknál a lapvédelem nem kapcsolható sem ki, sem be. 116. Feladat: Az előző, „Valutaváltás” feladatban készített táblázat védelmét állítsuk be úgy, hogy csak a B3-D12 cellatartományba (sötétszürke színnel jelölt cellák) lehessen adatokat bevinni. Megoldás: Jelöljük ki a B3-D12 cellatartományt, majd indítsuk el a „Cellák formázása” ablakot a „Formátum” menü „Cellák…” pontjával. Lapozzunk a „Védelem” regiszterhez, és vegyük le a pipát a „Zárolt” opcióról, végül nyomjuk meg az „OK” gombot. 431. Ábra: A szürke színű kijelölt cellákba Most nyissuk meg az „Eszközök” menüt, és válasszuk a lehessen csak adatot bevinni „Védelem” menüből a „Lapvédelem…” pontot. A megjelenő ablakban nyomjuk meg az „OK” gombot. Mit jelent a „Cellák formázása” ablakban látható „Rejtett” opció? Azt korábban tanultuk, hogy ha egy cellába képletet írunk, akkor a későbbiekben ott mindig csak a kiszámolt értéket láthatjuk (legalábbis alapesetben). Abban az esetben viszont, hogyha a kérdéses cellát jelöljük ki aktuálisnak, a szerkesztőlécen a képlet mégis leolvasható. Ellenben ha bekapcsoljuk a „Rejtett” opciót, akkor már ott sem. És mit eredményez a „Füzetvédelem” bekapcsolása? Még nem tanultuk a lap műveleteket, mint például munkalapok beszúrása, törlése, áthelyezése, másolása, stb. (XVII. fejezet 20.). Nos, ha a „Füzetvédelem” opciót bekapcsoljuk, akkor a munkalap műveletek nem lesznek elérhetők. További védelem jelszavas mentéssel Ha a munkafüzet megnyitását sem akarjuk mindenkinek megengedni, vagy a megnyitást ugyan igen, de a javítást nem, akkor a fájlt jelszóval védve kell elmenteni. Ezt úgy tehetjük meg, hogy a mentés párbeszédablakban az „Eszközök” legördülő listából kiválasztjuk a „Beállítások” menüpontot. Itt megadhatunk két jelszót, egyet a betekintésre, egyet a módosításra. Ha egy így elmentett dokumentumot azután később megnyitunk, akkor kérni fog egy jelszót. Annak függvényében tekinthetünk csak bele, vagy módosíthatjuk is a munkafüzetet, hogy megnyitáskor melyik jelszót adjuk meg. Kifinomultabb további védelmi lehetőség adatbevitelkor az „Érvényesítés” segítségével Az eddig tanultak csak arra biztosítanak lehetőséget, hogy azt szabályozzuk le, hogy egyáltalán meg lehessen-e nyitni a munkafüzetet, illetve ha van módosítási jogosultságunk is, hová lehessen adatokat bevinni. Ez kétségkívül nagyon fontos, de néha kevés a tökéletességhez. Gondoljunk csak bele, mi történik akkor, ha egy cellába teljesen rossz adatot adunk meg. Nyilván a képletek által kiszámolt eredmények is hibásak lesznek. Egy cella bemenő értékeinek korlátozása Ha azt szeretnénk elérni, hogy egy cellába csak bizonyos feltételeknek megfelelő adatokat lehessen megadni, akkor a következők szerint kell eljárnunk: 1. Lépjünk a kérdéses cellába. 2. Indítsuk el az „Adatok” menüből az „Érvényesítés…” opciót, és lapozzunk a „Beállítások” regiszterhez. 3. Adjuk meg a megfelelő adattípust a „Megengedve:” legördülő listából, ahol a következők közül választhatunk: ¾ Egész szám csak adott feltétel szerinti egész számot adhatnak meg; ¾ Tizedes tört adott feltétel szerinti bármely szám bevihető; ¾ Lista az adat csak adott lista elemei közül adható meg; ¾ Dátum egy feltétel szerinti dátum vihető be; ¾ Idő egy feltétel szerinti idő lehet az adat; ¾ Szöveghossz a bevihető szöveg hossza szabályozható;

432. Ábra: Érvényességi feltételek megadása

273

PC Suli XP alapokon Egyéni egy képletet adhatunk meg, és ahhoz történik a viszonyítás. Adjuk meg a feltétel típusát a „Jelleg:” legördülő lista segítségével, ahonnét a következők közül választhatunk: a következők között van két adatot kell megadni; nincs a következők között két adatot kell megadni; egyenlő egy adatot kell megadni; nem egyenlő egy adatot kell megadni; nagyobb, mint egy adatot kell megadni; kisebb, mint egy adatot kell megadni; nagyobb, vagy egyenlő egy adatot kell megadni; kisebb, vagy egyenlő egy adatot kell megadni. Adjuk meg a feltételhez tartozó adatot, adatokat is. Ez a képleteknél már megismert módon vagy begépeléssel, vagy mutogatással lehetséges. Ha „Lista” lett kiválasztva a „Megengedve:” legördülő listából, akkor húzással adjuk meg azt a cellatartományt, ahol a megengedett listaelemek találhatóak.

¾

4. ¾ ¾ ¾ ¾ ¾ ¾ ¾ ¾

5.

Adatbevitelt megkönnyítő üzenetek megadása Ha egy Excel táblázatnál az adatbevitelt az imént megbeszéltek szerint korlátoztuk, akkor célszerű lehet nemi segítséget adni adatbevitelkor, például az érvényes adatok kijelzésével. Ehhez a következők szerint kell eljárnunk: 1. Lépjünk a kérdéses cellába. 2. Indítsuk el az „Adatok” menüből az „Érvényesítés…” opciót, és lapozzunk a „Figyelmeztető üzenet” regiszterhez. 3. Adjuk meg a figyelmeztetés címét, és magát a figyelmeztetést (az ábrák alapján mindkettő szerepe érthető).

433. Ábra: Bevitelt megkönnyítő üzenet megadása, és az üzenet, ami hatására megjelenik Ha ezek után a kérdéses cellára lépünk, akkor a megadott üzenet jelenik meg a cella mellett. Maga az üzenet egyébként maximum 255 karakter hosszú lehet. Hibaüzenet rendelése elhibázott adatbevitelhez

434. Ábra: Hibaüzenet megadása és maga a hibaüzenet, ha rossz adatot viszünk be 1. 2. 3.

Ha azt szeretnénk, hogy rossz adatbevitel esetén hibaüzenetet küldjön a program, akkor a következők szerint járjunk el:

Lépjünk a kérdéses cellába. Indítsuk el az „Adatok” menüből az „Érvényesítés…” opciót, és lapozzunk a „Hibajelzés” regiszterhez. Válasszuk ki a hibaüzenet „Stílusát”, majd adjuk meg a „Cím” és az „Üzenet” adatokat. Amihez még némi magyarázat szükséges, az a különféle „Stílusok” viselkedése: Stílus

¾

Információ

¾

Megállás

¾

Figyelmeztetés

1. gomb képe és hatása

2. gomb képe és hatása

Beírja a hibás adatot a cellába.

Meghagyja a cella bevitel előtti értékét.

3. gomb képe és hatása

Visszatér az adat szerkesztéséhez. Meghagyja a cella bevitel előtti értékét. Beírja a cellába a rossz adatot.

Meghagyja a cella bevitel előtti értékét. Visszatér az adat szerkesztéséhez.

117. Feladat: Próbáljuk az előző, „Valutaváltás” feladatot kiegészíteni a most tanult lehetőségek segítségével úgy, hogy Euróból csak bizonyos értékhatárokon belüli adatokat fogadjon el az Excel. Az értékhatárok azonban az adatbevitel megkönnyítése érdekében kerüljenek kijelzésre, ha a kérdéses cellákra állunk.

¾ ¾ ¾ ¾

Megoldás: Mivel szorgalmi feladatról van szó, csak címszavakban ismertetem a megoldást: oldjuk fel a védelmet; jelöljük ki az azonos érvényesítési feltétellel ellátandó cellacsoportok közül az elsőt (B3-B12); indítsuk el az „Adatok” menüből az „Érvényesítés…” opciót; a „Beállítások” regiszteren a „Megengedve” legördülő listából válasszuk ki a „Tizedestört”-et, és adjuk meg az értékhatárokat;

274

XVII. Az Excel XP ¾ ¾ ¾ ¾

a „Figyelmeztető üzenet” regiszteren adjuk meg a „Cím” mezőbe „Fontos!” szöveget, a „Figyelmeztetés”, mezőbe pedig, az általunk megadott feltételt fogalmazzuk meg; a „Hibajelzés” regiszteren tegyük meg ugyanezt; nyomjuk meg az „OK” gombot; kapcsoljuk vissza a védelmet.

15. Automatikus statisztikai számítások és más függvények használata Az esetek többségében nem csak az alapműveletekkel (összeadás, kivonás, szorzás, osztás, esetleg hatványozás) kívánunk dolgozni, hanem egyéb matematikai, statisztikai, trigonometriai stb. műveletekre is szükségünk van. Ezeket a műveleteket függvényként tudjuk majd megadni. A függvények többségét a függvényvarázslóval lehet a legegyszerűbben bevinni. Ez alól csak a néhány leggyakrabban használt statisztikai függvény és az összegzés a kivétel, amit a „Szokásos” eszközsoron is megtalálunk.

15.1

Összegzés és automatikus statisztikai számítások

Az automatikus számításokkal pillanatok alatt lehet statisztikai műveleteket végezni. Csak oda kell állni, ahol az eredményt ikonra kattintani. Ha nem összegzést szeretnénk végezni, akkor a ikonnal a látni szeretnénk, majd az összegzéshez a listát le kell nyitni A listában a legfontosabb statisztikai függvényeket találjuk, melyek mindegyikéről részletesebben is lesz szó később (XVI. fejezet 15.2.). ¾ Összeg Szum() ¾ Átlag Átlag() ¾ Darabszám Darab() ¾ Maximum Max() ¾ Minimum Min() Az Excel felismeri, és egy futó pontvonallal jelzi, hogy a kérdéses cellához képest hol helyezkednek el az azok a cellák, amelyekkel a számítást akarjuk végezni. Ha ez nem felelne meg, húzással másik tartományt is kijelölhetünk. Ha a tartomány már jó, zárjuk le az adatbevitelt a korábban tanult módszerek bármelyikével. Használhatjuk azonban az automatikus számításokat úgy is, hogy előbb az adatokat tartalmazó sor-, vagy oszloptartományt jelöljük ki, és azután választjuk ki a megfelelő automatikus statisztikai műveletet. Ekkor az eredmény automatikusan a kijelölt sortartománytól jobbra, illetve az oszloptartomány alatt jelenik meg. Honnét veszi az adatokat az összegzés és az automatikus statisztikai függvények? Válasszuk a dolgokat ketté, ugyanis csak akkor adható rá egyértelmű válasz, ha külön vizsgáljuk az imént feltett két kérdést. Nincs kijelölés Ebben az esetben az Excel elsőként azt nézi meg, hogy az aktuális cella felett (amelyben állva az ikonnal indítottuk a statisztikai függvényt) található-e számokat tartalmazó cella. Ha igen, akkor az első üres, vagy nem számot tartalmazó celláig jelöli ki a függvény argumentumát, amit persze manuálisan felülbírálhatunk. Ha viszont a kérdéses cella felett egyáltalán nincsenek számok, akkor (és csak akkor) a cellától balra lévő cellákkal jár el ugyanígy. Van kijelölés Ha csak egy sor, vagy oszloptartomány van kijelölve, akkor a kijelölést összegzi a korábban leírtaknak megfelelően. De akkor vajon hogy jár el az Excel, ha a kijelölt tartomány több sort és oszlopot ölel át? A válasz egyértelmű: minden oszlopot külön-külön ad össze a kijelölések alá. Ha ott nincs hely (mert a kijelölések alatt is vannak számok, akkor beszúrja az összegeknek szükséges cellákat. Lehet egyszerűbben is kiszámolni bizonyos cellák összegét, átlagát, stb.? Igen. Sokkal egyszerűbben is eljárhatunk, ha csak átmenetileg szeretnénk meghatározni bizonyos cellák átlagát, összegét, megszámolni a tartományban lévő nem üres cellák számát, kikerestetni a maximumot vagy éppen a minimumot. Ilyenkor jelöljük ki a cellákat, majd az állapotsoron kattintsunk a jobb egér gombbal az ábrán jelzett részre, és válasszuk ki a helyi menüből a megfelelő műveletet. A lehetőségek megegyeznek az automatikus számításoknál korábban már felsoroltakkal. Figyelem! Ez a funkció csak kijelöléskor működik! A kijelölés viszont lehet akár többszörös is. Még az sem baj, ha valamelyik terület véletlenül kétszer is kijelölésre kerül.

Itt kell keresni a tárgyalt eszközt

435. Ábra: Egyszerűbb számolási lehetőség

275

PC Suli XP alapokon Jól jegyezzük meg ezt a lehetőséget, mert sokszor fogjuk nagy hasznát venni.

118. Feladat: A korábbi, „Valutaváltás” feladatot egészítsük ki egy „Összesen” sorral és oszloppal. Az összesen oszlopba természetesen csak a forintértékeket kell összeadni. Megoldás: Először is fel kell oldani a lapvédelmet, mert védett munkalapon nem tudjuk megoldani a feladatot. Ehhez válasszuk ki az „Eszközök” menüből a „Védelem”, majd abból a „Lapvédelem feloldása…” opciót. Ugyan van hely az összegnek is, de szúrjunk be még egy sort (a paraméter adatok és a tényleges adatok sohase „érjenek öszsze”). Ezt ugyan még nem tanultuk, de az alábbiak alapján könnyedén megoldható a feladat: ¾ jelöljük ki a 13. sort, majd válasszuk a „Beszúrás” menüből a „Sor” opciót. Most menjünk az A13 cellába, és gépeljük be: „Összesen”. Ugyanígy járjunk el H2 cellával. ikonnal. Az összegzendő cellákat az Excel Jelöljük ki a B2-G12 tartományt, majd indítsuk el az AutoSzumma eszközt a most jól ismeri fel, és a helyes összegeket jó számformátummal beírja minden oszlop alá. ikonra kattintással. Mivel azonban az automatikus Kattintsunk a H3-as cellába, és indítsuk el most is az AutoSzummát a argumentum felismerés nem jó eredményt ad, jelöljük ki, mit is kell összeadnia az Excelnek. Jelöljük ki tehát az E3-G3 tartoikonra. Fogjuk meg az autokitöltőjét a H3-as cellának, majd húzzuk le H13-ig, vagy kattintsunk mányt, majd kattintsunk a rá duplát. A szegélyek és a kitöltések formázását a korábban tanultaknak megfelelően végezzük el!

15.2

Függvények megadása

A függvények segítségével például a következő, gyakran szükséges értékeket határozhatjuk meg: maximum, minimum érték kikeresése, elemek összeszámolása; ¾ számtani, mértani átlag kiszámítása; ¾ feltételes összegzések, adott értékhez tartozó elemek keresése; ¾ stb. A függvényeket négy módszerrel is meg tudjuk majd adni: 1. a már tárgyalt „AutoSzum” listából, melynek utolsó eleme a „További függvények”, 2. a képlet begépelése közben a szerkesztőlécen megjelenő „Függvénylistával”, ami az utoljára használt függvényeket tartalmazza, és amelyben az utolsó elem szintén a „További függvények”, 3. a „Függvényvarázslóval”, 4. vagy a teljes függvény egyszerű begépelésével. Mivel az első két eljárás is valójában a harmadikat, a függvényvarázslót indítja el, a bemutatandó módszerek száma csak kettő. Az Excel több mint 300 függvényt tartalmaz. Az átlag felhasználó ezek közül, legfeljebb ha egy tucatot használ. Szerencsére bármelyikre is van szükségünk, mindegyik függvény hasonlóan adható meg. Példaként nézzük meg, miként lehet megoldani azt, hogy az Excel egy cellatartományból keresse ki a maximumot, majd szintén egy-egy feladaton vagy példán keresztül néhány fontos további függvény is bemutatásra kerül. ¾

15.2.1 Függvényvarázsló Elsőként indítsuk el a függvényvarázslót a szerkesztőlécen lévő ikonnal. A megjelenő párbeszédablakban előbb válasszuk ki a „Választható kategóriák” legördülő listában a megfelelőt, majd a „Függvény neve” listában a konkrét függvényt, azután nyomjuk meg az „OK” gombot. Külön fel szeretném hívni a „Legutóbb használt” kategóriára a figyelmet. Ez a kategória ugyanis a felhasználó szokásainak megfelelően dinamikusan változik. Előbb-utóbb benne lesz a leggyakrabban használt 10 függvény. Így majd nem kell őket a többi kategóriában keresgetni. Ha kiválasztottuk a függvényt, kattintsunk az OK gombra, aminek hatására megjelenik a „Függvényargumentumok” ablak, melyben a függvény argumentumát (értelmezési tartományát) kell megadni. Ezt lehetséges, sőt, célszerű az egérrel végezni. Csak abba az adatbeviteli mezőbe kell kattintani, ahová a cella címet, vagy cellatartományt meg szeretnénk adni (ha egy argumentuma van a függ-

276

436. Ábra: A függvényvarázsló

XVII. Az Excel XP vénynek, akkor még ez sem szükséges). Ezután kattintsunk az argumentumnak megadandó cellára, vagy húzással jelöljük ki a kívánt tartományt. Ha nem férünk a megadandó cellákhoz, akkor a „Függvényargumentumok” ablakot húzzuk el az útból, vagy kattintsunk rá az adatmező végén látható jelre. Ennek hatására a „Függvényargumentumok” ablak eltűnik, pontosabban csak egy adatmezővé zsugorodik. Amennyiben újra meg szeretnénk jeleníteni, kattintsunk az adatmező végén jelre. látható Ha megadtunk minden argumentumot, kattintsunk a „Kész” gombra, minek hatására máris megjelenik a maximum konk437. Ábra: A függvény argumentumának megadása a „Függvényargumentumok” ablakban 438. Ábra: A zsugorított „Függvényargumentumok” ablak rét értéke a cellában. Hasonló módszerrel kell minden függvényt megadni. A függvényvarázsló viselkedése az Excel 97 verzióban Az Excel 97 verzióban a függvényvarázsló jelentős mértékben másként viselkedett. Miután kiválasztottuk a függvényt, az úgynevezett „Képletpaletta” jelent meg, és a függvény argumentumát ott kellett megadni. Mivel a „Képletpaletta” a legtöbb felhasználónak útban volt csak, jobblétre szenderült.

15.2.2 Függvény begépelése Ha egy függvényt elkezdünk begépelni egy képletbe, akkor amint a kezdő zárójelet is beírtuk, megjelenik egy kis címkén az adott függvény teljes szintakszisa. Nincs már más hátra, mint a paraméterek megadása, melyeket természetesen pontosvesszővel kell elvá439. Ábra: lasztani. A címkén megjelenő szintakszis Őszintén bevalva az Excel korábbi verzióiban én nem nagyon szoktam a függvényeket így, begépelve használni, mert általában nem emlékszem a pontos paraméterezési szabályokra (gondolom ezzel nem csak én vagyok így), és ez a szolgáltatás csak az XP verzió egyik kellemes újdonsága. Az Excel XP-ben ráadásul a feliratban az egyes paraméterek, és maga a függvénynév is hiperlinkek. A név linkre kattintva a Súgóban máris elolvashatjuk az adott függvény teljes dokumentációját is. A paraméter linkekre kattintva pedig a cellában is az adott paraméterhez ugrik a program, és azt ki is jelöli (ez csak a már bevitt paraméterkre igaz). Így talán még nem érthető miért is jó ez nekünk, ezért némi magyarázatot fűzök hozzá. Nos, ez akkor hasznos, ha egy több paraméteres függvényt alkalmazunk, és utólag jövünk rá arra, hogy egy korábban már bevitt 440. Ábra: Így kell utólagos paramétert elhibáztunk. Ilyenkor csak rákattintunk a címkén a rossz paraméterre, és javításra használni a címkét a cellában található valódi képletben már oda is ugrik a program úgy, hogy azt egyúttal ki is jelöli. Ez azért jó, mert az új paraméter bevitelével a régi rögtön törlődik. Ha tehát az ábra szerinti szituációban a „[szám2]” hiperlinkre kattintunk, akkor a képletben az „E5:E9” kerül kijelölésre. Ha így begépelés közben a szerkesztőlécen a ikonra kattintunk, akkor is megjelenik a „Függvényargumentumok” ablak. Maga a címke egyébként a szokásos módon, azaz egérrel történő húzással mozgatható. Hogy adhatjuk meg a leggyorsabban a függvényeket? Amennyiben nem olyan függvényt akarunk alkalmazni, amelyik megtalálható a listájában (összeg, maximum, minimum, átlag, darabszám), akkor gépeljük be a függvény nevét, egy kezdő zárójelet, majd kattintsunk a ikonra, és a „Függvényargumentumok” ablakban adjuk meg a paramétereket. Így egy viszonylag részletes segítség is látható a paraméterek megadása közben.

15.2.3 Képletbe, illetve másik függvénybe ágyazott függvények A mindennapi feladatokban egy-egy függvényt gyakran kell a képleten belül alkalmazni. Arra viszont a gyakorlatban csak ritkán van arra szükség (mondjuk ECDL vizsgán, de akkor szinte minden feladatban), hogy a függvényen belül szerepeljen egy újabb függvény, amit beágyazásnak neveznek. Elvileg akár 7 szintig is egymásba ágyazhatóak a függvények. Ilyenkor a belső, beágyalista gombot kell lenyitnunk a szerkesztőléc bal szélén (a felirata mindig az utoljára zott függvény megadásához a alkalmazott függvény neve, tehát nem feltétlenül „Szum”). A továbbiakban a korábban tanultaknak megfelelően kell eljárnunk: ¾ válasszuk ki a megfelelő függvényt; ¾ a „Függvényargumentumok” ablak segítségével, a megbeszélteknek megfelelően adjuk meg a beágyazott függvény argumentumát;

277

PC Suli XP alapokon ¾

¾

kattintsunk a szerkesztőlécre, és folytassuk a képlet bevitelét a beágyazott, tehát a belső függvény zárójele mögött egy pontosvessző begépelésével, aminek hatására a „Függvényargumentumok” ablakban újra a külső függvény paraméterei jelennek meg; fejezzük be a külső függvény paraméterezését, majd ha végeztünk, de csak akkor, kattintsunk a „Kész” gombra.

Ha úgy gépeljük a függvényt, akkor a megfelelő függvénynevet kell beírni, amit persze most is kezdőzárójel követ. Ekkor a függvény címke átvált a beágyazott függvényre, és akkor áll csak vissza a külső függvényre, ha a beágyazott függvény zárójelét bezárjuk. A beágyazás egyébként úgy kikerülhető, hogy a beágyazandó függvényt külön cellában alkalmazzuk, és a külső függvényben csak hivatkozunk ennek az eredményére. Ezt úgy tehetjük meg, hogy ennek a cellának a koordinátáit, mint paramétert adjuk meg. Befejezésül felhívnám a figyelmet arra, hogy bizonyos függvények hibaüzenetet eredményeznek, ha nincs érték azokban a cellákban, melyeket a függvény argumentumának adunk meg. A tényleg gyakran használt függvények közül ilyen például az „Átlag”. Melyik függvényt hogy használjuk? Őszintén megmondva én a súgót csak a legritkább esetben szoktam megnyitni. Most azonban olyan kérdéskörről kezdtünk beszélni, amelynél szinte nélkülözhetetlen. Miért? A beépített függvények száma miatt eleve lehetetlen az összes függvény pontos szintaktikájának ismerete, ráadásul néhány esetben tényleg bonyolult a függvények használata. Miután kiválasztottuk, esetleg begépeltük a megfelelő függvényt, kérjünk segítséget a hiperhivatkozással, vagy gépelés esetén, a függvénycímkén lévő név hiperlinkkel. Az így kapott ablakban nagyon részletes leírását fogjuk megkapni a kérdéses függvénynek. A használat megkönnyítése érdekében a szintakszison kívül több példát is ad a Súgó. Mindezek mellett a kapcsolódó témák megtekintésére is van lehetőségünk, az ablak tetején lévő hiperhivatkozásra történő kattintással.

15.2.4 A leggyakrabban használt függvények A következőkben kategóriákba csoportosítva bemutatásra kerül néhány fontos függvény. Ezek ismerete több mint elegendő a legtöbb gyakorlati feladat megoldásához, valamint a számítógép-kezelő, a szoftver-üzemeltető, illetve az ECDL vizsga letételéhez is. A könnyebb azonosíthatóság érdekében, a magyarázatokban majd félkövéren megismétlem a szintaktikában szereplő paramétereket. Amelyik paraméter nem kötelező, azt szögletes zárójelben (például: „[paraméter]”) szerepeltetem. Véleményem szerint a legfontosabb 23 függvény a következő (a kiegészítő információkban további 21 függvény kerül bemutatásra, illetve a karakteres adatokkal végezhető műveleteknél további 3 már szerepelt (XVII. fejezet 5.3.5)): ¾ összegzés (XVII. fejezet 15.2.4.1.1) SZUM(); ¾ feltételes összegzés (XVII. fejezet 15.2.4.1.2) SZUMHA(); ¾ átlag (XVII. fejezet 15.2.4.2.1) ÁTLAG(); ¾ maximum (XVII. fejezet 15.2.4.2.2) MAX(); ¾ minimum (XVII. fejezet 15.2.4.2.3) MIN(); ¾ számok száma (XVII. fejezet 15.2.4.2.4) DARAB(); ¾ oszlopfelirathoz tartozó érték (XVII. fejezet 15.2.4.3.1) FKERES(); ¾ sorfelirathoz tartozó érték (XVII. fejezet 15.2.4.3.2) VKERES(); ¾ egy érték koordinátája (XVII. fejezet 15.2.4.3.3) HOL.VAN(); ¾ egy táblázat adott koordinátájú értéke (XVII. fejezet 15.2.4.3.4) OFSZET(); ¾ több feltétel együttes teljesülése (XVII. fejezet 15.2.4.4.1) ÉS(); ¾ több feltételből egy teljesülése (XVII. fejezet 15.2.4.4.2) VAGY(); ¾ mi történjen, ha egy feltétel teljesül, és mi, ha nem (XVII. fejezet 15.2.4.4.3) HA(); ¾ feltételeknek megfelelő sorok egy oszlopának összege (XVII. fejezet 15.2.4.5.1) AB.SZUM(); ¾ feltételeknek megfelelő sorok egy oszlopában lévő számok száma (XVII. fejezet 15.2.4.5.2) AB.DARAB(); ¾ feltételeknek megfelelő sorok egy oszlopában lévő számok átlaga (XVII. fejezet 15.2.4.5.3) AB.ÁTLAG(); ¾ feltételeknek megfelelő sorok egy oszlopában lévő számok maximuma (XVII. fejezet 15.2.4.5.4) AB.MAX(); ¾ feltételeknek megfelelő sorok egy oszlopában lévő számok minimuma (XVII. fejezet 15.2.4.5.5) AB.MIN(); ¾ részlet nagysága (XVII. fejezet 15.2.4.6.1) RÉSZLET(); ¾ részletek száma (XVII. fejezet 15.2.4.6.2) PER.SZÁM(); ¾ felvehető hitel nagysága, vagy mai érték (XVII. fejezet 15.2.4.6.3) MÉ(); ¾ megtakarítás értéke, vagy jövőbeni érték (XVII. fejezet 15.2.4.6.4) JBÉ(); ¾ kamatláb meghatározása (XVII. fejezet 15.2.4.6.5) RÁTA().

15.2.4.1 Matematikai és trigonometriai függvények Ez a kategória többségében mindenki által tanult, de nem biztos, hogy megtanult függvényeket tartalmaz. Alkalmazásuk ezért általában csak matematikai problémát szokott jelenteni.

278

XVII. Az Excel XP 15.2.4.1.1 Összegzés (SZUM) Összegzés Æ SZUM(szám1; [szám2]…) A SZUM segítségével a függvény szám1; [szám2]… nevű argumentumaiban megadott számok adhatók össze. A gyakorlatban azonban számok helyett általában inkább tartományokat adunk meg. Több argumentum megadása a legegyszerűbben többszörös kijelöléssel lehetséges (összesen 30 tartományt lehet megadni). Ha nem szám szerepel a függvény így megadott egyik argumentumának valamelyik cellájában, azt a függvény figyelmen kívül hagyja. Az összegzés egyszerűbb megvalósítása az AutoSzum eszközzel korábban már részletesen bemutatásra került (XVII. fejezet 15.1.). Ehhez a

ikont kell alkalmazni.

15.2.4.1.2 Feltételes összegzés (SZUMHA) Feltételes összegzés Æ SZUMHA(tartomány; kritérium; [összegtartomány]) A SZUMHA függvény a tartomány nevű argumentumában megadott tartományban lévő számokat adja össze, amennyiben a szám megfelel a kritérium nevű argumentumban megadott feltételnek. A kritérium idézőjelbe rakott logikai kifejezés és szám lehet. A következőkben lássunk mindegyikre egy-egy példát: ¾ ">350" a függvény csak akkor veszi figyelembe az összegzéshez a tartomány argumentumában megadott adatokat, ha az adott adat nagyobb, mint 350 (amennyiben a „Függvényargumentumok” ablakban adjuk meg a kritériumot, a program automatikusan idézőjelbe rakja azt); ¾ 350 a függvény csak akkor veszi figyelembe az összegzéshez a tartomány argumentumában megadott adatokat, ha az adott adat pontosan 350. Ha megadjuk a harmadik, nem kötelező összegtartomány nevű argumentumot is, akkor az első és a harmadik paraméter egy ugyanolyan méretű és alakú tartomány kell, hogy legyen. Ilyenkor a tartomány paraméterben megadott adatnak nem is kell feltétlenül számnak, vagy logikai kifejezésnek lenni (hiszen az összegtartomány adatai kerülnek összegzésre), és a kritérium lehet szöveg is. Erre is egy példa: ¾ „Péntek” a függvény csak akkor veszi figyelembe az összegzéshez az összegtartomány argumentumban megadott adatokat, ha az adott adat a tartomány argumentumban egy „Péntek” tartalmú cellához tartozik (az összegtartomány és a tartomány argumentumban lévő cellák a helyük szerint vannak egymással kapcsolatban). Ha összegtartomány is megadásra kerül maga az összegzés a következő módon történik: ¾ megnézi, hogy a „tartományban” szereplő 1. adat megfelel-e a kritériumnak; ¾ ha igen, az összegbe beleszámítja az „összegtartomány” 1. adatát; ¾ megnézi, hogy a „tartományban” szereplő 2. adat megfelel-e a kritériumnak; ¾ ha igen, az összegbe beleszámítja az „összegtartomány” 2. adatát; ¾ stb. Hatványozás és négyzetgyökvonás (HATVÁNY, GYÖK) Hatványozás HATVÁNY(szám; kitevő) Négyzetgyökvonás GYÖK(szám) Nem fűzök hozzájuk magyarázatot, mert értelmezésük egyszerű, továbbá mivel a hatványozás a ^ műveleti jellel (AltGr-3, majd szóköz), a gyökvonás pedig törtkitevőjű hatványozással egyszerűbben is megoldható, nem is nagyon fogjuk alkalmazni őket. Például: ¾ A1 cella tartalmának a gyöke =A1^(1/2) (azért kell a zárójel, mert anélkül A1 első hatványát osztja kettővel); ¾ A1 cella tartalma a négyzeten =A1^2; ¾ A1 cella köbgyöke =A1^(1/3) (azért kell a zárójel, mert anélkül A1 első hatványát osztja hárommal); ¾ A1 cella a köbön =A1^3. Szögfüggvények (SIN, COS, TAN, ARCSIN, ARCCOS, ARCTAN) Adott szög szinusza Adott szög koszinusza

SIN(szám) COS(szám)

Adott szög tangense TAN(szám) Ezeknek a függvényeknek a használata is magától értetődő. Egyetlen fontos megjegyzés: a szám nevű paraméterben a szöget radiánban kell megadni. Egy szám mekkora szög szinusza ARCSIN(szám) Egy szám mekkora szög koszinusza ARCCOS(szám) Használatuk szintén könnyű, és az is logikus, hogy a szöget radiánban adja vissza. Talán arra is emlékezünk matematika óráról, hogy egy szög szinusza és koszinusza -1 és 1 közötti érték, ezért a szám paraméter értéke -1 és 1 közé kell, hogy essen. A fokra történő átszámításhoz a kapott értéket szorozzuk meg 180/PI() értékkel, vagy használjuk a rögtön ismertetésre kerülő szintaktikájú FOK() függvényt. Egy szám mekkora szög tangense ARCTAN(szám) Használata mindenben egyezik a másik két arcus függvényre elmondottakkal, azzal a különbséggel, hogy a szám paraméter értéke ez esetben bármekkora lehet.

279

PC Suli XP alapokon Fok és radián átszámítása, Π (FOK, RADIÁN, PI) Átváltás fokról radiánra FOK(szám) Átváltás radiánról fokra RADIÁN(szám) Műszaki számításoknál nagyon gyakran szükséges az alkalmazása ezeknek függvénynek, melyekhez szerencsére nem kell részletes magyarázat. Pi pontos értéke PI() Pi értékét adja vissza, paramétere nincs. Logaritmusok (LOG, LN) Egy szám adott alapú logaritmusa LOG(szám[; alap]) Az első, szám paraméterben megadott pozitív valós számnak, a második, alap paraméterben megadott alapú logaritmusát számolja ki. Ha nem adjuk meg a második paramétert, 10-es alapú logaritmust számol az Excel. Természetes alapú logaritmus LN(szám) A szám paraméterben megadott pozitív valós szám természetes alapú logaritmusát adja. Kerekítés (INT, KEREK, KEREKLE, KEREKFEL) Egy tört szám egész része INT(szám) A szám paraméterben megadott tört szám végéről egyszerűen lehagyja a tizedeseket. Egy tört szám kerekített értéke KEREK(szám; hány_számjegy) A szám paraméterben megadott tört számot a kerekítés szabályainak figyelembe vételével (4-ig lefelé, 5-től felfelé) a hány_számjegy paraméterben megadott érték figyelembevételével kerekíti. Amennyiben a hány_számjegy paraméter értéke: ¾ pozitív hány_számjegy számú tizedesre; ¾ 0 egészre; ¾ negatív -1 esetén 10-re,-2 esetén százra, -3 esetén ezerre (ez könyveléskor gyakori feladat), stb. történik a kerekítés. Egy tört lefelé kerekített értéke

¾ ¾

KEREKLE(szám; hány_számjegy)

Egy tört felfelé kerekített értéke KEREKFEL(szám; hány_számjegy) Hasonlóan működik, mint a KEREK, csak nem a kerekítési szabályainak figyelembe vételével, hanem: KEREKLE mindig lefelé történik a kerekítés; KEREKFEL mindig felfelé történik a kerekítés.

15.2.4.2 Statisztikai függvények Az ebbe a kategóriába tartozó függvények közül a legfontosabbakat inkább az automatikus számításoknál megbeszélt módszerrel fogjuk majd alkalmazni (XVII. fejezet 15.1.).

15.2.4.2.1 Számtani átlag (ÁTLAG) Számtani átlag Æ ÁTLAG(szám1; [szám2]…) Az ÁTLAG segítségével a függvény szám1; [szám2]… nevű argumentumaiban megadott számok számtani átlaga számítható ki. A SZUM függvényhez hasonlóan a gyakorlatban itt is inkább tartományokat adunk meg a számok helyett. Több argumentum megadása a legegyszerűbb többszörös kijelöléssel (természetesen most is összesen 30 tartományt lehet megadni). Ha nem szám szerepel a függvény így megadott egyik argumentumának valamelyik cellájában, azt a függvény figyelmen kívül hagyja. Abban az esetben, ha a megadott argumentumban egyáltalán nincs számot tartalmazó cella #ZÉRÓOSZTÓ! hibaüzenetet kapunk (mivel 0 darab szám átlagát kellene kiszámolnia, amihez nullával kellene osztania az Excelnek, az pedig értelmetlen). A 0 (nulla) értéket tartalmazó cella nem üres! Az átlagszámítás egyszerűbb megvalósítása az automatikus számításoknál már részletesen bemutatásra került (XVII. fejezet 15.1.). Bizonyára emlékezünk rá, hogy ehhez a

ikont kell lenyitni, majd kiválasztani az „Átlag” elemet. Mértani átlag (MÉRTANI.KÖZÉP)

Mértani átlag MÉRTANI.KÖZÉP (szám1; [szám2]…) Az MÉRTANI.KÖZÉP függvény segítségével a szám1; [szám2]… nevű argumentumaiban megadott számok mértani átlaga számítható ki. A használata szinte mindenben megegyezik a számtani átlaggal, de az argumentumában nem lehet negatív szám. Az alkalmazott képletben összeszorozza az argumentumban megadott értékeket, és annyiadik gyököt von, ahány szorzótényezőt figyelembe tudott venni (csak számokkal számol a képletben).

15.2.4.2.2 Legnagyobb érték (MAX) Legnagyobb érték Æ MAX(szám1; [szám2]…) A MAX segítségével a függvény szám1; [szám2]… nevű argumentumaiban megadott számok közül a legnagyobb érték kerestethető ki. Ezt a függvényt is pontosan úgy kell alkalmazni, mint az ÁTLAG függvényt. A MAX függvény abban az esetben, ha a megadott argumentumban nincs számot tartalmazó cella, 0 eredményt ad.

280

XVII. Az Excel XP Gyakorlati feladatokban általában azt is meg kell határozni, hogy a maximum érték melyik magyarázó sorfelirathoz tartozik. Például egy táblázat tartalmazza egy hét pénzátváltási adatait, és az a kérdés, melyik napon volt a legnagyobb a forgalom. Ennek a megoldása csak több függvénnyel lehetséges (erre kicsit később konkrét feladatot fogunk nézni): ¾ a MAX függvénnyel kikerestetjük a legmagasabb értéket; ¾ a HOL.VAN függvénnyel meghatározzuk, hogy a kikeresett érték a tartomány hányadik sorában szerepel; ¾ végül az OFSZET (eltolás) függvény segítségével a tartomány bal felső sarkától indulva meghatározzuk az első oszlopnak az imént a HOL.VAN függvénnyel megkapott értékű sorában szereplő adatot. A ikonnal az automatikus argumentum felismerés most is használható (XVII. fejezet 15.1.). Lenyitása után most a „Maximum” elemet kell választani.

15.2.4.2.3 Legkisebb érték (MIN) Legkisebb érték Æ MIN(szám1; [szám2]…) A MIN segítségével a függvény szám1; [szám2]… nevű argumentumaiban megadott számok közül a legkisebb érték kerestethető ki. Minden másban egyezik a MAX függvénnyel. Megadása egyszerűbb a

ikonnal (XVII. fejezet 15.1.), melynek lenyitása után most a „Minimum” elemet kell választani.

15.2.4.2.4 Számok száma (DARAB) Számok száma Æ DARAB(érték1; [érték2]…) A DARAB segítségével megszámoltatható, hogy a függvény érték1; [érték2]… nevű argumentumaiban hány szám szerepel. A gyakorlatban számok helyett általában inkább tartományokat adunk meg. Több argumentum megadása a legegyszerűbben természetesen most is többszörös kijelöléssel lehetséges (összesen 30 tartományt lehet megadni). Ha nem szám szerepel a függvény így megadott egyik argumentumának valamelyik cellájában, azt a függvény figyelmen kívül hagyja. Amennyiben az ilyen elemeket is össze szeretnénk számoltatni, alkalmazzuk a DARAB2 függvényt. Az automatikus argumentum felismerés most is használható (XVII. fejezet 15.1.). Miután a tőségeket, most a „Darabszám” pontra kattintsunk.

ikonnal megnyitottuk a lehe-

Elemek száma (DARAB2) Elemek száma DARAB2 (érték1; [érték2]…) Mindenben egyezik a DARAB függvénnyel, csak az argumentumában szereplő számok mellett a szöveget és a logikai értékeket is megszámolja, beleértve az üres szöveget is. Az üres szöveg megadása így lehetséges: "" (azaz két idézőjel, közte semmi, még szóköz sem). Üres elemek száma (DARABÜRES) Üres elemek száma DARABÜRES (érték1; [érték2]…) Azt mondhatnánk, pont az ellenkező logikával dolgozik, mint az előző DARAB2 függvény, mert az üres értékeket számolja meg. Az üres szöveget beleveszi a számításba, de a 0 értéket nem. Nem üres elemek száma (DARABTELI) Nem üres elemek száma DARABTELI (tartomány; [kritérium]) Hasonlóan működik, mint a DARAB függvény. A különbség annyi, hogy míg annál minden számot tartalmazó cella megszámolásra került, most megadható a kritérium paraméterben egy feltétel, hogy mely értékeket számolja csak meg a függvény. A feltétel megadására pontosan olyan szabályok vonatkoznak, mint amit a SZUMHA függvénynél megbeszéltünk (XVII. fejezet 15.2.4.1.2).

169. Feladat (ÁTLAG, MAX, MIN): A pénzváltás táblázatot egészítsük ki az „Összesen” sor alatt három további sorral, ahová számítassuk ki az átlagot, valamint kerestessük ki a mindenkori maximumot és minimumot! Megoldás: Üres sorok beszúrása, feliratok megadása: Mivel már nincs elég sorunk, szúrjunk be 3 sort. Ezt ugyan még nem tanultuk, de a lépéseket követve könnyen meg tudjuk oldani, annál is inkább, mert egy korábbi feladatban is szerepelt már (mivel védett a munkalap, előtte ne felejtsük el feloldani a védelmet): ¾ jelöljük ki a 14. sort; ¾ a „Beszúrás” menüből válasszuk ki a „Sor” opciót, majd ugyanezt ismételjük meg még kétszer. Elsőként most is a feliratokat adjuk meg. Az „Összeg” felirat alá az A14, A15, A16 cellákba gépeljük be rendre az „Átlag”, a „Maximum”, és a „Minimum” feliratokat. Számítások: Jelöljük ki a B2-H12 tartományt, majd indítsuk el az automatikus statisztikai számítások közül az átlagot úgy, hogy a ikonnal lenyitjuk a választékot, és az „Átlag” elemre kattintunk. A program az átlagokat számformátummal együtt beírja minden oszlop alá.

281

PC Suli XP alapokon Jelöljük ki ismét a B2-B12 tartományt, és hasonlóan az előzőhöz válasszuk a Maximumot. Befejezésül a helyes kijelölés megadása után kerestessük ki a Minimumot. Formátumok: A formátumokkal nem sok munka van, mert csak a rácsvonalakat kell megrajzolni, amit a korábban tanultaknak megfelelően végezzünk el!

15.2.4.3 Mátrix függvények A mátrix kategóriába tartozó függvények szinte kivétel nélkül kereső függvények (az általunk tárgyaltak mind azok). Ezt úgy kell érteni, hogy egy adattartományban adott értéket, vagy annak valamilyen paraméterét lehet a segítségükkel megállapítatni.

15.2.4.3.1 Magyarázó oszlopfelirathoz tartozó érték (FKERES, azaz függőleges keresés) Magyarázó oszlopfelirathoz tartozó érték Æ FKERES(keresési_érték; tábla; oszlop_szám[; tartományban_keres]) Az FKERES segítségével a keresési_érték paraméterben megadott adatot tudjuk kikerestetni a tábla paraméterben megadott tartomány első oszlopában, majd annak sorában megállapítatni az oszlop_szám paraméterben megadott oszlopában lévő értéket. A negyedik, tartományban_keres paraméterrel azt adhatjuk meg, hogy pontos egyezést kérünk-e. Ha IGAZ értéknek adjuk meg, vagy elhagyjuk, akkor nem kell pontos egyezés. Ilyenkor az utolsó olyan értéket találja meg, mely még kisebb a keresési_érték paraméterben megadott adatnál (csak rendezett táblában működik helyesen). Az FKERES függvényt három módon használhatjuk: ¾ rendezett tartományban kerestetjük az adott értéknél még kisebb, vagy legfeljebb egyenlő legnagyobb értéket (I. példa); ¾ rendezett tartományban kerestetünk pontos egyezéssel (II. példa); ¾ rendezetlen tartományban kerestetünk pontos egyezéssel (III. példa). Ez így persze aligha emészthető elsőre, de lássunk mindegyik esetre egy-egy példát. I. példa: egy adott értéknél még kisebb utolsó adatot kerestetjük Egy táblázat A1:C15 celláiban tároljuk egy használtautó kereskedés autóinak adatait, ár szerinti növekvő sorrendben, mégpedig az egyes oszlopokban a következőket: ¾ ár A oszlop; ¾ megnevezés B oszlop; ¾ rendszám C oszlop. Ha arra vagyunk kíváncsiak, hogy 3 000 000 Ft-ért mi a típusa a legdrágább autónak, amit még meg tudunk venni, akkor az FKERES függvényt a következő módon kell paraméterezni: ¾ FKERES(3000000;A1:C15;2) Æ Ford Focus ¾ keresési_érték 3 000 000; ¾ tábla A1:C15, tehát az a tartomány, amelyben az adatok vannak, és amelynek első oszlopában keres a 441. Ábra: I. példa az FKERES alkalmazására függvény; ¾ oszlop_szám 2, mivel a megadott tartományon belül a 2. oszlopban szerepel az autó típusa. II. példa: a rendezési kulcs oszlopában kerestetünk egy pontosan egyező adatot Egy másik táblázat A1:C15 celláiban tároljuk egy raktár adatait, cikkszám szerinti növekvő sorrendben, mégpedig az egyes oszlopokban a következőket: ¾ cikkszám A oszlop; ¾ megnevezés B oszlop; ¾ pillanatnyi készlet C oszlop; ¾ mennyiségi egység D oszlop. Amennyiben arra vagyunk kíváncsiak, hogy az adott „52324” cikkszámú termékből mennyi van raktáron, akkor az FKERES függvényt a következő módon kell paraméterezni (most megadandó a tartományban_keres paraméter, mert pontos egyezés szükséges): ¾ FKERES(52324;A1:C15;3;HAMIS) Æ 231 442. Ábra: II. példa az FKERES alkalmazására ¾ keresési_érték 52324 Idézőjel csak akkor szük-

282

XVII. Az Excel XP séges, ha a cikkszám szövegként van tárolva, azaz a cikkszámokat például aposztróf jellel kezdve adtuk meg (’ ami Shit-1); ¾ tábla A1:C15, tehát az a tartomány, amelyben az adatok vannak, és amelynek első oszlopában keresni kell; ¾ oszlop_szám 3, mivel a megadott tartományon belül a 3. oszlopban szerepel a pillanatnyi készlet; ¾ tartományban_keres HAMIS, mivel most pontos egyezést kérünk. III. példa: nem a rendezési kulcs szerinti oszlopban kerestetünk egy pontosan egyező adatot Ha ugyanazokból az adatokból viszont arra vagyunk kíváncsiak, hogy a „Sertéslapocka” nevű termékből mennyi van raktáron, akkor az FKERES függvényt a következő módon kell paraméterezni: ¾ FKERES("Sertés lapocka";B1:C15;2;HAMIS) Æ 123 ¾ keresési_érték "Sertés lapocka"; ¾ tábla B1:C15, tehát az a tartomány, amelyben az adatok vannak, de most B oszlop az első oszlopa a tartománynak, mert az FKERES mindenképpen a tartomány első oszlopában keres; ¾ oszlop_szám 2, mivel a megadott tartományon belül a 2. oszlopban szerepel a pillanatnyi készlet (hiszen az „A” oszlopot most nem vehettük bele a kijelölésbe, mert a függvény az első oszlopban keres); ¾ tartományban_keres HAMIS, mivel pontos egyezést kérünk.

15.2.4.3.2 Magyarázó sorfelirathoz tartozó érték (VKERES, azaz vízszintes keresés) Magyarázó sorfelirathoz tartozó érték Æ VKERES(keresési_érték; tábla; sor_szám[; tartományban_keres]) Az FKERES segítségével a keresési_érték paraméterben megadott adatot tudjuk kikerestetni a tábla paraméterben megadott tartomány első sorában, majd annak oszlopában megállapítatni a sor_szám paraméterben megadott sorában lévő értéket. A negyedik, tartományban_keres paraméterrel azt adhatjuk meg most is, hogy pontos egyezést kérünk-e. Ha IGAZ értéknek adjuk meg, vagy elhagyjuk, akkor nem kell pontos egyezés. Ilyenkor az utolsó olyan értéket találja meg, mely még kisebb a keresési_érték paraméterben megadott adatnál (csak rendezett táblában működik helyesen). Azt mondhatjuk, hogy az FKERES 90 fokos elforgatásával kapjuk a VKERES függvényt. Mivel ennek megfelelően a két függvény használata szinte megegyezik, csak egy feladatot nézünk majd rá, példákat nem.

15.2.4.3.3 Egy érték koordinátája (HOL.VAN) Egy érték koordinátái Æ HOL.VAN(keresési_érték; tábla [egyezés_típusa]) A HOL.VAN segítségével a keresési_érték paraméterben megadott adatot tudjuk kikerestetni a tábla paraméterben megadott egy soros, vagy egy oszlopos tartományban. A harmadik, egyezés_típusa paraméterrel az egyezés jellegét befolyásolhatjuk. A paraméter háromféle megengedett értéke (ha a táblának rendezettnek kell lennie, arra és a rendezés irányára utalok): ¾ 1 az adott értéknél kisebb, vagy egyenlő legnagyobb érték keresése (emelkedő sorrendű rendezettség szükséges, ha nem adjuk meg az egyezés_típusa paramétert, ez az alapértelmezés); ¾ 0 pontosan egyező érték keresése (rendezettség nem szükséges); ¾ -1 az adott értéknél nagyobb, vagy egyenlő legkisebb érték keresése (csökkenő sorrendű rendezettség szükséges). Példa: Egy táblázat A1:C15 celláiban tároljuk egy raktár adatait, cikkszám szerinti növekvő sorrendben, mégpedig az egyes oszlopokban a következőket: ¾ cikkszám A oszlop; ¾ megnevezés B oszlop; ¾ székhelyen eladott C oszlop; A táblázatot úgy helyeztük el, hogy a magyarázó feliratok az 1. sorban, az első tételsorok pedig a 2. sorban szerepelnek. Szeretnénk megtudni, hogy a „52324” cikkszá443. Ábra: Példa a HOL.VAN alkalmazására mú termék hányadik sorban szerepel (ennek látszólag nem sok értelme van, de később látjuk, hogy mégis milyen hasznos lesz). Ehhez a következő paraméterezés szükséges: ¾ HOL.VAN(52324;A2:A15;0) Æ 9 ¾ keresési_érték 52324 (idézőjel csak akkor szükséges, ha a cikkszám szövegként van tárolva); ¾ tábla A2:A15, hiszen a tábla paraméterben megadott tartomány csak egy oszlopos, vagy egy soros lehet (most nem kell kijelölni semmilyen magyarázó feliratot);

283

PC Suli XP alapokon egyezés_típusa

¾

0, amit meg kell adni, mert rendezett ugyan a tábla paraméterben megadott tartomány, de pontos egyezés szükséges.

15.2.4.3.4 Egy táblázat adott koordinátájú értéke (OFSZET) Egy táblázat adott koordinátájú értéke Æ OFSZET(hivatkozás; sor; oszlop [;magasság]) [;szélesség]) Az OFSZET segítségével a hivatkozás paraméterben megadott kiindulási pontból lefelé a sor, és jobbra az oszlop paraméterben megadott helyen lévő cella tartalmát kapjuk vissza. Ha a negyedik, magasság, és az ötödik szélesség paramétert is megadjuk, akkor egy tartományt ad vissza. Ennek azonban csak akkor van értelme, ha egy olyan függvénybe ágyazzuk bele, melynek az argumentuma tartomány. Lássunk erre két példát. I. példa: nem szerepel a 4. és 5. paraméter Egy táblázat A1:B27 celláiban tároljuk az angol ABC nagybetűinek ASCII kódjait, az alábbiak szerint: ¾ az angol ABC nagy betűi A oszlop; ¾ a neki megfelelő ASCII kód B oszlop; A táblázatot úgy helyeztük el, hogy az 1. sorban a magyarázó feliratok, a 2. sorban pedig az első betű és ASCII kód pár szerepel. Azt szeretnénk megtudni, hogy a 3. betűnek (ez a C) mi a kódja. Ehhez a következő paraméterezés szükséges: ¾ OFSZET(A1;3;1) Æ 67 ¾ hivatkozás A1 (innét fog jobbra és lefelé lépni a 2. és 3. paraméterben megadott értékeket); ¾ sor 3, mert A1-tól 1-el lefelé az „A”, 2-vel lefelé a „B”, 3-al lefelé pedig a „C” található; ¾ oszlop 1, mer A1-től 1-el jobbra, a B oszlopban szerepelnek az ASCII kó444. Ábra: doknak megfelelő karakterek. Példa az OFSZET II. példa: szerepel a 4. és 5. paraméter alkalmazására Egy táblázat A1:C15 celláiban tároljuk egy raktár adatait, cikkszám szerinti növekvő sorrendben, mégpedig az egyes oszlopokban a következőket: ¾ cikkszám A oszlop; ¾ megnevezés B oszlop; ¾ székhelyen eladott darabszám C oszlop; ¾ Pécsett eladott mennyiség D oszlop; ¾ Győrben eladott mennyiség E oszlop. A táblázatot úgy helyeztük el, hogy az 1. sorban a magyarázó feliratok, a 2. sorban pedig az első tételsor szerepel. Ez eddig megegyezik a HOL.VAN példa feladatával (XVII. fejezet 15.2.4.3.3). Azt szeretnénk megtudni, hogy a 9. adatsorban szereplő „52324” cikkszámú termékből összesen mennyit adtak el a három városban. Ehhez a következő paraméterezés szükséges: ¾ OFSZET(A1;9;2;1;3) beágyazva egy SZUM-ba, azaz SZUM(OFSZET(A1;9;2;1;3)) Æ 760 ¾ hivatkozás A1 (innét fog jobbra és lefelé lépni a 2. és 3. paraméterben megadott értéket); ¾ sor 12, mert A1-tól 1-el lefelé van az 1-es adatsor, 2-vel lefelé a 2-es adatsor, stb.; ¾ oszlop 2, mer A1-től 2-vel jobbra, a C oszlopban kezdődnek az eladott darabszámok; ¾ magasság 1, mert 1 sor magasságú területet kell figyelembe venni a SZUM függvénynek; ¾ szélesség 3 mert 3 oszlop (C, D, és E oszlopok) szélességű területet kell figyelembe venni a SZUM függvénynek. Csak beágyazva fog működni! A 9 begépelése helyett megtehetjük, hogy a HOL.VAN függvénnyel (XVII. fejezet 15.2.4.3.3) előbb megállapítatjuk a „52324” cikkszám helyzetét, és mint paramétert adjuk meg (akár beágyazott függvényként). ¾ SZUM(OFSZET(A1; HOL.VAN(52324;A2:A15;0);2;1;3)) Æ 760 Hát ez így már bizony elég rémisztő, de akit megnyugtat, én sem szoktam tudni kapásból megoldani az ilyen összetetten beágyazott függvényeket. Helyette alkalmazzuk a három függvényt amennyire csak lehet külön-külön cellában, és a cellákra hivatkozzunk, ahol kell (a SZUM-ba mindenképpen be kell ágyazni az OFSZET-et). Persze most is legyenek magyarázó feliratok. Egy lehetséges megoldás: ¾ G1 „Mit keres?”; ¾ G2 üres; ¾ G3 „Helyezése”; ¾ G4 HOL.VAN(G2;A2:A15;0); ¾ G5 „Összesen eladva”;

284

XVII. Az Excel XP G6 SZUM(OFSZET(A1;9;2;1;3)). A keresett cikkszámot a G2 cellában kell megadni. Ez már tényleg egy elegáns megoldás, legfeljebb még azt kellene megoldani, hogy ha nem létező cikkszámot adunk meg, akkor hibaüzenetet adjon – gondolnánk. De ez sem feladat, mert a HOL.VAN eleve a „#HIÁNYZIK” hibaüzenetet küldi, ha nem találja meg a keresett adatot. 170. Feladat (FKERES, HOL.VAN, OFSZET): A korábban elkészült táblázatból már leolvasható a mindenkori maximum és minimum érték, de az sajnos nem, hogy az adat mely dátumhoz kapcsolódik. Ezért a meglévő táblázat mellé, mondjuk a J oszloptól, kerestessük ki az „Összesen” oszlop szélső értékeit.

¾

445. Ábra: A megoldandó feladat A feladatra két megoldást is nézünk. I. megoldás Most is a feliratokkal kezdjük: Vegyük le a védelmet. A J1 cellába írjuk be: „A szélső értékekhez tartozó dátumok”. Zárjuk le az adatbevitelt Enter billentyűvel, és a J2 cellába írjuk be „Maximális átváltás napja”, Enter, a J3-ba pedig „Minimális átváltás napja” és Enter. A függvények megadása: Mielőtt a képleteket bevinnénk, néhány gondolat az alkalmazandó FKERES függvénnyel kapcsolatban. Az FKERES a következőket tudja: ¾ ha kijelölünk egy tartományt; ¾ és megadunk egy értéket; ¾ a tartomány bal szélső oszlopában megkeresi a megadott értéket; ¾ majd a megtalált érték sorában a tartomány n-edik oszlopában (n értékét mi adjuk meg) található értéket kiolvassa. Ez szempontunkból most azt a problémát veti fel, hogy megkeresi majd ugyan a függvény a maximális értékhez tartozó sort, de mivel a hozzátartozó magyarázó felirat tőle balra van, így nem vehetjük bele a kijelölésbe (mert az első oszlopban keres), ezért nem is tudja visszaadni az értékhez tartozó napot. A megoldás az lehet, hogy a magyarázó feliratokat a táblázat jobboldalára is beszúrjuk (ha ott zavar, akkor majd a végén elrejtjük). Egy elegánsabb módszert a II. megoldásban fogunk látni. A magyarázó feliratnak szúrjunk be egy oszlopot. Ehhez jelöljük ki a J oszlopot, majd válasszuk ki a „Beszúrás” menüből az „Oszlopok” opciót. Most másoljuk át a magyarázó feliratokat. Jelöljük ki az A3-A12 tartományt, majd kattintsunk a cellába, és most a

ikonra. Menjünk az I3

ikonra kattintsunk. Ezzel készen is van a másolás.

Menjünk az L2 cellába, és indítsuk el a függvényvarázslót a ikonnal. Válasszuk ki a „Mátrix” kategóriából az „FKERES” függvényt, majd kattintsunk az „OK” gombra. Adjuk meg a függvény argumentumait a következők szerint: ¾ elvileg a „Keresési_érték” mezőben villog a szövegkurzor, mi pedig kattintsunk a H15 cellába; ¾ kattintsunk a következő, „Tábla” nevű mezőbe, majd jelöljük ki H3-I12 tartományt; ¾ kattintsunk az „Oszlop_szám” mezőbe, majd írjuk be: 2 (egy kettes számjegy); ¾ kattintsunk a „Tartományban_keres” mezőbe, és írjuk be: hamis. Befejezésül kattintsunk a „Kész” gombra. ikonnal. Válasszuk ki most is az „FKERES” függMenjünk az L3 cellába, és indítsuk el most is a függvényvarázslót a vényt, majd kattintsunk az „OK” gombra. Adjuk meg az argumentumokat az alábbi lépésekkel:

285

PC Suli XP alapokon elvileg a „Keresési_érték” mezőben villog a szövegkurzor, mi pedig kattintsunk a H16 cellába; kattintsunk a „Tábla” nevű mezőbe, majd jelöljük ki most is H3-I12 tartományt; ¾ kattintsunk az „Oszlop_szám” mezőbe, majd írjuk be: 2 (egy kettes számjegy); ¾ kattintsunk a „Tartományban_keres” mezőbe, és írjuk be: hamis. Befejezésül kattintsunk a „Kész” gombra. Mint látjuk a két argumentum, szinte megegyezik, csak a keresett értékek különböznek. Formátum: Egy bökkenő van csak, a képletek helyén számokat, és nem pedig dátumot látunk. De emlékezzünk csak: a dátumokat az Excel számként kezeli, és csak a formátumuk miatt jeleníti meg őket dátumként! Jelöljük ki az L2-L3 tartományt, majd a cellaformázást elindítva („Formátum”, „Cellák…”) lapozzunk a „Szám” regiszterhez. A „Dátum” kategóriából válasszuk ki a „március 14.” formát. Ezzel gyakorlatilag már készen vagyunk, csak néhány „csinosító” művelet van hátra. Rejtsük el az I oszlopot! Ehhez jelöljük ki, majd a „Formátum” menüből válasszuk az „Oszlop”, végül az „Elrejtés” opciókat. Befejezésül adjuk meg a szegélyeket, ha kell, állítsuk véglegesre az oszlopszélességeket, és rakjuk vissza a lapvédelmet. II. megoldás Ez a megoldás elegánsabb, de talán nehezebben érthető. Feliratok: Ugyanaz, mint az I. megoldásban. A függvények megadása: Menjünk a K2 cellába (mivel most nem kellet beszúrni egy oszlopot, most vagyunk a képlet helyén), majd indítsuk el a függvényvarázslót a ikonra kattintással. Válasszuk ki a „Mátrix” kategóriából az „OFSZET” függvényt, majd kattintsunk az „OK” gombra. A „Függvényargumentumok” ablakban adjuk meg az argumentumokat (melyek között további függvények is lesznek) az alábbiak szerint: ¾ elvileg a „Hivatkozás” mezőben villog a szövegkurzor, mi pedig kattintsunk a H2 cellába; ¾ kattintsunk a következő, „Sorok” nevű mezőbe, majd a szerkesztőlécen nyissuk le a ikont, és válasszuk a „További függvények…” elemet; ¾ válasszuk ki a „Mátrix” kategóriából a „HOL.VAN” függvényt, majd a „Függvényargumentumok” ablakban adjuk meg a beágyazott függvény argumentumait is; ¾ most a „Keresési_érték” mezőben villog a kurzor, mi pedig kattintsunk a H15 cellára; ¾ kattintsunk a második, „Tábla” argumentumba, majd jelöljük ki a H3-H12 tartományt; ¾ az „Egyezés_típusa” argumentumot állítsuk be 0-ra; ¾ kattintsunk a szerkesztőlécen a pillanatnyi képlet végén lévő két befejező zárójel közé, majd gépeljünk egy pontosveszszőt; amivel visszatértünk a külső „OFSZET” függvényhez; ¾ az „Oszlopok” paraméternek adjunk meg -7 értéket (mert 7 oszloppal balra van a keresett felirat). Befejezésül kattintsunk a „Kész” gombra. ikon segítségével válasszuk ki a „Mátrix” kategóriából az „OFSZET” függvényt, majd kattintMenjünk a K3 cellába és a sunk az „OK” gombra. Adjuk meg most is a függvények argumentumait: ¾ a „Hivatkozás” értéke legyen H2; ¾ kattintsunk a következő, „Sorok” nevű mezőbe, majd a szerkesztőlécen nyissuk le a ikont, és válasszuk a „További függvények…” elemet; ¾ válasszuk ki a „Mátrix” kategóriából a „HOL.VAN” függvényt; ¾ a „Keresési_érték” legyen H15; ¾ a „Tábla” legyen H3-H12; ¾ az „Egyezés_típusa” legyen 0; ¾ kattintsunk a szerkesztőlécen a pillanatnyi képlet végén lévő két befejező zárójel közé, majd gépeljünk egy pontosvesszőt, amivel ismét visszatértünk a külső „OFSZET” függvényhez; ¾ az „Oszlopok” legyen most is -7. Befejezésül most is kattintsunk a „Kész” gombra. Formátum: A formátumot adjuk meg úgy, mint az I. megoldásban. 171. Feladat (VKERES): Készítsünk egy olyan táblázatot, mely kiszámolja a havi SZJA előleget. Megoldás: A megoldás során olyan részeredményeket is kiszámolunk és megjelenítünk a táblázatban, melyekre gyakorlatilag nem is vagyunk kíváncsiak. Ennek a könnyebb érthetőség az oka. Ha gondoljuk, a feladat befejezéseként majd elrejthetjük őket. ¾ ¾

286

XVII. Az Excel XP

446. Ábra: A következő feladat A személyi jövedelemadó számításához egy táblázatban megadjuk majd az éves adókulcsokat, és azokat az értékhatárokat, melyekhez az adott kulcs tartozik, illetve az adott sáv alsó határára eső halmozott adót. A feladatban a 2004. évi adatok szerepelnek, de ha aktualizáljuk őket, más években is helyes eredményt fogunk kapni. Magyarázó feliratok: Elsőként gépeljük be az oszlopok magyarázó feliratait (mivel vízszintesen nincs elég hely, egymás alá írtam az egy sorba beviendő adatokat): A1 „Név” B1 „Havi bruttó bér” C1 „Éves bruttó bér” D1 „A sáv alsó határa” E1 „A sáv alsó határára eső adó” F1 „A sáv adója” G1 „Havi SZJA” H1 „Havi nettó bér” J1 „Adótábla” Maga az adótábla (ezek a J2:M4 tartományba kerülnek): J2 „Sáv alsó határa” K2 0 L2 800 000 M2 1 500 000 J3 „Alsó határra eső adó” K3 0 L3 144 000 M3 326 000 J4 „Adott sáv adókulcsa” K4 0,18 L4 0,26 M4 0,38 Az adatok (ezek az A2:B6 tartományba kerülnek): A2 „Kovács József” B2 220 000 A3 „Szalay László” B3 175 000 A4 „Salamon Katalin” B4 53 000 A5 „Szórádi Éva” B5 98 000 A6 „Salakta Pál” B6 112 000 A magyarázó feliratok, és a kiinduló adatok formázása: Kivételesen, elsősorban a jobb áttekinthetőség érdekében már menetközben formázzuk meg az eddig bevitt adatokat. Az A1-H1 tartomány szövegének igazítása Jelöljük ki a kérdéses tartományt, majd lapozzunk a „Formátum” menü „Cellák” opciójának „Igazítás” regiszteréhez, azután forgassuk el a szöveget 90 fokkal, és mind vízszintesen, mind függőlegesen igazítsuk középre. Ezek után még állítsuk félkövérre is a , valamint töltsük ki sárgával a ikon segítségével. Az adótábla formázása Jelöljük ki elsőként a J1-M1 tartományt, majd igazítsuk a gesen is középre, valamint a tartományt, és formázzuk meg a

ikonnal. A „Cellák formázása” ablakban ezt is állítsuk függőle-

ikonnal félkövérre is, végül töltsük ki sárgával a ikonnal, majd kattintsunk kétszer a

jelöljük ki a K4-M4 tartományt, és kattintsunk a

ikonnal . Most jelöljük ki a K2-M3

ikonra. E terület formázásának befejezéseként

ikonra.

A B2-H6 tartomány formázása Jelöljük ki, majd kattintsunk a

ikonra egyszer, aztán kétszer a

ikonra.

A képletek: C2 cellában Kattintsunk a C2 cellába, majd gépeljünk egy egyenlőségjelet. Kattintsunk a B2 cellára, majd gépeljük be „*12” (idézőjel nélkül), és zárjuk le az adatbevitelt a ikonnal. Fogjuk meg az autokitöltőt, és húzzuk le C6-ig, vagy kattintsunk rá duplát.

287

PC Suli XP alapokon D2 cellában Kattintsunk D2-be. Indítsuk a függvényvarázslót a ikonnal. Keressük meg a VKERES függvényt („Mátrix” kategória), majd nyomjuk meg az „OK” gombot. Kattintsunk a C2 cellára. ikonra, és jelöljük ki J2-M2 tartományt, majd nyomjuk meg az F4 funkcióbillenKattintsunk a „Tábla” mezőbe, azután a tyűt. Nyissuk le a „Függvényargumentumok” ablakot a ikonnal Kattintsunk a „Sor_szám” mezőbe, és gépeljük be „1” (idézőjel nélkül). Végül nyomjuk meg a kész gombot. Az autokitöltőt megfogva húzzuk le D6-ig, vagy kattintsunk rá duplát. E2 cellában Kattintsunk E2-be. ikonnal. Keressük meg a VKERES függvényt („Mátrix” kategória), majd nyomjuk meg az Indítsuk a függvényvarázslót a „OK” gombot. Kattintsunk a C2 cellára. ikonra, és jelöljük ki J2-M3 tartományt, majd nyomjuk meg az F4 funkcióbillenKattintsunk a „Tábla” mezőbe, azután a tyűt. Kattintsunk a „Sor_szám” mezőbe, és gépeljük be „2” (idézőjel nélkül). Végül nyomjuk meg a kész gombot. Az autokitöltőt megfogva húzzuk le E6-ig, vagy kattintsunk rá duplát. F2 cellában Kattintsunk az F2 cellába. Indítsuk el a VKERES függvényt, majd a „Keresési_érték” mezőbe adjuk meg most is a C2-es cellát. A „Tábla” most J2-M4 legyen (persze most is nyomjuk meg az F4 funkcióbillentyűt), a Sor_szám” pedig „3” (idézőjel nélkül). Ha ezekkel végeztünk, akkor viszont ne a „Kész” gombot nyomjuk meg, hanem kattintsunk a szerkesztőlécen lévő képlet mögé. A szövegkurzor most már ott jelent meg. Gépeljünk egy „*” jelet, majd folytassuk a képletet. Gépeljünk egy kezdő zárójelet, majd kattintsunk a C2 cellára, aztán következzék egy „-” jel, majd kattintsunk a D2 cellára, végül gépelikonra, és az autokitöltőt húzzuk le F6-ig, vagy kattintjünk egy befejező zárójelet (mindent idézőjel nélkül). Kattintsunk a sunk rá duplát. G2 cellában Kattintsunk a G2 cellába, majd gépeljük be „=(”, kattintsunk az E2-re, gépeljünk egy „+” jelet, kattintsunk az F2-re, és gépelikonnal, majd az autokitöltőt húzzuk le jük be a „/12)” (persze mindent idézőjelek nélkül). Zárjuk le az adatbevitelt a G6-ig, vagy kattintsunk rá duplát. H2 cellában Végül az utolsó képlethez kattintsunk előbb H2-be, majd gépeljünk egy egyenlőségjelet. Kattintsunk B2-re, következik egy ikonnal zárjuk le a bevitelt, végül húzzuk le az autokitöltőt H6-ig, „-” jel (idézőjel nélkül), majd kattintás G2-re. Most is a vagy kattintsunk rá duplát. Utolsó simítások: Adjuk meg a szegélyeket, és állítsuk be véglegesre az oszlopszélességeket. Ha gondoljuk, rejtsük el a részeredményeket tartalmazó oszlopokat (jelöljük ki a D-F oszlopokat, majd „Formátum” menü, „Oszlop”, „Elrejtés” opció).

15.2.4.4 Logikai függvények A logikai függvények tárgyalása előtt egy pár mondatot szólni kell a logikai kifejezésekről is. Minden logikai kifejezés csak két értéket vehet fel: IGAZ, vagy HAMIS. A logikai kifejezések többnyire egy-egy cellába kerülnek begépelésre, és általában maguk is egy-egy cella értékét hasonlítják öszsze valamilyen konstanssal, de az is lehet, hogy két cellát egymással. Ha egy cellába gépeljük be őket, akkor – mint minden kifejezést – egyenlőségjellel kell kezdeni. A következő példákból minden fontos kiderül: ¾ =C2>500 akkor ad IGAZ értéket, ha C2 cella tartalma nagyobb, mint 500; ¾ =D2<=1200 csak akkor IGAZ, ha D2 legalább 1 200; ¾ =E2<>”Zöld” ha E2 nem zöld, akkor IGAZ, különben HAMIS; ¾ =F2=”Zöld” ha F2 zöld, akkor IGAZ; ¾ =G2>”Nagy” ha a G2 cellában lévő szöveg ABC rendben hátrább lenne, mint „Nagy”, akkor IGAZ. A példákból is látható, hogy az alkalmazható összehasonlító operátorok: „=”, „<>” (nem egyenlő), „<”, „>”, „<=”, „>=”. A logikai kifejezésekben alkalmazhatók még konstansok (szám és szöveg), valamint címek. Szövegek összehasonlításakor a kisés nagybetűk nem kerülnek megkülönböztetésre. Egy-egy ilyen logikai kifejezés kiértékelése önmagában általában nem jelent gondot. A feladatok jelentős részében azonban több feltétel kiértékelésének kombinációjától függ, hogy mit hogyan kell számolni. Ilyenkor már merülhetnek fel problémák. Most

288

XVII. Az Excel XP ugyan nem megyünk bele részletesen az úgynevezett igazságtáblákba, mert azokról később lesz még szó bőven (XXVI. fejezet), de a két legfontosabb logikai függvény az ÉS() és a VAGY() tárgyalásakor a lényeg úgyis kiderül.

15.2.4.4.1 Több feltétel együttes teljesülése (ÉS) Több feltétel együttes teljesülése Æ ÉS(Logikai1; Logikai2; …) Az ÉS függvény Logikai1, Logikai2, … nevű valamennyi paramétere egy-egy olyan cellára történő hivatkozás, melyben logikai kifejezés szerepel, vagy maga a paraméter a logikai kifejezés (amennyiben közvetlenül a függvénybe gépeljük be a logikai kifejezést, akkor azt nem kell egyenlőségjellel kezdeni). Az ÉS függvény akkor ad IGAZ értéket, ha minden argumentuma IGAZ. Lássunk két példát rá: cím1 adat1 cím2 adat2 függvényÆeredménye indoklás 1 indoklás2 ¾ C2 600 D2 300 ÉS(C2>500; D2<=1200) Æ IGAZ mert C2>500 Æ IGAZ D2<=1200 Æ IGAZ ¾ E2 ”Kék” C2: 300 ÉS(E2>”Kék”; C2<=1200) Æ HAMIS mert E2>”Kék”Æ HAMIS C2<=1200Æ IGAZ Az első példa értéke IGAZ csak, mert csak ott IGAZ mindkét argumentum (több argumentum esetén mindnek IGAZ-nak kell lenni)!

15.2.4.4.2 Több feltételből egy teljesülése (VAGY) Több feltételből egy teljesülése Æ VAGY(Logikai1; Logikai2; …) A VAGY függvény Logikai1, Logikai2, … nevű valamennyi paramétere szintén egy-egy olyan cellára történő hivatkozás, melyben logikai kifejezés szerepel, vagy maga az argumentum a logikai kifejezés. Most is igaz, hogy amennyiben közvetlenül a függvénybe gépeljük be a logikai kifejezést, akkor azt nem kell egyenlőségjellel kezdeni. A VAGY függvény akkor ad IGAZ értéket, ha legalább egy argumentuma IGAZ. Lássuk az előző példákat VAGY függvénnyel: cím1 adat1 cím2 adat2 függvényÆeredménye indoklás 1 indoklás2 ¾ C2 600 D12 300 VAGY(C2>500; D12<=1200) Æ IGAZ mert C2>500 Æ IGAZ D12<=1200 Æ IGAZ ¾ E2 ”Kék” C2: 300 VAGY(E2>”Kék”; C2<=1200) Æ IGAZ mert E2>”Kék”Æ HAMIS C2<=1200Æ IGAZ Most mindkét példa értéke IGAZ, mert mindegyikben van IGAZ argumentum (több argumentum esetén is elég csak egynek IGAZ-nak kell lenni)!

15.2.4.4.3 Mi történjen, ha egy feltétel teljesül, és mi, ha nem (HA) Mi történjen, ha egy feltétel teljesül, és mi, ha nem Æ HA(Logikai_vizsgálat; Érték_ha_igaz [; Érték_ha_hamis]) A HA függvény Logikai_vizsgálat paramétere az, ami a neve: egy logikai vizsgálat. Ez lehet egy olyan cellára történő hivatkozás, melyben logikai kifejezés szerepel, vagy maga a logikai kifejezés. Most is igaz, hogy amennyiben közvetlenül a függvénybe gépeljük be a logikai kifejezést, akkor azt nem kell egyenlőségjellel kezdeni. Emennyiben a kiértékelés eredménye IGAZ, akkor az Érték_ha_igaz paraméter, ha pedig HAMIS, akkor az Érték_ha_hamis paraméter értéke jelenik meg abban a cellában, ahol maga a HA függvény szerepel. Természetesen ez utóbbi két paraméter is hivatkozhat cellára. Lássunk erre is két példát: cím1 adat1 cím2 adat2 cím adat függvényÆeredménye indoklás ¾ C2 600 D2 Nagy E2 Kicsi HA(C2>500; D2;E2) Æ Nagy mert C2>500 Æ IGAZ ¾ C2 300 D2 Nagy E2 Kicsi HA(C2>500; D2;E2) Æ Kicsi mert C2>300 Æ HAMIS 172. Feladat (VAGY, HA): Az előző 171. feladaton végezzünk még egy kiegészítést: ha a 20 000 Ft SZJA előleg felett fizetők száma meghaladja a hármat, vagy az adózók által fizetett összes adó összege magasabb 260 000 Ft-nál, akkor jelenjen meg egy „Sok SZJA”, ellenkező esetben padig egy „Kevés SZJA” figyelmeztető felirat az A8 cellában. Megoldás: Két megoldást is bemutatok. Előbb az egyszerűség kedvéért nem fogunk beágyazást alkalmazni, hanem a részeredményeket is kiszámítatjuk, és majd azokra hivatkozunk. Azután beágyazott függvényekkel is elvégezzük ugyanazt. I. Beágyazás nélkül Magyarázó feliratok: Az A10 cellába írjuk be „Összes adó”, A11-be „”20 000 felett adózók száma, A12-be „Van 20 000 felett, vagy több mint 60 000”

289

PC Suli XP alapokon Képletek: B10 Kattintsunk B10-be, majd a ikonnal indítsuk el az AutoSzummát, azután jelöljük ki a G2:G6 tartományt, végül nyomjunk Entert. B11 Jó helyen, a B11-ben állunk, itt indítsuk el a ikonnal, majd váfüggvényvarázslót a lasszuk ki a „Statisztikai” kategóriából a „DARAB.TELI” függvényt, és kattintsunk az „OK” gombra. A „Függvényargumentumok” ablakban a „Tartomány” mezőbe adjuk meg a G2:G6 tartományt, a „Kritérium” argumentumnak pedig adjuk meg a „>20000” feltételt, befejezésül nyomjuk meg a „Kész” gombot. 447. Ábra: A megoldandó, egy kicsit speciális feladat B12 Kattintsunk a B12-be, majd indítsuk a ikonnal, és válasszuk ki a „Logikai” kategóriából a „VAGY” függvényt. A „Logikai1” argumentumfüggvényvarázslót a nak a „B10>260000”, a Logikai2” argumentumnak pedig a „B11>3” kifejezést adjuk meg, majd kattintsunk a „Kész” gombra. A8 ikonnal, és a „Logikai” kategóriából most a „HA” függvényt válaszÁlljunk az A8 cellába, indítsuk a függvényvarázslót a szuk. A „Logikai_vizsgálat” argumentumnak B12 cellát adjuk meg. Az „Érték_ha_igaz” argumentumba „Sok SZJA”, az „Érték_ha_hamis” argumentumba „Kevés SZJA” szöveget gépeljünk be, majd kattintsunk a „Kész” gombra. Formázás feliratok: A formázásokat (szegélyek, kitöltések) már nem jelent gondot a korábban tanultaknak megfelelően önállóan elvégezni. II. Beágyazással A8 cella képlete A képlet meglehetősen bonyolult, mert három beágyazott függvényt is tartalmaz a külső függvény. Álljunk az A8 cellába, inikonnal, és a „Logikai” kategóriából válasszuk ki a „HA” függvényt. Most benne állunk a dítsuk a függvényvarázslót a „Függvényargumentumok” ablakban a „Logikai_vizsgálat” mezőben. ikonnal válasszuk ki a „További függvények…” közül a „Logikai” kategóriából a A szerkesztőlécen lévő „VAGY” függvényt. Most a „Függvényargumentumok” ablakban a „Logikai1” argumentumban állunk. ikont, és a „További függvények…” elem segítségével a „Mat. és trigonom.” A szerkesztőlécen nyissuk le a kategóriából a „SZUM” függvényt válasszuk. Most a „Függvényargumentumok” ablakban a „Szám1” argumentumban állunk. Jelöljük ki a G2:G6 tartományt, majd kattintsunk a pillanatnyi képlet végén lévő három befejező zárójel közül jobbról a második elé, és idézőjel nélkül gépeljük be: „>260000”. Ezzel visszakerültünk a „VAGY” függvény „Függvényargumentumok” ablakába. ikonnal nyissuk meg függvények listáját, majd válasszuk a „ToKattintsunk bele a „Logikai2” mezőbe, és a vábbi függvények…” elemet. A kapott ablakban a „Statisztikai” kategóriából indítsuk el a „DARABTELI” függvényt. Most a „Függvényargumentumok” ablak „Tartomány” argumentumában állunk. Jelöljük ki a G2:G6 tartományt, majd kattintsunk bele a „Kritérium” argumentumba. Gépeljük be: „>20000” (most kell az idézőjel is), majd kattintsunk a pillanatnyi képlet végén lévő három befejező zárójel közül jobbról a második elé. Idézőjel nélkül gépeljük be: „>3”. Kattintsunk a képlet végén jobbról az első befejező zárójel elé, majd gépeljünk be egy pontosvesszőt. Ezzel visszakerültünk a „HA” függvény „Függvényargumentumok” ablakába. Az „Érték_ha_igaz” és az „Érték_ha_hamis” mezőkbe gépeljük be: „Sok SZJA”, illetve „Kevés SZJA”. Kattintsunk a „Kész” gombra. Ezzel átvitt és valódi értelemben is minden szempontból „készen vagyunk”. Formázás feliratok: Most is a tanultaknak megfelelően alakítsuk ki A8 cella képét.

290

XVII. Az Excel XP 15.2.4.5 Adatbázis függvények Aki már érti a statisztikai függvényeket, az gyorsan megtanulja majd az ebbe a kategóriába tartozó függvényeket is. Mindegyiknek az a lényege, hogy nem a kijelölt tartomány összes tételét veszi bele a számításba, hanem csak bizonyos feltételeknek megfelelőket. Ráadásul a feltételek az adott tétel sorának bármely adatára vonatkozhatnak (de minden sorban ugyanarra az oszlopra). A feltételek – az úgynevezett kritériumok – megadása jelenti az egyetlen nehézséget. Ennek mikéntjére rögtön nézünk három példát, és találkozunk még továbbiakkal az irányított szűröknél is (XVII. fejezet 22.4.2), mert ott szintén meg kell majd adni kritériumokat. A kritérium (feltétel) lehet egyetlen logikai kifejezés, de lehet egészen összetett is, „ÉS” és „VAGY” kapcsolatokkal. Egy elemi (tehát csak egy feltételt tartalmazó) kritériumot úgy kell megadni, hogy felírjuk annak az oszlopnak a magyarázó feliratát, amelyre a feltétel vonatkozik, majd az alatta lévő cellába megadjuk a feltételt. Maga a feltétel logikai kifejezés és szám lehet. A következőkben lássunk mindkettőre egy-egy példát: ¾ >350 az adatbázis függvény csak akkor veszi figyelembe a számításhoz a tartomány argumentumában megadott adatokat, ha az adott adat nagyobb, mint 350; ¾ =350 a függvény csak akkor veszi figyelembe a számításhoz a tartomány argumentumában megadott adatokat, ha az adott adat pontosan 350. Még egyszer hangsúlyozom, a fenti feltételeket úgy kell megadni, hogy előbb begépeljük egy cellába annak az oszlopnak (mezőnek) a nevét, amelyre a feltétel vonatkozni fog, majd az alatta lévő cellába magát a feltételt. Ha több feltételt is meg akarunk adni „ÉS” és „VAGY” kapcsolatokkal, akkor azt kell megjegyezni, hogy az egy sorba írt feltételek kapcsolata „ÉS”, a külön sorba írt feltételek kapcsolata pedig „VAGY”. Lássunk az „ÉS” és a „VAGY” kapcsolatokra is példákat. I. példa: egyetlen „ÉS” kapcsolat Tegyük fel, hogy csak azokat a tételsorokat (rekordokat) akarjuk a számításnál figyelembe vetetni, ahol a lakhely „Győr”, és a születési dátum korábbi, mint „1984.01.01” (a két feltételnek egyszerre kell teljesülnie, tehát a kapcsolat „ÉS”, amit egy sorba kell írni). A feladat megoldása a következő: ¾ Két egymás melletti cellába begépeljük annak a két oszlopnak a magyarázó feliratát, amelyben a lakhely és a születési dátum szerepel (tegyük fel, hogy ez „Lakhely” és „Született”); ¾ Az alattuk lévő cellákba vigyük be a két feltételt, de mivel a kapcsolat közöttük „ÉS”, ezért a két feltételnek feltétlenül egy sorban kell lennie: Lakhely Győr

Született <1984.01.01

Az adatbázis függvény kritérium tartományába majd azokat a cellákat kell megadni, amelyekben a feltételek szerepelnek, bele értve a magyarázó feliratokat is (ez most 4 cella, ami egy 2 soros és 2 oszlopos tartomány). II. példa: egyetlen „VAGY” kapcsolat Most viszont csak azokat a tételsorokat (rekordokat) akarjuk a számításnál figyelembe vetetni, ahol a lakhely nem „Budapest”, vagy a születési dátum későbbi, mint „1984.01.01” (a két feltételből elég az egyiknek teljesülnie, tehát a kapcsolat „VAGY”, amit külön sorba kell írni). A megoldás a következő: ¾ Két egymás melletti cellába begépeljük most is annak a két oszlopnak a magyarázó feliratát, amelyben a lakhely és a születési dátum szerepel; ¾ Az alattuk lévő cellákba a két feltételt, de mivel a kapcsolat közöttük „VAGY”, ezért a két feltételnek két külön sorba kell kerülnie (természetesen ilyenkor az egyik magyarázó felirat alatt egy cella üres marad): ¾

Lakhely <>Budapest

Született

>1984.01.01 ¾ Az adatbázis függvény kritérium tartományába majd most is azokat a cellákat kell megadni, amelyekben a feltételek szerepelnek, persze a feliratokkal együtt (ez most már 6 cella, egy 3 soros és 2 oszlopos tartományban). III. példa: két „ÉS” és egy „VAGY” kapcsolat egy időben Azokat a tételeket akarjuk a számításnál figyelembe venni, ahol: ¾ Amennyiben a lakhely „Budapest”, akkor a születési idő későbbi, mint „1984.01.01” (mivel ennek a két feltételnek egyszerre kell teljesülnie, a kapcsolat „ÉS”, ezért egy sorba kell írni); ¾ Amennyiben a lakhely nem „Budapest”, akkor a születési idő korábbi, mint „1980.01.01” (a két feltételnek szintén egyszerre kell teljesülnie, a kapcsolat most is „ÉS”, azaz egy sorba kell írni).

291

PC Suli XP alapokon ¾

¾ ¾

A két összetett feltételből viszont elég, ha az egyik igaz (ez „VAGY” kapcsolat, tehát két külön sorba kell írni az első és a második összetett feltételt). A megoldás most a következő: Két egymás melletti cellába begépeljük most is annak a két oszlopnak a magyarázó feliratát, amelyben a lakhely és a születési dátum szerepel; Az alattuk lévő cellákba a négy feltételt kell begépelni, úgy, hogy egy sorba kerüljön az egyik, egy másik sorba a másik kettő „ÉS” kapcsolatú két feltétel (a két sor között pedig „VAGY” lesz a kapcsolat):

Lakhely Született =Budapest >1984.01.01 <>Budapest <1980.01.01 ¾ Az adatbázis függvény kritérium tartományába a feltételek, és magyarázó felirataik kell hogy kerüljenek (ez most szintén 6 cella, egy 3 soros és két oszlopos tartományban). IV. példa: több „ÉS” és több „VAGY” kapcsolat egy időben Azokat a tételeket akarjuk a számításnál figyelembe venni, ahol: ¾ Amennyiben a lakhely „Budapest”, akkor a születési idő későbbi, mint „1984.01.01” és korábbi, mint „1984.12.31” (tehát ennek a három feltételnek egyszerre kell teljesülnie, vagyis „ÉS” a kapcsolat, amit egy sorba kell írni); ¾ Amennyiben a lakhely „Győr”, akkor a születési idő korábbi, mint „1975.01.01” (a két feltételnek egyszerre kell teljesülnie, azaz a kapcsolat „ÉS”, amit egy sorba kell megadni). ¾ Amennyiben a lakhely nem „Budapest” és nem „Győr”, akkor a születési idő korábbi, mint „1980.01.01” (ennek a három feltételnek is egyszerre kell teljesülnie, szintén „ÉS”, azaz egy sorba kell bevinni). A három összetett feltételből viszont elég, ha az egyik igaz („VAGY” kapcsolat). Azt már talán megtanultuk, hogy „ÉS” kapcsolat esetén egy sorba kell írni az elemi feltételeket. A gondot most az jelenti, hogy több feltételt is ugyanarra a mezőre kellene megadni. A problémát úgy tudjuk megoldani, hogy többször is megadjuk ugyanannak a mezőnek a nevét (annyiszor, ahány elemi feltétel vonatkozik rá „ÉS” kapcsolattal). A megoldás most a következő: ¾ Egymás melletti cellákba begépeljük be az oszlopok magyarázó feliratát, annyiszor, ahányszor kell (ez lehet, hogy menet közben alakul csak ki); ¾ Az alattuk lévő cellákba a vigyük be a feltételeket, a korábban tanultak szerint:

¾

Lakhely Lakhely Született Született =Budapest >1984.01.01 <1984.12.31 =Győr <1975.01.01 <>Győr <>Budapest <1980.01.01 Az adatbázis függvény kritérium tartományába a feltételek, és magyarázó felirataik kell hogy kerüljenek (ez most már 16 cella, egy 4 soros és 4 oszlopos tartományban).

15.2.4.5.1 Feltételeknek megfelelő sorok egy oszlopának összege (AB.SZUM) Feltételnek megfelelő sorok oszlopának összege Æ AB.SZUM(tartomány; oszlop; kritérium) Az AB.SZUM segítségével a tartomány nevű argumentumában megadott területből (később adatbázisnak fogjuk hívni) az oszlop paraméterben megadott oszlopban (ezt pedig mezőként említjük majd) lévő számokat lehet összegezni. Az oszlopot most magyarázó oszlop felirata segítségével, vagy számával kell megadni. Az összegbe azonban csak azokat a tételsorokat számítja bele, amelyek megfelelnek a kritérium argumentumban megadott feltételeknek. A három összegző függvény összehasonlítása (SZUM, SZUMHA, AB.SZUM) Miért van szükség három különböző összegző függvényre? Tulajdonképpen az AB.SZUM függvénnyel mindent meg lehetne oldani, csak a használata bonyolult (legalább is a másik kettőnél bonyolultabb). Az AB.SZUM segítségével több, egészen bonyolult feltételnek is eleget tevő tételsor egy oszlopát lehet összegezni. A SZUMHA segítségével már csak egy feltétel adható meg, míg a SZUM feltétel nélkül összegez. Ne keverjük majd össze a SZUMHA és az AB.SZUM kritérium megadását: míg az előbbinél a függvénybe is be lehet gépelni a kritériumot, addig az utóbbinál egy külön kritérium tartományban kell megadni!

15.2.4.5.2 Feltételeknek megfelelő sorok egy oszlopában lévő számok száma (AB.DARAB) Feltételnek megfelelő sorok egy oszlopában lévő számok száma Æ AB.DARAB(tartomány; oszlop; kritérium) Szinte megegyezik az AB.SZUM függvénnyel, de nem összegez, hanem az oszlop argumentumban, a feliratával, vagy számával megadott mezőben előforduló, számot tartalmazó cellákat számolja meg (mint a DARAB függvény). A kritérium megadása a szokásos.

292

XVII. Az Excel XP 15.2.4.5.3 Feltételeknek megfelelő sorok egy oszlopában lévő számok átlaga (AB.ÁTLAG) Feltételnek megfelelő sorok egy oszlopában lévő számok átlaga Æ AB.ÁTLAG(tartomány; oszlop; kritérium) Mindenben megegyezik a két korábbi adatbázis függvénnyel, de most az oszlop argumentumban, a magyarázó feliratával, vagy számával megadott mezőben előforduló számok átlagát számolja ki (mint az ÁTLAG függvény). A kritérium megadása a részletesen bemutatott módon lehetséges.

15.2.4.5.4 Feltételeknek megfelelő sorok egy oszlopában lévő számok maximuma (AB.MAX) Feltételnek megfelelő sorok egy oszlopában lévő számok maximuma Æ AB.MAX(tartomány; oszlop; kritérium) Ez a függvény az oszlop argumentumban, a magyarázó feliratával, vagy számával megadott mezőben előforduló számok közül a maximumot adja vissza (mint a MAX függvény). A kritérium megadása természetesen most is a szokásos.

15.2.4.5.5 Feltételeknek megfelelő sorok egy oszlopában lévő számok minimuma (AB.MIN) Feltételnek megfelelő sorok egy oszlopában lévő számok minimuma Æ AB.MIN(tartomány; oszlop; kritérium) Mindenben megegyezik a korábbi adatbázis függvényekkel, de az oszlop argumentumban, a magyarázó feliratával, vagy számával megadott mezőben előforduló számok közül a minimálisat keresi ki (mint a MIN függvény). A kritérium megadása a megbeszélt módszerrel lehetséges. 173. Feladat (AB.DARAB, AB.SZUM): Az előző táblázatunkat egészítsük ki egy-egy olyan adattal, mely azt mutatja meg, hogy hány olyan dolgozó van, aki 20 000 Ft-nál magasabb SZJA előleget fizet, és ezek együttes összege mennyi! Megoldás: Bár a feladat megoldható lenne a DARABTELI (20 000 felettiek száma) és a SZUMHA (20 000 felettiek összege) függvényekkel is, használjunk adatbázis függvényeket. Kattintsunk A8 cellába és gépeljük be „20 000 Ft felettiek száma:”, Enter, majd „20 000 Ft felettiek összege”, és Enter. Jelöljük ki az A1-H1 tartományt, majd másoljuk ki a vágólapra a ikonnal. Menjünk az A11 cellába, majd kattintikonra. sunk a 448. Ábra: Az előző feladat kiegészítéseként megoldandó új feladat Menjünk azon felirat alá, melyre feltételt akarunk megszabni. Ez most a G12 cella, ahová gépeljük be a feltételt, azaz „>20000” ikonnal. Válasszuk az AB.DARAB függvényt („Adatbázis” Kattintsunk a B8 cellába, és indítsuk el a függvényvarázslót a kategória). A „Függvényargumentumok” ablakban adjuk meg a függvény argumentumait. Az „Adatbázis” mezőbe vigyük be ikonokat), a „Mező” mezőbe „7” (hetes számjegy) kerüljön, a „Kritérium” mezőbe A1-H6 tartományt (használjuk a pedig A11-H12 tartomány. A végén a „Kész” gombbal zárjuk le a bevitelt. ikonnal. Válasszuk az AB.SZUM függvényt („Adatbázis” Kattintsunk a B9 cellába. Indítsuk el a függvényvarázslót a kategória). A képletpalettán most is adjuk meg az argumentumokat. Az „Adatbázis” mezőbe vigyük be A1-H6 tartományt ikonokat), a „Mező” mezőbe „7” (hetes számjegy) kerüljön, a „Kritérium” mezőbe pedig A11-H12 tar(használjuk a tomány. A végén a „Kész” gombbal zárjuk le a bevitelt. A formázásokat a minta szerint végezzük el.

15.2.4.6 Pénzügyi függvények Az ebbe a kategóriába tartózó függvények legnagyobb része egészen speciális pénzügyi feladatok gyors és viszonylag egyszerű kiszámítására lett kifejlesztve. Van olyan, mely például a leszámítolt értékpapír, mondjuk egy kincstárjegy éves hozamát adja eredményül. Ez ugye a legtöbb ember számára nem nevezhető tipikus feladatnak, így nem is fogunk ilyenekkel foglalkozni.

293

PC Suli XP alapokon Az viszont mindenki életében elő szokott fordulni, hogy kölcsönt vesz fel, vagy éppen fordítva, havi fix összeget rak takarékba. A bemutatásra kerülő függvények segítségével kiszámítható, hogy: ¾ amennyiben felveszünk egy adott összegű hitelt, akkor annak mekkora lesz a havi törlesztő részlete (RÉSZLET); ¾ illetve amennyiben meg akarunk takarítani egy bizonyos összeget, akkor ahhoz havonta mennyit kell befizetni, (RÉSZLET); ¾ amennyiben a hitel mellett a havi törlesztő részlet nagyságát adjuk meg, akkor azt mennyi ideig kell fizetni (PER.SZÁM); ¾ vagy amennyiben a megtakarítandó összeg mellett a havi megtakarítás nagysága ismert, akkor a teljes összeg mennyi idő alatt jön össze (PER.SZÁM); ¾ mekkora hitelt vehetünk fel, ha tudjuk, hogy havonta adott törlesztési összeget tudunk vállalni (MÉ); ¾ mennyit fog érni a megtakarításunk, ha havonta adott összeget teszünk félre (JBÉ); ¾ mekkora a felszámított kamat adott összegű kölcsön, részlet és törlesztési szám esetén (RÁTA); ¾ illetve mekkora kamatot adtak ismert összegű megtakarítás, részlet és törlesztési szám esetén (RÁTA). A bemutatásra kerülő függvények feltételezik, hogy a kamat fix a teljes törlesztési időszak alatt (ez a kamatokra általában nem mondható el), és a törlesztési összegek is egyenlőek. Mindegyik tárgyalt függvénynél a következő paraméterek közül szerepel négy, és az ötödiket akarjuk az Excellel kiszámoltatni: paraméter neve magyarázat melyik függvény adja vissza ¾ részlet egy fizetés összege RÉSZLET ¾ időszakok_száma ennyi alkalommal történik fizetés PER.SZÁM ¾ ráta a kamatláb, amit százalékban kell megadni RÁTA ¾ mai_érték a futamidő eleji összeg (például ezt vesszük fel hitelként) MÉ ¾ jövőbeli_érték a futamidő végi összeg (például ennyit gyűjtünk össze) JBÉ A legtöbb esetben, ha a két utolsó paraméter, a mai_érték és a jövőbeli_érték egyszerre szerepel a függvény argumentumai között, akkor általában az egyiket kell csak megadni, a másik nulla. Miért? ¾ mert a tartozást (mai_érték) addig fizetjük, míg nulla nem lesz (jövőbeli_érték); ¾ illetve a megtakarítást nulláról indítjuk (mai_érték), és adott összeget rakunk félre (jövőbeli_érték). Az összes függvénnyel kapcsolatban két további nagyon fontos dologra is fel kell hívnom a figyelmet: ¾ amennyiben a törlesztési, vagy megtakarítási időszak havi ciklusú, akkor a kamatot is havi kamatként kell megadni; ¾ a paraméterként megadott összegeket előjelesen kell bevinni, illetve mindegyik függvény is így adja vissza az eredményét, azaz negatív előjellel a szempontunkból nézve kiadásokat, pozitívval a bevételeket. Mai érték és jövőbeli érték Tapasztalatom szerint a legtöbb felhasználó nem tudja jól alkalmazni a pénzügyi függvényeket, mert nem értik a különbséget a mai érték, és a jövőbeni érték között. Ez többnyire még a gazdasági iskolák hallgatóira is igaz. A futamidő elején lévő tőke nagysága a mai, míg a futamidő végén lévő a jövőbeni érték. A jövőbeni érték tehát nem azt jelenti, hogy az adott összeg a jövőben mennyit fog érni (nincs ember, aki ezt pontosan ki tudná számolni, hiszen az infláció változó értékű), hanem azt, hogy a tőke még nem áll rendelkezésünkre, csak a futamidő végére lesz meg.

15.2.4.6.1 Részlet nagysága (RÉSZLET) Részlet nagysága Æ RÉSZLET(ráta; időszakok_száma; [; mai_érték] [; jövőbeli_érték][; típus]) A függvényt két célra is használhatjuk: ¾ meghatározhatjuk, hogy adott összegű tartozás (mai_érték) esetén ismert kamat (ráta) és fizetési periódusszám (időszakok_száma) mellett mekkora törlesztő részletet kell fizetni, ¾ illetve kiszámolhatjuk, hogy egy tervezett összegű megtakarítás (jövőbeli_érték) adott kamat (ráta) és fizetési periódusszám (időszakok_száma) mellett mekkora összegű befizetésekkel jön össze. Vigyázzunk: ¾ ha havonta akarjuk fizetni a részletet, a kamatot is havi kamatként kell megadni (éves kamat/12); ¾ ha viszont negyedévente fizetünk, akkor a kamatnak is negyedévinek kell lennie (éves kamat/4); ¾ stb! A mai_érték és a jövőbeli_érték argumentum közül az egyiket meg kell adni! Az utolsó, többnyire nem használt típus paraméterrel azt adjuk meg, hogy a periódusok elején, vagy végén fizetünk. Ha elhagyjuk a feltételezett értéke 0, ami azt jelenti, hogy a periódusok végén fizetünk (1 esetén a periódusok elején). Vigyázzunk, ne keverjük össze a PRÉSZLET és az RRÉSZLET függvényekkel, mert azok csak a tőketörlesztést, illetve a kamattörlesztést határozzák meg! Példa: Számítsuk ki, hogy 10 % éves kamat mellett (ez a ráta) 5 év alatt (ez az időszakok_száma) mekkora összeget kell beraknunk a bankba (ez a részlet, amit keresünk), ha 1 000 000 Ft-ot akarunk összegyűjteni (ez a jövőbeli_érték)! A megoldáshoz vezető eszmefuttatás a következő:

294

XVII. Az Excel XP Mivel a futamidő elején 0 összeggel rendelkezünk, tehát a mai_érték 0, a 1 00 000 Ft pedig csak a futamidő végére jön össze, tehát ez a jövőbeli_érték. Vigyázzunk arra is, hogy a ráta és az időszakok_száma paramétert egyfajta időintervallumra, mondjuk havira hozzuk, azaz a 10%-ot osszuk el, az 5 évet pedig szorozzuk 12-vel! A részletet negatív előjellel fogjuk megkapni, mert mi fizetjük! A megoldás: =RÉSZLET(10%/12; 5*12; 0; 1000000) Æ -12 913,71 Ft 174. Feladat (RÉSZLET): Számítsuk ki, hogy mennyi törlesztő részletet kell fizetni az egyes adósoknak, ha az éves kamat a törlesztés teljes időszakára évi 22 %. Természetesen adott a tőketartozás, és hónapokban megadva a törlesztések száma is. Megoldás: Vigyük be a magyarázó feliratokat, és a forrásadatokat: A1 „Adós neve” 449. Ábra: Törlesztő részlet számítása B1 „Tartozás” C1 „Törlesztő részletek száma” D1 „A részlet összege” A2 „Kovács József” B2 125 000 C2 24 A3 „Szalay László” B3 2 560 000 C3 12 A4 „Salamon Katalin” B4 251 230 C4 16 A5 „Szórádi Éva” B5 2 564 875 C5 18 A6 „Salakta Pál” B6 214 875 C6 12 Adjuk meg a képletet a D2 cellába: Kattintsunk a D2-re, majd a ikonra. A „Pénzügyi” kategóriából válasszuk a RÉSZLET függvényt és kattintsunk az „OK” gombra. A „Függvényargumentumok” ablakban a következőket vigyük be: a „Ráta” mezőbe „22%/12” (mivel a kamatláb évi 22 %, a fizetési periódus havi, a kamatot is át kell számítani havira), az „Időszakok_száma” mezőbe C2 cellacím, és a „Mai_érték” mezőbe B2 cellacím. Ha ez megvan, kattintsunk a „Kész” gombra. Fogjuk meg az autokitöltőt, és húzzuk le D6-ig, vagy kattintsunk rá duplát. Formázás: A formázáshoz egyetlen kiegészítő magyarázat. A fejlécben lévő többsoros feliratokhoz a „Cellák formázása” ablak „Igazítás” regiszterén pipáljuk ki a „Sortöréssel több sorba” jelölőnégyzetet, vagy Alt - Enter billentyűkombinációval adjuk meg magunk a sortörés helyét már adatbevitel közben.

15.2.4.6.2 Részletek száma (PER.SZÁM) Részletek száma Æ PER.SZÁM (ráta; részlet [; mai_érték] [; jövőbeli_érték][; típus]) A függvénnyel azt határozhatjuk meg, hogy hány alkalommal kell fizetnünk, amennyiben adott fix ráta (kamatláb) mellett, a részlet paraméterben megadott egyenlő összegű részletekben akarunk egy összeget visszafizetni (mai_érték), vagy megtakarítani (jövőbeli_érték). A mai_érték és a jövőbeli_érték argumentum közül legalább az egyiket meg kell adni! Vigyázzunk most is arra, hogy amilyen időszakra vonatkozik a kamatláb, abban az időszakban adja meg a függvény a részletek számát. Tehát ha például éves rátát adunk meg, akkor években, ha pedig havi kamatlábat adunk meg, akkor hónapokban adja vissza a részletek számát is. A típus paraméter használata megegyezik a RÉSZLET függvényben leírtakkal. Példa: Számítsuk ki, hogy 6 % éves kamat mellett (ez a ráta) havi 12 000 Ft-os befizetéssel (ez a részlet) mennyi idő alatt jön össze 1 000 000 Ft (ez a jövőbeli_érték)! A megoldás során most a következőket kell átgondolni: Mivel a futamidő elején 0 Ft összeggel rendelkezünk, a mai_érték 0, az 1 00 000 Ft pedig a jövőbeli_érték, mert csak a futamidő végére jön össze. Vigyázzunk arra is, hogy a ráta és az időszakok_száma paramétert egyfajta időintervallumra hozzuk, azaz a 6%-ot osszuk el 12-vel, így majd hónapokban kapjuk meg a fizetési periódusok számát is! A részletet negatív előjellel kell megadni, mert mi fizetjük! A megoldás: =PER.SZÁM(6%/12; -12000; 0; 1000000) Æ 69,83534743

295