Să calculăm coeficientul de corelație și covarianța pentru tipuri diferite relații ale variabilelor aleatoare.

Coeficient de corelație(criteriul de corelare Pearson, engleză Coeficientul de corelație Pearson Product Moment) determină gradul liniar relații dintre variabile aleatoare.

După cum rezultă din definiție, a calcula coeficient de corelație este necesar să se cunoască distribuția variabilelor aleatoare X și Y. Dacă distribuțiile sunt necunoscute, atunci pentru a estima coeficient de corelație folosit coeficientul de corelație al eșantionuluir ( este denumit și ca Rxy sau rxy) :

unde Sx – deviație standard un eșantion dintr-o variabilă aleatoare x, calculată prin formula:

După cum se vede din formula de calcul corelații, numitorul (produsul abaterilor standard) normalizează pur și simplu numărătorul astfel încât corelație se dovedește a fi un număr adimensional de la -1 la 1. CorelațieȘi covarianta furnizați aceleași informații (dacă sunt cunoscute abateri standard ), dar corelație mai comod de utilizat, deoarece este adimensional.

calculati coeficient de corelațieȘi covarianța eșantionuluiîn MS EXCEL nu este dificil, deoarece există funcții speciale CORREL() și COVAR() . Este mult mai dificil să vă dați seama cum să interpretați valorile obținute, cea mai mare parte a articolului este dedicată acestui lucru.

Digresiune teoretică

Amintește-ți asta corelație numită relație statistică, constând în faptul că valori diferite ale unei variabile corespund diferitelor mediu valorile altuia (cu o modificare a valorii lui X Rău Y se modifică în mod regulat). Se presupune că ambii variabilele X și Y sunt Aleatoriu valori și au o oarecare împrăștiere aleatorie în raport cu acestea Valoarea medie.

Notă. Dacă o singură variabilă, de exemplu, Y, are o natură aleatorie, iar valorile celeilalte sunt deterministe (setate de cercetător), atunci putem vorbi doar de regresie.

Astfel, de exemplu, atunci când se studiază dependența temperaturii medii anuale, nu se poate vorbi despre corelații temperatura și anul de observație și, în consecință, aplicați indicatorii corelații cu interpretarea lor respectivă.

corelațieîntre variabile poate apărea în mai multe moduri:

Prezența unei relații cauzale între variabile. De exemplu, valoarea investiției în Cercetare științifică(variabila X) și numărul de brevete primite (Y). Prima variabilă apare ca variabilă independentă (factor), al doilea - variabilă dependentă (rezultat). Trebuie amintit că dependența cantităților determină prezența unei corelații între ele, dar nu invers.
Prezența conjugării (cauză comună). De exemplu, odată cu creșterea organizației, fondul de salarizare (PAY) și costul închirierii spațiilor cresc. Evident, este greșit să presupunem că închirierea spațiilor depinde de statul de plată. Ambele variabile sunt în multe cazuri dependente liniar de numărul de angajați.
Influența reciprocă a variabilelor (când o variabilă se modifică, a doua variabilă se modifică și invers). Cu această abordare sunt admise două formulări ale problemei; Orice variabilă poate acționa atât ca variabilă independentă, cât și ca variabilă dependentă.

În acest fel, indicator de corelare arată cât de puternic relație liniarăîntre doi factori (dacă există), iar regresia vă permite să preziceți un factor pe baza celuilalt.

Corelație, ca orice altă statistică, poate fi utilă dacă este folosită corect, dar are și limitări în utilizarea sa. Dacă arată o relație liniară clar definită sau o lipsă completă de relație, atunci corelație reflecta minunat. Dar, dacă datele arată o relație neliniară (de exemplu, pătratică), prezența unor grupuri separate de valori sau valori aberante, atunci valoarea calculată coeficient de corelație poate induce în eroare (vezi fișierul exemplu).

Corelație aproape de 1 sau -1 (adică aproape de 1 în valoare absolută) indică o relație liniară puternică a variabilelor, o valoare apropiată de 0 indică nicio relație. Pozitiv corelațieînseamnă că odată cu creșterea unui indicator, celălalt, în medie, crește, iar cu un indicator negativ, scade.

Pentru a calcula coeficientul de corelație, este necesar ca variabilele potrivite să îndeplinească următoarele condiții:

numărul de variabile trebuie să fie egal cu două;
variabilele ar trebui să fie cantitative (de exemplu, frecvență, pondere, preț). Media calculată a acestor variabile are o semnificație clară: prețul mediu sau greutatea medie a pacientului. Spre deosebire de variabilele cantitative, variabilele calitative (nominale) iau valori doar dintr-un set finit de categorii (de exemplu, sex sau grup de sânge). Valorile numerice sunt comparate condiționat cu aceste valori (de exemplu, femeie - 1 și masculin - 2). Este clar că în acest caz calculul Valoarea medie, care este necesar pentru a găsi corelații, este incorectă, ceea ce înseamnă că calculul corelații;
variabilele trebuie să fie aleatoare și să aibă .

Datele bidimensionale pot avea o structură diferită. Unele dintre ele necesită abordări specifice pentru a lucra cu:

Pentru date neliniare corelație trebuie folosit cu prudență. Pentru unele probleme, poate fi util să se transforme una sau ambele variabile în așa fel încât să se obțină o relație liniară (aceasta necesită a se face o ipoteză despre tipul de relație neliniară pentru a sugera tipul de transformare dorit).
Prin intermediul diagrame de dispersieîn unele date se poate observa o variație inegală (împrăștiere). Problema cu variația inegală este că locurile cu variație mare nu numai că oferă informațiile cele mai puțin precise, dar au și cea mai mare influență în calcul. indicatori statistici. Această problemă este adesea rezolvată prin transformarea datelor, cum ar fi utilizarea unui logaritm.
În unele date, se poate observa gruparea, ceea ce poate indica necesitatea împărțirii populației în părți.
Un outlier (outlier) poate distorsiona valoarea calculată a coeficientului de corelație. O valoare anormală se poate datora întâmplării, unei erori în colectarea datelor sau poate reflecta de fapt o anumită caracteristică a relației. Deoarece valorile aberante se abate puternic de la valoarea medie, acesta are o contribuție mare la calcularea indicatorului. Adesea, statisticile sunt calculate cu și fără valori aberante.

Folosind MS EXCEL pentru a calcula corelația

Să luăm ca exemplu 2 variabile XȘi Yși în mod corespunzător, prelevarea de probe format din mai multe perechi de valori (Х i ; Y i). Pentru claritate, să construim.

Notă: Pentru mai multe informații despre trasarea diagramelor, consultați articolul. În fișierul exemplu de construire diagrame de dispersie folosit deoarece am deviat aici de la cerința ca variabila X să fie aleatorie (acest lucru simplifică generarea tipuri variate relații: construirea de tendințe și o răspândire dată). În cazul datelor reale, este necesar să folosiți o diagramă de dispersie (vezi mai jos).

Calcule corelații Să desenăm pentru diferite cazuri relația dintre variabile: liniară, pătratică iar la lipsa de comunicare.

Notă: În fișierul eșantion, puteți seta parametrii tendinței liniare (pantă, intersecție cu axa Y) și gradul de răspândire în jurul acestei linii de tendință. De asemenea, puteți ajusta setările de dependență pătratică.

În fișierul exemplu de construire diagrame de dispersieîn cazul absenţei dependenţei de variabile se utilizează o diagramă de dispersie. În acest caz, punctele de pe diagramă sunt aranjate sub forma unui nor.

Notă: Rețineți că prin schimbarea scarii diagramei de-a lungul axei verticale sau orizontale, norului de puncte i se poate da aspectul unei linii verticale sau orizontale. Este clar că în acest caz variabilele vor rămâne independente.

După cum am menționat mai sus, pentru a calcula coeficient de corelațieîn MS EXCEL există funcții CORREL(). De asemenea, puteți utiliza funcția similară PEARSON(), care returnează același rezultat.

Pentru a vă asigura că calculele corelații sunt produse de funcția CORREL() conform formulelor de mai sus, fișierul exemplu arată calculul corelații folosind formule mai detaliate:

=COVARIANȚĂ.Y(B28:B88;D28:D88)/STDEV.Y(B28:B88)/STDEV.Y(D28:D88)

=COVARIAȚIE.V(B28:B88;D28:D88)/STDEV.V(B28:B88)/STDEV.V(D28:D88)

Notă: pătrat coeficient de corelație r este coeficient de determinare R2, care este calculat la construirea liniei de regresie folosind funcția QVPIRSON(). Valoarea lui R2 poate fi afișată și pe diagramă de dispersie, prin construirea unei tendințe liniare folosind funcționalitatea standard a MS EXCEL (selectați graficul, selectați fila Aspect, apoi în grup Analiză apasa butonul linie de tendințăși alegeți Aproximație liniară). Pentru mai multe informații despre trasarea unei linii de tendință, consultați, de exemplu, .

Folosind MS EXCEL pentru a calcula covarianța

covarianta este apropiat ca semnificație de (este și o măsură a dispersiei), cu diferența că este definit pentru 2 variabile și dispersie- pentru un. Prin urmare, cov(x;x)=VAR(x).

Pentru a calcula covarianța în MS EXCEL (începând cu versiunea 2010) se folosesc funcțiile COVARIATION.G() și COVARIATION.V(). În primul caz, formula de calcul este similară cu cea de mai sus (terminând .G reprezintă Populația), în al doilea - în locul factorului 1/n se folosește 1/(n-1), adică. final .ÎN reprezintă Probă.

Notă: Funcția COVAR(), care este prezentă în MS EXCEL din versiunile anterioare, este similară cu funcția COVARIANCE.G().

Notă: Funcțiile CORREL() și COVAR() din versiunea în limba engleză sunt reprezentate ca CORREL și COVAR. Funcțiile COVARIANCE.G() și COVARIANCE.V() ca COVARIANCE.P și COVARIANCE.S.

Formule suplimentare de calcul covarianţele:

=SUMA PRODUS(B28:B88-MEDIE(B28:B88),(D28:D88-MEJIE(D28:D88)))/NUMĂRĂ(D28:D88)

=SUMA PRODUS(B28:B88-MEDIE(B28:B88),(D28:D88))/NUMĂRĂ(D28:D88)

=SUMĂPRODUS(B28:B88;D28:D88)/NUMĂRĂ(D28:D88)-MEDIE(B28:B88)*MEDIA(D28:D88)

