Magance tsarin daidaitawa a Microsoft Excel

Toarfin warware tsarin daidaituwa sau da yawa yana da amfani ba kawai a cikin nazarin ba, har ma a aikace. A lokaci guda, ba kowane mai amfani da PC ya san cewa Excel yana da zaɓuɓɓukan kansa don warware daidaitattun layi. Bari mu bincika yadda za ayi amfani da wannan kayan aikin kayan aikin tebur don cim ma wannan aikin ta hanyoyi daban-daban.

Zaɓuɓɓukan yanke shawara

Ana iya ɗauka kowane daidaituwa kawai idan an samo tushen sa. Excel yana da zaɓuɓɓuka da yawa don samo tushen. Bari mu kalli kowane ɗayansu.

Hanyar 1: hanyar matrix

Hanya mafi gama gari don warware tsarin daidaitawa tare da kayan aikin Excel shine amfani da hanyar matrix. Ya ƙunshi ƙirƙirar matrix na bayanin coefficients, sannan kuma a samar da matrix mara damuwa. Bari muyi kokarin amfani da wannan hanyar dan magance tsarin daidaitattun abubuwa:


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

1. Mun cika matrix da lambobi, waɗanda sune masu daidaita abubuwan daidaitawa. Wadannan lambobin ya kamata a tsara su ta hanyar tsari, cikin la'akari da wurin da kowane tushe yake magana da shi. Idan a cikin magana guda ɗaya ɗayan tushen ba ya nan, to a wannan yanayin ana ganin cewa mahaɗin daidai yake da sifili. Idan ba a nuna mai aiki daidai ba a cikin daidaitawa, amma akwai tushen mai daidaitawa, to ana la’akari da cewa abu mai aiki shine 1. Nuna teburin da aka fito dashi azaman vector A.



2. Na dabam, rubuta dabi'u bayan daidai alamar. Bayyana su ta hanyar su na yau da kullun, azaman vector B.



3. Yanzu, don samo tushen daidaituwa, da farko, muna buƙatar nemo matsex ɗin juzu'in na wanda yake. Abin farin ciki, Excel yana da ma'aikacin musamman wanda aka tsara don warware wannan matsalar. An kira shi MOBR. Ya na da sauki sauki wurin rubutu:

   = MOBR (tsararru)

   Hujja Shirya - Wannan, hakika, adireshin tebur mai tushe.

   Don haka, zamu zabi a kan takardar yanki na sel marasa komai, wanda yayi daidai da girman girman kewayon matrix na asali. Latsa maballin "Saka aikin"wanda yake kusa da layin tsari.



4. Farawa Wizards na Aiki. Je zuwa rukuni "Ilmin lissafi". A lissafin da ya bayyana, nemi sunan MOBR. Bayan an samo shi, zaɓi shi kuma danna maɓallin "Ok".



5. Farashin muhawara na aiki yana farawa MOBR. Tana da filin ɗaya kaɗai a yawan muhawara - Shirya. Anan kuna buƙatar tantance adireshin teburin namu. Don waɗannan dalilai, saita siginan kwamfuta a cikin wannan filin. Sannan mun riƙe maɓallin linzamin kwamfuta na hagu kuma zaɓi yanki a kan takardar da matrix yake. Kamar yadda kake gani, bayanai akan masu tsara wurin an shigar dasu kai tsaye a filin taga. Bayan an gama wannan aikin, mafi bayyane shine zai danna maballin "Ok"amma kada ku yi sauri. Gaskiyar ita ce danna maɓallin wannan maɓallin daidai yake da amfani da umarnin Shigar. Amma lokacin aiki tare da jerin bayanan bayan an gama shigarwar dabarar, kar a danna maballin Shigar, kuma yi saiti na gajerun hanyoyin keyboard Ctrl + Shift + Shigar. Yi wannan aiki.



6. Don haka, bayan wannan, shirin yana yin lissafin kuma, a kayan fitarwa a cikin yankin da aka zaɓa da farko, muna da matrix wanda yake jujjuyar da wanda aka bayar.



7. Yanzu za mu buƙaci mu ninka matattarar matsix ta hanyar matrix B, wanda ya ƙunshi shafi ɗaya na dabi'u waɗanda ke bayan alamar daidai a cikin maganganu. Don ninka teburin cikin Excel akwai kuma aikin da ake kira daban MULKIN. Wannan magana tana da asalin rubutun:

   = MULKI (Array1; Array2)

   Mun zaɓi kewayon, a cikin yanayinmu, ya ƙunshi sel huɗu. Gaba, sake gudu Mayan fasalinta danna gunkin "Saka aikin".



8. A cikin rukuni "Ilmin lissafi"kaddamar Wizards na Aiki, zaɓi sunan MUMNOZH kuma danna maballin "Ok".



9. Ana kunna taga aikin aiki. MULKIN. A fagen "Array1" shigar da tsarawar matrix ɗin namu. Don yin wannan, azaman lokacin ƙarshe, saita siginan kwamfuta a cikin filin kuma tare da maɓallin linzamin kwamfuta na hagu zaedi za selectin tebur mai dacewa tare da siginan kwamfuta. Muna aiwatar da irin wannan matakin don shigar da masu gudanarwa a cikin filin Shirya2, kawai wannan lokacin zaɓi ƙimar shafi B. Bayan an aiwatar da ayyukan da ke sama, sake ba mu cikin sauri don danna maɓallin "Ok" ko maballin Shigar, kuma buga maɓallin haɗuwa Ctrl + Shift + Shigar.



10. Bayan wannan aikin, ana nuna Tushen daidaitawa a cikin sel da aka zaɓa: X1, X2, X3 da X4. Za a shirya su a jere. Don haka, zamu iya cewa mun warware wannan tsarin. Don tabbatar da daidaiton mafita, ya isa a sauya waɗannan amsoshin a cikin tsarin magana ta asali a maimakon tushen mai dacewa. Idan an lura da daidaito, to wannan yana nufin cewa an gabatar da tsarin daidaitattun abubuwa daidai.

Darasi: Matrix na juji a cikin Excel

Hanyar 2: zaɓi na sigogi

Hanya na biyu da aka sani don warware tsarin daidaituwa a cikin Excel shine amfani da hanyar zaɓi sigogi. Babban mahimmancin wannan hanyar shine bincika daga akasin haka. Wannan shine, dangane da sakamako sananne, muna bincika hujja ba da sani ba. Bari muyi amfani da daidaitattun Quadratic misali

3x ^ 2 + 4x-132 = 0

1. Yarda da darajar x daidai 0. Mun lissafta ƙimar da ta yi daidai da shi f (x)ta hanyar amfani da wannan tsari:

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

   Madadin darajar "X" sauya adireshin tantanin da ke wurin 0dauka daga gare mu x.



2. Je zuwa shafin "Bayanai". Latsa maballin "Idan bincike". Wannan maɓallin tana kasancewa akan haƙarƙan kwalin kayan aiki. "Aiki tare da bayanai". Lissafin faɗakarwa yana buɗewa. Zabi wani matsayi a ciki "Zabi ne ...".



3. Zaɓin zaɓi na siga yana farawa. Kamar yadda kake gani, ya kunshi fannoni uku. A fagen Saiti a cikin tantanin halitta ayyana adireshin tantanin da ke wurin f (x)lasafta mu kadan kadan. A fagen "Darajar" shigar da lamba "0". A fagen "Canza Ka'idodi" saka adireshin tantanin halitta wanda darajar take a ciki xa baya yarda da mu domin 0. Bayan kammala wadannan matakan, danna maballin "Ok".



4. Bayan wannan, Excel zai yi lissafin ta hanyar zaɓar siga. Wannan za a ba da rahoton wannan ta taga bayanin bayyana. A ciki, danna maɓallin "Ok".



5. Sakamakon yin lissafin tushen daidaito zai kasance a cikin tantanin da muka sanya a cikin filin "Canza Ka'idodi". A cikin lamarinmu, kamar yadda muke gani, x zai zama daidai 6.

Hakanan za'a iya bincika wannan sakamakon ta hanyar sauya wannan darajar a cikin magana don warwarewa maimakon darajar x.

Darasi: Zaɓin sigogi a cikin Excel

Hanyar 3: Hanyar Gaggawa

