Mérnöki informatika I

Mérnöki informatika I. jegyzet

Microsoft Excel

Összeállította:

Vámosi Attila tanársegéd Debreceni Egyetem Műszaki Kar Műszaki Alaptárgyi Tanszék 2015.

Gyakorlaton a Microsoft Excel 2007-es magyar nyelvű verziójával dolgozunk. A jegyzetben szereplő leírások, feladatok és megoldások is erre a verzióra vonatkoznak. A korábbi verziókhoz képest esetenként némi eltérés tapasztalható. Az egységes szóhasználat és az egyértelmű leírások érdekében tekintsük át az alábbi alapfogalmakat: Munkafüzet Munkalap Oszlop Sor Cella

– ez maga az Excel állomány. – a munkafüzet egy oldala, ezen a felületen dolgozunk – a munkalap oszlopai, melyekre betűkkel hivatkozhatunk – a munkalap sorai, melyekre számokkal hivatkozunk – egy oszlop és egy sor metszéspontja, ide kerülnek be az adatok, képletek. A cellára az oszlopának betűjelével és sorának számával hivatkozunk (pl.: A1 vagy B3) Cella tartomány – több cellából álló téglalap alakú terület, melyre a bal felső és a jobb alsó cellájának jelével hivatkozunk (pl: az A1:B3 tartomány az A1, A2, A3, B1, B2 és B3 cellákat tartalmazza.) Szerkesztőléc – A cellába kerülő adat, képlet szerkesztésére szolgáló sor.

Cellaformázás Egy cellát vagy cellatartományt az egérrel kijelölve, majd az egér jobb gombját megnyomva a legördülő menüből a Cellaformázás… menüpontot választva állíthatjuk be a cella vagy cellák tulajdonságait. Beállítható tulajdonságok:  Szám: a cellába kerülő adat típusa, mely előre definiált kategóriákból választható  Igazítás: a cellába kerülő adat cellán belüli elhelyezése, iránya, elforgatása adható meg  Betűtípus: a cellába kerülő adat megjelenítése, betűtípus, betűstílus, betűméret, szín megadása  Szegély: a cella rácsainak stílusa, vastagsága, színe állítható be  Kitöltés: a cella hátterének kitöltése színnel vagy valamilyen mintázattal  Védelem: a cella elrejtése vagy szerkesztésének zárolása állítható be 1 Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel

Adatbevitel, értékadás Adatot a cellába egyszerűen a cellát kijelölve a billentyűzetről begépeléssel vihetünk be. Ha ugyanazt az értéket több egymás alatti vagy melletti cellába is be kell írnunk, akkor elég csak az első cellát kitöltenünk, majd a kijelölt cella jobb alsó sarkában lévő kis négyzetet megfogva (egérgombot lenyomva tartva) a megfelelő irányba húzva az egeret az Excel automatikusan bemásolja az értéket a többi táblába is. Vannak előre definiált listák, melyeket az Excel automatikusan felismer, ilyenek például a hét napjai, vagy a hónapok nevei. Elég csak begépelni a pl. a hétfő vagy a január szavakat és az Excel folytatólagosan kitölti a cellákat a következő értékekkel. Ha egy cellába beírjuk, pl. hogy 2+3 akkor ezt a program egy szövegként kezeli és a cellában ez a szöveg fog megjelenni. Ha elé írunk egy egyenlőség jelet (értékadás) akkor a program kiértékeli a beírt kifejezést és az eredményt jeleníti meg. Például =2+3 szöveg beírása után a cellában 5 fog megjelenni. Ha kijelöljük a cellát, akkor a szerkesztőlécben viszont a konkrét kifejezést látjuk. Cellákba beírt értékekkel úgy tudunk számolni, hogy a kifejezésben a számok helyett a számokat tartalmazó cellák címeit (a cella betű és szám kódja) adjuk meg. Tehát ha az A1 cellába beírunk egy 2-est, a B1 cellába pedig egy 3-ast és a C1 cellába az =A1+B1 kifejezést, akkor a C1 cellában az A1 cellában és a B1 cellában szereplő számok összege fog megjelenni, vagyis egy 5-ös. Ha ezek után A1 vagy B1 cella tartalmát átírjuk, akkor az Excel újraszámolja a C1 cellába beírt kifejezést és a cellában megjelelő szám automatikusan megváltozik. Az értékadást és az előbb említett tartalommásolást együtt használva beírhatunk sorozatokat is. Megadjuk az első cellába a sorozat első értékét (pl. A1 cellába egy 1-est) majd a mellette lévő cellába a sorozat következő elemének kiszámítására vonatkozó kifejezést, hivatkozva benne az első elem cellájára (B1 cellába =A1*2 kifejezést írva). Ezután a második cellát kijelölve és a jobb alsó sarokban lévő kis négyzetet megfogva elhúzzuk az egeret a kívánt irányba és a cellákban sorra megjelennek az előző cellák értékeinek a kétszeresei. Ha megnézzük az előbb másolt cellák tartalmát láthatjuk, hogy a bennük lévő hivatkozás mindig az előtte lévő cellára változott. Erre figyeljünk oda, mert ez a másolás minden hivatkozást átír (relatív hivatkozás). Ha ez előző feladatot például úgy akarnánk megoldani, hogy a szorzószám változtatható legyen, vagyis A1 cellába kerül a szorzó 2-es, B1 cellába a kezdő 1-es és B2 cellába írjuk az =A1*B1 kifejezést, akkor a B2 cellában még jó eredményt kapunk, de a másolás után a többi cellában 0 jelenik meg. Ez azért van, mert a másolt cellákban mindkét hivatkozás módosul és mivel az első sorban az A1 cellán kívül nincs más kitöltve így lényegében 0-val való szorzás keletkezik. Ahhoz, hogy ezt kivédjük B2 cellában lévő kifejezésben A1 cellát rögzítenünk kell (abszolút hivatkozást kell létrehozni). Ezt úgy tehetjük meg, hogy az A és az 1 elé is $ jelet teszünk, vagyis a kifejezést =$A$1*B1 alakra javítjuk. Ha ezután másoljuk a cellát, akkor a kifejezésben a $A$1 végig megmarad és csak a másik hivatkozás módosul.

