Příklady metody nejmenších čtverců s řešeními v Excelu. Metoda nejmenších čtverců v Excelu. Regresní analýza. Pár slov o správnosti výchozích dat použitých pro predikci

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

Prostudujme ukazatele v určité tematické 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 buď vědeckou hypotézou, nebo založen na základním zdravém rozumu. Nechme však vědu stranou a prozkoumejme chutnější oblasti – jmenovitě obchody s potravinami. Označme podle:

– prodejní plocha prodejny potravin, m2,
– roční obrat obchodu s potravinami, miliony rublů.

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

Předpokládejme, že po provedení pozorování/experimentů/výpočtů/tance 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 obchodu lze získat pomocí matematické statistiky. Nenechme 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 ve známé formě 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řijatelná sada se skládá z 5-6 bodů. Navíc, když je množství dat malé, nelze do vzorku zahrnout „anomální“ výsledky. Takže například malý elitní obchod může vydělat řádově více než „jeho kolegové“, čímž zkresluje obecný vzorec, který musíte najít!

Jednoduše řečeno, musíme vybrat funkci, plán který prochází co nejblíže k bodům . Tato funkce se nazývá přibližující se (přiblížení - přiblížení) nebo teoretická funkce . Obecně řečeno, okamžitě se zde objeví zřejmý „konkurent“ - 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 bude neustále „smyčkovat“ a špatně odráží hlavní trend).

Hledaná funkce tedy musí být zcela 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á metoda nejmenších čtverců. Nejprve se podívejme na 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ý je součet, 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, jako odhad přesnosti aproximace, je třeba vzít součet moduly odchylky:

nebo zhroucený: (pro případ, že by někdo nevěděl: – toto je ikona součtu a – 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 samozřejmě, kde je tento součet menší, je tato funkce přesnější.

Taková metoda existuje a je tzv metoda nejmenšího modulu. V praxi se však značně rozšířil metoda nejmenších čtverců, ve kterém případné záporné hodnoty nejsou eliminovány modulem, ale umocněním odchylek:

, načež je úsilí zaměřeno na výběr takové funkce, aby součet čtverců odchylek byl co nejmenší. Ve skutečnosti odtud pochází název metody.

A teď se vrátíme k něčemu jinému 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 okamžitě rád „zmenšil pole působnosti“. Jakou třídu funkcí bych si měl vybrat pro výzkum? Primitivní, ale účinná technika:

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

Pokud se body nacházejí např. podél nadsázka, pak je samozřejmě 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 hledané parametry závislosti:

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

Vzpomeňme si na náš příklad: předpokládejme, že „ukládací“ body mají tendenci být umístěny v přímce a existuje každý důvod se domnívat, že lineární závislost obrat z maloobchodních prostor. Najděte TAKOVÉ koeficienty „a“ ​​a „be“ takové, aby byl součet čtverců odchylek byl nejmenší. Všechno je jako obvykle - první Parciální derivace 1. řádu. Podle pravidlo linearity Přímo pod ikonou součtu můžete rozlišovat:

Pokud chcete tyto informace použít pro esej nebo semestrální práci, budu velmi vděčný za odkaz v seznamu zdrojů, takto podrobné výpočty najdete málokde:

Vytvořme standardní systém:

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

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

Přepišme systém do „aplikované“ formy:

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

Známe souřadnice bodů? Víme. Množství můžeme to najít? Snadno. Udělejme to nejjednodušší soustava dvou lineárních rovnic o dvou neznámých(„a“ a „být“). Systém řešíme např. Cramerova metoda, v důsledku čehož získáme 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í. Kontrola zahrnuje dodatečné výpočty, a proto ji ponechá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 naší příkladové situaci, Eq. umožňuje předvídat, jaký obchodní obrat ("Igrek") obchod bude mít tu či onu hodnotu 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ího kurikula 7.-8. V 95 procentech případů budete požádáni o nalezení právě lineární funkce, ale na samém konci článku ukážu, že není o nic složitější najít rovnice optimální hyperboly, exponenciální a některých dalších funkcí.

Vlastně už zbývá jen 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 sestrojí experimentální body a graf aproximační funkce v kartézském pravoúhlém souřadnicovém systému . Najděte součet čtverců odchylek mezi empirickými a teoretickými hodnotami. Zjistěte, zda by funkce byla lepší (z pohledu metody nejmenších čtverců) přiblížit experimentální body.

Vezměte prosím na vědomí, že významy „x“ jsou přirozené a mají charakteristický smysluplný význam, o kterém budu mluvit o něco později; ale samozřejmě mohou být i zlomkové. Navíc v závislosti na obsahu konkrétního úkolu mohou být hodnoty „X“ i „hra“ 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áznamu 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 systémy často nejsou darem a v takových případech šetří Cramerova metoda:
, což znamená, že systém má jedinečné řešení.

Pojďme zkontrolovat. Chápu, že nechcete, ale proč přeskakovat chyby tam, kde je absolutně nelze přehlédnout? Dosadíme nalezené řešení do levé strany každé rovnice soustavy:

Získají se pravé strany 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 Je to ona, kdo nejlépe aproximuje 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 sklon. Funkce nám říká, ž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í grafu aproximační funkce najdeme její dvě hodnoty:

a proveďte výkres:


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

Vypočítejme součet čtverců odchylek mezi empirickými a teoretickými hodnotami. Geometricky se jedná o součet druhých mocnin délek „malinových“ segmentů (dva z nich jsou tak malé, že nejsou ani vidět).

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


Opět je lze provést ručně; pro případ uvedu příklad pro 1. bod:

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

Ještě jednou opakujeme: Co znamená získaný výsledek? Z všechny lineární funkce funkce y indikátor je nejmenší, to znamená, že ve své rodině je nejlepší aproximací. A tady, mimochodem, poslední otázka problému není náhodná: co když navrhovaná exponenciální funkce bylo by lepší přiblížit experimentální body?

Pojďme najít odpovídající součet čtverců odchylek - pro rozlišení je označím písmenem „epsilon“. Technika je úplně stejná:


A znovu, pro každý případ, výpočty pro 1. bod:

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

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

Zde je však třeba poznamenat, ž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ého výzkumu je obtížné říci, která funkce je přesnější.

Tím je řešení uzavřeno a vracím se k otázce přirozených hodnot argumentu. V různých studiích, obvykle ekonomických nebo sociologických, se přirozená „X“ používají k číslování měsíců, let nebo jiných stejných časových intervalů. Zvažte například následující problém.

Metoda nejmenších čtverců (LS) je založena na minimalizaci součtu čtverců odchylek vybrané funkce od studovaných dat. V tomto článku aproximujeme dostupná data pomocí lineární funkcey = A X + b .

Metoda nejmenších čtverců(Angličtina) Obyčejný Nejméně Čtverce , O.L.S.) je jednou ze základních metod regresní analýzy z hlediska odhadu neznámých parametrů regresní modely podle vzorových údajů.

Uvažujme aproximaci pomocí funkcí, které závisí pouze na jedné proměnné:

• Lineární: y=ax+b (tento článek)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+с
• : y=ax 2 +bx+c

Poznámka: V tomto článku jsou zvažovány případy aproximace polynomem od 3. do 6. stupně. Uvažuje se zde aproximace trigonometrickým polynomem.

Lineární závislost

Zajímá nás souvislost mezi 2 proměnnými X A y. Existuje předpoklad, že y záleží na X podle lineárního zákona y = sekera + b. K určení parametrů tohoto vztahu výzkumník provedl pozorování: pro každou hodnotu x i bylo provedeno měření y i (viz soubor s příkladem). Nechť tedy existuje 20 párů hodnot (x i; y i).

Poznámka: Pokud je krok změny X je konstantní, pak stavět rozptylové grafy lze použít, pokud ne, musíte použít typ grafu Bod .

Z diagramu je zřejmé, že vztah mezi proměnnými je blízký lineární. Abychom pochopili, která z mnoha přímek nejvíce „správně“ popisuje vztah mezi proměnnými, je nutné určit kritérium, podle kterého se budou čáry porovnávat.

Jako takové kritérium používáme výraz:

Kde ŷ i = A * x i + b ; n – počet dvojic hodnot (v našem případě n=20)

