SZENT ISTVÁN EGYETEM Gépészmérnöki Kar. Orova Lászlóné dr. Számítástechnika I. Tantárgyhoz Kidolgozott Excel feladatok. Gödöllő, 2004

SZENT ISTVÁN EGYETEM Gépészmérnöki Kar

Orova Lászlóné dr.

Számítástechnika I. Tantárgyhoz Kidolgozott Excel feladatok

Gödöllő, 2004.

SZIE Informatika Tanszék

Excel - kidolgozott feladatok

Bevezető

A Számítástechnika I. tantárgy keretében a hallgatók megismerkednek az informatikai alapokkal, majd az Excel táblázatkezelő használatát sajátítják el. Ez a feladatgyűjtemény összefoglalja e tárgy keretében oktatott, Excellel kapcsolatos főbb témaköröket, ismertnek tekintve az alapvető táblázatkezelői műveleteket, mint pl. formázás, képletek bevitele, beépített függvények beszúrása. Az Excel további alkalmazási területeivel a Számítástechnika II. tárgy foglalkozik. A példatár szerkezete: témakörönként rövid elméleti összefoglaló, kidolgozott példa, majd gyakorlásra ajánlott feladatok, melyek megoldása a példatár végén megtalálhatóak. Jelen példatár Dr. Molnár Sándor Számítástechnika I. tárgy keretében tartott előadásaira épül. A példatár használatát megkönnyíti Dezső Ottó, Dr. Csikós Miklósné: Számítástechnika II. jegyzetének ismerete, mely a Szent István Egyetemen jelenik meg évente. Ez a feladatgyűjtemény kézirat, lehetséges, hogy még tartalmaz hibákat. Minden egyes, először jelzett hibáért pontjutalmat ad a szerző.

Tartalomjegyzék

1.

FÜGGVÉNYÁBRÁZOLÁS

3

2.

MÁTRIXMŰVELETEK

8

3.

LINEÁRIS TRANSZFORMÁCIÓK

12

4.

LINEÁRIS EGYENLETRENDSZER MEGOLDÁSA

15

5.

FELADATOK EREDMÉNYE

18

2

Excel – kidolgozott feladatok


1. FÜGGVÉNYÁBRÁZOLÁS Az Excel a függvényt megadó matematikai összefüggés alapján nem tudja közvetlenül a függvény görbéjét lerajzolni, de síkbeli (térbeli) pontokat adott koordinátákkal meg tud jeleníteni. A függvényábrázolás főbb lépései: A függvény néhány pontjának meghatározása: pontok koordinátáit tartalmazó táblázat. A pontok ábrázolása diagramvarázsló segítségével Pont (xy), vagy a Felület típusú diagrammal, attól függően, hogy a függvény egy-, vagy kétváltozós.

Függvényábrázolás Descartes-féle koordinátarendszerben Kidolgozott feladat Ábrázolja az f ( x) = 2 ln x + sin x 2 függvényt az [1;5] intervallumon 0.2-es lépésközzel! (Trigonometrikus függvény radiánt használ az Excelben.) Kidolgozás

3



Függvényábrázolás polárkoordináta rendszerben A polárkoordináta rendszerben megadott függvényt először át kell írni Descartes-féle koordináta rendszerbe, majd azt az előzőekhez hasonlóan lehet ábrázolni: Kidolgozott példa Ábrázolja az r = ϕ függvényt a [0;2 ϕ ] intervallumon! Kidolgozás

4



Kétváltozós függvény ábrázolása Kidolgozott feladat: Ábrázolja f(x,y)=x2+y2 függvényt a [-2;2] intervallumon! Kidolgozás Felület típusú diagram alkalmazásával:

Egyenlet megoldása grafikusan Feladat: f(x)=g(x) meghatározása Egy diagramon ábrázolva f(x) és g(x) függvényeket a görbék metszéspontjának leolvasásával az egyenlet közelítő megoldása meghatározható. Kidolgozott példa 3sinx=2x,

x=?

a [-4;4] intervallumon.

5



Kidolgozás

Az egyenlet megoldása x ≅ ±1,4

Egyenlet megoldása Célérték-kereséssel Egyenlet megoldására az Excel beépített lehetősége a Célérték-keresés. Főbb lépések •

Az egyenlet konstansra rendezése

•

Az egyenlet ismeretlent tartalmazó oldalának cellába vitele Excel képletként, kezdeti érték felvételével

•

Eszközök menü Célérték-keresés

Csak a kezdeti értékhez legközelebbi gyököt találja meg, a többit más kezdeti értékhez tartozó Célérték-kereséssel lehet meghatározni. Érdemes ezért először grafikusan meghatározni a gyökök számát és körülbelüli értékét. Kidolgozott példa Oldja meg 2 ln x + sin x 2 = 1,5 egyenletet az [1;5] intervallumon, •

Az egyenlet bal oldalának ábrázolása a megadott intervallumon →

•

a gyökök közelítő helye x1 ≅ 1,3 ; x 2 ≅ 1,6 ; x3 ≅ 2,6 ld. a 3. oldalon a görbét.

6

gyökök száma: 3,

Excel – kidolgozott feladatok •


A három különböző gyökre külön-külön Célérték-keresés: ◦

Célcella: képletet tartalmazó cella (egyenlet bal oldala)

◦

Célérték: milyen értéke legyen a képletnek (egyenlet jobb oldala). Mindig egy valós szám!

◦

Módosuló cella: ahol a változó van. (Az x értékét tartalmazó cella, amire a képletben hivatkozunk.)

Eredmény a módosuló cellában olvasható le: A28= 1,287

A másik két kezdeti értékre is lefuttatva a Célérték-keresést: x2= 1,59216997, x3= 2,44725069

Feladatok 1.1

Ábrázolja az f ( x) = e x −1 + 2 függvény görbéjét a [0;5] intervallumon!

1.2

Ábrázolja az g ( x) = e

1.3

cos(3 x 2 ) + 1 Ábrázolja az h( x) = 4 függvény görbéjét a [-5;5] intervallumon! x + sin x + 1

1.4

Ábrázolja az r (ϕ ) = 3 sin ϕ függvény görbéjét a [0;2 π ] intervallumon!

1.5

Ábrázolja az r (ϕ ) = sin(ϕ / 2) 2 függvény görbéjét a [0;2 π ] intervallumon

1.6

Ábrázolja f ( x, y ) = sin x + cos y függvényt a [-2;2] intervallumon!

x − cos( x )

függvényt a [0;15] intervallumon 0,5-es lépésközzel!

7



2. MÁTRIXMŰVELETEK Összeadás, kivonás Mátrixok összeadása, kivonása: megfelelő elemek összege (különbsége), csak azonos méretű mátrixokkal végezhető műveletek. Kidolgozott példa

⎡2 − 1 3 ⎤ A = ⎢⎢0 1 − 4⎥⎥ ⎢⎣3 0 1 ⎥⎦

A + B = ? , ha

⎡ 1 0 3⎤ B = ⎢⎢− 3 1 2⎥⎥ ⎢⎣ 0 5 1⎥⎦

Főbb lépések •

A kiindulási mátrixok Excel táblázatba, tömbbe írása, a mátrix minden egyes eleme külön cellába kerül.

•

Az eredmény mátrix helyének kijelölése: B5:D7 tömb.

•

Szerkesztőlécen a képlet beírása: a két tömb összege (a tömbök megfelelő celláinak összege)

•

Az eredménynek több cellában kell megjelennie (többértékű függvényt alkalmaztunk), ezért nem Enter-rel, hanem Ctrl + Shift + Enter együttes lenyomásával zárjuk a szerkesztést. (Érdemes az Enter-t utoljára lenyomni, miközben a másik két billentyűt benyomva tartjuk.) Az eredmény:

Mátrix szorzása konstanssal Kidolgozott példa: Határozza meg B = c A mátrixot, ha c = 5 ! A megoldás hasonló:

menete

az

összevonáshoz

•

A kiindulási adatok bevitele.

•

Az eredmény mátrix helyének kijelölése: B5:D7 tömb.

•

Szerkesztőlécen a képlet beírása: =G2*B1:D3

•

Ctrl + Shift + Enter Az eredmény:

8



Mátrixok szorzása Két mátrix összeszorozható, ha méretükre igaz: az első mátrix oszlopainak száma megegyezik a második mátrix sorainak számával. Az eredménymátrix sorainak száma az első mátrix sorainak számával, az oszlopainak száma a második mátrix oszlopainak számával egyenlő. A fentiekből következik, hogy a tényezők sorrendje csak speciális esetben cserélhető fel. Mátrixszorzás lépései Excelben: •

A mátrixok táblázatra vitele.

•

Eredménymátrix tömbjének kijelölése.

•

Beépített függvény használata

•

Ctrl + Shift + Enter

=mszorzat(tömb1;tömb2)

Kidolgozott példa

AB = ? ,

ha

0 ⎡1 ⎢− 1 2 ⎢ A=⎢1 0 ⎢ ⎢ 1 −1 ⎢⎣ 0 1

2⎤ 1 ⎥⎥ 0⎥ ⎥ 0⎥ 1 ⎥⎦

⎡1 3 ⎤ B ⎢⎢0 2 ⎥⎥ ⎢⎣1 −1⎥⎦

Lépések: •

A mátrixok táblázatba vitele után:

•

Eredménymátrix tömbjének kijelölése,

•

=mszorzat(B2:D6;G3:H5),

•


• Eredmény:

9



Mátrix transzponálása A mátrix transzponálása a megfelelő sorok és oszlopok felcserélése. Kidolgozott példa

Állítsa elő az A mátrix transzponáltját! 0 2⎤ ⎡1 ⎢− 1 2 1 ⎥ ⎥ ⎢ A=⎢1 0 0⎥ ⎥ ⎢ ⎢ 1 − 1 0⎥ ⎢⎣ 0 1 1 ⎥⎦ Megoldás menete a mátrixok táblázatba vitele után: •


•

=transzponálás(B1:D5)

•


Mátrix determinánsa Az A négyzetes mátrix determinánsa: det A , egy valós szám. Ha det A ≠ 0 , akkor az A mátrix sorai, oszlopai lineárisan függetlenek, azaz egyik sor (oszlop) sem állítható elő a többi sor(ok) (oszlop(ok)) valamelyikeinek lineáris kombinációjaként. (Pl. másik két sor összegeként, különbségeként, az egyik oszlop 3szorosaként, stb…). Ha det A = 0 , akkor éppen ellenkezőleg, az A mátrix sorai, oszlopai lineárisan összefüggők. (Pl. egyik sor előállítható másik két sor különbségének 5-szöröseként, stb…) Kidolgozott példa det A = ? ,

ha

⎡1 2 − 1⎤ A = ⎢⎢0 7 3 ⎥⎥ ⎢⎣5 − 3 0 ⎥⎦

Megoldás menete a mátrix táblázatba vitele után: •

Eredmény cellájának kijelölése,

•

=mdeterm(tömb),

•

Enter, mivel az eredményt egyetlen cellában kell kiíratni.

10



Mátrix inverze Az A mátrix inverze az a mátrix, mellyel bármely oldalról megszorozva az eredmény egységmátrix: A* A

−1

−1

= A *A= E

Fontos tudnivalók •

Csak négyzetes mátrixnak van inverze, ha a determináns nem nulla.

•

Az inverz mátrix az eredeti mátrixszal azonos méretű.

•

Az egységmátrix mindig négyzetes, főátlóban egyeseket, másutt nullákat tartalmaz. (Jelen esetben mérete a mátrix méretével azonos.)

Kidolgozott példa:

A

−1

=?,

ha

⎡1 2 − 1⎤ A = ⎢⎢0 7 3 ⎥⎥ ⎢⎣5 − 3 0 ⎥⎦ Megoldás menete A mátrix táblázatba vitele után: •


•

=inverz.mátrix(tömb),

•

Ctrl + Shift + Enter Eredmény:

F e l a d a t o k: 2.1

Adottak a következő mátrixok:

⎡ 0 1⎤ A = ⎢⎢− 8 3⎥⎥ ⎢⎣ 1 4⎥⎦

⎡ 1 5 3⎤ B = ⎢⎢7 2 0⎥⎥ ⎢⎣2 0 1⎥⎦

⎡2 0 0⎤ C = ⎢⎢0 2 4⎥⎥ ⎢⎣2 1 2⎥⎦

⎡ 2 6 4⎤ D=⎢ ⎥ ⎣2 1 0⎦

Végezze el az alábbiak közül az elvégezhető műveleteket Excel segítségével! a)

A∗ D

b)

B ∗C

d)

A+ B

e)

( B + C ) ∗ det( B)

f)

D ∗C

c)

A+ D

T

−1

−1

11



3. LINEÁRIS TRANSZFORMÁCIÓK A síkbeli lineáris transzformációk (eltolás, tükrözés, nagyítás, forgatás) megvalósíthatók egyegy alkalmasan megválasztott transzformációs mátrix és a síkbeli alakzat jellemző pontjaiból alkotott mátrix szorzataként.

Az eltolás mátrixa miatt szükséges a z=1-es síkban levő síkidomokat transzformálni. Kidolgozott példa

Forgassa el az ABC háromszöget 30 fokkal, ábrázolja az eredeti és a transzformált alakzatot ugyanabban a koordináta-rendszerben, ha A(2,1), B(6,3), C(4,7). A háromszöget akkor tudjuk ábrázoltatni, ha feltüntetjük az összekötendő pontokat, ezért az A pont koordinátái kétszer szerepelnek a mátrixban. Az Excel szögfüggvényei radiánt használnak a szögek mértékegységeként.

12



Kidolgozás

13



Az eredeti és az elforgatott háromszög: Forgatás 30 fokkal 9 -0,035898385, 8,062177826

8 7

4, 7

6

3,696152423, 5,598076211

5

Adatsor1 Adatsor3

4 6, 3

3 1,232050808, 1,866025404

2

2, 1

1 0 -1

0

1

2

3

4

5

6

7

Feladatok 3.1 Forgassa el az ABCD négyszöget az A csúcsa körül, ha A(1;2;1), B(3;1;1), C(6;4;1), D(5;7;1)! 3.2 Tükrözze az ABC háromszöget az AB oldal egyenesére, ha A(-2;3;1), B(3;3;1), C(1;5;1)

14



4. LINEÁRIS EGYENLETRENDSZER MEGOLDÁSA Lineáris egyenletrendszer általános alakja a11 x1 + a12 x 2 + ...a1m x m = b1 a 21 x1 + a 22 x 2 + ...a 2 m x m = b2 . . a n1 x1 + a n 2 x 2 + ...a nm x m = bn Feladat: adott aij és bi

i=1, 2, …n,

j=1, 2, ….m esetén xj meghatározása b≠0 esetén.

Lineáris egyenletrendszer inverzének segítségével

megoldása

az

együtthatómátrix

A fenti egyenletrendszer átírható a mátrixszorzás szabályainak megfelelően az alakban: Ax=b, ahol ⎡ a11 ⎢a A = ⎢ 21 ⎢ : ⎢ ⎣a n1

... a1m ⎤ ⎡ x1 ⎤ a 22 ... a 2 m ⎥⎥ x = ⎢⎢ : ⎥⎥ az együtthatómátrix az ismeretlenek ⎥ ⎥ ⎣⎢ x m ⎦⎥ a n 2 ... a nm ⎦ ⎡ b1 ⎤ b = ⎢⎢ : ⎥⎥ az egyenletrendszer jobb oldalából képzett oszlopvektor. oszlopvektora, ⎢⎣bn ⎥⎦ a12

Az inhomogén egyenletrendszer ( b ≠ 0 ) megoldható az alábbi alakban, ha az egyenletek lineárisan függetlenek egymástól, azaz, ha det A ≠ 0 : x=A-1*b A lineáris egyenletrendszer megoldásához szükséges műveletek: •

det A ≠ 0 érvényességének megvizsgálása

•

A-1 meghatározása

•

a szükséges mátrixszorzás elvégzése (sorrend fontos!)

15



Kidolgozott példa

Oldja meg az alábbi egyenletrendszert: x1 + 2 x2 − x3 = −1 7 x2 + 3 x3 = −7 5 x1 − 3 x2 = 8

A már megismert Excelben a megoldás:

műveletekkel

az

Egyenletrendszer megoldására az Excel beépített lehetősége a SOLVER.

Lineáris egyenletrendszer megoldása Solver segítségével Az előbbi feladat megoldása Eszlözök /Solver segítségével: (Ha a menüben a SOLVER nem jelenik meg, rá kell keresni a Solver.xla-ra, majd el kell indítani, vagy Eszközök/Bővíménykezelő menüpontban be kell jelölni a Solvert. A Solver alkalmas szélsőéték-feladatok megoldására, lineáris és nemlineáris egyenletrendszerek megoldására, lineáris programozási feladat megoldására ld. később.) Szükséges lépések: •

Az egyenletrendszert alkotó egyenletek konstansra rendezése.

•

