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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

P.S.

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

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

Метод найменших квадратів використовується для оцінки параметрів рівняння регресії.

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

вибір форми зв'язку (виду аналітичного рівняння регресії);
оцінку параметрів рівняння;
оцінку якості аналітичного рівняння регресії.

Найбільш часто для опису статистичного зв'язку ознак використовується лінійна форма. Увага до лінійного зв'язку пояснюється чіткою економічною інтерпретацією її параметрів, обмеженою варіацією змінних і тим, що в більшості випадків нелінійні форми зв'язку для виконання розрахунків перетворять (шляхом логарифмування або заміни змінних) в лінійну форму.
У разі лінійної парної зв'язку рівняння регресії набуде вигляду: y i \u003d a + b · x i + u i. Параметри даного рівняння а і b оцінюються за даними статистичного спостереження x і y. Результатом такої оцінки є рівняння:, де, - оцінки параметрів a і b, - значення результативної ознаки (змінної), отримане за рівнянням регресії (розрахункове значення).

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

Завдання оцінювання параметрів лінійного парного рівняння методом найменших квадратів полягає в наступному: отримати такі оцінки параметрів,, при яких сума квадратів відхилень фактичних значень результативної ознаки - y i від розрахункових значень - мінімальна.
формально критерій МНК можна записати так: .

Класифікація методів найменших квадратів

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

Проілюструємо суть класичного методу найменших квадратів графічно. Для цього побудуємо точковий графік за даними спостережень (x i, y i, i \u003d 1; n) в прямокутній системі координат (такий точковий графік називають кореляційним полем). Спробуємо підібрати пряму лінію, яка найближче розташована до точок кореляційного поля. Відповідно до методу найменших квадратів лінія вибирається так, щоб сума квадратів відстаней по вертикалі між точками кореляційного поля і цією лінією була б мінімальною.

Математична запис даної задачі: .
Значення y i і x i \u003d 1 ... n нам відомі, це дані спостережень. У функції S вони являють собою константи. Змінними в даній функції є шукані оцінки параметрів -,. Щоб знайти мінімум функції 2-ух змінних необхідно обчислити приватні похідні даної функції по кожному з параметрів і прирівняти їх нулю, тобто .
В результаті отримаємо систему з 2-ух нормальних лінійних рівнянь:
Вирішуючи цю систему, знайдемо шукані оцінки параметрів:

Правильність розрахунку параметрів рівняння регресії може бути перевірена порівнянням сум (можливо деяке розбіжність через округлення розрахунків).
Для розрахунку оцінок параметрів, можна побудувати таблицю 1.
Знак коефіцієнта регресії b вказує напрямок зв'язку (якщо b\u003e 0, зв'язок пряма, якщо b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Формально значення параметра а - середнє значення y при х рівному нулю. Якщо ознака-фактор не має і не може мати нульового значення, то вищевказана трактування параметра а не має сенсу.

Оцінка тісноти зв'язку між ознаками здійснюється за допомогою коефіцієнта лінійної парної кореляції - r x, y. Він може бути розрахований за формулою: . Крім того, коефіцієнт лінійної парної кореляції може бути визначений через коефіцієнт регресії b: .
Область допустимих значень лінійного коефіцієнта парної кореляції від -1 до +1. Знак коефіцієнта кореляції вказує напрямок зв'язку. Якщо r x, y\u003e 0, то зв'язок прямий; якщо r x, y<0, то связь обратная.
Якщо даний коефіцієнт за модулем близький до одиниці, то зв'язок між ознаками може бути інтерпретована як досить тісний лінійна. Якщо його модуль дорівнює одиниці ê r x, y ê \u003d 1, то зв'язок між ознаками функціональна лінійна. Якщо ознаки х і y лінійно незалежні, то r x, y близький до 0.
Для розрахунку r x, y можна використовувати також таблицю 1.

Для оцінки якості отриманого рівняння регресії розраховують теоретичний коефіцієнт детермінації - R 2 yx:

,
де d 2 - пояснена рівнянням регресії дисперсія y;
e 2 - залишкова (непояснена рівнянням регресії) дисперсія y;
s 2 y - загальна (повна) дисперсія y.
Коефіцієнт детермінації характеризує частку варіації (дисперсії) результативної ознаки y, пояснюється регресією (а, отже, і чинником х), в загальній варіації (дисперсії) y. Коефіцієнт детермінації R 2 yx приймає значення від 0 до 1. Відповідно величина 1-R 2 yx характеризує частку дисперсії y, викликану впливом інших неврахованих в моделі факторів і помилками специфікації.
При парній лінійної регресії R 2 yx \u003d r 2 yx.

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

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

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

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

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

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

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

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

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

КОРРЕЛ(массів1;массів2),

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

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

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 \u003d КОРРЕЛ (B3: J3; B2: J2).

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

B5 \u003d $ 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 \u003d const;

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

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

Виберемо в якості апроксимуючої залежності поліном другого ступеня (рис. 11) і виведемо рівняння, що описує цей поліном на графік (рис. 12). Отримана діаграма представлена \u200b\u200bна рис. 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) представлена \u200b\u200bв таблиці

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) \u003d At \u200b\u200b4 + 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 \u003d $ A $ 7 * B2 ^ 4 + $ B $ 7 * B2 ^ 3 + $ C $ 7 * B2 ^ 2 + $ D $ 7 * B2 + $ E $ 7.

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

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

B5 \u003d (B4-B3) ^ 2,

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

F7 \u003d СУММ (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 с.

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

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

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

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

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

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

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

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

суть методу

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

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

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

При будь-апроксимації особливої \u200b\u200bважливості набуває оцінка її точності. Позначимо через e i різниця (відхилення) між функціональними і експериментальними значеннями для точки x i, т. Е. E i \u003d 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-ма невідомими виду:

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

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

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

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

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

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

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

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

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

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

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

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

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

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