Апроксимація дослідних даних. Метод найменших квадратів. Апроксимація функції методом найменших квадратів Використовуючи метод найменших квадратів апроксимувати

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

На заключному уроці теми ми познайомимося з найвідомішим додатком ФНП, яке знаходить найширше застосування у різних галузях науки та практичної діяльності. Це може бути фізика, хімія, біологія, економіка, соціологія, психологія і таке інше. Волею долі мені часто доводиться мати справу з економікою, і тому сьогодні я оформлю вам путівку до дивовижної країни під назвою Економетрика=) …Як це не хочете?! Там дуже добре – треба тільки наважитися! …Але ось те, що ви, напевно, точно хочете – так це навчитися вирішувати завдання методом найменших квадратів. І особливо старанні читачі навчаться вирішувати їх не тільки безпомилково, але ще й ДУЖЕ ШВИДКО;-) Але спочатку загальна постановка задачі+ супутній приклад:

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

– торгову площу продовольчого магазину, кв.м.,
- Річний товарообіг продовольчого магазину, млн. руб.

Цілком зрозуміло, що чим більша площа магазину, тим у більшості випадків буде більшим його товарообіг.

Припустимо, що після проведення спостережень/дослідів/підрахунків/танців з бубном у нашому розпорядженні виявляються числові дані:

З гастрономами, гадаю, все зрозуміло: - це площа 1-го магазину, - його річний товарообіг, - площа 2-го магазину, - його річний товарообіг і т.д. До речі, зовсім не обов'язково мати доступ до секретних матеріалів – досить точну оцінку товарообігу можна отримати засобами математичної статистики. Втім, не відволікаємось, курс комерційного шпигунства – він уже платний =)

Табличні дані також можна записати у вигляді точок та зобразити у звичній для нас декартовій системі .

Відповімо на важливе питання: скільки точок потрібно якісного дослідження?

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

Якщо дуже просто - нам потрібно підібрати функцію, графікякою проходить якомога ближче до точок . Таку функцію називають апроксимуючою (апроксимація – наближення)або теоретичною функцією . Взагалі кажучи, тут одразу з'являється очевидний «претендент» – багаточлен високого ступеня, графік якого проходить через всі точки. Але цей варіант складний, а часто й просто некоректний (т.к. графік буде весь час «петляти» і погано відображатиме головну тенденцію).

Таким чином, розшукувана функція повинна бути досить простою і в той же час відображати залежність адекватно. Як ви здогадуєтеся, один із методів знаходження таких функцій і називається методом найменших квадратів. Спочатку розберемо його суть у загальному вигляді. Нехай деяка функція наближає експериментальні дані:

Як оцінити точність наближення? Обчислимо і різниці (відхилення) між експериментальними та функціональними значеннями (Вивчаємо креслення). Перша думка, яка спадає на думку – це оцінити, наскільки велика сума, але проблема полягає в тому, що різниці можуть бути і негативні. (наприклад, ) та відхилення внаслідок такого підсумовування будуть взаємознищуватись. Тому як оцінка точності наближення напрошується прийняти суму модуліввідхилень:

або в згорнутому вигляді: (раптом хто не знає: – це значок суми, а - Допоміжна змінна-«лічильник», яка набуває значення від 1 до ) .

Наближаючи експериментальні точки різними функціями, ми будемо отримувати різні значення і, очевидно, де ця сума менша – та функція і точніше.

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

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

І зараз ми повертаємося до іншого важливого моменту: як зазначалося вище, функція, що підбирається, повинна бути досить проста - але ж і таких функцій теж чимало: лінійна , гіперболічна , експоненційна , логарифмічна , квадратична і т.д. І, звичайно, тут одразу б хотілося «скоротити поле діяльності». Який клас функцій вибрати на дослідження? Примітивний, але ефективний прийом:

- Найпростіше зобразити точки на кресленні та проаналізувати їх розташування. Якщо вони мають тенденцію розташовуватися по прямій, слід шукати рівняння прямої з оптимальними значеннями та . Іншими словами, завдання полягає у знаходженні ТАКИХ коефіцієнтів – щоб сума квадратів відхилень була найменшою.

Якщо ж точки розташовані, наприклад, по гіперболі, то свідомо зрозуміло, що лінійна функція даватиме погане наближення. У цьому випадку шукаємо найбільш «вигідні» коефіцієнти для рівняння гіперболи – ті, що дають мінімальну суму квадратів .

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

І по суті нам потрібно вирішити стандартне завдання – знайти мінімум функції двох змінних.

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

Якщо хочете використовувати дану інформацію для реферату або курсовика - буду дуже вдячний за посилання в списку джерел, такі докладні викладки знайдете мало де:

Складемо стандартну систему:

Скорочуємо кожне рівняння на «двійку» і, крім того, «розвалюємо» суми:

Примітка : самостійно проаналізуйте, чому «а» та «бе» можна винести за значок суми До речі, формально це можна зробити і із сумою

Перепишемо систему у «прикладному» вигляді:

після чого починає промальовуватися алгоритм розв'язання нашого завдання:

Координати точок ми знаємо? Знаємо. Суми знайти можемо? Легко. Складаємо найпростішу систему двох лінійних рівнянь із двома невідомими(«а» та «бе»). Систему вирішуємо, наприклад, методом Крамера, у результаті отримуємо стаціонарну точку . Перевіряючи достатня умова екстремумуможна переконатися, що в даній точці функція досягає саме мінімуму. Перевірка пов'язана з додатковими викладками і тому залишимо її за кадром (при необхідності кадр, що бракує, можна подивитисятут ) . Робимо остаточний висновок:

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

Розглянуте завдання має велике практичне значення. У ситуації з нашим прикладом, рівняння дозволяє прогнозувати, який товарообіг («Ігрек»)буде біля магазину при тому чи іншому значенні торгової площі (Тому чи іншому значенні «ікс»). Так, отриманий прогноз буде лише прогнозом, але у багатьох випадках він виявиться досить точним.

Я розберу лише одне завдання з «реальними» числами, оскільки жодних труднощів у ній немає – всі обчислення на рівні шкільної програми 7-8 класу. У 95 відсотків випадків вам буде запропоновано знайти саме лінійну функцію, але в самому кінці статті я покажу, що нітрохи не складніше знайти рівняння оптимальної гіперболи, експоненти та деяких інших функцій.

По суті, залишилося роздати обіцяні плюшки – щоб ви навчилися вирішувати такі приклади не лише безпомилково, а ще й швидко. Уважно вивчаємо стандарт:

Завдання

В результаті дослідження взаємозв'язку двох показників отримано такі пари чисел:

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

Зауважте, що «іксові» значення – натуральні, і це має характерний змістовний зміст, про який я розповім трохи згодом; але вони, зрозуміло, можуть і дробовими. Крім того, залежно від змісту того чи іншого завдання як «іксові», так і «ігрові» значення повністю або частково можуть бути негативними. Ну а у нас дане «безлике» завдання, і ми починаємо її рішення:

Коефіцієнти оптимальної функції знайдемо як розв'язання системи:

З метою більш компактного запису змінну-«лічильник» можна опустити, оскільки і так зрозуміло, що підсумовування здійснюється від 1 до .

Розрахунок потрібних сум зручніше оформити у табличному вигляді:

Обчислення можна провести на мікрокалькуляторі, але краще використовувати Ексель - і швидше, і без помилок; дивимося короткий відеоролик:

Таким чином, отримуємо наступну систему:

