Создание электронной модели

Чтобы привлечь компьютер к решению этой задачи необходимо ввести исходные данные на лист Excel. Это можно сделать, например, так, как изображено на рис.1. Сначала заносим в таблицу неизменяемые данные: цены продукции, удельные расходы труда на выпуск каждого вида столов и их наличные запасы в каждом цехе.

Рис. 1. Условие задание в экранной форме

Затем заполняем изменяемые ячейки B3:D3, в которых расположены компоненты плана . На этапе ввода исходных данных сюда заносятся любые числа, например, единицы. После решения в этих ячейках будут находиться оптимальные значения переменных. Целевая функция (суммарный доход) и левые части ограничений (затраты труда в каждом цехе) подсчитываются в соответствие с составленной моделью по формуле (1) и левым частям (2).

Содержимое соответствующих ячеек приведено ниже

< Е4 > = СУММПРОИЗВ(B4:D4;B$3:D$3) – вычисление суммарного дохода.

< E8 > = СУММПРОИЗВ(B8:D8;B$3:D$3),

< E9 > = СУММПРОИЗВ(B9:D9;B$3:D$3),

< E10 > = СУММПРОИЗВ(B10:D10;B$3:D$3) – вычисление затрат труда в каждом цехе на выпуск всей продукции.

Функция СУММПРОИЗВ (массив1, массив2) относится к разряду математических функций. Она вычисляет произведения соответствующих элементов массивов, после чего суммирует полученные произведения. Вставку функции можно осуществить через одноименный пункт меню Вставка, или с помощью кнопки . Если ссылку на диапазон изменяемых ячеек B3:D3 в формуле для < E4 > сделать абсолютной B$3:D$3, для чего можно воспользоваться клавишей F4, то ячейки E8:E10 легко заполнить с помощью операции копирования ячейки E4.

Ячейка Е4, содержащая формулу для вычисления целевой функции является целевой ячейкой.

3. Использование надстройки «Поиск решения»

После активации строки Поиск решения в меню Cервис, мы увидим следующее окно:

Рис. 2. Экранная форма «Поиска решения»

В поле Установить целевую ячейку надо ввести адрес (или имя) ячейки, в которой находится формула для вычисления целевой функции, в нашей задаче это адрес E4. Если перед вызовом надстройки целевую ячейку сделать активной, щелкнув по ней мышкой, то требуемый адрес появится автоматически. Имеется возможность максимизировать, минимизировать значение целевой ячейки, или установить значение в целевой ячейке равным некоторому числу путем установки переключателя в соответствующее положение.

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

В поле Ограничения необходимо указать условия, накладываемые на переменные (изменяемые ячейки). Для ввода ограничений используются кнопки Добавить и Изменить, которые вызывают окно Добавление ограничения (рис.3), служащее для установки между ячейками соотношений типа , или , а также наложения ограничений на целочисленность и бинарность (возможность для переменных принимать только два значения 0 или 1).

Рис. 3. Ввод ограничений

Поле Ссылка на ячейку служит для указания ячейки или диапазона, на значения которых необходимо наложить ограничение. Внесем туда диапазон ячеек E8:E10, содержащий левые части ограничений, выделив их в листе мышкой.

Поле Ограничение служит для задания условия, которое накладывается на значения ячейки или диапазона, указанного в поле Ссылка на ячейку. Выбор необходимого условного оператора (, , , цел или двоич) производится из выпадающего списка в центре окна. В правое поле окна вводится ограничение - число, формула, ссылка на ячейку или диапазон. В нашем примере укажем диапазон F8:F10, содержащий правые части ограничений (2) – наличные запасы трудоресурсов.

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

Завершается ввод ограничений нажатием кнопки OK и возвратом в главное окно Поиск решения.

В этом окне также присутствуют кнопки Выполнить и Параметры. Первая служит для запуска алгоритма поиска оптимального решения. Вторая – предлагает настроить поиск более конкретно. Можно изменять условия и варианты поиска решения для линейных и нелинейных задач, а также загружать и сохранять оптимизируемые модели. Значения и состояния элементов управления, используемые по умолчанию, подходят для решения большинства задач.

Нажатие кнопки Параметры выводит на экран одноименное окно:

Рис. 4. Параметры «Поиска решений»

Опишем элементы окна параметров поиска решения: