Использование надстройки «Поиск решения»

Стр 1 из 7Следующая ⇒

РАСЧЕТНО – ГРАФИЧЕСКАЯ РАБОТА

ДЛЯ СТУДЕНТОВ 111у группы

Решение задач оптимизации в Excel

Методические указания

Работу подготовила: преподаватель

Платонова Татьяна Евгеньевна, к. пед. н.,

Доцент кафедры высшей математики

Содержание

1. Рекомендации к выполнению работы .................................................... 3

2. Образец титульного листа ..................................................................... 4

3. Требования к выполнению работы ....................................................... 5

4. Образец решения задачи 1..................................................................... 5

5. Анализ результатов решения............................................................... 14

5.1. Отчет по результатам...................................................................... 17

5.2. Отчет по устойчивости.................................................................... 18

5.3. Отчет по пределам........................................................................... 23

6.Индивидуальные задания к расчетно-графической работе ................ 24

7. Приложение 1: Образец выполнения РГР задачи 2 об оптимизации плана товарооборота........................................................................................... 44

8. Электронные ресурсы........................................................................... 50

Рекомендации к выполнению работы

На первом листе кроме данных о студенте (Ф.И.О., группа, курс, № зачетной книжки или студенческого билета) обязательно указывается номер варианта! Номер варианта выбирается по последней цифре номера зачетной книжки.

Расчетно-графическая работа состоит из 2 комплексных заданий. Условие задач следует переписывать только для своего варианта.

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

Расчетно-графическая работа выполняется на листах формата A4 акку- ратным почерком или на компьютере. Пример оформления титульного листа приведен ниже. Титульный лист, графики и таблицы обязательно оформляются на компьютере. Приветствуется выполнение расчетов с использованием MS Excel.

Выполненную и оформленную расчетно-графическую работу необходимо представить в деканатне позднее, чем за 15 календарных дней до начала сессии. На экзамене по дисциплине «Методы оптимальных решений» один из вопросов обязательно касается проведенных в работе расчетов.

При отсутствии выполненной контрольной работы и при выполненной работе с неправильным номером варианта студент до экзамена не допуска- ется. При невозможности объяснить решение более чем одного из заданий студент также не допускается до экзамена. Если студент не может объяснить одно из заданий, он получает на экзамене дополнительные упрощенные за- дачи на соответствующие разделы курса.

«ЧОУ ВПО Институт экономики, управления и права (г. Казань)»

Зеленодольский филиал

Экономический факультет

Кафедра высшей математики

РАСЧЕТНО – ГРАФИЧЕСКАЯ РАБОТА

ПО МЕТОДАМ ОПТИМАЛЬНЫХ РЕШЕНИЙ

Вариант ___

Выполнил:

студент группы №_____

экономического факультета

Фамилия Имя Отчество

зачетная книжка № ____________

контактный телефон: ____________

Руководитель:

Платонова Татьяна Евгеньевна

Зеленодольск – 2013 г.

Требования к выполнению работы

Расчетно-графическая работа выполняется на втором курсе в процессе изучения дисциплины Математика-2. Цель работы – овладение методами решения задач оптимизации на компьютере с использованием надстройки «Поиск решения» электронных таблиц MS Excel.

Каждый студент должен решить две содержательные задачи оптимизации, сформулированные в словесной форме. Для этого необходимо:

1. Построить математическую модель задачи;

2. Представить ее в табличной форме на листе Excel;

3. Найти оптимальный план средствами надстройки «Поиск решения»;

4. Вывести отчеты по результатам и устойчивости и объяснить их смысл;

5. Ответить на все вопросы из задания;

Отчет о проделанной работе может быть выполнен и распечатан либо в формате MS Word, либо в формате MS Excel.

Образец решения задачи 1

Построение математической модели

Производственная задача. Фирма специализируется на производстве компьютерных столов трех видов А, В, С, что требует различных затрат труда на каждой стадии производства:

Производственный участок	Затраты труда (чел.-час.) на стол
Производственный участок	А	В	С
Лесопилка	1	2	4
Сборочный цех	2	4	2
Отделочный цех	1	1	2

В течение недели можно планировать работу на лесопилке на 360 чел.-час., в сборочном цехе – на 520 чел.-час. и в отделочном цехе – на 220 чел.-час. Цены реализации одного стола типа А, В, С составляют 9, 11, 15 долларов соответственно. Сколько столов каждой модели надо производить в неделю, чтобы максимизировать суммарный недельный доход?

Требуется также провести постоптимальный анализ полученного решения.

Составим математическую модель задачи, выбрав в качестве переменных – количество компьютерных столов каждого из трех видов A, B, C, планируемых к выпуску. Тогда ожидаемый суммарный доход может быть подсчитан по формуле

(1)

Переменные задачи удовлетворяют ограничениям

(2)

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

Очевидно, что построенная модель имеет линейную структуру и, следовательно, является задачей линейного программирования.

Создание электронной модели

Чтобы привлечь компьютер к решению этой задачи необходимо ввести исходные данные на лист Excel. Это можно сделать, например, так, как изображено на рис.1. Сначала заносим в таблицу неизменяемые данные: цены продукции, удельные расходы труда на выпуск каждого вида столов и их наличные запасы в каждом цехе.

Рис. 1.

Затем заполняем изменяемые ячейки B3:D3, в которых расположены компоненты плана . На этапе ввода исходных данных сюда заносятся любые числа, например, единицы. После решения в этих ячейках будут находиться оптимальные значения переменных. Целевая функция (суммарный доход) и левые части ограничений (затраты труда в каждом цехе) подсчитываются в соответствие с составленной моделью по формуле (1) и левым частям (2).

Содержимое соответствующих ячеек приведено ниже

< Е4 > = СУММПРОИЗВ(B4:D4;B$3:D$3) – вычисление суммарного дохода.

< E8 > = СУММПРОИЗВ(B8:D8;B$3:D$3),

< E9 > = СУММПРОИЗВ(B9:D9;B$3:D$3),

< E10 > = СУММПРОИЗВ(B10:D10;B$3:D$3) – вычисление затрат труда в каждом цехе на выпуск всей продукции.

Функция СУММПРОИЗВ(массив1, массив2) относится к разряду математических функций. Она вычисляет произведения соответствующих элементов массивов, после чего суммирует полученные произведения. Вставку функции можно осуществить через одноименный пункт меню Вставка, или с помощью кнопки . Если ссылку на диапазон изменяемых ячеек B3:D3 в формуле для < E4 > сделать абсолютной B$3:D$3, для чего можно воспользоваться клавишей F4, то ячейки E8:E10 легко заполнить с помощью операции копирования ячейки E4.

Ячейка Е4, содержащая формулу для вычисления целевой функции является целевой ячейкой.

Использование надстройки «Поиск решения»

После активации строки Поиск решения в меню Cервис, мы увидим следующее окно:

Рис. 2.

В поле Установить целевую ячейку надо ввести адрес (или имя) ячейки, в которой находится формула для вычисления целевой функции, в нашей задаче это адрес E4. Если перед вызовом надстройки целевую ячейку сделать активной, щелкнув по ней мышкой, то требуемый адрес появится автоматически. Имеется возможность максимизировать, минимизировать значение целевой ячейки, или установить значение в целевой ячейке равным некоторому числу путем установки переключателя в соответствующее положение.

В поле Изменяя ячейки вводятся имена или адреса изменяемых ячеек. Их можно вводить либо единым массивом, либо разделенные запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Их значения изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указанной в поле Установить целевую ячейку. Допускается установка до 200 изменяемых ячеек. Кнопка Предположить должна помочь автоматически найти все ячейки, влияющие на формулу модели, однако результат часто бывает неточным, поэтому внесем адреса изменяемых ячеек, выделив их на листе мышкой.

В поле Ограничения необходимо указать условия, накладываемые на переменные (изменяемые ячейки). Для ввода ограничений используются кнопки Добавить и Изменить, которые вызывают окно Добавление ограничения (рис.3), служащее для установки между ячейками соотношений типа , или , а также наложения ограничений на целочисленность и бинарность (возможность для переменных принимать только два значения 0 или 1) .

Рис. 3.

Поле Ссылка на ячейку служит для указания ячейки или диапазона, на значения которых необходимо наложить ограничение. Внесем туда диапазон ячеек E8:E10, содержащий левые части ограничений, выделив их в листе мышкой.

Поле Ограничение служит для задания условия, которое накладывается на значения ячейки или диапазона, указанного в поле Ссылка на ячейку. Выбор необходимого условного оператора ( , , , цел или двоич ) производится из выпадающего списка в центре окна. В правое поле окна вводится ограничение - число, формула, ссылка на ячейку или диапазон. В нашем примере укажем диапазон F8:F10, содержащий правые части ограничений (2) – наличные запасы трудоресурсов.

Кнопка Добавить используется для ввода новых ограничений без возврата в окно Поиск решения. Нам эта кнопка не понадобится, так как все ограничения мы ввели с помощью одного диапазона. В принципе, в этом же окне можно ввести условия неотрицательности переменных, однако для этой цели в программе предусмотрена специальная возможность, которой мы воспользуемся позже.

Завершается ввод ограничений нажатием кнопки OK и возвратом в главное окно Поиск решения.

В этом окне также присутствуют кнопки Выполнить и Параметры. Первая служит для запуска алгоритма поиска оптимального решения. Вторая – предлагает настроить поиск более конкретно. Можно изменять условия и варианты поиска решения для линейных и нелинейных задач, а также загружать и сохранять оптимизируемые модели. Значения и состояния элементов управления, используемые по умолчанию, подходят для решения большинства задач.

Нажатие кнопки Параметры выводит на экран одноименное окно:

Рис. 4.

Опишем элементы окна параметров поиска решения:

Максимальное время

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

Итерации

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

Точность

Служит для задания точности, с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 (нуля) до 1. Низкая точность соответствует введенному числу, содержащему меньшее количество десятичных знаков, чем число, используемое по умолчанию – например, 0,0001. Высокая точность увеличит время, которое требуется для того, чтобы сошелся процесс оптимизации.

Допустимое отклонение

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

Сходимость

Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость, поиск прекращается. Сходимость применяется только к нелинейным задачам, условием служит дробь из интервала от 0 (нуля) до 1. Лучшую сходимость характеризует близость значения к нулю. Лучшая сходимость требует больше времени на поиск оптимального решения.

Линейная модель

Служит для ускорения поиска решения линейной задачи оптимизации или линейной аппроксимации нелинейной задачи. Инициализация этой опции определяет использование в алгоритме поиска симплекс-метода.

Дата добавления: 2018-09-22; просмотров: 322; Мы поможем в написании вашей работы!

Поделиться с друзьями:

12 3 4 5 6 7 Следующая ⇒

Мы поможем в написании ваших работ!