SZÁMÍTÁSTECHNIKA EXCEL II

SZÁMÍTÁSTECHNIKA EXCEL II. segédlet az SZTE MFK MMTE 182 kurzushoz

Írta: Kéry László

Hódmezővásárhely 2005 0.9b (2005. április 7.) verzió

Excel a felsőfokú matematikában

1. Az Excel alkalmazása néhány felsőfokú matematikai feladat megoldására Megjegyzés: Ebben a jegyzetben a példák megoldása a Microsoft Excel 97 verziójával történik.

1.1.

Lineáris programozás: Normálfeladat 1.

Oldjuk meg a Matematikai II. jegyzet 374. oldalának példáját! 1.1.1.

A matematikai modell x1 ,

x2 ,

x3

≥

0

x1 +

x2 +

x3

≤

100

3x1 + 2x2 + 4x3

≤

210

3x1 + 2x2

≤

150

Z = 5x1 + 4x2 + 6x3 → maximum. A célfüggvény maximumát keressük! 1.1.2.

Az adatrögzítés szabályai

Az adatok rögzítésének szabályai az Excel-ben: 1.

Az x-eknek megfeleltjük az A oszlop celláinak tartalmát: x1 → A1, x2 → A2, …

2.

Az x-ek nemnegatív feltételezéséről a későbbiekben fogunk intézkedni.

3.

A további korlátozó feltételek (egyenlőtlenségek) bal oldalait mint képleteket (az x-eket már A-kkal helyettesítve) beírjuk a B oszlopba, tehát pl. a B1 cella tartalma: =A1+A2+A3

4.

Az egyenlőtlenségek jobb oldalait célszerűen a C oszlopba rögzítjük a megfelelő sorokba, tehát pl. a C1 cella tartalma 100.

0.9b verzió

Számítástechnika: EXCEL II. segédlet

3


5.

A célfüggvény jobb oldalát mint képletet, szintén a B oszlopba rögzítjük, jelen esetben a B4 cellába: =5*A1+4*A2+6*A3

Megjegyzés: Az adatrögzítéskor az x-eknek megfelelő tartományt üresen hagyjuk. Az Excel ott fogja kiszámolni az eredményeket. Kezdjük rögzíteni az adatokat, írjuk be a B1 cellába a képletet (természetesen kisbetűkkel!):

Ha a bevitelt lezárjuk, a B1 cellában 0 jelenik meg:

Ez természetes, mivel a beírt képlet az A-kkal számol, jelenleg az A-k semmik, amelyek ha számnak tekintjük, nullaértékűek, így a képlet eredménye nulla. Ez a látvány nem igazán jó, mert nem látjuk a matematikai összefüggéseket, amelyekből eredetileg kiindultunk. Az Excel átállítható az Eszközök menü Beállítások… menüpontjának Megjelenítés fülén a Képletek jelölőnégyzetével olyan üzemmódra, hogy a képletek eredménye helyett a beírt képleteket mutassa:

4


0.9b verzió


Állítsuk át a megjelenítést, így a következő képernyőképet kapjuk:

Rögzítsük a szabályok szerint a többi adatot is. Az adatrögzítésünk eredményeként a képernyőnk a következőként néz ki (az oszlopszélességeket kicsit megváltoztatva):

Megjegyzés: Ne feledjük el, hogy míg a matematikában az x-ek és az együtthatók közé nem szoktuk kiírni a szorzás jelét, az Excel képletekben minden egyes szorzást a * műveleti jellel jelölnünk kell. 1.1.3.

A megoldás első lépései

A lineáris programozási feladatokat az Excel-ben az Eszközök menü Solver menüpontjával oldjuk meg. A menüpont kiválasztása után megjelenik a Solver paraméterek párbeszédpanel:

0.9b verzió


5


A párbeszédpanel értelmezése: − Célcella: a célfüggvény cellája. − Meg kell határozni a feladat típusát: maximum vagy minimum. − Módosuló cellák: az x-eknek fenntartott A1, A2, … tartomány. − Korlátozó feltételek: itt kell definiálnunk a matematikában megfogalmazott egyenlőtlenségeket. A párbeszédpanel kitöltésének lépései: 1.

Meghatározzuk a célcellát. Mivel a célcella beviteli mező az aktív és ott egyébként a – számunkra nem jó – jelenlegi aktív cella hivatkozása van, belekattintunk a B4 cellába.

2.

Meghatározzuk a feladat típusát. Mivel a Maximum van kiválasztva alapértelmezés szerint, ezért a rádiógomboknál nem kell új beállítást alkalmazni.

3.

Meghatározzuk a módosuló cellákat. Belekattintunk a Módosuló cellák beviteli mezőbe és A1-től A3-ig végrehajtott húzással bevisszük az x-eknek megfelelő tartományhivatkozást.

6


0.9b verzió


1.1.4.

A korlátozó feltételek maghatározása

A korlátozó feltételek beviteléhez rákattintunk a Hozzáadás gombra. A korlátozó feltételeket az alábbi típusú párbeszédpanelen visszük be:

A párbeszédpanel értelmezése: − A Cellahivatkozás tartalmazza a korlátozó feltétel (egyenlőtlenség) bal oldalának celláját. − Ki kell választani egy listáról a megfelelő relációt. − A Korlátozó feltétel beviteli mezőben meg kell határoznunk, hogy hová írtuk be az adott egyenlőtlenség jobb oldalát. A korlátozó feltételek bevitelének lépései: Jelen feladatunknál a következő lépéssorozatot kell végrehajtanunk. 1.

Belekattintunk a B1 cellába.

2.

Az alapértelmezett (<=) relációt nem kell megváltoztatni.

3.

Belekattintunk a Korlátozó feltétel beviteli mezőbe.

4.

Belekattintunk a C1 cellába.

5.

A felvesz gombra kattintunk.

A második és harmadik korlátozó feltételeink az előzőhöz teljesen hasonlóak, ezért azt nem részletezzük. (Végre kell hajtani az 1-5 lépéseket a B2 és C2, valamint a B3 és C3 cellákra aktualizálva.) Megjegyzés: A Korlátozó feltétel felvétele párbeszédpanelt az utolsó feltétel felvételéhez legjobb az OK gombbal lezárni. Ha nem így tettük volna (a felvesz gombra kattintottunk az utolsó feltételnél), akkor a párbeszédpanelből csak a Mégse vagy Bezárás gombbal tudunk kilépni.

0.9b verzió


7


1.1.5.

Az változók nemnegatív korlátjának rögzítése

Az eredeti matematikai feltétel megfogalmazások közül az elsőről (arról, hogy a x-ek nem lehetnek negatív értékűek) a Solver paraméterek párbeszédpanel Beállítás gombjával elérhető újabb párbeszédpanellel kell rendelkeznünk:

A fenti párbeszédpanelen a Nemnegatív feltételezése jelölő négyzetet kell beállítanunk (eredetileg az mindig törölt állapotú). A Solver beállítások párbeszédpanel leokézásával a Solver paramétereket beállítottuk:

8


0.9b verzió


1.1.6.

Elkövethető hibák

1.

A cellák, tartományok kijelölése előtt nem ellenőrizzük le, hogy a kurzor abban a beviteli mezőben van-e, amelyikben lévő paramétert éppen meg akarjuk határozni.

2.

Nem jól határozzuk meg, hogy minimum vagy maximum feladatról van szó.

3.

A módosuló cellák tartományát kisebbre vagy nagyobbra vesszük, mint az xek (A-k) darabszáma. Az első esetben súlyos hibát követünk el, a második esetben az eredmények között felesleges nullák fognak szerepelni.

4.

A korlátozó feltételeket rosszul határozzuk meg. (Érdemes szemmel átfutni rajta a felvételeik után!)

5.

Elfelejtjük a Nemnegatív feltételezését beállítani a Solver beállítások párbeszédpanelen.

1.1.7.

Megoldás

A feladat megoldásának egy részét a Solver paraméterek Megoldás gombjára kattintva kapjuk meg:

Az A oszlopban már is megjelennek az x-ek, de az alábbi párbeszédpanelen még be kell jelölnünk az is, hogy kérünk egy eredményjelentést:

0.9b verzió


9


A párbeszédpanel érvényesítés után az Excel automatikusan előállít egy új munkafüzet lapot az elé a munkafüzet lap elé, amelyen éppen dolgozunk. Az új munkafüzet lap neve Eredmény jelentés 1. Tartalma a következő:

1.1.8.

Az Eredmény jelentés fontosabb információi és matematikai értelmezése

− A célfüggvény a B4 cellában helyezkedik el és végértéke 390. − A módosuló cellák A1, A2 és A3, értékük rendre 0, 75 és 15. − A erőforrások közül az első kapacitását nem használtuk ki (Bőven 10). − A másik kettőt teljesen kihasználtuk (Éppen 0). Az eredeti matematikai feladatra tehát összefoglalóan a következő választ adhatjuk: A célfüggvény a maximumát az x1=0, x2=75 és az x3=15 értéknél éri el, maximuma 390.

10


0.9b verzió


Az első korlátot nem használtuk ki 10 egységgel. Az eredményeinket vessük egybe a Matematika II. jegyzet 375. oldalán közölt eredményekkel! Fontos! Az Excel Solver-ének Eredmény jelentése egy lehetséges optimális megoldást ad, az esetleges alternatív optimumokról nem tájékoztat.

1.2.

Lineáris programozás: Normálfeladat 2.

Oldjuk meg a Matematikai II. jegyzet 375. oldalán kezdődő példáját! A feladat megoldásához váltsunk át egy következő munkafüzet-lapra, vagy kezdjünk új dokumentumot. 1.2.1.

A matematikai modell

A feladat matematikai modelljét a 376. oldalon találjuk: x1 ,

x2 ,

x3

≥

0

x1 +

x2 +

x3

≤

200

3x1 +12x2 +20x3

≤ 2800

x1 + 7x2 +15x3

≤ 2500

Z = 6000x1 + 6500x2 + 12000x3 → maximum. A célfüggvény maximumát keressük! A feladatot majdnem teljesen az előzőhöz hasonló módon oldjuk meg. Csak a korlátozó feltételek definiálásánál lesz változás. 1.2.2.

Az adatrögzítés szabályai

Az adatok rögzítésének szabályai változatlanok, de íme, ismétlésként: 1.

Az x-eknek megfeleltjük az A oszlop celláinak tartalmát: x1 → A1, x2 → A2, …

0.9b verzió


11


2.

Az x-ek nemnegatív feltételezéséről a későbbiekben fogunk intézkedni.

3.

A további korlátozó feltételek (egyenlőtlenségek) bal oldalait mint képleteket (az x-eket már A-kal helyettesítve) beírjuk a B oszlopba, tehát pl. a B1 cella tartalma: =A1+A2+A3

4.

Az egyenlőtlenségek jobb oldalait célszerűen a C oszlopba rögzítjük a megfelelő sorokba, tehát pl. a C1 cella tartalma 200.

5.

