Matematické Fórum

iv21 napsal(a):

nevíte si náhodou pls někdo rady s tímto příkladem?

jedná se o nelineární regresi a máme to řešit v excelu pomocí řešitele. Jsou dány hodnoty x, y a funkce, máme zjistit hodnoty parametru A, a, b.



x    0    0,3      0,6     0,9     1,2      1,5        1,8       2,1       2,4       2,7       3
y    0    1,56   2,08    1,73    1,14    0,26    -0,58    -0,72    -0,9    -0,64    0,05

diky moc

NIC NENI TREBA LOGARITMOVAT!

Řešení je jednoduché.

Toto jsou experimentální data:
x    0    0,3      0,6     0,9     1,2      1,5        1,8       2,1       2,4       2,7       3
y    0    1,56   2,08    1,73    1,14    0,26    -0,58    -0,72    -0,9    -0,64    0,05

A toto je matematická rovnice, která nám v podstatě popisuje matematicko-fyzikální model pro experimentání data:



jinak řečeno, tato matematická rovnice nám popisuje závislost výše uvedených experimentálních dat.

Tak a teď jak na excel:
Do buňky A1 napíšeme "x" (bez těch uvozovek) - to je naše nezávisle proměnná. Buňka B1 bude nadepsaná jako "y" - a to je naše závisle proměnná. Buňku C1 nadepíšeme "Model". Buňka D1 bude obsahovat "A"; buňka E1 potom "a", a nakonec buňka F1 bude obsahovat písmeno "b". Všechny písmena bez uvozovek, slouží pouze pro pojmenování příslušných sloupců!
Do sloupce A pod buňku (A1), která již obsahuje písmeno x, vložíme experimentální data nezávisle proměnné x. To znamená, že do buňky A2 vložíme 0; A3 obsahuje 0,3; A4 pak 0,6; atd....
Úplně stejně vložíme data závisle proměnné y. Buňka B2 obsahuje 0; B3 pak 1,56; B4 pak hodnotu 2,08; atd.

Sloupec C zatím necháme prázdný, bude obsahovat pouze nápis Model v buňce C1!

Do buňky D2 prozatím vložíme třeba číslo "1" a to samé číslo také napíšeme do buňky E2 a F2

Do buňky G1 napíšeme "Kritérium" a buňku G2 prozatím necháme prázdnou.

Nyní se vrátíme ke sloupci C a do buňky C2 napíšeme matematický vzorec, který popisuje závislost experimentálních dat, tzn. buňka C2 bude obsahovat výraz

=$D$2*SIN($E$2*A2)*EXP(-$F$2*A2)

Máme li takto vložený vzorec v buňce C2 tak jej odentrujeme a výsledkem by měla být "0". Teď tento vzorec "roztáhneme" do celého sloupce C až do buňky C12. (když je buňka C2 obsahující vzorec označena, tzn. je orámována černým rámečkem, který obsahuje v pravém dolním rohu čtvereček, tak na ten čtvereček najedeme kurzorem myši a kurzor se změní na křížek. Jakmile je z kurzoru křížek, stiskneme levé tlačítko myši a se stále stisknutím levým tlačítkem myši tu buňku C2 roztáhneme dolů do dalších buněk ve sloupci C, až po buňku C12 včetně. Tím se nám rozkopíruje vzorec z buňky C2 do ostatních buněk ve sloupci C a absolutní odkazy nám zajistí, že se vzorce z dalších buněk budou pořád odkazovat na parametry rovnice A, a, b v buňkách D2; E2; F2. Kdyby tam ty absolutní odkazy nebyly, tzn. byl by tam pouze relativní odkaz (bez znaků $ -

=D2*SIN(E2*A2)*EXP(-F2*A2)

, posouvali by se odkazy na hodnoty parametrů A, a, b zároveň s řádky, tak jako relativní odkaz na buňku B2 a počítali bychom nesmysly!!!!!!

Tak když máme roztažené vzorce i do dalších buněk sloupce C, tak se vrátíme k doposud prázdné buňce G2. Zde vložíme funkci =SUMXMY2 a pole_x bude obsahovat buňky sloupce B, konkrétně buňky od B2 až po B12 a pole_y buňky sloupce C, tedy sloupce které obsahují náš matematický model, konkrétně C2 až C12. Celý zápis tedy bude vypadat takto:

=SUMXMY2(B2:B12;C2:C12)

Toto kritérium je pro Řešitel velice důležité, počítá součet čtverců dvou odpovídajících matic! Díky němu je schopen dopočítat správné parametry aby nám model seděl na experimentální data!

Tak a nyní máme vše připraveno k použití Řešitele!

Excel 2003:
Kliknem na Nástroje/Řešitel a otevře se nám dialogové okno. Do políčka Nastavit buňku nastavíme odkaz na buňku obsahující funkci

=SUMXMY2(B2:B12;C2:C12)

, čili Kriterium. Z výběru Rovno: Max, Min, Hodnota zatrhneme Min. Měněné buňky jsou buňky parametrů modelu, tzn. buňky D2,E2,F2. Rozklikneme Možnosti  a zadáme například tyto hodnoty: Maximální čas: 1000; Počet Iterací: 1000; Přesnost: 0,0000000001; Tolerance: 0,00000001; Konvergence: 0,0000000001;  Extrapolace: Kvadratická; Derivace: Sdružená; Metoda: Sdružená, jinak nic jiného v nabídce Možnosti není zaškrtnuté! Odentrujeme a klikeneme Řešit a je hotovo! Řešitel nám našel parametry A, a, b!

Nemáte zač. Rád jsem pomohl, snad ten postup někomu do budoucna pomůže.

Jen ještě jedna věc. Někdy si řešitel nedokáže poradit se zadanou matematicko-fyzikální rovnicí popisující závislost y na x. Proto je vhodné si zobrazit pomocí grafu experimentální body závislosti y na x a nechat si je proložit "vymodelovanou" závislostí pomocí řešitele. Je to dobrá zpětná kontrola vypočítaných parametrů Řešitelem.
Tehdy parametry modelu prostě použijeme pro výpočet hustější sítě dat. Například v případě že máme pouze deset experimentálních hodnot rozdělených po  = 1, je vhodné modelované data pak vypočítat pro = 0,1 (0,01 a pod.), nebo dle potřeby. Získá se tak mnohem jemnější, "zakulacenější", křivka, která by v případě nalezení správného řešení měla kopírovat závislost experimentálních dat.
Jak jsem ale psal, Řešitel není dokonalý nástroj a parametry někdy dopočítává "špatně". T. j. již na první pohled je při grafickém znázornění závislosti experimentálních a modelových dat vidět, že "model" prostě nekoresponduje s experimentálními daty, lidově řečeno prostě "nesedí". V takovém případě máme dvě možnosti. První je, že je matematická rovnice popisující danou závislost y na x špatně zvolená a je vhodné nalézti jinou, která bude popisovat závislost mnohem lépe, avšak někdy je matematická rovnice správná, ale Řešitel prostě z nějakého důvodu nedokáže v nekonečném množství řešení nalézti to "správné". Tehdy je potřeba Řešiteli trochu pomoci a to tak, že se za "nastřelené" hodnoty parametrů modelu (tzn. ty které on pak změní) odhadnou co nejblíže správnému řešení, což obvykle problém vyřeší. Stejně tak, je někdy potřeba nastavit v možnostech Řešitele okrajové podmínky a tím mu v podstatě zúžit oblasti, ve kterých má vyhledávat správné řešení. Jako okrajové podmínky se také volí definiční obory dané funkce, aby se Řešitel zbytečně nesnažil hledat tam, kde to nemá smysl atd..
Vždy je ale třeba brát výsledky Řešitele s rezervou, neboť (a velmi často to tak bývá) má zadaná funkce nekonečně mnoho možných řešení a Řešitel najde pouze jedno z nich.

Zdravím,

jak jsem psala, jsem víc v nedosahu, než v dosahu (nakonec až do poloviny listopadu), tak spíš jen drobné komentáře, než podrobnější diskuse.

↑ Joe Hallenbeck:

V posledních letech data nijak aktivně nezpracovávám, jsou to pozůstatky z předchozích časů, řešitele - spíš pro místní fórum-účely a velmi omezeně (nenáročné optimalizační úlohy, pokud někdo konzultoval, ale tuto ani nevím, jak dopadla). Ale určitě to tak bude, jak popisuješ, ohledně "brát s rezervou".

↑ Eratosthenes:

:-) Konkrétně k této úloze už jsem okomentovala jak logaritmování, tak i to zhavarování na záporných hodnotách ↑ příspěvek 4:, ale pořád (i když i kolegové povídali) nejsem úplně přesvědčená, že v případě předpisů (příklad ) logaritmováním převoditelných na lineární závislost to je zcela nekorektní (např. pro zpracování Arrhenius rovnice). Pořád se používalo i u Rektoryse to čtu, že se používá i s uvedenou úpravou podmínky pro nejmenší čtverce. Asi mi to tak zůstane :-)

Ale ještě jednou - v tomto konkrétním zadaní je pochopitelně nepoužitelné.