Metodo minimi quadrati(LSM) 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 È possibile utilizzare le seguenti formule (vedi 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 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.

Ovviamente puoi usare un polinomio 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. È utilizzato in molte aree tra cui analisi di regressione(in Excel la sua implementazione viene effettuata 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 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

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

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.

Metodo dei minimi quadrati (LSM)

Sistema m equazioni lineari con n incognite ha la forma:

Sono possibili tre casi: m N. Il caso in cui m=n è stato considerato nei paragrafi precedenti. Quando m

Se m>n e il sistema è coerente, allora la matrice A ha almeno m - n righe linearmente dipendenti. Qui la soluzione può essere ottenuta selezionando n qualsiasi equazione linearmente indipendente (se esistono) e applicando la formula X = A -1 CV, cioè riducendo il problema a uno risolto in precedenza. In questo caso, la soluzione risultante soddisferà sempre le restanti equazioni m - n.

Tuttavia, quando si utilizza un computer, è più conveniente utilizzare un approccio più generale: il metodo dei minimi quadrati.

Metodo dei minimi quadrati algebrici

Il metodo algebrico dei minimi quadrati è un metodo per risolvere sistemi di equazioni lineari

minimizzando la norma euclidea

Ascia? B? >inf. (1.2)

Analisi dei dati sperimentali

Consideriamo qualche esperimento durante il quale in momenti di tempo

Ad esempio, viene misurata la temperatura Q(t). Lascia che i risultati della misurazione siano specificati da un array

Supponiamo che le condizioni sperimentali siano tali che le misurazioni vengano effettuate con un errore noto. In questi casi, la legge della variazione di temperatura Q(t) viene ricercata utilizzando un determinato polinomio

P(t) = + + + ... +,

determinare i coefficienti incogniti, ..., partendo dalla considerazione che il valore E(, ...,), definito dall'uguaglianza

Approssimazione algebrica di Gauss tramite Excel

ha preso il valore minimo. Poiché la somma dei quadrati è ridotta al minimo, questo metodo è chiamato approssimazione dei dati ai minimi quadrati.

Se sostituiamo P(t) con la sua espressione, otteniamo

Impostiamo il compito di definire un array in modo che il valore sia minimo, cioè Definiamo l'array utilizzando il metodo dei minimi quadrati. Per fare ciò uguagliamo a zero le derivate parziali:

Se inserisci la matrice m × n A = (), i = 1, 2..., m; j = 1, 2, ..., n, dove

io = 1, 2..., m; j = 1, 2, ..., n,

allora assumerà la forma l'uguaglianza scritta

Riscriviamo l'uguaglianza scritta in termini di operazioni con matrici. Per la definizione di moltiplicare una matrice per una colonna, abbiamo

Per una matrice trasposta, una relazione simile si presenta così

Introduciamo la notazione: indicheremo la i-esima componente del vettore Ax. In accordo con le uguaglianze matriciali scritte, avremo

In forma matriciale questa uguaglianza può essere riscritta come

A T x= A T B (1.3)

Qui A è una matrice rettangolare m×n. Inoltre, nei problemi di approssimazione dei dati, di regola, m > n. L'equazione (1.3) è chiamata equazione normale.

È stato possibile fin dall’inizio, utilizzando la norma euclidea dei vettori, scrivere il problema in forma matriciale equivalente:

Il nostro obiettivo è minimizzare questa funzione in x. Affinché in un punto della soluzione venga raggiunto un minimo, la derivata prima rispetto a x in questo punto deve essere uguale a zero. Le derivate di questa funzione sono

2A T B + 2A T Asse

e quindi la soluzione deve soddisfare il sistema di equazioni lineari

(A T A)x = (A T B).

Queste equazioni sono chiamate equazioni normali. Se A è una matrice m× n, allora A>A - n × n è una matrice, cioè La matrice di un'equazione normale è sempre una matrice quadrata simmetrica. Inoltre possiede la proprietà di definitezza positiva nel senso che (A>Ax, x) = (Ax, Ax) ? 0.

Commento. A volte la soluzione di un'equazione della forma (1.3) è chiamata soluzione del sistema Ax = B, dove A è una matrice rettangolare m × n (m > n) che utilizza il metodo dei minimi quadrati.

Il problema dei minimi quadrati può essere interpretato graficamente come la minimizzazione delle distanze verticali dai punti dati a una curva del modello (vedi Figura 1.1). Questa idea si basa sul presupposto che tutti gli errori nell'approssimazione corrispondano ad errori nelle osservazioni. Se ci sono errori anche nelle variabili indipendenti, allora potrebbe essere più appropriato minimizzare la distanza euclidea dai dati al modello.

Multinazionale in Excel

L'algoritmo seguente per l'implementazione di OLS in Excel presuppone che tutti i dati iniziali siano già noti. Moltiplichiamo entrambi i lati dell'equazione matriciale AЧX=B del sistema a sinistra per la matrice trasposta del sistema А Т:

A T AX=A T B

Quindi moltiplichiamo entrambi i lati dell'equazione a sinistra per la matrice (A T A) -1. Se questa matrice esiste, allora il sistema è definito. Considerando che

(A T A) -1 *(A T A)=E, otteniamo

X=(A T A) -1 A T B.

L'equazione di matrice risultante è una soluzione a un sistema di m equazioni lineari con n incognite per m>n.

Consideriamo l'applicazione dell'algoritmo di cui sopra su esempio specifico.

Esempio. Lascia che sia necessario risolvere il sistema

In Excel, il foglio della soluzione nella modalità di visualizzazione della formula per questo problema è simile al seguente:

Risultati del calcolo:

Il vettore X richiesto si trova nell'intervallo E11:E12.

Durante la risoluzione di un dato sistema di equazioni lineari, sono state utilizzate le seguenti funzioni:

1. MOBR - resi matrice inversa per una matrice memorizzata in un array.

Sintassi: MOBR(array).

Array è un array numerico con un numero uguale di righe e colonne.

2. MULTIPULT - restituisce il prodotto di matrici (le matrici sono memorizzate in array). Il risultato è un array con lo stesso numero di righe di array1 e lo stesso numero di colonne di array2.

Sintassi: MULTIPLA(array1,array2).

Array1, array2 sono array moltiplicabili.

Dopo aver immesso una funzione nella cella in alto a sinistra di un intervallo di matrice, selezionare la matrice, iniziando con la cella contenente la formula, premere F2, quindi premere CTRL+MAIUSC+INVIO.

3. TRASPORTO: converte un insieme di celle verticale in uno orizzontale o viceversa. Come risultato dell'utilizzo di questa funzione, viene visualizzato un array con il numero di righe uguale al numero di colonne dell'array originale e il numero di colonne uguale al numero di righe dell'array iniziale.

4.1. Utilizzo delle funzioni integrate

Calcolo coefficienti di regressione effettuata utilizzando la funzione

LINEST(Valori_y; valori x; Cost; statistiche),

Valori_y- array di valori y,

valori x- array di valori opzionale X, se array X viene omesso, si presuppone che si tratti di un array (1;2;3;...) della stessa dimensione di Valori_y,

Cost- un valore booleano che indica se la costante è richiesta B era uguale a 0. Se Cost ha il significato VERO o omesso, quindi B viene calcolato nel modo consueto. Se l'argomento Costè FALSO, quindi B si presuppone che sia 0 e i valori UN sono selezionati in modo che la relazione sia soddisfatta y=ascia.

Statisticheè un valore booleano che indica se è necessario restituire statistiche di regressione aggiuntive. Se l'argomento Statistiche ha il significato VERO, quindi la funzione LINEST restituisce statistiche di regressione aggiuntive. Se l'argomento Statistiche ha il significato MENZOGNA o omesso, quindi la funzione LINEST restituisce solo il coefficiente UN e costante B.

Va ricordato che il risultato delle funzioni REGR.LIN()è un insieme di valori – un array.

Per il calcolo coefficiente di correlazione viene utilizzata la funzione

CORRELAZIONE(Array1;Array2),

restituendo i valori del coefficiente di correlazione, dove Array1- matrice di valori sì, Array2- matrice di valori X. Array1 E Array2 deve essere della stessa dimensione.

ESEMPIO 1. Dipendenza sì(X) è presentato nella tabella. Costruire retta di regressione e calcolare coefficiente di correlazione.

Inseriamo una tabella di valori in un foglio MS Excel e costruiamo un grafico a dispersione. Il foglio di lavoro assumerà la forma mostrata in Fig. 2.

Per calcolare i valori dei coefficienti di regressione UN E B seleziona le celle A7:B7, Andiamo alla funzione guidata e nella categoria Statistico selezionare una funzione LINEST. Compilare la finestra di dialogo che appare come mostrato in Fig. 3 e premere OK.

Di conseguenza, il valore calcolato verrà visualizzato solo nella cella A6(Fig. 4). Affinché il valore appaia nella cella B6è necessario entrare in modalità modifica (tasto F2), quindi premere la combinazione di tasti CTRL+MAIUSC+INVIO.

Per calcolare il valore del coefficiente di correlazione in una cella C6è stato presentato seguente formula:

C7=CORREZIONE(B3:J3;B2:J2).

Conoscere i coefficienti di regressione UN E B calcoliamo i valori della funzione sì=ascia+B per dato X. Per fare ciò, introduciamo la formula

B5=$A$7*B2+$B$7

e copiarlo nell'intervallo C5:J5(Fig. 5).

Tracciamo la retta di regressione sul diagramma. Selezionare i punti sperimentali sul grafico, fare clic con il tasto destro e selezionare il comando Dati iniziali. Nella finestra di dialogo che appare (Fig. 5), selezionare la scheda Riga e fare clic sul pulsante Aggiungere. Compiliamo i campi di input come mostrato in Fig. 6 e premere il pulsante OK. Una linea di regressione verrà aggiunta al grafico dei dati sperimentali. Per impostazione predefinita, il suo grafico verrà disegnato come punti non collegati da linee smussate.

Per modificare l'aspetto della linea di regressione, eseguire i passaggi seguenti. Fare clic con il tasto destro sui punti che rappresentano il grafico a linee e selezionare il comando Tipo di grafico e impostare il tipo di diagramma di dispersione, come mostrato in Fig. 7.

Il tipo, il colore e lo spessore della linea possono essere modificati come segue. Selezionare una linea nel diagramma, fare clic con il tasto destro e selezionare il comando nel menu contestuale Formato della serie di dati... Successivamente, effettuare le impostazioni, ad esempio, come mostrato in Fig. 8.

Come risultato di tutte le trasformazioni, otteniamo un grafico di dati sperimentali e una linea di regressione in un'area grafica (Fig. 9).

4.2. Utilizzando una linea di tendenza.

La costruzione di varie dipendenze approssimative in MS Excel è implementata come proprietà del grafico - linea di tendenza.

ESEMPIO 2. Come risultato dell'esperimento, è stata determinata una certa dipendenza dalla tabella.

Seleziona e costruisci una dipendenza approssimata. Costruire grafici di dipendenze tabulari e analitiche selezionate.

La soluzione del problema può essere suddivisa nelle seguenti fasi: inserimento dei dati iniziali, costruzione di un grafico a dispersione e aggiunta di una linea di tendenza a questo grafico.

Diamo un'occhiata a questo processo in dettaglio. Inseriamo i dati iniziali nel foglio di lavoro e tracciamo i dati sperimentali. Successivamente, seleziona i punti sperimentali sul grafico, fai clic con il pulsante destro del mouse e utilizza il comando Aggiungere l linea di tendenza(Fig. 10).

La finestra di dialogo che appare consente di costruire una relazione approssimata.

La prima scheda (Fig. 11) di questa finestra indica il tipo di dipendenza approssimata.

Sulla seconda (Fig. 12) si determinano i parametri costruttivi:

· nome della dipendenza approssimata;

· previsione in avanti (indietro) di N unità (questo parametro determina di quante unità in avanti (indietro) la linea di tendenza deve essere estesa);

se mostrare il punto di intersezione di una curva con una retta y=cost;

· mostrare o meno la funzione approssimante sul diagramma (la possibilità di mostrare l'equazione sul diagramma);

· se posizionare o meno il valore della deviazione standard sul diagramma (la possibilità di posizionare sul diagramma il valore dell'affidabilità dell'approssimazione).

Scegliamo un polinomio di secondo grado come dipendenza approssimante (Fig. 11) e rappresentiamo l'equazione che descrive questo polinomio su un grafico (Fig. 12). Il diagramma risultante è mostrato in Fig. 13.

Allo stesso modo utilizzando linee di tendenzaè possibile selezionare i parametri di tali dipendenze come

lineare sì=a∙x+B,

logaritmico sì=a∙ln(X)+B,

· esponenziale sì=a∙e b,

· calmare sì=a∙x b,

polinomio sì=a∙x 2 +b∙x+C, sì=a∙x 3 +b∙x 2 +c∙x+d e così via, fino ad un polinomio del 6° grado compreso,

· filtrazione lineare.

4.3. Utilizzo dello strumento di analisi delle opzioni: trovare una soluzione.

Di notevole interesse è l'implementazione in MS Excel della selezione dei parametri di una relazione funzionale utilizzando il metodo dei minimi quadrati utilizzando lo strumento di analisi delle opzioni: Cerca una soluzione. Questa tecnica consente di selezionare i parametri di una funzione di qualsiasi tipo. Consideriamo questa possibilità utilizzando come esempio il seguente problema.

ESEMPIO 3. Come risultato dell'esperimento, è stata ottenuta la dipendenza z(t), presentata nella tabella

Seleziona i coefficienti di dipendenza Z(t)=At 4 +Bt 3 +Ct 2 +Dt+K metodo dei minimi quadrati.

Questo problema è equivalente al problema di trovare il minimo di una funzione di cinque variabili

Consideriamo il processo di risoluzione del problema di ottimizzazione (Fig. 14).

Passiamo ai valori UN, IN, CON, D E A immagazzinati nelle cellule A7:E7. Calcoliamo i valori teorici della funzione Z(T)=A 4 +Bt 3 +Ct 2 +Dt+K per dato T(B2:J2). Per fare questo, nella cella B4 inserire il valore della funzione al primo punto (cell B2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

Copiamo questa formula nell'intervallo C4:J4 e ottieni il valore atteso della funzione nei punti le cui ascisse sono memorizzate nelle celle B2:J2.

Al cellulare B5 Introduciamo una formula che calcola il quadrato della differenza tra i punti sperimentali e quelli calcolati:

B5=(B4-B3)^2,

e copiarlo nell'intervallo C5:J5. In una cella F7 memorizzeremo l'errore quadratico totale (10). Per fare ciò, inserisci la formula:

F7 = SOMMA(B5:J5).

Usiamo il comando Service®Cerca una soluzione e risolvere il problema di ottimizzazione senza restrizioni. Compiliamo di conseguenza i campi di input nella finestra di dialogo mostrata in Fig.. 14 e premere il pulsante Eseguire. Se viene trovata una soluzione, verrà visualizzata la finestra mostrata in Fig. 15.

Il risultato del blocco decisionale verrà inviato alle celle A7:E7valori dei parametri funzioni Z(T)=A 4 +Bt 3 +Ct 2 +Dt+K. Nelle celle B4:J4 noi abbiamo valore della funzione attesa ai punti di partenza. In una cella F7 verrà memorizzato errore quadrato totale.

È possibile visualizzare punti sperimentali e una linea adattata in un'area grafica selezionando un intervallo B2:J4, chiamata Mago dei grafici e quindi formattare l'aspetto dei grafici risultanti.

Riso. 17 visualizza il foglio di lavoro MS Excel dopo che i calcoli sono stati eseguiti.