Aceste formule folosesc proprietatea covarianţele:

Dacă variabile XȘi y sunt independente, atunci covarianța lor este 0. Dacă variabilele nu sunt independente, atunci varianța sumei lor este:

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

DAR dispersie diferența lor este

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

Evaluarea semnificației statistice a coeficientului de corelație

Pentru a testa ipoteza, trebuie să cunoaștem distribuția variabilei aleatoare, i.e. coeficient de corelație r. De obicei, testarea ipotezelor se efectuează nu pentru r, ci pentru o variabilă aleatoare t r:

care are n-2 grade de libertate.

Dacă valoarea calculată a variabilei aleatoare |t r | mai mare decât valoarea critică t α,n-2 (α-specificată), atunci ipoteza nulă respins (corelarea valorilor este semnificativă statistic).

Pachet de analiză suplimentară

B pentru a calcula covarianța și corelația există instrumente cu același nume analiză.

După apelarea instrumentului, apare o casetă de dialog care conține următoarele câmpuri:

interval de intrare: trebuie să introduceți o legătură către un interval cu date inițiale pentru 2 variabile
Gruparea: În general, datele brute sunt introduse în 2 coloane
Etichete pe prima linie: dacă este bifat, atunci interval de intrare trebuie să conțină titluri de coloane. Este recomandat să bifați caseta astfel încât rezultatul Add-in-ului să conțină coloane informative
interval de ieșire: Intervalul de celule în care vor fi plasate rezultatele calculului. Este suficient să specificați celula din stânga sus a acestui interval.

Add-in-ul returnează valorile de corelație și covarianță calculate (pentru covarianță, sunt calculate și variațiile ambelor variabile aleatoare).

În cazul unei variabile aleatoare multidimensionale (vector aleatoriu), caracteristica răspândirii componentelor sale și a relațiilor dintre acestea este matricea de covarianță.

Matricea de covarianță este definită ca așteptarea matematică a produsului dintre un vector aleator centrat și același vector, dar transpus:

Unde

Matricea de covarianță are forma

unde diagonalele sunt varianțele coordonatelor vectorului aleator o n \u003d D Xi, o 22 \u003d D X2, o kk = D Xk , iar elementele rămase sunt covarianțele dintre coordonate

° 12 \u003d M "x i x 2 j a 1 * \u003d M-jc, **\u003e

Matricea de covarianță este o matrice simetrică, adică.

De exemplu, luați în considerare matricea de covarianță a unui vector bidimensional

În mod similar, matricea de covarianță este obținută pentru orice vector /^-dimensional.

Dispersiile de coordonate pot fi reprezentate ca

unde Gi,C2,...,0? - abaterile pătratice medii ale coordonatelor vectoriale aleatorii.

Coeficientul de corelație este, după cum știți, raportul dintre covarianță și produsul abaterilor standard:

După normalizarea cu ultimul raport al termenilor matricei de covarianță, se obține matricea de corelație

care este simetric si nenegativ definit.

Un analog multidimensional al dispersiei unei variabile aleatoare este dispersia generalizată, care este înțeleasă ca valoare a determinantului matricei de covarianță

Un alt caracteristică comună gradul de dispersie al unei variabile aleatoare multivariate este urma matricei de covarianta

unde Ск - elemente diagonale ale matricei de covarianță.

Adesea în multidimensional analize statistice se foloseşte distribuţia normală.

O generalizare a densității normale de probabilitate în cazul unui vector aleator ^-dimensional este funcția

unde q = (pj, q 2 , M^) m - vector coloană a așteptărilor matematice;

|X| - determinant al matricei de covarianta X;

1 - matrice de covarianță inversă.

Matricea X -1 , inversă dimensiunii matricei X ph p, poate fi obtinut căi diferite. Una dintre ele este metoda Jordan-Gauss. În acest caz, ecuația matriceală

Unde X- vector coloană de variabile, al căror număr este egal cu i; b- vector coloană i-dimensional al părților din dreapta.

Înmulțiți ecuația (6.21) din stânga cu matricea inversă ХГ 1:

De la lucrare matrice inversă dă matricea identităţii E, apoi

Dacă în schimb b luați vectorul unitar

apoi produsul X -1 -e x dă prima coloană a matricei inverse. Dacă luăm al doilea vector unitar

apoi produsul E 1 e 2 dă prima coloană a matricei inverse și așa mai departe. Astfel, rezolvând succesiv ecuațiile

folosind metoda Jordan-Gauss, obținem toate coloanele matricei inverse.

O alta metoda de obtinere a unei matrice inversa matricei E este legata de calculul complementelor algebrice A tJ .= (/= 1, 2,..., P; j = 1, 2, ..., P) la elementele matricei date E, înlocuindu-le în locul elementelor matricei E și transportând o astfel de matrice:

Matricea inversă se obține după împărțirea elementelor ÎN la determinantul matricei E:

O caracteristică importantă a obținerii matricei inverse în acest caz este că matricea de covarianță E este slab condiționată. Acest lucru duce la faptul că pot apărea erori destul de grave la inversarea unor astfel de matrici. Toate acestea necesită asigurarea preciziei necesare a procesului de calcul sau utilizarea unor metode speciale la calcularea unor astfel de matrici.

