Microsoft Excel funkcija: pronalaženje rješenja. Kratak opis dodatka

Dom Microsoft Excel dodatak "Traženje rješenja"

je moćan alat za pronalaženje rješenja i koristi se u rješavanju problema optimizacije. Procedura pretraživanja rješenja vam omogućava da pronađete optimalnu vrijednost formule sadržane u ćeliji, koja se naziva ciljna ćelija. Procedura radi na grupi ćelija koje su direktno ili indirektno povezane sa formulom u ciljnoj ćeliji.

Kratak opis dodatka

Da bi se dobio dati rezultat pomoću formule, postupak mijenja vrijednosti u ćelijama koje utiču. Ograničenja vrijednosti se koriste za smanjenje raspona vrijednosti korištenih u modelu. Dodatak Solution Finder je standardni Microsoft Office Excel dodatak i dostupan je odmah kada instalirate Microsoft Office općenito ili Microsoft Excel posebno.

Kako instalirati dodatak?

Dodatak Find Solution može se instalirati na dva načina. Standardni dodaci kao što su "Solution Search" i "Analysis Package" instalirani su zajedno sa MS Office ili MS Excel. Ako standardni dodatak nije instaliran tokom početne instalacije, morate ponovo pokrenuti proces instalacije. Pogledajmo instaliranje dodatka "Traži rješenje" koristeći Microsoft Office 2010 kao primjer. U verzijama 2003 i 2007 sve se radi na isti način.

Dakle, pokrenite instalacioni disk sa MS Office 2010 aplikacijskim paketom i izaberite opciju „Dodaj ili ukloni komponente“.

Zatim kliknite na dugme "Nastavi", pronađite aplikaciju Microsoft Excel u instalacijskim parametrima, pronađite odjeljak "Dodaci" u komponentama ove aplikacije, odaberite dodatak "Traženje rješenja" i postavite "Pokreni iz mog kompjuter”.

Ponovo kliknite na dugme "Nastavi" i pričekajte dok se dodatak ne instalira.

Kako povezati dodatak?

Prije upotrebe, prvo morate omogućiti dodatak tako što ćete označiti polje ispred njegovog imena na listi dostupnih dodataka u dijaloškom okviru Dodaci. Pozivanje ovog prozora neznatno varira ovisno o verziji aplikacije. Ovo je detaljno napisano u posebnom članku " Kako instalirati dodatak za Excel 2003/2007/2010? " sa snimcima ekrana za svaku od tri verzije Excel aplikacije, tako da se neću ponavljati. Da, samo ću dodati nekoliko riječi o drugom načinu instaliranja ovog dodatka. Na internetu možete pronaći datoteku sa imenom(ovo je dodatak “Traži rješenje”) i instalirajte ga u skladu s opisom na linku iznad.

Svijet se mijenja vrtoglavom brzinom, htjeli mi to ili ne. Ova istina je posebno poznata korisnicima računara. Uostalom, softver se mijenja i ažurira s nevjerovatnom učestalošću. Na sreću, uredski paketi ne pate od ovoga mnogo, ali postoje izuzeci. Koja je važnost kancelarijskih softverskih paketa? Svaka uredska aplikacija je zgodan alat dizajniran za rad s bazama podataka. Broj pomoćnih elemenata u ovom sistemu je sve veći.

Uz pomoć alata za vizualizaciju koji su se pojavili samo u novim verzijama ovakvih programa, postalo je mnogo lakše raditi. Zahvaljujući novom filteru za pretragu, posao je značajno ubrzan. I sam Microsoft Excel 2010 radi brže. Ali čini se da su zaposleni u uredu tek nedavno savladavali zamršenosti rada sa Officeom 2007. Ali odjednom je došlo do prezentacije Officea 2010, što je samo dodatno zadalo nevolje nesretnim korisnicima. Primjer je "traži rješenje" u programu Microsoft Excel 2010.

Ovaj dodatak ne samo da je koristan, već vam omogućava i da rad s uređivačem proračunskih tablica učinite mnogo produktivnijim, što vam omogućava da riješite veliki broj složenih problema. Posebno je relevantan sa stanovišta optimizacije, koja je danas relevantna za mnoge kompanije. Ali zašto Microsoft Excel 2010? Ako govorimo konkretno o Excelu u ovoj verziji, došlo je do značajnih promjena u njemu. Na primjer, ispravljen je veliki broj grešaka u formulama, zbog čega su se greške u proračunima često javljale u prethodnim verzijama programa. Ali i najmanja greška u proračunu ponekad može dovesti do prilično neugodnih posljedica.

Korišćenje trake interfejsa koji vam omogućava da pregledate grafikone i grafikone pre nego što ih umetnete u tabelu olakšava korisnicima pripremu složenih, profesionalnih dokumenata. Također, nova verzija uređivača uključila je nove vrste formula koje mogu biti vrlo korisne za ekonomiste i računovođe. Ova okolnost naglašava Microsoftov fokus na korporativne korisnike. S obzirom da su sve dolje opisane situacije tipične za njih, onda u tome nema ništa iznenađujuće.

Ako niste koristili dodatak „pretraga rješenja“, možete ga zasebno instalirati. Kako ga instalirati? Ovo se radi prilično lako. Ako koristite Excel uređivač proračunskih tablica 2003 ili stariji, da biste izvršili ovu radnju, morate otići na stavku "Alati" i tamo odabrati "Dodaci". A gdje tražiti “traganje za rješenjima” ako govorimo o modernijoj verziji? Ako koristite Excel 2007, tada možete pronaći dugme „traži rešenje“ na kartici „Podaci“. Kako raditi s ovim? Možda sva ova objašnjenja mogu izgledati malo dugačka, ali ovaj dodatak funkcionira sasvim logično. Ne morate biti kompjuterski genije da biste ga ovladali. Da bismo u potpunosti razumjeli princip njegove upotrebe, razmotrimo jednostavan primjer.

Kako "traga za rješenjem" funkcionira u programu Excel 2010?

Primjer: imate zadatak da podijelite bonuse u organizaciji. Da bismo pojednostavili rješenje, pretpostavimo da morate podijeliti bonus među svim zaposlenicima filijale. Premium budžet - 100.000 rubalja. Bonus se može raspodijeliti proporcionalno plati svakog zaposlenog. Gdje početi? Prije svega, potrebno je razviti tabelu, unijeti u nju sve potrebne informacije i formulne izraze. Ukupan iznos premije će se smatrati rezultatom. Vrijedno je uzeti u obzir da je ciljna ćelija (na primjer, C8) povezana s odjeljkom koji će se promijeniti (na primjer, E2).

U rasponu C2-C7 mogu postojati dodatne formule pomoću kojih možete izračunati iznos bonusa za svakog zaposlenog. Nakon toga morate pokrenuti dodatak „traži rješenje“. Zatim se tražene vrijednosti postavljaju u prozoru koji se otvori. Posebnu pažnju treba obratiti na činjenicu da izgled prozora može uvelike varirati u različitim verzijama uredskog paketa. Tako da ćete u ovoj situaciji morati sami da shvatite. Ali nema suštinskih razlika, tako da učenje neće oduzeti mnogo vremena.

Koje su opcije prisutne u dijaloškom okviru?

Da biste olakšali svoj rad, trebali biste znati o vrijednostima koje općenito postoje u određenom radnom rasponu. Prije svega, ciljna ćelija. Imajte na umu da za razliku od drugih operacija, koje mogu koristiti više polja za unos podataka, ovdje može postojati samo jedno. Osim toga, vrijedi uzeti u obzir da može postojati nekoliko opcija optimizacije. Posebnu pažnju treba obratiti na minimalnu i maksimalnu moguću ukupnu vrijednost. Obratite pažnju i na konkretan rezultat. Ako vam je potrebna potonja opcija, tada željeni rezultat mora biti precizno naveden u polju za unos. Također treba uzeti u obzir da pojedinačna polja ili raspon mogu djelovati kao promjenjive ćelije. Za opseg program uzima konačnu vrijednost upoređujući je s originalnim podacima.

Kako se dodaju ograničenja?

Ako trebate dodati neka ograničenja programu, trebate koristiti dugme „Dodaj“. Važno je uzeti u obzir sljedeću točku: kada postavljate takve vrijednosti, morate biti izuzetno oprezni. Budući da se dodatak "pretraga rješenja" u Excelu koristi u prilično važnim operacijama, važno je dobiti najispravnije vrijednosti kao rezultat. Sami rezultati će ovisiti o ograničenjima. Možete postaviti ograničenja i za pojedinačne ćelije i za cijele raspone.

Koje se varijante formula i simbola mogu koristiti u ovom slučaju? Mogu se koristiti sljedeći znakovi: =, >=,<=. Также допускаются формулы «Цел», «Бин» и «Раз». Важно учитывать, что последний вариант допускает использование различных значений. Это доступно в версиях Exel 2010 и выше. В данных пакетах офисного программного обеспечения надстройка «поиск решения» в Exel выполняется намного быстрее и качественнее. Если речь идет о расчете премии, то в данном случае коэффициент может быть только положительным. Для задания данного параметра можно использовать несколько методов. Чтобы легко выполнить данную операцию, необходимо использовать кнопку «Добавить». Также можно выставить флажок «Сделать переменные без ограничений неотрицательными».

Gdje možete pronaći ovu opciju u starijim verzijama programa? Ako koristite Excel 2007 ili stariji, ovoj opciji se može pristupiti klikom na dugme „Opcije“. Ovdje možete vidjeti stavku “Opcije pretraživanja rješenja”.

Potražite gotov rezultat

Za traženje gotovog rješenja potrebno je kliknuti na dugme „Pokreni“. Kao rezultat, pojavljuje se dijaloški okvir Rezultati pretraživanja rješenja. Ako ste zadovoljni konačnim odgovorom, potrebno je samo da kliknete na dugme “OK”. Kao rezultat, odgovor koji vam se sviđa bit će zabilježen u tabeli. U slučaju da se rezultirajuća vrijednost ne slaže s vašim gledištem, morate kliknuti na dugme „Otkaži“. Tabela će se na kraju vratiti na svoju prvobitnu vrijednost. Možete nastaviti tražiti optimalno rješenje. Ako ste promijenili izvorne podatke, tada će se ova odluka morati ponoviti.

Gdje se u Excelu može koristiti dodatak „pretraga rješenja“?

Razmotrimo još jedan primjer - minimiziranje troškova. Kao što je već spomenuto, ova funkcija se može koristiti za optimizaciju proizvodnih procesa. Hajde da vidimo kako možemo smanjiti troškove kompanije koja se bavi niskogradnjom. Pretpostavimo da imamo samu organizaciju i tri dobavljača koji isporučuju građevinski materijal. Troškovi izgradnje će biti uključeni u cijenu objekta, pa je u interesu kompanije da izabere dobavljača čiji će radovi koštati manje.

Koje informacije treba unijeti u „pretragu rješenja“ u MS Excelu? Potrebno je navesti troškove građevinskog materijala, potrebu za istim na gradilištu i troškove transporta građevinskog materijala. Svaki par "dobavljač-kupac" treba uzeti u obzir. Ciljna ćelija treba da naznači ukupan iznos svih troškova transporta. Ako je sve urađeno ispravno, funkcija „traženje rješenja“ omogućit će kreiranje najprofitabilnije strategije koja će donijeti najveći mogući prihod.

Postoje mnogi problemi koji se mogu znatno lakše riješiti korištenjem alata za pronalaženje rješenja. Ali da biste to učinili, morate početi organiziranjem radnog lista prema modelu pogodnom za pronalaženje rješenja, što zahtijeva dobro razumijevanje odnosa između varijabli i formula. Iako formulacija problema obično predstavlja glavnu poteškoću, vrijeme i trud utrošeni na pripremu modela potpuno su opravdani, jer dobiveni rezultati mogu zaštititi od nepotrebnog rasipanja resursa, u slučaju pogrešnog planiranja, pomoći u povećanju profita kroz optimalno upravljanje finansijama. ili identificirati najbolji omjer obima proizvodnje, zaliha i naziva proizvoda.

Iza vaše suštine problem optimizacije je matematički model određenog procesa proizvodnje proizvoda, njegove distribucije, skladištenja, prerade, transporta, kupovine ili prodaje, obavljanja niza usluga itd. Ovo je uobičajen matematički problem tipa Dato/Pronađi/Uslov, ali koji ima mnogo mogućih rješenja. Dakle, problem optimizacije je zadatak da se iz skupa mogućih opcija izabere najbolja, optimalna. Rješenje za takav problem se zove plan ili program, na primjer, kažu - proizvodni plan ili program rekonstrukcije. Drugim riječima, to su nepoznanice koje trebamo pronaći, na primjer, količinu proizvodnje koja će dati maksimalan profit. Problem optimizacije je traženje ekstrema, odnosno maksimalne ili minimalne vrijednosti određene funkcije, koja se naziva ciljna funkcija Na primjer, ovo može biti funkcija profita - prihod minus troškovi. Budući da je sve na svijetu ograničeno (vrijeme, novac, prirodni i ljudski resursi), problemi optimizacije uvijek postoje ograničenja, na primjer, količina metala, radnika i mašina u pogonu za proizvodnju dijelova. Slijedi primjer dizajna vrlo jednostavnog problema optimizacije, ali uz njegovu pomoć možete lako razumjeti organizaciju konstruiranja tablice za efektivnost rješenja praktičnih problema optimizacije.

Imamo klasičan problem kada preduzeće proizvodi dve vrste proizvoda (proizvod A i proizvod B) po određenoj ceni, za njihovu proizvodnju su potrebne 4 vrste resursa (resurs 1, resurs 2, resurs 3, resurs 4), koji su dostupni na adresi kompanije u određenoj količini (Inventar), postoji i informacija o tome koliko je svakog resursa potrebno za proizvodnju jedinice proizvodnje, odnosno proizvoda A i proizvoda B. Trebamo pronaći količinu proizvoda A i proizvoda B koja maksimizira prihod (prihod) (vidi sliku).

Zatim moramo uspostaviti odnose između ograničenja, plana i funkcije cilja. Da bismo to učinili, gradimo dodatni stupac (Used) u koji unosimo formulu SUMPRODUCT(Norma; Plan). Norma je trošak određenog resursa za proizvodnju jedinice proizvoda A i B, a Plan je količina proizvodnje koju tražimo. U ćelije Prihod unesite formulu SUMPRODUCT(Cijena; Plan). Stoga smo formulama ispunili kolonu Korišteno i ćeliju Prihod. Budući da je plan varijable od kojih zavise količina utrošenih resursa i prihod, ćelije sa formulama direktno zavise od podataka koji se tamo pojavljuju kao rezultat traženja rješenja. Iz navedenog možemo izvući sljedeće zaključke da svaki problem optimizacije mora imati tri komponente:

nepoznato(ono što tražimo, odnosno plan);

ograničenje za nepoznate (područje pretrage);

ciljna funkcija(cilj za koji tražimo ekstrem).

Moćan alat za analizu podataka Excel je nadgradnja Solver (Traži rješenje). Uz njegovu pomoć možete odrediti na kojim vrijednostima navedenih utjecajnih ćelija formula u ciljnoj ćeliji poprima željenu vrijednost (minimalna, maksimalna ili jednaka nekoj vrijednosti). Možete postaviti ograničenja za proceduru traženja rješenja i nije neophodno da se koriste iste ćelije za utjecaj. Za izračunavanje date vrijednosti koriste se različite metode matematičkog pretraživanja. Možete postaviti način rada u kojem se dobijene vrijednosti varijabli automatski unose u tabelu. Osim toga, rezultati programa se mogu predstaviti u obliku izvještaja. Program Search for Solutions (u originalnom Excel Solveru) je dodatni dodatak za MS Excel tabelarni procesor, koji je dizajniran za rješavanje određenih sistema jednačina, problema linearne i nelinearne optimizacije, koji se koristi od 1991. godine. Veličina problema koji se može riješiti korištenjem osnovne verzije ovog programa ograničena je sljedećim ograničenjima:

broj nepoznatih (odlučujuća varijabla) – 200;

broj formulačkih ograničenja na nepoznate – 100;

broj graničnih uslova (jednostavno ograničenje) za nepoznate je 400.

Programer programa Solver, Frontline System, dugo se specijalizirao za razvoj moćnih i praktičnih metoda optimizacije ugrađenih u okruženje popularnih procesora proračunskih tablica različitih proizvođača (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3). Visoka efikasnost njihovog korišćenja objašnjava se integracijom programa za optimizaciju i poslovnog dokumenta tabele. Zahvaljujući svjetskoj popularnosti MS Excel procesora proračunskih tablica, program Solver ugrađen u njegovo okruženje je najčešći alat za pronalaženje optimalnih rješenja u modernom poslovanju. Podrazumevano, dodatak Find Solution je onemogućen u Excelu. Da ga aktivirate u Excel 2007, kliknite na ikonu Dugme za Microsoft Office, kliknite Excel opcije a zatim odaberite kategoriju Dodaci. Na terenu Kontrola odaberite vrijednost Excel dodaci i pritisnite dugme Idi. Na terenu Dostupni dodaci označite polje pored stavke Pronalaženje rješenja i pritisnite dugme OK.

IN Excel 2003 i odaberite naredbu ispod Usluga/Dodaci , u dijaloškom okviru Dodaci koji se pojavi potvrdite izbor u polju za potvrdu Pronalaženje rješenja i kliknite na dugme OK. Ako se tada pojavi dijaloški okvir u kojem se od vas traži da potvrdite svoje namjere, kliknite na Da. (Možda će vam trebati Office instalacioni CD.)

Procedura traženja rješenja 1. Kreirajte tabelu sa formulama koje uspostavljaju odnose između ćelija.

2. Odaberite ciljnu ćeliju koja treba da poprimi traženu vrijednost i odaberite naredbu: - U Excel 2007 Podaci/analiza/Pronalaženje rješenja;

