Регресионната линия е графично отражение на връзката между явленията. Можете много ясно да изградите регресионна линия в Excel.

За да направите това ви трябва:

1. Отворете Excel

2. Създаване на колони с данни. В нашия пример ще изградим регресионна линия или връзка между агресивността и неувереността в себе си при първокласниците. В експеримента са участвали 30 деца, данните са представени в таблицата на Excel:

1 колона - номер на тема

2 колона - агресивноств точки

3 колона - неувереност в себе сив точки

3. След това трябва да изберете и двете колони (без името на колоната), щракнете върху раздела вмъкнете , изберете място , и изберете най-първото от предложените оформления точка с маркери .

4. Така че имаме шаблон за регресионната линия - т.нар. точкова диаграма. За да отидете на линията на регресия, щракнете върху получената фигура и натиснете tab конструктор, намерете на панела оформления на диаграми и изберете М Акет9 , пише още f(x)

5. И така, имаме регресионна линия. Графиката също показва неговото уравнение и квадрата на корелационния коефициент

6. Остава само да добавите името на графиката и имената на осите. Също така, ако желаете, можете да премахнете легендата, да намалите броя хоризонтални линиирешетки (таб оформление , тогава нето ). Основните промени и настройки се извършват в раздела Оформление

Регресионната линия е построена в MS Excel. Сега можете да го добавите към текста на работата.

Това е най-често срещаният начин да се покаже зависимостта на една променлива от други, например, как става ниво на БВПот размера чуждестранни инвестицииили от Лихвен процент по кредитите на Националната банкаили от цените на основните енергийни ресурси.

Моделирането ви позволява да покажете величината на тази зависимост (коефициенти), благодарение на което можете директно да направите прогноза и да извършите някакъв вид планиране въз основа на тези прогнози. Също така, въз основа на регресионен анализ е възможно да се вземат управленски решения, насочени към стимулиране на приоритетни причини, влияещи върху крайния резултат; самият модел ще помогне да се идентифицират тези приоритетни фактори.

Общ изглед на модела на линейна регресия:

Y=a 0 +a 1 x 1 +...+a k x k

Къде а - регресионни параметри (коефициенти), х - влияещи фактори, к - брой фактори на модела.

Изходни данни

Сред първоначалните данни се нуждаем от определен набор от данни, които биха представлявали няколко последователни или взаимосвързани стойности на крайния параметър Y (например БВП) и същия брой стойности на показателите, чието влияние изучаваме ( например чуждестранни инвестиции).

Фигурата по-горе показва таблица със същите тези изходни данни, Y е индикатор за икономически активното население, а броят на предприятията, размерът на инвестициите в капитал и доходите на домакинствата са влияещи фактори, тоест X.

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

Да се ​​изгради адекватна линейни моделиЖелателно е изходните данни да нямат силни спадове или сривове; в такива случаи е препоръчително да се извърши изглаждане, но следващия път ще говорим за изглаждане.

Пакет за анализ

Параметрите на линеен регресионен модел могат също да бъдат изчислени ръчно с помощта на обикновения метод на най-малките квадрати (OLS), но това отнема доста време. Това може да се изчисли малко по-бързо, като се използва същият метод, като се използват формули в Excel, където самата програма ще направи изчисленията, но все пак ще трябва да въвеждате формулите ръчно.

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

Активирайте пакета за анализ

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

В Excel горе вляво активирайте раздела Файл, в менюто, което се отваря, потърсете елемента Опциии щракнете върху него.

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

Изскачащ прозорец ще ви подкани да изберете наличните добавки; в него трябва да поставите отметка в квадратчето Пакет за анализи в същото време, за всеки случай, Намиране на решение(също полезно нещо) и след това потвърдете избора си, като щракнете върху бутона добре.

Инструкции за намиране на параметри на линейна регресия с помощта на пакета за анализ

След като активирате добавката Analysis Pack, тя винаги ще бъде достъпна в раздела на главното меню даннипод връзката Анализ на данни

В прозореца на активния инструмент Анализ на данниот списъка с възможности, които търсим и избираме Регресия

След това ще се отвори прозорец за настройка и избор на изходни данни за изчисляване на параметрите на регресионния модел. Тук трябва да посочите интервалите на първоначалните данни, а именно описвания параметър (Y) и факторите, които го влияят (X), както е показано на фигурата по-долу, останалите параметри по принцип не са задължителни за конфигуриране.

След като сте избрали изходните данни и щракнете върху бутона OK, Excel произвежда изчисления на нов лист от активната работна книга (освен ако не е зададено друго в настройките), тези изчисления изглеждат така:

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

така че 0,865 - Това R 2- коефициент на детерминация, показващ, че 86,5% от изчислените параметри на модела, т.е. самият модел, обясняват зависимостта и промените в изследвания параметър - Yот изследваните фактори - X-ове. Ако е преувеличено, тогава това е показател за качеството на моделаи колкото по-високо е, толкова по-добре. Ясно е, че не може да бъде повече от 1 и се счита за добро, когато R 2 е над 0,8, а ако е по-малко от 0,5, тогава разумността на такъв модел може спокойно да бъде поставена под въпрос.

Сега нека да преминем към коефициенти на модела:
2079,85 - Това а 0- коефициент, който показва какво ще бъде Y, ако всички фактори, използвани в модела, са равни на 0, разбира се, че това е зависимост от други фактори, които не са описани в модела;
-0,0056 - а 1- коефициент, който показва тежестта на влиянието на фактора x 1 върху Y, т.е. броят на предприятията в даден модел влияе върху показателя за икономически активното население с тежест само -0,0056 (доста малка степен на влияние ). Знакът минус показва, че това влияние е отрицателно, т.е. колкото повече предприятия, толкова по-малко икономически активно население, колкото и парадоксално да е това по смисъл;
-0,0026 - а 2- коефициент на влияние на обема на инвестициите в капитал върху числеността на икономически активното население според модела, това влияние също е отрицателно;
0,0028 - а 3- коефициент на влияние на доходите на населението върху размера на икономически активното население, тук влиянието е положително, т.е. според модела увеличаването на доходите ще допринесе за увеличаване на размера на икономически активното население.

Нека съберем изчислените коефициенти в модела:

Y = 2079,85 - 0,0056x 1 - 0,0026x 2 + 0,0028x 3

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

Моделни оценки и прогнози

Както вече обсъдихме по-горе, моделът е изграден не само за да покаже величината на зависимостта на параметъра, който се изследва от влияещите фактори, но и така, че познавайки тези влияещи фактори, е възможно да се направи прогноза. Изготвянето на тази прогноза е доста лесно; просто трябва да замените стойностите на влияещите фактори на мястото на съответните X в полученото уравнение на модела. На фигурата по-долу тези изчисления са направени в Excel в отделна колона.

Действителните стойности (тези, които са възникнали в действителност) и изчислените стойности според модела на една и съща фигура се показват под формата на графики, за да покажат разликата и следователно грешката на модела.

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

Ще разгледаме как да изградим авторегресивен модел в следващата статия, но сега нека приемем, че знаем какви ще бъдат стойностите на влияещите фактори в бъдещия период (в примера 2008 г.) и като заместим тези стойности в изчисленията ще получим нашата прогноза за 2008 г.

Статистическата обработка на данни може да се извърши и с помощта на добавка ПАКЕТ ЗА АНАЛИЗ(фиг. 62).

От предложените елементи изберете елемента „ РЕГРЕСИЯ“ и кликнете върху него с левия бутон на мишката. След това щракнете върху OK.

Ще се появи прозорец, както е показано на фиг. 63.

Инструмент за анализ " РЕГРЕСИЯ» се използва за напасване на графика към набор от наблюдения, използвайки метода на най-малките квадрати. Регресията се използва за анализиране на въздействието върху отделно зависимо лице стойностна променливаедна или повече независими променливи. Например, няколко фактора влияят върху спортните постижения на спортиста, включително възраст, височина и тегло. Възможно е да се изчисли степента, до която всеки от тези три фактора влияе на представянето на спортиста и след това да се използват тези данни, за да се предвиди представянето на друг спортист.

Инструментът за регресия използва функцията LINEST.

Диалогов прозорец РЕГРЕСИЯ

Етикети Поставете отметка в квадратчето, ако първият ред или първата колона на входния диапазон съдържа заглавия. Премахнете отметката от това квадратче, ако няма заглавки. В този случай подходящи заглавияза изходната таблица данните ще бъдат създадени автоматично.

Ниво на надеждност Поставете отметка в квадратчето, за да включите допълнително ниво в изходната обобщена таблица. В съответното поле въведете нивото на сигурност, което искате да приложите, в допълнение към нивото по подразбиране от 95%.

Константа - нула Поставете отметка в квадратчето, за да принудите линията на регресия да премине през началото.

Изходен интервал Въведете връзка отляво горна клеткаизходен диапазон. Осигурете поне седем колони за изходната обобщена таблица, която ще включва: резултати от ANOVA, коефициенти, стандартна грешка на изчислението Y, стандартни отклонения, брой наблюдения, стандартни грешки за коефициентите.

Нов работен лист Поставете превключвателя в тази позиция, за да отворите нов листв работната книга и поставете резултатите от анализа, като започнете от клетка A1. Ако е необходимо, въведете име за новия лист в полето, разположено срещу съответния радио бутон.

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

Остатъци Поставете отметка в квадратчето, за да включите остатъците в изходната таблица.

Стандартизирани остатъци Поставете отметка в квадратчето, за да включите стандартизирани остатъци в изходната таблица.

Residual Plot Поставете отметка в квадратчето, за да начертаете остатъците за всяка независима променлива.

Fit Plot Поставете отметка в квадратчето, за да начертаете прогнозираните спрямо наблюдаваните стойности.

Диаграма на нормалната вероятностПоставете отметка в квадратчето, за да начертаете нормална вероятностна графика.

функция LINEST

За да извършите изчисления, изберете с курсора клетката, в която искаме да изведем средната стойност и натиснете клавиша = на клавиатурата. След това в полето Име посочете желаната функция, Например СРЕДЕН(фиг. 22).

функция LINESTизчислява статистика за серия, използвайки най-малките квадрати, за да изчисли права линия, която по възможно най-добрия начинприближава наличните данни и след това връща масив, който описва получената права линия. Можете също така да комбинирате функцията LINESTс други функции за изчисляване на други видове модели, които са линейни в неизвестни параметри (чиито неизвестни параметри са линейни), включително полиномни, логаритмични, експоненциални и степенни редове. Тъй като се връща масив от стойности, функцията трябва да бъде посочена като формула за масив.

Уравнението за права линия е:

y=m 1 x 1 +m 2 x 2 +...+b (в случай на няколко диапазона от стойности x),

където зависимата стойност y е функция на независимата стойност x, m стойностите са коефициентите, съответстващи на всяка независима променлива x, а b е константа. Обърнете внимание, че y, x и m могат да бъдат вектори. функция LINESTвръща масив(mn;mn-1;…;m 1 ;b). LINESTможе също така да върне допълнителни регресионни статистики.

LINEST(известни_стойности_y; известни_стойности_x; конст; статистика)

Known_y_values​ - набор от y-стойности, които вече са известни за релацията y=mx+b.

Ако масивът unknown_y_values ​​има една колона, тогава всяка колона в масива unknown_x_values ​​се третира като отделна променлива.

Ако масивът unknown_y_values ​​има един ред, тогава всеки ред в масива unknown_x_values ​​се третира като отделна променлива.

Известни_x-стойности са незадължителен набор от x-стойности, които вече са известни за връзката y=mx+b.

Масивът unknown_x_values ​​може да съдържа един или повече набора от променливи. Ако се използва само една променлива, тогава масивите unknown_y_values ​​​​и unknown_x_values ​​могат да имат произволна форма - стига да имат едно и също измерение. Ако се използва повече от една променлива, тогава unknown_y_values ​​трябва да бъде вектор (т.е. интервал с височина един ред или ширина на една колона).

Ако array_known_x_values ​​​​се пропусне, тогава се приема, че масивът (1;2;3;...) е със същия размер като array_known_values_y.

Const е булева стойност, която указва дали константата b трябва да бъде равна на 0.

Ако аргументът "const" е TRUE или е пропуснат, тогава константата b се оценява както обикновено.

Ако аргументът „const“ е FALSE, тогава стойността на b е зададена на 0 и стойностите на m са избрани така, че връзката y=mx да е изпълнена.

Статистика - Булева стойност, която указва дали трябва да се върнат допълнителни регресионни статистики.

Ако статистиката е TRUE, LINEST връща допълнителни регресионни статистики. Върнатият масив ще изглежда така: (mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid).

Ако статистиката е FALSE или е пропусната, LINEST връща само коефициентите m и константата b.

Допълнителна регресионна статистика (Таблица 17)

