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.

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 primo 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 grafico a dispersione lo abbiamo visto anche nel corso di Excel base di MasterExcel.it.

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

 

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

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

Successivamente, 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.

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

Metodo 2 – Strumenti di Analisi

Ora clicchiamo su Dati > Analisi dati.

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

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

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

Precisazione: non possiamo selezionare le celle contenenti del testo, ma solo quelle che contengono dei 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” e premiamo su “OK“.

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

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

Per capire se il procedimento è stato portato a termine in modo corretto, osserva il valore di “R al quadrato”; Excel lo chiama così, mentre io l’ho sempre chiamato “R quadro”.

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

Anche i valori del dato “intercetta” e “variabile X 1” corrispondono a quelli riportati nel nostro grafico. Sembra proprio che abbiamo azzeccato il procedimento!
Io ti consiglio di controllare sempre in questo modo perché è facile confondersi utilizzando uno strumento d’analisi poco familiare.

Complimenti!
Hai appena terminato la tua seconda regressione lineare con Excel. Passiamo subito ad un atro 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 che utilizzeremo una funzione molto differente da quelle usate finora su Excel, quindi presta attenzione.

Non dovremo 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.

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

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

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

 

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 di Microsoft:

Funzione Regr.Lin.

Conclusioni

Oggi hai imparato ad eseguire una regressione lineare su Excel. Io ti consiglio l’utilizzo dei primi due metodi combinati e di lasciar stare 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!

Lasciaci un commento!

Ti è piaciuta la lezione di Excel? Faccelo sapere qui sotto nei commenti.