Карэляцыйныя аналіз у Excel. Прыклад выканання карэляцыйнага аналізу

Карэляцыйныя аналіз - распаўсюджаны метад даследавання, які выкарыстоўваецца для вызначэння ўзроўню залежнасці 1-га значэння ад 2-га. Табліца мае спецыяльны інструмент, які дазваляе рэалізаваць гэты від даследаванняў.

Сутнасць карэляцыйнага аналізу

Неабходна вызначыць сувязь паміж двума рознымі велічынямі. Іншымі словамі, ён паказвае, у які бок (меншы / большы) змяняецца значэнне ў залежнасці ад змен у другім.

Мэта карэляцыйнага аналізу

Залежнасць усталёўваецца, калі пачынаецца вызначэнне каэфіцыента карэляцыі. Гэты метад адрозніваецца ад Рэгрэсійная аналізу тым, што тут толькі адзін паказчык, які разлічваецца з дапамогай карэляцыі. Інтэрвал змяняецца ад +1 да -1. Калі ён станоўчы, то павелічэнне першага значэння спрыяе павелічэнню 2-га. Калі адмоўны, то павелічэнне 1-га значэння спрыяе памяншэнню 2-га. Чым вышэй каэфіцыент, тым мацней адно значэнне ўплывае на другое.

Важна! Пры 0-м каэфіцыенце сувязі паміж велічынямі няма.

Разлік каэфіцыента карэляцыі

Разбяром разлік на некалькіх выбарках. Напрыклад, ёсць таблічныя дадзеныя, дзе выдаткі на рэкламнае прасоўванне і аб'ём продажаў апісаны па месяцах у асобных слупках. На аснове табліцы высвятлім ступень залежнасці аб'ёму продажаў ад грашовых сродкаў, выдаткаваных на рэкламнае прасоўванне.

Спосаб 1: Вызначэнне карэляцыі з дапамогай майстра функцый

CORREL – функцыя, якая дазваляе рэалізаваць карэляцыйны аналіз. Агульная форма - КАРЭЛ(масіў1;масіў2). Падрабязная інструкцыя:

1. Неабходна выбраць вочка, у якой плануецца адлюстраваць вынік разліку. Каб увесці формулу, націсніце «Уставіць функцыю» злева ад тэкставага поля.

2. Адкрыецца майстар функцый. Тут трэба знайсці КАРЭЛ, націсніце на яго, затым на «ОК».

3. Адкрыецца акно аргументаў. У радок «Масіў1» неабходна ўвесці каардынаты інтэрвалаў першага з значэнняў. У гэтым прыкладзе гэта слупок "Кошт продажаў". Вам проста трэба вылучыць усе вочкі, якія знаходзяцца ў гэтым слупку. Аналагічным чынам вам трэба дадаць каардынаты другога слупка ў радок «Масіў1». У нашым прыкладзе гэта слупок «Выдаткі на рэкламу».

4. Пасля ўводу ўсіх дыяпазонаў націсніце кнопку «ОК».

Каэфіцыент адлюстроўваўся ў вочку, якая была пазначана ў пачатку нашых дзеянняў. Атрыманы вынік - 0,97. Гэты паказчык адлюстроўвае высокую залежнасць першага значэння ад другога.

Спосаб 2: Разлік карэляцыі з дапамогай Analysis ToolPak

Ёсць яшчэ адзін метад вызначэння карэляцыі. Тут выкарыстоўваецца адна з функцый, якія знаходзяцца ў пакеце аналізу. Перад выкарыстаннем неабходна актываваць інструмент. Падрабязная інструкцыя:

1. Перайдзіце ў раздзел «Файл».

2. Адкрыецца новае акно, у якім трэба націснуць на раздзел «Настройкі».
3. Націсніце на «Дадаткі».
4. Унізе знаходзім элемент «Упраўленне». Тут трэба выбраць у кантэкстным меню «Надбудовы Excel» і націснуць «ОК».

5. Адкрылася спецыяльнае акно дапаўненняў. Пастаўце галачку побач з элементам «Пакет аналізу». Націскаем «ОК».
6. Актывацыя прайшла паспяхова. Зараз пяройдзем да Data. З'явіўся блок «Аналіз», у якім трэба націснуць «Аналіз дадзеных».
7. У новым акне, якое з'явілася, абярыце элемент «Карэляцыя» і націсніце «ОК».

8. На экране з'явілася акно налад аналізу. У радку «Інтэрвал уводу» неабходна ўвесці дыяпазон абсалютна ўсіх слупкоў, якія прымаюць удзел у аналізе. У гэтым прыкладзе гэта слупкі «Кошт продажаў» і «Выдаткі на рэкламу». Налады адлюстравання вываду першапачаткова ўстаноўлены ў New Worksheet, што азначае, што вынікі будуць адлюстроўвацца на іншым аркушы. Пры жаданні вы можаце змяніць месца вываду выніку. Пасля выканання ўсіх налад націсніце «ОК».

Падведзены выніковыя вынікі. Вынік такі ж, як і ў першым спосабе - 0,97.

Вызначэнне і разлік множнага каэфіцыента карэляцыі ў MS Excel

Для выяўлення ступені залежнасці некалькіх велічынь выкарыстоўваюцца некалькі каэфіцыентаў. У далейшым вынікі зводзяцца ў асобную табліцу, якая называецца карэляцыйнай матрыцай.

Падрабязнае кіраўніцтва:

1. У раздзеле «Дадзеныя» знаходзім ужо вядомы блок «Аналіз» і націскаем «Аналіз дадзеных».

2. У якое з'явілася акне націсніце на элемент «Карэляцыя» і націсніце «ОК».
3. У радку «Input interval» забіваем інтэрвал для трох і больш слупкоў зыходнай табліцы. Дыяпазон можна ўвесці ўручную або проста вылучыць яго ЛКМ, і ён аўтаматычна з'явіцца ў патрэбнай радку. У «Групоўка» выберыце адпаведны метад групоўкі. У «Вывадным параметры» вызначае месца, дзе будуць адлюстроўвацца вынікі карэляцыі. Націскаем «ОК».

4. гатовы! Пабудавана карэляцыйная матрыца.

Каэфіцыент парнай карэляцыі ў Excel

Давайце разбярэмся, як правільна намаляваць каэфіцыент парнай карэляцыі ў табліцы Excel.

Разлік каэфіцыента парнай карэляцыі ў Excel

Напрыклад, у вас ёсць значэнні x і y.

X - залежная зменная, а y - незалежная. Неабходна знайсці кірунак і сілу ўзаемасувязі паміж гэтымі паказчыкамі. Пакрокавая інструкцыя:

1. Знойдзем сярэднія значэння з дапамогай функцыі Сэрца.

2. Падлічым кожны х и xavg, у и сярэдні з дапамогай аператара «-».

3. Перамнажаем вылічаныя рознасці.

4. Разлічваем суму паказчыкаў гэтай графы. Лічнік - знойдзены вынік.

5. Вылічыце назоўнікі рознасці х и х-сярэдняе, у и у-сярэдні. Для гэтага выканаем квадратуру.

6. Выкарыстанне функцыі АЎТАСУММА, знайдзіце паказчыкі ў атрыманых слупках. Робім множанне. Выкарыстанне функцыі ROOT узвядзіце вынік у квадрат.

7. Разлічваем дзель, выкарыстоўваючы значэнні назоўніка і лічніка.

8. CORREL - гэта інтэграваная функцыя, якая дазваляе прадухіліць складаныя вылічэнні. Заходзім у «Майстар функцый», выбіраем CORREL і паказваем масівы індыкатараў х и у. Будуем графік, які адлюстроўвае атрыманыя значэння.

Матрыца парных каэфіцыентаў карэляцыі ў Excel

Разбяром, як разлічыць каэфіцыенты парных матрыц. Напрыклад, ёсць матрыца чатырох зменных.

Пакрокавая інструкцыя:

1. Заходзім у раздзел «Аналіз дадзеных», размешчаны ў блоку «Аналіз» ўкладкі «Дадзеныя». Абярыце Карэляцыя са спісу, які з'явіцца.
2. Выстаўляем усе неабходныя налады. “Інтэрвал уводу” – інтэрвал усіх чатырох слупкоў. «Інтэрвал вываду» - месца, дзе мы хочам адлюстраваць вынікі. Націскаем на кнопку «ОК».
3. У абраным месцы была пабудавана карэляцыйная матрыца. Кожнае перасячэнне радка і слупка з'яўляецца каэфіцыентам карэляцыі. Калі каардынаты супадаюць, адлюстроўваецца лічба 1.

Функцыя CORREL для вызначэння ўзаемасувязі і карэляцыі ў Excel

CORREL – функцыя, якая выкарыстоўваецца для разліку каэфіцыента карэляцыі паміж 2 масівамі. Давайце паглядзім на чатырох прыкладах усіх магчымасцяў гэтай функцыі.

Прыклады выкарыстання функцыі CORREL ў Excel

Першы прыклад. Ёсць таблічка з інфармацыяй аб сярэдніх заробках супрацоўнікаў кампаніі за адзінаццаць гадоў і курсе $. Неабходна выявіць сувязь паміж гэтымі двума велічынямі. Табліца выглядае так:

Алгарытм разліку выглядае так:

Адлюстраваны бал блізкі да 1. Вынік:

Вызначэнне каэфіцыента карэляцыі ўплыву дзеянняў на вынік

Другі прыклад. Два ўдзельнікі таргоў звярнуліся ў дзве розныя агенцтвы па дапамогу ў пятнаццацідзённай акцыі. Кожны дзень праводзілася сацапытанне, якое вызначала ступень падтрымкі кожнага прэтэндэнта. Любы суразмоўца мог выбраць аднаго з двух прэтэндэнтаў або выступіць супраць усіх. Неабходна вызначыць, наколькі кожная рэкламная акцыя паўплывала на ступень падтрымкі абітурыентаў, якая кампанія больш эфектыўная.

Выкарыстоўваючы прыведзеныя ніжэй формулы, разлічваем каэфіцыент карэляцыі:

• =КАРЭЛ(A3:A17; B3:B17).
• =КАРЭЛ(A3:A17;C3:C17).

вынікі:

З атрыманых вынікаў становіцца ясна, што ступень падтрымкі 1-га заяўніка павялічвалася з кожным днём прасоўвання рэкламы, такім чынам, каэфіцыент карэляцыі набліжаецца да 1. Пры запуску рэкламы іншы заяўнік меў вялікую колькасць даверу, а для 5 дзён назіралася станоўчая дынаміка. Затым ступень даверу зніжалася і на пятнаццаты дзень апусцілася ніжэй за першапачатковыя паказчыкі. Нізкія балы сведчаць аб тым, што прасоўванне негатыўна паўплывала на падтрымку. Не варта забываць, што на паказчыкі могуць паўплываць і іншыя спадарожныя фактары, якія не ўлічваюцца ў таблічным выглядзе.

Аналіз папулярнасці кантэнту па суадносінах праглядаў відэа і рэпостаў

Трэці прыклад. Чалавек для прасоўвання ўласных ролікаў на відэахостынгу YouTube выкарыстоўвае сацыяльныя сеткі для рэкламы канала. Ён заўважае, што паміж колькасцю рэпостаў у сацыяльных сетках і колькасцю праглядаў на канале ёсць нейкая залежнасць. Ці можна прадказаць будучую прадукцыйнасць з дапамогай электронных табліц? Неабходна вызначыць мэтазгоднасць прымянення раўнання лінейнай рэгрэсіі для прагназавання колькасці праглядаў відэа ў залежнасці ад колькасці рэпостаў. Табліца са значэннямі:

Зараз неабходна вызначыць наяўнасць ўзаемасувязі паміж 2 паказчыкамі па формуле, прыведзенай ніжэй:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Моцная прамая залежнасць”;”Моцная адваротная залежнасць”);”Слабая сувязь ці яе няма”)' class='formula'>

Калі выніковы каэфіцыент вышэй за 0,7, то мэтазгодней выкарыстоўваць функцыю лінейнай рэгрэсіі. У гэтым прыкладзе мы робім:

Цяпер мы будуем графік:

Мы ўжываем гэта ўраўненне для вызначэння колькасці праглядаў пры 200, 500 і 1000 абагульваннях: =9,2937*D4-206,12. Мы атрымліваем наступныя вынікі:

функцыя ПРАГНОЗ дазваляе вызначыць колькасць праглядаў на дадзены момант, калі было, напрыклад, дзвесце пяцьдзесят репост. Мы ўжываем: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Значэнні не звязаны”)' class='formula'>. Мы атрымліваем наступныя вынікі:

Асаблівасці выкарыстання функцыі КАРЭЛЬ у Excel

Гэтая функцыя мае наступныя магчымасці:

1. Пустыя вочкі не ўлічваюцца.
2. Ячэйкі, якія змяшчаюць інфармацыю тыпу Boolean і Text, не ўлічваюцца.
3. Падвойнае адмаўленне «-» выкарыстоўваецца для ўліку лагічных значэнняў у выглядзе лікаў.
4. Колькасць ячэек у вывучаемых масівах павінна супадаць, інакш будзе выведзена паведамленне #N/A.

Ацэнка статыстычнай значнасці каэфіцыента карэляцыі

Пры праверцы значнасці каэфіцыента карэляцыі нулявая гіпотэза заключаецца ў тым, што індыкатар мае значэнне 0, у той час як альтэрнатыва не мае значэння. Для праверкі выкарыстоўваецца наступная формула:

заключэнне

Карэляцыйны аналіз у электроннай табліцы - просты і аўтаматызаваны працэс. Для яго выканання трэба толькі ведаць, дзе знаходзяцца неабходныя інструменты і як іх актываваць праз налады праграмы.