Как да създадете модел на линейна регресия в Excel

Какво е линейна регресия?

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

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

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

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

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

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

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

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

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

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

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

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

Тълкувайте резултатите

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

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

Начертаване на регресия в Excel

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