Решение задач оптимизации в ms excel презентация
Обновлено: 06.07.2024
Решение задач оптимизации в MS Excel ГБОУ Центр образования № 133 Невского района авт. Баринова Е.А.
Для решения задач оптимизации необходимо: Задать целевую функцию Создать математическую модель задачи Решить задачу на компьютере
Математическая модель Математическая модель – это приближенное описание какого-либо класса явлений средствами математической символики. При составлении математической модели решения задачи оптимизации искомые величины принимаются за неизвестные и составляется система неравенств, наиболее полно характеризующих решение поставленной задачи. В любую математическую модель входят две составляющие: Ограничения, которые устанавливают зависимости между переменными. Граничные условия показывают, в каких пределах могут быть значения искомых переменных в оптимальном решении.
Задача Компания производит полки для ванных комнат двух типов - А и В. Агенты по продаже считают, что за неделю на рынке может быть реализовано до 550 полок. Для каждой полки типа А требуется 2 м2 материала, для полки типа В - 3 м2 материала. Компания может получить до 1200 м2 материала в неделю. Для изготовления одной полки типа А требуется 12 мин. работы оборудования, а для изготовления одной полки типа В - 30 мин. Оборудование можно использовать 160 час. в неделю. Если прибыль от продажи полок типа А составляет 3 долл., а от полок типа В - 4 долл., то сколько полок надо выпускать в неделю, чтобы получить максимальную прибыль?
Целевая функция Очевидно, что в качестве критерия оптимизации в данном случае выступает функция прибыли. Оптимальным будет считаться тот из вариантов решения, в котором значение прибыли будет максимальным. Учитывая, что «…прибыль от продажи полок типа А составляет 3 долл., а от полок типа В - 4 долл.…» целевая функция будет выглядеть следующим образом: 3x1 + 4x2 ? max, где x1 – объем производства полок типа A x2 – объем производства полок типа B
Ограничение на объем производства: «…Агенты по продаже считают, что неделю на рынке может быть реализовано до 550 полок…» Очевидно, что совокупный объем производства полок не должен превышать 550 единиц, или, в математическом виде: x1 + x2 ? 550
Ограничение на использование оборудования: «…Для изготовления одной полки типа А требуется 12 мин. работы оборудования, а для изготовления одной полки типа В - 30 мин. Оборудование можно использовать 160 часов в неделю…» На основе этой информации можно сделать вывод, что общее время использования оборудования в рамках данного проекта не должно превышать 160 часов в неделю. Переведя время, необходимое для изготовления одной полки в часы (с целью сопоставимости единиц измерения правой и левой части неравенства) получим: 0,2x1 + 0,5x2 ? 160
Ограничение на использование материалов: «…Для каждой полки типа А требуется 2 м2 материала, для полки типа В - 3 м2 материала. Компания может получить до 1200 м2 материала в неделю…» На основе этой информации можно сделать вывод, что общее количество материала, затрачиваемого для реализации данного проекта, не должно превышать 1200 м2: 2x1 + 3x2 ? 1200
Граничные условия В качестве граничных условий в данном примере могут быть использованы следующие утверждения, вытекающие из сути поставленной задачи: Объем производства полок типа А и полок типа В – неотрицательное значение. Объем производства полок типа А и полок типа В – целое число, запишем таким образом: x1, x2 ? 0 x1, x2 – целое
Ввод условий задачи Ввод условий задачи состоит из следующих основных шагов: Создание формы для ввода данных, необходимых для последующего решения. Ввод исходных данных и зависимостей из математической модели. Указание целевой ячейки (ячейки, в которую введена целевая функция), ввод ограничений и граничных условий в диалоговом окне Поиск решения.
Создание формы для ввода данных Такая форма должна содержать возможность ввода всех данных, необходимых для решения поставленной задачи: искомых переменных; целевой функции; правой и левой части неравенств, описывающих ограничения, налагаемые на возможные варианты решения поставленной задачи.
Ввод исходных данных Отметим, что целевая функция и левые части неравенств, определяющих возможные варианты решения поставленной задачи, вводятся формулой, в которой роль искомых переменных играют адреса ячеек, зарезервированных для вывода их значений после решения задачи, а роль коэффициентов – адреса ячеек, содержащих соответственные коэффициенты.
Назначение целевой функции, ввод ограничений и граничных условий Данная стадия ввода условия задачи осуществляется в диалоговом окне Поиск решения
Назначить целевую ячейку Для этого в поле «Установить целевую ячейку:» вводится адрес ячейки, содержащей целевую функцию. Затем устанавливается направление последней – значение, к которому она должна стремиться исходя из условий задачи (минимальное, максимальное, конкретное, задаваемое пользователем). В поле «Изменяя ячейки:» ввести адреса ячеек, зарезервированных для искомых переменных.
Ввести ограничения и граничные условия Ввести ограничения и граничные условия. Для этого в диалоговом окне Поиск решения нажать на кнопку Добавить. В открывшемся диалоговом окне Добавление ограничений: в поле «Ссылка на ячейку:» ввести адрес ячейки листа, содержащей формулу для расчета показателя, используемого в качестве левой части неравенства, из списка знаков неравенств выбрать необходимый знак, в поле «Ограничение:» указать адрес ячейки, содержащей показатель, используемый в качестве правой части неравенства.
Получение результата После нажатия на кнопку Выполнить диалогового окна Поиск решения на экране появляется диалоговое окно Результаты поиска решения.
Оптимальное решение поставленной задачи полок типа А - в количестве 450 штук (В3); полок типа В – в количестве 100 штук (С3). При этом максимальная прибыль будет составлять 1720 единиц, а ресурсы используются следующим образом: потребление материала – 1200 единиц (D10); использование оборудования – 140 часов (D11).
Решение оптимизационных задач в среде электронных таблиц Excel
Решение оптимизационных задач в среде электронных таблиц Excel С помощью надстроек ЭТ можно решать самые разнообразные задачи. Поиск решения является надстройкой, которая позволяет решать задачи оптимизационного моделирования.
Решение оптимизационных задач в среде электронных таблиц Excel Для установки надстройки Поиск решения необходимо: Знак оффиса в левом верхнем углу Excel Копка «Параметры Excel» Вкладка «Надстройки» → Перейти установить флажок около пункта Поиск решения; щелкнуть на кнопке ОК.
Задача1. Пусть в колхозе требуется распределить площадь пашни между двумя культурами в соответствии со следующими данными: Заданы ресурсы производства: земли – 1800 га, человеко-дней – 8000. Величины x и y являются неизвестными и подлежат определению. Культура Площадь га Урожай, ц/га Затраты, руб./га Цена за 1 ц, руб. Затраты, человеко-дней на 1 га. 1 x 10 50 6 2 2 y 15 80 8 10
Решение оптимизационных задач в среде электронных таблиц Excel Построение математической модели задачи включает в себя: задание целевой функции (ее надо максимизировать или минимизировать); задание системы ограничений в форме линейных уравнений и неравенств; Требование неотрицательности переменных.
Решение оптимизационных задач в среде электронных таблиц Excel Решим задачу по оптимизации критерия, а именно по максимуму прибыли. Для прибыли (согласно данным таблицы) имеем формулу:
Решение оптимизационных задач в среде электронных таблиц Excel Ограничения имеют следующий вид: ограничение по площади: ограничение по человеко-дням: или Кроме того, ясно, что
Решение оптимизационных задач в среде электронных таблиц Excel Учтя все условия задачи, приходим к её математической модели неотрицательных целочисленных решений системы линейных неравенств Среди них найти такие, которые соответствуют максимуму линейной функции
Решение оптимизационных задач в среде электронных таблиц Excel Теперь заполним расчетную форму в табличном процессоре Excel. Введем: в столбец А - подписи к величинам и расчетным формулам, в столбец В – расчетные формулы (отображаются вычисленные по этим формулам значения),
Решение оптимизационных задач в среде электронных таблиц Excel В меню Данные выбираем Поиск решения и заполняем открывшееся диалоговое окно следующим образом:
Решение оптимизационных задач в среде электронных таблиц Excel Далее щелкаем в этом окне на кнопке Параметры и в открывшемся диалоговом окне Параметры поиска решения устанавливаем флажки Линейная модель и Неотрицательные значения. Щелкаем по кнопке ОК. В окне Поиск решения щелкаем на кнопке Выполнить.
Решение оптимизационных задач в среде электронных таблиц Excel Задача 2. Предполагается, что рацион коров составляется из двух видов кормов – сена и концентратов. Суточная потребность кормов на 1 корову равна 20 кормовых единиц. В таблице приведены числовые данные о себестоимости кормов в данном хозяйстве.
Решение оптимизационных задач в среде электронных таблиц Excel Найти самый дешевый рацион, если ежедневный рацион кормления сельскохозяйственных животных должен включать не менее 16 кг сена. Виды кормов Содержание кормовых единиц в 1 кг кормов Себестоимость кормов, в рублях. Сено 0,5 1,5 Концентраты 1,0 2,5
Решение оптимизационных задач в среде электронных таблиц Excel Ограничения: Целевая функция:
Решение оптимизационных задач в среде электронных таблиц Excel Задача3. Мебельная фабрика выпускает кресла двух типов. На изготовление кресла первого типа расходуется 2 м досок стандартного сечения, 0,8 м2 обивочной ткани и затрачивается 2 человеко-часа, а на изготовление кресла второго типа – соответственно 4 м, 1,25 м2 и 1,75 человеко-часа. Известно, что цена одного кресла первого типа равна 1500 рублей, второго типа – 2000 рублей. Сколько кресел каждого типа надо выпускать, чтобы стоимость выпускаемой продукции была максимальной, если фабрика имеет в наличии 4400 м досок, 1500 м2 обивочной ткани и может затратить 3200 человеко-часов рабочего времени на изготовление этой продукции?
Решение оптимизационных задач в среде электронных таблиц Excel Ограничения: Целевая функция:
Решение оптимизационных задач в среде электронных таблиц Excel Задача 4 Хозрасчетной бригаде выделено для возделывания кормовых культур 100 га пашни. Эту пашню предполагается занять кукурузой и свеклой, причем свеклой решено занять не менее 40 га. Как должна быть распределена площадь пашни по культурам, чтобы получилось наибольшее число кормовых единиц? При этом должно быть учтено следующее: 1ц кукурузного силоса содержит 0,2 кормовой единицы, 1ц свеклы – 0,26 кормовой единицы; на возделывание 1га кукурузного поля необходимо затратить 38 человеко-часов труда механизаторов и 15 человеко-часов ручного труда, а на возделывание 1га поля, занятого свеклой, соответственно 43 и 185 человеко-часов; ожидаемый урожай кукурузы – 500 ц с 1 га, а свеклы – 200 ц с 1 га; наконец, всего на возделывание кормовых культур можно затратить 4000 человеко-часов механизаторов и 15000 человеко-часов ручного труда.
Решение оптимизационных задач в среде электронных таблиц Excel Ограничения: Целевая функция:
Презентация на тему: " Решение задач оптимизации в MS Excel ГБОУ Центр образования 133 Невского района авт. Баринова Е. А." — Транскрипт:
1 Решение задач оптимизации в MS Excel ГБОУ Центр образования 133 Невского района авт. Баринова Е. А.
2 Для решения задач оптимизации необходимо : Задать целевую функцию Создать математическую модель задачи Решить задачу на компьютере
3 Математическая модель Математическая модель – это приближенное описание какого - либо класса явлений средствами математической символики. При составлении математической модели решения задачи оптимизации искомые величины принимаются за неизвестные и составляется система неравенств, наиболее полно характеризующих решение поставленной задачи. В любую математическую модель входят две составляющие : Ограничения, которые устанавливают зависимости между переменными. Граничные условия показывают, в каких пределах могут быть значения искомых переменных в оптимальном решении.
4 Задача Компания производит полки для ванных комнат двух типов - А и В. Агенты по продаже считают, что за неделю на рынке может быть реализовано до 550 полок. Для каждой полки типа А требуется 2 м 2 материала, для полки типа В - 3 м 2 материала. Компания может получить до 1200 м 2 материала в неделю. Для изготовления одной полки типа А требуется 12 мин. работы оборудования, а для изготовления одной полки типа В - 30 мин. Оборудование можно использовать 160 час. в неделю. Если прибыль от продажи полок типа А составляет 3 долл., а от полок типа В - 4 долл., то сколько полок надо выпускать в неделю, чтобы получить максимальную прибыль ?
5 Целевая функция Очевидно, что в качестве критерия оптимизации в данном случае выступает функция прибыли. Оптимальным будет считаться тот из вариантов решения, в котором значение прибыли будет максимальным. Учитывая, что «… прибыль от продажи полок типа А составляет 3 долл., а от полок типа В - 4 долл.…» целевая функция будет выглядеть следующим образом : 3x1 + 4x2 max, где x1 – объем производства полок типа A x2 – объем производства полок типа B
6 Ограничение на объем производства : «… Агенты по продаже считают, что неделю на рынке может быть реализовано до 550 полок …» Очевидно, что совокупный объем производства полок не должен превышать 550 единиц, или, в математическом виде : x1 + x2 550
7 Ограничение на использование оборудования : «… Для изготовления одной полки типа А требуется 12 мин. работы оборудования, а для изготовления одной полки типа В - 30 мин. Оборудование можно использовать 160 часов в неделю …» На основе этой информации можно сделать вывод, что общее время использования оборудования в рамках данного проекта не должно превышать 160 часов в неделю. Переведя время, необходимое для изготовления одной полки в часы ( с целью сопоставимости единиц измерения правой и левой части неравенства ) получим : 0,2x1 + 0,5x2 160
8 Ограничение на использование материалов : «… Для каждой полки типа А требуется 2 м 2 материала, для полки типа В - 3 м 2 материала. Компания может получить до 1200 м 2 материала в неделю …» На основе этой информации можно сделать вывод, что общее количество материала, затрачиваемого для реализации данного проекта, не должно превышать 1200 м 2 : 2x1 + 3x2 1200
9 Граничные условия В качестве граничных условий в данном примере могут быть использованы следующие утверждения, вытекающие из сути поставленной задачи : Объем производства полок типа А и полок типа В – неотрицательное значение. Объем производства полок типа А и полок типа В – целое число, запишем таким образом : x1, x2 0 x1, x2 – целое
10 Ввод условий задачи Ввод условий задачи состоит из следующих основных шагов : Создание формы для ввода данных, необходимых для последующего решения. Ввод исходных данных и зависимостей из математической модели. Указание целевой ячейки ( ячейки, в которую введена целевая функция ), ввод ограничений и граничных условий в диалоговом окне Поиск решения.
11 Создание формы для ввода данных Такая форма должна содержать возможность ввода всех данных, необходимых для решения поставленной задачи : искомых переменных ; целевой функции ; правой и левой части неравенств, описывающих ограничения, налагаемые на возможные варианты решения поставленной задачи.
12 Ввод исходных данных Отметим, что целевая функция и левые части неравенств, определяющих возможные варианты решения поставленной задачи, вводятся формулой, в которой роль искомых переменных играют адреса ячеек, зарезервированных для вывода их значений после решения задачи, а роль коэффициентов – адреса ячеек, содержащих соответственные коэффициенты.
13 Назначение целевой функции, ввод ограничений и граничных условий Данная стадия ввода условия задачи осуществляется в диалоговом окне Поиск решения
14 Назначить целевую ячейку Для этого в поле « Установить целевую ячейку :» вводится адрес ячейки, содержащей целевую функцию. Затем устанавливается направление последней – значение, к которому она должна стремиться исходя из условий задачи ( минимальное, максимальное, конкретное, задаваемое пользователем ). В поле « Изменяя ячейки :» ввести адреса ячеек, зарезервированных для искомых переменных.
15 Ввести ограничения и граничные условия Ввести ограничения и граничные условия. Для этого в диалоговом окне Поиск решения нажать на кнопку Добавить. В открывшемся диалоговом окне Добавление ограничений : в поле « Ссылка на ячейку :» ввести адрес ячейки листа, содержащей формулу для расчета показателя, используемого в качестве левой части неравенства, из списка знаков неравенств выбрать необходимый знак, в поле « Ограничение :» указать адрес ячейки, содержащей показатель, используемый в качестве правой части неравенства.
16 Получение результата После нажатия на кнопку Выполнить диалогового окна Поиск решения на экране появляется диалоговое окно Результаты поиска решения.
18 Оптимальное решение поставленной задачи полок типа А - в количестве 450 штук ( В 3); полок типа В – в количестве 100 штук ( С 3). При этом максимальная прибыль будет составлять 1720 единиц, а ресурсы используются следующим образом : потребление материала – 1200 единиц (D10); использование оборудования – 140 часов (D11).
Возможности электронных таблиц не
ограничиваются вычислениям по
формулам и построением диаграмм и
графиков. С помощью надстроек
электронных таблиц можно решать
оптимизационные задачи методом
подбора параметра и методом поиска
решения.
Задача
оптимизации
–
поиск
оптимального
(наилучшего)
решения
данной задачи при соблюдении некоторых
условий.
В EXCEL подобные задачи решаются с
использованием надстроек.
4. Установка надстроек:
Выбрать Сервис – Надстройки
На панели Надстройки в списке Доступные
надстройки выбрать нужные путем установки флажков
Нажать ОК
5. Вопросы:
1.
2.
3.
4.
Что такое задача оптимизации?
Приведите примеры оптимизационных
задач?
Необходимы ли специальные способы
для решения таких задач?
Как установить надстройки в EXCEL?
6. Надстройка Поиск решения – позволяет решать задачи оптимизационного моделирования.
Процедура поиска решения позволяет найти
оптимальное значение формулы,
содержащейся в ячейке, которая называется
целевой. Эта процедура работает с группой
ячеек связанных с формулой, содержащейся в
целевой ячейке. Чтобы получить искомый
результат в целевой ячейке, процедура изменяет
значения во влияющих ячейках. Для сужения
множества значений модели, применяются
ограничения.
7. При решении задач будет руководствоваться следующим алгоритмом:
1.
2.
3.
4.
5.
6.
7.
Разобрать условие задачи;
Построить математическую модель;
Выбрать поисковые переменные;
Задать ограничения;
Выбрать критерий оптимизации;
Решить задачу на компьютере;
Проанализировать полученный
результат.
8. Задача №1
Число 10 представьте в виде суммы двух
неотрицательных слагаемых так, чтобы
сумма
кубов
этих
чисел
была
наибольшей.
9. Математическая модель
1.
2.
3.
Число а (а≥0),
Число b: 10-a ≥0,
Выражение S =а³+ b³ стремится к
максимуму.
10. Поисковые переменные
11. Ограничения
12. Критерий оптимизации
Сумма кубов чисел a и b должна быть
максимальной:
S =а*а*а+ b* b* b = max
13. Решение на компьютере
1.
Заполним таблицу, указав произвольное
значение для поисковой переменной и
вычислим значение второй переменной:
2.
Найдем оптимальное решение, для этого
необходимо:
Выделить целевую ячейку С6;
Выбрать Сервис, Поиск решения;
Установить целевую ячейку, равную
максимальному значению;
Указать диапазон изменяемых ячеек;
Выбрать кнопку Добавить для записи
ограничений;
После записи ограничения нажать Добавить;
Для последнего ограничения –ОК;
Нажать кнопку Выполнить;
На новом листе Отчет по результатам 1
можно увидеть:
19. Анализ результатов
В электронных таблицах найдено
оптимальное решение:
Искомые числа а =10, b=0.
Решение задачи в EXEL
Математическое решение задачи
20. Задача №2 «Покраска пола»
Вычислить количество краски для
покрытия пола в спортивном зале.
21. Разбор условия задачи
1.
2.
Суть задачи в нахождении количества
банок краски, для этого необходимо
знать:
площадь всего зала;
какую площадь можно покрыть
содержимым одной банки.
22. Построение математической модели
Измерим длину зала – а м. (пусть 18,1 ≤ а≤18,3) и
ширину b м. (пусть 7,6 ≤ b≤7,7),
Найдем площадь зала по формуле: S=ab,
Выясним какую площадь S1, можно покрыть
содержимым одной банки (пусть меньше 10м
квадратных),
Вычислим необходимое количество банок по
формуле: n=S/S1.
23. Выбор поисковых переменных
а – длина зала,
b – ширина зала,
S1 – площадь, которую можно покрыть
одной банкой краски.
24. Ограничения
25. Критерий оптимизации
26. Решение задачи на компьютере
1.
Заполним таблицу, указав произвольные
значения для поисковых переменных:
2.
Найдем оптимальное решение, для
этого:
Выделить целевую ячейку С7;
Выбрать Сервис, Поиск решения;
Установить целевую ячейку, равную
минимальному значению;
Указать диапазон изменяемых ячеек;
Выбрать кнопку Добавить для записи
ограничений;
После записи ограничения нажать Добавить;
Для последнего ограничения –ОК;
Нажать кнопку Выполнить;
На новом листе Отчет по результатам 1
можно увидеть:
32. Анализ результатов
В электронных таблицах найдено
оптимальное решение:
для покраски пола в актовом зале
необходимо не более 14 банок.
Решение задачи в EXCEL
33. Вопросы
1.
2.
3.
4.
5.
Какие задачи можно решать используя
надстройку Поиск решения?
Перечислите этапы решения задач при работе с
надстройкой Поиск решения?
Можно ли в целевой ячейке записать какое-либо
значение, а не формулу?
Какие возможности дает надстройка Поиск
решения?
Где могут пригодиться функции надстройки
Поиск решения?
34. Надстройка Подбор параметра – изменяет значение в одной ячейке до тех пор, пока формула, зависимая от этой ячейки, не возвратит
нужный результат.
Подбор параметра является частью блока
задач, который иногда называют
инструментами анализа «что-если»
(процесс изменений значений ячеек и
анализ влияний этих изменений на
результат вычислений формул)
35. Задача №3
36. Математическая модель
1.
2.
Для решения подобных уравнений
действуют по следующему алгоритму:
составляют таблицу значений функции
у= х³-sinx-0,5;
Строят график, который позволит
определить значение аргумента х при
у=0.
Построим таблицу значений функции: у=
х³-sinx-0,5 на интервале от -1,5 до 1,5 с
шагом 0,5;
По графику приближенно можно
определить, что корень уравнения х≈1
40. Методом подбора параметра вычислим значение х с точностью до 5 знаков после запятой:
2. Установим значение функции у=0
изменяя значение аргумента
Нажмем ОК и на панели Результат
подбора параметра будет выведена
информация о величине подбираемого и
подобранного значений, а в таблице
изменятся значения аргумента и функции
В ячейке G2 появится искомое значение
аргумента, с заданной точностью
х=1,11854
Решение в EXCEL
44. Задача №4
Заведующий больницей должен составить штатное
расписание: сколько сотрудников, на какие должности и с
каким окладом принять на работу. Общий месячный фонд
зарплаты составляет 10000 у.е. Известно, что для нормальной
работы больницы нужно 5 — 7 санитарок ,8—10 медсестер,
10—12 врачей, 1 зав. Аптекой, 3 зав. Отделениями, 1 главный
врач, 1 завхоз, 1 зав. Больницей. Совет решил, беря за основу
оклад санитарки, что медсестра должна получать в 1,5 раза
больше санитарки врач в 3 раза больше санитарки; зав.
отделением — на 30 у.е. больше, чем врач; зав. аптекой — в
2 раза больше санитарки; завхоз — на 40 у.е. больше
медсестры; главный врач — в 4 раза больше санитарки; зав.
больницей — на 20 у.е. больше главного врача. Составьте
штатное расписание больницы.
45. Математическая модель
Так как за основу взять оклад санитарки,
тогда рассчитаем зарплаты сотрудников
по следующей формуле: АС + В, где С —
оклад санитарки, А и В — коэффициенты,
которые определены решением совета.
Для медсестры А=1,5, В=0, и т.д.
Необходимо уложиться в фонд зарплаты,
изменяя оклад санитарки.
Заполним следующую таблицу, установив
значение оклада санитарки 150 у.ед.:
47. Заполним столбцы D, E, F
48. Используя Сервис – Подбор параметра, установим значение фонда заработной платы равным 10 000 у.ед., изменяя оклад санитарки
49. Получим следующее штатное расписание:
Изменяя количество сотрудников, можно
составить несколько вариантов штатного
расписания
Решение в EXCEL
51. Вопросы
1.
2.
3.
4.
Какие задачи можно решать используя
надстройку Подбор параметра?
Какие возможности дает надстройка
Подбор параметра?
Где могут пригодиться функции
надстройки Подбор параметра?
Пригодится ли вам и где материал
данной презентации?
Помогите другим пользователям — будьте первым, кто поделится своим мнением об этой презентации.
Аннотация к презентации
Смотреть презентацию онлайн с анимацией на тему "Решение задач оптимизации в MS Excel" по информатике. Презентация состоит из 18 слайдов. Для учеников 10-11 класса. Материал добавлен в 2016 году. Средняя оценка: 4.0 балла из 5.. Возможность скчачать презентацию powerpoint бесплатно и без регистрации. Размер файла 1.44 Мб.
Содержание
Решение задач оптимизации в MS Excel
ГБОУ Центр образования № 133 Невского района авт. Баринова Е.А.
Для решения задач оптимизации необходимо:
Задать целевую функцию Создать математическую модель задачи Решить задачу на компьютере
Математическая модель
Математическая модель – это приближенное описание какого-либо класса явлений средствами математической символики. При составлении математической модели решения задачи оптимизации искомые величины принимаются за неизвестные и составляется система неравенств, наиболее полно характеризующих решение поставленной задачи. В любую математическую модель входят две составляющие: Ограничения, которые устанавливают зависимости между переменными. Граничные условия показывают, в каких пределах могут быть значения искомых переменных в оптимальном решении.
Задача
Компания производит полки для ванных комнат двух типов - А и В. Агенты по продаже считают, что за неделю на рынке может быть реализовано до 550 полок. Для каждой полки типа А требуется 2 м2 материала, для полки типа В - 3 м2 материала. Компания может получить до 1200 м2 материала в неделю. Для изготовления одной полки типа А требуется 12 мин. работы оборудования, а для изготовления одной полки типа В - 30 мин. Оборудование можно использовать 160 час. в неделю. Если прибыль от продажи полок типа А составляет 3 долл., а от полок типа В - 4 долл., то сколько полок надо выпускать в неделю, чтобы получить максимальную прибыль?
Целевая функция
Очевидно, что в качестве критерия оптимизации в данном случае выступает функция прибыли. Оптимальным будет считаться тот из вариантов решения, в котором значение прибыли будет максимальным. Учитывая, что «…прибыль от продажи полок типа А составляет 3 долл., а от полок типа В - 4 долл.…» целевая функция будет выглядеть следующим образом: 3x1 + 4x2 max, где x1 – объем производства полок типа A x2 – объем производства полок типа B
Ограничение на объем производства:
«…Агенты по продаже считают, что неделю на рынке может быть реализовано до 550 полок…» Очевидно, что совокупный объем производства полок не должен превышать 550 единиц, или, в математическом виде: x1 + x2 550
Ограничение на использование оборудования:
«…Для изготовления одной полки типа А требуется 12 мин. работы оборудования, а для изготовления одной полки типа В - 30 мин. Оборудование можно использовать 160 часов в неделю…» На основе этой информации можно сделать вывод, что общее время использования оборудования в рамках данного проекта не должно превышать 160 часов в неделю. Переведя время, необходимое для изготовления одной полки в часы (с целью сопоставимости единиц измерения правой и левой части неравенства) получим: 0,2x1 + 0,5x2 160
Ограничение на использование материалов:
«…Для каждой полки типа А требуется 2 м2 материала, для полки типа В - 3 м2 материала. Компания может получить до 1200 м2 материала в неделю…» На основе этой информации можно сделать вывод, что общее количество материала, затрачиваемого для реализации данного проекта, не должно превышать 1200 м2: 2x1 + 3x2 1200
Граничные условия
В качестве граничных условий в данном примере могут быть использованы следующие утверждения, вытекающие из сути поставленной задачи: Объем производства полок типа А и полок типа В – неотрицательное значение. Объем производства полок типа А и полок типа В – целое число, запишем таким образом: x1, x2 0 x1, x2 – целое
Ввод условий задачи
Ввод условий задачи состоит из следующих основных шагов: Создание формы для ввода данных, необходимых для последующего решения. Ввод исходных данных и зависимостей из математической модели. Указание целевой ячейки (ячейки, в которую введена целевая функция), ввод ограничений и граничных условий в диалоговом окне Поиск решения.
Создание формы для ввода данных
Такая форма должна содержать возможность ввода всех данных, необходимых для решения поставленной задачи: искомых переменных; целевой функции; правой и левой части неравенств, описывающих ограничения, налагаемые на возможные варианты решения поставленной задачи.
Ввод исходных данных
Отметим, что целевая функция и левые части неравенств, определяющих возможные варианты решения поставленной задачи, вводятся формулой, в которой роль искомых переменных играют адреса ячеек, зарезервированных для вывода их значений после решения задачи, а роль коэффициентов – адреса ячеек, содержащих соответственные коэффициенты.
Назначение целевой функции, ввод ограничений и граничных условий
Данная стадия ввода условия задачи осуществляется в диалоговом окне Поиск решения
Назначить целевую ячейку
Для этого в поле «Установить целевую ячейку:» вводится адрес ячейки, содержащей целевую функцию. Затем устанавливается направление последней – значение, к которому она должна стремиться исходя из условий задачи (минимальное, максимальное, конкретное, задаваемое пользователем). В поле «Изменяя ячейки:» ввести адреса ячеек, зарезервированных для искомых переменных.
Ввести ограничения и граничные условия
Ввести ограничения и граничные условия. Для этого в диалоговом окне Поиск решения нажать на кнопку Добавить. В открывшемся диалоговом окне Добавление ограничений: в поле «Ссылка на ячейку:» ввести адрес ячейки листа, содержащей формулу для расчета показателя, используемого в качестве левой части неравенства, из списка знаков неравенств выбрать необходимый знак, в поле «Ограничение:» указать адрес ячейки, содержащей показатель, используемый в качестве правой части неравенства.
Получение результата
После нажатия на кнопку Выполнить диалогового окна Поиск решения на экране появляется диалоговое окно Результаты поиска решения.
Решение найдено
Оптимальное решение поставленной задачи
полок типа А - в количестве 450 штук (В3); полок типа В – в количестве 100 штук (С3). При этом максимальная прибыль будет составлять 1720 единиц, а ресурсы используются следующим образом: потребление материала – 1200 единиц (D10); использование оборудования – 140 часов (D11).
Читайте также: