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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4. Визначити коефіцієнт кореляції r (рис. 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₁) (рис. 4). Одержано рівняння регресії Y = 1,32 – 0,03Х.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

	A	B	C	D	E	F	G	H
		Таблиця 1
	X, міс.	Y, кіл-ть кл-тів, К*1000
		1,2
		1,1
		1,5
		1,3
		1,1
		1,0
	r	-0,35857
	b0	1,32
	b1	-0,03

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

	A	B	C	D	E	F	G	H
		Таблиця 1
	X, міс.	Y, кіл-ть кл-тів, К*1000
		1,2
		1,1
		1,5
		1,3
		1,1
		1,0
	r	-0,35857
	b0	1,32
	b1	-0,03
		Таблиця 2
	X	Y=1,32-0,03X
		1,29
		1,26
		1,23
		1,20
		1,17
		1,14
		1,11
		1,08
		1,05
		1,29
		1,26
		1,23

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

Поиск по сайту: