Exemplu de formulă de interpolare liniară. Utilizarea extrapolării în Microsoft Excel

Există o situație când într-o serie de valori cunoscute trebuie să găsiți rezultate intermediare. În matematică aceasta se numește interpolare. În Excel aceasta metoda poate fi folosit atât pentru date tabulare, cât și pentru construirea de grafice. Să ne uităm la fiecare dintre aceste metode.

Condiția principală în care poate fi utilizată interpolarea este ca valoarea dorită să fie în interiorul matricei de date și nu în afara limitei acesteia. De exemplu, dacă avem un set de argumente 15, 21 și 29, atunci putem folosi interpolarea pentru a găsi funcția pentru argumentul 25. Dar nu mai există nicio modalitate de a găsi valoarea corespunzătoare pentru argumentul 30. Aceasta este principala diferență dintre această procedură și extrapolare.

Metoda 1: Interpolare pentru date tabulare

În primul rând, să ne uităm la aplicațiile de interpolare pentru datele care se află într-un tabel. De exemplu, să luăm o matrice de argumente și valorile funcției corespunzătoare ale acestora, a căror relație poate fi descrisă ecuație liniară. Aceste date sunt prezentate în tabelul de mai jos. Trebuie să găsim funcția corespunzătoare pentru argument 28 . Cel mai simplu mod de a face acest lucru este folosirea operatorului PREDICȚIE.

Metoda 2: Interpolați graficul folosind setările acestuia

Procedura de interpolare poate fi folosită și la construirea graficelor de funcții. Este relevant dacă tabelul pe care se bazează graficul nu indică valoarea funcției corespunzătoare pentru unul dintre argumente, ca în imaginea de mai jos.

După cum puteți vedea, graficul a fost corectat, iar decalajul a fost eliminat prin interpolare.

Metoda 3: Interpolați graficul folosind o funcție

De asemenea, puteți interpola graficul folosind functie speciala ND. Returnează valori nedefinite în celula specificată.

O poți face și mai ușor fără să alergi Expertul de funcțiiși utilizați doar tastatura pentru a introduce valoarea într-o celulă goală "#N / A" fără ghilimele. Dar depinde de ce este mai convenabil pentru care utilizator.

După cum puteți vedea, în Excel puteți interpola sub formă de date tabelare folosind funcția PREDICȚIE, și grafică. În acest din urmă caz, acest lucru se poate face folosind setările diagramei sau folosind funcția ND, provocând o eroare "#N / A". Alegerea metodei de utilizat depinde de enunțul problemei, precum și de preferințele personale ale utilizatorului.

Acesta este un capitol din cartea lui Bill Jelen.

Provocare: Unele probleme de proiectare inginerească necesită utilizarea de tabele pentru a calcula valorile parametrilor. Deoarece tabelele sunt discrete, proiectantul folosește interpolarea liniară pentru a obține o valoare intermediară a parametrului. Tabelul (Fig. 1) include înălțimea deasupra solului (parametru de control) și viteza vântului (parametru calculat). De exemplu, dacă trebuie să găsiți viteza vântului corespunzătoare unei înălțimi de 47 de metri, atunci ar trebui să aplicați formula: 130 + (180 – 130) * 7 / (50 – 40) = 165 m/sec.

Descărcați nota în sau format, exemple în format

Ce se întâmplă dacă există doi parametri de control? Este posibil să se efectueze calcule folosind o singură formulă? Tabelul (Fig. 2) arată valorile presiunii vântului pentru diferite înălțimi și deschideri ale structurilor. Este necesar să se calculeze presiunea vântului la o înălțime de 25 de metri și o deschidere de 300 de metri.

Soluție: Rezolvăm problema extinzând metoda utilizată pentru caz cu un singur parametru de control. Urmați acești pași:

Începeți cu tabelul prezentat în fig. 2. Adăugați celule sursă pentru înălțime și întindere în J1 și, respectiv, J2 (Figura 3).

Orez. 3. Formulele din celulele J3:J17 explică funcționarea megaformulei

Pentru ușurința utilizării formulelor, definiți nume (Fig. 4).

Urmăriți cum formula funcționează secvenţial de la celula J3 la celula J17.

Utilizați substituția secvenţială inversă pentru a construi megaformula. Copiați textul formulei din celula J17 în J19. Înlocuiți referința la J15 din formulă cu valoarea din celula J15: J7+(J8-J7)*J11/J13. Și așa mai departe. Rezultatul este o formulă formată din 984 de caractere, care nu poate fi percepută în această formă. Îl puteți vedea în fișierul Excel atașat. Nu sunt sigur că acest tip de megaformulă este util de utilizat.

Rezumat: Interpolarea liniară este utilizată pentru a obține o valoare intermediară a unui parametru dacă valorile tabelului specificat numai pentru limitele intervalului; Se propune o metodă de calcul folosind doi parametri de control.

Interpolare. Introducere. Prezentarea generală a problemei

La rezolvarea diverselor probleme practice rezultatele cercetării sunt prezentate sub formă de tabele care afișează dependența uneia sau mai multor mărimi măsurate de un parametru definitoriu (argument). Aceste tipuri de tabele sunt de obicei prezentate sub forma a două sau mai multe rânduri (coloane) și sunt folosite pentru a forma modele matematice.

Tabelar specificat în modele matematice funcțiile sunt de obicei scrise în tabele de forma:


Y1(X)	Y(X0)	Y(X1)	Y(Xn)

Ym(X)	Y(X0)	Y(X1)	Y(Xn)

Informațiile limitate furnizate de astfel de tabele necesită în unele cazuri obținerea valorilor funcțiilor Y j (X) (j=1,2,…,m) la punctele X care nu coincid cu punctele nodale ale tabelului X i (i=0,1,2,… ,n) . În astfel de cazuri, este necesar să se determine o expresie analitică φ j (X) pentru a calcula valorile aproximative ale funcției studiate Y j (X) în punctele X specificate arbitrar. Funcția φ j (X) utilizată pentru a determina valorile aproximative ale funcției Y j (X) se numește funcție de aproximare (din latinescul aproximo - se apropie). Apropierea funcției de aproximare φ j (X) de funcția de aproximare Y j (X) este asigurată prin alegerea algoritmului de aproximare adecvat.

Vom face toate considerațiile și concluziile ulterioare pentru tabelele care conțin datele inițiale ale unei funcții studiate (adică pentru tabelele cu m=1).

1. Metode de interpolare

1.1 Enunțarea problemei de interpolare

Cel mai adesea, pentru a determina funcția φ(X), se folosește o formulare, numită formularea problemei de interpolare.

În această formulare clasică a problemei de interpolare, este necesară determinarea funcției analitice aproximative φ(X), ale cărei valori la punctele nodale X i se potrivesc cu valorile Y(Х i ) din tabelul original, i.e. conditii

ϕ (X i )= Y i (i = 0,1,2,...,n)

Funcția de aproximare φ(X) astfel construită permite să se obțină o aproximare destul de apropiată a funcției interpolate Y(X) în intervalul de valori al argumentului [X 0 ; X n ], determinat de tabel. Când specificați valorile argumentului X, neapartenereîn acest interval, problema de interpolare se transformă într-o problemă de extrapolare. În aceste cazuri, precizia

valorile obținute la calcularea valorilor funcției φ(X) depind de distanța dintre valoarea argumentului X de X 0, dacă X<Х 0 , или отХ n , еслиХ >Xn.

La modelare matematică funcția de interpolare poate fi utilizată pentru a calcula valori aproximative ale funcției studiate în punctele intermediare ale subintervalelor [Х i ; X i+1]. Această procedură se numește compactarea mesei.

Algoritmul de interpolare este determinat de metoda de calcul a valorilor funcției φ(X). Cea mai simplă și mai evidentă opțiune de implementare a funcției de interpolare este înlocuirea funcției studiate Y(X) pe intervalul [X i ; X i+1 ] printr-o dreaptă care leagă punctele Y i , Y i+1 . Această metodă se numește metodă interpolare liniară.

1.2 Interpolare liniară

Cu interpolare liniară, valoarea funcției în punctul X, situat între nodurile X i și X i+1, este determinată de formula unei linii drepte care leagă două puncte adiacente ale tabelului

Y(X) = Y(Xi)+	Y(Xi + 1 )− Y(Xi )	(X − Xi ) (i= 0,1,2, ...,n),

	X i+ 1− X i

În fig. Figura 1 prezintă un exemplu de tabel obținut ca rezultat al măsurătorilor unei anumite mărimi Y(X). Rândurile tabelului sursă sunt evidențiate. În dreapta tabelului este un grafic de dispersie corespunzător acestui tabel. Tabelul este compactat folosind formula

(3) valori ale funcției aproximative în punctele X corespunzătoare punctelor medii ale subintervalelor (i=0, 1, 2, …, n).

Fig.1. Tabel condensat al funcției Y(X) și diagrama ei corespunzătoare

Când luăm în considerare graficul din fig. 1 se poate observa că punctele obținute în urma compactării tabelului prin metoda interpolării liniare se află pe segmente drepte care leagă punctele tabelului inițial. Precizie liniară

interpolare, depinde semnificativ de natura funcției interpolate și de distanța dintre nodurile tabelului X i, , X i+1.

Evident, dacă funcția este netedă, atunci, chiar și cu relativ distanta lungaîntre noduri, un grafic construit prin conectarea punctelor cu segmente de linie dreaptă permite evaluarea destul de precisă a naturii funcției Y(X). Dacă funcția se modifică destul de repede, iar distanțele dintre noduri sunt mari, atunci funcția de interpolare liniară nu permite obținerea unei aproximări suficient de precise față de funcția reală.

Funcția de interpolare liniară poate fi utilizată pentru general analiza preliminarași evaluarea corectitudinii rezultatelor interpolării, care sunt apoi obținute prin alte metode mai precise. Această evaluare devine deosebit de relevantă în cazurile în care calculele sunt efectuate manual.

1.3 Interpolare prin polinom canonic

Metoda de interpolare a unei funcții printr-un polinom canonic se bazează pe construirea funcției de interpolare ca polinom sub forma [1]

ϕ (x) = Pn (x) = c0 + c1 x+ c2 x2 + ... + cn xn

Coeficienții c i ai polinomului (4) sunt parametri de interpolare liberă, care sunt determinați din condițiile Lagrange:

Pn (xi )= Yi , (i= 0 , 1 , ... , n)

Folosind (4) și (5) scriem sistemul de ecuații

C x+ c x2		C xn = Y

C x+ c x2		C xn


	C x2		C xn = Y

Vectorul soluție cu i (i = 0, 1, 2, …, n) al sistemului de liniare ecuații algebrice(6) există și poate fi găsit dacă nu există noduri care se potrivesc între i. Determinantul sistemului (6) se numește determinant Vandermonde1 și are o expresie analitică [2].

1 Vandermonde determinant numit determinant

El egal cu zero dacă și numai dacă xi = xj pentru unii. (Material de pe Wikipedia - enciclopedia liberă)

Pentru a determina valorile coeficienților cu i (i = 0, 1, 2, … , n)

ecuațiile (5) pot fi scrise sub formă de vector-matrice

A* C= Y,

unde A, matricea coeficienților determinată de tabelul de grade al vectorului de argumente X = (x i 0, x i, x i 2, …, x i n) T (i = 0, 1, 2, …, n)

				x0 2		x0 n




				xn 2		xn n

C este vectorul coloană al coeficienților i (i = 0, 1, 2, … , n), iar Y este vectorul coloanei valorilor Y i (i = 0, 1, 2, … , n) ale interpolatului funcţia la nodurile de interpolare.

Rezolvarea acestui sistem de ecuații algebrice liniare poate fi obținută folosind una dintre metodele descrise în [3]. De exemplu, conform formulei


C = A− 1 Y,

unde A -1 este matricea inversă a matricei A. Pentru obtinerea matrice inversă A -1 puteți utiliza funcția MOBR() inclusă în set specificații standard programe Microsoft Excel.

După ce valorile coeficienților cu i sunt determinate folosind funcția (4), valorile funcției interpolate pot fi calculate pentru orice valoare a argumentelor.

Să scriem matricea A pentru tabelul prezentat în Fig. 1, fără a ține cont de rândurile care compactează tabelul.

Fig.2 Matricea sistemului de ecuații pentru calcularea coeficienților polinomului canonic

Folosind funcția MOBR(), obținem matricea A -1 inversă cu matricea A (Fig. 3). După care, conform formulei (9) obținem vectorul coeficienților C = (c 0 , c 1 , c 2 , …, c n ) T prezentat în Fig. 4.

Pentru a calcula valorile polinomului canonic în celula coloanei canonice Y corespunzătoare valorilor x 0, introducem transformarea în următoarea vedere formula corespunzătoare liniei zero a sistemului (6)

=((((c 5	* x 0 +c 4 )x 0 +c 3 )x 0 +c 2 )x 0 +c 1 )x 0 +c 0