2 Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel

Képletek, függvények Láthattuk, hogy az alapműveleteket (összeadás, kivonás, szorzás, osztás) egyszerűen a műveleti jeleik (+, -, *, /) begépelésével megadhatjuk. Ugyanígy alkalmazható a műveletek sorrendjének módosításához a zárójelek ( ) használata is. Vannak viszont olyan műveletek, pl. a hatványozás, aminek számítástechnikában alkalmazott jelölését nem biztos, hogy mindenki ismeri. (Hatványozás egyébként megadható a számok összeszorzásával is, például 24  =2*2*2*2 is, de nagyobb hatványoknál ezt nem célszerű használni.) Hatványozásra a számítástechnikában elfogadott jelölés az AltGr+3 gombokkal előhívható ^ jel. Ezt használva 24 megadható =2^4 alakban is, ahol az első szám a hatványozandó szám, a második pedig a kitevő. Harmadik lehetséges megoldás hatványozásra a HATVÁNY függvény használata. Függvényeket a Képletek menüfülre váltva a megjelenő Függvénytárból választva szúrhatunk be egy cellába. Ha nem tudjuk a függvény pontos nevét vagy azt, hogy melyik csoportban keressük, akkor az fx Függvény beszúrása ikont választva rákereshetünk a függvény nevére vagy annak töredékére. Függvény beszúrása is mindig az = jellel kezdődjön, ennek hiányában az Excel csak szövegként kezeli a beírt kifejezést és nem számítja ki az értékét. A függvénynek van egy neve (kis és nagybetű itt nem számít) és a neve után zárójelben az attribútumai. Ha nincs neki ilyen, akkor is kell a név után egy üres () zárójel! A HATVÁNY függvénynek például két attribútuma van, melyeket pontosvesszővel kell elválasztani egymástól. Az első a hatványozandó szám, a második a kitevő. Tehát használva 24 függvény használatával =HATVÁNY(2;4) alakban írandó. A másik ilyen művelet, ami viszont nem érhető el billentyűzetről az a gyökvonás. Az Excel a négyzetgyök számítására tartalmaz függvényt. A √2 az alábbi formában számítható ki: =GYÖK(2) , ahol a zárójelben az a szám szerepel, melynek négyzetgyökét keressük. Az Excel rengeteg függvényt ismer, de nem mindet. Például köbgyök számítására nem tartalmaz függvényt. Az ilyen feladatokat matematikai összefüggésekkel kell megvalósítani. Köbgyök számítása például törtkitevőjű hatvány számításával oldható meg, felhasználva az alábbi matematikai azonosságot, miszerint √ =

azaz

√ =

Tehát például 2 köbgyökét úgy határozhatjuk meg, hogy a 2-t 1/3-ik hatványra emeljük, melyet leírhatunk =2^(1/3) alakban (a zárójelet ne felejtsük el, különben egész más eredményt kapunk) vagy =HATVÁNY(2;1/3) alakban is.

3 Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel

Gyakorló feladatok: Gyakorló feladat 1. Legyen adott egy derékszögű háromszög két befogójának hossza az A1 és a B1 cellában. Pitagorasz tétele felhasználásával C1 cellába számítsuk ki az átfogó hosszát! Megoldás: Pitagorasz tétele: + = melyből = √ + Egyik lehetséges megoldás, ha a C1 cellába az alábbi kifejezést írjuk: =GYÖK(A1^2+B1^2) vagy az ezzel egyenértékű: =GYÖK(HATVÁNY(A1;2)+HATVÁNY(B1;2))

Gyakorló feladat 2. Adott az alábbi másodfokú egyenlet: 2x2–8x–10=0. A megoldóképlet felhasználásával számítsuk ki x1 és x2 értékét! Megoldás: ∓√

A másodfokú egyenlet megoldó képlete: , = Ebben a feladatban a=2, b= –8 és c= –10. A1 cellába az alábbi kifejezést írva kapjuk x1-et: =(8-GYÖK((-8)^2-4*2*(-10)))/(2*2) A2 cellába pedig az alábbi kifejezéssel x2-t: =(8+GYÖK((-8)^2-4*2*(-10)))/(2*2)

Gyakorló feladat 3. Készítsük el úgy a 2. feladatot, hogy a,b és c tetszőleges értékekkel megadható legyen! Megoldás: Tároljuk a értékét az A1 cellában, b értékét a B1 cellában és c értékét a C1 cellában! A2 cellába az alábbi kifejezést írva kapjuk x1-et: =(-B1-GYÖK(B1^2-4*A1*C1))/(2*A1) A3 cellába pedig az alábbi kifejezéssel x2-t: =(-B1+GYÖK(B1^2-4*A1*C1))/(2*A1)

4 Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel

Alapfüggvények A leggyakrabban használt statisztikai függvények a következők: Összesítés (SZUM függvény) Megadott számok vagy megadott tartományban szereplő számok összegét adja meg. Alakja: =SZUM(szám1;szám2;…) vagy =SZUM(tartomány) Példa: A1, A2 és A3 cellákban lévő számok összege: =SZUM(A1;A2;A3) vagy =SZUM(A1:A3)

Átlagszámítás (ÁTLAG függvény) Megadott számok vagy megadott tartományban szereplő számok átlagát adja meg. Alakja: =ÁTLAG(szám1;szám2;…) vagy =ÁTLAG(tartomány) Példa: A1, A2 és A3 cellákban lévő számok átlaga: =ÁTLAG(A1;A2;A3) vagy =ÁTLAG(A1:A3)

