Ukuxazulula uhlelo lwe-equation ku-Microsoft Excel

Amandla wokuxazulula izinhlelo zezibalo ngokuvamile angaba wusizo hhayi ekutadisheni kuphela, kodwa nasekusebenzeni. Ngasikhathi sinye, akuyena wonke umsebenzisi we-PC owaziyo ukuthi i-Excel inezinketho zayo zokuxazulula i-zibalo ezilinganayo. Ake sibheke ukuthi singalisebenzisa kanjani leli thuluzi le processor yetafula ukufeza lomsebenzi ngezindlela ezahlukahlukene.

Izinketho zesinqumo

Noma yikuphi ukulingana kungabhekwa njengokuxazululwa kuphela lapho izimpande zaso zitholakala. I-Excel inezinketho eziningana zokuthola izimpande. Ake sibheke ngamunye wabo.

Indlela yoku-1: indlela ye-matrix

Indlela ejwayelekile yokuxazulula uhlelo olukumugqa olusebenzisa ngamathuluzi e-Excel ukusebenzisa indlela ye-matrix. Kuqukethe ekwakheni i-matrix ye-expression coefficients, bese wenza i-matrix ephambene. Ake sizame ukusebenzisa le ndlela ukuxazulula uhlelo olulandelayo lwezibalo:


14x1+2x2+8x4=218
7x1-3x2+5x3+12x4=213
5x1+x2-2x3+4x4=83
6x1+2x2+x3-3x4=21

1. Sigcwalisa i-matrix ngezinombolo, okuyi-coefficients ye-equation. Lezi zinombolo kufanele zihlelwe ngokulandelana ngokulandelana, kucatshangelwa indawo yempande ngayinye ezihambelana ngayo. Uma ngenkulumo eyodwa eyodwa yezimpande ingekho, khona-ke kuleli cala i-coefficient ibhekwa ilingana no-zero. Uma ukufakwa kungakhonjisiwe ku-equation, kepha kukhona impande ehambisanayo, khona-ke kubhekwa ukuthi lokho kwenziwa 1. Thatha ithebula elenziwe njengevithamini A.



2. Ngokwehlukana, bhala amanani ngemuva kwesibonakaliso esilinganayo. Bakhombise ngegama labo elijwayelekile, njengev veter B.



3. Manje, ukuthola izimpande ze-equation, okokuqala, sidinga ukuthola i-matrix engafaniyo yaleyo ekhona. Ngenhlanhla, i-Excel ine-opharetha ekhethekile eyenzelwe ukuxazulula le nkinga. Ubizwa MOBR. Ine-syntax elula elula:

   = MOBR (uhlu)

   Ukuxabana Array - Lokhu, empeleni, ikheli lethebula lomthombo.

   Ngakho-ke, sikhetha eshidi isifunda samaseli angenalutho, alingana ngosayizi kuya kububanzi be-matrix yoqobo. Chofoza inkinobho "Faka umsebenzi"eseduze komugqa wamafomula.



4. Ukuqala Umsebenzi Wizards. Iya esigabeni "Izibalo". Kuluhlu olubonakala, funa igama MOBR. Ngemuva kokuthi itholakale, ikhethe bese uchofoza inkinobho "Kulungile".



5. Iwindi lokuphikisana ngomsebenzi liyaqala MOBR. Inenkambu eyodwa kuphela enanini lokuphikisana - Array. Lapha udinga ukucacisa ikheli lethebula lethu. Ngalezi zinhloso, setha ikhesa kule nkambu. Ngemuva kwalokho sibamba phansi inkinobho yegundane kwesobunxele bese ukhetha indawo eshidini lapho kukhona khona matrix. Njengoba ukwazi ukubona, imininingwane ekuxhumaniseni kokubekwa ifakwa ngokuzenzakalela enkundleni yewindi. Ngemuva kokuthi lo msebenzi usuphothuliwe, okusobala kakhulu kuzoba ngokuchofoza inkinobho "Kulungile"kepha ungajahi. Iqiniso ngukuthi ukuchofoza le nkinobho kufana nokusebenzisa umyalo Ngena. Kepha uma usebenza nama-arrays ngemuva kokuqeda ukufakwa kwefomula, ungachofozi inkinobho Ngena, futhi wenze iqoqo lezinqamuleli zekhibhodi I-Ctrl + Shift + Faka. Yenza lo msebenzi.



6. Ngakho-ke, ngemuva kwalokhu, uhlelo lwenza izibalo futhi, ekuphumeni endaweni ekhethwe ngaphambilini, sinomatrix ophikisana nalolo olunikeziwe.



