Анализа на корелација во Excel. Пример за извршување на корелациона анализа

Анализата на корелација е вообичаен метод на истражување што се користи за одредување на нивото на зависност на првата вредност од втората. Табелата има посебна алатка која ви овозможува да го спроведете овој тип на истражување.

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

Неопходно е да се одреди односот помеѓу две различни количини. Со други зборови, открива во која насока (помала / поголема) вредноста се менува во зависност од промените во втората.

Цел на корелација анализа

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

Важно! Кај 0-тиот коефициент нема врска помеѓу количините.

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

Ајде да ја анализираме пресметката на неколку примероци. На пример, има табеларни податоци, каде трошоците за промоција на реклами и обемот на продажба се опишани по месеци во посебни колони. Врз основа на табелата, ќе го дознаеме нивото на зависност на обемот на продажба од парите потрошени за рекламна промоција.

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

CORREL – функција која ви овозможува да спроведете корелација анализа. Општа форма - CORREL(massiv1;massiv2). Детални инструкции:

1. Неопходно е да се избере ќелијата во која се планира да се прикаже резултатот од пресметката. Кликнете на „Вметни функција“ што се наоѓа лево од полето за текст за да ја внесете формулата.

2. Се отвора Волшебникот за функции. Тука треба да најдете КОРЕЛ, кликнете на неа, потоа на „OK“.

3. Се отвора прозорецот со аргументи. Во линијата „Array1“ мора да ги внесете координатите на интервалите од 1-та од вредностите. Во овој пример, ова е колоната Продажна вредност. Треба само да ги изберете сите ќелии што се во оваа колона. Слично на тоа, треба да ги додадете координатите на втората колона во линијата „Array2“. Во нашиот пример, ова е колоната Трошоци за рекламирање.

4. Откако ќе ги внесете сите опсези, кликнете на копчето „OK“.

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

Метод 2: Пресметајте ја корелацијата користејќи го Пакетот со алатки за анализа

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

1. Одете во делот „Датотека“.

2. Ќе се отвори нов прозорец, во кој треба да кликнете на делот „Поставки“.
3. Кликнете на „Додатоци“.
4. Го наоѓаме елементот „Управување“ на дното. Овде треба да изберете „Excel Add-ins“ од контекстното мени и кликнете „OK“.

5. Се отвори специјален прозорец со додатоци. Ставете ознака до елементот „Пакет за анализа“. Ние кликнуваме на „OK“.
6. Активирањето беше успешно. Сега да одиме на податоци. Се појави блокот „Анализа“, во кој треба да кликнете на „Анализа на податоци“.
7. Во новиот прозорец што се појавува, изберете го елементот „Корелација“ и кликнете на „OK“.

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

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

Дефиниција и пресметка на коефициентот на повеќекратна корелација во MS Excel

За да се идентификува нивото на зависност на неколку количини, се користат повеќе коефициенти. Во иднина, резултатите се сумирани во посебна табела, наречена матрица на корелација.

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

1. Во делот „Податоци“, го наоѓаме веќе познатиот блок „Анализа“ и кликнете на „Анализа на податоци“.

2. Во прозорецот што се појавува, кликнете на елементот „Корелација“ и кликнете на „OK“.
3. Во линијата „Влезен интервал“ возиме во интервалот за три или повеќе колони од изворната табела. Опсегот може да се внесе рачно или едноставно да го изберете со LMB, и тој автоматски ќе се појави во саканата линија. Во „Групирање“ изберете го соодветниот метод на групирање. Во „Излезен параметар“ ја одредува локацијата каде што ќе се прикажат резултатите од корелацијата. Ние кликнуваме на „OK“.

4. Подготвени! Беше изградена матрицата на корелација.

Коефициент на корелација на пар во Excel

Ајде да откриеме како правилно да го нацртаме коефициентот на корелација на парот во табела на Excel.

Пресметка на коефициент на корелација на пар во Excel

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

X е зависна променлива, а y е независна. Неопходно е да се најде насоката и силата на врската помеѓу овие индикатори. Чекор-по-чекор инструкција:

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

2. Ајде да го пресметаме секој х и xavg, у и просек користејќи го операторот «-».

3. Ние ги множиме пресметаните разлики.

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

5. Пресметај ги именителите на разликата х и x-просек, y и y-средна. За да го направите ова, ќе извршиме квадратура.

6. Користење на функцијата АВТОСУМА, најдете ги индикаторите во добиените колони. Правиме множење. Користење на функцијата ROOT квадрат на резултатот.

7. Го пресметуваме количникот користејќи ги вредностите на именителот и броителот.

8. CORREL е интегрирана функција која ви овозможува да спречите сложени пресметки. Одиме во „Волшебникот за функции“, изберете CORREL и наведете ги низите индикатори х и у. Градиме график кој ги прикажува добиените вредности.

Матрица на коефициенти на парна корелација во Excel

Ајде да анализираме како да ги пресметаме коефициентите на спарените матрици. На пример, постои матрица од четири променливи.

Чекор-по-чекор инструкција:

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

Функција CORREL за одредување на врската и корелацијата во Excel

CORREL – функција која се користи за пресметување на коефициентот на корелација помеѓу 2 низи. Ајде да погледнеме четири примери за сите способности на оваа функција.

Примери за користење на функцијата CORREL во Excel

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

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

Прикажаниот резултат е блиску до 1. Резултат:

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

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

Користејќи ги формулите подолу, го пресметуваме коефициентот на корелација:

• =CORREL(A3:A17;B3:B17).
• =CORREL(A3:A17;C3:C17).

Резултати:

Од добиените резултати, станува јасно дека степенот на поддршка за првиот апликант се зголемувал со секој ден на рекламна промоција, па затоа, коефициентот на корелација се приближува до 1. Кога рекламирањето било лансирано, другиот апликант имал голем број доверба, а за 1 дена имаше позитивен тренд. Потоа степенот на доверба се намали и до петнаесеттиот ден падна под почетните показатели. Ниските резултати сугерираат дека промоцијата негативно влијаела на поддршката. Не заборавајте дека други придружни фактори кои не се разгледуваат во табеларна форма, исто така, може да влијаат на индикаторите.

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

Трет пример. Лицето за да промовира свои видеа на видео хостинг на 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;ПРЕДВИДУВАЊЕ(D7;B3:B8;A3:A8);"Вредностите не се поврзани")' class='формула'>. Ги добиваме следните резултати:

Карактеристики на користење на функцијата CORREL во Excel

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

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

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

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

Заклучок

Анализата на корелација во табела е едноставен и автоматизиран процес. За да го извршите, треба само да знаете каде се наоѓаат потребните алатки и како да ги активирате преку поставките на програмата.