За допомогою табличного процесора MS Excel

1. Зробити форму для задачі у вигляді табл. 1 (рис. 1.3).

2. Ввести вихідні дані задачі в форму (рис. 1.4).

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

· виділити діапазон даних В3:В8 (значення Y);

· викликати «Майстер діаграм»;

· вибрати тип діаграми – точкова;

· діапазон даних: діапазон В3:В8, ряди в стовпцях;

· ряд: значення Х – А3:А8, значення Y – В3:В8;

· заголовки: назва діаграми – Кореляційне поле; вісь Х – Х, місяці; вісь Y – Y, кількість клієнтів, К*1000;

· осі: вісь Х (категорій), вісь Y (значень);

· лінії сітки: вісь Y (значень) – основні лінії;

· легенди немає;

· підпису значень немає;

· помістити графік на наявному листі.

Для переходу між окремими кроками «Майстра діаграм» необхідно клацнути мишкою на кнопці «Далі» або «Назад»; після закінчення побудови графіка – клацнути на кнопці «Готово».

4. Визначити коефіцієнт кореляції r (рис. 1.4): курсор в комірку В9, викликати «Майстер функцій» f_x, категорія – «Статистичні», функція – КОРРЕЛ, «Масив 1» – виділити мишкою діапазон даних А3:А8 (значення Х), «Масив 2» – виділити мишкою діапазон даних В3:В8 (значення Y).

5. Визначити коефіцієнти регресії:

5.1. В меню «Сервіс» вибрати команду «Аналіз даних», в списку «Інструменти аналізу» вибрати «Регресія» і клацнути мишкою на кнопці «ОК».

5.2. У вікні «Регресія» ввести:

· вхідний інтервал Y – виділити мишкою діапазон даних В3:В8 (значення Y);

· вхідний інтервал Х – виділити мишкою діапазон даних А3:А8 (значення Х);

· параметри висновку – новий робочий лист;

· «ОК».

5.3. З листа 4 скопіювати обчислені значення коефіцієнтів регресії (комірка В17 - b₀, комірка В18 – b₁) і вставити в лист 1 в комірки В10 (b₀) і В11 (b₁) (рис. 1.4). Одержано рівняння регресії Y = 1,32 – 0,03Х.

6. Побудувати табл. 2 та заповнити її перший стовпець в діапазоні А20:А31 числами від 1 до 12 (рис. 1.5).

7. Обчислити точки графіка регресії (рис. 1.5):

· курсор в комірку В20, ввести формулу "=$В$10+$В$11*А20", нажати „Enter”;

· скопіювати вміст комірки В20 і вставити в комірки В21:В31 (вміст комірок підраховується автоматично).

8. Побудувати графік регресії (рис. 1.5):

· виділити діапазон даних В20:В31 (значення Y);

· викликати «Майстер діаграм»;

· вибрати тип діаграми – точкова зі значеннями, які з'єднуються лініями, що згладжують;

· діапазон даних: діапазон В20:В31, ряди в стовпцях;

· ряд: значення Х – А20:А31, значення Y – В20:В31;

· заголовки: назва діаграми – Лінія регресії; вісь Х – Х, місяці; вісь Y – Y, кількість клієнтів, К*1000;

· осі: вісь Х (категорій), вісь Y (значень);

· лінії сітки: вісь Y (значень) – основні лінії;

· легенди немає;

· підпису значень немає;

· помістити графік на наявному листі.

Для переходу між окремими кроками «Майстра діаграм» необхідно клацнути мишкою на кнопці «Далі» або «Назад»; після закінчення побудови графіка – клацнути на кнопці «Готово». Готовий графік відформатувати.

9. Результати розв’язання задачі наводяться на рис. 1.5.

Значення коефіцієнта кореляції -0,36 показує, що зв'язок між часом і кількістю обслугованих клієнтів зворотній (з часом кількість клієнтів зменшується), слабкий (плин часу несуттєво впливає на зміну кількості клієнтів). Відповідно до отриманого рівняння регресії y=1,32 – 0,03x прогнозується зменшення кількості обслугованих клієнтів близько 30 чоловік кожного місяця, що свідчить про недоцільність розширення виробничих потужностей підприємства ближчим часом.

 

	A	B	C	D	E	F	G	H
1		Таблиця 1
2	X, міс.	Y, кіл-ть кл-тів, К*1000
3
4
5
6
7
8
9	r
10	b 0
11	b 1

Рис. 1.3. Форма для розв’язання задачі методом кореляційно-регресивного аналізу

 

	A	B	C	D	E	F	G	H
1		Таблиця 1
2	X, міс.	Y, кіл-ть кл-тів, К*1000
3	1	1,2
4	2	1,1
5	3	1,5
6	4	1,3
7	5	1,1
8	6	1,0
9	r	-0,35857
10	b 0	1,32
11	b 1	-0,03
12
13
14
15

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

	A	B	C	D	E	F	G	H
1		Таблиця 1
2	X, міс.	Y, кіл-ть кл-тів, К*1000
3	1	1,2
4	2	1,1
5	3	1,5
6	4	1,3
7	5	1,1
8	6	1,0
9	r	-0,35857
10	b 0	1,32
11	b 1	-0,03
12
13
14
15
16
17
18		Таблиця 2
19	X	Y=1,32-0,03X
20	1	1,29
21	2	1,26
22	3	1,23
23	4	1,20
24	5	1,17
25	6	1,14
26	7	1,11
27	8	1,08
28	9	1,05
29	10	1,29
30	11	1,26
31	12	1,23
32
33
34

Рис. 1.5. Результати прогнозу та графік регресії