Darabszám (DARAB függvény) Megadott számok vagy megadott tartományban szereplő számok darabszámát adja meg. Alakja: =DARAB(szám1;szám2;…) vagy =DARAB(tartomány) Példa: A1: A10 cellákban lévő számok darabszáma: =DARAB(A1:A10)

DARAB2 függvény Megadott tartományban a kitöltött cellák darabszámát adja meg. (Vagyis a DARAB2 függvény megszámolja a betűket tartalmazó cellákat is, míg a DARAB függvény csak a számmal kitöltöttekkel foglalkozik.) Alakja: =DARAB2(szám1;szám2;…) vagy =DARAB2(tartomány) Példa: A1: A10 cellákban lévő kitöltött cellák darabszáma: =DARAB2(A1:A10)

DARABTELI függvény Megadott tartományban szereplő számok közül megszámolja azokat, melyek teljesítenek egy adott feltételt. Alakja: = DARABTELI(tartomány;feltétel) Példa: A1: A10 cellákban lévő, 10-nél nagyobb számok darabszáma: =DARABTELI(A1:A10;”>10”)

Minimum és maximum keresés (MIN és MAX függvény) Megadott számok vagy megadott tartományban szereplő számok közül a legkisebbet és a legnagyobbat adja meg. Alakja: =MIN(szám1;szám2;…) vagy =MIN(tartomány) illetve =MAX(szám1;szám2;…) vagy =MAX(tartomány) Példa: A1: A10 cellákban lévő számok közül a legkisebb: =MIN(A1:A10) és a legnagyobb: =MAX(A1:A10) 5 Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel

Gyakorló feladat 4. Adottak az alábbi számok: A 1 125 2 135 3 241 4 368 5 814 6 573 Számítsuk ki az összegüket és az átlagukat! Adjuk meg a legkisebb és a legnagyobb számot! Számoljuk meg hány cellában szerepel 300-nál nagyobb szám! Megoldás: A számok összegét az alábbi kifejezés adja: =SZUM(A1:A6) Az átlagot a következő kifejezéssel kapjuk: =ÁTLAG(A1:A6) A legkisebb szám: =MIN(A1:A6) A legnagyobb szám: =MAX(A1:A6) 300-nál nagyobb számot tartalmazó cellák száma: =DARABTELI(A1:A6;”>300”)

eredmény: 2256 eredmény: 376 eredmény: 125 eredmény: 814 eredmény: 3

Szögfüggvények Az Excelben a szögfüggvények kiszámítására megtaláljuk a használatos függvényeket (egy kivételével). Szög szinuszát a SIN(szög) függvénnyel, koszinuszát a COS(szög) függvénnyel, tangensét a TAN(szög) függvénnyel számíthatjuk ki. A kotangens értékre nincs külön függvény, de mivel a kotangens a tangens reciproka, ezért az 1/TAN(szög) függvénnyel kiszámítható. Fontos tudni, hogy ezek a függvények az argumentumukban radiánban megadott szög értékét adják meg, így ha fokban megadott szögnek szeretnék kiszámolni valamelyik értékét, akkor a szöget át kell váltani radiánba! Fokot radiánba úgy kell átváltani, hogy megszorozzuk π-vel és elosztjuk 180-nal. π értékét az Excel 15 tizedesjegy pontossággal ismeri és a PI() függvény tartalmazza. Tehát ha 60° szinuszát szeretnénk kiszámítani, akkor azt az alábbi képlettel tehetjük meg: =SIN(60*PI()/180) A radiánba történő átváltást a RADIÁN(fok) függvénnyel is végezhetjük, azaz =SIN(RADIÁN(60)) is jó megoldás. Az inverz szögfüggvények is léteznek ( ARCSIN(), ARCCOS(), ARCTAN() ). Ezek a függvények viszont a szöget radiánban adják vissza, a fokban szeretnénk, akkor át kell váltani, amit vagy úgy kapunk meg, hogy elosztjuk π-vel és megszorozzuk 180-nal vagy átváltjuk a FOK(szög) függvénnyel. Gyakorló feladat 5. Számítsuk ki 0° és 180° között 15°-onként adott szögek szinuszát, koszinuszát, tangensét és kotangensét! Megoldás: Írjuk A1 cellába 0-t, A2-be pedig =A1+15 kifejezést. Ezután az A2 cella jobb alsó sarkában lévő kis négyzettel húzzuk lefelé addig, míg 180-hoz nem ér. 6 Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel

B1 cellába az alábbi kifejezést írva kapjuk a szöghöz tartozó szinusz értéket: =SIN(RADIÁN(A1)) C1 cellába az alábbi kifejezést írva kapjuk a szöghöz tartozó koszinusz értéket: =COS(RADIÁN(A1)) D1 cellába az alábbi kifejezést írva kapjuk a szöghöz tartozó tangens értéket: =TAN(RADIÁN(A1)) E1 cellába az alábbi kifejezést írva kapjuk a szöghöz tartozó kotangens értéket: =1/TAN(RADIÁN(A1)) Ezután B1:E1 tartományt kijelölve a jobb alsó sarokban lévő kis négyzetet húzzuk le a 180° soráig! (Tangens és kotangens értékek megkaphatók a szinusz és koszinusz értékeket tartalmazó cellák hányadosaként is, tehát a tangens =B1/C1 a kotangens a=C1/B1 kifejezéssel is számítható.)