Výše uvedený výraz je součtem čtverců vzdáleností mezi pozorovanými hodnotami y i a ŷ i a je často označován jako SSE ( Součet z Čtvercový Chyby (Zbytky), součet čtverečních chyb (zbytky)) .

Metoda nejmenších čtverců je vybrat takový řádek ŷ = sekera + b, pro který má výše uvedený výraz minimální hodnotu.

Poznámka: Jakákoli čára ve dvourozměrném prostoru je jednoznačně určena hodnotami 2 parametrů: A (sklon) a b (posun).

Předpokládá se, že čím menší je součet čtverců vzdáleností, tím lépe se odpovídající čára přibližuje dostupným datům a může být dále použita k predikci hodnot y z proměnné x. Je jasné, že i když ve skutečnosti neexistuje žádný vztah mezi proměnnými nebo je vztah nelineární, pak OLS stále vybere „nejlepší“ linii. Metoda nejmenších čtverců tedy neříká nic o přítomnosti skutečného vztahu mezi proměnnými; metoda vám jednoduše umožňuje vybrat takové parametry funkce A A b , pro které je výše uvedený výraz minimální.

Prováděním nepříliš složitých matematických operací (více podrobností viz) můžete vypočítat parametry A A b :

Jak je vidět ze vzorce, parametr A představuje poměr kovariance a proto v MS EXCEL pro výpočet parametru A Můžete použít následující vzorce (viz Soubor příkladu lineárního listu):

= KOVAR(B26:B45;C26:C45)/ DISP.G(B26:B45) nebo

= COVARIANCE.B(B26:B45;C26:C45)/DISP.B(B26:B45)

Také pro výpočet parametru A můžete použít vzorec = NÁKLON(C26:C45;B26:B45). Pro parametr b použijte vzorec = LEG(C26:C45;B26:B45) .

Konečně funkce LINREGRESE() umožňuje vypočítat oba parametry najednou. Chcete-li zadat vzorec LINREGRESE(C26:C45;B26:B45) Musíte vybrat 2 buňky v řadě a kliknout CTRL + POSUN + ENTER(viz článek o). Hodnota bude vrácena v levé buňce A , napravo - b .

Poznámka: Aby nedošlo k záměně se vstupem maticové vzorce budete muset dodatečně použít funkci INDEX(). Vzorec = INDEX(LINEST(C26:C45;B26:B45);1) nebo jen = LINREGRESE(C26:C45;B26:B45) vrátí parametr zodpovědný za sklon čáry, tzn. A . Vzorec = INDEX(LINEST(C26:C45;B26:B45);2) vrátí parametr zodpovědný za průsečík přímky s osou Y, tzn. b .

Po výpočtu parametrů, rozptylový diagram můžete nakreslit odpovídající čáru.

Dalším způsobem, jak vykreslit přímku pomocí metody nejmenších čtverců, je nástroj graf Trendová linie. Chcete-li to provést, vyberte diagram, vyberte z nabídky Karta rozvržení, V skupinová analýza klikněte Trendová linie, pak Lineární aproximace .

Zaškrtnutím políčka „zobrazit rovnici v diagramu“ v dialogovém okně se můžete ujistit, že parametry nalezené výše odpovídají hodnotám v diagramu.

Poznámka: Aby se parametry shodovaly, typ diagramu musí být . Jde o to, že při konstrukci diagramu Plán Hodnoty osy X nemůže uživatel zadat (uživatel může zadat pouze popisky, které neovlivňují umístění bodů). Místo hodnot X je použita sekvence 1; 2; 3; ... (pro číslování kategorií). Pokud tedy stavíte trendová linie na typovém diagramu Plán, pak se místo skutečných hodnot X použijí hodnoty této sekvence, což povede k nesprávnému výsledku (pokud se ovšem skutečné hodnoty X neshodují se sekvencí 1; 2; 3; ...).

Metoda nejmenších čtverců je matematický postup pro sestavení lineární rovnice, která bude nejpřesněji odpovídat množině dvou řad čísel. Účelem použití této metody je minimalizovat celkovou čtvercovou chybu. Excel má nástroje, které vám mohou pomoci použít tuto metodu ve vašich výpočtech. Pojďme zjistit, jak se to dělá.

· Použití metody v Excelu

o Povolení doplňku „Solution Search“.

o Problémové stavy

o Řešení

Použití metody v Excelu

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.

Povolení doplňku Najít řešení

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

1. Přejděte na kartu "Soubor".

2. Klikněte na název sekce "Možnosti".

3. V okně, které se otevře, vyberte podsekci "Doplňky".

4. V bloku "Řízení", který se nachází ve spodní části okna, nastavte přepínač do polohy "Doplňky aplikace Excel"(pokud má jinou hodnotu) a klikněte na tlačítko "Jít...".

5. Otevře se malé okno. Vedle parametru zaškrtneme "Najít řešení". Klikněte na tlačítko "OK".

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

Lekce: Hledání řešení v Excelu

Podmínky problému

Popišme použití 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 je známo, že kdy 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.

1. Nalevo od první hodnoty X dát číslo 1 . Toto bude přibližná hodnota hodnoty prvního koeficientu n.

2. Napravo od sloupce y přidat další sloupec - nx. Do první buňky tohoto sloupce napíšeme vzorec pro násobení koeficientu n na buňku první proměnné X. Zároveň vytvoříme vazbu na pole s koeficientem absolutním, protože tato hodnota se nezmění. Klikněte na tlačítko Vstupte.

3. Pomocí značky výplně zkopírujte tento vzorec pro celý rozsah tabulky ve sloupci níže.

4. V samostatné buňce vypočítejte součet rozdílů mezi čtverci hodnot y A nx. Chcete-li to provést, klepněte na tlačítko "Vložit funkci".

5. V otevřené "Průvodce funkcí" hledá záznam "SUMMKVARNA". Vyberte jej a stiskněte tlačítko "OK".

6. Otevře se okno s argumenty. V terénu "Array_x" y. V terénu "Array_y" zadejte rozsah buněk sloupce nx. Chcete-li zadat hodnoty, jednoduše umístěte kurzor do pole a vyberte odpovídající rozsah na listu. Po zadání klikněte na tlačítko "OK".

7. Přejděte na kartu "Data". Na pásu karet v panelu nástrojů "Analýza" klikněte na tlačítko "Najít řešení".

8. Otevře se okno parametrů pro tento nástroj. V terénu „Optimalizujte účelovou funkci“ uveďte adresu buňky se vzorcem "SUMMKVARNA". V parametru "Před" nezapomeňte nastavit přepínač do polohy "Minimální". V terénu "Změna buněk" uveďte adresu s hodnotou koeficientu n. Klikněte na tlačítko "Najít řešení".

9. Řešení se zobrazí v buňce koeficientů n. Tato hodnota bude nejmenší čtverec funkce. Pokud výsledek uživatele uspokojí, klikněte na tlačítko "OK" v dodatečném okně.

Jak vidíte, aplikace metody nejmenších čtverců je poměrně složitý matematický postup. Ukázali jsme to v akci na jednoduchém příkladu, ale je toho mnohem víc složité případy. Nástroje Microsoft Excel jsou však navrženy tak, aby výpočty co nejvíce zjednodušily.

http://multitest.semico.ru/mnk.htm

Obecná ustanovení

Čím menší číslo v absolutní hodnotě, tím lepší je zvolená přímka (2). Jako charakteristiku přesnosti výběru přímky (2) můžeme vzít součet čtverců

Minimální podmínky pro S budou

	(6)
	(7)

Rovnice (6) a (7) lze zapsat takto:

	(8)
	(9)

Z rovnic (8) a (9) je snadné najít a a b z experimentálních hodnot xi a y i. Přímka (2), definovaná rovnicemi (8) a (9), se nazývá přímka získaná metodou nejmenších čtverců (tento název zdůrazňuje, že součet čtverců S má minimum). Rovnice (8) a (9), ze kterých je určena přímka (2), se nazývají normální rovnice.

Můžete uvést jednoduchý a obecný způsob skládání normálních rovnic. Pomocí experimentálních bodů (1) a rovnice (2) můžeme napsat soustavu rovnic pro a a b

Vynásobme levou a pravou stranu každé z těchto rovnic koeficientem první neznámé a (tj. x 1, x 2, ..., x n) a výsledné rovnice sečteme, vznikne první normální rovnice (8) .

