SZENT ISTVN EGYETEM...Excel – kidolgozott feladatok SZIE Informatika Tanszék 1....
Transcript of SZENT ISTVN EGYETEM...Excel – kidolgozott feladatok SZIE Informatika Tanszék 1....
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 SZIE Informatika Tanszék
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üggvényt az [1;5] intervallumon 0.2-es lépésközzel! (Trigonometrikus függvény radiánt használ az Excelben.)
2sinln2)( xxxf +=
Kidolgozás
3
SZIE Informatika Tanszék Excel - kidolgozott feladatok
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
Excel – kidolgozott feladatok SZIE Informatika Tanszék
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
SZIE Informatika Tanszék Excel - kidolgozott feladatok
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 egyenletet az [1;5] intervallumon, 5,1sinln2 2 =+ xx
• Az egyenlet bal oldalának ábrázolása a megadott intervallumon → gyökök száma: 3,
• a gyökök közelítő helye ; 3,11 ≅x 6,12 ≅x ; 6,23 ≅x ld. a 3. oldalon a görbét.
6
Excel – kidolgozott feladatok SZIE Informatika Tanszék
• 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üggvény görbéjét a [0;5] intervallumon! 2)( 1 += −xexf
1.2 Ábrázolja az )cos()( xxexg −= függvényt a [0;15] intervallumon 0,5-es lépésközzel!
1.3 Ábrázolja az 1sin1)3cos()( 4
2
+++
=xx
xxh függvény görbéjét a [-5;5] intervallumon!
1.4 Ábrázolja az ϕϕ sin3)( =r függvény görbéjét a [0;2π ] intervallumon!
1.5 Ábrázolja az függvény görbéjét a [0;22)2/sin()( ϕϕ =r π ] intervallumon
1.6 Ábrázolja függvényt a [-2;2] intervallumon! yxyxf cossin),( +=
7
SZIE Informatika Tanszék Excel - kidolgozott feladatok
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
?=+ BA , ha ⎥⎥⎥
⎦
⎤
⎢⎢⎢
⎣
⎡−
−=
103410
312A
⎥⎥⎥
⎦
⎤
⎢⎢⎢
⎣
⎡−=
150213301
B
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 AcB = mátrixot, ha 5=c !
A megoldás menete az összevonáshoz hasonló:
• 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
Excel – kidolgozott feladatok SZIE Informatika Tanszék
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 =mszorzat(tömb1;tömb2)
• Ctrl + Shift + Enter
Kidolgozott példa
?=AB , ha
⎥⎥⎥⎥⎥⎥
⎦
⎤
⎢⎢⎢⎢⎢⎢
⎣
⎡
−
−=
110011001121201
A ⎥⎥⎥
⎦
⎤
⎢⎢⎢
⎣
⎡
−112031
B
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),
• Ctrl + Shift + Enter
•
Eredmény:
9
SZIE Informatika Tanszék Excel - kidolgozott feladatok
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!
⎥⎥⎥⎥⎥⎥
⎦
⎤
⎢⎢⎢⎢⎢⎢
⎣
⎡
−
−=
110011001121201
A
Megoldás menete a mátrixok táblázatba vitele után:
• Eredménymátrix tömbjének kijelölése,
• =transzponálás(B1:D5)
• Ctrl + Shift + Enter
Mátrix determinánsa Az A négyzetes mátrix determinánsa: Adet , egy valós szám.
Ha 0det ≠A , 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 3-szorosaként, stb…).
Ha 0det =A , 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 ⎥⎥⎥
⎦
⎤
⎢⎢⎢
⎣
⎡
−
−=
035370121
A
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
Excel – kidolgozott feladatok SZIE Informatika Tanszék
Mátrix inverze Az A mátrix inverze az a mátrix, mellyel bármely oldalról megszorozva az eredmény egységmátrix:
EAAAA == −− ** 11
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:
?1 =−A , ha
⎥⎥⎥
⎦
⎤
⎢⎢⎢
⎣
⎡
−
−=
035370121
A
Megoldás menete A mátrix táblázatba vitele után:
• Eredménymátrix tömbjének kijelölése,
• =inverz.mátrix(tömb),
• Ctrl + Shift + Enter
Eredmény:
Feladatok: 2.1 Adottak a következő mátrixok:
⎥⎥⎥
⎦
⎤
⎢⎢⎢
⎣
⎡−=
413810
A ⎥⎥⎥
⎦
⎤
⎢⎢⎢
⎣
⎡=
102027351
B ⎥⎥⎥
⎦
⎤
⎢⎢⎢
⎣
⎡=
212420002
C ⎥⎦
⎤⎢⎣
⎡=
012462
D
Végezze el az alábbiak közül az elvégezhető műveleteket Excel segítségével!
a) DA∗ b) CB ∗ c) TDA +
d) BA + e) )det()( 1 BCB ∗+−
f) 1−∗CD
11
SZIE Informatika Tanszék Excel - kidolgozott feladatok
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 egy-egy 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
Excel – kidolgozott feladatok SZIE Informatika Tanszék
Kidolgozás
13
SZIE Informatika Tanszék Excel - kidolgozott feladatok
Az eredeti és az elforgatott háromszög:
Forgatás 30 fokkal
2, 1
6, 3
4, 7
2, 1
1,232050808, 1,866025404
3,696152423, 5,598076211
-0,035898385, 8,062177826
1,232050808, 1,866025404
0
1
2
3
4
5
6
7
8
9
-1 0 1 2 3 4 5 6 7
Adatsor1
Adatsor3
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
Excel – kidolgozott feladatok SZIE Informatika Tanszék
4. LINEÁRIS EGYENLETRENDSZER MEGOLDÁSA Lineáris egyenletrendszer általános alakja
nmnmnn
mm
mm
bxaxaxa
bxaxaxabxaxaxa
=++
=++=++
.....
......
2211
22222121
11212111
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 megoldása az együtthatómátrix inverzének segítségével A fenti egyenletrendszer átírható a mátrixszorzás szabályainak megfelelően az alakban:
Ax=b, ahol
⎥⎥⎥⎥
⎦
⎤
⎢⎢⎢⎢
⎣
⎡
=
nmnn
m
m
aaa
aaaaaa
A
...:
...
...
21
22221
11211
az együtthatómátrix ⎥⎥⎥
⎦
⎤
⎢⎢⎢
⎣
⎡=
mx
xx :
1
az ismeretlenek
oszlopvektora, ⎥⎥⎥
⎦
⎤
⎢⎢⎢
⎣
⎡=
nb
bb :
1
az egyenletrendszer jobb oldalából képzett oszlopvektor.
Az inhomogén egyenletrendszer ( 0≠b ) megoldható az alábbi alakban, ha az egyenletek lineárisan függetlenek egymástól, azaz, ha 0det ≠A :
x=A-1*b
A lineáris egyenletrendszer megoldásához szükséges műveletek:
• 0det ≠A é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
SZIE Informatika Tanszék Excel - kidolgozott feladatok
Kidolgozott példa
Oldja meg az alábbi egyenletrendszert:
835737
12
21
32
321
=−−=+
−=−+
xxxx
xxx
A már megismert műveletekkel az Excelben a megoldás:
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:
◦ 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.
16
Excel – kidolgozott feladatok SZIE Informatika Tanszék
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 Oldja meg az alábbi egyenletrendszereket az ismertetett módszerekkel:
a) b)
8642
4322
=+++=+++
=++−=++
dcbadcba
dcbcba
34232
32
=+=++
=+−
zyzyx
yx c)
14355123
=++−=+−=+
wvuwuvu
17
SZIE Informatika Tanszék Excel - kidolgozott feladatok
5. FELADATOK EREDMÉNYE
1.1 Ábrázolja az függvény görbéjét a [0,5] intervallumon! 2)( 1 += −xexf
f(x)
0
10
20
30
40
50
60
0 1 2 3 4 5 6
1.2 Ábrázolja az )cos()( xxexg −= függvényt a [0;15] intervallumon 0,5-es lépésközzel!
g(x)
0
20
40
60
80
100
120
0 5 10 15 20
1.3 Ábrázolja az 1sin1)3cos()( 4
2
+++
=xx
xxh függvényt a [-5;5] intervallumon!
h(x)
-0,50
0,51
1,52
2,53
3,5
-6 -4 -2 0 2 4 6
18
Excel – kidolgozott feladatok SZIE Informatika Tanszék
1.4 Ábrázolja az ϕϕ sin3)( =r függvény görbéjét a [0;2π ] intervallumon!
r=3sin(fi)
-0,50
0,51
1,52
2,53
3,5
-2 -1,5 -1 -0,5 0 0,5 1 1,5 2
1.5 Ábrázolja az függvény görbéjét a [0;22)2/sin()( ϕϕ =r π ] intervallumon
r=sin(fi/2)^2
-0,8
-0,6
-0,4
-0,2
0
0,2
0,4
0,6
0,8
-1,5 -1 -0,5 0 0,5
1.6 Ábrázolja függvényt a [-2;2] intervallumon! xxyxf cossin),( +=
1 5 9
13 17 21
S1
S11
S21
-1,5-1
-0,50
0,5
1
1,5
2
f(x,y)=sin x+cos x
19
SZIE Informatika Tanszék Excel - kidolgozott feladatok
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
Excel – kidolgozott feladatok SZIE Informatika Tanszék
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) b)
8642
4322
=+++=+++
=++−=++
dcbadcba
dcbcba
34232
32
=+=++
=+−
zyzyx
yx c)
14355123
=++−=+−=+
wvuwuvu
a) a=1 b=0 c=1 d=1
b) Nincs egyértelmű megoldás, mert az együtthatómátrix determinánsa nulla.
c) u= -1 v=1 w=0
21