Как да направите корелационен анализ в excel. Математически методи в психологията

„Корелация“ в превод от латински означава „съотношение“, „взаимна връзка“. Количествена характеристика на връзката може да се получи чрез изчисляване на коефициента на корелация. Този коефициент, популярен в статистическите анализи, показва дали определени параметри са свързани помежду си (например височина и тегло; ниво на интелигентност и академични постижения; брой наранявания и продължителност на работа).

Използване на корелация

Изчислението на корелацията е особено широко използвано в икономиката, социологическите изследвания, медицината и биометрията - навсякъде, където могат да се получат два набора от данни, между които може да се намери връзка.

Можете да изчислите корелацията ръчно, като извършите проста аритметика. Процесът на изчисление обаче се оказва много времеемък, ако наборът от данни е голям. Особеността на метода е, че изисква събирането на голямо количество изходни данни, за да се отрази най-точно дали има връзка между характеристиките. Следователно сериозното използване на корелационния анализ е невъзможно без използването на компютърни технологии. Една от най-популярните и достъпни програми за решаване на този проблем е.

Как да извършите корелация в Excel?

Най-отнемащата време стъпка при определяне на корелацията е събирането на масива от данни. Сравняваните данни обикновено се намират в две колони или редове. Таблицата трябва да бъде направена без пропуски в клетките. Съвременните версии на Excel (от 2007 г. и по-рано) не изискват допълнителни настройки за статистически изчисления; могат да се направят необходимите манипулации:

1. Изберете празна клетка, в която ще се покажат резултатите от изчислението.
2. Щракнете върху „Формули“ в главното меню на Excel.
3. Сред бутоните, групирани в „Библиотека с функции“, изберете „Други функции“.
4. В падащите списъци изберете функцията за изчисляване на корелация (Статистически - CORREL).
5. Excel отваря панела с аргументи на функцията. „Масив 1“ и „Масив 2“ са диапазоните от данни, които се сравняват. За да попълните автоматично тези полета, можете просто да изберете необходимите клетки от таблицата.
6. Щракнете върху „OK“, за да затворите прозореца с аргументи на функцията. Изчисленият коефициент на корелация се появява в клетката.

Корелацията може да бъде пряка (ако коефициентът е по-голям от нула) и обратна (от -1 до 0).

Първият означава, че с увеличаването на единия параметър се увеличава и другият. Обратната (отрицателна) корелация отразява факта, че когато една променлива нараства, другата намалява.

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

Ако коефициентът показва средна или силна връзка (от ±0,5 до ±0,99), трябва да се помни, че това е само статистическа връзка, която изобщо не гарантира влиянието на един параметър върху друг. Също така не може да се изключи, че и двата параметъра са независими един от друг, но се влияят от някакъв трети неотчетен фактор. Excel ви помага незабавно да изчислите коефициента на корелация, но обикновено количествените методи сами по себе си не са достатъчни за установяване на причинно-следствени връзки в корелирани проби.

Количествена характеристика на връзката може да се получи чрез изчисляване на коефициента на корелация.

Корелационен анализ в Excel

Самата функция има обща форма CORREL(масив1, масив2). В полето "Array1" въведете координатите на диапазона от клетки на една от стойностите, чиято зависимост трябва да се определи. Както можете да видите, коефициентът на корелация под формата на число се появява в клетката, която преди това сме избрали. Отваря се прозорец с параметри за корелационен анализ. За разлика от предишния метод, в полето „Интервал на въвеждане“ въвеждаме интервала не на всяка колона поотделно, а на всички колони, които участват в анализа. Както можете да видите, приложението Excel предлага два метода за корелационен анализ наведнъж.

Корелационна графика в excel

6) Първият елемент от финалната таблица ще се появи в горната лява клетка на избраната област. Следователно хипотезата H0 се отхвърля, т.е. регресионните параметри и корелационният коефициент не са случайно различни от нула, а са статистически значими. 7. Получените оценки на регресионното уравнение позволяват то да се използва за прогнозиране.

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

Ако коефициентът е 0, това показва, че няма връзка между стойностите. За да намерите връзката между променливите и y, използвайте вградената в Microsoft Excel функция „CORREL“. Например за „Масив1“ изберете стойностите y, а за „Масив2“ изберете стойностите x. В резултат на това ще получите коефициента на корелация, изчислен от програмата. След това трябва да изчислите разликата между всяко x и xav и yav. В избраните клетки напишете формулите x-x, y-. Не забравяйте да закачите клетки със средни стойности. Полученият резултат ще бъде желаният коефициент на корелация.

Горната формула за изчисляване на коефициента на Pearson показва колко трудоемък е този процес, ако се извършва ръчно. Второ, моля, препоръчайте какъв тип корелационен анализ може да се използва за различни проби с голямо разпространение на данни? Как да докажа статистически, че има значителна разлика между групата над 60 и всички останали?

Направи си сам: Изчисляване на валутните корелации с помощта на Excel

Например, ние използваме Microsoft Excel, но всяка друга програма, в която можете да използвате корелационна формула, ще свърши работа. 7. След това изберете клетките с данни за EUR/USD. 9. Натиснете Enter, за да изчислите коефициента на корелация за EUR/USD и USD/JPY. Не си струва да актуализирате числата всеки ден (е, освен ако не сте обсебени от валутните корелации).

Срещали ли сте вече необходимостта да изчислите степента на връзка между две статистически величини и да определите формулата, по която те корелират? За да направя това, използвах функцията CORREL - тук има малко информация за нея. Връща степента на корелация между два диапазона от данни. Теоретично, корелационната функция може да бъде прецизирана чрез преобразуването й от линейна в експоненциална или логаритмична. Анализът на данните и корелационните графики могат значително да подобрят неговата надеждност.

Да приемем, че клетка B2 съдържа самия коефициент на корелация, а клетка B3 съдържа броя на пълните наблюдения. Имате ли рускоезичен офис, между другото, намерих и грешка - значимостта не се изчислява за отрицателни корелации? Ако и двете променливи са метрични и имат нормално разпределение, тогава изборът е правилен. И възможно ли е да се характеризира критерият за сходство на кривите, като се използва само една CC. Нямате сходство на „криви“, а сходство на две серии, които по принцип могат да бъдат описани с крива.

Срещали ли сте вече необходимостта да изчислите степента на връзка между две статистически величини и да определите формулата, по която те корелират? Един нормален човек може да се запита защо изобщо е необходимо това. Колкото и да е странно, това всъщност е необходимо. Познаването на надеждни корелации може да ви помогне да правите луди пари, ако сте, да речем, търговец на акции. Проблемът е, че по някаква причина никой не разкрива тези корелации (изненадващо, нали?).

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

Тази статия е предназначена за напреднали нива на владеене на Microsoft Excel. Ако нямате време да прочетете цялата статия, можете да изтеглите файла и да разберете сами.

Ако често ви се налага да правите нещо подобноСилно ви препоръчвам да обмислите закупуването на книгата. Статистически изчисления в Excel.

Какво е важно да знаете за корелациите

За да изчислите надеждна корелация, трябва да имате надеждна извадка; колкото по-голяма е тя, толкова по-надежден ще бъде резултатът. За целите на този пример взех ежедневна извадка от обменните курсове за 10 години. Данните са свободно достъпни, взех ги от сайта http://oanda.com.

Какво всъщност направих

(1) След като получих необработените данни, започнах с проверка на степента на корелация между двата набора от данни. За да направя това, използвах функцията CORREL - има малко информация за нея. Връща степента на корелация между два диапазона от данни. Резултатът, честно казано, не беше особено впечатляващ (само около 70%). Най-общо казано, степента на корелация между две величини обикновено се счита за квадрат на тази величина, т.е. корелацията се оказа надеждна с приблизително 49%. Това е много малко!

(2) Това ми се стори много странно. Какви грешки може да са се промъкнали в изчисленията ми? Затова реших да направя графика и да видя какво може да се случи. Графиката беше специално разбита по години, за да можете визуално да видите къде се нарушава корелацията. Графикът се получи така

(3) От графиката е очевидно, че в диапазона от около 35 рубли за евро корелацията започва да се разделя на две части. Поради това се оказа ненадежден. Трябваше да се установи защо това се случва.

(4) Цветът показва, че тези данни се отнасят за 2007, 2008, 2009 г. Със сигурност! Периодите на икономически пикове и рецесии обикновено са статистически ненадеждни, което се случи в този случай. Затова се опитах да изключа тези периоди от данните (и за да проверя, проверих степента на корелация на данните в този период). Степента на корелация само на тези данни е 0,01%, тоест напълно липсва. Но без тях данните корелират с приблизително 81%. Това вече е доста надеждна корелация. Ето графика с функция.

Следващи стъпки

Теоретично, корелационната функция може да бъде прецизирана чрез преобразуването й от линейна в експоненциална или логаритмична. В този случай статистическата надеждност на корелацията се увеличава с приблизително един процент, но сложността на прилагане на формулата се увеличава невероятно. Затова си задавам въпроса: наистина ли е необходимо това? Вие решавате - за всеки конкретен случай.

1. Отворете Excel

2. Създаване на колони с данни. В нашия пример ще разгледаме връзката или корелацията между агресивността и неувереността в себе си при първокласниците. В експеримента са участвали 30 деца, данните са представени в таблицата на Excel:

1 колона - номер на тема

2 колона - агресивноств точки

3 колона - неувереноств точки

3. След това трябва да изберете празна клетка до таблицата и да щракнете върху иконата f(x)в панела на Excel

4. Ще се отвори менюто с функции, трябва да изберете между категориите Статистически , а след това сред списъка с функции намерете по азбучен ред КОРЕЛи щракнете върху OK

5. След това ще се отвори меню с аргументи на функцията, което ще ви позволи да изберете колоните с данни, от които се нуждаем. За да изберете първата колона Агресивносттрябва да кликнете върху синия бутон до линията Масив1

6.Изберете данни за Масив1от колоната Агресивности щракнете върху синия бутон в диалоговия прозорец

7. След това, подобно на Array 1, щракнете върху синия бутон до реда Масив2

8.Изберете данни за Масив2- колона Неуверености натиснете отново синия бутон, след което OK

9. Тук коефициентът на корелация r-Pearson е изчислен и записан в избраната клетка. В нашия случай той е положителен и приблизително равен на 0,225 . Това говори за умерено положителенВръзки между агресивността и неувереността в себе си при първокласниците

По този начин, статистическо заключениеексперимента ще бъде: r = 0,225, беше разкрита умерена положителна връзка между променливите агресивностИ неувереност.

Някои проучвания изискват p-ниво на значимост на коефициента на корелация да бъде посочено, но Excel, за разлика от SPSS, не предоставя тази опция. Всичко е наред, има (А. Д. Наследов).

Можете също да го прикачите към резултатите от изследването.