Практична робота 1 використання графічного методу

Використання діаграм (ліній тренда) Еxcel - найбільш наочний й інформативний спосіб досліджувати зв'язок між двома змінними.

Приклад 1

Для приклада використаємо дані таблиці 6. Припустимо, менеджер фірми розглядає можливість збільшення обсягу продажів шляхом збільшення витрат на рекламу, тобто встановити зв'язок між кошторисом на рекламу вашої фірми для кожного виду продукції й показником обсягу продажів в одиницях даної продукції. У таблиці 6 показані дві змінні - витрати на рекламу й обсяги продажів. Побудуємо діаграму, що показує зв'язок між кошторисом на рекламу й обсягом продажів і додамо лінію тренда.

Таблиця 6

Вихідні дані для визначення залежностей між витратами на рекламу й обсягами продажів продукції

	А	В	С
1	Затрати на рекламу, грн	Ціна за 1 штуку, грн	Обсяг продаж, шт.
2	3500	88	16523
3	10073	110	6305
4	11825	85	1769
5	33550	28	30570
6	37200	101	7698
7	55400	71	9554
8	55565	7	54154
9	66501	82	54450
10	71000	62	47800
11	82107	24	74598
12	83100	91	25257
13	90496	40	80608
14	100000	45	40800
15	102100	21	63200
16	132222	40	69675
17	136297	8	98715
18	139114	63	75886
19	165575	5	83360

Послідовність дій:

1. Відкрити файл Аналіз. Додати новий аркуш. Перейменувати його й давши ім'я Регресія.

2. На аркуші Регресія розмістити таблицю 6.

3. Клацнути на кнопці Майстер діаграм.

- На першому кроці роботи Майстер діаграм вибрати тип діаграми (Крапкова). Клацнути на кнопці Далі.

- На другому кроці роботи Майстер діаграм увести адреси осередків, що містять дані для побудови діаграми А1:А19 й утримуючи клавішу Ctrl С1:С19. Клацнути по кнопці Далі.

- На третьому кроці роботи Майстер діаграм вибрати параметри діаграми - додати легенду й назви осей (див. мал.4). Клацнути на кнопці Далі.

- На четвертому кроці роботи Майстер діаграм вибрати місце розміщення діаграми на окремому аркуші й клацнути на кнопці Готово.

4. Коли діаграма з'явиться, клацнути на ній для редагування.

5. Для побудови тренда, треба клацнути правою кнопкою миші на кожному з маркерів ряду. З контекстного меню, що з'явився, вибрати команду Додати лінію тренда. Вибрати тип апроксимації Лінійна.

6. Клацнути на вкладці Параметри й перевірити, щоб стояли прапорці Показувати рівняння на діаграмі й Помістити на діаграму величину вірогідності апроксимації ( R2 ). Клацнути на кнопці ОК.

линия тренда

Рис.4 Графічне зображення залежності обсягу продажів від витрат на рекламу

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

Графік містить рівняння регресії: y = 0.543x + 5221.1,

y - обсяг продажів,

x - витрати на рекламу.

Права частина рівняння регресії - число 0,543 - називається кутовим коефіцієнтом, а число 5221,1 - відрізком, що відтинають на осі ординат. Коефіцієнт нахилу являє собою міру нахилу лінії тренда: чим більше число, що визначає цей коефіцієнт, тим крутіше лінія тренда. Коефіцієнт нахилу може бути негативним числом, якщо між зв'язок між змінними обернено пропорційна, у цьому випадку лінія тренда буде йти з верхнього лівого кута графіка в нижній правий. Щоб переконатися в цьому, побудуйте графік між ціною на продукцію й обсягами продажів, найімовірніше кутовий коефіцієнт буде негативним. Відрізок на осі ординат указує, у якому місці лінія тренда перетинає вісь y (вертикальну вісь). У цьому випадку - це число 5221,1. Це означає, що, якщо витрати на рекламу будуть рівні 0, то ми можемо продати 5220 одиниць продукції.

На графіку зазначене значення коефіцієнта детермінації R2 , що дорівнює 0,7081. Воно означає, що приблизно 71 % міри мінливості обсягів продажів пов'язаний з мірою мінливості витрат на рекламу.

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

- значення R2 - КВПИРСОН;

- відрізок, що відтинає на осі ординат - ОТРЕЗОК (В3:В20;А3:А20);

- коефіцієнт нахилу лінійної регресії - НАКЛОН (В3:В20; А3:А20).