Risolvi un sistema di equazioni utilizzando il metodo dei minimi quadrati in Excel. Applicazione del metodo dei minimi quadrati in Excel. Qualche parola sulla correttezza dei dati iniziali utilizzati per la previsione

Metodo minimi quadratiè una procedura matematica per costruire un'equazione lineare che si avvicina il più possibile a un insieme di due serie di numeri. Lo scopo dell'utilizzo di questo metodo è ridurre al minimo l'errore quadrato totale. Excel dispone di strumenti che possono aiutarti ad applicare questo metodo ai tuoi calcoli. Scopriamo come è fatto.

· Utilizzando il metodo in Excel

o Abilitazione del componente aggiuntivo "Ricerca soluzioni".

o Condizioni problematiche

o Soluzione

Utilizzando il metodo in Excel

Il metodo dei minimi quadrati (LSM) è una descrizione matematica della dipendenza di una variabile da un'altra. Può essere utilizzato per fare previsioni.

Abilitazione del componente aggiuntivo Trova soluzione

Per utilizzare MNC in Excel, è necessario abilitare il componente aggiuntivo "Trovare una soluzione", che è disabilitato per impostazione predefinita.

1. Vai alla scheda "File".

2. Fare clic sul nome della sezione "Opzioni".

3. Nella finestra che si apre, seleziona la sottosezione "Componenti aggiuntivi".

4. Nel blocco "Controllo", che si trova nella parte inferiore della finestra, impostare l'interruttore sulla posizione "Componenti aggiuntivi di Excel"(se ha un valore diverso) e cliccare sul pulsante "Andare...".

5. Si apre una piccola finestra. Mettiamo un segno di spunta accanto al parametro "Trovare una soluzione". Fare clic sul pulsante "OK".

Ora la funzione Trovare una soluzione in Excel è attivato e i relativi strumenti vengono visualizzati sulla barra multifunzione.

Lezione: Trovare una soluzione in Excel

Condizioni del problema

Descriviamo l'applicazione di LSM su esempio specifico. Abbiamo due file di numeri X E sì, la cui sequenza è mostrata nell'immagine sottostante.

Questa dipendenza può essere descritta più accuratamente dalla funzione:

Allo stesso tempo, si sa quando x=0 y anche uguale 0 . Pertanto, questa equazione può essere descritta dalla dipendenza y=nx.

Dobbiamo trovare la somma minima dei quadrati della differenza.

Soluzione

Passiamo alla descrizione dell'applicazione diretta del metodo.

1. A sinistra del primo valore X metti un numero 1 . Questo sarà un valore approssimativo del valore del primo coefficiente N.

2. A destra della colonna sì aggiungi un'altra colonna - nx. Nella prima cella di questa colonna scriviamo la formula per moltiplicare il coefficiente N per cella della prima variabile X. Allo stesso tempo, creiamo il collegamento al campo con il coefficiente assoluto, poiché questo valore non cambierà. Fare clic sul pulsante accedere.

3. Utilizzando l'indicatore di riempimento, copia questa formula nell'intero intervallo della tabella nella colonna sottostante.

4. In una cella separata, calcola la somma delle differenze tra i quadrati dei valori sì E nx. Per fare ciò, fare clic sul pulsante "Funzione Inserisci".

5. All'aperto "Funzione guidata" alla ricerca di una voce "SUMMKVARNA". Selezionalo e premi il pulsante "OK".

6. Si apre la finestra degli argomenti. Nel campo "Matrice_x" sì. Nel campo "Array_y" immettere l'intervallo di celle della colonna nx. Per inserire i valori è sufficiente posizionare il cursore nel campo e selezionare l'intervallo corrispondente sul foglio. Dopo aver inserito, fare clic sul pulsante "OK".

7. Vai alla scheda "Dati". Sulla barra multifunzione nella casella degli strumenti "Analisi" fare clic sul pulsante "Trovare una soluzione".

8. Si apre la finestra dei parametri per questo strumento. Nel campo “Ottimizza la funzione obiettivo” indicare l'indirizzo della cella con la formula "SUMMKVARNA". Nel parametro "Prima" assicurati di impostare l'interruttore sulla posizione "Minimo". Nel campo "Cambio di celle" indicare l'indirizzo con il valore del coefficiente N. Fare clic sul pulsante "Trovare una soluzione".

9. La soluzione verrà visualizzata nella cella del coefficiente N. Questo valore sarà il minimo quadrato della funzione. Se il risultato soddisfa l'utente, fare clic sul pulsante "OK" in una finestra aggiuntiva.

Come puoi vedere, l'applicazione del metodo dei minimi quadrati è una procedura matematica piuttosto complessa. Lo abbiamo mostrato in azione utilizzando un semplice esempio, ma esistono casi molto più complessi. Tuttavia, gli strumenti di Microsoft Excel sono progettati per semplificare il più possibile i calcoli.

http://multitest.semico.ru/mnk.htm

Disposizioni generali

Come meno numero in valore assoluto, migliore è la scelta della retta (2). Come caratteristica dell'accuratezza della selezione di una linea retta (2), possiamo prendere la somma dei quadrati

Le condizioni minime per S saranno

	(6)
	(7)

Le equazioni (6) e (7) possono essere scritte come segue:

	(8)
	(9)

Dalle equazioni (8) e (9) è facile ricavare a e b dai valori sperimentali di xi e y i. La linea (2), definita dalle equazioni (8) e (9), è chiamata linea ottenuta con il metodo dei minimi quadrati (questo nome sottolinea che la somma dei quadrati S ha un minimo). Le equazioni (8) e (9), da cui si determina la retta (2), sono chiamate equazioni normali.

Puoi indicare un modo semplice e generale per comporre equazioni normali. Utilizzando i punti sperimentali (1) e l'equazione (2), possiamo scrivere un sistema di equazioni per a e b

y1 =asse1+b,
y2 =asse2+b, ...		(10)
y n = ax n + b,

Moltiplichiamo i lati sinistro e destro di ciascuna di queste equazioni per il coefficiente della prima incognita a (cioè per x 1, x 2, ..., x n) e aggiungiamo le equazioni risultanti, ottenendo la prima equazione normale (8) .

Moltiplichiamo i lati sinistro e destro di ciascuna di queste equazioni per il coefficiente della seconda incognita b, cioè per 1 e aggiungiamo le equazioni risultanti, il risultato è la seconda equazione normale (9).

Questo metodo per ottenere le equazioni normali è generale: è adatto, ad esempio, alla funzione

esiste un valore costante e deve essere determinato dai dati sperimentali (1).

Il sistema di equazioni per k può essere scritto:

Trova la retta (2) utilizzando il metodo dei minimi quadrati.

Soluzione. Noi troviamo:

X io =21, y io =46,3, x io 2 =91, x io y io = 179,1.

Scriviamo le equazioni (8) e (9)91a+21b=179,1,

21a+6b=46,3, da qui troviamo
a=0,98 b=4,3.

Che trova la più ampia applicazione in vari campi della scienza e dell'attività pratica. Potrebbe trattarsi di fisica, chimica, biologia, economia, sociologia, psicologia e così via. Per volontà del destino, devo spesso occuparmi dell'economia, e quindi oggi organizzerò per te un viaggio in un paese meraviglioso chiamato Econometria=) ...Come puoi non volerlo?! Si sta molto bene lì, devi solo prendere una decisione! ...Ma quello che probabilmente desideri è imparare a risolvere i problemi metodo dei minimi quadrati. E i lettori particolarmente diligenti impareranno a risolverli non solo in modo accurato, ma anche MOLTO VELOCE ;-) Ma prima esposizione generale del problema+ esempio allegato:

Studiamo gli indicatori in una determinata area tematica che hanno un'espressione quantitativa. Allo stesso tempo, ci sono tutte le ragioni per credere che l'indicatore dipenda dall'indicatore. Questa ipotesi potrebbe essere come ipotesi scientifica e basarsi sul buon senso di base. Lasciamo però da parte la scienza ed esploriamo aree più appetitose, vale a dire i negozi di alimentari. Indichiamo con:

– superficie commerciale di un negozio di alimentari, mq,
– fatturato annuo di un negozio di alimentari, milioni di rubli.

È assolutamente chiaro che maggiore è la superficie del negozio, maggiore sarà nella maggior parte dei casi il suo fatturato.

Supponiamo che dopo aver effettuato osservazioni/esperimenti/calcoli/danze con un tamburello abbiamo a nostra disposizione dei dati numerici:

Con i negozi di alimentari, penso che tutto sia chiaro: - questa è l'area del 1o negozio, - il suo fatturato annuo, - l'area del 2o negozio, - il suo fatturato annuo, ecc. A proposito, non è affatto necessario avere accesso a materiali classificati: è possibile ottenere una valutazione abbastanza accurata del fatturato commerciale mediante statistica matematica. Però non distraiamoci, il corso di spionaggio commerciale è già pagato =)

I dati tabulari possono anche essere scritti sotto forma di punti e rappresentati nella forma familiare Sistema cartesiano .

Rispondiamo ad una domanda importante: Quanti punti sono necessari per uno studio qualitativo?

Piu 'grande e', meglio 'e. Il set minimo accettabile è composto da 5-6 punti. Inoltre, quando la quantità di dati è piccola, i risultati “anomali” non possono essere inclusi nel campione. Quindi, ad esempio, un piccolo negozio d’élite può guadagnare ordini di grandezza più dei “suoi colleghi”, distorcendo così modello generale, che è quello che devi trovare!

Per dirla in modo molto semplice, dobbiamo selezionare una funzione, programma che passa il più vicino possibile ai punti . Questa funzione si chiama approssimativo (approssimazione - approssimazione) O funzione teorica . In generale, qui appare immediatamente un ovvio "concorrente": il polinomio alto grado, il cui grafico passa per TUTTI i punti. Ma questa opzione è complicata e spesso semplicemente errata. (poiché il grafico “si ripete” continuamente e riflette scarsamente la tendenza principale).

