Как да изчислим линейния коефициент на корелация. Множествен коефициент на корелация в Excel (Excel)

Днешната статия ще говори за това как променливите могат да бъдат свързани една с друга. Използвайки корелация, можем да определим дали има връзка между първата и втората променлива. Надявам се да намерите това занимание също толкова забавно, колкото и предишните!

Корелацията измерва силата и посоката на връзката между x и y. Фигурата показва различни видове корелация под формата на точкови диаграми на подредени двойки (x, y). Традиционно променливата x се поставя върху хоризонталната ос, а променливата y се поставя върху вертикалната ос.

Графика A е пример за положителна линейна корелация: с нарастването на x, y също се увеличава и то линейно. Графика B ни показва пример за отрицателна линейна корелация, където с нарастване на x, y намалява линейно. В графика C виждаме, че няма корелация между x и y. Тези променливи не си влияят по никакъв начин.

И накрая, графика D е пример за нелинейни връзки между променливи. Когато x нараства, y първо намалява, след това променя посоката си и се увеличава.

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

Коефициент на корелация

Коефициентът на корелация, r, ни дава както силата, така и посоката на връзката между независимите и зависимите променливи. Стойностите на r варират между - 1,0 и + 1,0. Когато r е положително, връзката между x и y е положителна (графика A на фигурата), а когато r е отрицателна, връзката също е отрицателна (графика B). Коефициент на корелация, близък до нула, показва, че няма връзка между x и y (графика C).

Силата на връзката между x и y се определя от това дали коефициентът на корелация е близо до - 1,0 или +- 1,0. Разгледайте следния чертеж.

Графика A показва перфектна положителна корелация между x и y при r = + 1,0. Графика B - идеална отрицателна корелация между x и y при r = - 1.0. Графики C и D са примери за по-слаби връзки между зависимите и независимите променливи.

Коефициентът на корелация, r, определя както силата, така и посоката на връзката между зависимите и независимите променливи. Стойностите на r варират от - 1,0 (силна отрицателна връзка) до + 1,0 (силна положителна връзка). Когато r = 0 няма връзка между променливите x и y.

Можем да изчислим действителния коефициент на корелация, като използваме следното уравнение:

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

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

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

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

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

CORREL (масив 1; масив 2),

масив 1 = диапазон от данни за първата променлива,

масив 2 = диапазон от данни за втората променлива.

Например, фигурата показва функцията CORREL, използвана за изчисляване на коефициента на корелация за примера за оценка от изпита.

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

Корелационен анализ в 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?Нямате сходството на „кривите“, а сходството на две серии, които по принцип могат да бъдат описани с крива.

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, не предоставя тази опция. Всичко е наред, има (А. Д. Наследов).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

За териториите на областта са предоставени данни за 200Х.

Упражнение:

1. Конструирайте корелационно поле и формулирайте хипотеза за формата на връзката.

2. Изчислете параметрите на уравнението на линейната регресия

4. Използвайки средния (общ) коефициент на еластичност, дайте сравнителна оценка на силата на връзката между фактора и резултата.

7. Изчислете прогнозната стойност на резултата, ако прогнозната стойност на фактора се увеличи с 10% от средното си ниво. Определете прогнозния доверителен интервал за нивото на значимост.

Решение:

Нека разрешим този проблем с помощта на Excel.

1. Чрез сравняване на наличните данни x и y, например, класирането им в нарастващ ред на фактор x, може да се наблюдава наличието на пряка връзка между характеристиките, когато увеличаването на средния жизнен минимум на глава от населението увеличава среднодневния заплата. Въз основа на това можем да направим предположението, че връзката между характеристиките е пряка и може да бъде описана с уравнение на права линия. Същото заключение се потвърждава и от графичен анализ.

За да създадете корелационно поле, можете да използвате Excel PPP. Въведете първоначалните данни в последователност: първо x, след това y.

Изберете областта от клетки, която съдържа данни.

След това изберете: Вмъкване / Точков график / Точков график с маркерикакто е показано на фигура 1.

Фигура 1 Конструкция на корелационното поле

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

2. Да се ​​изчислят параметрите на уравнението на линейната регресия
Нека използваме вградената статистическа функция LINEST.

За това:

1) Отворете съществуващ файл, съдържащ анализираните данни;
2) Изберете област 5x2 от празни клетки (5 реда, 2 колони), за да покажете резултатите от регресионната статистика.
3) Активирайте Съветник за функции: в главното меню изберете Формули / Вмъкване на функция.
4) В прозореца Категорияприемате Статистически, в прозореца на функцията - LINEST. Щракнете върху бутона Добрекакто е показано на фигура 2;

Фигура 2 Диалогов прозорец на съветника за функции

5) Попълнете аргументите на функцията:

Известни стойности за

Известни стойности на x

Константа- логическа стойност, която показва наличието или липсата на свободен член в уравнението; ако Constant = 1, тогава свободният член се изчислява по обичайния начин, ако Constant = 0, тогава свободният член е 0;

Статистика- логическа стойност, която показва дали да се покаже допълнителна информация за регресионния анализ или не. Ако Статистика = 1, тогава се показва допълнителна информация, ако Статистика = 0, тогава се показват само оценки на параметрите на уравнението.

Щракнете върху бутона Добре;

Фигура 3 Диалогов прозорец за аргументи на функция LINEST

6) Първият елемент от финалната таблица ще се появи в горната лява клетка на избраната област. За да отворите цялата таблица, натиснете бутона , а след това към клавишната комбинация ++ .

