Рассмотрим последовательность решения оптимизационной задачи на примере задачи получении оптимальной смеси, рассмотренной в предыдущей лекции.

Основные этапы решения оптимизационных задач в Excel

Лекция 5. Решение оптимизационных задач в электронной таблице Excel

Цель: Рассмотреть систематизированные основы знаний по использования электронной таблицы Excel для решение оптимизационных задач.

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

1. Разработка математической модели.

2. Перенос модель в электронную таблицу.

3. Решение оптимизационной задач с помощь программы надстройки ПОИСК РЕШЕНИЯ

Этап 1. Разработка математической модели.

Пример 1(задача о смесях). Стандартом предусмотрено, что октановое число автомобильного бензина А-76 должно быть не ниже 76, а содержание серы в нем – не более 0,3%. Для изготовления такого бензина на заводе используется смесь из четырех компонентов. Данные о ресурсах смешиваемых компонентов, их себестоимости и их октановом числе, а также о содержании серы приведены в таблице

Требуется определить, сколько тонн каждого компонента следует использовать для получения 1000 т автомобильного бензина А-76, чтобы его себестоимость была минимальной.

Решение. Для решения этой задачи сформулируем ее экономико-математическую модель, т.е. сформулируем задачу математически. Введем необходимые обозначения: пусть x_j (j = 1,2,3,4) – количество в смеси компонента с номером j. С учетом этих обозначений имеем задачу (критерий оптимальности – «минимум себестоимости»):

min f() = 40x₁ + 45x2 + 60x₃ + 90x₄,

x₁ + х₂ + х₃ + x₄ = 1000, (1)

68x₁ + 72x₂ + 80x₃ + 90x₄ ³ 76 • 1000, (2)

0,35x₁ + 0,35x₂ + 0,3x₃ + 0,2x₄ ≤ 0,3 • 1000, (3)

x₁ £ 700,

x₂ £ 600,

x₃ £ 500,

x₄ £ 300,

x_j ³0, j = 1,2,3,4.

Функциональное ограничение (1) отражает необходимость получения заданного количества смеси (1 000 т), (2) и (3) – ограничения по октановому числу и содержанию серы в смеси, остальные – ограничения на имеющиеся объемы соответствующих ресурсов (компонентов). Прямые ограничения очевидны, но принципиально важны для выбора метода решения.

Полученная математическая задача – задача линейного программирования.

Этап 2. Перенос модель в электронную таблицу.

Такой перенос может быть реализован в виде последовательности следующих 5 шагов.

1. Продумайте организацию и введите исходные данные модели (коэффициенты целевой функции и ограничений, правые части ограничений) в ЭТ, снабдив их понятными названиями.

2. Зарезервируйте отдельную ячейку для каждой независимой переменной алгебраической модели.

Для рассматриваемой задачи предлагается следующий вид таблицы данных:

3. Заполните таблицу исходными данными (значениями параметров и знаками ограничений):

4. В одной из ячеек создайте формулу, соответствующую целевой функции алгебраической модели.

5. Выберите ячейки и создайте в них формулы, соответствующие левой части каждого ограничения.