Riješite funkciju u Excelu. Omogući, koristi slučaj sa snimkama zaslona

“Traži rješenje” je Excel dodatak, putem kojeg je moguće izabrati najbolje rješenje problema temeljeno na navedenim ograničenjima. Funkcija omogućuje raspored zaposlenika, raspodjelu troškova ili ulaganja. Poznavanje načina na koji ova značajka radi uštedjet će vam vrijeme i trud.

Što je Search for Solutions

U kombinaciji s raznim drugim opcijama u Excelu postoji jedna manje popularna, ali iznimno potrebna funkcija “Traži rješenje”. Unatoč činjenici da ga nije lako pronaći, upoznavanje i korištenje pomaže u rješavanju mnogih problema. Opcija obrađuje podatke i daje optimalno rješenje od dopuštenih. U članku je opisano kako izravno funkcionira Search for a Solution.

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

Unatoč učinkovitosti, predmetna opcija nije na istaknutom mjestu na alatnoj traci ili kontekstnom izborniku. Većina korisnika koji rade u Excelu nisu svjesni njegove prisutnosti. Prema zadanim postavkama, ova funkcija je onemogućena, da biste je prikazali, izvršite sljedeće radnje:

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

   

4. Na monitoru će se prikazati dodatni prozor "Dodaci". Označite okvir pored željene funkcije i kliknite OK.
5. Željena funkcija pojavit će se na vrpci desno od odjeljka "Podaci".

O modelima

Ove će informacije biti od velike koristi onima koji se tek upoznaju s pojmom „optimizacijski model“. Prije korištenja "Potrage za rješenjem", preporuča se proučiti materijale o metodama izgradnje modela:

• opcija koja se razmatra omogućit će identificiranje najbolje metode za dodjelu sredstava za investicije, punjenje prostora, nabavu robe ili druge radnje gdje je potrebno pronaći najbolje rješenje.
• “Optimalna metoda” bi u takvoj situaciji značila: povećanje prihoda, smanjenje troškova, poboljšanje kvalitete itd.

Tipični zadaci optimizacije:

• Određivanje proizvodnog plana, tijekom kojeg će dobit od prodaje puštene robe biti maksimalna.
• Određivanje karata prijevoza, tijekom kojih su troškovi prijevoza minimizirani.
• Tražite raspodjelu više strojeva za različite vrste poslova, kako bi se smanjili troškovi proizvodnje.
• Određivanje najkraćeg roka za završetak radova.

Važno! Za formalizaciju zadatka potrebno je izraditi model koji odražava glavne parametre predmetnog područja. 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 vrpcu, morate razumjeti kako opcija radi. Na primjer, postoje podaci o prodaji robe navedene u tablici. Zadatak je dodijeliti popust za svaku stavku, što bi bilo 4.5 milijuna rubalja. Parametar se prikazuje unutar ćelije koja se zove cilj. Na temelju njega izračunavaju se drugi parametri.

Naš će zadatak biti izračunati popust kojim se množe iznosi za prodaju raznih proizvoda. Ova 2 elementa povezana su 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 njezino postavljanje

Kada je formula spremna, trebate izravno upotrijebiti samu funkciju:

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

2. Otvorit će se “Opcije” u kojima su postavljene potrebne postavke. U retku "Optimiziraj funkciju cilja:" trebate navesti ćeliju u kojoj se prikazuje zbroj popusta. Koordinate je moguće propisati sami ili odabrati iz dokumenta.

3. Zatim morate prijeći na postavke ostalih parametara. U odjeljku “Do:” moguće je postaviti maksimalno i minimalno ograničenje ili točan broj.

4. Zatim se popunjava polje “Promjena vrijednosti varijabli:”. Ovdje se upisuju podaci željene ćelije koja sadrži određenu vrijednost. Koordinate se registriraju neovisno ili se klikne odgovarajuća ćelija u dokumentu.

5. Zatim se uređuje kartica “Prema ograničenjima:” gdje se postavljaju ograničenja primijenjenih podataka. Na primjer, isključeni su decimalni razlomci ili negativni brojevi.

6. Nakon toga se otvara prozor koji vam omogućuje dodavanje ograničenja u izračunima. Početni redak sadrži koordinate ćelije ili cijelog raspona. U skladu s uvjetima zadatka, naznačeni su podaci željene ćelije, gdje se prikazuje indikator popusta. Zatim se određuje znak usporedbe. Postavlja se na "veće ili jednako" tako da konačna vrijednost nije s predznakom minus. “Ograničenje” postavljeno u retku 3 je 0 u ovoj situaciji. Također je moguće postaviti ograničenje s “Dodaj”. Sljedeći koraci su isti.

7. Kada su gornji koraci dovršeni, postavljeno ograničenje se pojavljuje u najvećem retku. Popis može biti velik i ovisit će o složenosti izrač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 dijelu nalazi se opcija “Opcije” koja vam to omogućuje.

9. U postavkama možete postaviti "Točnost ograničenja" i "Granice rješenja". U našoj situaciji nema potrebe za korištenjem ovih opcija.

10. Kada su postavke dovršene, pokreće se sama funkcija – kliknite “Pronađi rješenje”.