Тут можна помножити друге рівняння на 3 та від 1-го рівняння почленно відняти 2-е. Але це везіння - на практиці системи частіше не подарункові, і в таких випадках рятує метод Крамера:
Отже, система має єдине рішення.

Виконаємо перевірку. Розумію, що не хочеться, але навіщо пропускати помилки там, де їх можна стовідсотково не пропустити? Підставимо знайдене рішення в ліву частину кожного рівняння системи:

Отримано праві частини відповідних рівнянь, отже система вирішена правильно.

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

На відміну від прямий залежності товарообігу магазину від його площі, знайдена залежність є зворотній (Принцип «що більше – тим менше»), і цей факт відразу виявляється по негативному кутовому коефіцієнту. Функція повідомляє нам про те, що зі збільшення якогось показника на 1 одиницю значення залежного показника зменшується в середньомуна 0,65 одиниць. Як то кажуть, що вище ціна на гречку, то менше її продано.

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

і виконаємо креслення:

Побудована пряма називається лінією тренду (а саме – лінією лінійного тренду, тобто у загальному випадку тренд – це не обов'язково пряма лінія). Всім знайомий вислів «бути в тренді», і, гадаю, що цей термін не потребує додаткових коментарів.

Обчислимо суму квадратів відхилень між емпіричними та теоретичними значеннями. Геометрично – це сума квадратів довжин «малинових» відрізків (два з яких настільки малі, що їх навіть не видно).

Обчислення зведемо до таблиці:

Їх можна знову ж таки провести вручну, про всяк випадок наведу приклад для 1-ї точки:

але набагато ефективніше вчинити вже відомим чином:

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

Знайдемо відповідну суму квадратів відхилень – щоб розрізняти, я позначу їх літерою «епсілон». Техніка така сама:

І знову на будь-який пожежний обчислення для 1-ї точки:

В Екселі користуємося стандартною функцією EXP (Синтаксис можна подивитися в екселевський Довідці).

Висновок: , отже, експоненційна функція наближає експериментальні точки гірше, ніж пряма .

Але тут слід зазначити, що «гірше» – це ще не означаєщо погано. Зараз збудував графік цієї експоненційної функції – і він теж проходить близько до точок - Так, що без аналітичного дослідження і сказати важко, яка функція точніше.

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

Є такі дані про роздрібний товарообіг магазину за перше півріччя:

Використовуючи аналітичне вирівнювання по прямій, визначте обсяг товарообігу за липень.

Так без проблем: нумеруємо місяці 1, 2, 3, 4, 5, 6 і використовуємо звичайний алгоритм, в результаті чого отримуємо рівняння – єдине, коли йдеться про час, зазвичай використовують букву «те» (хоча це не критично). Отримане рівняння показує, що у першому півріччі товарообіг збільшувався загалом на 27,74 д.е. за місяць. Отримаємо прогноз на липень (місяць №7): д.е.

І подібних завдань – темрява темрява. Бажаючі можуть скористатися додатковим сервісом, а саме моїм екселевський калькулятор (демо-версія), який вирішує розібране завдання практично миттєво!Робоча версія програми доступна з обмінуабо за символічну плату.

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

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

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

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

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

З експоненційною залежністю ситуація трохи складніша. Щоб звести справу до лінійного випадку, прологарифмуємо функцію та скористаємося властивостям логарифму:

Тепер, зіставляючи отриману функцію з лінійною функцією , приходимо висновку, що у системі (*) потрібно замінити на , а – на . Для зручності позначимо:

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

Щоб наблизити експериментальні точки оптимальною параболою слід знайти мінімум функції трьох змінних . Після здійснення стандартних дій отримуємо наступну «робочу» систему:

Так, звичайно, сум тут більше, але при використанні улюбленої програми труднощів взагалі ніяких. І насамкінець розповім, як за допомогою Екселю швидко виконати перевірку та побудувати потрібну лінію тренду: створюємо точкову діаграму, виділяємо мишею будь-яку з точок. і через праве клацання вибираємо опцію «Додати лінію тренду». Далі вибираємо тип діаграми та на вкладці «Параметри»активуємо опцію "Показувати рівняння на діаграмі". ОК

Як завжди статтю хочеться завершити якоюсь красивою фразою, і я вже мало не надрукував «Будьте в тренді!». Але вчасно передумав. І не через те, що вона є шаблонною. Не знаю, кому як, а мені щось зовсім не хочеться слідувати американському, що пропагується, і особливо європейському тренду =) Тому я побажаю кожному з вас дотримуватися своєї власної лінії!

http://www.grandars.ru/student/vysshaya-matematika/metod-naimenshih-kvadratov.html

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

Розглянемо процедуру оцінки параметрів лінійної економетричної моделі за допомогою методу найменших квадратів докладніше. Така модель у загальному вигляді може бути представлена рівнянням (1.2):

y t = a 0 + a 1 x 1t + ... + a n x nt + ε t.

Вихідними даними в оцінці параметрів a 0 , a 1 ,..., a n є вектор значень залежної змінної y= (y 1 , y 2 , ... , y T)" і матриця значень незалежних змінних

у якій перший стовпець, що складається з одиниць, відповідає коефіцієнту моделі .

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

Приклади розв'язання задач методом найменших квадратів

приклад 2.1.Торговельне підприємство має мережу, що складається з 12 магазинів, інформацію про діяльність яких представлено у табл. 2.1.

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

Таблиця 2.1

Номер магазину	Річний товарообіг, млн руб.	Торгова площа, тис. м2
	19,76	0,24
	38,09	0,31
	40,95	0,55
	41,08	0,48
	56,29	0,78
	68,51	0,98
	75,01	0,94
	89,05	1,21
	91,13	1,29
	91,26	1,12
	99,84	1,29
	108,55	1,49

Рішення шляхом найменших квадратів.Позначимо - річний товарообіг-го магазину, млн руб.; - торгова площа магазину, тис. м 2 .

Рис.2.1. Діаграма розсіювання для прикладу 2.1

Для визначення форми функціональної залежності між змінними та побудуємо діаграму розсіювання (рис. 2.1).

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

Інформація щодо подальших розрахунків подана у табл. 2.2. За допомогою методу найменших квадратів оцінимо параметри лінійної однофакторної економетричної моделі

Таблиця 2.2

t	y t	x 1t	y t 2	x 1t 2	x 1t y t

	19,76	0,24	390,4576	0,0576	4,7424
	38,09	0,31	1450,8481	0,0961	11,8079
	40,95	0,55	1676,9025	0,3025	22,5225
	41,08	0,48	1687,5664	0,2304	19,7184
	56,29	0,78	3168,5641	0,6084	43,9062
	68,51	0,98	4693,6201	0,9604	67,1398
	75,01	0,94	5626,5001	0,8836	70,5094
	89,05	1,21	7929,9025	1,4641	107,7505
	91,13	1,29	8304,6769	1,6641	117,5577
	91,26	1,12	8328,3876	1,2544	102,2112
	99,84	1,29	9968,0256	1,6641	128,7936
	108,55	1,49	11783,1025	2,2201	161,7395
S	819,52	10,68	65008,554	11,4058	858,3991
Середнє	68,29	0,89

Таким чином,

Отже, зі збільшенням торгової площі на 1 тис. м 2 за інших рівних умов середньорічний товарообіг збільшується на 67,8871 млн руб.

приклад 2.2.Керівництво підприємства помітило, що річний товарообіг залежить тільки від торгової площі магазину (див. приклад 2.1), а й від середнього числа відвідувачів. Відповідна інформація представлена у табл. 2.3.

Таблиця 2.3

Рішення.Позначимо - середня кількість відвідувачів магазину на день, тис. чол.

Для визначення форми функціональної залежності між змінними та побудуємо діаграму розсіювання (рис. 2.2).

З діаграми розсіяння можна дійти невтішного висновку про позитивну залежність річного товарообігу від середньої кількості відвідувачів щодня (тобто. зростатиме зі зростанням ). Форма функціональної залежності – лінійна.

Мал. 2.2. Діаграма розсіювання для прикладу 2.2

Таблиця 2.4

t	x 2t	x 2t 2	y t x 2t	x 1t x 2t

	8,25	68,0625	163,02	1,98
	10,24	104,8575	390,0416	3,1744
	9,31	86,6761	381,2445	5,1205
	11,01	121,2201	452,2908	5,2848
	8,54	72,9316	480,7166	6,6612
	7,51	56,4001	514,5101	7,3598
	12,36	152,7696	927,1236	11,6184
	10,81	116,8561	962,6305	13,0801
	9,89	97,8121	901,2757	12,7581
	13,72	188,2384	1252,0872	15,3664
	12,27	150,5529	1225,0368	15,8283
	13,92	193,7664	1511,016	20,7408
S	127,83	1410,44	9160,9934	118,9728
Середнє	10,65

Загалом необхідно визначити параметри двофакторної економетричної моделі

у t = a 0 + a 1 х 1t + a 2 х 2t + ε t

Інформація, потрібна для подальших розрахунків, подана у табл. 2.4.

Оцінимо параметри лінійної двофакторної економетричної моделі за допомогою методу найменших квадратів.

Таким чином,

Оцінка коефіцієнта = 61,6583 показує, що за інших рівних умов зі збільшенням торгової площі на 1 тис. м 2 річний товарообіг збільшиться в середньому на 61,6583 млн руб.

Оцінка коефіцієнта = 2,2748 показує, що з інших рівних умов із збільшенням середньої кількості відвідувачів на 1 тис. чол. на день річний товарообіг збільшиться в середньому на 2,2748 млн. руб.

приклад 2.3.Використовуючи інформацію, подану у табл. 2.2 та 2.4, оцінити параметр однофакторної економетричної моделі

де - Центроване значення річного товарообігу-го магазину, млн руб.; - Центроване значення середньоденного числа відвідувачів t-го магазину, тис. чол. (Див. Приклади 2.1-2.2).

Рішення.Додаткова інформація, необхідна для розрахунків, подана у табл. 2.5.

Таблиця 2.5



	-48,53	-2,40	5,7720	116,6013
	-30,20	-0,41	0,1702	12,4589
	-27,34	-1,34	1,8023	36,7084
	-27,21	0,36	0,1278	-9,7288
	-12,00	-2,11	4,4627	25,3570
	0,22	-3,14	9,8753	-0,6809
	6,72	1,71	2,9156	11,4687
	20,76	0,16	0,0348	3,2992
	22,84	-0,76	0,5814	-17,413
	22,97	3,07	9,4096	70,4503
	31,55	1,62	2,6163	51,0267
	40,26	3,27	10,6766	131,5387
Сума			48,4344	431,0566

Використовуючи формулу (2.35), отримаємо

Таким чином,

http://www.cleverstudents.ru/articles/mnk.html

приклад.

Експериментальні дані про значення змінних хі унаведено у таблиці.

В результаті їх вирівнювання отримано функцію

Використовуючи метод найменших квадратів, апроксимувати ці дані лінійною залежністю y=ax+b(Знайти параметри аі b). З'ясувати, яка з двох ліній краще (у сенсі способу менших квадратів) вирівнює експериментальні дані. Зробити креслення.

Рішення.

У нашому прикладі n=5. Заповнюємо таблицю для зручності обчислення сум, що входять до формул шуканих коефіцієнтів.

Значення у четвертому рядку таблиці отримані множенням значень 2-го рядка на значення 3-го рядка для кожного номера i.

Значення у п'ятому рядку таблиці отримані зведенням у квадрат значень другого рядка для кожного номера i.

Значення останнього стовпця таблиці – це суми значень рядків.

Використовуємо формули методу найменших квадратів для знаходження коефіцієнтів аі b. Підставляємо у них відповідні значення з останнього стовпця таблиці:

Отже, y = 0.165x+2.184- пряма апроксимуюча.

Залишилося з'ясувати, яка з ліній y = 0.165x+2.184або краще апроксимує вихідні дані, тобто провести оцінку шляхом найменших квадратів.

Доказ.

Щоб при знайдених аі bфункція приймала найменше значення, необхідно, щоб у цій точці матриця квадратичної форми диференціала другого порядку для функції була позитивно визначеною. Покажемо це.

Диференціал другого порядку має вигляд:

Тобто

Отже, матриця квадратичної форми має вигляд

причому значення елементів не залежать від аі b.

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

Кутовий мінор першого порядку . Нерівність сувора, тому що точки

Яке знаходить найширше застосування у різних галузях науки та практичної діяльності. Це може бути фізика, хімія, біологія, економіка, соціологія, психологія і таке інше. Волею долі мені часто доводиться мати справу з економікою, і тому сьогодні я оформлю вам путівку до дивовижної країни під назвою Економетрика=) …Як це не хочете?! Там дуже добре – треба тільки наважитися! …Але ось те, що ви, напевно, точно хочете – так це навчитися вирішувати завдання методом найменших квадратів. І особливо старанні читачі навчаться вирішувати їх не тільки безпомилково, але ще й ДУЖЕ ШВИДКО;-) Але спочатку загальна постановка задачі+ супутній приклад:

– торгову площу продовольчого магазину, кв.м.,
- Річний товарообіг продовольчого магазину, млн. руб.

Цілком зрозуміло, що чим більша площа магазину, тим у більшості випадків буде більшим його товарообіг.

Табличні дані також можна записати у вигляді точок та зобразити у звичній для нас декартовій системі .

Відповімо на важливе питання: скільки точок потрібно якісного дослідження?

Якщо дуже просто - нам потрібно підібрати функцію, графікякою проходить якомога ближче до точок . Таку функцію називають апроксимуючою (апроксимація – наближення)або теоретичною функцією . Взагалі кажучи, тут одразу з'являється очевидний «претендент» – багаточлен високого ступеня, графік якого проходить через всі точки. Але цей варіант складний, а часто й просто некоректний (т.к. графік буде весь час «петляти» і погано відображатиме головну тенденцію).

Таким чином, розшукувана функція повинна бути досить простою і в той же час відображати залежність адекватно. Як ви здогадуєтеся, один із методів знаходження таких функцій і називається методом найменших квадратів. Спочатку розберемо його суть у загальному вигляді. Нехай деяка функція наближає експериментальні дані:

Як оцінити точність наближення? Обчислимо і різниці (відхилення) між експериментальними та функціональними значеннями (Вивчаємо креслення). Перша думка, яка спадає на думку – це оцінити, наскільки велика сума, але проблема полягає в тому, що різниці можуть бути і негативні. (наприклад, ) та відхилення внаслідок такого підсумовування будуть взаємознищуватись. Тому як оцінка точності наближення напрошується прийняти суму модуліввідхилень:

або в згорнутому вигляді: (раптом хто не знає: – це значок суми, а – допоміжна змінна-«лічильник», яка набуває значення від 1 до ).

Наближаючи експериментальні точки різними функціями, ми отримуватимемо різні значення , і очевидно, де ця сума менша – та функція і точніше.

