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

Коефициент на корелация (или линеен коефициенткорелация) се обозначава като „r“ (в редки случаи като „ρ“) и характеризира линейна корелация(т.е. връзка, която е дадена от някаква стойност и посока) на две или повече променливи. Стойността на коефициента е между -1 и +1, тоест корелацията може да бъде както положителна, така и отрицателна. Ако коефициентът на корелация е -1, има идеална отрицателна корелация; ако коефициентът на корелация е +1, има перфектна положителна корелация. В други случаи има положителна корелация, отрицателна корелация или никаква корелация между две променливи. Коефициентът на корелация може да се изчисли ръчно, с помощта на безплатни онлайн калкулатори или с помощта на добър графичен калкулатор.

стъпки

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

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

• Дадени са например четири двойки стойности (числа) на променливите "x" и "y". Можете да създадете следната таблица:
  • x || г
  • 1 || 1
  • 2 || 3
  • 4 || 5
  • 5 || 7

1. Изчислете средноаритметичното на "х".За да направите това, добавете всички стойности на „x“ и след това разделете получения резултат на броя на стойностите.

   • В нашия пример са дадени четири стойности на променливата "x". За да изчислите средноаритметичната стойност на "x", добавете тези стойности и след това разделете сумата на 4. Изчисленията ще бъдат написани така:
   • μ x = (1 + 2 + 4 + 5) / 4 (\displaystyle \mu _(x)=(1+2+4+5)/4)
   • μ x = 12 / 4 (\displaystyle \mu _(x)=12/4)
   • μ x = 3 (\displaystyle \mu _(x)=3)

2. Намерете средното аритметично "y".За да направите това, бягайте подобни действия, т.е. съберете всички стойности на „y“ и след това разделете сумата на броя на стойностите.

   • В нашия пример са дадени четири стойности на променливата "y". Добавете тези стойности и след това разделете сумата на 4. Изчисленията ще бъдат написани така:
   • μ y = (1 + 3 + 5 + 7) / 4 (\displaystyle \mu _(y)=(1+3+5+7)/4)
   • μ y = 16 / 4 (\displaystyle \mu _(y)=16/4)
   • μ y = 4 (\displaystyle \mu _(y)=4)

3. Изчислете стандартното отклонение на "x".След като изчислите средните стойности на „x“ и „y“, намерете стандартни отклонениятези променливи. Стандартното отклонение се изчислява по следната формула:

   • σ x = 1 n − 1 Σ (x − μ x) 2 (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(n-1))\Sigma (x-\mu _( x))^(2))))
   • σ x = 1 4 − 1 ∗ ((1 − 3) 2 + (2 − 3) 2 + (4 − 3) 2 + (5 − 3) 2) (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(4-1))*((1-3)^(2)+(2-3)^(2)+(4-3)^(2)+(5-3) ^(2)))))
   • σ x = 1 3 ∗ (4 + 1 + 1 + 4) (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(3))*(4+1+1+4)) ))
   • σ x = 1 3 ∗ (10) (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(3))*(10))))
   • σ x = 10 3 (\displaystyle \sigma _(x)=(\sqrt (\frac (10)(3))))
   • σ x = 1,83 (\displaystyle \sigma _(x)=1,83)

4. Изчислете стандартното отклонение на "y".Следвайте стъпките, описани в предишната стъпка. Използвайте същата формула, но заменете стойностите "y" в нея.

   • В нашия пример изчисленията ще бъдат написани така:
   • σ y = 1 4 − 1 ∗ ((1 − 4) 2 + (3 − 4) 2 + (5 − 4) 2 + (7 − 4) 2) (\displaystyle \sigma _(y)=(\sqrt ((\frac (1)(4-1))*((1-4)^(2)+(3-4)^(2)+(5-4)^(2)+(7-4) ^(2)))))
   • σ y = 1 3 ∗ (9 + 1 + 1 + 9) (\displaystyle \sigma _(y)=(\sqrt ((\frac (1)(3))*(9+1+1+9)) ))
   • σ y = 1 3 ∗ (20) (\displaystyle \sigma _(y)=(\sqrt ((\frac (1)(3))*(20))))
   • σ y = 20 3 (\displaystyle \sigma _(y)=(\sqrt (\frac (20)(3))))
   • σ y = 2,58 (\displaystyle \sigma _(y)=2,58)

