Metóda najmenších štvorcov v programe Excel

Metóda najmenších štvorcov je matematický postup na vytvorenie lineárnej rovnice, ktorá by najpresnejšie zodpovedala množine dvoch radov čísel. Účelom použitia tejto metódy je minimalizovať celkovú kvadratickú chybu. Program Excel má nástroje, s ktorými môže byť táto metóda aplikovaná pri výpočte. Zaoberáme sa tým, ako sa to robí.

Použitie metódy v programe Excel

Metóda najmenších štvorcov (MNC) je matematický opis závislosti jednej premennej z druhej. Môže sa použiť pri predpovedaní.

Povolenie doplnku "Solution Sledovanie"

Aby ste mohli používať MNA v programe Excel, musíte povoliť add-on "Solution Search", ktorý je štandardne zakázaný.

1. Prejdite na kartu "Súbor".



2. Kliknite na časť "Parametre".



3. V okne, ktoré sa otvorí, zastavte výber na pododdiele "Add-In".



4. V bloku "Management", ktorý sa nachádza v dolnej časti okna, nastavte prepínač na polohu "Excel Add-In" (ak je nastavená iná hodnota) a kliknite na tlačidlo "Go ...".



5. Otvorí sa malé okno. Vložíme do toho a začiarknite políčko v blízkosti parametra "Solution Search". Kliknite na tlačidlo "OK".

Teraz je aktivovaná funkcia funkcie riešenia v programe Excel a jeho nástroje sa objavili na páske.

Lekcia: Hľadať riešenia v exteriéri

Podmienky problému

Osobitne opisujeme použitie MNK. Máme dva riadky čísel X. a y. Sekvencia, ktorá je uvedená na obrázku nižšie.

Najpresnejšie závislosť môže opísať funkciu:

y = a + nx

Zároveň je známe, že keď x = 0. y. Rovnaký 0 . Preto táto rovnica môže byť opísaná závislosť y = nx..

Musíme nájsť minimálny súčet štvorcov rozdielu.

Riešenie

Poďme na opis priamej aplikácie metódy.

1. Vľavo od prvého významu X. digitovať 1 . Bude to približná hodnota prvej hodnoty koeficientu N..



2. Vpravo od stĺpca y. Pridajte ďalší stĺpec - NX. V prvej bunke tohto stĺpca napíšte vzorec násobenie koeficientu N. na bunke prvej premennej X. . Súčasne sa odkaz na pole s koeficientom absolútne, pretože táto hodnota sa nezmení. Kliknite na tlačidlo ENTER.



3. Pomocou výplne, skopírujte tento vzorec na celý rozsah tabuľky v stĺpci nižšie.



4. V samostatnej bunke vypočítajte súčtu rozdielu v štvorcoch hodnôt y. a Nx. . Ak to chcete urobiť, kliknite na tlačidlo "Vložiť funkciu".



5. V "Majstrovi funkcií", ktorý sa otvára, hľadám záznam "Summkvson". Vyberte ho a kliknite na tlačidlo "OK".



6. Otvorí sa okno argumentu. V poli "Massive_X" zastupujeme do rozsahu buniek stĺpcov y. . V poli "Massive_y" zastupujeme do rozsahu buniek stĺpcov Nx. . Ak chcete zadať hodnoty, jednoducho nainštalujte kurzor do poľa a vyberte príslušný rozsah na hárku. Po zadaní stlačte tlačidlo "OK".



7. Prejdite na kartu "Údaje". Na páske v bloku "Analýza" Kliknite na tlačidlo "Solution Search".



8. Otvorí sa okno parametra. V poli "Optimalizácia Cieľová funkcia" zadajte adresu bunky so vzorcom "Summkvson". V parametri "BC", nastavíte prepínač do polohy "Minimálna". V poli "Zmena bunky" zadajte adresu s hodnotou koeficientu N. . Kliknite na tlačidlo "Nájsť Riešenie".



9. Riešenie sa zobrazí v bunke koeficientu N. . Táto hodnota bude najmenším štvorcom funkcie. Ak výsledok spĺňa používateľa, potom kliknite na tlačidlo "OK" v možnosti.

Ako vidíte, použitie metódy najmenšieho metodiky je pomerne komplikovaný matematický postup. Ukázali sme ho v akcii na najjednoduchšom príklade a existujú oveľa zložitejšie prípady. Avšak, Microsoft Excel Toolkit je navrhnutý tak, aby zjednodušil výpočty.