І зараз ми повертаємося до іншого важливого моменту: як зазначалося вище, функція, що підбирається, повинна бути досить проста - але ж і таких функцій теж чимало: лінійна , гіперболічна, експоненційна, логарифмічна, квадратична і т.д. І, звичайно, тут одразу б хотілося «скоротити поле діяльності». Який клас функцій вибрати на дослідження? Примітивний, але ефективний прийом:

І по суті нам потрібно вирішити стандартне завдання – знайти мінімум функції двох змінних.

Складемо стандартну систему:

Скорочуємо кожне рівняння на «двійку» і, крім того, «розвалюємо» суми:

Примітка : самостійно проаналізуйте, чому «а» та «бе» можна винести за значок суми До речі, формально це можна зробити і із сумою

Координати точок ми знаємо? Знаємо. Суми знайти можемо? Легко. Складаємо найпростішу систему двох лінійних рівнянь із двома невідомими(«а» та «бе»). Систему вирішуємо, наприклад, методом Крамера, у результаті отримуємо стаціонарну точку . Перевіряючи достатня умова екстремумуможна переконатися, що в даній точці функція досягає саме мінімуму. Перевірка пов'язана з додатковими викладками і тому залишимо її за кадром (при необхідності кадр, що бракує, можна подивитися ). Робимо остаточний висновок:

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

Завдання

Коефіцієнти оптимальної функції знайдемо як розв'язання системи:

Таким чином, отримуємо наступну систему:

На відміну від прямий залежності товарообігу магазину від його площі, знайдена залежність є зворотній (Принцип «що більше – тим менше»), і цей факт відразу виявляється по негативному кутовому коефіцієнту. Функція повідомляє нам про те, що зі збільшення якогось показника на 1 одиницю значення залежного показника зменшується в середньомуна 0,65 одиниць. Як то кажуть, що вище ціна на гречку, то менше її продано.

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

і виконаємо креслення:

Побудована пряма називається лінією тренду (а саме – лінією лінійного тренду, тобто у загальному випадку тренд – це не обов'язково пряма лінія). Всім знайомий вислів «бути в тренді», і, гадаю, що цей термін не потребує додаткових коментарів.

Висновок: , отже, експоненційна функція наближає експериментальні точки гірше, ніж пряма .

приклад.

Експериментальні дані про значення змінних хі унаведено у таблиці.

В результаті їх вирівнювання отримано функцію

Суть методу найменших квадратів (МНК).

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

Таким чином, рішення прикладу зводиться до знаходження екстремуму функції двох змінних.

Висновок формул знаходження коефіцієнтів.

Складається та вирішується система із двох рівнянь із двома невідомими. Знаходимо приватні похідні функції за змінними аі b, Прирівнюємо ці похідні до нуля.

Вирішуємо отриману систему рівнянь будь-яким методом (наприклад методом підстановкиабо методом Крамера) та отримуємо формули для знаходження коефіцієнтів за методом найменших квадратів (МНК).

За даними аі bфункція набуває найменшого значення. Доказ цього факту наведено нижче за текстом наприкінці сторінки.

Ось і весь спосіб найменших квадратів. Формула для знаходження параметра aмістить суми ,,,і параметр n- Кількість експериментальних даних. Значення цих сум рекомендуємо обчислювати окремо. Коефіцієнт bзнаходиться після обчислення a.

Настав час згадати про вихідний приклад.

Рішення.

Значення у п'ятому рядку таблиці отримані зведенням у квадрат значень другого рядка для кожного номера i.

Значення останнього стовпця таблиці – це суми значень рядків.

Отже, y = 0.165x+2.184- пряма апроксимуюча.

Оцінка похибки способу менших квадратів.

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

Оскільки , то пряма y = 0.165x+2.184краще наближає вихідні дані.

Графічна ілюстрація методу найменших квадратів (МНК).

На графіках все чудово видно. Червона лінія – це знайдена пряма y = 0.165x+2.184, синя лінія – це , Рожеві точки - це вихідні дані.

На практиці при моделюванні різних процесів - зокрема, економічних, фізичних, технічних, соціальних - широко використовуються ті чи інші способи обчислення наближених значень функцій за відомими значеннями в деяких фіксованих точках.

Такі завдання наближення функцій часто виникають:

при побудові наближених формул для обчислення значень характерних величин досліджуваного процесу за табличними даними, отриманими в результаті експерименту;

при чисельному інтегруванні, диференціюванні, розв'язанні диференціальних рівнянь тощо;

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

щодо значень характерних величин процесу поза розглядуваного інтервалу, зокрема при прогнозуванні.

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

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

Excel для побудови регресій є дві можливості.

Додавання обраних регресій (ліній тренду - trendlines) у діаграму, побудовану на основі таблиці даних для досліджуваної характеристики процесу (доступне лише за наявності побудованої діаграми);

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

Додавання ліній тренду до діаграми

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

будувати на основі методу найменших квадратів і додавати в діаграму п'ять типів регресій, які з тим чи іншим ступенем точності моделюють досліджуваний процес;

додавати до діаграми рівняння побудованої регресії;

визначати ступінь відповідності обраної регресії даних, що відображаються на діаграмі.

На основі даних діаграми Excel дозволяє отримувати лінійний, поліноміальний, логарифмічний, статечний, експоненційний типи регресій, які задаються рівнянням:

y = y(x)

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

1 . Лінійна регресія хороша при моделюванні характеристик, значення яких збільшуються або зменшуються з постійною швидкістю. Це найпростіша у побудові модель досліджуваного процесу. Вона будується відповідно до рівняння:

y = mx + b

де m – тангенс кута нахилу лінійної регресії до осі абсцис; b - координата точки перетину лінійної регресії з віссю ординат.

2 . Поліноміальна лінія тренду корисна для опису характеристик, що мають кілька яскраво виражених екстремумів (максимумів та мінімумів). Вибір ступеня полінома визначається кількістю екстремумів досліджуваної характеристики. Так, поліном другого ступеня може добре описати процес, що має лише один максимум або мінімум; поліном третього ступеня - трохи більше двох екстремумів; поліном четвертого ступеня - трохи більше трьох екстремумів тощо.

У цьому випадку лінія тренду будується відповідно до рівняння:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

де коефіцієнти c0, c1, c2, c6 - константи, значення яких визначаються в ході побудови.

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

y = c ln(x) + b

4 . Ступінна лінія тренду дає хороші результати, якщо значення досліджуваної залежності характеризуються постійною зміною швидкості зростання. Прикладом такої залежності може бути графік рівноприскореного руху автомобіля. Якщо серед даних зустрічаються нульові чи негативні значення, використовувати статечну лінію тренда не можна.

Будується відповідно до рівняння:

y = c xb

де коефіцієнти b, с – константи.

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

Будується відповідно до рівняння:

y = c ebx

де коефіцієнти b, с – константи.

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

Визначається за такою формулою:

Для додавання лінії тренду до ряду даних слід:

активізувати побудовану з урахуванням низки даних діаграму, т. е. клацнути у межах області діаграми. У головному меню з'явиться пункт Діаграма;

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

Ці ж дії легко реалізуються, якщо навести покажчик миші на графік, що відповідає одному з рядів даних, та клацнути правою кнопкою миші; у контекстному меню, що з'явилося, вибрати команду Додати лінію тренда. На екрані з'явиться діалогове вікно Лінія тренду з відкритою вкладкою Тип (рис. 1).

Після цього необхідно:

Вибрати на вкладці Тип необхідний тип лінії тренда (за промовчанням вибирається тип Лінійний). Для Поліноміального типу в полі Ступінь слід задати ступінь обраного полінома.

1 . У полі Побудований ряд перераховані всі ряди даних аналізованої діаграми. Для додавання лінії тренда до конкретного ряду даних слід у полі Побудований на ряді вибрати його ім'я.

При необхідності, перейшовши на вкладку Параметри (мал. 2), можна для лінії тренда задати такі параметри:

змінити назву лінії тренду в полі Назва апроксимуючої (згладженої) кривої.

задати кількість періодів (вперед чи назад) для прогнозу у полі Прогноз;

вивести в ділянку діаграми рівняння лінії тренду, для чого слід включити прапорець показати рівняння на діаграмі;

вивести в ділянку діаграми значення достовірності апроксимації R2, для чого слід включити прапорець помістити на діаграму величину достовірності апроксимації (R^2);

задати точку перетину лінії тренду з віссю Y, для чого слід включити прапорець перетин кривої з віссю Y в точці;

клацнути на кнопці OK, щоб закрити діалогове вікно.

Для того, щоб розпочати редагування вже побудованої лінії тренду, існує три способи:

скористатися командою Виділена лінія тренду з меню Формат, вибравши попередньо лінію тренда;

вибрати команду Формат лінії тренда з контекстного меню, яке викликається клацанням правої кнопки миші по лінії тренду;

подвійним клацанням по лінії тренду.

На екрані з'явиться діалогове вікно Формат лінії тренду (рис. 3), що містить три вкладки: Вид, Тип, Параметри, причому вміст останніх двох повністю збігається з аналогічними вкладками діалогового вікна Лінія тренду (рис.1-2). На вкладці Вигляд можна задати тип лінії, її колір та товщину.

Для видалення вже побудованої лінії тренда слід вибрати лінію тренда, що видаляється, і натиснути клавішу Delete.

Перевагами розглянутого інструменту регресійного аналізу є:

відносна легкість побудови на діаграмах лінії тренду без створення нею таблиці даних;

досить широкий перелік типів запропонованих ліній трендів, причому цей перелік входять найчастіше використовувані типи регресії;

можливість прогнозування поведінки досліджуваного процесу на довільне (не більше здорового глузду) кількість кроків уперед, і навіть назад;

можливість одержання рівняння лінії тренда в аналітичному вигляді;

можливість, за потреби, отримання оцінки достовірності проведеної апроксимації.

До недоліків можна віднести такі моменти:

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

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

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

Лініями тренду можна доповнити ряди даних, представлені на діаграмах типу графік, гістограма, плоскі ненормовані діаграми з областями, лінійчасті, точкові, пухирцеві та біржові.

Не можна доповнити лініями тренду ряди даних на об'ємних, нормованих, пелюсткових, кругових та кільцевих діаграмах.

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

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

В Excel є кілька функцій для побудови лінійної регресії, зокрема:

ТЕНДЕНЦІЯ;

Нахил і відрізок.

А також кілька функцій для побудови експоненційної лінії тренду, зокрема:

ЛДРФПРИБЛ.

Слід зазначити, що прийоми побудови регресій за допомогою функцій ТЕНДЕНЦІЯ та РОСТ практично збігаються. Те саме можна сказати і про пару функцій Лінейн і ЛГРФПРИБЛ. Для чотирьох цих функцій під час створення таблиці значень використовуються такі можливості Excel, як формули масивів, що дещо захаращує процес побудови регресій. Зауважимо також, що побудова лінійної регресії, на наш погляд, найлегше здійснити за допомогою функцій НАКЛОН і ВІДРІЗОК, де перша визначає кутовий коефіцієнт лінійної регресії, а друга - відрізок, що відсікається регресією на осі ординат.

Достоїнствами інструменту вбудованих функцій для регресійного аналізу є:

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

стандартна методика побудови ліній тренду на основі сформованих рядів даних;

можливість прогнозування поведінки досліджуваного процесу необхідну кількість кроків уперед чи назад.

А до недоліків відноситься те, що в Excel немає вбудованих функцій для створення інших (крім лінійного та експонентного) типів ліній тренду. Ця обставина часто дозволяє підібрати досить точну модель досліджуваного процесу, і навіть отримати близькі до реальності прогнози. Крім того, при використанні функцій ТЕНДЕНЦІЯ та РОСТ не відомі рівняння ліній тренду.

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

Приклади вирішення конкретних завдань

Розглянемо рішення конкретних завдань за допомогою перерахованих інструментів Excel.

Завдання 1

З таблицею даних про прибуток автотранспортного підприємства за 1995-2002 рр. необхідно виконати такі дії.

Побудувати діаграму.

У діаграму додати лінійну та поліноміальну (квадратичну та кубічну) лінії тренду.

Використовуючи рівняння ліній тренду, отримати табличні дані щодо прибутку підприємства для кожної лінії тренду за 1995-2004 роки.

Скласти прогноз щодо прибутку підприємства на 2003 та 2004 роки.

Розв'язання задачі

У діапазон осередків A4:C11 робочого листа Excel вводимо робочу таблицю, подану на рис. 4.

Виділивши діапазон осередків В4: С11, будуємо діаграму.

Активізуємо побудовану діаграму та за описаною вище методикою після вибору типу лінії тренду в діалоговому вікні Лінія тренду (див. рис. 1) по черзі додаємо в діаграму лінійну, квадратичну та кубічну лінії тренду. У цьому ж діалоговому вікні відкриваємо вкладку Параметри (див. рис. 2), в полі Назва апроксимуючої (згладженої) кривої вводимо найменування тренда, що додається, а в полі Прогноз вперед на: періодів задаємо значення 2, так як планується зробити прогноз по прибутку на два року наперед. Для виведення в області діаграми рівняння регресії та значення достовірності апроксимації R2 включаємо прапорці показувати рівняння на екрані та помістити на діаграму величину достовірності апроксимації (R^2). Для кращого візуального сприйняття змінюємо тип, колір та товщину побудованих ліній тренду, для чого скористаємось вкладкою Вид діалогового вікна Формат лінії тренду (див. рис. 3). Отримана діаграма з доданими лініями тренду представлена на рис. 5.

Для отримання табличних даних щодо прибутку підприємства для кожної лінії тренду за 1995-2004 роки. скористаємось рівняннями ліній тренду, представленими на рис. 5. Для цього в комірки діапазону D3:F3 вводимо текстову інформацію про тип обраної лінії тренду: Лінійний тренд, Квадратичний тренд, Кубічний тренд. Далі вводимо в комірку D4 формулу лінійної регресії і, використовуючи маркер заповнення, копіюємо цю формулу з відносними посиланнями діапазону комірок D5:D13. Слід зазначити, що кожному осередку з формулою лінійної регресії з діапазону осередків D4:D13 як аргумент стоїть відповідний осередок з діапазону A4:A13. Аналогічно для квадратичної регресії заповнюється діапазон осередків E4: E13, а кубічної регресії - діапазон осередків F4: F13. Таким чином, складено прогноз щодо прибутку підприємства на 2003 та 2004 роки. за допомогою трьох трендів. Отримана таблиця значень представлена рис. 6.

Завдання 2

Побудувати діаграму.

У діаграму додати логарифмічну, статечну та експоненційну лінії тренду.

Вивести рівняння отриманих ліній тренду, і навіть величини достовірності апроксимації R2 кожної з них.

Використовуючи рівняння ліній тренду, отримати табличні дані про прибуток підприємства кожної лінії тренду за 1995-2002 гг.

Скласти прогноз про прибуток підприємства на 2003 та 2004 рр., використовуючи ці лінії тренду.

Розв'язання задачі

Дотримуючись методики, наведеної при вирішенні задачі 1, отримуємо діаграму з доданими до неї логарифмічної, статечної та експоненційної лініями тренду (рис. 7). Далі, використовуючи отримані рівняння ліній тренду, заповнюємо таблицю значень із прибутку підприємства, включаючи прогнозовані значення на 2003 та 2004 роки. (Рис. 8).

На рис. 5 та рис. видно, що моделі з логарифмічним трендом відповідає найменше значення достовірності апроксимації.

R2 = 0,8659

Найбільші значення R2 відповідають моделям з поліноміальним трендом: квадратичним (R2 = 0,9263) і кубічним (R2 = 0,933).

Завдання 3

З таблицею даних про прибуток автотранспортного підприємства за 1995-2002 рр., що наведена в задачі 1, необхідно виконати такі дії.

Отримати ряди даних для лінійної та експоненційної лінії тренду з використанням функцій ТЕНДЕНЦІЯ та РОСТ.

Використовуючи функції ТЕНДЕНЦІЯ та РОСТ, скласти прогноз про прибуток підприємства на 2003 та 2004 роки.

Для вихідних даних та отриманих рядів даних побудувати діаграму.

Розв'язання задачі

Скористайтеся робочою таблицею задачі 1 (див. рис. 4). Почнемо з функції ТЕНДЕНЦІЯ:

виділяємо діапазон осередків D4:D11, який слід заповнити значеннями функції ТЕНДЕНЦІЯ, що відповідають відомим даним про прибуток підприємства;

викликаємо команду Функція з меню Вставка. У діалоговому вікні Майстер функцій виділяємо функцію ТЕНДЕНЦІЯ з категорії Статистичні, після чого клацаємо по кнопці ОК. Цю операцію можна здійснити натисканням кнопки (Вставка функції) стандартної панелі інструментів.

У діалоговому вікні, що з'явилося Аргументи функції вводимо в поле Відомі_значення_y діапазон осередків C4:C11; у полі Відомі_значення_х - діапазон осередків B4: B11;

щоб формула, що вводиться, стала формулою масиву, використовуємо комбінацію клавіш + + .

Введена нами формула у рядку формул матиме вигляд: =(ТЕНДЕНЦІЯ(C4:C11;B4:B11)).

В результаті діапазон комірок D4:D11 заповнюється відповідними значеннями функції ТЕНДЕНЦІЯ (рис. 9).

Для складання прогнозу про прибуток підприємства на 2003 та 2004 роки. необхідно:

виділити діапазон осередків D12:D13, куди заноситимуться значення, прогнозовані функцією ТЕНДЕНЦІЯ.

викликати функцію ТЕНДЕНЦІЯ і в діалоговому вікні, що з'явилося Аргументи функції ввести в поле Відомі_значення_y - діапазон осередків C4:C11; у полі Відомі_значення_х - діапазон осередків B4: B11; а в полі Нові_значення_х - діапазон осередків B12: B13.

перетворити цю формулу на формулу масиву, використовуючи комбінацію клавіш Ctrl + Shift + Enter.

Введена формула матиме вигляд: =(ТЕНДЕНЦІЯ(C4:C11;B4:B11;B12:B13)), а діапазон осередків D12:D13 заповниться прогнозованими значеннями функції ТЕНДЕНЦІЯ (див. рис. 9).

Аналогічно заповнюється ряд даних за допомогою функції РОСТ, яка використовується при аналізі нелінійних залежностей і працює так само, як її лінійний аналог ТЕНДЕНЦІЯ.

На рис.10 представлена таблиця як показу формул.

Для вихідних даних та отриманих рядів даних побудовано діаграму, зображену на рис. 11.

Завдання 4

З таблицею даних про вступ до диспетчерської служби автотранспортного підприємства заявок на послуги за період з 1 до 11 числа поточного місяця необхідно виконати такі дії.

Отримати ряди даних для лінійної регресії: використовуючи функції НАКЛОН та ВІДРІЗОК; використовуючи функцію Лінейн.

Отримати ряд даних для експоненційної регресії з використанням функції ЛГРФПРИБЛ.

Використовуючи вищезгадані функції, скласти прогноз про надходження заявок до диспетчерської служби на період з 12 до 14 числа поточного місяця.

Для вихідних та отриманих рядів даних побудувати діаграму.

Розв'язання задачі

Зазначимо, що, на відміну від функцій ТЕНДЕНЦІЯ і ЗРОСТАННЯ, жодна з перерахованих вище функцій (НАХИЛ, ВІДРІЗОК, ЛІНІЙН, ЛГРФПРИБ) не є регресією. Ці функції грають лише допоміжну роль, визначаючи необхідні параметри регресії.

Для лінійної та експоненційної регресій, побудованих за допомогою функцій НАКЛОН, ВІДРІЗОК, ЛІНІЙН, ЛГРФПРИБ, зовнішній вигляд їх рівнянь завжди відомий, на відміну від лінійної та експоненційної регресій, що відповідають функціям ТЕНДЕНЦІЯ та РОЗДІЛ.

1 . Побудуємо лінійну регресію, яка має рівняння:

y = mx+b

за допомогою функцій НАХИЛ і ВІДРІЗОК, причому кутовий коефіцієнт регресії m визначається функцією НАХИЛ, а вільний член b - функцією ВІДРІЗОК.

Для цього здійснюємо такі дії:

заносимо вихідну таблицю в діапазон осередків A4: B14;

значення параметра m буде визначатися в комірці С19. Вибираємо з категорії Статистичні функції Нахил; заносимо діапазон осередків B4:B14 у поле відомі_значення_y та діапазон осередків А4:А14 у поле відомі_значення_х. У комірку С19 буде введена формула: = НАХЛАН(B4:B14;A4:A14);

за аналогічною методикою визначається значення параметра b у комірці D19. І її вміст матиме вигляд: = ВІДРІЗОК(B4:B14;A4:A14). Таким чином, необхідні для побудови лінійної регресії значення параметрів m і b зберігатимуться відповідно в осередках C19, D19;

далі заносимо в комірку С4 формулу лінійної регресії як: =$C*A4+$D. У цій формулі осередки С19 та D19 записані з абсолютними посиланнями (адреса осередку не повинна змінюватися при можливому копіюванні). Знак абсолютного посилання $ можна набити або з клавіатури або за допомогою клавіші F4, попередньо встановивши курсор на адресу комірки. Скориставшись маркером заповнення, скопіюємо цю формулу в діапазон осередків С4:С17. Отримуємо потрібний ряд даних (рис. 12). У зв'язку з тим, що кількість заявок - ціле число, слід встановити на вкладці Число вікна Формат осередків числовий формат із числом десяткових знаків 0.

2 . Тепер збудуємо лінійну регресію, задану рівнянням:

y = mx+b

за допомогою функції ЛІНІЙН.

Для цього:

вводимо в діапазон осередків C20:D20 функцію ЛІНІЙН як формулу масиву: =(ЛІНЕЙН(B4:B14;A4:A14)). В результаті отримуємо в комірці C20 значення параметра m, а в комірці D20 значення параметра b;

вводимо в комірку D4 формулу: = $ C * A4 + $ D;

копіюємо цю формулу за допомогою маркера заповнення в діапазон осередків D4: D17 і отримуємо ряд даних, що шукається.

3 . Будуємо експоненційну регресію, яка має рівняння:

за допомогою функції ЛГРФПРИБЛ воно виконується аналогічно:

в діапазон осередків C21:D21 вводимо функцію ЛГРФПРИБЛ як формулу масиву: =( ЛГРФПРИБЛ (B4:B14;A4:A14)). При цьому в комірці C21 буде визначено значення параметра m, а в комірці D21 значення параметра b;

у комірку E4 вводиться формула: =$D*$C^A4;

за допомогою маркера заповнення ця формула копіюється в діапазон клітин E4:E17, де і розташується ряд даних для експоненційної регресії (див. рис. 12).

На рис. 13 наведено таблицю, де видно використовувані нами функції з необхідними діапазонами осередків, а також формули.

Величина R 2 називається коефіцієнтом детермінації.

Завданням побудови регресійної залежності є знаходження вектора коефіцієнтів m моделі (1) при якому коефіцієнт R набуває максимального значення.

Для оцінки значущості R застосовується F-критерій Фішера, що обчислюється за формулою

де n- розмір вибірки (кількість експериментів);

k – число коефіцієнтів моделі.

Якщо F перевищує деяке критичне значення для даних nі kі прийнятої довірчої ймовірності, величина R вважається істотною. Таблиці критичних значень F наводяться у довідниках математичної статистики.

Отже, значимість R визначається як його величиною, а й співвідношенням між кількістю експериментів і кількістю коефіцієнтів (параметрів) моделі. Дійсно, кореляційне відношення для n=2 для простої лінійної моделі дорівнює 1 (через 2 точки на площині завжди можна провести єдину пряму). Однак, якщо експериментальні дані є випадковими величинами, довіряти такому значенню R слід з великою обережністю. Зазвичай отримання значимого R і достовірної регресії прагнуть до того, щоб кількість експериментів істотно перевищувала кількість коефіцієнтів моделі (n>k).

Для побудови лінійної регресійної моделі необхідно:

1) підготувати список з n рядків і m стовпців, що містить експериментальні дані (стовпець, що містить вихідну величину Yмає бути або першим, або останнім у списку); Наприклад візьмемо дані попереднього завдання, додавши стовпець під назвою "№ періоду", пронумеруємо номери періодів від 1 до 12. (це значення Х)

