Метод найменших квадратів в екселя приклад. Лінійний парний регресійний аналіз. Кілька слів про коректність вихідних даних, використовуваних для передбачення

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

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

Включення надбудови «Пошук рішення»

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

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

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

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

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

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

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

Рішення

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

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

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

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

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

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

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

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

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

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

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

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

Проілюструємо суть класичного методу найменших квадратів графічно. Для цього побудуємо точковий графік за даними спостережень (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.

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

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

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

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

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

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

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

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

Чим більше тим краще. Мінімально допустимий набір складається з 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 \u003d ax.

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

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

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

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

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

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

Введемо таблицю значень в лист 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. В результаті експерименту була визначена деяка табличная залежність.

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

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

Розглянемо цей процес докладно. Введемо вихідні дані в робочий лист і побудуємо графік експериментальних даних. Далі виділимо експериментальні точки на графіку, клацнемо правою кнопкою миші і скористаємося командою Додатил інію тренда (Рис. 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в таблиці

Підібрати коефіцієнти залежно 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 с.