Алгоритм розв’язання задачі 1

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

Для лінійної апроксимації статистичних даних; де m – тангенс кута нахилу прямої до вісі абсцис, b – ордината точки перетину прямої з віссю ординат,

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

· ПРЕДСКАЗ,

· ТЕНДЕНЦИЯ.

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

Має такий синтаксис:

ПРЕДСКАЗ(х; відомі_знач_у; відомі_знач_х),

де х – значення незалежної величини, що спостерігається (наприклад, певне значення часу), для якого віщується значення залежної величини, що спостерігається; відомі_знач_у – масив відомих значень залежної величини, значення якої спостерігаються;

відомі_знач_х – масив відомих значень незалежної величини (наприклад, значення часу), для яких відомі значення залежної величини, яка спостерігається.

Розмір масивів відомі_знач_у та відомі_знач_х повинен бути однаковим.

Якщо аргумент відомі_знач_х відсутній, то вважається, що це масив {1; 2; 3; …..; n }, де n – розмір масивів відомі_знач_у та відомі_знач_х.

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

= ПРЕДСКАЗ(A2; $B$3:; $B$7; $C$3:; $C$7.

Функція ТЕНДЕНЦИЯ

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

Має такий синтаксис:

ТЕНДЕНЦИЯ (відомі_знач_у; відомі_знач_х; нові_знач_ х; стала ),

де нові_знач_ х – масивзначень незалежної величини, що спостерігається (наприклад, певне значення часу), для якого віщується значення залежної величини, що спостерігається; відомі_знач_у – масив відомих значень залежної величини, значення якої спостерігаються;

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

Розмір масивів відомі_знач_у та відомі_знач_х повин бути однаковим.

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

Для експоненціальної апроксимації статистичних даних, де с, b – сталі, MS Excel має функцію прогнозування РОСТ.

Функція РОСТ

Має такий синтаксис:

РОСТ (відомі_знач_у; відомі_знач_х; нові_знач_ х; стала ),

де нові_знач_ х – масивзначень незалежної величини, що спостерігається (наприклад, певне значення часу), для якого віщується значення залежної величини, що спостерігається; відомі_знач_у – масив відомих значень залежної величини, значення якої спостерігаються;

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

Розмір масивів відомі_знач_у та відомі_знач_х повин бути однаковим.

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

Якщо аргумент відомі_знач_х відсутній, то вважається, що це масив {1; 2; 3; …..; n }, де n – розмір масивів відомі_знач_у та відомі_знач_х.

 

ЗМІСТ І ПОСЛІДОВНІСТЬ ВИКОНАННЯ ЗАВДАНЬ

1. Завантажте табличний процесор Microsoft Excel 2007.

2. Розв’яжіть на ЛИСТ1 задачу1.

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

Табл. 1

Обсяг продажу у 2005 році (тис. од.)
Рік
Обсяг

3. Розв’яжіть на ЛИСТ2 задачу прогнозуванням даних з застосуванням вбудованих функцій прогнозування.

4. В залежності від варіанту розв’яжіть на ЛИСТ3 задачі. За даними цієї таблиці побудувати діаграму по роках. Підібрати вид лінії тренда, вивести на графіку рівняння лінії тренда і величину вірогідності апроксимації. Зробити прогноз на два наступні роки. За даними графіка зробити аналіз прогнозу. Написати текст, у якому відобразити результати прогнозу (зросте чи спаде очікувана прогнозована величина).

:

Варіант 1

Роки
Реалізація меблів	35,6	38,7	39,4	41,8	43,3	42,9	41,8

Варіант 2

Роки
Продаж цукру	135,2	138,7	139,9	141,6	143,1	142,5	141,8

Варіант 3

Роки
Реалізація парфумерії	9,7	10,3	10,8	10,7	11,6	11,4	11,4

Варіант 4

Роки
Продаж галантереї	14,5	16,2	16,5	17,2	17,8	17,7	17,5

Варіант 5

Роки
Реалізація тканини	32,8	30,2	21,7	27,8	27,5	27,2	27,9

Варіант 6

Роки
Продаж взуття	36,3	38,5	39,7	39,1	39,0	38,7	40,0

Варіант 7

Роки
Продаж одягу	52,7	56,5	60,7	54,8	70,4	68,1	67,8

Варіант 8

Роки
Про-даж риби	10,84	11,12	10,6	11,31	11,62	12,0	12,73	11,12

Варіант 9

Роки
Продаж маргарину	2,8	2,9	3,0	3,6	3,8	3,9	4,1

Варіант 10

Роки
Продаж жиру	9,8	10,1	10,3	11,9	11,9	11,8	12,1

Варіант 11

Роки
Продаж олії	4,7	4,6	4,6	5,3	5,3	5,5	5,6

Варіант12

Роки
Продаж оселедця	1,97	2,12	1,28	1,71	1,6	1,11	1,18	1,02

5. Збережіть файл під назвою " ПР№7" в папці Мої документи/36 група.

МЕТОДИЧНІ РЕКОМЕНДАЦІЇ З ВИКОНАННЯ ТА ОФОРМЛЕННЯ

Алгоритм розв’язання задачі 1

1. Дайте одному з робочих аркушів книги ім’я Прогнозування.

2. В комірках A1:G3 цього робочого аркуша створіть зміст наведеної вище таблиці і у рядку Рік додайте ще значення 2006 і 2007.

3. Виділіть дані у комірках В2: І3 і побудуйте діаграму - гістограму.