Алексеева Римма Юрьевна

Компьютерное моделирование прикладных задач в электронных таблицах. Решение оптимизационных задач с помощью надстройки "Поиск решения".

Материал опубликован 13 November 2019

Лицей современных технологий управления №2 г.Пензы Алексеева Р.Ю.

Тема: Задачи оптимизации в электронных таблицах.

Цели: Закрепить основные навыки работы в электронных таблицах.
Развитие логического мышления и познавательного интереса.
Воспитание информационной культуры.

I . Оргмомент.

II . Повторение основных принципов решения оптимизационных задач с помощью надстройки электронных таблиц «Поиск решения».

Фронтальный опрос:

Что такое задачи оптимизации?

В каких случаях задача имеет оптимальное решение?

Какие задачи относятся к задачам оптимизации?

Какие правила (алгоритм) следует соблюдать при решении оптимизационных задач?

Как вызвать в электронных таблицах надстройку Поиск решения?

Как активизировать эту надстройку, если она не установлена?

Что такое целевая функция?

Что показывают ограничения в задачах оптимизации?

Задача оптимизации – поиск оптимального (наилучшего) решения данной задачи при соблюдении некоторых условий.

Задача имеет оптимальное решение, если она удовлетворяет двум требованиям:

Имеет более одного решения, т.е. существуют допустимые решения;

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

При решении задач оптимизации целесообразно руководствоваться следующим алгоритмом:

Разобрать условие задачи,

Построить математическую модель,

Установить поисковые величины,

Установить условия оптимизации,

Задать ограничения,

Решить задачу на компьютере,

Проанализировать результат.

Основные понятия:

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

III . Закрепление практических навыков решения задач оптимизации.

Задача № 1. ( открыть файл-1 из папки урока)

Бык стоит – 10 т.руб., корова – 5 т.руб., а теленок – 500 руб. Сколько можно купить быков, коров и телят на 100 т.руб., при условии, что надо сформировать стадо из 100 голов скота.

Разместим данные задачи в таблице

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

S1=10000X1

S2=5000X2

S3=500X3

K=X1+X2+X3

SUM=S1+S2+S3

В качестве целевой функции возьмем величину SUM, она должна быть SUM=100000 руб.

Ограничения (необходимые условия):

K=100

X1,X2,X3 – целые

X10 X20 X30

Реализуем математическую модель средствами электронных таблиц

Вызываем надстройку Поиск решения и заполняем соответствующие поля

После команды Выполнить получим результат

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

Задача №2. ( открыть файл

Задача №2.
XLS / 500 Кб

/data/files/b1573667549.xls (Задача №2.) )

Фабрика выпускает три вида тканей, причем суточное плановое задание составляет не менее 90 м тканей первого вида, 70 м — второго и 60 м — третьего. Суточные ресурсы следующие: 780 единиц производственного оборудования, 850 единиц сырья и 790 единиц электроэнергии, расход которых на один метр тканей представлен в табл.

Определить, сколько метров ткани каждого вида следует выпустить, чтобы общая стоимость выпускаемой продукции была максимальной.

Цена за 1 м ткани вида I равна 80 у. е., II - 70 у. е., III - 60 у. е.

Затраты ресурсов на производство тканей

Ресурсы	Ткани
Ресурсы	I	II	III
Оборудование	2	3	4
Сырье	1	4	5
Электроэнергия	3	4	2

Разместим данные задачи в таблице

Построим математическую модель в формулах электронных таблиц.

Затраченные суточные ресурсы

F4=C4*C7+D4*D7+E4*E7 и т. д.

Стоимость суточного выпуска тканей по видам:

C9=C7*C8 и т. д.

Целевая функция - общая стоимость за сутки, вычисляется по формуле G4=C9+D9+E9
и в процессе оптимизации должна получить максимальное значение.

Ограничения:

Затраченные ресурсы не должны превышать ресурсы, имеющиеся в наличие

F4≤C12 и т. д.

Выпуск ткани должен быть не меньше установленного плана

C7F13 и т. д.

Кол-во произведенной ткани по видам (C7 и т. д.) исчисляется целыми числами (в метрах).

Вызываем надстройку Поиск решения, заполняем соответствующие поля и выполняем поиск.

Задача №3. ( открыть файл-

Оптимизация транспортных расходов.
XLS / 569.5 Кб

/data/files/w1573667814.xls (Оптимизация транспортных расходов.) )

Производство продукции осуществляется на 4-х предприятиях, а затем развозится в 5 пунктов потребления. Предприятия могут выпускать в день 235, 175, 185 и 175 единиц продукции. Пункты потребления готовы принимать ежедневно 125, 160, 60, 250 и 175 единиц продукции. Хранение на предприятии единицы продукции обходится в 2 у. е. в день, штраф за недопоставленную продукцию— 3,5 у. е. в день. Стоимость перевозки единицы продукции (в у. е.) с предприятий в пункты потребления приведена в табл.

Необходимо минимизировать суммарные транспортные расходы по перевозке продукции.

Транспортные расходы

Предприятия	Пункты потребления
Предприятия	1	2	3	4	5
1	3,2	3	2,35	4	3,65
2	3	2,85	2,5	3,9	3,55
3	3,75	2,5	2,4	3,5	3,4
4	4	2	2,1	4,1	3,4

Проверка сбалансированности модели задачи:

модель является сбалансированной, т. к. суммарный объем производимой продукции в день равен суммарному объему потребности в ней:

235 + 175+185 + 175 = 125 + 160 + 60 + 250+ 175.

Поэтому при решении этой задачи не учитываются издержки, связанные со складированием и недопоставкой продукции.

Разместим данные в таблице

Построим математическую модель в формулах электронных таблиц.

Поставки предприятий (для предприятия 1) H4 = СУММ(C4:G4) и т.д.

Поставки по пунктам реализации (для пункта 1) C8 = СУММ(C4:C7) и т.д.

Транспортные расходы на поставку по пунктам реализации

(для пункта 1) С9 = С4*С14+С5*С15+С6*С16+С7*С17 и т.д.

Общие транспортные расходы H9 = СУММ(С9:G9)

Целевая функция - Общие транспортные расходы (ячейка H9 ) должна быть минимальной.

Ограничения :

Поставки предприятий должны быть равны их объему производства

H4 = I4 и т.д.

Поставки по пунктам реализации должны быть равны их потребности в продукции

С8 = С10 и т.д.

Объёмы перевозок не должны быть отрицательными числами

С4  0 и т.д.

Объёмы перевозок должны быть целыми величинами.

Вызываем надстройку Поиск решения, заполняем соответствующие поля.

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

IV . Подведение итогов урока.

Фронтальный опрос:

Для чего предназначена надстройка электронных таблиц Поиск решения?

Каковы основные этапы при решении оптимизационных задач?

В каких сферах деятельности человека встречаются оптимизационные задачи?

V. Домашнее задание.

Сформулировать задачу оптимизации и решить ее средствами электронных таблиц.