Jednou z metod pro studium stochastických vztahů mezi rysy je regresní analýza.
Regresní analýza je odvození regresní rovnice, která se používá k nalezení průměrné hodnoty náhodné proměnné (vlastnost-výsledek), pokud je známa hodnota jiné (nebo jiných) proměnných (vlastnost-faktory). Zahrnuje následující kroky:

1. volba formy spojení (typ analytické regresní rovnice);
2. odhad parametrů rovnic;
3. hodnocení kvality analytické regresní rovnice.

Nejčastěji se pro odhad parametrů používá metoda nejmenších čtverců (LSM).
Metoda nejmenších čtverců poskytuje nejlepší (konzistentní, efektivní a nezkreslené) odhady parametrů regresní rovnice. Ale pouze pokud jsou splněny určité předpoklady o náhodném členu (u) a nezávislé proměnné (x) (viz předpoklady OLS).

Problém odhadu parametrů lineární párové rovnice metodou nejmenších čtverců spočívá v následujícím: získat takové odhady parametrů , , při kterých je součet druhých mocnin odchylek skutečných hodnot efektivního znaku - y i od vypočtených hodnot - minimální.
Formálně Kritérium OLS lze napsat takto: .

Klasifikace metod nejmenších čtverců

1. Metoda nejmenších čtverců.
2. Metoda maximální věrohodnosti (pro normální klasický lineární regresní model je postulována normalita regresních reziduí).
3. Zobecněná metoda nejmenších čtverců GLSM se používá v případě autokorelace chyb a v případě heteroskedasticity.
4. Metoda vážených nejmenších čtverců (speciální případ GLSM s heteroskedastickými rezidui).

Znázorněte podstatu klasická metoda nejmenších čtverců graficky. K tomu sestrojíme bodový graf podle pozorovacích dat (x i , y i , i=1;n) v pravoúhlém souřadnicovém systému (takovýto bodový graf se nazývá korelační pole). Zkusme najít přímku, která je nejblíže bodům korelačního pole. Podle metody nejmenších čtverců je přímka zvolena tak, aby součet čtverců vertikálních vzdáleností mezi body korelačního pole a touto přímkou ​​byl minimální.

Matematický zápis tohoto problému: .
Hodnoty y i a x i =1...n jsou nám známy, jedná se o pozorovací údaje. Ve funkci S jsou konstanty. Proměnné v této funkci jsou požadované odhady parametrů - , . Pro nalezení minima funkce 2 proměnných je nutné vypočítat parciální derivace této funkce vzhledem ke každému z parametrů a srovnat je s nulou, tzn. .
Výsledkem je systém 2 normálních lineárních rovnic:
Řešením tohoto systému najdeme požadované odhady parametrů:

