Корелациона анализа у Екцел-у. Пример извођења корелационе анализе

Корелациона анализа је уобичајен метод истраживања који се користи за одређивање нивоа зависности 1. вредности од 2. Табела има посебан алат који вам омогућава да спроведете ову врсту истраживања.

Суштина корелационе анализе

Потребно је утврдити однос између две различите величине. Другим речима, открива у ком правцу (мање/веће) се вредност мења у зависности од промена у секунди.

Сврха корелационе анализе

Зависност се успоставља када почне идентификација коефицијента корелације. Овај метод се разликује од регресионе анализе, јер постоји само један индикатор израчунат коришћењем корелације. Интервал се мења од +1 до -1. Ако је позитиван, онда повећање прве вредности доприноси повећању друге. Ако је негативан, онда повећање 2. вредности доприноси смањењу друге. Што је коефицијент већи, то јача једна вредност утиче на другу.

Важно! Код 0. коефицијента нема везе између величина.

Прорачун коефицијента корелације

Хајде да анализирамо прорачун на неколико узорака. На пример, постоје табеларни подаци, где су трошкови за рекламну промоцију и обим продаје описани по месецима у посебним колонама. На основу табеле сазнаћемо ниво зависности обима продаје од новца утрошеног на рекламну промоцију.

Метод 1: Одређивање корелације путем чаробњака за функције

ЦОРРЕЛ – функција која вам омогућава да примените анализу корелације. Општи облик - ЦОРРЕЛ(масив1;масив2). Детаљна упутства:

1. Неопходно је одабрати ћелију у којој се планира приказати резултат прорачуна. Кликните на „Убаци функцију“ која се налази лево од поља за текст да бисте унели формулу.

2. Отвара се чаробњак за функције. Овде треба да нађете ЦОРРЕЛ, кликните на њега, а затим на „ОК“.

3. Отвара се прозор са аргументима. У ред „Низ1“ морате унети координате интервала 1. вредности. У овом примеру, ово је колона Вредност продаје. Само треба да изаберете све ћелије које се налазе у овој колони. Слично томе, потребно је да додате координате друге колоне у ред „Низ2“. У нашем примеру, ово је колона Трошкови оглашавања.

4. Након што унесете све опсеге, кликните на дугме „ОК“.

Коефицијент је приказан у ћелији која је назначена на почетку наших акција. Добијени резултат је 0,97. Овај индикатор одражава високу зависност прве вредности од друге.

Метод 2: Израчунајте корелацију помоћу пакета алата за анализу

Постоји још један метод за одређивање корелације. Овде се користи једна од функција која се налази у пакету за анализу. Пре употребе, потребно је да активирате алат. Детаљна упутства:

1. Идите на одељак „Датотека“.

2. Отвориће се нови прозор у којем морате кликнути на одељак „Подешавања“.
3. Кликните на „Додаци“.
4. На дну налазимо елемент „Управљање“. Овде морате да изаберете „Екцел додаци“ из контекстног менија и кликнете на „ОК“.

5. Отворен је посебан прозор додатака. Поставите квачицу поред елемента „Пакет анализе“. Кликнемо на „ОК“.
6. Активација је успела. Сада идемо на Дата. Појавио се блок „Анализа“ у којем треба да кликнете на „Анализа података“.
7. У новом прозору који се појави изаберите елемент „Корелација“ и кликните на „ОК“.

8. На екрану се појавио прозор поставки анализе. У ред „Инпут интервал“ потребно је унети опсег апсолутно свих колона које учествују у анализи. У овом примеру, ово су колоне „Вредност продаје“ и „Трошкови оглашавања“. Поставке приказа излаза су првобитно постављене на Нови радни лист, што значи да ће резултати бити приказани на другом листу. Опционо, можете променити излазну локацију резултата. Након што извршите сва подешавања, кликните на „ОК“.

Коначни резултати су вани. Резултат је исти као код прве методе – 0,97.

Дефинисање и израчунавање коефицијента вишеструке корелације у МС Екцел-у

Да би се идентификовао ниво зависности неколико величина, користе се вишеструки коефицијенти. У будућности, резултати се сумирају у посебну табелу, названу матрица корелације.

Детаљни водич:

1. У одељку „Подаци“ налазимо већ познати блок „Анализа“ и кликнемо на „Анализа података“.

2. У прозору који се појави кликните на елемент „Корелација“ и кликните на „ОК“.
3. У реду „Инпут интервал“ уносимо интервал за три или више колона изворне табеле. Опсег се може унети ручно или једноставно одабрати помоћу ЛМБ-а и аутоматски ће се појавити у жељеном реду. У „Груписање“ изаберите одговарајући метод груписања. У „Излазни параметар“ одређује локацију на којој ће бити приказани резултати корелације. Кликнемо на „ОК“.

4. Спремни! Изграђена је корелациона матрица.

Коефицијент корелације пара у Екцел-у

Хајде да схватимо како правилно нацртати коефицијент корелације пара у Екцел табели.

Прорачун коефицијента корелације парова у Екцел-у

На пример, имате к и и вредности.

Кс је зависна променљива, а и је независна. Неопходно је пронаћи правац и снагу односа између ових индикатора. Корак по корак упутство:

1. Нађимо просечне вредности помоћу функције СРЦЕ.

2. Израчунајмо сваки х и кавг, у и лета користећи оператор «-».

3. Израчунате разлике множимо.

4. Израчунавамо збир индикатора у овој колони. Бројилац је пронађени резултат.

