Информатика
Решение задач оптимизации в Microsoft Excel
Задача. Завод производит электронные приборы трех видов (прибор А, прибор В и прибор С), используя при сборке микросхемы трех типов (тип 1, тип 2 и тип 3). Расход микросхем задается следующей таблицей:
Прибор А | Прибор В | Прибор С | |
Тип 1 | 2 | 5 | 1 |
Тип 2 | 2 | 0 | 4 |
ТипЗ | 2 | 1 | 1 |
Стоимость изготовленных приборов одинакова.
Ежедневно на склад завода поступает 400 микросхем типа 1 и по 500 микросхем типов 2 и 3. Каково оптимальное соотношение дневного производства приборов различного типа, если производственные мощности завода позволяют использовать запас поступивших микросхем полностью?
- Запустите программу Excel (Пуск ► Программы ► Microsoft Excel) и откройте рабочую книгу book.xls, созданную ранее.
- Создайте новый рабочий лист (Вставка ► Лист), дважды щелкните на егоярлычке и присвойте ему имя Организация производства.
- В ячейки А2, A3 и А4 занесите дневной запас комплектующих — числа 400, 500 и 500 соответственно.
- В ячейки С1, D1 и Е1 занесите нули — в дальнейшем значения этих ячеек будут подобраны автоматически.
- В ячейках диапазона С2:Е4 разместите таблицу расхода комплектующих.
- В ячейках В2:В4 нужно указать формулы для расчета расхода комплектующих
по типам. В ячейке В2 формула будет иметь вид =$C$1*C2+$D$1*D2+$E$1*E2, а остальные формулы можно получить методом автозаполнения (обратите внимание на использование абсолютных и относительных ссылок). - В ячейку F1 занесите формулу, вычисляющую общее число произведенных
приборов: для этого выделите диапазон С1 :Е1 и щелкните на кнопке Автосумма на стандартной панели инструментов. - Дайте команду Сервис ► Solver (Поиск решения) — откроется диалоговое окно Solver Parameters (Поиск решения).
- В поле Set Target Cell (Установить целевую) укажите ячейку, содержащую оптимизируемое значение (F1). Установите переключатель Equal To Мах (Равной максимальному значению) (требуется максимальный объем производства).
- В поле By Changing Cells (Изменяя ячейки) задайте диапазон подбираемых параметров — С1:Е1.
- Чтобы определить набор ограничений, щелкните на кнопке Add (Добавить). В диалоговом окне Add Constraint (Добавление ограничения) в поле Cell Reference (Ссылка на ячейку) укажите диапазон В2:В4. В качестве условия задайте <=. В поле Constraint (Ограничение) задайте диапазон А2:А4. Это условие указывает, что дневной расход комплектующих не должен превосходить запасов. Щелкните на кнопке ОК.
- Снова щелкните на кнопке Add (Добавить). В поле Cell Reference (Ссылка наячейку) укажите диапазон С1:Е1. В качестве условия задайте >=. В поле Constraint (Ограничение) задайте число 0. Это условие указывает, что число производимых приборов неотрицательно. Щелкните на кнопке ОК.
- Снова щелкните на кнопке Add (Добавить). Cell Reference (Ссылка на ячейку)укажите диапазон С1 :Е1. В качестве условия выберите пункт int (цел). Это условие не позволяет производить доли приборов. Щелкните на кнопке ОК.
- Щелкните на кнопке Solve (Выполнить). По завершении оптимизации откроется диалоговое окно Solver Results (Результаты поиска решения).
- Установите переключатель Keep Solver Solution (Сохранить найденное решение), после чего щелкните на кнопке ОК.
- Проанализируйте полученное решение. Кажется ли оно очевидным? Проверьте его оптимальность, экспериментируя со значениями ячеек С1:Е1. Чтобы восстановить оптимальные значения, можно в любой момент повторить операцию поиска решения.
- Сохраните рабочую книгу book.xls.