Kako riješiti jednadžbu u Excel. Rješenje Excel jednadžbi

Jedna od najpovoljnijih pitanja računarskog učenja predstavlja problem odabira i upotrebe programa obuke pedagoških ubrzanih treninga.

Prilikom proučavanja pojedinačnih tema i rješavanja nekih problema u matematičkim lekcijama u srednjim školama, na primjer, kada rješavaju jednadžbe dijeljenjem segmenta u pola ili metoda uzastopnih aproksimacija, to čine ne daju ljepotu, racionalnost metode rješenja koja se koristi.

U ovom sam članku predstavio te zadatke čije rješenje pomoću MS Excela omogućava vam vizualnu, dostupnu odluku o razumijevanju odluke, pokazati svoju logiku, racionalnost. Uz put, studenti dobivaju održive vještine za rad sa programom.

Pronalaženje korijena jednadžbe pomoću odabira parametra

Primjer 1.

Neka se zna da u osoblju bolnice sastoji se od 6 senibola, 8 medicinskih sestara, 10 ljekara, 3 šefova odjela, glavnog ljekara, šefom ekonomije i šefa bolnice. Ukupni mjesečni fond plaće iznosi 1000.000 konvencionalnih jedinica. Potrebno je utvrditi šta bi trebali biti zaposlenici bolnice.

Rješenje takvog zadatka može se tražiti metodom gašenja. Međutim, u najboljem slučaju potrebno je puno vremena. Možete ponuditi drugi način za rješavanje. U Excelu implementira se kao potraga za vrijednošću parametra formule koji zadovoljava njegovu specifičnu vrijednost.

Izgradite model rješavanja ovog zadatka. Kao osnova uzmite platu medicinskih sestara, a preostala plata će izračunati, na osnovu njega: u toliko puta ili toliko. Govoreći matematičkim jezikom, svaka plata je linearna funkcija od plaće sanitarije: a I * C + u I, gdje sa - platama sestrinstva; I ja i u I - koeficijenti koji se za svaki položaj određuju na sljedeći način:

  • medicinska sestra dobiva 1,5 puta više sestrinstva (a 2 \u003d 1,5; u 2 \u003d 0);
  • doktor je 3 puta više medicinske sestre (3 \u003d 3; u 3 \u003d 0);
  • Šef odjela - do 30 god. više od ljekara (a 4 \u003d 3; b 4 \u003d 30);
  • Šef ljekarne - 2 puta više medicinske sestre (5 \u003d 2; u 5 \u003d 0);
  • Šef farme - na 40 god. Više medicinskih sestara (a 6 \u003d 1,5; u 6 \u003d 40);
  • Šef bolnice - na 20. god. Više glavnog ljekara (a 8 \u003d 4; u 8 \u003d 20);
  • glavni ljekar je 4 puta više medicinske sestre (7 \u003d 4; na 7 \u003d 0);

Znajući broj ljudi na svakom postu, naš se model može napisati kao jednadžba: n 1 * (a 1 * c + b 1) + n 2 * (a 2 * c + b 2) + ... + n 8 * (A 8 * c + b 8) \u003d 1000000, gdje je n 1 broj znakova, n 2 je broj medicinskih sestara itd.

U ovoj jednačini znamo 1 ... A 8, B 1 ... B 8 i N 1 ... N 8, i sa nepoznatim. Analiza jednadžbe pokazuje da je zadatak izračunavanja plata smanjen za rješavanje linearne jednadžbe u odnosu na C. Pretpostavimo da plata medicinske sestre 150,00 Y.E.

Unesite izvorne podatke u radni list proračunske tablice, kao što je prikazano u nastavku.

Plata med. Radnici

Pozicija

Plata

Broj zaposlenih

Ukupna plata

Sanitarni

Medicinska sestra

Glava Odjel

Glava Ljekarna.

Glavna ljekara

Glava Bolnica

Opći fond je jednak

U stupcu D izračunajte plaće za svaki post. Na primjer, za ćeliju D4, formula izračuna ima oblik \u003d B4 * $ D $ 3 + C4.

U stupcu F izračunajte platu svih zaposlenih u ovom položaju. Na primjer, za F3 ćelije, formula izračuna ima oblik \u003d D3 * E3.

U ćeliji F11, ukupni fond za bolničku platu. Radni list proračunske tablice izgledat će prikazan dolje.

Plata med. Radnici

Pozicija

Plata

Broj zaposlenih

Ukupna plata

Sanitarni

Medicinska sestra

Glava Odjel

Glava Ljekarna.

Glavna ljekara

Glava Bolnica

Opći fond je jednak

Da bi se utvrdila plata sestrinstva tako da je Fond za nagodbu jednak navedenom:

  • Aktivirajte naredbu Izbor parametrana kartici Podaci / podaci / analizu / analiza podataka "Što, ako";
  • U polju "Postavljanje u ćeliju", čini se da je prozor unosao referencu na F11 ćeliju koja sadrži formulu;
  • U polju "Vrijednost" birajte željeni rezultat 1000000;
  • U polju "Promjena vrijednosti ćelije" unesite vezu na promjenjivu D3 ćeliju i kliknite na gumb UREDU.

Analiza zadatka pokazuje da linearne jednadžbe mogu se riješiti pomoću Excela. Naravno, takva jednadžba može riješiti bilo koji školski. Međutim, zbog ovog jednostavnog primjera postalo je očigledno da potraga za vrijednošću parametra formule koja zadovoljava određenu vrijednost nije ništa drugo nego numeričko rješenje jednadžbi. Drugim riječima, koristeći Excel, svake jednadžbe s jednom varijable mogu se riješiti.

Zadatak za studente:

Napravite nekoliko opcija za osoblje pomoću funkcije Izbor parametrai dogovorite oblik tabele :

  • Promijenite broj zaposlenih u različitim položajima ;
  • Pokupiti platu medicinsku sestru u novim uvjetima;
  • Stvorite tablicu nekoliko opcija za raspored osoblja.

Razmotrite još jedan primjer pronalaska korijena jednadžbe pomoću odabira parametra. Prilikom rješavanja ove jednadžbe koristi se i metoda uzastopnih aproksimacija. Studenti u nastavi sa detaljnom proučavanjem matematike upoznaju se sa ovom metodom. Stoga je taj primjer dostupan za ostale studente, predlažem kratku teoriju ove metode.

Neka se jednadžba zabilježe u obliku x \u003d f (x). Odaberite neku početnu aproksimaciju x 1 i zamijenite ga umjesto x u f (x). Dobivena vrijednost x 2 \u003d f (x 1) ove funkcije smatra se drugom aproksimacijom. Zatim se nalazi treća aproksimacija formule x 3 \u003d f (x 2) i tako dalje. Dakle, dobivamo redoslijed x 1, x 2, x 3, ..., x n, ... brojevi koji imaju granicu α. Zatim, ako je funkcija F (x) kontinuirana, od jednakosti x N + 1 \u003d F (x n) Dobivamo α \u003d f (α). To znači da je α rješenje jednadžbe x \u003d f (x).

Primer 2.

Dajmo polinom trećeg stepena:

x 3 -0.01x 2 -0.7044x + 0.139104 \u003d 0.

Budući da tražimo polinom korijena trećeg stepena, tada ne postoji više od tri prava korijena. Da biste pronašli korijene, oni su u početku lokalizirani, odnosno kako bi pronašli intervale na kojima postoje. Ti intervali lokacije korijena mogu poslužiti kao intervali, na krajevima koje funkcija ima suprotan znak. Da biste pronašli intervale, na krajevima kojih funkcija mijenja znak, potrebno je izgraditi njegov raspored ili je strpati. Napravit ćemo tablicu funkcija u intervalu [-1; 1] u koracima od 0,2. Za ovo vam je potrebno:

  • Uvesti vrijednost -1 u ćeliju A2, a u ćeliji A3 vrijednost je -0,8.
  • Odaberite raspon A2: A3, položite pokazivač miša na markeru za punjenje ovog raspona i istežite ga na asortimanu A4: A12, argument se protera.
  • U B2 ćeliju unesite formulu: \u003d A2 ^ 3-0.01 * A2 ^ 2-0.7044 * A2 + 0,139104
  • Odaberite B2 ćeliju. Postavite pokazivač miša na marker za punjenje ove ćelije i istežite ga na rasponu B3: B12. Funkcija je takođe nestala.

Vrijednost H. argumenta

Vrijednost funkcije u

Može se vidjeti iz tablice da polinom mijenja znak u intervalima [-1; -0,8], a, stoga, na svakom od ovih intervala postoji njegov korijen. Budući da polinom trećeg stepena nema više od tri korijena, svi su lokalizirani.

Prije pronalaska korijena odabirom parametra, morate izvesti neki pripremni rad:

  • Podesite tačnost korijenom. Korijen korištenjem odabira parametara je metoda uzastopnih aproksimacija. Za ovo u postavci za brzo pristupa / Ostale naredbe , I na kartici Formule dijaloški okvir Exel parametri Set B. Parametri izračuna relativna greška i ograničiti Iteracije su 0,00001 i 1000, respektivno.
  • Uzmite ćeliju na radni list, na primjer C2, ispod željenog korijena. Ova ćelija će igrati dvostruku ulogu. Prije upotrebe odabira parametra u njemu, početno je približavanje korijenu jednadžbe i nakon upotrebe - pronađena je približna vrijednost korijena.
  • Korijen pomoću odabira parametara pronalazimo metodu uzastopnih aproksimacija. Stoga u C2 ćeliji morate unijeti vrijednost koja se približava željenom korijenu. U našem slučaju prvi segment korijenske lokalizacije je [-1; -0.8]. Slijedom toga, za početnu aproksimaciju korijena, razumno je uzeti prosječnu točku ovog segmenta -0,9.
  • Uzmite ćeliju, na primjer D2, pod funkcijom za koju se provodi korijenska pretraga, a umjesto nepoznate funkcije, ova značajka treba ukazivati \u200b\u200bna vezu do ćelije, dodijeljenog pod željenim korijenom. Dakle, u D2 ćeliji unesite formulu: \u003d C2 ^ 3-0.01 * C2 ^ 2-0.7044 * C2 + 0,139104

Slično tome, morate ići s još dva željena korijena:

  • Uzmite C8 ćeliju ispod drugog korijena, unesite početnu aproksimaciju 0,3, a u D8 ćeliji unesite sljedeću formulu: \u003d C8 ^ 3-0.01 * C8 ^ 2-0.7044 * C8 + 0,139104
  • Podijelite C10 ćeliju pod drugi korijen, unesite početnu aproksimaciju od 0,7, a u ćeliji D10 uvodite sljedeću formulu: \u003d C10 ^ 3-0.01 * C10 ^ 2-0.7044 * C10 + 0,139104

Rezultati akcija prikazani su u tabeli.

Znači H.

V. Vrijednost

Početna aproksimacija prije nanošenja metode

Značenje funkcija

Sada možete preći na pronalazak prvog korijena jednadžbe:

Odaberite naredbu Izbor parametra. Ekran prikazuje dijaloški okvir. Izbor parametra.

  • U polju Instalirajte u ćelijuunesite vezu do D2 ćelije. Ovo polje pruža referencu na ćeliju u kojoj se uvodi formula koja izračunava vrijednost lijevog dijela jednadžbe. Da biste pronašli korijen pomoću odabranog parametra, jednadžba se mora podnijeti u ovom obliku tako da njegov pravi dio ne sadrži varijablu.
  • U polju Vrijednost Unesite 0. Ovo ukazuje na vrijednost iz desnog dijela jednadžbe.
  • U polju Promjena vrijednosti ćelije Unesite C2. Ovo polje pruža vezu do ćelije dodijeljene pod varijabli.
  • Pritisnite tipku UREDU..

Prozor prikazuje prozor Rezultat odabira parametra Sa rezultatima tima Izbor parametra. Pored toga, proizvod koji se razmatra postavlja pronađenu približnu vrijednost korijena u C2 ćeliju. U ovom slučaju je to -0.920. Slično tome, postoje dva preostala korijena u C8 i C10 ćelijama. Oni su jednaki 0,210 i 0,721.

Znači H.

V. Vrijednost

Korijen jednadžbe

Značenje funkcija

Zadatak za studente:

Pronađite sve korijene jednadžbi

1. x 3 -2,92x 2 + 1,4355x + 0,791136 \u003d 0

2. x 3 -2.56x 2 -1,3251x + 4,395006 \u003d 0

3. x 3 + 2,84x 2-5,66064x-14,766336 \u003d 0

Pronalaženje korijena jednadžbe dijeljenjem segmenta na pola

Kratka metoda metode. Neka kontinuirana funkcija f (x) ima vrijednosti različitih znakova na krajevima segmenta, odnosno f (a) f (b)<0.Тогда уравнение F(x)=0 имеет корень внутри этого отрезка. Отрезок отрезком локализации корня. Пусть c=(a+b)/2 – середина отрезка . Если F(a)F(c)<=0, то корень находится на отрезке , который берем за новый отрезок локализации корня. Если F(a)F(c)>0, zatim za novi segment lokacije korijena koji uzimamo. To je da je novi segment korijenske lokalizacije dvostruko manje. Proces divizije segmenta za korijenski lokalizacija se nastavlja sve dok njegova dužina ne postane manje ε, tačnost korijena je. U ovom slučaju, svaka se segment lokalizacije razlikuje od korijena ne više od ε / 2.

Pronađite korijene jednadžbe x 2 -2 \u003d 0 s tačnošću od 0,001 dijeljenjem segmenta na pola. Za početni segment korijenske lokalizacije je odabrana. Da biste implementirali ovu metodu, unesite formulam ili vrijednosti u nastavku u tabeli ispod u tablici:

Ćelija

Formula ili vrijednost

\u003d (A3 ^ 2-2) * (C3 ^ 2-2)

Ako (b3-a3<$B$1;""Корень найден и равен "" & текст (C3;""0,000""); "" "")

Ako (D3.<=0; A3;C3)

Ako (D3.<=0; C3; B3)

\u003d (A4 ^ 2-2) * (C4 ^ 2-2)

Ako (b4-a4<$B$1; ""Корень найден и равен "" & текст(C4; ""0,000""); "" "")

Sada ostaje samo za odabir asortimana A4: F4, položite pokazivač miša na markeru da biste ga napunili i spustili dok se poruka ne pojavi u stupcu F u korijenu. U ovom slučaju, poruka će se pojaviti u ćeliji F14, a vrijednost korijena je tačna na 0,001 jednaka 1,415.

Broj koraka može se unaprijed definirati i kopirati formule u rasponu potrebnog broja linija. Broj koraka prije pronalaska korijena određuje se formulom: +1 (1), gdje [X] ima cijeli dio broja x, t - navedene tačnosti.

Zaključno, primećujem da je primer koji se razmotrio:

  • Konkretna operacija žica koja kombinira nekoliko redaka u jedan (naznačen simbolom ampersansa i). Pri kombinovanju dva reda, drugi niz se dodaje direktno na kraj prvog retka.
  • Funkcija radnog lista iz kategorije funkcija za rad sa tekstom teksta (tekst). Ova funkcija pretvara vrijednost u tekst u datom numeričkom formatu.

Zadatak za studente:

Izračunajte korijen COSX \u003d X jednadžbe na segmentu s tačnošću od 0,001. Broj koraka za određivanje korijena za izračunavanje upotrebe formule (1).

Upotreba MS Exel značajno proširuje raspon zadataka koji se mogu koristiti u učenju. To je zbog mogućnosti prenošenja radno intenzivnog operacija na računar, na primjer, u rješavanju jednadžbi metodama iteracija i dijeljenje segmenta na pola.

Literatura:

  1. Informatika u školi / ed. Makarova N. V. - Sankt Peterburg: Peter Kom, 1999.
  2. Simboli L.V. Rješavanje poslovnih zadataka u Microsoft Office - m.: Zao "Izdavaštvo Binom", 2001.
  3. Shokolovich V.F. Trening informacione tehnologije. Informatika i obrazovanje. 1998. - №2.
  4. Ignekova G.S. Metodički aspekti obuke nastavnika za računarske nauke. Informatika i obrazovanje. 1998. - №3.

Microft Office Excel 2007 je poseban prozorski poseban program koji vam omogućava da napravite različite tablice sa unesenim podacima. Štaviše, ovaj program vam omogućava da riješite jednadžbe.

Otvorite Excel 2007. Za najjednostavnije rješenje jednadžbe koristite značajku rješenja rješenja. Istina, u mnogim standardnim uredskim paketima, ova nadgradnja nije instalirana. Da biste instalirali, otvorene Excel Settings, koje se nalaze u donjem desnom uglu dijaloškog okvira za skočni donji okvir. U izborniku CILT koji se otvori u sljedećem redoslijedu: "Add-in" - "Pretraživanje rješenja" - "Idi".

Nakon tranzicije, potvrdite okvir pored stavke "Rešenje pretrage" i kliknite OK.

Tada će Excel konfigurirati program.

Zatim, da biste riješili jednadžbu, unesite je u polje lista. Neka vaša jednadžba sa dvije varijable: f (x1, x2) \u003d 3 × 1 + 2 × 2 - max, u slučaju određenih ograničenja:

  • X1 - X2 ≥ -2
  • 3 × 1 - 2 × 2 ≤ 6
  • 2 × 1 + 3 × 2 ≥ 2
  • X2 ≤ 3.
  • X1 ≥ 0
  • X2 ≤ 0

Unesite varijable x1 i x2 u stupcu A i X2. Zatim označite polje na kojem se nalaze vrijednosti varijabli. Zatim u stupcu i unesite funkciju F (x1, x2) \u003d. I desno od toga da istakne istu ćeliju da će vrijednost ove funkcije biti u pravu.

Zatim u crvenom polju unesite samu jednadžbu 3 × 1 + 2 × 2. Imajte na umu da je x1 ćelija B1 i X2 - ćelija B2.

Sada unesite sva ograničenja na terenu.

Zatim idite na odjeljak "Pretraživanje odluke" (mapa podataka). Pronađite polje "Instalirajte ciljnu ćeliju" tamo gdje trebate staviti crvenu ćeliju. Suprotno "\u003d" Pišemo maksimalnu vrijednost.
U polju "Promjena ćelije" dodajte plave ćelije - X1, X2.

Ako ste unijeli sva ograničenja, provjerite ih ispravno, a zatim kliknite gumb "Run". U slučaju da se svi podaci unose pravilno, program mora izračunati nepoznato. U našem slučaju x1 \u003d 4, H2 \u003d 3 i F (x1, x2) \u003d 18. Jednadžba se odlučuje.

Sposobnost rješavanja sustava jednadžbi često može imati koristi ne samo u studiranju, već i u praksi. Istovremeno, ne svaki korisnik PC-a zna da egzel ima vlastite varijante rješenja linearnih jednadžbi. Otkrijmo kako pomoću alata ovog tabelarnog procesora obavlja ovaj zadatak na različite načine.

Metoda 1: Metoda matrice

Najčešći način rješavanja sustava linearnih alatnih jednadžbi Excel je upotreba matrične metode. Sastoji se u izgradnji matrice koeficijenata izraza, a zatim u stvaranju matrice povratka. Pokušajmo koristiti ovu metodu za rješavanje sljedećeg sustava jednadžbi:

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

  1. Ispunite matrične brojeve koji su koeficijenti jednadžbe. Ovi brojevi trebaju biti smješteni uzastopno redoslijedom, uzimajući u obzir lokaciju svakog korijena na koji odgovaraju. Ako je jedan od korijena u nekom izrazu odsutan, a zatim u ovom slučaju, koeficijent se smatra nulom. Ako koeficijent nije označen u jednadžbi, ali odgovarajući korijen je dostupan, vjeruje se da je koeficijent jednak 1 . Pogledajte rezultirajuću tablicu kao vektor SVEDOK JOVANOVIĆ - ODGOVOR:.

  2. Odvojeno zabilježite vrijednosti nakon znaka "jednako". Označavamo njihovim zajedničkim imenom poput vektora B..

  3. Sada, da biste pronašli korijenje jednadžbe, prvo, moramo pronaći matricu obrnuto postojeće. Srećom, Excel ima poseban operater koji je dizajniran za rješavanje ovog zadatka. Zvani O. Mesing. Ima prilično jednostavnu sintaksu:

    Mesing (niz)

    Argument "Array" - To je, u stvari, adresa izvornog stola.

    Dakle, dodijelimo područje praznih ćelija na listu, što je u veličini jednako rasponu originalne matrice. Kliknite na gumb "Umetnite funkciju"Smješten u blizini crte formula.

  4. Trčanje Masters funkcionira. Idite u kategoriju "Matematičko". Na uvedenoj listi izgledaju "Mesing". Nakon što se nađe, ističemo ga i kliknemo na gumb Uredu.

  5. Mesing. To je samo jedno polje u pogledu broja argumenata - "Array". Ovdje trebate odrediti adresu našeg stola. U ove svrhe postavite kursor u ovo polje. Zatim popijte lijevu tipku miša i označite područje na listu u kojem se matrica nalazi. Kao što vidite, podaci o koordinatorima za plasman automatski se unose u polje prozora. Nakon završetka ovog zadatka, bilo bi najočitije kliknu na dugme UreduAli ne žurite. Činjenica je da je klik na ovo dugme ekvivalentno primjeni naredbe Ući. Ali prilikom rada sa nizovima nakon završetka ulazne formule ne biste trebali kliknuti na gumb Ućii napravite skup kombinacije ključeva Ctrl + Shift + Enter. Izvedite ovu operaciju.

  6. Dakle, nakon toga, program iznosi proračune i na izlazu u unaprijed odabranom području imamo matricu, obrnuto ovo.

  7. Sada ćemo morati umnožiti obrnutu matricu na matricu B.koji se sastoji od jednog stupca vrijednosti raspoređenih nakon znaka "jednako" U izrazima. Za množenje tablica u Excelu postoji i zasebna funkcija koja se zove Mumeset. Ovaj operater ima sljedeću sintaksu:

    Majka (array1; array2)

    Izdvajamo raspon, u našem slučaju koji se sastoji od četiri ćelije. Sledeće ponovo pokretanje Magistar funkcijaPritiskom na ikonu "Umetnite funkciju".

  8. U kategoriji "Matematičko"Započeo Masters funkcionira, Dodijelite ime "MUMZNOM" i kliknite na gumb Uredu.

  9. Aktiviran je prozor funkcije argumenata. Mumeset. U polju "Massive1" Uvodimo koordinate naše reverzne matrice. Za to, kao i zadnji put postavili smo kursor na polje i sa lijevim gumbom miša, ističemo kursoru odgovarajuću tablicu. Izvodi se slična akcija koja se koordinate u polju napravi "Massive2", samo ovaj put raspoređuju vrijednosti stupca B.. Nakon što su gore navedene akcije, ne žurite na dugme ponovo Uredu ili ključ Ući, i upišite kombinaciju ključeva Ctrl + Shift + Enter.

  10. Nakon ove akcije u unaprijed odabranoj ćeliji bit će prikazani korijeni jednadžbe: X1., X2, X3. i X4.. Oni će biti smješteni dosljedno. Dakle, možemo reći da smo riješili ovaj sistem. Da bi provjerio ispravnost rješenja, dovoljno je zamijeniti podatke u originalni sistem izražavanja umjesto odgovarajućih korijena. Ako se poštuje jednakost, to znači da se zastupljeni sistem jednadžbi ispravno riješi.

    11. Metoda 2: Izbor parametara

    Druga poznata metoda za rješavanje sistema jednadžbi u Excelu je primjena metode odabira parametara. Suština ove metode je pronaći od suprotnog. To je na osnovu rezultata, proizvedemo nepoznati argument. Na primjer, koristimo kvadratnu jednadžbu


    Ovaj se rezultat može provjeriti i zamjenjujući ovu vrijednost u riješenom izrazu umjesto vrijednosti x..

    Metoda 3: Metoda krema

    Sada pokušamo da rešimo sistem jednadžbi od strane krmera. Na primjer, uzmite isti sistem koji je korišten u 1. metoda 1.:

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

    1. Kao i na prvi način, napravimo matricu SVEDOK JOVANOVIĆ - ODGOVOR: Iz koeficijenata jednadžbi i tablice B. iz vrijednosti koje stoje nakon znaka "jednako".

    2. Dalje, napravimo još četiri stola. Svaki od njih je kopija matrice SVEDOK JOVANOVIĆ - ODGOVOR:, Samo ove kopije naizmjenično jedan stupac zamjenjuje se tablicom. B.. Prva tabela je prvi stupac, drugi stol - drugi itd.

    3. Sada moramo izračunati odrednice za sve ove tablice. Sistem jednadžbi imat će rješenja samo ako će sve odrednice imati vrijednost koja nije nula. Da biste izračunali ovu vrijednost u Excelu, postoji zasebna funkcija - Mopred. Sintaksa ovog operatera je sljedeći:

      Mobred (niz)

      Dakle, poput funkcije Mesing, Jedini argument se navodi na tablicu koja se obrađuje.

      Dakle, ističemo ćeliju u kojoj će odrednica prve matrice biti izlazna. Zatim kliknite na ljubazno dugme na prethodnim metodama "Umetnite funkciju".

    4. Aktiviran je prozor Masters funkcionira. Idite u kategoriju "Matematičko" a među popisom operatora raspoređuju tamo "Mopred". Nakon toga kliknimo na dugme Uredu.

    5. Počinje prozor funkcije argumenata Mopred. Kao što vidite, ima samo jedno polje - "Array". U ovom polju unesite adresu prve pretvorene matrice. Da biste to učinili, postavite kursor na polje, a zatim odaberite Raspon matrice. Nakon toga kliknimo na dugme Uredu. Ova funkcija prikazuje rezultat u jednu ćeliju, a ne nizu, tako da nije potrebno pribjeći izračunu da pritisnete kombinaciju tipki Ctrl + Shift + Enter.

    6. Funkcija izračunava rezultat i prikazuje ga u unaprijed odabranu ćeliju. Kao što vidimo, u našem slučaju odrednica je jednaka -740 , Odnosno, nije jednak nuli, što nam odgovara.

    7. Slično tome, izračunavamo izračun odrednica za ostala tri tabla.

    8. U završnoj fazi izračunava se odrednica primarne matrice. Postupak se odvija cijelo vrijeme istog algoritma. Kao što vidimo, odrednica primarne tablice razlikuje se i od nule, što znači da se matrica smatra nevegenerate, odnosno sustav jednadžbi su rješenja.

    9. Sada je vrijeme da pronađete korijene jednadžbe. Korijen jednadžbe bit će jednak omjeru odrednice odgovarajuće pretvorene matrice do odrednice primarne tablice. Dakle, podjele naizmjenično sva četiri odrednice pretvorenih matrica na broj -148 Koja je odrednica izvorne tablice, dobit ćemo četiri korijena. Kao što vidimo, oni su jednaki vrijednosti 5 , 14 , 8 i 15 . Tako se tačno podudaraju sa korijenima koje smo pronašli pomoću obrnute matrice u 1. metoda 1.To potvrđuje ispravnost rješenja sustava jednadžbe.

    Metoda 4: Gauss metoda

    Riješite sistem jednadžbi možete primijeniti i putem Gauss metode. Na primjer, uzmemo jednostavniji sistem jednadžbi tri nepoznate:

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

    1. Opet dosljedno pisati koeficijente u tablici SVEDOK JOVANOVIĆ - ODGOVOR:i besplatni članovi koji se nalaze nakon znaka "jednako" - u stolu B.. Ali ovaj put je okupio obje tablice, jer će biti potrebno za rad u budućnosti. Važno stanje je u prvoj ćeliji matrice SVEDOK JOVANOVIĆ - ODGOVOR: Vrijednost je bila različita od nule. U suprotnom slučaju, linije treba preurediti.

    2. Kopirajte prvi niz dviju povezanih matrica u donju liniju (za jasnoću možete preskočiti jednu liniju). U prvoj ćeliji koja se nalazi u liniji je čak niža od prethodne, uvodimo sljedeću formulu:

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

      Ako ste matrice postavili na drugačiji način, tada će adrese ćelija formule imati drugu vrijednost, ali možete ih izračunati upoređivanjem s tim formulama i slikama koje su ovdje date.

      Nakon unosa formule, označite cijeli raspon ćelija i kliknite kombinaciju tipki Ctrl + Shift + Enter. Formula za rješavanje primjenjivat će se na red i bit će ispunjeno vrijednostima. Dakle, izvršili smo oduzimanje iz drugog reda prvog pomnoženog omjera prvih koeficijenata dvaju prvih izraza sistema.

    3. Nakon toga kopirajte rezultirajuće niz i ubacite je u donju liniju.

    4. Izaberite dva prva retka nakon linije koja nedostaje. Kliknite na gumb "Kopija"koja se nalazi na vrpci na kartici "Dom".

    5. Preskočimo niz nakon posljednjeg unosa na listu. Odaberite prvu ćeliju u sljedećem retku. Kliknite desno dugme miša. U otvorenom kontekstnom meniju donećete kursor na stavku "Poseban umetak". Na gore navedenom popisu odaberite položaj "Vrijednosti".

    6. Do sljedećeg retka uvodimo formulu niza. To omogućava oduzimanje od trećeg reda prethodne grupe podataka druge retke pomnoženo u odnosu drugog koeficijenta trećeg i drugog niza. U našem slučaju formula će imati sljedeći obrazac:

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

      Nakon unosa formule odaberite cijeli asortiman i primijenite kombinaciju tipke Ctrl + Shift + Enter.

    7. Sada biste trebali izvesti unatrag naprijed prema Gauss metodi. Preskočimo tri retka od posljednjeg unosa. U četvrtoj liniji uvodimo formulu polja:

      Dakle, podijelimo najnoviji niz izračunati nas na trećem koeficijentu. Nakon što su postigli formulu, ističemo čitavu liniju i kliknemo tipku za tastaturu Ctrl + Shift + Enter.

    8. Podižemo se na niz i ulazimo u sljedeću formulu niza u sebi:

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

      Kombinacijom uobičajene tipke kliknemo da primijenimo Formulu Array.

    9. Podignite drugu liniju iznad. U njoj uvode formulu niza sljedećeg obrasca:

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

      Ponovo izdvojite cijeli niz i koristite prečicu tastature Ctrl + Shift + Enter.

    10. Sada gledamo brojeve koji su se pokazali u posljednjem stupcu posljednjeg bloka redaka koji su nas ranije izračunali. To je ovi brojevi ( 4 , 7 i 5 ) Biće korijeni ovog sistema jednadžbi. Moguće provjeriti, zamjenjujući ih umjesto vrijednosti. X1., X2 i X3. U izrazu.

    Kao što vidimo, u Exceleu, sustav jednadžbi može se riješiti na više načina, od kojih svaka ima svoje prednosti i nedostatke. Ali sve ove metode mogu se 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 nula. U preostalim slučajevima, sami korisnik čeka da odluči kojom opcijom smatra prikladnijim za sebe.

Dodatak "Rešenje Excel" Rešenje "je analitički alat koji nam omogućava brzo i lako odrediti kada i koji rezultat dobijamo pod određenim uvjetima. Opcije za pronalaženje rješenja rješenja mnogo je veće nego što mogu pružiti "izbor parametara" u Excelu.

Glavne razlike između pretraživanja rješenja i odabira parametra:

  1. Izbor više parametara u Excelu.
  2. Prekrivajući uslove za ograničavanje promjena u ćelijama koje sadrže varijabilne vrijednosti.
  3. Sposobnost korištenja u slučajevima kada može biti mnogo rješenja jednog zadatka.

Primjeri i zadaci za pronalaženje rješenja u Excelu

Razmotrite analitičke mogućnosti nadgradnje. Na primjer, morate nakupljati 14.000 dolara za 10 godina. Za 10 godina, želite odložiti 1000 $ na račun Depozita na 1000 USD na depozitnom računu na 5% godišnje. Ispod u izgrađenom tablici u Excelu, koji je jasno vidljiv ostatku akumuliranih sredstava za svaku godinu. Kao što se može vidjeti pod takvim uvjetima depozitnog računa i doprinosa za akumulaciju, cilj se neće postići ni nakon 10 godina. Prilikom rješavanja ovog zadatka možete ići na dva načina:
  1. Pronađite banku koja nudi veću kamatnu stopu na depozite.
  2. Povećajte veličinu godišnjih akumulativnih doprinosa na bankovni račun.

Možemo promijeniti promjenjive vrijednosti u ćelijama B1 i B2 kako birati potrebne uvjete za akumuliranje potrebne količine novca.

Podrška "Pretraživanje rešenja" - omogućava nam da istovremeno koristimo 2 ove mogućnosti da brzo simuliramo najoptimalnije uslove za postizanje cilja. Za ovo:


Kao što vidite, program je malo povećao kamatnu stopu i zbroj godišnjih doprinosa.



Ograničenje parametara prilikom traženja rješenja

Pretpostavimo da ste otišli u banku sa ovim stolom, ali banka odbija podići vašu kamatnu stopu. U takvim slučajevima moramo znati koliko ćemo morati povećati iznos godišnje ulaganja. Moramo uspostaviti granicu u ćeliji s jednom promjenjivom vrijednošću. Ali prije pokretanja promijenite vrijednosti u promjenjivim ćelijama na početnu: u B1 za 5%, a u B2 na -1000 $. A sada radimo sledeće.

Značajan dio zadataka koji se rješavaju pomoću proračunskih tablica sugerira da će otkriti željeni rezultat, korisnik već ima barem neke izvorne podatke. Međutim, Exsel 2010 ima potrebne alate s kojima možete riješiti ovaj zadatak naprotiv - da odaberete potrebne podatke da biste dobili potreban rezultat.

"Pretraživanje rešenja" i jedan je od ovih alata koji su prikladni za "zadatke optimizacije". A ako ranije niste morali koristiti, sada je vrijeme da to popravite.

Dakle - započnite s ugradnjom ove nadgradnje (jer se neće pojaviti samostalno). Srećom, sada je moguće to učiniti dovoljno i brzo - otvorite meni "Usluga", a već u njemu "nadgradnju"

Ostat će samo u stupcu "Upravljanje", navedite "Excel dodatak", a nakon pritiska na tipku "Go".

Nakon ove jednostavne akcije, u podacima će se prikazati "rješenje" aktiviranje aktiviranja aktivacije. Kao što je prikazano na slici

Pogledajmo kako se rješenja u Excelu 2010 pravilno koriste, na nekoliko jednostavnih primjera.

Primjer prvog .

Pretpostavimo da preuzmete poštu šefa velikog proizvodnog odjela i morate pravilno distribuirati nagradu zaposlenima. Pretpostavimo da je ukupni iznos nagrada 100.000 rubalja, a potrebno je da su premije proporcionalne plaći.

To je, sada moramo odabrati pravi koeficijent proporcionalnosti kako bismo utvrdili veličinu nagrade u odnosu na platu.

Prije svega, potrebno je brzo izvući (ako još nije) tablica na kojoj će se izvor formule i podaci biti pohranjeni, u skladu s kojima će biti moguće dobiti željeni rezultat. Za nas je ovaj rezultat ukupni iznos nagrade. A sada je pažnja ciljna C8 ćelija trebala biti uz pomoć formula vezanih za željenu varijabilnu ćeliju pod adresom E2. Ovo je kritično. U primjeru, povezujemo ih koristeći intermedijarne formule, koji su odgovorni za izračunavanje nagrade svakom zaposlenom (C2: C7).

Sada možete aktivirati "pretragu odluke". Otvara se novi prozor u kojem trebamo odrediti potrebne parametre.

Pod " 1 »To je naznačeno našom ciljnom ćelijom. Može biti samo jedan.

« 2 "To su moguće optimizacije optimizacije. Možete odabrati "Maksimalno", "Minimum" ili "specifične" moguće vrijednosti. A ako trebate konkretno, mora biti naveden u odgovarajućem grafikonu.

« 3 »- Promjenjive ćelije mogu biti donekle (cijeli raspon ili odvojeno navedene adrese). Uostalom, Excel će raditi s njima, okrećući se prema opcijama tako da vrijednost navedena u ciljnoj ćeliji je.

« 4 "- Ako trebate postaviti ograničenja, trebali biste koristiti gumb" Dodaj ", ali pogledat ćemo malo kasnije.

« 5 "- Prelaz gumba na interaktivno računanje na osnovu programa koji smo naveli.

Ali sada se vratimo na sposobnost promjene zadatka, pomoću gumba za dodavanje. Ova je faza prilično odgovorna (ne manja od izgradnje formula), jer je to ograničenje koje omogućava dobijanje ispravnog rezultata izlaza. Sve se ovdje učini kao pogodno, tako da ih možete postaviti ne samo za cijeli raspon, već i za određene ćelije.

Da biste to učinili, možete koristiti brojne određene (i poznate korisnike Excel 2010) znakove "\u003d", "\u003e \u003d", "<=», а также варианты «цел» (от «целое»), «бин» («бинарное» или же «двоичное»), «раз» («все разные»).

Ali u našem primjeru, ograničenje može biti samo jedan - pozitivan koeficijent. Možete ga postaviti naravno na nekoliko načina - ili pomoću "Dodaj" (ono što se naziva "izričito navedite granicu") ili jednostavno naznačite funkciju ponašanja "napravite varijable bez ograničenja ne-negativnih." To se može učiniti u dodatku "Pretraživanje rješenja" klikom na gumb "Parameters".

Usput, nakon potvrđivanja parametara i pokretanje programa (tipka "RUN") možete vidjeti rezultat u tablici. Tada će program prikazati prozor rezultata pretraživanja.

Ako je demonstrirani rezultat u potpunosti prikladan za vas, tada će ga ostaviti samo da ga potvrdi (tipka "OK") koja ispravlja rezultat u vašem stolu. Ako vam nešto u proračunima ne odgovara, onda morate otkazati rezultat (tipka "Odustani"), vratite se na prethodno stanje našeg stola i ispravite greške.