Exemplu. Scrieți o expresie a densității de probabilitate pentru o variabilă aleatoare bidimensională distribuită normal (X v X 2)

cu condiția ca așteptările, variațiile și covarianțele matematice ale acestor mărimi să aibă următoarele valori:

Soluţie. Matricea de covarianță inversă pentru matricea (6.19) poate fi obținută folosind următoarea expresie a matricei inverse pentru matricea X:

unde A este determinantul matricei X.

A și, L 12, A 21, A 22- adunări algebrice la elementele corespunzătoare ale matricei X.

Atunci pentru matricea ]r- ! obținem expresia

Deoarece 12 \u003d 01O2P și ° 2i \u003d a 2 a iP\u003e a i2 a 2i \u003d cyfst | p, atunci

Să găsim produsul

Funcția de densitate de probabilitate poate fi scrisă ca

Înlocuind datele inițiale, obținem următoarea expresie pentru funcția de densitate de probabilitate

Să luăm în considerare tehnica de calcul a covarianței și a corelației randamentelor titlurilor de valoare folosind un exemplu.

Rentabilitatea hârtiei X timp de cinci ani a fost de 20%, 25%, 22%, 28%, respectiv 24%. Randament pe hârtie F: 24%, 28%, 25%, 27%, 23%. Determinați covarianța randamentelor stocurilor.

Să rezolvăm problema în două moduri.

a) Imprimați către ordine cronologicaîn celulele cu Al nr A5, valorile de randament ale hârtiei X, iar în celulele de la B1 la B5, randamentul hârtiei F. Vom obține soluția în celula C1, așa că trecem cu mouse-ul peste ea și facem clic cu mouse-ul. Tipărim în celula C1 formula:

și apăsați tasta Enter. În celula C1, a apărut o soluție la problemă - numărul 3.08, adică. covarianța eșantionului pentru exemplul nostru.

b) Covarianța poate fi calculată utilizând programul „Function Wizard”. Pentru a face acest lucru, treceți cu mouse-ul peste pictograma A din bara de instrumente și faceți clic cu mouse-ul. Apare fereastra Function Wizard. În câmpul din stânga ("Categorie"), mutați cursorul peste linia "Statistică" și faceți clic cu mouse-ul. Linia a fost evidențiată cu albastru, iar în câmpul din dreapta al ferestrei („Funcție”) a apărut o listă de funcții statistice. Plasați cursorul pe linia „KOVAR” și faceți clic pe butonul stâng al mouse-ului. Linia este evidențiată cu albastru. Plasați cursorul pe butonul OK și faceți clic cu mouse-ul. A apărut fereastra „KOVAR”. Există două rânduri în fereastră, care se numesc „Matrice 1” și „Matrice 2”. În prima linie introducem numerele de celule de la A1 la A5. Pentru a face acest lucru, mutați cursorul peste semnul 3 situat în partea dreaptă a primei linii și faceți clic cu mouse-ul. Fereastra „KOVAR” s-a transformat într-un câmp de prima linie. Plasați cursorul pe celula A1, apăsați butonul stâng al mouse-ului și, ținând-l apăsat, mutați cursorul în jos la celula A5 și eliberați tasta. În câmpul de linie a apărut intrarea A1:A5. Plasați din nou peste semn și faceți clic pe mouse. A apărut fereastra extinsă „KOVAR”. Introducem numerele de celule cu Bl nr B5 pe a doua linie. Pentru a face acest lucru, mutați cursorul peste semnul 5J din a doua linie și faceți clic cu mouse-ul. Plasați cursorul pe celula B1, apăsați butonul stâng al mouse-ului și, ținând-l apăsat, mutați cursorul în jos la celula B5, eliberați tasta. În câmpul de linie a apărut intrarea B1:B5. Treceți cursorul peste butonul 3| și faceți clic pe mouse. A apărut fereastra extinsă „KOVAR”. Plasați cursorul pe butonul OK și faceți clic cu mouse-ul. Numărul 3.08 a apărut în celula C1.

Determinați coeficientul de corelație al randamentelor titlurilor pentru condițiile exemplului 1. Soluție. Să rezolvăm problema în două moduri.

a) Tipărim în ordine cronologică în celulele cu Al nr A5 valorile de randament ale hârtiei X, iar în celulele de la B1 la B5 - randamentul hârtiei F. Obținem soluția în celula C1, așa că trecem cu mouse-ul peste ea și faceți clic cu mouse-ul. Tipărim în celula C1 formula:

și apăsați tasta Enter. În celula C1, a apărut o soluție la problemă - numărul 0,612114.