A célfüggvény jobb oldalát mint képletet, szintén a B oszlopba rögzítjük, jelen esetben a B4 cellába: =6000*A1+6500*A2+12000*A3

Megjegyzés: Az adatrögzítéskor az x-eknek megfelelő tartományt üresen hagyjuk. Az Excel ott fogja kiszámolni az eredményeket. Rögzítsük az adatainkat! Ha nem feleltjük el az Eszközök menü Beállítások… párbeszédpanelen kérni a képletek mutattatását, akkor az adatrögzítésünk látványa a következő:

12


0.9b verzió


1.2.3.

A megoldás első lépései

A megoldáshoz ismét az Eszközök menü Solver menüpontját használjuk. A Solver paraméterek párbeszédpanelt a már megismert módszerrel töltsük ki a korlátozó feltételek kivételével!

1.2.4.

A korlátozó feltételek maghatározása

Ennél a pontnál eltérünk az előző feladat megoldásától! A korlátozó feltételek megfogalmazásánál kihasználjuk azt, hogy három darab, egymás alatt elhelyezkedő ikerfeltételünk van (mindegyik <= relációt tartalmaz!). Ezért a Korlátozó feltételek felvétele párbeszédpanelen a korlátozó feltételeket az alábbi

alakban állítjuk elő a következő lépéssorozattal: 1.

A Cellahivatkozás mező tartalmának beviteléhez húzzunk a B1:B3 tartományon!

2.

A reláció jellel nem kell foglalkoznunk, mert az alapértelmezett (<=) megfelel.

3.

0.9b verzió

Belekattintunk a Korlátozó feltétel beviteli mezőbe.


13


4.

Húzunk a C1:C3 tartományon.

5.

A párbeszédpanelt leokézzuk.

A három darab korlátozó feltétel az alábbi módon jelenik meg a Solver paraméterek párbeszédpanelen:

1.2.5.

Az x-ek nemnegatív korlátjának rögzítése

Ne feledkezzünk meg a Beállítás gombbal elérhető párbeszédpanelen a Nem negatív feltételezése jelölő négyzet beállításról! 1.2.6.

Megoldás

A feladat megoldásának egy részét a Solver paraméterek Megoldás gombjára kattintva kapjuk meg:

Az A oszlopban már is megjelennek az x-ek. Megjegyzés: Ha az Excel mutatja a képleteket, akkor a számokat – megfelelő oszlopszélesség esetén – a belső számábrázolásának megfelelő teljes hosszal és pontossággal jeleníti meg. Ezt látjuk most az A oszlopban.

14


0.9b verzió


Kérjünk Eredményjelentést:

Megjegyzés: Az eredményjelentéseket tartalmazó munkafüzet-lapokat az Excel – ha több eredményjelentésünk is van azonos munkafüzeten belül –, Eredmény jelentés x névvel látja el, ahol x egy sorszám. 1.2.7.


− A célfüggvény a B4 cellában helyezkedik el és végértéke kb. 1976471. − A módosuló cellák A1, A2 és A3, értékük rendre kb. 70,6; 0 és 129,4. − A korlátozó feltételek közül a harmadikat nem használtuk ki (Bőven kb. 488). − A másik két korlátot teljesen kihasználtuk (Éppen 0).

0.9b verzió


15


Az eredeti matematikai feladatra tehát összefoglalóan a következő választ adhatjuk: A célfüggvény a maximumát az x1=70,6; x2=0 és az x3=129,4 értéknél éri el, maximuma 1976471 (közelítő értékek!). A harmadik korlátot nem használtuk ki 488 egységgel. Az eredményeinket vesse egybe a Matematika II. jegyzet 378. oldalán közölt eredményekkel! Keressen magyarázatot az eltérésekre! Megjegyzések: •

A korlátozó feltételek hozzáadásakor a jobb oldalon nem lenne fontos a C oszlopba beírt konstansokra hivatkozni, hanem közvetlenül is beírhatnánk számértékeket. Ebben az esetben viszont nehéz lenne a korlátaink változására a Mi lenne, ha … játékot eljátszani.

•

A C oszlopokba írt konstansokat egyszerűbben meg tudjuk változtatni és a Solvertől újabb megoldást kérni. Ha nem a C oszlopba írtuk volna a konstansokat, akkor a próbálgatásokhoz át kellene szerkesztenünk a korlátozó feltételeket.

•

Az ikerfeltételek egyszerűbb megfogalmazásától is el kellett volna tekintenünk.

1.2.8.

A Mi lenne, ha… játék

A Mi lenne ha… játékhoz – ha eddig még nem tette volna meg –, olvassa el a fenti példához tartozó szövegeket a Matematika II. jegyzetben a 375. és a 378. oldalán! a)

Mi lenne, ha 300 hektáron termelhetnénk?

Megoldás:

16

1.

A C1 cellába 300 rögzítése.

2.

Eszközök, Solver…, Megoldás, Eredményjelentés, OK.


0.9b verzió


Értékelés: A javasolható termelés: burgonya kb. 188,2 hektáron, cukorrépát ne termeljünk, vegyes zöldséget kb. 111,8 hektáron. A várható bruttó jövedelmünk kb. 2470588 Ft-ra növekedne és júniusban megmaradna kb. 635 munkanap. b)

Mi lenne, ha a cukorrépa hektáronkénti bruttó jövedelme elérné a 8000 Ft-ot (az eredeti, 200 hektáron termelés esetén)?

Megjegyzés: A Solver futtatása után nincs visszavonási lehetőségünk! Az eredeti feladat szerinti alapmegoldáshoz visszatérni csak úgy tudunk, ha egy mentett állapotot ismét megnyitunk, vagy a C1 cellába visszaírva a 200 értéket ismét lefuttatjuk a Solvert.

0.9b verzió


17


Megoldás (az eredeti állapotból indulva): 1.

A B4 cellában lévő célfüggvényt átszerkesztjük a következőre: =6000*A1+8000*A2+12000*A3

2.


Értékelés: Ha a cukorrépa termelésekor hektáronként 8000 Ft lenne a bruttó jövedelem, akkor sem lenne érdemes termelni. c)

Mi lenne, ha a cukorrépa hektáronkénti bruttó jövedelme elérné a 12000 Ft-ot (az eredeti, 200 hektáron termelés esetén)?

Megoldás (az eredeti állapotból indulva): 1.

A B4 cellában lévő célfüggvényt átszerkesztjük a következőre: =6000*A1+12000*A2+12000*A3

2.

18



0.9b verzió


Értékelés: Ha a cukorrépa termelésekor hektáronként 12000 Ft lenne a bruttó jövedelem, akkor: − A várható bruttó jövedelem 2400000 Ft. − Burgonyát ne termeljünk. − Cukorrépát 150 hektáron termeljünk. − Vegyes zöldségfélét 50 hektáron termeljünk. − Júniusban megmarad 700 munkanap erőforrásunk (amit ajánlott értékesíteni).

1.3.

Lineáris programozás: Módosított normálfeladat.

Oldjuk meg a Matematika II jegyzet 383. oldalán található feladatot! A feladat megoldásához váltsunk át egy következő munkafüzet-lapra, vagy kezdjünk új dokumentumot.

0.9b verzió


19


1.3.1.


A feladat matematikai modelljét a 383. oldalon találjuk: x1 ,

≥

0

≤

100

x4

≤

200

x2 + 4x3 + 2x4

=

200

x2

=

150

x2 ,

2x1 + 4x2 + x1

x1 +

x 3,

x4

x3

+ 5x3 +

+

x4

Z = 2x1 + 2x2 + x3 + x4 → maximum. A célfüggvény maximumát keressük! A feladatot majdnem teljesen az előzőhöz hasonló módon oldjuk meg. Csak a korlátozó feltételek definiálásánál lesz változás. A megoldásnál ügyelnünk kell arra, hogy most már = relációk is szerepelnek; a korlátozó feltételek felvételénél az ikerfeltételeket egyszerűsítve fogjuk rögzíteni. 1.3.2.

Adatrögzítés

A képletek mutattatása után rögzítjük az adatokat az alábbiak szerint:

20


0.9b verzió


1.3.3.

A megoldás lépései

A Solver paramétereket az alábbiak szerint adjuk meg:

Most csak a korlátozó feltételek felvételét részletezzük: 1.

A Korlátozó feltételek Hozzáadás gombjára kattintunk.

2.

Húzunk a B1:B2 tartományon.

3.

Az alapértelmezett relációjelet nem módosítjuk (<=).

4.

Belekattintunk a Korlátozó feltétel mezőbe.

5.

Húzunk a C1:C2 tartományon:

6.

A Felvesz gombra kattintunk.

7.

Húzunk a B3:B4 tartományon.

8.

A reláció jelet módosítjuk az = jelre.

Megjegyzés: Most, mivel a reláció jelet módosítottuk, a kurzor átugrott a korlátozó feltétel mezőbe, ezért nem kell belekattintani.

0.9b verzió


21


9.

Húzás a C3:C4 tartományon:

10. Mivel ez az utolsó feltétel, ezért a párbeszédpanelt leokézzuk. 11. Nem feledkezünk meg a Nemnegatív feltételezése beállításáról, és kérünk egy Megoldást valamint egy Eredmény jelentést:

Az A oszlopban már is megjelennek az x-ek. Megjegyzés: Az A3 cellában lévő érték normál alakban van megadva. Értelmezése: Az E előtti számot be kell szorozni a 10-nek az E mögé írt hatványával: -3,99999988687227*10-7 Ez a szám egy negatív szám, abszolút értékét tekintve igen kicsiny, gyakorlatilag nullának tekinthető.

22


0.9b verzió


1.3.4.


− A célfüggvény a B5 cellában helyezkedik el és végértéke kb. 200. − A módosuló cellák A1, A2, A3 és A4, értékük rendre kb. 50, 0, 0, 100. − A korlátozó feltételek közül a másodikat nem használtuk ki (Bőven kb. 50). − A többi három korlátot gyakorlatilag teljesen kihasználtuk. Az eredeti matematikai feladatra tehát összefoglalóan a következő választ adhatjuk: A célfüggvény a maximumát az x1=50, x2=0, x3=0 és az x4=100 értéknél éri el, maximuma 200 (közelítő értékek!). A második korlátot nem használtuk ki 50 egységgel.

0.9b verzió


23


Az eredményeinket vesse egybe a Matematika II. jegyzet 391. oldalán közölt eredményekkel!

1.4.

Lineáris programozás: Szállítási feladat

Oldjuk meg a matematikai jegyzet 419. oldalán kezdődő szállítási feladatot! 1.4.1.


A matematikai modellt a 421. oldalon találjuk. xij

≥

0

i = 1, 2, 3;

j = 1, 2, 3, 4.

x11 + x12 + x13 + x14

=

15

x21 + x22 + x23 + x24

=

15

x31 + x32 + x33 + x34

=

15

x11 + x21 + x31

=

3

x12 + x22 + x32

=

18

x13 + x23 + x33

=

6

x14 + x24 + x34

=

18

