3. 
   В поле «Управление» выбрать значение «Надстройки Excel», затем кнопку «Перейти».
   
4. 
   В поле «Доступные надстройки» установить флажок рядом с пунктом «Поиск решения» (рис. 2.13) и нажать кнопку ОК.
   

После выполнения этих действий команда Поиск решения будет доступной в группе команд Анализ вкладки Данные (рис. 2.10).



Рис. 2.13. Окно «Надстройки»

Параметры инструмента Поиск решения

Как отмечалось ранее, доступ к инструменту «Поиск решения» осуществляется с помощью команды «Данные – Анализ - Поиск решения» (рис. 2.14).

Данная команда отображает окно диалога «Параметры поиска решения». Перед использованием рассматриваемого инструмента на листе электронной таблицы должны быть сформированы целевая функция, область изменяемых ячеек (неизвестные), значения которых будут найдены в процессе решения. Решение (изменяемые ячейки) должно находиться в определенных пределах или удовлетворять определенным ограничениям.

Параметры задачи ограничиваются такими предельными показателями:

• 
  количество неизвестных – 200;
  
• 
  количество формульных ограничений на неизвестные – 100;
  
• 
  количество предельных условий на неизвестные – 400.
  

Рис. 2.14. Окно «параметры поиска решения»

В окне диалога «Параметры поиска решения» в поле «Оптимизировать целевую функцию» указывается адрес ячейки с целевой функцией. Целевая функция зависит от изменяемых ячеек и связана с ними некоторой формулой. Оптимизируется значение целевой функции до максимума, минимума, или некоторого определенного значения.

В поле «Изменяя ячейки переменных» указывается адрес блока ячеек, которые и будут решением.

В область «В соответствии с ограничениями» вводятся ограничения на решение. Кнопки «Добавить, Изменить, Удалить» управляют ограничениями, их действия интуитивно понятны.

Если в

---

пределах одного рабочего листа Excel необходимо рассмотреть несколько моделей оптимизации (например, найти максимум и минимум одной функции или максимальные значения нескольких функций), то удобнее сохранить эти модели, используя кнопку «Загрузить/сохранить». Диапазон для сохраняемой модели содержит информацию о целевой ячейке, об изменяемых ячейках, о каждом из ограничений и все значения окна диалога «Параметры». Выбор сохраненной ранее модели для решения конкретной оптимизационной задачи осуществляется также с помощью кнопки «Загрузить/сохранить».

Флажок в поле «Сделать переменные без ограничений неотрицательными» позволяет не вводить дополнительно ограничения на изменяемые ячейки, если их значения неотрицательны.

Поиск решения в зависимости от типа решаемых задач, позволяет использовать методы:

• 
  Симплексный метод.
  
• 
  Метод ОПГ (обобщенного приведенного градиента).
  
• 
  Эволюционный поиск решения.
  

Метод решения выбирается из раскрывающегося списка «Выберите метод решения» рассматриваемого окна диалога.

Кнопка «Найти решение» запускает процесс решения задачи.

Иногда, в результате выполнения процедуры поиска решения, само решение не находится, даже если известно, что решение существует. Часто эту проблему удается решить, изменив некоторые параметры и повторно запустив «Поиск решения». Указанные параметры устанавливаются в диалоговом окне «Параметры» (рис. 2.15), которое отобразится, если в окне диалога «Параметры поиска решения» выбрать кнопку «Параметры».



Рис. 2.15. Окно «Параметры»

Ниже описаны основные параметры вкладки.

Точность ограничения. Указывает насколько точно выполняются ограничения. Задача может быть решена быстрее, если задать меньшую точность.

Использовать автоматическое масштабирование. Служит для автоматической нормализации входных и выходных значений, значительно различающихся по величине.

---

Показывать результаты итераций. Если этот параметр активизирован, то после выполнения очередной итерации решение приостанавливается, и отображаются найденные результаты.

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

Максимальное время. Предоставляет возможность ограничить максимальное время решения задачи (в секундах). Если появится сообщение, что время на решение задачи истекло, то его можно добавить.

Число итераций. Используется для ввода максимального числа промежуточных решений, допустимых при поиске решения.

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

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

Пример решения задач линейного программирования.

Задача определения оптимального ассортиментапродукции

Предприятие изготавливает четыре вида продукции – A, B, C и D. Для производства продукции используются ресурсы – трудовые, материальные, финансовые. Максимальный запас ресурсов на производстве 800, 2000, 2900 соответственно. Расход ресурсов на единицу производства продукции A, B, C и D и предельно допустимые значения выпуска каждого вида даны в табл. 2.6.

Таблица 2.6. Условие задачи 1

Ресурсы	Расход ресурса на единицу продукции					Запас ресурса
	A	B	C	D
Трудовые	8	3	4	4	800
Материальные	7	8	12	10	2000
Финансовые	15	14	13	14	2900
Нижняя граница выпуска	12		3
Верхняя граница выпуска	30	25

---

Прибыль от реализации единицы продукции равны: 8 д. е. – для A, 10 д. е. – для B, 7 д. е. – для C, 8 д. е. – для D.

Какой объем продукции каждого вида должно производить предприятие, чтобы прибыль от реализации продукции была максимальной?

Решение.Составим математическую модель для решения поставленной задачи.

Обозначим переменные:

x₁ – объем произведенной продукции вида А; x₂ – объем произведенной продукции вида B; x₃ – объем произведенной продукции вида C; x₄ – объем произведенной продукции вида D;

Поскольку производство продукции ограничено имеющимися в распоряжении предприятия ресурсами и спросом на данную продукцию, а также учитывая, что объем изготовляемой продукции не может быть отрицательным, должны выполняться следующие неравенства:



Прибыль от реализации продукции составит:



Cреди всех неотрицательных решений системы линейных неравенств требуется найти такое, при котором функция F принимает максимальное значение F_max.

Рассматриваемая задача относится к разряду типовых задач оптимизации производственной программы предприятия. В качестве критериев оптимальности в этих задачах могут быть также использованы прибыль, себестоимость, номенклатура производимой продукции, затраты станочного времени и др.

Создадим на рабочем листе таблицу для ввода исходных данных (рис. 2.16). Заливкой выделены ячейки для ввода формул и вывода результата.



Рис. 2.16. Экранная форма задачи

Заполним таблицу.

Блок ячеек В3:Е3 содержит оптимальное решение, значение этих ячеек будет получено в результате решения задачи.

Блок ячеек В4:Е4 содержит значения прибыли

---

от реализации продукции. В ячейках В9: Е13 отображен расход ресурсов на единицу производства продукции A, B, C и D и предельно допустимые значения выпуска каждого вида.

Для вычисления целевой функции в ячейке F4 используем функцию =СУММПРОИЗВ($B$3:$E$3;B4:E4) (рис. 2.17).



Рис. 2.17. Функция СУММПРОИЗВ

В ячейки F9:F11 введены формулы для расчета ограничений по ресурсам. На рис. 2.18 представлена таблица с исходными данными, целевой функцией, ограничениями и граничными условиями.



Рис. 2.18. Исходные данные с формулами

На вкладке «Данные» в группе «Анализ» выберем команду Поиск решения.

На экране отобразится диалоговое окно «Параметры поиска решения», в котором установим следующие параметры (рис. 2.19):



Рис. 2.19. Окно «Параметры поиска решения»

• 
  в поле «Оптимизировать целевую функцию» указываем адрес ячейки со значением целевой функции – F4;
  
• 
  выбираем нахождение максимума целевой функции;
  
• 
  в поле «Изменяя ячейки переменных» указываем адреса ячеек со значениями искомых переменных B3:Е3;
  
• 
  в области «В соответствии с ограничениями» с помощью кнопки «Добавить» размещаем все ограничения задачи (добавление ограничений будет рассмотрено ниже);
  
• 
  установим флажок в поле «Сделать переменные без ограничений неотрицательными»;
  
• 
  в списке «Выберите метод решения» указываем «Поиск решения линейных задач симплекс-методом»;
  
• 
  нажимаем кнопку «Найти решение»
  

Остановимся подробно на добавлении ограничений в область В соответствии с ограничениями.

Все ограничения указаны в математической модели задачи. Для добавления ограничения необходимо выбрать кнопку «Добавить». Отобразится окно диалога «Добавление ограничений» (Рис. 2.20).

---

Смотрите также файлы

• 3 Социальнопсихологический тренинг общения.doc

• Приоритетные направления начального общего образования в рамках реализации Федерального государственного образовательного стандарта второго поколения.doc

• Едеральное агентство по техническому регулированию и метрологии н ациональный.doc

• Практическая работа Использование библиотеки цифрового образовательного контента в учебной деятельности.docx

• Минобрнауки россии санктПетербургский государственный.docx