Pertanto, la funzione ricercata deve essere abbastanza semplice e allo stesso tempo riflettere adeguatamente la dipendenza. Come puoi immaginare, viene chiamato uno dei metodi per trovare tali funzioni metodo dei minimi quadrati. Innanzitutto, consideriamo la sua essenza in termini generali. Lascia che alcune funzioni approssimino i dati sperimentali:

Come valutare l'accuratezza di questa approssimazione? Calcoliamo anche le differenze (deviazioni) tra i valori sperimentali e funzionali (studiamo il disegno). Il primo pensiero che mi viene in mente è quello di stimare quanto è grande la somma, ma il problema è che le differenze possono essere negative (Per esempio, ) e le deviazioni risultanti da tale somma si annulleranno a vicenda. Pertanto, come stima dell'accuratezza dell'approssimazione, si chiede di prendere la somma moduli deviazioni:

o compresso: (nel caso qualcuno non lo sapesse: – questa è l'icona della somma, e – una variabile ausiliaria “contatore”, che assume valori da 1 a ).

Approssimando punti sperimentali con varie funzioni, otterremo significati diversi e ovviamente, laddove tale importo è inferiore, tale funzione è più accurata.

Un tale metodo esiste e si chiama metodo del modulo minimo. Tuttavia, in pratica è diventato molto più diffuso metodo dei minimi quadrati, in cui eventuali valori negativi vengono eliminati non dal modulo, ma elevando al quadrato le deviazioni:

, dopo di che gli sforzi sono mirati a selezionare una funzione tale che la somma delle deviazioni al quadrato era il più piccolo possibile. In realtà è proprio da qui che deriva il nome del metodo.

E ora torniamo a qualcos'altro punto importante: come notato sopra, la funzione selezionata dovrebbe essere abbastanza semplice, ma esistono anche molte funzioni simili: lineare , iperbolico, esponenziale, logaritmico, quadratico eccetera. E, naturalmente, qui vorrei subito “ridurre il campo di attività”. Quale classe di funzioni dovrei scegliere per la ricerca? Una tecnica primitiva ma efficace:

– Il modo più semplice è rappresentare i punti sul disegno e analizzarne la posizione. Se tendono a correre in linea retta, dovresti cercarli equazione di una retta con valori ottimali e . In altre parole, il compito è trovare TALI coefficienti in modo che la somma delle deviazioni al quadrato sia la più piccola.

Se i punti si trovano, ad esempio, lungo iperbole, allora è ovviamente chiaro che la funzione lineare darà una scarsa approssimazione. In questo caso, stiamo cercando i coefficienti più “favorevoli” per l’equazione dell’iperbole – quelli che danno la somma minima dei quadrati .

Ora notiamo che in entrambi i casi stiamo parlando funzioni di due variabili, i cui argomenti sono parametri di dipendenza cercati:

Ed essenzialmente dobbiamo risolvere un problema standard: trovare funzione minima di due variabili.

Ricordiamo il nostro esempio: supponiamo che i punti “negozio” tendano a essere posizionati in linea retta e ci siano tutte le ragioni per crederlo dipendenza lineare fatturato derivante dagli spazi commerciali. Troviamo TALI coefficienti “a” e “be” tali che la somma delle deviazioni al quadrato era il più piccolo. Tutto è come al solito: prima Derivate parziali del 1° ordine. Secondo regola di linearità Puoi differenziare proprio sotto l'icona della somma:

Se vuoi usare questa informazione per un saggio o un corso - sarò molto grato per il collegamento nell'elenco delle fonti; troverai calcoli così dettagliati in pochi posti:

Creiamo un sistema standard:

Riduciamo ciascuna equazione di “due” e, inoltre, “scomponiamo” le somme:

Nota : analizza in modo indipendente il motivo per cui "a" e "be" possono essere eliminati oltre l'icona della somma. A proposito, formalmente questo può essere fatto con la somma

Riscriviamo il sistema in forma “applicata”:

dopodiché inizia ad emergere l'algoritmo per risolvere il nostro problema:

Conosciamo le coordinate dei punti? Sappiamo. Importi possiamo trovarlo? Facilmente. Facciamo il più semplice sistema di due equazioni lineari in due incognite(“a” e “essere”). Risolviamo il sistema, ad esempio, Il metodo di Cramer, per cui otteniamo un punto stazionario. Controllo condizione sufficiente per un estremo, possiamo verificare che a questo punto la funzione raggiunge esattamente minimo. Il controllo comporta calcoli aggiuntivi e quindi lo lasceremo dietro le quinte (se necessario è possibile visionare la cornice mancante). Traiamo la conclusione finale:

Funzione il modo migliore (almeno rispetto a qualsiasi altra funzione lineare) avvicina i punti sperimentali . In parole povere, il suo grafico passa il più vicino possibile a questi punti. Nella tradizione econometria viene anche chiamata la funzione di approssimazione risultante equazione di regressione lineare accoppiata .

Il problema in esame ha una portata ampia significato pratico. Nella nostra situazione di esempio, l’Eq. ti permette di prevedere quale fatturato commerciale ("Igrek") il negozio avrà l'uno o l'altro valore dell'area di vendita (l'uno o l'altro significato di “x”). Sì, la previsione risultante sarà solo una previsione, ma in molti casi si rivelerà abbastanza accurata.

Analizzerò solo un problema con i numeri "reali", poiché non ci sono difficoltà: tutti i calcoli sono a livello curriculum scolastico 7-8 gradi. Nel 95% dei casi ti verrà chiesto di trovare solo una funzione lineare, ma alla fine dell'articolo mostrerò che non è più difficile trovare le equazioni dell'iperbole ottimale, dell'esponenziale e di alcune altre funzioni.

In effetti, non resta che distribuire i gadget promessi, in modo che tu possa imparare a risolvere tali esempi non solo in modo accurato, ma anche rapido. Studiamo attentamente la norma:

Compito

Come risultato dello studio della relazione tra due indicatori, sono state ottenute le seguenti coppie di numeri:

Utilizzando il metodo dei minimi quadrati, trova la funzione lineare che meglio approssima quella empirica (esperto) dati. Realizza un disegno su cui costruire i punti sperimentali e un grafico della funzione approssimata in un sistema di coordinate cartesiane rettangolari . Trova la somma dei quadrati delle deviazioni tra i valori empirici e teorici. Scopri se la funzionalità sarebbe migliore (dal punto di vista del metodo dei minimi quadrati) avvicinare i punti sperimentali.

Tieni presente che i significati "x" sono naturali, e questo ha un significato significativo caratteristico, di cui parlerò poco dopo; ma ovviamente possono anche essere frazionari. Inoltre, a seconda del contenuto di una particolare attività, sia i valori “X” che quelli “gioco” possono essere completamente o parzialmente negativi. Ebbene, ci è stato assegnato un compito “senza volto” e lo iniziamo soluzione:

Troviamo i coefficienti della funzione ottima come soluzione del sistema:

Per una registrazione più compatta la variabile “contatore” può essere omessa, poiché è già chiaro che la somma viene effettuata da 1 a .

È più conveniente calcolare gli importi richiesti in forma tabellare:

I calcoli possono essere eseguiti su un microcalcolatore, ma è molto meglio utilizzare Excel, sia più veloce che senza errori; guarda un breve video:

Pertanto, otteniamo quanto segue sistema:

Qui puoi moltiplicare la seconda equazione per 3 e sottrarre la seconda dalla prima equazione termine per termine. Ma questa è fortuna: in pratica, i sistemi spesso non sono un dono e in questi casi salvano Il metodo di Cramer:
, il che significa che il sistema ha una soluzione unica.

Controlliamo. Capisco che non vuoi, ma perché saltare gli errori dove non possono assolutamente mancare? Sostituiamo la soluzione trovata nella parte sinistra di ciascuna equazione del sistema:

Si ottengono i membri destri delle equazioni corrispondenti, il che significa che il sistema è risolto correttamente.

Pertanto, la funzione approssimante desiderata: – da tutte le funzioni lineariÈ lei che meglio approssima i dati sperimentali.

A differenza di Dritto dipendenza del fatturato del negozio dalla sua area, la dipendenza riscontrata è inversione (principio “più, meno”), e questo fatto viene immediatamente rivelato dal negativo pendenza. Funzione ci dice che con un aumento di un determinato indicatore di 1 unità, il valore dell'indicatore dipendente diminuisce media di 0,65 unità. Come si suol dire, maggiore è il prezzo del grano saraceno, meno viene venduto.

Per tracciare il grafico della funzione approssimante, troviamo i suoi due valori:

ed esegui il disegno:

La retta costruita si chiama linea di tendenza (vale a dire, una linea di tendenza lineare, ovvero in generale una tendenza non è necessariamente una linea retta). Tutti conoscono l'espressione “essere di tendenza” e penso che questo termine non abbia bisogno di ulteriori commenti.

Calcoliamo la somma delle deviazioni quadrate tra valori empirici e teorici. Geometricamente, questa è la somma dei quadrati delle lunghezze dei segmenti “lampone”. (due dei quali sono così piccoli da non essere nemmeno visibili).

Riassumiamo i calcoli in una tabella:

Anche in questo caso, possono essere eseguiti manualmente; per ogni evenienza, darò un esempio per il primo punto:

ma è molto più efficace farlo nel modo già noto:

Ripetiamo ancora una volta: Qual è il significato del risultato ottenuto? Da tutte le funzioni lineari funzione l'indicatore è il più piccolo, cioè nella sua famiglia è la migliore approssimazione. E qui, a proposito, la domanda finale del problema non è casuale: e se la funzione esponenziale proposta sarebbe meglio avvicinare i punti sperimentali?

Troviamo la corrispondente somma dei quadrati delle deviazioni: per distinguerle, le indicherò con la lettera "epsilon". La tecnica è esattamente la stessa:

E ancora, per ogni evenienza, i calcoli per il 1° punto:

In Excel utilizziamo la funzione standard ESP (la sintassi è disponibile nella Guida di Excel).

Conclusione: , il che significa che la funzione esponenziale approssima i punti sperimentali peggio di una retta .

Ma qui va notato che “peggio” è non significa ancora, che c'è. Ora ho costruito un grafico di questa funzione esponenziale - e passa anche vicino ai punti - tanto che senza ricerca analitica è difficile dire quale funzione sia più precisa.

Ciò conclude la soluzione e torno alla questione dei valori naturali dell'argomento. In vari studi, solitamente economici o sociologici, le “X” naturali vengono utilizzate per numerare mesi, anni o altri intervalli di tempo uguali. Consideriamo, ad esempio, il seguente problema.

Il metodo dei minimi quadrati (OLS) appartiene al campo dell'analisi di regressione. Ha molti usi in quanto consente una rappresentazione approssimativa data funzione altri sono più semplici. L'LSM può essere estremamente utile nell'elaborazione delle osservazioni e viene utilizzato attivamente per stimare alcune quantità sulla base dei risultati di misurazioni di altre contenenti errori casuali. In questo articolo imparerai come implementare i calcoli dei minimi quadrati in Excel.

Esposizione del problema utilizzando un esempio specifico

Supponiamo che ci siano due indicatori X e Y. Inoltre, Y dipende da X. Poiché OLS ci interessa dal punto di vista dell'analisi di regressione (in Excel i suoi metodi sono implementati utilizzando funzioni integrate), dovremmo passare immediatamente a considerare a problema specifico.

Quindi, sia X lo spazio commerciale di un negozio di alimentari, misurato metri quadrati e Y è il fatturato annuo, determinato in milioni di rubli.

È necessario fare una previsione del fatturato (Y) che avrà il negozio se ha questo o quello spazio commerciale. Ovviamente la funzione Y = f (X) è crescente, poiché l'ipermercato vende più beni della bancarella.

Qualche parola sulla correttezza dei dati iniziali utilizzati per la previsione

Supponiamo di avere una tabella creata utilizzando i dati per n negozi.

Secondo statistica matematica, i risultati saranno più o meno corretti se verranno esaminati dati su almeno 5-6 oggetti. Inoltre non è possibile utilizzare risultati “anomali”. In particolare, una piccola boutique d'élite può avere un fatturato molte volte superiore al fatturato dei grandi punti vendita della classe “masmarket”.

L'essenza del metodo

I dati della tabella possono essere rappresentati su un piano cartesiano sotto forma di punti M 1 (x 1, y 1), ... M n (x n, y n). Ora la soluzione del problema si ridurrà alla scelta di una funzione approssimante y = f (x), che abbia un grafico passante il più vicino possibile ai punti M 1, M 2, .. M n.

Naturalmente, è possibile utilizzare un polinomio di alto grado, ma questa opzione non è solo difficile da implementare, ma anche semplicemente errata, poiché non rifletterà la tendenza principale da rilevare. La soluzione più ragionevole è cercare la retta y = ax + b, che meglio approssima i dati sperimentali, o più precisamente, i coefficienti a e b.

Valutazione dell'accuratezza

Con ogni approssimazione, valutarne l’accuratezza è di particolare importanza. Indichiamo con e i la differenza (deviazione) tra i valori funzionali e sperimentali per il punto x i, cioè e i = y i - f (x i).

Ovviamente, per valutare la correttezza dell'approssimazione, si può utilizzare la somma degli scarti, ovvero, quando si sceglie una retta per una rappresentazione approssimata della dipendenza di X da Y, si dovrebbe dare la preferenza a quella con il valore più piccolo di somma e i in tutti i punti considerati. Tuttavia, non tutto è così semplice, poiché insieme alle deviazioni positive ce ne saranno anche di negative.

Il problema può essere risolto utilizzando i moduli di deviazione o i relativi quadrati. Quest’ultimo metodo è il più utilizzato. Viene utilizzato in molte aree, inclusa l'analisi di regressione (implementata in Excel utilizzando due funzioni integrate) e ha da tempo dimostrato la sua efficacia.

Metodo dei minimi quadrati

Excel, come sai, ha una funzione Somma automatica incorporata che ti consente di calcolare i valori di tutti i valori situati nell'intervallo selezionato. Quindi nulla ci impedirà di calcolare il valore dell'espressione (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

In notazione matematica questo assomiglia a:

Poiché inizialmente si è deciso di approssimare utilizzando una retta, abbiamo:

Pertanto, il compito di trovare la retta che meglio descrive la dipendenza specifica delle quantità X e Y si riduce al calcolo del minimo di una funzione di due variabili:

Per fare ciò, è necessario equiparare a zero le derivate parziali rispetto alle nuove variabili aeb e risolvere un sistema primitivo costituito da due equazioni con 2 incognite della forma:

Dopo alcune semplici trasformazioni, inclusa la divisione per 2 e la manipolazione delle somme, otteniamo:

Risolvendolo, ad esempio, utilizzando il metodo di Cramer, otteniamo un punto stazionario con determinati coefficienti a* e b*. Questo è il minimo, cioè per prevedere quale fatturato avrà un negozio per una certa zona, è adatta la retta y = a * x + b *, che è modello di regressione per l'esempio in questione. Naturalmente, non ti permetterà di trovare il risultato esatto, ma ti aiuterà a farti un'idea se l'acquisto di un'area specifica con credito negozio sarà ripagato.

Come implementare i minimi quadrati in Excel

Excel ha una funzione per calcolare i valori utilizzando i minimi quadrati. Ha la seguente forma: “TREND” (valori Y conosciuti; valori X conosciuti; nuovi valori X; costante). Applichiamo la formula per il calcolo OLS in Excel alla nostra tabella.

Per fare ciò, inserisci il segno "=" nella cella in cui deve essere visualizzato il risultato del calcolo utilizzando il metodo dei minimi quadrati in Excel e seleziona la funzione "TENDENZA". Nella finestra che si apre, compila gli appositi campi, evidenziando:

intervallo di valori noti per Y (in in questo caso dati relativi al fatturato commerciale);
intervallo x 1 , …x n , ovvero la dimensione della superficie commerciale;
valori sia noti che sconosciuti di x, per i quali è necessario scoprire l'entità del fatturato (per informazioni sulla loro posizione nel foglio di lavoro, vedere sotto).

Inoltre, la formula contiene la variabile logica “Const”. Se inserisci 1 nel campo corrispondente, ciò significherà che dovresti effettuare i calcoli, assumendo che b = 0.

Se hai bisogno di scoprire la previsione per più di un valore x, dopo aver inserito la formula non dovresti premere "Invio", ma devi digitare la combinazione "Maiusc" + "Control" + "Invio" sulla tastiera.

Alcune funzionalità

L'analisi di regressione può essere accessibile anche ai manichini. La formula di Excel per prevedere il valore di una serie di variabili sconosciute, TENDENZA, può essere utilizzata anche da coloro che non hanno mai sentito parlare dei minimi quadrati. Basta solo conoscere alcune caratteristiche del suo lavoro. In particolare:

Se disponi l'intervallo di valori noti della variabile y in una riga o colonna, ciascuna riga (colonna) con valori noti di x verrà percepita dal programma come una variabile separata.
Se un intervallo con x noto non è specificato nella finestra TREND, quando si utilizza la funzione in Excel, il programma lo tratterà come un array composto da numeri interi, il cui numero corrisponde all'intervallo con i valori specificati di variabile y.
Per emettere un array di valori “previsti”, l'espressione per il calcolo della tendenza deve essere inserita come formula di array.
Se non vengono specificati nuovi valori di x, la funzione TENDENZA li considera uguali a quelli noti. Se non vengono specificati, viene preso come argomento l'array 1; 2; 3; 4;..., che è commisurato alla gamma con già parametri dati sì.
L'intervallo contenente i nuovi valori x deve avere le stesse o più righe o colonne dell'intervallo contenente i valori y specificati. In altre parole, deve essere proporzionale alle variabili indipendenti.
Un array con valori x noti può contenere più variabili. Tuttavia, se ne parliamo solo uno, è necessario che gli intervalli con i valori indicati di xey siano proporzionali. Nel caso di più variabili, è necessario che l'intervallo con i valori y indicati rientri in una colonna o riga.

Funzione PREVISIONE

L'analisi di regressione in Excel viene implementata utilizzando diverse funzioni. Uno di questi si chiama “PREDIZIONE”. È simile a “TREND”, ovvero fornisce il risultato di calcoli utilizzando il metodo dei minimi quadrati. Tuttavia, solo per una X, per la quale il valore di Y è sconosciuto.

Ora conosci le formule in Excel for dummies che ti consentono di prevedere il valore futuro di un particolare indicatore secondo una tendenza lineare.

Il metodo dei minimi quadrati (LS) si basa sulla minimizzazione della somma delle deviazioni quadrate della funzione selezionata dai dati in studio. In questo articolo approssimeremo i dati disponibili utilizzando una funzione linearesì = UN X + B .

Metodo dei minimi quadrati(Inglese) Ordinario Meno Piazze , O.L.S.) è uno dei metodi di base dell'analisi di regressione in termini di stima di parametri sconosciuti modelli di regressione secondo i dati del campione.

Consideriamo l'approssimazione mediante funzioni che dipendono solo da una variabile:

Lineare: y=ax+b (questo articolo)
: y=a*Ln(x)+b
: y=a*xm
: y=a*ESP(b*x)+ñ
: y=asse 2 +bx+c

Nota: In questo articolo vengono considerati i casi di approssimazione mediante polinomio dal 3° al 6° grado. Qui viene considerata l'approssimazione mediante un polinomio trigonometrico.

Dipendenza lineare

Siamo interessati alla connessione tra 2 variabili X E sì. Si presume che sì dipende da X secondo la legge lineare sì = ascia + B. Per determinare i parametri di questa relazione, il ricercatore ha effettuato delle osservazioni: per ogni valore di xi è stata effettuata una misurazione di y i (vedi file di esempio). Di conseguenza, siano 20 coppie di valori (x i; y i).

Nota: Se il passaggio di modifica è X è costante, quindi costruire grafici a dispersione può essere utilizzato, in caso contrario è necessario utilizzare il tipo di grafico Macchiare .

Dal diagramma è ovvio che la relazione tra le variabili è quasi lineare. Per capire quale tra le tante rette descrive più “correttamente” la relazione tra variabili, è necessario determinare il criterio con cui le rette verranno confrontate.

Come criterio usiamo l'espressione:

Dove ŷ io = UN * x io + B ; n – numero di coppie di valori (nel nostro caso n=20)

L'espressione di cui sopra è la somma delle distanze al quadrato tra i valori osservati di y i e ŷ i ed è spesso indicata come SSE ( Somma Di Quadrato Errori (Residui), somma degli errori quadratici (residui)) .

Metodo dei minimi quadratiè selezionare tale linea ŷ = ascia + B, per il quale l'espressione precedente assume il valore minimo.

Nota: Qualsiasi linea nello spazio bidimensionale è determinata in modo univoco dai valori di 2 parametri: UN (pendenza) e B (spostare).

Si ritiene che quanto minore è la somma delle distanze quadrate, tanto meglio la linea corrispondente si avvicina ai dati disponibili e può essere ulteriormente utilizzata per prevedere i valori di y dalla variabile x. È chiaro che anche se in realtà non esiste alcuna relazione tra le variabili o la relazione non è lineare, allora OLS selezionerà comunque la linea “migliore”. Pertanto, il metodo dei minimi quadrati non dice nulla sulla presenza di una relazione reale tra le variabili; il metodo consente semplicemente di selezionare tali parametri della funzione UN E B , per cui l'espressione di cui sopra è minima.

Eseguendo operazioni matematiche non molto complesse (per maggiori dettagli vedere), è possibile calcolare i parametri UN E B :

Come si può vedere dalla formula, il parametro UN rappresenta il rapporto di covarianza e, quindi in MS EXCEL per calcolare il parametro UN può essere utilizzata seguenti formule(cm. File di esempio del foglio lineare):

= KOVAR(B26:B45;C26:C45)/ DISP.G(B26:B45) O

= COVARIANZA.B(B26:B45;C26:C45)/DISP.B(B26:B45)

Anche per calcolare il parametro UN puoi usare la formula = INCLINAZIONE(C26:C45;B26:B45). Per parametro B usa la formula = GAMBA(C26:C45;B26:B45) .

Infine, la funzione LINEST() consente di calcolare entrambi i parametri contemporaneamente. Per inserire una formula REGR.LIN(C26:C45;B26:B45)È necessario selezionare 2 celle di seguito e fare clic CTRL + SPOSTARE + ACCEDERE(vedi articolo su). Il valore verrà restituito nella cella di sinistra UN , sulla destra - B .

Nota: Per evitare di fare confusione con l'input formule di matrice dovrai utilizzare inoltre la funzione INDICE(). Formula = INDICE(LINES(C26:C45;B26:B45);1) o semplicemente = REGR.LIN(C26:C45;B26:B45) restituirà il parametro responsabile della pendenza della linea, cioè UN . Formula = INDICE(LINES(C26:C45;B26:B45);2) restituirà il parametro responsabile dell'intersezione della linea con l'asse Y, cioè B .

Dopo aver calcolato i parametri, diagramma di dispersione puoi disegnare la linea corrispondente.

Un altro modo per tracciare una linea retta utilizzando il metodo dei minimi quadrati è lo strumento grafico Linea di tendenza. Per fare ciò, seleziona il diagramma, seleziona dal menu Scheda Layout, V Analisi di gruppo clic Linea di tendenza, Poi Approssimazione lineare .

Selezionando la casella "mostra equazione nel diagramma" nella finestra di dialogo, puoi assicurarti che i parametri trovati sopra corrispondano ai valori nel diagramma.

Nota: Affinché i parametri corrispondano, il tipo di diagramma deve essere . Il punto è che quando si costruisce un diagramma Programma I valori dell'asse X non possono essere specificati dall'utente (l'utente può solo specificare etichette che non influiscono sulla posizione dei punti). Al posto dei valori X viene utilizzata la sequenza 1; 2; 3; ... (per la numerazione delle categorie). Pertanto, se costruisci linea di tendenza su un diagramma di tipo Programma, al posto dei valori effettivi di X verranno utilizzati i valori di questa sequenza, il che porterà a un risultato errato (a meno che, ovviamente, valori reali X non corrisponde alla sequenza 1; 2; 3; ...).

Ha molteplici applicazioni, poiché consente una rappresentazione approssimata di una determinata funzione mediante altre più semplici. L'LSM può essere estremamente utile nell'elaborazione delle osservazioni e viene utilizzato attivamente per stimare alcune quantità sulla base dei risultati di misurazioni di altre contenenti errori casuali. In questo articolo imparerai come implementare i calcoli dei minimi quadrati in Excel.

Esposizione del problema utilizzando un esempio specifico

Quindi, sia X lo spazio commerciale di un negozio di alimentari, misurato in metri quadrati, e Y il fatturato annuo, misurato in milioni di rubli.

Qualche parola sulla correttezza dei dati iniziali utilizzati per la previsione

Supponiamo di avere una tabella creata utilizzando i dati per n negozi.

Secondo la statistica matematica, i risultati saranno più o meno corretti se verranno esaminati i dati su almeno 5-6 oggetti. Inoltre non è possibile utilizzare risultati “anomali”. In particolare, una piccola boutique d'élite può avere un fatturato molte volte superiore al fatturato dei grandi punti vendita della classe “masmarket”.

L'essenza del metodo

Valutazione dell'accuratezza

Metodo dei minimi quadrati

In notazione matematica questo assomiglia a:

Poiché inizialmente si è deciso di approssimare utilizzando una retta, abbiamo:

Pertanto, il compito di trovare la retta che meglio descrive la dipendenza specifica delle quantità X e Y si riduce al calcolo del minimo di una funzione di due variabili:

Per fare ciò, è necessario equiparare a zero le derivate parziali rispetto alle nuove variabili aeb e risolvere un sistema primitivo costituito da due equazioni con 2 incognite della forma:

Dopo alcune semplici trasformazioni, inclusa la divisione per 2 e la manipolazione delle somme, otteniamo:

Risolvendolo, ad esempio, utilizzando il metodo di Cramer, otteniamo un punto stazionario con determinati coefficienti a* e b*. Questo è il minimo, cioè per prevedere quale fatturato avrà un negozio per una determinata zona, è adatta la retta y = a*x+b*, che è un modello di regressione per l'esempio in questione. Naturalmente, non ti permetterà di trovare il risultato esatto, ma ti aiuterà a farti un'idea se l'acquisto di un'area specifica con credito negozio sarà ripagato.

Come implementare i minimi quadrati in Excel

intervallo di valori noti per Y (in questo caso, dati relativi al fatturato commerciale);
intervallo x 1 , …x n , ovvero la dimensione della superficie commerciale;
valori sia noti che sconosciuti di x, per i quali è necessario scoprire l'entità del fatturato (per informazioni sulla loro posizione nel foglio di lavoro, vedere sotto).

Inoltre, la formula contiene la variabile logica “Const”. Se inserisci 1 nel campo corrispondente, ciò significherà che dovresti effettuare i calcoli, assumendo che b = 0.

Alcune funzionalità

Se disponi l'intervallo di valori noti della variabile y in una riga o colonna, ciascuna riga (colonna) con valori noti di x verrà percepita dal programma come una variabile separata.
Se un intervallo con x noto non è specificato nella finestra TREND, quando si utilizza la funzione in Excel, il programma lo tratterà come un array composto da numeri interi, il cui numero corrisponde all'intervallo con i valori specificati di variabile y.
Per emettere un array di valori “previsti”, l'espressione per il calcolo della tendenza deve essere inserita come formula di array.
Se non vengono specificati nuovi valori di x, la funzione TENDENZA li considera uguali a quelli noti. Se non vengono specificati, viene preso come argomento l'array 1; 2; 3; 4;…, che è commisurato all'intervallo con parametri già specificati y.
L'intervallo contenente i nuovi valori x deve avere le stesse o più righe o colonne dell'intervallo contenente i valori y specificati. In altre parole, deve essere proporzionale alle variabili indipendenti.
Un array con valori x noti può contenere più variabili. Tuttavia, se ne parliamo solo uno, è necessario che gli intervalli con i valori indicati di xey siano proporzionali. Nel caso di più variabili, è necessario che l'intervallo con i valori y indicati rientri in una colonna o riga.

Funzione PREVISIONE

Implementato utilizzando diverse funzioni. Uno di questi si chiama “PREDIZIONE”. È simile a “TREND”, ovvero fornisce il risultato di calcoli utilizzando il metodo dei minimi quadrati. Tuttavia, solo per una X, per la quale il valore di Y è sconosciuto.

Ora conosci le formule in Excel for dummies che ti consentono di prevedere il valore futuro di un particolare indicatore secondo una tendenza lineare.