Správnost výpočtu parametrů regresní rovnice lze zkontrolovat porovnáním součtů (je možná určitá nesrovnalost kvůli zaokrouhlování výpočtů).
Chcete-li vypočítat odhady parametrů, můžete sestavit tabulku 1.
Znaménko regresního koeficientu b udává směr vztahu (je-li b > 0, je vztah přímý, je-li b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Formálně je hodnota parametru a průměrná hodnota y pro x rovna nule. Pokud znaménkový faktor nemá a nemůže mít nulovou hodnotu, pak výše uvedená interpretace parametru a nedává smysl.

Posouzení těsnosti vztahu mezi rysy se provádí pomocí koeficientu lineární párové korelace - r x,y . Lze jej vypočítat pomocí vzorce: . Kromě toho lze koeficient lineární párové korelace určit pomocí regresního koeficientu b: .
Rozsah přípustných hodnot lineárního koeficientu párové korelace je od –1 do +1. Znaménko korelačního koeficientu udává směr vztahu. Jestliže r x, y >0, pak je spojení přímé; pokud r x, y<0, то связь обратная.
Pokud se tento koeficient blíží jednotce v modulu, pak lze vztah mezi prvky interpretovat jako poměrně blízký lineární. Je-li jeho modul roven jedné ê r x , y ê =1, pak je vztah mezi znaky funkčně lineární. Pokud jsou rysy x a y lineárně nezávislé, pak r x,y je blízko 0.
Tabulka 1 může být také použita pro výpočet r x,y.

Pro posouzení kvality získané regresní rovnice se vypočítá teoretický koeficient determinace - R 2 yx:

,
kde d 2 je rozptyl y vysvětlený regresní rovnicí;
e 2 - reziduální (nevysvětleno regresní rovnicí) rozptyl y ;
s 2 y - celkový (celkový) rozptyl y .
Koeficient determinace charakterizuje podíl variace (disperze) výsledného znaku y, vysvětleného regresí (a následně faktoru x), na celkové variaci (disperzi) y. Koeficient determinace R 2 yx nabývá hodnot od 0 do 1. Hodnota 1-R 2 yx tedy charakterizuje podíl rozptylu y způsobeného vlivem dalších faktorů nezohledněných v modelu a specifikačních chyb.
S párovou lineární regresí R 2 yx =r 2 yx .

Metoda nejmenších čtverců (LSM)

Soustava m lineárních rovnic s n neznámými má tvar:

Jsou možné tři případy: m n. Případ, kdy m=n bylo uvažováno v předchozích odstavcích. Pro m

Pokud je m>n a systém konzistentní, pak matice A má alespoň m - n lineárně závislých řádků. Zde lze řešení získat výběrem n libovolných lineárně nezávislých rovnic (pokud existují) a aplikací vzorce X=A -1 CV, tedy redukováním problému na dříve řešený. V tomto případě bude výsledné řešení vždy splňovat zbývajících m - n rovnic.

Při použití počítače je však výhodnější použít obecnější přístup – metodu nejmenších čtverců.

Algebraické nejmenší čtverce

Algebraická metoda nejmenších čtverců je chápána jako metoda řešení soustav lineárních rovnic

minimalizací euklidovské normy

Sekera? b? > inf . (1.2)

Experimentální analýza dat

Uvažujme o nějakém experimentu, během kterého v okamžicích času

například se měří teplota Q(t). Nechť jsou výsledky měření dány polem

Předpokládejme, že podmínky experimentu jsou takové, že měření jsou prováděna se známou chybou. V těchto případech se zákon změny teploty Q(t) hledá pomocí nějakého polynomu

P(t) = + + + ... +,

určení neznámých koeficientů, ..., z úvah, že hodnotu E(, ...,) definuje rovnost

gaussova algebraická exel aproximace

nabral minimální hodnotu. Protože je součet čtverců minimalizován, nazývá se tato metoda nejmenšími čtverci přizpůsobenými datům.

Nahradíme-li P(t) jeho výrazem, dostaneme

Položme si za úkol definovat pole tak, aby hodnota byla minimální, tzn. definujte pole pomocí metody nejmenších čtverců. Abychom to udělali, srovnáme parciální derivace s nulou:

Pokud zadáte m × n matici A = (), i = 1, 2..., m; j = 1, 2, ..., n, kde

I = 1, 2..., m; j = 1, 2, ..., n,

pak má písemná rovnost formu

Přepišme zapsanou rovnost z hlediska operací s maticemi. Podle definice máme násobení matice sloupcem

Pro transponovanou matici vypadá podobný vztah takto

Zavedeme následující zápis: budeme označovat i -tou složku vektoru Ax V souladu se zapsanými maticovými rovnostmi budeme mít

V maticové formě lze tuto rovnost přepsat jako

A T x = A T B (1,3)

Zde A je obdélníková matice m×n. Navíc v problémech aproximace dat je zpravidla m > n. Rovnice (1.3) se nazývá normální rovnice.

Od samého počátku bylo možné pomocí euklidovské normy vektorů napsat problém v ekvivalentní maticové formě:

Naším cílem je minimalizovat tuto funkci v x. Aby bylo v bodě řešení dosaženo minima, musí být první derivace vzhledem k x v tomto bodě rovné nule. Derivace této funkce jsou

2A T B + 2A T Ax

a proto řešení musí splňovat soustavu lineárních rovnic

(A T A)x = (A T B).

Tyto rovnice se nazývají normální rovnice. Je-li A matice m × n, pak A>A - n × n je matice, tzn. matice normální rovnice je vždy čtvercová symetrická matice. Navíc má vlastnost pozitivní určitosti v tom smyslu, že (A>Ax, x) = (Ax, Ax) ? 0.

Komentář. Někdy se řešení rovnice tvaru (1.3) nazývá řešením soustavy Ax = B, kde A je pravoúhlá matice m × n (m > n) metodou nejmenších čtverců.

Problém nejmenších čtverců lze graficky interpretovat jako minimalizaci vertikálních vzdáleností od datových bodů ke křivce modelu (viz obrázek 1.1). Tato myšlenka je založena na předpokladu, že všechny aproximační chyby odpovídají pozorovacím chybám. Pokud jsou chyby i ve vysvětlujících proměnných, pak může být vhodnější minimalizovat euklidovskou vzdálenost od dat k modelu.

OLS v Excelu

Níže uvedený algoritmus pro implementaci OLS v Excelu předpokládá, že všechna počáteční data jsou již známa. Obě části maticové rovnice AЧX=B systému zleva vynásobíme transponovanou maticí systému А Т:

A T AX \u003d A T B

Poté obě části rovnice vlevo vynásobíme maticí (A T A) -1. Pokud tato matice existuje, pak je systém definován. S přihlédnutím ke skutečnosti, že

(A T A) -1 * (A T A) \u003d E, dostáváme

X \u003d (A T A) -1 A T B.

Výsledná maticová rovnice je řešením soustavy m lineárních rovnic s n neznámými pro m>n.

Zvažte použití výše uvedeného algoritmu na konkrétním příkladu.

Příklad. Budiž třeba vyřešit systém

V aplikaci Excel vypadá list řešení v režimu zobrazení vzorce pro tento problém takto:

Výsledky výpočtu:

Požadovaný vektor X se nachází v rozsahu E11:E12.

Při řešení dané soustavy lineárních rovnic byly použity následující funkce:

1. MINUTA - Vrací inverzní hodnotu matice uložené v poli.

Syntaxe: NBR(pole).

Pole je číselné pole se stejným počtem řádků a sloupců.

2. MULTIP - vrací součin matic (matice jsou uloženy v polích). Výsledkem je pole se stejným počtem řádků jako pole1 a stejným počtem sloupců jako pole2.

Syntaxe: MULT(pole1, pole2).

Pole1, pole2 -- vynásobená pole.

Po zadání funkce do levé horní buňky oblasti pole vyberte pole, počínaje buňkou obsahující vzorec, stiskněte klávesu F2 a poté stiskněte klávesy CTRL+SHIFT+ENTER.

3. TRANSPOSE - převede vertikální sadu buněk na horizontální nebo naopak. Výsledkem použití této funkce je pole s počtem řádků rovným počtu sloupců v původním poli a počtem sloupců rovným počtu řádků v počátečním poli.

Který nachází nejširší uplatnění v různých oblastech vědy i praxe. Může to být fyzika, chemie, biologie, ekonomie, sociologie, psychologie a tak dále a tak dále. Vůlí osudu se často musím potýkat s ekonomikou, a proto vám dnes zařídím letenku do úžasné země tzv. Ekonometrie=) … Jak to nechceš?! Je to tam moc dobré - stačí se rozhodnout! …Ale to, co pravděpodobně určitě chcete, je naučit se řešit problémy nejmenší čtverce. A hlavně pilní čtenáři se je naučí řešit nejen přesně, ale i VELMI RYCHLE ;-) Ale nejdřív obecné vyjádření problému+ související příklad:

Nechte ukazatele studovat v nějaké předmětové oblasti, které mají kvantitativní vyjádření. Zároveň existují všechny důvody se domnívat, že indikátor závisí na indikátoru. Tento předpoklad může být jak vědeckou hypotézou, tak založenou na elementárním zdravém rozumu. Nechme však vědu stranou a prozkoumejme chutnější oblasti – jmenovitě obchody s potravinami. Označit podle:

– obchodní prostory prodejny potravin, m2,
- roční obrat obchodu s potravinami, miliony rublů.

Je zcela jasné, že čím větší plocha prodejny, tím větší je její obrat ve většině případů.

Předpokládejme, že po provedení pozorování / experimentů / výpočtů / tančení s tamburínou máme k dispozici číselná data:

U obchodů s potravinami je myslím vše jasné: - jedná se o oblast 1. prodejny, - její roční obrat, - oblast 2. prodejny, - její roční obrat atd. Mimochodem, není vůbec nutné mít přístup k utajovaným materiálům - poměrně přesné posouzení obratu lze získat pomocí matematické statistiky. Nenechte se však rozptylovat, kurz komerční špionáže je již placený =)

Tabulkové údaje mohou být také zapsány ve formě bodů a zobrazeny pro nás obvyklým způsobem. Kartézský systém .

