Поиск решения MS EXCEL. Экстремум функции с несколькими переменными. Граничные условия заданы уравнениями

Пусть дана функция с несколькими переменными F(x1, x2, ...)=a1*x1+a2*x2+... Также даны граничные условия в виде b1*x1+b2*x2+...<=c (несколько условий). Нужно найти экстремум функции F (минимум или максимум). Это классическая задача для Поиска решения MS EXCEL, кроме того это линейная модель. Сделаем удобную форму для таких задач и покажем как настроить Поиск решения.

Задача

Пусть дана явная функция с 4 переменными:

Также даны несколько (семь) граничных условий (англ: restrictions, constraints):

Требуется найти максимум функции F.

Создание модели

Решим задачу с помощью инструмента MS EXCEL Поиск решения , хотя можно ее решить и другим способом, например аналитически.

На рисунке ниже приведена модель, созданная для решения задачи (см. файл примера ).

Переменные (выделено зеленым) . В качестве переменных модели, очевидно, выступают x1, x2, x3, x4. Эта задача хороша тем, что переменные задаются однозначно, не требуется осмысливать житейскую задачу, например как с оптимизацией затрат . Хотя математически - это эквивалентные задачи, только количество переменных разное.

После запуска Поиск решения будет методично (последовательно) по своему алгоритму подставлять в зеленые ячейки числовые значения и вычислять функцию F (красная ячейка).

Ограничения (выделено серым) . Ограничения модели - это ограничения на область изменения переменных. Они могут задаваться как простыми выражениями для одной переменной, например х1>=0, так и для некой комбинации переменных 5*x1+4*x2-x3-2*x4<=-3. В первом случае х1>=0 ограничения можно ввести прямо в окне Поиска решения (будет показано ниже), для более сложных зависимостей удобно подготовить вспомогательную таблицу (С26:Е29).

Составить модель, особенно первую, непросто. Может помочь такой подход: считать, что переменные (зеленые ячейки) уже содержат некие значения, пусть даже не оптимальные. Так легче составлять огграничения. В нашем случае ограниечение 5*x1+4*x2-x3-2*x4 можно записать с помощью формулы = СУММПРОИЗВ($D$19:$D$22;C26:C29) . В диапазоне D19:D22 содержатся коэффициенты 5; 4; -1; -2. Кроме того, если значения переменных заданы, то и значение целевой функции также автоматически рассчитано (тоже не оптимальное пока, до запуска Поиска решения).

Целевая функция (выделено красным) . Целевая функция - это то, что требуется оптимизировать, т.е. F. Формула для ее вычисления задана в явном виде - не нужно догадываться из условий обычной задачи как ее подсчитать. Это не всегда очевидно (см., например, статью про пропускную способность трубопровода ).

Ниже приведено окно Поиска решения с заполненными полями: целевая функция, переменные и ограничения.

После запуска Поиска решения ответ будет вычислен за доли секунды: F=3.