320 Shares 7666 views

Regressione in Excel: esempi equazione. regressione lineare

analisi di regressione – un metodo di studio statistico che mostra la dipendenza di un parametro di una o più variabili indipendenti. Nell'era pre-informatica, il suo uso è stato piuttosto difficile, soprattutto quando si trattava di grandi volumi di dati. Oggi, imparare a costruire una regressione in Excel, è possibile risolvere i problemi statistici complessi in pochi minuti. Di seguito sono riportati esempi specifici di economia.

tipi di regressione

Questo concetto è stato introdotto alla matematica da Francis Galton nel 1886. La regressione è:

  • lineari;
  • parabolica;
  • alimentazione;
  • esponenziale;
  • iperbolico;
  • esponenziale;
  • logaritmica.

ESEMPIO 1

Si consideri il problema di determinare la dipendenza del numero di dimissioni dei membri del personale della retribuzione media nelle 6 imprese industriali.

Task. Sei società hanno analizzato il salario medio mensile e il numero di dipendenti che smettono volontariamente. In forma tabellare abbiamo:

la

B

C

1

X

Numero di dimissioni

stipendio

2

y

30000 rubli

3

1

60

35000 rubli

4

2

35

40000 rubli

5

3

20

45000 rubli

6

4

20

50.000 rubli

7

5

15

55000 rubli

8

6

15

60000 rubli

Per il problema di determinare la dipendenza dei lavoratori importo separazioni dal stipendio medio per 6 imprese modello di regressione ha la forma di equazione y = a 0 + a 1 x 1 + … + a k x k, dove x i – variabili di influenza, un i – coefficienti di regressione, ak – serie di fattori.

Y per un determinato compito – è un indicatore di licenziare un dipendente, un fattore che contribuisce – lo stipendio, che viene indicato con X.

Sfruttando la potenza di foglio di calcolo "Excel"

L'analisi di regressione in Excel dovrebbe essere preceduta da un'applicazione per i dati della tabella esistenti funzioni incorporate. Tuttavia, per questi scopi è preferibile utilizzare un utilissimo add-in "Analisi dei pacchetti". Per abilitarla, è necessario:

  • con il "File" tab andare su "Impostazioni";
  • nella finestra che si apre, selezionare 'Add-ons';
  • cliccare sul pulsante "Go", che si trova in basso a destra della "gestione" linea;
  • mettere un segno di spunta accanto a "Strumenti di analisi" e confermare l'operazione premendo il tasto "OK".

Se fatto correttamente, il lato destro della scheda "Dati", che si trova sopra il foglio di lavoro "Excel", mostra il tasto desiderato.

Regressione lineare in Excel

Ora che avete a portata di mano tutti gli strumenti virtuali necessari per i calcoli econometrici, possiamo cominciare ad affrontare il nostro problema. Per fare questo:

  • pulsante viene premuto sulla "Analisi dei dati";
  • cliccare sul pulsante "regressione" nella finestra aperta;
  • una linguetta che sembra introdurre un intervallo di valori Y (il numero di separazioni lavoratori) e X (stipendio);
  • ribadire le loro azioni premendo il pulsante «OK».

Di conseguenza, il programma riempirà automaticamente la nuova analisi di regressione dei dati foglio di foglio di calcolo. Fate attenzione! In Excel, v'è la possibilità di impostare il posto che si preferisce per questo scopo. Ad esempio, può essere lo stesso foglio, in cui i valori Y e X, o anche un nuovo libro, specificamente progettato per la memorizzazione di tali dati.

risultati di analisi di regressione per R-square

I dati di Excel ottenuti i dati di esempio considerati hanno la forma:

Prima di tutto, dovremmo prestare attenzione al valore di R al quadrato. Esso rappresenta il coefficiente di determinazione. In questo esempio, R-quadrato = 0,755 (75,5%), m. E. I parametri calcolati del modello per spiegare la relazione tra i parametri considerati dal 75,5%. Più alto è il valore del coefficiente di determinazione, il modello selezionato è considerato più utile per compiti particolari. Si ritiene descrivere correttamente la situazione reale al valore R-quadrato sopra 0.8. Se il R-quadrato <0.5, poi un'analisi di regressione in Excel non può essere considerato ragionevole.

analisi del rapporto

