Корелационен анализ в Excel. Пример за извършване на корелационен анализ

Корелационният анализ е общ изследователски метод, използван за определяне на нивото на зависимост на 1-вата стойност от 2-рата. Електронната таблица има специален инструмент, който ви позволява да приложите този тип изследвания.

Същността на корелационния анализ

Необходимо е да се определи връзката между две различни величини. С други думи, той разкрива в каква посока (по-малка / по-голяма) се променя стойността в зависимост от промените във втората.

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

Зависимостта се установява, когато започне идентифицирането на коефициента на корелация. Този метод се различава от регресионния анализ, тъй като има само един индикатор, изчислен чрез корелация. Интервалът се променя от +1 до -1. Ако е положителен, тогава увеличението на първата стойност допринася за увеличението на втората. Ако е отрицателна, тогава увеличението на 2-вата стойност допринася за намаляване на 1-рата. Колкото по-висок е коефициентът, толкова по-силно една стойност влияе на втората.

Важно! При 0-ия коефициент няма връзка между количествата.

Изчисляване на коефициента на корелация

Нека анализираме изчислението на няколко проби. Например, има таблични данни, където разходите за рекламна промоция и обемът на продажбите са описани по месеци в отделни колони. Въз основа на таблицата ще разберем степента на зависимост на обема на продажбите от парите, изразходвани за рекламна промоция.

Метод 1: Определяне на корелация чрез съветника за функции

CORREL – функция, която ви позволява да реализирате корелационен анализ. Обща форма – КОРЕЛ(масив1;масив2). Подробни инструкции:

1. Необходимо е да изберете клетката, в която се планира да се покаже резултатът от изчислението. Щракнете върху „Вмъкване на функция“, разположено вляво от текстовото поле, за да въведете формулата.

2. Отваря се съветникът за функции. Тук трябва да намерите КОРЕЛ, щракнете върху него, след това върху „OK“.

3. Отваря се прозорецът с аргументи. В реда “Array1” трябва да въведете координатите на интервалите на първата от стойностите. В този пример това е колоната Sales Value. Просто трябва да изберете всички клетки, които са в тази колона. По същия начин трябва да добавите координатите на втората колона към реда „Array1“. В нашия пример това е колоната Разходи за реклама.

4. След като въведете всички диапазони, щракнете върху бутона „OK“.

Коефициентът беше показан в клетката, която беше посочена в началото на нашите действия. Полученият резултат е 0,97. Този индикатор отразява високата зависимост на първата стойност от втората.

Метод 2: Изчисляване на корелация с помощта на Analysis ToolPak

Има и друг метод за определяне на корелацията. Тук се използва една от функциите в пакета за анализ. Преди да го използвате, трябва да активирате инструмента. Подробни инструкции:

1. Отидете в секцията „Файл“.

2. Ще се отвори нов прозорец, в който трябва да кликнете върху секцията „Настройки“.
3. Кликнете върху „Добавки“.
4. Намираме елемента „Управление“ в долната част. Тук трябва да изберете „Добавки на Excel“ от контекстното меню и да щракнете върху „OK“.

5. Отвори се специален прозорец за добавки. Поставете отметка до елемента „Пакет за анализ“. Щракваме върху „OK“.
6. Активирането беше успешно. Сега да отидем на Data. Появи се блокът „Анализ“, в който трябва да щракнете върху „Анализ на данни“.
7. В новия прозорец, който се показва, изберете елемента „Корелация“ и щракнете върху „ОК“.

8. На екрана се появи прозорецът за настройки на анализа. В реда „Интервал на въвеждане“ е необходимо да въведете диапазона на абсолютно всички колони, участващи в анализа. В този пример това са колоните „Стойност на продажбите“ и „Разходи за реклама“. Настройките за изходен дисплей първоначално са зададени на Нов работен лист, което означава, че резултатите ще бъдат показани на различен лист. По желание можете да промените изходното местоположение на резултата. След като направите всички настройки, щракнете върху „OK“.

Излязоха окончателните резултати. Резултатът е същият като при първия метод – 0,97.

Дефиниране и изчисляване на коефициент на множествена корелация в MS Excel

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

Подробно ръководство:

1. В секцията „Данни“ намираме вече известния блок „Анализ“ и щракнете върху „Анализ на данни“.

2. В прозореца, който се показва, щракнете върху елемента „Корелация“ и щракнете върху „OK“.
3. В реда „Интервал на въвеждане“ въвеждаме интервала за три или повече колони от изходната таблица. Диапазонът може да бъде въведен ръчно или просто да го изберете с LMB и той автоматично ще се появи в желания ред. В „Групиране“ изберете подходящия метод за групиране. В „Изходен параметър“ указва мястото, където ще се показват резултатите от корелацията. Щракваме върху „OK“.

4. Готов! Построена е корелационната матрица.

Коефициент на корелация на двойки в Excel

Нека да разберем как правилно да начертаем коефициента на корелация на двойката в електронна таблица на Excel.

Изчисляване на коефициент на корелация на двойки в Excel

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

X е зависимата променлива, а y е независимата. Необходимо е да се намери посоката и силата на връзката между тези показатели. Инструкция стъпка по стъпка:

1. Нека намерим средните стойности с помощта на функцията СЪРЦЕ.

2. Нека изчислим всеки х и xср, у и ср с помощта на оператора «-».

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).
• =КОРЕЛ(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;PREDICTION(D7;B3:B8;A3:A8);”Стойностите не са свързани”)' class='formula'>. Получаваме следните резултати:

Характеристики на използване на функцията CORREL в Excel

Тази функция има следните характеристики:

1. Празните клетки не се вземат предвид.
2. Клетките, съдържащи информация за тип Boolean и Text, не се вземат предвид.
3. Двойното отрицание „-“ се използва за отчитане на логически стойности под формата на числа.
4. Броят на клетките в изследваните масиви трябва да съвпада, в противен случай ще се покаже съобщението #N/A.

Оценка на статистическата значимост на корелационния коефициент

Когато се тества значимостта на корелационния коефициент, нулевата хипотеза е, че индикаторът има стойност 0, докато алтернативата не. За проверка се използва следната формула:

Заключение

Корелационният анализ в електронна таблица е прост и автоматизиран процес. За да го изпълните, трябва само да знаете къде се намират необходимите инструменти и как да ги активирате чрез настройките на програмата.