Az ismeretlenek számára egy-egy cella kijelölése, célszerűen egy tömbben, kezdeti értékek megadásával. Pl.: 1.

•

Az egyes egyenletek ismeretlen tartalmazó oldalának egy-egy cellába vitele képlet formájában.

•

Solver párbeszédablak kitöltése:

16

◦

Célcella: egyik egyenlet bal oldala,

◦

Célérték: az előbbi egyenlet jobb oldala (konstans!!!),

◦

Módosuló cella: Ismeretlenek tömbje,

◦

Korlátozó feltételek: a többi egyenlet.



Kidolgozás

Megoldás gomb megnyomása után a Solver eredményeket az eredeti táblázatban kérve az egyenletrendszer megoldása azB5:D5 tömbben jelenik meg. (1; -1; 0)

Feladatok 4.1

a)

Oldja meg az alábbi egyenletrendszereket az ismertetett módszerekkel:

a + 2b + 2c = 3 −b+c+ d = 4 a + b + 2c + d = 4 a + b + c + 6d = 8

− 2x + y = 3

b)

2x + 3y + z = 2 4y + z = 3

c)

3u + 2v = −1 5u + w = −5 3u + 4v + w = 1

17



5. FELADATOK EREDMÉNYE 1.1

Ábrázolja az f ( x) = e x −1 + 2 függvény görbéjét a [0,5] intervallumon! f(x) 60 50 40 30 20 10 0 0

1.2

1

Ábrázolja az g ( x) = e

2

x − cos( x )

3

4

5

6

függvényt a [0;15] intervallumon 0,5-es lépésközzel! g(x)

120 100 80 60 40 20 0 0

1.3

5

Ábrázolja az h( x) =

10

15

20

cos(3x 2 ) + 1 függvényt a [-5;5] intervallumon! x 4 + sin x + 1 h(x) 3,5 3 2,5 2 1,5 1 0,5 0

-6

18

-4

-2

-0,5 0

2

4

6

Excel – kidolgozott feladatok 1.4


Ábrázolja az r (ϕ ) = 3 sin ϕ függvény görbéjét a [0;2 π ] intervallumon! r=3sin(fi) 3,5 3 2,5 2 1,5 1 0,5 0 -2

1.5

-1,5

-1

-0,5 -0,5 0

0,5

1

1,5

2

Ábrázolja az r (ϕ ) = sin(ϕ / 2) 2 függvény görbéjét a [0;2 π ] intervallumon r=sin(fi/2)^2 0,8 0,6 0,4 0,2 0 -1,5

-1

-0,5

-0,2 0

0,5

-0,4 -0,6 -0,8

Ábrázolja f ( x, y ) = sin x + cos x függvényt a [-2;2] intervallumon! f(x,y)=sin x+cos x 2 1,5 1 0,5 0 -0,5 S21

-1

S11 21

17

13

9

5

-1,5 1

1.6

S1

19



2.1

3.1

Forgassa el az ABCD négyszöget az A csúcsa körül, ha A(1;2;1), B(3;1;1), C(6;4;1), D(5;7;1)! A forgatás mátrixa O körül forgat, így a feladat csak több lépésben oldható meg: Az alakzat eltolása úgy, hogy az A csúcsa az origóba kerüljön, majd a transzformált alakzat elforgatása, s végül az elforgatott alakzat visszatolása, hogy az A csúcs az eredeti helyére kerüljön.

20



3.2 Tükrözze az ABC háromszöget az AB oldal egyenesére, ha A(-2,3,1), B(3,3,1), C(1,5,1) Tükrözés mátrixai koordináta-tengelyre tükröznek, ezért több transzformációs lépésben oldható meg a feladat.

4. a + 2b + 2c = 3

a)

b)

a + b + 2c + d = 4 a + b + c + 6d = 8 b=0

3u + 2v = −1

− 2x + y = 3

−b+c+ d = 4

c)

5u + w = −5 3u + 4v + w = 1

a)

a=1

b)

Nincs egyértelmű megoldás, mert az együtthatómátrix determinánsa nulla.

c)

u= -1

v=1

c=1

2x + 3y + z = 2 4y + z = 3

d=1

w=0

21

SZENT ISTVÁN EGYETEM Gépészmérnöki Kar. Orova Lászlóné dr. Számítástechnika I. Tantárgyhoz Kidolgozott Excel feladatok. Gödöllő, 2004

Recommend Documents