Come viene eseguita la correlazione in Excel? Determinazione del coefficiente di correlazione multipla in MS Excel

Il coefficiente di correlazione viene utilizzato quando è necessario determinare il valore del rapporto tra valori. Successivamente, questi dati vengono specificati in una tabella definita come matrice di correlazione. Utilizzando Microsoft Excel è possibile calcolare la correlazione.

Il coefficiente di correlazione è determinato da alcuni dati. Se il livello dell'indicatore è compreso tra 0 e 0,3, in questo caso non esiste alcuna connessione. Se l'indicatore è compreso tra 0,3 e 0,5, questa è una connessione debole. Se l'indicatore raggiunge 0,7, la connessione è nella media. Può essere definito alto quando l'indicatore raggiunge 0,7-0,9. Se l'indicatore è 1, questa è la connessione più forte.

Il primo passo è connettere il pacchetto di analisi dei dati. Senza la sua attivazione non è possibile eseguire ulteriori azioni. Puoi collegarlo aprendo la sezione “Home” e selezionando “Opzioni” dal menu.

Successivamente, si aprirà una nuova finestra. In esso è necessario selezionare "Componenti aggiuntivi" e nel campo di controllo delle impostazioni selezionare "Componenti aggiuntivi Excel" dagli elementi dell'elenco
Dopo aver avviato la finestra dei parametri, utilizza il menu verticale a sinistra per accedere alla sezione "Componenti aggiuntivi". Successivamente, fai clic su "Vai".

Dopo questi passaggi puoi iniziare a lavorare. È stata creata una tabella con i dati e utilizzando il suo esempio troveremo il coefficiente di correlazione multipla.
Innanzitutto, apri la sezione “Dati” e seleziona “Analisi dei dati” tra gli strumenti.

Si aprirà una finestra speciale con strumenti di analisi. Seleziona "Correlazione" e conferma l'azione.

Una nuova finestra con i parametri apparirà davanti all'utente. L'intervallo di input è un intervallo di valori nella tabella. Puoi impostarlo manualmente oppure selezionando i dati che verranno visualizzati in un apposito campo. Puoi anche separare gli elementi della tabella. Realizzeremo l'output sulla pagina corrente, il che significa che nelle impostazioni dei parametri di output selezioniamo "Intervallo di output". Successivamente confermiamo l'azione.

Avviso! La soluzione al tuo problema specifico sarà simile a questo esempio, includendo tutte le tabelle e i testi esplicativi seguenti, ma tenendo conto dei tuoi dati iniziali...

Compito:
Esiste un campione correlato di 26 coppie di valori (x k,y k):

K	1	2	3	4	5	6	7	8	9	10
xk	25.20000	26.40000	26.00000	25.80000	24.90000	25.70000	25.70000	25.70000	26.10000	25.80000
sì k	30.80000	29.40000	30.20000	30.50000	31.40000	30.30000	30.40000	30.50000	29.90000	30.40000

K	11	12	13	14	15	16	17	18	19	20
xk	25.90000	26.20000	25.60000	25.40000	26.60000	26.20000	26.00000	22.10000	25.90000	25.80000
sì k	30.30000	30.50000	30.60000	31.00000	29.60000	30.40000	30.70000	31.60000	30.50000	30.60000

K	21	22	23	24	25	26
xk	25.90000	26.30000	26.10000	26.00000	26.40000	25.80000
sì k	30.70000	30.10000	30.60000	30.50000	30.70000	30.80000

Necessario per calcolare/tracciare:
- coefficiente di correlazione;
- verificare l'ipotesi di dipendenza delle variabili casuali X e Y, ad un livello di significatività pari a α = 0,05;
- coefficienti dell'equazione di regressione lineare;
- diagramma di dispersione (campo di correlazione) e grafico della linea di regressione;

SOLUZIONE:

1. Calcolare il coefficiente di correlazione.

Il coefficiente di correlazione è un indicatore della reciproca influenza probabilistica di due variabili casuali. Coefficiente di correlazione R può prendere valori da -1 Prima +1 . Se il valore assoluto è più vicino a 1 , allora questa è la prova di una forte connessione tra le quantità, e se più vicina a 0 - quindi questo indica una connessione debole o la sua assenza. Se valore assoluto R uguale a uno, allora possiamo parlare di una connessione funzionale tra quantità, cioè una quantità può essere espressa attraverso un'altra utilizzando una funzione matematica.

Il coefficiente di correlazione può essere calcolato utilizzando le seguenti formule:

N

Σ

k = 1

(x k -M x) 2 , σy2 =

Mx

=

1 N

N Σ k = 1

xk,

Mio

=

