Використання діаграм (ліній тренда) Еxcel - найбільш наочний й інформативний спосіб досліджувати зв'язок між двома змінними.
Приклад 1
Для приклада використаємо дані таблиці 6. Припустимо, менеджер фірми розглядає можливість збільшення обсягу продажів шляхом збільшення витрат на рекламу, тобто встановити зв'язок між кошторисом на рекламу вашої фірми для кожного виду продукції й показником обсягу продажів в одиницях даної продукції. У таблиці 6 показані дві змінні - витрати на рекламу й обсяги продажів. Побудуємо діаграму, що показує зв'язок між кошторисом на рекламу й обсягом продажів і додамо лінію тренда.
Таблиця 6
Вихідні дані для визначення залежностей між витратами на рекламу й обсягами продажів продукції
| А | В | С | |
| Затрати на рекламу, грн | Ціна за 1 штуку, грн | Обсяг продаж, шт. | |
Послідовність дій:
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).
ПРАКТИЧНА РОБОТА 2
ВИКОРИСТАННЯ ФУНКЦІЇ "РЕГРЕСІЯ"
При регрессировании однієї змінної на кілька змінних графічний метод уже не підходить, у цьому випадку рекомендуємо скористатися інструментом Аналіз даних у меню Сервіс, у якому є функція Регресія.
Приклад 2
За допомогою цієї функції можна, наприклад, проаналізувати зв'язок між такими змінними, як "обсяг продажів, шт." (таку змінну називають залежною змінної), з одного боку, і множинними змінними, такими як "витрати на рекламу,грн" й "ціни за 1 шт,грн", з іншої сторони (див.табл.1). (їх звичайно називають незалежними змінними).
Послідовність дій.
1. Відкрити робочу книгу Аналіз.х1s за допомогою команди меню файл? Відкрити.
2. Установити курсор на аркуш Регресія. Як вихідні значення використаємо дані таблиці 6.
3. За допомогою команди меню Сервіс? Аналіз даних викликати діалогове вікно Аналіз даних. Вибрати інструмент аналізу - Регресія. Указати параметри для регресії (мал. 5):
- вхідний інтервал Y - блок осередків, що містить дані обсягу продажів, включаючи назву стовпця З1:З19;
- вхідний інтервал Х - блоки осередків, що містять витрати на рекламу й ціну за 1 продукції, включаючи назву стовпців А1:В19; указати Мітки;
- не вибирати Константа 0 (лінія регресії не проходить через початок координат); рівень надійності залишити за замовчуванням (рівень 95 % обчислюється автоматично);
- вихідний інтервал - новий робочий аркуш;
- не встановлювати Залишки, Стандартизовані залишки, Графік залишків, Графік підбора, Графік нормальної ймовірності;
- нажати кнопку ОК.
4. Новий аркуш разом перейменувати давши йому ім'я Підсумок4.
Рис.5 Діалогове вікно функції РЕГРЕСІЯ
Результати, які виходять із використанням інструмента Регресія, представляють велику кількість корисної інформації. Розглянемо їх.
У таблиці "Регресійна статистика" указуються:
- Множинний R,, що являє собою квадратний корінь із дисперсії (R2), це значення є коефіцієнтом кореляції й виражає кореляцію між обсягом продажів й отриманою комбінацією передбачуваних змінних.
- R-квадрат є безпосередньо коефіцієнтом детермінації (R2).
- Нормований R-квадрат ураховує кількість результатів спостережень і передбачуваних змінних. Він розраховується по формулі:
1 - (1- R2) * (n - 1) / (n - m - 1),
де R2 - коефіцієнт детермінації, n - кількість результатів спостережень, а m - кількість передбачуваних змінних. При проведенні множинного регресійного аналізу (якщо в порівнянні з кількістю передбачуваних змінних число результатів незначно) R2 має тенденцію відхилятися убік підвищення. Нормований R2 забезпечує інформацією про те, яке значення ви могли б одержати в іншому наборі даних, що був би набагато більше, ніж аналізований у цьому випадку. Якби розглянутий нами приклад був заснований, наприклад, на 100 результатах спостережень, то нормований R2 мав би дуже незначне відхилення від фактичного R2.
Таблиця 7
Результати розрахунків функції РЕГРЕСІЯ
| ВЫВОД ИТОГОВ | ||||||||||||||
| Регрессионная статистика | ||||||||||||||
| Множественный R | 0.898076207 | |||||||||||||
| R-квадрат | 0.806540874 | |||||||||||||
| Нормированный R-квадрат | 0.780746324 | |||||||||||||
| Стандартная ошибка | 14348.6222 | |||||||||||||
| Наблюдения | ||||||||||||||
| Дисперсионный анализ | ||||||||||||||
| df | SS | MS | F | Значимость F | ||||||||||
| Регрессия | 31.26787915 | 4.46087E-06 | ||||||||||||
| Остаток | 205882959.1 | |||||||||||||
| Итого | ||||||||||||||
| Коэффициен-ты | Стандарт-ная ошибка | t-статистика | P-Значение | Нижние 95% | Верхние 95% | |||||||||
| Y-пересечение | 36779.49257 | 13165.54282 | 2.793617632 | 0.013634136 | 8717.785046 | 64841.2001 | ||||||||
| Затраты на рекламу | 0.382841497 | 0.093439712 | 4.097203328 | 0.000951639 | 0.183679344 | 0.58200365 | ||||||||
| Цена за 1 штуку | -358.1412987 | 129.6571733 | -2.762217389 | 0.014524676 | -634.4991918 | -81.7834057 | ||||||||
У таблиці "Дисперсійний аналіз" представлені:
- Df - число ступенів волі (незалежні значення);
- SS - сума квадратів відхилень;
- MS - дисперсія, розраховується як відношення SS/Df;
- F - відношення дисперсії регресії до дисперсії залишку;
- Значимість F - рівень значимості, розраховується як МSрегрессия/МS0статок.
Опис цих коефіцієнтів дано в попередніх лабораторних роботах.
Третя таблиця представляє детальну інформацію про члени рівняння регресії і їхніх стандартних погрішностей (графи "Коефіцієнти" й "Стандартна помилка"). У графі "t-статистика" представлений параметр t, що являє собою частку кожного члена рівняння регресії в його стандартній погрішності.
Коефіцієнт змінної може використатися в рівнянні регресії, якщо обчислена для нього величина (1 - Р-значение) близька до 1. У нашому прикладі всі обрані параметри рівняння регресії значимі із цього погляду, тому модельне рівняння регресії буде виглядати так:
Y = 36779.49 + 0.3828* X(1) + (-358.1412)* Х(2), де
Y - обсяг продажів, шт
X(1) - витрати на рекламу
Х(2) - ціна за 1 штуку
Коефіцієнти для рівняння регресії належать інтервалу, верхні, нижні значення якого відповідають рівню надійності, зазначеному в графах: "нижні 95%", "верхні 95", відповідно можна на основі представлених у цих графах значеннях коефіцієнтів побудувати ще два варіанти рівняння регресії.
ПРАКТИЧНА РОБОТА 3
ВИКОРИСТАННЯ ФУНКЦІЇ "ЛИНЕЙН"
Інший метод одержання рівняння регресії складається у використанні убудованої статистичної функції Excel ЛИНЕЙН.
Вона розраховує статистику для ряду із застосуванням методу найменших квадратів, щоб обчислити пряму лінію, що щонайкраще апроксимує наявні дані. Функція расчитывает масив, що описує отриману пряму. Рівняння для прямої лінії має вигляд:
y = mx + b або y = m1x1 + m2x2 +... + b (у випадку декількох інтервалів значень x)
де залежне значення y є функцією незалежного значення x. Значення m - це коефіцієнти, що відповідають кожної незалежної змінної x, а b - це постійна. Функція ЛИНЕЙН расчитывает масив {mn;mn-1;...;m1;b}, а також може расчитывать додаткову регресійну статистику (табл.). Вона показує наскільки корисно отримане рівняння для пророкування майбутніх значень.
Додаткова регресійна статистика:
se1,se2,...,sen - стандартні значення помилок для коефіцієнтів m1,m2,...,mn.
Seb - стандартне значення помилки для постійної b (seb = #Н/Д, якщо константа має значення НЕПРАВДА).
R2 - коефіцієнт детермінації. Рівняються фактичні значення y і значення, одержувані з рівняння прямої; за результатами порівняння обчислюється коефіцієнт детермінації, нормований від 0 до 1. Якщо він дорівнює 1, то має місце повна кореляція з моделлю, тобто немає розходження між фактичним й оцінним значеннями y. У протилежному випадку, якщо коефіцієнт детермінації дорівнює 0, то рівняння регресії невдало для пророкування значень y.
Sey - cстандартная помилка для оцінки y.
F - F-статистика. Використається для визначення того, чи є спостережуваний взаємозв'язок між залежною й незалежної змінними випадковою чи ні.
Df - ступеня волі. Корисні для знаходження F-критичних значень у статистичній таблиці. Для визначення рівня надійності моделі потрібно зрівняти значення в таблиці з F-статистикою, расчитываемой функцією ЛИНЕЙН.
Ssreg - регресійна сума квадратів.
Ssresіd - залишкова сума квадратів. Для кожної крапки обчислюється квадрат різниці між прогнозованим значенням y і фактичним значенням y. Сума цих квадратів разностей і називається залишковою сумою квадратів.
Mіcrosoft Excel підраховує суму квадратів разностей між фактичними значеннями y і середнім значенням y, що називається загальною сумою квадратів (регрессионая сума квадратів + залишкова сума квадратів). Чим менше залишкова сума квадратів у порівнянні із загальною сумою квадратів, тим більше значення коефіцієнта детермінованості r2, що показує, наскільки гарне рівняння, отримане за допомогою регресійного аналізу, пояснює взаємозв'язку між змінними.
У наведеній нижче таблиці показано, у якому порядку расчитывается додаткова регресійна статистика.
Таблиця 8
Порядок розміщення додаткової регресійної статистики функції ЛИНЕЙН
| mn | Mn-1 | … | m1 | B |
| sen | Sen-1 | … | se1 | Seb |
| r2 | Sey | |||
| F | Df | |||
| ssreg | Ssresid |
Послідовність дій:
1. Відкрити робочу книгу Аналіз.х1з за допомогою команди меню файл - Відкрити.
2. Установити курсор на аркуш Регресія. Як вихідні значення використаємо дані таблиці 6.
3. Уиділити на аркуші блок вільних осередків, куди буде спрямований результат обчислень функції. Число стовпців блоку дорівнює числу оцінюваних факторів плюс один, для розміщення значення константи. У нашому випадку число стовпців буде дорівнює 3, тому що ми маємо два оцінюваних факторів (витрати на рекламу й ціна за 1 виріб). Число рядків повинне бути дорівнює 5 (див. табл.6) У цьому випадку блок осередків Е2:G7.
4. Клацнути на кнопці Майстер функцій.
- Вибрати в списку функцій категорії Статистичні функцію ЛИНЕЙН.
-У діалоговому вікні, що з'явилося, указати вихідні параметри для розрахунку:
Рис.6 Діалогове вікно функції ЛИНЕЙН
- Відомі значення y - це безліч значень y, які вже відомі для співвідношення y = mx + b. У нашому випадку це значення Стовпця обсяг продажів З2:З19;
- Відомі значення x - це необов'язкова безліч значень x, які вже відомі для співвідношення y = mx + b. Відповідні значення стовпців Витрати на рекламу й Ціна за 1 продукції А2:В19.
Відомі значення y й x можуть бути масивами будь-якої форми за умови, що вони мають однакову розмірність. Якщо відомі значення x опущені, то передбачається, що це масив {1;2;3;...} такого ж розміру як і відомі значення y.
- Константа- це логічне значення, що вказує, потрібно чи, щоб константа b була дорівнює 0.Якщо Конст має значення 1 або опущене, то b обчислюється звичайним образом, якщо конст має значення 0, то b покладається рівним 0 і значення m підбираються так, щоб виконувалося співвідношення y = mx. Уведіть значення 1.
- Статистика - це логічне значення, що вказує, потрібно чи повернути додаткову статистику по регресії. Якщо статистика має значення 1, то функція ЛИНЕЙН расчитывает додаткову регресійну статистику, так що расчитываемый масив буде мати вигляд: {mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresіd} (табл.8), інакше функція расчитывает тільки коефіцієнти m і постійну b. Уведіть значення 1.
5. Для того щоб у блоці розрахункових даних було видно не тільки значення першого осередку уведення завершите одночасним натисканням комбінації клавіш Ctrl+Shіft+Enter.
6. З отримані результати з результатами попередньої лабораторної роботи (Табл.7).
Таблиця 9
Результати розрахунку додаткової статистики функції ЛИНЕЙН
| -358,1412987 | 0,3828415 | 36779,49 |
| 129,6571733 | 0,09343971 | 13165,54 |
| 0,806540874 | 14348,6222 | #Н/Д |
| 31,26787915 | #Н/Д | |
| #Н/Д |
