Застосування методу найменших квадратів у Excel. Метод найменших квадратів у Excel

Метод найменших квадратів (МНК) належить до сфери регресійного аналізу. Він має безліч застосувань, оскільки дозволяє здійснювати наближене уявлення заданої функції іншими більш простими. МНК може виявитися надзвичайно корисним при обробці спостережень і його активно використовують для оцінки одних величин за результатами вимірювань інших, що містять випадкові помилки. З цієї статті ви дізнаєтеся, як реалізувати обчислення методом найменших квадратів в Excel.

Постановка задачі на конкретному прикладі

Припустимо, є два показника X і Y. Причому Y залежить від X. Так як МНК цікавить нас з погляду регресійного аналізу (в Excel його методи реалізуються за допомогою вбудованих функцій), то відразу ж перейти до розгляду конкретної задачі.

Отже, нехай X — торгова площа продовольчого магазину, що вимірюється в квадратних метрах, а Y - Річний товарообіг, що визначається в мільйонах рублів.

Потрібно зробити прогноз, який товарообіг (Y) матиме магазин, якщо в нього та чи інша торгова площа. Очевидно, що функція Y = f(X) зростаюча, оскільки гіпермаркет продає більше товарів, ніж ларьок.

Декілька слів про коректність вихідних даних, що використовуються для передбачення

Припустимо, ми маємо таблицю, побудовану за даними для n магазинів.

Згідно математичної статистики, результати будуть більш-менш коректними, якщо досліджуються дані щодо хоча б 5-6 об'єктів. Крім того, не можна використовувати "аномальні" результати. Зокрема, невеликий елітний бутік може мати товарообіг у рази більший, ніж товарообіг великих торгових точок класу «масмаркет».

Суть методу

Дані таблиці можна зобразити на декартовій площині у вигляді точок M 1 (x 1 y 1), … M n (x n y n). Тепер розв'язання задачі зведеться до підбору апроксимуючої функції y = f(x), що має графік, що проходить якомога ближче до точок M1, M2,.. Mn.

Звичайно, можна використовувати багаточлен високого ступеня, але такий варіант не тільки важко реалізувати, а й просто некоректний, тому що не відображатиме основну тенденцію, яку і потрібно виявити. Найрозумнішим рішенням є пошук прямої у = ax + b, яка найкраще наближає експериментальні дані, a точніше, коефіцієнтів – a та b.

Оцінка точності

При будь-якій апроксимації особливої важливості набуває оцінка її точності. Позначимо через e i різницю (відхилення) між функціональними та експериментальними значеннями для точки x i , тобто e i = y i - f (x i).

Очевидно, що для оцінки точності апроксимації можна використовувати суму відхилень, тобто при виборі прямої для наближеного уявлення залежності X від Y потрібно віддавати перевагу тій, у якої найменше значеннясуми e i у всіх точках, що розглядаються. Однак, не все так просто, тому що поряд із позитивними відхиленнями практично будуть присутні і негативні.

Вирішити питання можна, використовуючи модулі відхилень або їх квадрати. Останній метод набув найбільш широкого поширення. Він використовується в багатьох областях, включаючи регресійний аналіз (в Excel його реалізація здійснюється за допомогою двох вбудованих функцій) і давно довів свою ефективність.

Метод найменших квадратів