IN Excel 2003 i ispod Alati > Solver (Alati > Traži rješenje). Polje Postavi ciljnu ćeliju u dijaloškom okviru programskog dodatka Solver koji se otvori sadržat će adresu ciljne ćelije. 3. Postavite prekidače Equal To da postavite vrijednost ciljne ćelije na Max (maksimalna vrijednost), Min (minimalna vrijednost) ili Value of (vrijednost). U drugom slučaju, unesite vrijednost u polje s desne strane. 4. Navedite u polju Promjenom ćelija koje ćelije program treba promijeniti vrijednosti u potrazi za optimalnim rezultatom. 5. Kreirajte ograničenja na listi Subject to Constraints. Da biste to uradili, kliknite na dugme Dodaj i definišite ograničenje u dijalogu Dodaj ograničenje.

6. Kliknite na dugme na dugmetu Opcije i u prozoru koji se pojavi izaberite radio dugme Nenegativne vrednosti (ako varijable moraju biti pozitivni brojevi), Linearni model (ako se problem koji rešavate odnosi na linearni modeli)

7. Kliknite na dugme Solver da započnete proces pretraživanja rješenja.

8. Kada se pojavi dijaloški okvir Result Results, odaberite radio dugme Keep Solve Solution ili Restore Original Values. 9. Kliknite OK.

Opcije alata za rješenje Maksimalno vrijeme- služi za ograničavanje vremena određenog za traženje rješenja problema. U ovo polje možete unijeti vrijeme u sekundama do 32.767 (približno devet sati); Zadana vrijednost od 100 je u redu za većinu jednostavnih zadataka.

Ograničenje broja iteracija- kontrolira vrijeme rješavanja problema ograničavanjem broja računskih ciklusa (iteracija). Relativna greška- određuje tačnost proračuna. Što je niža vrijednost ovog parametra, to je veća tačnost proračuna. Tolerancija- namijenjen je postavljanju tolerancije za odstupanje od optimalnog rješenja ako je skup vrijednosti utjecajne ćelije ograničen skupom cijelih brojeva. Što je veća vrijednost tolerancije, manje je vremena potrebno da se pronađe rješenje. Konvergencija- odnosi se samo na nelinearne probleme. Kada relativna promjena vrijednosti u ciljnoj ćeliji u posljednjih pet iteracija postane manja od broja navedenog u polju Konvergencija, pretraga se zaustavlja. Linearni model- služi za ubrzavanje traženja rješenja primjenom linearnog modela na problem optimizacije. Nelinearni modeli uključuju upotrebu nelinearnih funkcija, faktora rasta i eksponencijalnog izglađivanja, što usporava proračune. Nenegativne vrijednosti- omogućava vam da postavite nultu donju granicu za one ćelije koje utiču na ćelije za koje odgovarajuće ograničenje nije postavljeno u dijalogu Dodaj ograničenje. Automatsko skaliranje- koristi se kada se brojevi u ćelijama koje se mijenjaju i u ciljnoj ćeliji značajno razlikuju. Prikaži rezultate iteracije- pauzira potragu za rješenjem za pregled rezultata pojedinačnih iteracija. Preuzmite model- nakon klika na ovo dugme otvara se istoimeni dijaloški okvir u koji možete unijeti link do raspona ćelija koje sadrže model optimizacije. Sačuvaj model- služi za prikaz na ekranu istoimenog dijaloškog okvira u koji možete unijeti vezu do raspona ćelija namijenjenih za pohranjivanje optimizacionog modela. Linearna evaluacija- odaberite ovaj prekidač za rad s linearnim modelom. Kvadratna procjena- odaberite ovaj prekidač za rad s nelinearnim modelom. Direktne razlike- koristi se u većini problema gdje je stopa promjene ograničenja relativno niska. Povećava brzinu alata Find Solution. Centralne razlike- koristi se za funkcije koje imaju diskontinuirani izvod. Ova metoda zahtijeva više proračuna, ali njena upotreba može biti opravdana ako se pošalje poruka da nije moguće dobiti preciznije rješenje. Newtonov metod pretraživanja - zahtijeva više memorije, ali izvodi manje iteracija od metode konjugiranog gradijenta. Metoda za pronalaženje konjugiranih gradijenata- implementira metodu konjugiranog gradijenta, koja zahtijeva manje memorije, ali izvodi više iteracija od Newtonove metode. Ovu metodu treba koristiti ako je problem dovoljno velik da uštedi memoriju ili ako iteracije daju premalu razliku u uzastopnim aproksimacijama.

Većina problema riješenih korištenjem proračunske tablice uključuje pronalaženje željenog rezultata koristeći poznate izvorne podatke. Ali Excel ima alate koji vam omogućavaju da riješite inverzni problem: odaberite početne podatke da biste dobili željeni rezultat.