o per formula Rx,y = M xy - M x M y SxSy (1.4), dove: Mx = 1 N N Σ k = 1 xk, Mio = 1 N N Σ k = 1 sì, ok, Mxy = 1 N N Σ k = 1 x k y k (1.5) Sx2 = 1 N N Σ k = 1 xk2 - Mx2, S y 2 = 1 N N Σ k = 1 sì k 2 - Mio sì 2 (1.6) In pratica, la formula (1.4) viene utilizzata più spesso per calcolare il coefficiente di correlazione perché richiede meno calcoli. Tuttavia, se la covarianza è stata precedentemente calcolata cov(X,Y), allora è più vantaggioso usare la formula (1.1), perché Oltre al valore di covarianza stesso, è possibile utilizzare anche i risultati dei calcoli intermedi. 1.1 Calcoliamo il coefficiente di correlazione utilizzando la formula (1.4), per fare ciò, calcoliamo i valori di x k 2, y k 2 e x k y k e li inseriamo nella Tabella 1. Tabella 1 K xk sì k xk 2 sì k 2 xksì k 1 2 3 4 5 6 1 25.2 30.8 635.04000 948.64000 776.16000 2 26.4 29.4 696.96000 864.36000 776.16000 3 26.0 30.2 676.00000 912.04000 785.20000 4 25.8 30.5 665.64000 930.25000 786.90000 5 24.9 31.4 620.01000 985.96000 781.86000 6 25.7 30.3 660.49000 918.09000 778.71000 7 25.7 30.4 660.49000 924.16000 781.28000 8 25.7 30.5 660.49000 930.25000 783.85000 9 26.1 29.9 681.21000 894.01000 780.39000 10 25.8 30.4 665.64000 924.16000 784.32000 11 25.9 30.3 670.81000 918.09000 784.77000 12 26.2 30.5 686.44000 930.25000 799.10000 13 25.6 30.6 655.36000 936.36000 783.36000 14 25.4 31 645.16000 961.00000 787.40000 15 26.6 29.6 707.56000 876.16000 787.36000 16 26.2 30.4 686.44000 924.16000 796.48000 17 26 30.7 676.00000 942.49000 798.20000 18 22.1 31.6 488.41000 998.56000 698.36000 19 25.9 30.5 670.81000 930.25000 789.95000 20 25.8 30.6 665.64000 936.36000 789.48000 21 25.9 30.7 670.81000 942.49000 795.13000 22 26.3 30.1 691.69000 906.01000 791.63000 23 26.1 30.6 681.21000 936.36000 798.66000 24 26 30.5 676.00000 930.25000 793.00000 25 26.4 30.7 696.96000 942.49000 810.48000 26 25.8 30.8 665.64000 948.64000 794.64000 1.2. Calcoliamo M x usando la formula (1.5). 1.2.1. xk x1 + x2 + … + x26 = 25,20000 + 26,40000 + ... + 25,80000 = 669,500000 1.2.2. 669.50000 / 26 = 25.75000 M x = 25,750000 1.3. Calcoliamo M y in modo simile. 1.3.1. Aggiungiamo tutti gli elementi in sequenza sì k y1 + y2 + … + y26 = 30,80000 + 29,40000 + ... + 30,80000 = 793,000000 1.3.2. Dividere la somma risultante per il numero di elementi del campione 793.00000 / 26 = 30.50000 Mio y = 30,500000 1.4. In modo simile calcoliamo M xy. 1.4.1. Aggiungiamo in sequenza tutti gli elementi della 6a colonna della tabella 1 776.16000 + 776.16000 + ... + 794.64000 = 20412.830000 1.4.2. Dividi la somma risultante per il numero di elementi 20412.83000 / 26 = 785.10885 Mxy = 785.108846 1.5. Calcoliamo il valore di S x 2 utilizzando la formula (1.6.). 1.5.1. Aggiungiamo in sequenza tutti gli elementi della 4a colonna della tabella 1 635.04000 + 696.96000 + ... + 665.64000 = 17256.910000 1.5.2. Dividi la somma risultante per il numero di elementi 17256.91000 / 26 = 663.72731 1.5.3. Sottrai il quadrato di M x dall'ultimo numero per ottenere il valore di S x 2 Sx2 = 663.72731 - 25.75000 2 = 663.72731 - 663.06250 = 0.66481 1.6. Calcoliamo il valore di S y 2 utilizzando la formula (1.6.). 1.6.1. Aggiungiamo in sequenza tutti gli elementi della 5a colonna della tabella 1 948.64000 + 864.36000 + ... + 948.64000 = 24191.840000 1.6.2. Dividi la somma risultante per il numero di elementi 24191.84000 / 26 = 930.45538 1.6.3. Sottrai il quadrato di M y dall'ultimo numero per ottenere il valore di S y 2 S y 2 = 930.45538 - 30.50000 2 = 930.45538 - 930.25000 = 0.20538 1.7. Calcoliamo il prodotto delle quantità S x 2 e S y 2. S x 2 S y 2 = 0,66481 0,20538 = 0,136541 1.8. Prendiamo la radice quadrata dell'ultimo numero e otteniamo il valore S x S y. S x S y = 0,36951 1.9. Calcoliamo il valore del coefficiente di correlazione utilizzando la formula (1.4.). R = (785,10885 - 25,75000 30,50000) / 0,36951 = (785,10885 - 785,37500) / 0,36951 = -0,72028 RISPOSTA: R x,y = -0,720279 2. Controlliamo la significatività del coefficiente di correlazione (controlliamo l'ipotesi di dipendenza). Poiché la stima del coefficiente di correlazione è calcolata su un campione finito e pertanto può discostarsi dal valore della popolazione, è necessario testare la significatività del coefficiente di correlazione. Il controllo viene effettuato utilizzando il test t: t = Rx,y √ n-2 √ 1 - R2x,y (2.1) Valore casuale T segue la distribuzione t di Student e utilizzando la tabella della distribuzione t è necessario trovare il valore critico del criterio (t cr.α) ad un dato livello di significatività α. Se t calcolato con la formula (2.1) in valore assoluto risulta essere inferiore a t cr.α , allora non esiste dipendenza tra le variabili casuali X e Y. Altrimenti, i dati sperimentali non contraddicono l'ipotesi sulla dipendenza delle variabili casuali. 2.1. Calcoliamo il valore del criterio t utilizzando la formula (2.1) e otteniamo: t = -0.72028 √ 26 - 2 √ 1 - (-0.72028) 2 = -5.08680 2.2. Utilizzando la tabella di distribuzione t, determiniamo il valore critico del parametro t cr.α Il valore desiderato di tcr.α si trova all'intersezione della riga corrispondente al numero di gradi di libertà e della colonna corrispondente al livello di significatività α specificato. Nel nostro caso, il numero di gradi di libertà è n - 2 = 26 - 2 = 24 e α = 0.05 , che corrisponde al valore critico del criterio t cr.α = 2.064 (vedi tabella 2) Tavolo 2 distribuzione t Numero di gradi di libertà (n-2) α = 0,1 α = 0,05 α = 0,02 α = 0,01 α = 0,002 α = 0,001 1 6.314 12.706 31.821 63.657 318.31 636.62 2 2.920 4.303 6.965 9.925 22.327 31.598 3 2.353 3.182 4.541 5.841 10.214 12.924 4 2.132 2.776 3.747 4.604 7.173 8.610 5 2.015 2.571 3.365 4.032 5.893 6.869 6 1.943 2.447 3.143 3.707 5.208 5.959 7 1.895 2.365 2.998 3.499 4.785 5.408 8 1.860 2.306 2.896 3.355 4.501 5.041 9 1.833 2.262 2.821 3.250 4.297 4.781 10 1.812 2.228 2.764 3.169 4.144 4.587 11 1.796 2.201 2.718 3.106 4.025 4.437 12 1.782 2.179 2.681 3.055 3.930 4.318 13 1.771 2.160 2.650 3.012 3.852 4.221 14 1.761 2.145 2.624 2.977 3.787 4.140 15 1.753 2.131 2.602 2.947 3.733 4.073 16 1.746 2.120 2.583 2.921 3.686 4.015 17 1.740 2.110 2.567 2.898 3.646 3.965 18 1.734 2.101 2.552 2.878 3.610 3.922 19 1.729 2.093 2.539 2.861 3.579 3.883 20 1.725 2.086 2.528 2.845 3.552 3.850 21 1.721 2.080 2.518 2.831 3.527 3.819 22 1.717 2.074 2.508 2.819 3.505 3.792 23 1.714 2.069 2.500 2.807 3.485 3.767 24 1.711 2.064 2.492 2.797 3.467 3.745 25 1.708 2.060 2.485 2.787 3.450 3.725 26 1.706 2.056 2.479 2.779 3.435 3.707 27 1.703 2.052 2.473 2.771 3.421 3.690 28 1.701 2.048 2.467 2.763 3.408 3.674 29 1.699 2.045 2.462 2.756 3.396 3.659 30 1.697 2.042 2.457 2.750 3.385 3.646 40 1.684 2.021 2.423 2.704 3.307 3.551 60 1.671 2.000 2.390 2.660 3.232 3.460 120 1.658 1.980 2.358 2.617 3.160 3.373 ∞ 1.645 1.960 2.326 2.576 3.090 3.291 2.2. Confrontiamo il valore assoluto del criterio t e t cr.α Il valore assoluto del criterio t non è inferiore al valore critico t = 5.08680, t cr.α = 2.064, quindi dati sperimentali, con probabilità 0,95(1 - α), non contraddire l'ipotesi sulla dipendenza delle variabili casuali X e Y. 3. Calcolare i coefficienti dell'equazione di regressione lineare. Un'equazione di regressione lineare è un'equazione di una linea retta che approssima (descrive approssimativamente) la relazione tra le variabili casuali X e Y. Se assumiamo che il valore X sia libero e Y dipenda da X, allora l'equazione di regressione verrà scritta come segue Y = a + b X (3.1), dove: b = Rx,y σy σx = Rx,y Sì Sx (3.2), a = M y - b M x (3.3) Il coefficiente calcolato utilizzando la formula (3.2) B chiamato coefficiente di regressione lineare. In alcune fonti UNè chiamato coefficiente di regressione costante e B secondo le variabili. Gli errori nella previsione di Y per un dato valore X vengono calcolati utilizzando le formule: Viene anche chiamata la quantità σ y/x (formula 3.4). deviazione standard residua, caratterizza la deviazione del valore Y dalla retta di regressione descritta dall'equazione (3.1) per un valore fisso (dato) di X.

