Решение оптимизационных задач в среде электронных таблиц Excel
план-конспект урока по информатике и икт (11 класс)

Интегрированный урок в 11 классе

(информационные технологии – экономика, математика)

Тема урока: «Решение оптимизационных задач в среде электронных таблиц Excel»

Учитель Боженкова Елена Александровна, МБОУ «СОШ №6» г.Сафоново Смоленской области

Предмет  Информатика

Класс     11 класс  ФГОС

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

Цель урока: 

• Образовательные: изучение возможностей MS Excel по решению оптимизационных задач и практическое освоение соответствующих навыков и умений.  

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

Задачи:

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

Оборудование: Пакет программ MS Office (MS Excel, MS Power Point), 11 компьютеров, мультимедиа проектор, интерактивная доска.

Методы обучения: Беседа, сравнение, постановка и решение учебных проблем, самостоятельная работа.

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

Тип урока: комбинированный – урок повторения,  изучения нового материала и практического закрепления полученных знаний, умений и навыков.  

Вид урока: сдвоенный, продолжительность1 час 20 минут.

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

I.  Этап.  Организационный.  Приветствие. Проверка готовности учащихся к уроку.

II. Этап. Актуализация знаний и умений, постановка проблемы 

Учитель. Сегодня на уроке мы продолжим тему информационное моделирование.   Нам предстоит решить важные экономические задачи  из разных сфер нашей жизни с помощью построения математической и компьютерной моделей. Поэтому повторим  основные этапы моделирования.

Ответы учеников.

 1 этап. Постановка задачи (описание задачи, цель моделирования, анализ объекта).

 2 этап. Разработка математической модели (информационная модель, знаковая модель).

 3 этап. Компьютерный эксперимент (план моделирования, технология моделирования).

 4 этап. Анализ результатов моделирования.

Ход урока

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

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

Из словаря – это процесс выбора наилучшего варианта из возможных.

В математике - математическое программирование занимается математическими методами решения задач нахождения наилучших вариантов из всех возможных.

Оптимизация  экономике -  выбор наилучшего варианта решения из множества возможных;  процесс приведения системы в наилучшее состояние.

В информатике  — модификация системы для улучшения её эффективности. Система может быть одиночной компьютерной программой, цифровым устройством, набором компьютеров или даже целой сетью, такой как Интернет.

Модификация — преобразование, видоизменение чего-либо с приобретением новых свойств.

Обращаем внимание на формулировки и делаем выводы общности процессов в разных сферах нашей жизни.

Учитель. В сфере управления сложными системами (например, в экономике) применяется оптимизационное моделирование, в процессе которого осуществляется поиск наиболее оптимального пути развития системы. Проблемы оптимизации присутствуют в самых различных процессах — это распределение ресурсов,  управление запасами, перевозка грузов, прибыль, расходы и др.

Учитель: Вы уже знакомы со многими функциями, которые имеются в программе электронных таблиц MS Excel. Но следует отметить, что возможности современных электронных таблиц не ограничиваются вычислениями по формулам и построением диаграмм и графиков. С помощью надстроек ЭТ можно решать самые разнообразные задачи, в том числе и оптимизационные задачи.

А теперь попробуйте сформулировать тему урока если в ее названии есть слова: задача, оптимизация, MS Excel.

(Формулируем тему и фиксируем ее в тетрадях. Озвучиваем цели и задачи урока.)

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

• выбрать команду Сервис, Надстройки;
• установить флажок около пункта Поиск решения;
• щелкнуть на кнопке ОК.

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

Учитель.   При решении оптимизационных задач в экономике можно выделить основные этапы.

1.  Постановка задачи, т. е. ее содержательная формулировка с точки зрения достижения цели и накладываемых ограничений.

2.  Построение математической модели,  

3.  Проверка модели и полученного с ее помощью решения.  

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

Эти этапы очень похожи на этапы информационного моделирования, с которыми вы же знакомы.

III.  Этап.   Объяснение нового материала

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

Построим модель решения этой задачи

1 этап. Постановка задачи

Цель моделирования

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

(Текст задачи раздается на карточках) Пусть в колхозе требуется распределить площадь пашни между двумя культурами в соответствии со следующими данными:

Кроме того, заданы ресурсы производства:

земли – 1800  га, человеко-дней – 8000.

Величины x и y являются неизвестными и подлежат определению.

2 этап. Разработка модели (Проводим совместные рассуждения по заданию целевой функции, фиксируем в тетради)

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

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

Решим задачу по оптимизации критерия, а именно по максимуму прибыли.

Ограничения задачи имеют следующий вид:

ограничение по площади: ;                                                       (1)

ограничение по человеко-дням: , или         (2)

Кроме того, ясно, что ,                                                                  (3)

Для прибыли (согласно данным таблицы) имеем формулу:

.                                                  (4)

Учтя все условия задачи, приходим к её математической модели неотрицательных целочисленных решений системы линейных неравенств

найти такое, которое соответствует максимуму линейной функции .

3 этап. Компьютерный эксперимент

Выполнение практического задания  (Компьютерная модель)

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

Теперь начинаем практический этап решения задачи. Заполним расчетную форму в табличном процессоре E xcel.

Введем:   в столбец А - подписи к величинам и расчетным формулам,

                 в столбец В – расчетные формулы (отображаются вычисленные по этим формулам значения),

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

Далее щелкаем в этом окне на кнопке Параметры и в открывшемся диалоговом окне Параметры поиска решения устанавливаем флажки Линейная модель и Неотрицательные значения. Щелкаем по кнопке ОК.

В окне Поиск решения щелкаем на кнопке Выполнить.

Оптимальное решение найдено:

4 этап. Анализ результатов моделирования

Просим учеников сделать вывод.    Таким образом,  x = 1250 (га),     y = 550 (га) -  искомый оптимальный вариант распределения площади пашни между двумя культурами, при котором достигается максимальная прибыль, и выполняются все условия задачи.

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

IV.   Практическая работа по закреплению изученного материала.

Учащимся предлагается самостоятельно решить задачу.

Задача 2.

Предполагается, что рацион коров составляется из двух видов кормов – сена и концентратов. Суточная потребность кормов на 1 корову равна 20 кормовых единиц. В таблице приведены числовые данные о себестоимости кормов в данном хозяйстве.

Найти самый дешевый рацион, если ежедневный рацион кормления сельскохозяйственных животных должен включать не менее 16 кг. сена.

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

Целевая функция:

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

Рефлексия.    Я предлагаю вам выразить своё отношение к полученным знаниям.

Я с пользой и хорошо работал на уроке.

Я принимал в нем участие.

Я не очень понимал, о чем идет речь.

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

Мебельная фабрика выпускает кресла двух типов. На изготовление кресла первого типа расходуется 2 м досок стандартного сечения, 0,8 м2 обивочной ткани и затрачивается 2 человеко-часа, а на изготовление кресла второго типа – соответственно 4 м, 1,25 м2 и 1,75 человеко-часа. Известно, что цена одного кресла первого типа равна 1500 рублей, второго типа – 2000 рублей. Сколько кресел каждого типа надо выпускать, чтобы стоимость выпускаемой продукции была максимальной, если фабрика имеет в наличии 4400 м досок, 1500 м2 обивочной ткани и может затратить 3200 человеко-часов рабочего времени на изготовление этой продукции?

VI. Подведение итогов.  Выставление оценок