„Excel Solver“: kas tai yra ir kada jį naudoti

„Excel“ skaičiuoklės įrankiai, tokie kaip „Tikslo paieška“, pakeičiantys vieną kintamąjį, yra naudingi, bet, deja, dauguma problemų versle nėra tokios lengvos. Paprastai susidursite su formulėmis, kuriose yra bent du, o kartais ir dešimtys kintamųjų. Dažnai problema turi daugiau nei vieną sprendimą, o jūsų iššūkis bus naudoti „Excel“ ieškant optimalaus sprendimo (ty tokį, kuris padidina pelną, sumažina išlaidas arba atitinka kitus kriterijus). Šiems didesniems iššūkiams jums reikia raumeningesnio įrankio. „Excel“ turi tik atsakymą: sprendėjas. „Excel Solver“ yra sudėtinga optimizavimo programa, leidžianti rasti sprendimus sudėtingoms problemoms, kurioms kitu atveju reikėtų aukšto lygio matematinės analizės.

„Excel Solver“ supratimas

Solver, kaip ir tikslo siekimas , skaičiavimams atlikti naudoja kartotinį metodą. Iteracijos naudojimas reiškia, kad Excel Solver bando sprendimą, analizuoja rezultatus, bando kitą sprendimą ir pan. Tačiau ši ciklinė iteracija nėra tik Solverio spėlionės. Tai būtų kvaila. Ne, „Excel Solver“ tiria, kaip rezultatai keičiasi su kiekviena nauja iteracija, ir, naudodamasi kai kuriais sudėtingais matematiniais procesais (kurie, laimei, vyksta fone ir gali būti ignoruojami), paprastai gali pasakyti, kuria kryptimi ji turėtų pasukti sprendimą.

Excel Solver pranašumai

Taip, tikslo siekimas ir sprendimas yra pasikartojantys, tačiau tai nereiškia, kad jie yra lygūs. Tiesą sakant, „Excel Solver“ lentelei suteikia daug pranašumų:

• „Excel Solver“ leidžia nurodyti keletą reguliuojamų langelių. Iš viso galite naudoti iki 200 reguliuojamų langelių.
• „Excel Solver“ leidžia nustatyti reguliuojamų langelių apribojimus. Pavyzdžiui, galite nurodyti Solver rasti sprendimą, kuris ne tik padidintų pelną, bet ir atitiktų tam tikras sąlygas, pvz., pasiektų 20–30 procentų bendrąjį pelną arba sumažintų išlaidas nei 100 000 USD. Teigiama, kad šios sąlygos riboja sprendimą.
• „Excel Solver“ siekia ne tik norimo rezultato („tikslo“ programoje Goal Seek), bet ir optimalaus. Pavyzdžiui, ieškant optimalaus rezultato gali reikšti, kad galite rasti sprendimą, kuris būtų maksimalus arba minimalus.
• Sudėtingoms problemoms spręsti Solver gali sukurti kelis sprendimus . Tada galite išsaugoti šiuos skirtingus sprendimus pagal skirtingus scenarijus.

Kada turėtumėte naudoti „Excel Solver“?

Gerai, fotografuokime tiesiai: „Excel Solver“ yra galingas įrankis, kurio daugumai „Excel“ naudotojų nereikia. Būtų per daug, pavyzdžiui, naudoti Solver grynajam pelnui apskaičiuoti, atsižvelgiant į fiksuotas pajamas ir sąnaudas. Tačiau daugeliui problemų reikia ne mažiau kaip sprendimas „Solver“. Šios problemos apima daugybę skirtingų sričių ir situacijų, tačiau jas visos turi bendrų bruožų:

• Jie turi vieną objektyvo langelį (taip pat vadinamą tiksliniu langeliu ), kuriame yra formulė, kurią norite padidinti, sumažinti arba nustatyti tam tikrą reikšmę. Ši formulė gali būti apskaičiavimas, pvz., bendros transportavimo išlaidos arba grynasis pelnas.
• Tikslinėje langelio formulėje yra nuorodų į vieną ar daugiau kintamųjų langelių (dar vadinamų nežinomais arba besikeičiančiais langeliais ). Solver koreguoja šiuos langelius, kad surastų optimalų tikslinės langelio formulės sprendimą. Šiuose kintamuosiuose langeliuose gali būti tokių elementų kaip parduoti vienetai, pristatymo išlaidos arba reklamos išlaidos.
• Pasirinktinai yra viena ar daugiau apribojimų langelių, kurie turi atitikti tam tikrus kriterijus. Pavyzdžiui, galite reikalauti, kad reklama sudarytų mažiau nei 10 procentų visų išlaidų arba kad nuolaida klientams būtų nuo 40 iki 60 procentų.

