Регресията е инструмент за анализ, който използваме за анализиране на големи количества данни и направяне на прогнози и прогнози в Microsoft Excel.
Искате ли да предскажете бъдещето? Не, няма да учим астрология. Ние се занимаваме с числа и днес ще научим регресионен анализ в Excel.
За да прогнозираме бъдещи оценки, ще проучим:
- РЕГРЕСИОНЕН АНАЛИЗ С ИЗКЛЮЧВАНЕ НА ФУНКЦИИ (РЪЧНО РЕГРЕСИОННО НАМЕРЯВАНЕ)
- РЕГРЕСИОНЕН АНАЛИЗ С ИЗПОЛЗВАНЕ НА ИНСТРУМЕНТА ЗА АНАЛИЗ НА EXCEL
- РЕГРЕССИОННА ГРАФИКА В ОТЛИЧНО
Хайде да го направим…
Сценарий:
Да предположим, че продавате безалкохолни напитки. Колко готино ще бъде, ако можете да предвидите:
- Колко безалкохолни напитки ще бъдат продадени през следващата година въз основа на данните от предходната година?
- Кои полета трябва да бъдат фокусирани?
- И как можете да увеличите продажбите си, като промените стратегията си?
Ще бъде изгодно страхотно. Нали?… Знам. Така че нека започнем.
Имате 11 записа за продадени и безалкохолни напитки.
Сега въз основа на тези данни искате да предвидите броя на продавачите, необходими за постигане на 2000 продажби на безалкохолни напитки.
Уравнението на регресията е инструмент за извършване на такива близки оценки. За да направим това, първо трябва да знаем регресията.
РЕГРЕСИОНЕН АНАЛИЗ С ИЗКЛЮЧВАНЕ НА ФУНКЦИИ (РЪЧНО РЕГРЕСИОННО НАМЕРЯВАНЕ)
Тази част ще ви накара да разберете по -добре регресията, отколкото просто да разкажете процедурата за регресия на Excel.
Въведение:
Проста линейна регресия:
Изследването на връзката между две променливи се нарича проста линейна регресия. Където една променлива зависи от другата независима променлива. Зависимата променлива често се извиква с имена като променлива Driven, Response и Target. А независимата променлива често се произнася като променлива за шофиране, предсказване или просто независима. Тези имена ясно ги описват.
Сега нека сравним това с вашия сценарий. Искате да знаете броя на продавачите, необходим за постигане 2000 продажби. Така че тук зависимата променлива е броят на продавачите, а независимата променлива се продава безалкохолни напитки.
Независимата променлива се обозначава най -вече като х и зависима променлива като y.
В нашия случай се продават безалкохолни напитки х а броят на продавачите е y.
Ако искаме да знаем колко безалкохолни напитки ще бъдат продадени, ако назначим 200 продавачи, тогава сценарият ще бъде обратно.
Преместване на.
„Простата“ математика на уравнението на линейна регресия:
Е, не е просто. Но Excel направи това лесно.
Трябва да предвидим необходимия брой продавачи за всички 11 случая, за да получим 12 -то най -близко прогнозиране.
Да речем:
Безалкохолна напитка Продадено е х
Броя на търговците е y
Прогнозираното y (брой продавачи) също се обажда Регресионно уравнение, би било
| x*Наклон+прихващане (отпуснете се, покрих го) |
Сега сигурно се чудите къде статистика ще получите ли наклона и ще прихванете. Не се притеснявайте, Excel има функции за тях. Не е нужно да се научите как да намерите наклона и да го прихванете ръчно.
Ако искате, ще подготвя отделен урок за това. Кажете ми в секцията за коментари. Това са някои важни инструменти за анализ на данни.
Сега нека пристъпим към нашето изчисление:
Етап 1: Подгответе тази малка маса
Стъпка 2: Намерете наклона на регресионната линия
Функцията Excel за наклони е
| = SLOPE (известни_y, известни_x) |
Вашите известни_y са в обхвата В2: В12 и known_x са в обхвата C2: C12
В клетката B16, напишете формулата по -долу
| = НАКЛОН (B2: B12, C2: C12) |
(Забележка: Наклонът се нарича също коефициент на x в уравнението на регресията)
Ще получите 0.058409. Закръглете до 2 десетични цифри и ще получите 0.06.
Стъпка 3: Намерете пресечната точка на регресионната линия
Функцията Excel за прихващане е
| =INTERCEPT (известни_y, известни_x) |
Ние знаем какво е нашето известни x и y
В клетката В17, запишете тази формула
| = ПРЕХВЪРЛЯНЕ (В2: В12, C2: C12) |
Ще получите стойност -1.1118969. Закръглете до 2 десетични цифри. Ще получите -1.11.
Нашето уравнение за линейна регресия е = x*0,06 + (-1,11). Сега можем лесно да предвидим възможно y в зависимост от целта x.
Стъпка 4: В D2 напишете формулата по -долу
| =C2*$ B $ 16+$ B $ 17(Регресионно уравнение) |
Ще получите стойност от 13.55.
Изберете D2 до D13 и натиснете CTRL+D за попълване на формулата в диапазона D2: D13
В клетката D13 имате необходимия брой продавачи.
| Следователно, за да се постигне целта на 2000 Продажби на безалкохолни напитки, имате нужда от приблизителна оценка от 115,71 продавачи или да речем 116, тъй като е незаконно да режете хората на парчета. |
Използвайки това, можете лесно да провеждате What-If анализ в Excel. Просто променете броя на продажбите и това ще ви покаже, че много продавачи ще са необходими, за да се постигне тази цел за продажби.
Поиграйте, за да разберете:
Колко работна сила ви е необходима, за да увеличите продажбите?
Колко продажби ще се увеличат, ако увеличите продавачите си?
Направете вашата оценка по -надеждна:
Сега знаете, че имате нужда от 116 продавачи, за да извършите 2000 продажби.
В аналитиката нищо не се казва и вярва. Трябва да дадете процент на надеждност на вашата оценка. Това е все едно да дадете сертификат за вашето уравнение.
Формула за коефициент на корелация:
Следващото нещо, което ще бъдете попитани, е доколко тези две променливи са свързани. В статично изражение трябва да кажете коефициента на корелация.
Функцията на Excel за корелация е
| = CORREL (масив1, масив2) |
Във вашия случай, known_x's и Know_y’s са array1 и array2 независимо.
В B18 въведете тази формула
| = CORREL ((В2: В12, C2: C12) |
Ще имате 0.919090. Форматирайте клетка В2 в процент. Сега имайте 92% на корелация.
Сега какво е това 92% означава. Това означава, че там 92% на шансовете за увеличаване на продажбите, ако увеличите броя на продавачите и 92% продажбите намаляват, ако намалите броя на продавачите. Нарича се Положителен коефициент на корелация.
R Squire (R^2):
R Стойността на Squire ви казва, с какъв процент вашето уравнение на регресията не е случайно. Колко е точен според предоставените данни.
Функцията Excel за R squire е RSQ.
| RSQ (известен_y, Known_x's) |
В нашия случай ще получим R squire стойност в клетка B19.
В B19 въведете тази формула
| = RSQ (В2: В12, C2: C12) |
Така че имаме 84% от квадратната стойност на r. Което е много добро обяснение на нашата регресия. Той казва, че 84% от нашите данни просто не са случайни. Y (брой продавачи) е много зависим от X (продажби на безалкохолни напитки).
Има много други тестове, които можем да направим върху тези данни, за да гарантираме нашата регресия. Но ръчно това ще бъде сложна и продължителна процедура. Ето защо Excel предоставя пакет с инструменти за анализ. С помощта на този инструмент можем да направим този регресионен анализ за секунди.
РЕГРЕСИЯ В ОТЛИЧНО ИЗПОЛЗВАНЕ НА ДОБАВКАТА НА ИНСТРУМЕНТА НА ИНСТРУМЕНТА НА EXCEL
Ако вече знаете какво представляват уравненията на регресията и просто искате вашите резултати бързо, тогава тази част е за вас. Но ако искате да разберете лесно уравненията на регресията, превъртете нагоре до РЕГРЕСИОНЕН АНАЛИЗ ИЗ ИЗКЛЮЧВАНЕ НА ФУНКЦИИ ВЪВ ВРЪЗКА (РЪЧНО РЕГРЕСИРАНЕ).
Excel предоставя цял набор от инструменти за анализ в своя пакет с инструменти за анализ. По подразбиране той не е наличен в раздела Данни. Трябва да го добавите. Така че нека го добавим първо.
Добавяне на пакет с инструменти за анализ към Excel 2016
Ако не знаете къде е анализът на данните в Excel, следвайте тези стъпки
Стъпка 1: Отидете на Опции на Excel: Файл? Настроики? Добавки
Стъпка 2: Щракнете върху Добавки. Ще видите списък с наличните добавки.
Изберете Анализ ToolPak и в долната част на прозореца намерете управление. В управлението изберете добавки на Excel и щракнете върху ОТЧЕТИ.
Ще се отвори прозорец с добавки. Тук изберете Analysis ToolPak. След това щракнете върху бутона OK.
Сега можете да получите достъп до всички функции на ToolPak за анализ на данни от раздел Data.
Използване на Analysis ToolPak за регресия
Стъпка 1: Отидете в раздела Данни, Намерете Анализ на данни. След това кликнете върху него.
Ще се появи диалогов прозорец.
Стъпка 2: Намерете „Регресия“ в списъка с инструменти за анализ и натиснете бутона OK.
Регресията ще се появи прозорец за въвеждане. Ще видите редица налични опции за въвеждане. Но засега просто ще се концентрираме върху Y Range и X Range, оставяйки всичко останало по подразбиране.
Стъпка 4: Осигурете входове:
Брой на търговците е Y
Продажбите на безалкохолни напитки са х
Следователно
- Y Обхват = B2: B11
И
- X Обхват = C2: C11
За изходния диапазон съм избрал E4 на същия лист. Можете да изберете нов работен лист, за да получите резултати на нов работен лист в същата работна книга или пълна нова работна книга. Когато приключите с въведените данни, натиснете бутона OK.
Резултати:
Ще ви бъде предоставена разнообразна информация от вашите данни. Не се претоварвайте. Не е нужно да консумирате всички ястия.
Ще се занимаваме само с тези резултати, които ще ни помогнат да преценим необходимия брой продавачи
Стъпка 5: Знаем регресионното уравнение за оценка на y, това е
x*Наклон+прихващане
Просто трябва да намерим Наклон и Прихващане в резултатите.
И ето ги.
Коефициентът на прихващане е ясно споменат.
Наклонът е написан като „X променлива 1’, Понякога споменати и като коефициент на X. Закръглете ги и ще получим -1.11 като прихващане и 0,06 като наклон.
Стъпка 6: От резултатите можем да управляваме уравнението на регресията. И това би било
= x*(0,06) + (-1,11)
Подгответе тази таблица в Excel.
За сега, х е 2000, което е в клетка Е2.
В клетка F2 въведете тази формула
= E2*F21+F20
Ще получите резултат от 115.7052757.
Закръгляването ще ни даде 116 от необходимите търговци.
Така че научихме как да оформяме уравнението на регресията ръчно и с помощта на Analysis ToolPak. Как можете да използвате това уравнение за оценка на бъдещата статистика?
Сега нека разберем резултата от регресията, даден от Analysis Toolpak.
Разбиране на изхода за регресия:
Няма полза, ако правите регресионен анализ с помощта на пакет от инструменти за анализ в Excel и не можете да интерпретирате значението му.
Обобщен раздел:
Както подсказва името, това е обобщение на данните.
-
- Множествен R: Той показва доколко регресионното уравнение е подходящо за данните. Нарича се още коефициент на корелация.
В нашия случай е така 0.919090619 или 0.92 (закръглям). Това означава, че има 92% шанс за увеличение на продажбите, ако увеличим броя на нашите търговци.
-
- R Square: Той показва надеждността на намерената регресия. Той ни казва колко наблюдения са част от нашата линия на регресия. В нашия случай е 0,844727566 или 0,85. Това означава, че нашата регресия е подходяща с 85%.
- Коригиран квадрат R: Коригираният квадрат е само по -доказана версия на R квадрат. Основно полезен при множествен регресионен анализ.
- Стандартна грешка: Докато R. Squire ви казва колко точки от данни попадат в близост до регресионната линия, стандартната грешка ви казва докъде може да стигне точка от данните от регресионната линия.
В нашия случай е така 6.74.
- Наблюдение: Това е просто броят на наблюденията, който е 11 в нашия пример.
Секция Анова:
Този раздел почти не се използва при линейна регресия.
- df. Това е степен на свобода. Използва се при ръчно изчисляване на регресията.
- SS. Сума от квадрати. Това е просто сума от квадрати на отклонения. Използва се за намиране на стойности на R squire.
- ГОСПОЖИЦА. Това означава квадратна стойност.
- И 5. F и значимост на F. Ако значимостта на F (p-стойността на наклона) е по-малка от F теста, можете да отхвърлите нулевата хипотеза и да докажете своята хипотеза. На прост език можете да заключите, че има някакъв ефект на x върху y при промяна.
В нашия случай F е 48.96264, а значимостта на F е 0.000063. Това означава, че нашата регресия отговаря на данните.
Раздел за регресия:
В този раздел имаме двете най -важни стойности за нашето уравнение на регресията.
- Прихващане: Тук имаме прихващане, което разказва къде х-прихваща на Y. Това е важна част от уравнението на регресията. В нашия случай е -1.11.
- X променлива 1 (Наклон). Нарича се още коефициент на х. Той определя тангента на регресионната линия.
РЕГРЕССИОННА ГРАФИКА В ОТЛИЧНО
В excel е лесно да се начертае регресионна диаграма. Просто следвайте тези стъпки. За да добавите регресионна диаграма в Excel 2016, 2013 и 2010, следвайте тези прости стъпки.
Етап 1. Поставете вашите познати x в първата колона и знайте y във втората.
В нашия случай знаем, че Known_x’s са безалкохолни напитки, продадени. А известните_у са търговци.
Стъпка 2. Изберете вашите известни диапазони x и y.
Стъпка 3: Отидете на раздела Вмъкване и кликнете върху разсейващата диаграма.
Ще имате диаграма, която изглежда така.
Стъпка 4. Добавете линията на тренда: Отидете на оформлението и намерете опцията линия на тренда в секцията за анализ.
Под опцията Trendline, щракнете върху Linear Trendline.
Графиката ви ще изглежда така.
Това е вашата графика на регресия.
Сега, ако добавите данните по -долу и разширите избраните данни. Ще видите промяна в графиката си.
За нашия пример добавихме 2000 към безалкохолните напитки, продадени и оставихме търговците празни. И когато разширим обхвата на графиката, това ще имаме.
Той ще даде необходимия брой продавачи за извършване на 2000 продажби на безалкохолни напитки в графичен вид. Което е малко под 120 в графиката. И от нашето уравнение на регресията знаем, че е 116.
В тази статия се опитах да обхвана всичко в регресионния анализ на Excel. Обясних регресията в excel 2016. Регресията в excel 2010 и excel 2013 е същата като в excel 2016.
За всякакви допълнителни запитвания по тази тема използвайте секцията за коментари. Задайте въпрос, дайте мнение или просто споменете моите граматически грешки. Всичко е добре дошло. Просто не се колебайте да използвате секцията за коментари.
Как да се изчисли функцията РЕЖИМ в Excel
Как да изчислим средната функция в Excel
Как да създадете графика на стандартното отклонение
Описателна статистика в Microsoft Excel 2016
Как да използвате функцията Excel NORMDIST
Как да използвате диаграма и анализ на Парето
Популярни статии:
50 Преки пътища в Excel за повишаване на вашата производителност
Как да използвате функцията VLOOKUP в Excel
Как да използвате функцията COUNTIF в Excel 2016
Как да използвате функцията SUMIF в Excel