Kerekítések Ha a cellaformázás során beállítunk egy szám formátumot és a megjelenítendő tizedesjegyek számát 0-ra állítjuk, akkora cellában kerekített egész számok fognak megjelenni. Ez a beállítás csak a megjelenítést kerekíti, ha számolunk ezzel a cellával az érték továbbra is a tört szám marad. (Megjegyzés: Magyar rendszerben alapesetben a tizedesjelölés vesszővel történik. Ha a gépünk át van állítva és a tizedes jelölés pontra van állítva, akkor a vesszővel beírt számot a program szövegként kezeli. Érdemes leellenőrizni a beállítást, melyet az Office gombra kattintva (bal felső sarokban lévő logó), majd lent Az Excel beállításai gombra kattintva, baloldalon a Speciális sorra kattintva jobb oldalon középtájon találjuk a Tizedesjelhez beállított jelölést.) Ha a cella értékét is kerekíteni szeretnénk többféle függvény közül is választhatunk. A KEREKÍTÉS(szám;hány_számjegy) függvény a kerekítés szabályai szerint kerekíti a számot annyi tizedesjegyre, amennyit a hány_számjegy argumentumban megadunk. (Ha egészre szeretnénk kerekíteni, akkor adjunk meg 0-t. Negatív számjegy is megadható, pl: -2 számjegyre kerekítés azt jelenti, hogy százas értékre kerekítjük: KEREKÍTÉS(1234,567;-2) eredménye: 1200) A KEREK.FEL(szám;hány_számjegy) függvény minden esetben felfelé, a KEREK.LE(szám;hány_számjegy) függvény pedig lefelé kerekíti a számot a hány_számjegy értékben megadott tizedesjegyre vonatkozóan. Ha nem kell kerekíteni, akkor a CSONK(szám) függvény egyszerűen levágja a tört részt.

Logikai függvények Logikai függvényeket akkor használunk, ha egy állításról el kell dönteni, hogy az igaz vagy hamis. Ha az állítás igaz, akkor IGAZ értéket (a számítástechnikában használatos az 1 jelölés is), ha hamis akkor a HAMIS értéket (0 jelölés) rendeljük hozzá. Az IGAZ() és a HAMIS() függvények eredménye egy-egy ilyen logikai válasz. Tehát az IGAZ() függvény logikai IGAZ értéket, a HAMIS() függvény logikai HAMIS értéket ad vissza. Két állítás együttes vizsgálata is fontos lehet egyes esetekben. Aszerint, hogy a két állítás IGAZ vagy HAMIS összesen négyféle eset lehetséges: A állítás IGAZ IGAZ HAMIS HAMIS

B állítás IGAZ HAMIS IGAZ HAMIS 7

Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel

Két állítás összehasonlítása az ÉS(állítás1;állítás2) és a VAGY(állítás1;állítás2) függvényekkel lehetséges. Az ÉS(állítás1;állítás2) függvény csak akkor ad IGAZ eredményt, ha mindkét állítás IGAZ. Ha valamelyik vagy mindkettő HAMIS, akkor az eredmény is HAMIS lesz. A VAGY(állítás1;állítás2) függvény akkor ad IGAZ eredményt, ha legalább az egyik állítás IGAZ, és csak akkor lesz HAMIS, ha mindkét állítás HAMIS. A két függvény közötti különbség az alábbi táblában jól látható: A állítás IGAZ IGAZ HAMIS HAMIS

B állítás IGAZ HAMIS IGAZ HAMIS

A és B IGAZ HAMIS HAMIS HAMIS

A vagy B IGAZ IGAZ IGAZ HAMIS

HA függvény Feltételes vizsgálatokhoz a HA(logikai_vizsgálat;érték_ha_igaz;érték_ha_hamis) függvény használható. Ha a logikai_vizsgálat eredménye IGAZ, akkor a függvény eredménye az érték_ha_igaz argumentumban megadott lesz, ha a vizsgálat eredménye HAMIS, akkor a függvény érték az érték_ha_hamis argumentum lesz. Például a HA(A1>B1;A1;B1) eredménye A1 lesz, ha A1>B1 és B1 lesz ha A1B1;”A1 nagyobb”;”A1 nem nagyobb”) eredménye ”A1 nagyobb” lesz ha A1>B1 és ”A1 nem nagyobb” lesz ha A110;A1<20);”benne van”;”nincs benne”) Az ÉS függvény miatt a logikai vizsgálat csak akkor lesz IGAZ, ha mindkét feltétel IGAZ.

Gyakorló feladat 6. Döntsük el az A1 cellában megadott számról, hogy páros vagy páratlan! Az eredmény szövegesen jelenjen meg! Megoldás: HA függvényt fogunk használni, de előtte meg kell vizsgálni a számot. Az Excel 2010-es verziójában már van egy PÁROS.E(szám) függvény, mely IGAZ értéket ad vissza, ha a szám páros. A 2007-es verzióban ez a függvény angol névvel ISEVEN(szám) érhető el. Ezt is használhatjuk, vagy használhatjuk a MARADÉK nevű függvényt, mely egy számot elosztva egy másik számmal annak maradékát adja vissza eredményül. Ha a számot 2-vel osztjuk és maradéka nulla, akkor a szám páros. Tehát a megoldás a két változattal: =HA(ISEVEN(A1);”páros”;”páratlan”) vagy =HA(MARADÉK(A1;2)=0;”páros”;”páratlan”)

8 Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel

Gyakorló feladat 7. Adottak az alábbi számok: A 1 246 2 678 3 115 4 873 5 394 6 491 Számítsuk ki az átlagon felüli számok összegét! Megoldás: A feladat megoldható egy HA függvény és egy SZUM függvény használatával két lépésben vagy megoldható egy lépésben a SZUMHA függvény alkalmazásával is. Két lépéssel: Az előző feladat elvét felhasználva a B oszlopba írjuk be a számok mellé a számot ha nagyobb, mint az átlag és 0-t ha kisebb vagy egyenlő. B1 cellába írjuk be a következő kifejezést: =HA(A1>ÁTLAG($A$1:$A$6);A1;0), majd másoljuk a többi cellába a kifejezést. A B 1 246 0 2 678 678 3 115 0 4 873 873 5 394 0 6 491 491 Így a B oszlopban már csak az átlagon felüli számok szerepelnek (és nullák), vagyis a B oszlop összege pont az átlagon felüli számok összege lesz. B7 cellába: =SZUM(B1:B6) az eredmény: 2042 A feladat egy lépésben, segédoszlop nélkül is megoldható a SZUMHA függvény használatával. SZUMHA függvénynek (feltételes összegzés) 3 bemenete van, amiből 2-t kell kötelezően megadni: Tartomány: az a cellatartomány, melyben a feltétel teljesülését vizsgáljuk Kritérium: az a feltétel, aminek teljesülnie kell ahhoz, hogy az érték az összegben szerepeljen Összeg_tartomány: akkor kell megadni, ha a feltételt nem az összeadandó tartományon vizsgáltuk. Ebben a példában az A1:A6 tartományt kell megnézni és a feltétel: ”>”&ÁTLAG(A1:A6), mivel az összegzendő ugyanez a tartomány így a harmadik paramétert nem kell megadni. Vagyis A7 cellába az alábbi kifejezést írva: =SZUMHA(A1:A6;”>”&ÁTLAG(A1:A6)), az eredmény egy lépésben: 2042.