Yanzu bari muyi ƙoƙarin warware tsarin daidaituwa ta amfani da hanyar Cramer. Misali, dauki wannan tsarin da aka yi amfani da shi a ciki Hanyar 1:


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

1. Kamar yadda a farkon hanyar, muna shirya matrix A daga coefficients na daidaituwa da tebur B daga dabi'u wadanda ke bin alamar daidai.



2. Na gaba, muna yin ƙarin tebur huɗu. Kowane ɗayansu kwafin matrix ne. A, waɗannan kofe kawai suna da shafi ɗaya waɗanda aka rage ta tebur B. Teburin farko yana da shafi na farko, tebur na biyu yana da na biyu, da sauransu.



3. Yanzu muna buƙatar lissafa ƙaddarawar don waɗannan allunan. Tsarin daidaituwa zai sami mafita kawai idan duk ƙaddara na da ƙima ban da sifili. Don ƙididdige wannan ƙimar, Excel kuma yana da wani aikin daban - MOPRED. Gaskiyar magana wannan bayanin ita ce:

   = MOPRED (tsararru)

   Saboda haka, kamar aikin MOBR, kawai hujja ita ce batun teburin da ake sarrafawa.

   Don haka, zaɓi tantanin halitta wanda za'a tantance farkon matrix ɗin. Sannan danna maballin da aka saba da shi daga hanyoyin da suka gabata "Saka aikin".



4. An kunna taga Wizards na Aiki. Je zuwa rukuni "Ilmin lissafi" kuma a cikin jerin masu aiki muna nuna sunan MOPRED. Bayan haka, danna maɓallin "Ok".



5. Farashin muhawara na aiki yana farawa MOPRED. Kamar yadda kake gani, tana da filin guda ɗaya kawai - Shirya. A cikin wannan filin mun shigar da adireshin farkon matrix wanda aka canza. Don yin wannan, saita siginan kwamfuta a cikin filin, sannan ka zaɓi kewayon matrix. Bayan haka, danna maɓallin "Ok". Wannan aikin yana nuna sakamako a cikin tantanin halitta guda ɗaya, ba tsararru ba, saboda haka, don samun lissafin, ba kwa buƙatar komawa zuwa maɓallin maɓalli Ctrl + Shift + Shigar.



6. Aikin yana lissafin sakamakon kuma yana nuna shi a cikin sel da aka zaɓa. Kamar yadda muke gani, a cikin yanayin mu mai ƙaddara ne -740, wato, ba daidai yake da sifili ba, wanda ya dace da mu.



7. Hakanan, muna lissafin ƙaddara don sauran tebur uku.



8. A mataki na karshe, zamu lissafta kayyade farkon karatun lissafi. Hanyar tana faruwa gwargwadon tsari iri ɗaya. Kamar yadda kake gani, mai yanke hukunci na farkon tebur shima bazeze bane, wanda ke nufin cewa an dauki matrix mara inganci, shine, tsarin daidaitattun abubuwa suna da mafita.



9. Yanzu lokaci yayi da zamu samo tushen daidaituwa. Tushen daidaituwa zai zama daidai da rabo na ƙaddara daga m matrix wanda aka canza zuwa ma'aunin teburin firamare. Don haka, rabawa bi da bi dukkan ƙayyadaddun abubuwa huɗun na matatun masu canzawa da lambar -148, wanda shine mai yanke shawara na tebur na asali, mun sami tushen huɗu. Kamar yadda kake gani, suna daidai da dabi'u 5, 14, 8 da 15. Don haka suka dace da tushen da muka samo ta yin amfani da matrix inverse hanya 1, wanda ke tabbatar da daidaiton mafita na tsarin daidaituwa.

Hanyar 4: Hanyar Gauss

Hakanan za'a iya magance tsarin daidaituwa ta amfani da hanyar Gauss. Misali, ɗauki mafi sauki tsarin daidaituwa daga rashin sani guda uku:


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

1. Har yanzu, muna rubuta coefficients a cikin tebur A, da kuma sharuɗɗan kyauta waɗanda ke bayan alamar daidai - zuwa tebur B. Amma a wannan karon, za mu kawo dukkanin teburin guda biyu tare, saboda muna buƙatar hakan don yin aiki a nan gaba. Wani muhimmin yanayin shine cewa a cikin sel na farko na matrix A darajar ta kasance nonzero. In ba haka ba, ya kamata ka sake shirya layin a wurare.