Ispravno rješenje zadatka primjera trebalo bi da ispadne ovo

Vrlo je važno - dobiti ispravan rezultat, čak i uz najmanju promjenu izvornih podataka, potrebno je ponovo pokrenuti "Traži rješenja".

Da biste detaljnije iskoristili kako ovaj program važi, pogledajmo još jedan primjer.

Pretpostavimo da ste vlasnik velikog poduzeća za namještaj i potrebno je uspostaviti proizvodnju na takav način da dobije najveći mogući profit. Proizvodite samo police za knjige, dok samo dva modela - "A" i "B", čija je proizvodnja ograničena isključivo prisutnošću (ili odsustvom) visokokvalitetnih ploča, kao i strojno vrijeme (obrada na stroju).

Model "A" zahtijeva 3 m 3 ploče, a model "B" je 1 m 3 Više (to je - 4). Od vaših dobavljača dobijate maksimalno 1.700 m 3 ploče. Istovremeno, model "A" kreira se za 12 minuta rada stroja i "B" - za 30 minuta. Samo nedelju dana mašina može raditi ne više od 160 sati.

Pitanje je - koliko proizvoda (i koji model) treba proizvesti firmu za tjedan dana kako bi dobio najveću moguću dobit ako polica "A" daje 60 rubalja, i "u" - 120?

Budući da je poznato redom akcije, počinjemo stvaranje tablice s podacima i formulama koje su vam potrebne. Lokacija ćelija, kao i prije, možete postaviti po vlastitom nahođenju. Ili koristiti naše

Na bilo koji pogodan način pokrećemo našu "pretragu rješenja", unesite podatke koji proizvode.

Dakle, razmislite šta imamo. Ciljna ćelija F7 sadrži formulu koja će izračunati profit. Parametar optimizacije postavljen je na maksimum. Među promjenama ćelija imamo "F3: G3". Ograničenja - Sve otkrivene vrijednosti moraju biti cijeli brojevi, ne-negativni, ukupni iznos potrošenog vremena stroja ne prelazi 160 (naša ćelija D9), količina sirovina ne prelazi 1700 (Cell D8).

Naravno, u ovom slučaju bilo je moguće odrediti adresu ćelija, već direktno registrirati potrebne digitalne vrijednosti, ali ako koristite adrese, promjene u ograničenjima mogu se provesti u tablici, koja će pomoći Oslonite se na profit ovog preduzeća u budućnosti, prilikom promene izvornih podataka.

Aktiviramo program i priprema rješenje.

Međutim, ovo nije jedino rješenje i možda ćete možda iskočiti još jedan rezultat. To se može pojaviti čak i ako su svi podaci navedeni istiniti i nije bilo grešaka u formulama.

Da. Može se dogoditi čak i ako smo rekli program za pretraživanje cjelina broj. A ako se iznenada dogodilo, potrebno je jednostavno izvršiti dodatnu konfiguraciju "Traži rješenja". Otvorite prozor "Traži za rješenja" i unesite "parametre".

Naš gornji parametar odgovoran je za tačnost. Što je manje, to je veća tačnost i u našem slučaju značajno povećava šanse za cijeli broj. Drugi parametar ("zanemaruju cijeli broj ograničenja") i daje odgovor na pitanje kako bismo mogli dobiti takav odgovor tako da je cijeli broj indikativno naznačio u upitu. "Pretraživanje rješenja" jednostavno je zanemarilo ovo ograničenje zbog činjenice da su mu rekli proširene postavke.

Zato budite izuzetno pažljivi u budućnosti.

Treće i možda posljednji primjer. Pokušajmo minimizirati troškove transportne kompanije pomoću pretraživanja rješenja u Excelu 2010.

Dakle, građevinska kompanija daje nalog za prijevoz pijeska koji se uzima iz 3 dobavljača (kamenoloma). Mora se dostaviti 5 različitih potrošača (koji su gradilišta). Troškovi isporuke uključeni su u troškove objekta, tako da je naš zadatak osigurati isporuku tereta na gradilište sa minimalnim troškovima.

Imamo - opskrbu pijeska u karijeri, potreba za gradilištima u pijesku, troškove transporta "dobavljača - potrošača".

Potrebno je pronaći shemu za optimalnu dostavu (gdje i odakle) u kojem bi ukupni trošak prijevoza bili minimalni.

Sive ćelije našeg stola sadrže formule iznosa na stupcima i žicama, a ciljna ćelija je formula za opće brojanje troškova isporuke isporuke. Pokrenite našu "pretragu rješenja" i izvršite potrebne postavke

Nakon toga nastavite da pronađete rješenje ovog zadatka

Međutim, nećemo zaboraviti da prilično često transportni zadaci mogu biti komplicirani nekim dodatnim ograničenjima. Pretpostavimo da se komplikacija nastala na putu, a sada iz karijere 2 jednostavno je tehnički nemoguće dostaviti teret na gradilište 3. Da biste uzeli u obzir ovo, samo morate dodati dodatnu granicu "$ D $ 13 \u003d 0". A ako sada pokrenete program, rezultat će biti različit

Konačno, ostaje da se kaže samo o odabiru načina odlučivanja. A ako je zadatak zaista vrlo težak, najvjerovatnije je dobiti potreban rezultat, najvjerovatnije ćete odabrati potrebnu metodu rješenja.

To je sve na ovom pitanju.

Tražili smo rješenja u Excelu 2010 - za rješavanje složenih zadataka

Pročitajte i