Последовательность действий.1. Открыть рабочую книгу Analiz.xls с помощью команды меню Файл > Открыть… (или комбинации клавиш Ctrl + O). 2. Вставить, если необходимо, новый лист с помощью команды меню Вставка > Лист. 3. Переименовать новый лист с помощью команды меню Формат > Лист > Переименовать, задав ему имя «Поиск решения». 4. Разместить на листе необходимые числовые ограничения параметров и целевую функцию, задав используемым параметрам имена для удобства дальнейшего моделирования (рис. 6). В ячейки B3:D3 занести, соответственно, оптовую цену продукции (коэффициенты целевой функции). Выделить блок ячеек B3:D3. С помощью команды меню Вставка > Имя > Присвоить… задать этому блоку ячеек имя «Цена». В ячейки B7:D11 занести коэффициенты расхода сырья на тонну продукции. В ячейки B7:D7 занести, соответственно, коэффициенты расхода сырья a; в ячейки B8:D8 – коэффициенты расхода сырья b и т.д. Выделить блок ячеек B7:D7 и присвоить ему имя «Расход_сырья_a». Выделить блок ячеек B8:D8 и присвоить ему имя «Расход_сырья_b». Выделить блок ячеек B9:D9 и присвоить ему имя «Расход_сырья_c». Выделить блок ячеек B10:D10 и присвоить ему имя «Расход_сырья_d». Выделить блок ячеек B11:D11 и присвоить ему имя «Расход_сырья_e». В ячейки Е7:Е11 занести суточный запас по видам сырья. Выделить блок ячеек E7:E11 и присвоить ему имя «Запас_сырья». В ячейки B15:D15 (для ограничения минимального объема производства) трижды занести ноль. Выделить блок ячеек B15:D15 и присвоить ему имя «Минимальный_объем». В ячейки B19:D19 установить неизвестные объемы производства ОА, ОБ и ОВ, придав им первоначально нулевые значения. Выделить блок ячеек B19:D19 и присвоить ему имя «Объем». В ячейки B23:D23 занести максимальный объем спроса по видам продукции. Выделить блок ячеек B23:D23 и присвоить ему имя «Максимальный_объем». В ячейки F7:F11 занести формулы расчета суммарного расхода сырья, соответственно: - в ячейку F7: =СУММПРОИЗВ(Расход_сырья_a;Объем); - в ячейку F8: =СУММПРОИЗВ(Расход_сырья_b;Объем) и т.д. Выделить блок ячеек F7:F11 и присвоить ему имя «Расход_сырья». В ячейку E25 занести формулу целевой функции: =СУММПРОИЗВ(Цена;Объем) Выделить ячейку E25 и присвоить имя «Доход».
Рис. 6. Исходные параметры и целевая функция. 5. Выполнить команду меню Сервис > Поиск решения… В появившемся диалоговом окне Поиск решения сформировать модель оптимизационной задачи (рис. 7). В поле Установить целевую ячейку: установить ссылку на ту ячейку, в которую была занесена формула целевой функции (Доход). В этой ячейке, после решения задачи будет находиться ее оптимальное значение. Наша целевая функция стремится к максимуму, поэтому в поле Равной: установить максимальному значению. В ячейки B19:D19 (Объем) мы заносили нулевые значения объема производства. После выполнения задачи в них будут автоматически занесены подобранные значения нашей целевой функции. Эти координаты меняют свои значения в процессе нахождения оптимального решения, поэтому в поле Изменяя ячейки: внести ссылки на диапазон подбираемых ячеек (Объем).
Рис. 7. Диалоговое окно Поиск решения. 6. В поле Ограничения: необходимо занести все ограничения целевой функции согласно условию задачи. Для этого нажимаем кнопку Добавить, открывающую диалоговое окно Добавление ограничения (рис. 8).
Рис. 8. Диалоговое окно Добавление ограничения. Здесь, в поле Ссылка на ячейку: ввести адрес ячейки, значение которой необходимо ограничить. Для первого условия – неотрицательного объема производства продукции – ввести Объем (имя блока ячеек). В следующем поле выбирается знак неравенства (в нашем случае выбрать >=). В последнем поле Ограничение: ввести адрес ячейки с ограничением или необходимое числовое значение. Для первого условия – неотрицательного объема производства продукции – ввести Минимальный_объем (имя блока ячеек). После нажимаем кнопку Добавить, которая автоматически добавляет введенное ограничение в список ограничений и очищает поле для ввода нового. Аналогично добавляются остальные ограничения. Если необходима корректировка уже созданного ограничения, то для этого нажать кнопку Изменить в окне Поиск решения и произвести все необходимые корректировки в открывшемся окне Изменение ограничения. С помощью кнопки Удалить можно удалить ненужное ограничение. 7. Далее необходимо задать параметры для решения задачи оптимизации. Для этого нажимаем кнопку Параметры, открывающую диалоговое окно Параметры поиска решения (рис. 9). В этом окне можно ограничить время выполнения поиска решения и выполнения промежуточных вычислений (Максимальное время: – не более 9 часов); Предельное число итераций; точность, с которой найденное решение соответствует целевому значению (Относительная погрешность); Допустимое отклонение для переменных от оптимального значения. Для задач с нелинейной целевой функцией задается параметр Сходимость, который влияет на прекращение поиска. Если относительные изменения значения целевой функции за последние пять итераций меньше указанного числа, поиск прекращается. Выполняется установка типа модели – Линейная модель, если целевая функция линейная. Можно выполнять Автоматическое масштабирование параметров модели и Показывать результаты итераций.
Рис. 9. Диалоговое окно Параметры поиска решения. При решении задачи можно выбрать метод экстраполяции оценок переменных для каждого шага поиска (Оценки) – линейная или квадратичная (для задач с нелинейной целевой функцией), метод численного дифференцирования для целевой функции (Разности) – прямые или центральные (для задач с нелинейной целевой функцией), Метод поиска – Ньютона (требуется много оперативной памяти) или сопряженных градиентов (больше итераций). Основным ограничением модели является максимальное число переменных – 200. Однако, можно сохранять на одном листе несколько оптимизационных моделей и загружать их по мере необходимости. Определенные значения уже стоят в этих полях по умолчанию, но при необходимости их можно изменить. Для нашей задачи в этом окне необходимо поставить флажок на поле Линейная модель, так как решаемая задача относится к задачам линейного программирования. Затем нажать кнопку ОК. 8. Запустить решение задачи, нажав кнопку Выполнить в окне Поиск решения. Программа начнет поиск оптимального решения, о результатах которого сообщит в окне Результаты поиска решения (рис. 10), а на самом листе, в соответствующих ячейках, отобразится найденное решение (рис. 11).
Рис. 10. Диалоговое окно Результаты поиска решения. Если решение найдено, его можно сохранить либо восстановить исходные значения переменных. Результат решения можно сохранить в качестве сценария. Для этого необходимо нажать кнопку Сохранить сценарий…
Рис. 11. Результат решения задачи. Для нашей задачи найденное решение будет звучать так: Максимально возможный суточный доход составит 42000 руб. при условии выпуска 4т продукции А, 5,5т продукции Б, 4,75т продукции В. Если оптимальное решение не существует, будет выведено диалоговое окно Результаты поиска решения с соответствующим сообщением (рис. 12).
Рис. 12. Диалоговое окно Результаты поиска решения. В этом случае следует попытаться, в первую очередь, изменить параметры поиска решения. В случае неудачного решения можно изменить исходные данные (запасы сырья, нормы расхода). 9. В конце решения сформировать отчеты. Для этого в диалоговом окне Результаты поиска решения в поле Тип отчета выбрать соответствующие отчеты и нажать кнопку ОК (рис. 10). Будут созданы три отчета на новых листах рабочей книги: Отчет по результатам 1, Отчет по устойчивости 1, Отчет по пределам 1. Они содержат сведения об изменяемых параметрах и их влиянии на целевую функцию. Отчет по результатам – сведения о целевой функции с указанием ячейки, исходного и конечного значения, сведения о переменных с указанием списка ячеек, исходных и конечных значений, сведения об ограничениях с указанием списка ячеек, формул, вычисленных значений, статуса и разницы (свободного остатка). Отчет по устойчивости – сведения о чувствительности модели (изменение целевой функции при изменении переменных и ограничений). Отчет по пределам – сведения о нижних и верхних границах значений переменных. Нижний предел – наименьшее значение переменной, верхний предел – наибольшее значение переменной (значения всех прочих переменных фиксированы и удовлетворяют ограничениям). Самостоятельно изучить отчеты и интерпретировать полученные результаты. 10. Закрыть файл с сохранением с помощью команды меню Файл > Закрыть. Самостоятельное задание Предприятие производит пять видов продукции: А, Б, В, Г и Д. Отпускные цены тонны продукции А равны 1000 руб., продукции Б – 2000 руб., продукции В – 3000 руб., продукции Г – 4000 руб., продукции Д – 5000 руб. Для их производства используется три типа сырья: a, b, c, суточные запасы которых и расход на 1 тонну соответствующей продукции приведены в табл. 2. Таблица 2 Суточные запасы сырья и расход на 1 тонну продукции
Согласно маркетинговым исследованиям суточный спрос на продукцию А не превышает 10т, на продукцию Б – 9т, на продукцию В – 8т, на продукцию Г – 7т, на продукцию Д – 6т. Какое количество продукции каждого вида должна производить компания, чтобы получить максимальный доход?
|