11. Nakon što program izvrši tražene izračune i izda konačne izračune u potrebne ćelije. Zatim se otvara prozor s rezultatima, gdje se rezultati spremaju / poništavaju ili se parametri pretraživanja konfiguriraju prema novom. Kada podaci zadovolje zahtjeve, pronađeno rješenje se sprema. Ako unaprijed označite okvir "Povratak na dijaloški okvir opcija pretraživanja rješenja", otvorit će se prozor s postavkama funkcija.

12. Postoji mogućnost da su se izračuni pokazali pogrešnim ili postoji potreba za promjenom početnih podataka kako bi se dobili drugi pokazatelji. U takvoj situaciji trebate ponovno otvoriti prozor postavki i još jednom provjeriti podatke.
13. Kada su podaci točni, može se koristiti alternativna metoda. U tu svrhu morate kliknuti trenutnu opciju i odabrati najprikladniju metodu s popisa koji se pojavi:

• Pronalaženje rješenja korištenjem generaliziranog gradijenta za nelinearne probleme. Prema zadanim postavkama koristi se ova opcija, ali moguće je koristiti i druge.
• Pronalaženje rješenja linearnih problema na temelju simpleks metode.
• Korištenje evolucijskog pretraživanja za dovršavanje zadatka.

Pažnja! Kada gore navedene opcije nisu uspjele riješiti zadatak, trebali biste ponovno provjeriti podatke u postavkama jer je to često glavna pogreška u takvim zadacima.

14. Kada se dobije željeni popust, ostaje ga primijeniti kako bi se izračunao iznos popusta za svaki artikl. U tu svrhu označen je početni element stupca "Iznos popusta", ispisana je formula «=D2*$G$2» i pritisnite “Enter”. Znakovi dolara stavljaju se tako da se G2 ne mijenja kada se formula razvuče na susjedne retke.

15. Sada će se dobiti iznos popusta za početni artikl. Zatim trebate pomaknuti kursor preko kuta ćelije, kada postane "plus", pritisne se LMB i formula se rasteže do potrebnih redaka.
16. Nakon toga, stol će biti konačno spreman.

Učitaj/spremi opcije pretraživanja

Ova je opcija korisna pri primjeni različitih opcija ograničenja.

1. U izborniku opcija Finder rješenja kliknite Učitaj/Spremi.
2. Unesite raspon za područje modela i kliknite Spremi ili Učitaj.

Prilikom spremanja modela upisuje se referenca na 1 ćeliju praznog stupca gdje će biti smješten optimizacijski model. Tijekom učitavanja modela unosi se referenca na cijeli raspon koji sadrži optimizacijski model.

Važno! Za spremanje zadnjih postavki u izborniku Mogućnosti rješenja sprema se radna knjiga. Svaki list u njemu ima vlastite opcije dodatka Solver. Osim toga, moguće je postaviti više od 1 zadatka za list klikom na gumb "Učitaj ili spremi" kako biste spremili pojedinačne zadatke.

Jednostavan primjer korištenja Solvera

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

Varijable modela označene su zelenom bojom. Funkcija cilja označena je crvenom bojom. Ograničenja: najmanjim brojem spremnika (većim ili jednakim 110) i težinom (=SUMPROIZVOD(B8:C8,B6:C6) – ukupna težina tare u kontejneru.

Po analogiji, razmatramo ukupni volumen: =SUMPROIZVOD(B7:C7,B8:C8). Takva je formula nužna za postavljanje ograničenja ukupnog volumena spremnika. Zatim se kroz “Traži rješenje” unose poveznice na elemente s varijablama, formulama i samim indikatorima (ili poveznice na određene ćelije). Naravno, broj kontejnera je cijeli broj (to je također ograničenje). Pritisnemo "Pronađi rješenje", kao rezultat toga nalazimo takav broj spremnika kada je ukupna masa maksimalna i uzeta su u obzir sva ograničenja.

Traženje rješenja nije uspjelo pronaći rješenja

Takva se obavijest pojavljuje kada predmetna funkcija nije pronašla kombinacije varijabilnih rezultata koji zadovoljavaju svako ograničenje. Kod Simplex metode vrlo je moguće da nema rješenja.

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

Na primjer, pri korištenju nelinearne metode elementi tablice s varijablama nisu popunjeni, a funkcija nije pronašla rješenja. To ne znači da rješenja nema. Sada se, uzimajući u obzir rezultate određene procjene, u elemente unose drugi podaci s varijablama koje su bliske dobivenim.

U bilo kojoj situaciji, prvo biste trebali ispitati model za nepostojanje sukoba ograničenja. Često je to povezano s nepravilnim odabirom omjera ili graničnog pokazatelja.

U gornjem primjeru, maksimalni pokazatelj volumena je 16 kubičnih metara. m umjesto 32, jer je takvo ograničenje u suprotnosti s pokazateljima za minimalni broj sjedala, budući da će odgovarati broju od 16,5 kubičnih metara. m.

Zaključak

Na temelju 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 ova opcija u početku skrivena, zbog čega većina korisnika nije svjesna njezine prisutnosti. Osim toga, funkciju je prilično teško naučiti i koristiti, ali uz odgovarajuće istraživanje, donijet će velike prednosti i olakšati izračune.