В Excel, як відомо, існує вбудована функція автосуми, що дозволяє обчислити значення всіх значень, які розташовані у виділеному діапазоні. Таким чином, ніщо не завадить нам розрахувати значення виразу (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

У математичному записі це має вигляд:

Оскільки спочатку було прийнято рішення про апроксимування за допомогою прямої, то маємо:

Таким чином, завдання знаходження прямої, яка найкраще описує конкретну залежність величин X та Y, зводиться до обчислення мінімуму функції двох змінних:

Для цього потрібно прирівняти до нуля приватні похідні за новими змінними a і b, і вирішити примітивну систему, що складається з двох рівнянь з двома невідомими видами:

Після нехитрих перетворень, включаючи поділ на 2 та маніпуляції із сумами, отримаємо:

Вирішуючи її, наприклад, методом Крамера, отримуємо стаціонарну точку з деякими коефіцієнтами a* та b*. Це і є мінімум, тобто для передбачення, який товарообіг буде у магазину при певній площі, підійде пряма y = a * x + b * , Що являє собою регресійну модель для прикладу, про який йдеться. Звичайно, вона не дозволить знайти точний результат, але допоможе одержати уявлення про те, чи окупиться покупка в кредит магазину конкретної площі.

Як реалізувати метод найменших квадратів в Excel

У "Ексель" є функція для розрахунку значення МНК. Вона має такий вигляд: «ТЕНДЕНЦІЯ» (відоме значення Y; відоме значення X; нові значення X; конст.). Застосуємо формулу розрахунку МНК Excel до нашої таблиці.

Для цього в комірку, в якій має бути відображено результат розрахунку за методом найменших квадратів в Excel, введемо знак = і виберемо функцію ТЕНДЕНЦІЯ. У вікні заповнимо відповідні поля, виділяючи:

діапазон відомих значень для Y (у разі дані для товарообігу);
діапазон x 1, … x n, тобто величини торгових площ;
і відомі, і невідомі значення x, для якого потрібно з'ясувати розмір товарообігу (інформацію про їхнє розташування на робочому аркуші див. далі).

Крім того, у формулі є логічна змінна «Конст». Якщо ввести у відповідне їй поле 1, це означатиме, що слід здійснити обчислення, вважаючи, що b = 0.

Якщо потрібно дізнатися прогноз більш ніж одного значення x, то після введення формули слід натиснути не на «Введення», а потрібно набрати на клавіатурі комбінацію «Shift» + «Control» + «Enter» («Введення»).

Деякі особливості

Регресійний аналізможе бути доступний навіть чайникам. Формула Excel для передбачення значення масиву невідомих змінних – «ТЕНДЕНЦІЯ» – може використовуватися навіть тими, хто ніколи не чув про метод найменших квадратів. Достатньо просто знати деякі особливості її роботи. Зокрема:

Якщо розташувати діапазон відомих значень змінної y в одному рядку або стовпці, то кожен рядок (стовпець) з відомими значеннями x сприйматиметься програмою як окрема змінна.
Якщо у вікні «ТЕНДЕНЦІЯ» не вказаний діапазон з відомими x, то у разі використання функції Excel програма буде розглядати його як масив, що складається з цілих чисел, кількість яких відповідає діапазону із заданими значеннями змінної y.
Щоб отримати на виході масив "передбачених" значень, вираз для обчислення тенденції потрібно вводити як формулу масиву.
Якщо не вказано нових значень x, то функція «ТЕНДЕНЦІЯ» вважає їх рівним відомим. Якщо вони не задані, то як аргумент береться масив 1; 2; 3; 4;…, який пропорційний діапазону з вже заданими параметрами y.
Діапазон, що містить нові значення x, повинен складатися з такої ж чи більшої кількості рядків або стовпців, як діапазон із заданими значеннями y. Іншими словами він має бути пропорційним незалежним змінним.
У масиві з відомими значеннями x може бути кілька змінних. Однак якщо йдеться лише про одну, то потрібно, щоб діапазони із заданими значеннями x та y були пропорційні. У випадку кількох змінних потрібно, щоб діапазон із заданими значеннями y містився в одному стовпчику або в одному рядку.

Функція «ПЕРЕДСКАЗ»

Регресійний аналіз у Excel реалізується за допомогою кількох функцій. Одна з них називається «Предказ». Вона аналогічна «ТЕНДЕНЦІЇ», тобто видає результат обчислень методом найменших квадратів. Однак лише для одного X, для якого невідомо значення Y.

Тепер ви знаєте формули в Excel для чайників, що дозволяють спрогнозувати величину майбутнього значення того чи іншого показника згідно з лінійним трендом.

Ну ось, на роботі перед інспекцією прозвітували, статтю будинку для конференції написано — можна тепер і в блог писати. Поки свої дані обробляв, зрозумів, що не можу не написати про дуже класну і потрібну надбудову в Excel, яка називається . Так що стаття буде присвячена саме цій надбудові, і розповім про неї на прикладі використання методу найменших квадратів(МНК) для пошуку невідомих коефіцієнтів рівняння під час опису експериментальних даних.

Як увімкнути надбудову «пошук рішення»

Спочатку розберемося, як цю надбудову включити.

1. Йдемо в меню «Файл» та вибираємо пункт «Параметри Excel»

2. У вікні вибираємо «Пошук рішення» і натискаємо «перейти».

3. У наступному вікні ставимо галочку навпроти пункту «пошук рішення» та натискаємо «ОК».

4. Надбудова активована — тепер її можна знайти в пункті меню «Дані».

Метод найменших квадратів

Тепер коротко про метод найменших квадратів (МНК) та про те, де його можна застосовувати.

Допустимо, у нас є набір даних після здійснення нами якогось експерименту, де ми вивчали вплив величини Х на величину Y.

Ми хочемо цей вплив описати математично, щоб потім цією формулою користуватися і знати, що, якщо ми поміняємо величину Х на стільки-то, отримаємо величину Y...

Візьму супер-простий приклад (див. мал.).

Їжаку зрозуміло, що точки розташувалися одна за одною ніби по прямій, а тому ми сміливо припускаємо, що наша залежність описується лінійною функцією y=kx+b. При цьому ми точно впевнені, що за X рівному нулю значення Y теж дорівнює нулю. Отже, функція, що описує залежність, буде ще простіше: y=kx (згадуємо шкільну програму).

Загалом, нам належить знайти коефіцієнт k. Ось це ми і зробимо за допомогою МНК із застосуванням надбудови «пошук рішення».

Метод полягає в тому, щоб (тут - увага: треба вдуматися) сума квадратів різниць експериментально отриманих та відповідних розрахункових значень була мінімальною. Тобто коли X1=1 реально виміряне значення Y1=4,6, а розрахункове y1=f (x1) одно 4, квадрат різниці буде (y1-Y1)^2=(4-4,6)^2=0,36 . З такими як і: коли X2=2, реально виміряне значення Y2=8,1, а розрахункове у2 дорівнює 8, квадрат різниці буде (y2-Y2)^2=(8-8,1)^2=0,01. І сума всіх цих квадратів має бути мінімально можливою.

Отже, приступимо до тренування з використання МНК та надбудови Excel «пошук рішення» .

Застосування надбудови пошук рішення

1. Якщо не включили надбудову «пошук рішення», то повертаємось до пункту Як увімкнути надбудову «пошук рішення» і вмикаємо 🙂

2. У комірку А1 введемо значення «1». Ця одиниця буде першим наближенням до реального значення коефіцієнта (k) нашої функціональної залежності y = kx.

3. У стовпці B у нас розташувалися значення параметра X, у стовпці C — значення параметра Y. У комірках стовпця D вводимо формулу: «коефіцієнт k помножити значення X». Наприклад, в комірці D1 вводимо = A1 * B1, в комірці D2 вводимо "= A1 * B2" і т.д.

4. Ми вважаємо, що коефіцієнт до дорівнює одиниціі функція f (x) = у = 1 * х - це перше наближення до нашого рішення. Можемо розрахувати суму квадратів різниць між виміряними значеннями величини Y та розрахованими за формулою y=1*х. Можемо все це зробити вручну, вбиваючи у формулу відповідні посилання на комірки: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... і т.д. помиляємось і розуміємо, що втратили купу часу.В Excel для розрахунку суми квадратів різниць є спеціальна формула, «СУМКВРАЗН», яка все за нас і зробить.Введемо її в комірку А2 і задаємо вихідні дані: діапазон виміряних значень Y (стовпець C) і діапазон розрахованих значень Y (стовпець D).

4. Суму різниць квадратів розрахували – тепер йдемо у вкладку «Дані» та обираємо «Пошук рішення».

5. У меню, що змінюється, вибираємо комірку A1 (та, що з коефіцієнтом k).

6. Як цільовий вибираємо комірку A2 і задаємо умову «встановити рівною мінімальному значенню». Пам'ятаємо, що це осередок, де у нас проводиться розрахунок суми квадратів різниць розрахункового та виміряного значень, і сума ця має бути мінімальною. Натискаємо "виконати".

7. Коефіцієнт k підібраний. Тепер можна переконатися, що розраховані значення тепер дуже близькі до виміряних.

P.S.

Взагалі, звичайно, для апроксимації експериментальних даних в Excel існують спеціальні інструменти, які дозволяють здійснювати опис даних за допомогою лінійної, експоненційної, статечної та поліноміальної функцією, тому часто можна обійтися і без адбудови «пошук рішення». Про всі ці способи апроксимації я розповідав у своєму, так що якщо цікаво, подивіться. А от коли справа стосується якоїсь екзотичної функції з одним невідомим коефіцієнтомабо задач оптимізації, то тут надбудовадуже до речі.

Надбудову «пошук рішення»можна використовувати і для інших завдань, головне — зрозуміти суть: є осередок, де ми підбираємо значення, а є цільовий осередок, в якому встановлено умову для підбору невідомого параметра.
От і все! У наступній статті розповім казку про відпустку, так що, щоб не пропустити вихід статті,

4.1. Використання вбудованих функцій

Обчислення коефіцієнтів регресіїздійснюється за допомогою функції

Лінейн(Значення_y; Значення_x; Конст; статистика),

Значення_y- масив значень y,

Значення_x- необов'язковий масив значень x, якщо масив хопущений, то передбачається, що це масив (1; 2; 3; ...) такого ж розміру, як і Значення_y,

Конст- логічне значення, яке вказує, чи потрібно, щоб константа bдорівнювала 0. Якщо Констмає значення ІСТИНАабо опущено, то bобчислюється звичайним чином. Якщо аргумент Констмає значення БРЕХНЯ, то bналежить рівним 0 і значення aпідбираються так, щоб виконувалось співвідношення y=ax.

Статистика- логічне значення, яке вказує, чи потрібно повернути додаткову статистику щодо регресії. Якщо аргумент Статистикамає значення ІСТИНА, то функція Лінейнповертає додаткову регресійну статистику. Якщо аргумент Статистикамає значення Брехняабо опущений, то функція Лінейнповертає лише коефіцієнт aта постійну b.

Необхідно пам'ятати, що результатом функцій ЛІНІЙН()є безліч значень масив.

Для розрахунку коефіцієнта кореляціївикористовується функція

Корел(Масив1;Масив2),

повертає значення коефіцієнта кореляції, де Масив1- масив значень y, Масив2- масив значень x. Масив1і Масив2мають бути однієї розмірності.

ПРИКЛАД 1. Залежність y(x) представлена у таблиці. Побудувати лінію регресіїта обчислити коефіцієнт кореляції.

y		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Введемо таблицю значень у аркуш MS Excel і побудуємо точковий графік. Робочий лист набуде вигляду зображеного на рис. 2.

Щоб розрахувати значення коефіцієнтів регресії аі bвиділення ячейки A7:B7,звернімося до майстра функцій та в категорії Статистичніоберемо функцію Лінейн. Заповнимо діалогове вікно, що з'явилося так, як показано на рис. 3 і натиснемо ОK.

В результаті обчислене значення з'явиться тільки в осередку A6(Рис.4). Для того щоб значення з'явилося і в осередку B6необхідно увійти в режим редагування (клавіша F2), а потім натиснути комбінацію клавіш CTRL+SHIFT+ENTER.

Для розрахунку значення коефіцієнта кореляції в комірку С6було введено таку формулу:

С7=КОРРЕЛ(B3:J3;B2:J2).

Знаючи коефіцієнти регресії аі bобчислимо значення функції y=ax+bдля заданих x. Для цього введемо формулу

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

і скопіюємо її в діапазон С5:J5(Рис. 5).

Зобразимо лінію регресії на діаграмі. Виділимо експериментальні точкина графіку, клацніть правою кнопкою миші та оберемо команду Початкові дані. У діалоговому вікні, що з'явилося (рис. 5) виберемо вкладку Ряді клацніть по кнопці Додати. Заповнимо поля введення, оскільки показано на рис. 6 і натиснемо кнопку ОК. До графіку експериментальних даних буде додано лінію регресії. За замовчуванням її графік буде зображений у вигляді точок, не з'єднаних лініями, що згладжують.

Щоб змінити вигляд лінії регресії, виконаємо наведені нижче дії. Клацніть правою кнопкою миші по точках, що зображають графік лінії, виберемо команду Тип діаграмиі встановимо вид точкової діаграми, оскільки показано на рис. 7.

Тип лінії, її колір та товщину можна змінити наступним чином. Виділити лінію на діаграмі, натиснути праву кнопку миші та у контекстному меню вибрати команду Формат рядів даних…Далі зробити установки, наприклад, оскільки показано на рис. 8.

В результаті всіх перетворень отримаємо графік експериментальних даних та лінію регресії в одній графічній галузі (рис. 9).

4.2. Використання лінії тренду.

Побудова різних апроксимуючих залежностей у MS Excel реалізовано як властивості діаграми – лінія тренду.

ПРИКЛАД 2. В результаті експерименту було визначено деяку табличну залежність.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Вибрати та побудувати апроксимуючу залежність. Побудувати графіки табличної та підібраної аналітичної залежності.

Розв'язання задачі можна розбити на такі етапи: введення вихідних даних, побудова точкового графіка та додавання до цього графіка лінії тренду.

Розглянемо цей процес докладно. Введемо вихідні дані у робочий лист і побудуємо графік експериментальних даних. Далі виділимо експериментальні точки на графіку, клацніть правою кнопкою миші та скористаємося командою Додатил інію тренду(Рис. 10).

Діалогове вікно, що з'явилося, дозволяє побудувати апроксимуючу залежність.

На першій вкладці (рис. 11) цього вікна вказується вид апроксимуючої залежності.

На другому (рис. 12) визначаються параметри побудови:

· Назва апроксимуючої залежності;

· Прогноз вперед (назад) на nодиниць (цей параметр визначає, скільки одиниць вперед (назад) необхідно продовжити лінію тренда);

· Чи показувати точку перетину кривої з прямою y=const;

· показувати апроксимуючу функцію на діаграмі чи ні (параметр показувати рівняння на діаграмі);

· поміщати чи діаграму величину среднеквадратичного відхилення чи ні (параметр помістити на діаграму величину достовірності апроксимації).

Виберемо як апроксимуючу залежність поліном другого ступеня (рис. 11) і виведемо рівняння, що описує цей поліном на графік (рис. 12). Отримана діаграма представлена на рис. 13.

Аналогічно за допомогою лінії трендуможна підібрати параметри таких залежностей як

· Лінійна y=a∙x+b,

· логарифмічна y=a∙ln(x)+b,

· Експонентна y=a∙e b,

· статечна y=a∙x b,

· поліноміальна y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+dі так далі, до полінома 6-го ступеня включно,

· Лінійна фільтрація.

4.3. Використання інструмента для аналізу варіантів: Пошук рішення.

Значний інтерес представляє реалізація у MS Excel підбору параметрів функціональної залежності методом найменших квадратів з використанням інструмента аналізу варіантів: Пошук рішення. Ця методика дозволяє підібрати параметри функції будь-якого виду. Розглянемо цю можливість з прикладу наступного завдання.

ПРИКЛАД 3. В результаті експерименту отримана залежність z(t) представлена в таблиці

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Підібрати коефіцієнти залежності Z(t)=At 4 +Bt 3 +Ct 2 +Dt+Kшляхом найменших квадратів.

Це завдання еквівалентне задачі знаходження мінімуму функції п'яти змінних

Розглянемо процес розв'язання задачі оптимізації (рис. 14).

Нехай значення А, У, З, Dі Дозберігаються в осередках A7:E7. Розрахуємо теоретичні значення функції Z(t)=At 4 +Bt 3 +Ct 2 +Dt+Kдля заданих t(B2:J2). Для цього в осередок B4введемо значення функції в першій точці (комірка B2):

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

Скопіюємо цю формулу в діапазон С4: J4і отримаємо очікуване значення функції у точках, абсциси яких зберігається в осередках B2:J2.

У осередок B5введемо формулу, що обчислює квадрат різниці між експериментальними та розрахунковими точками:

B5=(B4-B3)^2,

і скопіюємо її в діапазон С5:J5. У осередку F7зберігатимемо сумарну квадратичну помилку (10). Для цього введемо формулу:

F7 = СУМ(B5: J5).

Скористайтеся командою Сервіс®Пошук рішеннята вирішимо задачу оптимізації без обмежень. Заповнимо відповідним чином поля введення в діалоговому вікні, показаному на рис. 14 і натиснемо кнопку Виконати. Якщо рішення буде знайдено, з'явиться вікно, зображене на рис. 15.

Результатом роботи вирішального блоку буде виведення в комірки A7:E7значень параметрівфункції Z(t)=At 4 +Bt 3 +Ct 2 +Dt+K. У осередках B4:J4отримаємо очікуване значення функціїу вихідних точках. У осередку F7буде зберігатися сумарна квадратична помилка.

Зображувати експериментальні точки та підібрану лінію в одній графічній області можна, якщо виділити діапазон B2:J4, викликати Майстер діаграм, а потім відформатувати зовнішній виглядодержаних графіків.

Мал. 17 відображає робочий лист MS Excel після проведених обчислень.

4.1. Використання вбудованих функцій

Обчислення коефіцієнтів регресіїздійснюється за допомогою функції

Лінейн(Значення_y; Значення_x; Конст; статистика),

Значення_y- масив значень y,

Необхідно пам'ятати, що результатом функцій ЛІНІЙН()є безліч значень масив.

Для розрахунку коефіцієнта кореляціївикористовується функція

Корел(Масив1;Масив2),

y		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Для розрахунку значення коефіцієнта кореляції в комірку С6було введено таку формулу:

С7=КОРРЕЛ(B3:J3;B2:J2).

Знаючи коефіцієнти регресії аі bобчислимо значення функції y=ax+bдля заданих x. Для цього введемо формулу

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

і скопіюємо її в діапазон С5:J5(Рис. 5).

Зобразимо лінію регресії на діаграмі. Виділимо експериментальні точки на графіку, клацніть правою кнопкою миші та оберемо команду Початкові дані. У діалоговому вікні, що з'явилося (рис. 5) виберемо вкладку Ряді клацніть по кнопці Додати. Заповнимо поля введення, оскільки показано на рис. 6 і натиснемо кнопку ОК. До графіку експериментальних даних буде додано лінію регресії. За замовчуванням її графік буде зображений у вигляді точок, не з'єднаних лініями, що згладжують.

Мал. 6

4.2. Використання лінії тренду.

Побудова різних апроксимуючих залежностей у MS Excel реалізовано як властивості діаграми – лінія тренду.

ПРИКЛАД 2. В результаті експерименту було визначено деяку табличну залежність.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Діалогове вікно, що з'явилося, дозволяє побудувати апроксимуючу залежність.

На першій вкладці (рис. 11) цього вікна вказується вид апроксимуючої залежності.

На другому (рис. 12) визначаються параметри побудови:

· Назва апроксимуючої залежності;

· Чи показувати точку перетину кривої з прямою y=const;

· показувати апроксимуючу функцію на діаграмі чи ні (параметр показувати рівняння на діаграмі);

Аналогічно за допомогою лінії трендуможна підібрати параметри таких залежностей як

· Лінійна y=a∙x+b,

· логарифмічна y=a∙ln(x)+b,

· Експонентна y=a∙e b,

· статечна y=a∙x b,

· поліноміальна y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+dі так далі, до полінома 6-го ступеня включно,

· Лінійна фільтрація.

4.3. Використання вирішального блоку

Значний інтерес представляє реалізація у MS Excel підбору параметрів методом найменших квадратів із використанням вирішального блоку. Ця методика дозволяє підібрати параметри функції будь-якого виду. Розглянемо цю можливість з прикладу наступного завдання.

ПРИКЛАД 3. В результаті експерименту отримана залежність z(t) представлена в таблиці

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Підібрати коефіцієнти залежності Z(t)=At 4 +Bt 3 +Ct 2 +Dt+Kшляхом найменших квадратів.

Це завдання еквівалентне задачі знаходження мінімуму функції п'яти змінних

Розглянемо процес розв'язання задачі оптимізації (рис. 14).

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

У осередок B5введемо формулу, що обчислює квадрат різниці між експериментальними та розрахунковими точками:

B5=(B4-B3)^2,

F7 = СУМ(B5: J5).

Зображувати експериментальні точки та підібрану лінію в одній графічній області можна, якщо виділити діапазон B2:J4, викликати Майстер діаграма потім відформатувати зовнішній вигляд отриманих графіків.

Мал. 17 відображає робочий лист MS Excel після проведених обчислень.

5. СПИСОК ЛІТЕРАТУРИ

1. Алексєєв Є.Р., Чеснокова О.В., Розв'язання задач обчислювальної математики в пакетах Mathcad12, MATLAB7, Maple9. - НТ Прес, 2006.-596с. :іл. - (Самовчитель)

2. Алексєєв Є.Р., Чеснокова О.В., Є.А. Рудченко, Scilab, рішення інженерних та математичних завдань. -М., БІНОМ, 2008.-260с.

3. Березін І.С., Жидков Н.П., Методи обчислень.-М.: Наука, 1966.-632с.

4. Гарнаєв А.Ю., Використання MS EXCEL та VBA в економіці та фінансах. - СПб.: БХВ - Петербург, 1999.-332с.

5. Демидович Б.П., Марон І А., Шувалова В.З., Чисельні методи аналізу.-М.: Наука, 1967.-368с.

6. Корн Г., Корн Т., Довідник з математики для науковців та інженерів.-М., 1970, 720с.

7. Алексєєв Є.Р., Чеснокова О.В. Методичні вказівкидля виконання лабораторних робіт у MS EXCEL. Для студентів усіх спеціальностей. Донецьк, ДонНТУ, 2004. 112 с.

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

Метод найменших квадратів (МНК) є математичним описом залежності однієї змінної від другої. Його можна використовувати під час прогнозування.

Увімкнення надбудови «Пошук рішення»

Для того, щоб використовувати МНК в Екселі, потрібно увімкнути надбудову "Пошук рішення", яка за замовчуванням вимкнена.

Тепер функція Пошук рішенняв Excel активовано, а її інструменти з'явилися на стрічці.

Умови завдання

Опишемо застосування МНК на конкретному прикладі. Маємо два ряди чисел x і y , Послідовність яких представлена на зображенні нижче.

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

При цьому відомо що при x=0 yтеж одно 0 . Тому дане рівнянняможна описати залежністю y=nx .

Нам належить знайти мінімальну суму квадратів різниці.

Рішення

Перейдемо до опису безпосереднього застосування методу.

Як бачимо, застосування методу найменших квадратів є досить складною математичною процедурою. Ми показали її у дії на найпростішому прикладі, а існують набагато більше складні випадки. Втім, інструментарій Microsoft Excelпокликаний максимально спростити обчислення, що проводяться.