Riješite funkciju u Excelu. Omogućite, koristite slučaj sa snimcima ekrana

“Traži rješenje” je Excel dodatak, preko kojeg je moguće odabrati najbolje rješenje problema na osnovu navedenih ograničenja. Funkcija omogućava raspoređivanje zaposlenih, raspodjelu troškova ili investicija. Znajući kako ova funkcija radi, uštedjet ćete vrijeme i trud.

Šta je Search for Solutions

U kombinaciji sa raznim drugim opcijama u Excel-u, postoji i jedna manje popularna, ali izuzetno neophodna funkcija „Traži rešenje“. Unatoč činjenici da nije lako pronaći ga, upoznavanje i korištenje pomaže u rješavanju mnogih problema. Opcija obrađuje podatke i daje optimalno rješenje od dozvoljenih. Članak opisuje kako direktno funkcionira Potraga za rješenjem.

Kako uključiti funkciju "Traži rješenje".

Uprkos efikasnosti, dotična opcija nije na istaknutom mestu na traci sa alatkama ili kontekstnom meniju. Većina korisnika koji rade u Excelu nisu svjesni njegovog prisustva. Prema zadanim postavkama, ova funkcija je onemogućena, da biste je prikazali, izvršite sljedeće radnje:

1. Otvorite "File" klikom na odgovarajući naziv.
2. Kliknite na odjeljak "Postavke".
3. Zatim odaberite pododjeljak "Dodaci". Ovdje će se prikazati svi dodaci programa, ispod će se pojaviti natpis „Upravljanje“. Na njegovoj desnoj strani nalazit će se iskačući meni u kojem biste trebali odabrati “Excel dodaci”. Zatim kliknite na “Idi”.
   

   

4. Dodatni prozor “Dodaci” će biti prikazan na monitoru. Označite okvir pored željene funkcije i kliknite na OK.
5. Željena funkcija će se pojaviti na traci desno od odjeljka „Podaci“.

O modelima

Ove informacije će biti izuzetno korisne za one koji se tek upoznaju sa konceptom „modela optimizacije“. Prije korištenja "Traži rješenje", preporučuje se proučiti materijale o metodama izgradnje modela:

• opcija koja se razmatra omogućiće da se identifikuje najbolji način za izdvajanje sredstava za investicije, utovar prostorija, snabdevanje robom ili druge radnje gde je potrebno pronaći najbolje rešenje.
• „Optimalna metoda“ u takvoj situaciji bi značila: povećanje prihoda, smanjenje troškova, poboljšanje kvaliteta itd.

Tipični zadaci optimizacije:

• Utvrđivanje plana proizvodnje u kojem će profit od prodaje puštene robe biti maksimalan.
• Određivanje transportnih karata, tokom kojih se troškovi transporta minimiziraju.
• Tražite distribuciju nekoliko mašina za različite vrste poslova, kako bi se smanjili troškovi proizvodnje.
• Određivanje najkraćeg roka za završetak posla.

Važno! Da bi se formalizirao zadatak, potrebno je kreirati model koji odražava glavne parametre predmetne oblasti. U Excelu, model je skup formula koje koriste varijable. Razmatrana opcija traži takve pokazatelje da je funkcija cilja veća (manja) ili jednaka navedenoj vrijednosti.

Pripremna faza

Prije postavljanja funkcije na traku, morate razumjeti kako opcija funkcionira. Na primjer, postoje informacije o prodaji robe navedene u tabeli. Zadatak je dodijeliti popust za svaki artikl, koji bi iznosio 4.5 miliona rubalja. Parametar se prikazuje unutar ćelije koja se zove cilj. Na osnovu toga se izračunavaju drugi parametri.

Naš zadatak će biti izračunati popust kojim se množe iznosi za prodaju raznih proizvoda. Ova 2 elementa su povezana formulom napisanom ovako: =D13*$G$2. Gdje je u D13 upisana ukupna količina za implementaciju, a $G$2 je adresa željenog elementa.

Korištenje funkcije i njeno postavljanje

Kada je formula spremna, morate direktno koristiti samu funkciju:

1. Morate se prebaciti na odjeljak „Podaci“ i kliknuti na „Traži rješenje“.

