Tavola di correlazione in excel. Un esempio di calcolo della correlazione, costruzione di una regressione lineare e verifica dell'ipotesi della dipendenza di due RV dal nostro servizio

Regressione e analisi di correlazione– metodi di ricerca statistica. Questi sono i modi più comuni per mostrare la dipendenza di un parametro da una o più variabili indipendenti.

Di seguito su specifico esempi pratici Consideriamo queste due analisi molto popolari tra gli economisti. Daremo anche un esempio di come ottenere risultati quando vengono combinati.

Analisi di regressione in Excel

Mostra l'influenza di alcuni valori (indipendente, indipendente) sulla variabile dipendente. Ad esempio, come il numero di popolazione economicamente attiva dipende dal numero di imprese, salari e altri parametri. Oppure: in che modo gli investimenti esteri, i prezzi dell'energia, ecc. influiscono sul livello del PIL.

Il risultato dell'analisi consente di stabilire le priorità. E sulla base dei principali fattori, per prevedere, pianificare lo sviluppo aree prioritarie prendere decisioni manageriali.

La regressione avviene:

lineare (y = a + bx);
parabolico (y = a + bx + cx 2);
esponenziale (y = a * exp(bx));
potenza (y = a*x^b);
iperbolico (y = b/x + a);
logaritmico (y = b * 1n(x) + a);
esponenziale (y = a * b^x).

Si consideri l'esempio della creazione di un modello di regressione in Excel e dell'interpretazione dei risultati. Prendiamo un tipo lineare di regressione.

Un compito. A 6 imprese, la media mensile salario e il numero di dipendenti in pensione. È necessario determinare la dipendenza del numero di dipendenti in pensione dallo stipendio medio.

Modello regressione lineare ha la seguente forma:

Y \u003d a 0 + a 1 x 1 + ... + a k x k.

Dove a sono i coefficienti di regressione, x sono le variabili di influenza e k è il numero di fattori.

Nel nostro esempio, Y è l'indicatore dei lavoratori licenziati. Il fattore che influenza è il salario (x).

Excel dispone di funzioni incorporate che possono essere utilizzate per calcolare i parametri di un modello di regressione lineare. Ma il componente aggiuntivo Analysis ToolPak lo farà più velocemente.

Attiva un potente strumento analitico:

Una volta attivato, il componente aggiuntivo sarà disponibile nella scheda Dati.

Ora ci occuperemo direttamente dell'analisi di regressione.

Prima di tutto, prestiamo attenzione all'R-quadro e ai coefficienti.

R-quadrato è il coefficiente di determinazione. Nel nostro esempio, è 0,755 o 75,5%. Ciò significa che i parametri calcolati del modello spiegano la relazione tra i parametri studiati del 75,5%. Più alto è il coefficiente di determinazione, migliore è il modello. Buono - superiore a 0,8. Scarso - meno di 0,5 (un'analisi del genere difficilmente può essere considerata ragionevole). Nel nostro esempio - "non male".

Il coefficiente 64.1428 mostra quale sarà Y se tutte le variabili nel modello in esame sono uguali a 0. Cioè, anche altri fattori che non sono descritti nel modello influenzano il valore del parametro analizzato.

Il coefficiente -0,16285 mostra il peso della variabile X su Y. Cioè, lo stipendio mensile medio all'interno di questo modello influisce sul numero di rinunciatari con un peso di -0,16285 (questo è un piccolo grado di influenza). Il segno “-” indica un impatto negativo: più alto è lo stipendio, meno rinuncia. Il che è giusto.

Analisi di correlazione in Excel

L'analisi di correlazione aiuta a stabilire se esiste una relazione tra gli indicatori in uno o due campioni. Ad esempio, tra il tempo di funzionamento della macchina e il costo delle riparazioni, il prezzo dell'attrezzatura e la durata dell'operazione, l'altezza e il peso dei bambini, ecc.

Se esiste una relazione, allora se un aumento di un parametro porta ad un aumento (correlazione positiva) o una diminuzione (negativa) nell'altro. L'analisi di correlazione aiuta l'analista a determinare se il valore di un indicatore può prevedere il possibile valore di un altro.

Il coefficiente di correlazione è indicato con r. Varia da +1 a -1. Classificazione delle correlazioni per diverse aree sarà diverso. Quando il valore del coefficiente è 0, non esiste alcuna relazione lineare tra i campioni.

Considera come utilizzare Excel per trovare il coefficiente di correlazione.

La funzione CORREL viene utilizzata per trovare i coefficienti accoppiati.

Compito: determinare se esiste una relazione tra l'orario di lavoro tornio e il costo della sua manutenzione.

Posiziona il cursore in qualsiasi cella e premi il pulsante fx.

Nella categoria "Statistica", seleziona la funzione CORRELAZIONE.
Argomento "Array 1" - il primo intervallo di valori - il tempo della macchina: A2: A14.
Argomento "Array 2" - il secondo intervallo di valori - il costo delle riparazioni: B2:B14. Fare clic su OK.

Per determinare il tipo di connessione, è necessario esaminare il numero assoluto del coefficiente (ogni campo di attività ha la propria scala).

Per l'analisi di correlazione di più parametri (più di 2), è più conveniente utilizzare "Data Analysis" (componente aggiuntivo "Analysis Package"). Nell'elenco è necessario selezionare una correlazione e designare un array. Tutto.

I coefficienti risultanti verranno visualizzati nella matrice di correlazione. Come questo:

Analisi di correlazione-regressione

In pratica, queste due tecniche sono spesso utilizzate insieme.

Esempio:

Ora i dati dell'analisi di regressione sono visibili.

"Correlazione" in latino significa "correlazione", "relazione". Una caratteristica quantitativa della relazione può essere ottenuta calcolando il coefficiente di correlazione. Questo popolare in analisi statistiche il coefficiente mostra se alcuni parametri sono correlati tra loro (ad esempio, altezza e peso; livello di intelligenza e rendimento scolastico; numero di infortuni e ore di lavoro).

Usando la correlazione

Il calcolo della correlazione è ampiamente utilizzato in economia, ricerca sociologica, medicina e biometria, ovunque sia possibile ottenere due set di dati tra i quali è possibile trovare una relazione.

È possibile calcolare manualmente la correlazione eseguendo semplici operazioni aritmetiche. Tuttavia, il processo di calcolo richiede molto tempo se il set di dati è grande. La particolarità del metodo è che richiede la raccolta un largo numero dati di origine per visualizzare in modo più accurato se esiste una relazione tra le funzioni. Pertanto, l'uso serio dell'analisi di correlazione è impossibile senza l'uso della tecnologia informatica. Uno dei programmi più popolari e convenienti per risolvere questo problema è.

Come eseguire la correlazione in Excel?

Il passaggio che richiede più tempo per determinare la correlazione è il set di dati. I dati da confrontare sono solitamente disposti in due colonne o righe. La tabella dovrebbe essere fatta senza spazi vuoti nelle celle. Versioni moderne Excel (dal 2007 in poi) non richiede impostazioni aggiuntive per i calcoli statistici; le manipolazioni necessarie possono essere fatte:

Selezionare una cella vuota in cui verrà visualizzato il risultato del calcolo.
Fare clic sulla voce "Formule" nel menu principale di Excel.
Tra i pulsanti raggruppati nella "Libreria funzioni", selezionare "Altre funzioni".
Negli elenchi a discesa selezionare la funzione di calcolo della correlazione (Statistica - CORRELAZIONE).
Excel apre il pannello Argomenti funzione. "Array 1" e "Array 2" sono gli intervalli dei dati confrontati. Per compilare automaticamente questi campi, puoi semplicemente selezionare le celle della tabella desiderate.
Fare clic su OK per chiudere la finestra degli argomenti della funzione. Il coefficiente di correlazione calcolato apparirà nella cella.

La correlazione può essere diretta (se il coefficiente è maggiore di zero) e inversa (da -1 a 0).

Il primo significa che all'aumentare di un parametro, aumenta anche l'altro. Una correlazione inversa (negativa) riflette il fatto che all'aumentare di una variabile, l'altra diminuisce.

La correlazione può essere vicina allo zero. Questo di solito indica che i parametri studiati non sono correlati tra loro. Ma a volte si verifica una correlazione zero se viene creato un campione non riuscito che non riflette la relazione o se la relazione ha una natura complessa non lineare.

Se il coefficiente mostra una relazione media o forte (da ±0,5 a ±0,99), va ricordato che si tratta solo di una relazione statistica, che non garantisce affatto l'influenza di un parametro su un altro. È anche impossibile escludere la situazione in cui entrambi i parametri sono indipendenti l'uno dall'altro, ma sono influenzati da un terzo fattore non contabilizzato. Excel ti aiuta a calcolare istantaneamente il coefficiente di correlazione, ma di solito solo metodi quantitativi insufficiente per stabilire relazioni causali in campioni comparabili.

Il coefficiente di correlazione (o coefficiente di correlazione lineare) è indicato come "r" (in casi rari come "ρ") e caratterizza una correlazione lineare (ovvero una relazione data da qualche valore e direzione) di due o più variabili. Il valore del coefficiente è compreso tra -1 e +1, ovvero la correlazione può essere sia positiva che negativa. Se il coefficiente di correlazione è -1, c'è una perfetta correlazione negativa; se il coefficiente di correlazione è +1, c'è una perfetta correlazione positiva. In altri casi, c'è una correlazione positiva, una correlazione negativa o nessuna correlazione tra le due variabili. Il coefficiente di correlazione può essere calcolato manualmente, con calcolatori online gratuiti o con un buon calcolatore grafico.

Passi

Calcolo manuale del coefficiente di correlazione

Raccogliere dati. Prima di iniziare a calcolare il coefficiente di correlazione, esamina la coppia di numeri data. È meglio scriverli in una tabella che può essere disposta verticalmente o orizzontalmente. Etichetta ogni riga o colonna come una "x" e una "y".

Ad esempio, date quattro coppie di valori (numeri) delle variabili "x" e "y". Puoi creare la seguente tabella:
- x || si
- 1 || 1
- 2 || 3
- 4 || 5
- 5 || 7

Calcola la media aritmetica "x". Per fare ciò, somma tutti i valori di "x", quindi dividi il risultato per il numero di valori.
- Nel nostro esempio, ci vengono dati quattro valori per la variabile "x". Per calcolare la media aritmetica "x", somma questi valori, quindi dividi la somma per 4. I calcoli verranno scritti come segue:
- μ 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)
Trova la media aritmetica "y". Per fare ciò, segui gli stessi passaggi, ovvero somma tutti i valori di "y", quindi dividi la somma per il numero di valori.
- Nel nostro esempio, ci vengono dati quattro valori per la variabile "y". Aggiungi questi valori, quindi dividi la somma per 4. I calcoli verranno scritti come segue:
- μ 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)
Calcola la deviazione standard di "x". Una volta calcolate le medie di x e y, trova le deviazioni standard di queste variabili. La deviazione standard viene calcolata utilizzando la seguente formula:
- σ 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)
Calcola la deviazione standard "y". Segui i passaggi nel passaggio precedente. Usa la stessa formula, ma sostituisci i valori "y" in essa.
- Nel nostro esempio, i calcoli saranno scritti come segue:
- σ 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)
Annota la formula di base per il calcolo del coefficiente di correlazione. Questa formula include medie, deviazioni standard e il numero (n) di coppie di numeri di entrambe le variabili. Il coefficiente di correlazione è indicato come "r" (in rari casi, come "ρ"). Questo articolo utilizza la formula per calcolare il coefficiente di correlazione di Pearson.
- Qui e in altre fonti, le quantità possono essere denotate in modi diversi. Ad esempio, alcune formule hanno "ρ" e "σ", mentre altre hanno "r" e "s". Alcuni libri di testo danno altre formule, ma sono l'equivalente matematico della formula precedente.
Hai calcolato le medie e le deviazioni standard di entrambe le variabili, quindi puoi utilizzare la formula per calcolare il coefficiente di correlazione. Ricordiamo che "n" è il numero di coppie di valori di entrambe le variabili. Il valore di altre grandezze è stato calcolato in precedenza.
- Nel nostro esempio, i calcoli saranno scritti come segue:
- ρ = (1 n - 1) Σ (x - μ x σ x) ∗ (y - μ y σ y) (\ Displaystyle \ rho = \ sinistra ((\ frac (1) (n-1)) \ destra) \Sigma \left((\frac (x-\mu _(x))(\sigma _(x)))\right)*\left((\frac (y-\mu _(y))(\sigma _(y)))\destra))
- ρ = (1 3) ∗ (\ displaystyle \ rho = \ sinistra ((\ frac (1) (3)) \ destra) *)[ (1 - 3 1, 83) ∗ (1 - 4 2, 58) + (2 - 3 1, 83) ∗ (3 - 4 2, 58) (\displaystyle \left((\frac (1-3)( 1.83))\destra)*\sinistra((\frac (1-4)(2.58))\destra)+\sinistra((\frac (2-3)(1.83))\destra) *\sinistra((\ frac (3-4)(2,58))\destra))
  + (4 - 3 1 , 83) ∗ (5 - 4 2 , 58) + (5 - 3 1 , 83) ∗ (7 - 4 2 , 58) (\ displaystyle + \ left ((\ frac (4-3 )(1.83))\destra)*\sinistra((\frac (5-4)(2.58))\destra)+\sinistra((\frac (5-3)(1.83))\ destra)*\sinistra( (\frac (7-4)(2,58))\destra))]
- ρ = (1 3) ∗ (6 + 1 + 1 + 6 4 , 721) (\ Displaystyle \ rho = \ sinistra ((\ frac (1) (3)) \ destra) * \ sinistra ((\ frac (6 +1+1+6)(4.721))\destra))
- ρ = (1 3) ∗ 2 , 965 (\ displaystyle \ rho = \ sinistra ((\ frac (1) (3)) \ destra) * 2,965)
- ρ = (2 , 965 3) (\ displaystyle \ rho = \ sinistra ((\ frac (2.965) (3)) \ destra))
- ρ = 0,988 (\ displaystyle \ rho = 0,988)
Analizza il risultato. Nel nostro esempio, il coefficiente di correlazione è 0,988. Questo valore in qualche modo caratterizza un dato insieme di coppie di numeri. Prestare attenzione al segno e alla grandezza del valore.
- Poiché il valore del coefficiente di correlazione è positivo, esiste una correlazione positiva tra le variabili "x" e "y". Cioè, quando il valore di "x" aumenta, aumenta anche il valore di "y".
- Poiché il valore del coefficiente di correlazione è molto vicino a +1, i valori delle variabili x e y sono altamente correlati. Se metti dei punti piano coordinato, saranno posizionati vicino a una linea retta.
Utilizzo di calcolatrici online per calcolare il coefficiente di correlazione
1. Trova una calcolatrice su Internet per calcolare il coefficiente di correlazione. Questo coefficiente è spesso calcolato nelle statistiche. Se ci sono molte coppie di numeri, è praticamente impossibile calcolare manualmente il coefficiente di correlazione. Pertanto, ci sono calcolatori online per il calcolo del coefficiente di correlazione. Nel motore di ricerca, inserisci "calcolatore del coefficiente di correlazione" (senza virgolette).
2. Inserisci i dati. Leggi le istruzioni sul sito per inserire correttamente i dati (coppie di numeri). È estremamente importante inserire le coppie di numeri appropriate; altrimenti otterrai un risultato sbagliato. Tieni presente che diversi siti Web hanno diversi formati di immissione dei dati.
  - Ad esempio, sul sito http://ncalculators.com/statistics/correlation-coefficient-calculator.htm, i valori delle variabili "x" e "y" sono inseriti in due righe orizzontali. I valori sono separati da virgole. Cioè, nel nostro esempio, i valori di "x" sono inseriti in questo modo: 1,2,4,5, e i valori di "y" sono così: 1,3,5,7.
  - Su un altro sito, http://www.alcula.com/calculators/statistics/correlation-coefficient/ , i dati vengono inseriti verticalmente; in questo caso, non confondere le coppie di numeri corrispondenti.
3. Calcolare il coefficiente di correlazione. Dopo aver inserito i dati, è sufficiente fare clic sul pulsante "Calcola", "Calcola" o simili per ottenere il risultato.
  
  Utilizzando una calcolatrice grafica
  1. Inserisci i dati. Prendi una calcolatrice grafica, passa alla modalità di calcolo statistico e seleziona il comando Modifica.
    - Su calcolatrici diverse, devi premere tasti diversi. Questo articolo si concentra sulla calcolatrice Texas Instruments TI-86.
    - Per passare alla modalità di calcolo statistico, premere - Stat (sopra il tasto "+"). Quindi premere F2 - Modifica (Modifica).
  2. Elimina i dati salvati in precedenza. La maggior parte dei calcolatori conserva le statistiche inserite fino a quando non le cancelli. Per evitare di confondere i vecchi dati con i nuovi dati, eliminare prima tutte le informazioni memorizzate.
    - Utilizzare i tasti freccia per spostare il cursore ed evidenziare l'intestazione "xStat". Quindi premere Cancella e Invio per cancellare tutti i valori inseriti nella colonna xStat.
    - Utilizzare i tasti freccia per evidenziare l'intestazione "yStat". Quindi premere Cancella e Invio per cancellare tutti i valori inseriti nella colonna yStat.
  3. Inserisci i dati iniziali. Utilizzare i tasti freccia per spostare il cursore sulla prima cella sotto l'intestazione "xStat". Immettere il primo valore e premere Invio. Nella parte inferiore dello schermo verrà visualizzato "xStat (1) = __", con il valore immesso al posto di uno spazio. Dopo aver premuto Invio, il valore immesso apparirà nella tabella e il cursore si sposterà sulla riga successiva; questo visualizzerà "xStat(2) = __" nella parte inferiore dello schermo.
    - Inserisci tutti i valori della variabile "x".
    - Una volta inseriti tutti i valori per la variabile x, utilizzare i tasti freccia per navigare fino alla colonna yStat e inserire i valori per la variabile y.
    - Dopo aver inserito tutte le coppie di numeri, premere Esci per cancellare lo schermo e uscire dalla modalità di aggregazione.
  4. Calcolare il coefficiente di correlazione. Caratterizza la vicinanza dei dati a una linea retta. Il calcolatore grafico può determinare rapidamente la retta appropriata e calcolare il coefficiente di correlazione.
    - Fare clic su Stat (statistiche) - Calc (calcoli). Sulla TI-86, premere - - .
    - Selezionare la funzione "Regressione lineare". Sulla TI-86, premere , contrassegnato con "LinR". La riga "LinR _" verrà visualizzata sullo schermo con un cursore lampeggiante.
    - Ora inserisci i nomi di due variabili: xStat e yStat.
      - Sulla TI-86, apri l'elenco dei nomi; per fare ciò premere – – .
      - Le variabili disponibili vengono visualizzate nella riga inferiore dello schermo. Seleziona (probabilmente premendo F1 o F2), inserisci una virgola, quindi seleziona .
      - Premere Invio per elaborare i dati inseriti.
  5. Analizzare i risultati. Premendo Invio, sullo schermo verranno visualizzate le seguenti informazioni:
    - y = un + b x (\ displaystyle y = a + bx): è una funzione che descrive una retta. Si noti che la funzione non è scritta in forma standard (y = kx + b).
    - un = (\displaystyle a=). Questa è la coordinata y del punto in cui la linea si interseca con l'asse y.
    - b = (\displaystyle b=). esso pendenza dritto.
    - corr = (\displaystyle (\text(corr))=). Questo è il coefficiente di correlazione.
    - n = (\displaystyle n=). Questo è il numero di coppie di numeri che sono state utilizzate nel calcolo.

Un'utilità ampiamente utilizzata in molte aziende e imprese. La realtà è che quasi tutti i dipendenti devono essere esperti in Excel in una certa misura, poiché questo programma viene utilizzato per risolvere una gamma molto ampia di attività. Quando si lavora con le tabelle, è spesso necessario determinare se determinate variabili sono correlate tra loro. Per questo, viene utilizzata la cosiddetta correlazione. In questo articolo, daremo uno sguardo dettagliato a come calcolare il coefficiente di correlazione in Excel. Scopriamolo. Andare!

Cominciamo con qual è il coefficiente di correlazione in generale. Indica il grado di relazione tra due elementi e va sempre da -1 (forte relazione inversa) a 1 (forte relazione in avanti). Se il coefficiente è 0, ciò indica che non esiste alcuna relazione tra i valori.

Ora, dopo aver affrontato la teoria, passiamo alla pratica. Per trovare la relazione tra le variabili e y, utilizzare la funzione integrata di Microsoft Excel "CORREL". Per fare ciò, fare clic sul pulsante della procedura guidata della funzione (si trova accanto al campo della formula). Nella finestra che si apre, seleziona "CORREL" dall'elenco delle funzioni. Successivamente, imposta l'intervallo nei campi "Array1" e "Array2". Ad esempio, per "Array1" selezionare i valori y e per "Array2" selezionare i valori x. Di conseguenza, otterrai il coefficiente di correlazione calcolato dal programma.

