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

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

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

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

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

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

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

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

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

Таблиця 2.1

Номер магазину	Річний товарообіг, млн руб.	Торгова площа, тис. м2

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

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

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

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

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

Таблиця 2.2

Таким чином,

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

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

Таблиця 2.3

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

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

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

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

Таблиця 2.4

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

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

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

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

Таким чином,

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

приклад.

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

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

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

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

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

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

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

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

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

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

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

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

Рішення.

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

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

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

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

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

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

Залишилося з'ясувати, яка з ліній 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" та проаналізувати результати.

Слободянюк О.І. Метод найменших квадратів у шкільному фізичному експерименті // Фізiка: проблеми. викладання. - 1995. - Вип. 1. - С. 88-99.

Наразі розроблено кілька методів обробки результатів вимірювань. Найбільш уживаним і точним є метод найменших квадратів (МНК).

У статті викладається суть методу найменших квадратів, умови його застосування. Автори пропонують приклади використання методу МНК.

Як правило, всі фізичні експерименти зводяться до вимірювання залежності певної величини uвід однієї чи кількох інших величин z 1 , z 2 , …, z n.

Необхідність отримання залежності (а не проведення «точкового» виміру при фіксованих значеннях параметрів) виправдовується такими перевагами:

• можливістю перевірки теоретичних побудов;

• можливістю виключення трудновизначуваних параметрів;

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

Наразі розроблено кілька методів обробки результатів вимірювань. Найбільш уживаним, простим та обґрунтованим є метод найменших квадратів (МНК).

1. Суть методу найменших квадратів, умови його застосування

Допустимо, нам відомий вид функціональної залежності фізичної величини uвід іншої фізичної величини z, але не відомі параметри цієї залежності a, b, c,... . В результаті проведених вимірювань отримано таблицю значень u iпри деяких значеннях . Потрібно знайти такі значення параметрів a, b, c,... при яких функція найкраще описує експериментальні дані.

МНК стверджує, що «найкращою» кривою буде така, для якої сума квадратів відхилень експериментальних значень u iвід значень функції мінімальна. Таким чином, для визначення параметрів a, b, c,... необхідно знайти мінімум функції

. (1)

Зауважимо, що Φ розглядається тут як функція параметрів a, b, c,..., оскільки величини u i, z iвідомі з експериментальних даних.

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

для якого реалізація МНК найпростіша. Приклади перетворень такого типу наведено у табл. 1. Деякі перетворення будуть розглянуті нижче під час викладу конкретних прикладів.

Підставимо вираз (2) у вираз (1)

(3)

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

(4)

Ця система є лінійною і легко вирішується:

(5)

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

(6)

(кутові дужки означають середнє арифметичне за експериментальними даними) і запишемо

(7)

З другого рівняння системи (4) виразимо .

Вирази (6), (7) дозволяють досить швидко за допомогою непрограмованого калькулятора розрахувати параметри лінійної залежності (2).

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

1. Результати вимірів є незалежними.

2. Похибки вимірів підпорядковуються нормальному розподілу.

3. Величини хi, відомі точно.

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

За умови виконання цих умов параметри а, bлінійно виражаються через результати вимірів уi, (похибками вимірювань x iнехтуємо), тому похибка визначення параметрів може бути знайдена стандартним методом як похибка непрямого виміру. Декілька громіздкі викладки призводять до наступних формул для оцінок похибок:

(8)

де , Інші позначення зберігаємо колишніми:

(9)

Таким чином, формули (6) - (9) повністю вичерпують МНК для аналізу лінійної залежності. Формули (7) – (8) дають оцінки лише випадкових похибок вимірів. Їх використання цілком виправдано, якщо цей тип похибок переважає, що найчастіше буває практично. Свідченням такого переважання є помітний розкид точок ( уi, хi) на графіці, коли ці точки не лягають точно на пряму. Зазначимо, що постійна систематична приладова похибка не впливає визначення параметра аі є адитивною добавкою до похибки параметра b, тобто. якщо приладова похибка вимірювання величин уiрівна , то .

Зазначимо також, що у деяких випадках необхідно проводити кілька вимірювань величини uпри тому самому значенні z. І тут ніяких модифікацій МНК не потрібно. Достатньо розглядати ці значення незалежні, тобто. включати до розрахунків пари z i, u iВ· з одними і тими ж значеннями z i. Іншими словами, одному значенню zможе відповідати декілька значень u. Звичайно, не можуть бути всі zоднаковими, інакше у формулі (5) у знаменнику виявиться нуль.

2. Практична реалізація МНК для лінійної залежності на непрограмованому калькуляторі

Як показує досвід, краще для розрахунків параметрів лінійної залежності та їх похибок скористатися заздалегідь приготованим бланком (табл. 2). У колонці 1 записуються номери проведених вимірів ( i = 1, 2, ..., Ν ); у колонках 2, 3 – результати вимірювань величин z i, u i.

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

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

1) обчислити – для цього послідовно ввести на згадку всі значення хi, Записані в колонці 4, і після цього вміст розділити на кількість пар вимірювань N,результат записати у колонці 7;

2) обчислити , послідовно набираючи значення x i, накопичити у пам'яті суму їх квадратів (набрати значення – «помножити» – «рівно» – «на згадку +») і розділити на N, Від отриманого результату відняти квадрат середнього, результат записати в колонці 7;

3 – 4) аналогічно обчислити та ;

5) у пам'яті накопичити суму творів, розділити на N, відняти твір середніх і поділити на – отримати значення параметра а.

Подальші розрахунки цілком очевидні.

3. Приклад використання МНК

Завдання. За допомогою математичного маятника виміряти прискорення вільного падіння.

УстаткуванняКабіна: нитка, грузик, штатив, лінійка, секундомір.

Рішення. Період малих коливань математичного маятника Твизначається за формулою. Цю формулу можна перетворити на вигляд.

Іншими словами, між довжиною маятника lі квадратом періоду існує лінійна зв'язок, яку ми запишемо як: , де (перетворення до лінійного виду). Введення параметра bу разі не є обов'язковим, оскільки теоретично b= 0. Однак запис лінійної залежності в загальному вигляді дозволяє врахувати автоматично похибку у визначенні довжини маятника, більше того, у цьому випадку можна вимірювати не довжину маятника, а лише її зміну. Якщо ж всі виміри проведені коректно, то МНК повинен привести до результату , що свідчить про те, що .

Результати вимірювань зміни довжини маятника Δ l(вимірювалася відстань від точки підвісу до деякої фіксованої точки на нитці) та часу tдвадцяти коливань (виміряно за допомогою ручного годинника) наведені в табл. 3. Там же наведено результати розрахунків за викладеною методикою.

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

Остаточний результат м/с.

Значення параметра bне використовувалося (сенс отриманої величини – відстань від фіксованої точки на нитці до центру мас вантажу). Використання цього параметра виправдане складністю точного визначення положення центру ваги.

4. Експериментальні завдання, що передбачають використання МНК

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

Завдання 1. Період коливань математичного маятника залежить від амплітуди j 0 (у радіанах) згідно із законом

(10)

Визначте значення параметра β.

УстаткуванняКабіна: нитка, вантаж, штатив, транспортир, електронний секундомір.

Вказівки до рішення. Залежність періоду коливань від амплітуди досить слабка. Щоб виявити, необхідно проводити вимірювання з високою точністю (–0,01 с), для чого потрібен електронний секундомір.

Залежність (10) представимо у вигляді , де y =T,b = Т 0 . За МНК для лінійної залежності можна знайти значення параметрів аі bтоді шуканий коефіцієнт визначиться за формулою (зазначимо, що теоретичне значення).

Завдання 2. Визначте фокусну відстань лінзи, що збирає.

УстаткуванняКабіна: джерело світла, екран, лінза, лінійка.

Вказівки до рішення. Скористайтеся формулою тонкої лінзи

де d- Відстань від предмета до лінзи, f– відстань від лінзи до зображення, F- Фокусна відстань лінзи.

Позначимо тоді. Якщо виміряти кілька пар значень diі f iта нанести на графік точки , то ці точки повинні лягти на пряму, яка відсікає на осях х, увідрізки, чисельно рівні. Якщо обробити цю залежність МНК, можна отримати і потім знайти .

Завдання 3. Охолодження води описується формулою , де Δ T-Різниця температур води і повітря в кімнаті, Δ T 0 - ця ж різниця в момент часу t= 0. Визначте, скільки часу минуло з кипіння води.

Устаткування: гаряча вода в посудині, термометр, годинник.

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

Для вирішення поставленого завдання необхідно виміряти залежність температури води Tвід часу t. Далі перепишемо наведену формулу у вигляді , де T 0 – кімнатна температура, Tкип - температура кипіння води, t 0 – час, що минув від закипання на початок виміру. Оскільки в. формулу входять лише різниці температур, можна користуватися шкалою Цельсія. Прологарифмуємо останній вираз

(12)

і позначимо , x= t, отримаємо лінійну залежність

Обробляючи результати вимірювань за МНК, знайдемо значення параметрів а, b, з яких можна обчислити потрібне значення часу t 0: .

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

Устаткування: шматочки паперу, секундомір.

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

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

(потрібно визначити γ), при встановився рух ця сила чисельно дорівнює силі тяжіння , отже, швидкість руху, що встановився, а час падіння з висоти h:

(14)

Візьмемо кілька (1, 2, 3, ..., 5) однакових тарілочок та виміряємо час падіння t nскладених разом nтарілочок. Коефіцієнт зу формулі (13) буде однаковий (він залежить тільки від форми тарілочки), маса ж падаючих тіл , де m 0 – маса однієї тарілочки. Використовуємо (14): , у логарифмічній формі

(15)

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

Таким чином, вимірявши залежність часу падіння t n, від кількості складених разом nтарілочок і побудувавши залежність (15), по МНК можна визначити значення параметра ата шуканої величини.

Під час проведення експерименту необхідно пам'ятати, що час падіння шматочка паперу див з висоти дорівнює приблизно 1,5 з, тому необхідно вимірювати час падіння з похибкою порядку 0,1 з. Отже, для кожного значення числа nпотрібно отримати кілька значень tn. Підкреслимо, що в цій ситуації немає необхідності попередньо розраховувати середні значення, можна (і потрібно) розглядати всі результати виміру як незалежні та включати їх до бланку розрахунку.

Ще одне завдання такого типу докладно розглянуто в журналі «Фокус».

5. Висновок

Розглянутий алгоритм розрахунків з МНК апробовано на літніх зборах у таборі «Зубраня». Проведені з переможцями олімпіад заняття показали, що цей метод цілком доступний школярам старших класів із поглибленим вивченням фізики. Після набуття досвіду роботи на мікрокалькуляторі розрахунки займають приблизно 5-10 хв.

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

1. Тейлор Дж. Введення у теорію помилок. - М: Світ, 1985.

2. Більшов Л.М., Смирнов Н.В. Таблиці математичної статистики. - М.: Наука, 1983.

3. Тимофєєв А.. Перевіримо Стокса? - Фокус. - 1995. - №2. - С. 44-49.

Приведення до лінійної залежності

Вид залежності	Перетворення	Параметри

Бланк розрахунку параметрів лінійної залежності

i	z	u	x	y	Розрахункові формули	Результати

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

	Δl ,				Розрахункові формули	Результати

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

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

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

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

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

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

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

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

Суть методу

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3. Апроксимація функцій за допомогою методу

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

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

та інші.

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

Таблиця 4
				x n
				y n

(3.1)

де f - відома функція, a 0 , a 1 , …, a m - невідомі постійні параметри, значення яких слід знайти. У методі найменших квадратів наближення функції (3.1) до експериментальної залежності вважається найкращим, якщо виконується умова

(3.2)

тобто сум a квадратів відхилень шуканої аналітичної функції від експериментальної залежності має бути мінімальною .

Зауважимо, що функція Q називається нев'язкою.

Тому що нев'язка

вона має мінімум. Необхідною умовою мінімуму функції кількох змінних є рівність нулю всіх похідних приватних цієї функції за параметрами. Таким чином, відшукання найкращих значень параметрів апроксимуючої функції (3.1), тобто таких значень, при яких Q = Q (a 0 , a 1 , …, a m ) мінімальна, зводиться до розв'язання системи рівнянь:

(3.3)

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

Знаходження параметрів лінійної функції

Нехай експериментальні дані треба подати лінійною функцією:

Потрібно підібрати такі значення a і b , для яких функція

(3.4)

буде мінімальною. Необхідні умови мінімуму функції (3.4) зводяться до системи рівнянь:

Після перетворень отримуємо систему двох лінійних рівнянь із двома невідомими:

(3.5)

вирішуючи яку , знаходимо значення параметрів, що шукаються a і b.

Знаходження параметрів квадратичної функції

Якщо апроксимуючою функцією є квадратична залежність

то її параметри a, b, c знаходять із умови мінімуму функції:

(3.6)

Умови мінімуму функції (3.6) зводяться до системи рівнянь:

Після перетворень отримуємо систему трьох лінійних рівнянь із трьома невідомими:

(3.7)

при вирішенні якої знаходимо шукані значення параметрів a, b і c.

приклад . Нехай в результаті експерименту отримано наступну таблицю значень x і y:

Таблиця 5
y i	0,705	0,495	0,426	0,357	0,368	0,406	0,549	0,768

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

Рішення. Знаходження параметрів апроксимуючих функцій зводиться до вирішення систем лінійних рівнянь (3.5) та (3.7). Для вирішення задачі скористаємося процесором електронних таблиць Excel.

1. Спочатку зчепимо листи 1 і 2. Занесемо експериментальні значення x i та y iу стовпці А і В, починаючи з другого рядка (у першому рядку помістимо заголовки стовпців). Потім для цих стовпців обчислимо суми та помістимо їх у десятому рядку.

У стовпцях C – G розмістимо відповідно обчислення та підсумовування

2. Розчепимо листи. Подальші обчислення проведемо аналогічним чином для лінійної залежності на аркуші 1і для квадратичної залежності на аркуші 2.

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

Для обчислення зворотної матриці та перемноження матриць скористаємося Майстром функційта функціями МОБРі МУМНІЖ.

4. У блоці осередків H2: H 9 на основі отриманих коефіцієнтів обчислимо значення апроксимуючогополіномаy i вич., у блоці I 2: I 9 – відхилення D y i = y i експ. - y i вич., у стовпці J – нев'язку:

Отримані таблиці та побудовані за допомогою Майстри діаграмграфіки наведено на рисунках6, 7, 8.

Мал. 6. Таблиця обчислення коефіцієнтів лінійної функції,

апроксимуючоюекспериментальні дані.

Мал. 7. Таблиця обчислення коефіцієнтів квадратичної функції,

апроксимуючоюекспериментальні дані.

Мал. 8. Графічне подання результатів апроксимації

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

Відповідь. Апроксимували експериментальні дані лінійною залежністю y = 0,07881 x + 0,442262 з нев'язкою Q = 0,165167 та квадратичною залежністю y = 3,115476 x 2 – 5,2175 x + 2,529631 з нев'язкою Q = 0,002103 .

Завдання. Апроксимувати функцію, задану таблично, лінійною та квадратичною функціями.

Таблиця 6
№0	x	0,1	0,2	0,3	0,4	0,5	0,6	0,7	0,8
	y	3,030	3,142	3,358	3,463	3,772	3,251	3,170	3,665
№ 1
		3,314	3,278	3,262	3,292	3,332	3,397	3,487	3,563
№ 2
		1,045	1,162	1,264	1,172	1,070	0,898	0,656	0,344
№ 3
		6,715	6,735	6,750	6,741	6,645	6,639	6,647	6,612
№ 4
		2,325	2,515	2,638	2,700	2,696	2,626	2,491	2,291
№ 5
		1.752	1,762	1,777	1,797	1,821	1,850	1,884	1,944
№ 6
		1,924	1,710	1,525	1,370	1,264	1,190	1,148	1,127
№ 7
		1,025	1,144	1,336	1,419	1,479	1,530	1,568	1,248
№ 8
		5,785	5,685	5,605	5,545	5,505	5,480	5,495	5,510
№ 9
		4,052	4,092	4,152	4,234	4,338	4,468	4,599	Чи не знайшли відповідь на своє запитання? Подивіться тут Бібліотерапія: 15 книг, які допоможуть знайти себе О.М. Толстой «Ходіння по муках. Олексій Миколайович товстої - ходіння по муках Ходіння по муках твір автор Чому складно розпочати і що з цим робити