величина	Описание
se1,se2,...,sen	Стандартни стойности на грешка за коефициенти m1,m2,...,mn.
seb	Стандартна стойностгрешки за константа b (seb = #N/A, ако аргументът "const" е FALSE).
r2	Коефициент на детерминизъм. Сравняват се действителните стойности на y и стойностите, получени от уравнението на линията; Въз основа на резултатите от сравнението се изчислява коефициентът на детерминизъм, нормализиран от 0 до 1. Ако е равен на 1, тогава има пълна корелация с модела, т.е. няма разлика между действителните и прогнозните стойности от г. В обратния случай, ако коефициентът на детерминация е 0, няма смисъл да се използва регресионното уравнение за прогнозиране на стойностите на y. За повече информация как да изчислите r2, вижте "Бележки" в края този раздел.
сей	Стандартна грешка за оценка на y.
Е	F-статистика или F-наблюдавана стойност. F-статистиката се използва, за да се определи дали наблюдаваната връзка между зависима и независима променлива се дължи на случайност.
df	Степени на свобода. Степените на свобода са полезни за намиране F-критични стойностив статистическата таблица. За да определите нивото на достоверност на модела, сравнявате стойностите в таблицата с F статистиката, върната от функцията LINEST. За повече информация относно изчисляването на df вижте „Бележки“ в края на този раздел. След това, Пример 4 показва използването на F и df стойности.
ssreg	Регресионна сума на квадратите.
ssresid	Остатъчен сбор от квадрати. За повече информация относно изчисляването на ssreg и ssresid вижте „Бележки“ в края на този раздел.

Фигурата по-долу показва реда, в който се връщат допълнителни регресионни статистики (Фигура 64).

Бележки:

Всяка права линия може да бъде описана чрез нейния наклон и пресичане с оста y:

Наклон (m): За да определите наклона на линия, обикновено означавана с m, трябва да вземете две точки на линията (x 1,y 1) и (x 2,y 2); наклонът ще бъде равен на (y 2 -y 1)/(x 2 -x 1).

Y-пресечната точка (b): Y-пресечната точка на права, обикновено означавана с b, е y-стойността за точката, в която линията пресича оста y.

Уравнението на правата е y=mx+b. Ако стойностите на m и b са известни, тогава всяка точка от линията може да бъде изчислена чрез заместване на стойностите на y или x в уравнението. Можете също да използвате функцията TREND.

Ако има само една независима променлива x, можете да получите наклона и y-отсечката директно, като използвате следните формули:

Наклон: INDEX(LINEST(известни_y_стойности; известни_x_стойности); 1)

Y-отсечка: INDEX(LINEST(известни_y_стойности; известни_x_стойности); 2)

Точността на приближението с помощта на правата линия, изчислена от функцията LINEST, зависи от степента на разсейване на данните. Колкото по-близо до права линия са данните, толкова по-точен е моделът, използван от функцията LINEST. Функцията LINEST използва най-малките квадрати, за да определи най-доброто съответствие с данните. Когато има само една независима променлива x, m и b се изчисляват по следните формули:

където x и y са примерни средни стойности, например x = AVERAGE(известни_x) и y = AVERAGE(известни_y).

Функциите за напасване LINEST и LGRFPRIBL могат да изчислят правата линия или експоненциалната крива, която най-добре отговаря на данните. Те обаче не отговарят на въпроса кой от двата резултата е по-подходящ за решаване на проблема. Можете също така да оцените функцията TREND(известни_y; известни_x) за права линия или функцията GROW(известни_y; известни_x) за експоненциална крива. Тези функции, освен ако не са посочени new_x-стойности, връщат масив от изчислени y-стойности за действителните x-стойности по линия или крива. След това можете да сравните изчислените стойности с действителните стойности. Можете също да създавате диаграми за визуално сравнение.

Когато извършва регресионен анализ, Microsoft Excel изчислява за всяка точка квадрата на разликата между прогнозираната стойност на y и действителната стойност на y. Сумата от тези квадратни разлики се нарича остатъчна сума от квадрати (ssresid). След това Microsoft Excel изчислява общата сума на квадратите (stotal). Ако const = TRUE или стойността на този аргумент не е посочена, обща сумаквадрати ще бъде равна на сумата от квадратите на разликите между реалните стойности на y и средните стойности на y. Когато const = FALSE, общата сума от квадрати ще бъде равна на сумата от квадрати на реалните стойности на y (без изваждане на средната стойност на y от частичната стойност на y). След това регресионната сума на квадратите може да се изчисли, както следва: ssreg = sstotal - ssresid. Колкото по-малка е остатъчната сума на квадратите, толкова по-голяма е стойността на коефициента на детерминация r2, който показва колко добро е уравнението, получено с регресионен анализ, обяснява връзките между променливите. Коефициентът r2 е равен на ssreg/sstotal.

В някои случаи една или повече X колони (нека стойностите Y и X са в колони) нямат допълнителна предикативна стойност в други X колони, с други думи, премахването на една или повече X колони може да доведе до изчислени Y стойности със същата прецизност. В този случай излишните X колони ще бъдат изключени от регресионния модел. Това явление се нарича "колинеарност", тъй като излишните колони на X могат да бъдат представени като сбор от няколко нередундантни колони. Функцията LINEST проверява за колинеарност и премахва всички излишни X колони от регресионния модел, ако ги открие. Премахнатите X колони могат да бъдат идентифицирани в изхода LINEST чрез коефициент 0 и стойност на se 0. Премахването на една или повече колони като излишни променя стойността на df, тъй като зависи от броя на колоните X, действително използвани за прогнозни цели. За повече информация относно изчисляването на df вижте пример 4 по-долу, когато df се промени поради премахването на излишни колони, стойностите на sey и F също се променят. Не се препоръчва честото използване на колинеарност. Трябва обаче да се използва, ако някои X колони съдържат 0 или 1 като индикатор, показващ дали обектът на експеримента е включен в отделна група. Ако const = TRUE или стойност за този аргумент не е посочена, LINEST вмъква допълнителна X колона, за да моделира пресечната точка. Ако има колона със стойности 1 за мъже и 0 за жени и има колона със стойности 1 за жени и 0 за мъже, тогава последната колона се премахва, тъй като нейните стойности могат да бъдат получени от графата "мъжки индикатор".

Изчисляването на df за случаите, когато X колони не са премахнати от модела поради колинеарност, се извършва по следния начин: ако има k известни_x колони и стойността const = TRUE или не е посочена, тогава df = n – k – 1. Ако const = FALSE, тогава df = n - k. И в двата случая премахването на X колоните поради колинеарност увеличава df стойността с 1.

Формулите, които връщат масиви, трябва да бъдат въведени като формули за масиви.

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

Трябва да се отбележи, че y стойностите, предвидени от регресионното уравнение, може да не са правилни, ако попадат извън обхвата на y стойностите, които са били използвани за дефиниране на уравнението.

Основен алгоритъм, използван във функцията LINEST, се различава от алгоритъма на основната функция НАКЛОНи РЯЗАНЕ. Разликата между алгоритмите може да доведе до различни резултати с несигурни и колинеарни данни. Например, ако точките с данни на аргументаknown_y_values ​​са 0 и точките с данни на аргументаknown_x_values ​​са 1, тогава:

функция LINESTвръща стойност равна на 0. Алгоритъм на функцията LINESTизползвани за връщане на подходящи стойности за колинеарни данни и в в този случайможе да се намери поне един отговор.

Функциите SLOPE и LINE връщат грешка #DIV/0! Алгоритъмът на функциите SLOPE и INTERCEPT се използва за намиране само на един отговор, но в този случай може да има няколко.

В допълнение към изчисляването на статистика за други типове регресия, LINEST може да се използва за изчисляване на диапазони за други типове регресия чрез въвеждане на функции на променливите x и y като серии от променливите x и y за LINEST. Например следната формула:

LINEST(y_стойности, x_стойности^COLUMN($A:$C))

работи, като има една колона от Y стойности и една колона от X стойности за изчисляване на приближение на куб (полином от 3-та степен) от следната форма:

y=m 1 x+m 2 x 2 +m 3 x 3 +b

Формулата може да бъде модифицирана за изчисляване на други видове регресия, но в някои случаи са необходими корекции на изходните стойности и други статистики.

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

Изисква добавка за работа Пакет за анализ, който трябва да бъде активиран в елемента от менюто Услуга\Добавки

В Excel 2007, за да активирате пакета за анализ, трябва да щракнете върху отидете до блокиране Опции на Excel като натиснете бутона отляво горния ъгъл, а след това бутона Опции на Excel"в долната част на прозореца:

За да изградите регресионен модел, трябва да изберете елемента Услуга\Анализ на данни\Регресия. (В Excel 2007 този режим е в блока Данни/Анализ на данни/Регресия). Ще се появи диалогов прозорец, който трябва да попълните:

1) Интервал на въвеждане Y¾ съдържа връзка към клетки, които съдържат стойностите на получената характеристика г. Стойностите трябва да са подредени в колона;

