С помощта на Excel изчислете регресионните коефициенти на нелинейна функция. Регресия и ексел
Пакетът MS Excel ви позволява да свършите по-голямата част от работата много бързо, когато съставяте уравнение на линейна регресия. Важно е да разберете как да интерпретирате получените резултати.
Изисква добавка за работа Пакет за анализ, който трябва да бъде активиран в елемента от менюто Услуга\Добавки
В Excel 2007, за да активирате пакета за анализ, трябва да щракнете върху отидете до блокиране Опции на Excelкато щракнете върху бутона в горния ляв ъгъл и след това върху „ Опции на Excel"в долната част на прозореца:
За да изградите регресионен модел, трябва да изберете елемента Услуга\Анализ на данни\Регресия. (В Excel 2007 този режим е в блока Данни/Анализ на данни/Регресия). Ще се появи диалогов прозорец, който трябва да попълните:
1) Интервал на въвеждане Y¾ съдържа връзка към клетки, които съдържат стойностите на получената характеристика г. Стойностите трябва да са подредени в колона;
2) Интервал на въвеждане X¾ съдържа връзка към клетки, които съдържат стойности на факторите. Стойностите трябва да бъдат подредени в колони;
3) Подпишете Етикетизадайте дали първите клетки съдържат обяснителен текст (етикети с данни);
4) Ниво на надеждност¾ е нивото на сигурност, което се счита за 95% по подразбиране. Ако не сте доволни от тази стойност, тогава трябва да активирате този флаг и да въведете необходимата стойност;
5) Подпишете Константа-нуласе включва, ако е необходимо да се състави уравнение, в което свободната променлива е ;
6) Изходни опцииопределят къде да бъдат поставени резултатите. По подразбиране режим на изграждане Нов работен лист;
7) Блокирайте Остатъциви позволява да включите изхода на остатъците и изграждането на техните графики.
В резултат на това информация, съдържаща всички необходимата информацияи групирани в три блока: Регресионна статистика, Дисперсионен анализ, Теглене на баланс. Нека ги разгледаме по-отблизо.
1. Регресионна статистика:
многократни Рсе определя по формулата ( Коефициент на корелация на Пиърсън);
Р 
(коефициент на детерминация);
Нормализирано Р-квадрат се изчислява по формулата 
(използва се за множествена регресия);
Стандартна грешка Сизчислено по формулата 
;
Наблюдения ¾ е количеството данни н.
2. Дисперсионен анализ, линия Регресия:
Параметър dfравно на м(брой набори от фактори х);
Параметър ССсе определя по формулата ;
Параметър Г-ЦАсе определя по формулата ;
Статистика Есе определя по формулата ;
Значение Е. Ако полученото число надвишава , тогава хипотезата се приема (няма линейна връзка), в противен случай хипотезата се приема (има линейна зависимост).
3. Дисперсионен анализ, линия остатък:
Параметър dfравна на ;
Параметър ССсе определя по формулата 
;
Параметър Г-ЦАсе определя по формулата.
4. Дисперсионен анализ, линия Обща сумасъдържа сумата от първите две колони.
5. Дисперсионен анализ, линия Y-пресечкасъдържа коефициента, стандартната грешка и T-статистика.
П-стойност ¾ е стойността на нивата на значимост, съответстващи на изчисленото T-статистици. Определя се от функцията STUDIST( T-статистика; ). Ако П-стойност надвишава, тогава съответната променлива е статистически незначима и може да бъде изключена от модела.
Долни 95%И Топ 95%¾ са долната и горната граница на 95 процента доверителни интервали за коефициентите на теоретичното уравнение на линейна регресия. Ако стойността на вероятността за доверие в блока за въвеждане на данни е оставена на стойността си по подразбиране, тогава последните две колони ще дублират предишните. Ако потребителят е въвел стойност на достоверност, последните две колони съдържат стойностите на долната и горната граница за определеното ниво на достоверност.
6. Дисперсионен анализ, редовете съдържат стойностите на коефициентите, стандартните грешки, T-статистик, П-стойности и доверителни интервали за съответните .
7. Блокирайте Теглене на баланссъдържа предвидените стойности г(в нашата нотация това е ) и остатъци .
Регресионен анализ в Microsoft Excel– повечето пълни ръководстваотносно използването на MS Excel за решаване на проблеми регресионен анализв бизнес анализите. Конрад Карлберг ясно обяснява теоретичните въпроси, познаването на които ще ви помогне да избегнете много грешки както при провеждане на регресионен анализ, така и при оценка на резултатите от анализ, извършен от други хора. Всички материали, от прости корелации и t-тестове до множествен анализ на ковариацията, се основават на реални примерии е придружен Подробно описаниесъответните процедури стъпка по стъпка.
Книгата обсъжда характеристиките и противоречията, свързани с Функции на Excelза работа с регресия, обсъжда последствията от използването на всяка опция и всеки аргумент и обяснява как да използвате надеждно регресионни методив области, вариращи от медицински изследвания до финансов анализ.
Конрад Карлберг. Регресионен анализ в Microsoft Excel. – М.: Диалектика, 2017. – 400 с.
Изтеглете бележката в или формат, примери във формат
Глава 1: Оценка на променливостта на данните
Статистиците имат много мерки за вариация на свое разположение. Една от тях е сумата от квадратните отклонения на отделните стойности от средната. В Excel за това се използва функцията SQUARE(). Но по-често се използва дисперсия. Дисперсията е средната стойност на квадратните отклонения. Дисперсията е нечувствителна към броя на стойностите в изследвания набор от данни (докато сумата от квадратните отклонения нараства с броя на измерванията).
Excel предлага две функции, които връщат вариация: DISP.G() и DISP.V():
- Използвайте функцията DISP.G(), ако стойностите, които трябва да бъдат обработени, формират популация. Тоест стойностите, съдържащи се в диапазона, са единствените стойности, които ви интересуват.
- Използвайте функцията DISP.B(), ако стойностите за обработка формират извадка от по-голяма популация. Предполага се, че има допълнителни стойности, чиято дисперсия също можете да оцените.
Ако величина като средна стойност или коефициент на корелация се изчислява от популация, тя се нарича параметър. Подобно количество, изчислено на базата на извадка, се нарича статистика. Отчитане на отклоненията от средното V този комплект, ще получите сума от отклоненията на квадрат, която е по-малка, отколкото ако ги броите от която и да е друга стойност. Подобно твърдение е вярно за дисперсията.
Колкото по-голям е размерът на извадката, толкова по-точна е изчислената статистическа стойност. Но няма размер на извадката, по-малък от размера на популацията, за който можете да сте сигурни, че статистическата стойност съответства на стойността на параметъра.
Да кажем, че имате набор от 100 височини, чиято средна стойност се различава от средната за популацията, без значение колко малка е разликата. Чрез изчисляване на дисперсията за извадка ще получите стойност, да речем 4. Тази стойност е по-малка от всяка друга стойност, която може да бъде получена чрез изчисляване на отклонението на всяка от 100 стойности на височина спрямо всяка стойност, различна от средната за извадката , включително спрямо истинската средна обща съвкупност. Следователно изчислената дисперсия ще бъде различна и по-малка от дисперсията, която бихте получили, ако по някакъв начин откриете и използвате параметър на популацията, а не средна стойност на извадката.
Средната сума на квадратите, определена за извадката, дава по-ниска оценкавариации на населението. Изчислената по този начин дисперсия се нарича разместенОценяване. Оказва се, че за да се елиминира отклонението и да се получи безпристрастна оценка, е достатъчно да се раздели сумата на квадратите на отклоненията не на н, Където н- размер на извадката, и n – 1.
величина n – 1се нарича брой (брой) степени на свобода. Има различни начини за изчисляване на това количество, въпреки че всички те включват или изваждане на някакво число от размера на извадката, или преброяване на броя на категориите, в които попадат наблюденията.
Същността на разликата между функциите DISP.G() и DISP.V() е следната:
- Във функцията VAR.G() сборът от квадрати се разделя на броя наблюдения и следователно представлява предубедена оценка на дисперсията, истинската средна стойност.
- Във функцията DISP.B() сборът от квадрати се разделя на броя наблюдения минус 1, т.е. чрез броя на степените на свобода, което дава по-точна, безпристрастна оценка на дисперсията на популацията, от която е съставена извадката.
Стандартно отклонение стандартно отклонение, SD) – е корен квадратен от дисперсията:
Квадратурата на отклоненията трансформира измервателната скала в друга метрика, която е квадрат на оригиналната: метри - в квадратни метра, долари - в квадратни долари и т.н. Стандартното отклонение е корен квадратен от дисперсията и следователно ни връща към оригиналните мерни единици. Което е по-удобно.
Често е необходимо да се изчисли стандартното отклонение, след като данните са били подложени на известна манипулация. И въпреки че в тези случаи резултатите несъмнено са стандартни отклонения, те обикновено се наричат стандартни грешки. Има няколко типа стандартни грешки, включително стандартна грешка на измерване, стандартна грешка на пропорцията и стандартна грешка на средната стойност.
Да приемем, че сте събрали данни за височината на 25 произволно избрани възрастни мъже във всеки от 50-те щата. След това изчислявате средната височина на възрастни мъже във всеки щат. Получените 50 средни стойности от своя страна могат да се считат за наблюдения. От това можете да изчислите тяхното стандартно отклонение, което е стандартна грешка на средната стойност. Ориз. 1. сравнява разпределението на 1250 необработени индивидуални стойности (данни за ръста на 25 мъже във всеки от 50-те щата) с разпределението на средните 50 щата. Формулата за оценка на стандартната грешка на средната стойност (т.е. стандартното отклонение на средните стойности, а не индивидуалните наблюдения):
където е стандартната грешка на средната стойност; с– стандартно отклонение на първоначалните наблюдения; н– брой наблюдения в извадката.
Ориз. 1. Разликата в средните стойности от държава до държава е значително по-малка от разликата в индивидуалните наблюдения.
В статистиката има съгласие относно използването на гръцки и латински буквиза означаване на статистически величини. Прието е параметрите на генералната съвкупност да се обозначават с гръцки букви, а извадковите статистики с латински букви. Следователно, когато говорим за стандартното отклонение на съвкупността, ние го записваме като σ; ако се вземе предвид стандартното отклонение на извадката, тогава използваме обозначението s. Що се отнася до символите за означаване на средни стойности, те не се съгласуват толкова добре помежду си. Средната популация се обозначава с гръцката буква μ. Символът X̅ обаче традиционно се използва за представяне на средната стойност на извадката.
z-резултатизразява позицията на наблюдение в разпределението в единици стандартно отклонение. Например z = 1,5 означава, че наблюдението е на 1,5 стандартни отклонения от средната стойност. Срок z-резултатизползвани за индивидуални оценки, т.е. за приписани измервания отделни елементипроби. Терминът, използван за обозначаване на такива статистики (като средната стойност на държавата) z-резултат:
където X̅ е средната стойност на извадката, μ е средната стойност на популацията, е стандартната грешка на средната стойност на набор от проби:
където σ е стандартната грешка на популацията (индивидуални измервания), н– размер на извадката.
Да приемем, че работите като инструктор в голф клуб. Успяхте да измерите разстоянието на изстрелите си за дълъг период от време и знаете, че средната стойност е 205 ярда, а стандартното отклонение е 36 ярда. Предлага ви се нова бухалка, като се твърди, че тя ще увеличи разстоянието ви за удар с 10 ярда. Вие молите всеки от следващите 81 патрона на клуба да направи пробна стрелба с нов клуб и да запише разстоянието на замах. Оказа се, че средното разстояние с новия клуб е 215 ярда. Каква е вероятността разлика от 10 ярда (215 – 205) да се дължи единствено на грешка в извадката? Или казано по друг начин: каква е вероятността при по-задълбочени тестове новият клуб да не демонстрира увеличение на дистанцията на удар над съществуващата дългосрочна средна стойност от 205 ярда?
Можем да проверим това, като генерираме z-резултат. Стандартна грешка на средната стойност:
След това z-резултат:
Трябва да намерим вероятността средната стойност на извадката да бъде на 2,5σ разстояние от средната стойност на съвкупността. Ако вероятността е малка, значи разликите не се дължат на случайността, а на качеството на новия клуб. Excel няма готова функция за определяне на вероятността за z-score. Можете обаче да използвате формулата =1-NORM.ST.DIST(z-score,TRUE), където функцията NORM.ST.DIST() връща площта под нормалната крива вляво от z-score (Фигура 2).
Ориз. 2. Функцията NORM.ST.DIST() връща площта под кривата вляво от z-стойността; За да увеличите изображението, щракнете върху него Кликнете с десния бутонмишката и изберете Отворете изображението в нов раздел
Вторият аргумент на функцията NORM.ST.DIST() може да приема две стойности: TRUE – функцията връща площта на площта под кривата вляво от точката, зададена от първия аргумент; FALSE – функцията връща височината на кривата в точката, зададена от първия аргумент.
Ако средната популация (μ) и стандартното отклонение (σ) не са известни, се използва t-стойността (вижте подробностите). Структурите на z-score и t-score се различават по това, че стандартното отклонение s, получено от резултатите от извадката, се използва за намиране на t-score, а не известната стойност на параметъра на популацията σ. Нормалната крива има една форма и формата на разпределението на t-стойността варира в зависимост от броя на степените на свобода df. степени на свобода) от пробата, която представлява. Броят на степените на свобода на пробата е равен на n – 1, Където н- размер на извадката (фиг. 3).
Ориз. 3. Формата на t-разпределенията, които възникват в случаите, когато параметърът σ е неизвестен, се различава от формата на нормалното разпределение
Excel има две функции за t-разпределението, наричано още разпределение на Стюдънт: STUDENT.DIST() връща площта под кривата отляво на дадена t-стойност, а STUDENT.DIST.PH() връща площта на точно.
Глава 2. Корелация
Корелацията е мярка за зависимост между елементи на набор от подредени двойки. Характеризира се корелацията Корелационни коефициенти на Пиърсън–р. Коефициентът може да приема стойности в диапазона от –1,0 до +1,0.
Където S xИ S y– стандартни отклонения на променливите хИ Y, S xy– ковариация:
В тази формула ковариацията се разделя на стандартните отклонения на променливите хИ Y, като по този начин се премахват свързаните с единиците ефекти на мащабиране от ковариацията. Excel използва функцията CORREL(). Името на тази функция не съдържа квалифициращите елементи Г и В, които се използват в имената на функции като STANDARDEV(), VARIANCE() или COVARIANCE(). Въпреки че коефициентът на корелация на извадката осигурява пристрастна оценка, причината за отклонението е различна от тази в случай на дисперсия или стандартно отклонение.
В зависимост от големината на общия коефициент на корелация (често означаван с гръцката буква ρ ), коефициент на корелация rпроизвежда пристрастна оценка, като ефектът от пристрастието нараства с намаляване на размера на извадката. Ние обаче не се опитваме да коригираме това отклонение по същия начин, както направихме например при изчисляване на стандартното отклонение, когато заместихме не броя наблюдения, а броя на степените на свобода в съответната формула. В действителност броят на наблюденията, използвани за изчисляване на ковариацията, няма ефект върху величината.
Стандартният коефициент на корелация е предназначен за използване с променливи, които са свързани една с друга чрез линейна зависимост. Наличието на нелинейност и/или грешки в данните (outliers) водят до неправилно изчисляване на корелационния коефициент. За диагностициране на проблеми с данните се препоръчва да създадете диаграми на разсейване. Това е единственият тип диаграма в Excel, който третира както хоризонталната, така и вертикалната ос като стойностни оси. Линейната диаграма дефинира една от колоните като ос на категорията, което изкривява картината на данните (фиг. 4).
Ориз. 4. Регресионните линии изглеждат еднакви, но сравнете техните уравнения едно с друго
Наблюденията, използвани за конструиране на линейната диаграма, са подредени на равно разстояние по хоризонталната ос. Етикетите за разделяне по тази ос са просто етикети, а не числови стойности.
Въпреки че корелацията често означава, че има причинно-следствена връзка, тя не може да се използва за доказване, че това е така. Статистиката не се използва, за да се демонстрира дали една теория е вярна или невярна. За да изключите конкуриращите се обяснения за резултатите от наблюденията, поставете планирани експерименти. Статистическите данни се използват за обобщаване на информацията, събрана по време на такива експерименти, и за количествено определяне на вероятността взетото решение да е неправилно предвид наличната база от доказателства.
Глава 3: Проста регресия
Ако две променливи са свързани една с друга, така че стойността на корелационния коефициент надвишава, да речем, 0,5, тогава в този случай е възможно да се предвиди (с известна точност) неизвестната стойност на едната променлива от известната стойност на другата . За да получите прогнозни ценови стойности въз основа на данните, показани на фиг. 5 може да се използва всеки от няколко възможни начини, но почти сигурно няма да използвате този, показан на фиг. 5. Все пак трябва да се запознаете с него, защото никой друг метод не ви позволява да демонстрирате връзката между корелация и прогноза толкова ясно, колкото този. На фиг. 5 в диапазон B2:C12 показва произволна извадка от десет къщи и предоставя данни за площта на всяка къща (в квадратни фута) и нейната продажна цена.
Ориз. 5. Стойностите на прогнозните продажни цени образуват права линия
Намерете средните стойности, стандартните отклонения и коефициента на корелация (диапазон A14:C18). Изчислете z-резултатите на площта (E2:E12). Например клетка E3 съдържа формулата: =(B3-$B$14)/$B$15. Изчислете z-резултатите на прогнозната цена (F2:F12). Например клетка F3 съдържа формулата: =ЕЗ*$В$18. Преобразувайте z-резултати в цени в долари (H2:H12). В клетка NZ формулата е: =F3*$C$15+$C$14.
Имайте предвид, че прогнозираната стойност винаги има тенденция да се измества към средната стойност от 0. Колкото по-близо до нула е коефициентът на корелация, толкова по-близо до нула е прогнозираният z-резултат. В нашия пример коефициентът на корелация между площта и продажната цена е 0,67, а прогнозната цена е 1,0 * 0,67, т.е. 0,67. Това съответства на превишаване на стойност над средната, равна на две трети от стандартното отклонение. Ако коефициентът на корелация беше равен на 0,5, тогава прогнозната цена ще бъде 1,0 * 0,5, т.е. 0,5. Това съответства на превишаване на стойност над средната стойност, равно на само половината от стандартното отклонение. Всеки път, когато стойността на корелационния коефициент се различава от идеалната стойност, т.е. по-голям от -1,0 и по-малък от 1,0, резултатът на прогнозираната променлива трябва да бъде по-близо до нейната средна стойност, отколкото резултатът на предикторната (независима) променлива до нейната собствена. Това явление се нарича регресия към средната стойност или просто регресия.
Excel има няколко функции за определяне на коефициентите на уравнение на регресионна линия (наречена линия на тенденция в Excel) y =kx + b. За определяне кизпълнява функция
=НАКЛОН(известни_y_стойности, известни_x_стойности)
Тук прие прогнозираната променлива и х- независима променлива. Трябва стриктно да следвате този ред на променливите. Наклонът на регресионната линия, коефициентът на корелация, стандартните отклонения на променливите и ковариацията са тясно свързани (Фигура 6). Функцията INTERMEPT() връща стойността, засечена от регресионната линия на вертикалната ос:
=LIMIT(известни_y_стойности, известни_x_стойности)
Ориз. 6. Връзката между стандартните отклонения преобразува ковариацията в корелационен коефициент и наклона на регресионната линия
Обърнете внимание, че броят на стойностите x и y, предоставени като аргументи на функциите SLOPE() и INTERCEPT(), трябва да бъде еднакъв.
Регресионният анализ използва друг важен показател– R 2 (R-квадрат) или коефициент на детерминация. Той определя какъв принос за общата променливост на данните има връзката между хИ при. В Excel има функция за него, наречена CVPIERSON(), която приема точно същите аргументи като функцията CORREL().
Две променливи с различен от нула коефициент на корелация между тях се казва, че обясняват дисперсията или имат обяснена дисперсия. Обикновено обяснената дисперсия се изразява като процент. Така Р 2 = 0,81 означава, че 81% от дисперсията (разсейването) на две променливи е обяснено. Останалите 19% се дължат на случайни колебания.
Excel има функция TREND, която улеснява изчисленията. Функция TREND():
- приема известните стойности, които предоставяте хи известни стойности при;
- изчислява наклона на регресионната линия и константата (отсечка);
- връща прогнозирани стойности при, определен чрез прилагане на регресионно уравнение към известни стойности х(фиг. 7).
Функцията TREND() е функция за масив (ако не сте срещали такива функции преди, препоръчвам).
Ориз. 7. Използването на функцията TREND() ви позволява да ускорите и опростите изчисленията в сравнение с използването на чифт функции SLOPE() и INTERCEPT().
За да въведете функцията TREND() като формула за масив в клетки G3:G12, изберете диапазона G3:G12, въведете формулата TREND (NW:S12;V3:B12), натиснете и задръжте клавишите
Функцията TREND() има още два аргумента: нови_стойности_xИ конст. Първият ви позволява да направите прогноза за бъдещето, а вторият може да принуди линията на регресия да премине през началото (стойност TRUE казва на Excel да използва изчислената константа, стойност FALSE казва на Excel да използва константа = 0 ). Excel ви позволява да начертаете регресионна линия върху графика, така че да минава през началото. Започнете с начертаване на точкова диаграма, след което щракнете с десния бутон върху един от маркерите на серията данни. Изберете в прозореца, който се отваря контекстно менюпараграф Добавете тренд линия; изберете опция Линеен; ако е необходимо, превъртете надолу панела, поставете отметка в квадратчето Настройте кръстовище; Уверете се, че свързаното с него текстово поле е зададено на 0.0.
Ако имате три променливи и искате да определите корелацията между две от тях, като елиминирате влиянието на третата, можете да използвате частична корелация. Да предположим, че се интересувате от връзката между процента жители на града, които са завършили колеж, и броя на книгите в градските библиотеки. Събрахте данни за 50 града, но... Проблемът е, че и двата параметъра може да зависят от благосъстоянието на жителите на даден град. Разбира се, много е трудно да се намерят други 50 града, характеризиращи се с абсолютно същото ниво на благосъстояние на жителите.
Чрез използване на статистически методи за контрол на влиянието на богатството както върху финансовата подкрепа на библиотеката, така и върху достъпността на колежа, можете да получите по-прецизно количествено определяне на силата на връзката между променливите, които представляват интерес, а именно броя на книгите и броя на завършилите. Такава условна корелация между две променливи, когато стойностите на други променливи са фиксирани, се нарича частична корелация. Един от начините да го изчислите е да използвате уравнението:
Където rC.B. . У- коефициент на корелация между променливите College и Books с изключено влияние (фиксирана стойност) на променливата Wealth; rC.B.- коефициент на корелация между променливите Колеж и Книги; rCW- коефициент на корелация между променливите колеж и благосъстояние; rB.W.- коефициент на корелация между променливите Книги и Благосъстояние.
От друга страна, частичната корелация може да се изчисли въз основа на анализа на остатъците, т.е. разлики между прогнозираните стойности и свързаните с тях резултати от реални наблюдения (двата метода са представени на фиг. 8).
Ориз. 8. Частична корелация като корелация на остатъците
За да опростите изчислението на матрицата на коефициента на корелация (B16:E19), използвайте пакета Анализ на Excel(меню Данни –> Анализ –> Анализ на данни). По подразбиране този пакет не е активен в Excel. За да го инсталирате, преминете през менюто Файл –> Настроики –> Добавки. В долната част на отворения прозорец НастроикиExcelнамери полето контрол, изберете ДобавкиExcel, щракнете Отивам. Поставете отметка в квадратчето до добавката Пакет за анализ. Щракнете върху A Анализ на данни, изберете опция Корелация. Посочете $B$2:$D$13 като интервал за въвеждане, поставете отметка в квадратчето Етикети в първия ред, посочете $B$16:$E$19 като изходен интервал.
Друга възможност е да се определи получастична корелация. Например, вие изследвате ефектите на височината и възрастта върху теглото. Така имате две предикторни променливи - височина и възраст, и една предикторна променлива - тегло. Искате да изключите влиянието на една предикторна променлива върху друга, но не и върху предикторната променлива:
където H – височина, W – тегло, A – възраст; Индексът на коефициента на получастична корелация използва скоби, за да посочи коя променлива се премахва и от коя променлива. IN в такъв случайнотацията W(H.A) показва, че ефектът от променливата Age се премахва от променливата Height, но не и от променливата Weight.
Може да изглежда, че обсъжданият въпрос не е от съществено значение. В крайна сметка най-важното е колко точно работи общо уравнениерегресия, докато проблемът за относителния принос на отделните променливи към общата обяснена дисперсия изглежда от второстепенно значение. Това обаче не е така. След като започнете да се чудите дали дадена променлива изобщо си струва да се използва в уравнение на множествена регресия, въпросът става важен. Може да повлияе на оценката на правилността на избора на модел за анализ.
Глава 4. Функция LINEST().
Функцията LINEST() връща 10 регресионни статистики. Функцията LINEST() е функция за масив. За да го въведете, изберете диапазон, съдържащ пет реда и две колони, въведете формулата и щракнете
LINEST(B2:B21;A2:A21;TRUE;TRUE)
Ориз. 9. Функция LINEST(): a) изберете диапазона D2:E6, b) въведете формулата, както е показано в лентата за формули, c) щракнете върху
Функцията LINEST() връща:
- коефициент на регресия (или наклон, клетка D2);
- сегмент (или константа, клетка E3);
- стандартни грешкирегресионен коефициент и константа (диапазон D3:E3);
- коефициент на детерминация R 2 за регресия (клетка D4);
- стандартна грешка на оценката (клетка E4);
- F-тест за пълна регресия (клетка D5);
- брой степени на свобода за остатъчната сума от квадрати (клетка E5);
- регресионна сума на квадратите (клетка D6);
- остатъчна сума от квадрати (клетка E6).
Нека да разгледаме всяка от тези статистики и как те си взаимодействат.
Стандартна грешкав нашия случай това е стандартното отклонение, изчислено за извадкови грешки. Тоест, това е ситуация, при която генералната съвкупност има една статистика, а извадката има друга. Разделянето на регресионния коефициент на стандартната грешка ви дава стойност от 2,092/0,818 = 2,559. С други думи, регресионен коефициент от 2,092 е две и половина стандартни грешки разстояние от нулата.
Ако регресионният коефициент равно на нула, Че най-добра оценкапредвидената променлива е нейната средна стойност. Две и половина стандартни грешки са доста големи и можете спокойно да приемете, че коефициентът на регресия за съвкупността е различен от нула.
Можете да определите вероятността за получаване коефициент на вземане на пробирегресия 2,092, ако действителната му стойност в популацията е 0,0 с помощта на функцията
STUDENT.DIST.PH (t-критерий = 2,559; брой степени на свобода = 18)
Като цяло броят на степените на свобода = n – k – 1, където n е броят на наблюденията, а k е броят на променливите предиктори.
Тази формула връща 0,00987 или закръглено до 1%. Той ни казва, че ако коефициентът на регресия за съвкупността е 0%, тогава вероятността да се получи извадка от 20 души, за която изчисленият коефициент на регресия е 2,092, е скромният 1%.
F-тестът (клетка D5 на фиг. 9) изпълнява същите функции по отношение на пълната регресия като t-теста по отношение на коефициента на проста регресия по двойки. F тестът се използва, за да се провери дали коефициентът на определяне R 2 за регресия е достатъчно голям, за да отхвърли хипотезата, че в популацията той има стойност 0,0, което показва, че няма дисперсия, обяснена от предиктора и прогнозираната променлива. Когато има само една предикторна променлива, F тестът е точно равен на t теста на квадрат.
Досега разглеждахме интервални променливи. Ако имате променливи, които могат да приемат множество стойности, представляващи прости имена, например Мъж и Жена или Влечуго, Земноводно и Риба, ги представят като цифров код. Такива променливи се наричат номинални.
R2 статистикаопределя количествено пропорцията на обяснената дисперсия.
Стандартна грешка на оценката.На фиг. Фигура 4.9 представя прогнозираните стойности на променливата Weight, получена въз основа на връзката й с променливата Height. Диапазонът E2:E21 съдържа остатъчните стойности за променливата Weight. По-точно, тези остатъци се наричат грешки - оттук и терминът стандартна грешка на оценката.
Ориз. 10. Както R 2, така и стандартната грешка на оценката изразяват точността на прогнозите, получени чрез регресия
Колкото по-малка е стандартната грешка на оценката, толкова по-точно е регресионното уравнение и толкова по-близко очаквате всяка прогноза, произведена от уравнението, да съответства на действителното наблюдение. Стандартната грешка на оценката предоставя начин за количествено определяне на тези очаквания. Теглото на 95% от хората с определена височина ще бъде в диапазона:
(височина * 2.092 – 3.591) ± 2.092 * 21.118
F-статистикае съотношението на междугруповата дисперсия към вътрешногруповата дисперсия. Това име е въведено от статистика Джордж Снедекор в чест на сър, който разработи анализ на дисперсията (ANOVA, анализ на дисперсията) в началото на 20 век.
Коефициентът на детерминация R 2 изразява частта от общата сума на квадратите, свързани с регресията. Стойността (1 – R 2) изразява съотношението на общата сума на квадратите, свързани с остатъците – грешки при прогнозиране. F-тестът може да се получи с помощта на функцията LINEST (клетка F5 на фиг. 11), като се използват суми от квадрати (диапазон G10:J11), като се използват пропорции на дисперсия (диапазон G14:J15). Формулите могат да се изучават в прикачения Excel файл.
Ориз. 11. Изчисляване на F-критерий
Когато се използват номинални променливи, се използва фиктивно кодиране (Фигура 12). За кодиране на стойности е удобно да използвате стойностите 0 и 1. Вероятността F се изчислява с помощта на функцията:
F.DIST.PH(K2;I2;I3)
Тук функцията F.DIST.PH() връща вероятността за получаване на F-критерий, който се подчинява на централното F-разпределение (фиг. 13) за два набора от данни с броя на степените на свобода, дадени в клетки I2 и I3, чиято стойност съвпада със стойността, дадена в клетка К2.
Ориз. 12. Регресионен анализ с използване на фиктивни променливи
Ориз. 13. Централно F-разпределение при λ = 0
Глава 5. Множествена регресия
Когато преминете от обикновена регресия по двойки с една предикторна променлива към множествена регресия, вие добавяте една или повече променливи за прогнозиране. Съхранявайте стойностите на променливите за прогнозиране в съседни колони, като колони A и B в случай на два предиктора или A, B и C в случай на три предиктора. Преди да въведете формула, която включва функцията LINEST(), изберете пет реда и толкова колони, колкото са предикторните променливи, плюс още една за константата. В случай на регресия с две предикторни променливи може да се използва следната структура:
LINEST(A2: A41; B2: C41;; TRUE)
По същия начин в случая на три променливи:
LINEST(A2:A61;B2:D61;;ВЯРНО)
Да приемем, че искате да проучите възможните ефекти на възрастта и диетата върху нивата на LDL - липопротеини с ниска плътност, за които се смята, че са отговорни за образуването на атеросклеротични плаки, които причиняват атеротромбоза (фиг. 14).
Ориз. 14. Множествена регресия
R 2 на множествена регресия (отразена в клетка F13) е по-голям от R 2 на всяка проста регресия (E4, H4). Множествената регресия използва множество предикторни променливи едновременно. В този случай R2 почти винаги се увеличава.
За всякакви прости линейно уравнениеПри регресия с една предикторна променлива винаги ще има перфектна корелация между прогнозираните стойности и стойностите на предикторната променлива, тъй като в такова уравнение предикторните стойности се умножават по една константа, а друга константа е добавя към всеки продукт. Този ефект не се запазва при множествена регресия.
Показване на резултатите, върнати от функцията LINEST() за множествена регресия (Фигура 15). Коефициентите на регресия се извеждат като част от резултатите, върнати от функцията LINEST(). в обратен ред на променливите(G–H–I съответства на C–B–A).
Ориз. 15. Коефициентите и техните стандартни грешки се показват в обратен редследвайки ги в работния лист
Принципите и процедурите, използвани в регресионния анализ на променлива с единичен предиктор, се адаптират лесно, за да отчитат множество променливи за предсказване. Оказва се, че голяма част от тази адаптация зависи от елиминирането на влиянието на предикторните променливи една върху друга. Последното е свързано с частични и получастични корелации (фиг. 16).
Ориз. 16. Множествената регресия може да бъде изразена чрез регресия по двойки на остатъците (вижте Excel файл за формули)
В Excel има функции, които предоставят информация за t- и F-разпределения. Функции, чиито имена включват частта DIST, като STUDENT.DIST() и F.DIST(), приемат t-тест или F-тест като аргумент и връщат вероятността за наблюдение на определена стойност. Функции, чиито имена включват частта OBR, като STUDENT.INV() и F.INR(), приемат стойност на вероятността като аргумент и връщат стойност на критерий, съответстваща на посочената вероятност.
Докато търсим критични стойности t-разпределения, които отрязват краищата на неговите опашни области, ние предаваме 5% като аргумент на една от функциите STUDENT.INV(), която връща стойността, съответстваща на тази вероятност (фиг. 17, 18).
Ориз. 17. Двустранен t-тест
Ориз. 18. Едностранен t-тест
Като установите правило за вземане на решение за едностранния алфа регион, вие увеличавате статистическата мощност на теста. Ако, когато започнете експеримент, сте уверени, че имате всички основания да очаквате положителен (или отрицателен) коефициент на регресия, тогава трябва да извършите тест с една опашка. В този случай вероятността да вземете правилното решение, като отхвърлите хипотезата за нулев регресионен коефициент в популацията, ще бъде по-висока.
Статистиците предпочитат да използват термина насочен тествместо термина тест с една опашкаи срок ненасочен тествместо термина тест с две опашки. Термините насочени и ненасочени са предпочитани, защото подчертават типа хипотеза, а не естеството на опашките на разпределението.
Подход за оценка на въздействието на предиктори, базиран на сравнение на модели.На фиг. Фигура 19 представя резултатите от регресионен анализ, който тества приноса на променливата Диета към регресионното уравнение.
Ориз. 19. Сравняване на два модела чрез тестване на разликите в техните резултати
Резултатите от функцията LINEST() (диапазон H2:K6) са свързани с това, което наричам пълен модел, който регресира променливата LDL върху променливите Diet, Age и HDL. Диапазонът H9:J13 представя изчисления, без да се взема предвид предикторната променлива Diet. Наричам това ограничен модел. В пълния модел 49,2% от дисперсията в зависимата променлива LDL се обяснява с предикторните променливи. В ограничения модел само 30,8% от LDL се обяснява с променливите възраст и HDL. Загубата в R 2 поради изключване на променливата Diet от модела е 0,183. В диапазона G15:L17 са направени изчисления, които показват, че има само вероятност от 0,0288, че ефектът от променливата Diet е случаен. В останалите 97,1% диетата има ефект върху LDL.
Глава 6: Предположения и предупреждения за регресионен анализ
Терминът „предположение“ не е дефиниран достатъчно стриктно и начинът, по който се използва, предполага, че ако предположението не е изпълнено, тогава резултатите от целия анализ са най-малкото съмнителни или може би невалидни. Това всъщност не е така, въпреки че със сигурност има случаи, при които нарушаването на предположение коренно променя картината. Основни допускания: а) остатъците на променливата Y са нормално разпределени във всяка точка X по регресионната линия; б) Y стойностите са вътре линейна зависимостот X стойности; в) дисперсията на остатъците е приблизително еднаква във всяка точка X; г) няма зависимост между остатъците.
Ако предположенията не играят съществена роля, статистиците казват, че анализът е устойчив на нарушение на предположението. По-специално, когато използвате регресия, за да тествате разликите между груповите средни стойности, предположението, че стойностите на Y - и следователно остатъците - са нормално разпределени, не играе съществена роля: тестовете са устойчиви на нарушения на предположението за нормалност. Важно е данните да се анализират с помощта на диаграми. Например, включени в добавката Анализ на данниинструмент Регресия.
Ако данните не отговарят на допусканията на линейната регресия, на ваше разположение има подходи, различни от линейната регресия. Една от тях е логистичната регресия (фиг. 20). Близо до горната и долната граница на променливата за прогнозиране, линейната регресия създава нереалистични прогнози.
Ориз. 20. Логистична регресия
На фиг. Фигура 6.8 показва резултатите от два метода за анализ на данни, насочени към изследване на връзката между годишния доход и вероятността за закупуване на жилище. Очевидно е, че вероятността да направите покупка ще се увеличи с увеличаване на доходите. Графиките улесняват забелязването на разликите между резултатите, които линейната регресия прогнозира за вероятността от закупуване на жилище, и резултатите, които може да получите, използвайки различен подход.
На езика на статистиците отхвърлянето на нулевата хипотеза, когато тя всъщност е вярна, се нарича грешка тип I.
В добавката Анализ на даннипредлагани удобен инструментда генерирам произволни числа, което позволява на потребителя да посочи желаната форма на разпределението (например Нормално, Биномиално или Поасон), както и средното и стандартното отклонение.
Разлики между функциите на семейството STUDENT.DIST().Започвайки с Версии на Excel 2010 г. са налични три различни форми на функцията, които връщат съотношението на разпределението отляво и/или отдясно на дадена стойност на t-теста. Функцията STUDENT.DIST() връща частта от площта под кривата на разпределение вляво от зададената от вас t-тест стойност. Да кажем, че имате 36 наблюдения, така че броят на степените на свобода за анализа е 34 и стойността на t-теста = 1,69. В този случай формулата
STUDENT.DIST(+1.69;34;TRUE)
връща стойността 0,05, или 5% (Фигура 21). Третият аргумент на функцията STUDENT.DIST() може да бъде TRUE или FALSE. Ако е зададено на TRUE, функцията връща кумулативната площ под кривата отляво на посочения t-тест, изразена като пропорция. Ако е FALSE, функцията връща относителната височина на кривата в точката, съответстваща на t-теста. Други версии на функцията STUDENT.DIST() - STUDENT.DIST.PH() и STUDENT.DIST.2X() - приемат само стойността на t-теста и броя на степените на свобода като аргументи и не изискват указване на трета аргумент.
Ориз. 21. По-тъмната защрихована област в лявата опашка на разпределението съответства на дела на площта под кривата вляво от голяма положителна стойност на t-тест
За да определите площта вдясно от t-теста, използвайте една от формулите:
1 — STIODENT.DIST (1, 69;34;TRUE)
STUDENT.DIST.PH(1,69;34)
Цялата площ под кривата трябва да бъде 100%, така че изваждането от 1 на частта от площта вляво от стойността на t-теста, която функцията връща, дава частта от площта вдясно от стойността на t-теста. Може да намерите за предпочитане директно да получите частта от площта, която ви интересува, като използвате функцията STUDENT.DIST.PH(), където PH означава дясната опашка на разпределението (фиг. 22).
Ориз. 22. 5% алфа област за насочен тест
Използването на функциите STUDENT.DIST() или STUDENT.DIST.PH() предполага, че сте избрали насочена работна хипотеза. Насочената работна хипотеза, съчетана със задаване на алфа стойността на 5%, означава, че поставяте всичките 5% в дясната опашка на разпределенията. Ще трябва да отхвърлите нулевата хипотеза само ако вероятността за стойността на t-теста, която получавате, е 5% или по-малко. Насочените хипотези обикновено водят до по-чувствителни статистически тестове (тази по-голяма чувствителност се нарича още по-голяма статистическа мощност).
При ненасочен тест алфа стойността остава на същото ниво от 5%, но разпределението ще бъде различно. Тъй като трябва да разрешите два резултата, вероятността за фалшив положителен резултат трябва да бъде разпределена между двете опашки на разпределението. Общоприето е тази вероятност да се разпределя равномерно (фиг. 23).
Използвайки същата получена стойност на t-теста и същия брой степени на свобода, както в предишния пример, използвайте формулата
СТУДЕНТ.РАЗ.2Х(1,69;34)
Без конкретна причина функцията STUDENT.DIST.2X() връща кода на грешка #NUM!, ако й е дадена отрицателна стойност на t-тест като първи аргумент.
Ако пробите съдържат различен номерданни, използвайте двупробния t-тест с различни вариации, включени в пакета Анализ на данни.
Глава 7: Използване на регресия за тестване на разликите между груповите средни стойности
Променливите, които преди са се появявали под името предикторни променливи, ще бъдат наричани резултатни променливи в тази глава и терминът факторни променливи ще се използва вместо термина предикторни променливи.
Най-простият подход за кодиране на номинална променлива е фиктивно кодиране(фиг. 24).
Ориз. 24. Регресионен анализ на базата на фиктивно кодиране
Когато използвате фиктивно кодиране от всякакъв вид, трябва да се спазват следните правила:
- Броят на колоните, запазени за нови данни, трябва да бъде равен на броя на факторните нива минус
- Всеки вектор представлява едно факторно ниво.
- Субектите в едно от нивата, което често е контролната група, са кодирани с 0 във всички вектори.
Формулата в клетки F2:H6 =LINEST(A2:A22,C2:D22;;TRUE) връща регресионна статистика. За сравнение, на фиг. Фигура 24 показва резултатите от традиционната ANOVA, върната от инструмента. Еднопосочна ANOVAдобавки Анализ на данни.
Кодиране на ефектите.В друг вид кодиране, наречено кодиране на ефекти,Средната стойност на всяка група се сравнява със средната стойност на груповите средни стойности. Този аспект на кодирането на ефекта се дължи на използването на -1 вместо 0 като код за групата, която получава един и същ код във всички кодови вектори (Фигура 25).
Ориз. 25. Кодиране на ефектите
Когато се използва фиктивно кодиране, постоянната стойност, върната от LINEST(), е средната стойност на групата, на която са присвоени нулеви кодове във всички вектори (обикновено референтната група). В случай на кодиране на ефекти, константата е равна на общата средна стойност (клетка J2).
Общ линеен модел - полезен начинконцептуализация на компонентите на стойността на получената променлива:
Y ij = μ + α j + ε ij
Използването на гръцки букви в тази формула вместо латински букви подчертава факта, че се отнася до популацията, от която са взети проби, но може да бъде пренаписана, за да покаже, че се отнася до проби, взети от дадена популация:
Y ij = Y̅ + a j + e ij
Идеята е, че всяко наблюдение Y ij може да се разглежда като сбор от следните три компонента: голяма средна, μ; ефект от лечението j, и j ; стойност e ij, която представлява отклонението на индивидуалния количествен показател Y ij от комбинираната стойност на общата средна стойност и ефекта j-то лечение(фиг. 26). Целта на регресионното уравнение е да минимизира сумата от квадратите на остатъците.
Ориз. 26. Наблюдения, разложени на компоненти на общ линеен модел
Факторен анализ.Ако връзката между променливата на резултата и два или повече фактора се изследва едновременно, тогава в този случай говорим за използване на факторен анализ. Добавянето на един или повече фактори към еднопосочен ANOVA може да увеличи статистическата мощност. При еднопосочен анализ на дисперсията, дисперсията в променливата на резултата, която не може да бъде приписана на фактор, се включва в остатъчния среден квадрат. Но може да се окаже, че тази вариация е свързана с друг фактор. След това тази вариация може да бъде премахната от средната квадратична грешка, намаляването на която води до увеличаване на стойностите на F-теста и следователно до увеличаване на статистическата мощност на теста. Надстройка Анализ на даннивключва инструмент, който обработва два фактора едновременно (фиг. 27).
Ориз. 27. Инструмент Двупосочен анализ на дисперсията с повторения на Пакета за анализ
Инструментът ANOVA, използван на тази фигура, е полезен, защото връща средната стойност и дисперсията на променливата на резултата, както и стойността на брояча, за всяка група, включена в дизайна. На масата Дисперсионен анализпоказва два параметъра, които не присъстват в изхода на еднофакторната версия на инструмента ANOVA. Обърнете внимание на източниците на вариация пробаИ Колонив редове 27 и 28. Източник на вариация Колонисе отнася до пола. Източник на вариация пробасе отнася до всяка променлива, чиито стойности заемат различни струни. На фиг. 27 стойности за групата KursLech1 са в редове 2-6, групата KursLech2 е в редове 7-11, а групата KursLechZ е в редове 12-16.
Основната точка е, че и двата фактора, Пол (етикет Колони в клетка E28) и Лечение (етикет Проба в клетка E27), са включени в таблицата ANOVA като източници на вариация. Средствата за мъжете са различни от средствата за жените и това създава източник на вариации. Средствата за трите лечения също се различават, осигурявайки друг източник на вариации. Има и трети източник, взаимодействие, който се отнася до комбинирания ефект на променливите пол и лечение.
Глава 8. Анализ на ковариацията
Анализът на ковариацията или ANCOVA (анализ на ковариацията) намалява отклонението и увеличава статистическата мощност. Позволете ми да ви напомня, че един от начините за оценка на надеждността на регресионно уравнение е F-тестовете:
F = MS регресия/MS остатък
където MS (среден квадрат) е средният квадрат, а индексите на регресия и остатък показват съответно регресията и остатъчните компоненти. MS Residual се изчислява по формулата:
MS остатък = SS остатък / df остатък
където SS (сума от квадрати) е сумата от квадрати, а df е броят на степените на свобода. Когато добавите ковариация към регресионно уравнение, част от общата сума на квадратите се включва не в SS ResiduaI, а в SS Regression. Това води до намаляване на SS Residual, а оттам и MS Residual. Колкото по-малък е MS Residual, толкова по-голям е F-тестът и толкова по-вероятно е да отхвърлите нулевата хипотеза за липса на разлика между средните стойности. В резултат на това вие преразпределяте променливостта на променливата на резултата. При ANOVA, когато ковариацията не се взема предвид, променливостта се превръща в грешка. Но в ANCOVA част от променливостта, приписана преди това на члена на грешката, се присвоява на ковариата и става част от SS регресия.
Помислете за пример, в който същият набор от данни се анализира първо с ANOVA и след това с ANCOVA (Фигура 28).
Ориз. 28. ANOVA анализът показва, че резултатите, получени от регресионното уравнение, са ненадеждни
Проучването сравнява относителните ефекти на физическите упражнения, които развиват мускулната сила, и когнитивните упражнения (решаване на кръстословици), които стимулират мозъчната дейност. Субектите бяха разпределени на случаен принцип в две групи, така че и двете групи да бъдат изложени на едни и същи условия в началото на експеримента. След три месеца беше измерено когнитивното представяне на субектите. Резултатите от тези измервания са показани в колона B.
Диапазонът A2:C21 съдържа изходните данни, предадени на функцията LINEST() за извършване на анализ чрез кодиране на ефекти. Резултатите от функцията LINEST() са дадени в диапазона E2:F6, където клетка E2 показва коефициента на регресия, свързан с вектора на въздействие. Клетка E8 съдържа t-тест = 0,93, а клетка E9 тества надеждността на този t-тест. Стойността, съдържаща се в клетка E9, показва, че вероятността да се срещне разликата между груповите средства, наблюдавани в този експеримент, е 36%, ако груповите средни са равни в популацията. Малцина смятат този резултат за статистически значим.
На фиг. Фигура 29 показва какво се случва, когато добавите ковариата към анализа. В този случай добавих възрастта на всеки обект към набора от данни. Коефициентът на определяне R 2 за регресионното уравнение, което използва ковариата, е 0,80 (клетка F4). Стойността на R 2 в диапазона F15:G19, в който репликирах резултатите от ANOVA, получени без ковариата, е само 0,05 (клетка F17). Следователно, регресионно уравнение, което включва ковариата, прогнозира стойности за променливата на когнитивния резултат много по-точно, отколкото използването само на вектора на въздействието. За ANCOVA вероятността случайно получаванеСтойността на F-теста, показана в клетка F5, е по-малка от 0,01%.
Ориз. 29. ANCOVA връща напълно различна картина
Регресионният анализ е един от най-популярните методи за статистическо изследване. Може да се използва за установяване на степента на влияние на независимите променливи върху зависимата променлива. Microsoft Excel има инструменти, предназначени за извършване на този тип анализ. Нека да разгледаме какво представляват те и как да ги използваме.
Свързване на пакета за анализ
Но за да използвате функцията, която ви позволява да извършвате регресионен анализ, първо трябва да активирате пакета за анализ. Само тогава инструментите, необходими за тази процедура, ще се появят на лентата на Excel.
- Преминете към раздела „Файл“.
- Отидете в секцията „Настройки“.
- Отваря се прозорец Настройки на Excel. Отидете в подраздела „Добавки“.
- В най-долната част на прозореца, който се отваря, преместете превключвателя в блока „Контрол“ в позиция „ Excel добавки„ако е в различна позиция. Кликнете върху бутона „Отиди“.
- Отваря се прозорец с наличните добавки на Excel. Поставете отметка в квадратчето до „Пакет за анализ“. Кликнете върху бутона „OK“.
Сега, когато отидем в раздела „Данни“, на лентата в блока с инструменти „Анализ“ ще видим нов бутон - „Анализ на данни“.
Видове регресионен анализ
Има няколко вида регресии:
- параболичен;
- улегнал;
- логаритмичен;
- експоненциален;
- демонстративен;
- хиперболичен;
- линейна регресия.
Относно екзекуцията последен типЗа регресионния анализ в Excel ще говорим по-подробно по-късно.
Линейна регресия в Excel
По-долу, като пример, е дадена таблица, показваща средната дневна външна температура и броя на клиентите на магазина за съответния работен ден. Нека да разберем с помощта на регресионен анализ как точно метеорологичните условия под формата на температура на въздуха могат да повлияят на посещаемостта на търговски обект.
Общото уравнение на линейната регресия е както следва: Y = a0 + a1x1 +…+ akhk. В тази формула Y означава променливата, върху която се опитваме да изследваме влиянието на факторите. В нашия случай това е броят на купувачите. Стойността на x е различните фактори, които влияят на променливата. Параметрите a са регресионните коефициенти. Тоест те са тези, които определят значимостта на даден фактор. Индексът k означава общия брой на същите тези фактори.
Анализ на резултатите от анализа
Резултатите от регресионния анализ се показват под формата на таблица на мястото, посочено в настройките.
Един от основните показатели е R-квадрат. Това показва качеството на модела. В нашия случай този коефициент е 0,705 или около 70,5%. Това е приемливо ниво на качество. Зависимост по-малка от 0,5 е лоша.
Друг важен индикатор се намира в клетката в пресечната точка на реда „Y-пресечка“ и колоната „Коефициенти“. Това показва каква стойност ще има Y, а в нашия случай това е броят на купувачите, като всички други фактори са равни на нула. В тази таблица дадена стойносте равно на 58,04.
Стойността в пресечната точка на колоните „Променлива X1“ и „Коефициенти“ показва нивото на зависимост на Y от X. В нашия случай това е нивото на зависимост на броя клиенти на магазина от температурата. Коефициент от 1,31 се счита за показател с доста високо влияние.
Както виждаме, използвайки Програми на Microsoft Excel е доста лесен за създаване на таблица за регресионен анализ. Но само обучен човек може да работи с изходните данни и да разбере същността им.
Радваме се, че успяхме да ви помогнем да разрешите проблема.
Задайте въпроса си в коментарите, като подробно опишете същността на проблема. Нашите специалисти ще се опитат да отговорят възможно най-бързо.
Тази статия помогна ли ви?
Методът на линейната регресия ни позволява да опишем права линия, която най-добре отговаря на поредица от подредени двойки (x, y). Уравнението за права линия, известно като линейно уравнение, е дадено по-долу:
ŷ - очаквана стойност на y за дадена стойност на x,
x - независима променлива,
a - сегмент на оста y за права линия,
b е наклонът на правата линия.
Фигурата по-долу илюстрира тази концепция графично:
Фигурата по-горе показва линията, описана от уравнението ŷ =2+0,5x. Y-пресечната точка е точката, в която правата пресича оста y; в нашия случай a = 2. Наклонът на линията, b, отношението на издигането на линията към дължината на линията, има стойност 0,5. Положителен наклон означава, че линията се издига отляво надясно. Ако b = 0, линията е хоризонтална, което означава, че няма връзка между зависимите и независимите променливи. С други думи, промяната на стойността на x не засяга стойността на y.
ŷ и y често се бъркат. Графиката показва 6 подредени двойки точки и права, съгласно даденото уравнение
Тази фигура показва точката, съответстваща на подредената двойка x = 2 и y = 4. Обърнете внимание, че очакваната стойност на y според линията на х= 2 е ŷ. Можем да потвърдим това със следното уравнение:
ŷ = 2 + 0,5х =2 +0,5(2) =3.
Стойността y представлява действителната точка, а стойността ŷ е очакваната стойност на y с помощта на линейно уравнение за дадена стойност на x.
Следващата стъпка е да се определи линейното уравнение, което най-добре съответства на набора от подредени двойки, говорихме за това в предишната статия, където определихме формата на уравнението, използвайки метода на най-малките квадрати.
Използване на Excel за дефиниране на линейна регресия
За да използвате вградения в Excel инструмент за регресионен анализ, трябва да активирате добавката Пакет за анализ. Можете да го намерите, като щракнете върху раздела Файл -> Опции(2007+), в диалоговия прозорец, който се появява НастроикиExcelотидете на раздела Добавки.В полето контролизбирам ДобавкиExcelи щракнете Отивам.В прозореца, който се показва, поставете отметка в квадратчето до Пакет за анализ,щракнете ДОБРЕ.
В раздела Даннив група Анализще се появи нов бутон Анализ на данни.
За да демонстрираме как работи добавката, нека използваме данни от предишна статия, където момче и момиче споделят една маса в банята. Въведете данните от нашия пример за вана в колони A и B на празния лист.
Отидете в раздела Данни,в група Анализщракнете Анализ на данни.В прозореца, който се появява Анализ на данниизберете Регресиякакто е показано на фигурата и щракнете върху OK.
Задайте необходимите параметри на регресия в прозореца Регресия, както е показано на снимката:
Кликнете ДОБРЕ.Фигурата по-долу показва получените резултати:
Тези резултати са в съответствие с тези, които получихме чрез нашите собствени изчисления в предишната статия.
Регресионният анализ е статистически метод за изследване, който ви позволява да покажете зависимостта на определен параметър от една или повече независими променливи. В предкомпютърната ера използването му е било доста трудно, особено когато става въпрос за големи обеми данни. Днес, след като научихте как да създавате регресия в Excel, можете да решавате сложни статистически проблеми само за няколко минути. По-долу са конкретни примериот областта на икономиката.
Видове регресия
Самата тази концепция е въведена в математиката от Франсис Галтън през 1886 г. Регресията се случва:
- линеен;
- параболичен;
- улегнал;
- експоненциален;
- хиперболичен;
- демонстративен;
- логаритмичен.
Пример 1
Нека разгледаме проблема за определяне на зависимостта на броя на напусналите членове на екипа от средна работна заплатав 6 промишлени предприятия.
Задача. В шест предприятия са анализирани средната месечна работна заплата и броят на доброволно напусналите служители. В табличен вид имаме:
За задачата за определяне на зависимостта на броя на напусналите работници от средната работна заплата в 6 предприятия, регресионният модел има формата на уравнението Y = a0 + a1×1 +…+аkxk, където хi са влияещите променливи, ai са регресионните коефициенти, а k е броят на факторите.
За тази задача Y е индикаторът за напусналите служители, а влияещият фактор е заплатата, която означаваме с X.
Използване на възможностите на процесора за електронни таблици Excel
Регресионният анализ в Excel трябва да бъде предшестван от прилагане на вградени функции към съществуващи таблични данни. За тези цели обаче е по-добре да използвате много полезната добавка „Analysis Pack“. За да го активирате трябва:
- от раздела „Файл“ отидете в секцията „Опции“;
- в прозореца, който се отваря, изберете реда „Добавки“;
- кликнете върху бутона „Отиди“, разположен по-долу, вдясно от реда „Управление“;
- поставете отметка в квадратчето до името „Пакет за анализ“ и потвърдете действията си, като щракнете върху „Ok“.
Ако всичко е направено правилно, необходимият бутон ще се появи от дясната страна на раздела „Данни“, разположен над работния лист на Excel.
Линейна регресия в Excel
Сега, когато имате всичко необходимо под ръка виртуални инструментиза да извършим иконометрични изчисления, можем да започнем да решаваме нашия проблем. За това:
- Кликнете върху бутона „Анализ на данни“;
- в прозореца, който се отваря, щракнете върху бутона „Регресия“;
- в раздела, който се показва, въведете диапазона от стойности за Y (броят на напусналите служители) и за X (техните заплати);
- Потвърждаваме действията си с натискане на бутона „Ok“.
В резултат на това програмата автоматично ще попълни нов лист табличен процесорданни от регресионен анализ. Забележка! Excel ви позволява ръчно да зададете местоположението, което предпочитате за тази цел. Например, това може да е същият лист, където се намират стойностите Y и X, или дори нова работна книга, специално предназначена да съхранява такива данни.
Анализ на регресионните резултати за R-квадрат
IN Данни от Excelполучени по време на обработката на данните от разглеждания пример имат формата:
На първо място, трябва да обърнете внимание на стойността на R-квадрат. Представлява коефициента на детерминация. IN в този пример R-квадрат = 0,755 (75,5%), т.е. изчислените параметри на модела обясняват зависимостта между разглежданите параметри със 75,5%. Колкото по-висока е стойността на коефициента на детерминация, избраният модел се счита за по-приложим конкретна задача. Счита се, че правилно описва реалната ситуация, когато стойността на R-квадрат е над 0,8. Ако R-квадрат е tcr, тогава хипотезата за незначимостта на свободния член на линейното уравнение се отхвърля.
В разглежданата задача за свободния член с помощта на инструменти на Excel се получи, че t = 169.20903 и p = 2.89E-12, т.е. имаме нулева вероятност правилната хипотеза за незначимостта на свободния член да бъде отхвърлена. . За коефициента за неизвестното t=5.79405 и p=0.001158. С други думи, вероятността правилната хипотеза за незначимостта на коефициента за неизвестно да бъде отхвърлена е 0,12%.
По този начин може да се твърди, че полученото уравнение на линейна регресия е адекватно.
Проблемът с осъществимостта на закупуването на пакет от акции
Множествената регресия в Excel се извършва с помощта на същия инструмент за анализ на данни. Нека разгледаме конкретен проблем с приложението.
Ръководството на компанията NNN трябва да вземе решение за целесъобразността на закупуването на 20% дял в МММ АД. Цената на пакета (SP) е 70 милиона щатски долара. Специалистите на NNN са събрали данни за подобни транзакции. Беше решено да се оцени стойността на пакета акции според такива параметри, изразени в милиони щатски долари, като:
- дължими сметки (VK);
- годишен обем на оборота (VO);
- вземания (VD);
- себестойност на дълготрайните активи (COF).
Освен това се използва параметърът на просрочените заплати на предприятието (V3 P) в хиляди щатски долари.
Решение, използващо процесор за електронни таблици Excel
На първо място, трябва да създадете таблица с изходни данни. Изглежда така:
- извикайте прозореца „Анализ на данни“;
- изберете секцията „Регресия“;
- В полето “Input interval 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.
В по-познат математическа формаможе да се запише като:
y = 0,103*x1 + 0,541*x2 – 0,031*x3 +0,405*x4 +0,691*x5 – 265,844
Данните за МММ АД са представени в таблицата:
Като ги заместим в регресионното уравнение, получаваме цифра от 64,72 милиона щатски долара. Това означава, че акциите на МММ АД не си струва да се купуват, тъй като тяхната стойност от 70 милиона щатски долара е доста завишена.
Както можете да видите, използването на електронната таблица на Excel и регресионното уравнение направи възможно вземането на информирано решение относно осъществимостта на много специфична транзакция.
Сега знаете какво е регресия. Обсъдените по-горе примери за Excel ще ви помогнат да решите практически проблемиот областта на иконометрията.
Пакетът MS Excel ви позволява да свършите по-голямата част от работата много бързо, когато съставяте уравнение на линейна регресия. Важно е да разберете как да интерпретирате получените резултати. За да изградите регресионен модел, трябва да изберете Tools\Data Analysis\Regression (в Excel 2007 този режим е в блока Data/Data Analysis/Regression). След това копирайте резултатите в блок за анализ.
Промяната в резултантната характеристика y се дължи на промяната във факторната характеристика x. Делът на дисперсията, обяснен с регресия в общата дисперсия на получената характеристика характеризира коефициента на определяне R 2. За линейна връзка коефициентът на определяне е равен на квадрата на корелационния коефициент: R 2 = r xy 2 , където r xy е корелационният коефициент.
Например стойността на R 2 = 0,83 означава, че в 83% от случаите промените в x водят до промени в y. С други думи, точността на избора на регресионното уравнение е висока.
Изчислено за оценка на качеството на напасване на регресионното уравнение. За приемливи модели се препоръчва коефициентът на определяне да бъде по-голям от 50%. Моделите с коефициент на детерминация над 80% могат да се считат за доста добри. Стойността на коефициента на детерминация R 2 = 1 означава функционална връзка между променливите.
Кога нелинейна регресиякоефициентът на детерминация се изчислява с помощта на този калкулатор. При множествената регресия коефициентът на детерминация може да бъде намерен чрез услугата Множествена регресия
Най-общо коефициентът на детерминация се намира по формулата: или
Правило за добавяне на отклонения:
,
Където - обща сумаквадратни отклонения;
- сумата на квадратните отклонения, дължащи се на регресия („обяснено“ или „факториално“);
- остатъчна сума на квадратите на отклоненията.
С помощта на този онлайн калкулатор можете да изчислите коефициент на детерминацияи се проверява неговата значимост (Примерно решение).
Инструкции. Посочете количеството входни данни. Полученият разтвор се съхранява в Word файл. Автоматично се създава и шаблон за тестване на решението в Excel.