Excel Solver: Какво е това и кога трябва да го използвате

Инструментите за електронни таблици на Excel като Goal Seek, които променят една променлива, са полезни, но за съжаление повечето проблеми в бизнеса не са толкова лесни. Обикновено ще се сблъскате с формули с поне две, а понякога и десетки променливи. Често проблемът ще има повече от едно решение и вашето предизвикателство ще бъде да използвате Excel, за да намерите оптималното решение (тоест това, което максимизира печалбата, или минимизира разходите, или отговаря на други критерии). За тези по-големи предизвикателства се нуждаете от по-мускулест инструмент. Excel има само отговора: Solver. Excel Solver е усъвършенствана програма за оптимизация, която ви позволява да намерите решенията на сложни проблеми, които иначе биха изисквали математически анализ на високо ниво.

Разбиране на Excel Solver

Solver, подобно на Goal Seek , използва итеративен метод за извършване на своите изчисления. Използването на итерация означава, че Excel Solver опитва решение, анализира резултатите, опитва друго решение и т.н. Въпреки това, тази циклична итерация не е само догадки от страна на Solver. Това би било глупаво. Не, Excel Solver изследва как се променят резултатите с всяка нова итерация и чрез някои сложни математически процеси (които, за щастие, се случват на заден план и могат да бъдат игнорирани), обикновено може да каже в каква посока трябва да се насочи към решението.

Предимствата на Excel Solver

Да, Goal Seek и Solver са итеративни, но това не ги прави равни. Всъщност Excel Solver носи редица предимства на масата:

• Excel Solver ви позволява да зададете множество регулируеми клетки. Можете да използвате до 200 регулируеми клетки.
• Excel Solver ви позволява да настроите ограничения върху регулируемите клетки. Например, можете да кажете на Solver да намери решение, което не само увеличава печалбата, но и отговаря на определени условия, като например постигане на брутен марж между 20 и 30 процента или поддържане на разходите под 100 000 долара. За тези условия се казва, че са ограничения за решението.
• Excel Solver търси не само желания резултат („целта“ в Goal Seek), но и оптималния. Например, търсенето на оптимален резултат може да означава, че можете да намерите решение, което е възможно най-много или минимално.
• За сложни проблеми Solver може да генерира множество решения . След това можете да запазите тези различни решения при различни сценарии.

Кога трябва да използвате Excel Solver?

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

• Те имат една единствена целева клетка (наричана още целева клетка ), която съдържа формула, която искате да увеличите, минимизирате или зададете на конкретна стойност. Тази формула може да бъде изчисление като общи транспортни разходи или нетна печалба.
• Формулата на целевата клетка съдържа препратки към една или повече променливи клетки (наричани още неизвестни или променящи се клетки ). Solver коригира тези клетки, за да намери оптималното решение за формулата на целевата клетка. Тези променливи клетки могат да включват елементи като продадени единици, разходи за доставка или рекламни разходи.
• По избор има една или повече ограничителни клетки, които трябва да отговарят на определени критерии. Например, може да изискате рекламата да бъде по-малко от 10 процента от общите разходи или отстъпката за клиентите да бъде между 40 и 60 процента.

Например, изображението по-долу показва модел на данни на работен лист, който е настроен за Excel Solver. Моделът показва приходите (цената, умножената на продадените единици) и разходите за два продукта, печалбата, произведена от всеки продукт, и общата печалба. Въпросът, на който трябва да се отговори тук, е следният: Колко единици от всеки продукт трябва да бъдат продадени, за да получите обща печалба от $0? Това е известно в бизнеса като анализ на рентабилността.

Целта на този модел на данни е да се намери точката на рентабилност (където общата печалба е $0).

Това звучи като проста задача за търсене на цели, но този модел има сложен аспект: променливите разходи. Обикновено променливите разходи на даден продукт са неговите единични разходи, умножени на броя на продадените единици. Ако производството на продукт А струва $10, а вие продадете 10 000 единици, променливите разходи за този продукт са $100 000. В реалния свят обаче такива разходи често се смесват между множество продукти. Например, ако провеждате съвместна рекламна кампания за два продукта, тези разходи се поемат от двата продукта. Следователно този модел предполага, че разходите за един продукт са свързани с продадените единици на другия. Ето, например, формулата, използвана за изчисляване на разходите за надуваема дъска за дартс (клетка B8):

=B7 * B4 – C4

С други думи, променливите разходи за надуваемата дъска за дартс се намаляват с един долар за всяка продадена единица на Dog Polisher. Променливите разходи на последния използват подобна формула (в клетка C8):

=C7 * C4 – B4

Наличието на променливи разходи, свързани с множество продукти, поставя този модел на данни извън това, което Goal Seek може да направи, но Solver се справя с предизвикателството. Ето специалните клетки в модела, които Solver ще използва:

• Целевата клетка е С14; общата печалба и целевото решение за тази формула е 0 (т.е. точката на рентабилност).
• Променящите се клетки са B4 и C4, които съдържат броя на продадените единици за всеки продукт.
• За ограничения може да искате да добавите, че и двете клетки за печалба на продукта (B12 и C12) също трябва да са 0.

Зареждане на добавката Excel Solver

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

Инсталирате пакетните добавки с помощта на диалоговия прозорец Опции на Excel; можете да ги намерите в секцията Добавки. След като бъдат инсталирани, добавките са достъпни веднага. Обикновено се появяват в раздел, свързан с тяхната функция. Например, Solver се появява в раздела Данни.

Ето стъпките, които трябва да следвате, за да заредите добавката Excel Solver:

Изберете Файл→ Опции.
Появява се диалоговият прозорец Опции на Excel.

Изберете Добавки.

В списъка Управление изберете Добавки на Excel и след това изберете Go.
Excel показва диалоговия прозорец за добавки.

Поставете отметка в квадратчето Solver Add-In

Щракнете върху OK.
Excel добавя бутон за решаване към групата Анализ на раздела Данни.

Разгледайте, за да разберете как да използвате Excel Solver .