Материал: 4758
Рис.18
Столбец «Выплаты по кредиту» рассчитывается с помощью функции
=ОСПЛТ($F$1/12;A4;$C$2;$B$1).
Столбец «Полные выплаты» рассчитывается с помощью функции
=ПЛТ($F$1/12;$C$2;$B$1).
Содержание отчета по лабораторной работе
1)Таблица исходных данных
2)Таблица с основные формулами, по которым по которым происходил расчет.
3)Таблица с результатами.
Практическая работа № 2 (4 ч.)
Использование табличного процессора Excel для поиска оптимальных решений задач линейного программирования (ЗЛП).
Цель работы: Знакомство с методикой формулировки и решения задач математического программирования с использованием процедуры Поиск решения табличного процессора Excel.
Содержание работы и порядок ее выполнения
Задачи оптимизации образуют очень широкий класс практических задач, среди которых значительное место отводится задачам линейного программирования. Обычно их решение сопряжено с большим количеством вычислений, что затрудняет их решение вручную.
Удобным инструментом для автоматизации решения задач линейного программирования в Ехсе1 является процедура Поиск решения, с одноименной командой из меню Сервис в основном окне Ехсе1.
Лабораторная работа рассчитана на два лабораторных занятия. На первом занятии студент знакомится с процедурой Поиск решения и использует ее для формулировки и решения задачи составления оптимального по прибыли плана выпуска двух видов изделий по данным задания, выдаваемого преподавателем.
На втором занятии студент отчитывается преподавателю по теоретической части лабораторной работы и защищает полученные практические результаты. При защите и сдаче преподавателю лабораторной работы студент должен ответить на контрольные вопросы.
Пример постановки задачи линейного программирования в Excel
Рассмотрим задачу оптимального планирования выпуска продукции. Предположим, предприятие выпускает две модели подшипников,
причем каждая модель производится на отдельной технологической линии. Суточный объем производства первой линии – 60 изделий, второй линии – 75 изделий. На подшипник первой модели расходуется 10 однотипных элементов, на подшипник второй модели – 8 таких же элементов. Максимальный суточный запас используемых элементов равен 800 единицам. Прибыль от реализации одного подшипника первой и второй моделей равна 30 и 20 ден. ед. соответственно. Требуется составить оптимальный по прибыли план выпуска продукции, т.е. определить такие суточные объемы производства подшипников первой и второй моделей, чтобы прибыль была максимальной.
Пусть на первой линии выпускается первая модель, на второй линии выпускается вторая модель подшипника.
Составим экономико-математическую модель задачи: Обозначим: x1 – объем выпуска подшипников первой модели;
x2 – объем выпуска подшипников второй модели. Функция прибыли (часто называемая целевой функцией) имеет вид:
F(x) 30 x1 20 x2 max ;
Сформулируем ограничения на наличие ресурсов в математических терминах:
суточный объем производства первой линии не превышает 60 изделий, т.е. x1 60
суточный объем производства второй линии не превышает 75 изделий, т.е. x2 75
на подшипник первой модели расходуется 10 однотипных элементов, на подшипник второй модели – 8 таких же элементов. Максимальный суточный запас используемых элементов равен 800 единицам.
Следовательно, количество используемых элементов не должно превышать 800, т.е. 10 x1 8 x2 800 ;
кроме того, по смыслу задачи x1 0, |
x2 0 , т.е. объемы выпуска |
подшипников не могут быть отрицательными (последнее – это так называемые тривиальные, или естественные ограничения).
Таким образом, математическая модель задачи имеет вид:
F(x) 30 x1 20
при ограничениях:
x2 max ;
x1 60; |
|
||
|
|
|
|
x2 75; |
|
||
|
|
|
|
10 x1 8 x2 800; |
|||
x |
|
0, |
i 1, 2; |
|
i |
|
|
x |
i |
Z , |
где Z множествоцелых чисел. |
|
|
|
|
Математическую модель задачи реализуем на листе Excel, как показано на рис. 1.
A |
B |
C |
D |
E |
F |
G |
|
|
|
|
|
|
|
1 |
2 |
3 |
их значение: |
4 |
Коэф. в ЦФ: |
5 |
|
6 |
|
7 |
На одно |
8 |
изделие |
9 |
элементов: |
Переменные |
|
|
|
|
х1 |
х2 |
Целевая |
|
|
1 |
1 |
функция: |
|
|
30 |
20 |
|
|
|
Ограничения: |
вычисляемые |
знак |
исходные |
|
|
|
1 |
<= |
60 |
|
|
1 |
<= |
75 |
10 |
8 |
|
<= |
800 |
Рис. 1. Представление математической модели на листе Excel.
Ячейки В3:С3 – изменяемые ячейки, в которых записываются некоторые начальные значения (например 1). Расход элементов на один подшипник помещен в ячейки В9:С9, суточный запас элементов – в ячейку F9. Максимально возможные объемы производства подшипников по линиям помещены в ячейки F7:F8. Фактический объем израсходованных элементов связан с изменяемыми ячейками формулой: = B3*B9 + C3*C9. Ячейки D7:D8 связаны с ячейками B3:C3 и содержат формулы: = В3, = С3.
Ячейка D4 выбрана в качестве целевой ячейки и содержит формулу: СУММПРОИЗВ(В3:С3; В4:С4). Встроенная функция СУММПРОИЗВ в данном случае тождественна формуле: = B3*B4 + C3*C4.
Чтобы определить в ячейке D4 выражение для целевой функции, необходимо выполнить следующее:
1) установить курсор в ячейку D4, на панели инструментов нажать кнопку Мастер функций. 2) в диалоговом окне Мастер функций выберать категорию Математические и функцию СУММПРОИЗВ. 3) и диалоговом окне Аргументы функции в строку «Массив 1» ввести В3:С3, а в строку «Массив 2» ввести В4:С4 (рис. 2).
Задание 1. В соответствии с данными индивидуального задания, полученного у преподавателя, сформировать в ячейках листа Excel шаблон решения задачи оптимизации по прибыли плана выпуска продукции.
Рис. 2. Диалоговое окно функции СУММПРОИЗВ.
Процедура Поиск решения табличного процессора Excel
После внесения всех зависимостей, составляющих математическую модель, на рабочий лист Excel для дальнейшего решения вызывается процедура Поиск решения. С ее помощью могут быть решены многие задачи, формулируемые как задачи математического программирования, а именно:
минимизация затрат на транспортировку (традиционная задача);
составления оптимального по прибыли плана выпуска продукции;
распределение назначения работников по рабочим местам с целью получения наибольшей прибыли, или улучшения других показателей;
составление смесей;
оптимальный раскрой материалов;максимизация выпуска товаров при имеющихся ограничениях на сырье.
Вызов процедуры Поиск решения осуществляется одноименной командой из подменю Сервис главного меню Excel. При этом появляется диалоговое окно Поиск решения, показанное на рис. 3.
Вполе Установить целевую записывается ссылка на ячейку (в нашем случае на ячейку D4), содержащую формулу с целевой функцией. В группе переключателей Равной указывается направление оптимизации ЦФ (в нашем случае: равной максимальному значению).
Вполе Изменяемые ячейки указываются ячейки, изменением которых достигается экстремальное значение целевой ячейки. Они должны содержать какие-либо допустимые значения элементов решения оптимизационной задачи и быть связаны формулой (целевой функцией), помещаемой в
целевую ячейку. В итоге процедура поиска решения сводится к определению значений изменяемых ячеек.
Кнопка Предположить служит для автоматического поиска изменяемых ячеек. При этом определятся все ячейки, не содержащие формулы и влияющие на целевую функцию.
Ограничения оптимизационной задачи также задаются формулами, связывающими изменяемые ячейки со значениями ресурсов оптимизационной задачи, записанных во влияющих ячейках. В этих формулах ресурсы могут быть заданы либо в виде числовых значений, либо – в виде ссылок на ячейки. В процедуру поиска решений формулы ограничений вносятся с помощью кнопки Добавить.
Рис. 3 Диалоговое окно Поиск решения При этом вызывается диалоговое окно Добавление ограничения, показанное на рис. 4.
Рис. 4. Диалоговое окно Добавление ограничения
Поле Ссылка на ячейку служит для указания ячейки или диапазона, на значения которых необходимо наложить ограничение.
Поле Ограничение служит для задания условия, которое накладывается на значения ячейки или диапазона, указанного в поле Ссылка на ячейку. Для задания условия необходимо выбрать условный оператор (<=, =, >= или "цел") и ввести ограничение: число, формулу, ссылку на ячейку или диапазон ячеек в поле справа от раскрывающегося списка условных операторов.
Кнопка Добавить позволяет наложить новое условие на поиск решения задачи, не возвращаясь в окно диалога Поиск решения.
Работа в окне диалога Изменить ограничение аналогична работе в окне
Добавление ограничения.
Кнопка Параметры вызывает диалоговое окно Параметры поиска