Для решения задач оптимизации
Цель работы: изучение порядка работы с электронной таблицей при решении задач оптимизации
Исходные положения. Если финансы, оборудование, сырье и даже людей полагать ресурсами, то значительное число задач в экономике можно рассматривать как задачи распределения ресурсов. Часто математической моделью таких задач является задача линейного программирования.
Допустим требуется определить, в каком количестве надо выпускать продукцию четырех типов Прод 1, Прод 2, Прод 3, Прод 4, для изготовления которой требуются ресурсы трех видов: трудовые, оборудование, сырье. Количество ресурса каждого вида, необходимое для выпуска единицы продукции данного типа (норма расхода), наличие располагаемого ресурса, а также прибыль, получаемая от реализации единицы каждого типа продукции, приведены в таблице:
| Ресурс | Прод 1 | Прод 2 | Прод 3 | Прод 4 | Наличие |
| Прибыль | |||||
| Труд | |||||
| Оборуд. | |||||
| Сырье |
Математическая модель задачи:
F = 15 Х1 + 24 Х2 + 19 Х3 + 27 Х4 → MAX
2 X1 + 2 X2 + 2 X3 + 2 X4 <= 30
12 X1 + 8 X2 + 10 X3 + 6 X4 <= 200
8 X1 + 20 X2 + 12 X3 + 26 X4 <= 125
Xj ≥ 0; j=1-:-4
где Xj - количество выпускаемой продукции j-го типа;
bj - количество располагаемого ресурса i-го вида;
aij - норма расхода 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 подходит для решения большинства задач.
Относительная погрешность - задает точность вычислений.
Линейная модель - для решения задач линейного программирования.
Неотрицательные значения – для выполнения условия неотрицательности получаемых значений переменных. Другой способ выполнения этого условия – добавление ограничений для каждой переменной в виде xi ≥ 0.
После задания параметров нажать [OK].
Результаты поиска решения вызываются нажатием кнопки [Выполнить]. Для анализа результатов решения выводятся отчеты по результатам, устойчивости и пределам изменения переменных.
Для большей наглядности получаемых результатов можно построить столбиковую диаграмму, отображающую количество выпускаемой продукции по видам продукции. Для этого вызвать [Мастер диаграмм] –нажать кнопку с разноцветной столбиковой диаграммой или выбрать из меню Вставка строку Диаграмма. В открывшемся меню выбрать столбиковую диаграмму (Гистограмму), нажать [Далее>], в строку Диапазон ввести адреса ячеек, содержащих наименования продукции и количество выпускаемой продукции, нажать [Готово]. Перетащите диаграмму с помощью мыши на свободное место. Отформатируйте масштаб изображения так, чтобы исходные данные и диаграмма помещались на экране (набрать, например, масштаб 90%).
Порядок выполнения работы
1.Изучить методические указания
2.Получитьу преподавателя исходные данные
3.Провести необходимые расчеты на ЭВМ
4.Сделаты выводы и оформить отчет по выполненной работе
Отчет по работе должен содержать
1.Исходные положения
2.Исходные данные
3.Порядок работы на ЭВМ при решении задачи
4.Результаты решения
5. Анализ результатов и выводы.