Метад найменшых квадратаў ўяўляе сабой матэматычную працэдуру пабудовы лінейнага ўраўненні, якое б найбольш дакладна адпавядала набору двух шэрагаў лікаў. Мэтай прымянення дадзенага спосабу з'яўляецца мінімізацыя агульнай квадратычнай памылкі. У праграме Excel маюцца інструменты, з дапамогай якіх можна ўжываць дадзены метад пры вылічэннях. Давайце разбярэмся, як гэта робіцца.
Выкарыстанне метаду ў Эксэля
Метад найменшых квадратаў (МНК) з'яўляецца матэматычным апісаннем залежнасці адной зменнай ад другой. Яго можна выкарыстоўваць пры прагназаванні.
Ўключэнне надбудовы «Пошук рашэнні»
Для таго, каб выкарыстоўваць МНК ў Эксэля, трэба ўключыць надбудову «Пошук рашэнні», Якая па змаўчанні адключаная.
- Пераходзім ва ўкладку "Файл".
- Клікаем па найменні раздзела "Параметры".
- У якое адкрылася акне спыняем выбар на падраздзеле "Надбудовы".
- У блоку "Упраўленне", Які размешчаны ў ніжняй частцы акна, усталёўваем перамыкач у пазіцыю "Надбудовы Excel" (Калі ў ім выстаўлена іншае значэнне) і ціснем на кнопку "Перайсці ...".
- Адкрываецца невялікае акенца. Ставім у ім галачку каля параметру "Пошук рашэнні". Ціснем на кнопку "OK".
цяпер функцыя пошук рашэння у Excel актываваная, а яе інструменты з'явіліся на стужцы.
Урок: Пошук рашэнні ў Эксэля
ўмовы задачы
Апішам прымяненне МНК на канкрэтным прыкладзе. Маем два рады лікаў x і y, Паслядоўнасць якіх прадстаўлена на малюнку ніжэй.
Найбольш дакладна дадзеную залежнасць можа апісаць функцыя:
y = a + nx
Пры гэтым, вядома што пры x = 0 y таксама роўна 0. Таму дадзенае раўнанне можна апісаць залежнасцю y = nx.
Нам трэба будзе знайсці мінімальную суму квадратаў рознасці.
рашэнне
Пяройдзем да апісання непасрэднага прымянення метаду.
- Злева ад першага значэння x ставім лічбу 1. Гэта будзе набліжаная велічыня першага значэння каэфіцыента n.
- Справа ад слупка y дадаем яшчэ адну калонку - nx. У першую вочка дадзенага слупка запісваем формулу множання каэфіцыента n на вочка першай зменнай x. Пры гэтым, спасылку на поле з каэфіцыентам робім абсалютнай, так як гэта значэнне мяняцца не будзе. Клікаем па кнопцы Enter.
- Выкарыстоўваючы маркер запаўнення, які капіюецца дадзеную формулу на ўвесь дыяпазон табліцы ў Стоўбцах ніжэй.
- У асобнай вочку вылічваем суму рознасцяў квадратаў значэнняў y і nx. Для гэтага клікаем па кнопцы "Уставіць функцыю".
- У якое адкрылася «Майстру функцый» шукаем запіс "СУММКВРАЗН". Выбіраем яе і ціснем на кнопку "OK".
- Адкрываецца акно аргументаў. У полі "Массив_x" ўводзім дыяпазон вочак слупка y. У полі "Массив_y" ўводзім дыяпазон вочак слупка nx. Для таго, каб увесці значэння, проста усталёўваем курсор у полі і вылучаем адпаведны дыяпазон на лісце. Пасля ўводу ціснем на кнопку "OK".
- Пераходзім ва ўкладку "Дадзеныя". На стужцы ў блоку інструментаў "Аналіз" ціснем на кнопку "Пошук рашэнні".
- Адкрываецца акно параметраў дадзенага інструмента. У полі "Аптымізаваць мэтавую функцыю" паказваем адрас вочка з формулай "СУММКВРАЗН". У параметры "Да" абавязкова выстаўляем перамыкач у пазіцыю "Мінімум". У полі "Змяняючы вочкі" паказваем адрас са значэннем каэфіцыента n. Ціснем на кнопку "Знайсці рашэнне".
- Рашэнне будзе адлюстроўвацца ў вочку каэфіцыента n. Менавіта гэта значэнне будзе з'яўляцца найменшай квадратам функцыі. Калі вынік задавальняе карыстальніка, то варта націснуць на кнопку "OK" ў дадатковым акне.
Як бачым, прымяненне метаду найменшых квадратаў даволі складаная матэматычная працэдура. Мы паказалі яе ў дзеянні на найпростым прыкладзе, а існуюць значна больш складаныя выпадкі. Зрэшты, інструментар Microsoft Excel закліканы максімальна спрасціць вырабляюцца вылічэнні.
