Метод найменших квадратів (МНК) належить до сфери регресійного аналізу. Він має безліч застосувань, оскільки дозволяє здійснювати наближене уявлення заданої функції іншими більш простими. МНК може виявитися надзвичайно корисним при обробці спостережень і його активно використовують для оцінки одних величин за результатами вимірювань інших, що містять випадкові помилки. З цієї статті ви дізнаєтеся, як реалізувати обчислення методом найменших квадратів в 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 для чайників, що дозволяють спрогнозувати величину майбутнього значення того чи іншого показника згідно з лінійним трендом.
Яке знаходить саме широке застосуванняу різних галузях науки та практичної діяльності. Це може бути фізика, хімія, біологія, економіка, соціологія, психологія і таке інше. Волею долі мені часто доводиться мати справу з економікою, і тому сьогодні я оформлю вам путівку до дивовижної країни під назвою Економетрика=) …Як це не хочете?! Там дуже добре – треба тільки наважитися! …Але ось те, що ви, напевно, точно хочете – так це навчитися вирішувати завдання методом найменших квадратів. І особливо старанні читачі навчаться вирішувати їх не тільки безпомилково, але ще й ДУЖЕ ШВИДКО;-) Але спочатку загальна постановка задачі+ супутній приклад:
Нехай у деякій предметної області досліджуються показники, які мають кількісне вираз. У цьому є підстави вважати, що показник залежить від показника . Це може бути як наукової гіпотезою, і грунтуватися на елементарному здоровому глузді. Залишимо, проте, науку осторонь і досліджуємо більш апетитні області - зокрема, продовольчі магазини. Позначимо через:
– торгову площу продовольчого магазину, кв.м.,
- Річний товарообіг продовольчого магазину, млн. руб.
Цілком зрозуміло, що чим більше площамагазину, тим у більшості випадків буде більшим його товарообіг.
Припустимо, що після проведення спостережень/дослідів/підрахунків/танців з бубном у нашому розпорядженні виявляються числові дані: 
З гастрономами, гадаю, все зрозуміло: - це площа 1-го магазину, - його річний товарообіг, - площа 2-го магазину, - його річний товарообіг і т.д. До речі, зовсім не обов'язково мати доступ до секретних матеріалів – досить точну оцінку товарообігу можна отримати засобами математичної статистики. Втім, не відволікаємось, курс комерційного шпигунства – він уже платний =)
Табличні дані також можна записати у вигляді точок та зобразити у звичній для нас декартовій системі .
Відповімо на важливе питання: скільки точок потрібно якісного дослідження?
Чим більше тим краще. Мінімально допустимий набір складається з 5-6 пікселів. Крім того, при невеликій кількості даних у вибірку не можна включати «аномальні» результати. Так, наприклад, невеликий елітний магазин може рятувати на порядки більше «своїх колег», спотворюючи тим самим загальну закономірність, яку потрібно знайти!
Якщо дуже просто - нам потрібно підібрати функцію, графікякою проходить якомога ближче до точок 
. Таку функцію називають апроксимуючою
(апроксимація – наближення)або теоретичною функцією
. Взагалі кажучи, тут одразу з'являється очевидний «претендент» – багаточлен високого ступеня, графік якого проходить через всі точки. Але цей варіант складний, а часто й просто некоректний (т.к. графік буде весь час «петляти» і погано відображатиме головну тенденцію).
Таким чином, розшукувана функція повинна бути досить простою і в той же час відображати залежність адекватно. Як ви здогадуєтеся, один із методів знаходження таких функцій і називається методом найменших квадратів. Спочатку розберемо його суть у загальному вигляді. Нехай деяка функція наближає експериментальні дані: 
Як оцінити точність наближення? Обчислимо і різниці (відхилення) між експериментальними та функціональними значеннями (Вивчаємо креслення). Перша думка, яка спадає на думку – це оцінити, наскільки велика сума, але проблема полягає в тому, що різниці можуть бути і негативні. (наприклад, 
)
та відхилення внаслідок такого підсумовування будуть взаємознищуватись. Тому як оцінка точності наближення напрошується прийняти суму модуліввідхилень:
або в згорнутому вигляді: (раптом хто не знає: – це значок суми, а – допоміжна змінна-«лічильник», яка набуває значення від 1 до ).
Наближаючи експериментальні точки різними функціями, ми отримуватимемо різні значення, і, очевидно, де ця сума менше – та функція і точніше.
Такий метод існує і називається він методом найменших модулів. Однак на практиці набув значно більшого поширення метод найменших квадратів, В якому можливі негативні значення ліквідуються не модулем, а зведенням відхилень у квадрат:
, після чого зусилля спрямовані на підбір такої функції, щоб сума квадратів відхилень 
була якнайменше. Власне, звідси й назва методу.
І зараз ми повертаємось до іншого важливому моменту: Як зазначалося вище, функція, що підбирається, повинна бути досить проста - але ж і таких функцій теж чимало: лінійна , гіперболічна, експоненційна, логарифмічна, квадратична і т.д. І, звичайно, тут одразу б хотілося «скоротити поле діяльності». Який клас функцій вибрати на дослідження? Примітивний, але ефективний прийом:
- Найпростіше зобразити точки 
на кресленні та проаналізувати їх розташування. Якщо вони мають тенденцію розташовуватися по прямій, слід шукати рівняння прямої 
з оптимальними значеннями та . Іншими словами, завдання полягає у знаходженні ТАКИХ коефіцієнтів – щоб сума квадратів відхилень була найменшою.
Якщо ж точки розташовані, наприклад, по гіперболі, то свідомо зрозуміло, що лінійна функція даватиме погане наближення. У цьому випадку шукаємо найбільш «вигідні» коефіцієнти для рівняння гіперболи 
– ті, що дають мінімальну суму квадратів 
.
А тепер зверніть увагу, що в обох випадках мова йде про функції двох змінних, аргументами якої є параметри залежностей, що розшукуються:
І по суті нам потрібно вирішити стандартне завдання – знайти мінімум функції двох змінних.
Згадаймо про наш приклад: припустимо, що «магазинні» точки мають тенденцію розташовуватися по прямій лінії і є підстави вважати наявність лінійної залежностітоварообігу від торгової площі Знайдемо ТАКІ коефіцієнти «а» та «бе», щоб сума квадратів відхилень 
була найменшою. Все як завжди - спочатку приватні похідні 1-го порядку. Згідно правилу лінійностідиференціювати можна прямо під значком суми: 
Якщо хочете використовувати дану інформацію для реферату або курсовика - буду дуже вдячний за посилання в списку джерел, такі докладні викладки знайдете мало де: 
Складемо стандартну систему:
Скорочуємо кожне рівняння на «двійку» і, крім того, «розвалюємо» суми: 
Примітка
: самостійно проаналізуйте, чому «а» та «бе» можна винести за значок суми До речі, формально це можна зробити і із сумою
Перепишемо систему у «прикладному» вигляді: 
після чого починає промальовуватися алгоритм розв'язання нашого завдання:
Координати точок ми знаємо? Знаємо. Суми 
знайти можемо? Легко. Складаємо найпростішу систему двох лінійних рівнянь із двома невідомими(«а» та «бе»). Систему вирішуємо, наприклад, методом Крамера, у результаті отримуємо стаціонарну точку . Перевіряючи достатня умова екстремумуможна переконатися, що в даній точці функція 
досягає саме мінімуму. Перевірка пов'язана з додатковими викладками і тому залишимо її за кадром (при необхідності кадр, що бракує, можна подивитися ). Робимо остаточний висновок:
Функція 
найкращим чином (принаймні, порівняно з будь-якою іншою лінійною функцією)наближає експериментальні точки 
. Грубо кажучи, її графік відбувається максимально близько до цих точок. У традиціях економетрикиотриману апроксимуючу функцію також називають рівнянням парної лінійної регресії
.
Розглянуте завдання має велике практичне значення. У ситуації з нашим прикладом, рівняння 
дозволяє прогнозувати, який товарообіг («Ігрек»)буде біля магазину при тому чи іншому значенні торгової площі (Тому чи іншому значенні «ікс»). Так, отриманий прогноз буде лише прогнозом, але у багатьох випадках він виявиться досить точним.
Я розберу лише одне завдання з «реальними» числами, оскільки жодних труднощів у ній немає – всі обчислення на рівні шкільної програми 7-8 класу. У 95 відсотків випадків вам буде запропоновано знайти саме лінійну функцію, але в самому кінці статті я покажу, що нітрохи не складніше знайти рівняння оптимальної гіперболи, експоненти та деяких інших функцій.
По суті, залишилося роздати обіцяні плюшки – щоб ви навчилися вирішувати такі приклади не лише безпомилково, а ще й швидко. Уважно вивчаємо стандарт:
Завдання
В результаті дослідження взаємозв'язку двох показників отримані такі пари чисел: 
Методом найменших квадратів знайти лінійну функцію, яка найкраще наближає емпіричні (досвідчені)дані. Зробити креслення, на якому в декартовій прямокутній системі координат побудувати експериментальні точки та графік апроксимуючої функції 
. Знайти суму квадратів відхилень між емпіричними та теоретичними значеннями. З'ясувати, чи буде функція кращою (з погляду методу найменших квадратів)наближати експериментальні точки.
Зауважте, що «іксові» значення – натуральні, і це має характерний змістовний зміст, про який я розповім трохи згодом; але вони, зрозуміло, можуть і дробовими. Крім того, залежно від змісту того чи іншого завдання як «іксові», так і «ігрові» значення повністю або частково можуть бути негативними. Ну а у нас дане «безлике» завдання, і ми починаємо його Рішення:
Коефіцієнти оптимальної функції знайдемо як розв'язання системи: 
З метою більш компактного запису змінну-«лічильник» можна опустити, оскільки і так зрозуміло, що підсумовування здійснюється від 1 до .
Розрахунок потрібних сум зручніше оформити у табличному вигляді: 
Обчислення можна провести на мікрокалькуляторі, але краще використовувати Ексель - і швидше, і без помилок; дивимося короткий відеоролик:
Таким чином, отримуємо наступну систему:
Тут можна помножити друге рівняння на 3 та від 1-го рівняння почленно відняти 2-е. Але це везіння - на практиці системи частіше не подарункові, і в таких випадках рятує метод Крамера:
Отже, система має єдине рішення.
Виконаємо перевірку. Розумію, що не хочеться, але навіщо пропускати помилки там, де їх можна стовідсотково не пропустити? Підставимо знайдене рішення в ліву частину кожного рівняння системи:
Отримано праві частини відповідних рівнянь, отже система вирішена правильно.
Таким чином, шукана апроксимуюча функція: – з всіх лінійних функційекспериментальні дані найкраще наближає саме вона.
На відміну від прямий
залежності товарообігу магазину від його площі, знайдена залежність є зворотній
(Принцип «що більше – тим менше»), і цей факт відразу виявляється по негативному кутовому коефіцієнту. Функція 
повідомляє нам про те, що зі збільшення якогось показника на 1 одиницю значення залежного показника зменшується в середньомуна 0,65 одиниць. Як то кажуть, що вище ціна на гречку, то менше її продано.
Для побудови графіка апроксимуючої функції знайдемо два її значення:
і виконаємо креслення: 
Побудована пряма називається лінією тренду
(а саме – лінією лінійного тренду, тобто у загальному випадку тренд – це не обов'язково пряма лінія). Всім знайомий вислів «бути в тренді», і, гадаю, що цей термін не потребує додаткових коментарів.
Обчислимо суму квадратів відхилень 
між емпіричними та теоретичними значеннями. Геометрично – це сума квадратів довжин «малинових» відрізків (два з яких настільки малі, що їх навіть не видно).
Обчислення зведемо до таблиці: 
Їх можна знову ж таки провести вручну, про всяк випадок наведу приклад для 1-ї точки: 
але набагато ефективніше вчинити вже відомим чином:
Ще раз повторимо: у чому сенс отриманого результату?З всіх лінійних функційу функції 
показник є найменшим, тобто у своїй родині це найкраще наближення. І тут, до речі, невипадкове заключне питання завдання: а раптом запропонована експоненційна функція 
краще наближати експериментальні точки?
Знайдемо відповідну суму квадратів відхилень – щоб розрізняти, я позначу їх літерою «епсілон». Техніка така сама: 
І знову на будь-який пожежний обчислення для 1-ї точки: 
В Екселі користуємося стандартною функцією EXP (Синтаксис можна подивитися в екселевський Довідці).
Висновок: , отже, експоненційна функція наближає експериментальні точки гірше, ніж пряма 
.
Але тут слід зазначити, що «гірше» – це ще не означає, що погано. Зараз збудував графік цієї експоненційної функції – і він теж проходить близько до точок 
- Так, що без аналітичного дослідження і сказати важко, яка функція точніше.
На цьому рішення закінчено, і я повертаюся до питання про натуральні значення аргументу. У різних дослідженнях, зазвичай, економічних чи соціологічних, натуральними «іксами» нумерують місяці, роки чи інші рівні часові проміжки. Розглянемо, наприклад, таке завдання.
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 і натиснемо кнопку ОК. До графіку експериментальних даних буде додано лінію регресії. За замовчуванням її графік буде зображений у вигляді точок, не з'єднаних лініями, що згладжують.
Рис. 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 після проведених обчислень.
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 с.
Метод найменших квадратів (МНК) ґрунтується на мінімізації суми квадратів відхилень обраної функції від досліджуваних даних. У цій статті апроксимуємо наявні дані за допомогою лінійної функціїy = a x + b .
Метод найменших квадратів(англ. Ordinary Least Squares , OLS) є одним із базових методів регресійного аналізу в частині оцінки невідомих параметрів регресійних моделейза вибірковими даними.
Розглянемо наближення функціями, що залежать лише від однієї змінної:
- Лінійна: y=ax+b (ця стаття)
- : y=a*Ln(x)+b
- : y=a*x m
- : y=a*EXP(b*x)+с
- : y=ax 2 +bx+c
Примітка: Випадки наближення поліномом з 3-го до 6-го ступеня розглянуті в цій статті Наближення тригонометричним поліномом розглянуто тут.
Лінійна залежність
Нас цікавить зв'язок 2-х змінних хі y. Є припущення, що yзалежить від хза лінійним законом y = ax + b. Щоб визначити параметри цього взаємозв'язку дослідник провів спостереження: для кожного значення х i проведено вимір y (див. файл прикладу). Відповідно, нехай є 20 пар значень (х i; y i).
Примітка:Якщо крок зміни по х постійний, то для побудови діаграми розсіюванняможна використовувати , якщо ні, необхідно використовувати тип діаграми Крапкова .
З діаграми очевидно, що зв'язок між змінними близька до лінійної. Щоб зрозуміти яка з множини прямих ліній найбільш «правильно» описує залежність між змінними, необхідно визначити критерій, за яким будуть порівнюватися лінії.
Як такий критерій використовуємо вираз:
де ŷ i = a * x i + b ; n – число пар значень (у разі n=20)
Вищезгадане вираз являє собою суму квадратів відстаней між спостереженими значеннями y i ŷ i і часто позначається як SSE ( Sum of Squared Errors (Residuals), сума квадратів помилок (залишків)) .
Метод найменших квадратівполягає у підборі такої лінії ŷ = ax + b, Для якої вищезгадане вираз набуває мінімального значення.
Примітка:Будь-яка лінія у двовимірному просторі однозначно визначається значеннями 2-х параметрів: a (нахил) та b (Зрушення).
Вважається, що менше сума квадратів відстаней, тим відповідна лінія краще апроксимує наявні дані і може бути надалі використана для прогнозування значень y від змінної х. Зрозуміло, що навіть якщо насправді ніякого взаємозв'язку між змінними немає чи зв'язок нелінійний, то МНК все одно підбере найкращу лінію. Таким чином, МНК нічого не говорить про наявність реального взаємозв'язку змінних, метод просто дозволяє підібрати такі параметри функції a і b , Для яких вищезгадане вираз мінімально.
Виконавши не дуже складні математичні операції (докладніше див.), можна обчислити параметри a і b :
Як видно з формули, параметр a являє собою відношення коваріації і тому в MS EXCEL для обчислення параметра а можна використовувати такі формули (див. файл приклад лист Линійна):
= КОВАР(B26:B45;C26:C45)/ ДИСП.Г(B26:B45)або
= КОВАРІАЦІЯ.В(B26:B45;C26:C45)/ДИСП.В(B26:B45)
Також для обчислення параметра а можна використовувати формулу = Нахил (C26: C45; B26: B45). Для параметра b використовуйте формулу = ВІДРІЗОК(C26:C45;B26:B45) .
І нарешті, функція Лінейн() дозволяє обчислити відразу обидва параметри. Для введення формули Лінейн (C26: C45; B26: B45)необхідно виділити у рядку 2 комірки та натиснути CTRL + SHIFT + ENTER(Див. статтю про ). У лівому осередку буде повернено значення а , у правій – b .
Примітка: Щоб не зв'язуватися із введенням формул масивупотрібно додатково використовувати функцію ІНДЕКС() . Формула = ІНДЕКС(ЛІНЕЙН(C26:C45;B26:B45);1)або просто = Лінейн (C26: C45; B26: B45)поверне параметр, відповідальний нахил лінії, тобто. а . Формула = ІНДЕКС(ЛІНЕЙН(C26:C45;B26:B45);2)поверне параметр, відповідальний за перетин лінії з віссю Y, тобто. b .
Обчисливши параметри, діаграмі розсіюванняможна збудувати відповідну лінію.
Ще одним способом побудови прямої лінії за методом найменших квадратів є інструмент діаграми Лінія тренду. Для цього виділіть діаграму, у меню виберіть вкладку Макет, в групі Аналізнатисніть Лінія тренду, потім Лінійне наближення .
Поставивши в діалоговому вікні галочку в полі «показувати рівняння на діаграмі» можна переконатися, що знайдені параметри збігаються зі значеннями на діаграмі.
Примітка: Для того, щоб параметри збігалися необхідно, щоб тип діаграми був . Справа в тому, що при побудові діаграми Графікзначення осі Х не можуть бути задані користувачем (користувач може вказати тільки підписи, які не впливають на розташування точок). Замість значень використовується послідовність 1; 2; 3; …(для нумерації категорій). Тому, якщо будувати лінію трендуна діаграмі типу Графік, замість фактичних значень Х будуть використані значення цієї послідовності, що призведе до невірного результату (якщо, звичайно, фактичні значення Х не збігаються з послідовністю 1; 2; 3; …).
Метод найменших квадратів (МНК)
Система m лінійних рівняньз n невідомими має вигляд:
Можливі три випадки: m Якщо m>nі система є спільною, то матриця А має принаймні m - nлінійно залежних рядків. Тут рішення може бути отримано відбором n будь-яких лінійно незалежних рівнянь (якщо вони існують) і застосуванням формули Х = А -1 ЧВ, тобто зведенням завдання до раніше вирішеної. При цьому отримане рішення завжди задовольнятиме й іншим m - рівнянням. Однак при застосуванні комп'ютера зручніше використовувати загальніший підхід - метод найменших квадратів. Під методом алгебри найменших квадратів розуміється метод розв'язання систем лінійних рівнянь шляхом мінімізації евклідової норми Ax? b? > inf. (1.2) Розглянемо деякий експеримент, під час якого у моменти часу проводиться, наприклад, вимірювання температури Q(t). Нехай результати вимірювань задаються масивом Припустимо, що умови проведення експерименту такі, що виміри проводяться із явною похибкою. У цих випадках закон зміни температури Q(t) шукають за допомогою деякого полінома P(t) = + + + ... +, визначаючи невідомі коефіцієнти, ..., з тих міркувань, щоб величина E(, ...,), що визначається рівністю гаус алгебраїчний exel апроксимація набувала мінімального значення. Оскільки мінімізується сума квадратів, цей метод називається апроксимацією даних методом найменших квадратів. Якщо замінити P(t) його виразом, то отримаємо Поставимо завдання визначення масиву те щоб величина була мінімальна, тобто. визначимо масив шляхом найменших квадратів. Для цього прирівняємо приватні похідні до нуля: Якщо запровадити m Ч n матрицю A = (), i = 1, 2..., m; j = 1, 2, ..., n, де I = 1, 2..., m; j = 1, 2, ..., n, та виписана рівність набуде вигляду Перепишемо написану рівність у термінах операцій із матрицями. Маємо визначення множення матриці на стовпець Для транспонованої матриці аналогічне співвідношення виглядає так Введемо позначення: i -у компоненту вектора Ax будемо позначати Відповідно до виписаних матричних рівностей будемо мати У матричній формі ця рівність перепишеться у вигляді AT x = AT B (1.3) Тут A – прямокутна mЧ n матриця. Причому завдання апроксимації даних, зазвичай, m > n. Рівняння (1.3) називається нормальним рівнянням. Можна було від початку, використовуючи евклідову норму векторів, записати завдання у еквівалентної матричної формі: Наша мета мінімізувати цю функцію x. Щоб у точці рішення досягався мінімум, перші похідні по x у цій точці повинні дорівнювати нулю. Похідні цієї функції складають 2A T B + 2A T Ax і тому рішення має задовольняти системі лінійних рівнянь (ATA)x = (ATAB). Ці рівняння називають нормальними рівняннями. Якщо A - mЧ n матриця, то A>A - n Ч n - матриця, тобто. матриця нормального рівняння завжди квадратна симетрична матриця. Більше того, вона має властивість позитивної визначеності в тому сенсі, що (A>Ax, x) = (Ax, Ax)? 0. Зауваження. Іноді рішення рівняння виду (1.3) називають розв'язком системи Ax = В, де A прямокутна m Ч n (m > n) матриця методом найменших квадратів. Завдання найменших квадратів можна графічно інтерпретувати як мінімізацію вертикальних відстаней від точок даних до модельної кривої (див. рис.1.1). Ця ідея ґрунтується на припущенні, що всі помилки в апроксимації відповідають помилкам у спостереженнях. Якщо є також помилки в незалежних змінних, то може виявитися більш доречним мінімізувати евклідову відстань від даних до моделі. Нижче наведений алгоритм реалізації МНК в Excel передбачає, що всі вихідні дані вже відомі. Обидві частини матричного рівняння AЧX=B системи множимо ліворуч на транспоновану матрицю системи АТ: А Т АХ = А Т В Потім обидві частини рівняння множимо зліва на матрицю (АТА) -1 . Якщо ця матриця існує, система визначена. З урахуванням того що (АТА) -1 * (АТА) = Е, отримуємо Х = (АТА) -1 АТВ. Отримане матричне рівняння є рішенням системи лінійних рівнянь m з nневідомими при m>n. Розглянемо застосування вищеописаного алгоритму конкретному прикладі. приклад. Нехай необхідно вирішити систему Excelліст з рішенням в режимі відображення формул для даної задачі виглядає наступним чином: Результати розрахунків: Шуканий вектор Х розташований в діапазоні Е11: Е12. При розв'язанні заданої системи лінійних рівнянь використовувалися такі функції: 1. МОБР – повертає зворотну матрицю для матриці, що зберігається в масиві. Синтаксис: МОБР(масив). Масив - числовий масив з рівною кількістю рядків та стовпців. 2. МУМНІЖ - повертає твір матриць (матриці зберігаються в масивах). Результатом є масив з таким самим числом рядків, як масив1 і з таким самим числом стовпців, як масив2. Синтаксис: МУМНОЖ(масив1;масив2). Масив1, масив2 - масиви, що перемножуються. Після введення функції у ліву верхню комірку діапазону масиву слід виділити масив, починаючи з комірки, що містить формулу, натиснути клавішу F2, а потім натиснути клавіші CTRL+SHIFT+ENTER. 3. ТРАНСП - перетворює вертикальний набір осередків на горизонтальний, або навпаки. В результаті використання цієї функції з'являється масив з числом рядків, рівним числу стовпців вихідного масиву, і числом стовпців, рівним числу рядків початкового масиву.Алгебраїчний метод найменших квадратів
Аналіз даних експерименту
МНК у Excel
