Завдання 4. Розв’язати задачу статистичного аналізу в MS Excel

      Завдання:

На основі статистичних даних про прибуток (У) та інвестицій (Х) деякого підприємства:

1. побудувати лінійну однофакторну регресійну модель;

2. побудувати графіки реальних і прогнозних даних;

3. побудувати лінію тренда і рівняння з оцінкою параметрів;

4. виконати прогноз У на наступний період вважаючи, що Х₁₇=(8+ N) з використанням функцій ТЕНДЕНЦИЯ і ПРЕДСКАЗ;

4. зробити висновок.

Вихідні дані (в умовних одиницях) наведені в таблиці (N- номер варіанту).

№	У	Х
1	9,1+N	4,5+N
2	13,4+N	5,0+N
3	15,4+N	5,4+N
4	17,6+N	5,8+N
5	17,8+N	6,4+N
6	19,5+N	7,2+N
7	10,4+N	7,8+N
8	13,5+N	5,2+N
9	15,2+N	5,7+N
10	17,1+N	6,3+N
11	18,3+N	6,7+N
12	11,4+N	6,9+N
13	16,2+N	6,1+N
14	19,8+N	7,2+N
15	20,0+N	7,0+N
16	21,7+N	7,8+N

Розв’язання:

1. Рівняння лінійної однофакторної регресії має вигляд: (1) . Необхідно знайти параметри а, b. Використаємо функцію ЛИНЕЙН (меню <Вставка> < Функции> категорія <Статестические>) для знаходження параметрів а, b.

• Введемо таблицю задану в умові.
• Знайдемо функцію ЛИНЕЙН (меню <Вставка> < Функции> категорія <Статестические>).
• В діалоговому вікні функції ЛИНЕЙН введемо діапазон значень У та Х і натиснемо ОК.
• В результаті отримаємо комірку із #Знач.
• Для перегляду результату виконання функції необхідно: виділити одну клітинку праворуч разом із коміркою #Знач, натиснути F₂, а потім одночасно Ctrl, Shift, Enter. В результаті отримаємо два числа: перше число – b, а друге число – a.

Запишемо рівняння лінійної однофакторної регресії, для цього у рівняння (1) підставимо замість a і b відповідні числа.

2. Зайдемо в майстер Диаграм і виберемо тип ТОЧЕЧНАЯ. Введемо дані Х та У для побудови графіку реальних даних. Додамо до отриманого Ряду, ще один і введемо значення Х та  (значення У знайденого за допомогою рівняння (1)), які відповідають прогнозним даним. Перевірити, щоб вісь Х була горизонтальною, а У-вертикальною.

3. Для побудувати лінію тренда і рівняння з оцінкою параметрів використати можливості Excel.

4. Функції ТЕНДЕНЦИЯ і ПРЕДСКАЗ (меню <Вставка> < Функции> категорія <Статестические>).

Список рекомендованої літератури:

1. Клименко О.Ф., Головко Н.Р., Шарапов О.Д. Інформатика та комп'ютерна техніка: Навч.-метод. посібник / За заг. ред. О.Д.Шарапова. - К.: КНЕУ, 2002. - 534 с.

2. Экономическая информатика: Введение в зкономический анализ информационных систем: Учебник. - М.: ИНФРА - М, 2005. — 958 с.

3. Румянцев МИ. Экономическая информатика: Курс лекций. — Днепропетровск: ИМА - Пресе, 2008. - 220 с.

4. Економічна інформатика: Навчальний посібник / Григорків В.С., Маханець Л.Л., Білоскурський Р.Р., Якутова О.Ю., Верстяк А.В. - Чернівці: Книги - XXI, 2008. - 464 с.

5. Кучерява Т.О., Сільченко М.В., Шабаліна І.В. Інформатика та комп’ютерна техніка: активізація навчання. Практикум для індивід. роботи студентів. - К.: КНЕУ, 2006.- 448 с.

6. Мельникова О.П. Економічна інформатика: Навч. посіб. – К.: Центр учбової літератури, 2010. – 424 с.

7. Економічна інформатика: навч. посіб. / В.А. Ткаченко, Г.Ю. Під'ячий, В.А. Рябик. - Харків: НТУ "ХПИ" 2011. - 312 с.

Зразок