Лінійна і експонентна регресії – ЧАСТИНА 1

В арсенал засобів Excel включені наступні пять функцій для розрахунку лінійної регресії: ЛИНЕЙН (LINEST), ТЕНДЕНЦІЯ (TREND), ПРЕДСКАЗ (FORECAST), НАХИЛ (SLOPE) і СТОШУХ (STEYX), і дві функції для розрахунку експоненційної регресії: ЛГРФПРІБЛ (LОGEST) і РОСТ (GROWTH) Всі ці функції оперують масивами даних і повертають також масив результатів Будь-яку з них можна використовувати з однією або декількома незалежними змінними Нижче наводиться опис різних типів регресії:

► Лінійна регресіяВизначає кут нахилу прямої, найкращим чином представляє набір даних Метод лінійної регресії дозволяє, наприклад, грунтуючись на даних про продажі за поточний рік, розрахувати прогнозований обсяг продажів за березень наступного року Коротко кажучи, будується графік, на якому

по осі абсцис (X) відкладається час (тут місяці), а по осі ординат (Y) – обсяги продажів, і залежність останніх від часу апроксимується деякої кривої, в даному випадку – похилій прямий, саме тому даний варіант регресії називається лінійним Виходячи з припущення лінійного росту обсягів продажів, продовживши пряму в часі, можна складати прогнози на майбутнє

Експонентна регресія Розраховує експонентну криву, найкращим чином описує безліч даних, для яких не виконується умова пропорційного {лінійного) зміни за часом Наприклад, ряд вимірювань зростання населення завжди краще описується експонентою, ніж прямою лінією

Багатовимірна регресіяВиконує одночасний аналіз для декількох сукупностей даних, що в багатьох випадках дає більш реалістичний результат Excel дозволяє застосовувати для аналізу різних даних як лінійну, так і експонентну багатовимірну регресію Припустимо, що потрібно оцінити вартість реконструкції будинку в певному районі, грунтуючись на даних про його житлової площі, кількості кімнат, розмірі ділянки та вік споруди За допомогою багатовимірної регресії можна вирішити цю задачу, маючи на руках дані, зібрані за існуючими домівках

ПРИМІТКА

Зазвичай з поняттям «регресія» повязують рух назад Однак у світі статистики регресія часто використовується, навпаки, для передбачення майбутніх подій Щоб це поняття краще вклалося в голові, дамо йому таке визначення: регресія – це статистичний метод, що дозволяє знайти математичне рівняння, яке найкращим чином описує набір даних

Досить часто самі бізнесмени намагаються прогнозувати майбутні обсяги продажів, спираючись на дані про продажі за минулий період Метод процентовки, наприклад, враховує активи і пасиви підприємства, які по-різному змінюються в часі, тому при прогнозуванні майбутніх продажів кожної з цих частин призначається своя процентна залежність від часу Цей простий метод здатний давати цілком реалістичні прогнози у випадках повільної динаміки розвитку підприємства або динаміки з постійним значенням приросту, але він швидко втрачає точність при швидкому і нелінійному зростанні продажів

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

Лінійна регресія

Рівняння у = тх + b математично описує пряму лінію для набору даних з однієї незалежної змінної, дех –незалежна змінна,у– Залежна змінна,т –кутовий коефіцієнт, що визначає нахил лінії, аb   – Коефіцієнт, що визначає точку перетину прямої з віссю Y При багатовимірному регресійному аналізі лінія регресії представляє внесок

декількох незалежних змінних в модельований результат У цьому випадку рівняння лінійної регресії перепишеться в наступному вигляді: у = т1х1 + т2×2 + + тпхп

+  b,  деу– Як і раніше залежна змінна,х1..,  хп– Цепнезалежних

змінних, т1,., тп – Коефіцієнти при незалежних змінних, а b —  константа

Функція ЛИНЕЙН

Функція ЛИНЕЙН (LINEST) використовує останнє, більш загальне рівняння і методом найменших квадратів розраховує пряму лінію, яка найкращим чином узгоджувалася б з наявними даними – Значеннями незалежних змінних х і відповідними їм значеннями залежної змінної у Функція повертає масив, в який входять всі коефіцієнтит1..,  тпі вільний членb,  повністю описують розраховану пряму, і має такі аргументи: ізвестние_значенія_у ізвестние_зпаченія_х конст статистика,де ізвестние_значелшя_у – безліч значень у,які вже визначені раніше іншим способом Цей аргумент може бути представлений одним стовпцем, одним рядком або діапазоном клітинок Якщо масив ізвестние_значенія_у записаний в один стовпець або один рядок, то відповідно кожен стовпець або рядок масивуізвестние_зіаченія_х інтерпретується як окрема незалежна змінна Якщо ж аргумент ізвестние_значеіія_упредставлений у вигляді прямокутного діапазону, в рівнянні бере участь тільки одна незалежна змінна У такому випадку діапазон, що задає аргумент ізвестние_значенія_х, повинен мати ті ж розмір і форму, що й діапазон зі значеннями змінної у

