Analisi di correlazione in Excel. Un esempio di esecuzione di un'analisi di correlazione

L'analisi di correlazione è un metodo di ricerca comune utilizzato per determinare il livello di dipendenza del 1° valore dal 2°. Il foglio di calcolo ha uno strumento speciale che consente di implementare questo tipo di ricerca.

L'essenza dell'analisi di correlazione

È necessario determinare la relazione tra due diverse grandezze. In altre parole, rivela in quale direzione (più piccolo/più grande) il valore cambia a seconda dei cambiamenti nel secondo.

Scopo dell'analisi di correlazione

La dipendenza viene stabilita quando inizia l'identificazione del coefficiente di correlazione. Questo metodo differisce dall'analisi di regressione, poiché esiste un solo indicatore calcolato utilizzando la correlazione. L'intervallo cambia da +1 a -1. Se è positivo, un aumento del primo valore contribuisce ad un aumento del 2°. Se negativo, un aumento del 1° valore contribuisce ad una diminuzione del 2°. Più alto è il coefficiente, più forte un valore influisce sul secondo.

Importante! Al coefficiente 0, non c'è relazione tra le quantità.

Calcolo del coefficiente di correlazione

Analizziamo il calcolo su più campioni. Ad esempio, sono disponibili dati tabulari, in cui la spesa per la promozione pubblicitaria e il volume delle vendite sono descritti per mesi in colonne separate. Sulla base della tabella, scopriremo il livello di dipendenza del volume delle vendite dai soldi spesi per la promozione pubblicitaria.

Metodo 1: determinazione della correlazione tramite la procedura guidata di funzione

CORREL – una funzione che consente di implementare un'analisi di correlazione. Forma generale – CORRELAZIONE(massiccio1;massiccio2). Istruzioni dettagliate:

1. È necessario selezionare la cella in cui si prevede di visualizzare il risultato del calcolo. Fare clic su "Inserisci funzione" che si trova a sinistra del campo di testo per inserire la formula.

2. Si apre la procedura guidata delle funzioni. Qui devi trovare CORRELAZIONE, cliccaci sopra, quindi su “OK”.

3. Si apre la finestra degli argomenti. Nella riga “Array1” è necessario inserire le coordinate degli intervalli del 1° dei valori. In questo esempio, questa è la colonna Sales Value. Devi solo selezionare tutte le celle che si trovano in questa colonna. Allo stesso modo, è necessario aggiungere le coordinate della seconda colonna alla riga "Array2". Nel nostro esempio, questa è la colonna Costi pubblicitari.

4. Dopo aver inserito tutti gli intervalli, fare clic sul pulsante "OK".

Il coefficiente è stato visualizzato nella cella indicata all'inizio delle nostre azioni. Il risultato ottenuto è 0,97. Questo indicatore riflette l'elevata dipendenza del primo valore dal secondo.

Metodo 2: calcolare la correlazione utilizzando l'Analysis ToolPak

C'è un altro metodo per determinare la correlazione. Qui viene utilizzata una delle funzioni presenti nel pacchetto di analisi. Prima di utilizzarlo, è necessario attivare lo strumento. Istruzioni dettagliate:

1. Vai alla sezione "File".

2. Si aprirà una nuova finestra, in cui è necessario fare clic sulla sezione "Impostazioni".
3. Fare clic su "Componenti aggiuntivi".
4. Troviamo in basso l'elemento “Gestione”. Qui è necessario selezionare "Componenti aggiuntivi di Excel" dal menu di scelta rapida e fare clic su "OK".

5. Si è aperta una finestra speciale per i componenti aggiuntivi. Metti un segno di spunta accanto all'elemento "Pacchetto di analisi". Facciamo clic su "OK".
6. L'attivazione è riuscita. Ora andiamo a Dati. È apparso il blocco "Analisi", in cui è necessario fare clic su "Analisi dati".
7. Nella nuova finestra che appare, seleziona l'elemento “Correlazione” e clicca su “OK”.

