Для решения задач оптимизации

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

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

Допустим требуется определить, в каком количестве надо выпускать продукцию четырех типов Прод 1, Прод 2, Прод 3, Прод 4, для изготовления которой требуются ресурсы трех видов: трудовые, оборудование, сырье. Количество ресурса каждого вида, необходимое для выпуска единицы продукции данного типа (норма расхода), наличие располагаемого ресурса, а также прибыль, получаемая от реализации единицы каждого типа продукции, приведены в таблице:

Ресурс	Прод 1	Прод 2	Прод 3	Прод 4	Наличие
Прибыль
Труд
Оборуд.
Сырье

Математическая модель задачи:

F = 15 Х₁ + 24 Х₂+ 19 Х₃ + 27 Х₄ → MAX

2 X₁ + 2 X₂ + 2 X₃ + 2 X₄ <= 30

12 X₁ + 8 X₂+ 10 X₃ + 6 X₄ <= 200

8 X₁+ 20 X₂ + 12 X₃ + 26 X₄ <= 125

X_j≥ 0; j=1-:-4

где X_j - количество выпускаемой продукции j -го типа;

b_j - количество располагаемого ресурса i -го вида;

a_ij - норма расхода i -го ресурса для выпуска единицы продукции j -го типа;

с_ij - прибыль, получаемая от реализации единицы продукции j -го типа.

Ввод условий задачи в электронную таблицу состоит из следующих основных шагов:

1.Создание формы для ввода условий задачи

2.Ввод исходных данных

3.Ввод зависимостей из математической модели

4.Назначение целевой функции

5.Ввод ограничений и граничных условий.

Форма для ввода условий может иметь следующий вид (табл. 1).

После подготовки формы таблицы необходимо ввести исходные параметры (коэффициенты функции цели и ограничений и соответствующие зависимости) экономико-математической модели (табл. 2)

Для ввода зависимости (формулы) для целевой функции (ЦФ) необходимо выполнить следующее:

выделить ячейку, в которую будет вводиться формула;

с помощью мыши нажать кнопку Вставка функции [fx];

в диалоговом окне вызвать категорию Математические функции;

выделить в окне Функции СУММПРОИЗВ;

нажать [ Далее ], (появляется диалоговое окно);

в массив 1 ввести адреса ячеек, содержащих значения переменных (или с клавиатуры или протаскивая мышь по ячейкам);

в массив 2 ввести адреса коэффициентов функции цели;

нажать [ Готово ].

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

Таблица 1

Форма для ввода исходных данных для математической модели задачи

A	B	C	D	E	F	G	H
			Переменные
Имя	Прод 1	Прод 2	Прод 3	Прод 4
Значение
Нижн.гр
Верхн.гр					ЦФ	Направл.
Коэф. в ЦФ
			Ограничения
Вид					Лев.часть	знак	Прав.часть
Труд
Оборуд.
Сырье

Таблица 2

Ввод зависимостей из математической модели в электронную таблицу

A	B	C	D	E	F	G	H
			Переменные
Имя	Прод 1	Прод 2	Прод 3	Прод 4
Значение
Нижн.гр
Верхн.гр					ЦФ	Направл.
Коэф. в ЦФ					(формула)
			Ограничения
Вид					Лев.часть	знак	Прав.часть
Труд					(формула)	<=
Оборуд.					(формула)	<=
Сырье					(формула)	<=

Ссылка на ячейку в виде абсолютного адреса не изменится при копировании содержащей ее формулы. Абсолютный адрес ячейки имеет знак $ перед буквой столбца и номером строки (например, $A$1). В смешанном адресе ячейки только одна из его компонент абсолютна, а другая относительна (например, $A1 или A$1). Нажатием клавиши F4 тип ссылки на ячейку можно изменить с относительного на абсолютный, смешанный и снова на относительный.

Копировать формулы можно несколькими способами:

выделить ячейку - источник для копирования;

нажать [ Копировать (в буфер)] - кнопку в виде двух листов;

выделить ячейку в которую будем копировать;

нажать [ Вставить (из буфера)] - кнопка в виде папки и листа;

пунктир в источнике убирается клавишей [ Esc ].

Такого же результата можно добиться используя команды из меню Правка. Другим способом является перетаскивание формулы с помощью мыши:

выделить объект копирования и подвести курсор к границе объекта;

нажать [ Ctrl ] и удерживая переместить копию объекта на новое место;

отпустить кнопку мыши и [ Ctrl ].

Если область для копирования расположена вплотную к источнику, тогда копирование осуществляется протаскиванием мыши:

выделить ячейку - источник копирования;

курсор на квадратик в правом нижнем углу выделенной ячейки;

переместить курсор в виде перекрестия в ячейку(ки) куда будем копировать и отпустить кнопку мыши.

Для ввода направленности целевой функции и граничных условий вызвать в меню Сервис\Поиск решения в диалоговом окне:

в окно Установить целевую функцию ввести адрес ячейки, содержащей формулу ЦФ;

выбрать направление ЦФ (max, min или =значению);

в поле Изменяя ячейки ввести адреса ячеек, содержащих значения искомых переменных;

для ввода ограничений нажать [ Добавить ];

в левом окне ввести адрес ячейки, содержащей переменную или формулу левой части ограничений, выбрать знак (<=, >=, =), в правом окне ввести адрес ячейки, содержащей значение правой части ограничения (таким образом вводятся граничные условия для переменных и условия ограничений);

после ввода последнего ограничения нажать [ ОК ].

Если при вводе задачи возникает необходимость в изменении или удалении внесенных ограничений или граничных условий, вызвать команды [ Изменить] или [ Удалить].

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

Максимальное время - служит для назначения времени в секундах, выделяемого на поиск решения задачи. В поле можно ввести время, не превышающее 32767 с (более 9 часов). Значение 100, используемое по умолчанию, подходит для решения большинства задач.

Предельное число итераций - служит для назначения числа итераций. Используемое по умолчанию значение 100 подходит для решения большинства задач.

Относительная погрешность - задает точность вычислений.

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

Неотрицательные значения – для выполнения условия неотрицательности получаемых значений переменных. Другой способ выполнения этого условия – добавление ограничений для каждой переменной в виде x_i ≥ 0.

После задания параметров нажать [ OK ].

Результаты поиска решения вызываются нажатием кнопки [ Выполнить ]. Для анализа результатов решения выводятся отчеты по результатам, устойчивости и пределам изменения переменных.

Для большей наглядности получаемых результатов можно построить столбиковую диаграмму, отображающую количество выпускаемой продукции по видам продукции. Для этого вызвать [ Мастер диаграмм] – нажать кнопку с разноцветной столбиковой диаграммой или выбрать из меню Вставка строку Диаграмма. В открывшемся меню выбрать столбиковую диаграмму (Гистограмму), нажать [ Далее>], в строку Диапазон ввести адреса ячеек, содержащих наименования продукции и количество выпускаемой продукции, нажать [ Готово ]. Перетащите диаграмму с помощью мыши на свободное место. Отформатируйте масштаб изображения так, чтобы исходные данные и диаграмма помещались на экране (набрать, например, масштаб 90%).

Порядок выполнения работы

1.Изучить методические указания

2.Получитьу преподавателя исходные данные

3.Провести необходимые расчеты на ЭВМ

4.Сделаты выводы и оформить отчет по выполненной работе