2) Интервал на въвеждане X¾ съдържа връзка към клетки, които съдържат стойности на факторите. Стойностите трябва да бъдат подредени в колони;

3) Подпишете Етикетизадайте дали първите клетки съдържат обяснителен текст (етикети с данни);

4) Ниво на надеждност¾ е нивото на сигурност, което се счита за 95% по подразбиране. Ако тази стойност не ви подхожда, тогава трябва да активирате този флаг и да въведете необходимата стойност;

5) Подпишете Константа-нуласе включва, ако е необходимо да се състави уравнение, в което свободната променлива е ;

6) Изходни опцииопределят къде да бъдат поставени резултатите. По подразбиране режим на изграждане Нов работен лист;

7) Блокирайте Остатъциви позволява да включите изхода на остатъците и изграждането на техните графики.

В резултат на това информация, съдържаща всички необходимата информацияи групирани в три блока: Регресионна статистика , Дисперсионен анализ, Теглене на баланс. Нека ги разгледаме по-отблизо.

1. Регресионна статистика:

множество Рсе определя по формулата ( Коефициент на корелация на Пиърсън);

Р (коефициент на детерминация);

Нормализирано Р-квадрат се изчислява по формулата (използван за множествена регресия);

Стандартна грешка Сизчислено по формулата ;

