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).

Není nad to, ž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í funkce 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 existuje pro výpočet součtu druhých mocnin speciální vzorec „SUMQDIFF“, který vše udělá 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 n 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,

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 .

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ě lze říci, že se zde okamžitě objeví zjevný "předstírač" - 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 obecně rozeberme jeho podstatu. 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 nyní se vrátíme k dalšímu důležitému bodu: 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 kurz, 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.

4.1. Použití vestavěných funkcí

výpočet regresní koeficienty provedené pomocí funkce

LINEST(Hodnoty_y; Hodnoty_x; Konst; statistika),

Hodnoty_y- pole hodnot y,

Hodnoty_x- volitelné pole hodnot X if pole X vynechán, předpokládá se, že se jedná o pole (1;2;3;...) stejné velikosti jako Hodnoty_y,

Konst- booleovská hodnota, která udává, zda je konstanta vyžadována b byla rovna 0. Pokud Konst má význam SKUTEČNÝ nebo vynechán, pak b vypočítané obvyklým způsobem. Pokud argument Konst je tedy NEPRAVDA b předpokládá se 0 a hodnoty A se volí tak, aby vztah y=ax.

Statistika- booleovská hodnota, která označuje, zda je požadováno vrácení dalších regresních statistik. Pokud argument Statistika má význam SKUTEČNÝ, pak funkci LINEST vrátí další regresní statistiky. Pokud argument Statistika má význam NEPRAVDIVÉ nebo vynechán, pak funkce LINEST vrátí pouze koeficient A a trvalé b.

Je třeba mít na paměti, že výsledek funkcí LINREGRESE() je množina hodnot - pole.

Pro výpočet korelační koeficient funkce se používá

CORREL(Pole1;Pole2),

vrací hodnoty korelačního koeficientu, kde Pole1- pole hodnot y, Pole2- pole hodnot X. Pole1 a Pole2 musí mít stejnou velikost.

PŘÍKLAD 1. Závislost y(X) je uveden v tabulce. Stavět regresní čára a vypočítat korelační koeficient.

Pojďme zadat tabulku hodnot do listu MS Excel a vytvořit bodový graf. Pracovní list bude mít podobu znázorněnou na obr. 2.

Aby bylo možné vypočítat hodnoty regresních koeficientů A a b vyberte buňky A7:B7, vraťme se do průvodce funkcí a do kategorie Statistický vyberte funkci LINEST. Vyplňte dialogové okno, které se objeví, jak je znázorněno na obr. 3 a stiskněte OK.

V důsledku toho se vypočítaná hodnota zobrazí pouze v buňce A6(obr. 4). Aby se hodnota objevila v buňce B6 musíte vstoupit do režimu úprav (klíč F2) a poté stiskněte kombinaci kláves CTRL+SHIFT+ENTER.

Pro výpočet hodnoty korelačního koeficientu na buňku C6 byl zaveden následující vzorec:

C7=CORREL(B3:J3;B2:J2).

Znalost regresních koeficientů A a b vypočítat hodnoty funkce y=sekera+b za daný X. K tomu zavedeme vzorec

B5=$A$7*B2+$B$7

a zkopírujte jej do rozsahu С5:J5(obr. 5).

Nanesme do diagramu regresní přímku. Vyberte experimentální body v grafu, klikněte pravým tlačítkem a vyberte příkaz Počáteční údaje. V zobrazeném dialogovém okně (obr. 5) vyberte záložku Řádek a klikněte na tlačítko Přidat. Vyplňte vstupní pole, jak je znázorněno na obr. 6 a stiskněte tlačítko OK. K grafu experimentálních dat bude přidána regresní přímka. Ve výchozím nastavení bude jeho graf zobrazen jako tečky nepropojené vyhlazovacími čarami.

Rýže. 6

Chcete-li změnit vzhled regresní čáry, proveďte následující kroky. Klepněte pravým tlačítkem myši na body znázorňující čárový graf a vyberte příkaz Typ grafu a nastavte typ bodového grafu, jak je znázorněno na obr. 7.

Typ čáry, barvu a tloušťku lze změnit následovně. Vyberte čáru na diagramu, stiskněte pravé tlačítko myši a v kontextovém menu vyberte příkaz Formát datové řady… Dále proveďte nastavení, například jak je znázorněno na Obr. osm.

Výsledkem všech transformací získáme graf experimentálních dat a regresní přímku v jedné grafické ploše (obr. 9).

4.2. Pomocí trendové čáry.

Konstrukce různých aproximačních závislostí v MS Excel je implementována jako vlastnost grafu - trendová linie.

PŘÍKLAD 2. V důsledku experimentu byla stanovena určitá tabulková závislost.

Vyberte a vytvořte přibližnou závislost. Sestavte grafy tabulkových a proložených analytických závislostí.

Řešení problému lze rozdělit do následujících fází: zadání počátečních dat, konstrukce bodového grafu a přidání trendové čáry k tomuto grafu.

Zvažme tento proces podrobně. Zadáme počáteční data do listu a vyneseme experimentální data. Dále vyberte experimentální body v grafu, klikněte pravým tlačítkem a použijte příkaz Přidat l trendová linie(obr. 10).

Dialogové okno, které se objeví, umožňuje vytvořit přibližnou závislost.

První záložka (obr. 11) tohoto okna označuje typ aproximující závislosti.

Druhý (obr. 12) definuje konstrukční parametry:

název aproximující závislosti;

Předpověď dopředu (dozadu) zapnuta n jednotky (tento parametr určuje, o kolik jednotek dopředu (dozadu) je nutné prodloužit trendovou linii);

zda zobrazit průsečík křivky s úsečkou y=konst;

zda zobrazit aproximační funkci na diagramu nebo ne (zobrazit rovnici na parametru diagramu);

Zda do diagramu umístit hodnotu směrodatné odchylky či nikoli (parametr vloží do diagramu hodnotu aproximační spolehlivosti).

Zvolme polynom druhého stupně jako aproximační závislost (obr. 11) a odvodíme rovnici popisující tento polynom na grafu (obr. 12). Výsledný diagram je znázorněn na Obr. 13.

Podobně s trendové linie můžete si vybrat parametry takových závislostí jako

lineární y=a∙x+b,

logaritmický y=a ln(X)+b,

exponenciální y=a∙eb,

Napájení y=a x b,

polynom y=a∙x 2 +b∙x+C, y=a∙x 3 +b∙x 2 +c∙x+d a tak dále, až do polynomu 6. stupně včetně,

Lineární filtrování.

4.3. Pomocí Decideru

Značně zajímavá je implementace výběru parametrů metodou nejmenších čtverců pomocí rozhodovacího bloku v MS Excel. Tato technika umožňuje zvolit parametry funkce jakéhokoli druhu. Uvažujme tuto možnost na příkladu následujícího problému.

PŘÍKLAD 3. Výsledkem experimentu je závislost z(t) uvedená v tabulce

Vyberte koeficienty závislosti Z(t)=A4+Bt3+Ct2+Dt+K metodou nejmenších čtverců.

Tento problém je ekvivalentní problému hledání minima funkce pěti proměnných

Zvažte postup řešení optimalizační úlohy (obr. 14).