2) звернутися до меню Дані/Аналіз даних/Регресія

Якщо пункт "Аналіз даних" у меню "Сервіс" відсутній, слід звернутися до пункту "Надбудови" того ж меню і встановити прапорець "Пакет аналізу".

3) у діалоговому вікні "Регресія" задати:

· Вхідний інтервал Y;

· Вхідний інтервал X;

· Вихідний інтервал - верхній лівий осередок інтервалу, в який будуть розміщуватися результати обчислень (рекомендується розмістити на новому робочому аркуші);

4) натиснути "Ok" та проаналізувати результати.

Після вирівнювання отримаємо функцію наступного виду: g(x) = x + 1 3 + 1 .

Ми можемо апроксимувати ці дані за допомогою лінійної залежності y = a x + b, обчисливши відповідні параметри. Для цього нам потрібно буде застосувати так званий метод найменших квадратів. Також потрібно зробити креслення, щоб перевірити, яка лінія краще вирівнюватиме експериментальні дані.

У чому полягає МНК (метод найменших квадратів)

Головне, що нам потрібно зробити, – знайти такі коефіцієнти лінійної залежності, при яких значення функції двох змінних F (a, b) = ∑ i = 1 n (y i - (a x i + b)) 2 буде найменшим. Інакше кажучи, при певних значеннях a і b сума квадратів відхилень представлених даних від прямої буде мати мінімальне значення. У цьому полягає сенс методу найменших квадратів. Все, що нам треба зробити для вирішення прикладу, – це знайти екстремум функції двох змінних.

