Come calcolare il coefficiente di correlazione tra due variabili in Excel?

Di solito utilizziamo il coefficiente di correlazione (un valore compreso tra -1 e 1) per indicare la forza e la direzione della relazione lineare tra due variabili. Il coefficiente di correlazione è una statistica ampiamente utilizzata che aiuta a comprendere le relazioni, come ad esempio il legame tra vendite e spese pubblicitarie, temperatura e vendite di gelati, o altri dati appaiati. In Excel, ci sono diversi metodi semplici per calcolare il coefficiente di correlazione, inclusi funzioni integrate e strumenti di analisi.

Nota: Un coefficiente di correlazione di +1 indica una perfetta relazione lineare positiva, il che significa che all'aumentare della variabile X, aumenta anche la variabile Y; allo stesso modo, al diminuire di X, Y diminuisce. Viceversa, un valore di -1 mostra una perfetta correlazione negativa, quindi quando X aumenta, Y diminuisce, e viceversa. Un coefficiente vicino a 0 suggerisce una scarsa o assente relazione lineare tra le variabili.

Metodo A: Usa direttamente la funzione CORRELAZIONE

Metodo B: Applica l'analisi dei dati e genera l'analisi

Metodo C: Usa la funzione PEARSON come alternativa

Metodo D: Usa il codice VBA per calcolare i coefficienti di correlazione per più coppie

Metodo A: Usa direttamente la funzione CORRELAZIONE

Considera due elenchi di dati, ciascuno rappresentante una variabile. Se desideri calcolare il coefficiente di correlazione tra queste due variabili in Excel, questo metodo è sia veloce che efficiente.

Per un uso pratico, assicurati che entrambi gli intervalli di dati siano numerici e contengano lo stesso numero di osservazioni. Ad esempio, se hai i seguenti dati appaiati:

Seleziona una cella vuota dove desideri visualizzare il risultato del calcolo. Inserisci la seguente formula e poi premi il tasto "Invio" per calcolare il coefficiente di correlazione:

=CORREL(A2:A7,B2:B7)

In questa formula, A2:A7 e B2:B7 rappresentano le due liste di variabili che desideri analizzare. Gli intervalli devono avere la stessa lunghezza e ogni coppia deve corrispondere alla stessa osservazione.

Consiglio pratico: CORRELAZIONE ignora automaticamente le celle vuote e il testo, ma se non ci sono coppie numeriche valide nelle due colonne, restituirà un errore #DIV/0!. Assicurati che i tuoi dati siano allineati correttamente e contengano coppie numeriche per un calcolo accurato della correlazione.

Una volta calcolato il coefficiente di correlazione, puoi inserire un grafico a linee per osservare visivamente le relazioni e interpretare ulteriormente la correlazione, come mostrato di seguito:

Questo metodo è ideale per verifiche rapide e manuali tra due piccoli set di dati o quando si lavora interattivamente all'interno del foglio di calcolo. È adatto per utenti che cercano un risultato immediato senza bisogno di output statistici avanzati.

Sblocca la Magia di Excel con Kutools AI

• Esecuzione Intelligente: Esegui operazioni sulle celle, analizza i dati e crea grafici — tutto guidato da semplici comandi.
• Formule Personalizzate: Genera formule su misura per ottimizzare i tuoi flussi di lavoro.
• Codifica VBA: Scrivi e implementa codice VBA senza sforzo.
• Interpretazione delle Formule: Comprendi facilmente formule complesse.
• Traduzione del Testo: Superare le barriere linguistiche all'interno dei tuoi fogli di calcolo.

Potenzia le tue capacità di Excel con strumenti alimentati dall'IA. Scarica Ora ed esperisci un'efficienza mai vista prima!

Metodo B: Applica l'analisi dei dati e genera l'analisi

Se hai bisogno di analizzare la correlazione tra più variabili contemporaneamente o desideri una tabella di output più completa, il “Strumento di analisi dati” di Excel offre una soluzione utile. Questo componente aggiuntivo genera una matrice di correlazione e ti consente di confrontare più variabili in un unico passaggio, il che è prezioso per dataset più grandi o per la creazione di rapporti statistici.

