Cos'è una regressione lineare?
La regressione lineare è fondamentalmente una tecnica di modellazione statistica utilizzata per mostrare la relazione tra una variabile dipendente e una o più variabili indipendenti. È uno dei tipi più comuni di analisi predittiva. Questo tipo di distribuzione si forma in una linea, quindi è chiamata regressione lineare. In questo articolo, prenderemo gli esempi di analisi della regressione lineare in Excel.
Per eseguire prima l'analisi della regressione lineare, è necessario aggiungere componenti aggiuntivi di Excel seguendo i passaggi.
Fare clic su File - Opzioni (questo aprirà il popup delle opzioni di Excel per te).
Fare clic su Componenti aggiuntivi: selezionare Componenti aggiuntivi di Excel da Gestisci a discesa in Excel, quindi fare clic su Vai.
Questo aprirà il popup dei componenti aggiuntivi. Selezionare Analysis ToolPak quindi fare clic su Ok.
Il componente aggiuntivo per l'analisi dei dati verrà visualizzato nella scheda Inserisci.
Comprendiamo con gli esempi seguenti di analisi di regressione lineare in Excel.
Esempi di analisi di regressione lineare
Esempio 1
Supponiamo di avere vendite mensili e spese di marketing per lo scorso anno, e ora dobbiamo prevedere le vendite future sulla base delle vendite e del marketing dello scorso anno.
| Mese | Pubblicità | I saldi |
| Jan | 40937 | 502729 |
| Feb | 42376 | 507553 |
| Mar | 43355 | 516885 |
| Apr | 44126 | 528347 |
| Maggio | 45060 | 537298 |
| Jun | 49546 | 544066 |
| Lug | 56105 | 553664 |
| Ago | 59322 | 563201 |
| Sep | 59877 | 568657 |
| Ott | 60481 | 569384 |
| Nov | 62356 | 573764 |
| Dic | 63246 | 582746 |
Fare clic su Analisi dei dati nella scheda Dati e questo aprirà il popup di analisi dei dati per te.
Ora seleziona Regressione dall'elenco e fai clic su Ok.
Si aprirà il pop-up di regressione.
Seleziona Intervallo di vendita $ C $ 1: $ C $ 13 nella casella dell'asse Y poiché questa è la variabile dipendente e $ B $ 1: $ B $ 14 nell'asse X poiché la pubblicità spesa è la variabile indipendente.
Segno di spunta sulla casella Etichette se hai selezionato le intestazioni nei dati altrimenti ti darà l'errore.
Seleziona Intervallo di output se vuoi ottenere il valore sull'intervallo specifico sul foglio di lavoro, altrimenti seleziona Nuovo foglio di lavoro: e questo aggiungerà un nuovo foglio di lavoro e ti darà il risultato.
Quindi seleziona la casella Residui e fai clic su Ok.
Questo aggiungerà fogli di lavoro e ti darà il seguente risultato.
Cerchiamo di capire l'output.
Output di riepilogo
R multiplo: rappresenta il coefficiente di correlazione. Il valore 1 mostra una relazione positiva e il valore 0 non mostra alcuna relazione.
R Square: R Square rappresenta il coefficiente di determinazione. Questo ti dice la percentuale di punti che cadono sulla linea di regressione. 0,49 significa che il 49% dei valori si adatta al modello
Quadrato R aggiustato : questo è il quadrato R aggiustato, che richiede quando hai più di una variabile X.
Errore standard: rappresenta una stima della deviazione standard dell'errore. Questa è la precisione con cui viene misurato il coefficiente di regressione.
Osservazioni: questo è il numero di osservazioni che hai preso in un campione.
ANOVA - Df: Gradi di libertà
SS: somma dei quadrati.
MS: abbiamo due MS
- Regression MS è Regression SS / Regression Df.
- La SM residua è l'errore quadratico medio (SS residuo / Df residuo).
F: test F per l'ipotesi nulla.
Significatività F: valori P associati alla significatività
Coefficiente: il coefficiente fornisce la stima dei minimi quadrati.
Statistica T: Statistica T per ipotesi nulla vs ipotesi alternativa.
Valore p : questo è il valore p per il test di ipotesi.
95% inferiore e 95% superiore: sono il limite inferiore e il limite superiore per l'intervallo di confidenza
Output dei residui: abbiamo 12 osservazioni basate sui dati. La seconda colonna rappresenta le vendite previste e la terza colonna i residui. I residui sono fondamentalmente la differenza tra le vendite previste da quelle effettive.
Esempio n. 2
Seleziona la colonna relativa alle vendite e al marketing previste
Vai al gruppo del grafico nella scheda Inserisci. Seleziona l'icona del grafico a dispersione
Questo inserirà il grafico a dispersione in Excel. Vedi immagine sotto
Fare clic con il tasto destro su un punto qualsiasi, quindi selezionare Aggiungi linea di tendenza in Excel. Questo aggiungerà una linea di tendenza al tuo grafico.
- È possibile formattare la linea di tendenza facendo clic con il pulsante destro del mouse in un punto qualsiasi della linea di tendenza e quindi selezionare Formato linea di tendenza.
- Puoi apportare ulteriori miglioramenti al grafico. cioè, formattando la linea di tendenza, il colore e il titolo del cambio, ecc
- Puoi anche mostrare la formula sul grafico controllando la formula Visualizza sul grafico e visualizzare il valore R quadrato sul grafico.
Alcuni altri esempi di analisi della regressione lineare:
- Previsione dell'ombrello venduto in base alla pioggia avvenuta in Area.
- Previsione di AC venduta in base alla temperatura in estate.
- Durante la stagione degli esami, le vendite di cancelleria sono aumentate sostanzialmente.
- Previsione delle vendite quando la pubblicità è stata effettuata sulla base di un numero di serie TRP elevato in cui viene eseguita una pubblicità, popolarità del Brand Ambassador e Footfalls nel luogo di detenzione in cui viene pubblicato un annuncio.
- Vendita di una casa in base a Località, Area e prezzo.
Esempio n. 3
Supponiamo di avere nove studenti con il loro livello di QI e il numero che hanno ottenuto nel test.
| Alunno | Punteggio del test | IQ |
| Ram | 100 | 145 |
| Shyam | 97 | 140 |
| Kul | 93 | 130 |
| Kappu | 91 | 125 |
| Raju | 89 | 115 |
| Vishal | 86 | 110 |
| Vivek | 82 | 100 |
| Vinay | 78 | 95 |
| Kumar | 75 | 90 |
Passaggio 1: in primo luogo, scopri le variabili dipendenti e indipendenti. Qui il punteggio del test è la variabile dipendente e il QI è la variabile indipendente poiché il punteggio del test varia al variare del QI.
Passaggio 2: vai alla scheda Dati - Fai clic su Analisi dei dati - Seleziona la regressione - fai clic su OK.
Questo aprirà la finestra di regressione per te.
Passaggio 3. Inserire l'intervallo del punteggio del test nella casella Input Y Range e IQ nella casella Input X Range. (Controlla su Etichette se hai intestazioni nell'intervallo di dati. Seleziona le opzioni di output, quindi controlla i Residui desiderati. Fai clic su Ok.
Otterrai l'output di riepilogo mostrato nell'immagine sottostante.
Passaggio 4: analisi della regressione tramite output di riepilogo
Output di riepilogo
R multiplo: qui, il coefficiente di correlazione è 0,99, che è molto vicino a 1, il che significa che la relazione lineare è molto positiva.
R Square: il valore R Square è 0,983, il che significa che il 98,3% dei valori si adatta al modello.
Valore P: qui, il valore P è 1,86881E-07, che è molto inferiore a .1, il che significa che il QI ha valori predittivi significativi.
Vedi la tabella qui sotto.
Puoi vedere che quasi tutti i punti stanno cadendo in linea o in una linea di tendenza vicina.
Esempio n. 4
Dobbiamo prevedere le vendite di AC in base alle vendite e alla temperatura per un mese diverso.
| Mese | Temp | I saldi |
| Jan | 25 | 38893 |
| Feb | 28 | 42254 |
| Mar | 31 | 42845 |
| Apr | 33 | 47917 |
| Maggio | 37 | 51243 |
| Jun | 40 | 69588 |
| Lug | 38 | 56570 |
| Ago | 37 | 50000 |
Segui i passaggi seguenti per ottenere il risultato della regressione.
Passaggio 1: in primo luogo, scopri le variabili dipendenti e indipendenti. Qui Sales è la variabile dipendente e Temperature è una variabile indipendente poiché le vendite variano man mano che Temp cambia.
Passaggio 2: vai alla scheda Dati - Fai clic su Analisi dei dati - Seleziona la regressione - fai clic su OK.
Questo aprirà la finestra di regressione per te.
Passaggio 3. Immettere le vendite nella casella Intervallo di input Y e Temp nella casella Intervallo di input X. (Controlla su Etichette se hai intestazioni nell'intervallo di dati. Seleziona le opzioni di output, quindi controlla i Residui desiderati. Fai clic su Ok.
Questo ti darà un output di riepilogo come di seguito.
Passaggio 4: analizzare il risultato.
R multiplo: qui, il coefficiente di correlazione è 0,877, che è vicino a 1, il che significa che la relazione lineare è positiva.
R Square: il valore R Square è 0,770, il che significa che il 77% dei valori si adatta al modello
Valore P: qui, il valore P è 1,86881E-07, che è molto inferiore a 0,1, il che significa che il QI ha valori predittivi significativi.
Esempio n. 5
Ora eseguiamo un'analisi di regressione per più variabili indipendenti:
È necessario prevedere le vendite di un dispositivo mobile che verrà lanciato il prossimo anno. Hai il prezzo e la popolazione dei paesi che stanno influenzando le vendite di cellulari.
| Versione mobile | I saldi | Quantità | Popolazione |
| NOI | 63860 | 858 | 823 |
| UK | 61841 | 877 | 660 |
| KZ | 60876 | 873 | 631 |
| CH | 58188 | 726 | 842 |
| HN | 52728 | 864 | 573 |
| AU | 52388 | 680 | 809 |
| NZ | 51075 | 728 | 661 |
| RU | 49019 | 689 | 778 |
Segui i passaggi seguenti per ottenere il risultato della regressione.
Passaggio 1. In primo luogo, trova le variabili dipendenti e indipendenti. Qui le vendite sono variabili dipendenti, quantità e popolazione. Entrambe sono variabili indipendenti poiché le vendite variano con la quantità e la popolazione del paese.
Passaggio 2. Vai alla scheda Dati - Fare clic su Analisi dati - Selezionare la regressione - fare clic su OK.
Questo aprirà la finestra di regressione per te.
Passaggio 3. Immettere le vendite nella casella Input intervallo Y e selezionare la quantità e la popolazione nella casella Input X intervallo. (Controlla su Etichette se hai intestazioni nell'intervallo di dati. Seleziona le opzioni di output, quindi controlla i Residui desiderati. Fai clic su Ok.
Ora esegui la regressione utilizzando l'analisi dei dati nella scheda Dati. Questo ti darà il risultato seguente.
Output di riepilogo
R multiplo: qui, il coefficiente di correlazione è 0,93, che è molto vicino a 1, il che significa che la relazione lineare è molto positiva.
R Square: il valore R Square è 0,866, il che significa che l'86,7% dei valori si adatta al modello.
Significatività F: Significance F è inferiore a .1, il che significa che l'equazione di regressione ha un valore predittivo significativo.
Valore P : se guardi il valore P per Quantità e Popolazione, puoi vedere che i valori sono inferiori a .1, il che significa che la quantità e la popolazione hanno un valore predittivo significativo. Il minor numero di valori P significa che una variabile ha valori predittivi più significativi.
Tuttavia, sia la quantità che la popolazione hanno un valore predittivo significativo, ma se guardi al valore P per quantità e popolazione, puoi vedere che la quantità ha un valore P inferiore in Excel rispetto alla popolazione. Ciò significa che la quantità ha un valore predittivo più significativo della popolazione.
Cose da ricordare
- Controllare sempre le variabili dipendenti e indipendenti ogni volta che si seleziona un dato.
- L'analisi di regressione lineare considera la relazione tra la media delle variabili.
- Questo modella solo la relazione tra le variabili che sono lineari
- A volte non è la soluzione migliore per un problema del mondo reale. Ad esempio: (età e salario). Il più delle volte, il salario aumenta con l'aumentare dell'età. Tuttavia, dopo il pensionamento, l'età aumenta ma i salari diminuiscono.