b) Corelația poate fi calculată folosind „Asistentul de funcții”. Pentru a face acest lucru, selectați pictograma l din bara de instrumente cu cursorul și faceți clic cu mouse-ul. Apare fereastra Function Wizard. În câmpul din stânga ("Categorie"), selectați linia "Statistică" cu cursorul și faceți clic cu mouse-ul. O listă de funcții statistice a apărut în câmpul din dreapta ferestrei ("Funcție"). Selectați linia „CORREL” cu cursorul și faceți clic cu mouse-ul. Linia este evidențiată cu albastru. Plasați cursorul pe butonul OK și faceți clic cu mouse-ul. A apărut fereastra „CORREL”. Există două rânduri în fereastră, care se numesc „Matrice 1” și „Matrice 2”. În prima linie introducem numerele de celule cu Al nr A5. Pentru a face acest lucru, mutați cursorul peste semnul ZP din dreapta primei linii și faceți clic cu mouse-ul. Fereastra „CORREL” a devenit primul câmp de linie. Plasați cursorul pe celula A1, apăsați butonul stâng al mouse-ului și, ținând-l apăsat, mutați cursorul în jos la celula A5 și eliberați tasta. În câmpul de linie a apărut intrarea A1:A5. Plasați din nou cursorul pe semnul U și faceți clic cu mouse-ul. A apărut fereastra extinsă „CORREL”. Introducem numerele de celule cu Bl nr B5 pe a doua linie. Pentru a face acest lucru, mutați cursorul peste semnul W din a doua linie și faceți clic cu mouse-ul. Plasați cursorul pe celula B1, apăsați butonul stâng al mouse-ului și, ținând-l apăsat, mutați cursorul în jos la celula B5, eliberați tasta. În câmpul de linie a apărut intrarea B1:B5. Mutați cursorul peste butonul Щ și faceți clic cu mouse-ul. A apărut fereastra extinsă „CORREL”. Plasați cursorul pe butonul OK și faceți clic cu mouse-ul. Numărul 0,612114 a apărut în celula C1.

În exemplele 1 și 2, am calculat covarianța și corelația randamentelor a două titluri din portofoliu. Dacă portofoliul include mai multe titluri, atunci covarianțele și corelațiile randamentelor acestora pot fi calculate în perechi folosind metoda descrisă mai sus, dar aceasta este o variantă laborioasă de rezolvare a problemei. Excel are un pachet special de analiză a datelor care vă permite să rezolvați rapid o astfel de problemă pentru un numar mare hârtii. Luați în considerare calculul covarianței și corelațiilor cu ajutorul acestuia.

Știi că: Brokerul Forex „NPBFX” aduce absolut toate tranzacțiile clienților săi furnizorilor de lichidități (pe piața interbancară), lucrând la Tehnologii STP/NDD(Procesare directă - prin procesare tranzacție / Non Dealing Desk).

Este posibil ca „Analysis Pack” să nu fie instalat. Apoi trebuie instalat. Pentru a face acest lucru, treceți cu mouse-ul peste meniul „Instrumente” și faceți clic pe butonul stâng al mouse-ului. A apărut un meniu derulant. Selectați comanda „Add-Ins” cu cursorul și faceți clic pe butonul stâng al mouse-ului. Apare caseta de dialog Add-ins. Mutați cursorul peste caseta din stânga liniei „Pachet de analiză” și faceți clic pe butonul stâng al mouse-ului. Există o bifă în casetă. Plasați cursorul pe butonul OK și faceți clic cu mouse-ul. „Pachetul de analiză” este instalat. Luați în considerare definiția covarianței și a corelațiilor pentru mai multe titluri folosind un exemplu.

Exemplul 3: Calcularea covarianței

Există un eșantion de date privind randamentele titlurilor B, C și D pentru zece perioade. Tipărim valorile de randament pentru hârtie B în celulele de la B1 la B10, hârtie C de la C1 la CJ și hârtie D de la D1 la D10, așa cum se arată în fig. 1.8. Treceți cu mouse-ul peste meniul „Instrumente” și faceți clic pe butonul stâng al mouse-ului. A apărut un meniu derulant. Plasați cursorul pe linia „Analiza datelor” și faceți clic pe butonul stâng al mouse-ului. Apare fereastra de analiză a datelor. Plasați cursorul pe linia „Covarianță” și faceți clic pe butonul stâng al mouse-ului. Linia este evidențiată cu albastru. Plasați cursorul pe butonul OK și faceți clic cu mouse-ul. Apare fereastra „Covarianza” (vezi Fig. 1.10).

Plasați cursorul pe semnul 3 din dreapta câmpului liniei „Interval de intrare” și faceți clic cu mouse-ul. Fereastra „Covarianță” s-a prăbușit într-o casetă de rând. Plasați cursorul pe celula B1, apăsați butonul stâng al mouse-ului și, ținând-l apăsat, trageți în celula D10. În linie a apărut intrarea $B$1:$D$10. Deplasați din nou cursorul peste semn și faceți clic cu mouse-ul. Apare fereastra de covarianță extinsă. Datele sunt grupate pe coloane. Prin urmare, dacă nu există niciun punct în fereastra rotundă din stânga inscripției „în coloane”, atunci îndreptați cursorul spre el și faceți clic pe butonul stâng al mouse-ului. În fereastră va apărea un punct. Mai jos este linia „Interval de ieșire”. Ar trebui să existe un punct în fereastra rotundă din stânga inscripției. Dacă nu este acolo, atunci mutați cursorul peste această linie și faceți clic pe butonul stâng al mouse-ului. În fereastră va apărea un punct. Plasați cursorul pe semnul 3 din dreapta câmpului liniei „Interval de ieșire” și faceți clic cu mouse-ul. Fereastra „Covarianță” a devenit un câmp șir. Să luăm celula A12 ca început al intervalului de ieșire. Prin urmare, trecem cu mouse-ul peste el și apăsăm butonul stâng al mouse-ului. Intrarea $A$12 a apărut în câmpul șir. Plasați din nou cursorul pe semnul 3 și faceți clic cu mouse-ul. Fereastra „Covarianță” s-a extins. Plasați cursorul pe butonul OK și faceți clic cu mouse-ul. Soluția problemei a apărut pe foaie așa cum se arată în Fig. 1.11. În blocul B13 până la D15, este prezentată matricea de covarianță. De-a lungul diagonalei sale, i.e. în celulele B13, C14 și B15 există dispersii ale titlurilor B, C și, respectiv, D, în celulele rămase - covarianța randamentelor titlurilor de valoare: în celula B14 covarianța randamentelor titlurilor B și C, în B15 - titlurilor B și D, în C15 - titluri de valoare C și D .

