Reši funkcijo v Excelu. Omogoči, uporabite primer s posnetki zaslona

“Iskanje rešitve” je Excelov dodatek, preko katerega je mogoče izbrati najboljšo rešitev problemov glede na podane omejitve. Funkcija omogoča razporejanje zaposlenih, porazdelitev stroškov ali investicij. Če veste, kako deluje ta funkcija, boste prihranili čas in trud.

Kaj je iskanje rešitev

V kombinaciji z različnimi drugimi možnostmi v Excelu obstaja ena manj priljubljena, a izjemno potrebna funkcija "Iskanje rešitve". Kljub temu, da ga ni enostavno najti, pa spoznavanje in uporaba pomagata pri reševanju številnih težav. Opcija obdela podatke in poda optimalno rešitev izmed dovoljenih. Članek opisuje, kako neposredno deluje Search for a Solution.

Kako vklopiti funkcijo »Išči rešitev«.

Kljub učinkovitosti zadevna možnost ni na vidnem mestu v orodni vrstici ali kontekstnem meniju. Večina uporabnikov, ki delajo v Excelu, se ne zaveda njegove prisotnosti. Ta funkcija je privzeto onemogočena, če jo želite prikazati, izvedite naslednja dejanja:

1. Odprite »Datoteko« s klikom na ustrezno ime.
2. Kliknite razdelek »Nastavitve«.
3. Nato izberite pododdelek »Dodatki«. Tukaj bodo prikazani vsi dodatki programa, spodaj bo prikazan napis »Upravljanje«. Na desni strani bo pojavni meni, kjer bi morali izbrati »Dodatki za Excel«. Nato kliknite »Pojdi«.
   

   

4. Na monitorju se prikaže dodatno okno »Dodatki«. Potrdite polje poleg želene funkcije in kliknite V redu.
5. Želena funkcija se prikaže na traku desno od razdelka »Podatki«.

O modelih

Te informacije bodo zelo koristne za tiste, ki se šele spoznavajo s pojmom »optimizacijski model«. Pred uporabo »Iskanja rešitve« je priporočljivo preučiti gradivo o metodah gradnje modelov:

• obravnavana možnost bo omogočila identifikacijo najboljšega načina za dodelitev sredstev za investicije, polnjenje prostorov, dobavo blaga ali druga dejanja, kjer je treba najti najboljšo rešitev.
• »Optimalna metoda« bi v takšni situaciji pomenila: povečanje dohodka, zmanjšanje stroškov, izboljšanje kakovosti itd.

Tipične optimizacijske naloge:

• Določitev proizvodnega načrta, med katerim bo dobiček od prodaje sproščenega blaga največji.
• Določanje transportnih kart, med katerimi so stroški prevoza minimizirani.
• Iskanje distribucije več strojev za različne vrste dela, tako da se zmanjšajo proizvodni stroški.
• Določitev najkrajšega časa za dokončanje del.

Pomembno! Za formalizacijo naloge je potrebno ustvariti model, ki odraža glavne parametre predmetnega področja. V Excelu je model nabor formul, ki uporabljajo spremenljivke. Obravnavana možnost išče takšne indikatorje, da je ciljna funkcija večja (manjša) ali enaka navedeni vrednosti.

Pripravljalna faza

Preden funkcijo postavite na trak, morate razumeti, kako možnost deluje. Na primer, v tabeli so navedeni podatki o prodaji blaga. Naloga je dodeliti popust za vsako postavko, ki bi znašal 4.5 milijona rubljev. Parameter je prikazan znotraj celice, imenovane cilj. Na podlagi tega se izračunajo drugi parametri.

Naša naloga bo izračunati popust, s katerim se pomnožijo zneski za prodajo različnih izdelkov. Ta dva elementa sta povezana s formulo, zapisano takole: =D13*$G$2. Kjer je v D13 zapisana skupna količina za izvedbo, $G$2 pa je naslov želenega elementa.

Uporaba funkcije in njena nastavitev

Ko je formula pripravljena, morate neposredno uporabiti samo funkcijo:

1. Preklopiti morate na razdelek »Podatki« in klikniti »Išči rešitev«.

2. Odpre se »Možnosti«, kjer nastavimo zahtevane nastavitve. V vrstici »Optimiziraj ciljno funkcijo:« določite celico, v kateri je prikazana vsota popustov. Koordinate si lahko predpišete sami ali izberete iz dokumenta.

3. Nato morate iti na nastavitve drugih parametrov. V rubriki “Do:” je možno nastaviti najvišje in najnižje omejitve ali točno število.

4. Nato se izpolni polje »Spreminjanje vrednosti spremenljivk:«. Tu se vnesejo podatki želene celice, ki vsebuje določeno vrednost. Koordinate se registrirajo neodvisno ali pa se klikne na ustrezno celico v dokumentu.

5. Nato se uredi zavihek “Glede na omejitve:”, kjer se nastavijo omejitve uporabljenih podatkov. Na primer, decimalni ulomki ali negativna števila so izključeni.

6. Po tem se odpre okno, ki vam omogoča dodajanje omejitev v izračunih. Začetna vrstica vsebuje koordinate celice ali celotnega obsega. Po pogojih naloge so prikazani podatki želene celice, kjer je prikazan indikator popusta. Nato se določi primerjalni znak. Nastavljena je na "večje ali enako", tako da končna vrednost ni z znakom minus. »Omejitev«, nastavljena v vrstici 3, je v tej situaciji 0. Omejitev je mogoče nastaviti tudi z »Dodaj«. Naslednji koraki so enaki.

7. Ko so zgornji koraki končani, se nastavljena omejitev prikaže v največji vrstici. Seznam je lahko velik in bo odvisen od kompleksnosti izračunov, vendar v določeni situaciji zadostuje 1 pogoj.

8. Poleg tega je mogoče izbrati druge napredne nastavitve. Na spodnji desni strani je možnost »Možnosti«, ki vam to omogoča.

9. V nastavitvah lahko nastavite »Natančnost omejitve« in »Meje rešitve«. V naši situaciji ni potrebe po uporabi teh možnosti.

10. Ko so nastavitve končane, se zažene sama funkcija – kliknite »Najdi rešitev«.

11. Ko program izvede zahtevane izračune in izda končne izračune v zahtevanih celicah. Nato se odpre okno z rezultati, kjer se rezultati shranijo/prekličejo ali pa se iskalni parametri konfigurirajo po novem. Ko podatki ustrezajo zahtevam, se najdena rešitev shrani. Če vnaprej označite polje »Vrnitev v pogovorno okno možnosti iskanja rešitve«, se odpre okno z nastavitvami funkcij.

12. Obstaja možnost, da so se izračuni izkazali za napačne ali pa je treba spremeniti začetne podatke, da bi dobili druge kazalnike. V takšni situaciji morate znova odpreti okno z nastavitvami in še enkrat preveriti podatke.
13. Ko so podatki točni, se lahko uporabi alternativna metoda. Za te namene morate klikniti trenutno možnost in s seznama, ki se prikaže, izbrati najprimernejšo metodo:

• Iskanje rešitve z uporabo generaliziranega gradienta za nelinearne probleme. Ta možnost je privzeto uporabljena, vendar je mogoče uporabiti druge.
• Iskanje rešitev linearnih problemov na osnovi simpleks metode.
• Uporaba evolucijskega iskanja za dokončanje naloge.

Pozor! Ko zgoraj navedene možnosti niso bile kos nalogi, morate ponovno preveriti podatke v nastavitvah, saj je to pogosto glavna napaka pri takih nalogah.

14. Ko je želeni popust prejet, ga je treba uporabiti za izračun zneska popustov za posamezen artikel. V ta namen je označen začetni element stolpca "Znesek popusta", zapisana je formula «=D2*$G$2» in pritisnite “Enter”. Znaki za dolar so odloženi tako, da se G2 ne spremeni, ko se formula raztegne na sosednje vrstice.

15. Zdaj bo pridobljen znesek popusta za začetni artikel. Nato premaknite kazalec čez kot celice, ko postane "plus", pritisnete LMB in formula se raztegne na zahtevane vrstice.
16. Po tem bo miza končno pripravljena.

Naloži/Shrani možnosti iskanja

Ta možnost je uporabna pri uporabi različnih možnosti omejitev.

1. V meniju Možnosti iskalnika rešitev kliknite Naloži/Shrani.
2. Vnesite obseg za območje modela in kliknite Shrani ali Naloži.

Pri shranjevanju modela se vnese sklic na 1 celico praznega stolpca, kamor bo postavljen optimizacijski model. Med nalaganjem modela se vnese sklic na celoten obseg, ki vsebuje optimizacijski model.

Pomembno! Za shranjevanje zadnjih nastavitev v meniju Možnosti rešitve se shrani delovni zvezek. Vsak list v njem ima lastne možnosti dodatka Solver. Poleg tega je možno nastaviti več kot eno nalogo za list s klikom na gumb »Naloži ali shrani«, da shranite posamezna opravila.

Preprost primer uporabe Solverja

Posodo je treba naložiti s posodami, tako da je njegova masa največja. Rezervoar ima prostornino 32 kubičnih metrov. m. Napolnjena škatla ima maso 20 kg, njena prostornina je 0,15 kubičnih metrov. m. Škatla – 80 kg in 0,5 cu. m. Zahteva se, da je skupno število posod najmanj 110 kosov. Podatki so organizirani takole:

Spremenljivke modela so označene z zeleno. Ciljna funkcija je označena z rdečo. Omejitve: po najmanjšem številu posod (večjem ali enakem 110) in po teži (=SUMPRODUCT(B8:C8,B6:C6) – skupna tara teža v zabojniku.

Po analogiji upoštevamo skupno prostornino: =SUMPRODUCT(B7:C7,B8:C8). Takšna formula je potrebna za določitev omejitve skupne prostornine posod. Nato se prek »Išči rešitev« vnesejo povezave do elementov s spremenljivkami, formulami in samimi indikatorji (ali povezave do določenih celic). Seveda je število kontejnerjev celo število (je tudi omejitev). Pritisnemo »Najdi rešitev«, zaradi česar najdemo takšno število posod, ko je skupna masa največja in so upoštevane vse omejitve.

Iskanje rešitve ni uspelo najti rešitve

Takšno obvestilo se prikaže, ko zadevna funkcija ni našla kombinacij spremenljivk rezultatov, ki izpolnjujejo vsako omejitev. Pri uporabi metode Simplex je povsem možno, da rešitve ni.

Ko se uporablja metoda za reševanje nelinearnih problemov, v vseh primerih izhajajoč iz začetnih indikatorjev spremenljivk, to pomeni, da je možna rešitev daleč od takih parametrov. Če zaženete funkcijo z drugimi začetnimi indikatorji spremenljivk, verjetno obstaja rešitev.

Na primer, pri uporabi nelinearne metode elementi tabele s spremenljivkami niso bili zapolnjeni in funkcija ni našla rešitev. To ne pomeni, da rešitve ni. Zdaj se ob upoštevanju rezultatov določene ocene v elemente s spremenljivkami vnesejo drugi podatki, ki so blizu prejetim.

V vsakem primeru bi morali najprej preučiti model, ali ni navzkrižja omejitev. Pogosto je to povezano z nepravilno izbiro razmerja ali omejitvenega indikatorja.

V zgornjem primeru je indikator največje prostornine 16 kubičnih metrov. m namesto 32, ker je takšna omejitev v nasprotju s kazalniki za najmanjše število sedežev, saj bo ustrezala številu 16,5 kubičnih metrov. m.

zaključek

Na podlagi tega bo možnost »Išči rešitev« v Excelu pomagala pri reševanju specifičnih problemov, ki jih je precej težko ali nemogoče rešiti na običajne načine. Težava pri uporabi te metode je, da je na začetku ta možnost skrita, zato se večina uporabnikov ne zaveda njene prisotnosti. Poleg tega je funkcijo precej težko naučiti in uporabljati, vendar bo z ustrezno raziskavo prinesla velike koristi in olajšala izračune.