Jedan takav alat jePronalaženje rješenja, što je posebno pogodno za rješavanje takozvanih "optimizacijskih problema".

Ako ga ranije niste koristiliPronalaženje rješenja, tada ćete morati instalirati odgovarajući dodatak.

Možete to učiniti ovako:

za verzije starije od Excel 2007 putem naredbe meni Usluga --> Dodaci;

od Excel 2007 preko dijaloškog okviraExcel opcije

Od Excel 2007dugme za početakPronalaženje rješenjaće se pojaviti na kartici Podaci.

U verzijama prije Excel 2007, slična komanda će se pojaviti u izbornikuServis

Pogledajmo proceduru rada Pronalaženje rješenja koristeći jednostavan primjer.

Primjer 1. Podjela bonusa

Pretpostavimo da ste šef proizvodnog odjela i da morate pravedno raspodijeliti bonus u iznosu od 100.000 rubalja. između službenika odjeljenja srazmjerno njihovim službenim platama. Drugim riječima, potrebno je da odaberete koeficijent proporcionalnosti da biste izračunali veličinu bonusa na platu.

Prije svega, kreiramo tablicu s početnim podacima i formulama s kojima treba dobiti rezultat. U našem slučaju rezultat je ukupan iznos premije. Vrlo je važno da se ciljna ćelija (C8) preko formula poveže sa željenom ćelijom koja se mijenja (E2). U primjeru su povezani preko međuformula koje izračunavaju iznos bonusa za svakog zaposlenog (C2:C7).

Sada krenimo Pronalaženje rješenja i u dijaloškom okviru koji se otvori postavite potrebne parametre. Izgled dijaloških okvira se neznatno razlikuje između verzija:

Od Excel 2010

Prije Excel 2010

Nakon pritiska na dugmePronađite rješenje (Pokreni)Dobijeni rezultat već možete vidjeti u tabeli. Istovremeno, na ekranu se pojavljuje dijaloški okvirRezultati potrage za rješenjem.

Od Excel 2010

Prije Excel 2010

Ako vam odgovara rezultat koji vidite u tabeli, onda u dijalog box-u Rezultati pretraživanja rješenja pritisnite OK i zabilježite rezultat u tabelu. Ako vam rezultat ne odgovara, kliknite Otkaži i vratite se na prethodno stanje tabele.

Rješenje ovog problema izgleda ovako

Važno: za bilo kakve promjene izvornih podataka kako bi se dobio novi rezultat Pronalaženje rješenja moraće ponovo da trči.

Pogledajmo još jedan problem optimizacije (maksimiziranje profita)

Primjer 2. Proizvodnja namještaja (maksimizacija profita)

Kompanija proizvodi dva modela A i B montažnih polica za knjige.

Njihova proizvodnja je ograničena dostupnošću sirovina (kvalitetne ploče) i vremenom mašinske obrade.

Za svaki model proizvoda potrebno je 3 m²ploče, a za model proizvoda B - 4 m². Kompanija može dobiti do 1.700 m² ploča sedmično od svojih dobavljača.

Svaki model proizvoda zahtijeva 12 minuta strojnog vremena, a za model proizvoda B - 30 minuta. 160 sati mašinskog vremena može se koristiti sedmično.

Koliko proizvoda svakog modela treba da kompanija proizvodi nedeljno da bi postigla maksimalan profit ako svaki proizvod modela A donosi 60 rubalja. profit, a svaki proizvod modela B košta 120 rubalja. profit?

Već znamo proceduru.

Prvo kreiramo tabele sa izvornim podacima i formulama. Raspored ćelija na listu može biti apsolutno proizvoljan, koliko odgovara autoru. Na primjer, kao na slici

Pokrenimo Pronalaženje rješenjaiu dijaloškom okviru postavite potrebne parametre

1. Ciljna ćelija B12 sadrži formulu za izračunavanje dobiti
2. Optimizacijski parametar - maksimum
3. Modificirane ćelije B9:C9
4. Ograničenja: pronađene vrijednosti moraju biti cijeli brojevi, nenegativni; ukupna količina mašinskog vremena ne bi trebalo da prelazi 160 sati (poziv na ćeliju D16); ukupna količina sirovina ne bi trebalo da prelazi 1700 m² (referentna ćelija D15). Ovdje, umjesto veza do ćelija D15 i D16, mogu se navesti brojevi, ali kada se koriste veze, sve promjene ograničenja mogu se izvršiti direktno u tabeli
5. Pritisnite dugme Pronađite rješenje (Pokreni) i nakon potvrde dobijamo rezultat

Ali čak i ako ste ispravno kreirali formule i postavili ograničenja, rezultat može biti neočekivan. Na primjer, prilikom rješavanja ovog problema možete vidjeti sljedeći rezultat:

I to uprkos činjenici da je ograničenje postavljeno cijeli. U takvim slučajevima možete pokušati prilagoditi postavke Pronalaženje rješenja. Da biste to učinili u prozoru Pronalaženje rješenja pritisnite dugme Opcije i ulazimo u istoimeni dijaloški okvir

Prvi od odabranih parametara odgovoran je za tačnost proračuna. Smanjujući ga, možete postići točniji rezultat, u našem slučaju - cjelobrojne vrijednosti. Druga od istaknutih opcija (dostupna počevši od Excel 2010) odgovara na pitanje: kako bi se mogli dobiti razlomci kada se ograničavacijeli? Ispada Pronalaženje rješenjaovo ograničenje je jednostavno zanemareno u skladu sa označenom zastavicom.

Primjer 3. Transportni problem (minimizacija troškova)

Po nalogu građevinske firme, pijesak se transportuje od tri dobavljača (kamenoloma) do pet potrošača (gradilišta). Troškovi dostave uključeni su u cijenu projekta, pa je građevinska kompanija zainteresirana da na najjeftiniji način zadovolji potrebe za pijeskom svojih gradilišta.

Dato: rezerve pijeska u kamenolomima; potrebe za pijeskom na gradilištu; troškovi transporta između svakog para dobavljač-potrošač.

Potrebno je pronaći optimalnu šemu transporta koja će zadovoljiti potrebe (odakle i kamo), u kojoj bi ukupni troškovi transporta bili minimalni.

Primjer lokacije ćelija s izvornim podacima i ograničenjima, željene ćelije i ciljna ćelija prikazan je na slici

U sivim ćelijama nalaze se formule za zbrojeve po redovima i stupcima, au ciljnoj ćeliji formula za izračunavanje ukupnih troškova transporta.

Pokrećemo Potragu za rješenjem i postavljamo potrebne parametre (vidi sliku)

Kliknite Pronađite rješenje (Pokreni) i dobijete rezultat prikazan ispod

U zaključku, predlažem da se okušate u korištenju Pronalaženje rješenja i koristite ga za rješavanje starog problema:

Jedan seljak je na pijaci kupio 100 grla stoke za 100 rubalja. Bik košta 10 rubalja, krava 5 rubalja, tele 50 kopejki. Koliko bikova, krava i teladi je kupio seljak?

Izjava o problemu

Pretpostavimo da kompanija u kojoj radite ima dva skladišta, odakle se roba isporučuje u vaših pet prodavnica razbacanih po Moskvi.

Svaka prodavnica je u mogućnosti da proda određenu količinu robe koja nam je poznata. Svako skladište ima ograničen kapacitet. Zadatak je racionalno izabrati iz kojeg skladišta u koje prodavnice treba isporučiti robu kako bi se ukupni transportni troškovi sveli na minimum.

Prije nego započnete optimizaciju, morat ćete kreirati jednostavnu tablicu na Excel listu - naš matematički model koji opisuje situaciju:

Podrazumijeva se da:

• Siva tabela (B3:G5) opisuje jedinični trošak isporuke od svakog skladišta do svake trgovine.
• Ljubičaste ćelije (C14:G14) opisuju količinu robe koja je potrebna za prodaju za svaku prodavnicu.
• Crvene ćelije (J10:J11) prikazuju kapacitet svakog skladišta – maksimalnu količinu robe koju skladište može primiti.
• Žute (C12:G12) i plave (H10:H11) ćelije su zbroj redova i kolona za zelene ćelije, respektivno.
• Ukupni trošak isporuke (E17) izračunava se kao zbir proizvoda broja robe i njihovih odgovarajućih troškova isporuke.

Dakle, naš zadatak se svodi na odabir optimalnih vrijednosti zelenih ćelija. Štaviše, tako da ukupan iznos za liniju (plave ćelije) ne prelazi kapacitet skladišta (crvene ćelije), a da istovremeno svaka prodavnica dobije količinu robe koja joj je potrebna za prodaju (iznos za svaku prodavnicu u žutom ćelije treba da budu što bliže zahtevima – ljubičaste ćelije).

Rješenje

U matematici su davno formulisani i opisani slični problemi izbora optimalne alokacije resursa. I, naravno, odavno su razvijeni načini za njihovo rješavanje. Excel daje korisniku jedan od njih - uz pomoć moćnog dodatka Pronalaženje rješenja(rješavač), dostupan u programu Excel 2003 preko menija Servis(Alati) ili sa kartice Podaci u novim verzijama Excela.

Ako je na meniju Servis ili na kartici Podaci vaš Excel nema takvu naredbu – u redu je – to znači da dodatak jednostavno još nije povezan. Za povezivanje:

• u programu Excel 2003 i starijim - otvorite meni Usluga – Dodaci(Alati – Dodaj- Ins) , u prozoru koji se pojavi potvrdite izbor u polju Pronalaženje rješenja(Solver) i pritisnite OK. Excel aktivira odabrani dodatak i u meniju usluga (Alati) pojaviće se nova ekipa - Pronalaženje rješenja (Solver) .
• u programu Excel 2007 i novijim - kliknite na dugme Ured, zatim odaberite OpcijeExcel – Dodaci – Idi(Opcije Excela - Dodaci - Idi na) .

Pokrenimo dodatak. Otvorit će se sljedeći prozor:

U ovom prozoru morate postaviti sljedeće postavke:

Pored očiglednih ograničenja povezanih sa fizičkim faktorima (kapacitet skladišta i transportnih vozila, budžet i rokovi, itd.), ponekad je potrebno dodati ograničenja „posebno za Excel“. U našem slučaju, na primjer, morat ćemo dodati sljedeće ograničenje:

Dodatno će biti pojašnjeno da obim prevezene robe (zelenih ćelija) ne može biti negativan - za čoveka je to samo po sebi razumljivo, ali za kompjuter mora biti eksplicitno zapisano.

Nakon postavljanja svih potrebnih parametara, prozor bi trebao izgledati ovako:

Sada kada su podaci proračuna uneseni, pritisnite dugme Izvrši(riješi) za početak optimizacije. U teškim slučajevima s velikim brojem mijenjanja ćelija i ograničenja, pronalaženje rješenja može potrajati, ali naš zadatak za Excel neće predstavljati problem - za nekoliko trenutaka dobit ćemo sljedeće rezultate:

Napominjemo kako je interesantno raspoređen obim isporuke po prodavnicama, bez prekoračenja kapaciteta naših skladišta i zadovoljavanja svih zahteva za potrebnu količinu robe za svaku prodavnicu.

Ako nam pronađeno rješenje odgovara, onda ga možemo sačuvati, ili se vratiti na izvorne vrijednosti i pokušati ponovo s drugim parametrima. Također možete sačuvati odabranu kombinaciju parametara kao Scenario. Na zahtjev korisnika, Excel može napraviti tri tipa Izvještaji za problem koji se rješava na posebnim listovima: izvještaj o rezultatima, izvještaj o matematičkoj stabilnosti rješenja i izvještaj o granicama (ograničenjima) rješenja, međutim, u većini slučajeva oni su od interesa samo za stručnjake .

Međutim, postoje situacije kada Excel ne može pronaći odgovarajuće rješenje. Takav slučaj možete simulirati ako u našem primjeru navedete zahtjeve trgovine u iznosu većem od ukupnog kapaciteta skladišta. Zatim, prilikom optimizacije, Excel će pokušati da se približi rješenju što je više moguće, a zatim će prikazati poruku da ne može pronaći rješenje. Međutim, čak iu ovom slučaju imamo mnogo korisnih informacija – posebno možemo uočiti „slabe karike“ naših poslovnih procesa i razumjeti područja za poboljšanje.

Razmatrani primjer je, naravno, relativno jednostavan, ali se lako prilagođava za rješavanje mnogo složenijih nelinearnih problema. na primjer:

• Optimizacija finansijske alokacije prema rashodnim stavkama u poslovnom planu ili budžetu projekta. Ograničenja će, u ovom slučaju, biti iznos finansiranja i tajming projekta, a cilj optimizacije će biti maksimiziranje profita i minimiziranje troškova projekta.
• Optimizacija rasporeda zaposlenih kako bi se minimizirao platni fond preduzeća. Ograničenja će, u ovom slučaju, biti želje svakog zaposlenog u pogledu rasporeda rada i zahtjeva kadrovskog rasporeda.
• Optimizacija ulaganja – potreba za mudrom raspodjelom sredstava između nekoliko banaka, vrijednosnih papira ili dionica preduzeća s ciljem, opet, maksimiziranja profita ili (ako je to važnije) minimiziranja rizika.

U svakom slučaju, addon Pronalaženje rješenja(rješavač) je vrlo moćan i lijep Excel alat i vrijedan je vaše pažnje, jer može pomoći u mnogim teškim situacijama s kojima se morate suočiti u modernom poslovanju.