Разработка урока «Задачи оптимизационного моделирования в MS Excel»
методическая разработка по информатике и икт (10 класс) на тему

Государственное бюджетное образовательное учреждение

средняя общеобразовательная школа №90

Выборгского района Санкт-Петербурга

Разработка  урока

Тема урока: 

«Задачи оптимизационного моделирования в MS Excel»

Категория обучаемых: 10 - 11 классы

Требуемый объем часов: 3 урока по 45 минут

Разработчик                                                      Журавлева Е.А.

Санкт - Петербург  

2014

Данная разработка урока имеет большое значение в курсе «Информатики», так как она не только демонстрирует возможности информационных технологий – электронных таблиц, но и иллюстрирует моделирование экономических процессов.

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

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

Для закрепления навыков практической работы с оптимизационными задачами учащимся предлагаются несколько задач разного уровня сложности. Тем, кто недостаточно хорошо усвоил решение данного типа задач, предлагается ЗАДАЧА1, в которой рассматривается математическая модель этой задачи, остается только решить ее на компьютере. Для решения других задач учащимся необходимо самостоятельно разработать математическую модель и решить задачу на компьютере.

В заключении урока учащиеся знакомятся с новым понятием из раздела математики – линейным программированием.

Изучаемые понятия:

• Оптимизационное моделирование;
• Алгоритм решения задач на оптимизацию;
• Надстройка «Поиск решения» в Excel.

Цель урока:

1.  Получение представления о решении задач на оптимизацию,
2. Получение навыков работы с надстройкой «Поиск решения» в Excel,
3.  Закрепление этих знаний на практике,
4.  Выработка навыков работы с оптимизационными задачами в Excel.

Форма урока:

1. Комбинированный (лекция и практическая работа за компьютером).

Материалы:

1.  Приложение 1. Текст конспекта.
2.  Приложение 2. Решение задачи на компьютере.
3.  Приложение 3. Задания учащимся для индивидуальной работы.

Процедура проведения урока

1. Актуализация знаний (повторение пройденного материала в форме диалога и опроса на оценку):

1. Назначение MS Excel;
2. Примеры типов задач, решаемых на предыдущих уроках;
3. Адресация ячеек;
4. Диапазон ячеек;
5. Ввод данных.

2. Введение в новый материал:

Табличный процессор MS Excel предоставляет пользователю большие возможности при решении различных расчетных задач. В нем имеется надстройка «Поиск решения», которая позволяет решать задачи отыскания наибольших и наименьших значений (наилучших) при заданных ограничениях.

3. Лекционная подача материала с конспектированием основных понятий (см. Приложение 1).

4. Совместное решение задачи на компьютере (см. Приложение 2).

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

6. Закрепление пройденного материала (в форме  общего обсуждения):

• Что мы понимаем под оптимизацией при решении определенного типа задач?
• Какую возможность табличного процессора MS Excel мы использовали при решении задачи на оптимизацию?
• Что необходимо задать, чтобы решить задачу с использованием надстройки «Поиск решения»?
• Можно ли добиться улучшения целевой функции, если – да, то как?

7. Заключение.

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

Приложение 1

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

Оптимизационное моделирование – это поиск оптимального, т.е. наилучшего решения конкретной задачи при выполнении некоторых заданных условий.

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

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

1. Разобрать условие задачи.
2. На основе исходных данных построить математическую модель задачи:

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

3. Решить задачу на компьютере с помощью программы MS Excel.
4. Проанализировать полученные данные.

Перед началом работы в MS Excel необходимо убедиться, что надстройка «Поиск решения» установлена.

В Excel 2003: в меню «Сервис» имеется пункт «Поиск решения». Если его нет, нужно установить эту надстройку: в меню «Сервис» – «Надстройки» –  устанавливаем флажок «Поиск решения».

В Excel 2007:  «Данные» - «Анализ» -  «Поиск решения». Если его нет, нужно установить. Чтобы активизировать ее в Excel 2007, щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel, а затем выберите категорию Надстройки. В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти. В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК.

 Рассмотрим решение задачи на оптимизацию на конкретном примере.

Задача.

Цех молокозавода выпускает эскимо и другой вид мороженого (назовем его просто «мороженое»).

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

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

Построим математическую модель решения данной задачи.

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

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

Пусть:         t1 – время, необходимое для производства одного эскимо,

                t2 - время, необходимое для производства одного мороженого.

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

За 1 мин. соотношение времени при одновременном выпуске каждого из двух видов продукции х и у составит:

        t1х + t2у≤1                или, подставляя t1=3 t2                 получим 3t2х + t2у≤1

вынесем t2 за скобки и разделим на него левую и правую части уравнения, т.о.                3х+у≤1/t2

Но величина 1/t2 – это максимальный выпуск мороженого за 1 мин., т.е. она равна 90.

Итак, возможности производства определяет условие:  3х+у≤ 90