5. Израчунај имениоце разлике х и к-просек, и и и-средњи. Да бисмо то урадили, извршићемо квадрирање.

6. Коришћење функције АУТОСУММА, пронађите индикаторе у резултујућим колонама. Радимо множење. Коришћење функције РООТ квадрат резултат.

7. Израчунавамо количник користећи вредности имениоца и бројила.

8. ЦОРРЕЛ је интегрисана функција која вам омогућава да спречите сложене прорачуне. Идемо у „Чаробњак за функције“, изаберите ЦОРРЕЛ и наведите низове индикатора х и у. Градимо графикон који приказује добијене вредности.

Матрица коефицијената парне корелације у Екцел-у

Хајде да анализирамо како израчунати коефицијенте упарених матрица. На пример, постоји матрица од четири променљиве.

Упутство корак по корак:

1. Идемо на „Анализа података“, која се налази у блоку „Анализа“ на картици „Подаци“. Изаберите Корелација са листе која се појави.
2. Поставили смо сва потребна подешавања. „Инпут интервал“ – интервал у све четири колоне. „Излазни интервал“ – место где желимо да прикажемо укупне вредности. Кликнемо на дугме "ОК".
3. На изабраном месту је изграђена корелациона матрица. Сваки пресек реда и колоне је коефицијент корелације. Број 1 се приказује када се координате подударају.

ЦОРРЕЛ функција за одређивање односа и корелације у Екцел-у

ЦОРРЕЛ – функција која се користи за израчунавање коефицијента корелације између 2 низа. Погледајмо четири примера свих способности ове функције.

Примери коришћења функције ЦОРРЕЛ у Екцел-у

Први пример. Постоји табла са подацима о просечним платама запослених у компанији током једанаест година и курсу долара. Неопходно је идентификовати однос између ове две величине. Табела изгледа овако:

Алгоритам прорачуна изгледа овако:

Приказани резултат је близу 1. Резултат:

Одређивање коефицијента корелације утицаја акција на резултат

Други пример. Два понуђача обратила су се двема различитим агенцијама за помоћ око петнаестодневне промоције. Свакодневно је вршена социјална анкета која је одређивала степен подршке за сваког подносиоца захтева. Сваки испитаник је могао изабрати једног од два кандидата или се супротставити свима. Потребно је утврдити колико је свака рекламна промоција утицала на степен подршке апликантима, која компанија је ефикаснија.

Користећи формуле у наставку, израчунавамо коефицијент корелације:

• =ЦОРРЕЛ(А3:А17;Б3:Б17).
• =ЦОРРЕЛ(А3:А17;Ц3:Ц17).

Резултати:

Из добијених резултата постаје јасно да је степен подршке првом подносиоцу растао са сваким даном рекламне промоције, па се коефицијент корелације приближава 1. Када је оглашавање покренуто, други апликант је имао велики број поверења, а за 1 дана је био позитиван тренд. Затим је степен поверења опао и до петнаестог дана пао испод почетних показатеља. Ниски резултати сугеришу да је промоција негативно утицала на подршку. Не заборавите да други пратећи фактори који нису узети у обзир у табеларном облику такође могу утицати на индикаторе.

Анализа популарности садржаја према корелацији прегледа видеа и репостова

Трећи пример. Особа која промовише сопствене видео снимке на ИоуТубе видео хостингу користи друштвене мреже за рекламирање канала. Он примећује да постоји нека веза између броја репостова на друштвеним мрежама и броја прегледа на каналу. Да ли је могуће предвидети будуће перформансе помоћу алата за прорачунске табеле? Неопходно је идентификовати разумност примене једначине линеарне регресије за предвиђање броја прегледа видеа у зависности од броја репостова. Табела са вредностима:

Сада је потребно утврдити присуство односа између 2 индикатора према формули испод:

0,7;ИФ(ЦОРРЕЛ(А3:А8;Б3:Б8)>0,7;”Јака директна веза”;”Јака инверзна веза”);”Слаб или никакав однос”)' цласс='формула'>

Ако је резултујући коефицијент већи од 0,7, онда је прикладније користити функцију линеарне регресије. У овом примеру радимо:

Сада градимо графикон:

Примењујемо ову једначину да одредимо број прегледа на 200, 500 и 1000 дељења: =9,2937*Д4-206,12. Добијамо следеће резултате:

функција ПРОГНОЗА омогућава вам да одредите број прегледа у овом тренутку, ако је било, на пример, двеста педесет репостова. Ми примењујемо: 0,7;ПРЕДИЦТИОН(Д7;Б3:Б8;А3:А8);”Вредности нису повезане”)' цласс='формула'>. Добијамо следеће резултате:

Карактеристике коришћења функције ЦОРРЕЛ у Екцел-у

Ова функција има следеће карактеристике:

1. Празне ћелије се не узимају у обзир.
2. Ћелије које садрже информације о логичком и текстуалном типу се не узимају у обзир.
3. Двострука негација „-“ се користи за обрачун логичких вредности у облику бројева.
4. Број ћелија у проучаваним низовима мора да се подудара, иначе ће се приказати порука #Н/А.

Процена статистичке значајности коефицијента корелације

Приликом тестирања значајности коефицијента корелације, нулта хипотеза је да индикатор има вредност 0, док алтернатива нема. За верификацију се користи следећа формула:

Zakljucak

Корелациона анализа у табели је једноставан и аутоматизован процес. Да бисте га извршили, потребно је само да знате где се налазе потребни алати и како да их активирате кроз подешавања програма.