У вкладці ПАРАМЕТРЫ вказати назву регресії, число періодів, для яких буде здійснюватись прогноз; при необхідності режим відображення апроксимаційних рівнянь в діаграмі

Лабораторна робота № 12

Тема: Розв’язування задач апроксимації засобами Excel.

Мета: Зрозуміти суть задачі апроксимації. Навчитись використовути для прогнозування процесів лінії тренда та встроєні статистичні функції Excel.

Примітка: Текст виділений курсивом запишіть у робочий зошит.

Хід роботи

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

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

В Excel для побудови регресій (з метою прогнозування) існує дві можливості:

- добавлення вибраних регресій(ліній тренда) в діаграму, попередньо побудовану на основі таблиці даних;

Використання встроєнних статистичних функцій Excel.

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

Щоб добавити лінію тренда до вже побудованої діаграми:

- з контекстного меню діаграми → ДОБАВИТЬ ЛИНИЮ ТРЕНДА;

- у вкладці ТИП задати тип лінії тренда та ряд даних для якого вона будується;

у вкладці ПАРАМЕТРЫ вказати назву регресії, число періодів, для яких буде здійснюватись прогноз; при необхідності режим відображення апроксимаційних рівнянь в діаграмі.

1.Дано таблицю даних прибутків автотранспортного підприємства за 1995-2002 роки. Необхідно скласти прогноз прибутків підприємства на 2003 та 2004 роки.

Для розв’язання задачі виконайте наступні дії:

q побудуйте робочу таблицю даних за наведеним зразком;

q на основі даних діапазону клітинок D7:D14 будуємо діаграму, назвавши ряд даних ПРИБУТОК ПІДПРИЄМСТВА, та вказавши діапазон клітинок зі значеннями для підписів по осі Х;

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

o ТИП: для лінійної → ЛИНЕЙНАЯ; квадратичної та кубічної → ПОЛИНОМИАЛЬНАЯ з відповідним степенем;

o РЯД ДАНЫХ: для всіх регресій→ Прибуток підприємства;

o НАЗВА: відповідно → лінійний, квадратичний та кубічний тренд;

o ПРОГНОЗ: для всіх регресій → 2 періода (2003 та 2004 роки);

o для всіх регресій → режим відображення рівнянь на діаграмі.

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

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

ЗАУВАЖЕННЯ: в якості аргументу функцій регресій виступає діапазон клітинок В7:В10.

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

Для розв’язування задач на прогнозування в Excel можна використати також статистичні функції ТЕНДЕНЦИЯ та РОСТ. Їх формат наступний:

ТЕНДЕНЦИЯ(Известные_значения_ у;Известные_значения_х;Новые_значения_х);

РОСТ(Известные_значения_ у;Известные_значения_х;Новые_значения_х).

2. Дано таблицю даних реалізації продовольчих товарів на гуртівні ПАККО за 9 місяців 2002 року. Використавши статистичні функції Excel необхідно скласти прогноз реалізації на три останні місяці року.

Для розв’язання задічі виконайте наступні дії:

q побудуйте робочу таблицю даних за наведеним зразком;

q значення функцій ТЕНДЕНЦИЯ та РОСТ для відомих значень реалізації, знайдіть використавши формули масиву, відповідно:

- ТЕНДЕНЦИЯ (ИЗВ_ЗНАЧ_У; ИЗВ_ЗНАЧ_Х);

- РОСТ (ИЗВ_ЗНАЧ_У; ИЗВ_ЗНАЧ_Х);

де ИЗВ_ЗНАЧ_У, це дані реалізації, а ИЗВ_ЗНАЧ_Х – місяці;

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

- ТЕНДЕНЦИЯ (ИЗВ_ЗНАЧ_У; ИЗВ_ЗНАЧ_Х;НОВ_ЗНАЧ_Х);

- РОСТ (ИЗВ_ЗНАЧ_У; ИЗВ_ЗНАЧ_Х;НОВ_ЗНАЧ_Х);

де НОВ_ЗНАЧ_Х – місяці, для яких реалізація невідома;

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

3. Збережіть створену робочу книгу під назвою ЛАБ№12_<Прізвище>, та розмістіть її у папці СТУДЕНТ_ЛАБ№12 на Server.