Interpolation muna Excel: Features, maitirwo ezvinhu mienzaniso,

Excel table processor haibvumiri kukurumidza kuita zviyero zvakasiyana, asiwo kugadzirisa zvinenge zvakaoma mabasa. Semuenzaniso, inogona kushandiswa kuita mashematical modeling pamusana pegadziriro yehutauriri hwebasa, kusanganisira kuwana huwandu hwepakati pemabasa kuburikidza nekushandura. MuExcel, nekuda kwechinangwa ichi, maturusi akasiyana-siyana anopiwa, izvo zvinyorwa zvichatidzidzisa.

Nzira yekuyananisa: chii ichocho?

Mukunyora kwemasvomhu, nzira yekuwana miviri isingazivikanwi inozivikanwa yebasa Y (X) nedhete discrete yezvinozivikanwa kare inonzi iyi nzira.

Kupindirana kwebasa Y (X) kunogona kuitwa kunze kwemashoko aya ari mukati menguva [X ₀ , X _n ], yakadaro kuti maitiro Y (X ₀ ) ne Y (X _n ) anozivikanwa.

Kana X isiri ye [X ₀ , X _n ], ipapo nzira yekuwedzera inogona kushandiswa.

Muchikoro chekugadzirisa dambudziko rekushandura, zvinotarisirwa kuti uwane shanduro yekufungidzira inoshandiswa φ (X) iyo inofanirwa pane node inotenderera X inopindirana nemitengo Y (X _i ) yefurafura yepakutanga, kureva, mamiriro φ (X _i ) = Y _i (i = 0,1,2, ..., n).

Mutsara unopindirana muExcel

Mune fesheni yakakurumbira kudhindwa kubva kuMicrosoft, pane anoshanda chaizvo, PRESCASE.

Funga nezvekuiswa kwedheta iri pasi apa.

Irosi yekutanga ine mhedziso x, uye muchikamu chechipiri iyo inofanirwa nehutano hweimwe hutano huno f (x). Ngatitii tinoda kuziva kukosha kwepikano x = 28. Kuita izvi:

• Gadzirira chero sero risina chinhu pane pepa retafura yekugadzira, apo chigumisiro chichabva pane zvaitwa, seC1;
• Dzvanya pa "icon" fx "(" Insert function "), iri kuruboshwe rwemuchina wepepa;
• Muhwindo re "Masters of Functions" vanopinda mu "Mathematical";
• Tsvaga mutero "PRESCASE" uye chengeta pa "OK".

Kune mimwe mitambo muwindo rekuita. Pakutanga kupinda mukoshi yemakakatanwa kubva pabhodibhodhi (mune rimwe basa iri iri 28). Kuti uzadze "Zvose zvinozivikanwa _ y" munda, chengetera paikoni nemuorivhi mutsvuku kuruboshwe rwemahwindo akaenzana uye sarudza nzvimbo inowirirana pane pepa. Mune imwe nyaya, iyi chikamu chekoramende B neatari kubva paB2: B7 mararamiro.

Saizvozvowo, gadzai mundima "Mazivikanwi _ x" uye chengetedza pane "OK".

Somugumisiro, kukosha kwechikamu chakasimbiswa C1 ndeche 176, iyo ndiyo mhedziso yekugadzirisa maitiro.

Nzira yekufananidzira: kugadzirira

Kuongororwa mukati muExcel, muenzaniso weiwo unoratidzwa pamusoro, hausi iyo nzira chete yekuwana nayo maitiro anozivikanwa asingazivikanwi ebasa Y (X) kubva kune imwe sarudzo yevakatozivikanwa kare. Kunyanya, nzira yekufananidzira inogona kushandiswa. Inogona kubatsira kana iyo inobatanidza basa inokosha isati yasarudzwa mutafura kune imwe yenyaya, sezvinotaurwa pane iri pasi (ona sero nekero B9).

Kupindirana muExcel munyaya ino inotanga nechinangwa. Kuita izvi:

• Mu "Insert" tab ipai tabular range;
• Mubhokisi rekushandisa "Diagram" sarudza "Girafu" icon;
• Mutsamba inooneka, sarudza iyo inonyatsokodzera kugadzirisa kweimwe basa.

Sezvo sero B9 risina chinhu, grafu yakaputsika. Uyezve, ine chinyorwa chechina X, icho chisingadi, uye pane imwe nzvimbo yakanyanyisa, panzvimbo yemakakatanwa akakwirira, zvinhu zvakarongedzwa.

Kuongororwa mukati muExcel: chirongwa chinoratidza

Tichatarisana nekushandurwa kwemafirimu. Kuti uite izvi, sarudza rwonzi rwakatsetseka rwonzi uye rwuribvise nekudhonza Bhodhi rekunatsa, iri pabhodibhodhi.

Ipapo:

• Sarudza ndege iyo iyo girafu iripo;
• Muchirevo chemukati menyu sarudza bhatani "Sarudza data ...";
• Mu "Sarudza Dhiyabhorosi" yewindo mubhokisi rakarurama, chengeta "Shanduka";
• Dzvanya pamufananidzo wacho nemuorivhi mutsvuku kusvika kurudyi rwe "Axis Signature Range" munda;
• Sarudza rutivi rweA2: A11;
• Dzvanya pabhokisi "OK";
• Zvakare, iyo "Sarudza data source" window inovhurwa;
• Dzvanya pabhokisi "Masero akavanzwa uye asina chinhu" muchekeri rezasi kuruboshwe;
• Mutsara "Ratidza masero asina chinhu" iyo shanduko inotamira kune "Line" nzvimbo uye chengeta "OK";
• Iva nechokwadi chezviito izvi nenzira imwecheteyo.

Kana zvose zvikaitwa zvakanaka, kuputsa kuchabviswa, uye nekusvitsa murambi pane chinangwa che grafu iwe unogona kuona maonero akafanana ehutano uye basa.

Kushandisa nhengo yeLP yakakosha

Iye zvino zvaunoziva kuita interpolation mu Excel uchishandisa nzira yekufananidza kana nekubatsira kweAPERCASE operator, kugadzirisa matambudziko akawanda anoshanda kwauri hausi kuzoita basa guru. Zvisinei, izvi hazvisi zvose. Purogiramu yefurafura kubva kuMicrosoft inopa mukana wekuwana chinhu chisingazivikanwi chebasa uchishandisa ND function.

Ngatitii igrafu yakatovakwa, zvinyorwa zvakakodzera zvakasimbiswa pairi. Ngatiedzei kuvhara chikamu. Kuita izvi:

• Sarudza sero iri patafura iyo basa rinokosha risina kuwanikwa;
• Sarudza "Insert function" icon;
• Mu "Function Wizard" mu "Zvirongwa" window inowana tambo "Zvose zvakanyora zvinyorwa zvepafabheti" (mune dzimwe shanduro dzepurojekiti "Kuongorora zvinhu uye maitiro");
• Dzvanya pa "ND" kupinda uye shandisa "OK".

Pashure pacho, kukanganisa kunokosha "# N / D" inowanikwa muchitokisi B9. Zvisinei, kukanganisa kwegrafu kunotsvaga kubviswa.

Iwe unogona kuita kunyange nyore: kubva pakhizha muchitokisi B9 mavara "# N / D" (pasina zvinyorwa).

Bilinear interpolation

Denderedzwa yematambudziko iyo inogona kushandiswa kuenzanisira kuburikidza nemabasa ezvimwe zvinokonzerwa ndeyekuti pane zvishoma. Saka, zvinonzwisisika kutarisa kuti kududzirwa kwepachiviriro inowanikwa muExcel kunoshandiswa sei. Mienzaniso inogona kunge yakasiyana chaizvo. Somuenzaniso: pane tafura (ona pasi apa).

Inotarisirwa kuverenga mhepo inopisa pamakiromita 300 m inenge mamita 25 m.

Wedzera zvinyorwa zvitsva patafura sezvakaratidzwa mumufananidzo (ona pasi apa).

Sezvaunogona kuona, masero ekukwirira uye kupera muJ1 neJ2 akawedzerwa pairi.

Nechekudzoka shure kutevedzana kunotsiviwa, "mega formula" yaidiwa kuwana mhepo inoshandiswa pamitemo yakananga "inounganidzwa". Kuita izvi:

• Nyora rugwaro rwemugadziro kubva muchitokisi nekero J17 kune cell J19;
• Dzorera iyo kutaurwa kuna J15 nehuwandu mujeri J15: J7 + (J8-J7) * J11 / J13;
• Dzokorora zviito izvi kusvikira chimiro chinodiwa chinowanikwa.

Achishandisa spline

Nzira yakapfuura inenge ichioma, saka pane dzimwe nguva spline interpolation inoshandiswa. MuchiExcel, chinangwa chayo chiri mukuwana basa rekushandura φ (X) kubva pamagariro echimiro chimwechete kune zvinyorwa zvakasiyana zvepikicha. Zvadaro, maitiro φ (X) uye zvigadzirwa zvaro zvakabatanidzwa pamiganhu yezvikamu zvepfungwa dzemumwechete wezvikamu. MuExcel, nekuda kweizvi, mabasa anokosha anopiwa, uye zvinokwanisika kunyora macros paVBA. Zvisinei, inofanira kusikwa kune rimwe basa, saka kudzidza kwavo muhuwandu huzhinji hahuna musoro.

Iye zvino unoziva kuti unyore sei double interpolation formula mu Excel zvakakodzera, kana kuwana huno husingazivikanwe hwebasa rakasvibiswa kuburikidza nezvivakwa zvakagadzirwa mukati kana girafu. Tinovimba kuti ruzivo urwu ruchakubatsira mukugadzirisa matambudziko akawanda anoshanda.