Необовязковий аргументізвестние_значепія_х,містить значення незалежної змінної х, можна опускати Тоді вважається, що він являє собою масив {1 2 3 ..} такого ж розміру, як і масив ізвестние_значенія_у

Необовязкові аргументи конст і статистика є логічними константами і приймають значення ІСТИНА або БРЕХНЯ (допускається підстановка 1 замість ІСТИНА і 0 замість БРЕХНЯ) Якщо аргументконствизначений як ІСТИНА або опущений, вільний член b обчислюється звичайним чином, коли ж аргумент приймає значення БРЕХНЯ, b покладається рівним 0

Аргумент статистика вказує, чи потрібно повернути додаткову статистику по регресії Якщо він приймає значення ІСТИНА, функція ЛИНЕЙН повертає наступну додаткову інформацію:

seb .. , sen                       Стандартні значення помилок для коефіцієнтів

Seb                                         Стандартна помилка для константи b r2                                              Коефіцієнт змішаної кореляції Sey                                         Стандартна помилка для оцінки у

F                               F-статистика

Df                                             Ступені свободи

Ssreg                                      Регресійна сума квадратів

Ssresid                                   Залишкова сума квадратів

Перш ніж створювати формулу з включенням функції ЛИНЕЙН, необхідно виділити діапазон комірок, достатній для розміщення масиву повертаються нею величин Якщо аргумент статистика має значення БРЕХНЯ або опущений, функція ЛИНЕЙН поверне тільки коефіцієнти т і постійну b, тобто лінійний масив (вектор), в кожному осередку якого міститься по одному коефіцієнту т, а в останній комірці масиву – константа b Якщо для цього аргументу задано значення ІСТИНА, повертається масив буде мати наступну структуру:

тп

тп 1

..

m2

т1

b

sen

sen-1

..

se2

se1

seb

r2                               Sey

F                    Df

Ssreg                       Ssresid

Після виділення діапазону для повертається масиву даних введіть функцію і натисніть клавіші Ctrl + Shift + Enter, щоб результат був представлений у вигляді масиву Зверніть увагу, що коефіцієнти і значення стандартних помилок для незалежних

змінних повертаються у зворотному порядку Наприклад, якщо чотири незалежні

змінні розташовуються в чотирьох різних стовпцях, то функція ЛИНЕЙН розглядає крайній лівий стовпець як дані для змінноїхиале при цьому значення коефіцієнта т1 зявиться в крайньому правому стовпчику вихідного масиву

На рис 173 показаний приклад використання функції ЛИНЕЙН при однієї незалежної змінної Значення в стовпці В представляють попит продукції малого підприємства на місяць Числа в стовпці А – це порядкові номери місяців Припустимо, потрібно побудувати пряму регресії, що відображає залежність попиту продукції підприємства при його розвитку по місяцях, або, іншими словами, математично описати набір даних Для цього виділіть діапазон F6: G6, введіть формулу

= ЛИНЕЙН (В2: В19 А2: А19) і натисніть клавіші Ctrl + Shift + Enter Число в комірці F6 (20,613) показує кутовий коефіцієнт прямої регресії, а число в комірці G6 (4002,065) – це точка перетину лінії регресії з віссю Y

ПРИМІТКА

Функції ЛИНЕЙН і ЛГРФПРІБЛ повертають тільки координати по осі Y, які потім і використовуються для побудови прямих і кривих Різниця між ними полягає в тому, що функція ЛИНЕЙН аппроксимирует набір даних прямий, а ЛГРФПРІБЛ – експоненційної кривої При аналізі наявних даних ви повинні приймати цю різницю до уваги Функція ЛИНЕЙН краще підходить для розрахунків продажів, а функція ЛГРФПРІБЛ – для статистичного аналізу або аналізу даних, що відносяться до чисельності народонаселення Детальніше функція ЛГРФПРІБЛ розглядається далі в розділі «Функція ЛГРФПРІБЛ»

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*