Pavyzdžiui, toliau pateiktame paveikslėlyje parodytas darbalapio duomenų modelis, kuris visas nustatytas programai „Excel Solver“. Modelis parodo dviejų produktų pajamas (kaina padauginus iš parduotų vienetų) ir sąnaudas, kiekvieno produkto pagamintą pelną ir bendrą pelną. Čia reikia atsakyti į šį klausimą: kiek kiekvieno produkto vienetų reikia parduoti, kad bendras pelnas būtų 0 USD? Versle tai žinoma kaip lūžio analizė.

Šio duomenų modelio tikslas yra rasti lūžio tašką (kai bendras pelnas yra 0 USD).

Tai skamba kaip paprasta tikslo siekimo užduotis, tačiau šis modelis turi sudėtingą aspektą: kintamąsias išlaidas. Paprastai kintamieji gaminio kaštai yra jo vieneto savikaina, padauginta iš parduotų vienetų skaičiaus. Jei gaminio A gamyba kainuoja 10 USD, o parduodate 10 000 vienetų, to produkto kintamos išlaidos yra 100 000 USD. Tačiau realiame pasaulyje tokios išlaidos dažnai sumaišomos tarp kelių produktų. Pavyzdžiui, jei vykdote bendrą dviejų produktų reklamos kampaniją, šias išlaidas padengia abu produktai. Todėl šiame modelyje daroma prielaida, kad vieno produkto kaštai yra susiję su kito parduotais vienetais. Pavyzdžiui, čia yra formulė, naudojama pripučiamos smiginio lentos išlaidoms apskaičiuoti (ląstelė B8):

=B7 * B4 – C4

Kitaip tariant, kintamos pripučiamos smiginio lentos sąnaudos sumažinamos vienu doleriu už kiekvieną parduotą „Dog Polisher“ vienetą. Pastarosios kintamos išlaidos naudoja panašią formulę (C8 langelyje):

=C7 * C4 – B4

Dėl kintamų sąnaudų, susijusių su keliais produktais, šis duomenų modelis nepatenka į „Goal Seek“ galimybes, tačiau „Solver“ atlaiko iššūkį. Čia yra specialios modelio ląstelės, kurias naudos Solver:

• Objektyvi ląstelė yra C14; bendras pelnas ir tikslinis šios formulės sprendimas yra 0 (tai yra lūžio taškas).
• Keičiamos ląstelės yra B4 ir C4, kuriose yra kiekvieno produkto parduotų vienetų skaičius.
• Dėl apribojimų galbūt norėsite pridėti, kad abu produkto pelno langeliai (B12 ir C12) taip pat turėtų būti 0.

Įkeliamas „Excel Solver“ priedas

Papildinio yra programinė įranga, kuri prideda vieną ar daugiau funkcijų į Excel. Įdiegę priedus gausite papildomų „Excel“ funkcijų, kurių pagal numatytuosius nustatymus juostelėje nėra. Komplekte esanti priedo programinė įranga yra įtraukta į „Excel“, bet neįdiegiama automatiškai, kai įdiegiate „Excel“ . „Excel“ standartiškai pateikiami keli priedai, įskaitant „Solver“, leidžiantį išspręsti optimizavimo problemas.

Susietus priedus įdiegiate naudodami dialogo langą „Excel“ parinktys; juos galite rasti skiltyje Priedai. Įdiegę priedus, jie iškart pasiekiami. Paprastai jie rodomi skirtuke, susijusiame su jų funkcija. Pavyzdžiui, skirtuke Duomenys pasirodo Solver.

Norėdami įkelti „Excel Solver“ priedą, turite atlikti šiuos veiksmus:

Pasirinkite Failas → Parinktys.
Pasirodo dialogo langas „Excel“ parinktys.

Pasirinkite Priedai.

Sąraše Tvarkyti pasirinkite „Excel“ priedai, tada pasirinkite Eiti.
„Excel“ rodo dialogo langą „Papildiniai“.

Pažymėkite žymės langelį Solver Add-In

Spustelėkite Gerai.
„Excel“ prideda mygtuką „Spręstuvas“ į skirtuko „Duomenys“ grupę „Analizuoti“.

Pažiūrėkite ir sužinokite, kaip naudoti „Excel Solver“ .