Регресия в Excel: уравнение, примери. Линейна регресия

Регресионният анализ е статистически метод на изследване, който позволява да се покаже зависимостта на даден параметър от една или няколко независими променливи. В епохата на предкомпютъра, използването му беше доста трудно, особено когато става въпрос за големи количества данни. Днес, след като научите как да изградите регресия в Excel, можете да разрешите сложни статистически проблеми само за няколко минути. По-долу са дадени конкретни примери от областта на икономиката.

Видове регресия

Тази концепция беше въведена в математиката Франсис Галтън през 1886 г. Регресията се случва:

• линеен;
• параболична;
• закон за властта;
• експоненциално;
• хиперболичен;
• експоненциално;
• логаритмична.

Пример 1

Нека разгледаме проблема с определянето на зависимостта на броя на пенсионираните членове на колектива от средната работна заплата в 6 промишлени предприятия.

Задачата. Шест предприятия анализираха средната месечна заплата и броя на служителите, които напуснаха собствената си воля. В табличен вид имаме:

За проблема за определяне на зависимостта на броя на служителите, останали на средната работна заплата при 6 предприятия, регресионният модел има формата на уравнението Y = a₀ + и₁х₁ +hellip- + а_кх_к, където x_аз - повлияване на променливите, a_аз - коефициенти на регресия и k - брой фактори.

За тази задача Y е индикаторът на служителите, които са напуснали, а факторът, който влияе върху тях, е заплатата, която е означена с X.

Използване на процесор за таблици в Excel

Анализът на регресията в Excel трябва да бъде предшестван от приложението на вградените функции към наличните таблични данни. За тези цели обаче е по-добре да използвате много полезната добавка "Пакет за анализ". За да го активирате, трябва:

• От раздела "Файл" отидете в секцията "Опции";
• в отворения прозорец изберете реда "Добавки";
• Кликнете върху бутона "Отиди", разположен долу, вдясно от линията "Управление";
• Поставете отметка до името "Пакет за анализ" и потвърдете действията си, като кликнете върху "Ok".

Ако е направено правилно, правилният бутон се появява в дясната част на раздела Данни, разположен над работния лист на Excel.

Линейна регресия в Excel

Сега, когато разполагаме с всички необходими виртуални инструменти, за да приложим иконометрични изчисления, можем да започнем да решаваме нашия проблем. За да направите това:

• кликнете върху бутона "Анализ на данните";
• в отворения прозорец кликнете върху бутона "Регресия";
• в съответния раздел вписваме диапазона от стойности за Y (броя на останалите служители) и за X (заплатата им);
• потвърждаваме действията си, като кликнете върху бутона "ОК".

В резултат на това програмата автоматично попълва нов лист от табличния процесор с данни за регресионния анализ. Обърнете внимание! В Excel има възможност самостоятелно да определите мястото, което предпочитате за тази цел. Например, това може да бъде един и същ лист като стойностите Y и X или дори нова книга, специално предназначена за съхранение на такива данни.

Анализ на резултатите от регресията за R-квадрата

В Excel данните, получени по време на обработката на данните от въпросния пример, имат формата:

На първо място, трябва да обърнете внимание на стойността на R-квадрата. Това е коефициентът на определяне. В този пример R-квадрата = 0.755 (75.5%), т.е. изчислените параметри на модела обясняват зависимостта между разглежданите параметри с 75.5%. Колкото по-висока е стойността на коефициента на определяне, избраният модел се счита за по-приложим за определена задача. Смята се, че правилно описва реалната ситуация с R-квадратна стойност над 0.8. Ако R-квадрата<0.5, тогава такъв анализ на регресията в Excel не може да се счита за разумен.

Анализ на коефициентите

Номерът 64.1428 показва каква ще бъде стойността на Y, ако бъдат променени всички променливи xi в разглеждания модел. С други думи, може да се твърди, че стойността на анализирания параметър е повлияна от други фактори, които не са описани в конкретен модел.

Следващият фактор -0.16285 намира в клетка Б18, показва важно влияние на променливата X на Y. Това означава, че средното възнаграждение на служители в модел отразява броя на оставка от теглото на -0.16285, т. Е. степента на неговото въздействие изобщо малък. Знакът ";" показва, че коефициентът има отрицателна стойност. Това е очевидно, тъй като всеки знае, че колкото по-висока е заплатата в предприятието, толкова по-малко хора изразяват желание да прекратят трудовия договор или да напуснат.

Многократна регресия

С това понятие имаме предвид уравнението на връзката с няколко независими променливи на формата:

y = f (х₁+х₂+hellip-х_m) + epsilon-, където y е резултантният атрибут (зависима променлива) и x₁, х₂, hellip-х_m - това са признаци-фактори (независими променливи).

Оценка на параметрите

За множествена регресия (MP), тя се изпълнява с метода на най-малките квадрати (OLS). За линейни уравнения с форма Y = a + b₁х₁ +hellip- + b_mх_m+ epsilon - изграждаме система от нормални уравнения (виж по-долу)

За да разберете принципа на метода, разгледайте случая с два фактора. След това имаме ситуация, описана от формулата

Оттук получаваме:

където sigma- е отклонението на съответната характеристика, отразена в индекса.

OLS е приложима към уравнението на MP в стандартизиран мащаб. В този случай получаваме уравнението:

в която t_ш, т_{х1 hellip-}т_XM - стандартизирани променливи, за които средните стойности са 0- бета-_аз - стандартизирани регресионни коефициенти, а стандартното отклонение е 1.

