2015-10-22
2019
Расход микросхем задается следующей таблицей:
1. Запустите программу Excel (ПУСК - Программы - Microsoft Office - Excel 2007), откроется рабочая книга. Сохраните, пока еще пустую книгу, на рабочем диске под именем Поиск решения.
2. Переименуйте Лист1. Для этого щелкните правой клавишей мыши по ярлычку Лист1, выберите команду Переименовать и присвойте ему имя Организация производства.
3. Заполните таблицу по образцу. В названиях столбцов введите перенос текста по словам, выбрав команду ГЛАВНАЯ - группа Выравнивание - Перенос текста.
В ячейки В3, В4 и В5 занесите дневной запас комплектующих - числа 400, 500 и 5 00 соответственно.
4. В ячейки D2, Е2, F2 занесите нули, в дальнейшем значения этих ячеек будут подобраны автоматически.
5. В ячейках диапазона D3:F5 разместите таблицу расхода комплектующих (какое количество микросхем используется для каждого прибора).
6. В ячейках C3:C5 введите формулы для расчетарасхода комплектующих по типам микросхем. В ячейке C3 формула будет иметь вид =$D$2*D3+$E$2*E3+$F$2*F3 (количество приборов умножается на количество микросхем, которое используется для производства этоговидаприбора ). Остальные формулы можно получить методом автозаполнения, то есть протягиванием маркера заполнения по ячейкам (обратите внимание на использование абсолютных и относительных ссылок).
|
|
|
7 В ячейку G2 занесите формулу, вычисляющую общее число произведенных приборов: для этого выделите диапазон D2:F2 и щелкните на кнопке автосуммирования å ГЛАВНАЯ - группа Редактирование - Сумма.
8. Поиск решения. Выберите команду ДАННЫЕ - группа Анализ - Поиск решения. (SolverParameters) - откроется диалоговое окно.
9. В поле Установить целевую ячейку ( Set Target Cell) укажите ячейку, содержащую оптимизируемое значение, которое мы ищем (G2). Установите переключатель Равной максимальному значению (Equal To Мах), так как требуется максимальный объем производства.
10. В поле Изменяя ячейки (By Changing Cells) задайте диапазон (протягиванием мышки) подбираемых параметров – D2:F2.
11. Чтобы определить набор ограничений, щелкните на кнопке Добавить (Add). В диалоговом окне Добавление ограничения (Add Constraint) в поле Ссылка на ячейку (Cell Reference) укажите диапазон C3:C5. В качестве условия задайте <=. В поле Ограничение ( Constraint) задайте диапазон B3:B5. Это условие указывает, что дневной расход комплектующих не должен превосходить запасов. Щелкните на кнопке ОК.
12. Снова щелкните на кнопке Добавить (Add). В поле Ссылка на ячейку ( Cell Reference) укажите диапазон D2:F2. В качестве условия задайте >=. В поле Ограничение ( Constraint) задайте число 0. Это условие указывает, что число производимых приборов неотрицательно. Щелкните на кнопке ОК.
|
|
|
13. Снова щелкните на кнопке Добавить (Add). В поле Ссылка на ячейку ( Cell Reference) укажите диапазон D2:F2. В качестве условия выберите пункт цел (int). Это условие не позволяет производить доли приборов. Щелкните на кнопке ОК.
14. Щелкните на кнопке Выполнить (Solve). По завершении оптимизации откроется диалоговое окно Результаты поиска решения ( Solver Results).
15. Установите переключатель Сохранить найденное решение ( Keep Solver Solution), после чего щелкните на кнопке ОК.
- Проанализируйте полученное решение. Оказывается, чтобы практически полностью реализовать дневной запас микросхем нужно отказаться от выпуска Прибора 2.
- Проверьте оптимальность, экспериментируя со значениями ячеек D2:F2. Вы увидите, что расход микросхем каждый день будет неоптимальным. Чтобы восстановить оптимальные значения, можно в любой момент повторить операцию поиска решения (или просто отменить последние операции).
17. Сохраните рабочую книгу Поиск решения.xlsx.
Мы узнали, как использовать программу Excel для решения сложных задач оптимизации. Мы научились формулировать условия задачи табличным образом, формировать ограничения, которым должно удовлетворять решение, и производить поиск оптимального набора переменных. Мы также выяснили, что даже для несложной задачи оптимизации найти оптимальное решение подбором практически невозможно.
_________________________________