Допълнителна регресионна статистика ще бъде изведена в реда, показан на следната диаграма:

Фигура 4 Резултат от изчисляването на функцията LINEST

Получихме нивото на регресия:

Ние заключаваме: С увеличение на средния жизнен минимум на глава от населението с 1 rub. средната дневна заплата се увеличава средно с 0,92 рубли.

Това означава, че 52% от вариацията на заплатите (y) се обяснява с вариацията на фактора x - средната жизнена заплата на глава от населението, а 48% - с действието на други фактори, които не са включени в модела.

Използвайки изчисления коефициент на определяне, може да се изчисли коефициентът на корелация: .

Връзката се оценява като близка.

4. Използвайки средния (общ) коефициент на еластичност, определяме силата на влиянието на фактора върху резултата.

За уравнение на права линия определяме средния (общ) коефициент на еластичност, като използваме формулата:

Ще намерим средните стойности, като изберете областта от клетки с x стойности и изберете Формули / Автосума / Среднои ще направим същото със стойностите на y.

Фигура 5 Изчисляване на средните стойности на функцията и аргумент

Така, ако средната издръжка на живот на глава от населението се промени с 1% от средната си стойност, средната дневна заплата ще се промени средно с 0,51%.

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

Процедурата е следната:

1) проверете достъпа до Пакет за анализ. В главното меню изберете: Файл/Опции/Добавки.

2) В падащия списък контролИзбери предмет Excel добавкии натиснете бутона Отивам.

3) В прозореца Добавкипоставете отметка в квадратчето Пакет за анализи след това щракнете върху бутона Добре.

Ако Пакет за анализне е в списъка с полета Налични добавки, Натисни бутона Прегледза извършване на търсене.

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

4) В главното меню изберете: Данни / Анализ на данни / Инструменти за анализ / Регресияи след това щракнете върху бутона Добре.

5) Попълнете диалоговия прозорец за входни и изходни параметри на данни:

Интервал на въвеждане Y- диапазон, съдържащ данни на резултатния атрибут;

Интервал на въвеждане X- диапазон, съдържащ данни за факторната характеристика;

Етикети- флаг, който показва дали първият ред съдържа имена на колони или не;

Константа - нула- флаг, показващ наличието или липсата на свободен член в уравнението;

Изходен интервал- достатъчно е да посочите горната лява клетка на бъдещия диапазон;

6) Нов работен лист - можете да зададете произволно име за новия лист.

След това щракнете върху бутона Добре.

Фигура 6 Диалогов прозорец за въвеждане на параметри за инструмента за регресия

Резултатите от регресионния анализ за данните за проблема са представени на фигура 7.

Фигура 7 Резултат от използването на инструмента за регресия

5. Нека оценим качеството на уравненията, като използваме средната апроксимационна грешка. Нека използваме резултатите от регресионния анализ, представен на фигура 8.

Фигура 8 Резултат от използването на инструмента за регресия „Изтегляне на остатъка“

Нека създадем нова таблица, както е показано на Фигура 9. В колона C изчисляваме относителната грешка на приближението, използвайки формулата:

Фигура 9 Изчисляване на средната грешка на приближението

Средната грешка на приближението се изчислява по формулата:

Качеството на изработения модел се оценява като добро, тъй като не надвишава 8 - 10%.

6. От таблицата с регресионна статистика (Фигура 4) записваме действителната стойност на F-теста на Фишер:

Тъй като при 5% ниво на значимост, тогава можем да заключим, че регресионното уравнение е значимо (връзката е доказана).

8. Ще оценим статистическата значимост на регресионните параметри, като използваме t-статистиката на Student и като изчислим доверителния интервал на всеки индикатор.

Излагаме хипотезата H 0 за статистически незначима разлика между показателите и нулата:

.

за броя на степените на свобода

Фигура 7 показва действителните t-статистически стойности:

T-тестът за корелационния коефициент може да се изчисли по два начина:

Метод I:

Където - случайна грешка на коефициента на корелация.

Ще вземем данните за изчисление от таблицата на фигура 7.

Метод II:

Действителните t-статистически стойности надвишават стойностите на таблицата:

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

Доверителният интервал за параметър a се определя като

За параметър a границите от 95%, както е показано на фигура 7, бяха:

Доверителният интервал за регресионния коефициент се определя като

За коефициента на регресия b границите от 95%, както е показано на фигура 7, са:

Анализът на горната и долната граница на доверителните интервали води до заключението, че с вероятност параметрите a и b, намирайки се в зададените граници, не приемат нулеви стойности, т.е. не са статистически незначими и значително различни от нула.

7. Получените оценки на регресионното уравнение позволяват то да се използва за прогнозиране. Ако прогнозираните разходи за живот са:

Тогава прогнозната стойност на разходите за живот ще бъде:

Изчисляваме грешката на прогнозата по формулата:

Където

Ние също така ще изчислим дисперсията с помощта на Excel PPP. За това:

1) Активирайте Съветник за функции: в главното меню изберете Формули / Вмъкване на функция.

3) Попълнете диапазона, съдържащ числените данни на факторната характеристика. Кликнете Добре.

Фигура 10 Изчисляване на дисперсията

Получихме стойността на дисперсията

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

Доверителните интервали за прогнозиране на индивидуални стойности на y с вероятност 0,95 се определят от израза:

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

Условието на задачата е взето от: Workshop on econometrics: Proc. помощ / I.I. Елисеева, С.В. Куришева, Н.М. Гордеенко и др.; Изд. И.И. Елисеева. - М.: Финанси и статистика, 2003. - 192 с.: ил.