fbpx
.st0{fill:#FFFFFF;}

Analisi statistica, Excel Avanzato

Regressione lineare: il calcolo in Excel

 Febbraio 18, 2020

By  Damiano Causale

  • Home
  • Regressione lineare: il calcolo in Excel

In questo articolo voglio mostrarti come eseguire un’analisi di regressione lineare in Excel e come interpretare i risultati.

La prima domanda a cui vogliamo dare una risposta è: perché fare la regressione lineare Excel?

Supponiamo di lavorare in una azienda. Immaginiamo che ci vengono forniti diversi dati e ci venga chiesto di effettuare una stima delle vendite per il prossimo anno.

Probabilmente esistono diversi fattori che possono influenzare l’andamento delle vendite.

Ma quali sono quelli davvero importanti? Quali di questi vale la pena prendere in considerazione e quali, invece, possono essere ignorati?

Eseguire l’analisi di regressione lineare semplice in Excel ci aiuterà a trovare le risposte che cerchiamo.

Analisi di regressione in Excel: alcune nozioni di base

In statistica, l’analisi di regressione viene utilizzata per effettuare una stima tra le relazioni tra due o più variabili.

Possiamo fare subito una distinzione tra le variabili.

La variabile dipendente (o variabile y) è la variabile risposta ovvero il fattore principale che si sta tentando di comprendere e prevedere.

Le variabili indipendenti (o variabili x) sono le variabili esplicative ovvero i fattori che potrebbero influenzare la variabile dipendente.

Attraverso l’analisi di regressione possiamo capire come si comporta la variabile dipendente (y) quando varia una delle variabili indipendenti (x). Questo ci consente di determinare statisticamente quale delle variabili ha un impatto rilevante.

Inoltre, possiamo fare una ulteriore distinzione. Possiamo distinguere tra regressione lineare semplice e regressione lineare multipla.

La regressione lineare semplice consente di individuare la relazione tra una variabile dipendente e una variabile indipendente attraverso l’utilizzo di una funzione lineare.

La regressione lineare multipla consente di prevedere la variabile dipendente quando si utilizzano due o più variabili esplicative.

In entrambi i casi, se la relazione tra i dati NON segue una linea retta, è necessario utilizzare una regressione non lineare.

In questo articolo tratteremo un esempio di regressione lineare semplice.

Un esempio di regressione lineare semplice

Prendiamo in considerazione, come esempio, le vendite negli ultimi 24 mesi e le spese in pubblicità per lo stesso periodo di una azienda del settore agroalimentare.

Cerchiamo di capire subito il tipo di relazione tra la variabile indipendente (Spesa in pubblicità) e la variabile dipendente (Quantità venduta). Per far questo inseriamo un diagramma a dispersione.

diagramma di dispersione | regressione lineare con 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:

regressione lineare excel | regressione statistica

Dove:

regressione lineare excel | osservazioni

È il numero di osservazioni.

regressione lineare excel | variabile dipendente

è la variabile dipendente.

regressione lineare excel | variabile indipendente

è la variabile indipendente.

 modella di regressione lineare | retta di regressione o funzione di regressione.

è la retta di regressione o funzione di regressione.

intercetta della retta di regressione statistica

è 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.

coefficiente angolare della retta di regressione

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

errore statistico | regressione lineare minimi quadrati

è 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:

metodo dei minimi quadrati | regressione lineare excel

Che soddisfano l’equazione:

regressione lineare statistica con excel

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.

Regressione lineare | componente aggiuntivo di Excel Strumenti di analisi

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

Componenti aggiuntivi | Strumenti di analisi statistica Excel

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

Componenti aggiuntivi Excel | Strumenti di analisi

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.

regressione lineare Excel

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.

Scheda Dati | Analisi dati | Regressione

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

Analisi dati | Regressione

Verrà mostrata la finestra di dialogo Regressione.

Analisi dati | 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.

Analisi dati | Regressione

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.

Interpretazione dell'analisi di regressione: Output riepilogo

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 multiplo | coefficiente di correlazione

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: Analisi varianza

Interpretazione dell’analisi di regressione: i coefficienti di regressione

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.

Interpretazione dell'analisi di regressione: i coefficienti di regressione lineare excel

Sulla base dei nostri dati otterremo la seguente equazione.

Interpretazione dell'analisi di regressione: i coefficienti di regressione excel

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

Interpretazione dell'analisi di regressione: i coefficienti di regressione

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

Interpretazione dell'analisi di regressione lineare excel | i coefficienti di regressione

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à.

retta regressione excel

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

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:

regressione lineare excel | numero stimato e numero effettivo della quantità venduta

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 per 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).

diagramma a dispersione | grafico a dispersione | regressione lineare excel

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.

diagramma di dispersione | modello di regressione

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.

regressione excel | retta minimi quadrati

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

Linea di tendenza | retta di regressione lineare

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.

Formato della linea di tendenza

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.

equazione di regressione Excel

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.

grafico dispersione tracciato nell'ordine inverso genera un 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.

funzione REGR.LIN Excel | metodo dei minimi quadrati per il calcolo dell’equazione di regressione

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:

funzione REGR.LIN Excel

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.

funzione REGR.LIN con parametro [stat] impostato su VERO

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.

funzione REGR.LIN Excel | formula di matrice

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

funzione INTERCETTA Excel

Il risultato è -19,368.

Ora passiamo al calcolo della pendenza.

modello di regressione lineare excel

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

funzione PENDENZA Excel

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.

funzione CORRELAZIONE Excel | coefficiente di regressione lineare excel

Il risultato è 0,9857.

regressione significato | coefficiente di correlazione lineare | modello di regressione lineare

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.

Damiano Causale


Mi chiamo Damiano Causale, istruttore Microsoft certificato, per lavoro aiuto aziende e privati nell'utilizzo di Excel.

Damiano Causale

Your email address will not be published. Required fields are marked

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}