Как да настроите работен лист за решаване в Excel

Ето една работна книга на Excel, настроена за решаване на проблем с оптимизационно моделиране за бизнес с едно лице. Ако решите сами да създадете примера за работна книга Solver (добра идея), искате да кажете на Excel да показва действителни формули, а не резултати от формули в работната книга.

Това е, което прави тази работна книга, между другото. За да направите това, изберете диапазона на работния лист, в който искате да покажете действителните формули, а не резултатите от формулите и след това едновременно натиснете клавишите Ctrl и ` (сериозен акцент). С натискане на Ctrl+` казвате на Excel да показва формулата, а не резултата от формулата в избрания диапазон.

Настройването на работна книга Solver изисква три стъпки:

Идентифицирайте променливите на Solver.

Първо, искате да идентифицирате променливите във вашия проблем с оптимизационно моделиране. В случай на опит да разберете броя на книгите, които да напишете и семинарите, които да дадете, за да спечелите най-много пари във вашия едноличен бизнес, двете променливи на Solver са книги и семинари.

Вие ще въведете етикетите, показани в диапазон A1:A3 и след това началните стойности на променливата, показани в диапазон B2:B3. Тази част от работния лист не е нещо магическо. Той просто идентифицира кои променливи влизат в целевата функция. Целевата функция е формулата, която искате да увеличите или минимизирате. Стойностите, съхранени в диапазона на работния лист B2:B3, са началните предположения за това какви трябва да бъдат оптималните стойности на променливата.

Това е само предположение, че оптималният брой книги за писане е две и че оптималният брой семинари за провеждане е осем. Няма да знаете какъв всъщност е оптималният брой книги и семинари, докато не решите проблема.

Въпреки че не е нужно да наименувате клетките, които съдържат стойностите на променливите – в този случай клетки B2 и B3 – наименуването на тези клетки прави формулата на вашата целева функция и формулите на ограниченията ви много по-лесни за разбиране. Така че, трябва да назовете клетките.

Ако настроите работна книга като тази, можете да наименувате клетките със стойности на променливата, като изберете диапазона на работния лист A2:B3 и след това щракнете върху командния бутон Създаване от селекция в раздела Формула. Когато Excel покаже диалоговия прозорец Създаване на имена от селекция, поставете отметка в квадратчето Лява колона и щракнете върху OK.

Това казва на Excel да използва етикетите в лявата колона: Това ще бъде диапазонът A2:A3 — за име на диапазона B2:B3. С други думи, като следвате тези стъпки, вие наименувате клетка B2 Books и наименувате клетка B3 Seminars.

Опишете целевата функция.

Целевата функция, показана в клетка B5, дава формулата, която искате да оптимизирате. В случай на формула за печалба, вие искате да увеличите максимално функция, защото искате да увеличите печалбата, разбира се.

Не всички целеви функции трябва да бъдат максимизирани. Някои целеви функции трябва да бъдат сведени до минимум. Например, ако създадете обективна функция, която описва цената на някаква рекламна програма или риска от някаква инвестиционна програма, можете логично да изберете да намалите разходите си или да намалите рисковете си.

За да опишете целевата функция, създайте формула, която описва стойността, която искате да оптимизирате. В случай на функция за печалба за едноличен бизнес, вие печелите $15 000 за всяка книга, която напишете и $20 000 за всеки семинар, който провеждате. Можете да опишете това, като въведете формулата =15000*Книги+20000*Семинари .

С други думи, можете да изчислите печалбите на вашия бизнес с едно лице, като умножите броя на книгите, които пишете, по 15 000 долара и броя на семинарите, които давате, по 20 000 долара. Това показва клетка B5.

Идентифицирайте всички ограничения на целевата функция.

В обхвата на работния лист A8:C11 ограниченията са описани и идентифицирани върху целевата функция. Четири ограничения могат да ограничат печалбите, които можете да реализирате във вашия бизнес:

• Изискван лимит за пари: Първото ограничение (клетка A8) определя количествено необходимото ограничение за пари. В този пример всяка книга изисква $500 в брой, а всеки семинар изисква $2500 в брой. Ако разполагате с 20 000 щатски долара, които да инвестирате в книги и семинари, вие сте ограничени в броя на книгите, които можете да напишете, и в броя на семинарите, които можете да дадете от паричната предварителна инвестиция, която трябва да направите.

  Формулата в клетка B8 описва парите, необходими за вашия бизнес. Стойността, показана в клетка C8, 20000, идентифицира действителното ограничение.

• Ограничение на работните часове : Ограничението на работните часове се определя количествено чрез формулата в клетка B9 и стойността 1880 в клетка C9. Използвайте тези две части от информация, формулата и константната стойност, за да опишете ограничение на работното време. Накратко, това ограничение казва, че броят на часовете, които сте прекарали в книги и семинари, трябва да бъде по-малък от 1880.

• Правила за минимален брой книги: Ограничението, че трябва да пишете поне една книга годишно, се задава в клетки B10 и C10. Формулата =Книги влиза в клетка B10. Минималният брой книги, 1, влиза в клетка C10.

• Политика за минимален брой семинари: Ограничението, че трябва да провеждате поне четири семинара годишно, е зададено в клетки B11 и C11. Формулата влиза в клетка B11. Постоянната стойност на минималния брой семинари, 4, влиза в клетка C11.

След като дадете формулите за ограничения и предоставите константите, с които ще се сравняват резултатите от формулата, вие сте готови да решите проблема си с оптимизационно моделиране. С настроената работна книга решаването на функцията всъщност е много лесно.

Създаването на работната книга и дефинирането на проблема с формулите на целевата функция и ограниченията е трудната част.