Regressione lineare: il calcolo in Excel 

A colpo d’occhio otteniamo immediatamente le informazioni relative al tipo di relazione.

Il grafico ci informa che il tipo di relazione è di tipo lineare. La retta è crescente. Possiamo pertanto dedurre che ad un aumento della spessa in pubblicità possa corrispondere un aumento delle vendite.

Regressione lineare: l’equazione

In statistica, una regressione lineare è definita da un’equazione matematica.

L’equazione che definisce la regressione lineare è la seguente:

Dove:

È il numero di osservazioni.

è la variabile dipendente.

è la variabile indipendente.

è la retta di regressione o funzione di regressione.

è l’ intercetta della retta di regressione. Rappresenta il valore di Y quando la variabile X è uguale a 0. All’interno di un grafico di regressione, è il punto in cui la retta interseca l’asse Y.

è il coefficiente angolare della retta di regressione ovvero il tasso di variazione di Y quando X cambia. Rappresenta la pendenza della retta.

è l’errore statistico. Noto anche come errore stocastico. Rappresenta la differenza tra il valore effettivo di una variabile dipendente e il suo valore previsto.

L’errore statistico è sempre presente nell’equazione di regressione lineare poiché è necessario correggere la stima mai perfettamente precisa. In Excel, tuttavia, l’esecuzione di questo calcolo è gestito al suo interno.

Excel, esegue la regressione lineare usando il metodo dei minimi quadrati.

La somma dei quadrati su cui si basa un modello di analisi di regressione, è un metodo matematico per trovare la dispersione dei punti dei dati. L’obiettivo è ottenere la somma più piccola possibile dei quadrati e tracciare una linea che si avvicini di più ai dati.

È necessario pertanto trovare i seguenti valori:

Che soddisfano l’equazione:

Esistono modi differenti per calcolare i due valori.

Ora, vediamo i tre metodi principali per eseguire l’analisi di regressione lineare in Excel.

Come fare la regressione lineare in Excel: i 3 metodi

Iniziamo con il primo metodo per fare la regressione lineare in Excel.

Regressione lineare: il componente aggiuntivo di Excel Strumenti di analisi

Il primo metodo per eseguire la regressione in Excel utilizza il componente aggiuntivo chiamato Strumenti di analisi.

Questo strumento è incluso in Excel ed è necessario attivarlo. È disponibile in tutte le versioni di Excel (dalla versione 2003 alla versione 2019) ma, per impostazione predefinita, non è abilitato. Quindi, è necessario procedere alla sua attivazione. Vediamo come.

Fai su File e successivamente su Opzioni. Nella finestra di dialogo Opzioni di Excel, seleziona la voce Componenti aggiuntivi.

Dopo aver selezionato Componenti aggiuntivi, seleziona Strumenti di analisi e fai clic su Vai.

A questo punto Excel mostrerà la finestra di dialogo Componenti aggiuntivi. Applica la spunta su Strumenti di analisi e fai clic su OK.

Ora potrai trovare gli Strumenti di analisi nella scheda Dati all’interno della barra multifunzione.

Passiamo alla pratica analizzando l’esempio accennato all’inizio.

Eseguiamo una semplice regressione lineare in Excel prendendo in considerazione le vendite negli ultimi 24 mesi e le spese in pubblicità per lo stesso periodo di una azienda del settore agroalimentare.

Nella colonna B abbiamo la Spesa per la pubblicità che è la variabile indipendente (X). Nella colonna C abbiamo la Quantità venduta che rappresenta la variabile dipendente (Y).

Ovviamente, come accennato in precedenza, esistono diversi fattori che possono influenzare l’andamento delle vendite. Tuttavia, in questo esempio, ci concentreremo solo su una variabile: la spesa in pubblicità.

Procediamo. Nella scheda Dati, fai un clic sul pulsante Analisi dati presente nel gruppo Analisi.

Dalla finestra di dialogo Analisi dati, seleziona la voce Regressione e fai clic su OK.

Verrà mostrata la finestra di dialogo Regressione.

A questo punto, configura le impostazioni nel modo seguente.

Seleziona l’intervallo di celle C1:C25 che rappresenta l’Intervallo di input Y ovvero la variabile dipendente (Quantità venduta).

Seleziona l’intervallo di celle B1:B25 che rappresenta l’Intervallo di input X ovvero la variabile indipendente (Spesa in pubblicità).

Nel caso della regressione multipla, è necessario selezionare due o più colonne adiacenti con diverse variabili indipendenti.

Applica la spunta sulla casella Etichette se sono presenti le intestazioni nella parte superiore degli intervalli appena selezionati.

In modo facoltativo potrai decidere se la retta di regressione deve passare per l’origine. Ciò significa che, se l’opzione Passa per l’origine è attiva, il valore della variabile dipendente sarà uguale a zero quando il valore indipendente è uguale a zero.

Sempre in modo facoltativo potrai decidere il livello di confidenza. Per fare ciò, selezionare la casella di controllo Livello di confidenza e quindi immettere il livello di confidenza che desideri utilizzare.

Seleziona l’Opzione di output per specificare dove Excel deve posizionare i risultati dell’analisi di regressione.

Per posizionare i risultati dell’analisi della regressione in un intervallo nel foglio di lavoro esistente, seleziona il pulsante di opzione Intervallo di output, quindi identifica l’indirizzo dell’intervallo nella casella di testo Intervallo di output. Per posizionare i risultati della regressione in un’altra posizione (Nuovo foglio di lavoro o Nuova cartella di lavoro), seleziona uno degli altri pulsanti di opzione.

Nel nostro caso, per comodità, scegliamo l’opzione Nuovo foglio di lavoro.

Seleziona la casella di controllo Residui se desideri ottenere la differenza tra i valori previsti e quelli effettivi.

Il risultato delle impostazioni sarà il seguente.

Infine, fai clic su OK. Noterai che Excel ha creato l’output di riepilogo dell’analisi di regressione all’interno di un nuovo foglio di lavoro.

Osserviamo i risultati e cerchiamo di interpretare l’output dell’analisi di regressione.

Sebbene la procedura per eseguire la regressione sia semplice, l’interpretazione dei risultati è un po’ più complicata.

Per semplificare il lavoro e favorire la lettura e l’interpretazione dei dati, suddividiamo l’output in quattro diverse parti.

Interpretazione dell’analisi di regressione: Output riepilogo

Questa parte descrive la misura in cui l’equazione di regressione lineare calcolata si adatta ai dati di origine.

Analizziamo nel dettaglio ciascun valore.

R multiplo

Questo valore rappresenta il coefficiente di correlazione che misura la forza di una relazione lineare tra due variabili. Il coefficiente di correlazione può essere qualsiasi valore compreso tra 1 e -1. Il suo valore assoluto indica la forza della relazione. Maggiore è il valore, più forte è la relazione.

R al quadrato

È il valore che misura la proporzione della variazione della variabile dipendente che viene spiegata dalla retta di regressione. Questa proporzione deve essere un valore compreso tra zero e uno ed è spesso espresso come percentuale. Rappresenta il coefficiente di determinazione che viene utilizzato come indicatore della bontà dell’adattamento. Mostra quanti punti cadono sulla retta di regressione. Il valore di R al quadrato viene calcolato dalla somma totale dei quadrati ovvero è la somma degli scostamenti quadrati dei dati di origine dalla media.

Nell’esempio, il valore di R al quadrato è 0,9716. Ciò significa che il 97% dei valori si adatta al modello di analisi di regressione. In altri termini, il 97% delle variabili dipendenti (valori Y ovvero la Quantità venduta) sono spiegate da variabili indipendenti (valori X ovvero la Spesa di pubblicità). Di solito, un valore di R al quadrato uguale o superiore al 95% è considerato una buona misura.

