Egyszerű iterációs módszer Excelben. Példák néhány numerikus módszer megoldására Excelben

Akik közel állnak hozzád numerikus módszerek

EGY ISMERETLEN NEMLINEÁRIS EGYENLET MEGOLDÁSA.

Egy ismeretlennel rendelkező egyenlet felírható így kanonikus forma

Az egyenlet megoldása a gyökerek megkeresése, azaz. olyan x-értékek, amelyek az egyenletet azonossággá alakítják. Attól függően, hogy mely függvények szerepelnek az egyenletben, kettőt különítünk el nagy osztály egyenletek - algebrai és transzcendentális. Egy függvényt algebrainak nevezünk, ha a függvény értékének megszerzéséhez adott értéket x számtani műveleteket és hatványozást kell végrehajtania. A transzcendentális függvények közé tartozik az exponenciális, logaritmikus, trigonometrikus direkt és inverz stb.

Lelet pontos értékeket gyökerek csak kivételes esetekben lehetségesek. Általában módszereket használnak a gyökök közelítő kiszámítására adott E pontossággal. Ez azt jelenti, hogy ha megállapítást nyer, hogy a kívánt gyök az intervallumon belül van, ahol a a bal oldali határ, b pedig a jobb határvonal az intervallum és az intervallum hossza (b-a)<= E, то за приближенное значение корня можно принять любое число, находящееся внутри этого интервала.

A gyökerek hozzávetőleges értékeinek megtalálásának folyamata két szakaszra oszlik: 1) a gyökerek szétválasztása és 2) a gyökerek finomítása egy adott pontossági fokig. Nézzük ezeket a szakaszokat részletesebben.

1.1 A gyökerek szétválasztása.

Egy egyenlet bármely gyökere elválasztottnak tekinthető az intervallumon, ha a vizsgált egyenletnek nincs más gyöke ezen az intervallumon.

A gyökök szétválasztása azt jelenti, hogy az x megengedett értékeinek teljes tartományát szegmensekre osztjuk, amelyek mindegyike csak egy gyökeret tartalmaz. Ez a művelet kétféleképpen hajtható végre - grafikusan és táblázatos formában.

Ha az f(x) függvény olyan, hogy könnyen elkészíthető egy jó minőségű grafikon a változásáról, akkor ebből a grafikonból nagyjából két számot találhatunk, amelyek között a függvénynek az abszcissza tengellyel való egy metszéspontja van. Néha a konstrukció megkönnyítése érdekében célszerű az eredeti kanonikus egyenletet f 1 (x) = f 2 (x) formában ábrázolni, majd ezekből a függvényekből grafikonokat készíteni, és a gráfok metszéspontjának abszcisszái szolgálnak ennek az egyenletnek a gyökerei.

Ha van számítógépe, a leggyakoribb táblázatos módszer a gyökerek elválasztására. Ez abból áll, hogy az f(x) függvényt táblázatba foglaljuk, amikor az x egy bizonyos x kezdeti értékről egy x értékre változik egy dx lépéssel. A feladat az, hogy ebben a táblázatban találjuk meg az x két szomszédos értékét, amelyekre a függvény különböző előjelekkel rendelkezik. Tegyük fel, hogy ilyen két a és b=a+dx érték található, azaz. f(a)*f(b)<0. Тогда согласно теореме Больцано-Коши внутри отрезка , если функция f(x) непрерывна, существует точка с, в которой f(c)=0. EXCEL позволяет легко реализовать оба способа отделения корней. Рассмотрим их на примере.

Példa 1.1.

El kell választani az egyenlet gyökereit

Ehhez az EXCEL-szabályok szerint megírt f(X) = exp(X) - 10*X függvényt kell táblázatba foglalni, és fel kell készíteni a grafikonját, ahogy X egy dX lépéssel valamely X kezdetről X végére változik. Legyenek ezek az értékek először a következők: X kezdet = 0, X vége = 5, dX = 0,5. Ha az X változásának ezen határain belül nem tudunk egyetlen gyökeret elválasztani, akkor új x kezdeti és végső értéket kell beállítanunk, és esetleg módosítanunk kell a lépést.

Táblázat készítéséhez célszerű egy speciális TABLE szubrutint használni. Ehhez egy új munkalapon a B1 cellába írja be a következő szöveget: GYÖKKEREK ELVÁLASZTÁSA. Ezután az A2 cellába írjuk be a szöveget: x, és a szomszédos B2 cellába - a szöveget: f(x). Ezután az A3 cellát üresen hagyjuk, de a B3 cellába beírjuk a vizsgált függvény képletét az EXCEL szabályok szerint, nevezetesen

Ezután töltse ki az X változtatások numerikus sorozatát az A4:A14 sorokban 0-tól 5-ig 0,5-ös lépésekben.

Jelölje ki az A3:B14 cellablokkot. Most adjuk ki a menüparancsot Adatok - táblázat. A táblázatos eredmények a B4:B14 cellák blokkjába kerülnek. Annak érdekében, hogy vizuálisabbak legyenek, formázni kell a B4:B14 blokkot úgy, hogy a negatív számok piros színűek legyenek. Ebben az esetben könnyű megtalálni az X két szomszédos értékét, amelyeknél a függvényértékek eltérő előjelűek. Ezeket a gyökérszétválasztási intervallum végeinek kell tekinteni. A mi esetünkben, amint az a táblázatból is látható, két ilyen intervallum van - és a [ 3,5;4].

Ezután készítsük el a függvényünk grafikonját az A4:B14 blokk kiválasztásával és a meghívással Chart Master. Ennek eredményeként a képernyőn megkapjuk az f(X) változásának diagramját, amelyből a és a gyökök következő elválasztási intervallumai láthatók.

Ha most megváltoztatja az x számértékeit az A4:A14 blokkban, akkor a B4:B14 cellák és a grafikon függvényértékei automatikusan megváltoznak.

1.2 Gyökök finomítása: iterációs módszer.

A gyökér iterációs módszerrel történő finomításához a következőket kell megadni:

Maga a módszer két szakaszra osztható:
a) átmenet az f(X)=0 egyenlet kanonikus formájából az X = g(X) iteratív alakba,
b) számítási iteratív eljárás a gyökér finomítására.

Az egyenlet kanonikus formájától az iteratív formáig többféleképpen léphet át, csak az a fontos, hogy ennek során elégséges feltétele a módszer konvergenciájának: çg’(X)ç<1 на , azaz az iteráló függvény első deriváltjának modulusának 1-nél kisebbnek kell lennie az intervallumon. Sőt, minél kisebb ez a modul, annál nagyobb a konvergencia sebessége.