5. Запишете основната формула за изчисляване на коефициента на корелация.Тази формула включва средните стойности, стандартните отклонения и броя (n) двойки числа за двете променливи. Коефициентът на корелация се обозначава като "r" (в редки случаи като "ρ"). Тази статия използва формула за изчисляване на коефициента на корелация на Pearson.

   • Тук и в други източници количествата могат да бъдат обозначени по различен начин. Например, някои формули съдържат „ρ“ и „σ“, докато други съдържат „r“ и „s“. Някои учебници дават други формули, но те са математически аналози на горната формула.

6. Вие сте изчислили средните стойности и стандартните отклонения на двете променливи, така че можете да използвате формулата за изчисляване на коефициента на корелация. Спомнете си, че "n" е броят на двойките стойности за двете променливи. Стойностите на други количества са изчислени по-рано.

   • В нашия пример изчисленията ще бъдат написани така:
   • ρ = (1 n − 1) Σ (x − μ x σ x) ∗ (y − μ y σ y) (\displaystyle \rho =\left((\frac (1)(n-1))\right) \Sigma \left((\frac (x-\mu _(x))(\sigma _(x)))\right)*\left((\frac (y-\mu _(y))(\sigma _(у)))\надясно))
   • ρ = (1 3) ∗ (\displaystyle \rho =\left((\frac (1)(3))\right)*)[ (1 − 3 1 , 83) ∗ (1 − 4 2 , 58) + (2 − 3 1 , 83) ∗ (3 − 4 2 , 58) (\displaystyle \left((\frac (1-3)( 1,83))\десен)*\ляв((\frac (1-4)(2,58))\десен)+\ляв((\frac (2-3)(1,83))\десен) *\ляв((\ frac (3-4)(2,58))\вдясно))
     + (4 − 3 1 , 83) ∗ (5 − 4 2 , 58) + (5 − 3 1 , 83) ∗ (7 − 4 2 , 58) (\displaystyle +\left((\frac (4-3) )(1.83))\right)*\left((\frac (5-4)(2.58))\right)+\left((\frac (5-3)(1.83))\ right)*\left( (\frac (7-4)(2,58))\вдясно))]
   • ρ = (1 3) ∗ (6 + 1 + 1 + 6 4 , 721) (\displaystyle \rho =\left((\frac (1)(3))\right)*\left((\frac (6 +1+1+6)(4721))\десен))
   • ρ = (1 3) ∗ 2 , 965 (\displaystyle \rho =\left((\frac (1)(3))\right)*2,965)
   • ρ = (2 , 965 3) (\displaystyle \rho =\left((\frac (2,965)(3))\right))
   • ρ = 0,988 (\displaystyle \rho =0,988)