HA függvény használata esetén több feltételt is egybe ágyazhatunk. Tehát ha például a kisebb vagy nagyobb esetén az egyenlőséget is vizsgálni szeretnénk, akkor két HA függvényt kell egymásba ágyazni: =HA(A1=A2;”egyenlő”;HA(A1>A2;”A1 nagyobb”;”A2 nagyobb”) A fenti kifejezés eredménye ”egyenlő”, ha A1 és A2 cellában lévő számok egyenlők, ”A1 nagyobb”, ha A1 cellában lévő szám a nagyobb és ”A2 nagyobb” ha a A2 cellában lévő szám a nagyobb. A második HA függvény tehát csak akkor hajtódik végre, ha A1 és A2 cellában lévő számok nem egyenlők. Ilyen módon gyakorlatilag bármennyi HA függvényt egymásba ágyazhatunk, de nem célszerű egynél több beágyazás használata, mert átláthatatlanná, követhetetlenné válik a kifejezésünk. Ha több érték közül kell választani, akkor használjuk inkább a VÁLASZT(index;érték1;érték2…) függvényt. Ez a függvény egy megadott listából az indexszám alapján választja ki az eredményt. Az 9 Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel

indexszámnak 1 és 254 közöttinek kell lennie, vagyis 254 különböző eredményből választhatunk. Az indexszám megadható például egy feltétel eredményeként. Gyakorló feladat 8. Írjuk ki az A1 cellában megadott szám 5-tel osztva kapott maradékát betűvel. Megoldás: VÁLASZT függvényt használjuk, az indexszámot a MARADÉK(A1;5)+1 kifejezés adja (a +1 azért kell, mert az index szám nem lehet nulla, így az összes értéket eltoljuk eggyel). Az értékek rendre: 1: ”nulla” 2: ”egy” 3: ”kettő” 4: ”három” 5: ”négy” A2 cellába tehát az alábbi kifejezést kell írnunk: =VÁLASZT(MARADÉK(A1;5)+1; ”nulla”;”egy”;”kettő”;”három”;”négy”) A VÁLASZT keresőfüggvényt egy egyszer megadott listából tudjuk választani. Ha a listát változtatni szeretnénk, akkor a kifejezést át kell írni. Ha a lista a táblázat egy területén adva van, akkor be kell gépelni a kifejezésbe. Ez felesleges művelet és rugalmatlan. Ilyen esetekben az FKERES vagy a VKERES függvényeket célszerű használni. (A kettő között annyi a különbség, hogy az FKERES függőlegesen keres, a VKERES pedig vízszintesen.) FKERES (és VKERES) függvénynek három bemeneti paramétere van: Keresési_érték: a tábla első oszlopában (sorában) megkeresendő érték Tábla: a keresési értéket és az eredmény oszlopot (sort) is tartalmazó tartomány Oszlop_szám (Sor_szám): az eredményt tartalmazó oszlop (sor) táblán belüli sorszáma Az FKERES (és VKERES) függvény pontos értéket keres a táblában, ha nem talál ilyet, akkor az alulról közelítő értéket adja vissza eredményül. Például A1 cellában megadott érdemjegy kikeresése az alábbi táblából csak pontos értékekkel megadva: C D =FKERES(A1;$C$10:$D$14;2) 10 1 elégtelen 11 2 elégséges ahol A1 cellában adott a kikeresendő érdemjegy ezt keressük 12 3 közepes a C10:D14 tartomány első oszlopában és az eredmény 13 4 jó a megadott érték sora és a 2 oszlop metszéspontjában lévő cella 14 5 jeles Ha A1 cellában például egy százalékos érték van megadva nem kell mind a 100 lehetséges értéket felsorolni, elég megadni egy tartományt és az FKERES az alsó küszöb szerint vizsgálja az értéket: C 10 11 12 13 14

0 20 40 60 80

D 20% 40% 60% 80% 100%

E elégtelen elégséges közepes jó jeles

=FKERES(A1;$C$10:$E$14;3) ahol A1 cellában adott a kikeresendő érték ezt keressük a C10:E14 tartomány első oszlopában és az eredmény a megadott értékhez tartozó alulról legközelebbi érték sora és a 3 oszlop metszéspontjában lévő cella

(Amennyiben több érték kikeresése miatt másoljuk a kifejezést, akkor a másolás előtt a Tábla tartományát $ jelekkel rögzítsük, különben a kifejezés másolásával a Tábla tartománya is módosul és hibás eredményeket kapunk!) 10 Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel

Mátrixok Egy (n x m)-es mátrix, n db oszlopból és m db sorból áll. Általános alakja: =

⋮

⋮

… … ⋱ …

⋮

Excelben egy mátrixot úgy adhatunk meg, hogy beírjuk elemeit egy tetszőleges cellatartományba, pl. az A1:C3 tartományba: A B C 2 3 6 1 2 3 6 = 0 1 7 = 2 0 1 7 4 5 8 3 4 5 8 Műveletek mátrixokkal Mátrixok összeadása: Két mátrix csak akkor adható össze, ha azonos alakúak, azaz mindkettőnek ugyanannyi sora és oszlopa van. Az eredmény mátrixot úgy kapjuk meg, hogy a két mátrix megfelelő elemét összeadjuk: 2 0 4

3 6 1 1 7 + 5 5 8 7

5 3 8

2 2+1 4 = 0+5 4+7 6

3+5 6+2 3 1+3 7+4 = 5 5+8 8+6 11

8 8 4 11 13 14

Megoldás Excellel: Írjuk be a két összeadandó mátrixot egy-egy tartományba, pl: A B C D E F G 1 2 3 6 1 5 2 2 0 1 7 5 3 4 3 4 5 8 7 8 6 Ebben a példában az egyik mátrix az A1:C3 , a másik mátrix az E1:G3 tartományban található. Az összegüket pl. az I1:K3 tartományban úgy kaphatjuk meg, ha a cellákba az alábbi kifejezéseket írjuk: I J K I J K 1 =A1+E1 =B1+F1 =C1+G1 1 3 8 8  2 =A2+E2 =B2+F2 =C2+G2 2 5 4 11 3 =A3+E3 =B3+F3 =C3+G3 3 11 13 14

Mátrix szorzása számmal: Egy mátrixot úgy szorzunk meg egy számmal, hogy a mátrix minden elemét megszorozzuk a számmal: 2 3 6 5∙2 5∙3 5∙6 10 15 30 5∙ 0 1 7 = 5∙0 5∙1 5∙7 = 0 5 35 4 5 8 5∙4 5∙5 5∙8 20 25 40 Megoldás Excellel: Írjuk be a szorzó számot egy cellába, a mátrixot pedig egy tartományba, pl: A B C D E 1 5 2 3 6 2 0 1 7 3 4 5 8 11 Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel

Ebben a példában a szorzó szám az A1 cellában, a mátrix pedig a C1:E3 tartományban található. Szorzatukat pl. a G1:I3 tartományban úgy kaphatjuk meg, ha a cellákba az alábbi kifejezéseket írjuk: G H I G H I 1 =$A$1*C1 =$A$1*D1 =$A$1*E1 1 10 15 30  2 =$A$1*C2 =$A$1*D2 =$A$1*E2 2 0 5 35 3 =$A$1*C3 =$A$1*D3 =$A$1*E3 3 20 25 40

Mátrix determinánsa: Négyzetes mátrix az a mátrix, melynek sor és oszlop száma megegyezik (m=n). Egy négyzetes mátrix n-ed rendű determinánsa az a mátrixhoz rendelt szám, melyet az alábbi képlettel kapunk meg: (−1) (

=

, ,…, )

…

, ,…,

2 = 0 4

3 1 5

6 7 , 8

det( ) = 6

Megoldás Excellel: Írjuk be a mátrixot egy tartományba, pl. A1:C3: 1 2 3

A 2 0 4

B 3 1 5

C 6 7 8

A mátrix determinánsát úgy kaphatjuk meg, hogy használjuk az MDETERM(tartomány) függvényt. Tetszőleges cellába beírva az =MDETERM(A1:C3) kifejezést megkapjuk az eredményt, azaz a 6-ot.

Mátrix transzponálása: Egy mátrix transzponálása a mátrix sorainak és oszlopainak felcserélése (átlójára való tükrözése). 2 3 6 2 0 4 = 0 1 7 , = 3 1 5 4 5 8 6 7 8 Megoldás Excellel: Írjuk be a mátrixot egy tartományba, pl. A1:C3: 1 2 3

A 2 0 4

B 3 1 5

C 6 7 8

A mátrix transzponáltját pl. az E1:G3 tartományban úgy kaphatjuk meg, hogy használjuk a TRANSZPONÁLÁS(tartomány) függvényt. Előtte jelöljük ki az egér segítségével a céltartományt, tehát az E1:G3 cellákat úgy, hogy a kijelölést az E1 cellával kezdjük. Ezután írjuk be az =TRANSZPONÁLÁS(A1:C3) kifejezést és nyomjuk le egyszerre a Ctrl+Shift+Enter billentyűket. Ha jól csináltuk, akkor E1:G3 tartományban megjelenik a transzponált mátrix: E F G 1 2 0 4 2 3 1 5 3 6 7 8 12 Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel

Ha az E1 cellában #ÉRTÉK? hiba jelenik meg, akkor jelölje ki az E1:G3 tartományt az egérrel, majd nyomja meg az F2 gombot és utána egyszerre a Ctrl+Shift+Enter billentyűket.

Mátrixok szorzata: A és B mátrix akkor szorozhatók össze, ha A-nak annyi oszlopa van, mint ahány sora B-nek. Ekkor az eredmény egy olyan mátrix, melynek annyi sora van, mint A-nak és annyi oszlopa, mint B-nek. 2 3 13 30 16 5 3 2 = 4 1 é = , ∙ = 21 20 12 1 8 4 7 9 44 93 50 Megoldás Excellel: Írjuk be a két mátrixot egy-egy tartományba, pl.: A B 1 2 3 2 4 1 3 7 9

C

D 5 1

E 3 8

F 2 4

Az A mátrix most az A1:B3, a B mátrix a D1:F2 tartományba került. Szorzatukat pl. a H1:J3 tartományban úgy kaphatjuk meg, hogy használjuk az MSZORZAT(tartomány1;tartomány2) függvényt. Előtte jelöljük ki az egér segítségével a céltartományt, tehát a H1:J3 cellákat úgy, hogy a kijelölést a H1 cellával kezdjük. Ezután írjuk be az =MSZORZAT(A1:B3;D1:F2) kifejezést és nyomjuk le egyszerre a Ctrl+Shift+Enter billentyűket. Ha jól csináltuk, akkor H1:J3 tartományban megjelenik a transzponált mátrix: H I J 1 13 30 16 2 21 20 12 3 44 93 50 Ha csak a H1 cellában jelenik meg érték, akkor jelölje ki a H1:J3 tartományt az egérrel, majd nyomja meg az F2 gombot és utána egyszerre a Ctrl+Shift+Enter billentyűket.

Mátrix inverze: Egy A mátrix inverze az az A-1 mátrix, melyre igaz, hogy a két mátrix szorzata egy egységmátrix. ℎ

7 = 4 0

1 2 6

4 5 , 8

0,412 −0,471 0,088 = 0,941 −1,647 0,559 , −0,706 1,235 −0,294

∙

1 = 0 0

0 0 1 0 0 1

Megoldás Excellel: Írjuk be a mátrixot egy tartományba, pl. A1:C3: 1 2 3

A 7 4 0

B 1 2 6

C 4 5 8

A mátrix inverzét pl. az E1:G3 tartományban úgy kaphatjuk meg, hogy használjuk az INVERZ.MÁTRIX(tartomány) függvényt. Előtte jelöljük ki az egér segítségével a céltartományt, tehát az E1:G3 cellákat úgy, hogy a kijelölést az E1 cellával kezdjük. Ezután írjuk be az =INVERZ.MÁTRIX(A1:C3) kifejezést és nyomjuk le egyszerre a Ctrl+Shift+Enter billentyűket. Ha jól csináltuk, akkor E1:G3 tartományban megjelenik a mátrix inverze: 13 Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel

1 2 3

E 0,412 0,941 -0,706

F -0,471 -1,647 1,235

G 0,088 0,559 -0,294

Ha csak az E1 cellában jelenik meg érték, akkor jelölje ki az E1:G3 tartományt az egérrel, majd nyomja meg az F2 gombot és utána egyszerre a Ctrl+Shift+Enter billentyűket.

Határozott lineáris egyenletrendszer megoldása mátrixok segítségével Oldjuk meg az alábbi egyenletrendszert: 2 + 3 + 4 = 63 5 + − 6 = −32 4 + 3 = 39 Megoldás: Az egyenletrendszer felírható

∙

= 2 = 5 4

alakban, ahol: 3 4 1 −6 , = 0 3

é

63 = −32 39

melyből az ismeretleneket tartalmazó vektor az = ∙ szorzattal meghatározható: −0,024 0,071 0,173 63 3 0,079 −0,252 ∙ −32 = 7 = 0,307 0,031 −0,094 0,102 39 9 Megoldás Excellel: Első lépésként meg kell adni az A együttható mátrixot egy tartományban (pl: A1:C3): A B C 1 2 3 4 2 5 1 -6 3 4 0 3 majd, kiszámítani ennek az inverzét pl. az E1:G3 tartományban (INVERZ.MÁTRIX() függvénnyel): A B C D E F G 1 2 3 4 -0,024 0,071 0,173 2 5 1 -6 0,307 0,079 -0,252 3 4 0 3 0,031 -0,094 0,102 megadjuk a konstans tagokat tartalmazó vektort (pl: I1:I3 tartományban): A B C D E F G H 1 2 3 4 -0,024 0,071 0,173 2 5 1 -6 0,307 0,079 -0,252 3 4 0 3 0,031 -0,094 0,102

I 63 -32 39

végül összeszorozzuk az inverzmátrixot ezzel a vektorral (MSZORZAT() függvénnyel) és az eredmények kiolvashatók a K1:K3 tartományból (K1 tartalmazza x, K2 tartalmazza y és K3 tartalmazza z értékét): A B C D E F G H I J K 1 2 3 4 -0,024 0,071 0,173 63 3 2 5 1 -6 0,307 0,079 -0,252 -32 7 3 4 0 3 0,031 -0,094 0,102 39 9 14 Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel

Határozott lineáris egyenletrendszer megoldása Cramer-szabály segítségével Oldjuk meg az alábbi egyenletrendszert: 2 + 3 + 4 = 63 5 + − 6 = −32 4 + 3 = 39 Megoldás: Cramer-szabály szerint ez egyes ismeretlenek megkaphatóak, ha az együttható mátrixban a keresett ismeretlenhez tartozó oszlop elemeit kicseréljük a konstans vektorra, majd az így kapott mátrix determinánsát elosztjuk az eredeti együtthatómátrix determinánsával. Az egyenletrendszer eredeti együttható mátrixa: 2 3 4 = 5 1 −6 , 4 0 3

á

:

det( ) = −127

Az első ismeretlen (x) meghatározásához az együtthatómátrix első oszlopát kicseréljük a konstansvektorra 63 63 3 4 = −32 , í −ℎ ó á : = −32 1 −6 39 39 0 3 és ennek a mátrixnak a determinánsát elosztva az eredeti mátrix determinánsával éppen x-et kapjuk: det( ) −389 det( ) = −389, í = = =3 det( ) −127 A második és harmadik ismeretlent ugyanígy kapjuk azzal a különbséggel, hogy a konstansvektort a második, illetve harmadik oszlop helyére írjuk be! Megoldás Excellel: Első lépésként meg kell adni az A együttható mátrixot egy tartományban (pl: A1:C3), és ennek kiszámítani a determinánsát (pl. az E1 cellában) az =MDETERM(A1:C3) kifejezéssel: A B C D E 1 2 3 4 -127 2 5 1 -6 3 4 0 3 Ezután megadjuk az Ax mátrixot egy másik tartományban (pl: A5:C7), és ennek is kiszámítjuk a determinánsát (pl. az E5 cellában) az =MDETERM(A5:C7) kifejezéssel. Végül x értékét (az E7 cellában) a két determináns hányadosa adja (=E5/E1): A B C D E 5 63 3 4 -389 6 -32 1 -6 7 39 0 3 3 Gyakorló feladat 9. Oldja meg az alábbi egyenletrendszert inverz mátrix segítségével! 3 − 2 + 4 = 45 + 4 − 6 = −21 5 + 7 − 2 = 76 4 − 3 + 5 = 48 Ellenőrizze az eredményt Cramer-szabály alkalmazásával!

15 Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel

Grafikonok, diagramok Függvények ábrázolását Pont diagramokon célszerű megvalósítani (ez hasonlít legjobban a koordinátarendszerhez.) Gyakorló feladat 10. Ábrázoljuk 0° - 360° közötti tartományban a sin x függvényt! Megoldás: Excelben csak úgy tudunk valamit ábrázolni, ha megadjuk az értékeket tartalmazó cellákat. Első lépésként tehát létre kell hozni egy értéktáblázatot, mely 0 és 360 között valamilyen lépésközzel pl.15°-onként tartalmazza x értékeit és a hozzá tartozó sin x értékeket. Például így:

1 2

A x sin x

B

C

D

E

F

G

H

I

J

K

L

M

N

0 0

30 0,5

60 0,87

90 1

120 0,87

150 0,5

180 0

210 -0,5

240 -0,87

270 -1

300 -0,87

330 -0,5

360 0

Ezután elkészíthetjük a diagramot. Jelöljük ki a teljes tartományt beleértve az A oszlopot is (abból lesz a jelmagyarázat). Kattintsunk a Beszúrás fülön a Diagramok dobozban a Pont ikonra. A lenyíló ablakban ötféle diagramból választhatunk ami lényegében ugyanaz, csak az elsőben nincsenek összekötve a pontjelölők, a másodikban görbe vonalakkal vannak összekötve a pontjelölők, a harmadikban csak az összekötő görbe vonalak látszanak, a negyedikben egyenes vonalakkal vannak összekötve a pontjelölők míg az ötödikben csak egyenes vonalak látszanak. Ezt később is változtathatjuk, de válasszuk azt, amelyiket szeretnénk használni. Legyen most pontjelölők görbe vonalakkal (2-es típus).

Gyakorlatilag már el is készült a grafikonunk, már csak formázni kell. A diagramok kinézetén bármit változtathatunk. Attól függően, hogy melyik részét akarjuk változtatni, azt a területet vagy részt kell kijelölnünk (rákattintunk) és a jobb egérgombbal kattintva a formázás menüt válasszuk. Néhány fontosabb formázható rész: Más diagramtípus Ha meggondoltuk magunkat és más típusú diagramban szeretnénk az adatokat ábrázolni, akkor a diagramon kattintva a legördülő menüből a Más diagramtípus menüpontot választva átállíthatjuk a stílust. 16 Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel

Diagram áthelyezése Diagram két helyen helyezhető el adott munkalapon vagy külön diagramlapon. Ezt bármikor áthelyezhetjük, ha a menüből a Diagram áthelyezése gombot választjuk és megadjuk az új helyet. Rajzterület formázása Állítható a kitöltés színe, mintája. A szegély színe, stílusa. Árnyék és térhatás. Adatsorok formázása Állítható a jelölő típusa, alakja, színe. A vonal típusa, stílusa, vastagsága, színe. Jelölővonal színe, stílusa, térhatás. Tengely formázása Attól függően, hogy melyik tengelyt jelöljük ki, a beállítások csak arra a tengelyre vonatkoznak. Állítható a skála minimum és maximum értéke, fő és kisléptéke. A tengelyfeliratok betűtípusa, színe, mérete, igazítása. A tengely vonaltípusa, színe, vastagsága. Árnyék és térhatás. A tengely rácsozását viszont nem itt kell állítani, hanem a kijelölés után a jobb egérgombra legördülő menüben a Fő vezetőrácsok felvétele vagy a Segédrácsok felvétele menüponttal lehet megjeleníteni azokat. A fő vezetőrácsok a főléptéknél a segédrácsok a kisléptékeknél fognak megjelenni. (Eltávolítani a rácsok kijelölése után a Delete gombbal lehet.) Gyakorló feladat 11. Bővítsük ki előző grafikonunkat a cos x függvénnyel is! Megoldás: Először a cos x értékeket kell meghatároznunk és beírni az értéktáblázatba: A 1 x 2 sin x 3 cos x

B

C

D

E

F

G

H

0 0 1

30 0,5 0,87

60 0,87 0,5

90 1 0

120 0,87 -0,5

150 0,5 -0,87

180 0 -1

I

J

210 240 -0,5 -0,87 -0,87 -0,5

K

L

M

N

270 -1 0

300 -0,87 0,5

330 -0,5 0,87

360 0 1

Ezután kattintsunk a diagramon jobb egérgombbal és válasszuk ki az Adatok kijelölése menüpontot. Válasszuk bal oldalon az adatsor Hozzáadása gombot. Három értéket kell megadnunk: Az adatsor neve, ami majd megjelenik a jelmagyarázatban. (A3 cella) Az adatsor X értékeit (B1:N1 tartomány) Végül az adatsor Y értékeit (B3:N3 tartomány) Ezután az ablakot az OK gombbal bezárjuk és más fel is rajzoltuk a koszinusz függvényt, már csak formázni kell.

17 Vámosi Attila – Mérnöki informatika I. jegyzet – Microsoft Excel