Анализ на регресията в Excel (Съдържание)
- Регресионен анализ в Excel
- Обяснение на регресията математически
- Как да извършим линейна регресия в Excel?
- # 1 - Регресионен инструмент, използващ Analysis ToolPak в Excel
- # 2 - Регресионен анализ с помощта на Scatterplot с Trendline в Excel
Регресионен анализ в Excel
Линейна регресия е статистическа техника, която изследва линейната връзка между зависима променлива и една или повече независими променливи.
- Зависима променлива (известна като променлива реакция / резултат): променливата на вашия интерес и която искате да прогнозирате въз основа на наличната информация на независима променлива (и).
- Независима променлива (известна също като променлива обяснителна / предсказателна): е / са променлива (и), от която зависи променливата на отговора. Което означава, че това са променливите, с помощта на които променливата на отговора може да се предвиди.
Линейна връзка означава, че промяната в независима променлива (и) причинява промяна в зависимата променлива.
Съществуват и два типа линейни отношения.
- Положителна линейна връзка: Когато независимата променлива се увеличава, зависимата променлива също се увеличава.
- Отрицателна линейна връзка: Когато независимата променлива се увеличава, зависимата променлива намалява.
Това бяха някои от предварителните условия, преди да се пристъпи към регресионен анализ в excel.
Има два основни начина за извършване на линейна регресия в excel, използвайки:
- Регресионен инструмент чрез Analysis ToolPak
- Скатер диаграма с тенденция
Всъщност има още един метод, който използва ръчни формули за изчисляване на линейна регресия. Но защо трябва да го правите, когато excel прави изчисления за вас?
Следователно ще говорим само за двата метода, обсъдени по-горе.
Да предположим, че имате данни за ръст и тегло от 10 индивида. Ако начертаете тази информация чрез диаграма, нека да видим какво дава.
Както показва горният екран, линейната връзка може да бъде намерена във височина и тегло през графиката. Не се включвайте много в графиката сега, така или иначе ще я копаем дълбоко във втората част на тази статия.
Обяснение на регресията математически
Имаме математически израз за линейна регресия, както е посочено по-долу:
Y = aX + b + ε
Където,
- Y е променлива променлива или променлива за отговор.
- X е независима променлива или предиктор.
- a е наклонът на регресионната линия. Което представлява, че когато X се променя, има промяна в Y от "a" единици.
- b е прихващане. Това е стойността, която Y приема, когато стойността на X е нула.
- ε е терминът за случайна грешка. Възниква, защото прогнозираната стойност на Y никога няма да бъде абсолютно същата с действителната стойност за даден X. Този термин за грешка, няма нужда да се притесняваме. Тъй като има някои софтуери, които правят изчисляването на този термин за грешка в бекенда за вас. Excel е един от този софтуер.
В такъв случай уравнението става,
Y = aX + b
Което може да бъде представено като:
Тегло = a * Височина + b
Ще се опитаме да разберем стойностите на тези a и b, използвайки методи, които обсъдихме по-горе.
Как да извършим линейна регресия в Excel?
По-нататъшната статия обяснява основите на регресионния анализ в excel и показва няколко различни начина да направите линейна регресия в Excel.
Можете да изтеглите този шаблон на Excel за анализ на регресия тук - шаблон на Excel за анализ на регресия# 1 - Регресионен инструмент, използващ Analysis ToolPak в Excel
За нашия пример ще се опитаме да напаснем регресията за стойности на теглото (която е зависима променлива) с помощта на стойности на височина (което е независима променлива).
- В електронната таблица на excel щракнете върху Анализ на данни (присъства в групата за анализ ) под „ Данни“.
- Търсете регресия . Изберете го и натиснете ОК.
- Използвайте следните входове в прозореца Regression, който се отваря.
- Диапазон на въвеждане Y : Изберете клетките, които съдържат вашата зависима променлива (в този пример B1: B11)
- Диапазон на въвеждане X : Изберете клетките, които съдържат вашата независима променлива (в този пример A1: A11).
- Поставете отметка в квадратчето с име Етикети, ако вашите данни имат имена на колони (в този пример имаме имена на колони).
- Нивото на увереност е зададено на 95% по подразбиране, което може да бъде променено според изискванията на потребителите.
- Под Output options можете да персонализирате къде искате да видите резултата от регресионния анализ в Excel. В този случай искаме да видим изхода на същия лист. Следователно, съответно даден диапазон.
- Под опция „ Остатъци “ имате незадължителни входни данни като остатъчни, остатъчни парцели, стандартизирани остатъци, парцели за избор на линия, които можете да изберете според нуждите си. В този случай поставете отметка в остатъчната кутия, за да можем да видим дисперсията между прогнозираните и действителните стойности.
- Под опция Нормална вероятност можете да изберете Графици на нормална вероятност, които могат да ви помогнат да проверите нормалността на прогнозите. Кликнете върху OK .
- Excel ще изчисли регресионен анализ за вас за части от секунди.
До тук беше лесно и не толкова логично. Тълкуването на този изход и правенето на ценна информация от него обаче е трудна задача.
Една важна част от целия този изход е R Square / Регулиран квадрат R под таблица РЕЗЮМЕ ИЗХОД. Което предоставя информация, доколко е подходящ нашия модел. В този случай стойността на R Square е 0.9547. Което тълкува, че моделът има 95.47% точност (добро прилягане). Или на друг език, информацията за Y променлива се обяснява 95, 47% от X променлива.
Другата важна част от цялата продукция е таблица на коефициентите. Той дава стойности на коефициентите, които могат да бъдат използвани за изграждане на модела за бъдещи прогнози.
Сега нашето, регресионно уравнение за прогнозиране става:
Тегло = 0, 6746 * Височина - 38, 45508 (Стойността на наклона за височина е 0, 6746… и прихващането е -38, 45508…)
Получихте ли това, което сте определили? Вие сте дефинирали функция, в която сега просто трябва да поставите стойността на Height и ще получите стойността на Weight.
# 2 - Регресионен анализ с помощта на Scatterplot с Trendline в Excel
Сега ще видим как в excel можем да поберем уравнение на регресия върху самия разпръсквач.
- Изберете целите си две колонирани данни (включително заглавки).
- Кликнете върху Insert и изберете Scatter Plot в графа, както е показано на изображението по-долу.
- Вижте изходната графика.
- Сега трябва да имаме най-малко квадратна регресионна линия на тази графика. За да добавите този ред, щракнете с десния бутон върху която и да е от точките на данните на графиката и изберете опцията Добавяне на тенденция .
- Това ще ви позволи да имате тенденция на най-малко квадрат на регресия като по-долу.
- Под опцията Форматиране на тенденция, поставете отметка в квадратчето за показване на уравнение на диаграма.
- Тя ви позволява да видите уравнението на най-малко квадратната регресионна линия на графиката.
Това е уравнението, с което можем да прогнозираме стойностите на теглото за всеки даден набор от стойности на височина.
Неща, които трябва да запомните за регресионния анализ в Excel
- Можете да промените оформлението на тенденцията под опцията Форматиране на тенденция в диаграма на разпръснати.
- Винаги се препоръчва да погледнете остатъчните участъци, докато правите регресионен анализ, използвайки Data Analysis ToolPak в Excel. Дава ви по-добро разбиране на разпространението на действителните стойности на Y и на изчислените стойности на X.
- Простата линейна регресия в excel не се нуждае от ANOVA и коригиран квадрат R за проверка. Тези характеристики могат да бъдат взети предвид при множествена линейна регресия. Което е извън обхвата на тази статия.
Препоръчителни статии
Това е ръководство за регресионен анализ в Excel. Тук обсъждаме как да направите регресионен анализ в Excel, заедно с примери excel и шаблон за Excel, които можете да изтегляте. Можете да разгледате и другите ни предложени статии -
- Excel Инструмент за анализ на данни
- Изчислете ANOVA в Excel
- Как да намерите Excel движещи се средни стойности
- Z TEST Примери в Excel