.

S y 2 / S x 2 = 0,20538 / 0,66481 = 0,30894. Prendiamo la radice quadrata dell'ultimo numero e otteniamo:
S y / S x = 0,55582

3.3 Calcoliamo il coefficiente b secondo la formula (3.2)

B = -0.72028 0.55582 = -0.40035

3.4 Calcoliamo il coefficiente a secondo la formula (3.3)

UN = 30.50000 - (-0.40035 25.75000) = 40.80894

3.5 Stimiamo gli errori dell’equazione di regressione.

3.5.1 Prendendo la radice quadrata di S y 2 otteniamo:

= 0.31437
3.5.4 Calcoliamo l'errore relativo utilizzando la formula (3.5)

δ y/x = (0,31437 / 30,50000)100% = 1,03073%

4. Costruiamo un diagramma di dispersione (campo di correlazione) e un grafico a linee di regressione.

Un grafico a dispersione è una rappresentazione grafica di coppie corrispondenti (x k, y k) come punti su un piano, in coordinate rettangolari con gli assi X e Y. Il campo di correlazione è una delle rappresentazioni grafiche di un campione correlato (accoppiato). Anche il grafico della linea di regressione viene tracciato nello stesso sistema di coordinate. Le scale e i punti di partenza sugli assi dovrebbero essere scelti con attenzione per garantire che il diagramma sia il più chiaro possibile.

4.1. Trovare l'elemento minimo e massimo del campione X è rispettivamente il 18° e il 15° elemento, x min = 22.10000 e x max = 26.60000.

4.2. Troviamo che l'elemento minimo e massimo del campione Y sono rispettivamente il 2° e il 18° elemento, y min = 29,40000 e y max = 31,60000.

4.3. Sull'asse x, seleziona un punto iniziale leggermente a sinistra del punto x 18 = 22,10000 e una scala tale che il punto x 15 = 26,60000 si adatti all'asse e i punti rimanenti siano chiaramente visibili.

4.4. Sull'asse delle ordinate, selezionare un punto iniziale leggermente a sinistra del punto y 2 = 29.40000 e una scala tale che il punto y 18 = 31.60000 si adatti all'asse e i punti rimanenti siano chiaramente distinguibili.

4.5. Posizioniamo i valori x k sull'asse delle ascisse e i valori y k sull'asse delle ordinate.

4.6. Tracciamo i punti (x 1, y 1), (x 2, y 2),…, (x 26, y 26) sul piano delle coordinate. Otteniamo il diagramma di dispersione (campo di correlazione) mostrato nella figura seguente.

4.7. Disegniamo una linea di regressione.

Per fare ciò, troveremo due punti diversi con coordinate (x r1, y r1) e (x r2, y r2) che soddisfano l'equazione (3.6), tracciateli sul piano delle coordinate e tracciamo una linea retta che li attraversa. Come ascissa del primo punto prendiamo il valore x min = 22.10000. Sostituendo il valore x min nell'equazione (3.6), otteniamo l'ordinata del primo punto. Quindi, abbiamo un punto con coordinate (22.10000, 31.96127). In modo analogo otteniamo le coordinate del secondo punto, ponendo come ascissa il valore x max = 26.60000. Il secondo punto sarà: (26.60000, 30.15970).

La retta di regressione è mostrata nella figura seguente in rosso

Si tenga presente che la retta di regressione passa sempre per il punto dei valori medi di X e Y, cioè con coordinate (M x , M y).

LAVORO DI LABORATORIO

ANALISI DI CORRELAZIONE INECCELLERE

1.1 Analisi di correlazione in MS Excel

L'analisi di correlazione consiste nel determinare il grado di connessione tra due variabili casuali X e Y. Il coefficiente di correlazione viene utilizzato come misura di tale connessione. Il coefficiente di correlazione è stimato da un campione di n coppie di osservazioni correlate (xi, y i) dalla popolazione congiunta di X e Y. Per valutare il grado di relazione tra i valori di X e Y, misurati su scale quantitative, è si usa coefficiente di correlazione lineare(coefficiente di Pearson), che presuppone che i campioni X e Y siano distribuiti normalmente.

Il coefficiente di correlazione varia da -1 (rapporto lineare inverso stretto) a 1 (rapporto proporzionale diretto stretto). Quando impostato su 0, non esiste una relazione lineare tra i due campioni.

Classificazione generale delle correlazioni (secondo Ivanter E.V., Korosov A.V., 1992):

Esistono diversi tipi di coefficienti di correlazione, a seconda delle variabili X e Y, che possono essere misurati su scale diverse. È questo fatto che determina la scelta del coefficiente di correlazione appropriato (vedi Tabella 13):

In MS Excel, una funzione speciale viene utilizzata per calcolare i coefficienti di correlazione lineare delle coppie CORRELAZIONE (matrice1; matrice2),

№ soggetti

dove array1 è un riferimento all'intervallo di celle della prima selezione (X);

Esempio 1: A 10 scolari sono stati sottoposti test sul pensiero visivo-figurativo e verbale. Il tempo medio per risolvere le attività di test è stato misurato in secondi. Il ricercatore è interessato alla domanda: esiste una relazione tra il tempo necessario per risolvere questi problemi? La variabile X indica il tempo medio per risolvere i test visivo-figurativi e la variabile Y indica il tempo medio per risolvere i compiti dei test verbali.

R soluzione: Per identificare il grado di relazione è necessario innanzitutto inserire i dati in una tabella MS Excel (vedi tabella, Fig. 1). Quindi viene calcolato il valore del coefficiente di correlazione. Per fare ciò, posiziona il cursore nella cella C1. Sulla barra degli strumenti, fare clic sul pulsante Inserisci funzione (fx).

Nella finestra di dialogo Creazione guidata funzionalità visualizzata, selezionare una categoria Statistico e funzione CORRELAZIONE, quindi fare clic su OK. Utilizzando il puntatore del mouse, immettere l'intervallo di dati del campione X nel campo array1 (A1:A10). Nel campo array2, inserisci l'intervallo di dati del campione Y (B1:B10). Fare clic su OK. Nella cella C1 apparirà il valore del coefficiente di correlazione - 0,54119. Successivamente, è necessario esaminare il numero assoluto del coefficiente di correlazione e determinare il tipo di connessione (vicino, debole, medio, ecc.)

Riso. 1. Risultati del calcolo del coefficiente di correlazione

Pertanto, la connessione tra il momento della risoluzione dei compiti di test visivo-figurativo e verbale non è stata dimostrata.

Esercizio 1. I dati sono disponibili per 20 aziende agricole. Trovare coefficiente di correlazione tra le rese dei raccolti di grano e la qualità del terreno e valutarne l’importanza. I dati sono riportati nella tabella.

Tabella 2. Dipendenza della resa del grano dalla qualità del terreno

Numero dell'azienda agricola	Qualità del terreno, punteggio	Produttività, c/ha

Compito 2. Determinare se esiste una connessione tra il tempo di funzionamento dell'attrezzatura sportiva per il fitness (migliaia di ore) e il costo della sua riparazione (migliaia di rubli):

Tempo di funzionamento del simulatore (migliaia di ore)	Costo delle riparazioni (migliaia di rubli)

1.2 Correlazione multipla in MS Excel

Con un gran numero di osservazioni, quando i coefficienti di correlazione devono essere calcolati in sequenza per diversi campioni, per comodità, i coefficienti ottenuti vengono riassunti in tabelle denominate matrici di correlazione.

Matrice di correlazioneè una tabella quadrata in cui all'intersezione delle righe e delle colonne corrispondenti è presente un coefficiente di correlazione tra i parametri corrispondenti.

In MS Excel, la procedura viene utilizzata per calcolare le matrici di correlazione Correlazione dal pacchetto Analisi dei dati. La procedura permette di ottenere una matrice di correlazione contenente i coefficienti di correlazione tra vari parametri.

Per implementare la procedura è necessario:

1. eseguire il comando Servizio - Analisi dati;

2. nell'elenco che appare Strumenti di analisi seleziona la riga Correlazione e premere il pulsante OK;

3. nella finestra di dialogo che appare, specificare Intervallo di input, ovvero inserire un collegamento alle celle contenenti i dati analizzati. L'intervallo di input deve contenere almeno due colonne.

4. nella sezione Raggruppamento impostare l'interruttore in base ai dati inseriti (per colonne o per righe);

5. indicare giorno libero intervallo, ovvero inserire un collegamento alla cella da cui verranno visualizzati i risultati dell'analisi. La dimensione dell'intervallo di output verrà determinata automaticamente e verrà visualizzato un messaggio se l'intervallo di output potrebbe sovrapporsi ai dati di origine. premi il bottone OK.

Nell'intervallo di output verrà generata una matrice di correlazione in cui all'intersezione di ciascuna riga e colonna è presente un coefficiente di correlazione tra i parametri corrispondenti. Le celle nell'intervallo di output che hanno coordinate di riga e colonna corrispondenti contengono il valore 1 perché ogni colonna nell'intervallo di input è perfettamente correlata a se stessa

Esempio 2. Sono disponibili dati osservativi mensili sulle condizioni meteorologiche e sulla frequentazione di musei e parchi (vedi Tabella 3). È necessario verificare se esiste una relazione tra le condizioni meteorologiche e la frequentazione di musei e parchi.

Tabella 3. Risultati dell'osservazione

Numero di giorni sereni	Numero di visitatori del museo	Numero di visitatori del parco

Soluzione. Per eseguire l'analisi di correlazione, inserire i dati originali nell'intervallo A1:G3 (Fig. 2). Poi nel menù Servizio scegliere oggetto Analisi dati e poi inserisci la riga Correlazione. Nella finestra di dialogo visualizzata, specificare Intervallo di input(A2:C7). Specificare che i dati vengono visualizzati in colonne. Specificare l'intervallo di uscita (E1) e premere il pulsante OK.

Nella fig. 33 mostra che la correlazione tra condizioni meteorologiche e frequentazione del museo è -0,92, tra condizioni meteorologiche e frequentazione del parco è 0,97 e tra frequentazione del parco e museo è 0,92.

Pertanto, come risultato dell'analisi, sono state rivelate delle dipendenze: un forte grado di relazione lineare inversa tra la frequentazione del museo e il numero di giorni di sole e una relazione quasi lineare (diretta molto forte) tra la frequentazione del parco e le condizioni meteorologiche. Esiste una forte relazione inversa tra la frequentazione dei musei e quella dei parchi.

Riso. 2. Risultati del calcolo della matrice di correlazione dall'esempio 2

Compito 3. 10 manager sono stati valutati utilizzando il metodo della valutazione esperta delle caratteristiche psicologiche della personalità di un manager. 15 esperti hanno valutato ciascuna caratteristica psicologica utilizzando un sistema a cinque punti (vedi Tabella 4). Lo psicologo è interessato alla questione della relazione tra queste caratteristiche di un leader.

Tabella 4. Risultati dello studio

Soggetti	tatto	esattezza	criticità

Calcoliamo il coefficiente di correlazione e la covarianza per diversi tipi di relazioni tra variabili casuali.

Coefficiente di correlazione(criterio di correlazione Pearson, inglese Coefficiente di correlazione Pearson Product Moment) determina il grado lineare relazioni tra variabili casuali.

Come segue dalla definizione, per calcolare coefficiente di correlazioneè necessario conoscere la distribuzione delle variabili casuali X e Y. Se le distribuzioni sono sconosciute, allora da stimare coefficiente di correlazione usato coefficiente di correlazione campionariaR (è anche designato come Rxy O rxy) :

dove S x – deviazione standard campione di una variabile casuale x, calcolata con la formula:

Come si può vedere dalla formula di calcolo correlazioni, il denominatore (il prodotto delle deviazioni standard) normalizza semplicemente il numeratore in modo tale che correlazione risulta essere un numero adimensionale compreso tra -1 e 1. Correlazione E covarianza fornire le stesse informazioni (se conosciute deviazioni standard), Ma correlazione più comodo da usare, perché è una quantità adimensionale.

Calcolare coefficiente di correlazione E covarianza del campione in MS EXCEL non è difficile, poiché a questo scopo esistono le funzioni speciali CORREL() e KOVAR(). Molto più difficile è capire come interpretare i valori ottenuti, a questo è dedicata gran parte dell'articolo.

Ritiro teorico

Lascia che te lo ricordiamo connessione di correlazione chiamare una relazione statistica consistente nel fatto che valori diversi di una variabile corrispondono a valori diversi media i valori sono diversi (con una variazione del valore di X valore medio Y cambia in modo regolare). Si presume che Entrambi le variabili X e Y lo sono casuale valori e hanno una certa dispersione casuale rispetto ad essi valore medio.

Nota. Se solo una variabile, ad esempio Y, ha una natura casuale e i valori dell'altra sono deterministici (impostati dal ricercatore), allora possiamo solo parlare di regressione.

Quindi, ad esempio, quando si studia la dipendenza della temperatura media annuale, non si può parlarne correlazioni temperatura e anno di osservazione e, di conseguenza, applicare gli indicatori correlazioni con la relativa interpretazione.

Correlazione tra le variabili può presentarsi in diversi modi:

1. La presenza di una relazione causale tra le variabili. Ad esempio, l’importo degli investimenti nella ricerca scientifica (variabile X) e il numero di brevetti ricevuti (Y). La prima variabile appare come variabile indipendente (fattore), secondo - variabile dipendente (risultato). Va ricordato che la dipendenza delle quantità determina la presenza di una correlazione tra loro, ma non viceversa.
2. La presenza di coniugazione (causa comune). Ad esempio, man mano che l’organizzazione cresce, aumentano il fondo salariale (buste paga) e il costo dell’affitto dei locali. Ovviamente è sbagliato presumere che l'affitto dei locali dipenda dalla busta paga. Entrambe queste variabili dipendono linearmente dal numero del personale in molti casi.
3. Influenza reciproca delle variabili (quando cambia una, cambia la seconda variabile e viceversa). Con questo approccio sono consentite due formulazioni del problema; Qualsiasi variabile può agire sia come variabile indipendente che come variabile dipendente.