Pojďme si odpovědět na důležitou otázku: kolik bodů je potřeba pro kvalitativní studii?

Čím větší, tím lepší. Minimální přípustná sada se skládá z 5-6 bodů. Navíc s malým množstvím dat by do vzorku neměly být zahrnuty „abnormální“ výsledky. Takže například malý elitní obchod může pomoci řádově více než „jejich kolegové“, čímž zkresluje obecný vzorec, který je třeba najít!

Pokud je to docela jednoduché, musíme vybrat funkci, plán která prochází co nejblíže k bodům . Taková funkce se nazývá přibližující se (přiblížení - přiblížení) nebo teoretická funkce . Obecně řečeno, zde se okamžitě objeví zřejmý „představitel“ - polynom vysokého stupně, jehož graf prochází VŠEMI body. Tato možnost je však komplikovaná a často jednoduše nesprávná. (protože graf se neustále „navíjí“ a špatně odráží hlavní trend).

Požadovaná funkce tedy musí být dostatečně jednoduchá a zároveň adekvátně odrážet závislost. Jak asi tušíte, jedna z metod hledání takových funkcí se nazývá nejmenší čtverce. Nejprve si rozeberme jeho podstatu obecný pohled. Nechť nějakou funkci aproximuje experimentální data:


Jak vyhodnotit přesnost této aproximace? Vypočítejme také rozdíly (odchylky) mezi experimentálními a funkčními hodnotami (studujeme kresbu). První myšlenka, která vás napadne, je odhadnout, jak velký součet je, ale problém je, že rozdíly mohou být záporné. (Například, ) a odchylky v důsledku takového sčítání se vzájemně vyruší. Proto se jako odhad přesnosti aproximace navrhuje vzít součet moduly odchylky:

nebo ve složené podobě: (najednou, kdo neví: je ikona součtu a je pomocná proměnná-"počítadlo", které nabývá hodnot od 1 do ).

Aproximací experimentálních bodů s různými funkcemi získáme různé hodnoty a je zřejmé, že kde je tento součet menší, je tato funkce přesnější.

Taková metoda existuje a je volána metoda nejmenšího modulu. V praxi se však značně rozšířil. metoda nejmenších čtverců, ve kterém jsou možné záporné hodnoty eliminovány nikoli modulem, ale kvadraturou odchylek:

, načež úsilí směřuje k výběru takové funkce, aby součet čtverců odchylek byl co nejmenší. Vlastně odtud název metody.

A teď jsme zase u dalšího důležitý bod: jak je uvedeno výše, vybraná funkce by měla být poměrně jednoduchá - ale existuje také mnoho takových funkcí: lineární , hyperbolický, exponenciální, logaritmický, kvadratický atd. A samozřejmě bych zde hned rád „zmenšil pole působnosti“. Jakou třídu funkcí zvolit pro výzkum? Primitivní, ale účinná technika:

- Nejjednodušší způsob kreslení bodů na výkresu a analyzovat jejich umístění. Pokud mají tendenci být v přímé linii, pak byste měli hledat přímková rovnice s optimálními hodnotami a . Jinými slovy, úkolem je najít TAKOVÉ koeficienty – tak, aby součet čtverců odchylek byl nejmenší.

Pokud se body nacházejí např. podél nadsázka, pak je jasné, že lineární funkce poskytne špatnou aproximaci. V tomto případě hledáme „nejpříznivější“ koeficienty pro rovnici hyperboly - ty, které dávají minimální součet čtverců .

Nyní si všimněte, že v obou případech mluvíme o funkce dvou proměnných, jehož argumenty jsou hledali možnosti závislostí:

A v podstatě potřebujeme vyřešit standardní problém – najít minimálně funkce dvou proměnných.

Připomeňme si náš příklad: předpokládejme, že „obchodní“ body mají tendenci být umístěny v přímé linii a existuje každý důvod věřit v přítomnost lineární závislost obrat z obchodní oblasti. Najděte TAKOVÉ koeficienty "a" a "be" tak, aby byl součet čtverců odchylek byl nejmenší. Všechno jako obvykle - první parciální derivace 1. řádu. Podle pravidlo linearity můžete rozlišit přímo pod ikonou součtu:

Pokud budete chtít tyto informace použít pro esej nebo semestrální práci, budu velmi vděčný za odkaz v seznamu zdrojů, nikde tak podrobné výpočty nenajdete:

Udělejme standardní systém:

Každou rovnici zmenšíme o „dvojku“ a navíc „rozdělíme“ součty:

Poznámka : nezávisle analyzovat, proč lze z ikony součtu vyjmout „a“ a „být“. Mimochodem, formálně to lze provést součtem

Pojďme přepsat systém do "aplikované" formy:

poté se začne kreslit algoritmus pro řešení našeho problému:

Známe souřadnice bodů? Víme. Součty můžeme najít? Snadno. Skládáme to nejjednodušší soustava dvou lineárních rovnic se dvěma neznámými("a" a "beh"). Systém řešíme např. Cramerova metoda, což má za následek stacionární bod . Kontrola postačující podmínkou pro extrém, můžeme ověřit, že v tomto bodě funkce přesně dosáhne minimální. Ověření je spojeno s dalšími výpočty a proto jej necháme v zákulisí. (v případě potřeby lze chybějící rámeček zobrazit). Vyvodíme konečný závěr:

Funkce nejlepší způsob (alespoň ve srovnání s jakoukoli jinou lineární funkcí) přibližuje experimentální body . Zhruba řečeno, její graf prochází co nejblíže těmto bodům. V tradici ekonometrie výsledná aproximační funkce se také nazývá párová lineární regresní rovnice .

Zvažovaný problém má velký praktický význam. V situaci s naším příkladem rovnice umožňuje předvídat, jaký druh obratu ("yig") bude v obchodě s tou či onou hodnotou prodejní plochy (jeden nebo jiný význam "x"). Ano, výsledná předpověď bude pouze prognózou, ale v mnoha případech se ukáže jako docela přesná.

Rozeberu pouze jeden problém se "skutečnými" čísly, protože v něm nejsou žádné potíže - všechny výpočty jsou na úrovni školních osnov v 7.-8. V 95 procentech případů budete požádáni, abyste našli pouze lineární funkci, ale na samém konci článku ukážu, že není o nic složitější najít rovnice pro optimální hyperbolu, exponent a některé další funkce.

Vlastně zbývá rozdávat slíbené dobroty – abyste se takové příklady naučili řešit nejen přesně, ale i rychle. Pečlivě studujeme standard:

Úkol

Jako výsledek studia vztahu mezi dvěma ukazateli byly získány následující dvojice čísel:

Pomocí metody nejmenších čtverců najděte lineární funkci, která nejlépe aproximuje empirickou funkci (zkušený) data. Vytvořte výkres, na kterém v kartézském pravoúhlém systému souřadnic vykreslete experimentální body a graf aproximační funkce . Najděte součet čtverců odchylek mezi empirickými a teoretickými hodnotami. Zjistěte, zda je funkce lepší (z hlediska metody nejmenších čtverců) přibližné experimentální body.

Všimněte si, že hodnoty „x“ jsou přirozené hodnoty, a to má charakteristický smysluplný význam, o kterém budu mluvit o něco později; ale samozřejmě mohou být zlomkové. Navíc v závislosti na obsahu konkrétního úkolu mohou být hodnoty „X“ i „G“ zcela nebo částečně záporné. Dostali jsme úkol „bez tváře“ a začínáme s ním řešení:

Najdeme koeficienty optimální funkce jako řešení systému:

Pro účely kompaktnějšího zápisu lze proměnnou „counter“ vynechat, protože je již jasné, že sčítání se provádí od 1 do .

Je vhodnější vypočítat požadované částky v tabulkové formě:


Výpočty lze provádět na mikrokalkulátoru, ale mnohem lepší je používat Excel - rychlejší a bez chyb; podívejte se na krátké video:

Dostáváme tedy následující Systém:

Zde můžete vynásobit druhou rovnici 3 a odečíst 2. od 1. rovnice člen po členu. To je ale štěstí – v praxi často nejsou systémy nadané a v takových případech šetří Cramerova metoda:
, takže systém má unikátní řešení.

Udělejme kontrolu. Chápu, že nechci, ale proč přeskakovat chyby tam, kde je absolutně nemůžete minout? Nalezené řešení dosaďte na levou stranu každé rovnice soustavy:

Jsou získány správné části odpovídajících rovnic, což znamená, že systém je vyřešen správně.

Požadovaná aproximační funkce: – od všechny lineární funkce nejlépe se jím aproximují experimentální data.

Na rozdíl od rovný závislost obratu prodejny na její ploše, zjištěná závislost je zvrátit (zásada „čím více – tím méně“), a tuto skutečnost ihned odhalí záporák úhlový koeficient. Funkce nás informuje, že s nárůstem určitého ukazatele o 1 jednotku se hodnota závislého ukazatele snižuje průměrný o 0,65 jednotky. Jak se říká, čím vyšší je cena pohanky, tím méně se prodává.

Pro vykreslení aproximační funkce najdeme dvě její hodnoty:

a proveďte výkres:


Sestrojená čára se nazývá trendová linie (konkrétně lineární trendová čára, tj. v obecném případě trend nemusí být nutně přímka). Výraz „být v trendu“ zná každý a myslím, že tento výraz nepotřebuje dalších komentářů.

Vypočítejte součet čtverců odchylek mezi empirickými a teoretickými hodnotami. Geometricky jde o součet druhých mocnin délek „karmínových“ segmentů (dva z nich jsou tak malé, že je ani nevidíte).

Shrňme si výpočty do tabulky:


Lze je opět provést ručně, pro případ, že uvedu příklad pro 1. bod:

ale mnohem efektivnější je udělat již známý způsob:

Zopakujme si: jaký je význam výsledku? Z všechny lineární funkce funkce exponent je nejmenší, to znamená, že je to nejlepší aproximace ve své rodině. A tady, mimochodem, poslední otázka problému není náhodná: co když navrhovaná exponenciální funkce bude lepší aproximovat experimentální body?

Najděte odpovídající součet čtverců odchylek - pro jejich rozlišení je označím písmenem "epsilon". Technika je úplně stejná:


A znovu pro každý výpočet požáru pro 1. bod:

V Excelu používáme standardní funkci EXP (Syntaxi najdete v nápovědě Excelu).

Závěr: , takže exponenciální funkce aproximuje experimentální body hůře než přímka .

Zde je ale třeba podotknout, že „horší“ je ještě neznamená, co je špatně. Nyní jsem vytvořil graf této exponenciální funkce - a také prochází blízko bodů - natolik, že bez analytické studie je obtížné říci, která funkce je přesnější.

Tím je řešení dokončeno a vracím se k otázce přirozených hodnot argumentu. V různých studiích jsou zpravidla ekonomické nebo sociologické měsíce, roky nebo jiné stejné časové intervaly číslovány přirozeným „X“. Vezměme si například takový problém.

No a v práci se hlásili na inspekci, článek se psal doma na konferenci - teď můžete psát do blogu. Zatímco jsem zpracovával svá data, uvědomil jsem si, že nemohu jinak, než napsat o velmi skvělém a nezbytném doplňku v Excelu, který se nazývá . Článek tedy bude věnován tomuto konkrétnímu doplňku a řeknu vám o něm na příkladu použití metoda nejmenších čtverců(LSM) hledat neznámé koeficienty rovnice v popisu experimentálních dat.

Jak povolit doplněk "hledat řešení"

Nejprve zjistíme, jak tento doplněk povolit.

1. Přejděte do nabídky „Soubor“ a vyberte „Možnosti aplikace Excel“

2. V okně, které se zobrazí, vyberte „Vyhledat řešení“ a klikněte na „Přejít“.

3. V dalším okně zaškrtněte položku „hledat řešení“ a klikněte na „OK“.

4. Doplněk je aktivován – nyní jej naleznete v položce nabídky „Data“.

Metoda nejmenších čtverců

Nyní krátce o metoda nejmenších čtverců (LSM) a kde se dá uplatnit.

Řekněme, že máme soubor dat poté, co jsme provedli nějaký experiment, kde jsme studovali účinky hodnoty X na hodnotu Y.

Chceme tento vliv popsat matematicky, abychom později mohli použít tento vzorec a věděli, že když změníme hodnotu X o tolik, dostaneme hodnotu Y takové a takové ...

Vezměme si super jednoduchý příklad (viz obrázek).