A módszer számítási eljárása a következő. Kiindulási közelítést választunk, általában X 0 = (a+b)/2. Ekkor kiszámoljuk X 1 =g(X 0) és D= X 1 - X 0. Ha a D modul<= E, то X 1 является корнем уравнения. В противном случае переходим ко второй итерации: вычисляем Х 2 =g(X 1) и новое значение D=X 2 - X 1 . Опять проводим проверку на точность и при необходимости продолжаем итерации. Если g(X) выбрано правильно и удовлетворяет достаточному условию сходимости, то эта итерирующая процедура сойдется к корню. Следует отметить, что от знака g’(X) зависит характер сходимости: g’(X)>0 esetén a konvergencia monoton lesz, azaz növekvő iterációkkal D monoton (előjelváltás nélkül) közelíti E-t, míg g'(X)<0 сходимость будет колебательной , azaz D abszolút értékben megközelíti E-t, minden iterációnál előjelet váltva.

Nézzük meg egy példa segítségével az iterációs módszer megvalósítását EXCEL-ben.

Példa 1.2

Tisztázzuk iterációval a 2.1. példában elválasztott gyökök jelentését. Tehát legyen f(X)= exp(X) - 10*X, az első gyöknél a=0 és b=0,5. Legyen E=0,00001. Hogyan válasszunk iterációs függvényt? Például g(X)=0,1*exp(X). Az çg’(X)ç intervallumon<1 и достаточное условие сходимости выполняется. Кроме того, эта производная >1 az intervallumról és a konvergencia jellegéről monoton lesz.

Programozzuk az iterációs metódust ehhez a példához ugyanazon a munkalapon, ahol a gyökérszétválasztást végeztük. Az A22 cellába 0-val egyenlő számot írunk be. A B22 cellába az =0,1*EXP(A22), a C22 cellába pedig az =A22-B22 képletet írjuk. Így a 22. sor az első iteráció adatait tartalmazza. A 23. sorban lévő második iteráció adatainak lekéréséhez másolja a B22 cella tartalmát az A23 cellába, és írja be a =B22 képletet az A23-ba. Ezután át kell másolnia a B22 és C22 cellák képleteit a B23 és C23 cellákba. Az összes többi iterációból származó adatok megszerzéséhez ki kell jelölnie az A23, B23, C23 cellákat, és át kell másolnia azok tartalmát az A24: C32 blokkba. Ezek után elemezni kell a D = X - g(X) változást a C oszlopban, keresse meg D<0,00001 по модулю и выбрать соответствующее ему значение Х из столбца А. Это и есть приближенное значение корня.

A nagyobb áttekinthetőség érdekében diagramot készíthet az iterációs módszerhez. Az A22:C32 blokk kiválasztásával és a Diagram varázsló, három grafikont kapunk X, g(X) és D változásairól az iterációk számától függően, 3/5 lépés válassza a 2-es formátumot, és 4/5 lépés Diagram készítésekor az X-tengely címkéihez nulla oszlopokat kell kijelölni. Most már jól látható a D konvergenciájának monoton jellege.

Az intervallumon az egyenlet második gyökének tisztázásához egy másik iterációs függvényt kell választanunk úgy, hogy az első deriváltja abszolút értékben kisebb legyen egynél. Válasszuk a következőt: g(X)= LN(X)+LN(10). Az A22 cellába hozzáadjuk az új X0 = 3,75, a B22 cellában pedig az új képletet =LN(A22)+LN(10). Másoljuk át a képletet a B22-ből a B23:B32 blokkba, és azonnal kapunk új adatokat és egy átépített diagramot. Határozzuk meg a második gyök hozzávetőleges értékét.

1.3 Gyökerek finomítása: Newton-módszer.

A gyökér Newton-módszerrel történő tisztázásához a következőket kell megadni:

1) az f(X) = 0 egyenletet és az f(X) egyenletet képlet formájában kell megadni,

2) a számok - a bal oldali határ és a b - annak az intervallumnak a jobb határa, amelyen belül egy gyök található,

3) E szám - a gyökér megszerzésének meghatározott pontossága,

4) az f(X) függvénynek kétszer differenciálhatónak kell lennie, és ismerni kell az f’(X) és f”(X) képleteket.

A módszer a sorozat iteratív számításaiból áll

X i+1 = X i - f(X i)/f’(X i), ahol i=0,1,2, ...,

intervallumhoz tartozó és az f(X 0)*f”(X 0)>0 feltételt kielégítő X 0 kezdeti közelítés alapján. Elegendő feltételek a konvergenciához módszer szerint a vizsgált függvény első és második deriváltjának előjelet kell tartania az intervallumon. Kezdeti közelítésként általában a vagy b-t választják, attól függően, hogy melyik felel meg az X 0 kiválasztási képletének.

Newton módszere egyszerű geometriai értelmezést tesz lehetővé. Ha egy (X i ;f(X i)) koordinátájú ponton keresztül húzunk egy érintőt az f(X) görbére, akkor ennek az érintőnek a 0X tengellyel való metszéspontjának abszcisszája a gyök következő közelítése X i+1.

A Newton-módszer az iterációs módszer némi módosításának tekinthető, amely minden iterációs lépésnél a legjobb g(X) iterációs függvényt adja. Végezzük el a következő transzformációkat az eredeti f(X)=0 kanonikus egyenlettel. Szorozzuk meg bal és jobb oldalát valamilyen nullától eltérő l számmal. Ezután balról és jobbról összeadjuk az X mentén. Akkor megvan

X = g(X) = X +l*f(X).

G(X) differenciálásával kapjuk, hogy g’(X) = 1 + l*f’(X). A çg’(X)ç iterációs módszer konvergenciájának elégséges feltételéből<1. Потребуем, чтобы на i-том шаге итерации сходимость была самой быстрой, т.е. çg’(X i)ç =0. Тогда l=-1/ f’(X i) и мы пришли к методу Ньютона.

A módszer számítási eljárása a következő. Az X 0 kezdeti közelítést választjuk, amely általában egyenlő a vagy b-vel. Ekkor kiszámoljuk X 1 = X 0 - f(X 0)/f’(X 0) és D= X 1 - X 0. Ha a D modul<= E, то X 1 является корнем уравнения. В противном случае переходим ко второй итерации: вычисляем Х 2 и новое значение D=X 2 - X 1 . Опять проводим проверку на точность и при необходимости продолжаем итерации. Если X 0 выбрано правильно, а функция удовлетворяет достаточному условию сходимости, то эта итерирующая процедура быстро сойдется к корню.

1.3. példa.

Használjuk Newton módszerét az 1.1. példában elválasztott gyök értékének tisztázására. Tehát legyen f(X)= exp(X) - 10*X, az első gyökérre a=0 és b=0,5. Legyen E=0,00001. Az f(X) első és második derivált képlete a következő

f’(X) = exp(X) - 10 és f”(X) = exp(X).

Nyilvánvaló, hogy X 0 = a = 0, mert f(0)*f”(0) = 1 >0.