Як вивести формули для обчислення коефіцієнтів

Для того щоб вивести формули для обчислення коефіцієнтів, потрібно скласти та вирішити систему рівнянь із двома змінними. Для цього ми обчислюємо окремі похідні вирази F (a, b) = ∑ i = 1 n (y i - (a x i + b)) 2 по a та b і прирівнюємо їх до 0 .

δ F (a , b) δ a = 0 δ F (a , b) δ b = 0 ⇔ - 2 ∑ i = 1 n (y i - (a x i + b)) x i = 0 - 2 ∑ i = 1 n ( y i - (a x i + b)) = 0 ⇔ a ∑ i = 1 n x i 2 + b ∑ i = 1 n x i = ∑ i = 1 n x i y i a ∑ i = 1 n x i + ∑ i = 1 n b = ∑ i = 1 ∑ i = 1 n x i 2 + b ∑ i = 1 n x i = ∑ i = 1 n x i y i a ∑ i = 1 n x i + n b = ∑ i = 1 n y i

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

n ∑ i = 1 n x i y i - ∑ i = 1 n x i ∑ i = 1 n y i n ∑ i = 1 n - ∑ i = 1 n x i 2 b = ∑ i = 1 n y i - a ∑ i = 1 n x i n

Ми вирахували значення змінних, при яких функція
F (a, b) = ∑ i = 1 n (y i - (a x i + b)) 2 прийме мінімальне значення. У третьому пункті ми доведемо, чому воно є таким.

І це застосування методу найменших квадратів практично. Його формула, яка застосовується для пошуку параметра a включає ∑ i = 1 n x i , ∑ i = 1 n y i , ∑ i = 1 n x i y i , ∑ i = 1 n x i 2 , а також параметр
n – їм зазначено кількість експериментальних даних. Радимо вам обчислювати кожну суму окремо. Значення коефіцієнта b обчислюється відразу після a.

Звернемося знову до вихідного прикладу.

Приклад 1

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

	i = 1	i = 2	i = 3	i = 4	i = 5	∑ i = 1 5
x i	0	1	2	4	5	12
y i	2 , 1	2 , 4	2 , 6	2 , 8	3	12 , 9
x i y i	0	2 , 4	5 , 2	11 , 2	15	33 , 8
x i 2	0	1	4	16	25	46

Рішення

Четвертий рядок включає дані, отримані при множенні значень з другого рядка на значення третього для кожного окремого i . П'ятий рядок містить дані з другого, зведені у квадрат. В останньому стовпці наводяться суми значень окремих рядків.

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

n ∑ i = 1 n x i y i - ∑ i = 1 n x i ∑ i = 1 n y i n ∑ i = 1 n - ∑ i = 1 n x i 2 b = ∑ i = 1 n y i - a ∑ i = 1 n x i n 8 - 12 · 12 , 9 5 · 46 - 12 2 b = 12 , 9 - a · 12 5 ⇒ a ≈ 0 , 165 b ≈ 2 , 184

У нас вийшло, що потрібна пряма апроксимує виглядатиме як y = 0 , 165 x + 2 , 184 . Тепер нам треба визначити, яка лінія краще апроксимувати дані – g(x) = x + 1 3 + 1 або 0 , 165 x + 2 , 184 . Зробимо оцінку за допомогою методу найменших квадратів.

Щоб обчислити похибку, нам треба знайти суми квадратів відхилень даних від прямих σ 1 = ∑ i = 1 n (y i - (a x i + b i)) 2 і σ 2 = ∑ i = 1 n (y i - g (x i)) 2 , мінімальне значення буде відповідати більш відповідної лінії.

σ 1 = ∑ i = 1 n (y i - (a x i + b i)) 2 = = ∑ i = 1 5 (y i - (0 , 165 x i + 2 , 184)) 2 ≈ 0, 019 σ 2 = ∑ i = 1 n (y i - g (x i)) 2 = = ∑ i = 1 5 (y i - (x i + 1 3 + 1)) 2 ≈ 0 , 096