Наблюдения ¾ е количеството данни п.

2. Дисперсионен анализ, линия Регресия:

Параметър dfравни м(брой набори от фактори х);

Параметър ССсе определя по формулата ;

Параметър MSсе определя по формулата ;

Статистика Есе определя по формулата ;

Значение Е. Ако полученото число надвишава , тогава хипотезата се приема (няма линейна връзка), в противен случай хипотезата се приема (има линейна зависимост).

3. Дисперсионен анализ, линия остатък:

Параметър dfравно на ;

Параметър ССсе определя по формулата ;

Параметър MSсе определя по формулата.

4. Дисперсионен анализ, линия Общосъдържа сумата от първите две колони.

5. Дисперсионен анализ, линия Y-пресечкасъдържа коефициента, стандартната грешка и t- статистика.

П-стойност ¾ е стойността на нивата на значимост, съответстващи на изчисленото t-статистици. Определя се от функцията STUDIST( t-статистика; ). Ако П-стойност надвишава, тогава съответната променлива е статистически незначима и може да бъде изключена от модела.

Долни 95%и Топ 95%¾ са долната и горната граница на 95 процента доверителни интервали за коефициентите на теоретичното уравнение на линейна регресия. Ако стойността на достоверността в блока за въвеждане на данни е оставена на стойността си по подразбиране, тогава последните две колони ще дублират предишните. Ако потребителят е въвел своя собствена стойност на достоверност, последните две колони съдържат долната и горната граница на стойностите за определеното ниво на достоверност.

6. Дисперсионен анализ, редовете съдържат стойностите на коефициента, стандартни грешки, t-статистик, П-стойности и доверителни интервали за съответните .

7. Блокирайте Теглене на баланссъдържа предвидените стойности г(в нашата нотация това е ) и остатъци .

28 окт

Добър ден, скъпи читатели на блога! Днес ще говорим за нелинейни регресии. Решение линейни регресииможете да видите на ЛИНК.

Този методизползвани главно в икономическото моделиране и прогнозиране. Целта му е да наблюдава и идентифицира зависимости между два показателя.

Основните видове нелинейни регресии са:

• полином (квадратичен, кубичен);
• хиперболичен;
• улегнал;
• демонстративен;
• логаритмичен

Може също да се използва различни комбинации. Например, за анализ на времеви редове в банковото дело, застраховането и демографските изследвания се използва кривата на Gompzer, която е вид логаритмична регресия.

При прогнозирането с помощта на нелинейни регресии основното нещо е да се намери коефициентът на корелация, който ще ни покаже дали има тясна връзка между два параметъра или не. Като правило, ако коефициентът на корелация е близо до 1, тогава има връзка и прогнозата ще бъде доста точна. Още един важен елементнелинейни регресии е средната относителна грешка ( А ), ако е в интервала<8…10%, значит модель достаточно точна.

Това е мястото, където вероятно ще завършим теоретичния блок и ще преминем към практически изчисления.

