Информатика "Поиск решения" 1 курс СПО
план-конспект урока

Тема

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

Цель

Уметь применять средство «Поиск решения»

Объект работы

Электронная таблица

Материалы и инструменты

Персональный компьютер с установленным MS Excel

Задание

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

Задача: Цех молокозавода выпускает эскимо и другой вид мороженого (просто «мороженое»). Эскимо в 2 раза дороже мороженого. За одну минуту выпускается 90 порций мороженого или 30 порций эскимо, возможен одновременный выпуск двух видов продукции. Из-за ограничения срока реализации продукции и недостаточного объема холодильных камер в течение часа на хранение может быть принято не более 3600 шт. изделий.

Определить наибольшую стоимость выпускаемой продукции (прибыль) и оптимальный план выпуска мороженого и эскимо за одну минуту.

1. Подготовка к работе

1.1. Анализ условия задачи.

1.2. Составление математической модели

1.3. Запуск MS Excel. Создайте новую книгу MS Excel в Вашей папке «ПР 13»

Дано:

Пусть одновременно выпускается 2 вида продукции.

Обозначим число выпускаемых за 1 мин. эскимо – х, мороженого – у.

Пусть:         t1 – время, необходимое для производства одного эскимо, t2 - время, необходимое для производства одного мороженого.Из условия задачи следует, что за 1 мин. производится 90 порций мороженого или 30 порций эскимо, т.о. времени на производство одного эскимо затрачивается в 3 раза больше, чем на производство одного мороженого: t1=3 t2

t1х + t2у≤1        t1=3 t2 получим 3t2х + t2у≤1, 3х+у≤1/t2

1/t2  равна 90 шт.

3х+у≤ 90

за одну минуту  3600/60=60 порций:    х+у≤60 1 эскимо - с1 (руб.), а цену мороженого – с2 (руб.)

с1=2с2

Общая стоимость продукции, выпускаемой цехом за 1 минуту:

S= с1х+ с2у,    заменяя с1=2с2         получим  S=2с2х+ с2у= с2(2х+у)

Поскольку с2 – заданная положительная константа, то для упрощения задачи можно принять с2=1.

По условию задачи необходимо найти наибольшую возможную стоимость выпускаемой продукции. Т.о. следует добиваться максимального значения целевой функции S=2х+у.

среди целочисленных решений системы линейных неравенств необходимо найти такое, при котором достигается максимизация линейной функции S=2х+у.

2. Создание таблицы

1. Создайте новую книгу MS Excel в Вашей папке «ПР 13»
2. Оформите лист по образцу (см рис.):
3. В описанной модели необходимо максимизировать значение в ячейке В11. В качестве начальных значений х и у принимаются нули.
4. Ограничения задачи представлены в таблице

3 Выбор поиска решения

• Запуск средства «Поиск решения».
•  Выбрать меню Сервис
•  Выбрать Поиск решения
• В поле «Установить целевую ячейку» уже находится ссылка на выделенную на предыдущем шаге ячейку (при необходимости эту ссылку можно изменить).

• Установить переключатель «Равной» максимальному значению (ищется максимальное значение целевой ячейки В11). Перейдите в поле «Изменяя ячейки:» и укажите диапазон ячеек, которые должны изменяться в процессе поиска наилучшего решения. В данном примере это ячейки $B$4:$B$5.

• Щелкните по кнопке «Добавить», чтобы ввести первое ограничение задачи. Откроется диалоговое окно «Добавление ограничения»
• Введите первое ограничение $B$4:$B$5>=0

• Щелкните по кнопке «Добавить», введите следующее ограничение и т.д. из таблицы на предыдущей странице. Примечание: для задания целочисленности значений ячеек В4 и В5 из второго раскрывающегося списка выберите «цел», при этом в поле «Ограничение» автоматически появится «целое». После ввода последнего ограничения  нажмите ОК.

• Нажав на кнопку «Параметры» можно проверить, и при необходимости, изменить условия и варианты поиска решения, что в нашей задаче не требуется.
• Нажмите кнопку «Найти решение». По окончании поиска решения появится диалоговое окно результатов:

1. Установите «Сохранить найденное решение», чтобы сохранить  предложенные значения. С помощью этого диалогового окна можно также сформировать отчет. Нажмите ОК. Получится решение:

4. Результаты решения

Просмотр результатов решения. Сохранение файла

Распечатка таблицы Закрыть MS Excel

Появится имя файла

Распечатанная таблица

5. Закрепление материала

Дополнительные задания к этой задаче

1. Предположим, что цех получил дополнительную холодильную установку, холодильник может принять не 60, а 110 порций продукции за 1 минуту. Определите,  поможет ли ликвидация этого «узкого места» увеличить прибыль.
2. Увеличить прибыль можно ничего не изменяя в организации производства, а просто увеличив цены на продукцию. Например, пусть цена эскимо будет не в два, а в 2,5 раза больше, чем цена мороженого. Предполагаемая прибыль увеличится, но найдет ли спрос более дорогая продукция?

Открыть файл

Самостоятельно решить задания.