A 43. sorban lévő második iteráció adatainak lekéréséhez másolja a D42 cella tartalmát az A43 cellába, és írja be a =D42 képletet az A43-ba. Ezután át kell másolnia a B42, C42, D42, E42 cellák képleteit a B43, C43, D43, E43 cellákba. Az összes többi iterációból származó adatok beszerzéséhez ki kell jelölnie a cellákat a 43. sorban, és át kell másolnia azok tartalmát az A44:E47 blokkba. Ezek után elemezze a D változását az E oszlopban, keresse meg D-t<0,00001 по модулю и выбрать соответствующее ему значение Х из столбца А. Это и есть приближенное значение корня. При правильно введенных формулах метод Ньютона сходится за 3 или 4 итерации. Поэтому строить диаграмму для этого метода нет необходимости.

1.4. Gyökerek finomítása: felezési módszer (szakasz felezése).

A gyökér felező módszerrel történő tisztázásához a következőket kell megadni:

1) az f(X) = 0 egyenletet és az f(X) egyenletet képlet formájában kell megadni,

2) a számok - a bal oldali határ és a b - annak az intervallumnak a jobb határa, amelyen belül egy gyök található,

3) E szám - a gyökér megszerzésének meghatározott pontossága.

Emlékezzünk vissza, hogy az intervallum végén az f(X) függvény különböző előjelekkel rendelkezik. A módszer számítási eljárása az, hogy minden iterációs lépésben kiválasztunk egy c köztes pontot az intervallumon úgy, hogy az legyen az intervallum közepe, azaz c = (a+b)/2. Ekkor az intervallumot ez a pont két egyenlő részre osztja és , amelyek hossza egyenlő (b-a)/2. A kapott két szegmens közül azt választjuk ki, amelynek végén az f(X) függvény ellentétes előjelű értékeket vesz fel. Jelöljük ismét mint . Ezzel az első iteráció véget ér. Ezután ismét kettéosztjuk az új szegmenst, és végrehajtjuk a második és az azt követő iterációkat. A szakasz felezését addig hajtjuk végre, amíg valamelyik K-edik lépésnél az újonnan kapott szegmens kisebb vagy egyenlő lesz, mint az E pontossági érték. A K lépés értéke könnyen kiszámítható a képletből

(b-a)/2 k<=E,

ahol a és b az intervallum bal és jobb határának kezdeti értékei.

A felezési módszer minden folytonos függvényre konvergál, beleértve a nem differenciálható függvényeket is.

1.4. példa.

Használjuk a felező módszert az 1.1. példában elválasztott gyök értékének tisztázására. Tehát legyen f(X)= exp(X) - 10*X, az első gyöknél a=0 és b=0,5. Legyen E=0,00001.

Programozzuk a felezési metódust ehhez a példához ugyanazon a munkalapon, ahol a gyökérszétválasztást végeztük. Az A52 és B52 cellákba be kell írnia a és b számértékeit, a C52 cellába pedig az =(A52+B52)/2 képletet. Ezután a D52 cellába írjuk be az =EXP(A52)-10*A52 képletet, az E52 cellába az =EXP(C52)-10*C52 képletet, az F52 cellába a =D52*E52 képletet, és végül a G52 cellában a =B52- A52 képletet írjuk. Az 52. sorban elkészítettük az első iterációt. A második iterációnál az A53 és B53 cellák értékei az F52 cellában lévő szám előjelétől függenek. Ha F52>0, akkor az A53 értéke egyenlő C52-vel. Ellenkező esetben egyenlőnek kell lennie A52-vel. A B53 cellában fordítva van: ha F52<0, то значение В53 равно С52, иначе В52.

Az IF nevű beépített EXCEL-függvény segít megoldani ezt a nehézséget. Legyen az A53 cella az aktuális cella. A képletsorban a zöld pipa mellett kattintson a képet tartalmazó gombra f(x). Így hívják Funkciómester. A megjelenő párbeszédpanelen válassza ki a mezőt Kategóriák Funkciók kategória Logikus, és a terepen Funkció neve- név IF. A párbeszéd második lépésében töltse ki a három szabad mezőt az alábbiak szerint: a mezőben Logikai_kifejezésírja be az „F52>0”-t (természetesen idézőjelek nélkül!) a mezőbe Érték_ha_igaz adjunk hozzá C52-t, és a mezőben Érték_ha_hamis- A52. Kattintsunk a gombra Befejezés. Ez minden.

Ugyanezt kell tenni a B53 cellával is. Csak Logikai kifejezés az „F52<0”, Érték_ha_igaz C52 lesz, és Érték_ha_hamis illetve B52.

Ezután át kell másolnia a C52:G52 cellablokk képleteit a C53:G53 blokkba. Ezt követően a második iterációt az 53. sorban hajtjuk végre. A következő iterációk eléréséhez elegendő az A53:E53 blokk 53. sorából az A54:E68 blokkba másolni a képleteket. Ezután szokás szerint az E oszlopban kell találni egy sort, ahol D értéke kisebb, mint E. Ekkor ebben a sorban a C oszlopban lévő szám a gyökér hozzávetőleges értéke.

Az A, B és C oszlopokban lévő értékek változásait az elsőtől az utolsó iterációig ábrázolhatja. Ehhez ki kell választani az A52:C68 cellablokkot. További utasításokért lásd az 1.2 példát.

Tisztázzuk az 1.1. példában elválasztott gyök jelentését. Tehát legyen f(X)= exp(X) - 10*X. Keressünk egy gyökeret, amely az intervallumon fekszik. Hagyjuk üresen az A70-es cellát. A B70 cellába az =EXP(A70)-10*A70 képletet írjuk. Válasszon ki egy menüparancsot Szolgáltatás- Paraméter kiválasztása. Megnyílik egy párbeszéd Paraméter kiválasztása, amelyben a terepen Beállítás celláraírd a mezőbe B70-et Jelentéseírjon be 0 (nulla) értéket a mezőbe Egy cella megváltoztatása jelöljük az A70-et. Kattintson az OK gombra, és egy új párbeszédablak jelenik meg a művelet eredményével. Az ablakban A megoldás kiválasztásának állapota megjelenik a talált érték. Ha most az OK gombra kattint, a talált gyökérérték az A70-es cellába, a függvényérték pedig a B70-es cellába kerül.

Ahhoz, hogy az intervallumon egy másik gyöket találjunk, meg kell változtatni a kezdeti közelítést, amely táblázatunkban az A70 cellában található. Írjuk ebbe a cellába az egyik intervallumhatárt, például 4-et, és hajtsuk végre újra a paraméterválasztási eljárást. Az A70 és B70 cellák tartalma megváltozik, ezekben a cellákban jelennek meg a nagyobb gyökér koordinátái.

2. LINEÁRIS ALGEBRAI EGYENLETRENDSZEREK

Általában a lineáris algebrai egyenletrendszert a következőképpen írjuk fel: a 11 x 1 +a 12 x 2 +... +a 1n x n = b 1

a 21 x 1 +a 22 x 2 +... +a 2n x n = b 2

......................

a n1 x n +a n2 x 2 +... +a nn x n = b n

Ennek a rendszernek az együtthatókészletét négyzetmátrixba írjuk A-tól n vonalak és n oszlopok

a 11 a 12 ... a 1n

a 21 a 22 ... a 2n

a n1 a n2 ... a nn

A mátrixszámítás segítségével az eredeti egyenletrendszer a következőképpen írható fel

A*X = B,

Ahol X- ismeretlenek vektoroszlopa dimenzióval n, A IN- szabad kifejezések vektoroszlopa, dimenzió is n.

Ezt a rendszert ún közös, ha van legalább egy megoldása, és bizonyos, ha van egy egyedi megoldása. Ha az összes szabad tag nulla, akkor a rendszer meghívásra kerül homogén.

A rendszer egyedi megoldásának szükséges és elégséges feltétele a DET=0 feltétel, ahol DET a mátrix determinánsa A. A gyakorlatban, amikor számítógépen számolunk, nem mindig lehetséges a DET nullával való pontos egyenlősége. Ha a DET közel nulla, a rendszereket rosszul kondicionáltnak nevezzük. Számítógépen történő megoldásukkor a kezdeti adatok kis hibái a megoldásban jelentős hibákhoz vezethetnek. A DET~0 feltétel szükséges egy rosszul kondicionált rendszerhez, de nem elégséges. Ezért egy rendszer számítógépen történő megoldása során a számítógép korlátozott bitrácsához kapcsolódó hiba becslése szükséges.

Két mennyiség jellemzi a kapott megoldás pontostól való eltérésének mértékét. Hadd Hk- a rendszer valódi megoldása, Xc- számítógépen ilyen vagy olyan módszerrel kapott megoldás, akkor a megoldás hibája:
E = Xk - Xc. A második érték az eltérés, egyenlő R = B - A*Xc. A gyakorlati számításokban a pontosságot a maradékok segítségével ellenőrzik, bár ez nem teljesen helyes.

2.1. Mátrix módszer.

Az EXCEL lehetővé teszi egy lineáris algebrai egyenletrendszer megoldását a mátrix módszerrel, azaz.

X = A -1 *B.

Így a rendszer mátrix módszerrel történő megoldásának algoritmusa a következő számítási eljárások sorozataként ábrázolható:

1) Szerezd meg a mátrixot A -1, a mátrix inverze A;

2) kapjunk megoldást a rendszerre a képlet segítségével Xc = A-1*B;

3) számítsuk ki a szabad tagok új vektorát Nap = A*Xc;

4) számítsa ki a maradékot R = B - Bc;

5) kapjunk megoldást a rendszerre a képlet segítségével dXc = A -1 *R;

6) Hasonlítsa össze a vektor összes komponensét dXc modulo adott E hibával: ha mindegyik kisebb mint E, akkor fejezze be a számításokat, ellenkező esetben ismételje meg a számításokat a 2. lépéstől, ahol Xc = Xc + dXc.

Nézzük meg egy példa segítségével a rendszer EXCEL segítségével történő megoldásának mátrixos módszerét.

2.1. példa.

Egyenletrendszer megoldása

20,9 x 1 + 1,2 x 2 + 2,1 x 3 + 0,9 x 4 = 21,7

1,2x1 +21,2x2 + 1,5x3 + 2,5x4 = 27,46

2,1x1 + 1,5x2 +19,8x3 + 1,3x4 = 28,76

0,9x1 + 2,5x2 + 1,3x3 +32,1x4 = 49,72

Az EXCEL a következő beépített függvényekkel rendelkezik, amelyek mátrixszámításokat valósítanak meg:

a) MOBR - mátrix inverzió,

b) MULTIPLICITY - két mátrix szorzása,

c) MOPRED - a mátrix determinánsának kiszámítása.

Ezen függvények használatakor fontos, hogy a munkalapon a forrás- és munkamátrixoknak, oszlopvektoroknak megfelelő cellablokkokat helyesen és tömören rendezzük el. Nyissunk meg egy új munkalapot a választott parancsikonra kattintva. Vegyük a mátrix alá A sejtblokk A3:D6. Az érthetőség kedvéért zárjuk fekete keretbe. Ehhez válassza ki az A3:D6 blokkot, és adja ki a menüparancsot Formátum – Cellákés a megnyíló párbeszédpanelen válassza ki a lapot Keret. Megnyílik egy új párbeszédablak, amelyben rákattintunk a mezőre Keret-Körvonalés válassza ki a mezőben Keret-stílus legvastagabb vonalszélesség. Erősítsük meg döntésünket az OK gombra kattintva. Most válassza ki az A8:D11 blokkot a mátrixnak A -1és fekete keretbe is zárja, a mátrixblokkhoz hasonló műveleteket végrehajtva A. Ezután válasszon cellablokkokat vektoroszlopokhoz (fekete kerettel körbeírva): F8:F11 blokk - vektorhoz IN, blokk H8:H11 - a vektor alatt Xs A -1 *B, blokk H3:H6 - a vektor alatt Nap szorzás eredménye A*Xc, és az egyértelműség kedvéért kiválasztunk egy további F3:F6 blokkot, ahová a vektor komponenseit másoljuk Xs blokkból H8:H11. Végül írja be a * szorzójelet az E4 és E9 cellákba, és az egyenlőségjelet = a G4 és G9 cellákba, majd az E és G oszlopokat egymás után kiemelve adja ki a menüparancsot Formátum - Oszlop - Szélesség beállítása. Így elkészítettünk egy feladatlapot a probléma megoldásához.

Adjuk meg a kezdő adatokat: mátrixszámokat A az A3:D6 blokk celláiba, és a számok a szabad tagok vektorai IN- az F8:F11 blokk celláiban.

Kezdjük az algoritmus végrehajtását a mátrix megfordításával A. Ehhez válassza ki az A8:D11 blokkot, ahová a művelet eredményét kell elhelyezni. Ez a blokk feketévé válik, az A8-as cella kivételével. Kattintsunk a gombra f x a panelen Standard hívás kezdeményezésével Funkciómesterek. Megnyílik egy párbeszédpanel, amelyben a mezőből Funkció kategória válasszon egy sort Mat. és trigonometria, és a mezőről Funkció neve- MOBR vonal. Menjünk tovább a párbeszédpanel második lépésére a gombra kattintva lépés>. Itt a mezőn Sor be kell írnia az A3:D6 parancsot a billentyűzetről, ami megfelel a mátrix által elfoglalt cellablokknak A. A gombra kattintva Befejezés, láthatja, hogy az A8:D11 blokkban csak az A8 cella van kitöltve. Az EXCEL-nek további két lépésre van szüksége a hívási művelet befejezéséhez. Először is aktiválnia kell a képletsort, ha rákattint (bárhol a sorban!) - az egérkurzor I formát ölt. A műveletek helyességét a képlettől balra megjelenő négy gomb ellenőrzi. képletsor, köztük egy zöld pipa. Ezt követően nyomjuk meg a billentyűzeten a „Ctrl” billentyűt, majd elengedés nélkül a „Shift” billentyűt, elengedés nélkül pedig az „Enter” billentyűt, pl. Ennek eredményeként mindhárom gombot egyszerre kell megnyomni! Most a teljes A8:D11 blokk tele lesz számokkal, és kiválaszthatja a H8:H11 blokkot a szorzás megkezdéséhez. A -1 *B.

Miután kiválasztotta ezt a blokkot, hívja újra Funkcióvarázslóés a terepen Funkció neve- válassza a MULTIPLE funkciót. A gombra kattintva lépés>, térjünk át a párbeszéd második lépésére, ahol a terepen Tömb1írja be az A8:D11 címet, és a mezőbe Tömb2- cím: F8:F11. Kattintsunk a gombra Befejezésés azt találjuk, hogy a H8:H11 blokkban csak a H8 cella van kitöltve. Aktiválja a képletsort (egy zöld pipa jelenjen meg!), és a fent leírt módszerrel nyomjon le egyszerre három billentyűt: „Ctrl”-„Shift”-„Enter”. A szorzás eredménye a H8:H11 blokkban jelenik meg.

A kapott rendszermegoldás pontosságának ellenőrzésére elvégezzük a számítási műveletet Вс=А*Хс. Ebből a célból a H8:H11 blokkból csak a cellák számértékeit (és ne képleteit!) másoljuk át az F3:F6 cellákba. Ezt a következőképpen kell megtenni. Válasszuk ki a H8:H11 blokkot. Adjunk ki egy menüparancsot Szerkesztés- Másolat. Válassza ki az F3:F6 blokkot. Adjunk ki egy menüparancsot Szerkesztés- Speciális betét. Megnyílik egy párbeszédpanel, amelyben a mezőben Beszúrás módot kell választani Értékek. Erősítsük meg döntésünket az OK gombra kattintva.

A művelet után az A3:D6 és az F3:F6 blokkok számokkal vannak feltöltve. Elkezdheti a mátrixszorzást A vektorhoz Xs. Ehhez ki kell választania a H3:H6 blokkot, hívja Funkciómesterés ugyanúgy jár el, mint a számításnál Xc=A -1 *B, kap Nap. Amint az a táblázatból látható, a vektorok számértékei INÉs Nap egybeesik, ami a számítások jó pontosságát jelzi, i.e. példánkban a maradék nulla.

Erősítsük meg a mátrix jó kondicionálását A determinánsának kiszámításával. Ehhez aktiválja a D13 cellát. Használatával Funkciómesterek Nevezzük meg a MOPRED függvényt. A tömb mezőbe írja be az A3:D6 blokk címét. A gombra kattintva Befejezés, a D13 cellában megkapjuk a mátrix determináns számértékét A. Amint látható, lényegesen nagyobb, mint nulla, ami azt jelzi, hogy a mátrix jól kondicionált.

2.2. A közelítő számítások módszere.

A lineáris algebrai egyenletrendszerek megoldásának egyik legelterjedtebb iteratív módszere, amelyet az egyszerűség és a könnyű programozás jellemez, a közelítő számítások módszere vagy a Jacobi-módszer.

Tegyük fel, hogy meg kell oldanunk a rendszert

a 11 x 1 +a 12 x 2 +a 13 x 3 = b 1

a 21 x 1 +a 22 x 2 +a 23 x 3 = b 2

a 31 x 1 +a 32 x 2 +a 33 x 3 = b 3

Tegyük fel, hogy az a 11, a 22, a 33 átlós elemek nem nullák. Ellenkező esetben átrendezheti az egyenleteket. Fejezzük ki a változókat az első, a második és a harmadik egyenletből. Majd

x 1 = / a 11

x 2 = / a 22

x 3 = / a 33

Állítsuk be az ismeretlenek kezdeti közelítését

Behelyettesítve őket a transzformált rendszer jobb oldalába, új első közelítést kapunk

Adott a rendszer n algebrai egyenletek -val n ismeretlen:

Ez a rendszer mátrix formában írható fel:
,

;;.

Ahol A - négyzetes együttható mátrix, X - ismeretlenek oszlopvektora, B - szabad kifejezések oszlopvektora.

A lineáris egyenletrendszerek megoldására szolgáló numerikus módszerek direkt és iteratív megoldásokra oszthatók. Az előbbiek véges relációkat használnak az ismeretlenek kiszámításához. Példa erre a Gauss-módszer. A másodikok egymást követő közelítéseken alapulnak. Ilyen például az egyszerű iterációs módszer és a Seidel módszer.

1. Gauss módszer

A módszer a rendszermátrix háromszög alakúra redukálásán alapul. Ezt úgy érik el, hogy az ismeretleneket szekvenciálisan kiiktatják a rendszeregyenletekből. Először is, az első egyenlet segítségével kiküszöböljük x 1 minden további egyenletből. Ezután a második egyenlet segítségével kiküszöböljük x 2 a későbbiekből stb. Ezt a folyamatot a Gauss-módszer előrehúzásának nevezik, és az utolsó bal oldaláig tart n az egyenletből csak egy ismeretlen tag marad meg x n. Az előremozgás eredményeként a rendszer a következő formát ölti:

(2)

A Gauss-módszer fordítottja az ismeretlen ismeretlenek szekvenciális kiszámításából indul ki. x nés véget ér x 1 .

1. Egyszerű iterációs módszer és Seidel módszer

A lineáris egyenletrendszerek iteratív módszerekkel történő megoldása a következőkre vezethető vissza. Meg van adva az ismeretlenek vektorának kezdeti közelítése, amely általában a nulla vektor:

.

Ezután egy ciklikus számítási folyamatot szervezünk, amelynek minden ciklusa egy iterációt jelent. Minden iteráció eredményeképpen az ismeretlenek vektorának új értéket kapunk. Az iterációs folyamat véget ér, ha mindegyiknél én komponense az ismeretlenek vektorának, akkor a feltétel teljesül

(3)

Ahol k- iterációs szám, - meghatározott pontossággal.

Az iteratív módszerek hátránya a szigorú konvergencia feltétel. A módszer konvergéséhez szükséges és elegendő, hogy a mátrixban A az összes átlós elem abszolút értéke nagyobb volt, mint a megfelelő sorban lévő összes többi elem moduljának összege:

(4)

Ha a konvergencia feltétele teljesül, akkor lehetőség van iteratív folyamat megszervezésére az (1) rendszer redukált formában történő írásával. Ebben az esetben a főátlón lévő kifejezések normalizálódnak, és az egyenlőségjeltől balra maradnak, a többi pedig a jobb oldalra kerül. Az egyszerű iterációs módszerhez a redukált egyenletrendszer alakja a következő:

(5)

A különbség a Seidel-módszer és az egyszerű iterációs módszer között az, hogy az ismeretlenek vektorának következő közelítésének kiszámításakor az ugyanabban az iterációs lépésben már finomított értékeket használjuk. Ez biztosítja a Seidel-módszer gyorsabb konvergenciáját. Az adott egyenletrendszer a következőképpen alakul:

(6)

3.4. Megvalósítás Excelben

Példaként tekintsük az egyenletrendszert:

Ez a rendszer teljesíti a konvergencia feltételt, és direkt és iteratív módszerekkel is megoldható. A műveletek sorrendje (7. ábra):

Töltse ki az 1. sorban a „Numerikus módszerek lineáris egyenletrendszerek megoldására” címsort.

A D3:H6 mezőbe írja be a kiindulási adatokat az ábrán látható módon.

Írja be a „Gauss-módszer” címsort (középre igazítás) az F8-as cellába.

Másolja az E4:H6 forrásadatokat a B10:E12 területre.

Ez a kezdeti adat a Gauss-módszer előrefutásához. Jelöljük a megfelelő sorokat A1-nek, A2-nek és A3-nak.

Készítse elő az első menethez szükséges helyet a B1, B2 és B3 sorok nevének bejelölésével a G10:G12 területen.

Írja be a „=B10/$B$10” képletet a H10 cellába.

Másolja ezt a képletet az I10:K10 cellákba. Ez 11-szeres normalizálás.

Írja be a „=B11-H10*$B$11” képletet a H11 cellába.

Másolja ezt a képletet az I11:K11 cellákba.

Írja be a „=B12-H10*$B$12” képletet a H12 cellába.

Másolja ezt a képletet az I12:K12 cellákba.

Készítse elő a helyet a második menethez az A14:A16 terület megjelölésével a C1, C2 és C3 sorok nevével.

Írja be a „=H10” képletet a B14 cellába. Másolja ezt a képletet a C14:E14 cellákba.

15. Írja be a „=B15” képletet a H15 cellába. Másolja ezt a képletet az I15:K15 cellákba.

16. Írja be a „=B16/$D$16” képletet a H16 cellába. Másolja ezt a képletet az I16:K16 cellákba.

17. Készítsen helyet a Gauss-módszer fordítottja számára úgy, hogy beírja a megfelelő „x3=”, „x2=” és „x1=” szövegeket a B18, E18 és H18 cellákba.

18. Írja be a „=K16” képletet a C18 cellába. Adjuk meg a változó értékét X 3.

19. Írja be a „=K15-J15*K16” képletet az F18 cellába. Adjuk meg a változó értékét X 2.

20. Írja be a „=K10-I10*F18-J10*C18” képletet az I18-as cellába. Adjuk meg a változó értékét X 1.

21. Írja be az „Egyszerű iterációs módszer” címsort (középre igazítás) az F21-es cellába.

22. Írja be az „e=” szöveget a J21-es cellába (jobbra igazítva).

23. Írja be az e (0,0001) pontossági értéket a K21 cellába.

24. Az A23:A25 területen tüntesse fel a változók nevét!

25. A B23:B25 területen állítsa be a változók kezdeti értékeit (nullák).

26. Írja be a „=($H$4-$F$4*B24-$G$4*B25)/$E$4” képletet a C23 cellába. Adjuk meg a változó értékét X 1 az első iterációban.

27. Írja be a „=($H$5-$E$5*B23-$G$5*B25)/$F$5” képletet a C24 cellába. Adjuk meg a változó értékét X 2 az első iterációban.

28. Írja be a „=($H$6-$E$6*B23-$F$6*B24)/$G$6” képletet a C25 cellába. Adjuk meg a változó értékét X 3 az első iterációban.

