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

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

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

Общата форма на уравнението, използвано за множество регресии, е:

Й^ = а + б₁х₁ + б₂х₂ + б₃х₃ …

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

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

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

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

Можете да извършите многовариантна регресия в Excel, като използвате вградена функция, която е достъпна чрез Анализ на данни инструмент под Данни раздела и Анализ група. Щракнете върху Анализ на данни и намерете опцията за регресия в прозореца, който се показва, маркирайте го и щракнете Добре. Кликнете върху изберете клетки икона до Входен диапазон Y поле и след това изберете колоната, съдържаща резултатите за вашата зависима променлива. След това направете същото за Входен X диапазон поле, но изберете множеството колони за вашите независими променливи. Тези колони трябва да са една до друга, така че ако не са, трябва да ги преместите, преди да създадете регресията.

Прозорецът за регресия има набор от допълнителни опции, които можете да изберете, за да приспособите процеса според вашите нужди. Например, можете да зададете ниво на доверие, различно от 95 процента, ако желаете, да изберете да покажете остатъци и да посочите къде се поставя изходът във вашата работна книга. Тази последна опция автоматично се задава на Нов работен лист Ply, така че резултатите се показват на нов лист, но можете да промените тази или всяка друга опция, за да отговарят на вашите нужди. Освен това проверете Етикети поле, ако колоните за вашите независими променливи имат етикети в горната част, така че те да се показват в изхода.

Щракнете върху Добре за да генерирате вашия регресионен анализ в Excel и да бъдете отведени в новия лист.

Има три основни раздела за изхода, който ви е представен след извършване на множествена регресия в Excel: статистика на регресията, ANOVA и подробности за прогнозната регресионна линия. Статистиката на регресията включва коефициента на множествена корелация („Multiple R“), който показва посоката и силата на корелацията от −1 до +1. Коефициентът на детерминация, "R Square", ви казва какъв процент (като десетичен знак) от вариацията в зависимата променлива се обяснява с независимите променливи. "Коригиран квадрат R" ви дава индикация за обяснителна сила, но не е лесно да се тълкува, и "Стандартна грешка" ви дава мярка за вариацията между наблюдаваните резултати и вашата регресия линия.

Разделът ANOVA съдържа статистическа информация за размера на вариацията, обяснена с регресионната линия, с "SS Regression", който ви казва сумата, обяснена с реда, и "SS Residual", представляваща сумата, която не е обясни. Разделите "MS" означават "Средноквадрат", а "F Statistic" е тестовата статистика, използвана за тестване за значим резултат, като секцията "Значение F" ви дава P-стойността.

И накрая, последният раздел ви разказва за характеристиките на изчислената регресионна линия, по-специално стойностите на коефициенти, дали са значително свързани със зависимата променлива и размера на вариация, която може да има в тях. Положителните коефициенти показват положителна връзка между въпросната променлива и зависимата променлива, така че когато едната се увеличава, другата също. Отрицателните стойности означават, че зависимата променлива намалява с увеличаване на независимата променлива. Така че, ако коефициентът "квадрат" на множествена регресия на цените на жилище е 300, това означава, че допълнителен квадратен фут пространство увеличава цената на къщата средно с $300.

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

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

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