Rješavanje sistema jednadžbi u Microsoft Excel-u

Sposobnost rješavanja sistema jednadžbi često može biti korisna ne samo u proučavanju, nego i u praksi. U isto vrijeme, ne zna svaki korisnik PC-a da Excel ima svoje mogućnosti rješavanja linearnih jednadžbi. Otkrijmo kako koristiti ovaj alat alata stolnog procesora za postizanje ovog zadatka na različite načine.

Opcije odluke

Svaka jednadžba može se smatrati riješenom tek kad se nađu njezini korijeni. Excel ima nekoliko opcija za pronalaženje korijena. Pogledajmo svaki od njih.

Metoda 1: Metoda matrice

Najčešći način rješavanja sustava linearnih jednadžbi s alatima Excel je uporaba matrične metode. Sastoji se u konstrukciji matrice koeficijenata izraza, a zatim u stvaranju inverzne matrice. Pokušajmo ovom metodom riješiti sljedeći sustav jednadžbi:


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

1. Matricu ispunjavamo brojevima koji su koeficijenti jednadžbe. Te bi brojeve trebalo poredati redoslijedom, uzimajući u obzir mjesto svakog korijena kojem odgovaraju. Ako je u jednom izrazu jedan korijen odsutan, tada se u ovom slučaju koeficijent smatra jednakim nuli. Ako koeficijent nije naveden u jednadžbi, ali postoji odgovarajući korijen, tada se smatra da je koeficijent 1. Označite rezultirajuću tablicu kao vektor A.



2. Odvojeno napišite vrijednosti nakon znaka jednakosti. Označi ih njihovim zajedničkim imenom, kao vektorom B.



3. Sada, da bismo pronašli korijene jednadžbe, najprije moramo pronaći obrnutu matricu postojeće. Srećom, Excel ima posebnog operatera koji je dizajniran da riješi ovaj problem. Zove se MOBR. Ima prilično jednostavnu sintaksu:

   = MOBR (niz)

   Argument Niz - Ovo je, u stvari, adresa izvorne tabele.

   Dakle, na listu odaberemo regiju praznih ćelija, koja je jednaka veličini rasponu izvorne matrice. Kliknite na dugme "Umetanje funkcije"koji se nalazi blizu linije formula.



4. Pokretanje Čarobnjaci za funkcije. Idite u kategoriju "Matematički". Na popisu koji se pojavi potražite ime MOBR. Nakon što je pronađena, odaberite je i kliknite na gumb "OK".



5. Pokreće se prozor argumenta funkcije MOBR. Ima samo jedno polje u broju argumenata - Niz. Ovdje trebate navesti adresu naše tablice. U ove svrhe postavite kursor u ovo polje. Zatim držimo lijevu tipku miša i odaberemo područje na listu u kojem se nalazi matrica. Kao što vidite, podaci o koordinatama položaja automatski se unose u polje prozora. Nakon što je ovaj zadatak završen, najočitije bi bilo kliknuti na gumb "OK"ali ne žurite. Činjenica je da je klikom na ovaj gumb ekvivalentno korištenju naredbe Unesite. Ali kada radite s nizovima nakon što završite unos formule, ne klikajte na gumb Unesitei napravite skup prečica na tastaturi Ctrl + Shift + Enter. Izvršite ovu operaciju.



6. Dakle, nakon ovoga, program izvodi proračune i na izlazu u prethodno odabranom području imamo matricu koja je obrnuta datoj.



7. Sada ćemo morati pomnožiti inverznu matricu s matricom B, koji se sastoji od jednog stupca vrijednosti smještenog nakon znaka jednako u izrazima. Za umnožavanje tablica u Excelu postoji i zasebna funkcija koja se zove VIŠE. Ova izjava ima sljedeću sintaksu:

   = MALO (Array1; Array2)

   Mi biramo raspon, u našem slučaju, koji se sastoji od četiri ćelije. Dalje, trči ponovo Čarobnjak za funkcijeklikom na ikonu "Umetanje funkcije".



8. U kategoriji "Matematički"lansirano Čarobnjaci za funkcije, odaberite ime MUMNOZH i kliknite na dugme "OK".



9. Prozor argumenta funkcije je aktiviran. VIŠE. U polju "Array1" unesite koordinate naše obrnute matrice. Da biste to učinili, kao i prošli put, postavite kursor u polje i pritiskom lijeve tipke miša odaberite odgovarajuću tablicu sa kursorom. Izvodimo sličnu akciju za unos koordinata u polje Array2, samo ovaj put odaberite vrijednosti stupca B. Nakon što su gore navedene akcije izvedene, opet ne žurimo sa pritiskom na tipku "OK" ili ključ Unesitei unesite kombinaciju tipki Ctrl + Shift + Enter.