29. Írja be a C26 cellába a következő képletet: „=IF(АВS(С23-В23)>$К$21;" "; IF(АВS(С24-В24)>$К$21;" ";IF(АВS(С25-В25) > $К$21;" "; ""roots")))".

30. Jelölje ki a C23:C26 tartományt és másolja át a K oszlopba húzási technikával. Amikor a „roots” üzenet megjelenik a 26. sorban, a megfelelő oszlop a változók hozzávetőleges értékeit tartalmazza. X 1,x 2, x 3, amelyek adott pontosságú egyenletrendszer megoldását jelentik.

31. Az A27:K42 területen készítsen diagramot, amely bemutatja a változók értékeinek közelítésének folyamatát X 1,X 2,x 3 a rendszer megoldásához. A diagram a „Graph” módban készül, ahol az iterációs szám az abszcissza tengely mentén van ábrázolva.

32. Írja be a „Seidel-módszer” címsort (középre igazítás) az F43-as cellába.

33. Írja be az „e=” szöveget (jobbra igazítva) a J43 cellába.

34. Írja be az e(0,0001) pontossági értéket a K43 cellába.

35. Az A45:A47 területen tüntesse fel a változók nevét!

36. A B45:B47 területen állítsa be a változók kezdeti értékeit (nullák).

37. Írja be a „=($H$4-$F$4*B46-$G$4*B47)/$E$4” képletet a C45 cellába. Adjuk meg a változó értékét X 1 az első iterációban.

38. Írja be a „=($H$5-$E$5*C45-$G$5*B47)/$F$5” képletet a C46 cellába. Adjuk meg a változó értékét X 2 az első iterációban.

39. Írja be a „=($H$6-$E$6*C45-$F$6*C46)/$G$6” képletet a C47 cellába. Adjuk meg a változó értékét x 3, az első iterációnál.

40. Írja be a C48 cellába a következő képletet: "=IF(AB5(C45-B45)>$К$43;" "; IF(АВS(С46-В46)>$К$43;" ";IF(АВS(С47-В47) > $43 K$;" ";"roots")))".

41. Jelölje ki a C45:C48 tartományt és másolja át a K oszlopba húzási technikával. Amikor a „roots” üzenet megjelenik a 26. sorban, a megfelelő oszlop a változók hozzávetőleges értékeit tartalmazza. X 1,X 2,x 3, amelyek adott pontosságú egyenletrendszer megoldásai. Látható, hogy a Seidel módszer gyorsabban konvergál, mint az egyszerű iterációs módszer, vagyis itt kevesebb iterációval érjük el a megadott pontosságot.

42. Az A49:K62 területen készítsen diagramot, amely bemutatja az x1, x2, x3 változók értékeinek megközelítését a rendszer megoldásához. A diagram a „Graph” módban készül, ahol az iterációs szám az abszcissza tengely mentén van ábrázolva.

Az Excel számos eszközzel rendelkezik a különböző típusú egyenletek különböző módszerekkel történő megoldására.

Nézzünk meg néhány megoldást példák segítségével.

Egyenletek megoldása Excel paraméterek kiválasztásával

A Paraméterválasztó eszközt olyan helyzetben használják, amikor az eredmény ismert, de az argumentumok ismeretlenek. Az Excel addig módosítja az értékeket, amíg a számítás meg nem adja a kívánt összeget.

A parancs elérési útja: "Adatok" - "Munka adatokkal" - "Mi lenne, ha elemzés" - "Paraméter kiválasztása".

Nézzük meg az x 2 + 3x + 2 = 0 másodfokú egyenlet megoldásának példáját. A gyökér megtalálásának eljárása Excel segítségével:

A program ciklikus folyamatot használ a paraméter kiválasztásához. Az iterációk és a hibák számának módosításához lépjen az Excel beállításaihoz. A „Képletek” lapon állítsa be az iterációk maximális számát és a relatív hibát. Jelölje be az „Iteratív számítások engedélyezése” jelölőnégyzetet.



Egyenletrendszer megoldása mátrix módszerrel Excelben

Az egyenletrendszer adott:

Az egyenletek gyökereit megkapjuk.

Egyenletrendszer megoldása Cramer módszerrel Excelben

Vegyük az előző példából az egyenletrendszert:

Ezek Cramer módszerével történő megoldásához kiszámítjuk azon mátrixok determinánsait, amelyeket úgy kaptunk, hogy az A mátrixban egy oszlopot helyettesítünk a B oszlopmátrixszal.

A determinánsok kiszámításához a MOPRED függvényt használjuk. Az argumentum egy tartomány a megfelelő mátrixszal.

Számítsuk ki az A mátrix determinánsát is (tömb - A mátrix tartománya).

A rendszer determinánsa nagyobb, mint 0 – a megoldás a Cramer-képlet (D x / |A|) segítségével kereshető.

X 1 kiszámításához: =U2/$U$1, ahol U2 – D1. Az X 2 kiszámításához: =U3/$U$1. Stb. Nézzük az egyenletek gyökereit:

Egyenletrendszerek megoldása a Gauss-módszerrel Excelben

Vegyük például a legegyszerűbb egyenletrendszert:

3a + 2b - 5c = -1
2a – b – 3c = 13
a + 2b – c = 9

Az együtthatókat az A mátrixba írjuk. Szabad tagok - a B mátrixba.

Az egyértelműség kedvéért kitöltéssel kiemeljük az ingyenes feltételeket. Ha az A mátrix első cellája 0-t tartalmaz, akkor a sorokat fel kell cserélni, hogy itt 0-tól eltérő érték jelenjen meg.

Példák egyenletek megoldására az iterációs módszerrel Excelben

A munkafüzetben a számításokat a következőképpen kell beállítani:

Ez az „Excel-beállítások” „Képletek” lapján történik. Keressük meg az x – x 3 + 1 = 0 (a = 1, b = 2) egyenlet gyökét iterációval ciklikus hivatkozások segítségével. Képlet:

Х n+1 = X n – F (X n) / M, n = 0, 1, 2, … .

M – a modulo derivált maximális értéke. Az M megtalálásához végezzük el a számításokat:

f’ (1) = -2 * f’ (2) = -11.

A kapott érték kisebb, mint 0. Ezért a függvénynek ellentétes előjele lesz: f (x) = -x + x 3 – 1. M = 11.

Az A3 cellába írjuk be az értéket: a = 1. Pontosság – három tizedesjegy. A szomszédos cellában (B3) lévő x aktuális értékének kiszámításához írja be a következő képletet: =IF(B3=0;A3;B3-(-B3+POWER(B3;3)-1/11)).

A C3 cellában szabályozzuk f (x): a =B3-POWER(B3,3)+1 képlet segítségével.

Az egyenlet gyöke 1,179. Írjuk be a 2 értéket az A3 cellába. Ugyanazt az eredményt kapjuk:

Egy adott intervallumon csak egy gyök található.

Példa 3.1 . Keress megoldást a (3.1) lineáris algebrai egyenletrendszerre Jacobi módszerrel!

Adott rendszerre iteratív módszerek használhatók, mert feltétel teljesül „átlós együtthatók túlsúlya”, amely biztosítja e módszerek konvergenciáját.

A Jacobi-módszer számítási sémája a (3.1) ábrán látható.

Adja meg a rendszert (3.1). normál formára:

, (3.2)

vagy mátrix formában

, (3.3)

3.1. ábra.

Egy adott pontosság eléréséhez szükséges iterációk számának meghatározása e,és a rendszer közelítő megoldása hasznos az oszlopban N telepíteni Feltételes formátum. Ennek a formázásnak az eredménye a 3.1. ábrán látható. Oszlopcellák N, amelynek értékei kielégítik a (3.4) feltételt, árnyékoljuk.

(3.4)

Az eredményeket elemezve a negyedik iterációt az eredeti rendszer közelítő megoldásának vesszük, adott e=0,1 pontossággal,

azok. x 1=10216; x 2= 2,0225, x 3= 0,9912

Az érték megváltoztatása e egy cellában H5 az eredeti rendszer új, közelítő megoldását új pontossággal kaphatjuk meg.

Elemezze az iteratív folyamat konvergenciáját úgy, hogy ábrázolja az SLAE megoldás egyes összetevőiben bekövetkező változásokat az iterációs számtól függően.

Ehhez válasszon ki egy cellablokkot A10:D20és használata Diagram varázsló, az iteratív folyamat konvergenciáját tükröző gráfokat szerkeszt, 3.2. ábra.

Egy lineáris algebrai egyenletrendszert hasonló módon old meg a Seidel-módszer.

4. sz. laboratóriumi munka

Téma. Numerikus módszerek peremfeltételes lineáris közönséges differenciálegyenletek megoldására. Véges különbség módszer

Gyakorlat. Oldja meg a határérték-feladatot véges differencia módszerrel úgy, hogy két közelítést (két iterációt) készítünk egy h lépéssel és egy h/2 lépéssel.

Elemezze a kapott eredményeket. A feladatokra vonatkozó lehetőségeket a 4. számú melléklet tartalmazza.

Munkarend

1. Építsd meg kézzel a határérték-probléma véges különbség közelítése (véges különbség SLAE) lépéssel h , adott lehetőség.

2. A véges különbség módszerével formázzuk be Excel lépés lineáris algebrai véges-differencia egyenletek rendszere h szakasz lebontása . Írd fel ezt a SLAE-t a könyv munkalapjára Excel. A tervezési diagram a 4.1.

3. Oldja meg a kapott SLAE-t a sweep módszerrel.

4. Ellenőrizze az SLAE megoldás helyességét a bővítmény segítségével Excel Keressen megoldást.

5. Csökkentse a rács lépését 2-szer, és oldja meg újra a problémát. Mutassa be az eredményeket grafikus formában.

6. Hasonlítsa össze az eredményeket. Vonjon le következtetést a fiók folytatásának vagy megszüntetésének szükségességéről.

Határérték-probléma megoldása Microsoft Excel táblázatok segítségével.

4.1. példa. Keressen megoldást a határérték-problémára a véges különbség módszerével , y(1)=1, y’ (2)=0,5 a szegmensen xÎ lépéssel h=0,2 és lépéssel h=0,1. Hasonlítsa össze a kapott eredményeket, és vonjon le következtetést a fiók folytatásának vagy megszüntetésének szükségességéről.

A h=0,2 lépés tervezési diagramja a 4.1. ábrán látható.

A kapott megoldás (rácsfüggvény) Y {1.000, 1.245, 1.474, 1.673, 1.829, 1.930}, X Az L és B oszlopban található (1; 1,2; 1,4; 1,6; 1,8;2) az eredeti feladat első iterációjaként (első közelítéseként) tekinthető.

Megtalálni második iteráció készítse el a rácsot kétszer vastagabbá (n=10, h=0,1 lépés), és ismételje meg a fenti algoritmust.

Ez megtehető ugyanazon vagy a könyv másik lapján. Excel. A megoldást (második közelítés) a 4.2. ábra mutatja.

Hasonlítsa össze a kapott közelítő megoldásokat! Az áttekinthetőség kedvéért e két közelítés (két rácsfüggvény) grafikonját ábrázolhatja. 4.3. ábra.

Egy határérték-feladat közelítő megoldásainak grafikonjainak elkészítési eljárása

1. Készítsen gráfot a probléma megoldására egy h=0,2 (n=5) lépéses differencia rácsra!

2. Aktiválja a már felépített diagramot, és válassza ki a parancsot menü Diagram\Adatok hozzáadása

3. Az ablakban Új adatok adjon meg részleteket x i, y i különbségrácshoz h/2 lépéssel (n=10).

4. Az ablakban Speciális betét jelölje be a négyzeteket:

Ø új sorok,

A bemutatott adatokból látható, hogy a határérték-probléma két közelítő megoldása (két rácsfüggvény) legfeljebb 5%-kal tér el egymástól. Ezért a második iterációt az eredeti probléma közelítő megoldásának tekintjük, azaz.

Y{1, 1.124, 1.246, 1.364, 1.478, 1.584, 1.683, 1.772, 1.849, 1.914, 1.964}

5. sz. laboratóriumi munka

Hadd emlékeztessem Önt arra, hogy egy kör alakú hivatkozás jelenik meg, ha egy képletet beírnak egy olyan Excel cellába, amely magára a cellára mutató hivatkozást tartalmaz (közvetlenül vagy más hivatkozások láncán keresztül). Például (1. ábra) a C2 cellában van egy képlet, amely magára a C2 cellára vonatkozik.

De!... A körkörös utalás nem mindig katasztrófa. A körkörös hivatkozás használható az egyenletek iteratív megoldására. Először is hagynia kell, hogy az Excel elvégezze a számításokat, még akkor is, ha van körkörös hivatkozás. Normál módban az Excel, ha körkörös hivatkozást észlel, hibaüzenetet jelenít meg, és kéri a javítást. Normál módban az Excel nem tud számításokat végezni, mert a körkörös hivatkozás végtelen számítási hurkot hoz létre. Eltávolíthatja a ciklikus referenciát, vagy engedélyezheti a számításokat a ciklikus referencia képletével, de korlátozva a ciklus ismétlődéseinek számát. A második lehetőség megvalósításához kattintson az „Iroda” gombra (a bal felső sarokban), majd az „Excel-beállítások”-ra (2. ábra).

Töltse le a jegyzetet formátumban, a példákat formátumban

Rizs. 2. Excel-beállítások

A megnyíló „Excel-beállítások” ablakban lépjen a Képletek fülre, és jelölje be az „Iteratív számítások engedélyezése” pontot (3. ábra). Ne feledje, hogy ez a lehetőség a teljes Excel alkalmazásban engedélyezve van (nem egyetlen fájl esetében), és mindaddig érvényben marad, amíg le nem tiltja.

Rizs. 3. Engedélyezze az iteratív számításokat

Ugyanazon a lapon kiválaszthatja, hogy a számítások hogyan történjenek: automatikusan vagy manuálisan. Automatikus számításokkal az Excel azonnal kiszámolja a végeredményt manuális számításokkal, minden iteráció eredményét megfigyelheti (egyszerûen az F9 megnyomásával, minden új számítási ciklust elindítva).

Oldjuk meg a harmadfokú egyenletet: x 3 – 4x 2 – 4x + 5 = 0 (4. ábra). Ennek az egyenletnek (és bármely más, teljesen tetszőleges típusú egyenletnek) megoldásához csak egy Excel cellára van szükség.

Rizs. 4. Az f(x) függvény grafikonja

Az egyenlet megoldásához szükségünk van egy ismétlődő képletre (vagyis olyan képletre, amely a sorozat minden tagját egy vagy több korábbi taggal fejezi ki):

(1) x = x – f(x)/f’(x), ahol

x – változó;

f(x) egy függvény, amely definiálja azt az egyenletet, amelynek gyökereit keressük; f(x) = x 3 – 4x 2 – 4x + 5

f’(x) – f(x) függvényünk származéka; f’(x) = 3x2 – 8x – 4; a fő elemi függvények származékai tekinthetők meg.

Ha érdekli, honnan származik az (1) képlet, akkor elolvashatja pl.

A végső ismétlődő képlet így néz ki:

(2) x = x – (x 3 – 4 x 2 – 4 x + 5)/(3 x 2 – 8 x – 4)

Jelöljünk ki egy tetszőleges cellát az Excel munkalapon (5. ábra; példánkban ez a G19 cella), adjunk neki nevet. X, és írja be a képletet:

(3) =x-(x^3-4*x^2-4x+5)/(3*x^2-8*x-4)

Talán helyette X használja a cella címét... de elfogadja, hogy a név X, vonzóbbnak tűnik; A következő képletet írtam be a G20 cellába:

(4) =G20-(G20^3-4*G20^2-4*G20+5)/(3*G20^2-8*G20-4)

Rizs. 5. Ismétlődő képlet: (a) egy elnevezett cellához; (b) normál cellacímhez

Amint beírjuk a képletet és megnyomjuk az Entert, a válasz azonnal megjelenik a cellában - az érték 0,77. Ez az érték megfelel az egyenlet egyik gyökének, nevezetesen a másodiknak (lásd az f(x) függvény grafikonját a 4. ábrán). Mivel kezdeti tippet nem adtak meg, az iteratív számítási folyamat a cellában tárolt alapértelmezett értékkel kezdődött Xés egyenlő nullával. Hogyan kapjuk meg az egyenlet fennmaradó gyökét?

A kiindulási érték megváltoztatásához, amelytől az ismétlődő képlet az iterációit kezdi, javasoljuk az IF függvény használatát:

(5) =IF(x=0;-5;x-(x^3-4*x^2-4*x+5)/(3*x^2-8*x-4))

Itt a „-5” érték az ismétlődő képlet kezdeti értéke. Megváltoztatásával elérheti az egyenlet összes gyökerét.

Előző cikk: Mekkora a fénysebesség Következő cikk: Harmonikus rezgések Az oszcillációs frekvencia fizikai képlete