Nepřemýšlíte nad tím, že body jsou umístěny za sebou jakoby v přímce, a proto bezpečně předpokládáme, že naše závislost je popsána lineární funkcí y=kx+b. Zároveň máme jistotu, že když se X rovná nule, rovná se i hodnota Y nule. To znamená, že funkce popisující závislost bude ještě jednodušší: y=kx (vzpomeňte si na školní osnovy).

Obecně musíme najít koeficient k. To je to, s čím budeme dělat MNC pomocí doplňku „hledat řešení“.

Metodou je (zde - pozor: musíte o tom přemýšlet) součet čtverců rozdílů mezi experimentálně získanými a odpovídajícími vypočtenými hodnotami byl minimální. To znamená, že když X1=1 je skutečná naměřená hodnota Y1=4,6 a vypočtené y1=f (x1) je 4, druhá mocnina rozdílu bude (y1-Y1)^2=(4-4,6)^2= 0,36. Totéž platí pro následující: když X2=2, aktuální naměřená hodnota Y2=8,1 a vypočtené y2 je 8, druhá mocnina rozdílu bude (y2-Y2)^2=(8-8,1)^2=0,01. A součet všech těchto čtverců by měl být co nejmenší.

Začněme tedy trénovat používání LSM a Doplňky Excelu "hledat řešení" .

Aplikace add-in find řešení

1. Pokud jste nepovolili doplněk „hledat řešení“, vraťte se ke kroku Jak povolit doplněk „hledat řešení“ a povolit 🙂

2. Do buňky A1 zadejte hodnotu "1". Tato jednotka bude první aproximací ke skutečné hodnotě koeficientu (k) naší funkční závislosti y=kx.

3. Ve sloupci B máme hodnoty parametru X, ve sloupci C hodnoty parametru Y. Do buněk sloupce D zadáme vzorec: „koeficient k vynásobený hodnotou X“. Například do buňky D1 zadejte „=A1*B1“, do buňky D2 zadejte „=A1*B2“ a tak dále.

4. Věříme, že koeficient k je roven jedné a funkce f (x) \u003d y \u003d 1 * x je první aproximací našeho řešení. Můžeme vypočítat součet druhých mocnin rozdílů mezi naměřenými hodnotami Y a těmi vypočítanými pomocí vzorce y=1*x. To vše můžeme udělat ručně tím, že zařadíme příslušné odkazy na buňky do vzorce: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... atd. Nakonec se mýlí a chápeme, že jsme ztratili spoustu času. V Excelu je pro výpočet součtu čtverců rozdílů speciální vzorec „SUMQDIFF“, který udělá vše za nás. Zadáme jej do buňky A2 a nastavíme počáteční data: rozsah naměřených hodnot Y (sloupec C) a rozsah vypočtených hodnot Y (sloupec D).

4. Byl vypočten součet rozdílů čtverců – nyní přejděte na záložku „Data“ a vyberte „Hledat řešení“.

5. V zobrazené nabídce vyberte buňku A1 jako buňku, kterou chcete změnit (buňku s koeficientem k).

6. Jako cíl vyberte buňku A2 a nastavte podmínku "nastavit rovno minimální hodnotě." Pamatujte, že toto je buňka, kde počítáme součet druhých mocnin rozdílů mezi vypočtenými a naměřenými hodnotami a tato částka by měla být minimální. Stiskneme "provést".

7. Je zvolen koeficient k. Nyní je vidět, že vypočtené hodnoty jsou nyní velmi blízké těm naměřeným.

P.S.

Obecně samozřejmě pro aproximaci experimentálních dat v Excelu existují speciální nástroje, které umožňují popsat data pomocí lineární, exponenciální, mocninné a polynomiální funkce, takže se často obejdete bez doplňky "Hledat řešení". O všech těchto metodách aproximace jsem mluvil ve svém článku, takže pokud máte zájem, podívejte se. Ale když jde o nějakou exotickou funkci s jedním neznámým koeficientem nebo problémy s optimalizací, pak zde nástavba co nejlépe.

Doplněk "hledat řešení" lze použít pro jiné úkoly, jde hlavně o to pochopit podstatu: existuje buňka, kde vybíráme hodnotu, a je tu cílová buňka, ve které je nastavena podmínka pro výběr neznámého parametru.
To je vše! V příštím článku budu vyprávět pohádku o dovolené, takže abyste nepromeškali vydání článku,