Създаване на линеен регресионен модел в Excel

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

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

Ключови заведения

• Линейната регресия моделира връзката между зависима и независима променлива (и).
• Регресионен анализ може да бъде постигнат, ако променливите са независими, няма хетероседастичност и условията за грешка на променливите не са свързани.
• Моделирането на линейна регресия в Excel е по-лесно с инструмента за анализ на данни.

Важни съображения

Има няколко критични предположения относно вашия набор от данни, които трябва да са верни, за да продължите с регресионния анализ:

1. Променливите трябва да са наистина независими (използвайки тест на Chi-квадрат).
2. Данните не трябва да имат различни отклонения от грешки (това се нарича хетерокедастичност (също изписана хетероседастичност)).
3. Условията за грешка на всяка променлива трябва да бъдат некорелирани. Ако не, това означава, че променливите са серийно свързани.

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

Извеждане на регресия в Excel

Първата стъпка при извършване на регресионен анализ в Excel е да проверите отново дали е инсталиран безплатният Excel плъгин за анализ на данни ToolPak. Този плъгин прави изчисляването на набор от статистически данни много лесно. Не е необходимо да се очертава линейна линейна регресия, но прави създаването на статистически таблици по-лесно. За да проверите дали е инсталиран, изберете "Данни" от лентата с инструменти. Ако „Анализ на данни“ е опция, функцията е инсталирана и готова за използване. Ако не е инсталиран, можете да заявите тази опция, като кликнете върху бутона Office и изберете "Опции в Excel".

Използвайки инструмента за анализ на данни, създаването на регресионен изход е само с няколко кликвания.

Независимата променлива отива в диапазона X.

Имайки предвид възвръщаемостта на S&P 500, кажете, че искаме да знаем дали можем да преценим силата и връзката на възвръщаемостта на запасите от Visa (V). Данните за връщане на запаси Visa (V) попълват колона 1 като зависима променлива. S&P 500 връща данни запълва колона 2 като независима променлива.

1. Изберете „Данни“ от лентата с инструменти. Показва се менюто "Данни".
2. Изберете „Анализ на данните“. Показва се диалоговият прозорец Анализ на данни - инструменти за анализ.
3. От менюто изберете "Регресия" и натиснете "ОК".
4. В диалоговия прозорец Регресия щракнете върху полето "Въвеждане Y Диапазон" и изберете зависимите променливи данни (Visa (V) възвръщаемост на запасите).
5. Щракнете върху полето "Въвеждане на X обхват" и изберете независимите променливи данни (S&P 500 връща).
6. Кликнете върху „OK“, за да стартирате резултатите.

[Забележка: Ако таблицата изглежда малка, щракнете с десния бутон върху изображението и отворете в нов раздел за по-висока разделителна способност.]

Интерпретирайте резултатите

Използвайки тези данни (същите от нашата статия с R-квадрат), получаваме следната таблица:

Стойността R2, известна също като коефициент на определяне, измерва пропорцията на отклонение в зависимата променлива, обяснена от независимата променлива или доколко регресионният модел пасва на данните. Стойността R2 варира от 0 до 1, а по-високата стойност показва по-добро прилягане. P-стойността или вероятностната стойност също варира от 0 до 1 и показва дали тестът е значителен. За разлика от R2 стойността, по-малка р-стойност е благоприятна, тъй като показва корелация между зависимите и независимите променливи.

Графиране на регресия в Excel

Можем да начертаем регресия в Excel, като откроим данните и я представим като диаграма на разсейване. За да добавите регресионна линия, изберете "Оформление" от менюто "Инструменти на диаграмата". В диалоговия прозорец изберете "Trendline" и след това "Linear Trendline". За да добавите стойността R2, изберете „Още опции за тенденцията“ от менюто „Trendline. Накрая, изберете„ Показване на R-квадратна стойност на диаграма “. Визуалният резултат обобщава силата на връзката, макар и за сметка на това, че не предоставите толкова подробности, колкото таблицата по-горе.