Практична робота №5. MS Excel. Задачі апроксимації і прогнозування даних. Метод найменших квадратів. Елементи регресійного аналізу. Побу дова ліній тренду на діаграмах

Мета

Уміти використовувати математичні функції для роботи з масивами даних: MMULT (МУМНОЖ), MINVERSE (МОБР), MDETERM (МОПРЕД), TRANSPOSE (ТРАНСІ!) і статистиці ні функції для дослідження тенденцій (тренду) в даних: TREND(ТЕНДЕНЦИЯ), LINEST (ЛИНЕЙН), GROWT (РОСТ), LOGEST (ЛГРФПРИБЛ).

Задача 6 «Прогноз доходу фірми»

Протягом перших десяти місяців року доходи фірми були такими: 200 000, 220 000, 230 000, 225 000, 235 000, 225 000, 230 000, 250 000, 245 000, 280 000. Побудувати лінію тренду і спрогнозувати дохід фірми у листопаді й грудні.

План

1.  Поняття про метод найменших квадратів.

2.  Реалізація регресійного аналізу в ЕТ.

3.  Використання функції TREND.

4.  Використання функції LINEST.

5.  Поняття про масиви і формули масивів.

6.  Побудова ліній тренду за допомогою діаграм.

Теоретичні відомості

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

Такий аналіз даних також називають регресійним аналізом.

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

 Потрібно спрогнозувати температуру 11 і 12 числа, вважаючи, що тенденція щодо зміни температури стабільна.

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

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

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

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