Regressione Lineare Excel

Benvenuto ad una nuova lezione di MasterExcel.it. In questa lezione vedremo come utilizzare la regressione lineare su Excel. Se sei a digiuno di statistica, vai su: Regressione Lineare.

Guarda il Video della lezione: Regressione Lineare Excel

 

Come sempre utilizzeremo un file di esempio, visibile nell’immagine sottostante.

regressione lineare excel

In questo caso abbiamo dei dati che corrispondono ad una variabile indipendente “X” ed una variabile dipendente “Y”. I valori di questa variabile sono rappresentati nella colonna “A” e “B”. Ricorda di disporre i tuoi dati in quest’ordine prima di continuare.

Ti mostrerò tre metodi per eseguire una regressione lineare con Excel.

Metodo 1 – Grafico

Il metodo che ti propongo per eseguire una regressione lineare con Excel è quello di  creare un grafico. Quindi selezioniamo tutti i nostri valori e andiamo su Inserisci> Grafico a dispersione. Scegliamo un grafico senza linee.

Il risultato sarà simile a quello nell’immagine sottostante.

regressione-lineare-excel-01-compressor

 

Dalla 2013 in poi, possiamo andare sul “+” a destra del grafico. Nelle versioni di Excel precedenti, mi pare sia doppio click sul grafico, oppure tasto destro sul grafico e Formato Grafico. Nella mia versione cliccando sul “+” Excel mi apre un menù da cui selezionare gli elementi che vogliamo far apparire nel nostro grafico. Selezioniamo linea di tendenza e scegliamo la tipologia “lineare”.

Precisazioni: sulle vecchie versioni di Excel, questa tipologia si trova in “opzioni grafico” e dovrebbe chiamarsi “Linea di tendenza 1”.

Ora da “opzioni linea di tendenza”, scegliamo “Lineare”. Selezioniamo anche “visualizza l’equazione sul grafico” e “visualizza il valore R quadrato sul grafico”.

Il nostro risultato sarà simile a quello nell’immagine sottostante.

regressione-lineare-excel-02-compressor

Complimenti!
Hai appena eseguito la tua prima Regressione Lineare su Excel. Forse non è quello che ti aspettavi, guarda quindi gli altri due metodi.

Metodo 2 – Strumenti di Analisi

Ora rechiamoci su Dati> Analisi dati.

regressione-lineare-excel-03-compressor

Una volta fatto questo si aprirà una finestra in cui potremo selezionare i nostri strumenti di analisi.

regressione-lineare-excel-04-compressor

Selezioniamo lo strumento “Regressione” e premiamo su Ok. Ci aprirà una finestra come quella dell’immagine sottostante:

regressione-lineare-excel-05-compressor

Selezioniamo ora l’intervallo di input Y e X. Per la Y selezioniamo tutti i valori della colonna “B”, “variabile dipendente”. Per la X selezioniamo tutti i valori della colonna “A”, “variabile indipendente”.

Precisazioni: non possiamo selezionare le celle contenenti del testo, ma solo quelle che contengono i nostri valori numerici. Una bella seccatura per noi che amiamo selezionare le colonne intere del tipo A:A… :/

Su “Opzioni di output”, selezioniamo “Nuovo foglio di lavoro”. Ora premiamo su Ok.

Il nostro risultato sarà simile a quello nell’immagine sottostante.

regressione-lineare-excel-06-compressor

Come vedi Excel ci ha fornito tutto l’analisi 😀 non male eh?

Per capire se il procedimento è stato portato a termine nel modo corretto, osserva il valore di “R al quadrato”, Excel lo chiama così. Io lo ho sempre chiamato R quadro.

In questo caso, R quadro è “0,997872”.  Lo stesso valore, arrotondato, che ritroviamo sul nostro grafico.

regressione-lineare-excel-07-compressor

Anche i valori del dato “intercetta” e “variabile X 1” corrispondono a quelli riportati nel nostro grafico. Pare che abbiamo azzeccato il procedimento.
Io ti consiglio di controllare sempre in questo modo, è facile confondersi utilizzando uno strumento d’analisi che non usi spesso.

Complimenti!
Hai appena terminato la tua seconda regressione lineare con Excel. Passiamo subito ad un latro metodo.

Metodo 3 – Funzione Regressione Lineare

Per spiegarti questo metodo, ho dovuto modificare leggermente il nostro file di esempio aggiungendo alcuni testi.
Prima di continuare ti preannuncio anche che andremo ad utilizzare una funzione molto differente da quelle usate finora su Excel. Quindi presta attenzione.

regressione-lineare-excel-08-compressor

Non dovremmo far altro che utilizzare una formula Excel specifica:

REGR.LIN

Rechiamoci nella cella “C2” e scriviamo la nostra formula “=regr.lin”. Selezioniamo i valori della nostra Y, punto e virgola, della nostra X, punto e virgola, lasciamo in bianco il campo della “costante” e, nel campo “stat” selezioniamo “VERO”. Questo ci permetterà di ottenere ulteriori statistiche della regressione.

Premiamo Invio. Nella cella “C2” sarà apparso il valore “1,632408”. Ossia il coefficiente angolare (il valore che moltiplica la x) nell’equazione del nostro grafico e nell’analisi dei dati che abbiamo effettuato poco fa!

Proseguiamo, e fai attenzione. Selezioniamo 10 celle e torniamo nel campo della formula, come nell’immagine sottostante. Io nell’esempio ne seleziono due in più per farti vedere una cosa tra poco.

regressione-lineare-excel-09-compressor

Dall’interno della formula, premiamo CTRL+SHIFT+INVIO. Ed osserva la magia di Excel.

regressione-lineare-excel-10-compressor

I valori “N/D” sono valori di errore. Questo tipo di funzione infatti, funziona solo selezionando un totale di 10 celle. Le successive produrranno tutte errore N/D.

Ma in che cosa sono questi dati? In realtà è molto semplice, te l’ho già mostrato.

regressione-lineare-excel-11-compressor

I valori sono i seguenti:

Pendenza Intercetta
s1;s2;…;sn sb
R2 Errore Standard
F gdl
sqreg sqres

Per maggiori informazioni su questa funzione ti rimando al sito della Microsoft:

Funzione Regr.Lin.

Conclusioni

Oggi hai imparato a far eseguire ad Excel una regressione lineare. Io ti consiglio l’utilizzo dei primi due metodi combinati e di lasciar perdere il terzo metodo, e ti consiglio anche di non perdere la prossima lezione. Dato che ora sai fare una regressione lineare con Excel, è arrivato il momento di imparare ad eseguire una Interpolazione Lineare con Excel!

Ti aspetto nella prossima lezione.

Submit a Comment

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *