Метад найменшых квадратаў у Excel

Метад найменшых квадратаў ўяўляе сабой матэматычную працэдуру пабудовы лінейнага ўраўненні, якое б найбольш дакладна адпавядала набору двух шэрагаў лікаў. Мэтай прымянення дадзенага спосабу з'яўляецца мінімізацыя агульнай квадратычнай памылкі. У праграме Excel маюцца інструменты, з дапамогай якіх можна ўжываць дадзены метад пры вылічэннях. Давайце разбярэмся, як гэта робіцца.

Выкарыстанне метаду ў Эксэля

Метад найменшых квадратаў (МНК) з'яўляецца матэматычным апісаннем залежнасці адной зменнай ад другой. Яго можна выкарыстоўваць пры прагназаванні.

Ўключэнне надбудовы «Пошук рашэнні»

Для таго, каб выкарыстоўваць МНК ў Эксэля, трэба ўключыць надбудову «Пошук рашэнні», якая па змаўчанні адключаная.

1. Пераходзім ва ўкладку «Файл».



2. Клікаем па найменні часткі «Параметры».



3. У якое адкрылася акне спыняем выбар на падраздзеле «Надбудовы».



4. У блоку «Упраўленне», які размешчаны ў ніжняй частцы акна, усталёўваем перамыкач у пазіцыю «Надбудовы Excel» (калі ў ім выстаўлена іншае значэнне) і ціснем на кнопку «Перайсці ...».



5. Адкрываецца невялікае акенца. Ставім у ім галачку каля параметру «Пошук рашэнні». Ціснем на кнопку «OK».

Цяпер функцыя Пошук рашэнні ў Excel актываваная, а яе інструменты з'явіліся на стужцы.

Урок: Пошук рашэнні ў Эксэля

ўмовы задачы

Апішам прымяненне МНК на канкрэтным прыкладзе. Маем два рады лікаў x і y , Паслядоўнасць якіх прадстаўлена на малюнку ніжэй.

Найбольш дакладна дадзеную залежнасць можа апісаць функцыя:

y = a + nx

Пры гэтым, вядома што пры x = 0 y таксама роўна 0 . Таму дадзенае раўнанне можна апісаць залежнасцю y = nx.

Нам трэба будзе знайсці мінімальную суму квадратаў рознасці.

рашэнне

Пяройдзем да апісання непасрэднага прымянення метаду.

1. Злева ад першага значэння x ставім лічбу 1 . Гэта будзе набліжаная велічыня першага значэння каэфіцыента n.



2. Справа ад слупка y дадаем яшчэ адну калонку - nx. У першую вочка дадзенага слупка запісваем формулу множання каэфіцыента n на вочка першай зменнай x . Пры гэтым, спасылку на поле з каэфіцыентам робім абсалютнай, так як гэта значэнне мяняцца не будзе. Клікаем па кнопцы Enter.



3. Выкарыстоўваючы маркер запаўнення, які капіюецца дадзеную формулу на ўвесь дыяпазон табліцы ў Стоўбцах ніжэй.



4. У асобнай вочку вылічваем суму рознасцяў квадратаў значэнняў y і nx . Для гэтага клікаем па кнопцы «Уставіць функцыю».



5. У якое адкрылася «Майстру функцый» шукаем запіс «СУММКВРАЗН». Выбіраем яе і ціснем на кнопку «OK».



6. Адкрываецца акно аргументаў. У полі «Массив_x» ўводзім дыяпазон вочак слупка y . У полі «Массив_y» ўводзім дыяпазон вочак слупка nx . Для таго, каб увесці значэння, проста усталёўваем курсор у полі і вылучаем адпаведны дыяпазон на лісце. Пасля ўводу ціснем на кнопку «OK».



7. Пераходзім ва ўкладку «Дадзеныя». На стужцы ў блоку інструментаў «Аналіз» ціснем на кнопку «Пошук рашэнні».



8. Адкрываецца акно параметраў дадзенага інструмента. У полі «Аптымізаваць мэтавую функцыю» паказваем адрас вочка з формулай «СУММКВРАЗН». У параметры «Да» абавязкова выстаўляем перамыкач у пазіцыю «Мінімум». У полі «Змяняючы вочкі» паказваем адрас са значэннем каэфіцыента n . Ціснем на кнопку «Знайсці рашэнне».



9. Рашэнне будзе адлюстроўвацца ў вочку каэфіцыента n . Менавіта гэта значэнне будзе з'яўляцца найменшай квадратам функцыі. Калі вынік задавальняе карыстальніка, то варта націснуць на кнопку «OK» ў дадатковым акне.

Як бачым, прымяненне метаду найменшых квадратаў даволі складаная матэматычная працэдура. Мы паказалі яе ў дзеянні на найпростым прыкладзе, а існуюць значна больш складаныя выпадкі. Зрэшты, інструментар Microsoft Excel закліканы максімальна спрасціць вырабляюцца вылічэнні.