7. Manje sizodinga ukuphindaphinda i-matrix ephambene nge-matrix B, equkethe ikholomu eyodwa yamanani abekwe ngemuva kwesibonakaliso okulinganayo ngamabinzana. Ukuphindaphinda amatafula e-Excel kukhona umsebenzi ohlukile obizwa IMINININGWANE. Lesi sitatimende sine-syntax elandelayo:

   = IMNYANGO (Array1; Array2)

   Sikhetha uhla, kithi, oluqukethe amaseli amane. Okulandelayo, gijimani futhi Isici Wizardngokuchofoza isithonjana "Faka umsebenzi".



8. Esigabeni "Izibalo"yethulwe Umsebenzi Wizards, khetha igama MUMNOZH bese uchofoza inkinobho "Kulungile".



9. Iwindi lokuphikisana ngomsebenzi lisebenza. IMINININGWANE. Ensimini "I-Array1" faka izixhumanisi ze-matrix yethu ephambene. Ukuze wenze lokhu, njengokokugcina, setha ikhesa endle bese inkinobho yenkinobho yesinxele icindezelwe khetha ithebula elihambisanayo nesikhombisi. Senza isenzo esifanayo ukufaka izixhumanisi emkhakheni Array2, kuphela kulokhu khetha amanani wekholomu B. Ngemuva kokuba kwenziwe lezi zinto ezingenhla, futhi asiphuthumi ukucindezela inkinobho "Kulungile" noma ukhiye Ngena, bese uthayipha inhlanganisela yokhiye I-Ctrl + Shift + Faka.



10. Ngemuva kwalesi senzo, izimpande ze-equation ziboniswa kuseli elikhethwe ngaphambilini: X1, I-X2, X3 futhi X4. Azohlelwa ngokulandelana. Ngakho-ke, singasho ukuthi sixazulule lolu hlelo. Ukuze uqinisekise ukunemba kwekhambi, kwanele ukufaka lezi zimpendulo ohlelweni lokubonisa lwangempela esikhundleni sezimpande ezihambisanayo. Uma ukulingana kubhekwa, khona-ke lokhu kusho ukuthi uhlelo olwethuliwe lwezibalo luxazululwa kahle.

Isifundo: I-matrix ehlukile ku-Excel

Indlela 2: ukukhethwa kwamapharamitha

Indlela yesibili eyaziwayo yokuxazulula uhlelo lwezibalo ku-Excel isebenzisa indlela yokukhetha amapharamitha. Umnyombo wale ndlela ukusesha okuhlukile. Lokho wukuthi, kususelwa kumphumela owaziwayo, sifunela ukuphikisana okungaziwa. Masisebenzise i-quadratic equation njengesibonelo

3x ^ 2 + 4x-132 = 0

1. Yamukela inani x ngokulinganayo 0. Sibala inani elihambisana nalo f (x)ngokusebenzisa ifomula elandelayo:

   = 3 * x ^ 2 + 4 * x-132

   Esikhundleni senani "X" faka ikheli lekheli lapho inombolo itholakala khona 0othathwe yithina x.



2. Iya kuthebhu "Idatha". Chofoza inkinobho "Yini uma uhlaziya". Le nkinobho itholakala kubambo kubhokisi lamathuluzi. "Sebenza nedatha". Uhlu olwehlayo luvula. Khetha isikhundla kukho "Ukukhetha ipharamitha ...".



3. Iwindi lokukhetha ipharamitha liqala. Njengoba ubona, iqukethe izinkambu ezintathu. Ensimini Hlala esitokisini chaza ikheli leseli lapho ifomula likhona f (x)ibalwe ngathi ngaphambili. Ensimini "Inani" faka inombolo "0". Ensimini "Amanani Aguqukayo" chaza ikheli leseli lapho inani litholakala khona xeyamukelwa ngaphambilini ngathi 0. Ngemuva kokuqeda lezi zinyathelo, chofoza inkinobho "Kulungile".



4. Ngemuva kwalokho u-Excel uzokwenza ukubala ngokukhetha ipharamitha. Lokhu kuzobikwa ngewindows yolwazi evelile. Kuyo, chofoza inkinobho "Kulungile".



5. Umphumela wokubala impande ye-equation uzoba esitokisini esikubekele inkambu "Amanani Aguqukayo". Okwethu, njengoba sibona, x izolingana 6.

Lo mphumela ungabhekwa nangokuthi ufake leli nani endaweni yokubiza ukuze lixazululwe esikhundleni senani x.

Isifundo: Ukukhetha ipharamitha ku-Excel

Indlela yesi-3: Indlela yeCramer

Manje ake sizame ukuxazulula uhlelo lwezibalo kusetshenziswa indlela yeCramer. Isibonelo, thatha uhlelo olufanayo olwalusetshenziswe ngaphakathi Indlela 1:


14x1+2x2+8x4=218
7x1-3x2+5x3+12x4=213
5x1+x2-2x3+4x4=83
6x1+2x2+x3-3x4=21

1. Njengasekuqaleni kwendlela yokuqala, sibhala i-matrix A okuvela ku-coefficients yezibalo etafuleni B kusuka kumanani alandela isibonakaliso okulinganayo.



2. Okulandelayo, senza amatafula amane amanye. Ngamunye wabo uyikhophi ye-matrix. A, la makhophi kuphela anekholomu eyodwa athathwe yithebula B. Ithebula lokuqala linekholamu yokuqala, ithebula lesibili linesibili, njll.



3. Manje sidinga ukubala izincazelo zawo wonke lamatafula. Uhlelo lwezibalo luzoba nezisombululo kuphela uma ngabe bonke abangabaluleki benenani ngaphandle kwe-zero. Ukubala leli nani, i-Excel futhi inomsebenzi ohlukile - KUPHELILE. I-syntax yalesi sitatimende imi ngalendlela elandelayo:

   = MOPRED (uhlu)

   Ngakho-ke, njengomsebenzi MOBR, okuwukuphela kwengxabano okuyireferensi etafuleni elicubungwayo.

   Ngakho-ke, khetha iseli lapho izoboniswa khona isibonakaliso se-matrix yokuqala. Bese uchofoza inkinobho ejwayelekile kusuka ezindleleni ezedlule "Faka umsebenzi".



4. Iwindows liyasebenza Umsebenzi Wizards. Iya esigabeni "Izibalo" futhi phakathi kohlu lwe-opharetha siqhakambisa igama KUPHELILE. Ngemuva kwalokho chofoza inkinobho "Kulungile".



5. Iwindi lokuphikisana ngomsebenzi liyaqala KUPHELILE. Njengoba ubona, inenkambu eyodwa kuphela - Array. Kule nkambu sifaka ikheli le-matrix yokuqala eguquliwe. Ukuze wenze lokhu, setha ikhesa endle, bese ukhetha ibanga le-matrix. Ngemuva kwalokho chofoza inkinobho "Kulungile". Lo msebenzi ukhombisa umphumela kuseli elilodwa, hhayi uhlu oluthile, ngakho-ke, ukuthola ukubalwa, awudingi ukuphendukela ekucindezeleni inhlanganisela yokhiye I-Ctrl + Shift + Faka.



6. Umsebenzi ubala umphumela bese uwukhombisa kuseli elikhethwe kuqala. Njengoba sibona, kithi okucacayo ukuthi -740, okungukuthi, ayilingani no-zero, osifanele.



7. Ngokufanayo, sibala izincazelo zamanye amatafula amathathu.



8. Esigabeni sokugcina, sibala inani le-matrix eyinhloko. Inqubo yenzeka ngokuya nge-algorithm efanayo. Njengoba ubona, isichasiselo setafula eliyinhloko nayo siyingqayizivele, okusho ukuthi i-matrix ibhekwa njengengashabalali, okungukuthi, uhlelo lwezibalo lunezisombululo.



9. Manje yisikhathi sokuthola izimpande ze-equation. Impande ye-equation izolingana nesilinganiso sokuchazwa kwe-matrix ehambisanayo eguqululiwe kwisazi setafula eliphambili. Ngakho-ke, ukuhlukanisa ngokulandelana nakho konke ukunqunyelwa kwamatikuletsheni ngenombolo -148, okuyisincazelo setafula lasekuqaleni, sithola izimpande ezine. Njengoba ukwazi ukubona, alingana namanani 5, 14, 8 futhi 15. Ngakho-ke zifana ncamashi nezimpande esazithola sisebenzisa i-matrix ephambene ngaphakathi indlela 1, okuqinisekisa ukunemba kwekhambi lohlelo lwezibalo.

Indlela 4: Indlela yeGauss

Uhlelo lwezibalo lungaxazululwa ngokusebenzisa indlela yeGauss. Isibonelo, thatha uhlelo olulula kakhulu lwezibalo kusuka kokungaziwayo abathathu:


14x1+2x2+8x3=110
7x1-3x2+5x3=32
5x1+x2-2x3=17

1. Siphinde sibhale ama-coefficients etafuleni A, kanye nemigomo yamahhala etholakala ngemuva kwesibonakaliso okulinganayo - etafuleni B. Kepha kulokhu, sizosondeza womabili lamatafula, ngoba sizowadinga ukuthi asebenze ngokuzayo. Isimo esibalulekile ukuthi esitokisini sokuqala se-matrix A inani lalingaziwa. Ngaphandle kwalokho, kufanele uhlele kabusha imigqa ezindaweni.



