Упражнение 7.40

Добавив в рабочую книгу Свод рабочий лист с именем Поиск решения, введите на него три таблицы (рис. 7.49) с исходными данными и решите следующую аналитическую задачу.

Рис. 7.49. Табличная модель задачи производства продукции

Условие задачи. На предприятии из определенной номенклатуры модулей (МОД1-МОД5), запас которых ограничен, выпускается несколько блоков изделия (Блок1 – Блок4). Известны данные о прибыли при производстве каждого блока изделий и возможные объемы выпуска блоков. Необходимо сформировать программу производства каждого типа блоков из имеющегося складского запаса модулей, чтобы была достигнута наибольшая прибыль.

Указания по решению задачи. Табличная модель задачи выбора выпускаемой продукции представлена в виде трех взаимосвязанных таблиц, где в ячейках B5 : B9 хранятся данные о фиксированных значениях складского запаса модулей, а в ячейках диапазона D5 : G9 – значения количества модулей в различных блоках. Количество используемых блоков (ячейки C5 : C9) зависит от объема выпуска блоков изделий, а поэтому расчетная формула в ячейке C5 имеет такой вид :

=$A$18 *D5 + $B$18*E5 + $C18*F5 + $D$18*G5 и далее эта формула копируется в ячейки C6 :C9.

В строке 13 приведена в виде фиксированных чисел прибыль от каждого произведенного (выпущенного) блока, а в ячейке B14 значение прибыли рассчитывается по формуле

= B13 * MAKC (A18 : D18) и далее копируется.

Целевой ячейкой для задачи оптимизации прибыли является ячейка F14, содержащая формулу = СУММ (B14 : E14).

Диапазон изменяемых ячеек - это ячейки A18 : D18, содержащие первоначально произвольно назначенные фиксированные значения, которые и будут подбираться для обеспечения максимальной прибыли.

В качестве ограничений необходимо учесть следующее :

• Объем (количество) произведенных блоков не может быть отрицательной и дробной величиной, т.е. (A18 : D18) >= 0 и (A18 : D18) >= целое

• Количество используемых модулей должно быть меньше складского запаса, т.е. C5 : C9 <= B5 : B9.

В результате введения формул первоначальный набор таблиц принимает вид как на рис. 7.50.

Рис. 7.50. Таблицы с результатами работы формул

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

Задав команду Сервис\Поиск решения, в открывшемся окне в поле Установить целевую ячейку вводим ссылку на ячейку F14, где хотим получить величину максимальной прибыли.

В поле Изменяя ячейки вводим ссылку на ячейки A18 :D18, а затем записываем все значения ограничений (рис.7.51).

Рис.7.51. Окно Поиск решения для задания ограничений в модель

Задав необходимые параметры, нажав кнопку <Параметры> ( рис.7.52),

Рис.7.52. Окно задания параметров поиска решения

а затем кнопку <Выполнить>, мы получаем ответ на поиск решения (рис. 7.53).

Рис.7.53. Таблицы с данными и Окно Результаты поиска решения

Нажатием на кнопку <Сохранить сценарий> можно задать его имя и сохранить. Сценарии можно просмотреть, задав команду Сервис\Сценарии (рис. 7.54).

Рис. 7.54. Окно со структурой сценария.