Vynásobme levou a pravou stranu každé z těchto rovnic koeficientem druhé neznámé b, tzn. o 1 a sečteme výsledné rovnice, výsledkem je druhá normální rovnice (9).

Tento způsob získávání normálních rovnic je obecný: je vhodný např. pro funkci

existuje konstantní hodnota a musí být určena z experimentálních dat (1).

Systém rovnic pro k lze napsat:

Najděte přímku (2) metodou nejmenších čtverců.

Řešení. Shledáváme:

Xi=21, yi=46,3, xi2=91, xiyi=179,1.

Napíšeme rovnice (8) a (9)91a+21b=179,1,

21a+6b=46,3, odtud najdeme
a = 0,98 b = 4,3.

No a v práci jsme se hlásili na inspekci, článek se psal doma na konferenci - teď můžeme psát na blog. Zatímco jsem zpracovával svá data, uvědomil jsem si, že si nemohu pomoci a napsat o velmi skvělém a nezbytném doplňku v Excelu s názvem . Č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) k hledání neznámých koeficientů rovnic při 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 objeví, vyberte „Vyhledat řešení“ a klikněte na „Přejít“.

3. V dalším okně zaškrtněte políčko vedle „hledat řešení“ a klikněte na „OK“.

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

Metoda nejmenších čtverců

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

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

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

Vezmu super jednoduchý příklad (viz obrázek).

Není nad to, že body jsou umístěny za sebou jakoby na 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ň jsme si naprosto jisti, ž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 „hledání řešení“.

Metoda spočívá v tom, že (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 je minimální. To znamená, že když X1=1 skutečná naměřená hodnota Y1=4,6 a vypočtené y1=f (x1) se rovná 4, druhá mocnina rozdílu bude (y1-Y1)^2=(4-4,6)^ 2 = 0,36. Je to stejné s následujícím: když X2=2, skutečná 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í" .

Použití doplňku k nalezení řešení

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

2. Do buňky A1 zadejte hodnotu „1“. Tato jednotka bude první aproximací ke skutečné hodnotě koeficientu (k) našeho funkčního vztahu y=kx.

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

4. Domníváme se, že koeficient k je roven jedné a funkce f (x)=y=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 provést ručně zadáním odpovídajících odkazů na buňky do vzorce: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... atd. Nakonec uděláme chybu a uvědomíme si, že jsme promarnili spoustu času. Pro výpočet součtu druhých mocnin rozdílů v Excelu existuje speciální vzorec „SUMQUARRENT“, který vše udělá za nás. Zadejte jej do buňky A2 a nastavte 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 (tu s koeficientem k) jako buňku, kterou chcete změnit.

6. Vyberte buňku A2 jako cíl a nastavte podmínku „nastavit rovno minimální hodnotě“. Pamatujeme si, že je to buňka, kde počítáme součet druhých mocnin rozdílů mezi vypočtenými a naměřenými hodnotami a tento součet by měl být minimální. Klikněte na „provést“.

7. Byl zvolen koeficient k. Nyní si můžete ověřit, že vypočítané 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í popisovat data pomocí lineárních, exponenciálních, mocninných a polynomiálních funkcí, takže se často obejdete bez doplňky „hledat řešení“.. O všech těchto aproximačních metodách jsem mluvil ve svém, 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 nemohl přijít v lepší čas.

Doplněk pro hledání ř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 zadána podmínka pro výběr neznámého parametru.
To je vše! V příštím článku vám povím pohádku o dovolené, takže abyste nezmeškali zveřejnění článku,

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

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

LINEST(Hodnoty_y; x-hodnoty; Const; statistika),

Hodnoty_y- pole hodnot y,

x-hodnoty- volitelné pole hodnot X, pokud pole X je vynecháno, předpokládá se, že se jedná o pole (1;2;3;...) stejné velikosti jako Hodnoty_y,

Const- booleovská hodnota, která udává, zda je konstanta vyžadována b byla rovna 0. Pokud Const má význam SKUTEČNÝ nebo pak vynecháno b se počítá běžným způsobem. Pokud argument Const je tedy NEPRAVDA b předpokládá se 0 a hodnoty A jsou vybrány tak, aby byl vztah splněn y=ax.

Statistika je logická 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 LHÁT nebo vynechán, pak funkce LINEST vrátí pouze koeficient A a konstantní 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í linie 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, Pojďme do průvodce funkcí a do kategorie Statistický vyberte funkci LINEST. Vyplníme 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 v buňce C6 byl zaveden následující vzorec:

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

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

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

a zkopírujte jej do rozsahu C5: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. Do grafu experimentálních dat bude přidána regresní přímka. Ve výchozím nastavení bude jeho graf vykreslen jako body 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 rozptylového diagramu, jak je znázorněno na obr. 7.

Typ čáry, barvu a tloušťku lze změnit následovně. Vyberte čáru v diagramu, klikněte pravým tlačítkem a vyberte příkaz v kontextové nabídce Formát datové řady... Dále proveďte nastavení, například jak je znázorněno na Obr. 8.

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ýsledkem experimentu byla stanovena určitá tabulková závislost.

Vyberte a sestrojte aproximující závislost. Sestavte grafy tabulkových a vybraný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, sestavení bodového grafu a přidání trendové čáry do tohoto grafu.

Podívejme se na tento proces podrobně. Zadáme počáteční data do listu a vyneseme experimentální data. Dále vyberte experimentální body na 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.

Na druhém (obr. 12) jsou určeny parametry konstrukce:

· název aproximující závislosti;

· předpověď dopředu (dozadu) o n jednotky (tento parametr určuje, o kolik jednotek dopředu (dozadu) je třeba prodloužit trendovou linii);

zda zobrazit průsečík křivky s přímkou y=konst;

· zobrazit aproximační funkci na diagramu nebo ne (možnost zobrazit rovnici na diagramu);

· zda do diagramu umístit hodnotu směrodatné odchylky či nikoliv (možnost umístit do diagramu hodnotu aproximační spolehlivosti).

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

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

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

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

· exponenciální y=a∙e b,

· uklidnit 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í filtrace.

4.3. Použití řešitelského bloku

Významnou zajímavostí je implementace v MS Excel výběru parametrů metodou nejmenších čtverců pomocí řešitelského bloku. Tato technika umožňuje vybrat parametry funkce libovolného typu. Zvažme tuto možnost pomocí následujícího problému jako příkladu.

PŘÍKLAD 3. Jako výsledek experimentu byla získána závislost z(t), uvedená v tabulce

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

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

Uvažujme proces řešení optimalizační úlohy (obr. 14).

Nechte hodnoty A, V, S, D A NA uloženy v buňkách A7:E7. Pojďme vypočítat teoretické hodnoty funkce Z(t)=Při 4 +Bt 3 +Ct 2 +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írujeme tento vzorec do rozsahu C4: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 Představme si 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 C5:J5. V buňce F7 uložíme celkovou druhou mocninu chyby (10). Chcete-li to provést, zadejte vzorec:

F7 = SUM(B5:J5).

Použijme příkaz Service®Hledat řešení a řešit optimalizační problém bez omezení. Vyplňte odpovídajícím způsobem vstupní pole v dialogovém okně znázorněném na Obr. 14 a stiskněte tlačítko Vykonat. Pokud je nalezeno řešení, okno zobrazené na Obr. 15.

Výsledek rozhodovacího bloku bude odeslán do buněk A7:E7hodnoty parametrů funkcí Z(t)=Při 4 +Bt 3 +Ct 2 +Dt+K. V buňkách B4:J4 dostaneme očekávaná hodnota funkce ve výchozích bodech. V buňce F7 budou uloženy celková kvadratická chyba.

Výběrem rozsahu můžete zobrazit experimentální body a přizpůsobenou čáru v jedné grafické ploše B2:J4, volání Průvodce grafem a poté formátovat vzhled přijaté grafy.

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

5. REFERENCE

1. Alekseev E.R., Chesnokova O.V., Řešení problémů výpočetní matematiky v balíčcích Mathcad12, MATLAB7, Maple9. – NT Press, 2006.–596 s. :il. -(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 s.

3. Berezin I.S., Zhidkov N.P., Computational methods. – M.: Nauka, 1966. – 632 s.

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

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

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

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.