K=10x11 + 8x12 + 6x13 + 4x14 + 20x21 + 16x22 + 8x23 + + 14x24 + 18x31 + 18x32 + 16x33 + 8x34 → minimum. A célfüggvény minimumát keressük! A megoldást ismét a Solverrel fogjuk végrehajtani, kétféle megoldást fogunk megnézni. a)

Megoldás I. („A biztos módszer”)

A megoldásunk az előzőektől kicsit eltér. − Az x11-et megfeleltetjük az A1 cella tartalmának.

24


0.9b verzió


− Az x21-et megfeleltetjük az A2 cella tartalmának. − Az x31-et megfeleltetjük az A3 cella tartalmának. − Az x12-őt megfeleltetjük az B1 cella tartalmának. − … − Az X34-et megfeleltetjük a D3 cella tartalmának. Az adatainkat az alábbi módon rögzítjük:

Megjegyzések: •

A baloldalakat az E oszlopba, a jobboldalakat az F oszlopba rögzítettük.

•

Az E1 cella tartalma kihúzással másolható.

•

Az E4-E6 cellák tartalmát külön-külön be kell írni (vagy másolás után átszerkeszteni, de úgy sem könnyebb).

•

Az ábrán a célfüggvény csak a szerkesztőlécen látszik teljes egészében.

A solver paraméterek párbeszédpanel tartalma az alábbi:

0.9b verzió


25


Megjegyzések: •

Az eddigiektől eltérően ez minimum feladat.

•

A módosuló cellák (A1:D3) kétdimenziós kiterjedésű tartományban vannak.

•

Mivel a korlátozó feltételek ikerfeltételek (mindegyik =-et tartalmaz), ezért azokra egyetlen feltétel megfogalmazásával hivatkozunk.

•

A szállítási feladatnál sem feledkezhetünk meg a Nemnegatív feltételezése beállításról.

A megoldás eredménye az alábbi Eredmény jelentés munkafüzet lapon olvasható:

26


0.9b verzió


Az eredeti matematikai feladatra tehát összefoglalóan a következő választ adhatjuk: A célfüggvény a minimumát az x11=3, x12=9, x14=3, x22=9, x23=6 és az x34=15 értéknél éri el, a minimuma 426. Az eredményeinket vesse egybe a Matematika II. jegyzet 426. oldalán közölt eredményekkel! b)

Megoldás II. („Haladóknak”)

Ennél a módszernél egyszerűsítéseket és egy új függvényt fogunk használni. Az adatokat az alábbi módon rögzítjük:

Megjegyzések: •

A kötségmátrix-ot rögzítjük az A5:D7 tartományba.

•

Mivel a korlátozó feltételek bal oldalai gyakorlatilag a – még egyelőre üres – A1:D3 megoldásmátrix sorainak illetve oszlopainak összegei, ezért azokat a SZUM() függvény segítségével állítjuk elő. A hatékony munka érdekében használja az Autoszum szolgáltatást, és ahol lehet, a kihúzással másolást!

A célfüggvényt az itt és most nem teljes részleteiben közölt SZORZATÖSSZEG() függvény segítségével képezzük: A SZORZATÖSSZEG() függvény szintaxisa: SZORZATÖSSZEG(tömb1;tömb2;tömb3; ...) ahol tömb1, tömb2, tömb3, … (minimum 2 db. tömb) az összeszorzandó tömbök elemeinek tartományai.

0.9b verzió


27


A SZORZATÖSSZEG() függvény szemantikája: A SZORZATÖSSZEG() függvény az argumentumában felsorolt azonos méretű tartományok azonos pozícióiban lévő celláinak tartalmát összeszorozza, és a szorzatokat összeadja. A Solver paraméterek megadása a I-es módszerrel teljesen azonos. Kérjünk megoldást és hasonlítsuk össze az Eredmény jelentést az I-es módszerrel kapott és a matematikai jegyzetben megadott eredményekkel.

1.5.

Lineáris regresszió

Oldjuk meg a Matematika II. jegyzet 317. oldalán található lineáris regresszió feladatot! 1.5.1.

A megoldás logikai lépéssorozata

1.

A feladat megoldásához (a jegyzettől eltérően) az adatainkat sorokba fogjuk rögzíteni.

2.

Az összetartozó értékpárokat diagramon ábrázoljuk.

3.

A ponthalmazra illeszthető, a ponthalmazt a legkisebb négyzetek módszerével legjobban közelítő egyenest és annak paramétereit az Excel segítségével fogjuk meghatározni.

1.5.2.

Adatrögzítés

Rögzítsük az adatainkat az alábbi minta szerint:

Megjegyzések: •

Az

adatok

rögzítésekor

célszerűen

a

számértékeket

a

numerikus

számbillentyűzeten visszük be, és jobbra irányba a billentyűvel lépünk. •

A 8,0 érték az Excel-ben 8 egészként jelenik meg. A matematikai elvárásoknak megfelelően az első sor összes adatára állítsuk be az egy tizedes megjelenítést!

28


0.9b verzió


Fontos! A továbbiakban részletezésre kerülő megoldás leghűebb követéséhez (az adatbevitel után mindenképp) formázza meg a táblázatot: 1.

Az A oszlopot állítsa a szükséges legkeskenyebbre (az oszlopcímke jobb szélén végrehajtott dupla kattintással).

2.

A B-M oszlopokat a kijelölésük után keskenyítse meg kb. 4 karakter szélesre.

3.

Az A-M oszlopok kijelölése után állítsa be a Kijelölés mértékű nagyítást:

1.5.3.

Diagram készítés

