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

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

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

линейна;

параболична;

степен;

експоненциално;

хиперболичен;

индикативна;

логаритмична.

Пример 1

Разгледайте задачата за определяне на зависимостта на броя на освободените членове на екипа върху средната работна заплата в 6 промишлени предприятия.


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





А



Б










1



X



43) Заплата



2



y



30000 рубли

58)
3



1



60



35000 рубли
73)

4



2



35



40000 рубли 88)


5



3



20



45000 рубли



6



4



20



50000 рубли



7



5



15



55000 рубли



8



6








60000 рубли. kxk, където xi се влияят от променливи, ai - коефициенти на регресия, ak - брой фактори. За тази задача Y е броят на уволнените служители, а факторът е заплатата, която ние обозначаваме X.

Използвайки възможностите на процесора за електронни таблици на Excel

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

от раздела Файл отидете в раздела "Опции";

в прозореца, изберете реда "Добавки";

Кликнете върху бутона "Go", намиращ се по-долу, вдясно от реда "Управление";

поставете отметка до името "Пакет за анализ" и потвърдете действията си, като кликнете върху "ОК".

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

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

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

кликнете върху бутона "Анализ на данните";

В прозореца натиснете бутона "Регресия";

В появилия се раздел въведете диапазона от стойности Y (брой излишни работници) и X (техните заплати);

потвърждаваме действията си, като натискаме бутона "OK".

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

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

В Excel данните, получени по време на обработката на данните от разглеждания пример, имат следния вид: Преди всичко трябва да обърнете внимание на стойността на R-квадрата. Това е коефициент на определяне. В този пример R-квадратът = 0755 (755%), т.е. изчислените параметри на модела обясняват зависимостта между разглежданите параметри от 755%. Колкото по-висока е стойността на коефициента на детерминиране, избраният модел се счита за по-подходящ за конкретна задача. Счита се, че той правилно описва реалната ситуация със стойността на R-квадрата над 08. Ако R-квадрата на анализа на коефициентите 641428 показва стойността на Y, ако всички променливи xi в нашия модел се нулират. С други думи, може да се твърди, че стойностите на анализирания параметър се влияят от други фактори, които не са описани в конкретен модел. Следващият фактор от -016285, разположен в клетка В18, е теглотовлиянието на променливата X върху Y. Това означава, че средната месечна заплата на служителите в рамките на разглеждания модел влияе върху броя на освободените с тегло -016285, т.е. степента на нейното влияние е много малка. Знакът "-" показва, че коефициентът има отрицателна стойност. Това е очевидно, тъй като всеки знае, че колкото повече заплата в предприятието, толкова по-малко хора изразяват желанието си да прекратят трудов договор или да бъдат освободени.

Множествена регресия

В този термин е уравнението на комуникацията с няколко независими променливи от вида: y = f (x 1 + x 2 + xm) + ?, където y е производителна характеристика (зависима променлива), 1, x 2, xm са атрибутни фактори (независими променливи).

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

За множествена регресия (МР) тя се извършва по метода на най-малките квадрати (MNC). За линейни уравнения от вида Y = a + b 1 x 1 ++ b m x m +? Конструираме система от нормални уравнения (виж по-долу)
За да се разбере принципът на метода, разгледайте двуфакторния случай. Тогава имаме ситуация, описана с формула Оттук получаваме: Къде? - е вариацията на съответния знак, отразена в индекса. МНК ще се прилагат към уравнението на МР в стандартизиран мащаб. В този случай получаваме уравнението: в които t y, t x 1 t xm - стандартизирани променливи, за които средните стойности са равни на 0; ? i - стандартизирани регресионни коефициенти и средно квадратично отклонение - 1. Имайте предвид, че всички? и в този случай се дават като нормализирани и централизирани, следователно тяхното сравнение помежду си се счита за правилно и допустимо. Освен това,Приема се да се извърши приспадането на факторите, отхвърляйки тези, които имат най-малки стойности?

Задачи, използващи уравнението на линейната регресия

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














в


)


номер на месеца



месец име



цената на стоките N



2



1



януари


228) 1750 рубли за тон



3



2



февруари

)

1755 рубли за тон



4



3



март



1767 рубли за тон



5



4


)
Април



1760 рубли от и



6



5



май



1770 рубли за тон



7



6



юни
306)

1790 рубли за тон



8



7



Юли



1810 рубли за тон



9



8

335)




1840 рубли за тон








350)







