Поиск решения эксель: Загрузка надстройки «Поиск решения» в Excel

Если вы нажмете кнопку Options в диалоговом окне Solver (см. рис. 2), вы улучшите точность решений, полученных Solver. Например. вы можете изменить значения Точность ограничения по умолчанию 0,000001), Максимум Время ( Секунды ) и Итерации .

Иногда Solver находит локальное минимальное или максимальное значение вместо глобального минимума или максимума. Это можно исправить, выбрав другие начальные значения. Вы можете сделать это автоматически, нажав кнопку Options в диалоговом окне Solver, а затем выбрав опцию Use Multistart на вкладке GRG Nonlinear диалогового окна Options . Решатель теперь будет запускаться несколько раз, используя разные начальные значения, выбирая значения, дающие наилучший результат. Цена этого варианта — более медленное время работы.

Служба поддержки Майкрософт (2019 г.) Определение и решение проблемы с помощью Solver
https://support.microsoft.com/en-us/office/define-and-solve-a-problem-by- использование-решателя-5d1a388f-079d-43ac-a7eb-f63e45925040

Оптимизационное моделирование с помощью Solver в Excel • Стивен Л. Нельсон

Инструмент Excel позволяет решать задачи оптимизационного моделирования, также известные как программы линейного программирования. В задаче оптимизационного моделирования вы хотите оптимизировать целевую функцию, но в то же время признаете наличие ограничений или пределов. Хотя это абстрактное определение звучит сложно, по крайней мере на концептуальном уровне, оптимизационное моделирование имеет здравый смысл, если вы предоставите конкретный пример.

EasyRefresher: как работает оптимизационное моделирование

Предположим, вы работаете застройщиком и подрядчиком в сфере жилой недвижимости. Вы создаете и продаете два продукта: строительные участки и дома. Предположим, вы зарабатываете 20 000 долларов на каждом доме, который вы строите, и 15 000 долларов на каждом участке под застройку, который вы разрабатываете, а затем продаете. Ваша основная финансовая цель состоит в том, чтобы максимизировать прибыль, и эта цель может быть выражена в виде целевой функции или уравнения, которое вы хотите максимизировать:

15 000$*Участки+20 000$*Дома=Прибыль

Конечно, любая целевая функция ограничена определенными ограничениями. Чтобы продолжить рассмотрение вымышленного случая жилой застройки, предположим, что у вас есть два основных ограничивающих фактора: оборотный капитал и мощность бульдозера. Ваш оборотный капитал в размере 1 200 000 долларов США ограничивает количество участков и домов, которые вы можете ежегодно продавать, потому что каждый участок требует денежных вложений в размере 50 000 долларов, а каждый дом требует денежных вложений в размере 25 000 долларов. Тот факт, что у вас есть один бульдозер, доступный только на 3000 часов в год, также ограничивает количество участков и домов, которые вы можете ежегодно продавать, потому что каждый участок требует 80 часов бульдозера, а каждый дом требует 200 часов бульдозера. Эти два ограничения также могут быть выражены в виде уравнений. Например, ограничение оборотного капитала может быть выражено следующим образом:

$50,000*Участки+$25,000*Дома<=$1,200,000

Эта формула говорит, что результат формулы $50,000, умноженный на количество участков плюс $25,000, умноженный на количество домов, должен быть меньше или равен лимиту оборотного капитала в $1,200,000. Символ меньше или равно представлен оператором <=.

Ограничение мощности бульдозера может быть выражено следующим образом:

80*Участки+200*Дома<=3000

Эта формула показывает результат формулы, умноженной на 80-кратное количество участков плюс 200-кратное количество домов. быть меньше или равно предельному наработке бульдозера в 3000 часов. Опять же, символ меньше или равно представлен оператором <=.

Как правило, у вас также есть ограничения политики, когда вы работаете с задачей моделирования оптимизации. Предположим, что в соответствии с политикой вы хотите поддерживать определенный уровень активности как в обустройстве участков, так и в строительстве домов. Например, вы можете сказать, что, поскольку вы должны поддерживать опыт своей команды как в области разработки необработанных земель, так и в сфере жилищного подряда, вы хотите застраивать не менее 10 участков каждый год и строить не менее 5 домов. Эти два ограничения также должны быть выражены в виде уравнений. Ограничение политики минимального количества лотов может быть выражено следующим образом:

Участки>=10

Эта формула говорит, что вы хотите построить не менее 10 участков под застройку. Или, переформулируя, эта формула говорит, что переменная лотов должна быть больше или равна 10. Символ больше или равно представлен оператором >=.

Ограничение политики минимального количества домов может быть выражено следующим образом:

Дома>=5

Эта формула говорит, что вы хотите построить не менее 5 домов. Или, переформулируя, эта формула говорит, что переменная домов должна быть больше или равна 10. Опять же, символ больше или равно представлен оператором >=.

С помощью информации, представленной в предыдущих абзацах этого EasyRefresherTM, я описал вашу вымышленную проблему оптимизации-моделирования. Вы хотите максимизировать свою прибыль, которую можно описать с помощью следующей целевой функции:

15 000 долларов * лоты + 20 000 долларов * дома = прибыль

, но вы не можете развивать неограниченное количество участков под застройку или строить неограниченное количество домов. На вас распространяются следующие ограничения:

50 000 долларов США*Участки земли+25 000 долларов США*Дома <= 1 200 000 долларов США

80*Участки+200*Дома<=3000

Участки>=10

Дома>=5

с помощью такой техники, как симплексная алгебра. Или вы можете ввести целевую функцию и уравнения ограничений в Excel, и он решит проблему, что представляет собой метод решения, описанный в следующих параграфах.

Решение задачи оптимизации

Чтобы использовать Решатель Excel, сначала создайте рабочую книгу, в которой описывается ваша задача моделирования оптимизации, включая ее целевую функцию и любые ограничения, а затем скажите Солверу искать оптимальное решение. Пока вы понимаете концепции оптимизационного моделирования, как описано в предыдущем EasyRefresher, этот процесс прост.

Настройка рабочей книги для решателя

Чтобы настроить рабочую книгу для решателя, необходимо выполнить три шага: ввести предположения переменных, которые оптимизируют вашу целевую функцию, ввести целевую функцию, а затем ввести функции ограничений. На рис. 6-17 показана рабочая тетрадь, созданная для решения примера задачи, обсуждавшейся в EasyRefresher.

Чтобы создать эту или любую другую книгу модели оптимизации, выполните следующие действия:

1. При необходимости укажите Excel, чтобы отображались фактические формулы, а не результаты.
   Вам не нужно предпринимать этот шаг, но поскольку при оптимизационном моделировании вас действительно больше интересует, как выглядят формулы, а не результаты, которые они дают, вы, вероятно, захотите указать Excel отображать формулы, а не результаты формулы. Для этого выберите команду «Параметры» в меню «Сервис», перейдите на вкладку «Вид» и установите флажок «Формулы». Рисунок 6-17 отображает формулы вместо результатов формул.
2. Укажите начальные предположения для переменных.
   Вам необходимо предоставить начальные предположения для переменных, которые вы пытаетесь оптимизировать. Вы можете сделать это, просто введя значения в ячейки, но я рекомендую вам создать небольшое расписание имен переменных и предполагаемых значений переменных, как показано на рис. 6-17, в диапазоне рабочего листа A1:B3. показано на рис. 6.17 — и вы действительно должны — вы также захотите назвать ячейки, содержащие ваши предположения. В этом случае вы можете сделать это, выбрав диапазон рабочего листа, который содержит имена переменных (участки, дома) и предположения — A2:B3 на рис. 6-17 — и затем выбрав команду «Имя» меню «Вставка», а затем выбрав Создать команду. Когда Excel отобразит диалоговое окно «Создание имен», установите флажок «Левый столбец» и нажмите «ОК».