Еще одно условие  - ограниченная емкость холодильника. В течение 1 часа холодильник может принять 3600 шт. продукции, т.е. за одну минуту

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.
2. В новой рабочей книге оформите лист в соответствии с рисунком 1

В описанной модели необходимо максимизировать значение в ячейке В11. В качестве начальных значений х и у принимаются нули.

Ограничения задачи представлены в таблице 1:

Таблица 1

Дальнейшее решение задачи будем осуществлять с помощью надстройки «Поиск решения».

3. Выделите ячейку с оптимизируемым значением В11.
4. Выберите  надстройку  Поиск решения. Загрузится надстройка и появляется диалоговое окно «Поиск решения» (рис.2)

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

9. Введите первое ограничение $B$4:$B$5>=0
10. Щелкните по кнопке «Добавить», введите следующее ограничение и т.д. из таблицы на предыдущей странице.

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

11. После ввода последнего ограничения  нажмите ОК.

Окно поиск решения примет вид (рис.4)

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

12. Нажмите кнопку «Выполнить». По окончании поиска решения появится диалоговое окно результатов (рис.5).

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

Рисунок 6

15. Сохраните решение задачи в своей папке под именем МОЛОКОЗАВОД.

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

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

Приложение 3

Задача №1.

Представьте, что вас выбрали директором завода и вы, изучив спрос, решили организовать участок для производства двух товаров широкого потребления – мясорубки и скороварки (А и Б).

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

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

Всякий хороший директор стремиться к тому, чтобы прибыль была наибольшей.

Условие задачи.

На участке работает 20 человек, каждый из них в среднем за год работает 1800 часов. Выделенные ресурсы: 32 т металла, 54 тыс. квт.ч. электроэнергии.

План по реализации: не менее 2 тыс. изделий  А и не менее 3 тыс. изделий Б.

На выпуск 1 тыс. изд. А затрачивается 3 т металла, 3 тыс. квт.ч. электроэнергии и 3 тыс. часов рабочего времени.

На выпуск 1 тыс. изд. Б -  1 т металла, 6 тыс. квт.ч. электроэнергии и 3 тыс. часов рабочего времени.

От реализации 1 тыс. изд. А завод получает прибыль 50 тыс. руб., а от реализации 1 тыс. изд. Б – 70 тыс. руб.

Выпуск какого количества изделий А и Б (в тыс. шт.) надо запланировать, чтобы прибыль от их реализации была наибольшей.

Математическая модель данной задачи..

Пусть        х – планируемое количество изделий А (тыс. шт.)

                у – планируемое количество изделий Б(тыс. шт.).

План по реализации:                                        х≥2  и  у≥3

Общий расход металла:                                        3х+у ≤ 32

Общий расход электроэнергии                                х+6у ≤ 54

Ограничение на ресурсы рабочего времени                3х+3у ≤ 36

Прибыль от реализации                50х+70у

ЗАДАНИЕ

1. Запустите MS Excel.
2. На основе разработанной математической модели введите в новой рабочей книге все необходимые данные.
3. С помощью команды Сервис – Поиск решения загрузите надстройку. С помощью диалогового окна «Поиск решения» получите требуемые результаты и сохраните найденное решение.
4. Сохраните решение задачи в своей папке под именем УЧАСТОК.

Задача №2.

Какие размеры должен иметь бак объемом V=abh=2000 см3, чтобы на его изготовление пошло как можно меньше материала? Сторона а должна быть не меньше 10 см.

1. Самостоятельно разработайте математическую модель данной задачи.
2. Запустите MS Excel.
3. На основе разработанной математической модели введите в новой рабочей книге все необходимые данные.
4. С помощью команды Сервис – Поиск решения загрузите надстройку. С помощью диалогового окна «Поиск решения» получите требуемые результаты и сохраните найденное решение.
5. Сохраните решение задачи в своей папке под именем БАК

Задача №3.

Фирма производит две модели А и Б сборных книжных полок. Их производство ограничено наличием сырья (высококачественных досок) и временем машинной обработки. Для каждого изделия модели А требуется 3 м2 досок, а для изделия модели Б – 4 м2. Фирма может получать от своих поставщиков до 1700 м2 досок в неделю. Для каждого изделия модели А требуется 12 мин. машинного времени, а для изделия модели Б – 30 мин. В неделю можно использовать 160 ч машинного времени. Сколько изделий каждой модели следует выпускать фирме в неделю, если каждое изделие модели А приносит 2$ прибыли, а каждое изделие модели Б – 4$ прибыли?

1. Самостоятельно разработайте математическую модель данной задачи.
2. Запустите MS Excel.
3. На основе разработанной математической модели введите в новой рабочей книге все необходимые данные.
4. С помощью команды Сервис – Поиск решения загрузите надстройку. С помощью диалогового окна «Поиск решения» получите требуемые результаты и сохраните найденное решение.
5. Сохраните решение задачи в своей папке под именем ПОЛКИ.