Пакет анализа в MS Excel

Второе задание заключается  в решении задачи на поиск оптимального решения с использованием Ms Excel. Допускается  использование другого программного пакета, реализующего аналогичные задачи. В контрольной работе необходимо привести:

• формулировку задачи;
• подготовленные исходные данные (для Excel введенные Вами таблицы, на основании которых будет произведено решение);
• краткое описание процесса подготовки данных и решения,
• полученный результат

 

2. Обувная мастерская располагает  листами кожи двух типов, из  которых выкраивают заготовки А и В. Из листа первого типа можно выкроить 3 заготовки А и 5 заготовок В, отходы составляют при этом 40 кв. см. Из листа кожи второго типа - 7 заготовок А и 2 заготовки В, отходы - 50 кв. см. Сколько листов каждого типа надо раскроить, чтобы отходы были минимальными, если необходимо изготовить не менее 210 заготовок А и не менее 250 заготовок В.

 

Формулировка задачи:

Пусть х₁ – количество использованных листов первого типа, х₂ – количество использованных листов второго типа.

Целевая функция:

Условия (ограничения):

Подготовленные исходные данные:

1. Создадим книгу MS Excel.
2. Добавим в книгу лист с названием «Обувь».
3. На листе «Обувь» создадим таблицу с исходными данными (рис.1).

Рис.1. Исходные данные для решения задачи.

 

Краткое описание процесса подготовки данных и решения:

1. В ячейки В5 и С5, введем формулы для расчета количества заготовок:

В5 =B2*E2+B3*E3

С5 =C2*E2+C3*E3

В ячейку D6 введем формулу для расчета совокупного размера отходов:

D6 =D2*E2+D3*E3

2. Далее воспользуемся встроенными возможностями MS Excel: надстройкой «Поиск решения». Для этого перейдем в меню «Сервис», выберем пункт «Надстройки», в появившемся окне поставим галочку напротив надстройки «Поиск решения».
3. После установки компонента перейдем в меню «Сервис» и выберем пункт «Поиск решения». В появившемся диалоговом окне установим целевую ячейку – совокупный размер отходов: $D$6, выберем ячейки, которые будет менять – количество листов кожи первого и второго типа: $Е$2:$Е$3 выберем ограничения по количеству заготовок А и В, установленному в ячейках B4 и С4 в соответствии с условиями задания (рис.2).

Рис.2. Задание параметров поиска для работы надстройки «Поиск решения»

 

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

Рис.3. Задание параметров поиска для работы надстройки «Поиск решения»

 

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

 

 

Рис.4. Результаты применения надстройки «Поиск решения»

 

Полученный результат:

В итоге получаем таблицу с рассчитанным количеством использованных листов первого и второго типа (47 листов первого типа кожи и 10 листов второго типа кожи), а также с рассчитанным количеством заготовок А и В (А – 211 шт., В – 255 шт.). Минимальный совокупный размер отходов составит 2380 кв.см. кожи.

Рис.5. Результаты решения задачи в MS Excel.