R al quadrato corretto

Rappresenta R al quadrato corretto per il numero di variabili indipendenti nel modello. È consigliabile utilizzare questo valore per l’analisi di regressione multipla anziché il valore di R al quadrato.

Errore standard

Questo valore mostra la precisione dell’analisi di regressione: più piccolo è questo valore, più è precisa l’equazione di regressione. È da considerarsi un’altra misura di bontà di adattamento. Mentre R al quadrato rappresenta la percentuale della varianza delle variabili dipendenti spiegata dal modello, l’Errore standard è una misura assoluta che mostra la distanza media attorno alla retta di regressione.

Osservazioni

È il numero di osservazioni. Nell’esempio è pari a 24.

Interpretazione dell’analisi di regressione: Analisi varianza

Questa parte è dedicata all’ANALISI VARIANZA. Viene usata raramente per l’analisi della regressione lineare semplice.

Tuttavia, è importante osservare l’ultimo valore: la Significatività F.

Questo valore dà un’idea di quanto sono statisticamente significativi (ovvero affidabili) i risultati. Se il valore della Significatività F è inferiore a 0,05 (5%), il modello utilizzato è buono. Se è maggiore di 0,05, probabilmente è meglio scegliere un’altra variabile indipendente.

Interpretazione dell’analisi di regressione: i coefficienti di regressione

L’aspetto più interessante di questa sezione sono i coefficienti.

Attraverso i coefficienti è possibile creare l’equazione di regressione lineare in Excel.

Sulla base dei nostri dati otterremo la seguente equazione.

Dove Y è la Quantità venduta e X è la Spesa in pubblicità mensile. Sostituendo i valori arrotondati alla seconda cifra decimale avremo:

Ad esempio, con una spesa mensile in pubblicità di 78 euro (quella del mese di gennaio), la quantità venduta sarebbe di circa:

Il coefficiente della Spesa in pubblicità ci dice che per ogni aumento di un euro nella pubblicità, la quantità venduta aumenta di 0,45 unità. Questa rappresenta un’informazione preziosa che può essere utile per fare una previsione sula quantità venduta. Ad esempio, se la spesa per la pubblicità è pari a 200 euro, è possibile raggiungere una quantità venduta di 70,63 unità.

In questo modo è possibile scoprire il numero della quantità che potrà essere venduta con qualsiasi importo specificato di spesa in pubblicità (variabile X).

Interpretazione dell’analisi di regressione: analisi dei residui

Se confronti il numero stimato e il numero effettivo della quantità venduta in corrispondenza di una spesa in pubblicità mensile di 78 euro (quella relativa al mese di gennaio), noterai che questi valori variano leggermente:

La differenza dipende dal fatto che le variabili indipendenti (nell’esempio la Spesa in pubblicità) non sono mai così precise nel fare la stima delle variabili dipendenti (nell’esempio la Quantità venduta).

Per questo motivo, i residui possono aiutarci a capire di quanto differiscono i valori effettivi dai valori previsti.

Per il mese di Gennaio (spesa in pubblicità mensile di 78 euro), il residuo è di circa -1,81. Pertanto, se aggiungiamo questo numero al valore previsto (15,81), otteniamo il valore effettivo (14).

Se lo desideri, puoi anche creare un grafico a dispersione per l’analisi dei residui.

Grafico a dispersione con una linea di tendenza

Il secondo metodo per eseguire la regressione in Excel è inserire un grafico di regressione lineare.

Il grafico consente di visualizzare rapidamente la relazione tra le due variabili.

Vediamo come.

Seleziona l’intervallo di dati B1:C25 (nei dati sono comprese le intestazioni).

Fai un clic sull’icona del grafico a Dispersione presente nel gruppo Grafici della scheda Inserisci e seleziona il primo.