7. Анализирайте резултата.В нашия пример коефициентът на корелация е 0,988. Тази стойност по някакъв начин характеризира този набор от двойки числа. Обърнете внимание на знака и големината на стойността.

   • Тъй като стойността на корелационния коефициент е положителна, има положителна корелация между променливите “x” и “y”. Тоест, когато стойността на "x" се увеличава, стойността на "y" също се увеличава.
   • Тъй като стойността на коефициента на корелация е много близка до +1, стойностите на променливите "x" и "y" са силно взаимосвързани. Ако нанесете точки върху координатната равнина, те ще бъдат разположени близо до определена права линия.

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

   1. Намерете в интернет калкулатор за изчисляване на коефициента на корелация.Този коефициент доста често се изчислява в статистиката. Ако има много двойки числа, е почти невъзможно ръчно да се изчисли коефициентът на корелация. Затова има онлайн калкулатори за изчисляване на коефициента на корелация. В търсачката въведете „калкулатор на коефициента на корелация“ (без кавички).

   2. Въвеждане на данни.Моля, прегледайте инструкциите на уебсайта, за да се уверите, че въвеждате данните (числови двойки) правилно. Изключително важно е да въведете подходящите двойки числа; в противен случай ще получите неправилен резултат. Не забравяйте, че различните уебсайтове имат различни формати за въвеждане на данни.

      • Например на уебсайта http://ncalculators.com/statistics/correlation-coefficient-calculator.htm стойностите на променливите „x“ и „y“ се въвеждат в два хоризонтални реда. Стойностите са разделени със запетаи. Това означава, че в нашия пример стойностите „x“ се въвеждат така: 1,2,4,5, а стойностите „y“ така: 1,3,5,7.
      • На друг сайт, http://www.alcula.com/calculators/statistics/correlation-coefficient/, данните се въвеждат вертикално; в този случай не бъркайте съответните двойки числа.

   3. Изчислете коефициента на корелация.След като въведете данните, просто щракнете върху бутона „Изчисли“, „Изчисли“ или подобен, за да получите резултата.

      Използване на графичен калкулатор

      1. Въвеждане на данни.Вземете графичен калкулатор, влезте в статистически режим и изберете командата Редактиране.

         • Различните калкулатори изискват натискане на различни клавиши. Тази статия обсъжда калкулатора Texas Instruments TI-86.
         • За да превключите в режим на статистическо изчисление, натиснете – Stat (над бутона „+“). След това натиснете F2 – Редактиране.

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

         • Използвайте клавишите със стрелки, за да преместите курсора и да маркирате заглавието "xStat". След това натиснете Clear и Enter, за да изчистите всички стойности, въведени в колоната xStat.
         • Използвайте клавишите със стрелки, за да маркирате заглавието "yStat". След това натиснете Clear и Enter, за да изчистите всички стойности, въведени в колоната yStat.

      3. Въведете първоначалните данни.Използвайте клавишите със стрелки, за да преместите курсора до първата клетка под заглавието "xStat". Въведете първата стойност и натиснете Enter. “xStat (1) = __” ще се покаже в долната част на екрана, където въведената стойност ще се появи вместо интервал. След като натиснете Enter, въведената стойност ще се появи в таблицата и курсорът ще се премести на следващия ред; това ще покаже „xStat (2) = __“ в долната част на екрана.

         • Въведете всички стойности за променливата "x".
         • След като въведете всички стойности за променливата x, използвайте клавишите със стрелки, за да преминете към колоната yStat и въведете стойностите за променливата y.
         • След като всички двойки числа са въведени, натиснете Изход, за да изчистите екрана и да излезете от режима на статистическо изчисление.

      4. Изчислете коефициента на корелация.Той характеризира колко близо са данните до определен ред. Графичен калкулатор може бързо да определи подходящата линия и да изчисли коефициента на корелация.

         • Щракнете върху Stat – Calc. На TI-86 трябва да натиснете – –.
         • Изберете функцията "Линейна регресия". На TI-86 натиснете , което е обозначено с "LinR". Екранът ще покаже реда „LinR_“ с мигащ курсор.
         • Сега въведете имената на две променливи: xStat и yStat.
           • На TI-86 отворете списъка с имена; За да направите това, натиснете – –.
           • Долният ред на екрана ще покаже наличните променливи. Изберете (вероятно ще трябва да натиснете F1 или F2, за да направите това), въведете запетая и след това изберете .
           • Натиснете Enter за обработка на въведените данни.

      5. Анализирайте резултатите си.С натискане на Enter на екрана ще се покаже следната информация:

         • y = a + b x (\displaystyle y=a+bx): Това е функция, която описва права линия. Моля, обърнете внимание, че функцията не е написана в стандартна форма (y = kh + b).
         • a = (\displaystyle a=). Това е координатата "y" на точката, където линията пресича оста Y.
         • b = (\displaystyle b=). Това е наклонът на линията.
         • corr = (\displaystyle (\text(corr))=). Това е коефициентът на корелация.
         • n = (\displaystyle n=). Това е броят двойки числа, използвани в изчисленията.

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

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

Както следва от определението, да се изчисли коефициент на корелацияизисква се да се знае разпределението на случайните променливи X и Y. Ако разпределенията са неизвестни, тогава да се оцени коефициент на корелацияизползвани извадков коефициент на корелацияr (той също е обозначен като Rxy или r xy) :

където S x – стандартно отклонениепроби случайна величина x, изчислено по формулата:

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

Изчисли коефициент на корелацияИ примерна ковариацияв MS EXCEL не е трудно, тъй като за тази цел има специални функции CORREL() и KOVAR(). Много по-трудно е да разберете как да интерпретирате получените стойности; по-голямата част от статията е посветена на това.

Теоретично отстъпление

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

