Какво е Excel Solver?

2024 Автор: Abigail Brown | [email protected]. Последно модифициран: 2023-12-17 06:40

Добавката Excel Solver извършва математическа оптимизация. Това обикновено се използва за приспособяване на сложни модели към данни или намиране на итеративни решения на проблеми. Например, може да искате да напаснете крива през някои точки от данни, като използвате уравнение. Решавателят може да намери константите в уравнението, които дават най-добро съответствие на данните. Друго приложение е, когато е трудно да се пренареди модел, за да направи необходимия изход предмет на уравнение.

Къде е Solver в Excel?

Добавката Solver е включена в Excel, но не винаги се зарежда като част от инсталация по подразбиране. За да проверите дали е зареден, изберете раздела DATA и потърсете иконата Solver в секцията Analysis.

Ако не можете да намерите Solver под раздела DATA, тогава ще трябва да заредите добавката:

1. Изберете раздела ФАЙЛ и след това изберете Опции.

   

2. В диалоговия прозорец Опции изберете Добавки от разделите отляво.

   

3. В долната част на прозореца изберете Добавки на Excel от падащото меню Manage и изберете Go…

   

4. Поставете отметка в квадратчето до Solver Add-in и изберете OK.

   

5. Командата Solver сега трябва да се появи в раздела DATA. Готови сте да използвате Solver.

   

Използване на Solver в Excel

Нека започнем с прост пример, за да разберем какво прави Solver. Представете си, че искаме да знаем какъв радиус ще даде кръг с площ от 50 квадратни единици. Знаем уравнението за площта на кръг (A=pi r²). Можем, разбира се, да пренаредим това уравнение, за да дадем радиуса, необходим за дадена област, но за пример нека се преструваме, че не знаем как да направим това.

Създайте електронна таблица с радиус в B1 и изчислете площта в B2, като използвате уравнението =pi()B1^2.

Можем ръчно да коригираме стойността в B1, докато B2 покаже стойност, която е достатъчно близка до 50. В зависимост от това колко точни сме трябва да бъде, това може да е практичен подход. Въпреки това, ако трябва да бъдем много точни, ще отнеме много време, за да направим необходимите корекции. Всъщност това по същество прави Solver. Прави корекции на стойностите в определени клетки и проверява стойността в целевата клетка:

1. Изберете DATA раздел и Solver, за да заредите Solver Parameters диалогов прозорец

2. Задаване на клетката за цел да бъде площта, B2. Това е стойността, която ще бъде проверена, коригирайки други клетки, докато тази достигне правилната стойност.

   

3. Изберете бутона за Стойност на: и задайте стойност 50. Това е стойността, която B2 трябва да постигне.

   

4. В полето, озаглавено Чрез промяна на променливи клетки: въведете клетката, съдържаща радиуса, B1.

   

5. Оставете другите опции както са по подразбиране и изберете Решаване. Оптимизацията се извършва, стойността на B1 се коригира, докато B2 стане 50 и се показва диалогът Solver Results.

   

6. Изберете OK, за да запазите решението.

   

Този прост пример показа как работи решаващата програма. В този случай бихме могли по-лесно да намерим решението по други начини. След това ще разгледаме някои примери, при които Solver дава решения, които биха били трудни за намиране по друг начин.

Поставяне на сложен модел с помощта на добавката Excel Solver

Excel има вградена функция за извършване на линейна регресия, напасвайки права линия през набор от данни. Много общи нелинейни функции могат да бъдат линеаризирани, което означава, че линейната регресия може да се използва за напасване на функции като експоненциални. За по-сложни функции Solver може да се използва за извършване на „минимизиране на най-малките квадрати“. В този пример ще разгледаме напасването на уравнение във формата ax^b+cx^d към данните, показани по-долу.

Това включва следните стъпки:

1. Подредете набора от данни със стойностите x в колона A и y-стойностите в колона B.
2. Създайте 4-те стойности на коефициента (a, b, c и d) някъде в електронната таблица, те могат да получат произволни начални стойности.

3. Създайте колона с подходящи Y стойности, като използвате уравнение от формата ax^b+cx^d, което препраща към коефициентите, създадени в стъпка 2, и x стойностите в колона A. Имайте предвид, че за да копирате формулата надолу колоната, препратките към коефициентите трябва да са абсолютни, докато препратките към x стойностите трябва да са относителни.

   

4. Въпреки че не е от съществено значение, можете да получите визуална индикация за това колко добре отговаря уравнението, като начертаете двете y колони срещу стойностите на x на една XY точкова диаграма. Има смисъл да се използват маркери за оригиналните точки от данни, тъй като това са дискретни стойности с шум, и да се използва линия за напаснатото уравнение.

   

5. След това се нуждаем от начин за количествено определяне на разликата между данните и нашето монтирано уравнение. Стандартният начин да направите това е да изчислите сумата от квадратните разлики. В трета колона за всеки ред първоначалната стойност на данните за Y се изважда от стойността на напаснатото уравнение и резултатът се повдига на квадрат. И така, в D2, стойността се дава от =(C2-B2)^2 След това се изчислява сумата от всички тези квадратни стойности. Тъй като стойностите са на квадрат, те могат да бъдат само положителни.

   

6. Вече сте готови да извършите оптимизацията с помощта на Solver. Има четири коефициента, които трябва да бъдат коригирани (a, b, c и d). Имате и една единствена обективна стойност, която да минимизирате, сумата от разликите на квадрат. Стартирайте програмата за решаване, както по-горе, и задайте параметрите на програмата за решаване, за да се позовават на тези стойности, както е показано по-долу.

   

7. Премахнете отметката от опцията Направете неограничените променливи неотрицателни, това ще принуди всички коефициенти да приемат положителни стойности.

   

8. Изберете Решаване и прегледайте резултатите. Диаграмата ще се актуализира, давайки добра индикация за годността. Ако солвърът не даде добро прилягане при първия опит, можете да опитате да го стартирате отново. Ако съответствието се е подобрило, опитайте да разрешите от текущите стойности. В противен случай можете да опитате ръчно да подобрите прилягането, преди да разрешите.

   

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

Итеративно решаване на модел

Понякога има сравнително просто уравнение, което дава резултат по отношение на някои входни данни. Въпреки това, когато се опитваме да обърнем проблема, не е възможно да намерим просто решение. Например мощността, консумирана от превозно средство, се дава приблизително от P=av + bv^3, където v е скоростта, a е коефициент за съпротивлението при търкаляне и b е коефициент за аеродинамично съпротивление. Въпреки че това е доста просто уравнение, не е лесно да се пренареди, за да се даде уравнение на скоростта, която превозното средство ще достигне за дадена мощност. Можем обаче да използваме Solver, за да намерим итеративно тази скорост. Например, намерете скоростта, постигната с входяща мощност от 740 W.

1. Настройте проста електронна таблица със скоростта, коефициентите a и b и мощността, изчислена от тях.

   

2. Стартирайте Solver и въведете силата, B5, като цел. Задайте целева стойност на 740 и изберете скоростта, B2, като променливи клетки за промяна. Изберете solve, за да стартирате решението.

   

3. Решавателят коригира стойността на скоростта, докато мощността стане много близо до 740, осигурявайки необходимата скорост.

   

4. Решаването на модели по този начин често може да бъде по-бързо и по-малко податливо на грешки, отколкото обръщането на сложни модели.

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

Изборът на метод за решаване също може да бъде труден. Simplex LP е подходящ само за линейни модели, ако проблемът не е линеен, той ще се провали със съобщение, че това условие не е изпълнено. Другите два метода са подходящи за нелинейни методи. GRG Nonlinear е най-бързият, но решението му може да зависи силно от първоначалните стартови условия. Той има гъвкавостта, че не изисква променливи да имат зададени граници. Еволюционният решаващ инструмент често е най-надеждният, но изисква всички променливи да имат както горна, така и долна граница, което може да е трудно да се изчисли предварително.

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