Пълно ръководство за използване на Solver в Excel за решаване на проблеми

Microsoft Excel е оборудван с различни функции, които рационализират процеса на извършване на изчисления и решаване на уравнения, като по този начин повишават производителността. Една от тези функционалности е инструментът Solver, който има прилики с функцията Goal Seek.

Обикновено използван за анализ „Какво ако“, Solver позволява на потребителите да установят стойността на конкретна клетка при множество ограничения. Може да се използва за определяне на конкретна стойност, минимален праг или максимална граница за число. Въпреки че може да не адресира всеки проблем, Solver е безценен ресурс за сценарии за оптимизация, където намирането на най-доброто възможно решение е от решаващо значение.

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

Типичните приложения на Solver включват минимизиране на транспортните разходи, изработване на оптимални работни графици, установяване на най-добрия бюджет за рекламни инициативи или максимизиране на възвръщаемостта на инвестициите, за да назовем само няколко.

Активиране на Solver в Excel

За да започнете да използвате Solver, първо трябва да активирате тази добавка, тъй като тя не е активирана по подразбиране като функцията Goal Seek. За щастие, процесът е доста лесен.

Започнете, като изберете менюто „Файл“ в горната част на екрана и след това щракнете върху „Опции“.

След това щракнете върху „Добавки“, разположени от лявата страна на прозореца с опции.

Сега изберете „Добавки на Excel“ от падащото меню „Управление“ в долната част и щракнете върху „Отиди“.

В следващия диалогов прозорец поставете отметка в квадратчето до „Добавка за решаване“, за да я активирате, след което натиснете „OK“.

Вече трябва да видите Solver наличен, когато щракнете върху раздела „Данни“ в Excel.

Ключови компоненти на Solver

Преди Solver да може да идентифицира оптималната стойност за всеки проблем, трябва да бъдат установени три основни компонента:

Клетка с цел: Тази клетка съдържа формулата, която представлява целта или целта на проблема, независимо дали да се минимизира, да се увеличи или да се постигне конкретна стойност.
Клетки с променливи: Тези клетки съдържат променливите, които Solver ще коригира, за да постигне целта. Максимум 200 променливи клетки могат да бъдат обозначени в Solver.
Ограничения: Ограниченията са параметрите, в рамките на които Solver трябва да работи, за да постигне желания резултат. Те определят условията, които трябва да бъдат изпълнени при определяне на изискваните стойности.

Прилагане на Solver

След като Solver бъде добавен към Excel, можете да продължите да го използвате. В този пример ще използваме Solver, за да изчислим печалбата от бизнес за производство на палети въз основа на известни стойности на ресурсите, като ресурсите, необходими за палет, заедно с наличието на различни видове палети.

Клетките B3 до E3 изброяват различните видове палети, които компанията трябва да произвежда. Редът точно по-долу представлява броя на палетите, които трябва да бъдат произведени за всеки тип, инициализиран на нула. Следващият ред описва печалбата, свързана с всеки тип палет. Нашата цел е да определим колко палети да произведем за всеки вид, като общата печалба се показва в клетка F5. Ограниченията тук са наличните ресурси, диктуващи колко палети може да произведе компанията.

За да започнете, щракнете върху „Solver“, разположен в горния десен ъгъл, което ще изведе диалоговия прозорец на Solver. Въведете име или препратка към клетка за целевата клетка, като се уверите, че съдържа формула. В този сценарий клетка F5 служи като целева функция, която дава общата печалба за всички комбинирани видове палети, като се вземат предвид както наличните ресурси, така и палетите, които трябва да бъдат произведени.

В полето „Чрез промяна на променливи клетки“ изберете диапазона B4:E4 или чрез плъзгане на мишката, или чрез директно въвеждане на имената на клетките. Тези клетки представляват броя на палетите за тип и в момента са зададени на нула. Solver ще коригира тези стойности по време на изпълнение.

След това щракнете върху бутона „Добавяне“, за да въведете ограничения. Solver ще изчисли колко палети може да произведе компанията въз основа на наличните материали като лепило, пресоване, борови стърготини и дъбови стърготини. Ще наблюдавате как стойностите в колоната „Използвани“, които в момента са нула, се променят, когато изпълните Solver.

Въведете F8:F11 за „Cell Reference“, която съответства на колоната „Used“, и G8:G11 за колоната „Available“ в полето Constraint. Уверете се, че връзката е зададена <=по подразбиране, което показва, че стойностите в колоната Използвани трябва да бъдат по-малки или равни на тези в колоната Налични.

След като въведете всички променливи и ограничения, щракнете отново върху „Добавяне“ в диалоговия прозорец „Добавяне на ограничение“ и след това го затворете. Ще забележите също, че опцията „Направете неограничените променливи неотрицателни“ е активирана по подразбиране в диалоговия прозорец на параметрите на Solver, като гарантира, че всички променливи остават неотрицателни, дори ако не са зададени конкретни ограничения.

След като завършите въвеждането в диалоговия прозорец „Параметри на решаване“, щракнете върху бутона „Решаване“ и изчакайте Excel да достави резултатите.

Когато резултатите бъдат генерирани, ще се появи диалоговият прозорец Резултати от решаването, разкриващ новите стойности в клетки B4 до E4. Имайте предвид, че Solver променя вашите данни; ако предпочитате да се върнете към първоначалните стойности, можете да изберете опцията „Възстановяване на оригиналните стойности“. След като решите дали да запазите решението или да се върнете към оригиналните данни, уверете се, че „Отговор“ е отметнато вдясно, след което щракнете върху „OK“, за да излезете от диалоговия прозорец.

Ако решите да запазите новото решение, то ще бъде отразено във вашата електронна таблица при затваряне на диалоговия прозорец на Solver. Продукцията на компанията ще включва 23 палети Tahoe, 15 палети Pacific, 39 палети Savannah и нито един от палетите Aspen, които ще бъдат посочени в реда Pallets от B4 до D4. Освен това клетката за обща печалба ще се актуализира от нула до $58 800.

Важни съображения

Подобно на функцията Goal Seek на Excel, Solver изисква предварително да зададете необходимите формули, за да функционира правилно.
Можете да повлияете на метода за решаване на проблеми, като изберете бутона „Опции“ в диалоговия прозорец „Параметри на решаване“, където можете да посочите стойности за „Всички методи“, „GRG Nonlinear“ и „Evolutionary“.
Освен това Solver ви позволява да запазвате и зареждате модели за по-късна употреба. Когато зареждате съществуващи модели, уверете се, че сте въвели препратка за целия диапазон от клетки, отнасящи се до разглеждания проблем.
Препоръчително е да работите с копие на вашите данни, когато използвате Solver, тъй като той модифицира оригиналните данни, след като бъдат изпълнени, и възстановяването на тези данни може да не е възможно след извършване на промени.

Източник