NYUGAT-MAGYARORSZÁGI EGYETEM EGYETEMI INFORMATIKA KÖZPONT
INFORMATIKA GYAKORLATOK TÁBLÁZATKEZELÉS
RÁCS ÁGNES SZÁMÍTÁSTECHNIKAI FŐMUNKATÁRS
2002.
-1-
TARTALOM
ALAPVETŐ TUDNIVALÓK
3
ADATBÁZIS-KEZELÉS
8
A FELADATOKBAN HASZNÁLT FÜGGVÉNYEK ADATBÁZIS-FÜGGVÉNYEK DÁTUMFÜGGVÉNYEK KERESŐ FÜGGVÉNYEK LOGIKAI FÜGGVÉNYEK MATEMATIKAI ÉS TRIGONOMETRIAI FÜGGVÉNYEK PÉNZÜGYI FÜGGVÉNYEK STATISZTIKAI FÜGGVÉNYEK SZÖVEGFÜGGVÉNYEK
11 11 12 12 13 14 15 16 18
FELADATOK
19
JAVASLATOK A MEGOLDÁSHOZ
27
-2-
EXCEL 6.0 for Windows
ALAPVETŐ TUDNIVALÓK A táblázatkezelő általában az OFFICE programkönyvtár alá van telepítve, vagyis onnan indítható. A számolótáblában egyszerre több munkafüzetet is megnyithatunk (Fájl menü), és munkafüzetenként több lapot kezelhetünk. A lapok tulajdonképpen táblázatok, ahol egy négyzetet cellának nevezünk, és sor és oszlopazonosítójával címezzük. Az oszlopokhoz (a sakktáblához hasonlóan) betűk vannak rendelve, a sorokhoz számok, vagyis a C4 cella a 4. sor 3. oszlopában található. Az Eszközök/ Egyebek/ Általános menüben áttérhetünk az ún. S101 szabványú címzésre is, melyben a C4 cella azonosítója S403 lesz, ahol S a sorra, 0 pedig az oszlopra utal. A cellákba szövegeket, számokat (a formátum alapján osztályozza bevitelkor az adatot a program) és képleteket (kötelezően a ’=’ jellel kezdődik) írhatunk. Ha az A1 szabványú hivatkozás nem tartalmaz ’$’ jelet, akkor relatív címzésről beszélünk, vagyis a képlet másolásakor a benne található cellahivatkozás is módosul. Legyen C4 tartalma a =A1+B5 képlet, és másoljuk a képletet a D6 cellába, akkor D6 már a =B3+C7 képletet tartalmazza. Viszont amely cellaindex elé $ jelet teszünk, az a komponens az átvitel során nem módosul: ha C4 tartalma =$A1+B$5 képlet, akkor D6-ba átmásolva =$A3+C$5 képletre módosul. Az S101 szabványú hivatkozást a képlet mozgatása nem módosítja, tehát látszólag abszolút címzést valósít meg, de lehetővé tesz relatív címzést is azáltal, hogy a sor és oszlopindexek szögletes zárójelbe írhatók, pl. ha az aktuális cella S403, és a benne tárolt képlet az S[-1]0[2] hivatkozást tartalmazza, akkor ez abszolút értelemben tulajdonképpen az S305 cellát azonosítja. Ha képletünkben nem az aktuális munkafüzetre és munkalapra hivatkozunk, akkor a cellaazonosító kiegészítendő a (pl. MAPPA3) munkafüzet és munkalap (pl. MUNKA2) nevével, a következő formában: [MAPPA3]MUNKA2!C4 Képleteink esetenként nemcsak cella, hanem tömb (téglalap alakú résztábla)hivatkozásokat is tartalmazhatnak, ilyenkor a blokkot átlós határcelláival a következőképpen címezzük: pl. C4:F8 A kijelölt tartománynak általában nem kell összefüggőnek lennie, ekkor elemeit vesszővel választjuk el, pl. C4:F8, A6:B9, D3 A képletek nem csak a szokásos matematikai műveleti jeleket, pl. + ^ - * / hanem a < = > relációjeleket és zárójelzést is tartalmazhatnak, így a képlet értéke nemcsak szám, hanem logikai érték (IGAZ, HAMIS) is lehet. A cellát rámutatással jelölhetjük ki: az egérrel a cellára pozícionálunk és megnyomjuk a baloldali egérbillentyűt. Ezután már felülírható a cella tartalma, vagy javítható, ha a módosítás előtt az F2 funkcióbillentyűt is megnyomjuk. Összefüggő blokkot az egér vonszolásával (az egeret megnyomott bal gombbal mozgatva) jelölhetünk ki, ha pedig a tartomány nem összefüggő, akkor a részblokkok kijelölése közben tartsuk lenyomva a Ctrl billentyűt. A táblázatkezelő nagy előnye, hogy a képletek értéke mindig a paraméterek aktuális értékén alapul, ezért egyetlen cella módosítása a táblázat számos (képlet)cellájának átírását eredményezheti. Ez nagy táblázatok esetén észrevehetően lelassíthatja a program válaszidejét, ezért az Excel lehetővé teszi az Eszközök/ Egyebek/ Számolás almenüben a képletek -3-
automatikus újraszámításának letiltását, manuális engedélyezését. Ugyanitt állítható be, hogy az iterációs számítások (pl. Eszközök/ Célértékszámítás, Solver) milyen pontosságig, legfeljebb hány lépésig tartsanak. A relatív és abszolút címzés pedig lehetővé teszi, hogy hasonló képletsorokat egyetlen képletből másolással származtassunk.
Az adatbevitel hasonlóan felgyorsítható a Szerkesztés/Kitöltés funkcióval, ahol számtani illetve mértani sorozatokat hozhatunk létre egy lépésben. A funkció hívása előtt kijelölendő a feltöltendő sor vagy oszlop, és első (vagy utolsó) pozíciójába írjuk be a sorozat kezdőértékét is. Lehetőség van felső, vagy alsó korlát (végérték) megadására, ekkor nem szükséges kijelölni az egész blokkot, csak a bal felső pozícióját. Bizonyos esetekben a kijelölt művelet (pl. gyakorisági táblázat létrehozása) eredménye nem egy szám, hanem vektor (mátrix), ekkor alkalmazzuk a tömbképletet, vagyis úgy írjuk be a képletet, hogy előtte nem egy cellát, hanem blokkot jelölünk ki, és az adatbevitel/módosítás végén a Ctrl+Shift+Enter billentyűkombinációt alkalmazzuk miközben a kurzor a szerkesztőlécen van.. A táblázatkezelő szolgáltatásaihoz elsősorban a legördülő menükön keresztül férünk hozzá, de a gyakorlott felhasználók gyorsabban haladnak az eszközsorok segítségével (ha pontosan tudják, melyik ikon milyen szolgáltatást jelöl), vagy a gyorsbillentyűkkel (pl. egy blokk vágólapra tétele a Ctrl+X kombinációval kezdeményezhető). Ne felejtsük el, hogy mindig rendelkezésre áll az Excel helyzetérzékeny súgója, vagyis az alkalmazás részletei, szabályai közvetlenül kiolvashatók a súgó billentyű vagy a súgó ikon kijelölése után. Emellett külön súgómenü biztosít számunkra egy hipertext típusú kézikönyvet, mely segít eligazodni a program nyújtotta zavarbaejtő bőségben. A táblázatkezelő használatáról, lehetőségeiről jó áttekintést kapunk a menürendszer tanulmányozása révén: Fájl menü: a számítógép perifériáival való kapcsolattartást szabályozza, pl. nyomtatás, lemezkezelés. Nagy könnyebbség, hogy felajánlja megnyitásra az utoljára használt munkafüzeteket is az 1-2-3 pontokban. Legördülő menüje a következő: Új munkafüzet Megnyitás Bezárás Mentés Mentés másként Munkaterület mentése Fájlkeresés Dokumentum adatlapja Oldalbeállítás Nyomtatási kép Nyomtatás 1. 2. 3. Excel vége
Ctrl+N Ctrl+O Ctrl+S
Ctrl+P
-4-
Szerkesztés menü: a táblázat tartalmának módosítására szolgál, ill. megkönnyíti a táblázatban való mozgás. Legördülő menüje a következő: Nem vonható vissza Nem ismételhető Kivágás Másolás Beillesztés Irányított beillesztés Kitöltés Tartalom törlése Törlés Lap törlése Lap áthelyezése v. másolása Keresés Csere Ugrás Csatolások Objektum
Ctrl+Z F4 Ctrl+X Ctrl+C Ctrl+V
Shift+F5 Ctrl+H F5
Megjegyzem, hogy a kivágással, vagy másolással vágólapra tett blokk beillesztése előtt elegendő a célhelynek csak a bal felső celláját kijelölni. Kijelölt diagramot, vagy kijelölt blokk tartalmát, formátumát, vagy mindkettőt a tartalom törlése menüvel távolítjuk el. Nézet menü: segítségével a táblázatkezelő képernyőn való megjelenése szabályozható, pl. milyen eszközsorokat lássunk, legyen-e állapotsor vagy szerkesztőléc. Legördülő menüje a következő: Szerkesztőléc Állapotsor Eszköztárak Teljes képernyő Nagyítás Látványkezelő
Hadd essen néhány szó a szerkesztőléc lehetőségeiről! Bal szélen a kijelölt cella, vagy blokk bal felső cellájának hivatkozását láthatjuk függvényszerkesztés előtt. A függvény beillesztése után a külső függvény neve jelenik itt meg. Ha a függvény valamely párbeszéddobozába újabb függvényt szeretnénk illeszteni, a párbeszéddoboz megfelelő helyére való pozicionálás után kattintsunk a szerkesztőléc cella-függvény megnevezésének legördülő menüjére, innét válasszuk ki a beépítendő függvényt! Megjegyzem, hogy a belső függvény adatainak kitöltése után nincs mód a külső függvény párbeszédablakának további kitöltésére (legfeljebb a szerkesztőléc képletsorában gépelgethetjük a még hiányzó adatokat), ezért célszerű a belső függvény felhívása előtt minden lehetséges más adatot kitölteni. A szerkesztőlécen szerkesztés közben láthatunk egy piros keresztet, ezzel térhetünk vissza a szerkesztés előtti állapothoz. A szerkesztés közben látható zöld kereszttel elfogadjuk az új tartalmat. A képlet szerkesztésekor beszúrhatunk a vágólapról oda korábban (esetleg más cellából) kimásolt képletet. Szövegkonstansok betűtípusát is a szerkesztőlécen való kijelölés után állíthatjuk be
-5-
Beszúrás menü: legördülő menüje magáért beszél: Cellák Sorok Oszlopok Munkalap Diagram Makró Oldaltörés Függvény Név Jegyzet Kép Objektum
Formátum menüvel: a táblázat tartalma nem, csak megjelenítése módosítható Legördülő menüje a következő: A cellák menüben további oldalak (pl. Szegélyek, Igazítás, Szám(típus), Mintázat, Védelem ) találhatók a BETŰTÍPUSon kívül abban az esetben, ha a cella tartalma már elfogadott az enter, vagy a zöld pipa használatával. Legördülő menüje a következő: Cellák Sor Oszlop Lap
Ctrl+1
Autoformázás Stílus Elhelyezés
Eszközök menü: általában máshova be nem sorolható, olyan funkciókat tartalmaz, melyek bonyolultabbak egy függvényhívásnál, egyváltozós egyenlet vagy többváltozós szélsőértékfeladat megoldása, helyesírás-ellenőrzés, makrózás. Legördülő menüje a következő: Helyesírás Automatikus javítás Célérték-keresés, Solver Esetvizsgáló Munkalap-vizsgálat Védelem Bővítménykezelő Makró Makrórögzítő Makróhozzárendelés Egyebek, Testreszabás, Beállítások
-6-
Adatok menü: adatbázis-kezeléssel összefüggő funkciókat tartalmaz. Legördülő menüje a következő: Sorba rendezés Szűrő Ürlap Részösszegek Adattábla Szövegből oszlopok Összesítés Tagolás és részletek Kimutatás Kimutatás-mező Adatfrissítés
Megjegyzem, hogy a sorbarendezés menüben általában az előre kijelölt blokk sorainak sorrendjét változtatjuk meg, de ezen oldal EGYEBEK című beállításaival az oszlopok sorrendje is változtatható. Ne felejtkezzünk meg arról, hogy az egy sorban (vagy oszlopban) levő adatok összefüggő adatok, rendezés után is azonos sorban (oszlopban) kell lenniük! Ablak menü: a képernyő kettő vagy négy ablakra bontható, miáltal az aktuális munkalap egymástól távoli blokkjai egyidejűleg megjeleníthetők, ill. lehetővé teszi több munkalap egyidejű megfigyelését. Legördülő menüje a következő: Új ablak Elrendezés Elrejtés Felfedés Felosztás Ablaktábla rögzítése Megnyitott munkafüzetek
Súgó menü: segítségével a kézikönyvben barangolhatunk. Legördülő menüje a következő: Tartalom Témakörök keresése Tárgymutató Gyors áttekintés Lotus 1-2-3 súgó Multiplan súgó Terméktámogatás Névjegy
Diagram menü segítségével egy kész diagram adatain, illetve beállításain módosíthatunk. A diagram menü csak akkor jelenik meg a menüsorban, ha rákattintással kijelöltünk egy korábban elkészített diagramot. Legördülő menüjének felső négy elemével a diagramvarázsló egyes oldalaira juthatunk. Külön hangsúlyozom, hogy a FORRÁSADAT almenünek két oldala van, az ADATSOR oldal alján kell beállítani az X tengely adatait, elkülönítve a többi adatsortól. Az ADATSOR HOZZÁADÁSA menüvel az előző adatoktól független, vagy az előzővel azonos függvény új adatpontjait illeszthetjük a diagramba. Figyeljünk az X tengelyen ábrázolandó értékek beállítására! A trendvonal felvétele menüvel lineáris, polinomiális, exponenciális függvényt -7-
illeszthetünk adatsorainkra, illetve számíttathatjuk ki ezek egyenletét. Javaslom azonban, hogy az egyenletek paramétereit a további számításokhoz dinamikusabban használható cellákban is számítsuk ki a megfelelő statisztikai függvényekkel. Minták Forrásadat Diagram beállításai Hely… Adatsor hozzáadása Trendvonal felvétele
Függvények A táblázatkezelő igen hatékony elemei a beépített függvények, melyek a problémák széles körére adnak gyors megoldást. A függvényt a felhasználó közvetlenül is képletbe gépelheti, de a félreütések elkerülése végett célszerűbb a függvényeket a függvényvarázsló ikon vagy a Beszúrás menü segítségével beépíteni. Ekkor a függvények csoportosítva lesznek felsorolva (Pénzügyi, Dátum, Matematikai, Statisztikai, Mátrix, Adatbázis, Szöveg, Logikai, Információs), és a csoport kijelölése után válogathatunk az oda tartozó függvények között. Minden függvényről egy rövid leírást is kapunk, és a paraméterek egyenként kerülnek lekérdezésre. Megengedett a függvények egymásba ágyazása, vagyis paraméter is lehet függvény. A függvény paramétereit a szerkesztőlécen illetve a kész leírt képletben ’;’ jel választja el egymástól, ezeket ne gépeljük be a függvényvarázsló paraméter-dobozaiba, a dobozhatáron automatikusan beépítésre kerülnek. Szeretném óvni a Felhasználót a számára kevéssé ismert funkciójú függvények használatától, pl. a statisztikai függvények közül az átlag jelentésével mindenki tisztában van, viszont a statisztikai próbákat kivitelező függvényeket ne alkalmazzuk az elméleti háttér (modell) ismerete nélkül. Hasonlóképpen egyes pénzügyi függvények fogalomrendszere (pl. amortizáció) is csak szűk körben ismert, és félreértett paraméterezésük nyilván hibás eredményt okoz. ADATBÁZIS-KEZELÉS Az adatbázis rekordokból, vagyis összetartozó adatok sorából áll. A rekordok mezőkből (egyes adatok) állnak, és minden rekord azonos mezőszerkezetű (de nem feltétlen teljes, azaz lehetnek fel nem töltött mezők is). A táblázatkezelő ezt a legáttekinthetőbb formában valósítja meg: Az adatbázis olyan blokk, amelynek sorai a rekordok, és oszloponként azonos jelentésű adatokat tartalmaz. Tehát a mezőket az oszlopokhoz rendeljük hozzá, amit azzal is nyomatékosítunk, hogy az adatbázis első sora még nem adatrekord, hanem a mezőneveket tartalmazza (fejléc, csak 1 cellasor lehet). Az adatbázis-kezelés lényege az, hogy valamilyen feltételrendszer alapján szelektálunk az adatbázis rekordjai között, és a kapott részhalmazzal végzünk valamely műveletet (megjelenítjük, másoljuk, számolunk vele).
-8-
A kiválasztó feltételrendszert kritériumtáblával írjuk le, amelyet egy külön blokkban készítünk el. Fejlécében ugyanazon mezőneveket tartalmazza, mint az eredeti adatbázisblokk (nem szükségképpen mindet). A kritériumtábla mezőnevei alatti cellákba írjuk a rá vonatkozó feltételeket (pl. >6 feltétel vonatkozhat számokat tartalmazó mezőre). Lényeges, hogy több mezőre vonatkozó feltételek esetén az azonos sorba írtak közt logikai ÉS kapcsolat van, míg a külön sorba írt feltételek közt logikai VAGY kapcsolat van. Lássunk egy példát is! Adatbázisunk a következő oszlopokat, mezőket tartalmazza: Családnév
Keresztnév
Helység
Utca
Házszám
Telefonszám
Április 15.-én telefonon szeretnénk üdvözölni az éppen névnapjukat ünneplő soproniakat, ezért kritériumtáblánk a következőképpen néz ki: Keresztnév Helység Anasztázia Sopron Tas Sopron A táblát azért írtuk fel így, mert Egyidejűleg kell teljesülnie a keresztnév és a helységnév megegyezésének, közöttük logikai ’és’ kapcsolat van, a két feltétel egy sorba írandó, A fenti időpontban Anasztázia és Tas névnap van, a két név bármelyike kielégíti a feltételt, közöttük logikai ’vagy’ kapcsolat van, a két feltétel külön sorba írandó. Ezután két lehetőségünk van: vagy letakarjuk a feltételeket nem kielégítő rekordokat (Adatok/Szűrő/Irányított szűrés/Helyben), vagy a kiválasztott rekordokat új helyre másoljuk. Másolásnál új szerkezetet is adhatunk az adatbázisnak, például most csak a teljes névre és a telefonszámra vagyunk kíváncsiak, ezért a célterületre csak a Családnév
Telefonszám
Keresztnév
mezőneveket vigyük (gépeljük, másoljuk) át, ezen adatok ugyanis már elegendőek ahhoz, hogy kedvesen elbeszélgessünk ismerősünkkel az idő múlásáról. Ne feledkezzünk meg arról, hogy kijelöléskor az adatbázishoz, a kritériumtáblához és a célterülethez hozzátartoznak a mezőnevek is! Legnagyobb körültekintésünk ellenére is előfordulhat, hogy a szükségesnél több rekord választódik ki, pl. a sopronhorpácsi Tas nevű ismerősünk is a listán szerepelne. Ennek oka az, hogy a táblázatkezelő csak a szövegegyezés elejét ellenőrzi, és a jelzett falu neve is a Sopron szóval kezdődik. A ’téves riasztást’ kiküszöbölhetjük, ha a helységnév hosszát is előírjuk, ami a HOSSZ szövegfüggvénnyel kivitelezhető: Keresztnév Anasztázia Tas
Helység Sopron Sopron
=HOSSZ (Helység) =6 =HOSSZ (Helység) =6
Tehát a kritériumtáblát egy új oszloppal egészítjük ki, amely logikai értéket visszaadó képletet tartalmaz. A kritériumtábla azon oszlopához, mely képletet tartalmaz, nem rendelhetünk érvényes mezőnevet, a legegyszerűbb, ha a névmezőt üresen hagyjuk. Külön felhívnám a figyelmet a HOSSZ függvény Helység paraméterére, ami ilyen formában csak névkijelölés (Beszúrás/Név/Megadás) után használható. Adott cellához (blokkhoz) hozzárendelhetek nevet -9-
a nevek listájáról, begépelhetek egy újat, vagy megadhatom, hogy melyik a cellával határos cella tartalmazza a cella nevét – ekkor a kijelölésnek a nevet tartalmazó cella is része legyen. A neveket a továbbiakban változóként használhatom, ami megkönnyíti a képletek felírását és értelmezését. Amennyiben végső célunk nem a kiválasztott rekordok megjelenítése, hanem valamilyen számolás ezen rekordok adataival, akkor egyszerűbb, ha adatbázis-függvényt használunk. Ezek a szokásos számoló függvényektől csak annyiban különböznek, hogy nevük az ’AB.’ karaktersorozattal kezdődik, és paramétereik egységesen: Az adatbázis blokkja, a mezőnevek sorát is beleértve, A függvényt az adatbázis melyik oszlopán (mezőn) hajtsuk végre, A kritériumtábla blokkja. Megjegyezzük még, hogy a kritériumtáblabeli szövegilleszkedés vizsgálatánál a megszokott jelentése szerint használhatók a ’?’ és ’*’ karakterek, és összehasonlításkor felhasználhatók a relációjelek (< = >) is. További lehetőség adatbázisokon rendezést végrehajtani valamely mező (oszlop) szerint, csökkenő vagy növekvő sorrendben, az Adatok/Sorba rendezés menü segítségével. Ilyenkor a rekordok változatlanok maradnak, csak sorrendjük módosul. A funkció kérése előtt ne felejtsük el kijelölni a teljes adatbázist, különben a ki nem jelölt oszlopokban található adatok nem vesznek részt a rendezésben. Az elsődleges, másodlagos stb. rendezési mezőket a program külön kérésére adjuk meg, és a másodlagos rendezési mezőre csak akkor van szükség, ha az elsődleges mező oszlopa több egyező adatot tartalmaz, ekkor ugyanis a rekordok sorrendje nem dönthető el egyértelműen.
-10-
A FELADATOKBAN HASZNÁLT FÜGGVÉNYEK ADATBÁZIS-FÜGGVÉNYEK Az adatbázist az őt tartalmazó blokk, a mezőt a mezőnév, a feltételt az előzőleg elkészített kritériumtábla cellahivatkozásával adjuk meg. AB.ÁTLAG A „feltétel”-nek megfelelő rekordok (sorok) „mező” oszlopának átlagértéke. Szintaxis: AB.ÁTLAG(adatbázis; mező; feltétel) AB.DARAB A „feltétel”-nek megfelelő és számot tartalmazó mezők számát adja. Szintaxis: AB.DARAB(adatbázis; mező; feltétel) AB.DARAB2 A „feltétel”-nek megfelelő és nem üres mezők számát adja. Szintaxis: AB.DARAB2(adatbázis; mező; feltétel) AB.MAX A „feltétel”-nek megfelelő sorok mező oszlopának a legnagyobb értékét adja. Szintaxis: AB.MAX(adatbázis; mező; feltétel) AB.MIN Az „adatbázis” „feltétel”-nek megfelelő rekordjainak „mező” oszlopából a legkisebb értéket adja. Szintaxis: AB.MIN(adatbázis; mező; feltétel) AB.SZORZAT Az „adatbázis” „feltétel”-nek megfelelő rekordjaira „mező” oszlop értékeinek szorzatát adja. Szintaxis: AB.SZORZAT(adatbázis; mező; feltétel) AB.SZÓRÁS Az „adatbázis” „feltétel”-nek megfelelő rekordjaira a „mező” oszlop értékei alapján a mintán alapuló szórást adja. Szintaxis: AB.SZÓRÁS(adatbázis; mező; feltétel) AB.SZUM Az „adatbázis” „feltétel”-nek megfelelő rekordjaira „mező” oszlop értékeinek összegét adja. Szintaxis: AB.SZUM(adatbázis; mező; feltétel) AB.VAR Mintán alapuló eltérésbecslés. Az „adatbázis” „feltétel”-nek megfelelő rekordjaira a „mező” oszlop értékei alapján az eltérésbecslést adja. Szintaxis: AB.VAR(adatbázis; ,mező; feltétel)
-11-
DÁTUMFÜGGVÉNYEK DÁTUM A megadott dátum értékét szám formátumra alakítja. FORMÁTUM/CELLÁK menü szerint Szintaxis: DÁTUM(év; hó; nap) Év értéke 1900-2078 között lehet. Hónap 1-12 közötti szám, amely a megfelelő hónapot jelenti. Nap szám, amely a nap, hónapon belüli száma. Példa: DÁTUM(1998; 09;12) DÁTUMÉRTÉK A dátum_text-ben megadott dátumot szám alakúra konvertálja. Szintaxis: DÁTUMÉRTÉK(dátum_text) Dátum_text Excel formátumban szövegkonstansként megadott dátum. „ Példa: DÁTUMÉRTÉK( 1989.09.12”) MOST
Megjelenítése
a
Az aktuális gépi időt adja (dátum, óra, perc, másodperc).
Szintaxis:
MOST()
MA Az aktuális gépi dátumot adja. Szintaxis: MA() ÉV A paraméterként megadott dátumértékből az év értéket adja vissza egész számként. Szintaxis: ÉV(dátum) KERESŐ FÜGGVÉNYEK VKERES Egy tömb legfelső sorában a megadott értéket keresi meg, és ebben az oszlopban a megadott sorban lévő értékét adja eredményül. Szintaxis: VKERES(keresett_érték; tábla; sor) Keresett_érték a táblázat első sorában keresendő érték Tábla az információkat tartalmazó blokk. Sor a tábla paraméterben megadott blokk megfelelő sor, amely a keresett értéket tartalmazza. FKERES A függvény a blokk első oszlopában a megadott értéket vagy az ahhoz legközelebbit keresi meg, és ebben a sorban a megadott oszlopban lévő értéket adja eredményül. Szintaxis: FKERES(keresett; blokk; oszlop) Keresett a blokk vagy tömb első oszlopában keresett érték. Blokk az adatokat és a keresett adatot is tartalmazó cella blokk. Oszlopa keresett érték megtalálása után, a sorban ennyi oszlopot lép a függvény jobbra az eredmény érték megadásához. -12-
LOGIKAI FÜGGVÉNYEK ÉS Logikai értéke TRUE vagyis IGAZ, ha az összes paraméter IGAZ, értéke FALSE, vagyis HAMIS, ha a paraméterek közül akárcsak egy is HAMIS. Szintaxis: ÉS(logikai1; logikai2;…) Logikai1, logikai2,… maximum 30 logikai kifejezés, amelynek igaz vagy hamis voltát meg akarjuk vizsgálni. Az argumentum logikai értékeket, tömböket vagy olyan hivatkozásokat tartalmazhat, amely logikai kifejezésekből állnak. Ha a tömb üres cellákat, vagy szöveg konstanst is tartalmaz, azt a függvény figyelmen kívül hagyja. Ha a kijelölt terület logikai értéken kívül mást is tartalmaz, akkor #VALUE! Hibaüzenetet kapunk. HAMIS HAMIS logikai értéket ad. Szintaxis: HAMIS() Nincs paramétere, a zárójelpárt mégis ki kell írni. HA Egy értéket ad, ha a logikai_test értéke IGAZ és egy másik értéket, ha HAMIS. Szintaxis: HA(logikai_test; igazesetén; hamisesetén) Logikai_test bármely érték vagy kifejezés, amely IGAZ-ként vagy HAMIS-ként értékelhető. Igazesetén az az érték, amelyet akkor ad a függvény, ha a logikai_test értéke IGAZ. Hamisesetén az az érték, amelyet akkor ad a függvény, ha a logikai_test értéke HAMIS. NEM A függvény argumentumának ellentettjét adja. Szintaxis: NEM(log) Log logikai kifejezés, IGAZ, vagy HAMIS illetve ilyen eredményt adó kifejezés. Ha a kifejezés értéke IGAZ, a függvény értéke HAMIS és fordítva. VAGY A függvény IGAZ értéket ad, ha a paraméterek közül egy vagy több igaz. Szintaxis: VAGY(log1; log2;…) Log1, log2,… max. 30 feltétel adható meg. IGAZ IGAZ logikai értéket ad a függvény. Szintaxis: IGAZ()
-13-
MATEMATIKAI ÉS TRIGONOMETRIAI FÜGGVÉNYEK
ABS Egy szám abszolút értékét adja. Szintaxis: ABS(szám) Szám valós szám, amelynek az abszolút értékét akarjuk meghatározni. COS Az adott szög cosinuszát adja. Szintaxis: COS(szám) Szám radiánban adott szög érték. Ha fokban akarjuk megadni akkor a számot szorozzuk meg PI()/180-al. INT A szám-ot lefelé kerekíti a legközelebbi egészre. Szintaxis: INT(szám) LN A szám természetes alapú logaritmusát adja. Szintaxis: LN(szám) LOG A szám adott alap-ú logaritmusát adja. Szintaxis: LOG(szám; alap) MDETERM A tömb mátrix determinánsát adja. Szintaxis: MDETERM(tömb) INVERZ.MÁTRIX
A megadott mátrix inverzét adja. Szintaxis: INVERZ.MÁTRIX(tömb) Tömb egyenlő számú sorból és oszlopból álló cella blokk. Felhívom a figyelmet, hogy az eredmény is tömb, a tömbfüggvényekre alkalmazandó szabályok szerint kell eljárni PI A függvény a (3.14159265358979) értékét adja, 15 jegy pontossággal. Szintaxis: PI() KEREK A szám-ot megadott tizedes jegy-re kerekíti. Szintaxis: KEREK(szám; jegy) Ha a jegyek száma negatív vagy 0, abszolútértékének megfelelő 10 hatványra történik a kerekítés.
-14-
SIN Az adott szög sinusát adja a függvény. A szám radiánban adott szög érték. Szintaxis: SIN(szám) Használja a RADIÁN függvényt a beadott paraméterre, ha azok fokban adottak. GYÖK A szám négyzetgyökét adja a függvény. Szintaxis: GYÖK(szám) TAN A radiánban adott „szög” tangensét adja a függvény. Szintaxis: TAN(szög) Használja a RADIÁN függvényt a beadott paraméterre, ha azok fokban adottak. RADIÁN A fokban megadott szögértéket radiánná alakítja. Szintaxis: RADIÁN(szög_fokban_megadva) PÉNZÜGYI FÜGGVÉNYEK
RÉSZLET Az időszakosan törlesztendő éves részletek értékét adja a függvény, állandó nagyságú törlesztő részletet és kamatlábat feltételezve. Szintaxis: RÉSZLET(kamatláb; nper; pv; fv; típus) Kamatláb a kamatláb értéke. Nper a fizetési időszakok száma egy évben. Pv jelenérték Fv jövőbeli érték, vagy készpénz egyenleg, amelyet az utolsó részletfizetés után akarunk elkönyvelni. Típus 0 vagy1 érték, a fizetés esedékességét határozza meg: 0=az időszak végén 1=az időszak elején RÁTA Az éves törlesztő részlet egy hónapra vetített kamatlábát határozza meg a függvény. Az értéket a függvény iterrációval határozza meg, így 0 értéket, vagy több megoldást is adhat. Szintaxis: RÁTA(nper; pmt; pv; fv; típus találgat) Nper a fizetési időszakok száma. Pl: ha négy évre vettünk fel havi törlesztésű kölcsönt, akkor a fizetési időszakok száma 48. Pmt az időszakonkénti törlesztés összege. Pv jelenérték Fv jövőbeli érték, vagy készpénz egyenleg, amelyet az utolsó részletfizetés után akarunk elkönyvelni. Típus 0 vagy 1 érték, a fizetés esedékességét határozza meg: 0=az időszak végén 1=az időszak elején találgat a kamatláb becsült értéke.
-15-
STATISZTIKAI FÜGGVÉNYEK
ÁTLAG Az argumentum értékeinek számtani átlagát adja. Szintaxis: ÁTLAG(szám1; szám2;… ) Szám1, szám2,… az értékek maximális száma 30. A függvény argumentuma lehet szám, név, cellahivatkozás, tömb. A blokk üres vagy szöveget tartalmazó celláit figyelmen kívül hagyja. GYAKORISÁG A gyakorisági eloszlás értékét, mint 1 x n-es tömböt adja eredményként. Szintaxis: GYAKORISÁG(adattömb; csoporttömb) Adattömb Annak a cellablokknak a hivatkozása, amely értékeinek az eloszlását szeretnénk feltérképezni. Csoporttömb Elemei a számegyenest intervallumokra osztják (minden cella egyetlen számot tartalmazhat, két egymást követő cella határoz meg egy intervallumot). A függvény eredménye minden intervallumhoz megadja, hogy az adattömbnek hány darab eleme esik bele. A függvény eredménye eggyel több szám (cella), mint a csoporttömb elemeinek száma, mivel az utolsó intervallumnak nem adható meg felső korlát (plusz végtelen). A csoporttömböt a függvény felépítése előtt kell elkészíteni. A függvény az úgynevezett TÖMBFÜGGVÉNYek közé tartozik. A TÖMBKÉPLET elkészítése előtt jelölje ki az eredménynek szánt cellákat (egynél több), bal felső cellájában építse fel a függvényt, kattintson a szerkesztőlécre, majd a CTRL+SHIFT+ENTER billentyűkombinációval zárja le. LIN.ILL A legkisebb négyzetek módszerével illeszt a függvény egy egyenest a függvénypontokra. A függvény eredménye egy tömb, amely az egyenes paramétereit tartalmazza. Szintaxis: LIN.ILL(ismert _y; ismert_x; konstans; stat) Ismert_y az y=mx + b összefüggésből ismert y értékek. Ismert_x az y=mx + b összefüggésből ismert x értékek Konstans logikai érték. IGAZ: a b paraméter meghatározása normál módon történik. HAMIS: b=0 feltételezéssel élünk. Stat logikai érték. IGAZ: kiegészítő statisztikai elemzés készül. HAMIS: a függvény csak az m együtthatót és a b konstans értéket adja eredményül. A függvény az úgynevezett TÖMBFÜGGVÉNYek közé tartozik, alkalmazzuk a TÖMBKÉPLETre vonatkozó szabályokat. Lásd még→ GYAKORISÁG függvényt.
-16-
LOG.ILL Exponenciális görbét illeszt a függvénypontokra, és a görbe paramétereit adja. Szintaxis: LOG.ILL(ismert_y; ismert_x; konst; stat) Ismert_y a y=b*mx összefüggésből ismert y értékek. Ismert x a y=b*mx összefüggésből ismert x értékek Konst logikai érték. IGAZ: a b paraméter meghatározása normál módon történik. HAMIS: b=1 feltételezéssel élünk. Stat logikai érték. IGAZ: kiegészítő statisztikai elemzés készül, HAMIS: nem készül statisztikai elemzés, a függvény csak az m együtthatót és a b állandót adja. A függvény az úgynevezett TÖMBFÜGGVÉNYek közé tartozik, alkalmazzuk a BLOKKKÉPLETre vonatkozó szabályokat. Lásd még→ GYAKORISÁG függvényt. MAX A paraméterek közül a legnagyobb értéket adja. Szintaxis: MAX(szám1; szám2;…) Szám1, szám2,… max. 30 paraméter adható meg. A számok tömbhivatkozások is lehetnek MEDIÁN
Az adot számok mediánját határozza meg a függvény. A medián a nagyság szerint sorba rendezett értékek közül a középső érték. A medián az az érték amelynél kisebb, illetve nagyobb értékek gyakorisága megegyezik. Szintaxis: MEDIÁN(szám1; szám2;…) Szám1, szám2,… max. 30 paraméter adható meg. A számok tömbhivatkozások is lehetnek MIN A megadot számok közül a legkisebbet választja ki a függvény. Szintaxis: MIN(szám1; szám2;…) Szám1, szám2,… max. 30 paraméter adható meg. A számok tömbhivatkozások is lehetnek TREND Az ismert_y és ismert_x értékekkel adott pontokra, a függvény a legkisebb négyzetek módszerével egyenest illeszt. Eredményként az új_x értékekhez tartozó y értékeket adja a függvény. Szintaxis: TREND(ismert_y; ismert_x; új_x.; konstans) Ismert_y az y = mx + b összefüggésből ismert y értékek. Ismert_x az y = mx + b összefüggésből ismert x értékek Új_x x értékek, amelyekhez tartozó y értékeket keresünk. Konstans logikai érték. IGAZ: a „b” paramétert a rendszer a normál módon meghatározza. HAMIS: feltesszük, hogy b=0. Alkalmazható a függvény tömbfüggvényként, tömbképletként (pl. → GYAKORISÁG), de az ismert pontokat abszolút címmel használva kitöltőnyéllel is megkaphatjuk az új pontok Y koordinátáit.
-17-
SZÖVEGFÜGGVÉNYEK
KARAKTER A kód-nak megfelelő karaktert adja. Szintaxis: KARAKTER(kód) Kód értéke 1-255 között lehet. A karakter ASCII kódja. KÓD A szöveg konstans első karakterének ASCII kódját adja. Szintaxis: KÓD(szöveg) SZÖVEG.TALÁL Egy szövegkonstansban részszöveget keres a függvény. Annak a karakternek a számát adja, ahonnan kezdődően először megtalálta a szövegkonstansban a résszöveget. Szintaxis: SZÖVEG.TALÁL(keresett; szöveg; kezd) Keresett a keresendő résszöveg. Nem tartalmazhat joker karaktereket. Szöveg a szövegkonstans, amelyben a résszöveg előfordulását keressük. Kezd annak a karakternek a sorszáma, amelytől a keresés kezdődjön. BAL Szöveg konstans első néhány karakterét adja. Szintaxis: BAL(szöveg; kar_szám) Szöveg a szöveg konstans. Kar_szám ennyi karaktert ad vissza a függvény. HOSSZ A stringben lévő karakterek száma. Szintaxis: HOSSZ(string) KÖZÉP A kezdőérték-ben megadott karakterpozíciótól kezdődően szám számú karaktert választ ki a szöveg-ből. Szintaxis: KÖZÉP(szöveg; kezdőérték; szám) JOBB A szövegkonstans utolsó néhány karakterét adja eredményül. Szintaxis: JOBB(szöveg; szám) Szöveg szöveg konstans. Szám a szövegkonstans végéről visszaadandó karakterek száma. ÉRTÉK A számjegyekből is álló szöveg-et számmá konvertálja a függvény. Szintaxis: ÉRTÉK(szöveg)
-18-
FELADATOK 1. Feladat A
B
C
D
E
F
G
ÁRAK Ft
1. kofa
2. kofa
3. kofa
4. kofa
SOPKER
FÜSZÉRT
1
2 3 4 5 6
Körte Szőlő Narancs Banán Alma
150 300 150 170 55
130 500 220 150 65
125 240 310 180 50
120 340 120 210 89
110 210 130 180 87
K SzükZÖLDÉRT séges kg H
99 340 145 130 120
80 145 250 150 70
30 0 30 40 50
Készítsen egy ugyanilyen alakú RAKTÁRKÉSZLET táblázatot is kg mértékegységgel! A rovatfejeket másolással állítsa elő!
1) Számítsa ki az egyes gyümölcsök átlagárait. Kerekítse 1 tizedes jegyre. 2) Állapítsa meg az egyes gyümölcsök maximális ill. minimális árait. 3) Számítsa ki, hogy a szükséges mennyiséget minimális áron vásárolva mennyit kell fizetnünk. 4) Számítsa ki az egyes kofák, boltok lehetséges legnagyobb árbevételét (ha a teljes készletet el tudják adni saját árukon) az egyes gyümölcsökből tételesen és összesen. 5) Számítsa ki az egyes kofák, boltok árbevételét az egyes gyümölcsökből tételesen és összesen, ha a teljes készletet átlagáron adják el. 6) Számítsa ki az egyes boltok, ill. kofák összes raktárkészletét. Az alatta levő sorban vegye figyelembe, hogy a boltok készletének 20%-a, a kofák készletének 5%-a tönkre megy. 7) Készítsen gyakorisági táblázatot : hány gyümölcsár esik 100 Ft alá, 100-200 Ft közé, 200-300 FT közé ill. 300 Ft fölé. 8) Készítsen oszlopdiagramot a kofák és boltok összraktárkészletéről!
-19-
2. Feladat Hozza létre az alábbi (1996-os) ártáblázatot a Boltok nevű munkafüzetlap A1…G7 blokkjába, és mentse le ÁRAK néven:
A 1 2 3 4 5 6 7 8 9
CÉG/TERMÉK
CSEMEGE FŰSZÉRT MEINL AKKORD FENYŐ S-MARKET Átlag Minimum
B C D LISZT CUKOR TEJ 45 84 42 88 46 83 44 84 46 85 43 82
55 55 56 54 56 55
E F G KENYÉR RIZS TÚRÓ 78 143 159 76 137 155 80 142 156 82 140 156 81 144 158 79 141 156
1) Képezzen összeget a H oszlopban (mezőnév KOSÁR legyen). 2) Képezzen átlagot a 8. sorban, minimumot a 9. sorban. 3) Színezze sárgára a táblázat szöveges mezőit, és dupla vonallal válassza el az S-MARKET sort az átlagtól. 4) Állapítsa meg az ártáblázat azon tételeinek számát, melyek a 0-50, 50-80, 80100, 100 fölötti intervallumba esnek! 5) Rendezze a táblázatot elsődlegesen a KOSÁR mező szerint növekvő sorrendbe, másodlagosan pedig a liszt ára szerint csökkenően. 6) Másolja át a táblázatot, azon rekordokat (sorokat) szelektálva, melyekben a liszt ára legalább 44 Ft, a rizs pedig legfeljebb 142 Ft. Az eredmény H1-től kezdve tárolódjon. 7) Határozza meg (nem feltétlenül adatbázisfüggvény segítségével) a rizsárak átlagát azon rekordokban, ahol a tej és kenyér árának összege 135 Ft-nál kevesebb. 8) Tegyük fel, hogy statisztikai kapcsolat van a liszt és a kenyér ára között. Adjon becslést a kenyér árára egy olyan üzletben, ahol a liszt csak 40 FT-ba kerül a statisztikai TREND függvény segítségével. 9) Rajzoljon grafikont a táblázat adataiból, ahol az egyes adatsorokat a boltok sorai adják. 10) A J oszlopban írassa ki azon boltok nevét, melyekben vagy a liszt ára nagyobb, mint 45 Ft, vagy a tej ára nagyobb 55 Ft-nál.
-20-
3. Feladat A 1 HÓ/KIADÁS 2 JANUÁR 3 FEBRUÁR 4 MÁRCIUS 5 ÁPRILIS 6 MÁJUS 7 JÚNIUS 8 JÚLIUS 9 AUGUSZTUS 10 SZEPTEMBER 11 OKTÓBER 12 NOVEMBER 13 DECEMBER (1993 évi adatok)
B ÉLELEM
C GÁZ
8000 7500 8100 8300 7800 7600 7900 8000 7600 8100 8300 8200
D VÍZ
2000 2200 1900 1400 700 500 450 550 650 900 1300 1800
E VILLANY
1300 1250 1280 1320 1350 1300 1400 1370 1330 1400 1350 1370
1000 1050 1100 1080 1100 1040 1000 1010 1040 1080 1100 1120
F TÖRLESZT.
6400 6400 6400 6400 6400 6400 6400 6400 6400 6400 6400 6400
1) Képezzen összeget havonként, átlagot, összeget költségnemenként! Állapítsa meg mennyi a legnagyobb, legkisebb érték költségnemenként, ill. a havi összegben! 2) Számítsa ki, hány % az egyes költségfajták évi összegének részesedése az egész évi költségekből! Számítsa ki, a havi összegek hányadai az évi kiadásnak! 3) Írassa ki azon hónapok nevét, amelyekben az összkiadás meghaladja az átlagos havi összkiadást! 4) Egészítse ki a táblázatot egy „ISKOLA” nevű oszloppal, melyben a téli hónapokban (amelyeknek neve „r” betűre végződik) a költség 10000 Ft, máskor az üdülési költségek miatt 15000Ft! 5) Készítsen gyakorisági táblázatot a havi élelemköltségek alapján 200 Ft-os intervallumokkal 7000 és 9000 között! 6) Rajzoltasson oszlopdiagramot, ahol az egyes adatsorokat a havi közüzemi díjak alkotják. 7) Másolja át a táblázatot a 2. munkalapra! Rendezze élelemköltség szerint növekvőre, másodlagosan a közüzemi díjak összege alapján növekvőre! 8) Növelje az A oszlop szélességét 12-re. Keretezze be a táblázatot, és húzza alá a fejlécet dupla vonallal. 9) Másolja át a táblázatot a törlesztés nélkül, azon rekordokat (sorokat)
szelektálva, melyekben a gázdíj legfeljebb 800 Ft, az élelem pedig legalább 8000 Ft. Az eredmény H1-től kezdődően tárolódjon.
-21-
4. Feladat A 1 Hónap 2 Január 3 Február 4 Március 5 Április 6 Május 7 Június 8 Július 9 Augusztus 10 Szeptember 11 Október 12 November 13 December 14 Átlag 15 Összeg
B Gyermek 2 2 2 2 3 3 3 3 3 3 3 3
C D Munkabér Szakasz.díj 28700 33500 31000 30700 35300 34600 29200 30800 32200 34000 33900 36600
E F Alk. Kedv. Adóalap 250 250 250 250 250 250 250 250 250 250 250 250
G Adó
xxxxxxxxx
(A gyermekek számának növekedését áprilisban új családtag születése okozta 1985-ben) Színezze sárgára a táblázat szöveges mezőit, és dupla vonallal válassza el az Összesen sort a Decembertől! 1) A szakszervezeti díj oszlopát úgy töltsük fel, hogy az a mindenkori munkabér 1%-a legyen. Az adóalap oszlopot úgy töltsük fel, hogy az alapbért csökkentsük a szakszervezeti díjjal, és gyermekenként 2000Ft-tal, amíg csak 2 gyermek van, ahol ennél több, ott gyermekenként 3000Ft-tal 2) Képezzük
a
megfelelő
oszlopösszegeket
(a
GYERMEKEK
oszlop
kivételével) ÁTLAGOT, MAXIMUMOT, MINIMUMOT. 3) A havi adó a havi munkabér 30%-a, ha az adóalap oszlop összege nem éri el az 1 milliót, egyébként 40%. 4) Ábrázolja oszlopdiagramon a munkabér és adóalap oszlopokat a hónap függvényében. 5) Határozza meg, hány alkalommal esik a munkabér 30.000ezer alá, 30.00035.000 közé, illetve 35.000 fölé. 6) Számítsa ki, mennyi az 1 főre jutó jövedelem havonta, majd ezek részesedését a családban az egész évi bevételből! ( A család tagjainak száma: gyermekek száma+2.) 7) Illesszen 1 közelítő egyenest a munkabérekre, szúrjon be egy oszlopot a hónapnevek elé, töltse fel 1-től sorszámokkal, ezeket használja független változóként (x-ként)! Becsülje meg, mennyi lesz az alapbér lineáris növekedést feltételezve a 13., 15., 18., 24. Hónapban (jövő év). Ábrázolja függvényként! 8) Másolja át a táblázatot a 2. Munkalapra, rendezze át a sorokat havi jövedelem
szerint növekvőre! -22-
5. Feladat
Hozza létre a következő fizetési jegyzék táblázatot: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
A Név Kiss Pál Kiss Irma Kiss József Nagy Irma Nagy Pál Nagy Előd Tóth Géza Tóth Ákos Horváth Pál Horváth Péter Horváth Ödön Kovács Pál Kovács Éva Kovács Edit Veres Pál Vértes Éva Juhász Pál Átlag Összeg
B C Alapbér Ny. járulék 39000 45000 125000 240000 500000 60000 80000 85000 90000 75000 150000 49000 54000 67000 76000 185000 210000
D E Mv. járulék Adóelőleg
F Kifizetés
1) Számítsa ki a hiányzó értékeket az alapbér függvényében. (Nyugdíj járulék 10%, Munkavállalói járulék 1,5%, az Adóelőleg 15%, ha az alapbér kisebb, mint 30.000, 30%, ha az alapbér 30.000 és 50.000 közt van, és 40% ha 50.000 fölött van. 2) Készítsen gyakorisági táblázatot az alábbi értékekre! Hány személy alapbére esik az alábbi osztópontokkal megadott intervallumokba: 40.000; 80.000; 120.000; 160.000; 200.000; 200.000 fölé? 3) Ábrázolja diagramon a gyakorisági táblázat értékeit az intervallumok függvényében. A vízszintes tengelyen legyenek a gyakorisági intervallumok a függőleges tengelyen az értékek. 4) Szúrjon be egy oszlopot a Név után, írja be az egyén személyi számát 1460217 alakban. Rendezze a táblázatot nemek szerint, illetve az azonos neműeket név szerint ABC sorrendbe! Számítsa ki külön a férfiak, külön a nők alapbérének összegét. 5) Számítsa ki egyénenként, hogy milyen %-ban részesül az egész csoport összalapbéréből. 6) Egészítse ki a táblázatot egy Nyugdíjas oszloppal. Jelölje meg azokat a
személyeket „Ny” betűvel, akik az idén betöltik legalább a 62. évüket!
-23-
6. Feladat Az 1. Táblázat egy vállalat termékeinek januári egységárát tartalmazza (Ft). A B C D E F G 1 ÁR Kerámia Fürdőkád Csempe Csaptelep Tükör Mosogató járólap 2 január 54 12000 43 6588 5000 75600
Egészítse ki az egységártáblázatot egész évre, havonta 0,5 százalékos áremelkedést feltételezve. Az év során havonta az alábbi mennyiségeket adták el: Darab Január Február
Kerámia Fürdőkád járólap 10000 20000 3400
Csempe 4 5 3
Csaptelep
Tükör
Mosogató
12000 90000 6000
December Töltse ki tetszőlegesen a hiányzó adatokat egész évre!
1) Számítsa ki, hogy az egyes termékekből átlagosan egy hónapban mennyit adtak el. 2) Mennyit adtak el összesen az egyes termékekből? Termékenként mennyi a legnagyobb, ill. legkisebb forgalmazás e hónapokban? 3) Számítsa ki, mennyi termékenként és havonta az árbevétel (Hasonlóan 6 oszlopos, 12 soros táblázat, melynek sorait a havi bevételek adják termékenként)! 4) A termékenkénti évi összes bevétel hány %-a az évi összes bevételnek? Ábrázolja ezt kördiagramon a termék nevével címkézve. 5) Mennyi a havi bevételek maximuma illetve minimuma? Készítsen egy gyakoriságtáblázatot a havi összbevételek eloszlásáról a legkisebb és a legnagyobb érték között! 6) A havi bevételek oszlopa mellett jelezze a hónap nevével azokat a hónapokat, amelyekben a maximális bevétel 75%-át eléri a bevétel (a hónap nevének bejegyzéséhez a táblázat 1 oszlopának értékeit használja)! 7) Lineáris növekedést feltételezve becsülje meg a következő év néhány hónapjában a várható bevételt. Az x értékek céljára a 13;14;…;17;18 (sor) számokat használja a hónapnevek helyett. 8) Egészítse ki a Bevétel táblát egy KEDVEZMÉNY oszloppal, melyben az „R” betűre végződő hónapokban (téli hónapok) 10% kedvezményt adunk az összbevételből. Mennyi a kedvezmény összege?
-24-
7. Feladat
Ön a megtakarításait különböző bankok értékpapírjaiban kamatoztatja. Ezen értékpapírokat különböző időpontokban vásárolta, és eltérő futamidejűek (1,3,6,9,12,13 hónaposak). A lejárat előtti felvételt a bankok alacsony kamattal büntetik, csak a lejáratig érvényes az értékpapír meghirdetett kamata, utána a látraszóló betét kamatát számítják fel. Az egyes értékpapírok jellemzőit az alábbi táblázat tartalmazza : A
B
C
D
1
2 3 4 5 6 7 8 9
Bank neve
Betét időpontja
Lejárat időpontja
Összeg Ft
Mező Konzum OTP Polgári Konzum Budapest Külker Mező
95.01.18 95.02.05 95.05.23 95.08.11 95.09.03 95.11.24 96.01.04 96.03.08
96.02.18 96.02.05 95.11.23 96.02.11 96.06.03 96.02.24 96.07.04 96.06.08
20,000 15,000 35,000 40,000 10,000 30,000 45,000 25,000
E
F
Éves kamatláb Lejárat előtt
G Éves kamatláb Lejártakor
20% 18% 5% 18% 16% 12% 8% 16%
30% 28% 24% 23% 27% 23% 20% 22%
Éves kamatláb Lejárat után
15% 20% 10% 15% 20% 13% 10% 16%
1) Válassza ki valamennyi oszlopból a legkisebb (minimum függvény), legnagyobb (maximum függvény) értéket, számítsa ki az átlagot (dátumok középértékét is), a Betét összege oszlop összegét! 2) Rendezze sorba a táblázatot elsődlegesen a bank neve, másodlagosan a betét összege alapján. 3) Készítsen gyakorisági táblázatot a betétek összege oszlopból 10,000 alsó, 50,000 felső korláttal, 10,000 intervallumhosszal. 4) Egészítse ki a táblázatot egy ’Betét értéke lejáratkor’ oszloppal. 5) Írja be az A15 mezőbe: ’A kivét időpontja’! Írja be C15 mezőbe. 96.03.13.! Egészítse ki a táblázatot egy ’ Betét értéke kivétkor’ oszloppal, amely a A15 mezőbe írt dátum alapján számolja ki a betét kivétkori értékét (figyelembe véve az egyes időintervallumokra érvényes betéti kamatokat). 6) Összegezze az előzőleg számított oszlop adatait, és határozza meg, hogy a betétek összege a kivétig hány százalékot kamatozott. 7) Készítsen kördiagramot a betétek összegéből, címkézze a bankok nevével.
-25-
8. Feladat Ön a BÉT (Budapesti Értéktőzsde) devizapiacán szándékozott a pénzét befektetni 1993-ban. Az alábbi táblázat mutatja a kiválasztott valuták elmúlt félévi kondícióit (éves betéti kamat 1 hónapos lekötés esetén, illetve az egyes valuták középárfolyamát):
1 2 3 4 5 6
A Valuta neve DEM USD JPY ITL
B kamat % 3,3 5,3 0,4 9,1
C Május Ft 99,06 152,55 1,424 ,09794
D Június Ft 98,5 150,2 1,418 ,0952
E Július Ft 100 153 1,438 ,0979
F Aug. Ft 102,2 155,3 1,47 ,0992
G Szept. Ft 103,5 157 1,495 ,1007
H Okt. Ft 105 159,1 1,52 ,1023
I Nov. Ft 106,5 162,2 1,55 ,1039
Tegyük fel, hogy Ön mindegyik valutából 1000Ft értékben vásárolt májusban, és valutáit kamatozó devizaszámlára teszi. Decemberben visszaváltotta valutáit forintra, hogy a családnak megvehesse a szokásos ajándékokat. Mennyi hasznot hozott Önnek ez a 7 hónapos befektetés? Hasonlítsa össze ezt a hasznot egy forintbetét hozamával (az éves betéti kamat 1 hónapos lekötés esetén három hónapig 21%, utána 20% volt.). Ellenőrizze, milyen nyereséget ért volna el, ha úgy próbált volna befektetni, hogy a valuta nemét havonta változtatta volna úgy, hogy pénze mindig a legmagasabb hozamú valutában legyen! (hozam=kamatnyereség+árfolyamnyereség) Számítsa ki az így elérhető hasznot! Feltételezheti azt, hogy a valuták közötti átváltásnak nincs költsége. Ábrázolja az egyes valuták havonkénti hozamát oszlopdiagramon. Mekkorának kellett volna lennie az egyes valuták betéti kamatlábának, hogy a valuták hozama megegyezzen a forintbetétek hozamával (a befektetés 7 hónapja alatt).
-26-
J Dec. Ft 108 163,3 1,575 ,1054
JAVASLATOK A MEGOLDÁSHOZ 1. Feladat Megjegyzés: Az A,B,C stb. oszlophivatkozásokat és 1, 2, 3 stb. sorhivatkozásokat nem szükséges begépelni, ezeket az EXCEL beállítástól függően adja, nem is szükséges a táblázatot éppen erre a helyre helyezni, csak a magyarázatok kedvéért van feltüntetve. A raktárkészlet táblázatot célszerű az egységár táblázat alá elkészíteni, tetszőleges adatokkal, hogy ne kelljen oszlopokat beszúrni az egységár táblázat mellé helyezendő eredményeknek. A Szükséges KG oszlop nem tartozik az egységár oszlophoz, néhány üres oszloppal jelezzük ezt a tényt. Jelentése. egy vendéglátó egység által vásárlandó mennyiség. 1/1-3.). Az 1.,2.,3. feladat megoldását célszerű az egységár táblázat mellé helyezni, mivel annak minden sorához egy számérték tartozik. Használja a függvényvarázslót a képletek elkészítéséhez az egyik sorban (pl. a körte sorában), és kitöltőnyéllel másolja át őket a többi gyümölcsre! Használja a statisztikai függvénycsoport ÁTLAG, MAX, MIN függvényeit, illetve kerekítéshez a matematikai függvénycsoport KEREK függvényét. Megjegyzem, hogy a kerekítés függvény véglegesen megváltoztatja a számértéket, a további számítások a kerekített értékkel történnek, nem úgy, mint az eszköztár „Tizedesjegyek csökkentése” ikon, mely csak a képernyőn váló megjelenítést változtatja meg. A fizetendő összeg oszlopának sorait a minimumár és a „szükséges kg” azonos sorbeli elemének szorzata adja. Használhatjuk a SZORZAT függvényt is, de egyszerűbb a * műveleti jel használata. A két lehetőség nem ad azonos eredményt ha üres cella az egyik szorzandó 0 helyett. Ekkor a SZORZAT függvény 1 értékkel helyettesíti az üres cella értékét. Adjuk össze a szorzatokat! Használja az eszköztár AUTOSUM ikonját! 1/ 4) Az egységár illetve raktárkészlet táblázathoz hasonló méretű a tételes táblázat, melynek elemeit az előbbiek azonos pozíción levő elemeinek szorzata adja. A képletet az új táblázat valamelyik (pl. bal felső) sarkában célszerű elkészíteni, majd kitöltőnyéllel vízszintesen is és függőlegesen is másolni. (irányváltáskor egy pillanatra el kell engedni ez egeret, majd a kijelölt sor- vagy oszlopblokk kitöltőnyelével folytatni). 1/ 5) Az egységár illetve raktárkészlet táblázathoz hasonló méretű a tételes táblázat, melynek elemeit a raktárkészlet azonos pozíción levő elemének és a sorhoz tartozó -27-
gyümölcs átlagárának szorzata adja. Az átlagár oszlop oszlophivatkozását abszolúttá kell tenni. Lásd abszolút cím. 1/ 6) Számítsuk ki a raktárkészlet táblázat alatt az oszlopösszegeket (AUTOSZUM, kitöltőnyél)! Az alatta levő sorban válasszuk le az oszlop-rovatfejek utolsó 4 betűjét a JOBB függvénnyel! Ezt felhasználjuk egy sorral lejjebb egy HA függvényben, melyben a kérdezett logikai kifejezés. az oszlop-rovatfej utolsó 4 betűje egyenlő-e a „kofa” szövegkonstanssal. Igaz esetén az oszlop összraktárkészletét 0,95-dal szorozzuk, hamis esetén 0,8-del. Nagyobb gyakorlattal rendelkezők a JOBB függvényt beépíthetik belső függvényként a HA függvénybe, így egyetlen sor elegendő a második kérdés megválaszolásához. Lásd Beépített függvény. 1/ 7) Használja a statisztikai GYAKORISÁG függvényt! Adattömb az egységárak tábla számcellái, csoporttömbként gépeljük be egymás alatti cellákba a 100, 200, 300 számokat (kötőjelek nélkül)! Lásd TÖMBFÜGGVÉNY. 1/ 8) Célszerű kijelölni a raktárkészlet tábla oszlop-rovatfejeit, majd CTRL gombbal folytatva az oszlopösszegeket. Írjon informatív diagramcímet, az y tengelyre mértékegységet! Lásd DIAGRAM VARÁZSLÓ.
2. Feladat 2/ 1. AUTOSUM, kitöltőnyél. 2/ 2 ÁTLAG, MIN statisztikai függvények. 2/ 3. FORMÁTUM/CELLA/SZEGÉLYEK menü a megfelelő tömb kijelölése után. 2/ 4. Először készítse el a csoporttömböt. Egymás alatti cellákba gépelje be az 50, 80, 100 számokat! Elviekben a csoporttömbhöz tartozik a felső korlát hiányában üresen hagyott 100-at követő cella is (ide írhatjuk, hogy „100 fölött”, de ezen kívül csak számokat tartalmazhat a csoporttömb, kötőjeleket, szövegeket nem).Jelöljünk ki az eredménynek legfeljebb 1-gyel több cellát, mint ahány számcella van a csoporttömbben.
Építsük
fel
a
GYAKORISÁG
függvényt,
kattintsunk
a
szerkesztőlécre, majd CTRL+SHIFT+ENTER gombkombinációval zárjuk le. 2/ 5. Jelölje ki az egész táblázatot (rovatfejekkel együtt), használja az ADATOK/SORBARENDEZÉS menüt! A másodlagos szempont oszlopát a „MAJD” cím után kell kiválasztani. Megjegyzem, hogy ez nem két külön rendezést jelent, a másodlagos szempont csak azon sorok sorrendjét befolyásolja, amelyekben az elsődleges szempontban (KOSÁR) egyező értékek vannak. -28-
2/ 6. Készítsen kritériumtáblát, ami azonos a szűrőtartománnyal (ADATBÁZISFÜGGVÉNYEK)! Használja az ADATOK/ SZŰRÉS/ IRÁNYÍTOTT SZŰRÉS menüt! A célhelynek csak a bal felső celláját kell megadni. LISZT
RIZS
>=44
<=143
2/ 7. a) Adatbázis függvény nélkül: Alkalmazzunk HA függvényt kitöltőnyéllel oszlopban másolva, melynek logikai vizsgálatában soronként döntsük el, hogy a lisztár + tejár >135 teljesül-e (a „lisztár” és „tejár” helyére a sorbeli cellahivatkozások kerülnek)! Igaz esetén a sorbeli rizsár legyen a HA függvény eredménye, hamis esetén valamilyen szöveg, például „-„! Ezután használjuk erre az oszlopra a beépített ÁTLAG függvényt, amely a szövegcellákat kihagyja az átlagolásból. b) Az AB.ÁTLAG adatbázis-függvény használata előtt képezzük a táblázat következő kenyértej névvel ellátott oszlopában a kenyér- és tejárak összegét soronként. Készítenünk kell a táblázattól külön egy kritériumtartományt, amely azon rovatfejekből áll amelyekre korlátozó feltételek vannak, illetve alatta a feltételek relációi. Esetünkben. kenyértej >135 Ezután a rizs oszlopa alatt egy üres cellában alkalmazhatjuk az AB.ÁTLAG függvényt. Adatbázis az egész táblázat rovatfejekkel, kenyértej oszloppal együtt, mező a rizs rovat, rovatfej cellájával hivatkozva, kritérium az előbbi tartomány cellahivatkozása. 2/ 8. Ismert X-ek a lisztárak, ismert Y-ok a kenyérárak, új X=40. 2/ 9.Jelölje ki az egész táblázatot rovatfejekkel együtt! Írjon informatív címet! 2/ 10. Alkalmazzon HA függvényt, melynek logikai vizsgálata egy VAGY függvény által adott logikai érték (igaz, vagy hamis). A VAGY függvény két bemenő adata.az adott sorbeli lisztár>45 illetve
az adott sorbeli tejár> 55. Természetesen a
kitöltőnyeles másoláskor a lisztár illetve tejár cellahivatkozása soronként változik. Ha a VAGY függvény eredménye igaz, a HA függvény eredménye az adott sorbeli bolt neve (cellahivatkozás, mely kitöltőnyeles másoláskor változik), ha a VAGY függvény eredménye hamis, a HA függvény eredménye „-”! Természetesen nem mi, hanem a
-29-
gép dönti el soronként, hogy melyik értéket kell a cellába írnia, így nekünk mindkét lehetőséget fel kell tüntetnünk a képletben.
3. Feladat 3/ 1. Használja az AUTOSUM ikont január sorában, majd kitöltőnyéllel másolja a képletet a többi hónapra függőlegesen! Az élelem oszlop alatt számítsa ki a költségenkénti évi összeget, átlagot (függvényvarázsló ÁTLAG függvénye, majd alatta AUTOSUM), kitöltőnyéllel egyszerre másolja a képleteket a többi oszlop alá egészen az előbb képzett havi összegek alá is! Megjegyzem, szándékosan vettem előre az átlagot, utána az összegképzést, mivel tapasztalatom szerint sokan csak közvetlenül az összegzendő cellák alá vagy mellé tudják az összeget képezni, elrontva vele a közbeeső egyéb adatokat (itt az átlagot), vagy azt is hozzáadják az összeghez. Ennek az a hibás gyakorlat az oka, hogy az AUTOSUM használata előtt nem az eredmény helyét jelölik ki, hanem az összeadandó cellákat! 3/ 2. Vegyük észre, hogy a havi összegek összege az évi kiadás! Ezzel a cellával kell osztanunk
sorban
a
költségnemenkénti
összegeket
(vízszintes
kitöltőnyeles
másoláshoz az évi összeg cellahivatkozásának oszlopindexét kell abszolúttá tenni), majd oszlopban a havi összegeket (függőleges kitöltőnyeles másoláshoz az évi összeg cellahivatkozásának sorindexét kell abszolúttá tenni). 3/ 3. Használja a HA függvényt! Logikai kifejezésben (kitöltőnyeles másolás után) soronként eldöntendő, hogy a havi összkiadás nagyobb-e a havi kiadások átlagánál. A havi összkiadás hónapról hónapra a relatív címzésnek megfelelően változó cella, míg a havi kiadások átlaga egyetlen sorindexben abszolút címzésű cella. Ha a logikai kifejezés igaz, az eredménycella értéke ugyanezen sorban a hónapnevet tartalmazó cella értéke lesz (relatív címzéssel kitöltőnyéllel változtatható), hamis esetén az eredménycella üres maradjon (a semmit is idézőjelbe kell tenni bármely más szövegkonstanshoz hasonlóan)! 3/ 4. Alkalmazzon HA függvényt, melynek logikai kifejezésében soronként eldöntendő, hogy a hónap neve „r”-re illetve nem ”r”-re végződik! A hónap nevének utolsó betűjét beépített függvényként a JOBB függvénnyel választhatja le. Felhívom figyelmüket arra, hogy beépített függvények függvényvarázslós felépítése előtt célszerű a külső függvény minden más adatát kitölteni, mivel a beépített függvény párbeszédablakának kitöltése után nincs mód visszatérni a külső függvény -30-
párbeszédablakához.
Belső
függvények
függvényvarázslós
aktivizálása
a
szerkesztőléc cellahivatkozásának legördülő listájából lehetséges! 3/ 5. Először el kell készítenie a csoporttömböt (csak számokat tartalmazhat, kötőjeleket nem)! Azután jelölje ki az eredmény helyét (legfeljebb eggyel több cella, mint a csoporttömb celláinak száma)! Építse fel a GYAKORISÁG függvényt! Kattintsa a kurzort a szerkesztőlécre, zárja le a függvényt a CTRL+SHIFT+ENTER billentyűkombinációval! 3/ 6. Jelölje ki a kért adathalmazt a hónapnevekkel, rovatfejekkel együtt CTRL gomb segítségével! Használja a diagramvarázslót, írjon informatív címet! 3/ 7. Jelölje ki a rendezendő táblázatot rovatfejekkel együtt! Használja az ADATOK menü SORBARENDEZÉS funkcióját! Felhívom figyelmüket arra, hogy nem elegendő csak a rendezési utasításban megadott oszlopot, vagy sort kijelölni, mivel egész sorok vagy oszlopok cserélnek helyet rendezéskor, hiszen ezek összetartozó adatok. 3/ 8. Jelölje ki az A oszlopot, használja a FORMÁTUM/ OSZLOP/ SZÉLESSÉG menüt! Jelölje ki a megadott blokkot, használja a FORMÁTUM/ CELLA/ SZEGÉLYEK menüt! 3/ 9 Készítsen kritériumtáblát, ami azonos a szűrőtartománnyal (ADATBÁZISFÜGGVÉNYEK)! Használja az ADATOK/ SZŰRÉS/ IRÁNYÍTOTT SZŰRÉS menüt! A célhelynek csak a bal felső celláját kell megadni.
4. Feladat Az azonos értékű oszlopok kitöltéséhez használja a kitöltőnyelet! Használja a FORMÁTUM/ CELLA/ SZEGÉLYEK menüt! 4/ 1. D2 cella tartalma: C2*0,01 , Kitöltőnyél! F2
cella
tartalma:
HA(B2=2;C2-D2-E2-B2*2000;C2-D2-E2-B2*3000)
Kitöltőnyél! 4/ 3. G2 cella tartalma: HA(F$15<1000000;C2*0,3;C2*0,4) Kitöltőnyél! 4/ 4. Jelölje ki az adóalap, munkabér és hónapok oszlopot a rovatfejekkel együtt CTRL gomb segítségével, használja a diagram-varázslót! Informatív cím a táblázat érvényességének ideje! 4/ 5. GYAKORISÁG függvény 4/ 6. H2 cella tartalma: C2/(B2+2) Kitöltőnyél -31-
I2 cella tartalma: H2/C$15
Kitöltőnyél
4/ 7.A hónapnevek elé írt sorszámok csak tartalmi szempontból sorszámok, nem írhatunk utánuk pontot! Akár a TREND függvényt, akár az egyenes egyenletének MEREDEKSÉG és METSZet függvényét használjuk, a független változó értékei csak számok lehetnek (ponttal szöveggé válnának) Külön cellában számítsuk ki az illesztendő egyenes (y=mX+b) meredekségét (m) illetve metszetét (b) a hasonló nevű statisztikai függvénnyel! A becsléshez gépelje be a 13,14,18, …24 számokat egy-egy egymást követő cellákba, ezek lesznek az új X-ek! Használja például a C20:C32 blokkot! Ezután építse fel az y=m*X+b
egyenes
cellahivatkozásaival!
egyenletét (D20
a
meredekség,
tartalma:
metszet
B$20*C20+B$21,
és
X
amennyiben
értékeinek B20
a
meredekséget, B21 a metszetet tartalmazza. Függőleges kitöltőnyeles másoláshoz a sorindexet abszolúttá kell tenni, vízszintes másoláshoz az oszlopindexet!) Alkalmazhatjuk a TREND függvényt is, itt se felejtkezzünk meg az abszolút címek használatáról az ismert X-eket (1-től 12-ig a hónapokat jelentő számok) illetve az ismert Y-okat (alapbérek blokkja 1-től 12-ig terjedő hónapokban) illetően. Ennél a megoldásnál nem ismerjük az egyenes egyenletét. Függvényábrázolás a diagramvarázsló XY típusú diagramjával lehetséges csak, annak érdekében, hogy az X értékek az X tengelyen arányosan (nem feltétlenül egyenlő növekménnyel) helyezkedjenek el. Természetesen minden függvénypontot egy XY koordinátapár ad meg, ehhez CTRL gomb segítségével ki kell jelölni az X-ek és Y-ok halmazát együtt. Miután ábrázoltuk 1-től 12-ig terjedő hónapok alapbéreit, mód van ugyanebben a diagramban ábrázolni a becsült értékeket is(13-tól 24-ig). 4/
8.
Másolás
előtt
jelölje
ki
a
másolandó
blokkot,
használja
a
SZERKESZTÉS/MÁSOL menüt, vagy a megfelelő ikont az eszköztárból! A 2. munkalapon elegendő a célblokk bal felső cellájának kijelölése, majd használjuk a SZERKESZTÉS/BEILLESZT menüt! Rendezéshez jelölje ki a teljes rendezendő blokkot (minden sor és minden oszlop), majd használja az ADATOK/SORBARENDEZÉS menüt. Ügyeljen a „VAN ROVATFEJ” opció helyes beállítására!
-32-
5. Feladat 5./ 1. C2 cella tartalma:B2*0,15 kitöltőnyél! D2 cella tartalma:B2*0,015 kitöltőnyél! E2 cellában használjunk kettős HA függvényt! A külső HA függvény logikai vizsgálatával döntsük el, hogy az adott sorban (kitöltőnyél használatával) az alapbér kisebb-e 30 ezernél, ekkor az igaz-érték párbeszéd-dobozában számítsuk ki az alapbér 0.15-szorosát! A hamis-érték dobozában használjunk beépített HA függvényt, mellyel már csak azt kell eldöntenünk, hogy az alapbér 50 ezer alatt, illetve fölött van-e (ezen HA függvény csak akkor érvényes, ha az alapbér 30 ezer felett van). Ennek megfelelően töltsük ki az „igaz-érték esetén” illetve „hamis-érték-esetén” dobozokat! HA(B2<30000;B2*0,15;HA(B2<50000;B2*0,3;B2*0,4)) 5./ 2. Először készítse el a csoporttömböt (pl. A20 cellában 40000, A21-ben 80000, ezek kijelölésével számtani sorozat kitöltőnyéllel 200000-ig), majd alkalmazza a statisztikai GYAKORISÁG függvényt! 5./ 3. Célszerű először csak a gyakorisági értékeket kijelölni, oszlopdiagramban ábrázolni, a diagramvarázsló ADATSOR oldalán a kategóriacímkék (X tengely felirata) című doboz tartalmát töltsük ki a csoporttömb hivatkozásával kijelöléssel! 5./ 4. Oszlopbeszúráshoz használja a BESZÚRÁS menüt, miután kijelölte a B oszlopot! A személyi számok beírása után készítsen egy új oszlopot, amely csak a nemre vonatkozó információt tartalmazza, pl. BAL(B2;1) képlet lehet a H2 cella tartalma (kitöltőnyél). Jelölje ki a táblázatot a NEME oszloppal együtt, majd használja az ADATOK/SORBARENDEZÉS menüt! Első szempont a NEME oszlop, a MAJD utáni második szempont a NÉV oszlop.(A NÉV szerinti rendezés csak ott számít, ahol a NEME oszlopban egyező értékek vannak.) A
csoportonkénti
összeg,
átlag
képzéséhez
használjuk
az
ADATOK/RÉSZÖSSZEGEK menüt! Csoportképző alap a NEME oszlop. 5./ 5. Soronként ossza el az alapbért az 5./ 4. feladatban kapott végösszeggel! Használjon abszolút címet, kitöltőnyelet! 5,/ 6. Használjon HA függvényt! Egy személy korának megállapításához használja a szövegfüggvények
csoportjából
a
KÖZÉP
függvényt!
év:”19”&KÖZÉP(B2;3;2), kor: ÉV(MA())- születési év képlete) -33-
(születési
6. Feladat. Az ÁR táblázat 2. sorát képlettel határozzuk meg! B3 cella tartalma.B2*1,005. Kitöltőnyéllel másoljuk vízszintesen a februári értékek meghatározásához, majd függőlegesen is, hiszen minden hónapban az egységár az előző haviból számítandó. A DARAB, sőt a későbbi táblázatok rovatfejeit másolással állítsa elő, így nem kell újra meg újra a formátumot sem beállítani például a hosszú, két szavas cellák esetén sortörésesre! 6/ 1. Az ÁTLAG függvényt a DARAB táblázat alatt a táblázat oszlopaira használja! 6/ 2. AUTOSUM, MIN, MAX függvények a DARAB táblázat oszlopaira! 6/ 3. A BEVÉTEL tábla minden értékét az ugyanezen pozíción levő egységár és eladott darab szorzata adja. Adja össze a bevételtábla sorainak is és oszlopainak is a számértékeit! 6/ 4. Az előző feladatban a bevételtábla jobb alsó sarkában megkaptuk az egész évi bevételt (vagy a havi összegek összegeként, vagy a termékenkénti évi bevételek összegeként). Ezzel a cellával kell az oszlopok összegeit elosztani. Használjon abszolút címet! A diagramkészítéshez CTRL gomb segítségével a termékenkénti összegeken (vagy százalékokon) kívül a rovatfejeket is jelölje ki! A diagramvarázsló „diagram beállításai” oldalán érdemes az értékeket vagy a százalékokat is megjeleníttetni. Ne felejtsen el informatív címet írni! 6/ 5. Használja a MAX illetve MIN függvényt! Ossza fel (fejben is lehet) a maximum és minimum közti intervallumot négy, öt nagyjából egyenlő részre (használhat ezresekre kerekített értékeket is), ezekből a számokból készítse el a csoporttömböt! Használja a GYAKORISÁG függvényt! 6/ 6. HA(adott havi bevétel> havi bevételek maximuma*0,75 ; adott sorbeli hónapnév;”-„) Az adott havi bevétel, havi bevételek maximuma, adott sorbeli hónapnév helyére cellahivatkozásokat kell írnia, a havi bevételek maximumát abszolút címmel használja! 6/ 7.Az ismert hónapok bevételei mellé, vagy a hónapnevek elé szúrja be az 1-12 számokat (csak tartalmilag sorszámok, nem tehetünk utánuk pontot, nehogy szövegtípusúvá váljanak). A becsült értékek leendő cellái elé gépelje be az új X-eket! Használja a TREND függvényt! Az ismert X-ekre, illetve Y-okra használjon abszolút címet, és ekkor kitöltőnyéllel kaphatja a további becsült értékeket, vagy a TREND függvény használata előtt jelölje ki az eredmények blokkját, és kezelje
-34-
tömbfüggvényként a TREND-et (a kijelölt blokk első cellájában legyen a képlet, kurzort kattintsa a kész képletsorba , majd CTRL+SHIFT+ENTER)! Több információhoz juthat, ha a 4. feladatsor 7. feladata szerint az egyenes egyenletét is felírja, illetve ábrázolja is az értékeket. 6/ 8. Használjon HA függvényt! Logikai vizsgálatával soronként döntse el, hogy a hónap neve „r”-re végződik-e vagy sem! A hónap nevének utolsó betűjét a JOBB függvénnyel választhatja le. JOBB(hónap nevének hivatkozása;1)=”r” igaz-e.
7. Feladat A rovatfejeket 1 cellasorba írja, hogy a táblázat sorbarendezésekor használhatók legyenek! Használja a FORMÁTUM/ CELLA/ IGAZÍTÁS/ SORTÖRÉSSEL több sorba menüt! 7/ 1.Kitöltőnyeles másoláskor a számformátumot is másoljuk, ezért lehetséges, hogy a százalékok minimuma, átlaga dátumformátumban jelenik meg. A számolások elvégzése után az egyforma adattípusokat blokként kijelölve állítsuk be a megfelelő számtípust a FORMÁTUM/ CELLA/ SZÁM menüben. 7/ 2.ADATOK/ SORBARENDEZÉS a táblázat kijelölése után. 7/4.Az éves kamatot napi kamatra számítjuk át 360-nal való osztással (banki év napjainak száma). A betéttől a lejáratig eltelt napok számát a két dátum különbsége adja. Így a H2 cella tartalma. D2*F2/360*(C2-B2)+D2 7/ 5.Alkalmazzuk a HA függvényt! Logikai vizsgálatával döntsük el, a kivét időpontja a lejárat előtt van-e! Igaz esetén a 7/ 4.-es megoldáshoz hasonlóan járunk el a lejárat előtti kamatlábakkal. Hamis esetén a lejáratkori betétérték (7/ 4.-es feladatban már kiszámítottuk) kamatozik a lejárat utáni napokon a lejárat utáni kamatlábbal. Abban az esetben, ha a kivét időpontja a lejárat napja, a kivét dátumának és a lejárat dátumának különbsége 0, tehát az eredmény a lejáratkori érték marad. 7/ 6. Betétek_összege*kamatláb+betétek_összege = kivétek_összege egyenletet kell a kamatláb változóra kifejezni, és egy cellában kiszámíttatni.
-35-
8. Feladat
K3 cella tartalma: 1000/c3*(1+7*B3/1200)*j3 B3/1200 az évi kamatláb egy hónapra eső része százalékban. Kitöltőnyéllel függőlegesen K6-ig másoljuk. K2 a forintbetét értéke: 1000*(1+3*21/1200+4*20/1200) A havi hozamokat a D7.J10 táblában számoljuk kitöltőnyél használatával. Minden hónapban csak 1000 Ft-ot váltunk be valutára. D7 cella tartalma: 1000/c3*(1+$B3/1200)*D3-1000 D11 cella tartalma: MAX(D7:D10) K11 cella tartalma: SZUM(D11:J11) Az utolsó kérdés megoldásához használja az ESZKÖZÖK/ CÉLÉRTÉKKERESÉS menüt! A célcella annak a cellának a hivatkozása, amelyben a valutához tartozó hozam-képlet van. Célérték a forintbetét hozama. Módosuló cella a valutához tartozó kamatláb cellájának hivatkozása.
-36-