Відповідь:оскільки σ 1< σ 2 , то прямой, наилучшим образом аппроксимирующей исходные данные, будет
y = 0,165 x + 2,184.

Метод найменших квадратів наочно показано на графічній ілюстрації. За допомогою червоної лінії відзначено пряму g(x) = x + 1 3 + 1, синю – y = 0, 165 x + 2, 184 . Вихідні дані позначені рожевими крапками.

Пояснимо, навіщо саме потрібні наближення такого виду.

Вони можуть бути використані в завданнях, що вимагають згладжування даних, а також у тих, де дані треба інтерполювати або екстраполювати. Наприклад, у задачі, розібраній вище, можна було б знайти значення спостерігається величини y при x = 3 або x = 6 . Таким прикладам ми присвятили окрему статтю.

Доказ методу МНК

Щоб функція прийняла мінімальне значення при обчислених a і b потрібно, щоб у цій точці матриця квадратичної форми диференціала функції виду F (a, b) = ∑ i = 1 n (y i - (a x i + b)) 2 була позитивно визначеною. Покажемо, як це має виглядати.

Приклад 2

Ми маємо диференціал другого порядку наступного виду:

d 2 F (a ; b) = δ 2 F (a ; b) δ a 2 d 2 a + 2 δ 2 F (a ; b) δ a δ b d a d b + δ 2 F (a ; b) δ b 2 d 2 b

Рішення

δ 2 F (a ; b) δ a 2 = δ δ F (a ; b) δ a δ a = = δ - 2 ∑ i = 1 n (y i - (a x i + b)) x i δ a = 2 ∑ i = 1 n (x i) 2 δ 2 F (a ; b) δ a δ b = δ δ F (a ; b) δ a δ b = = δ - 2 ∑ i = 1 n (y i - (a x i + b) ) x i δ b = 2 ∑ i = 1 n x i δ 2 F (a ; b) δ b 2 = δ δ F (a ; b) δ b δ b = δ - 2 ∑ i = 1 n (y i - (a x i + b)) δ b = 2 ∑ i = 1 n (1) = 2 n

Інакше кажучи, можна записати так: d 2 F (a; b) = 2 ∑ i = 1 n (x i) 2 d 2 a + 2 · 2 ∑ x i i = 1 n d a d b + (2 n) d 2 b .

Ми отримали матрицю квадратичної форми виду M = 2 ∑ i = 1 n (x i) 2 2 ∑ i = 1 n x i 2 ∑ i = 1 n x i 2 n .

У цьому випадку значення окремих елементів не змінюватимуться залежно від a та b. Чи ця матриця є позитивно визначеною? Щоб відповісти на це питання, перевіримо, чи є її кутові мінори позитивними.

Обчислюємо кутовий мінор першого порядку: 2 ∑ i = 1 n (xi) 2 > 0 . Оскільки точки x i не збігаються, то нерівність є суворою. Матимемо це на увазі при подальших розрахунках.

Обчислюємо кутовий мінор другого порядку:

d e t (M) = 2 ∑ i = 1 n (x i) 2 2 ∑ i = 1 n x i 2 ∑ i = 1 n x i 2 n = 4 n ∑ i = 1 n (xi) 2 - ∑ i = 1 n x i 2

Після цього переходимо до доказу нерівності n ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2 > 0 за допомогою математичної індукції.

Перевіримо, чи буде ця нерівність справедливою за довільного n . Візьмемо 2 і підрахуємо:

2 ∑ i = 1 2 (x i) 2 - ∑ i = 1 2 x i 2 = 2 x 1 2 + x 2 2 - x 1 + x 2 2 = = x 1 2 - 2 x 1 x 2 + x 2 2 = x 1 + x 2 2 > 0

У нас вийшла правильна рівність (якщо значення x 1 і x 2 не співпадатимуть).

Зробимо припущення, що це нерівність буде правильним для n , тобто. n ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2 > 0 – справедливо.
Тепер доведемо справедливість при n + 1, тобто. що (n + 1) ∑ i = 1 n + 1 (x i) 2 - ∑ i = 1 n + 1 x i 2 > 0, якщо правильно n ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2 > 0 .

Обчислюємо:

(n + 1) ∑ i = 1 n + 1 (x i) 2 - ∑ i = 1 n + 1 x i 2 = = (n + 1) ∑ i = 1 n (x i) 2 + x n + 1 2 - ∑ i = 1 n x i + x n + 1 2 = = n ∑ i = 1 n (x i) 2 + n · x n + 1 2 + ∑ i = 1 n (x i) 2 + x n + 1 2 - - ∑ i = 1 n x i 2 + 2 x n + 1 ∑ i = 1 n x i + x n + 1 2 = = ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2 + n · x n + 1 2 - x n + 1 ∑ i = 1 n x i + ∑ i = 1 n (x i) 2 = = ∑ i = 1 n (xi) 2 - ∑ i = 1 n x i 2 + x n + 1 2 - 2 x n + 1 x 1 + x 1 2 + + x n + 1 2 - 2 x n + 1 x 2 + x 2 2 +. . . + x n + 1 2 - 2 x n + 1 x 1 + x n 2 = = n ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2 + + (x n + 1 - x 1) 2 + (x n + 1 - x 2) 2+. . . + (x n – 1 – x n) 2 > 0

Вираз, укладений у фігурні дужки, буде більше 0 (виходячи з того, що ми припускали в пункті 2), та інші доданки будуть більшими за 0, оскільки всі вони є квадратами чисел. Ми довели нерівність.

Відповідь:знайдені a і b відповідатимуть найменшому значенню функції F (a , b) = ∑ i = 1 n (y i - (a x i + b)) 2 , отже, є шуканими параметрами методу найменших квадратів (МНК).

Якщо ви помітили помилку в тексті, будь ласка, виділіть її та натисніть Ctrl+Enter

приклад.

Експериментальні дані про значення змінних хі унаведено у таблиці.

В результаті їх вирівнювання отримано функцію

Суть методу найменших квадратів (МНК).

Таким чином, рішення прикладу зводиться до знаходження екстремуму функції двох змінних.

Висновок формул знаходження коефіцієнтів.

Вирішуємо отриману систему рівнянь будь-яким методом (наприклад методом підстановкиабо ) і отримуємо формули для знаходження коефіцієнтів методом найменших квадратів (МНК).

За даними аі bфункція набуває найменшого значення. Доказ цього факту наведено.

Ось і весь спосіб найменших квадратів. Формула для знаходження параметра aмістить суми , , , та параметр n- Кількість експериментальних даних. Значення цих сум рекомендуємо обчислювати окремо. Коефіцієнт bзнаходиться після обчислення a.

Настав час згадати про вихідний приклад.

Рішення.

Значення у п'ятому рядку таблиці отримані зведенням у квадрат значень другого рядка для кожного номера i.

Значення останнього стовпця таблиці – це суми значень рядків.

Отже, y = 0.165x+2.184- пряма апроксимуюча.

Оцінка похибки способу менших квадратів.

Оскільки , то пряма y = 0.165x+2.184краще наближає вихідні дані.

Графічна ілюстрація методу найменших квадратів (МНК).

Навіщо це потрібно, до чого всі ці апроксимації?

Я особисто використовую для вирішення завдань згладжування даних, задач інтерполяції та екстраполяції (у вихідному прикладі могли б попросити знайти значення спостережуваної величини yпри x=3або при x=6методом МНК). Але докладніше поговоримо про це пізніше в іншому розділі сайту.

Доказ.