За да разрешите този проблем в процесора на Excel, трябва да използвате вече Idoma хора, представени в горния пример функцията "Анализ на данни." След това изберете секцията "Регресия" и попитайтенастройки. Трябва да се помни, че в полето за въвеждане на интервал Y трябва да бъде въведен диапазонът на стойностите на зависимата променлива (в този случай цената на продукта в конкретните месеци на годината), а в "Входен интервал X" за независимия (номер на месеца). Потвърдете действията, като кликнете върху „ОК“. На нов лист (ако е посочено) получаваме данни за регресия. Конструираме линейно уравнение на формата y = ax + b, където коефициентите на линията с името на номера на месеца и коефициентите и линията "Y-пресичане" от буквата с резултатите от регресионния анализ действат като параметри a и b. По този начин, уравнението на линейната регресия (UR) на задача 3 се записва като: Цена на продукта N = 11714 * месец номер + 172754. или алгебрична нотация y = 11714 x + 172754

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

Да се ​​реши дали е адекватно получено уравнението на линейната регресия, коефициентите на множествена корелация (КМК) и определянето, както и критерия на Фишер и критерия на Студент. В таблицата "Excel" с резултатите от регресията, те действат като множество R R-квадрати, F-статистика и t-статистика, съответно. KMK R дава възможност да се оцени близостта на вероятностната връзка между независими и зависими променливи. Неговата висока стойност показва доста силна връзка между променливите "Месец на месеца" и "Цената на стоките в рубли за 1 тон". Въпреки това естеството на тази връзка остава неизвестно. Квадратът на коефициента на определяне R 2 (RI) е числена характеристика на частта от общото разпространение и показва, че разпространението на която и да е част от експерименталните данни, т.е. стойностите на зависимата променлива, съответства.уравнение на линейна регресия. В разглеждания проблем тази стойност е равна на 848%, т.е. статистическите данни с висока степен на точност са описани с получената SD. F-статистика, наричана още Фишеров критерий, се използва за оценка на значимостта на линейната зависимост, опровергаване или потвърждаване на хипотезата за неговото съществуване. Стойността на t-статистиката (критерий на студента) помага да се оцени значимостта на коефициента за неизвестен или свободен член на линейната зависимост. Ако стойността на t-критерия & gt; t cr, тогава хипотезата за незначителността на свободен член на линейно уравнение се отхвърля. В разглеждания проблем за свободен член, използващ инструментите на Excel, беше установено, че t = 16920903 и p = 289 E-12, т.е. имаме нулева вероятност, че правилната хипотеза за незначителността на свободния термин ще бъде отхвърлена. За коефициент с неизвестно t = 579405 и р = 0001158. С други думи, вероятността за отхвърляне на правилната хипотеза за незначителността на коефициента при неизвестното е 012%. По този начин може да се твърди, че полученото уравнение за линейна регресия е адекватно.

Целта на придобиването на дялово участие

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

Задължения по сметки (VK);

годишен оборот (VO);

вземания (VD);

стойност на дълготрайните активи (SOF).

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

Решения за електронни таблици на Excel

Първо, трябва да съставите таблица с изходни данни. Той има следния вид: Следваща:

извикайте прозореца "Анализ на данните";

изберете раздела "Регресия";

в прозореца "Входен интервал Y" се въвежда диапазонът от стойности на зависимите променливи на колона G;

щраква върху иконата с червена стрелка вдясно от прозореца "Input X interval" и разпределя колона от стойности за колони B, C, D, F.

Маркирайте "Нов работен лист" и щракнете върху "OK" , Получете регресионен анализ за тази задача.

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

"Ние събираме" от закръглените данни, представени по-горе на листа на Excel табличния процесор, уравнението на регресия: SP = 0103 * SOF + 0541 * VO - 0031 * VK + 0405 * VD + 0691 * VZP - 265844. В по-обичайна математическа форма тя може да бъде записана като: y = 0103 * x1 + 0541 * x2 - 0031 * x3 + 0405 * x4 + 0691 * x5 - 265844 Данните за АД "МММ" са представени в таблицата: 375)
SOF, USD



VO, USD



VK, USD



VD, USD



VZP, USD



SP, USD



1025
402)

5355



452



415



2155



6472

\ tцифра от 6472 милиона щатски долара. Това означава, че акциите на "МММ" АД не трябва да се купуват, тъй като тяхната стойност в 70-те милиона щатски долара е доста надценена. Както можете да видите, използването на електронната таблица на Excel и уравнението за регресия ни позволиха да вземем разумно решение за осъществимостта на напълно специфична транзакция. Сега знаете каква е регресията. Примерите в Excel, разгледани по-горе, ще ви помогнат да решите практически проблеми в областта на иконометрията.