| 12+ Свидетельство СМИ ЭЛ № ФС 77 - 70917 Лицензия на образовательную деятельность №0001058 |
Пользовательское соглашение Контактная и правовая информация |
 | Алексеева Римма Юрьевна47 Россия, Пензенская обл., Пенза |
Компьютерное моделирование прикладных задач в электронных таблицах. Решение оптимизационных задач с помощью надстройки "Поиск решения".
Лицей современных технологий управления №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 – целые
X10 X20 X30
Реализуем математическую модель средствами электронных таблиц
Вызываем надстройку Поиск решения и заполняем соответствующие поля
После команды Выполнить получим результат
Проведем анализ полученного результата, проверим, все ли условия задачи выполняются.
Задача №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 и т. д.
Выпуск ткани должен быть не меньше установленного плана
C7F13 и т. д.
Кол-во произведенной ткани по видам (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. Домашнее задание.
Сформулировать задачу оптимизации и решить ее средствами электронных таблиц.