C0 +x *(c1 + x *(c2 + x*(c3 + x*(c4 + x* c5 ))))

În loc să scrieți „c i” într-o formulă introdusă într-o celulă tabele Excel, trebuie să stea referință absolută la celula corespunzătoare care conține acest coeficient (vezi Fig. 4). În loc de „x 0” - legătură relativă pe celulă a coloanei X (vezi Fig. 5).

Y canonical(0) al valorii care se potrivește cu valoarea din celula Ylin(0) . La întinderea formulei scrise în celula Y canonică (0), valorile lui Y canonic (i) corespunzătoare punctelor nodale ale originalului trebuie să coincidă și ele

tabele (vezi Fig. 5).

Orez. 5. Diagrame construite folosind tabele de interpolare liniare și canonice

Vedem o comparație a graficelor de funcții construite din tabele calculate folosind formule de interpolare liniare și canonice într-un număr de nodurile intermediare abatere semnificativă a valorilor obținute folosind formule de interpolare liniare și canonice. O judecată mai rezonabilă asupra acurateței interpolării se poate baza pe obținere Informații suplimentare despre natura procesului modelat.

Cel mai simplu și cel mai frecvent utilizat tip de interpolare locală este interpolare liniară. Este asta puncte date (X i , y i) la ( i = 0. 1, ..., n) sunt conectate prin segmente drepte, iar funcția f(X) se apropie o polilinie cu vârfuri în aceste puncte.

Ecuațiile fiecărui segment al liniei întrerupte sunt în general diferite. Deoarece există n intervale ( X i - 1, X i), atunci pentru fiecare dintre ele se folosește ecuația unei drepte care trece prin două puncte ca ecuație a polinomului de interpolare. În special, pentru intervalul i-lea putem scrie ecuația unei drepte care trece prin punctele ( X i -1, y i -1 ) Și ( X i , y i), la fel de

y=a i x+b i , x i-1 xx i

a i =

Prin urmare, atunci când utilizați interpolarea liniară, trebuie mai întâi să determinați intervalul în care se încadrează valoarea argumentului x, apoi să o înlocuiți în formula (*) și să găsiți valoarea aproximativă a funcției în acest moment.

Figura 3-3-Grafic de interpolare liniară.

Rezolvarea unei probleme profesionale

Menținem datele experimentale

ORIGIN:=0 Începutul matricei de date - numărare de la zero

i:=1..6 Numărul de elemente din matrice

Datele experimentale sunt organizate în doi vectori

Să efectuăm interpolarea utilizând funcțiile MathCad încorporate

Interpolare liniară

Lf(x i):=linterp(x,y,x)

Interpolarea pin cubic

CS:=cspline(x,y)

Construirea unei spline cubice folosind date experimentale

Lf(x i):=linterp(x,y,x i)

Interpolare B-spline

Setați ordinea de interpolare. Vectorul u trebuie să aibă (n-1) mai puține elemente decât vectorul X, iar primul element trebuie să fie mai mic sau egal cu primul element X, iar ultimul este mai mare sau egal cu ultimul element al lui x.

BS:=bspline(x,y,u,n)

Construim un B-spline pe baza datelor experimentale

BSf(x i):=(BS, x,y,x i)

Construim un grafic al tuturor funcțiilor de aproximare pe un plan de coordonate.

Figura 4.1-Grafic al tuturor funcțiilor de aproximare pe un plan de coordonate.

Concluzie

În matematica computațională, interpolarea funcțiilor joacă un rol semnificativ, adică. constructie de catre funcţie dată altul (de obicei mai simplu), ale cărui valori coincid cu valorile unei anumite funcții la un anumit număr de puncte. În plus, interpolarea are atât o semnificație practică, cât și teoretică. În practică, se pune adesea problema reconstrucției unei funcții continue din valorile ei tabulate, de exemplu, obținute în cursul unui experiment. Pentru a evalua multe funcții, se dovedește că este eficient să le aproximați prin polinoame sau funcții raționale fracționale. Teoria interpolării este utilizată în construirea și studiul formulelor de cuadratura pentru integrarea numerică, pentru a obține metode de rezolvare a ecuațiilor diferențiale și integrale. Principalul dezavantaj al interpolării polinomiale este că este instabilă pe una dintre cele mai convenabile și mai frecvent utilizate grile - grila cu noduri echidistante. Dacă sarcina permite, această problemă poate fi rezolvată prin alegerea unei rețele cu noduri Chebyshev. Dacă nu putem alege liber nodurile de interpolare sau pur și simplu avem nevoie de un algoritm care nu este prea solicitant în alegerea nodurilor, atunci interpolarea rațională poate fi o alternativă potrivită la interpolarea polinomială.