Così, indicatore di correlazione mostra quanto è forte relazione lineare tra due fattori (se ce n'è uno) e la regressione consente di prevedere un fattore in base all'altro.

Correlazione, come qualsiasi altro indicatore statistico, può essere utile se utilizzato correttamente, ma presenta anche dei limiti nel suo utilizzo. Se mostra una relazione lineare chiaramente definita o una completa mancanza di relazione, allora correlazione lo rifletterà meravigliosamente. Ma, se i dati mostrano una relazione non lineare (ad esempio quadratica), la presenza di gruppi separati di valori o valori anomali, quindi il valore calcolato coefficiente di correlazione potrebbe essere fuorviante (vedi file di esempio).

Correlazione vicino a 1 o -1 (cioè vicino in valore assoluto a 1) mostra una forte relazione lineare tra le variabili, un valore vicino a 0 non mostra alcuna relazione. Positivo correlazione significa che con un aumento di un indicatore, l'altro in media aumenta e con un indicatore negativo diminuisce.

Per calcolare il coefficiente di correlazione è necessario che le variabili confrontate soddisfino le seguenti condizioni:

• il numero di variabili deve essere pari a due;
• le variabili devono essere quantitative (es. frequenza, peso, prezzo). La media calcolata di queste variabili ha un significato chiaro: prezzo medio o peso medio del paziente. A differenza delle variabili quantitative, le variabili qualitative (nominali) assumono valori solo da un insieme finito di categorie (ad esempio, sesso o gruppo sanguigno). Questi valori sono convenzionalmente associati a valori numerici (ad esempio, il genere femminile è 1 e il genere maschile è 2). È chiaro che in questo caso il calcolo valore medio, che è necessario trovare correlazioni, non è corretto e pertanto il calcolo stesso non è corretto correlazioni;
• le variabili devono essere variabili casuali e avere .

I dati bidimensionali possono avere strutture diverse. Alcuni di essi richiedono determinati approcci con cui lavorare:

• Per dati con relazione non lineare correlazione deve essere usato con cautela. Per alcuni problemi, può essere utile trasformare una o entrambe le variabili per produrre una relazione lineare (questo richiede di fare un'ipotesi sul tipo di relazione non lineare per suggerire il tipo di trasformazione necessaria).
• Usando grafici a dispersione Alcuni dati potrebbero presentare variazioni diseguali (dispersione). Il problema con la variazione non uniforme è che le località con una variazione elevata non solo forniscono le informazioni meno accurate, ma hanno anche il maggiore impatto nel calcolo delle statistiche. Questo problema viene spesso risolto anche trasformando i dati, ad esempio utilizzando i logaritmi.
• Si può osservare che alcuni dati sono divisi in gruppi (clustering), il che può indicare la necessità di dividere la popolazione in parti.
• Un valore anomalo (un valore nettamente diverso) può distorcere il valore calcolato del coefficiente di correlazione. Un valore anomalo può essere dovuto al caso, a un errore nella raccolta dei dati o potrebbe effettivamente riflettere alcune caratteristiche della relazione. Poiché l’outlier si discosta notevolmente dal valore medio, fornisce un grande contributo al calcolo dell’indicatore. Gli indicatori statistici sono spesso calcolati con e senza tenere conto dei valori anomali.

Utilizzo di MS EXCEL per calcolare la correlazione

Prendiamo come esempio 2 variabili X E Y e corrispondentemente, campione costituito da più coppie di valori (X i; Y i). Per chiarezza, costruiamo .

Nota: per ulteriori informazioni sulla costruzione dei diagrammi, vedere l'articolo. Nel file di esempio per building grafici a dispersione usato perché Qui ci siamo allontanati dal requisito che la variabile X sia casuale (questo semplifica la generazione di vari tipi di relazioni: costruzione di tendenze e un dato spread). Per i dati reali, è necessario utilizzare un grafico a dispersione (vedi sotto).

Calcoli correlazioni Disegniamo le relazioni tra le variabili per vari casi: lineare, quadratico e a mancanza di comunicazione.

Nota: Nel file di esempio è possibile impostare i parametri del trend lineare (pendenza, intercetta Y) e il grado di dispersione rispetto a questa linea di trend. È inoltre possibile regolare i parametri quadratici.

Nel file di esempio per building grafici a dispersione se non c'è dipendenza delle variabili, viene utilizzato un diagramma a dispersione. In questo caso i punti sul diagramma sono disposti sotto forma di nuvola.

Nota: Si noti che modificando la scala del diagramma lungo l'asse verticale o orizzontale, è possibile dare alla nuvola di punti l'aspetto di una linea verticale o orizzontale. È chiaro che le variabili rimarranno indipendenti.

Come accennato in precedenza, per calcolare coefficiente di correlazione in MS EXCEL c'è una funzione CORREL(). Puoi anche utilizzare la funzione simile PEARSON(), che restituisce lo stesso risultato.

Per assicurarsi che i calcoli correlazioni sono prodotti dalla funzione CORREL() utilizzando le formule sopra; il file di esempio mostra il calcolo correlazioni utilizzando formule più dettagliate:

=COVARIANZA.G(B28:B88;D28:D88)/DEV.ST.G(B28:B88)/DEV.ST.G(D28:D88)

=COVARIANZA.B(B28:B88;D28:D88)/DEV.ST.B(B28:B88)/DEV.ST.B(D28:D88)

Nota: Piazza coefficiente di correlazione r è uguale a coefficiente di determinazione R2, che viene calcolato quando si costruisce una linea di regressione utilizzando la funzione QPIRSON(). È anche possibile emettere il valore di R2 diagramma di dispersione costruendo un trend lineare utilizzando la funzionalità standard di MS EXCEL (seleziona il grafico, seleziona la scheda Disposizione, poi nel gruppo Analisi fare clic sul pulsante Linea di tendenza e seleziona Approssimazione lineare). Per ulteriori informazioni sulla costruzione di una linea di tendenza, vedere, ad esempio, .

Utilizzo di MS EXCEL per calcolare la covarianza

Covarianza ha un significato vicino a (anche una misura di dispersione) con la differenza che è definito per 2 variabili, e dispersione- per uno. Pertanto, cov(x;x)=VAR(x).

Per calcolare la covarianza in MS EXCEL (a partire dalla versione 2010), vengono utilizzate le funzioni COVARIATION.Г() e COVARIATION.В(). Nel primo caso la formula per il calcolo è simile alla precedente (end .G sta per Popolazione), nella seconda, invece del moltiplicatore 1/n, viene utilizzato 1/(n-1), cioè fine .IN sta per Campione.

Nota: La funzione COVAR(), presente in MS EXCEL nelle versioni precedenti, è simile alla funzione COVARIATION.G().

Nota: Le funzioni CORREL() e COVAR() sono presentate nella versione inglese come CORREL e COVAR. Le funzioni COVARIANCE.G() e COVARIANCE.B() come COVARIANCE.P e COVARIANCE.S.

Formule aggiuntive per il calcolo covarianze:

=SOMMAPRODOTTO(B28:B88-MEDIA(B28:B88);(D28:D88-MEDIA(D28:D88)))/CONT.(D28:D88)

=SOMMAPRODOTTO(B28:B88-MEDIA(B28:B88),(D28:D88))/CONT.(D28:D88)

=SOMMAPRODOTTO(B28:B88;D28:D88)/CONTEGGIO(D28:D88)-MEDIA(B28:B88)*MEDIA(D28:D88)

Queste formule utilizzano la proprietà covarianze:

Se le variabili X E sì indipendenti, allora la loro covarianza è 0. Se le variabili non sono indipendenti, allora la varianza della loro somma è uguale a:

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

UN dispersione la loro differenza è uguale

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

Stima della significatività statistica del coefficiente di correlazione

Per verificare l'ipotesi, dobbiamo conoscere la distribuzione della variabile casuale, cioè coefficiente di correlazione R. Di solito, l'ipotesi viene verificata non per r, ma per la variabile casuale t r:

che ha n-2 gradi di libertà.

Se il valore calcolato della variabile casuale |t r | è maggiore del valore critico t α,n-2 (α-specificato), allora l’ipotesi nulla viene rifiutata (la relazione tra i valori è statisticamente significativa).

Componente aggiuntivo del pacchetto di analisi

B per calcolare la covarianza e la correlazione ci sono strumenti con lo stesso nome analisi.

Dopo aver richiamato lo strumento, viene visualizzata una finestra di dialogo contenente i seguenti campi:

• Intervallo di input: è necessario inserire un collegamento a un intervallo con dati di origine per 2 variabili
• Raggruppamento: Di norma i dati di origine vengono inseriti in 2 colonne
• Etichette nella prima riga: se la casella è selezionata, allora Intervallo di input deve contenere intestazioni di colonna. Si consiglia di selezionare la casella in modo che il risultato del componente aggiuntivo contenga colonne informative
• Intervallo di uscita: l'intervallo di celle in cui verranno posizionati i risultati del calcolo. È sufficiente indicare la cella in alto a sinistra di questo intervallo.

Il componente aggiuntivo restituisce i valori di correlazione e covarianza calcolati (per la covarianza vengono calcolate anche le varianze di entrambe le variabili casuali).

Hai già riscontrato la necessità di calcolare il grado di connessione tra due quantità statistiche e determinare la formula con cui sono correlate? Una persona normale potrebbe chiedersi perché questo sarebbe necessario. Stranamente, questo è effettivamente necessario. Conoscere correlazioni affidabili può aiutarti a fare soldi pazzeschi se sei, ad esempio, un operatore di borsa. Il problema è che per qualche motivo nessuno rivela queste correlazioni (sorprendente, non è vero?).

Contiamoli noi stessi! Ad esempio, ho deciso di provare a calcolare la correlazione tra il rublo e il dollaro tramite l'euro. Diamo un'occhiata a come questo viene fatto in dettaglio.

Questo articolo è progettato per livelli avanzati di competenza di Microsoft Excel. Se non hai tempo di leggere l'intero articolo, puoi scaricare il file e capirlo da solo.

Se ti ritrovi spesso a dover fare qualcosa del genere Consiglio vivamente di prendere in considerazione l'acquisto del libro. Calcoli statistici in Excel.

Cosa è importante sapere sulle correlazioni

Per calcolare una correlazione affidabile, è necessario disporre di un campione affidabile; quanto più grande è, tanto più affidabile sarà il risultato. Ai fini di questo esempio, ho preso un campione giornaliero di tassi di cambio nell’arco di 10 anni. I dati sono liberamente consultabili, li ho presi dal sito http://oanda.com.

Cosa ho fatto veramente?

(1) Una volta ottenuti i dati grezzi, ho iniziato controllando il grado di correlazione tra i due set di dati. Per fare ciò, ho utilizzato la funzione CORRELAZIONE: ci sono alcune informazioni a riguardo. Restituisce il grado di correlazione tra due intervalli di dati. Il risultato, francamente, non è stato particolarmente impressionante (solo circa il 70%). In generale, il grado di correlazione tra due quantità è solitamente considerato il quadrato di questa quantità, ovvero la correlazione si è rivelata affidabile di circa il 49%. Questo è davvero poco!

(2) Questo mi è sembrato molto strano. Quali errori potrebbero essersi insinuati nei miei calcoli? Quindi ho deciso di fare un grafico e vedere cosa potrebbe succedere. Il grafico è stato appositamente suddiviso per anno in modo da poter vedere visivamente dove si interrompe la correlazione. Il programma è andato così

(3) Dal grafico risulta evidente che nell'intervallo di circa 35 rubli per euro la correlazione comincia a dividersi in due parti. Per questo motivo si è rivelato inaffidabile. Era necessario determinare il motivo per cui ciò stava accadendo.

(4) Il colore evidenzia che questi dati si riferiscono agli anni 2007, 2008, 2009. Certamente! I periodi di picchi economici e di recessioni sono solitamente statisticamente inaffidabili, ed è quello che è successo in questo caso. Pertanto, ho provato a escludere questi periodi dai dati (e per verificare, ho controllato il grado di correlazione dei dati in questo periodo). Il grado di correlazione di questi soli dati è pari allo 0,01%, ovvero è completamente assente. Ma senza di essi, i dati sono correlati di circa l’81%. Questa è già una correlazione abbastanza affidabile. Ecco un grafico con la funzione.

Prossimi passi

Teoricamente, la funzione di correlazione può essere perfezionata convertendola da lineare a esponenziale o logaritmica. In questo caso, l'affidabilità statistica della correlazione aumenta di circa l'1%, ma la complessità dell'applicazione della formula aumenta incredibilmente. Mi pongo quindi la domanda: è davvero necessario? Sta a te decidere, per ogni caso specifico.