Informatika | Alapismeretek, ECDL » Kéry László - Excel a felsőfokú matematikában

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.11 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.12 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 Excel a felsőfokú matematikában 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+4A2+6A3 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában Á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.13 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ó Számítástechnika: EXCEL II. segédlet 5 Excel a felsőfokú matematikában 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 1.14 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ó Számítástechnika: EXCEL II. segédlet 7 Excel a felsőfokú matematikában 1.15 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 1.16 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.17 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ó Számítástechnika: EXCEL II. segédlet 9 Excel a felsőfokú matematikában 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.18 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 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.21 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.22 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ó Számítástechnika: EXCEL II. segédlet 11 Excel a felsőfokú matematikában 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+6500A2+12000A3 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 1.23 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.24 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. Számítástechnika: EXCEL II. segédlet 13 Excel a felsőfokú matematikában 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.25 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.26 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 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.27 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 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ó Számítástechnika: EXCEL II. segédlet 15 Excel a felsőfokú matematikában 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.28 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. Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában É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ó Számítástechnika: EXCEL II. segédlet 17 Excel a felsőfokú matematikában 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+8000A2+12000A3 2. Eszközök, Solver, Megoldás, Eredményjelentés, OK. É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+12000A2+12000A3 2. 18 Eszközök, Solver, Megoldás, Eredményjelentés, OK. Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában É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ó Számítástechnika: EXCEL

II. segédlet 19 Excel a felsőfokú matematikában 1.31 A matematikai modell 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.32 Adatrögzítés A képletek mutattatása után rögzítjük az adatokat az alábbiak szerint: 20 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 1.33 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ó Számítástechnika: EXCEL II. segédlet 21 Excel a felsőfokú matematikában 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 1.34 Az Eredmény jelentés fontosabb információi és matematikai értelmezése − 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ó Számítástechnika: EXCEL II. segédlet 23 Excel a felsőfokú matematikában 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.41 A matematikai modell 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában − 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ó Számítástechnika: EXCEL II. segédlet 25 Excel a felsőfokú matematikában 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 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ó Számítástechnika: EXCEL II. segédlet 27 Excel a felsőfokú matematikában 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.51 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.52 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 <TAB> 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 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.53 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ó Számítástechnika: EXCEL II. segédlet 29 Excel a felsőfokú matematikában 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 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ó Számítástechnika:

EXCEL II. segédlet 31 Excel a felsőfokú matematikában 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 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ó

Számítástechnika: EXCEL II. segédlet 33 Excel a felsőfokú matematikában 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.54 É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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 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ó Számítástechnika: EXCEL II. segédlet 35 Excel a felsőfokú matematikában 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 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ó Számítástechnika: EXCEL II. segédlet 37 Excel a felsőfokú matematikában 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. Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 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ó Számítástechnika: EXCEL II. segédlet 39 Excel a felsőfokú matematikában A diagramot előállítottuk: 1.55 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 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. Számítástechnika: EXCEL II. segédlet 41 Excel a felsőfokú matematikában 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.56 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a

felsőfokú matematikában 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. A párbeszédpanelt érvényesítjük. 6. A feliratot a Rajzterület bal felső sarka közelébe húzzuk. 0.9b verzió Számítástechnika: EXCEL II. segédlet 43 Excel a felsőfokú matematikában 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 A párbeszédpanelt érvényesítjük. Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 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.61 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ó Számítástechnika: EXCEL II. segédlet 45 Excel a felsőfokú matematikában 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.62 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 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ó Számítástechnika: EXCEL II. segédlet 47 Excel a felsőfokú matematikában 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.63 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 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. Kattintsunk a Kimeneti tartomány rádiógombra. 8. Tegyük a kurzort a Kimeneti tartomány beviteli mezőjébe. 9. Kattintsunk a D1 cellába. 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ó Számítástechnika: EXCEL II. segédlet 49 Excel a felsőfokú matematikában 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.64 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

Számítástechnika: EXCEL II. segédlet 0.9b verzió Excel a felsőfokú matematikában 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. 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 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. Számítástechnika: EXCEL II. segédlet 51 Excel a felsőfokú matematikában 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 Számítástechnika: EXCEL II. segédlet 0.9b verzió