Забележка. Ако само една променлива, например Y, има случаен характер, а стойностите на другата са детерминистични (зададени от изследователя), тогава можем да говорим само за регресия.

Така например при изследване на зависимостта на средната годишна температура не може да се говори за корелациитемпература и година на наблюдение и съответно прилагане на показатели корелациисъс съответното им тълкуване.

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

1. Наличието на причинно-следствена връзка между променливите. Например размерът на инвестицията в Научно изследване(променлива X) и броя на получените патенти (Y). Първата променлива се появява като независима променлива (фактор), второ - зависима променлива (резултат). Трябва да се помни, че зависимостта на количествата определя наличието на корелация между тях, но не и обратното.
2. Наличието на конюгация ( обща кауза). Например, с разрастването на организацията се увеличава фондът за заплати (ведомостта за заплати) и разходите за наемане на помещения. Очевидно е погрешно да се приеме, че наемането на помещения зависи от заплатите. И двете променливи зависят линейно от броя на персонала в много случаи.
3. Взаимно влияние на променливите (когато едната се променя, втората променлива се променя и обратно). При този подход се допускат две формулировки на проблема; Всяка променлива може да действа както като независима променлива, така и като зависима променлива.

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

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

Корелацияблизка до 1 или -1 (т.е. близка по абсолютна стойност до 1) показва силна линейна връзка между променливите, стойност близка до 0 не показва връзка. Положителен корелацияозначава, че при увеличение на един показател, другият средно нараства, а при отрицателен показател намалява.

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

• броят на променливите трябва да бъде равен на две;
• променливите трябва да са количествени (напр. честота, тегло, цена). Изчислената средна стойност на тези променливи има смисъл: средна ценаили средно тегло на пациента. За разлика от количествените променливи, качествените (номинални) променливи приемат стойности само от краен набор от категории (например пол или кръвна група). Тези стойности обикновено се свързват с числени стойности (например женският пол е 1, а мъжкият пол е 2). Ясно е, че в този случай изчислението средна стойност, който е необходим за намиране корелации, е неправилно и следователно самото изчисление е неправилно корелации;
• променливите трябва да са случайни променливи и да имат .

Двумерните данни могат да имат различни структури. Някои от тях изискват определени подходи за работа:

• За данни с нелинейна връзка корелациятрябва да се използва с повишено внимание. За някои проблеми може да е полезно да трансформирате една или и двете променливи, за да създадете линейна връзка (това изисква да се направи предположение за типа на нелинейната връзка, за да се предложи типът на необходимата трансформация).
• Като се използва точкови диаграмиНякои данни може да показват неравномерни вариации (разсейване). Проблемът с неравномерната вариация е, че местоположенията с висока вариация не само предоставят най-малко точната информация, но също така оказват най-голямо влияние при изчисляването на статистиката. Този проблем също често се решава чрез трансформиране на данните, като например използване на логаритми.
• Може да се наблюдава разделяне на някои данни на групи (групиране), което може да показва необходимостта от разделяне на популацията на части.
• Извънредна стойност (рязко отклоняваща се стойност) може да изкриви изчислената стойност на корелационния коефициент. Отклонението може да се дължи на случайност, грешка в събирането на данни или всъщност може да отразява някаква характеристика на връзката. Тъй като извънредната стойност се отклонява значително от средната стойност, тя има голям принос за изчисляването на индикатора. Статистическите показатели често се изчисляват с и без отчитане на отклонения.

Използване на MS EXCEL за изчисляване на корелация

Да вземем 2 променливи като пример хИ Yи съответно, пробасъстоящ се от няколко двойки стойности (X i; Y i). За по-голяма яснота, нека изградим.

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

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

Забележка: В примерния файл можете да зададете параметрите на линейния тренд (наклон, Y-отсечка) и степента на разсейване спрямо тази линия на тренда. Можете също така да коригирате квадратичните параметри.

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

Забележка: Моля, обърнете внимание, че чрез промяна на мащаба на диаграмата вертикално или хоризонтална ос, облакът от точки може да изглежда като вертикална или хоризонтална линия. Ясно е, че променливите ще останат независими.

Както бе споменато по-горе, за изчисляване коефициент на корелацияв MS EXCEL има функция CORREL(). Можете също да използвате подобна функция PEARSON(), която връща същия резултат.

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

