Come trovare e tracciare i residui in Excel

I grafici dei residui svolgono un ruolo essenziale nell’analisi di regressione. Ti dicono quanto accuratamente la tua linea di regressione rappresenta la relazione tra due variabili. La seguente guida spiega come calcolare e tracciare i residui di Excel utilizzando due metodi.

Ma prima di arrivare alle cose buone, capiamo brevemente i residui e la loro importanza in un’analisi di regressione.

Cosa sono i residui e perché sono importanti?

Un residuo è una differenza tra il valore osservato (effettivo) e il valore previsto. In termini più semplici, un residuo è un errore. Ad esempio, se il valore osservato è 10 e il tuo modello ha fornito un valore di 8, il valore residuo sarebbe 2.

D’altra parte, un grafico dei residui è un grafico a dispersione in cui la variabile indipendente (età) è tracciata sull’asse orizzontale e i residui (errori) sono tracciati sull’asse verticale.

Ok, sai cosa sono i residui, ma perché sono importanti? Per capirlo, considera il seguente caso:

Vogliamo capire la relazione tra età e tassi di risparmio. Per questo, tracceremo una retta di regressione. Tuttavia, non siamo sicuri che la linea di regressione rappresenti accuratamente la relazione tra le due variabili. È qui che entrano in gioco i residui. Usiamo un grafico dei residui per verificare l’accuratezza della linea di regressione.

Ora che sai cosa sono i residui, impariamo come creare un grafico dei residui in Excel:

Come creare un grafico residuo utilizzando l’equazione della linea di tendenza

Il primo metodo per tracciare i residui in Excel utilizza un’equazione della linea di tendenza per calcolare i valori previsti per i nostri dati. Una volta ottenuto ciò, possiamo calcolare i residui sottraendo i valori previsti dai valori osservati. Infine, creiamo un grafico a dispersione con i predittori sull’asse orizzontale ei residui sull’asse verticale.

Ti guidiamo attraverso il processo, un passo alla volta:

Passaggio 1: inserire i dati

Iniziamo inserendo i valori per il predittore (sotto il X colonna) e i valori osservati (sotto il Y colonna):

Passaggio 2: creare un grafico a dispersione dei dati

Ora creiamo un grafico a dispersione dei dati seguendo i passaggi seguenti:

1. Seleziona i dati (sotto X E Y colonne).
2. Vai al Nastro di Excel e clicca Inserire.
3. Nel Grafici sezione, fare clic sulla freccia sotto la Grafico a dispersione opzione.

   

4. Seleziona il primo grafico a dispersione (Scatter con solo marcatori).
5. Otterrai un grafico a dispersione simile al seguente grafico:

   

Passaggio 3: aggiungere una linea di tendenza e visualizzare l’equazione della linea di tendenza sul grafico a dispersione

Segui i passaggi seguenti per aggiungere una linea di tendenza al grafico a dispersione:

1. Fare clic sul grafico a dispersione.
2. Vai al Nastro di Excel e clicca Disposizione.
3. Nel Sfondo sezione, fare clic sulla freccia sotto la Linea di tendenza icona.
4. Seleziona il Linea di tendenza lineare opzione.

   

Ora vedrai una linea (linea di tendenza) che interseca i punti sul grafico a dispersione. Per visualizzare l’equazione della linea di tendenza sul grafico a dispersione, attenersi alla seguente procedura:

1. Vai di nuovo al Linea di tendenza icona e fare clic sulla freccia sotto di essa.
2. Clic Altre opzioni della linea di tendenza.
3. UN Formato linea di tendenza apparirà la finestra di dialogo.
4. Controlla il Visualizza l’equazione sul grafico opzione nella parte inferiore del file Formato linea di tendenza la finestra di dialogo. L’equazione della linea di tendenza verrà mostrata sul grafico.

   

Passaggio 4: calcolare i valori previsti

Per calcolare i valori previsti, crea un’altra colonna (Z) accanto ai tuoi dati e procedi nel seguente modo:

1. Inserisci la formula della linea di tendenza mostrata sul grafico e incollala sotto la colonna Z (cell C2). Cambiare il X nella formula con i valori sotto la colonna X.
2. Quindi, per il primo valore previsto, la formula sarebbe data come segue:

   =1.5115*A2+23.133

   

3. Sostituire A2 con A3 nella formula della linea di tendenza per il secondo valore previsto.
4. Per il terzo valore previsto, sostituisci A3 con A4e così via, finché non si ottengono tutti i valori previsti per i predittori corrispondenti.

   

Passaggio 5: trova i valori residui

Ora che abbiamo i valori previsti, possiamo trovare i valori residui sottraendo i valori previsti dai valori osservati (effettivi) sotto la colonna Y. La formula per il primo valore residuo sarebbe =B2-C2. Per il secondo, la formula sarebbe =B3-DO3e così via.

Passaggio 6: creare il grafico residuo

Per creare un grafico residuo, abbiamo bisogno del predittore e dei valori residui. Ora che abbiamo entrambi, procedi nel seguente modo:

1. Seleziona le colonne Y E z.
2. Fare clic con il tasto destro e selezionare Nascondere.

   

3. Ora seleziona il X E Residui colonne.
4. Vai al Inserire scheda nella barra multifunzione di Excel.
5. Fare clic sulla freccia sotto il Disperdere icona.
6. Seleziona il Scatter con solo marcatori opzione.

   

7. Otterrai la tua trama residua, come mostrato di seguito.

   

Per il secondo metodo, tutto ciò che devi fare è fornire a Excel i dati e fa tutto il lavoro. Puoi creare una trama residua in pochi clic. Ma per questo, dovrai caricare gli strumenti di analisi di Excel. Iniziamo:

Passaggio 1: caricare gli strumenti di analisi

1. Vai al File scheda e fare clic Opzioni.
2. IL Opzioni Excel apparirà la finestra di dialogo.

   

3. Vai al Maneggio casella (in basso), selezionare Componenti aggiuntivi di Excele fare clic Andare.
4. UN Componenti aggiuntivi apparirà la finestra di dialogo.

   

5. Controlla il Strumenti di analisi casella e fare clic OK.
6. Ora vai al Nastro di Excel e clicca Dati.
7. Troverai il Analisi dei dati icona nel Analisi sezione.

   

Passaggio 2: inserire i dati

1. Seleziona le colonne UN E De fare clic con il pulsante destro del mouse. Clic Scopri.

   

2. Ora copia il file X E Y colonne.

   

   Noterai che il grafico è cambiato dopo aver scoperto le colonne. Per riportarlo alla trama residua originale, dovremo nascondere il file Y E z colonne (selezionando il Y E z colonne, facendo clic con il pulsante destro del mouse e selezionando Nascondere).

3. Apri un nuovo foglio Excel e incolla il file X E Y colonne.
4. Fare clic sul nuovo apparso Analisi dei dati opzione nel Analisi sezione del Dati scheda.
5. UN Analisi dei dati apparirà la finestra di dialogo.

   

6. Trova e seleziona Regressione Sotto Strumenti di analisi.
7. Clic OK. IL Regressione apparirà la finestra di dialogo.
8. Immettere i valori nel Y colonna (i predittori, B2: B11) nel Immettere l’intervallo Y campo.
9. Immettere l’intervallo di celle nel file X colonna (le variabili indipendenti, A2:A11) nel Immettere l’intervallo X campo.

   

Passaggio 3: creare il grafico residuo

Nel Regressione finestra di dialogo, sotto il Opzioni di uscitacontrollo Nuovo strato del foglio di lavoroe fare clic OK.

Excel creerà automaticamente il grafico residuo insieme ai seguenti output che è possibile utilizzare per verificare l’affidabilità del modello di regressione:

• Statistiche di regressione
• Tabella ANOVA
• Tabella dei coefficienti
• Uscita residua

Excel’s Analysis ToolPak offre una gamma di preziose funzionalità statistiche che puoi utilizzare per analizzare i tuoi set di dati. Dopo aver creato il grafico residuo tramite l’Analysis ToolPak di Excel, potresti iniziare a grattarti la testa guardando tutte le tabelle complicate. Ma una volta che impari a eseguire l’analisi fondamentale dei dati in Excel, i numeri non sembrano così spaventosi.