Exemplul 4 Calcularea corelațiilor

Există un eșantion de date privind randamentele a trei titluri de valoare - B, C și D - pentru zece perioade. Ca și în problema 3, imprimăm valorile de randament pentru hârtie B în celulele de la B1 la B10, hârtie C de la C1 la C10 și hârtie D de la D1 la D10 (Fig. 1.9). Treceți cu mouse-ul peste meniul „Instrumente” și faceți clic pe butonul stâng al mouse-ului. A apărut un meniu derulant. Plasați cursorul pe linia „Analiza datelor” și faceți clic pe butonul stâng al mouse-ului. Apare fereastra de analiză a datelor. Plasați cursorul pe linia „Corelație” și faceți clic pe butonul stâng al mouse-ului. Linia este evidențiată cu albastru. Plasați cursorul pe butonul OK și faceți clic cu mouse-ul. A apărut fereastra de corelare (este similară ca structură cu fereastra „Covarianță”). Plasați cursorul pe semnul 3 din dreapta câmpului liniei „Interval de intrare” și faceți clic cu mouse-ul. Fereastra „Corelație” s-a prăbușit într-o casetă de linie. Plasați cursorul pe celula B1, apăsați butonul stâng al mouse-ului și, ținând-l apăsat, mutați cursorul în celula D10. În linie a apărut intrarea $B$1:$D$10. Deplasați din nou cursorul peste semn și faceți clic cu mouse-ul. Apare fereastra extinsă „Corelație”. Datele sunt grupate pe coloane. Prin urmare, dacă nu există niciun punct în fereastra rotundă din stânga inscripției „în coloane”, atunci îndreptați cursorul spre el și faceți clic pe butonul stâng al mouse-ului. În fereastră va apărea un punct. Mai jos este linia „Interval de ieșire”. Ar trebui să existe un punct în fereastra rotundă din stânga inscripției. Dacă nu este acolo, atunci mutați cursorul peste această linie și faceți clic pe butonul stâng al mouse-ului. În fereastră va apărea un punct. Plasați cursorul pe semnul 3 din dreapta câmpului liniei „Interval de ieșire” și faceți clic cu mouse-ul. Fereastra „Corelație” a devenit un câmp șir. Să luăm celula A12 ca început al intervalului de ieșire. Prin urmare, trecem cu mouse-ul peste el și apăsăm butonul stâng al mouse-ului. Intrarea $A$12 a apărut în câmpul șir. Plasați din nou cursorul pe semnul 3 și faceți clic cu mouse-ul. Fereastra „Corelație” s-a extins. Plasați cursorul pe butonul OK și faceți clic cu mouse-ul. Soluția problemei a apărut pe foaie așa cum se arată în Figura 1.12. Blocul de la B13 la D15 prezintă matricea de corelație. De-a lungul diagonalei sale, i.e. în celulele B13, C14 și D15 există unități, în celulele rămase - corelații de randamente ale titlurilor de valoare: în celula B14 corelarea randamentelor titlurilor de valoare B și C, în B15 - titluri de valoare B și D, în C15 - titluri de valoare C și D .

Conţinut

Acest articol descrie sintaxa formulei și utilizarea funcției COVARIAȚIE.Gîn Microsoft Excel.

Returnează covarianța populației, media aritmetică a produselor variațiilor pentru fiecare pereche de puncte de date din două seturi de date. Covarianța este utilizată pentru a determina relația dintre două seturi de date. De exemplu, puteți verifica dacă un nivel de venit mai ridicat corespunde cu mai mult nivel inalt educaţie.

Sintaxă

COVARIANCE.G(matrice1,matrice2)

Sintaxa funcției COVARIANCE.G are următoarele argumente:

Matrice1 este un argument necesar. Primul interval de celule cu numere întregi.

Matrice 2 este un argument necesar. Al doilea interval de celule cu numere întregi.

Remarci

Exemplu

Copiați eșantionul de date din următorul tabel și inserați-l în celula A1 a unei noi foi Excel. Pentru a afișa rezultatele formulei, selectați-le și apăsați F2 urmat de ENTER. Modificați lățimea coloanelor, dacă este necesar, pentru a vedea toate datele.

Cât de des ați auzit afirmații care spun că un fenomen este corelat cu altul?

„Creșterea ridicată este corelată cu educație bunăși fericire, au constatat experții de la serviciul sociologic Gallup.

„Prețul petrolului este corelat cu cursurile de schimb”.

„Durerile musculare după exercițiu nu se corelează cu hipertrofia fibrelor musculare”.