=COVARIANCE.G(B28:B88;D28:D88)/STDEV.G(B28:B88)/STDEV.G(D28:D88)

=COVARIANCE.B(B28:B88;D28:D88)/STDEV.B(B28:B88)/STDEV.B(D28:D88)

Забележка: Квадрат коефициент на корелация r е равно на коефициент на детерминация R2, който се изчислява при конструиране на регресионна линия с помощта на функцията QPIRSON(). Стойността на R2 може също да бъде изведена на точкова диаграмачрез изграждане на линеен тренд с помощта на стандартната функционалност на MS EXCEL (изберете диаграмата, изберете раздела Оформление, след това в групата Анализщракнете върху бутона Линия на тенденцияи изберете Линейна апроксимация). За повече информация относно конструирането на тренд линия вижте например .

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

Ковариацияе близък по значение до (също мярка за дисперсия) с тази разлика, че е дефиниран за 2 променливи и дисперсия- за един. Следователно, cov(x;x)=VAR(x).

За изчисляване на ковариацията в MS EXCEL (от версия 2010) се използват функциите COVARIATION.Г() и COVARIATION.В(). В първия случай формулата за изчисляване е подобна на горната (край .Gозначава Население ), във втория вместо множителя 1/n се използва 1/(n-1), т.е. край .INозначава проба.

Забележка: Функцията COVAR(), която присъства в MS EXCEL в по-ранни версии, е подобна на функцията COVARIATION.G().

Забележка: Функциите CORREL() и COVAR() са представени в английската версия като CORREL и COVAR. Функциите COVARIANCE.G() и COVARIANCE.B() като COVARIANCE.P и COVARIANCE.S.

Допълнителни формули за изчисление ковариации:

=SUMPRODUCT(B28:B88-СРЕДНО(B28:B88);(D28:D88-СРЕДНО(D28:D88)))/БРОЙ(D28:D88)

=SUMPRODUCT(B28:B88-СРЕДНО(B28:B88),(D28:D88))/БРОЙ(D28:D88)

=SUMPRODUCT(B28:B88;D28:D88)/COUNT(D28:D88)-AVERAGE(B28:B88)*AVERAGE(D28:D88)

Тези формули използват свойството ковариации:

Ако променливите хИ гнезависими, тогава тяхната ковариация е 0. Ако променливите не са независими, тогава дисперсията на тяхната сума е равна на:

VAR(x+y)= VAR(x)+ VAR(y)+2COV(x;y)

А дисперсияразликата им е равна

VAR(x-y)= VAR(x)+ VAR(y)-2COV(x;y)

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

За да проверим хипотезата, трябва да знаем разпределението на случайната величина, т.е. коефициент на корелация r. Обикновено хипотезата се тества не за r, а за случайната променлива t r:

който има n-2 степени на свобода.

Ако изчислената стойност на случайната променлива |t r | е по-голяма от критичната стойност t α,n-2 (α-определена), тогава нулевата хипотеза се отхвърля (връзката между стойностите е статистически значима).

Добавка за пакет за анализ

B за изчисляване на ковариация и корелация има инструменти със същото име анализ.

След извикване на инструмента се появява диалогов прозорец, съдържащ следните полета:

• Интервал на въвеждане: трябва да въведете връзка към диапазон с изходни данни за 2 променливи
• Групиране: По правило изходните данни се въвеждат в 2 колони
• Етикети в първия ред: ако квадратчето е отметнато, тогава Интервал на въвежданетрябва да съдържа заглавки на колони. Препоръчително е да поставите отметка в квадратчето, така че резултатът от добавката да съдържа информативни колони
• Изходен интервал: диапазонът от клетки, където ще бъдат поставени резултатите от изчислението. Достатъчно е да посочите горната лява клетка на този диапазон.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Има 2 метода за изчисляване на коефициента на корелация: методът на квадратите (Пиърсън), методът на ранговете (Спирман).

Най-точен е методът на квадратите (Pearson), при който корелационният коефициент се определя по формулата: , където

r xy е коефициентът на корелация между статистическите серии X и Y.

d x е отклонението на всяко от числата на статистическата серия X от средното му аритметично.

d y е отклонението на всяко от числата на статистическата серия Y от средното му аритметично.

В зависимост от силата на връзката и нейната посока коефициентът на корелация може да варира от 0 до 1 (-1). Корелационен коефициент 0 показва пълна липса на връзка. Колкото по-близо е нивото на коефициента на корелация до 1 или (-1), толкова по-голяма и по-близка е пряката или обратната връзка, която измерва. Когато коефициентът на корелация е равен на 1 или (-1), връзката е пълна и функционираща.

Схема за оценка на силата на корелация с помощта на коефициента на корелация

Силата на връзката	Стойността на коефициента на корелация, ако има такъв
	директна връзка (+)	обратна връзка (-)
Няма връзка
Връзката е малка (слаба)	от 0 до +0,29	от 0 до –0,29
Връзка средна (умерена)	от +0,3 до +0,69	от –0,3 до –0,69
Връзката е голяма (силна)	от +0,7 до +0,99	от –0,7 до –0,99
Пълна комуникация (функционален)

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

ОПРЕДЕЛЕТЕ СИЛАТА И ХАРАКТЕРА НА ВРЪЗКАТА МЕЖДУ

	Време е- ност гуша (V г )	д x = V х –М х	д y= V г –М г	д х д г	д х 2	д г 2
				Σ -1345 ,0	Σ 13996 ,0	Σ 313 , 47

1. Определете средното съдържание на йод във водата (в mg/l).

mg/l

2. Определете средната честота на гуша в %.

3. Определете отклонението на всеки V x от M x, т.е. dx.

201–138=63; 178–138=40 и т.н.

4. По същия начин определяме отклонението на всяко V y от M y, т.е. г г.

0,2–3,8=-3,6; 0,6–38=-3,2 и т.н.

5. Определете продуктите на отклоненията. Обобщаваме получения продукт и получаваме.

6. Повдигаме на квадрат d x и сумираме резултатите, получаваме.

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

8. Накрая заместваме всички получени суми във формулата:

За да разрешите проблема с надеждността на коефициента на корелация, определете го средна грешкапо формулата:

(Ако броят на наблюденията е по-малък от 30, тогава знаменателят е n–1).

В нашия пример

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

В нашия пример

Следователно коефициентът на корелация не е надежден, което налага увеличаване на броя на наблюденията.

Коефициентът на корелация може да се определи по малко по-малко точен, но много по-лесен начин - методът на ранговете (Spearman).

Метод на Spearman: P=1-(6∑d 2 /n-(n 2 -1))

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

заменете стойността на характеристиката във всяка от сравняваните серии с пореден номер (ранг). Ранговете или числата показват местата на индикаторите (стойностите) на първия и втория ред. При което числови стойностина втория атрибут, ранговете трябва да бъдат присвоени в същия ред, както е приет при разпределянето им на стойностите на първия атрибут. При равни стойности на характеристика в серия, ранговете трябва да се определят като средното число от сумата на поредните номера на тези стойности

определете разликата в ранга между x и y (d): d = x - y

повдигнете на квадрат получената разлика в ранга (d 2)

получете сумата от квадратите на разликата (Σ d 2) и заменете получените стойности във формулата:

Пример:Използвайки метода на ранга, установете посоката и силата на връзката между годините трудов стаж и честотата на нараняванията, ако се получат следните данни:

Обосновка за избора на метода:За да се реши даден проблем, може да се избере само метод рангова корелация, защото Първият ред на атрибута „трудов стаж в години“ има отворени опции (трудов стаж до 1 година и 7 или повече години), което не позволява използването на по-точен метод - методът на квадратите - за установяване на връзка между сравняваните характеристики.

Решение. Последователността на изчисленията е представена в текста, резултатите са представени в табл. 2.

таблица 2

Трудов стаж в години	Брой наранявания	Поредни числа (рангове)		Разлика в ранга	Квадратна разлика в ранговете
				d(x-y)	д 2

Всеки от редовете сдвоени характеристики е обозначен с “x” и “y” (колони 1-2).