Ábrázoljuk az összetartozó értékeket diagramon. Célunk, hogy a jegyzetben közreadott minta arányait minél jobban megközelítsük, azaz valami ehhez hasonlót állítsunk elő:

0.9b verzió


29


A diagramot a Diagram varázsló

segítségével állítjuk elő. A Diagram varázsló négy

lépésben vezet végig bennünket a diagram előállításához. A varázsló indítása előtt célszerű az adatok valamelyik celláját kijelölni aktív cellaként. 1. lépés

Az első lépésben kiválasztjuk a Pont (XY) diagram típust. Az altípusok közül az összekötés nélküli pontokat választjuk.

30


0.9b verzió


A Minta megtekintéséhez tartsa lenyomva gombot az egérkurzorral nyomva tartva, ha az aktív cella az egyébként jól definiálható, ábrázolandó tartományban van, már is megtekinthetjük a kiválasztott típusnak megfelelő diagram előnézeti képét:

A tovább gombbal megyünk a második lépésre.

0.9b verzió


31


2. lépés

Diagram forrásadatként, ha az előző vagy ebben a lépésben az Excel nem tudja meghatározni (jól) az ábrázolandó tartományt, a Tartomány mezőbe húzással vigyük be azt a tartományhivatkozást, amelybe az adatokat rögzítettük. Bejelöljük, hogy az adataink Sorokban helyezkednek el, majd Tovább.

32


0.9b verzió


3. lépés

Beírjuk a Diagramcím mezőbe a diagram címét, ami lehetőleg utal a tartalmára, például: Lineáris regresszió1. Beírjuk az értéktengelyek feliratait is. Megjegyzés: Érdemes a mintát figyelni a bevitel közben. Ha a bevitel után egy kicsit várunk, a mintán megjelenik az új felirat. 4. lépés A negyedik lépésben meg kell határoznunk, hogy hol legyen a diagram. Elhelyezhetjük egy új munkalapon vagy objektumként itt, ezen (vagy esetleg másik) munkalapon:

0.9b verzió


33


Válasszuk az objektumként itt lehetőséget, mert a további vizsgálatainkhoz és a Mi lenne, ha… játékhoz így célszerű elhelyezni. A megjelent diagram nemigen hasonlít még a megcélzott formájú, fentebb és a jegyzetben közöltre:

A felíratok túl nagyok, a diagram rajzterülete kicsi, és a képernyőt is jobban kihasználhatnánk (nagyobb méretű diagram kellene). Megjegyzés: Az előállt igen torz diagram annak köszönhető, hogy a képernyőt kinagyítottuk. A diagramot jelenleg csak nyers állapotúnak tekinthetjük, szerencsére az összes objektumát átformálhatjuk. 1.5.4. Érdemes

A diagram formázása megjegyezni:

a

diagramok

minden

látható

objektuma

formázását

kezdeményezhetjük a rajta végrehajtott dupla kattintással. Ettől a szabálytól a címek és feliratok abban különböznek, hogy egy már kijelölt (azaz egyszer már rákattintott)

1

Lektori megjegyzés: Szerencsésebb lenne a „A búza kalászonkénti szemszámának alakulása” diagramcím.

34


0.9b verzió


címen vagy feliraton újabb kattintáskor szerkesztő üzemmódba kerülünk. Ha ilyenkor szeretnénk formázni, kattintsunk duplát az adott szöveges objektum keretén. Hajtsuk végre az alábbi formázásokat, hogy a mintát a lehető leghűebben közelítsük. a)

A diagram méretének beállítása: 1.

Húzással helyezzük a diagramot az adatsorok alá a bal felső sarok közelébe.

2.

A megjelent méretező fogantyúk közül a jobb alsó húzásával nagyítsuk fel.

0.9b verzió


35


b)

A feliratok méretének csökkentése: 1.

Dupla kattintás a Diagramcímen.

2.

A második (Betűtípus) fülön a betűméretet csökkentsük le 10 pont méretűre:

A fenti módszerrel csökkentsük le a tengelyek feliratait és a jelmagyarázatot is, de azok legyenek 8 pont méretűek (a rajtuk végrehajtott dupla kattintás után a megjelenő párbeszédpanelek mindegyikének van Betűtípus füle).

36


0.9b verzió


A diagramon még mindig túl nagyok a tengelyeken lévő számok, ezeket is csökkenthetjük a rajtuk végrehajtott dupla kattintás után megjelenő Tengely formázása párbeszédpanelen. Legyenek szintén 8 pont méretűek:

A diagramunk kezd hasonlítani a mintához. A még feltűnő eltérések: − A tengelyek metszéspontja jelenleg a 0,0 értékeknél van, amelynek a minta szerint a 6,10 értékeknél kellene lennie. − A maximális értékek sem a minta szerintiek. − A jelmagyarázat jelenleg a rajzterülettől jobbra van, a mintán viszont a rajzterületen.

0.9b verzió


37


c)

A tengelyek formázása

Az x tengely formázásához: 1.

Dupla kattintás az x tengely valamelyik számértékén.

2.

Skála fül kiválasztása:

3.

A Minimum mezőbe 6 rögzítése.

4.

A Maximum mezőbe 11 rögzítése, és OK.

Az y tengely formázását hajtsa végre önállóan az x tengely formázásánál megadott lépések szerint! Megjegyzés: A Tengely formázása párbeszédpanelen a minimum és maximum értékek rögzítéséhez nem kell a beviteli mezők előtt lévő jelölőnégyzeteket törölni, azok az értékek beírásakor automatikusan törlődnek. d)

38

Utolsó simítások 1.

A Jelmagyarázatot húzással helyezzük át a Rajzterületre.

2.

Jelöljük ki a Rajzterületet valahol a belsejébe kattintással.


0.9b verzió


3.

A Rajzterület jobb oldalának közepén megjelent fogantyúval nagyítsuk meg a Rajzterületet jobbra irányban úgy, hogy a mintához hasonló, minél nagyobb területet foglaljon el.

4.

Az x tengelyen lévő (egész) számok tartalmaznak értéktelen nullákat az első tizedes helyen. Szüntessük ezt meg az x tengely formázásához tartozó párbeszédpanel Szám fülén a Tizedesjegyek 0-ra állításával:

5.

A Kalász hossza (cm) tengelycímet húzzuk az eredeti mintán látott, jobb alsó helyre.

0.9b verzió


39


A diagramot előállítottuk:

1.5.5.

A regressziós egyenes előállítása

A regressziós egyenes előállításához szükséges lépéssorozat: 1.

A diagramon ábrázolt adatsor valamelyik tagján jobb oldali egérgomb kattintással behívjuk a hozzá tartozó helyi menüt:

40


0.9b verzió


2.

Kiválasztjuk a Trendvonal felvétele… menüpontot.

3.

A megjelenő párbeszédpanelen a Típus fülön a Lineáris az alapértelmezetten kiválasztott, ezért azt most, mivel lineáris regresszióról van szó, nem kell módosítanunk.

4.

Az Egyebek fülön bejelöljük az Egyenlet látszik a diagramon, és az R-négyzet értéke látszik a diagramon jelölőnégyzeteket:

5.

0.9b verzió

A párbeszédpanelt érvényesítjük.


41


Megjelent a regressziós egyenes rajza, egyenlete és az R-négyzet:

A formázás azonban még hagy kívánnivalót maga után… A felirat nem eléggé olvasható és túl nagy, valamint a regressziós egyenest (a trendvonalat) szokás az adatoknál hosszabban ábrázolni. 1.5.6.

A regressziós egyenes és feliratainak formázása

Az ajánlott további formázások: 1.

A regressziós egyenes feliratának valamely részén duplát kattintunk.

2.

A megjelenő Adatfeliratok formázása párbeszédpanel Betűtípus fülén 10 pont betűméretet állítunk be.

42


0.9b verzió


3.

A Mintázat fülön a Választott rádiógomb kiválasztásával szegélyt adunk a feliratnak:

4.

A Terület színválasztékából kiválasztjuk a Fehéret.

5.


6.

A feliratot a Rajzterület bal felső sarka közelébe húzzuk.

0.9b verzió


43


A regressziós egyenes megnyújtása a Trendvonal formázása párbeszédpanelen történik: 7.

A Trendvonalon duplát kattintunk:

8.

Az Egyebek fülön az Előrejelzés Előre és Vissza mezőbe 1-1 értéket rögzítünk.

9.

44



0.9b verzió


A feladatot megoldottuk. Az eredményeket (az egyenes egyenletét és a determinációs együttható értékét) hasonlítsa össze a Matematika jegyzet 326. és 328. oldalán közöltekkel!

1.6.

Statisztikai feladatok megoldása

A feladatgyűjtemény 227/a feladatát vesszük alapul a statisztikai számításainkhoz. A feladat szerint hízott libák két csoportjából mintát veszünk és az alábbi mérési eredményeink vannak: I Gyakoriság (db):

1

1

1

1

1

Hízott libák tömege (kg):

5

9

7

8

11

II Gyakoriság (db):

2

1

4

Hízott libák tömege (kg).

5

7

8

1.6.1.

Az adatok rögzítésének szabálya a statisztikai feladatok megoldásához

Minden adatot rögzítünk! Ez azt jelenti, hogy a gyakorisággal megadott értékeknél nem rögzítjük a gyakoriságot mint számértéket, hanem a gyakoriság darabszámú értékeket rögzítjük. A fenti adatok rögzítése a statisztikai feladatok megoldásához a következő módon történik:

0.9b verzió


45


A továbbiakban a statisztikai számításokhoz az Eszközök menü Adatelemzés… menüpontjával

fogjuk

a

megfelelő

módszert

kiválasztani.

A

kiválasztás

párbeszédpanelje az alábbi:

A fenti listáról használni fogjuk a − Leíró statisztika − Két mintás F-próba a szórásnégyzetre − Két mintás t-próba egyenlő szórásnégyzeteknél adatelemző módszereket. 1.6.2.

Leíró statisztika

Igen gyakran a minták alapján néhány alapvető statisztikai jellemzőt kell meghatároznunk. Ezt az Excelben a Leíró statisztika szolgáltatással számíttathatjuk ki.

46


0.9b verzió


Az adatrögzítés után az adatelemző módszerek közül válasszuk ki a Leíró statisztika adatelemző módszert. A megjelenő párbeszédpanel kitöltésének lépései:

1.

Bemeneti tartományként az A1:B8 tartományt húzással vigyük be.

2.

Ellenőrizzük le, hogy a Csoportosítási alap megfelelő-e. Jelenleg az Oszlopok alapértelmezett csoportosítás megfelel.

3.

Mivel az adatainkkal együtt feliratok is rögzítettünk, ezért a Feliratok az első sorban jelölő négyzetet jelöljük be.

4.

Jelöljük be a Várható érték konfidenciaszintje jelölőnégyzetet, az értékét most hagyjuk 95%-on.

A számítási eredmények helyének meghatározásához: 5.

Kattintsunk a Kimeneti tartomány rádiógombra.

6.

