Реферат на тему:

Кореляційно-регресивний аналіз

Основне завдання кореляційного і регресійного методів аналізу полягає в
аналізі статистичних даних для виявлення математичної залежності між
досліджуваними ознаками і встановлення за допомогою коефіцієнтів
кореляції порівняльної оцінки щільності взаємозв’язку, який має певний
числовий вираз.

  Кореляційний і регресійний методи аналізу вирішують два основних
завдання:

— визначають за допомогою рівнянь регресії аналітичну форму зв‘язку між
варіацією ознак Х і У;

— встановлюють ступінь щільності зв‘язку між ознаками.

Найчастіше трапляються такі типи зв‘язків:

— факторна ознака безпосередньо пов‘язана з результативною;

— результативна ознака визначається комплексом діючих факторів;

— дві результативні ознаки спричинені дією однієї загальної причини.

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

5.5.1. Побудова рівняння регресії на прикладі лінійної моделі

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

Ви менеджер фірми по продажах шин до автомобілів і постійно ведете облік
продажів. У вашому розпорядженні є дві величини, що спостерігаються: х —
номер тижня, у — число проданих шин (тис.шт.) (табл. 5.9). Фірма зовсім
молода, була створена десять тижнів назад, і тому у вашому розпорядженні
є статистика тільки за цей дуже обмежений проміжок часу.

Таблиця 5.9- Значення величин, що спостерігаються

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

де n — число спостережень (у даному випадку п = 10).

Для рішення цієї задачі:

1.  Заповніть чарунки А2:В11 (рис. 5.19).

2.  Відведіть під змінні а и b чарунки D2 і Е2.

3. В чарунку F2 уведіть функцію мінімізації (це формула масиву, тому не
забудьте завершити її введення натисканням комбінації клавіш
++).

{=СУММ((В2:В11-D2*А2:А11-E2)^2) }

4.   Виберіть команду Сервіс | Пошук рішення. Діалогове вікно Пошук
рішення заповните, як показано на мал. 5.19. Відзначимо, що на змінні а
и b не накладаються ніяких обмежень.

Рис. 5.19 – Вихідні дані для побудови лінійної моделі діалогове вікно
„Пошук рішення”

5. Натисніть кнопку Виконати. У результаті обчислень засіб Пошук рішення
знайде а = 1,527272655 і b = 6,400000616  (див. рис. 5.20).

Рис. 5.20 – Теоретичне значення спостережуваної величини і коефіцієнти
рівняння регресії

Параметри а і b лінійної моделі у = ах + b з попереднього прикладу можна
визначити за допомогою функцій НАКЛОН (SLOPE) і ОТРЕЗОК (INTERCEPT).

Функція НАКЛОН (SLOPE) визначає коефіцієнт нахилу лінійного тренду, а
функція ОТРЕЗОК (INTERCEPT) — точку перетину лінії лінійного тренду з
віссю ординат.

Синтаксис:

НАКЛОН (поч_знач_у; поч_знач_х)

ОТРЕЗОК ((поч_знач_у; поч_знач_х)

— поч_знач_у — масив відомих значень залежної величини, що
спостерігається;

— поч_знач_х — масив відомих значень незалежної величини, що
спостерігається. Якщо поч_знач_х опущені, то передбачається, що це масив
{1; 2; 3; …} такого ж розміру, як і поч_знач_у.

Функції НАКЛОН і ОТРЕЗОК обчислюють результат за наступними формулами:

,

,

.

В чарунках D5 і Е5 (рис. 5.20) знайдені значення а і Ь, відповідно, за
формулами

= НАКЛОН (В2:В11;А2:А11),

= ОТРЕЗОК (В2:В11;А2:А11).

Знайшовши коефіцієнти рівняння регресії, на їх основі легко визначити
теоретичні значення величини, що спостерігається. Для цього:

1.   Введіть в чарунку С2 формулу

=$D$5*A2+$E$5

2.   Виберіть чарунку С2, розташуєте покажчик миші на маркері заповнення
і протягніть його на діапазон С3:С11.

Теоретичне значення можна також обчислити за допомогою функції ПРЕДСКАЗ
(FORECAST), не визначаючи попередньо коефіцієнти лінійної моделі у
фіксованій точці.

Синтаксис:     

ПРЕДСКАЗ (х;   поч_знач_у;   поч_знач__х)

— х — точка даних, для якої прогнозується значення;

— поч_знач_у — масив відомих значень залежної величини, що
спостерігається;

— поч_знач_х — масив відомих значень незалежної величини, що
спостерігається. Якщо поч_знач_х опущені, то передбачається, що це масив
{1; 2; 3; …} такого ж розміру, як і поч_знач_у.

Наприклад, теоретичне значення в чарунці С2 можна було б також визначити
по формулі

=ПРЕДСКАЗ(А2;$В$2:$В$11;$А$2:$А$11)

Функція ТЕНДЕНЦИЯ (TREND) обчислює значення рівняння лінійної регресії
для цілого діапазону значень незалежної змінної як для випадку
одновимірного, так і багатовимірного рівняння регресії. Багатовимірна
лінійна модель регресії має вид:

Синтаксис:

ТЕНДЕНЦИЯ (поч_знач_у;  поч_знач_х;  нов_знач_х;   константа)

— поч_знач_у — масив відомих значень залежної величини, що
спостерігається;

— поч_знач_х — масив відомих значень незалежної величини, що
спостерігається. Якщо поч_знач_х опущені, то передбачається, що це масив
{1; 2; 3; …} такого ж розміру, як і поч_знач_у;

— нов_знач_х — нові значення х, для яких ТЕНДЕНЦИЯ повертає відповідні
значення у;

— константа — логічне значення, що вказує, чи потрібно, щоб константа b
дорівнювала 0. Якщо константа має значення ИСТИНА чи опущена, то b
обчислюється звичайним чином. Якщо константа має значення ЛОЖЬ, то b
дорівнює 0.

Якщо будується багатовимірна лінійна модель, то поч_энач_х і нов_знач__х
повинні містити стовпець (чи рядок) для кожної незалежної змінної. Якщо
нов_знач_х опущені, то передбачається, що вони збігаються з поч_знач_х.

Функція ЛИНЕЙН (LINEST) вертає масив {mn, …, m1, b} значень параметрів
рівняння багатомірної лінійної регресії.

Синтаксис:

ЛИНЕЙН (поч_знач_у;  поч_знач_х;   константа;   стат)

— поч_энач_у — масив відомих значень залежної величини, що
спостерігається;

— поч_знач_х — масив відомих значень незалежної величини, що
спостерігається. Якщо поч_знач_х опущені, то передбачається, що це масив
{1; 2; 3; …} такого ж розміру, як і поч_знач_у;

— константа — логічне значення, що вказує, чи потрібно, щоб константа b
була рівна 0. Якщо константа має значення ИСТИНА чи опущена, то b
обчислюється звичайним чином. Якщо константа має значення ЛОЖЬ, то b
покладається рівним 0;

\

? TH V

Oe

G, то функція ЛИНЕЙН повертає додаткову регресійну статистику. Якщо стат
має значення ЛОЖЬ чи опущена, то функція ЛИНЕЙН повертає тільки значення
коефіцієнтів.

Ми навчилися знаходити коефіцієнти рівняння регресії. Тепер побудуємо
його діаграму. У MS Excel лінія рівняння регресії називається лінією
тренду, що показує тенденцію зміни даних і служить для складання
прогнозів. Для створення лінії тренду на основі діаграми
використовується один з п’яти типів апроксимацій чи лінійна фільтрація
(табл. 5.9).

Таблиця 5.10. Типи апроксимацій

Тип Опис

Лінійна y=ax+b, де  а — тангенс кута нахилу,     b — точка перетину з
віссю ординат

Логарифмічна y=clnx+b,де с и b — константи

Поліноміальна y=cnxn+…+ c1x+b,де cn, …,c1 и b — константи

Степенева y=cxb,де с и b — константи

Експоненціальна y=c?bx,де с и b — константи

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

На діаграмі можна виділити будь-який ряд даних і додати до нього лінію
тренда. Коли лінія тренду додається до ряду даних, вона зв’язується з
ним, і тому при зміні значень будь-яких точок ряду даних лінія тренду
автоматично перераховується й обновляється на діаграмі.

Крім того, існує можливість вибирати точку, у якій лінія тренду
перетинає вісь ординат, додавати до діаграми рівняння регресії і
величину вірогідності апроксимації.

Покажемо на нашому прикладі про продажах шин, як будується лінія тренду.
Для цього:

1.   За допомогою майстра діаграм побудуйте по діапазону чарунок А2:В11
точковий графік.

2.   Виберіть діаграму чи графік, а потім команду Діаграма | Додати
лінію тренда. На екрані відобразиться діалогове вікно Лінія тренда.

3.   На вкладці Тип діалогового вікна Лінія тренда виберіть тип лінії
тренду. У даному випадку — Лінійна (рис. 5.21).

Рис. 5.21 — Вкладка „Тип” діалогового вікна „Лінія тренду”

Рис. 5.22 — Вкладка „Параметри” діалогового вікна „Лінія тренду”

4. На вкладці Параметри діалогового вікна Лінія тренда можна установити
параметри лінії тренда (мал. 5.22). У групі Прогноз можна вказати число
періодів, на які лінія тренда або складає прогноз, або визначає історію
процесу. Якщо ви відмітите прапорець показувати рівняння на діаграмі, то
рівняння лінії тренду відобразиться на діаграмі. Якщо встановите
прапорець помістити на діаграму величину вірогідності апроксимації
(R^2), то на діаграмі відобразиться величина вірогідності апроксимації,
тобто квадрат коефіцієнта кореляції. За коефіцієнтом кореляції можна
судити про правомірність використання лінійного рівняння регресії. Якщо
він лежить у діапазоні від 0.9 до 1, то дану залежність можна
використовувати для пророкування результату. Чим коефіцієнт кореляції
ближче до одиниці, тим він більш обґрунтоване вказує на лінійну
залежність між величинами, що спостерігаються. Якщо коефіцієнт кореляції
лежить близько до -1, то це говорить про зворотну залежність між ними.
Прапорець перетинання кривої з віссю Y у точці встановлюється тільки в
тому випадку, коли ця точка відома. Наприклад, якщо даний прапорець
включений і у відповідне йому поле введене значення 0, те це означає, що
шукається модель у = Ьх.

5. Натисніть на кнопку ОК.

Рис. 5.23 — Лінія тренду

Результат виконання команди Додати лінію тренда приведений на рис. 5.23.
Квадрат коефіцієнта кореляції дорівнює 0.9009. Отже, лінійна модель може
бути використана для пророкування результатів.

5.5.2. Експоненціальна модель

Іншою моделлю регресійного аналізу, яка часто зустрічається на практиці,
є експоненціальна модель, що описується рівнянням

y=bmx

Значення експонентного трендe можна передбачити за допомогою функції
РОСТ (GROWTH).

Синтаксис:

РОСТ (поч_знач_у;  поч_знач_х;  нов_знач_х;   константа)

— поч_энач_у — масив відомих значень залежної величини, що
спостерігається;

— поч_знач_х — масив відомих значень незалежної величини, що
спостерігається. Якщо поч_знач_х опущені, то передбачається, що це масив
{1; 2; 3; …} такого ж розміру, як і поч_знач_у;

— нов_знач_х — нові значення х, для яких РОСТ повертає відповідні
значення у;

— константа — логічне значення, що вказує, чи потрібно, щоб константа b
дорівнювала 0. Якщо константа має значення ИСТИНА чи опущена, то b
обчислюється звичайним чином. Якщо константа має значення ЛОЖЬ, то b
покладається рівним 0.

Значення параметрів експонентної моделі визначаються за допомогою
функції ЛГРФПРИБЛ (LOGEST).

Синтаксис:

ЛГРФПРИБЛ(поч_знач_у; поч_знач_х; константа; стат)

— поч_знач_у — масив відомих значень залежної величини, що
спостерігається;

— поч_знач_х — масив відомих значень незалежної величини, що
спостерігається. Якщо поч_знач_х опущені, то передбачається, що це масив
{1; 2; 3; …} такого ж розміру, як і поч_энач_у;

— константа — логічне значення, що вказує, чи потрібно, щоб константа b
дорівнювала 0. Якщо константа має значення ИСТИНА чи опущена, то b
обчислюється звичайним чином. Якщо константа має значення ЛОЖЬ, то b
покладається рівним 0;

— стат— логічне значення, що вказує, чи потрібно вернути додаткову
статистику по регресії, наприклад коефіцієнт кореляції. Якщо стат має
значення ИСТИНА, то функція ЛГРФПРИБЛ обертає додаткову регресійну
статистику. Якщо  стат має значення ЛОЖ чи опущена, то функція ЛГРФПРИБЛ
повертає тільки значення коефіцієнтів.

Лінійний і експонентний тренди тісно зв’язані між собою. .Покажемо це на
розглянутому в даному розділі прикладі з продажами шин.

Спочатку на основі наявних статистичних даних по обсязі продажів за
перші десять тижнів зробимо прогноз на основі лінійної моделі очікуваних
продажів за наступні п‘ять тижні (рис. 5.24). З цією метою введіть в
чарунки діапазону В12:В16 наступну формулу масиву (не забудьте її
введення завершити натисканням комбінації клавіш ++
+):

{=ТЕНДЕНЦИЯ(В2:В11;А2:А11;А12:А16)}

У діапазоні С2:С16 зробимо прогноз на основі експонентної моделі. З цією
метою в чарунки цього діапазону введіть наступну формулу масиву (не
забудьте її введення завершити натисканням комбінації клавіш
++):

{=РОСТ(В2:В11;А2:А11;А2:А16)}

В чарунки діапазону D2:D16 уведіть формулу масиву

{=ЕХР(ТЕНДЕНЦИЯ(LN(B2:B11);А2:А11;А2:А16))}

Очевидно, що значення в діапазонах С2:С16 і D2:D16 співпадають.

Для визначення параметрів експонентної моделі в чарунки діапазону Е2:F2
введіть формулу масиву

{=ЛГРФПРИБЛ(В2:В11;А2:А11}}

Квадрат коефіцієнта кореляції експонентної моделі дорівнює 0.8744 і
менше квадрата коефіцієнта кореляції лінійної моделі. Таким чином, у
даному випадку лінійна модель більш вірогідно описує залежність між
величинами, що спостерігаються.

Рис. 5.24 — Зв’язок між лінійною й експонентною лініями тренда

ЛІТЕРАТУРА

Бухвалов А.В. и др. Финансовые вычисления для профессионалов.- СПб.:
БХВ-Петербург, 2001.-320с. ил.

Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах.- СПб.:
БХВ-Петербург, 2001.- 816с.:ил.

Евдокимов В.В. и др. Экономическая информатика. Учебник для вузов. Под
ред. Д.э.н., проф. В.В.Евдокимова. – СПб.: Питер, 1997. – 592с.

Згуровський М.З., Коваленко І.І., Міхайленко В.М. Вступ до комп’ютерних
інформаційних технологій: Навч.посіб. – К.: Вид-во Європ. ун-ту
(фінанси, інформ. системи, менеджм. і бізнес), 2000.- 265 с.

Информатика. Базовый курс/ Симонович С.В. и др.- СПб.: Питер, 2000.-
640с.:ил.

Карлберг, Конрад. Бизнес-анализ с помощью Excel.: Пер с англ.- К.:
Диалектика, 1997.- 448с.: ил.

Лук‘янова В.В. Комп‘ютерний аналіз даних: Посібник. – К.: Видавничий
центр „Академія”, 2003. – 344с. (Альма-матер)

Похожие записи