2. Kopisha umugqa wokuqala wamatric amabili axhunyiwe kulayini ongezansi (ukucaciseleka, weqa umugqa owodwa). Esitokisini sokuqala, esitholakala emgqeni siphansi ngisho nangaphambilini, sifaka ifomula elandelayo:

   = B8: E8- $ B $ 7: $ E $ 7 * (B8 / $ B $ 7)

   Uma uhlelele umatikuletsheni ngendlela ehlukile, khona-ke amakheli amaseli wefomula azoba nencazelo ehlukile, kepha ungawabala ngokuwaqhathanisa namafomula nezithombe ezinikezwe lapha.

   Ngemuva kokuthi ifomula ifakiwe, khetha lonke ulayini wamaseli bese ucindezela inhlanganisela yokhiye I-Ctrl + Shift + Faka. Ifomula lamalungu afanayo lizosetshenziswa emugqeni futhi lizogcwaliswa ngamanani. Ngakho-ke, sikhiphe emgqeni wesibili wokuqala, siphindaphindwe ngesilinganiso se-coefficients yokuqala yezinkulumo ezimbili zokuqala zohlelo.



3. Ngemuva kwalokho, kopisha ngentambo evelayo bese unamathisela emugqeni ongezansi.



4. Khetha imigqa emibili yokuqala ngemuva komugqa olahlekile. Chofoza inkinobho Kopishaetholakala kubambo kuthebhu "Ikhaya".



5. Weqa umugqa ngemuva kwerekhodi lokugcina ephepheni. Khetha iseli lokuqala emugqeni olandelayo. Qhafaza kwesokudla. Kumenyu yokuqukethwe evula, hambisa isikhombisi ku "Faka okhethekile". Kuluhlu olungeziwe olwethuliwe, khetha isikhundla "Amanani".



6. Kumugqa olandelayo, faka ifomula yamalungu afanayo. Iyasusa kusuka kumugqa wesithathu iqembu ledatha langaphambilini lomugqa wesibili, liphindaphindwe ngesilinganiso sokukwaneleka kwesibili kwemigqa yesithathu neyesibili. Kithina, ifomula izoba nefomu elilandelayo:

   = B13: E13- $ B $ 12: $ E $ 12 * (C13 / $ C $ 12)

   Ngemuva kokufaka ifomula, khetha wonke umugqa bese usebenzisa isinqamuleli sekhibhodi I-Ctrl + Shift + Faka.



7. Manje kufanele wenze ukugijima okubuyela emuva ngokusho kwendlela yeGauss. Weqa imigqa emithathu kwirekhodi lokugcina. Kumugqa wesine sifaka ifomula yamalungu afanayo:

   = B17: E17 / D17

   Ngakho-ke, sihlukanisa umugqa wokugcina obalwe yithina ngokwakhiwa kwawo kwesithathu. Ngemuva kokuthayipha ifomula, khetha wonke umugqa bese ucindezela inhlanganisela yokhiye I-Ctrl + Shift + Faka.



8. Sikhuphuka umugqa bese sifaka ifomula elandelayo elandelayo:

   = (B16: E16-B21: E21 * D16) / C16

   Sicindezela isinqamuleli sekhibhodi ejwayelekile ukusebenzisa ifomula yamalungu afanayo.



9. Siphakamisa umugqa owodwa ngaphezulu. Kuyo sifaka ifomula lamalungu afomu alandelayo:

   = (B15: E15-B20: E20 * C15-B21: E21 * D15) / B15

   Phinda ukhethe umugqa wonke bese usebenzisa isinqamuleli sekhibhodi I-Ctrl + Shift + Faka.



10. Manje sibheka izinombolo ezivelile kwikholomu yokugcina yebhulowu yokugcina yemigqa esiyibalile ngaphambili. Lezi zinombolo (4, 7 futhi 5) kuzoba izimpande zalolu hlelo lwezibalo. Ungakuqinisekisa lokhu ngokubamiselela esikhundleni samanani X1, I-X2 futhi X3 ekukhulumeni.

Njengoba ubona, e-Excel, uhlelo lwezibalo lingaxazululwa ngezindlela eziningi, ngayinye enezinzuzo zalo kanye nobubi bayo. Kepha zonke lezi zindlela zingahlukaniswa ngombandela ngamaqembu amabili amakhulu: i-matrix nokusebenzisa ithuluzi lokukhetha ipharamitha. Kwezinye izimo, izindlela ze-matrix azihlali zilungele ukuxazulula inkinga. Ikakhulu, lapho isibonakaliso se-matrix silingana no-zero. Kwezinye izimo, umsebenzisi ngokwakhe ukhululekile ukunquma ukuthi iyiphi indlela acabanga ukuthi angayisebenzisa ngokwengeziwe.