Analisi della regressione di Excel (sommario)
- Analisi di regressione in Excel
- Spiegazione della regressione matematica
- Come eseguire la regressione lineare in Excel?
- # 1 - Strumento di regressione che utilizza Analysis ToolPak in Excel
- # 2 - Analisi di regressione tramite Scatterplot con Trendline in Excel
Analisi di regressione in Excel
La regressione lineare è una tecnica statistica che esamina la relazione lineare tra una variabile dipendente e una o più variabili indipendenti.
- Variabile dipendente (nota anche come variabile risposta / risultato): è la variabile di tuo interesse e che desideri prevedere in base alle informazioni disponibili sulle variabili indipendenti.
- Variabile indipendente (nota anche come variabile esplicativa / predittiva): è / sono le variabili da cui dipende la variabile di risposta. Ciò significa che queste sono le variabili che utilizzano la variabile di risposta che può essere prevista.
Relazione lineare significa che il cambiamento in una o più variabili indipendenti provoca un cambiamento nella variabile dipendente.
Esistono fondamentalmente anche due tipi di relazioni lineari.
- Relazione lineare positiva: quando aumenta la variabile indipendente, aumenta anche la variabile dipendente.
- Relazione lineare negativa: quando aumenta la variabile indipendente, la variabile dipendente diminuisce.
Questi erano alcuni dei prerequisiti prima di procedere effettivamente all'analisi di regressione in Excel.
Esistono due modi di base per eseguire la regressione lineare in Excel utilizzando:
- Strumento di regressione tramite Analysis ToolPak
- Grafico a dispersione con linea di tendenza
Esiste in realtà un altro metodo che utilizza le formule manuali per calcolare la regressione lineare. Ma perché dovresti provarci quando Excel fa i calcoli per te?
Pertanto, parleremo solo dei due metodi discussi sopra.
Supponiamo che tu abbia un dato di altezza e peso di 10 individui. Se traccia queste informazioni attraverso un grafico, vediamo cosa fornisce.
Come mostra lo screenshot sopra, la relazione lineare può essere trovata in Altezza e Peso attraverso il grafico. Non essere molto coinvolto nel grafico ora, stiamo comunque scavando in profondità nella seconda parte di questo articolo.
Spiegazione della regressione matematica
Abbiamo un'espressione matematica per la regressione lineare come di seguito:
Y = aX + b + ε
Dove,
- Y è una variabile dipendente o variabile di risposta.
- X è una variabile o un predittore indipendente.
- a è la pendenza della linea di regressione. Il che rappresenta che quando X cambia, c'è una variazione in Y di "a" unità.
- b sta intercettando. È il valore che Y assume quando il valore di X è zero.
- ε è il termine di errore casuale. Si verifica perché il valore previsto di Y non sarà mai esattamente lo stesso del valore effettivo per X dato. Questo termine di errore, non dobbiamo preoccuparci. Poiché ci sono alcuni software che fanno il calcolo di questo termine di errore nel backend per te. Excel è uno di quei software.
In tal caso, l'equazione diventa,
Y = aX + b
Che può essere rappresentato come:
Peso = a * Altezza + b
Cercheremo di scoprire i valori di questi aeb usando i metodi di cui abbiamo discusso sopra.
Come eseguire la regressione lineare in Excel?
Il prossimo articolo spiega le basi dell'analisi di regressione in Excel e mostra alcuni modi diversi per eseguire la regressione lineare in Excel.
Puoi scaricare questo modello Excel di analisi di regressione qui - Modello Excel di analisi di regressione# 1 - Strumento di regressione che utilizza Analysis ToolPak in Excel
Nel nostro esempio, proveremo ad adattare la regressione per i valori di Peso (che è una variabile dipendente) con l'aiuto dei valori di Altezza (che è una variabile indipendente).
- Nel foglio di calcolo Excel, fai clic su Analisi dei dati (presente in Gruppo di analisi ) in Dati.
- Cerca la regressione . Selezionalo e premi ok.
- Utilizzare i seguenti input nel riquadro Regressione che si apre.
- Input Y Range : Seleziona le celle che contengono la tua variabile dipendente (in questo esempio B1: B11)
- Input X Range : seleziona le celle che contengono la tua variabile indipendente (in questo esempio A1: A11).
- Seleziona la casella Etichette se i tuoi dati hanno nomi di colonna (in questo esempio abbiamo nomi di colonne).
- Il livello di confidenza è impostato al 95% per impostazione predefinita, che può essere modificato in base alle esigenze degli utenti.
- In Opzioni di output, puoi personalizzare la posizione in cui desideri visualizzare l'output dell'analisi di regressione in Excel. In questo caso, vogliamo vedere l'output sullo stesso foglio. Pertanto, dato intervallo di conseguenza.
- Sotto l'opzione Residui, hai input opzionali come Residui, Grafici residui, Residui standardizzati, Grafici di adattamento linea che puoi selezionare secondo le tue necessità. In questo caso, selezionare la casella di controllo Residui in modo da poter vedere la dispersione tra i valori previsti e quelli effettivi.
- Sotto l'opzione Probabilità normale, puoi selezionare Grafici di probabilità normali che possono aiutarti a controllare la normalità dei predittori. Clicca su OK .
- Excel calcolerà l'analisi della regressione per te in una frazione di secondi.
Fino a qui, è stato facile e non così logico. Tuttavia, interpretare questo risultato e ricavarne preziosi spunti è un compito complicato.
Una parte importante di questo intero output è R Square / R rettificato R sotto la tabella SINTESI OUTPUT. Che fornisce informazioni su quanto è adatto il nostro modello. In questo caso, il valore di R Square è 0, 9547. Il che interpreta che il modello ha una precisione del 95, 47% (buona vestibilità). O in un'altra lingua, le informazioni sulla variabile Y sono spiegate al 95, 47% dalla variabile X.
L'altra parte importante dell'intero output è una tabella di coefficienti. Fornisce valori di coefficienti che possono essere utilizzati per costruire il modello per previsioni future.
Ora la nostra equazione di regressione per la previsione diventa:
Peso = 0, 6746 * Altezza - 38, 45508 (il valore della pendenza per l'altezza è 0, 6746 … e l'intercettazione è -38, 45508 …)
Hai ottenuto ciò che hai definito? Hai definito una funzione in cui ora devi solo inserire il valore di Height e otterrai il valore di Weight.
# 2 - Analisi di regressione tramite Scatterplot con Trendline in Excel
Ora vedremo come in Excel possiamo adattare un'equazione di regressione su un diagramma a dispersione stesso.
- Seleziona tutti i tuoi dati a due colonne (comprese le intestazioni).
- Fare clic su Inserisci e selezionare Grafico a dispersione nella sezione dei grafici, come mostrato nell'immagine seguente.
- Vedi il grafico di output.
- Ora, dobbiamo avere una linea di regressione almeno quadrata su questo grafico. Per aggiungere questa linea, fare clic con il tasto destro su uno dei punti dati sul grafico e selezionare l'opzione Aggiungi linea di tendenza .
- Ti consentirà di avere una linea di tendenza del minimo quadrato di regressione come di seguito.
- Sotto l'opzione Formatta linea di tendenza, seleziona la casella Visualizza equazione sul grafico.
- Ti consente di vedere l'equazione della linea di regressione al minimo quadrato sul grafico.
Questa è l'equazione con la quale possiamo prevedere i valori di peso per ogni dato set di valori di Altezza.
Cose da ricordare sull'analisi della regressione in Excel
- È possibile modificare il layout della linea di tendenza sotto l'opzione Formato linea di tendenza nel grafico a dispersione.
- Si consiglia sempre di dare un'occhiata ai grafici residui mentre si esegue l'analisi di regressione utilizzando Data Analysis ToolPak in Excel. Ti dà una migliore comprensione della diffusione dei valori Y effettivi e dei valori X stimati.
- La semplice regressione lineare in Excel non richiede ANOVA e rettificato R Square per controllare. Queste funzioni possono essere prese in considerazione per la regressione lineare multipla. Che va oltre lo scopo di questo articolo.
Articoli consigliati
Questa è stata una guida per l'analisi di regressione in Excel. Qui discutiamo come eseguire l'analisi di regressione in Excel insieme a esempi di Excel e modello di Excel scaricabile. Puoi anche consultare i nostri altri articoli suggeriti:
- Strumento Excel per l'analisi dei dati
- Calcola ANOVA in Excel
- Come trovare le medie mobili di Excel
- Z TEST Esempi in Excel