Avem impresia că conceptul de „corelare” a devenit utilizat pe scară largă nu numai în știință, ci și în Viata de zi cu zi. Corelația reflectă gradul dependență liniarăîntre două evenimente aleatorii. Deci, când prețul petrolului începe să scadă, dolarul față de rublă începe să crească.

Din toate cele de mai sus, putem concluziona că atunci când descriem variabile aleatoare bidimensionale, nu există suficiente caracteristici cunoscute, ca așteptare matematică, varianță, abatere standard. Prin urmare, două caracteristici foarte importante sunt adesea folosite pentru a le descrie: covariantaȘi corelație.

covarianta

covarianta$cov\left(X,\ Y\right)$ ale variabilelor aleatoare $X$ și $Y$ este așteptarea produsului dintre variabile aleatoare $XM\left(X\right)$ și $YM\left(Y\ dreapta)$, adică:

$$cov\left(X,\ Y\right)=M\stanga(\left(XM\left(X\dreapta)\dreapta)\left(YM\left(Y\dreapta)\dreapta)\dreapta). $$

Poate fi convenabil să se calculeze covarianța variabilelor aleatoare $X$ și $Y$ folosind următoarea formulă:

$$cov\left(X,\ Y\right)=M\stanga(XY\dreapta)-M\left(X\dreapta)M\left(Y\dreapta),$$

care poate fi obținută din prima formulă folosind proprietățile așteptării matematice. Enumerăm principalele proprietăți de covarianță.

1 . Covarianța unei variabile aleatoare cu ea însăși este varianța acesteia.

$$cov\left(X,\ X\right)=D\left(X\dreapta).$$

2 . Covarianța este simetrică.

$$cov\left(X,\ Y\right)=cov\left(Y,\ X\dreapta).$$

3 . Dacă variabilele aleatoare $X$ și $Y$ sunt independente, atunci:

$$cov\left(X,\ Y\right)=0.$$

4 . Factorul constant poate fi scos din semnul de covarianță.

$$cov\left(cX,\ Y\right)=cov\left(X,\ cY\right)=c\cdot cov\left(X,\Y\right).$$

5 . Covarianța nu se va modifica dacă la una dintre variabilele aleatoare se adaugă o valoare constantă (sau două deodată):

$$cov\left(X+c,\ Y\right)=cov\left(X,\ Y+c\right)=cov\left(X+x,\ Y+c\right)=cov\left( X,\ Y\dreapta).$$

6 . $cov\left(aX+b,\ cY+d\right)=ac\cdot cov\left(X,\Y\right)$.

7 . $\left|cov\left(X,\ Y\right)\right|\le \sqrt(D\left(X\right)D\left(Y\right))$.

8 . $\left|cov\left(X,\ Y\right)\right|=\sqrt(D\left(X\right)D\left(Y\right))\Leftrightarrow Y=aX+b$.

9 . Varianța sumei (diferenței) variabilelor aleatoare este egală cu suma varianțelor acestora plus (minus) de două ori covarianța acestor variabile aleatoare:

$$D\left(X\pm Y\right)=D\stanga(X\dreapta)+D\left(Y\dreapta)\pm 2cov\left(X,\Y\dreapta).$$

Exemplul 1 . Este dat tabelul de corelare a vectorului aleator $\left(X,\Y\right)$. Calculați covarianța $cov\left(X,\Y\right)$.

$\begin(matrice)(|c|c|)
\hline

\hline
-2 & 0,1 & 0 & 0,2 \\
\hline
0 și 0,05 și p_(22) și 0 \\
\hline
1 & 0 & 0,2 & 0,05 \\
\hline
7 & 0,1 & 0 & 0,1 \\
\hline
\end(matrice)$

Evenimentele $\left(X=x_i,\ Y=y_j\right)$ formează un grup complet de evenimente, astfel încât suma tuturor probabilităților $p_(ij)$ date în tabel trebuie să fie egală cu 1. Atunci $0, 1+0+0 ,2+0.05+p_(22)+0+0+0.2+0.05+0.1+0+0.1=1$, deci $p_(22)=0.2$.

$\begin(matrice)(|c|c|)
\hline
X\backslash Y & -6 & 0 & 3 \\
\hline
-2 & 0,1 & 0 & 0,2 \\
\hline
0 & 0,05 & 0,2 & 0 \\
\hline
1 & 0 & 0,2 & 0,05 \\
\hline
7 & 0,1 & 0 & 0,1 \\
\hline
\end(matrice)$

Folosind formula $p_(i) =\sum _(j)p_(ij) $, găsim seria de distribuție a variabilei aleatoare $X$.

$\begin(matrice)(|c|c|)
\hline
X & -2 & 0 & 1 & 7 \\
\hline
p_i & 0,3 & 0,25 & 0,25 & 0,2 \\
\hline
\end(matrice)$

$$M\left(X\right)=\sum^n_(i=1)(x_ip_i)=-2\cdot 0.3+0\cdot 0.25+1\cdot 0.25+7\cdot 0 ,2=1.05.$ $

$$D\left(X\right)=\sum^n_(i=1)(p_i(\left(x_i-M\left(X\right)\right))^2)=0,3\cdot ( \left (-2-1.05\right))^2+0.25\cdot (\left(0-1.05\right))^2+0.25\cdot (\left(1-1, 05\right))^2+$$

$$+\ 0,2\cdot (\left(7-1,05\right))^2=10,1475.$$

$$\sigma \left(X\right)=\sqrt(D\left(X\right))=\sqrt(10,1475)\aproximativ 3,186.$$

Folosind formula $q_(j) =\sum _(i)p_(ij) $, găsim seria de distribuție a variabilei aleatoare $Y$.

$\begin(matrice)(|c|c|)
\hline
Y & -6 & 0 & 3 \\
\hline
p_i & 0,25 & 0,4 & 0,35 \\
\hline
\end(matrice)$

$$M\left(Y\right)=\sum^n_(i=1)(y_ip_i)=-6\cdot 0,25+0\cdot 0,4+3\cdot 0,35=-0,45 .$$

$$D\left(Y\right)=\sum^n_(i=1)(p_i(\left(y_i-M\left(Y\right)\right))^2)=0,25\cdot ( \left (-6+0,45\right))^2+0,4\cdot (\left(0+0,45\right))^2+0,35\cdot (\left(3+0, 45\right))^2=11,9475. $$

$$\sigma \left(Y\right)=\sqrt(D\left(Y\right))=\sqrt(11.9475)\aproximativ 3.457.$$

Deoarece $P\left(X=-2,\ Y=-6\right)=0,1\ne 0,3\cdot 0,25$, variabilele aleatoare $X,\ Y$ sunt dependente.

Definim covarianta $cov\ \left(X,\ Y\right)$ a variabilelor aleatoare $X,\ Y$ prin formula $cov\left(X,\ Y\right)=M\left(XY\right) )-M\ stânga(X\dreapta)M\stanga(Y\dreapta)$. Valorea estimata produsul variabilelor aleatoare $X,\ Y$ este egal cu:

$$M\left(XY\right)=\sum_(i,\j)(p_(ij)x_iy_j)=0,1\cdot \left(-2\right)\cdot \left(-6\right) +0.2\cdot \left(-2\right)\cdot 3+0.05\cdot 1\cdot 3+0.1\cdot 7\cdot \left(-6\right)+0.1\cdot 7\cdot 3=-1.95. $$

Apoi $cov\left(X,\ Y\right)=M\left(XY\right)-M\left(X\right)M\left(Y\right)=-1,95-1,05\cdot \left(- 0,45\right)=-1,4775.$ Dacă variabilele aleatoare sunt independente, atunci covarianța lor este zero. În cazul nostru $cov(X,Y)\ne 0$.

Corelație

Coeficient de corelație variabilele aleatoare $X$ și $Y$ se numesc număr:

$$\rho \left(X,\ Y\right)=((cov\left(X,\ Y\right))\peste (\sqrt(D\left(X\right)D\left(Y\right) )))).$$

Enumerăm principalele proprietățile coeficientului de corelație.

1 . $\rho \left(X,\ X\right)=1$.

2 . $\rho \left(X,\ Y\right)=\rho \left(Y,\ X\right)$.

3 . $\rho \left(X,\ Y\right)=0$ pentru variabile aleatoare independente $X$ și $Y$.

4 . $\rho \left(aX+b,\ cY+d\right)=(sgn \left(ac\right)\rho \left(X,\ Y\right)\ )$, unde $(sgn \left( ac\right)\ )$ este semnul produsului $ac$.

5 . $\left|\rho \left(X,\ Y\right)\right|\le 1$.

6 . $\left|\rho \left(X,\ Y\right)\right|=1\Leftrightarrow Y=aX+b$.

Mai devreme s-a spus că coeficientul de corelație $\rho \left(X,\ Y\right)$ reflectă gradul de relație liniară dintre două variabile aleatoare $X$ și $Y$.

Pentru $\rho \left(X,\ Y\right)>0$, putem concluziona că, ca variabilă aleatorie $X$ valoare aleatorie$Y$ tinde să crească. Aceasta se numește corelație pozitivă. De exemplu, înălțimea și greutatea unei persoane sunt corelate pozitiv.

Pentru $\rho \left(X,\Y\right)<0$ можно сделать вывод о том, что с ростом случайной величины $X$ случайная величина $Y$ имеет тенденцию к уменьшению. Это называется отрицательной корреляционной зависимостью. Например, температура и время сохранности продуктов питания связаны между собой отрицательной корреляционной зависимостью.

Pentru $\rho \left(X,\ Y\right)=0$ variabilele aleatoare $X$ și $Y$ sunt numite necorelate. De remarcat faptul că necorelarea variabilelor aleatoare $X$ și $Y$ nu înseamnă independența lor statistică, înseamnă doar că nu există o relație liniară între ele.

Exemplul 2 . Să determinăm coeficientul de corelație $\rho \left(X,\ Y\right)$ pentru variabila aleatoare bidimensională $\left(X,\Y\right)$ din Exemplul 1.

Coeficientul de corelație al variabilelor aleatoare $X,\ Y$ este $r_(XY) =(cov(X,Y)\over \sigma (X)\sigma (Y)) =(-1.4775\over 3.186\cdot 3.457 ) =-0,134.$ Deoarece $r_(XY)<0$, то с ростом $X$ случайная величина $Y$ имеет тенденцию к уменьшению (отрицательная корреляционная зависимость).