Come calcolare la regressione lineare in Excel


La regressione lineare è un metodo statistico utilizzato per analizzare la relazione tra due o più variabili. In particolare, la regressione lineare semplice si concentra sulla relazione tra due variabili, una variabile indipendente (X) e una variabile dipendente (Y), e cerca di stabilire una relazione lineare tra di esse. La sua formula generale è:

Y=aX+b

Dove:

  • Y è la variabile dipendente che stai cercando di spiegare o prevedere.
  • X è la variabile indipendente che utilizzi per spiegare o prevedere Y.
  • a è il coefficiente di regressione (la pendenza della retta di regressione).
  • b è l’intercetta della retta di regressione.

Ecco un esempio facile per illustrare la regressione lineare:

Supponiamo di essere interessati a capire come il numero di ore studiate influisca sulle votazioni degli studenti. Abbiamo raccolto dati da alcuni studenti in cui abbiamo registrato il numero di ore di studio (X) e i voti ottenuti (Y) per ognuno di essi.

Ecco i dati:

Ore di Studio (X) Voti (Y)
1 60
2 70
3 75
4 80
5 85

Ora vogliamo utilizzare la regressione lineare per vedere se c’è una relazione tra il numero di ore di studio e i voti ottenuti.

  1. Calcoliamo la media di X e Y:
    • Media di X (ore di studio): (1 + 2 + 3 + 4 + 5) / 5 = 3
    • Media di Y (voti): (60 + 70 + 75 + 80 + 85) / 5 = 74
  2. Calcoliamo la pendenza (a) e l’intercetta (b) della retta di regressione:a=∑((Xi−Xˉ)(Yi−Yˉ))∑((Xi−Xˉ)2)b=Yˉ−aXˉ
    • Calcoliamo a: a=(1−3)(60−74)+(2−3)(70−74)+(3−3)(75−74)+(4−3)(80−74)+(5−3)(85−74)(1−3)2+(2−3)2+(3−3)2+(4−3)2+(5−3)2 a=−28−8+1+6+224=−74=−1.75
    • Calcoliamo b: b=74−(−1.75)(3)=79.25
  3. Ora abbiamo la nostra equazione di regressione lineare: Y=−1.75X+79.25

Questo significa che per ogni aumento di una unità nelle ore di studio (X), ci aspettiamo una diminuzione di 1.75 punti nei voti (Y). L’intercetta (79.25) rappresenta il punteggio previsto quando il numero di ore di studio è zero, il che potrebbe non avere un significato pratico in questo contesto.

Usando questa equazione, puoi fare previsioni sui voti degli studenti in base alle ore di studio. Ad esempio, se uno studente studia 2 ore (X = 2), la previsione per il voto (Y) sarebbe:

Y=−1.75(2)+79.25=76.75

Quindi, previamo che lo studente otterrebbe circa 76.75 punti. Questo è solo un esempio molto semplice di regressione lineare; nelle applicazioni reali, i dati possono essere più complessi e richiedere un’analisi più approfondita.

Come calcolare la regressione in Excel

Per effettuare una regressione lineare in Excel, puoi seguire questi passaggi che adesso andremo a dettagliare:

  1. Prepara i dati: Assicurati di avere i dati necessari per eseguire la regressione lineare. Dovrai avere almeno due colonne di dati: una per la variabile indipendente (X) e una per la variabile dipendente (Y).
  2. Apri Excel: Avvia Microsoft Excel e apri il foglio di lavoro in cui desideri eseguire la regressione lineare.
  3. Inserisci i dati: Inserisci i dati nelle colonne appropriate. Ad esempio, se stai cercando di determinare come una variabile (Y) dipende da un’altra variabile (X), inserisci i dati di X in una colonna e i dati di Y in un’altra colonna.
  4. Calcola la regressione: Ora, dovrai calcolare la regressione lineare. Puoi farlo in diversi modi, ma uno dei modi più comuni è utilizzare la funzione “Analisi dati” o “Analisi strumenti” (ToolPak) in Excel. Segui questi passaggi:a. Vai su “File” e seleziona “Opzioni”. b. Nella finestra delle opzioni, seleziona “Aggiungi-ins” o “Add-Ins”. c. Seleziona “Analisi dati” o “Strumenti per analisi” e fai clic su “OK” per attivare questa funzione.
  5. Seleziona la funzione di regressione: Ora che hai attivato l’analisi dati, vai nella scheda “Dati” (o in una scheda simile) e cerca l’opzione “Analisi dati” o “Strumenti per analisi”. Seleziona questa opzione.
  6. Seleziona “Regressione lineare”: Nell’elenco delle opzioni dell’analisi dati, trova e seleziona “Regressione” o “Regressione lineare” (potrebbe variare leggermente a seconda della versione di Excel).
  7. Configura i parametri: Nella finestra di configurazione della regressione, inserisci le informazioni necessarie. Specifica la colonna dei dati X e la colonna dei dati Y. Puoi anche selezionare altre opzioni come l’intervallo di confidenza desiderato, se necessario.
  8. Visualizza i risultati: Dopo aver configurato i parametri, fai clic su “OK” o “Calcola” per eseguire la regressione lineare. Excel calcolerà i coefficienti della retta di regressione, il coefficiente di determinazione (R²) e altri risultati statistici.
  9. Grafico: Puoi anche creare un grafico a dispersione dei dati e sovrapporre la retta di regressione per visualizzare graficamente il modello.
  10. Interpreta i risultati: Ora puoi interpretare i risultati della regressione lineare per determinare la relazione tra le variabili e quanto bene il modello si adatta ai dati.

Nel seguito, tieni conto che la posizione delle opzioni e i dettagli esatti possono variare leggermente in base alla versione di Excel che stai utilizzando.

Procedura calcolo regressione lineare Excel passo-passo

Calcolare una regressione in Excel passo dopo passo può richiedere diversi passaggi. Inizieremo con un esempio e seguirò i passaggi dettagliati utilizzando Microsoft Excel. Supponiamo che tu abbia raccolto dati su due variabili, ad esempio il tempo trascorso a studiare (variabile indipendente X) e i risultati dei test (variabile dipendente Y), e vuoi eseguire una regressione per determinare la relazione tra queste due variabili.

Passo 1: Preparazione dei dati

Inserisci i tuoi dati in un foglio di lavoro Excel. Supponiamo che i dati siano inseriti come segue (suggerimento: metti la prima riga in grassetto per migliorare la leggibilità):

Tempo di Studio (X) Risultati del Test (Y)
1 60
2 70
3 75
4 80
5 85

Passo 2: Calcolo delle medie

Calcola la media delle variabili X e Y:

  • Media di X (Tempo di Studio): =MEDIA(A2:A6) (inseriscila in una cella, ad esempio B7)
  • Media di Y (Risultati del Test): =MEDIA(B2:B6) (inseriscila in una cella, ad esempio B8)

Passo 3: Calcolo dei coefficienti di regressione

Per calcolare i coefficienti di regressione (a e b) utilizziamo le formule seguenti:

  • Calcolo di a:
    • =COVARIANZA.P(A2:A6,B2:B6)/VAR.P(A2:A6) (inseriscila in una cella, ad esempio B9)
  • Calcolo di b:
    • =B8 - B9*B7 (inseriscila in una cella, ad esempio B10)

Passo 4: Costruzione dell’equazione di regressione

Ora che hai ottenuto i coefficienti di regressione, puoi costruire l’equazione di regressione lineare:

Y=aX+b

In una cella, ad esempio B11, inserisci l’equazione utilizzando i valori dei coefficienti a e b che hai calcolato, cioè l’equazione lineare appena vista:

=B9*A2 + B10

Passo 5: Creazione del grafico di regressione

Per visualizzare graficamente la regressione, puoi creare un grafico a dispersione dei tuoi dati e sovrapporre la linea di regressione. Ecco come farlo:

  1. Seleziona i dati (X e Y) nel tuo foglio di lavoro.
  2. Vai nella scheda “Inserisci” di Excel e seleziona “Scatter Plot” o “Diagramma a dispersione“.
  3. Dovrebbe apparire un grafico a dispersione. Per aggiungere la linea di regressione:
    • Fai clic con il tasto destro su uno dei punti dati nel grafico.
    • Seleziona “Aggiungi linea di tendenza” o “Trendline” dal menu.
    • Nella finestra di opzioni, scegli “Lineare” come tipo di linea di tendenza.
    • Puoi anche visualizzare l’equazione della retta e il coefficiente R² nel grafico se selezioni queste opzioni.

Il tuo grafico dovrebbe ora mostrare la retta di regressione lineare sovrapposta ai dati a dispersione.

Passo 6: Interpretare i risultati

Ora puoi interpretare i risultati della regressione. L’equazione Y = aX + b ti permette di fare previsioni basate sui dati di X e di comprendere la relazione tra le due variabili. Ad esempio, puoi utilizzare l’equazione per prevedere i risultati dei test in base alle ore di studio. La pendenza (a) indica quanto influisce il tempo di studio sui risultati dei test, mentre l’intercetta (b) rappresenta il punteggio previsto quando il tempo di studio è zero, il che può non avere un significato pratico in questo contesto.

In questo esempio, puoi concludere che un aumento di una unità nel tempo di studio (X) è associato a un aumento di a punti nei risultati dei test (Y).

👇 Da non perdere 👇



Questo sito esiste da 4549 giorni (12 anni), e contiene ad oggi 4117 articoli (circa 3.293.600 parole in tutto) e 18 servizi online gratuiti. – Leggi un altro articolo a caso
Privacy e termini di servizio / Cookie - Il nostro network è composto da Lipercubo , Pagare.online e Trovalost
Seguici su Telegram, ne vale la pena ❤️ ➡ @trovalost
Questo sito contribuisce alla audience di sè stesso.