Il seguente metodo sarà rilevante per gli studenti che devono trovare una dipendenza utilizzando una data formula. Prima di tutto, devi conoscere i valori medi delle variabili x e y. Per fare ciò, seleziona i valori della variabile e utilizza la funzione "MEDIA". Successivamente, è necessario calcolare la differenza tra ogni x e x avg e y avg. Nelle celle selezionate scrivi formule x-x, y-. Non dimenticare di appuntare le celle con valori medi. Quindi trascina la formula verso il basso in modo che si applichi al resto dei numeri.

Ora che abbiamo tutti i dati necessari, possiamo calcolare la correlazione. Moltiplica le differenze risultanti in questo modo: (x-x avg) * (y-y avg). Dopo aver ottenuto il risultato per ciascuna delle variabili, somma i numeri risultanti utilizzando la funzione di somma automatica. Ecco come viene calcolato il numeratore.

Ora passiamo al denominatore. Le differenze calcolate devono essere elevate al quadrato. Per fare ciò, in una colonna separata, inserisci le formule: (x-x avg) 2 e (y-y avg) 2 . Quindi allunga le formule all'intera gamma. Quindi, utilizzando il pulsante "Somma automatica", trova la somma per tutte le colonne (per x e per y). Resta da moltiplicare le somme trovate ed estrarne Radice quadrata. L'ultimo passo è dividere il numeratore per il denominatore. Il risultato ottenuto sarà il coefficiente di correlazione desiderato.

Come puoi vedere, sapendo come lavorare correttamente con le funzioni di Microsoft Excel, puoi semplificare notevolmente il compito di calcolare espressioni matematiche complesse. Grazie agli strumenti implementati nel programma, puoi facilmente eseguire un'analisi di correlazione in Excel in un paio di minuti, risparmiando tempo e fatica. Scrivi nei commenti se l'articolo ti ha aiutato a capire il problema, chiedi tutto ciò che ti interessava sull'argomento discusso.

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

Un 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
*x K*	25.20000	26.40000	26.00000	25.80000	24.90000	25.70000	25.70000	25.70000	26.10000	25.80000
*e 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
*x K*	25.90000	26.20000	25.60000	25.40000	26.60000	26.20000	26.00000	22.10000	25.90000	25.80000
*e 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
*x K*	25.90000	26.30000	26.10000	26.00000	26.40000	25.80000
*e k*	30.70000	30.10000	30.60000	30.50000	30.70000	30.80000

È necessario calcolare/costruire:
- coefficiente di correlazione;
- verificare l'ipotesi della dipendenza delle variabili aleatorie X e Y, ad un livello di significatività α = 0.05;
- coefficienti dell'equazione di regressione lineare;
- diagramma a dispersione (campo di correlazione) e grafico a linee di regressione;

SOLUZIONE:

1. Calcola il coefficiente di correlazione.

Il coefficiente di correlazione è un indicatore dell'influenza probabilistica reciproca 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 è una prova forte connessione tra i valori e se più vicino a 0 - quindi, questo indica una connessione debole o la sua assenza. Se il valore assoluto R uguale a uno, allora possiamo parlare di una relazione funzionale tra quantità, cioè una quantità può essere espressa in termini di un'altra usando una funzione matematica.

È possibile calcolare il coefficiente di correlazione utilizzando le seguenti formule:

K = 1

(xk -Mx) 2 , e 2 =

K = 1

xK,

Mio

o secondo la formula

Rx,y

M xy - M x M y

SxSy

(1.4), dove:

K = 1

xK,

Mio

K = 1

yk,

Mxy

K = 1

x k y k (1.5)

Sx2

K = 1

x k 2 - M x 2,

S e 2

K = 1

y k 2 - M y 2 (1.6)

In pratica, la formula (1.4) è più spesso utilizzata per calcolare il coefficiente di correlazione, poiché richiede meno calcoli. Tuttavia, se la covarianza è stata calcolata in precedenza cov(X,Y), allora è più vantaggioso usare la formula (1.1), perché oltre al valore effettivo della covarianza, è possibile utilizzare anche i risultati dei calcoli intermedi.

1.1 Calcolare il coefficiente di correlazione utilizzando la formula (1.4), per questo calcoliamo i valori x k 2 , y k 2 e x k y k e li inseriamo nella tabella 1.

Tabella 1

K	*x K*	*e k*	x K 2	e k 2	*x Ke 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 con la formula (1.5).

1.2.1. x K

x 1 + x 2 + ... + x 26 = 25,20000 + 26,40000 + ... + 25,80000 = 669,500000

1.2.2.

669.50000 / 26 = 25.75000

M x = 25,750000

1.3. Allo stesso modo, calcoliamo M y.

1.3.1. Aggiungiamo tutti gli elementi in sequenza e k

y 1 + y 2 + … + y 26 = 30,80000 + 29,40000 + ... + 30,80000 = 793,000000

1.3.2. Dividi la somma risultante per il numero di elementi del campione

793.00000 / 26 = 30.50000

M y = 30,500000

1.4. Allo stesso modo, calcoliamo M xy.

1.4.1. Aggiungiamo in sequenza tutti gli elementi della sesta 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

M xy = 785,108846

1.5. Calcolare il valore di S x 2 usando 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 dall'ultimo numero il quadrato del valore M x otteniamo il valore per S x 2

Sx2 = 663.72731 - 25.75000 2 = 663.72731 - 663.06250 = 0.66481

1.6. Calcolare il valore di S y 2 con la formula (1.6.).

1.6.1. Aggiungiamo in sequenza tutti gli elementi della quinta 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 dall'ultimo numero il quadrato di M y , otteniamo il valore di S y 2

S e 2 = 930.45538 - 30.50000 2 = 930.45538 - 930.25000 = 0.20538

1.7. Calcoliamo il prodotto di S x 2 e S y 2.

S x 2 S y 2 = 0,66481 0,20538 = 0,136541

1.8. Estraiamo la radice quadrata dell'ultimo numero, otteniamo il valore S x S y.

S x S y = 0,36951

1.9. Calcolare il valore del coefficiente di correlazione secondo la formula (1.4.).

R = (785,10885 - 25,75000 30,50000) / 0,36951 = (785,10885 - 785,37500) / 0,36951 = -0,72028

RISPOSTA: Rx,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 quindi può discostarsi dal suo valore generale, è necessario verificare la significatività del coefficiente di correlazione. Il controllo viene effettuato utilizzando il criterio t:

t =

Rx,y


√	n-2


√	1 - R2x,y

(2.1)

Valore casuale t segue la distribuzione t di Student e secondo la tabella della distribuzione t è necessario trovare il valore critico del criterio (t cr.α) ad un dato livello di significatività α . Se il modulo t calcolato dalla formula (2.1) risulta essere minore di t cr.α , allora le dipendenze tra variabili casuali X e Y non lo sono. Altrimenti, i dati sperimentali non contraddicono l'ipotesi sulla dipendenza delle variabili casuali.

2.1. Calcola il valore del criterio t secondo la formula (2.1) otteniamo:

t =

-0.72028


√	26 - 2


√	1 - (-0.72028) 2

= -5.08680

2.2. Determiniamo il valore critico del parametro t cr.α dalla tabella della distribuzione t

