Determina il coefficiente di determinazione di questa equazione in excel. Equazione di regressione come fare in Excel

MS Excel consente di eseguire la maggior parte del lavoro molto rapidamente durante la costruzione di un'equazione di regressione lineare. È importante capire come interpretare i risultati ottenuti.

Per funzionare è necessario un componente aggiuntivo Pacchetto di analisi da inserire nella voce di menu Servizio \ Componenti aggiuntivi

In Excel 2007, per abilitare il pacchetto di analisi, è necessario fare clic su vai a bloccare Opzioni di Excel facendo clic sul pulsante nell'angolo in alto a sinistra, quindi sul pulsante " Opzioni di Excel"In fondo alla finestra:

Per costruire un modello di regressione, seleziona l'elemento Servizio \ Analisi dati \ Regressione... (In Excel 2007, questa modalità è nella casella Dati / Analisi dei dati / Regressione). Apparirà una finestra di dialogo che deve essere completata:

1) Intervallo di ingresso Y contiene un collegamento alle celle che contengono i valori della caratteristica risultante sì... I valori devono trovarsi in una colonna;

2) Intervallo di immissione X¾ contiene un riferimento alle celle che contengono i valori dei fattori. I valori devono essere in colonne;

3) Segno tag impostare se le prime celle contengono testo esplicativo (firme dati);

4) Livello di affidabilitྠQuesto è il livello di confidenza, che si presume sia 95% per impostazione predefinita. Se questo valore non ti soddisfa, devi abilitare questa funzione e inserire il valore richiesto;

5) Segno Zero costante si accende se è necessario costruire un'equazione in cui è presente una variabile libera;

6) Opzioni di uscita determinare dove posizionare i risultati. Per impostazione predefinita, crea la modalità Nuovo foglio di lavoro;

7) Blocco Avanzi permette di abilitare il prelievo dei residui e la costruzione dei loro grafici.

Di conseguenza, vengono visualizzate informazioni contenenti tutte le informazioni necessarie e raggruppate in tre blocchi: Statistiche di regressione, ANOVA, Prelevare il resto... Consideriamoli in modo più dettagliato.

1. Statistiche di regressione:

multiplo Rè definito dalla formula ( Coefficiente di correlazione di Pearson);

R (coefficiente di determinazione);

Normalizzato R-square è calcolato dalla formula (usato per regressione multipla);

Errore standard S calcolato dalla formula ;

Le osservazioni sono la quantità di dati n.

2. ANOVA, linea Regressione:

Parametro dfè uguale a m(numero di insiemi di fattori X);

Parametro SSè determinato dalla formula;

Parametro SMè determinato dalla formula;

Statistiche Fè determinato dalla formula;

Significato F... Se il numero ricevuto supera, l'ipotesi viene accettata (non esiste una relazione lineare), altrimenti l'ipotesi viene accettata (esiste una relazione lineare).

3. ANOVA, linea Resto:

Parametro dfè uguale;

Parametro SSè definito dalla formula ;

Parametro SMè definito dalla formula.

4. ANOVA, linea Totale contiene la somma delle prime due colonne.

5. ANOVA, linea Intersezione a Y contiene il valore del coefficiente, errore standard e T-statistiche.

P-value è il valore dei livelli di significatività corrispondenti al calcolato T-statistici. Determinato dalla funzione DISTRIB.T ( T-statistiche; ). Se P-value supera, allora la variabile corrispondente è statisticamente non significativa e può essere esclusa dal modello.

In basso 95% e Superiore 95% questi sono i limiti inferiore e superiore degli intervalli di confidenza al 95% per i coefficienti dell'equazione di regressione lineare teorica. Se nel blocco di immissione dati il ​​livello di confidenza è stato lasciato per impostazione predefinita, allora le ultime due colonne duplicheranno le precedenti. Se l'utente ha immesso il proprio livello di confidenza, le ultime due colonne contengono i limiti inferiore e superiore per il livello di confidenza specificato.

6. ANOVA, le righe contengono i valori dei coefficienti, errori standard, T-statistico, P-valori e intervalli di confidenza per quelli corrispondenti.

7. Blocca Prelevare il resto contiene i valori del predetto sì(nella nostra notazione lo è) e i resti.

Questo è il modo più comune per mostrare la dipendenza di una variabile dalle altre, ad esempio come dipende livello del PIL sul valore investimento straniero o da tasso di credito della Banca nazionale o da prezzi per le principali risorse energetiche.

La modellazione consente di mostrare l'entità di questa dipendenza (coefficienti), grazie alla quale è possibile effettuare una previsione diretta ed eseguire una pianificazione basata su tali previsioni. Inoltre, basandosi sull'analisi di regressione, è possibile prendere decisioni gestionali volte a stimolare le cause prioritarie che influenzano il risultato finale, il modello stesso aiuterà a evidenziare questi fattori prioritari.

Vista generale del modello di regressione lineare:

Y = a 0 + a 1 x 1 + ... + a k x k

dove un - parametri (coefficienti) di regressione, X - fattori influenzanti, K - il numero di fattori nel modello.

Dati iniziali

Tra i dati iniziali, abbiamo bisogno di un certo set di dati, che sarebbero diversi valori sequenziali o interconnessi del parametro finale Y (ad esempio PIL) e lo stesso numero di valori dell'indicatore, la cui influenza stiamo studiando (per esempio, investimenti esteri).

La figura sopra mostra una tabella con questi primissimi dati, Y è l'indicatore della popolazione economicamente attiva, e il numero di imprese, l'importo degli investimenti in capitale e il reddito della popolazione sono fattori che influenzano, cioè Xs.

Dalla figura si può anche trarre una conclusione errata che la simulazione può riguardare solo serie temporali, cioè serie di momenti registrate sequenzialmente nel tempo, ma non è così, con lo stesso successo è possibile simulare nel contesto di la struttura, ad esempio, i valori indicati in tabella possono essere scomposti non per anni, ma per regioni.

Per costruire modelli lineari adeguati, è auspicabile che i dati iniziali non abbiano forti cali o cadute, in questi casi è consigliabile eseguire lo smoothing, ma di smoothing parleremo la prossima volta.

Pacchetto di analisi

I parametri del modello di regressione lineare possono anche essere calcolati manualmente utilizzando il metodo dei minimi quadrati (OLS), ma questo richiede molto tempo. Un po 'più veloce può essere calcolato utilizzando lo stesso metodo utilizzando le formule in Excel, dove il programma eseguirà i calcoli da solo, ma devi comunque inserire le formule manualmente.

Excel ha un componente aggiuntivo Pacchetto di analisi, che è uno strumento piuttosto potente per aiutare l'analista. Questo toolkit, tra l'altro, è in grado di calcolare i parametri di regressione, secondo lo stesso OLS, in pochi clic, infatti, verrà discusso ulteriormente come utilizzare questo strumento.

Attivazione del pacchetto di analisi

Per impostazione predefinita, questo componente aggiuntivo è disabilitato e non lo troverai nel menu delle schede, quindi diamo un'occhiata passo passo a come attivarlo.

In Excel, in alto a sinistra, attiva la scheda File, nel menu che si apre, cerca la voce Parametri e cliccaci sopra.

Nella finestra che si apre, a sinistra, cerchiamo l'oggetto Componenti aggiuntivi e attivalo, in questa scheda in basso ci sarà un elenco a tendina di controlli, dove di default sarà scritto Componenti aggiuntivi di Excel, a destra dell'elenco a discesa ci sarà un pulsante Vai a e devi fare clic su di esso.

Una finestra pop-up ti offrirà di selezionare i componenti aggiuntivi disponibili, in essa devi mettere un segno di spunta davanti a Pacchetto di analisi e allo stesso tempo, per ogni evenienza, Trovare una soluzione(anch'essa utile), quindi confermare la selezione cliccando sul pulsante ok.

Istruzioni per trovare i parametri di regressione lineare utilizzando il pacchetto di analisi

Dopo aver attivato il componente aggiuntivo Pacchetto di analisi, sarà sempre disponibile nella scheda del menu principale Dati sotto il link Analisi dei dati

Nella finestra attiva dello strumento Analisi dei dati dall'elenco delle possibilità che cerchiamo e scegliamo Regressione

Successivamente, si aprirà una finestra per l'impostazione e la selezione dei dati iniziali per il calcolo dei parametri del modello di regressione. Qui è necessario specificare gli intervalli dei dati iniziali, ovvero il parametro descritto (Y) e i fattori che lo influenzano (X), come nella figura seguente, il resto dei parametri, in linea di principio, è opzionale per l'impostazione.

Dopo aver selezionato i dati iniziali e fatto clic sul pulsante OK, Excel visualizza i calcoli su un nuovo foglio della cartella di lavoro attiva (se non diversamente specificato nelle impostazioni), questi calcoli sono i seguenti:

Ho compilato le celle chiave in giallo, vanno fatte attenzione prima di tutto, anche gli altri parametri sono importanti, ma la loro analisi dettagliata richiede forse un post a parte.

Così, 0,865 - esso R 2- il coefficiente di determinazione, che mostra che l'86,5% dei parametri calcolati del modello, cioè il modello stesso, spiega la dipendenza e i cambiamenti nel parametro studiato - sì dai fattori indagati - xs... Se esagerato, allora questo è un indicatore della qualità del modello e più alto è meglio è. È chiaro che non può essere maggiore di 1 ed è considerato buono quando R 2 è maggiore di 0,8 e se inferiore a 0,5, la ragionevolezza di un tale modello può essere tranquillamente messa in discussione.

Ora passiamo a coefficienti del modello:
2079,85 - esso uno 0- un coefficiente che mostri quale sarà Y se tutti i fattori utilizzati nel modello sono uguali a 0, si assume che questa sia una dipendenza da altri fattori non descritti nel modello;
-0,0056 - un 1- un coefficiente che mostra l'importanza dell'influenza del fattore x 1 su Y, ovvero il numero di imprese all'interno di questo modello influenza l'indicatore della popolazione economicamente attiva con un peso di soli -0,0056 (un grado di influenza piuttosto piccolo ). Il segno meno mostra che questa influenza è negativa, cioè più imprese, meno popolazione economicamente attiva, per quanto paradossale nel suo significato;
-0,0026 - un 2- il coefficiente dell'influenza del volume degli investimenti in capitale sulla dimensione della popolazione economicamente attiva, secondo il modello, anche questa influenza è negativa;
0,0028 - un 3- il coefficiente dell'influenza del reddito della popolazione sulla dimensione della popolazione economicamente attiva, qui l'influenza è positiva, cioè, secondo il modello, un aumento del reddito contribuirà ad aumentare la dimensione della popolazione economicamente attiva .

Raccogliamo i coefficienti calcolati nel modello:

Y = 2079,85 - 0,0056x 1 - 0,0026x 2 + 0,0028x 3

In realtà, questo è il modello di regressione lineare, che assomiglia esattamente a questo per i dati iniziali utilizzati nell'esempio.

Valori calcolati e previsioni del modello

Come abbiamo discusso sopra, il modello è costruito non solo per mostrare l'entità delle dipendenze del parametro studiato dai fattori di influenza, ma anche in modo che, conoscendo questi fattori di influenza, sia possibile fare una previsione. È abbastanza semplice fare questa previsione, è sufficiente sostituire i valori dei fattori di influenza al posto delle x corrispondenti nell'equazione del modello risultante. Nella figura seguente, questi calcoli vengono eseguiti in Excel in una colonna separata.

I valori effettivi (quelli che si sono verificati nella realtà) e i valori calcolati per il modello nella stessa figura vengono visualizzati sotto forma di grafici per mostrare la differenza, e quindi l'errore del modello.

Ripeto ancora una volta, per fare una previsione secondo il modello, è necessario che ci siano fattori di influenza noti, e se si tratta di una serie storica e, di conseguenza, di una previsione per il futuro, ad esempio, per l'anno o il mese successivo, quindi è tutt'altro che sempre possibile scoprire quali fattori influenzeranno in questo stesso futuro. In tali casi, è anche necessario fare una previsione per i fattori di influenza, il più delle volte ciò viene fatto utilizzando un modello autoregressivo - un modello in cui l'oggetto investigato stesso e il tempo sono i fattori che influenzano, cioè la dipendenza dell'indicatore su ciò che era in passato è modellato.

Considereremo come costruire un modello autoregressivo nel prossimo articolo, ma supponiamo ora che quali saranno i valori dei fattori di influenza nel periodo futuro (nell'esempio del 2008) che conosciamo, sostituendo questi valori nel calcoli, otterremo la nostra previsione per il 2008.

L'analisi di regressione è uno dei metodi più richiesti di ricerca statistica. Può essere utilizzato per stabilire il grado di influenza delle variabili indipendenti sulla variabile dipendente. Nella funzionalità di Microsoft Excel, ci sono strumenti progettati per questo tipo di analisi. Vediamo cosa sono e come utilizzarli.

Collegamento del pacchetto di analisi

Ma, per poter utilizzare la funzione che permette di eseguire l'analisi di regressione, è necessario prima di tutto attivare il Pacchetto Analisi. Solo allora gli strumenti necessari per questa procedura appariranno sulla barra multifunzione di Excel.

1. Passa alla scheda "File".
2. Vai alla sezione "Parametri".
3. Si apre la finestra Opzioni di Excel. Vai alla sottosezione "Componenti aggiuntivi".
4. Nella parte inferiore della finestra che si apre, sposta l'interruttore nel blocco "Controllo" nella posizione "Componenti aggiuntivi di Excel", se si trova in una posizione diversa. Fare clic sul pulsante "Vai".
5. Si apre la finestra dei componenti aggiuntivi di Excel disponibili. Mettiamo un segno di spunta accanto alla voce "Pacchetto analisi". Fare clic sul pulsante "OK".

Ora, quando andiamo alla scheda "Dati", sulla barra multifunzione nella casella degli strumenti "Analisi" vedremo un nuovo pulsante - "Analisi dei dati".

Tipi di analisi di regressione

Esistono diversi tipi di regressioni:

• parabolico;
• potere-legge;
• logaritmico;
• esponenziale;
• indicativo;
• iperbolico;
• regressione lineare.

Parleremo più in dettaglio delle prestazioni dell'ultimo tipo di analisi di regressione in Excel.

Regressione lineare in Excel

Di seguito, ad esempio, c'è una tabella che mostra la temperatura media giornaliera dell'aria esterna e il numero di acquirenti del negozio per il giorno lavorativo corrispondente. Scopriamo con l'aiuto dell'analisi di regressione esattamente come le condizioni meteorologiche sotto forma di temperatura dell'aria possono influenzare la frequentazione della presa.

L'equazione di regressione lineare generale ha il seguente aspetto: Y = a0 + a1x1 +… + akhk. In questa formula, Y indica la variabile, l'influenza dei fattori su cui stiamo cercando di studiare. Nel nostro caso, questo è il numero di acquirenti. Il valore x rappresenta i vari fattori che influenzano la variabile. I parametri a sono i coefficienti di regressione. Cioè, sono loro che determinano il significato di questo o quel fattore. L'indice k indica il numero totale di questi stessi fattori.

Analisi dei risultati dell'analisi

I risultati dell'analisi di regressione vengono visualizzati sotto forma di tabella nel luogo specificato nelle impostazioni.

Uno degli indicatori principali è l'R-quadrato. Indica la qualità del modello. Nel nostro caso, questo rapporto è 0,705 o circa il 70,5%. Questo è un livello di qualità accettabile. La dipendenza inferiore a 0,5 è cattiva.

Un altro indicatore importante si trova nella cella all'intersezione della linea "Intersezione a Y" e la colonna "Coefficienti". Indica quale valore avrà Y, e nel nostro caso è il numero di acquirenti, con tutti gli altri fattori pari a zero. In questa tabella, questo valore è 58,04.

Il valore all'intersezione delle colonne "Variabile X1" e "Coefficienti" mostra il livello di dipendenza di Y da X. Nel nostro caso, è il livello di dipendenza del numero di clienti del negozio dalla temperatura. Un rapporto di 1,31 è considerato un indicatore di impatto piuttosto elevato.

Come puoi vedere, è abbastanza facile creare una tabella di analisi di regressione usando Microsoft Excel. Ma solo una persona formata può lavorare con i dati ottenuti all'uscita e comprenderne l'essenza.

Siamo lieti di essere stati in grado di aiutarti a risolvere il problema.

Fai la tua domanda nei commenti, descrivendo in dettaglio l'essenza del problema. I nostri esperti cercheranno di rispondere il più rapidamente possibile.

Questo articolo ti ha aiutato?

La regressione lineare ci permette di descrivere la retta che meglio corrisponde a una serie di coppie ordinate (x, y). L'equazione per una linea retta, nota come equazione lineare, è mostrata di seguito:

è il valore atteso di y per un dato valore di x,

x è la variabile indipendente,

a - segmento sull'asse y per una linea retta,

b - pendenza di una linea retta.

La figura seguente illustra graficamente questo concetto:

L'immagine sopra mostra la linea descritta dall'equazione ŷ = 2 + 0,5x. Il segmento sull'asse y è il punto di intersezione della linea con l'asse y; nel nostro caso a = 2. La pendenza della linea, b, il rapporto tra l'altezza della linea e la lunghezza della linea, ha un valore di 0,5. Una pendenza positiva significa che la linea sta salendo da sinistra a destra. Se b = 0, la linea è orizzontale, il che significa che non esiste alcuna relazione tra le variabili dipendenti e indipendenti. In altre parole, la modifica del valore x non influisce sul valore y.

e y sono spesso confusi. Il grafico mostra 6 coppie ordinate di punti e una linea secondo questa equazione

Questa figura mostra il punto corrispondente alla coppia ordinata x = 2 e y = 4. Si noti che il valore atteso di y secondo la retta in X= 2 è . Possiamo confermarlo con la seguente equazione:

ŷ = 2 + 0,5x = 2 +0,5 (2) = 3.

Il valore y è il punto effettivo e il valore è il valore y previsto utilizzando un'equazione lineare per un dato valore x.

Il prossimo passo è determinare l'equazione lineare che più si avvicina all'insieme delle coppie ordinate, ne abbiamo parlato nell'articolo precedente, dove abbiamo determinato la forma dell'equazione usando il metodo dei minimi quadrati.

Utilizzo di Excel per definire la regressione lineare

Per utilizzare lo strumento di analisi di regressione integrato in Excel, è necessario attivare il componente aggiuntivo Pacchetto di analisi... Lo trovi cliccando sulla scheda File -> Opzioni(2007+), nella finestra di dialogo che appare ParametriEccellere vai alla scheda Componenti aggiuntivi. in campo Controllo scegliere Componenti aggiuntiviEccellere e clicca Andare. Nella finestra che appare, metti un segno di spunta di fronte Pacchetto di analisi, premiamo OK.

Nella scheda Dati in gruppo Analisi apparirà un nuovo pulsante Analisi dei dati.

Per dimostrare come funziona il componente aggiuntivo, utilizziamo i dati dell'articolo precedente, in cui un ragazzo e una ragazza condividono un tavolo in bagno. Inserisci i dati per il nostro esempio di vasca da bagno nelle colonne A e B della lavagna vuota.

Vai alla scheda Dati, in gruppo Analisi clic Analisi dei dati. Nella finestra che appare Analisi dei dati scegliere Regressione come mostrato e fare clic su OK.

Imposta i parametri di regressione richiesti nella finestra Regressione, come mostrato nell'immagine:

Clic OK. La figura seguente mostra i risultati ottenuti:

Questi risultati sono coerenti con quelli che abbiamo ottenuto per auto-calcolo nel precedente articolo.

L'analisi di regressione è un metodo di ricerca statistica che permette di mostrare la dipendenza di un parametro da una o più variabili indipendenti. Nell'era pre-informatica, la sua applicazione era piuttosto difficile, soprattutto quando si trattava di grandi quantità di dati. Oggi, dopo aver imparato a costruire una regressione in Excel, puoi risolvere problemi statistici complessi in un paio di minuti. Di seguito sono riportati esempi specifici dal campo dell'economia.

Tipi di regressione

Il concetto stesso fu introdotto in matematica da Francis Galton nel 1886. La regressione avviene:

• lineare;
• parabolico;
• potere-legge;
• esponenziale;
• iperbolico;
• indicativo;
• logaritmico.

Esempio 1

Consideriamo il problema di determinare la dipendenza del numero di dipendenti che hanno lasciato il lavoro dal salario medio in 6 imprese industriali.

Compito. Sei imprese hanno analizzato lo stipendio medio mensile e il numero di dipendenti che si sono dimessi volontariamente. In forma tabellare abbiamo:

Per il problema della determinazione della dipendenza del numero di dipendenti cessati dal salario medio in 6 imprese, il modello di regressione ha la forma dell'equazione Y = a0 + a1 × 1 +… + akxk, dove xi sono variabili di influenza, ai sono coefficienti di regressione e k è il numero di fattori.

Per questa attività, Y è l'indicatore dei dipendenti che hanno lasciato e il fattore che influenza è lo stipendio, che indichiamo con X.

Utilizzo delle capacità dell'elaboratore di tabelle Excel

L'analisi di regressione in Excel deve essere preceduta dall'applicazione di funzioni integrate ai dati tabulari esistenti. Tuttavia, per questi scopi è meglio utilizzare l'utilissimo componente aggiuntivo "Pacchetto di analisi". Per attivarlo è necessario:

• dalla scheda "File" vai alla sezione "Parametri";
• nella finestra che si apre, seleziona la riga "Componenti aggiuntivi";
• cliccare sul pulsante "Vai" che si trova in basso, a destra della riga "Controllo";
• metti un segno di spunta accanto al nome "Pacchetto di analisi" e conferma le tue azioni facendo clic su "OK".

Se tutto è stato eseguito correttamente, il pulsante richiesto apparirà sul lato destro della scheda "Dati", situata sopra il foglio di lavoro "Excel".

Regressione lineare in Excel

Ora che abbiamo a portata di mano tutti gli strumenti virtuali necessari per eseguire calcoli econometrici, possiamo iniziare a risolvere il nostro problema. Per questo:

• cliccare sul pulsante "Analisi Dati";
• nella finestra che si apre, cliccare sul pulsante "Regressione";
• nella scheda che appare, inserisci l'intervallo di valori per Y (il numero di dipendenti che hanno lasciato) e per X (i loro stipendi);
• confermiamo le nostre azioni premendo il pulsante "Ok".

Di conseguenza, il programma compilerà automaticamente il nuovo foglio dell'elaboratore di fogli di calcolo con i dati dell'analisi di regressione. Nota! Excel ha la capacità di definire in modo indipendente la posizione che preferisci per questo scopo. Ad esempio, potrebbe essere lo stesso foglio che contiene i valori Y e X o anche una nuova cartella di lavoro progettata specificamente per archiviare questo tipo di dati.

Analisi dei risultati di regressione per R-Square

In Excel, i dati ottenuti nel corso dell'elaborazione dei dati dell'esempio in questione sono i seguenti:

Prima di tutto, dovresti prestare attenzione al valore dell'R-quadrato. Rappresenta il coefficiente di determinazione. In questo esempio, R-quadrato = 0,755 (75,5%), ovvero i parametri calcolati del modello spiegano la relazione tra i parametri considerati del 75,5%. Più alto è il valore del coefficiente di determinazione, più il modello scelto è considerato più applicabile per un compito specifico. Si ritiene che descriva correttamente la situazione reale quando il valore R al quadrato è superiore a 0,8. Se l'R-quadrato è tcr, l'ipotesi dell'insignificanza del termine libero dell'equazione lineare viene respinta.

Nel problema considerato per un termine libero utilizzando gli strumenti di Excel, si è ottenuto che t = 169.20903 e p = 2.89E-12, ovvero abbiamo una probabilità nulla che l'ipotesi corretta sull'insignificanza del termine libero sia respinto. Per il coefficiente allo sconosciuto t = 5,79405 e p = 0,001158. In altre parole, la probabilità che venga respinta l'ipotesi corretta circa l'insignificanza del coefficiente con l'incognita è dello 0,12%.

Pertanto, si può sostenere che l'equazione di regressione lineare risultante è adeguata.

Il problema dell'opportunità di acquistare un blocco di azioni

La regressione multipla in Excel viene eseguita utilizzando lo stesso strumento di analisi dei dati. Consideriamo un problema specifico applicato.

La direzione della società "NNN" deve decidere sull'opportunità di acquistare una partecipazione del 20% in JSC "MMM". Il costo del pacchetto (JV) è di 70 milioni di dollari USA. Gli specialisti della NNN hanno raccolto dati su transazioni simili. Si è deciso di valutare il valore del blocco di azioni mediante tali parametri, espressi in milioni di dollari USA, quali:

• conti da pagare (VK);
• il volume del fatturato annuo (VO);
• crediti (VD);
• il costo delle immobilizzazioni (SOF).

Inoltre, il parametro è l'arretrato salariale dell'impresa (V3 P) in migliaia di dollari USA.

Soluzione foglio di calcolo Excel

Prima di tutto, devi creare una tabella di dati iniziali. Sembra così:

• richiamare la finestra "Analisi dati";
• selezionare la sezione "Regressione";
• l'intervallo di valori delle variabili dipendenti dalla colonna G viene inserito nella casella "Intervallo di input Y";
• fare clic sull'icona con una freccia rossa a destra della finestra "Intervallo di input X" e selezionare sul foglio l'intervallo di tutti i valori dalle colonne B, C, D, F.

Seleziona la voce "Nuovo foglio di lavoro" e fai clic su "Ok".

Ottieni un'analisi di regressione per una determinata attività.

Studio dei risultati e conclusioni

"Raccogliamo" l'equazione di regressione dai dati arrotondati presentati sopra sul foglio di calcolo Excel:

SP = 0,103 * SOF + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265.844.

In una forma matematica più familiare, può essere scritto come:

y = 0,103 * x1 + 0,541 * x2 - 0,031 * x3 + 0,405 * x4 + 0,691 * x5 - 265.844

I dati per JSC "MMM" sono presentati nella tabella:

Sostituendoli nell'equazione di regressione, la cifra è di 64,72 milioni di dollari USA. Ciò significa che le azioni di JSC "MMM" non dovrebbero essere acquistate, poiché il loro valore di 70 milioni di dollari USA è piuttosto sopravvalutato.

Come puoi vedere, l'uso dell'elaboratore di fogli di calcolo Excel e l'equazione di regressione hanno permesso di prendere una decisione informata sull'opportunità di una transazione molto specifica.

Ora sai cos'è la regressione. Gli esempi in Excel discussi sopra ti aiuteranno a risolvere problemi pratici nel campo dell'econometria.

MS Excel consente di eseguire la maggior parte del lavoro molto rapidamente durante la costruzione di un'equazione di regressione lineare. È importante capire come interpretare i risultati ottenuti. Per costruire un modello di regressione, selezionare la voce Servizio \ Analisi dati \ Regressione (in Excel 2007 questa modalità si trova nella sezione Dati / Analisi dati / Regressione). Quindi copiare i risultati ottenuti nel blocco per l'analisi.

Dati iniziali:

Risultati dell'analisi

Includi nel rapporto
Calcolo dei parametri dell'equazione di regressione
Materiale teorico
Equazione di regressione su scala standard
Coefficiente di correlazione multipla (indice di correlazione multipla)
Coefficienti parziali di elasticità
Valutazione comparativa dell'influenza dei fattori analizzati sull'indicatore effettivo (d - coefficienti di determinazione separata)

Verifica della qualità dell'equazione di regressione costruita
Significato dei coefficienti di regressione b i (statistica t. Test di Student)
Significato dell'equazione nel suo insieme (statistica F. Criterio di Fisher). Coefficiente di determinazione
F-test privati

Livello di significatività 0.005 0.01 0.025 0.05 0.1 0.25 0.4

Analisi di regressione e correlazione - metodi di ricerca statistica. Questi sono i modi più comuni per mostrare come un parametro dipenda da una o più variabili indipendenti.

Di seguito, utilizzando esempi pratici specifici, prenderemo in considerazione queste due analisi molto popolari tra gli economisti. E anche daremo un esempio di come ottenere i risultati quando vengono combinati.

Analisi di regressione in Excel

Mostra l'effetto di alcuni valori (indipendente, indipendente) sulla variabile dipendente. Ad esempio, come il numero della popolazione economicamente attiva dipende dal numero di imprese, dalle dimensioni dei salari e da altri parametri. Oppure: come influiscono sul livello del PIL gli investimenti esteri, i prezzi dell'energia, ecc.

Il risultato dell'analisi consente di stabilire delle priorità. E sulla base dei fattori principali, prevedere, pianificare lo sviluppo delle aree prioritarie, prendere decisioni gestionali.

La regressione avviene:

• lineare (y = a + bx);
• parabolico (y = a + bx + cx 2);
• esponenziale (y = a * exp (bx));
• potenza (y = a * x ^ b);
• iperbolico (y = b / x + a);
• logaritmico (y = b * 1n (x) + a);
• esponenziale (y = a * b ^ x).

Diamo un'occhiata a un esempio di costruzione di un modello di regressione in Excel e interpretazione dei risultati. Prendiamo un tipo di regressione lineare.

Compito. In 6 imprese è stato analizzato lo stipendio medio mensile e il numero di dipendenti che hanno cessato l'attività. È necessario determinare la dipendenza del numero di dipendenti che hanno lasciato lo stipendio medio.

Il modello di regressione lineare è il seguente:

Y = a 0 + a 1 x 1 + ... + a k x k.

Dove a - coefficienti di regressione, x - variabili che influenzano, k - numero di fattori.

Nel nostro esempio, Y è l'indicatore dei dipendenti che hanno smesso. Il fattore che influenza è il salario (x).

Excel dispone di funzioni integrate che è possibile utilizzare per calcolare i parametri di un modello di regressione lineare. Ma il componente aggiuntivo Pacchetto di analisi lo farà più velocemente.

Attiviamo un potente strumento analitico:

Dopo l'attivazione, il componente aggiuntivo sarà disponibile nella scheda "Dati".

Passiamo ora direttamente all'analisi di regressione.

Prima di tutto, presta attenzione all'R-quadrato e ai coefficienti.

R-quadrato è il coefficiente di determinazione. Nel nostro esempio - 0,755 o 75,5%. Ciò significa che i parametri calcolati del modello spiegano la relazione tra i parametri studiati del 75,5%. Maggiore è il coefficiente di determinazione, migliore è il modello. Buono - sopra 0,8. Cattivo - meno di 0,5 (tale analisi difficilmente può essere considerata ragionevole). Nel nostro esempio - "non male".

Il coefficiente 64.1428 mostra quale sarà Y se tutte le variabili nel modello in esame sono uguali a 0. Cioè, altri fattori che non sono descritti nel modello influenzano anche il valore del parametro analizzato.

Il coefficiente -0,16285 mostra il peso della variabile X su Y. Cioè, lo stipendio medio mensile all'interno di questo modello influenza il numero di persone che smettono con un peso di -0,16285 (questo è un piccolo grado di influenza). Il segno “-” indica un impatto negativo: più è alto lo stipendio, meno rinunciano. Il che è giusto.



Analisi della correlazione in Excel

L'analisi della correlazione aiuta a stabilire se esiste una relazione tra gli indicatori in uno o due campioni. Ad esempio, tra il tempo di funzionamento della macchina e il costo delle riparazioni, il prezzo dell'attrezzatura e la durata del funzionamento, l'altezza e il peso dei bambini, ecc.

Se c'è una relazione, allora se un aumento di un parametro porta ad un aumento (correlazione positiva) o una diminuzione (negativa) nell'altro. L'analisi della correlazione aiuta l'analista a determinare se il valore di un indicatore può prevedere il possibile valore di un altro.

Il coefficiente di correlazione è indicato con r. Varia da +1 a -1. La classificazione delle correlazioni per le diverse aree sarà diversa. Quando il coefficiente è 0, non esiste una relazione lineare tra i campioni.

Diamo un'occhiata a come utilizzare gli strumenti di Excel per trovare il coefficiente di correlazione.

Per trovare coefficienti accoppiati, viene utilizzata la funzione CORRELAZIONE.

Obiettivo: Determinare se esiste una relazione tra il tempo di funzionamento del tornio e il costo della sua manutenzione.

Mettiamo il cursore in qualsiasi cella e premiamo il pulsante fx.

1. Nella categoria "Statistica" selezionare la funzione CORRELAZIONE.
2. Argomento dell'array 1 - il primo intervallo di valori - tempo di funzionamento della macchina: A2: A14.
3. Argomento dell'array 2 - secondo intervallo di valori - costo della riparazione: B2: B14. Fare clic su OK.

Per determinare il tipo di connessione, è necessario guardare il numero assoluto del coefficiente (ogni campo di attività ha la sua scala).

Per l'analisi di correlazione di più parametri (più di 2), è più conveniente utilizzare Analisi dati (componente aggiuntivo Pacchetto di analisi). Nell'elenco, è necessario selezionare una correlazione e designare un array. Qualunque cosa.

I coefficienti ottenuti verranno visualizzati nella matrice di correlazione. Come questo:

Analisi di correlazione-regressione

In pratica, queste due tecniche sono spesso usate insieme.

Esempio:

Ora sono visibili anche i dati di regressione.