Avantajele interpolării spline includ viteza mare de procesare a algoritmului de calcul, deoarece o spline este o funcție polinomială pe bucăți și în timpul interpolării, datele sunt procesate simultan pentru un număr mic de puncte de măsurare aparținând fragmentului care este considerat în acest moment. Suprafața interpolată descrie variabilitatea spațială diverse scaleși în același timp este netedă. Această din urmă împrejurare face posibilă analiza directă a geometriei și topologiei suprafeței folosind proceduri analitice

Există cazuri când trebuie să cunoașteți rezultatele unui calcul al funcției în afara zonei cunoscute. Deosebit de relevant această întrebare pentru procedura de prognoză. În Excel există mai multe moduri în care puteți face această operațiune. Să le privim cu exemple specifice.

Metoda 2: Extrapolare pentru grafic

Puteți efectua o procedură de extrapolare pentru un grafic prin trasarea unei linii de tendință.

În primul rând, construim diagrama în sine. Pentru a face acest lucru, utilizați cursorul în timp ce țineți apăsat butonul stâng al mouse-ului pentru a selecta întreaga zonă a tabelului, inclusiv argumentele și valorile funcției corespunzătoare. Apoi, trecând la filă "Introduce", faceți clic pe butonul "Programa". Această pictogramă este situată în bloc "Diagrame" pe centura de scule. Apare o listă Optiuni Disponibile grafice. O alegem pe cea mai potrivită la discreția noastră.

După ce graficul este construit, eliminați linia de argument suplimentar din acesta selectând-o și făcând clic pe butonul Șterge pe tastatura computerului.

În continuare, trebuie să schimbăm diviziunile scării orizontale, deoarece nu afișează valorile argumentelor așa cum avem nevoie. Pentru a face acest lucru, faceți clic Click dreapta mouse-ul peste diagramă și în lista care apare, opriți-vă la valoare „Selectați datele”.

În fereastra de selecție a sursei de date care se deschide, faceți clic pe butonul "Schimbare"în blocul de editare a etichetelor pe axa orizontală.

Se deschide fereastra pentru setarea semnăturii axei. Plasați cursorul în câmpul acestei ferestre, apoi selectați toate datele din coloană "X" fără numele său. Apoi faceți clic pe butonul "BINE".

După ce ne întoarcem la fereastra de selecție a sursei de date, repetăm aceeași procedură, adică facem clic pe butonul "BINE".

Acum graficul nostru este pregătit și putem începe direct să construim o linie de tendințe. Faceți clic pe diagramă, după care va fi activat pe panglică set suplimentar file – „Lucrul cu diagrame”. Trecerea la fila "Aspect"și apăsați butonul "Linie de tendință" in bloc "Analiză". Faceți clic pe element „Aproximație liniară” sau „Aproximație exponențială”.

Linia de tendință a fost adăugată, dar este complet sub linia graficului în sine, deoarece nu am specificat valoarea argumentului către care ar trebui să tindă. Pentru a face acest lucru, faceți clic din nou pe buton. "Linie de tendință", dar acum selectați elementul « Opțiuni suplimentare linii de tendință".

Se deschide fereastra de format de linie de tendință. În capitolul „Opțiuni pentru liniile de tendință” există un bloc de setări "Prognoza". Ca în metoda anterioară, să luăm argumentul pentru extrapolare 55 . După cum putem vedea, până acum graficul are o lungime până la argument 50 inclusiv. Se pare că va trebui să-l prelungim pentru altul 5 unitati. Pe axa orizontală puteți vedea că 5 unități sunt egale cu o diviziune. Deci aceasta este o perioadă. În câmp „Înainte mai departe” introduceți valoarea "1". Faceți clic pe butonul "Închide"în colțul din dreapta jos al ferestrei.

După cum puteți vedea, graficul a fost extins cu lungimea specificată folosind linia de tendință.

Deci, ne-am uitat la cele mai simple exemple de extrapolare pentru tabele și grafice. În primul caz, se utilizează funcția PREDICȚIE, iar în al doilea - o linie de tendință. Dar pe baza acestor exemple, puteți decide mult mai multe sarcini complexe prognoza.