La regressione è uno strumento di analisi, che utilizziamo per analizzare grandi quantità di dati e fare previsioni e previsioni in Microsoft Excel.
Vuoi prevedere il futuro? No, non impareremo l'astrologia. Ci interessano i numeri e oggi impareremo l'analisi di regressione in Excel.
Per prevedere le stime future, studieremo:
- ANALISI DELLA REGRESSIONE MEDIANTE FUNZIONI EXCEL (RICERCA MANUALE DELLA REGRESSIONE)
- ANALISI DI REGRESSIONE UTILIZZANDO IL TOOLPAK DI ANALISI DI EXCEL ADD-IN
- TABELLA DI REGRESSIONE IN EXCEL
Facciamolo…
Scenario:
Supponiamo che tu venda bibite. Quanto sarà bello se puoi prevedere:
- Quante bibite saranno vendute l'anno prossimo in base ai dati dell'anno precedente?
- Quali campi devono essere focalizzati?
- E come puoi aumentare le tue vendite cambiando la tua strategia?
Sarà redditizio fantastico. Giusto?… Lo so. Quindi iniziamo.
Hai 11 record di venditori e bibite vendute.
Ora sulla base di questi dati si vuole prevedere il numero di venditori necessari per realizzare 2000 vendite di bibite.
L'equazione di regressione è uno strumento per fare stime così ravvicinate. Per fare ciò, dobbiamo prima conoscere la regressione.
ANALISI DELLA REGRESSIONE MEDIANTE FUNZIONI EXCEL (RICERCA MANUALE DELLA REGRESSIONE)
Questa parte ti farà capire la regressione meglio che raccontare semplicemente la procedura di regressione di Excel.
Introduzione:
Regressione lineare semplice:
Lo studio della relazione tra due variabili è chiamato Regressione lineare semplice. Dove una variabile dipende dall'altra variabile indipendente. La variabile dipendente viene spesso chiamata con nomi come Driven, Response e Target variable. E la variabile indipendente è spesso pronunciata come Driving, Predictor o semplicemente variabile indipendente. Questi nomi li descrivono chiaramente.
Ora confrontiamo questo con il tuo scenario. Vuoi conoscere il numero di venditori necessari per raggiungere 2000 vendite. Quindi qui, la variabile dipendente è il numero di venditori e la variabile indipendente è la vendita di bibite.
La variabile indipendente è per lo più indicata come X e variabile dipendente come y.
Nel nostro caso si vendono bibite X e il numero di venditori è y.
Se vogliamo sapere quante bibite verranno vendute se nominiamo 200 venditori, allora lo scenario sarà viceversa.
Andare avanti.
La matematica "semplice" dell'equazione di regressione lineare:
Beh, non è semplice. Ma Excel lo ha reso semplice da fare.
Dobbiamo prevedere il numero richiesto di venditori per tutti gli 11 casi per ottenere la dodicesima previsione più vicina.
Diciamo:
La bibita venduta è X
Il numero dei venditori è sì
Il predetto sì (numero di venditori) chiamato anche Equazione di regressione, sarebbe
| x*Pendenza+Intercetta (rilassati, ho coperto) |
Ora ti starai chiedendo dove statistica otterrai la pendenza e l'intercettazione. Non preoccuparti, Excel ha funzioni per loro. Non è necessario imparare a trovare la pendenza e ad intercettarla manualmente.
Se vuoi, preparerò un tutorial separato per questo. Fatemi sapere nella sezione dedicata ai commenti. Questi sono alcuni importanti strumenti di analisi dei dati.
Ora passiamo al nostro calcolo:
Passo 1: Prepara questo tavolino
Passo 2: Trova la pendenza della retta di regressione
La funzione Excel per le piste è
| =PENDENZA(y_nota,x_nota) |
I tuoi noti sono nel raggio d'azione B2: B12 e le x_note sono nel raggio d'azione C2: C12
in cella B16, scrivi la formula qui sotto
| =PENDENZA(B2:B12, C2:C12) |
(Nota: la pendenza è anche chiamata coefficiente di x nell'equazione di regressione)
Otterrete 0.058409. Arrotonda fino a 2 cifre decimali e otterrai 0.06.
Passaggio 3: Trova l'intercetta della linea di regressione
La funzione di Excel per l'intercetta è
| =INTERCETTA(y_nota, x_nota) |
Sappiamo qual è il nostro note x e y
in cella B17, scrivi questa formula
| =INTERCETTA(B2: B12, C2: C12) |
Otterrai un valore di -1,1118969. Arrotondare a 2 cifre decimali. Otterrete -1.11.
La nostra equazione di regressione lineare è = x*0.06 + (-1.11). Ora possiamo prevedere facilmente y possibili a seconda dell'obiettivo x.
Passaggio 4: In D2 scrivi la formula qui sotto
| =do2*$B$16+$B$17(Equazione di regressione) |
Otterrai un valore di 13.55.
Selezionare da D2 a D13 e premere CTRL+D per riempire la formula nell'intervallo D2: D13
in cella D13 hai il numero richiesto di venditori.
| Quindi, per raggiungere l'obiettivo di 2000 Vendite di bevande analcoliche, hai bisogno di una stima di 115,71 venditori o diciamo 116 poiché è illegale tagliare a pezzi gli esseri umani. |
Ora usando questo puoi facilmente condurre analisi What-If in Excel. Basta cambiare il numero di vendite e ti mostrerà che molti venditori ci vorranno per raggiungere quell'obiettivo di vendita.
Giocaci intorno per scoprire:
Di quanta forza lavoro hai bisogno per aumentare le vendite?
Quante vendite aumenteranno se aumenti i tuoi venditori?
Rendi la tua stima più affidabile:
Ora sai che hai bisogno di 116 venditori per portare a termine 2000 vendite.
Nell'analisi, nulla viene semplicemente detto e creduto. Devi dare una percentuale di affidabilità sulla tua stima. È come dare un certificato della tua equazione.
Formula del coefficiente di correlazione:
La prossima cosa che ti verrà chiesto è quanto queste due variabili siano correlate. In termini statici, devi dire il coefficiente di correlazione.
La funzione di Excel per la correlazione è
| =CORREL(matrice1, matrice2) |
Nel tuo caso, x_nota e y_conosciuto sono array1 e array2 indipendentemente.
In B18 inserisci questa formula
| =CORRENTE((B2: B12, C2: C12) |
Tu avrai 0.919090. Formatta la cella B2 nella percentuale. ora avere 92% di correlazione.
Ora, che cosa? 92% si intende. Significa che c'è 92% di possibilità di vendita aumentano se si aumenta il numero di venditori e 92% delle vendite diminuisce se si diminuisce il numero dei venditori. È chiamato Coefficiente di correlazione positivo.
R Scudiero (R^2) :
Il valore R Squire ti dice, di quale percentuale la tua equazione di regressione non è un colpo di fortuna. Quanto è accurato dai dati forniti.
La funzione di Excel per R scudiero è RSQ.
| RSQ(y_nota, x_nota) |
Nel nostro caso, otterremo il valore R scudiero nella cella B19.
In B19 inserisci questa formula
| =RSQ(B2: B12, C2: C12) |
Quindi abbiamo l'84% del valore di r Square. Che è un'ottima spiegazione della nostra regressione. Dice che l'84% dei nostri dati non è casuale. Y (numero di venditori) dipende molto da X (vendite di bibite).
Ci sono molti altri test che possiamo fare su questi dati per garantire la nostra regressione. Ma manualmente sarà una procedura complessa e lunga. Ecco perché excel fornisce Analysis Toolpak. Usando questo strumento possiamo fare questa analisi di regressione in pochi secondi.
REGRESSIONE IN EXCEL MEDIANTE IL TOOLPAK DI ANALISI DI EXCEL ADD-IN
Se sai già cosa sono le equazioni di regressione e vuoi solo i tuoi risultati rapidamente, questa parte fa per te. Ma se vuoi capire facilmente le equazioni di regressione, scorri verso l'alto fino a ANALISI DELLA REGRESSIONE UTILIZZANDO LE FUNZIONI EXCEL (RICERCA MANUALE DELLA REGRESSIONE).
Excel fornisce una serie di strumenti per l'analisi nel suo Analysis Toolpak. Per impostazione predefinita, non è disponibile nella scheda Dati. Devi aggiungerlo. Quindi aggiungiamolo prima.
Aggiunta di strumenti di analisi a Excel 2016
Se non sai dove si trova l'analisi dei dati in Excel, segui questi passaggi
Passaggio 1: vai a Opzioni di Excel: File? Opzioni? Componenti aggiuntivi
Passaggio 2: fare clic su Componenti aggiuntivi. Vedrai un elenco di componenti aggiuntivi disponibili.
Seleziona Strumenti di analisi e nella parte inferiore della finestra, trova Gestisci. In gestisci seleziona Componenti aggiuntivi di Excel e fai clic su VAI.
Si aprirà la finestra dei componenti aggiuntivi. Qui, seleziona Strumenti di analisi. Quindi fare clic sul pulsante ok.
Ora puoi accedere a tutte le funzioni di ToolPak per l'analisi dei dati dalla scheda Dati.
Utilizzo degli strumenti di analisi per la regressione
Passaggio 1: vai alla scheda Dati, Individua analisi dei dati. Quindi fare clic su di esso.
Apparirà una finestra di dialogo.
Passaggio 2: trova "Regressione" nell'elenco Strumenti di analisi e premi il pulsante OK.
La regressione si aprirà la finestra di inserimento. Vedrai una serie di opzioni di input disponibili. Ma per ora, ci concentreremo solo su Y Range e X Range, lasciando tutto il resto come predefinito.
Passaggio 4: fornire input:
Il numero di venditori è sì
Le vendite di bibite sono X
Quindi
- Y Intervallo = B2: B11
e
- X Intervallo = C2: C11
Per l'intervallo di output, ho selezionato E4 sullo stesso foglio. È possibile selezionare un nuovo foglio di lavoro per ottenere risultati su un nuovo foglio di lavoro nella stessa cartella di lavoro o su una nuova cartella di lavoro completa. Quando hai finito con i tuoi input, premi il pulsante OK.
Risultati:
Sarai servito con una varietà di informazioni dai tuoi dati. Non lasciarti sopraffare. Non è necessario consumare tutti i piatti.
Ci occuperemo solo di quei risultati che ci aiuteranno a stimare il numero richiesto di venditori
Passaggio 5: Conosciamo l'equazione di regressione per la stima di si, questo è
x*Pendenza+Intercetta
Abbiamo solo bisogno di individuare Pendenza e Intercettare nei risultati.
Ed eccoli.
Il coefficiente di intercettazione è chiaramente menzionato.
La pendenza è scritta come "X Variabile 1', a volte indicato anche come coefficiente di X. Arrotondateli e otterremo -1.11 come intercetta e 0,06 come Pendenza.
Passaggio 6: Dai risultati, possiamo guidare l'equazione di regressione. E questo sarebbe
=x*(0,06) + (-1,11)
Prepara questa tabella in excel.
Per adesso, X è 2000, che è nella cella E2.
Nella cella F2 inserisci questa formula
=MI2*Fa21+Fa20
Otterrai un risultato di 115.7052757.
Arrotondare per eccesso ci darà 116 dei Venditori Obbligati.
Quindi abbiamo imparato come formare manualmente l'equazione di regressione e utilizzando Analysis ToolPak. Come puoi usare questa equazione per stimare le statistiche future?
Ora comprendiamo l'output di regressione fornito da Analysis Toolpak.
Comprensione dell'output della regressione:
Non vi è alcun vantaggio se si esegue l'analisi di regressione utilizzando il pacchetto di strumenti di analisi in Excel e non si riesce a interpretarne il significato.
Sezione Riepilogo:
Come suggerisce il nome, è una sintesi dei dati.
-
- Multiplo R: indica quanto è adatta l'equazione di regressione ai dati. Viene anche chiamato coefficiente di correlazione.
Nel nostro caso è 0.919090619 o 0.92 (arrotondare). Ciò significa che c'è una probabilità del 92% di un aumento delle vendite se aumentiamo il numero dei nostri venditori.
-
- R quadrato: indica l'affidabilità della regressione trovata. Ci dice quante osservazioni fanno parte della nostra linea di regressione. Nel nostro caso, è 0,844727566 o 0,85. Significa che la nostra regressione è adatta dell'85%.
- R quadrato rettificato: Il quadrato aggiustato è solo una versione più testimoniata di R quadrato. Utile principalmente nell'analisi di regressione multipla.
- Errore standard: Mentre R. Squire ti dice quanti punti dati cadono vicino alla linea di regressione, l'errore standard ti dice quanto lontano può andare un punto dati dalla linea di regressione.
Nel nostro caso è 6.74.
- Osservazione: questo è semplicemente il numero di osservazioni, che è 11 nel nostro esempio.
Sezione Anova:
Questa sezione è poco utilizzata nella regressione lineare.
- df. È un grado di libertà. Viene utilizzato durante il calcolo manuale della regressione.
- SS. Somma dei quadrati. È solo una somma dei quadrati delle varianze. Usato per trovare i valori R scudiero.
- SM. Questo significa valore al quadrato.
- e 5. F e Significato di F. Se il significato di F (valore p della pendenza) è inferiore al test F di quanto puoi scartare l'ipotesi nulla e dimostrare la tua ipotesi. In un linguaggio semplice, puoi concludere che c'è qualche effetto di x su y quando viene modificato.
Nel nostro caso, F è 48,96264 e il significato di F è 0,0000063. Significa che la nostra regressione si adatta ai dati.
Sezione di regressione:
In questa sezione abbiamo i due valori più importanti per la nostra equazione di regressione.
- Intercetta: qui abbiamo un'intercetta che indica dove si intercetta x su Y. Questa è una parte importante dell'equazione di regressione. È -1,11 nel nostro caso.
- X variabile 1 (Pendenza). Chiamato anche coefficiente di x. Definisce la tangente della retta di regressione.
TABELLA DI REGRESSIONE IN EXCEL
In Excel, è facile tracciare un grafico di regressione. Basta seguire questi passaggi. Per aggiungere un grafico di regressione in Excel 2016, 2013 e 2010 segui questi semplici passaggi.
Passo 1. Conosci le tue x nella prima colonna e conosci le y nella seconda.
Nel nostro caso, sappiamo che le x conosciute sono bibite vendute. E quelli conosciuti sono i venditori.
Passo 2. Seleziona l'intervallo di x e y noto.
Passaggio 3: Vai alla scheda Inserisci e fai clic sul grafico a dispersione.
Avrai un grafico simile a questo.
Passaggio 4. Aggiungi la linea di tendenza: Vai al layout e individua l'opzione della linea di tendenza nella sezione di analisi.
Sotto l'opzione Linea di tendenza, fai clic su Linea di tendenza lineare.
Avrai il tuo grafico simile a questo.
Questo è il tuo grafico di regressione.
Ora se aggiungi i dati di seguito ed estendi i dati selezionati. Vedrai un cambiamento nel tuo grafico.
Per il nostro esempio, abbiamo aggiunto 2000 alla bibita venduta e lasciato vuoto il campo Venditori. E quando estendiamo l'intervallo del grafico, questo è ciò che avremo.
Darà il numero richiesto di venditori per fare 2000 vendite di bibite in forma grafica. Che è leggermente inferiore a 120 nel grafico. E dalla nostra equazione di regressione, sappiamo che è 116.
In questo articolo, ho cercato di coprire tutto sotto Analisi della regressione di Excel. Ho spiegato la regressione in excel 2016. La regressione in excel 2010 ed excel 2013 è la stessa di excel 2016.
Per qualsiasi ulteriore domanda su questo argomento, utilizzare la sezione commenti. Fai una domanda, esprimi un'opinione o semplicemente menziona i miei errori grammaticali. Tutto è benvenuto. Non esitare a utilizzare la sezione commenti.
Come calcolare la funzione MODALITÀ in Excel
Come calcolare la funzione media in Excel
Come creare un grafico della deviazione standard
Statistiche descrittive in Microsoft Excel 2016
Come utilizzare la funzione DISTRIB.NORM di Excel
Come utilizzare il diagramma e l'analisi di Pareto
Articoli popolari:
50 scorciatoie Excel per aumentare la produttività
Come utilizzare la funzione CERCA.VERT in Excel
Come utilizzare la funzione CONTA.SE in Excel 2016
Come utilizzare la funzione SOMMA.SE in Excel