2. Otvoriće se „Opcije“ gde su postavljena potrebna podešavanja. U redu “Optimiziraj funkciju cilja:” treba navesti ćeliju u kojoj se prikazuje zbir popusta. Moguće je sami propisati koordinate ili izabrati iz dokumenta.

3. Zatim morate prijeći na postavke drugih parametara. U odeljku „Za:“ moguće je postaviti maksimalni i minimalni limit ili tačan broj.

4. Zatim se popunjava polje "Promjena vrijednosti varijabli:". Ovdje se unose podaci željene ćelije koja sadrži određenu vrijednost. Koordinate se registruju nezavisno ili se klikne na odgovarajuću ćeliju u dokumentu.

5. Zatim se uređuje kartica „Prema ograničenjima:“ u kojoj se postavljaju ograničenja za primijenjene podatke. Na primjer, decimalni razlomci ili negativni brojevi su isključeni.

6. Nakon toga otvara se prozor koji vam omogućava da dodate ograničenja u proračunima. Početna linija sadrži koordinate ćelije ili cijelog raspona. U skladu sa uslovima zadatka, prikazuju se podaci željene ćelije, gde se prikazuje indikator popusta. Tada se određuje znak poređenja. Postavlja se na “veće ili jednako” tako da konačna vrijednost nije sa predznakom minus. “Limit” postavljen u redu 3 je 0 u ovoj situaciji. Također je moguće postaviti ograničenje pomoću “Dodaj”. Sljedeći koraci su isti.

7. Kada su gore navedeni koraci dovršeni, postavljeno ograničenje se pojavljuje u najvećem redu. Lista može biti velika i ovisit će o složenosti proračuna, međutim, u određenoj situaciji dovoljan je 1 uvjet.

8. Osim toga, moguće je odabrati i druge napredne postavke. U donjem desnom uglu nalazi se opcija "Opcije" koja vam to omogućava.

9. U postavkama možete podesiti “Ograničenje preciznosti” i “Granice rješenja”. U našoj situaciji nema potrebe koristiti ove opcije.

10. Kada su podešavanja završena, sama funkcija se pokreće – kliknite na „Pronađi rešenje“.

11. Nakon što program izvrši potrebne proračune i izda završne proračune u traženim ćelijama. Zatim se otvara prozor s rezultatima u kojem se rezultati pohranjuju/poništavaju ili se parametri pretraživanja konfiguriraju prema novom. Kada podaci zadovolje zahtjeve, pronađeno rješenje se pohranjuje. Ako unaprijed označite okvir „Povratak na dijaloški okvir opcija pretraživanja rješenja“, otvorit će se prozor s postavkama funkcije.

12. Postoji mogućnost da se kalkulacije ispostavi da su pogrešne ili da postoji potreba za promjenom početnih podataka kako bi se dobili drugi pokazatelji. U takvoj situaciji morate ponovo otvoriti prozor postavki i još jednom provjeriti informacije.
13. Kada su podaci tačni, može se koristiti alternativni metod. U ove svrhe morate kliknuti na trenutnu opciju i odabrati najprikladniju metodu sa liste koja se pojavi:

• Pronalaženje rješenja korištenjem generaliziranog gradijenta za nelinearne probleme. Standardno se koristi ova opcija, ali je moguće koristiti i druge.
• Pronalaženje rješenja za linearne probleme na bazi simpleks metode.
• Korištenje evolucijske pretrage za dovršenje zadatka.

Pažnja! Kada gore navedene opcije nisu uspjele izaći na kraj sa zadatkom, trebali biste ponovo provjeriti podatke u postavkama, jer je to često glavna greška u takvim zadacima.

14. Kada dobijete željeni popust, ostaje da ga primenite za izračunavanje iznosa popusta za svaki artikal. U tu svrhu se ističe početni element kolone „Iznos popusta“, upisuje se formula «=D2*$G$2» i pritisnite “Enter”. Znakovi dolara se stavljaju tako da kada se formula rasteže na susedne linije, G2 se ne menja.

15. Sada će se dobiti iznos popusta za početni artikl. Zatim treba da pomerite kursor preko ugla ćelije, kada postane „plus“, pritisne se LMB i formula se razvuče na tražene linije.
16. Nakon toga stol će biti konačno spreman.