Имаме таблица на продажбите на автомобили за период от 15 години (нека го обозначим с X), броят на стъпките на измерване ще бъде аргументът n, имаме и приходи за тези периоди (нека го обозначим с Y), трябва да предвидим какво приходите ще бъдат в бъдеще. Нека изградим следната таблица:

За изследването ще трябва да решим уравнението (зависимостта на Y от X): y=ax 2 +bx+c+e. Това е двойна квадратна регресия. В този случай прилагаме метода на най-малките квадрати, за да открием неизвестните аргументи - a, b, c. Това ще доведе до система от алгебрични уравнения от вида:

За да решим тази система, ще използваме, например, метода на Крамър. Виждаме, че включените в системата суми са коефициенти за неизвестните. За да ги изчислим, ще добавим няколко колони към таблицата (D,E,F,G,H) и ще се подпишем според смисъла на изчисленията - в колона D ще поставим на квадрат x, в E ще го поставим на куб, в F ще умножим показателите x и y, в H повдигаме x на квадрат и умножаваме по y.

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

Нека формираме матрица А система, състояща се от коефициенти за неизвестни от лявата страна на уравненията. Нека го поставим в клетка A22 и го наречем " А=". Следваме системата от уравнения, която избрахме за решаване на регресията.

Тоест в клетка B21 трябва да поставим сумата от колоната, където сме повдигнали индикатора X на четвърта степен - F17. Нека просто се обърнем към клетката - “=F17”. След това се нуждаем от сумата на колоната, където X е кубичен - E17, след което вървим стриктно според системата. По този начин ще трябва да попълним цялата матрица.

В съответствие с алгоритъма на Cramer ще напишем матрица A1, подобна на A, в която вместо елементите от първата колона трябва да бъдат поставени елементите от десните страни на уравненията на системата. Тоест сумата от колоната X на квадрат, умножена по Y, сумата от колоната XY и сумата от колоната Y.

Ще ни трябват още две матрици - нека ги наречем A2 и A3, в които втората и третата колона ще се състоят от коефициентите на десните страни на уравненията. Картината ще бъде такава.

Следвайки избрания алгоритъм, ще трябва да изчислим стойностите на детерминантите (детерминанти, D) на получените матрици. Нека използваме формулата MOPRED. Ще поставим резултатите в клетки J21:K24.

Ще изчислим коефициентите на уравнението според Крамер в клетките срещу съответните детерминанти по формулата: а(в клетка M22) - “=K22/K21”; b(в клетка M23) - “=K23/K21”; с(в клетка M24) - “=K24/K21”.

Получаваме желаното от нас уравнение на сдвоена квадратна регресия:

y=-0,074x 2 +2,151x+6,523

Нека оценим близостта на линейната връзка с помощта на индекса на корелация.

За да изчислите, добавете допълнителна колона J към таблицата (нека я наречем y*). Изчислението ще бъде както следва (според полученото от нас регресионно уравнение) - „=$m$22*B2*B2+$M$23*B2+$M$24.“Нека го поставим в клетка J2. Всичко, което остава, е да плъзнете маркера за автоматично попълване надолу до клетка J16.

За да изчислите сумите (средно за Y-Y) 2, добавете колони K и L към таблицата със съответните формули. Изчисляваме средната стойност за колоната Y с помощта на функцията AVERAGE.

В клетка K25 ще поставим формулата за изчисляване на корелационния индекс - “=ROOT(1-(K17/L17))”.

Виждаме, че стойността от 0,959 е много близка до 1, което означава, че има тясна нелинейна връзка между продажбите и годините.

Остава да се оцени качеството на съответствие на полученото уравнение на квадратната регресия (индекс на определяне). Изчислява се по формулата за индекса на корелация на квадрат. Тоест формулата в клетка K26 ще бъде много проста - “=K25*K25”.

Коефициентът от 0,920 е близо до 1, което показва високо качество на прилягане.

Последната стъпка е да се изчисли относителната грешка. Нека добавим колона и въведем формулата там: “=ABS((C2-J2)/C2), ABS - модул, абсолютна стойност. Начертайте маркера надолу и в клетка M18 покажете средната стойност (AVERAGE), задайте процентния формат на клетките. Полученият резултат - 7,79% е в рамките на допустимите стойности на грешка<8…10%. Значит вычисления достаточно точны.

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

Приложен е примерен файл - ЛИНК!

Категории:// от 28.10.2017г