Nechte hodnoty ALE, V, Z, D a Na uloženy v buňkách A7:E7. Vypočítejte teoretické hodnoty funkce Z(t)=At4+Bt3+Ct2+Dt+K za daný t(B2:J2). K tomu v cele B4 zadejte hodnotu funkce v prvním bodě (buňka B2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

Zkopírujte tento vzorec do rozsahu С4:J4 a získat očekávanou hodnotu funkce v bodech, jejichž úsečky jsou uloženy v buňkách B2:J2.

Do buňky B5 zavádíme vzorec, který vypočítá druhou mocninu rozdílu mezi experimentálními a vypočtenými body:

B5=(B4-B3)^2,

a zkopírujte jej do rozsahu С5:J5. V buňce F7 uložíme celkovou kvadratickou chybu (10). K tomu zavedeme vzorec:

F7 = SUM(B5:J5).

Použijme příkaz Service®Hledat řešení a vyřešit problém optimalizace bez omezení. Vyplňte příslušná vstupní pole v dialogovém okně zobrazeném na Obr. 14 a stiskněte tlačítko Běh. Pokud je nalezeno řešení, okno zobrazené na Obr. patnáct.

Výsledkem rozhodovacího bloku bude výstup do buněk A7:E7hodnoty parametrů funkcí Z(t)=At4+Bt3+Ct2+Dt+K. V buňkách B4:J4 dostaneme očekávaná hodnota funkce ve výchozích bodech. V buňce F7 budou zachovány celková kvadratická chyba.

Pokud vyberete rozsah, můžete zobrazit experimentální body a přizpůsobenou čáru ve stejné grafické ploše B2:J4, volání Průvodce grafem a poté naformátujte vzhled výsledných grafů.

Rýže. 17 zobrazí po provedení výpočtů pracovní list MS Excel.

5. REFERENCE

1. Alekseev E.R., Chesnokova O.V., Řešení úloh výpočetní matematiky v balíčcích Mathcad12, MATLAB7, Maple9. – NT Press, 2006.–596s. :nemocný. - (Tutorial)

2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, řešení inženýrských a matematických problémů. –M., BINOM, 2008.–260. léta.

3. I. S. Berezin a N. P. Zhidkov, Metody výpočtu, Moskva: Nauka, 1966.

4. Garnaev A.Yu., Využití MS EXCEL a VBA v ekonomii a financích. - Petrohrad: BHV - Petersburg, 1999.-332s.

5. B. P. Demidovich, I. A. Maron a V. Z. Shuvalova, Numerické metody analýzy.–M.: Nauka, 1967.–368s.

6. Korn G., Korn T., Příručka matematiky pro vědce a inženýry.–M., 1970, 720s.

7. Alekseev E.R., Chesnokova O.V. Pokyny pro provádění laboratorních prací v MS EXCEL. Pro studenty všech oborů. Doněck, DonNTU, 2004. 112 s.

Metoda nejmenších čtverců je matematický postup pro konstrukci lineární rovnice, která nejvíce odpovídá množině dvou řad čísel. Účelem této metody je minimalizovat celkovou druhou mocninu chyby. Excel má nástroje, které lze použít k použití této metody ve výpočtech. Pojďme se podívat, jak se to dělá.

Metoda nejmenších čtverců (LSM) je matematický popis závislosti jedné proměnné na druhé. Může být použit pro předpovědi.

Povolte doplněk Řešitel

Abyste mohli používat OLS v Excelu, musíte doplněk povolit "Hledat řešení", která je ve výchozím nastavení zakázána.

Nyní funkce Hledání řešení v Excelu je aktivován a jeho nástroje se zobrazí na pásu karet.

Podmínky problému

Popišme si aplikaci LSM na konkrétním příkladu. Máme dvě řady čísel X a y , jehož pořadí je znázorněno na obrázku níže.

Tuto závislost lze nejpřesněji popsat funkcí:

Přitom se ví, že x=0 y také rovné 0 . Proto lze tuto rovnici popsat závislostí y=nx .

Musíme najít minimální součet druhých mocnin rozdílu.

Řešení

Přejděme k popisu přímé aplikace metody.

Jak vidíte, aplikace metody nejmenších čtverců je poměrně komplikovaný matematický postup. Ukázali jsme to v praxi na nejjednodušším příkladu, ale existují i ​​mnohem složitější případy. Sada nástrojů Microsoft Excel je však navržena tak, aby výpočty co nejvíce zjednodušila.