Interpolation a Excel: Features, da kuma hanyoyin da misalai,

Shirin kwamfutarka na Excel ba kawai damar ba da lissafi ba da sauri, amma har ma don warware matsaloli masu wuya. Alal misali, za'a iya amfani dashi don yin samfurin lissafin ilmin lissafi bisa ga saiti na dabi'u masu ganewa na aiki, ciki har da gano ƙimar tsaka-tsakin ayyuka ta hanyar interpolation. A cikin Excel, saboda wannan dalili, ana ba da kayan aiki daban-daban, wanda wannan labarin zai koyar.

Interpolation Hanyar: abin da yake da shi?

A cikin lissafin lissafin lissafi, hanyar da ake gano maƙasudin maras tabbas na aikin Y (X) ta hanyar da aka sani da aka sani an kira wannan hanyar.

A interpolation aiki Y (X) za a iya za'ayi kawai ga wadanda daga ta muhawara, wanda suke ciki tazara [X _0, X _n], irin wannan cewa dabi'u na Y (X ₀₎ da kuma Y (X _n).

Idan X ba ya kasance a [X _0, X _n], yana yiwuwa su yi amfani da extrapolation Hanyar.

A gargajiya halitta interpolation matsalar ake bukata a sami wani m nazari aiki φ (X), a cikin abin da dabi'u a nodal maki X _i yana da guda dabi'u Y (X _i) na asali tebur m. E. Followers yanayin _{φ (X i) = Y i} (i = 0,1,2, ..., n).

Haɗin linzamin linzami a Excel

A cikin sanarwa mai mahimmanci na tebur daga Microsoft, akwai mai amfani mai amfani, PRESCASE.

Yi la'akari da bayanan da aka sanya a cikin tebur a kasa.

Shafin na farko yana dauke da muhawarar x, kuma a cikin sashin na biyu sunaye daidai da wasu aikin linzami f (x). Da za mu so mu san darajar gardama x = 28. Don yin wannan:

• Yi amfani da kowane nau'i maras amfani a cikin takardar mai sarrafawa, inda sakamakon zai fito daga ayyukan da aka yi, misali C1;
• Danna kan gunkin "fx" ("Saka aiki"), wanda ke hagu zuwa hagu na hanyar layi;
• A cikin taga na "Masters of Functions" suna shigar da jinsi "Lissafi";
• Nemo mai aiki "PRESCASE" kuma danna "Ok".

Akwai filayen 3 a cikin muhawarar muhawara. A cikin farko shigar da darajar gardama daga keyboard (a cikin wani takamaiman aikin wannan shine 28). Domin cika filin "Akanan lambobi _ y", danna kan gunkin tare da arrow ta gefen hagu na matakan da ya dace kuma zaɓi wurin da ya dace akan takardar. A cikin wani batu, wannan ɓangare na shafi na B tare da adiresoshin daga B2: B7.

Hakazalika, cika filin "Lambobi sananne _ x" kuma danna maballin "Ok".

A sakamakon haka, darajar a cikin sel mai haske C1 shine 176, wanda shine sakamakon hanyar interpolation.

Hanyar zane: shiri

Ƙungiya tsakanin Excel, misali wanda aka gabatar a sama, ba hanyar hanya kaɗai ba ne kawai don gano abubuwan da ba a sani ba na aikin Y (X) daga wani salo mai mahimmanci wanda aka sani. Musamman, ana iya amfani da hanyar zane. Zai iya zama da amfani idan ba'a ƙayyade darajar aikin aiki a cikin teburin zuwa ɗaya daga cikin muhawara ba, kamar yadda aka gabatar a ƙasa (duba tantanin halitta tare da adireshin B9).

Ƙulla dangantaka a cikin Excel a cikin wannan yanayin ta fara da mãkirci. Don yin wannan:

• A cikin "Saka" shafin rarraba shafin yanar gizo;
• A cikin akwatin kayan aiki "Shirye-shiryen" zaɓi "Shafin" icon;
• A cikin jerin da ya bayyana, zaɓi abin da yafi dacewa don maganin wani aiki na musamman.

Tun da ƙwayar B9 ta komai, zane-zane ya karye. Bugu da ƙari, yana da wani ƙarin layi na X, wanda bai zama dole ba, kuma a kan iyaka a fili, maimakon ma'anar gardama, an tsara abubuwa a cikin tsari.

Hadawa a cikin Excel: fassarar hoto

Za mu yi hulɗa da aiki na jadawalin. Don yin wannan, zaɓi hanyar zane mai tsabta kuma share shi ta latsa maɓallin Delete, wanda ke kan keyboard.

Sa'an nan:

• Sanya jirgin saman wanda aka samo hoton ɗin;
• A cikin mahallin menu zaɓi maɓallin "Zaɓi bayanai ...";
• A cikin maɓallin "Zaɓi Bayanin Bayanin Bayanai" a cikin maɓallin dama, danna "Canji";
• Danna kan gunkin tare da arrow ta gefen dama na filin "Axis Signature Range";
• Sanya layin A2: A11;
• Danna maballin "OK";
• Bugu da ƙari, an buɗe maɓallin "Zaɓin bayanin bayanai";
• Danna kan maballin "Hannun da ke ɓoye" a cikin kusurwar hagu na hagu;
• A cikin layi "Nuna kullun jakar" an canja wurin canzawa zuwa "Layin" kuma danna "Ok";
• Tabbatar da waɗannan ayyuka a daidai wannan hanya.

Idan an yi duk abin da ya dace daidai, za a share hutu, kuma ta hanyar lalata siginan kwamfuta a kan maƙasudin da ake so a cikin jadawali za ka iya ganin dabi'un daidai da gardama da aiki.

Yin amfani da aikin LP na musamman

Yanzu da ka san yadda za a yi hulɗa a cikin Excel ta yin amfani da hanyar ɗaukar hoto ko tare da taimakon mai amfani na PRESCASE, warware matsalolin da dama da za a yi maka ba zai zama babban abu ba. Duk da haka, ba haka ba ne. Mai sarrafawa na tebur daga Microsoft yana samar da yiwuwar gano matakan da aka sani ba na aiki ta amfani da aikin ND.

Ka yi la'akari da cewa an riga an gina hoton, an shigar da saitunan saitunan daidai akan shi. Bari muyi ƙoƙarin rufe wannan rata. Don yin wannan:

• Zaɓi sel a cikin tebur inda aikin aikin ya ɓace;
• Zaɓi gunkin "Saka aiki";
• A cikin "Wizard Aikin" a cikin "Gurbin" Gurbin ya sami kirtani "Jerin jerin sunayen haruffa" (a cikin wasu sigogi na mai sarrafawa "Bincika abubuwan kima da dabi'u");
• Danna kan shigarwar "ND" kuma latsa maɓallin "OK".

Bayan haka, kuskuren kuskure "# N / D" ya bayyana a tantanin halitta B9. Duk da haka, ana katse katsewar jimlar ta atomatik.

Kuna iya sauƙaƙe: daga cikin keyboard a cell B9 da haruffan "# N / D" (ba tare da sharudda ba).

Haɗin gizon Bilinear

Da'irar matsalolin da za'a yiwu don yin amfani da samfurin gyare-gyare ta hanyar ayyuka na daya madaidaicin an iyakance shi. Sabili da haka, yana da mahimmanci don la'akari da yadda ake amfani dashi a cikin maɓallin Excel. Misalai na iya zama daban. Alal misali: akwai tebur (duba ƙasa).

Ana buƙatar lissafin iska a cikin mita 300 m a tsawon 25 m.

Ƙara sabon shigarwar zuwa tebur kamar yadda aka nuna a cikin adadi (duba ƙasa).

Kamar yadda ka gani, shi kara zuwa cell tsawo da kuma span a J1 da J2.

By baya bi da bi canzawa "tattara" megaformulu zama dole a sami iska matsa lamba a takamaiman sigogi. Don yin wannan:

• kwafan da rubutu na dabara da cell wuri a cikin cell J17 J19.
• ana maye gurbin a cikin dabara koma zuwa J15 zuwa J15 cell darajar: J7 + (J8-J7) * J11 / J13.
• Maimaita wadannan matakai sai da ake so dabara.

Amfani spline

Previous Hanyar ne quite amfani da tsauraran matakan, don haka a wasu lokuta fin so spline interpolation. Excel A yanayinsu ya kunshi gano interpolation aiki φ (X) bisa ga dabarbari da irin for daban-daban subsets da shaida. Bugu da ari kwashe docked dabi'u φ (x) Irinta a kan iyakar dabi'u na muhawara na kowane daga cikin subsets. A Excel domin wannan dalili akwai na musamman ayyuka da yiwu rubuta macros zuwa VBA. Duk da haka, da suka dole ne a halitta don takamaiman aiki, don haka su bincike kullum ba ya sa hankali.

Yanzu da ka san yadda za a rubuta wani dabara a Excel dual interpolating daidai ko zan samu ba a sani ba tamanin da mikakke aiki ta hanyar da gina-in aiki ko graphics. Muna fatan cewa wannan bayani zai taimake ku a warware da yawa m matsaloli.