Стойността на всеки признак се заменя с рангов (порядков) номер. Редът на разпределение на ранговете в реда "x" е следният: на минималната стойност на атрибута (опит до 1 година) се присвоява пореден номер "1", последващи варианти на същия ред атрибут, съответно, в в нарастващ ред, 2-ри, 3-ти, 4-ти и 5-ти поредни номера - рангове (виж колона 3). Подобен ред се следва при разпределянето на ранговете на втория атрибут „y“ (колона 4). В случаите, когато има няколко варианта с еднаква величина (например в стандартния проблем това са 12 и 12 наранявания на 100 работници със стаж 3-4 години и 5-6 години, поредният номер се обозначава със средния брой от сумата на поредните им номера. При класирането данните за броя на нараняванията (12 наранявания) трябва да заемат 2 и 3 места, така че средният им брой е (2 + 3)/2 = 2,5 от наранявания е “12” (атрибут) трябва да се разпределят едни и същи рангови числа – “2,5” (колона 4).

Определете разликата в ранга d = (x - y) - (колона 5)

Повдигнете на квадрат разликата в ранговете (d 2) и получете сумата от квадратите на разликата в ранговете Σ d 2 (колона 6).

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

където n е броят двойки опции, които се сравняват в реда „x“ и в реда „y“

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

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

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

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

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

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

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

Един начин, по който може да се извърши корелационен анализ, е чрез използване на функцията CORREL. Самата функция има обща форма CORREL(масив1, масив2).

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

   В полето “Array2” трябва да въведете координатите на втората колона. За нас това са рекламни разходи. Точно по същия начин, както в предишния случай, въвеждаме данните в полето.

   Кликнете върху бутона „OK“.

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

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

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

1. Отидете в раздела „Файл“.
2. В прозореца, който се отваря, преминете към секцията „Настройки“.
3. След това отидете на елемента „Добавки“.
4. В долната част на следващия прозорец, в секцията „Управление“, преместете превключвателя на позиция „Добавки на Excel“, ако е в различна позиция. Кликнете върху бутона „OK“.
5. В прозореца на добавките поставете отметка в квадратчето до елемента „Пакет за анализ“. Кликнете върху бутона „OK“.
6. След това пакетът за анализ се активира. Отидете в раздела „Данни“. Както можете да видите, това се появява тук на лентата нов блокинструменти – „Анализ“. Кликнете върху бутона „Анализ на данни“, който се намира в него.
7. Отваря се списък с различни опцииАнализ на данни. Изберете елемента „Корелация“. Кликнете върху бутона „OK“.
8. Отваря се прозорец с параметри за корелационен анализ. За разлика от предишния метод, в полето „Интервал на въвеждане“ въвеждаме интервала не на всяка колона поотделно, а на всички колони, които участват в анализа. В нашия случай това са данни в колоните „Разходи за реклама“ и „Стойност на продажбите“.

   Оставяме параметъра „Групиране“ непроменен – „По колони“, тъй като нашите групи данни са разделени на две колони. Ако бяха разбити ред по ред, тогава превключвателят трябва да бъде преместен на позиция „По ред“.

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

   Когато всички настройки са зададени, щракнете върху бутона „OK“.

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

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

Радваме се, че успяхме да ви помогнем да разрешите проблема.

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

Тази статия помогна ли ви?

Регресионният и корелационният анализ са статистически методи за изследване. Това са най-често срещаните начини за показване на зависимостта на параметър от една или повече независими променливи.

По-долу за конкретни практически примериНека да разгледаме тези два много популярни анализа сред икономистите. Ще дадем и пример за получаване на резултати при комбинирането им.

Регресионен анализ в Excel

Показва влиянието на някои стойности (независими, независими) върху зависимата променлива. Например, как броят на икономически активното население зависи от броя на предприятията, заплатите и други параметри. Или: как влияят на нивото на БВП чуждите инвестиции, цените на енергията и т.н.

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

Регресията се случва:

• линеен (y = a + bx);
• параболичен (y = a + bx + cx2);
• експоненциален (y = a * exp(bx));
• мощност (y = a*x^b);
• хиперболичен (y = b/x + a);
• логаритмичен (y = b * 1n(x) + a);
• експоненциален (y = a * b^x).

Нека да разгледаме конструкцията като пример регресионен моделв Excel и интерпретация на резултатите. Да вземем линейния тип регресия.

Задача. В 6 предприятия средномес заплатаи броя на напусналите служители. Необходимо е да се определи зависимостта на броя на напусналите служители от средната работна заплата.

Модел линейна регресияима следната форма:

Y = a0 + a1x1 +...+akhk.

Където a са регресионни коефициенти, x са влияещи променливи, k е броят на факторите.

В нашия пример Y е индикаторът за напускане на служители. Влияещият фактор е работната заплата (x).

Excel има вградени функции, които могат да ви помогнат да изчислите параметрите на линеен регресионен модел. Но добавката „Пакет за анализ“ ще направи това по-бързо.

Активираме мощен аналитичен инструмент:

1. Щракнете върху бутона „Офис“ и отидете в раздела „Опции на Excel“. „Добавки“.
2. В долната част, под падащия списък, в полето „Управление“ ще има надпис „Добавки на Excel“ (ако не е там, щракнете върху квадратчето за отметка вдясно и изберете). И бутонът „Отиди“. Кликнете.
3. Отваря се списък с наличните добавки. Изберете „Пакет за анализ“ и щракнете върху OK.

След като бъде активирана, добавката ще бъде достъпна в раздела Данни.

Сега нека направим самия регресионен анализ.

1. Отворете менюто с инструмента „Анализ на данни“. Изберете „Регресия“.
2. Това ще отвори меню за избор на входни стойности и опции за изход (къде да се покаже резултатът). В полетата за изходните данни посочваме диапазона на описания параметър (Y) и фактора, който го влияе (X). Останалите не трябва да се попълват.
3. След като щракнете върху OK, програмата ще покаже изчисленията на нов лист (можете да изберете интервал за показване на текущия лист или да присвоите изход към нова работна книга).

На първо място, обръщаме внимание на R-квадрат и коефициентите.

R-квадрат е коефициентът на детерминация. В нашия пример – 0,755, или 75,5%. Това означава, че изчислените параметри на модела обясняват 75,5% от връзката между изследваните параметри. Колкото по-висок е коефициентът на детерминация, толкова по-добър е моделът. Добър - над 0,8. Лошо – по-малко от 0,5 (такъв анализ едва ли може да се счита за разумен). В нашия пример – „не е зле“.

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

Коефициентът -0.16285 показва тежестта на променливата X върху Y. Тоест средната месечна заплата в рамките на този модел влияе върху броя на напусналите с тежест -0.16285 (това е малка степен на влияние). Знакът „-“ показва отрицателно въздействие: колкото по-висока е заплатата, толкова по-малко хора напускат. Което е справедливо.

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

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

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

Коефициентът на корелация се означава с r. Варира от +1 до -1. Класификация корелацииЗа различни областище бъде различно. Когато коефициентът е 0 линейна зависимостне съществува между пробите.

Нека да разгледаме как да намерим коефициента на корелация с помощта на Excel.

За намиране на сдвоени коефициенти се използва функцията CORREL.

Цел: Определете дали има връзка между времето за работа на струг и разходите за неговата поддръжка.

Поставете курсора в произволна клетка и натиснете бутона fx.

1. В категорията „Статистически“ изберете функцията CORREL.
2. Аргумент "Масив 1" - първият диапазон от стойности - време на работа на машината: A2:A14.
3. Аргумент “Масив 2” - втори диапазон от стойности – цена на ремонта: B2:B14. Натиснете OK.

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

За корелационен анализ на няколко параметъра (повече от 2) е по-удобно да използвате „Анализ на данни“ (добавката „Пакет за анализ“). Трябва да изберете корелация от списъка и да посочите масива. Всичко.

Получените коефициенти ще бъдат показани в корелационната матрица. Като този:

Корелационен и регресионен анализ

На практика тези две техники често се използват заедно.

1. Ние изграждаме поле за корелация: „Вмъкване“ - „Диаграма“ - „Диаграма на разсейване“ (позволява ви да сравнявате двойки). Диапазонът от стойности е всички цифрови данни в таблицата.
2. Щракнете с левия бутон върху произволна точка от диаграмата. След това надясно. В менюто, което се отваря, изберете „Добавяне на линия на тенденция“.
3. Задайте параметри за линията. Тип – „Линеен“. В долната част – „Покажи уравнението на диаграмата“.
4. Кликнете върху „Затвори“.

Сега данните от регресионния анализ станаха видими.

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 е изчислен и записан в избраната клетка. В нашия случай той е положителен и приблизително равен. Това говори за умерено положителенВръзки между агресивността и неувереността в себе си при първокласниците

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

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

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