8. Sullo schermo è apparsa la finestra delle impostazioni dell'analisi. Nella riga “Input interval” è necessario inserire l'intervallo di tutte le colonne che partecipano all'analisi. In questo esempio, queste sono le colonne "Valore vendite" e "Costi pubblicitari". Le impostazioni di visualizzazione dell'output sono inizialmente impostate su Nuovo foglio di lavoro, il che significa che i risultati verranno visualizzati su un foglio diverso. Facoltativamente, è possibile modificare la posizione di output del risultato. Dopo aver effettuato tutte le impostazioni, fare clic su "OK".

I punteggi finali sono fuori. Il risultato è lo stesso del primo metodo: 0,97.

Definizione e calcolo del coefficiente di correlazione multipla in MS Excel

Per identificare il livello di dipendenza di più grandezze, vengono utilizzati coefficienti multipli. In futuro, i risultati vengono riepilogati in una tabella separata, chiamata matrice di correlazione.

Guida dettagliata:

1. Nella sezione “Dati” troviamo il già noto blocco “Analisi” e clicchiamo su “Analisi Dati”.

2. Nella finestra che appare, clicca sull'elemento “Correlazione” e clicca su “OK”.
3. Nella riga "Intervallo di input" guidiamo nell'intervallo per tre o più colonne della tabella di origine. L'intervallo può essere inserito manualmente o semplicemente selezionarlo con il LMB e apparirà automaticamente nella riga desiderata. In "Raggruppamento" selezionare il metodo di raggruppamento appropriato. In "Parametro di output" specifica la posizione in cui verranno visualizzati i risultati della correlazione. Facciamo clic su "OK".

4. Pronto! La matrice di correlazione è stata costruita.

Coefficiente di correlazione delle coppie in Excel

Scopriamo come disegnare correttamente il coefficiente di correlazione di coppia in un foglio di calcolo Excel.

Calcolo del coefficiente di correlazione delle coppie in Excel

Ad esempio, hai valori x e y.

X è la variabile dipendente e y è l'indipendente. È necessario trovare la direzione e la forza della relazione tra questi indicatori. Istruzioni passo passo:

1. Troviamo i valori medi usando la funzione CUORE.

2. Calcoliamo ciascuno х и xavg, у и avg utilizzando l'operatore «-».

3. Moltiplichiamo le differenze calcolate.

4. Calcoliamo la somma degli indicatori in questa colonna. Il numeratore è il risultato trovato.

5. Calcola i denominatori della differenza х и x-media, y и y-medio. Per fare ciò, eseguiremo la quadratura.

6. Utilizzando la funzione AUTOSUMMA, trova gli indicatori nelle colonne risultanti. Facciamo la moltiplicazione. Usando la funzione ROOT quadrato il risultato.

7. Calcoliamo il quoziente utilizzando i valori del denominatore e del numeratore.

8. CORREL è una funzione integrata che consente di evitare calcoli complessi. Andiamo alla "Funzione guidata", selezioniamo CORREL e specifichiamo gli array di indicatori х и у. Costruiamo un grafico che mostra i valori ottenuti.

Matrice dei coefficienti di correlazione a coppie in Excel

Analizziamo come calcolare i coefficienti di matrici appaiate. Ad esempio, esiste una matrice di quattro variabili.

Istruzioni passo passo:

1. Andiamo alla "Analisi dei dati", situata nel blocco "Analisi" della scheda "Dati". Seleziona Correlazione dall'elenco visualizzato.
2. Impostiamo tutte le impostazioni necessarie. “Intervallo di input” – l'intervallo di tutte e quattro le colonne. “Intervallo di output” – il luogo in cui vogliamo visualizzare i totali. Facciamo clic sul pulsante "OK".
3. Nel luogo prescelto è stata costruita una matrice di correlazione. Ogni intersezione di una riga e di una colonna è un coefficiente di correlazione. Il numero 1 viene visualizzato quando le coordinate corrispondono.

Funzione CORREL per determinare la relazione e la correlazione in Excel

CORREL – una funzione utilizzata per calcolare il coefficiente di correlazione tra 2 array. Diamo un'occhiata a quattro esempi di tutte le capacità di questa funzione.

Esempi di utilizzo della funzione CORRELAZIONE in Excel

Primo esempio. C'è una targa con le informazioni sugli stipendi medi dei dipendenti dell'azienda nel corso di undici anni e il tasso di cambio di $. È necessario identificare la relazione tra queste due grandezze. La tabella si presenta così:

L'algoritmo di calcolo si presenta così:

Il punteggio visualizzato è vicino a 1. Risultato:

Determinazione del coefficiente di correlazione dell'impatto delle azioni sul risultato

Secondo esempio. Due offerenti si sono rivolti a due diverse agenzie per chiedere aiuto con una promozione di quindici giorni. Ogni giorno veniva condotto un sondaggio sociale, che determinava il grado di sostegno per ciascun candidato. Qualsiasi intervistato può scegliere uno dei due candidati o opporsi a tutti. È necessario determinare quanto ciascuna promozione pubblicitaria abbia influenzato il grado di supporto per i candidati, quale azienda è più efficiente.

Utilizzando le formule seguenti, calcoliamo il coefficiente di correlazione:

• =CORRELAZIONE(A3:A17;B3:B17).
• =CORREZIONE(A3:A17;C3:C17).

risultati:

Dai risultati ottenuti, risulta chiaro che il grado di sostegno per il 1° richiedente aumentava ogni giorno di promozione pubblicitaria, pertanto il coefficiente di correlazione si avvicinava a 1. Quando è stata lanciata la pubblicità, l'altro richiedente aveva un gran numero di fiducia, e per 5 giorni c'era un trend positivo. Quindi il grado di fiducia è diminuito e al quindicesimo giorno è sceso al di sotto degli indicatori iniziali. I punteggi bassi suggeriscono che la promozione ha avuto un impatto negativo sul supporto. Non dimenticare che anche altri fattori concomitanti non considerati in forma tabellare potrebbero influenzare gli indicatori.

Analisi della popolarità dei contenuti per correlazione tra visualizzazioni e repost di video

Terzo esempio. Una persona per promuovere i propri video sull'hosting video di YouTube utilizza i social network per pubblicizzare il canale. Nota che esiste una relazione tra il numero di repost nei social network e il numero di visualizzazioni sul canale. È possibile prevedere le prestazioni future utilizzando gli strumenti del foglio di calcolo? È necessario identificare la ragionevolezza dell'applicazione dell'equazione di regressione lineare per prevedere il numero di visualizzazioni video in base al numero di repost. Tabella con valori:

Ora è necessario determinare la presenza di una relazione tra 2 indicatori secondo la formula seguente:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Relazione diretta forte”;”Relazione inversa forte”);”Relazione debole o assente”)' class='formula'>

Se il coefficiente risultante è maggiore di 0,7, è più appropriato utilizzare la funzione di regressione lineare. In questo esempio facciamo:

Ora stiamo costruendo un grafico:

Applichiamo questa equazione per determinare il numero di visualizzazioni a 200, 500 e 1000 condivisioni: =9,2937*D4-206,12. Otteniamo i seguenti risultati:

Funzione PREVISIONI consente di determinare il numero di visualizzazioni al momento, se ci fossero, ad esempio, duecentocinquanta repost. Applichiamo: 0,7;PREDICTION(D7;B3:B8;A3:A8);”I valori non sono correlati”)' class='formula'>. Otteniamo i seguenti risultati:

Caratteristiche dell'utilizzo della funzione CORRELAZIONE in Excel

Questa funzione ha le seguenti caratteristiche:

1. Le celle vuote non vengono prese in considerazione.
2. Le celle contenenti informazioni sul tipo booleano e di testo non vengono prese in considerazione.
3. La doppia negazione "-" viene utilizzata per tenere conto dei valori logici sotto forma di numeri.
4. Il numero di celle negli array studiati deve corrispondere, altrimenti verrà visualizzato il messaggio #N/D.

Valutazione della significatività statistica del coefficiente di correlazione

Quando si verifica la significatività di un coefficiente di correlazione, l'ipotesi nulla è che l'indicatore abbia un valore pari a 0, mentre l'alternativa no. Per la verifica viene utilizzata la seguente formula:

Conclusione

L'analisi di correlazione in un foglio di calcolo è un processo semplice e automatizzato. Per eseguirlo, devi solo sapere dove si trovano gli strumenti necessari e come attivarli attraverso le impostazioni del programma.