Моля, обърнете внимание, че всичко бета-_аз в този случай са дадени като нормализирани и централизирани, така че тяхното сравнение помежду им се счита за правилно и допустимо. Освен това е обичайно да се изясняват факторите, като се изхвърлят тези, които имат най-ниските стойности бета-I.

Проблемът с използването на уравнението за линейна регресия

Да предположим, че има таблица на ценовата динамика на дадена стока N през последните 8 месеца. Необходимо е да се вземе решение за целесъобразността от закупуването на партидата на цена от 1850 рубли на тон.

За да разрешите този проблем в табличния процесор на Excel, трябва да използвате вече известния инструмент "Анализ на данните". След това изберете секцията "Регресия" и задайте параметрите. Ние трябва да помним, че в "Input обхват Y» трябва да се въведе в диапазон от стойности на зависимата променлива (в този случай цената на стоките в определени месеци от годината) и в "Input интервал X» - за независима (месец). Потвърдете действието, като кликнете върху "Ok". На новия лист (ако е посочено) получават данните за регресията.

На тях изграждаме линейно уравнение с форма y = ax + b, където параметрите на a и b са коефициентите на линията с името на числото на месеца и коефициентите и линиите "Y-пресичане" от листа с резултатите от регресионния анализ. По този начин линейната регресионна уравнение (VR) за проблем 3 е написана като:

Цената на стоката N = 11,714 * е числото на месеца + 1727,54.

или в алгебрична нотация

у = 11.714 х + 1727.54

Анализ на резултатите

За да се определи дали полученото линейно регресионно уравнение е адекватно, се използват коефициентите на множествена корелация (KMC) и определяне, както и критерия Fisher и теста на Student. В таблицата в Excel с регресионни резултати те се показват под имената на множество R, R-квадрат, F-статистика и t-статистика, съответно.

KMC R дава възможност да се оцени тежестта на вероятностната връзка между независимите и зависимите променливи. Неговата висока стойност показва относително силна връзка между променливите "Номер на месеца" и "Цената на стоките N в рубли на 1 тон". Въпреки това, естеството на тези отношения остава неизвестно.

Квадратът на коефициента на определяне R²(RI) е цифрова характеристика на частта от общото разпространение и показва разсейването на коя част от експерименталните данни, т.е. Стойностите на зависимата променлива съответстват на уравнението на линейната регресия. В разглеждания проблем тази стойност е 84.8%, т.е. статистическите данни се описват с висока степен на точност от получената SD.

Ф-статистиката, наричана още критерий на Фишър, се използва, за да се оцени значението на линейната зависимост, опровергавайки или потвърждавайки хипотезата за нейното съществуване.

Стойността на t-статистиката (Студентски тест) помага да се оцени значението на коефициента за неизвестен или свободен срок на линейната зависимост. Ако стойността на t-теста> t_кр, тогава се отхвърля хипотезата за незначимостта на свободния термин на линейното уравнение.

В този проблем за свободен Терминът чрез инструменти "Excel" е установено, че т = 169,20903, и р = 2,89E-12, т. Е. имат нулева вероятност верните ще бъде отхвърлена хипотезата за незначителност на свободната план. За коефициента с неизвестен t = 5.79405 и p = 0.001158. С други думи, вероятността правилната хипотеза за незначителността на коефициента за неизвестното да бъде отхвърлена е 0,12%.

По този начин може да се твърди, че полученото линейно регресионно уравнение е адекватно.

Проблемът за целесъобразността на купуването на блок акции

Многократната регресия в Excel се изпълнява, като се използва същият инструмент "Анализ на данните". Нека разгледаме конкретно приложен проблем.

Управляващото дружество "ННН" трябва да вземе решение относно целесъобразността от закупуване на 20% от акциите на АД "МММ". Цената на пакета (SP) е 70 милиона щатски долара. Специалисти от "NNN" събират данни за подобни сделки. Беше решено да се оцени стойността на участието в такива параметри, изразени в милиони щатски долари, като:

• дължими сметки (VK);
• обем на годишния оборот (VO);
• вземания (VD);
• стойност на дълготрайните активи (SOF).

Освен това, параметърът за просрочия на заплатите в предприятието (V3 P) се използва в хиляди долари.

Решение, използващо електронна таблица в Excel

На първо място, трябва да създадете таблица с входни данни. Тя има следната форма:

Напред:

• Обадете се в прозореца "Данни за анализ";
• изберете секцията "Регресия";
• В полето "Въведете интервал Y" въведете диапазона от стойности на зависимите променливи от графа G;
• кликнете върху иконата с червената стрелка отдясно на прозореца "Интервал на въвеждане X" и изберете диапазона от всички стойности от колони B, C, D, F на листа.

Маркирайте елемента "Нов работен лист" и кликнете върху "Ok".

За тази задача се получава регресионен анализ.

Резултати от проучването и заключения

"Събиране" от закръглените данни, представени по-горе в работния лист на Excel, уравнението на регресията:

SP = 0,103 * SOF + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265,844.

В по-позната математическа форма тя може да бъде записана като:

у = 0.103 * х1 + 0.541 * х2 - 0.031 * х3 + 0.405 * х4 + 0.691 * х5 - 265.844

Данните за МММ са, както следва:

Като ги замените в уравнението на регресията, вземете цифра от 64,72 милиона щатски долара. Това означава, че акциите на MMM не трябва да бъдат закупени, тъй като тяхната стойност от 70 милиона щатски долара е доста по-голяма.

Както виждате, използването на табличния процесор на Excel и регресионното уравнение позволиха да се вземе информирано решение за осъществимостта на много конкретна транзакция.

Сега знаете каква е регресията. Примерите в Excel, обсъдени по-горе, ще ви помогнат при решаването на практически проблеми от областта на иконометрията.