Numero 64,1428 mostra quale sarà il valore della Y, se tutte le variabili xi nel nostro modello sarà azzerato. In altre parole, si può sostenere che il valore del parametro analizzato è influenzata da fattori diversi da quelli descritti nel modello specifico.

Il fattore successivo -,16285 situato nella B18 cellulare, mostra l'influenza importante della variabile X a Y. Questo significa che il salario medio dei dipendenti all'interno del modello influenza il numero di dimissioni dal peso di -0,16285, t. E. Il grado del suo impatto a tutti piccolo. Il segno "-" indica che il coefficiente è negativo. E 'evidente, dal momento che sappiamo tutti che quanto più lo stipendio in azienda, meno persone hanno espresso il desiderio di risolvere il contratto di lavoro o licenziati.

regressione multipla

Sotto questo termine si riferisce alla equazione comunicazione con diverse variabili indipendenti della forma:

y = f (x + 1 x 2 + x … m) + ε, dove y – è un punteggio caratteristica (variabile dipendente), e x 1, x 2, x … m – sono fattori segni (variabili indipendenti).

stima dei parametri

Per la regressione multipla (MR) viene eseguita con un metodo dei minimi quadrati (LSM). Per le equazioni lineari della forma Y = a + b 1 x 1 + … + b m x m + ε costruzione di un sistema di equazioni normali (cm. Sotto)

Per capire il principio del metodo, consideriamo il caso a due fattori. Poi abbiamo la situazione descritta dalla formula

Quindi, si ottiene:

dove σ – è la varianza della rispettiva caratteristica, riflessa nell'indice.

MNC si applica l'equazione di MR standartiziruemom scala. In questo caso, si ottiene l'equazione:

in cui t y, t x 1, … t xm standartiziruemye variabili i cui valori medi sono 0; ß i – coefficienti di regressione standardizzati e deviazione standard – 1.

Si prega di notare che tutti i beta in questo caso definita come normalizzata e tsentraliziruemye, quindi un confronto tra un considerato valido e accettabile. Inoltre, è accettato di effettuare lo screening di fattori, scartando quelle che hanno i valori più bassi di βi.

Il problema utilizzando un'equazione di regressione lineare

Si supponga di avere una tabella delle dinamiche del prezzo di un determinato prodotto N per gli ultimi 8 mesi. È necessario decidere se l'acquisizione del suo partito al prezzo di 1850 rubli. / T.

la

B

C

1

il mese

nome del mese

Prezzo N

2

1

gennaio

1750 rubli per tonnellata

3

2

febbraio

1755 rubli per tonnellata

4

3

marzo

1767 rubli per tonnellata

5

4

aprile

1760 rubli per tonnellata

6

5

maggio

1770 rubli per tonnellata

7

6

giugno

1790 rubli per tonnellata

8

7

luglio

1810 rubli per tonnellata

9

8

agosto

1840 rubli per tonnellata

Per risolvere questo problema nel processore tabellare "Excel" necessaria per utilizzare già noto ad esempio funzione "Analisi dei dati" di cui sopra. Quindi, scegliere la sezione "regressione" e impostare i parametri. Dobbiamo ricordare che nel "Campo di ingresso Y» dovrebbe essere introdotto ad un intervallo di valori della variabile dipendente (in questo caso il prezzo delle merci in mesi specifici dell'anno) e nel "intervallo di X Input» – per un indipendente (del mese). Confermiamo l'azione facendo clic «Ok». In un nuovo foglio di lavoro (se così indicato), otteniamo i dati per la regressione.

Stiamo costruendo sopra un'equazione lineare della forma y = ax + b, dove come i parametri a e b sono i coefficienti del numero di riga del mese e nome dei coefficienti e la linea «Y-intersezione" del foglio con i risultati delle analisi di regressione. Pertanto, l'equazione di regressione lineare (EQ) 3 per il problema può essere scritta come:

Il prezzo dei beni N = 11.714 * 1727,54 mesi il numero +.

o in notazione algebrica

y = 11.714 x + 1727,54

Analisi dei risultati

Per decidere se la ricevuta equazione di regressione lineare utilizzando adeguatamente i coefficienti di correlazione multipla (CMC) e determinazione nonché prova e t-test di Fisher. Nella tabella di regressione "Excel" con i risultati che agiscono sotto i nomi più R, R-quadrato, rispettivamente F-t-statistiche e statistiche,.

KMC R permette di valutare il rapporto probabilistico vicinanza tra variabili indipendenti e dipendenti. Il suo alto valore indica un forte legame sufficiente tra la variabile "Numero del mese" e "N Prezzo del prodotto in rubli per 1 tonnellata." Tuttavia, la natura di questo rapporto è sconosciuta.

Il quadrato del coefficiente di determinazione R 2 (RI) è una caratteristica numerica della percentuale di dispersione totale e mostra una dispersione di parte di dati sperimentali, cioè, valori della variabile dipendente corrispondente a un'equazione di regressione lineare. In questo problema, questo valore è 84,8%, pf. E. Statistiche con un alto grado di precisione ottenuta sono descritti SD.

F-statistica, noti anche come criterio di Fisher utilizzati per valutare il significato della dipendenza lineare o confutare l'ipotesi confermando la sua esistenza.

Il valore della statistica t (t di Student) aiuta a valutare la significatività del coefficiente in qualsiasi sconosciuto membro dipendenza lineare libero. Se il valore di t-test> t cr, l'ipotesi di un'equazione lineare insignificante di libera termine è respinta.

In questo problema per un periodo gratuito attraverso strumenti "Excel" si è constatato che t = 169,20903, e p = 2,89E-12, t. E. Avere una probabilità pari a zero che i fedeli sarà respinta l'ipotesi della insignificanza del termine libero. Per coefficiente sconosciuta a t = 5,79405, e p = 0,001158. In altre parole, la probabilità che un'ipotesi corretta rifiutata sarà insignificante del coefficiente per l'ignoto, è 0,12%.

Così, si può sostenere che la ottiene equazione di regressione lineare in modo adeguato.

Il problema della opportunità di acquisto di azioni

regressione multipla è stata eseguita in Excel utilizzando lo stesso strumento "Analisi dei dati". Prendere in considerazione l'applicazione specifica.

Guida società «NNN» deve decidere se acquistare il 20% delle azioni della JSC «MMM». Prezzo del pacchetto (SP) è di 70 milioni di dollari. Gli specialisti del «NNN» raccolti dati sulle operazioni similari. Si è deciso di valutare il valore delle azioni su tali parametri, espressi in milioni di dollari USA, come ad esempio:

  • debiti (VK);
  • volume di fatturato annuo (VO);
  • crediti (VD);
  • valore delle immobilizzazioni (SOF).

Inoltre, utilizzare i debiti salario di imprese (V3 U) in migliaia di dollari.

I mezzi di Excel processore tabella di decisione

In primo luogo è necessario creare una tabella di dati in ingresso. E 'come segue:

Avanti:

  • cabina telefonica "l'analisi dei dati";
  • sezione selezionata "regressione";
  • la finestra "Input intervallo Y» somministrato gamma valori di variabili dipendenti dalla colonna G;
  • fare clic sull'icona con una freccia rossa alla destra della finestra "Input intervallo X» ed isolato su una gamma di lastre di tutti i valori della colonna B, C, D, F.

Segnare il punto "Nuovo foglio di lavoro" e cliccare su "Ok".

Ottenere un'analisi di regressione per questo compito.

I risultati dello studio e le conclusioni

"Collect" arrotondato dai dati presentati sopra sull'equazione processore Excel regressione tabella trasferimenti:

SD = 0.103 * SOF + 0.541 * VO – 0031 * VK + 0.405 * VD + 0.691 * VZP – 265.844.

In più usuale forma matematica può essere scritto come:

y = 0,103 * x1 + 0541 * x2 – 0,031 * x3 + 0,405 * x4 + 0691 * x5 – 265.844

I dati per «MMM» JSC presentati nella tabella che segue:

SOF, USD

VO, USD

VK, USD

VD, USD

VZP, USD

JV, USD

102.5

535,5

45.2

41.5

21.55

64.72

sostituendoli nell'equazione di regressione, ottenuto una cifra di 64.72 milioni di dollari. Ciò significa che le azioni della JSC «MMM» non devono comprare, perché il loro costo è piuttosto caro a 70 milioni di dollari.

Come si può vedere, l'uso del foglio di calcolo "Excel" e l'equazione di regressione permesso di prendere una decisione informata in merito all'opportunità di transazione molto specifici.

Ora si sa che cosa una regressione. Esempi di eccellere, di cui sopra, vi aiuterà a risolvere i problemi pratici di econometria.