Поиск решения и Подбор параметра

 

В данном подразделе рассмотрим полезные вычислительные возможности LO Calc, доступ к которым можно получить из пункта «Сервис» главного меню.

Поиск решения

В LO Calc имеется модуль электронной таблицы (ЭТ), называемый «Решатель» (или «Solver»), который используется для решения задач, относящихся к классу «линейной оптимизации».

В качестве примера рассмотрим задачу из учебного пособия «Математическое программирование», авторы Э.Ф.Брыжина и Э.А.Худобина, издательство СПбГИЭА, 1997 год.

Пусть имеется три вида сырья в количествах 45 ед., 19 ед. и 10 ед. Из этого сырья нужно изготовить продукцию двух видов. Задан расход сырья каждого вида на производство единицы каждого вида продукции и прибыть от единицы продукции (см. таблицу 12). Требуется найти такой вариант выпуска каждого вида продукции, при котором прибыль будет наибольшей.

 

Таблица 1

Исходные данные для задачи оптимизации

Продукция	Продукт 1	Продукт 2	Запасы сырья
Сырье
ПРИБЫЛЬ

Пусть x₁ означает количество единиц продукции первого вида, а x₂ – второго вида. Тогда на выпуск этой продукции будет израсходовано 5x₁ + 9x₂ единиц сырья первого вида, 3x₁ + 3x₂ единиц сырья второго вида и 2x₁ + x₂ – третьего. Суммарная прибыль составит 5x₁ + 6x₂ денежных единиц. Поскольку нельзя израсходовать сырья больше, чем имеется, а суммарная прибыль зависит от количества выпущенной продукции, то получим следующую математическую модель данной задачи.

Для решения задачи в ЭТ прежде всего нужно определиться с расположением исходных данных и составить необходимые формулы. Пусть таблица будет скомпонована в соответствии с рисунком 1.

Рис. 1. Исходные данные для линейной оптимизации

Количества продукции 1-го и 2-го вида (которые предстоит вычислить) располагаются в ячейках A3 и B3 соответственно. В C3 – формула для вычисления прибыли, в B6:B8 – формулы для левых частей ограничений (см. математическую модель). Соответствующие формулы, реализующие математическую модель, показаны на сером фоне справа от ячеек, в которые они должны быть записаны. Когда решение будет найдено, нули в ячейках ЭТ и пустые ячейки для параметров модели будут заменены какими-то другими значениями.

Вызов диалога настройки поиска решения обеспечивается командой главного меню «Сервис/Решатель...» (рисунок 2).

Рис. 2. Диалог настройки поиска решения

В диалоге настройки поиска решения несколько полей ввода, и в них потребуется устанавливать адреса ячеек. Для поля «Целевая ячейка» требуется указать адрес ячейки, содержащей формулу для расчёта прибыли f(x) (в рассматриваемом примере – ячейка C3). Адрес можно ввести прямо в поле ввода, а можно использовать «метод указания», нажав кнопку «Уменьшить» справа от поля ввода и щёлкнув ЛКМ по нужной ячейке (для восстановления вида диалога требуется повторный щелчок ЛКМ по той же кнопке).

Поскольку требуется найти максимум прибыли, переключатель «Результат» должен быть установлен в позицию «Максимум».

В поле ввода «Изменяя ячейки» требуется указать ячейки, в которых должны вычисляться параметры (в нашем случае x₁ и x₂, то есть диапазон A3:B3). Для заполнения этого поля также целесообразно использовать «метод указания», выделяя требуемый диапазон ячеек.

Далее, нужно добавить ограничения. Для этого в блоке «Ограничительные условия» требуется определить левую и правую части, а также вид (знак) ограничения.

В левой части каждого ограничения должен быть адрес одной ячейки (в которой записана формула для ограничения), а в правой части может быть как адрес ячейки, так и число (значение для ограничения), то и требуется в рассматриваемом примере. В нашем случае для всех ограничений нужно устанавливать знак «не меньше» (<=), хотя возможны варианты «не больше» (>=) и «равно» (=). Для ввода адресов ячеек в левую часть ограничения тоже можно использовать «метод указания».

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

Рис. 3. Дополнительные параметры при поиске решения

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

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

Рис. 4. Информация о найденном решении

Результаты поиска решения показаны на рисунке 5.

Рис. 5. Результаты решения задачи линейной оптимизации

Нужно отметить, что результаты полностью соответствуют приведённым в первоисточнике значениям 3 и 10/3.