10. Nakon ove akcije, korijeni jednadžbe se prikazuju u prethodno odabranoj ćeliji: X1, X2, X3 i X4. Oni će biti poredani u seriji. Dakle, možemo reći da smo riješili ovaj sistem. Da biste provjerili ispravnost rješenja, dovoljno je zamijeniti ove odgovore u izvornom sistemu izraza umjesto odgovarajućih korijena. Ako se promatra jednakost, to znači da je predstavljeni sistem jednadžbi pravilno riješen.

Lekcija: Inverzna matrica u Excelu

Metoda 2: odabir parametara

Drugi poznati način rješavanja sustava jednadžbi u Excelu je upotreba metode odabira parametara. Suština ove metode je traženje od suprotnog. To jest, na osnovu poznatog rezultata, tražimo nepoznati argument. Koristimo kvadratnu jednadžbu kao primjer

3x ^ 2 + 4x-132 = 0

1. Prihvati vrijednost x za jednake 0. Izračunavamo odgovarajuću vrijednost f (x)primenom sledeće formule:

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

   Umesto vrednosti "X" zamijenite adresu ćelije u kojoj se broj nalazi 0preuzeli za nas x.



2. Idite na karticu "Podaci". Kliknite na dugme "Šta ako analiza". Ovo dugme nalazi se na vrpci u okviru alata. "Rad sa podacima". Otvara se padajuća lista. Odaberite položaj u njemu "Odabir parametara ...".



3. Počinje prozor za odabir parametara. Kao što vidite, sastoji se od tri polja. U polju Postavite u ćeliju odredite adresu ćelije u kojoj se nalazi formula f (x)izračunali smo malo ranije. U polju "Vrijednost" unesite broj "0". U polju "Promjena vrijednosti" odredite adresu ćelije u kojoj se vrijednost nalazi xprethodno prihvaćeni od nas za 0. Nakon dovršetka ovih koraka kliknite na gumb "OK".



4. Nakon toga Excel će izvršiti proračun odabirom parametra. O tome će se izvijestiti pojavio se informativni prozor. U njemu kliknite na dugme "OK".



5. Rezultat izračunavanja korijena jednadžbe bit će u ćeliji koju smo dodijelili u polju "Promjena vrijednosti". U našem slučaju, kao što vidimo, x biće jednaki 6.

Ovaj rezultat se može provjeriti i zamjenom ove vrijednosti u izrazu koji se rješava umjesto vrijednosti x.

Lekcija: Odabir parametara u Excelu

3. metod: Cramer metoda

Pokušajmo sada riješiti sistem jednadžbi pomoću Cramer metode. Na primjer, uzmimo isti sistem koji je korišten Metoda 1:


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

1. Kao i u prvoj metodi, sastavljamo matricu A iz koeficijenata jednadžbi i tablice B iz vrijednosti koje slijede znak jednako.



2. Zatim izrađujemo još četiri tabele. Svaka od njih je kopija matrice. A, samo ove kopije imaju jedan stupac zamijenjen tablicom B. Prva tablica ima prvi stupac, druga tablica ima drugi, itd.



3. Sada moramo izračunati odrednice za sve ove tablice. Sustav jednadžbi imat će rješenja samo ako sve odrednice imaju vrijednost osim nule. Da bi izračunao ovu vrednost, Excel opet ima zasebnu funkciju - MOPRED. Sintaksa ove izjave je sljedeća:

   = MOPRED (niz)

   Dakle, poput funkcije MOBR, jedini argument je referenca na tablicu koja se obrađuje.

   Dakle, odaberite ćeliju u kojoj će biti prikazana odrednica prve matrice. Zatim kliknite na gumb poznat iz prethodnih metoda "Umetanje funkcije".



4. Prozor je aktiviran Čarobnjaci za funkcije. Idite u kategoriju "Matematički" a među spiskom operatera ističemo ime MOPRED. Nakon toga kliknite na gumb "OK".



5. Pokreće se prozor argumenta funkcije MOPRED. Kao što vidite, ima samo jedno polje - Niz. U ovo polje unosimo adresu prve transformirane matrice. Da biste to učinili, postavite kursor u polje, a zatim odaberite raspon matrice. Nakon toga kliknite na gumb "OK". Ova funkcija prikazuje rezultat u jednoj ćeliji, a ne nizu, pa da biste dobili izračun, ne morate pribjegavati pritisku kombinacije tipki Ctrl + Shift + Enter.



6. Funkcija izračunava rezultat i prikazuje ga u unaprijed odabranoj ćeliji. Kao što vidimo, u našem slučaju je odrednica -740, odnosno nije jednak nuli, što nama odgovara.



7. Slično tome, izračunavamo odrednice za ostale tri tablice.



8. U završnoj fazi izračunavamo odrednicu primarne matrice. Postupak se odvija prema istom algoritmu. Kao što vidite, odrednica primarne tablice je također neulazna, što znači da se matrica smatra degeneriranom, odnosno, sustav jednadžbi ima rješenja.



9. Sada je vrijeme da pronađete korijene jednadžbe. Korijen jednadžbe bit će jednak omjeru determinante odgovarajuće transformirane matrice prema determinanti primarne tablice. Tako se dijele zauzvrat sve četiri odrednice transformiranih matrica na broj -148, što je odrednica izvorne tablice, dobili smo četiri korijena. Kao što vidite, jednake su vrijednostima 5, 14, 8 i 15. Tako se točno podudaraju s korijenima koje smo pronašli koristeći inverznu matricu u metoda 1koji potvrđuje ispravnost rješenja sistema jednadžbi.

Metoda 4: Gaussova metoda

Sistem jednadžbi također se može riješiti primjenom Gaussove metode. Na primjer, uzmimo jednostavniji sistem jednadžbi iz tri nepoznanice:


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

1. Još jednom zapisujemo koeficijente u tablicu A, i slobodni izrazi koji se nalaze iza znaka jednako - na stol B. Ali ovaj put ćemo približiti obje tablice, jer će nam trebati da bi radili u budućnosti. Važan uvjet je da u prvoj ćeliji matrice A vrijednost je bila nula. Inače bi linije trebali preurediti na mjesta.



2. Kopirajte prvi red dviju povezanih matrica u liniju ispod (radi jasnoće možete preskočiti jedan red). U prvu ćeliju koja se nalazi u liniji još niže od prethodne unosimo sljedeću formulu:

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

   Ako ste matrice uredili drugačije, adrese ćelija formule imati će drugačije značenje, ali možete ih izračunati upoređujući ih s ovdje navedenim formulama i slikama.

   Nakon unosa formule odaberite cijeli red ćelija i pritisnite kombinaciju tipki Ctrl + Shift + Enter. Formula matrice primijenit će se na redak i ona će biti ispunjena vrijednostima. Dakle, oduzeli smo od drugog retka prvi, pomnožen s omjerom prvih koeficijenata prva dva izraza sustava.



3. Nakon toga kopirajte rezultirajući niz i zalijepite ga u donji redak.



4. Izaberite prve dvije linije nakon linije koja nedostaje. Kliknite na dugme Kopirajkoji se nalazi na vrpci u kartici "Početna".



5. Preskočimo crtu nakon posljednjeg zapisa na listu. Odaberite prvu ćeliju u sljedećem redu. Kliknite desnim klikom. U kontekstnom meniju koji se otvori pomaknite kursor na "Specijalni umetak". Na lansiranoj dodatnoj listi odaberite položaj "Vrijednosti".



6. U sljedećem retku unesite formulu matrice. Od trećeg retka oduzima se prethodna skupina podataka drugog reda, pomnožena s omjerom drugog koeficijenta trećeg i drugog reda. U našem slučaju formula će imati sljedeći oblik:

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

   Nakon unosa formule, odaberite cijeli red i upotrijebite prečicu na tipkovnici Ctrl + Shift + Enter.



7. Sada bi trebali izvesti obrnuti hod prema Gaussovoj metodi. Preskočimo tri retka sa posljednjeg zapisa. U četvrti redak unosimo formulu matrice:

   = B17: E17 / D17

   Tako dijelimo zadnju liniju koju smo izračunali prema njegovom trećem koeficijentu. Nakon što upišete formulu, odaberite cijeli redak i pritisnite kombinaciju tipki Ctrl + Shift + Enter.



8. Idemo uzlaznu liniju i u nju unesemo sljedeću formulu matrice:

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

   Pritiskamo uobičajenu prečicu na tipkovnici za primjenu formule niza.



9. Penjemo se još jednom linijom iznad. U nju unosimo formulu matrice sljedećeg oblika:

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

   Opet odaberite cijelu liniju i primijenite prečicu na tipkovnici Ctrl + Shift + Enter.



10. Sada pogledamo brojeve koji su se pokazali u posljednjem stupcu posljednjeg bloka redaka koji smo ranije izračunali. To su ovi brojevi (4, 7 i 5) biće korijeni ovog sistema jednačina. To možete provjeriti zamjenom umjesto vrijednosti X1, X2 i X3 u izražavanju.

Kao što vidite, u Excelu se sustav jednadžbi može riješiti na više načina, a svaki od njih ima svoje prednosti i nedostatke. Ali sve ove metode možemo uvjetno podijeliti u dvije velike grupe: matricu i pomoću alata za odabir parametara. U nekim slučajevima matrične metode nisu uvijek pogodne za rješavanje problema. Konkretno, kada je odrednica matrice jednaka nuli. U ostalim slučajevima, sam korisnik može slobodno odlučiti koju opciju smatra prikladnijom za sebe.