Раздел 1. Решение задач с помощью Excel. Лабораторная работа 1 (6 часов)

Цель работы. Познакомиться с сутью, областью применения и реализацией задачи СЛП при наличии и отсутствии решения в программном продукте Excel.

Основные сведения.

Задача СЛП используется для описания процесса оптимального планирования. Это задача (2.16) - (2.18) во введении, т. е. задача планирования при использовании интегрального метода.

Пример 1.1. Примем обозначения: j = 1, J – вид выпускаемой продукции; (T) и [T] момент и интервал времени (например, месяц); P_j[T] – определяемый план; a_j,  = 1,  - норма расхода ресурса вида  на единицу продукции вида j; - наличное и резервное количество ресурса вида ; R_j^-[T] и R_j⁺[T] - нижняя и верхняя граница выпуска, величины которых определяются позднее; С_j – прибыль от выпуска единицы продукции. Тогда задача составления оптимального плана имеет вид

R_j^-[T] <= P_j[T] <=R_j⁺[T], j = 1, J (1.1)

_J

a_jP_j[T] <= b_(0) {+ f_(0)} (1.2)

^{j = 1}

_J

G = C_j P_j[T]  max. (1.3)

^{j = 1}

Первые два выражения отражают ограничения, последнее – целевую функцию.

Будем называть эту форму записи свернутой.

Возможны развернутая и векторн-матричная форма этой задачи.

Развернутая форма.

R₁^-[T] <= P₁[T] <=R₁⁺[T],

…

R_J^-[T] <= P_J[T] <=R_J⁺[T],

a₁₁P₁[T] + a₁₂P₂[T] + … + a_1JP_J[T] ≤ b₁(0)

_…

a_1P₁[T] + a_2P₂[T] + … + a_JP_J[T] ≤ b_(0)

C₁P₁[T] + C₂P₂[T] + … + C_JP_J[T]  max.

Векторно-матричная форма.

R^-[T] <= P[T] <=R⁺[T]

AP[T] <= b

CP  max,

где P[T] = (P₁[T] P₂[T] … P_J [T])^T, R[T] = (R₁[T] R₂[T] … R_J[T])^T, b = (b₁(0) b₂(0) … b_(0)) ^T, C = (C₁ C₂ … C_J),

A =	a11	a12	…	a1J
	a21	a22	…	a2J
	…	…	…	…
	a1	a2	…	aJ

ЗАДАНИЕ

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

После завершения работы оформить отчет в электронном виде (см. предисловие).

Литература

Курицкий Б.Я. Поиск оптимальных решений средствами Excel 7.0 в примерах. СПб.: БХВ, 1997.384 с.

Статическое линейное программирование

Часть 1

Предварительные условия. Для решения задачи после открытия Excel необходимо перейти к элементам меню Данные - Поиск решения.

Если нет элемента Поиск решения, то его необходимо создать по такому алгоритму.

1. Щелкните значок Кнопка Microsoft Office, а затем щелкните Параметры Excel.

2. Выберите команду Надстройки, а затем в окне Управление выберите пункт Надстройки Excel.

3. Нажмите кнопку Перейти.

4. В окне Доступные надстройки установите флажок Поиск решения и нажмите кнопку ОК.

Совет.  Если Поиск решения отсутствует в списке поля Доступные надстройки, чтобы найти надстройку, нажмите кнопку Обзор.

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

5. После загрузки надстройки для поиска решения в группе Анализ на вкладке Данные становится виден Поиск решения.

Теперь можно приступать к решению задачи.

Все результаты запомнить, поскольку они будут использованы в последующих работах для контроля результатов в программном продукте MatLab.

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

Пример А.

Целевая функция F = 60 p₁ + 70 p₂ + 120 p₃ + 130 p₄  max (1)

Трудовые p₁ + p₂ + p₃ + p₄  16 (2)

Материалы 6 p₁ + 5 p₂ + 4 p₃ + 3 p₄  110 (3)

Финансы 4 p₁ + 6 p₂ + 10 p₃ + 13 p₄  100 (4)

p = {p_j}, p_j  0, j = 1, 4; (5)

p₁  10; p₃  6. (6)

Этапы решения задачи. Установка поиска решения.

Ввод исходных данных (с формированием интерфейса пользователя).

1. Первоначально создается интерфейс пользователя, показанный на рис. 1.

Рис. 1

2. Затем проводится заполнение интерфейса числовыми данными из примера (рис. 2).

Рис. 2

3. Набрать выражение для целевой функции (1) в ячейке F6, для чего использовать функцию Сумма произведений (СУММПРОЗВ) – рис. 3.

Рис. 3

4. После нажатия кнопки Ок ввести данные о целевой функции (рис. 4).

Рис. 4

Для удобства введения можно нажимать красную стрелку, что приведет к сворачиванию формы (рис. 5), и выделению соответствующих ячеек. Затем следует снова нажать красную стрелку, чтобы вернуться к рис. 4.

Рис. 5

5. Аналогично набираются выражения (2) - (4) в ячейках F9, F10, F11.

6. Открыть диалоговое окно Поиск решений (рис. 6). Установить целевую ячейку $F$6. Определить вид оптимизации (Максимальное или минимальное значение). Ввести адреса искомых переменных (изменяя ячейки) $B$3:$E$3.

Рис. 6

7. Нажать кнопку Добавить. Откроется окно на рис. 7. Ввести ограничения (5), (6) – рис 7.Указать, например, «левую» ячейку $B$3, знак ограничения  и «правую» ячейку $B$4. Нажать кнопку Добавить. При ошибках введения воспользоваться кнопками Изменить и Удалить (рис. 6).

8. Аналогично ввести ограничения (2) – (4): $F$9$H$9, $F$10$H$10, $F$11$H$11.

Рис. 7.

Выполнение задачи. 9. После ввода последнего ограничения нажать кнопку Ок или x. Происходит возврат в окно рис. 6 с указанием ограничений.

В окне рис. 6 нажать кнопку Параметры и откроется окно рис. 8.

Рис. 8

Установить Максимальное время 100 секунд, Предельное число итераций 100, Относительная погрешность 0;000001, Допустимое отклонение – 5 %, Линейная (оценка), Прямые (производные), Ньютона (метод). Нажать кнопку Ок и вернуться к рис. 6. Нажать кнопку Выполнить. Появляется заставка – рис. 9.

Рис. 9

Получится решение – рис. 10.

Рис. 10

Решение показать преподавателю и запомнить для последующих лабораторных работ.