Učitaj/Sačuvaj opcije pretrage

Ova opcija je korisna kada se primjenjuju različite opcije ograničenja.

1. U meniju Opcije pronalaženja rešenja kliknite na Učitaj/Sačuvaj.
2. Unesite raspon za područje modela i kliknite Spremi ili Učitaj.

Prilikom pohranjivanja modela upisuje se referenca na 1 ćeliju prazne kolone u koju će biti smješten optimizacijski model. Tokom učitavanja modela, referenca se unosi na cijeli raspon koji sadrži optimizacijski model.

Važno! Da biste sačuvali poslednja podešavanja u meniju Opcije rešenja, radna sveska se čuva. Svaki list u njemu ima vlastite opcije dodatka Solver. Osim toga, moguće je postaviti više od 1 zadatka za list klikom na dugme „Učitaj ili sačuvaj“ kako bi se pojedinačni zadaci sačuvali.

Jednostavan primjer korištenja Solvera

Kontejner je potrebno napuniti kontejnerima tako da njegova masa bude maksimalna. Rezervoar ima zapreminu od 32 kubna metra. m. Napunjena kutija ima težinu od 20 kg, zapremina je 0,15 kubnih metara. m. Kutija – 80 kg i 0,5 cu. m. Potrebno je da ukupan broj kontejnera bude najmanje 110 kom. Podaci su organizovani ovako:

Varijable modela su označene zelenom bojom. Ciljna funkcija je istaknuta crvenom bojom. Ograničenja: prema najmanjem broju kontejnera (većim ili jednakim 110) i po težini (=SUMPROIZVOD(B8:C8,B6:C6) – ukupna težina tare u kontejneru.

Analogno, uzimamo u obzir ukupni volumen: =SUMPROIZVOD(B7:C7,B8:C8). Takva formula je neophodna za postavljanje ograničenja na ukupnu zapreminu kontejnera. Zatim se kroz “Traži rješenje” unose veze do elemenata sa varijablama, formulama i samim indikatorima (ili veze do određenih ćelija). Naravno, broj kontejnera je cijeli broj (to je također ograničenje). Pritisnemo "Pronađi rješenje", kao rezultat toga pronađemo toliki broj kontejnera kada je ukupna masa maksimalna i sva ograničenja su uzeta u obzir.

Pretraga rješenja nije uspjela pronaći rješenja

Takvo obavještenje se pojavljuje kada dotična funkcija nije pronašla kombinacije varijabilnih rezultata koje zadovoljavaju svako ograničenje. Kada se koristi Simplex metoda, sasvim je moguće da nema rješenja.

Kada se koristi metoda za rješavanje nelinearnih problema, u svim slučajevima počevši od početnih indikatora varijabli, to ukazuje da je moguće rješenje daleko od takvih parametara. Ako pokrenete funkciju s drugim početnim indikatorima varijabli, onda vjerojatno postoji rješenje.

Na primjer, kada se koristi nelinearna metoda, elementi tablice s varijablama nisu popunjeni, a funkcija nije pronašla rješenja. To ne znači da nema rješenja. Sada, uzimajući u obzir rezultate određene procjene, drugi podaci se unose u elemente sa varijablama koje su bliske primljenim.

U svakoj situaciji, prvo bi trebalo da ispitate model na odsustvo sukoba ograničenja. Često je to povezano s nepravilnim odabirom omjera ili ograničavajućeg indikatora.

U gornjem primjeru, indikator maksimalne zapremine je 16 kubnih metara. m umjesto 32, jer je takvo ograničenje u suprotnosti sa pokazateljima za minimalni broj mjesta, jer će odgovarati broju od 16,5 kubnih metara. m.

zaključak

Na osnovu toga, opcija “Traži rješenje” u Excelu pomoći će u rješavanju specifičnih problema koje je prilično teško ili nemoguće riješiti na uobičajene načine. Poteškoća u primjeni ove metode je u tome što je u početku ova opcija skrivena, zbog čega većina korisnika nije svjesna njenog prisustva. Osim toga, funkciju je prilično teško naučiti i koristiti, ali uz odgovarajuće istraživanje, ona će donijeti velike koristi i olakšati proračune.