Il valore desiderato t kr.α si trova all'intersezione della riga corrispondente al numero di gradi di libertà e della colonna corrispondente a un determinato livello di significatività α .
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)	a = 0,1	a = 0,05	a = 0,02	a = 0,01	a = 0,002	a = 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 è minore di quello critico t = 5.08680, tcr.α = 2.064, quindi dati sperimentali, con una probabilità di 0,95(1 - α ), non contraddire l'ipotesi sulla dipendenza delle variabili casuali X e Y.

3. Calcoliamo i coefficienti dell'equazione di regressione lineare.

L'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 X sia libera e Y dipenda da X, allora l'equazione di regressione sarà scritta come segue

Y = a + b X (3.1), dove:

Rx,y

σx

Rx,y

(3.2),

a = M y - b M x (3.3)

Il coefficiente calcolato dalla formula (3.2) bè chiamato coefficiente di regressione lineare. In alcune fonti un chiamato coefficiente costante regressione e b secondo le variabili.

Gli errori di previsione Y per un dato valore X sono calcolati dalle formule:

Viene anche chiamato il valore σ y/x (formula 3.4). deviazione standard residua, caratterizza l'allontanamento di Y dalla retta di regressione descritta dall'equazione (3.1) a un valore (dato) fisso di X.

S y 2 / S x 2 = 0,20538 / 0,66481 = 0,30894. Estraiamo la radice quadrata dall'ultimo numero - otteniamo:
S y / S x = 0,55582

3.3 Calcolare il coefficiente b per formula (3.2)

b = -0.72028 0.55582 = -0.40035

3.4 Calcolare il coefficiente a per formula (3.3)

un = 30.50000 - (-0.40035 25.75000) = 40.80894

3.5 Stimare gli errori dell'equazione di regressione.

3.5.1 Estraiamo la radice quadrata da S y 2 e otteniamo:

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

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

4. Costruiamo uno scatterplot (campo di correlazione) e un grafico della retta di regressione.

Un grafico a dispersione è una rappresentazione grafica delle coppie corrispondenti (x k , y k ) come punti in un piano, in coordinate rettangolari con gli assi X e Y. Il campo di correlazione è una delle rappresentazioni grafiche di un campione collegato (accoppiato). Nello stesso sistema di coordinate viene tracciato anche il grafico della retta di regressione. Le scale ei punti di partenza sugli assi dovrebbero essere scelti con cura in modo che il diagramma sia il più chiaro possibile.

4.1. Troviamo che 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 è rispettivamente il 2° e il 18° elemento, y min = 29,40000 e y max = 31,60000.

4.3. Sull'asse delle ascisse, selezioniamo il punto iniziale appena a sinistra del punto x 18 = 22.10000, e una scala tale che il punto x 15 = 26.60000 si adatti all'asse e gli altri punti siano chiaramente distinti.

4.4. Sull'asse y, selezioniamo il punto iniziale appena a sinistra del punto y 2 = 29.40000, e una scala tale che il punto y 18 = 31.60000 si adatti all'asse e gli altri punti siano chiaramente distinti.

4.5. Sull'asse delle ascisse posizioniamo i valori x k , e sull'asse delle ordinate posizioniamo i valori y k .

4.6. Mettiamo i punti (x 1, y 1), (x 2, y 2), ..., (x 26, y 26) sul piano delle coordinate. Otteniamo un grafico a dispersione (campo di correlazione), mostrato nella figura seguente.

4.7. Tracciamo una linea di regressione.

Per fare ciò, troviamo due diversi punti con coordinate (x r1 , y r1) e (x r2 , y r2) che soddisfano l'equazione (3.6), li posizioniamo sul piano delle coordinate e tracciamo una linea attraverso di essi. Prendiamo x min = 22.10000 come ascissa del primo punto. Sostituiamo il valore di x min nell'equazione (3.6), otteniamo l'ordinata del primo punto. Quindi, abbiamo un punto con coordinate (22.10000, 31.96127). Analogamente si ottengono le coordinate del secondo punto, ponendo come ascissa il valore x max = 26,60000. Il secondo punto sarà: (26.60000, 30.15970).

La linea di regressione è mostrata nella figura sottostante in rosso

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