Tegyük a kurzort a Kimeneti tartomány beviteli mezőjébe.

7.

Kattintsunk a D1 cellába.

Megjegyzés: A D1 cella megjelölésével azt határoztuk meg, hogy az Excel az adott cellától kezdve lefelé és jobbra irányban állíthatja elő az eredményeket. 8.

Jelöljük be az Összesítő statisztika jelölőnégyzetet.

9.

Érvényesítsük a párbeszédpanelt.

0.9b verzió


47


10. Az eredményeket tartalmazó (D:G) oszlopokat az oszlopcímkék jobb szélén végrehajtott (4 db) duplakattintással vagy a D:G oszlopok kijelölése után bármelyik oszlopcímke jobb szélén végrehajtott (1 db) duplakattintással állítsuk a szükséges legkeskenyebb méretűre. Az alábbi táblázat állt elő:

Az eredmények közül a matematikában tanultaknak a következő megfeleltetések vannak: 1.

Várható érték: a minta átlaga.

2.

A minta varianciája: a minta szórásnégyzete.

A többi elnevezés a matematikában tanultakhoz hasonló. Megjegyzés: Az I csoportban a módusz azért hiányzik, mert nincsen leggyakoribb érték. 1.6.3.

F-próba

A két minta segítségével állapítsuk meg, hogy az alapsokaságok szórásai P=5% szignifikancia szint mellett különböznek-e? A számításhoz először vagy rögzítsük újra az adatainkat, vagy készítsünk róla egy másolatot.

48


0.9b verzió


Az adatrögzítés után az adatelemző módszerek közül válasszuk ki a Kétmintás F-próba a szórásnégyzetre adatelemző módszert. A megjelenő párbeszédpanel kitöltésének lépései:

3.

Az 1. változótartomány beviteléhez húzzunk az I. csoport adatain (húzás A1-től A6-ig).

4.

Kattintsunk a 2. változótartomány beviteli mezőjébe.

5.

Húzzunk a II. csoport adatain.

6.

A Feliratok jelölőnégyzetet jelöljük be, mert a tartományokba a feliratokat is belefoglaltuk.

Az Alfa: 0,05 értéket most nem kell módosítanunk, mert ez a szignifikancia szint (P=5%). 7.


8.


9.


Megjegyzés: A D1 cella megjelölésével most is azt határoztuk meg, hogy az Excel az adott cellától kezdve lefelé és jobbra irányban állíthatja elő az eredményeket. 10. Érvényesítsük a párbeszédpanelt.

0.9b verzió


49


Az oszlopok szélességének beállítása után az alábbi eredményeket kapjuk:

A matematikában tanultaktól eltérő jelölések: 1.

Várható érték: a minta átlaga.

2.

Variancia: a minta szórásnégyzete.

3.

Megfigyelések: a minta darabszáma

4.

df: a szabadságfok

5.

F: a számított F érték

Az eredmények matematikai értékelése: Mivel a számított F érték (2,5) kisebb, mint az F kritikus (~4,53), ezért azt a nullhipotézist, hogy a két alapsokaság szórásnégyzete nem különbözik a megadott (5%) szignifikancia szinten, elfogadjuk. 1.6.4.

t-próba

A két minta segítségével állapítsuk meg, hogy az alapsokaságok átlagai P=5% szignifikancia szint mellett különböznek-e? Megjegyzés: A t-próbát különböző módon kell végrehajtani, ha a szórásnégyzetek egyenlők vagy különbözőek. Az előző számításunk eredményeként megállapítottuk, hogy az alapsokaságok szórásnégyzetei egyenlőknek tekinthetők. A számításhoz először vagy rögzítsük újra az adatainkat, vagy készítsünk róla egy másolatot.

50


0.9b verzió


Az adatrögzítés után az adatelemző módszerek közül válasszuk ki a Két mintás t-próba egyenlő szórásnégyzeteknél adatelemző módszert. A megjelenő párbeszédpanel kitöltésének lépései:

Megjegyzés: A megjelenő párbeszédpanel nagyon hasonlít az F-próbánál látotthoz. 1.

Az 1. változótartomány beviteléhez húzzunk az I. csoport adatain (húzás A1-től A6-ig).

2.

Kattintsunk a 2. változótartomány beviteli mezőjébe.

3.

Húzzunk a II. csoport adatain.

4.

A Feliratok jelölőnégyzetet jelöljük be, mert a tartományokba a feliratokat is belefoglaltuk.

Az Alfa: 0,05 értéket most nem kell módosítanunk, mert ez a szignifikancia szint (P=5%). 5.


6.


7.


Megjegyzés: A D1 cella megjelölésével most is azt határoztuk meg, hogy az Excel az adott cellától kezdve lefelé és jobbra irányban állíthatja elő az eredményeket. 8.

0.9b verzió

Érvényesítsük a párbeszédpanelt.


51


Megjegyzés: A párbeszédpanelen lévő Feltételezett átlagos eltérés mezőt üresen hagyjuk. Az oszlopok szélességének beállítása után az alábbi eredményeket kapjuk:

Megjegyzés: A fenti feladatnál a táblázatban szereplő kettő kritikus érték közül a t kritikus kétszélű értéket kell figyelembe venni. Az eredmények matematikai értékelése: Mivel a számított t érték (kb. 0,95) kisebb mint a t kritikus (kb. 2,23), ezért azt a nullhipotézist, hogy a két alapsokaság átlaga nem különbözik a megadott (5%) szignifikancia szinten, elfogadjuk.

52


0.9b verzió

SZÁMÍTÁSTECHNIKA EXCEL II

Recommend Documents