1. Se hai già aggiunto il componente aggiuntivo Strumento di analisi dati alla scheda Dati, puoi passare direttamente al passaggio 3. Altrimenti, fai clic su File > Opzioni. Nella finestra di dialogo “Opzioni di Excel”, scegli Componenti aggiuntivi dal riquadro a sinistra, e poi fai clic sul pulsante Vai accanto alla casella “Componenti aggiuntivi di Excel”.

2. Nella finestra di dialogo “Componenti aggiuntivi”, seleziona la casella etichettata Strumento di analisi, poi fai clic su OK. Questo aggiungerà il gruppo "Analisi dati" alla scheda Dati .

3. Successivamente, fai clic su Dati > Analisi dati. Nella finestra pop-up “Analisi dati”, scegli Correlazione dalla lista e poi fai clic su OK.

4. Nella finestra di dialogo Correlazione, configura quanto segue:
1) Seleziona l'intervallo contenente i tuoi dati.
2) Scegli l'opzione "Colonne" o "Righe", a seconda di come sono organizzati i tuoi dati.
3) Se i tuoi dati includono intestazioni, seleziona l’opzione “Etichette nella prima riga”.
4) Specifica una posizione di output in “Opzioni di output” per visualizzare i risultati.

5. Fai clic su OK per generare la tabella di analisi della correlazione. I coefficienti di correlazione verranno presentati nell'intervallo specificato.

Questo metodo è adatto quando è necessario valutare le relazioni tra più di due variabili o si desidera una tabella riassuntiva per scopi di reporting. L'output dell'analisi dei dati è conciso ma non fornisce statistiche aggiuntive sulla significatività. Se ricevi risultati inaspettati, verifica nuovamente i dati per coerenza, celle vuote e selezione corretta degli intervalli.

Metodo C: Usa la funzione PEARSON come alternativa

Oltre a CORRELAZIONE, Excel fornisce la funzione PEARSON, che calcola anche il coefficiente di correlazione di Pearson tra due variabili. Funzionalmente, PEARSON e CORRELAZIONE restituiscono lo stesso risultato. Tuttavia, PEARSON segue rigorosamente la formula matematica originale, mentre CORRELAZIONE è ottimizzato per l'ambiente di Excel. Se sei abituato alla teoria statistica o utilizzi strumenti statistici esterni a Excel, PEARSON potrebbe risultare più familiare.

Ad esempio, con due elenchi numerici in A2:A7 e B2:B7, puoi calcolare la correlazione come segue:

1. Seleziona una cella dove desideri visualizzare il risultato, e inserisci questa formula:

=PEARSON(A2:A7,B2:B7)

2. Premi Invio per completare il calcolo. Se desideri analizzare altre coppie di dati, regola di conseguenza gli intervalli delle celle, oppure trascina la formula in altre celle.
Suggerimenti: PEARSON ignora il testo o i valori logici, quindi assicurati che entrambi gli intervalli contengano solo valori numerici e abbiano la stessa lunghezza. Se mancano dati in una colonna, allinea gli intervalli di conseguenza per evitare errori.

Usare PEARSON è particolarmente pratico per gli utenti che migrano da altri software statistici, o per ambienti accademici dove è prevista una stretta aderenza alla terminologia. Sia CORRELAZIONE che PEARSON danno lo stesso risultato per gli usi tipici in Excel.

Se riscontri un errore #DIV/0!, verifica che entrambi gli intervalli abbiano la stessa lunghezza e non contengano celle vuote o non numeriche non corrispondenti.

Punti a favore: Facile da usare, coerente con il software statistico; Svantaggi: Non offre differenze significative rispetto a CORRELAZIONE per la maggior parte degli utenti.

Metodo D: Usa il codice VBA per calcolare i coefficienti di correlazione per più coppie

Se hai bisogno di automatizzare il calcolo dei coefficienti di correlazione per diverse coppie di dati (ad esempio, quando lavori con molte combinazioni di variabili), scrivere una semplice macro VBA è una scelta efficiente. Questo metodo è adatto agli utenti avanzati che desiderano elaborare grandi set di dati o automatizzare attività di analisi ripetitive.

1. Per utilizzare questo metodo, apri prima l'editor VBA facendo clic su Sviluppatore > Visual Basic. Nella finestra di Visual Basic per Applicazioni, vai su Inserisci > Modulo, e incolla il seguente codice nel modulo:

Sub BatchCalculateCorrelations()
    Dim ws As Worksheet
    Dim rng1 As Range, rng2 As Range
    Dim lastRow As Long
    Dim i As Long
    Dim resultCol As Range
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    Set rng1 = Application.InputBox("Select first variable range (single column)", xTitleId, Type:=8)
    Set rng2 = Application.InputBox("Select second variable range (multiple columns)", xTitleId, Type:=8)
    Set resultCol = Application.InputBox("Select starting cell for output", xTitleId, Type:=8)
    
    If rng1.Rows.Count <> rng2.Rows.Count Then
        MsgBox "The two data ranges must have the same number of rows.", vbCritical, xTitleId
        Exit Sub
    End If
    
    For i = 1 To rng2.Columns.Count
        resultCol.Cells(1, i).Value = "Correlation with " & rng2.Cells(1, i).EntireColumn.Column
        resultCol.Cells(2, i).Value = WorksheetFunction.Correl(rng1, rng2.Columns(i))
    Next i
End Sub

2. Dopo aver inserito il codice, chiudi l'editor VBA. In Excel, premi Alt + F8, seleziona BatchCalculateCorrelations, e fai clic su Esegui. Ti verrà chiesto di selezionare:

• Il primo intervallo di variabili (una singola colonna, ad esempio A2:A7)
• Il secondo intervallo di variabili (una o più colonne, ad esempio B2:D7)
• La cella in cui desideri che i risultati inizino (ad esempio F2)

La macro calcola quindi il coefficiente di correlazione tra la prima variabile e ogni colonna nel secondo intervallo, visualizzando i risultati orizzontalmente a partire dalla cella scelta.

Vantaggi: Automatizza calcoli ripetitivi, risparmia tempo significativo con grandi set di dati e garantisce coerenza.

Se riscontri problemi come “I due intervalli di dati devono avere lo stesso numero di righe”, assicurati che tutte le colonne selezionate abbiano esattamente lo stesso numero di righe e non abbiano righe vuote. Per la risoluzione degli errori, verifica se le macro sono abilitate e se gli intervalli sono stati selezionati correttamente.

Quando si lavora con i coefficienti di correlazione in Excel, scegliere il metodo giusto dipende dalla struttura dei dati e dalle esigenze di analisi. Per calcoli rapidi e una tantum tra due serie, formule come CORRELAZIONE o PEARSON sono efficienti e facili da usare. Per più variabili o la necessità di tabelle riassuntive, Strumento di analisi dati è molto pratico. Se richiedi analisi ripetute su grandi set di dati o vuoi flussi di lavoro personalizzati, considera di automatizzare con VBA per risparmiare tempo e ridurre gli errori umani.
Assicurati sempre che gli intervalli di dati siano allineati, puliti e non contengano celle vuote o non numeriche per evitare errori di formula. Se riscontri risultati inaspettati, verifica nuovamente le selezioni e i tipi di dati.

Articoli correlati

• Calcola la variazione percentuale o la differenza tra due numeri in Excel
  Questo articolo parla del calcolo della variazione percentuale o della differenza tra due numeri in Excel.

• Calcola o assegna voti letterali in Excel
  Assegnare voti letterali a ciascuno studente in base ai loro punteggi può essere un compito comune per un insegnante. Ad esempio, ho una scala di valutazione definita dove il punteggio 0-59 = F, 60-69 = D, 70-79 = C, 80-89 = B, e 90-100 = A, vedi altro.

• Calcola il tasso di sconto o il prezzo in Excel
  Quando arriva il Natale, ci devono essere molte promozioni nei centri commerciali. Ma se i diversi tipi di articoli hanno diversi sconti, come puoi calcolare i tassi di sconto o i prezzi degli articoli diversi?

• Conta il numero di giorni / giorni lavorativi / fine settimana tra due date in Excel
  Forse, a volte, vuoi solo calcolare i giorni lavorativi tra due date, e altre volte, devi contare solo i giorni del fine settimana tra le due date.