Регресија је алат за анализу који користимо за анализу великих количина података и израду прогноза и предвиђања у програму Мицрософт Екцел.
Желите да предвидите будућност? Не, нећемо учити астрологију. Бавимо се бројевима и данас ћемо у Екцелу учити регресиону анализу.
Да бисмо предвидели будуће процене, проучићемо:
- АНАЛИЗА РЕГРЕСИЈЕ КОРИШТЕЊЕМ ЕКСЦЕЛ ФУНКЦИЈА (ПРИРУЧНИ НАЛАЗ РЕГРЕСИЈЕ)
- РЕГРЕСИЈСКА АНАЛИЗА КОРИШЋЕЊЕМ ЕКСЦЕЛ-ове АНАЛИЗНЕ АЛАТКЕ АДД-ИН
- РЕГРЕСИЈСКА ТАБЕЛА У ОДЛИЧНОМ
Учинимо то…
Сценариј:
Претпоставимо да продајете безалкохолна пића. Како би било супер ако можете предвидети:
- Колико ће безалкохолних пића бити продато следеће године на основу података из претходне године?
- Која поља треба фокусирати?
- И како можете повећати продају променом стратегије?
То ће бити исплативо сјајно. Тачно?… Знам. Па да почнемо.
Имате 11 евиденција продатих продавача и безалкохолних пића.
Сада на основу ових података желите да предвидите број продаваца који су потребни да се постигне 2000 продаја безалкохолних пића.
Регресијска једначина је алат за тако блиске процене. Да бисмо то учинили, прво морамо знати регресију.
АНАЛИЗА РЕГРЕСИЈЕ КОРИШЋЕЊЕМ ЕКСЦЕЛ ФУНКЦИЈА (ПРИРУЧНИ НАЛАЗ РЕГРЕСИЈЕ)
Овај део ће вам омогућити да боље разумете регресију него само да испричате екцел регресиони поступак.
Увод:
Једноставна линеарна регресија:
Проучавање односа између две променљиве назива се Једноставна линеарна регресија. Где једна променљива зависи од друге независне променљиве. Зависна променљива се често назива именима као што су Дривен, Респонсе и Таргет варијабла. А независна варијабла се често изговара као Дривинг, Предицтор или једноставно независна варијабла. Ова имена их јасно описују.
Хајде сада да упоредимо ово са вашим сценаријем. Желите да знате број продаваца који је потребан за постизање циља 2000 продаја. Дакле, овде је зависна променљива број продаваца, а независна варијабла се продаје безалкохолна пића.
Независна променљива се углавном означава као Икс а зависна променљива као и.
У нашем случају се продају безалкохолна пића Икс а број продаваца је и.
Ако желимо да знамо колико ће се безалкохолних пића продати ако то одредимо 200 продаваца, онда ће сценарио бити обрнут.
Идемо даље.
„Једноставна“ математика једначине линеарне регресије:
Па, није једноставно. Али Екцел је то учинио једноставним.
Морамо предвидети потребан број продаваца за свих 11 случајева да бисмо добили 12. најближе предвиђање.
Рецимо:
Безалкохолно пиће Продато је Икс
Број продавача је и
Предвиђено и (број продаваца) такође звали Регресиона једначина, би
| к*Нагиб+пресретање (опусти се, покривам све) |
Сада се сигурно питате где је стат хоћете ли добити нагиб и пресрести. Не брините, Екцел за њих има функције. Не морате да учите како пронаћи нагиб и ручно га пресрести.
Ако желите, за то ћу припремити засебно упутство. Обавестите ме у одељку за коментаре. Ово су неки важни алати за анализу података.
Идемо сада у наш прорачун:
Корак 1: Припремите овај мали сто
Корак 2: Пронађите нагиб регресионе линије
Екцел функција за падине је
| = СЛОПЕ (познати_и, познати_к) |
Ваши познати_и су у домету Б2: Б12 анд кновн_к’с аре ин ранге Ц2: Ц12
У ћелији Б16, напишите доњу формулу
| = КОСА (Б2: Б12, Ц2: Ц12) |
(Напомена: Нагиб се у регресионој једначини назива и коефицијент к)
Добићете 0.058409. Заокружите на 2 децималне цифре и добићете 0.06.
Корак 3: Пронађите пресретање регресионе линије
Екцел функција за пресретање је
| =ИНТЕРЦЕПТ (кновн_и'с, кновн_к'с) |
Знамо шта је наше познати к и и
У ћелији Б17, запишите ову формулу
| = ИНТЕРЦЕПТ (Б2: Б12, Ц2: Ц12) |
Добићете вредност -1.1118969. Заокружи на 2 децималне цифре. Добићете -1.11.
Наша једначина линеарне регресије је = к*0,06 + (-1,11). Сада можемо лако предвидети могуће и у зависности од циља к.
Корак 4: У Д2 напишите доњу формулу
| =Ц2*$ Б $ 16+$ Б $ 17(Једначина регресије) |
Добићете вредност од 13.55.
Одаберите Д2 до Д13 и притисните ЦТРЛ+Д да бисте попунили формулу у опсегу Д2: Д13
У ћелији Д13 имате потребан број продаваца.
| Дакле, за постизање циља 2000 Продаја безалкохолних пића, потребна вам је процена од 115,71 продавача или рецимо 116 пошто је незаконито сећи људе на комаде. |
Сада користећи ово, можете лако спровести Вхат-Иф анализу у екцелу. Само промените број продаја и показаће вам да ће многим продавачима бити потребно да постигну тај циљ продаје.
Играјте се око тога да бисте сазнали:
Колико радне снаге вам је потребно за повећање продаје?
Колико ће се продаја повећати ако повећате своје продаваче?
Учините своју процену поузданијом:
Сада знате да вам је потребно 116 продавача да бисте обавили 2000 продаја.
У аналитици се ништа не говори и верује. На своју процену морате навести проценат поузданости. То је као да дате потврду о својој једначини.
Формула коефицијента корелације:
Следеће што ће вас питати је колико су ове две променљиве повезане. Статички речено, морате рећи коефицијент корелације.
Екцел функција за корелацију је
| = ЦОРРЕЛ (низ1, низ2) |
У вашем случају, кновн_к -ови и Кнов_и -и су низ1 и низ2 без обзира на то.
У Б18 унесите ову формулу
| = ЦОРРЕЛ ((Б2: Б12, Ц2: Ц12) |
Имаћете 0.919090. Формирајте ћелију Б2 у процентима. Сада имајте 92% корелације.
Е сад, шта је ово 92% значи. Значи, тамо 92% шансе за повећање продаје ако повећате број продаваца и 92% смањење продаје ако смањите број продаваца. Зове се Позитиван коефицијент корелације.
Р штитоноша (Р^2):
Р Вредност штитоноше вам говори, за који проценат ваша регресиона једначина није случајност. Колико је то тачно према наведеним подацима.
Екцел функција за Р скуире је РСК.
| РСК (кновн_и'с, Кновн_к'с) |
У нашем случају, добићемо вредност Р скуире у ћелији Б19.
У Б19 унесите ову формулу
| = РСК (Б2: Б12, Ц2: Ц12) |
Дакле, имамо 84% р квадратне вредности. Што је врло добро објашњење наше регресије. Каже да 84% наших података није случајно. И (број продавача) увелико зависи од Кс (продаја безалкохолних пића).
Постоји много других тестова које можемо учинити на овим подацима како бисмо осигурали нашу регресију. Али ручно ће то бити сложен и дуготрајан поступак. Зато Екцел пружа пакет алатки за анализу. Помоћу овог алата можемо направити ову регресиону анализу за неколико секунди.
РЕГРЕСИЈА У ЕКСЦЕЛУ КОРИШЋЕЊЕМ ЕКСЦЕЛОВЕ АНАЛИЗНЕ АЛАТКЕ АДД-ИН
Ако већ знате шта су регресионе једначине и само желите брзо резултате, онда је овај део за вас. Али ако желите лако да разумете регресионе једначине, померите се горе до АНАЛИЗА РЕГРЕСИЈЕ КОРИШЋЕЊЕМ ОДЛИЧНИХ ФУНКЦИЈА (РУЧНИ РЕГРЕСНИ НАЛАЗ).
Екцел у свом пакету алата за анализу пружа читав низ алата за анализу. Подразумевано, није доступно на картици Подаци. Морате га додати. Па да то прво додамо.
Додавање пакета алата за анализу у Екцел 2016
Ако не знате где је анализа података у Екцелу, следите ове кораке
Корак 1: Идите на Екцел опције: Датотека? Опције? Додаци
Корак 2: Кликните на Додаци. Видећете листу доступних додатака.
Одаберите Аналисис ТоолПак и при дну прозора пронађите управљање. У управљању изаберите Екцел програмске додатке и кликните на ГО.
Отвориће се прозор додатака. Овде изаберите Аналисис ТоолПак. Затим кликните на дугме у реду.
Сада можете приступити свим функцијама ТоолПак -а за анализу података са картице Подаци.
Коришћење Аналисис ТоолПак -а за регресију
Корак 1: Идите на картицу Подаци, пронађите анализу података. Затим кликните на њу.
Појавиће се оквир за дијалог.
Корак 2: Пронађите „Регресија“ на листи Алати за анализу и притисните дугме У реду.
Регресија прозор за унос ће се појавити. Видећете бројне доступне опције уноса. Али за сада ћемо се концентрисати само на И распон и Кс распон, остављајући све остало подразумеваним.
Корак 4: Омогућите уносе:
Број продаваца је И
Продаја безалкохолних пића је Икс
Стога
- И Распон = Б2: Б11
И
- Кс Распон = Ц2: Ц11
За распон излаза одабрао сам Е4 на истом листу. Можете изабрати нови радни лист да бисте добили резултате на новом радном листу у истој радној свесци или комплетној новој радној свесци. Када завршите са уносом, притисните дугме ОК.
Резултати:
Биће вам достављени различити подаци из ваших података. Немојте се преоптеретити. Не морате да једете сва јела.
Бавићемо се само оним резултатима који ће нам помоћи да проценимо потребан број продаваца
5. корак: Позната је једначина регресије за процену и, то је
к*Нагиб+пресретање
Само треба да лоцирамо Слопе и Пресретање у резултатима.
И ево их.
Коефицијент пресретања је јасно споменут.
Нагиб је написан као „Кс променљива 1’, Понекад се спомиње и као коефицијент Кс. Заокружите их и добићемо -1.11 као пресретање и 0,06 као нагиб.
Корак 6: Из резултата можемо водити регресиону једначину. И то би било тако
= к*(0,06) + (-1,11)
Припремите ову табелу у екцелу.
За сад, Икс је 2000, што је у ћелији Е2.
У ћелију Ф2 унесите ову формулу
= Е2*Ф21+Ф20
Добићете резултат од 115.7052757.
Заокруживање ће нам дати 116 потребних продаваца.
Тако смо научили како ручно формирати регресиону једначину и помоћу Аналисис ТоолПак -а. Како можете користити ову једначину за процену будуће статистике?
Сада да разумемо излаз регресије који даје Аналисис Тоолпак.
Разумевање излаза регресије:
Нема користи ако радите регресијску анализу помоћу пакета алата за анализу у екцелу и не можете протумачити њено значење.
Резиме одељак:
Као што назив говори, то је сажетак података.
-
- Вишеструко Р: Говори колико одговара регресиона једначина подацима. Такође се назива коефицијент корелације.
У нашем случају јесте 0.919090619 или 0.92 (окупити). То значи да постоји 92% шансе за повећање продаје ако повећамо број наших продаваца.
-
- Р Скуаре: Он говори о поузданости пронађене регресије. Говори нам колико је запажања део наше регресионе линије. У нашем случају то је 0,844727566 или 0,85. То значи да је наша регресија погодна за 85%.
- Прилагођен квадрат Р: Прилагођени квадрат је само потврђенија верзија Р квадрата. Углавном корисно у вишеструкој регресионој анализи.
- Стандардна грешка: Док вам Р. Скуире говори колико тачака података пада близу регресионе линије, стандардна грешка вам говори колико тачка података може да иде од регресионе линије.
У нашем случају јесте 6.74.
- Запажање: Ово је једноставно број опсервација, који је 11 у нашем примеру.
Анова секција:
Овај одељак се ретко користи у линеарној регресији.
- дф. То је степен слободе. Користи се при ручном израчунавању регресије.
- СС. Збир квадрата. То је само збир квадрата варијанси. Користи се за проналажење вредности Р скуире.
- ГОСПОЂА. То значи вредност на квадрат.
- И 5. Ф и значај Ф. Ако је значај Ф (п-вредност нагиба) мањи од Ф теста, можете одбацити нулту хипотезу и доказати своју хипотезу. Једноставним језиком, можете закључити да постоји неки ефекат к на и када се промени.
У нашем случају, Ф је 48,96264, а значај Ф је 0,000063. То значи да наша регресија одговара подацима.
Одељак за регресију:
У овом одељку имамо две најважније вредности за нашу регресиону једначину.
- Пресретање: Овде имамо пресретање које говори где се к-пресреће на И. Ово је важан део регресионе једначине. У нашем случају је -1,11.
- Кс променљива 1 (Нагиб). Такође се назива коефицијент к. Он дефинише тангенту регресионе линије.
РЕГРЕСИЈСКА ТАБЕЛА У ОДЛИЧНОМ
У Екцелу је лако исцртати регресиони графикон. Само следите ове кораке. Да бисте додали регресиони графикон у Екцел 2016, 2013 и 2010, следите ове једноставне кораке.
Корак 1. Нека ваши познати к -ови буду у првој колони, а знајте и у другој.
У нашем случају знамо да су Кновн_к'с безалкохолна пића која се продају. А познати_и су продавачи.
Корак 2. Изаберите свој познати опсег к и и.
3. корак: Идите на картицу Уметање и кликните на табелу распршивања.
Имаћете графикон који изгледа овако.
Корак 4. Додајте линију тренда: Идите на распоред и пронађите опцију линије тренда у одељку за анализу.
Под опцијом Трендлине, кликните на Линеар Трендлине.
Ваш графикон ће изгледати овако.
Ово је ваш графикон регресије.
Сада ако додате податке испод и проширите одабране податке. Видећете промену на графикону.
За наш пример, додали смо 2000 у Безалкохолно пиће и продаваче оставили празно. А када проширимо опсег графикона, ово ћемо имати.
То ће дати потребан број продавача за 2000 продаја безалкохолних пића у графичком облику. Што је мало испод 120 на графикону. А из наше регресијске једначине знамо да је 116.
У овом чланку сам покушао да обухватим све у оквиру Екцел регресионе анализе. Објаснио сам регресију у екцелу 2016. Регресија у екцелу 2010 и екцелу 2013 је иста као у екцелу 2016.
За сва додатна питања о овој теми користите одељак за коментаре. Поставите питање, дајте мишљење или само спомените моје граматичке грешке. Све је добродошло. Само не оклевајте да користите одељак за коментаре.
Како израчунати функцију МОДЕ у програму Екцел
Како израчунати средњу функцију у Екцелу
Како направити графикон стандардне девијације
Описна статистика у програму Мицрософт Екцел 2016
Како се користи Екцел НОРМДИСТ функција
Како се користи Паретов графикон и анализа
Популарни чланци:
50 Екцел пречица за повећање ваше продуктивности
Како се користи функција ВЛООКУП у програму Екцел
Како се користи функција ЦОУНТИФ у програму Екцел 2016
Како се користи функција СУМИФ у програму Екцел