3. Опишите целевую функцию.
   На рис. 6-17 рабочий лист описывает уравнение со следующей формулой, расположенной в ячейке B5: =15000*Участки+20000*Дома Поскольку ячейки, содержащие переменные предположения, были названы Участки и Дома, целевая функция использует эти имена вместо ссылок на ячейки. Обратите внимание, что метка в ячейке A5 идентифицирует уравнение, но вам нужно ввести только фактическое уравнение, показанное в ячейке B5.
4. Опишите каждое ограничение.
   На рис. 6-17 ограничения описаны в диапазоне рабочего листа B8:C11. Чтобы описать отдельное ограничение, вы вводите уравнение ограничения в одну ячейку и ограничивающее постоянное значение в другую ячейку. Например, ограничение оборотного капитала, упомянутое в предыдущем EasyRefresherTM, гласит, что формула 50 000 долларов, умноженная на количество участков, плюс 25 000 долларов, умноженная на количество домов, должна быть меньше или равна 1 200 000 долларов (ограничивающая константа). Чтобы описать это первое ограничение, введите следующую формулу в ячейку B8: =Участки*50000+Дома*25000 и вы вводите постоянное значение, которое ограничивает эту формулу в ячейке C8:

   1200000

   Чтобы описать второе ограничение — то, которое количественно ограничивает грузоподъемность бульдозера, — введите следующую формулу в ячейку B9:

   =Участки*80+Дома*200

   , и вы вводите постоянное значение, которое ограничивает эту формулу в ячейке C9:

   3000

   Чтобы описать третье ограничение, вытекающее из вашего ограничения политики минимального количества лотов, введите следующую формулу в ячейку B10:

   = Партии

   , и вы вводите постоянное значение, которое ограничивает эту формулу в ячейке C10:

   10

   Наконец, чтобы описать четвертое ограничение, которое вытекает из вашего ограничения политики минимального количества домов, вы вводите следующую формулу в ячейку B11:

   = Дома

   , и вы вводите постоянное значение, которое ограничивает эту формулу в ячейке C11:

   5

   Выполнив предыдущие шаги, вы готовы использовать Solver для поиска оптимального решения вашей целевой функции.

Использование Решателя

Если вы настроите рабочие книги так, как показано на рис. 6-17, вы обнаружите, что Решатель прост в использовании. Вы просто выполняете следующие шаги:

1. Выберите команду Solver в меню Tools.
   Excel отобразит диалоговое окно «Параметры решателя» (см. рис. 6-18). Если вы не видите команду «Поиск решения» в меню «Инструменты», это означает, что надстройка «Поиск решения» еще не установлена. Чтобы установить Solver, выберите команду «Надстройки» в меню «Инструменты». Когда Excel отобразит диалоговое окно «Надстройки», прокрутите вниз список «Доступные надстройки», пока не увидите запись надстройки «Поиск решения». Установите флажок «Надстройка решения», нажмите «ОК», а затем, если Excel запросит это, предоставьте установочный компакт-диск Excel или Office. Рисунок 6-18. Диалоговое окно «Параметры решателя».
2. Определите целевую функцию.
   Введите адрес ячейки, содержащей вашу цель, в поле «Установить целевую ячейку». Например, на рис. 6-17 ячейка B5 содержит целевую функцию, поэтому вы должны ввести B5 в поле «Установить целевую ячейку».
3. Опишите, как Solver должен оптимизировать целевую функцию.
   Используйте кнопки параметров «Равно», чтобы указать, как Solver оптимизирует целевую функцию. Например, в случае функции прибыли вы хотите максимизировать функцию, поэтому нажимаете кнопку Max. Это относится к рабочей книге, показанной на рис. 6-17. Если бы ваша целевая функция описывала затраты, вместо этого вы бы хотели минимизировать функцию и поэтому нажали бы кнопку Min. У вас также могут быть ситуации, в которых вы хотите, чтобы целевая функция возвращала определенное значение, и поэтому в этом особом случае вы должны нажать кнопку «Значение» и затем указать указанное значение.
4. Сообщите Солователю, в каких ячейках находятся ваши догадки переменных.
   Используйте поле «Изменение ячеек», чтобы указать Excel, где вы сохранили переменные, используемые в целевой функции и уравнениях ограничений. На рис. 6.17, например, рабочая книга хранит эти переменные в ячейках B2 и B3, так что вы можете ввести адреса этих двух ячеек в поле By Changeing Cells. Если вы назвали переменные ячейки, вы также можете ввести имена ячеек, как показано на рис. 6-18. Ячейка B2 называется «Участки», а ячейка B3 — «Дома».
5. Сообщите Solver, что вы хотите начать описание ограничений.
   Нажмите кнопку «Добавить». Excel отобразит диалоговое окно «Добавить ограничение» (см. рис. 6-19). Рисунок 6-19. Диалоговое окно «Добавить ограничение».
6. Опишите первое ограничение.
   Чтобы добавить ограничение, используйте поле Ссылка на ячейку, чтобы определить ячейку, содержащую первое уравнение ограничения, используйте поле безымянного оператора, чтобы выбрать соответствующий оператор ограничения, а затем используйте поле Ограничение, чтобы определить ячейку, содержащую постоянное значение первого ограничения. . В случае рабочей книги, показанной на рис. 6-17, например, вы можете сделать это, щелкнув поле «Ссылка на ячейку», а затем щелкнув ячейку B8, выбрав оператор <=, а затем щелкнув поле «Ограничение» и затем щелкнув ячейку C8. . Рисунок 6-19показывает, как должно выглядеть диалоговое окно "Добавить ограничение" для указания этого ограничения. Щелкните Добавить, чтобы добавить ограничение. Затем повторите эту задачу, чтобы добавить больше ограничений. Безымянный раскрывающийся список содержит пять операторов ограничения: <= для указания меньше или равно, = для указания равно, >= для указания больше или равно, int для указания, что переменная должна быть целочисленной, и bin для указания того, что переменная должна быть двоичной (равной 1 или 0).
7. Добавьте любые неявные целочисленные ограничения.
   Во многих задачах оптимизации моделирования у вас также будут неявные целочисленные ограничения. Это означает, например, что вы не можете использовать десятичные значения как часть оптимального решения. Например, вы можете сказать, что должны построить целое число участков под застройку или построить целое число домов. Или, немного переформулировав, вы можете сказать, что вы не можете дойти до конца года, когда один из ваших строительных участков будет готов только наполовину или один из ваших домов будет построен только частично. Чтобы указать целочисленное ограничение, используйте поле «Ссылка на ячейку», чтобы определить ячейку переменной, которая должна быть целочисленной, а затем выберите оператор int в раскрывающемся списке без имени. На рис. 6-20 показано, как выглядит диалоговое окно Add Constraint, когда вы указываете целочисленное ограничение. Обратите внимание, что вы не вводите слово integer в поле Constraint. Эксель так делает. Рисунок 6-20. Диалоговое окно «Добавить ограничение», на этот раз показывающее, как выглядит целочисленное ограничение.
8. Добавьте любые бинарные ограничения.
   В некоторых задачах оптимизационного моделирования у вас также могут быть бинарные ограничения. Двоичное ограничение — это ограничение, в котором переменная должна быть равна 0 или 1. Чтобы указать двоичное ограничение, используйте поле «Ссылка на ячейку», чтобы указать ячейку переменной, которая должна быть двоичной, а затем выберите оператор бина из раскрывающегося списка без имени. .
9. Сообщите Excel, что вы закончили добавлять ограничения.
   Чтобы закрыть диалоговое окно «Добавить ограничение» после того, как вы закончите описание последнего ограничения, нажмите «ОК». Excel закроет диалоговое окно «Добавить ограничение» и вернет вас в диалоговое окно «Параметры решателя». Все добавленные вами ограничения отображаются в списке Subject To Constraints. Если вы добавите ограничение, а затем захотите его удалить, откройте диалоговое окно Solver Parameters, выберите ограничение и нажмите кнопку Удалить. Если вы добавите ограничение, а затем захотите его отредактировать, откройте диалоговое окно «Параметры решателя», выберите ограничение и нажмите кнопку «Изменить». Когда Excel отобразит диалоговое окно «Редактировать ограничение», используйте его для внесения изменений. Диалоговое окно «Редактировать ограничение» работает аналогично диалоговому окну «Добавить ограничение».
10. Скажите Excel, чтобы он искал решение.
    Нажмите кнопку «Решить», чтобы программа Excel нашла решение вашей задачи оптимизации моделирования. Excel ищет решение, а затем отображает диалоговое окно Solver Results (см. рис. 6-21). Рисунок 6-21. Диалоговое окно «Результаты решения».

    В этом диалоговом окне указываются значения переменных, которые оптимизируют вашу целевую функцию, и запрашивается, что вы хотите сделать с этими значениями.

    • Чтобы указать Excel сохранить свое решение, нажмите кнопку «Сохранить решение решения» и нажмите «ОК».
    • Чтобы указать Excel отказаться от своего решения, нажмите кнопку «Восстановить исходные значения» и нажмите «ОК».
    • Чтобы указать Excel сохранить свое решение в виде сценария, нажмите кнопку «Сохранить сценарий», а затем при появлении запроса укажите имя сценария.
    В предыдущем разделе «Анализ «что, если» с помощью диспетчера сценариев» обсуждается, как работают сценарии.

Просмотр отчетов решателя

В диалоговом окне «Результаты решателя» можно создать несколько отчетов об оптимизационном моделировании, которое выполняет решатель. Чтобы сгенерировать эти отчеты, щелкните нужный отчет или отчеты, когда Excel отобразит диалоговое окно «Результаты решения» (см. рис. 6-21).

Понимание отчета об ответах

Отчет об ответах, который Excel помещает на отдельный рабочий лист, предоставляет информацию о том, насколько оптимальное решение близко к вашим исходным предположениям, и о том, какие ограничения связывают или ограничивают оптимизацию. На рис. 6-22 показан пример отчета об ответах. В верхней части отчета Excel сравнивает исходный результат формулы функции возражения с результатом функции возражения, предоставленным исходными значениями переменных. Например, на рис. 6-22 Excel показывает исходное значение целевой функции как 425 000, а конечное значение целевой функции — как 440 000. В этом случае Решатель улучшает целевую функцию на 15 000.

После сравнения исходных и окончательных значений результатов формулы целевой функции Excel сравнивает исходные и окончательные значения переменных (см. рис. 6-22). Эта информация позволяет точно увидеть, насколько Excel корректирует переменные, чтобы оптимизировать целевую функцию.

В нижней части отчета с ответами Excel анализирует ограничения, вычисляя результаты формулы для ограничений и затем сравнивая эти результаты формулы с константами ограничения. На первый взгляд это звучит как рутинная работа, но эта информация часто очень полезна по двум важным причинам: во-первых, вы можете использовать информацию о состоянии, чтобы увидеть, какие ограничения являются обязывающими или ограничивающими. На рис. 6-22 связующим ограничением являются часы работы бульдозера. Во-вторых, вы можете использовать информацию Slack, чтобы увидеть, насколько близко данное ограничение становится обязательным. На рис. 6-22 ограничение оборотного капитала показывает только 25000 резервов; Другими словами, у вас есть только 2% погрешности с вашим оборотным капиталом (25 000 долларов США / 1 200 000 долларов США).

Понимание отчета о чувствительности

Отчет о чувствительности, который Excel также помещает на отдельный рабочий лист, показывает уменьшенные градиенты для переменных и множители Лагранжа для ограничений (см. рис. 6-23). Уменьшенное значение градиента показывает, как изменится целевая функция, если значение переменной увеличится на 1. Множитель Лагранжа показывает, как изменится целевая функция, если константа ограничения увеличится на 1.

Более внимательное изучение отчета о чувствительности, показанного на рис. 6-23, например, показывает, что уменьшенные значения градиента для обеих переменных Lots и Houses равны 0. Это указывает на то, что ни одно из значений не может быть увеличено. В отчете о чувствительности показаны множители Лагранжа для ограничения оборотного капитала и ограничения по часам работы бульдозера. Множитель Лагранжа для лимита часов работы бульдозера, равный 78,125, показывает, что увеличение количества доступных часов работы бульдозером на 1 час увеличивает целевую функцию (вашу прибыль) на 78,125.

Если вы создали линейную оптимизационную модель — а линейные модели я кратко рассмотрю в следующем разделе «Настройка работы решателя» — ваши отчеты о чувствительности включают несколько дополнительных сведений, включая снижение затрат, теневые цены, целевые коэффициенты и диапазоны правых частей ограничений.

Понимание отчета об ограничениях

Отчет об ограничениях, который Excel помещает на еще один рабочий лист, показывает, насколько могут изменяться значения переменных, но оставаться в рамках ограничений (см. рис. 6-24). Для каждой переменной отчет об ограничениях показывает рассчитанное оптимальное значение, минимально возможное допустимое значение и максимально возможное допустимое значение. На рис. 6-24 — и это часто бывает — нижний предел и верхний предел равны оптимальным значениям. Это показывает, что эти значения переменных могут быть изменены, не влияя на оптимальное решение и не нарушая ограничений. Обратите внимание, однако, что некоторые проблемы оптимизации позволяют вам изменять значения переменных, продолжая оптимизировать функцию и оставаясь в рамках установленных ограничений. Это происходит, когда имеется несколько наборов значений переменных, оптимизирующих уравнение.

Настройка работы решателя

В диалоговом окне «Параметры решателя» есть кнопка «Параметры», которую можно щелкнуть, чтобы отобразить диалоговое окно «Параметры решателя» (см. рис. 6-25). Диалоговое окно «Параметры решателя» позволяет настроить способ решения вашей проблемы решателем. В следующих абзацах кратко описаны все параметры Солвера, а также то, как и почему вы можете изменить их настройки.

Максимальное время и число итераций

Некоторые из этих параметров говорят сами за себя. Поле «Максимальное время», например, позволяет вам указать, как долго Solver должен работать над проблемой, и может ли оно быть установлено до 32 767 секунд (что составляет более девяти часов). Поле Итерации позволяет указать, сколько итераций Solver должен работать над проблемой, и его можно установить до 32 767.

Точность

В поле Точность можно указать, насколько точным должен быть Решатель при проверке возможного оптимального решения на соответствие вашим ограничениям. Настройка точности 0,000001, настройка по умолчанию, сообщает Excel, что если значение формулы ограничения находится в пределах 0,000001 от константы ограничения, ограничение удовлетворяется. В поле Точность можно установить любое значение от 0 до 1. Чтобы уменьшить точность, используйте большее значение Точности. Чтобы повысить точность, используйте меньшее значение Precision. Как и ожидалось, по мере повышения точности Excel требуется больше времени, чтобы найти решение.

Допуск

Поле Допуск позволяет указать, насколько точным должен быть Решатель, чтобы убедиться, что все целочисленные ограничения соблюдены. Параметр допуска по умолчанию, равный 5 или 5 %, означает, что если переменная целевой функции находится в пределах 5 % от целочисленного значения — другими словами, от 95 % до 105 %, — Excel может рассматривать ее как целое число. Параметр «Допуск», кстати, применяется только к задачам оптимизации, использующим целочисленные ограничения. Как и ожидалось, по мере повышения точности Excel требуется больше времени для достижения решения.

Схождение

Поле Схождение позволяет указать, когда Excel должен прекратить поиск лучшего решения. Вы можете установить значение сходимости на любое дробное значение от 0 до 1. Когда изменение целевой функции меньше, чем значение, указанное в поле сходимости, Excel перестает искать лучшее решение. Параметр Convergence, кстати, применяется только к нелинейным задачам оптимизационного моделирования. По мере того, как вы уменьшаете настройку сходимости (т. е. увеличиваете точность), Excel, как и следовало ожидать, занимает больше времени для достижения решения.

Предполагать линейную модель

Если отношения в вашей оптимизации являются линейными, вы можете установить флажок Предполагать линейную модель. Делая это, вы упрощаете вычисления, которые должен делать Excel, и тем самым ускоряете работу.

Предполагать неотрицательное значение

Если вы хотите указать Excel, что ваши переменные должны быть равны или больше 0, когда вы не установили ограничение нижнего предела, вы можете установить флажок Предполагать неотрицательное значение. По сути, когда вы устанавливаете этот флажок, вы указываете Excel создать еще один неявный набор ограничений.

Использовать автоматическое масштабирование

При работе с переменными и результатами формул, которые отличаются по величине, следует установить флажок «Автоматическое масштабирование». Примером такой ситуации может служить определение нормы прибыли (в процентах) с использованием набора больших долларовых переменных.

Показать результаты итерации

Вы можете установить флажок «Показать результаты итерации», чтобы программа Excel делала паузу после каждой итерации вычисления. После каждой итерации вычислений Excel отображает диалоговое окно «Показать пробное решение». Вы можете сохранить пробное решение, нажав кнопку Сохранить сценарий. Или вы можете продолжить работу над решением, нажав кнопку «Продолжить». Чтобы прекратить итерации, нажмите кнопку «Стоп».

Оценки

Вы используете кнопки параметров Оценки — Касательная и Квадратичная — для выбора подхода, который вы хотите использовать в Excel для получения первого пробного решения. Выберите Касательная, если вы хотите, чтобы Excel выполнял линейную экстраполяцию касательного вектора. Выберите «Квадратичный», если вы хотите, чтобы Excel выполнял квадратичную экстраполяцию — метод, который может дать лучшие результаты для задач нелинейного оптимизационного моделирования.

Производные

Используйте кнопки параметров Производные — Вперед и Центральный — для указания разности, используемой для оценки частных производных формул целевой функции и функции ограничения. Как правило, вы можете нажать кнопку «Вперед». Однако, если проблема оптимизации не может быть решена с помощью форвардных производных, вы можете нажать центральную кнопку. Использование дифференциалов вблизи центра цели часто требует больше вычислений для решения, но может быть лучше для задач с сильными ограничениями, таких как цены на авиабилеты.

Поиск

Кнопки параметров поиска — Newton и Conjugate — позволяют выбрать алгоритм, который Excel использует для поиска оптимального решения. Если на вашем персональном компьютере много свободной памяти, нажмите кнопку Newton, чтобы уменьшить количество итераций вычислений (хотя и за счет использования большего объема памяти). Если на вашем персональном компьютере нет дополнительной памяти, нажмите кнопку Conjugate, чтобы выделить больше времени, чтобы ваш компьютер не использовал столько памяти.

Сохранить модель и загрузить модель

Кнопки Сохранить модель и Загрузить модель позволяют сохранить описание модели оптимизации. Чтобы сохранить модель, например уравнения, которые вы настроили для сценария с домами, участками, оборотным капиталом и бульдозерами, нажмите кнопку «Сохранить модель», а затем укажите пустой диапазон рабочего листа, который Excel должен использовать для сохранения модели. Чтобы загрузить модель, нажмите кнопку «Загрузить модель», а затем укажите диапазон рабочего листа, содержащего модель.

Понимание сообщений об ошибках решателя

Для большинства простых задач оптимизации Excel довольно быстро находит решение вашей проблемы и отображает диалоговое окно «Результаты решения». В более сложных задачах — к сожалению, тех, с которыми вы, вероятно, столкнетесь в реальной жизни — Excel может столкнуться с трудностями. В этих случаях может отображаться одно из сообщений об ошибках, описанных в следующих параграфах.

Решатель сошелся с текущим решением

Это сообщение означает, что хотя Excel нашел то, что кажется решением, может быть лучшее решение. Чтобы заставить Excel искать лучшее решение, уменьшите значение параметра «Сходимость» в диалоговом окне «Параметры решателя», как описано в предыдущем разделе «Настройка операций решателя».

Решатель не может улучшить текущее решение

Это сообщение указывает на то, что Excel рассчитал грубое подходящее решение, но может существовать лучшее решение. Чтобы заставить Excel искать лучшее решение, установите для параметра «Точность» большее значение в диалоговом окне «Параметры решения». Опять же, в предыдущем разделе объясняется, как это сделать.

Остановка выбрана при достижении максимального срока

Это сообщение указывает на то, что время Excel истекло. Вы можете попытаться повторить решение, используя параметр alargerMaxTime. Чтобы указать значение alargerMaxTime, используйте диалоговое окно «Параметры решения».

Остановка выбрана при достижении максимального предела итераций

Это сообщение указывает на то, что Excel исчерпал количество итераций. Вы можете попробовать еще раз попытаться решить решение, используя большее значение параметра «Итерации». Чтобы указать большее значение итераций, используйте диалоговое окно «Параметры решателя».

Значения Set Target Cell не сходятся

Это сообщение указывает на то, что целевая функция продолжает увеличиваться или уменьшаться, несмотря на то, что все ограничения уже выполнены. Другими словами, с каждой итерацией Excel получает лучшее значение целевой функции, но не приближается к окончательному значению целевой функции. Если вы столкнулись с этой ошибкой, просмотрите свою целевую функцию и ограничения, чтобы убедиться, что вы правильно описали проблему оптимизации-моделирования.

Решатель не смог найти возможное решение

Это сообщение, вероятно, указывает на то, что ваша задача оптимизации моделирования не имеет ответа. Кроме того, это сообщение об ошибке может свидетельствовать о том, что вы неправильно описали целевую функцию или, что более вероятно, одно или несколько ограничений. В предыдущей модели, если бы ваш оборотный капитал был ограничен 600 000 долларов, у вас не было бы достаточно денег, чтобы обработать минимально необходимое количество участков и домов. Не было бы приемлемого решения.

Условия для предположения о линейной модели не соблюдены

Это сообщение указывает, что вы установили флажок Предполагать линейную модель, который отображается в диалоговом окне Параметры решателя, но Excel после просмотра результатов расчета заключает, что ваша модель не является линейной. Если вы видите это сообщение, сначала откройте диалоговое окно Solver Options и установите флажок Use Automatic Scaling. Затем снова попытайтесь решить свою оптимизационную модель. Если вы получите сообщение об ошибке во второй раз, снова отобразите окно «Параметры решателя», но на этот раз снимите флажок «Предполагать линейную модель». Затем попытайтесь решить свою проблему еще раз.

Решатель обнаружил значение ошибки в целевой ячейке или ячейке ограничения

Это сообщение указывает, что одна из ваших формул приводит к значению ошибки или что вы неправильно указали целочисленное или двоичное ограничение. Чтобы решить эту проблему с решателем, вам нужно исправить неправильную формулу.

Недостаточно памяти для решения проблемы

Это сообщение, как и следовало ожидать, указывает на то, что Excel не хватает памяти для успешного запуска Solver.