Immediatamente Excel inserisce il grafico all’interno del foglio di lavoro.

Ora, dobbiamo aggiungere la retta di regressione calcolata con il metodo dei minimi quadrati. Per far questo, fai un clic sul simbolo “+” e applica la spunta su Linea di tendenza.

Dopo aver aggiunto la Linea di tendenza, fai un clic su Altre opzioni. Così come mostrato nella seguente immagine.

Nel riquadro destro, relativo al Formato della linea di tendenza, applica la spunta su Visualizza l’equazione sul grafico e su Visualizza il valore R quadrato sul grafico.

Come puoi notare, l’equazione di regressione che Excel ha creato è la stessa della formula di regressione lineare che abbiamo creato in base all’output dei coefficienti.

Nel grafico di regressione, la variabile indipendente dovrebbe essere sempre sull’Asse X e la variabile dipendente sull’Asse Y.

Se il grafico viene tracciato nell’ordine inverso, il risultato sarà differente.

In alternativa, potrai scambiare le colonne nel foglio di lavoro e quindi inserire nuovamente il grafico oppure cambiare gli assi X e Y direttamente nel grafico qualora non fosse possibile riorganizzare i dati di origine.

Analisi di regressione lineare con le formule Excel

Passiamo, infine, al terzo metodo per eseguire la regressione lineare in Excel.

L’ultimo metodo che voglio mostrarti si basa sull’utilizzo delle funzioni statistiche di Excel.

Excel ci mette a disposizione alcune funzioni statistiche che possono aiutarci a fare l’analisi di regressione lineare.

Attraverso le seguenti funzioni ricaveremo la nostra equazione di regressione.

Le funzioni di cui avremo bisogno sono:

• LIN,
• INTERCETTA
• PENDENZA,
• e CORRELAZIONE.

La funzione REGR.LIN Excel utilizza il metodo dei minimi quadrati per il calcolo dell’equazione di regressione che spiega la relazione tra le variabili e restituisce un array che descrive quella retta.

Poiché la funzione REGR.LIN Excel è una formula di matrice, essa restituisce una matrice di valori. Ciò significa che è necessario utilizzare la combinazione di tasti CTRL + MAIUSC + Invio per completarla.

Quindi, seleziona due celle adiacenti nella stessa riga, E2:F2 nell’esempio e digita la seguente funzione:

Infine, premi CTRL + MAIUSC + Invio.

La formula restituisce il valore della pendenza nella cella E2 e il valore dell’intercetta nella cella F2.

Rispettivamente, 0,4510 e -19,3678.

Se desideri ottenere statistiche aggiuntive per l’analisi di regressione, utilizza la funzione REGR.LIN con il parametro [stat] impostato su VERO, come mostrato di seguito.

In alternativa alla funzione REGR.LIN Excel (formula di matrice), è possibile calcolare individualmente ciascuno dei valori attraverso singole funzioni.

Iniziamo con il calcolo dell’intercetta.

Utilizziamo la funzione INTERCETTA Excel per il calcolo di questo valore. La funzione è la seguente:

Il risultato è -19,368.

Ora passiamo al calcolo della pendenza.

Utilizziamo la funzione PENDENZA Excel per il calcolo di questo valore. La funzione è la seguente:

Il risultato è 0,451.

Inoltre, è possibile calcolare il coefficiente di correlazione (R multiplo nell’output di riepilogo dell’analisi di regressione). Esso indica quanto le due variabili sono correlate tra loro.

Utilizziamo la funzione CORRELAZIONE Excel per il calcolo di questo valore.

Il risultato è 0,9857.

A questo punto hai imparato come fare la regressione lineare in Excel sfruttando metodi differenti. Tuttavia, vorrei fare un breve precisazione. Excel non è un programma specifico per la statistica pertanto se desideri eseguire analisi di regressione a livello professionale, è possibile utilizzare software più specifici.