2. Kwafi layi na farko na tsararriyar lambobi guda biyu zuwa layin da ke ƙasa (don tsabta, zaku iya tsallake layin ɗaya). A cikin tantanin farko, wanda yake a cikin layin har ƙasa da wanda ya gabata, mun shigar da dabara mai zuwa:

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

   Idan kun shirya tsararriyar daban, to adreshin kwayar halittar dabara zai sami ma'ana ta daban, amma zaku iya lissafa su ta hanyar kwatanta su da dabarun da aka bayar anan.

   Bayan an shigar da dabariyar dabino, zaɓi gaba ɗaya jerin sel kuma latsa haɗin maɓallin Ctrl + Shift + Shigar. Za'a iya amfani da tsari mai tsauri akan jere kuma za'a cika shi da dabi'u. Ta haka, mun rage daga layin na farko na farko, aka ninka shi ta hanyar adadin coefficients na farkon maganganu biyu na farkon tsarin.



3. Bayan haka, kwafa sakamakon igiyar sannan manna shi a layin da ke ƙasa.



4. Zaɓi layin farko biyu bayan layin ɓace. Latsa maballin Kwafawanda yake a kan kintinkiri a cikin shafin "Gida".



5. Mun tsallake layin bayan rakodin ƙarshe akan takardar. Zaɓi tantanin farko a jere na gaba. Danna dama. A cikin menu na mahallin da zai buɗe, matsar da siginan kwamfuta zuwa "Saka ta musamman". A cikin ƙarin jerin abubuwan da aka ƙaddamar, zaɓi matsayi "Dabi'u".



6. A layi na gaba, shigar da tsararren tsari. Ya rage daga layi na uku rukunin bayanan data gabata na biyu, aka ninka shi ta hannun adadin biyu na na uku da na biyu. A yanayinmu, tsari yana da tsari mai zuwa:

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

   Bayan shigar da dabara, zabi duk layi sannan ka yi amfani da gajeriyar hanya Ctrl + Shift + Shigar.



7. Yanzu yakamata kayi aikin sakewa bisa ga hanyar Gauss. Mun tsallake layuka uku daga rakodin ƙarshe. A cikin layi na hudu mun shiga cikin tsararren tsari:

   = B17: E17 / D17

   Don haka, mun rarraba layin ƙarshe da mu ƙididdige ta hanyar mai amfani da ta uku. Bayan buga dabara, zabi dukkan layin sai a latsa hade Ctrl + Shift + Shigar.



8. Zamu hau layi mu shigar da tsararren tsari mai zuwa:

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

   Mun danna maɓallin gajeriyar hanya ta al'ada don amfani da tsararren tsari.



9. Mun tashi wani layin sama. A ciki zamu shigar da tsararren tsari na wannan tsari:

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

   Sake zaɓa ɗaukacin layi kuma yi amfani da gajeriyar hanya Ctrl + Shift + Shigar.



10. Yanzu za mu duba lambobin da suka juya a sashin layi na ƙarshen toshe na layuka waɗanda muka ƙididdige a baya. Waɗannan lambobin kenan (4, 7 da 5) zai kasance tushen wannan tsarin daidaituwa. Kuna iya tabbatar da wannan ta hanyar sauya su maimakon dabi'u X1, X2 da X3 a magana.

Kamar yadda kake gani, a cikin Excel, za'a iya magance tsarin daidaituwa ta hanyoyi da yawa, kowannensu yana da nasa fa'ida da rashin amfanin sa. Amma duk waɗannan hanyoyin za'a iya rarrabasu cikin sharaɗi cikin manyan rukuni biyu: matrix da kuma amfani da kayan zaɓin sigogi. A wasu halaye, hanyoyin matrix ba koyaushe dace da warware matsala. Musamman, lokacin da ƙaddara na matrix daidai yake da sifili. A wasu halaye, mai amfani da kansa yana da 'yancin yanke shawara wanda zaɓi ya fi dacewa da kansa.