Урок по теме "Информационное моделирование"

Урок по теме «Информационное моделирование», 11 класс

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

(Практическая работа)

Использовались технологии личностно-ориентированного обучения и информационно-коммуникативные

Тема урока: Решение экономических (оптимизационных) задач в среде ЭТ Excel.

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

Тип урока: комплексного применения знаний и умений (урок закрепления).

Задачи урока:

§   обучающая

–  закрепление знания общих принципов работы табличного процессора MS Excel и умения составить алгоритм для решения конкретной задачи;

–  научить учащихся решать оптимизационные задачи в среде электронных таблиц MS Excel;

– формирование представления о вычислениях в электронных таблицах как важной, удобной и широко применяемой на практике;

§   развивающая

– развитие навыков индивидуальной и групповой практической работы;

– развитие способности логически рассуждать, делать эвристические выводы;

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

§   воспитательная

–  воспитание творческого подхода к работе, желания экспериментировать;

– развитие познавательного интереса, воспитание информационной культуры;

– профессиональная ориентация и подготовка к дальнейшему самообразованию к будущей трудовой деятельности.

Форма проведения урока: работа в группах, индивидуальная работа.

Программное и техническое и дидактическое обеспечение урока:

– мультимедийный проектор;

– компьютерный класс 8+1 (локальная сеть);

– программа MS Excel;

– файл с задачами для практической работы и технологической картой с описанием примерного алгоритма работы.

Ход урока

1. Организационный этап.

Приветствие. Проверка присутствующих.

2. Проверка домашнего задания.

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

Учащимся предлагается пройти короткий тест (5 вопросов, выбор вопросов случайный из 10 имеющихся) с использованием программы «MyTest». В тест включены вопросы по оптимальному планированию и вопросы по работе в программе MS Excel. Для экономии времени урока используется функция автоматического сбора результатов работы программы «MyTest по локальной сети. (см. Приложение 1).

3. Мотивация учебной деятельности учащихся. Актуализация знаний.

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

Обсуждаем вопросы:

Что такое задача линейного программирования? Почему она так называется?

Что такое ресурсы? Что такое оптимальный план?

Для чего нужно средство MS Excel –«Поиск решения»? Как эта надстройка устанавливается?

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

Просматриваем образец решения задачи на проекционном экране.

4. Постановка цели и задач урока.

Рассмотрим решение задачи линейного программирования с использованием указанной возможности. Действуем согласно алгоритму:

1.      Усвоить условие задачи;

2.      Построить математическую модель:

a.    определить изменяемые (поисковые) переменные;

b.      задать ограничения;

c.       выбрать целевую функцию;

3.      Решить задачу в программе Excel, с помощью средства «Поиск решения»

4.      Проанализировать полученные данные;

5.      Показать результат – учителю;

6.      Установить пароль на открытие файла и сохранить в своей папке.

Рассылаю по локальной сети файл с заданием и технологической картой на Рабочие столы учеников (см. Приложение 2), приступаем к работе. По необходимости – консультирую.

Ученики, решившие свои задачи, помогают (по определенным правилам) одноклассникам закончить свои задачи.

ФИЗКУЛЬТМИНУТКА

5. Информация о домашнем задании и инструктаж по его выполнению.

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

6. Рефлексия (подведение итогов)

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

Выставление предварительных и окончательных оценок в электронный журнал.

Приложение 1

Тест по теме: «ЭТ Excel и опт. планирование»

1. Назовите категорию функций MS Excel содержащих формулы для корреляционного анализа данных:

1.      Финансовые

2.      Математические

3.      Дата и время

4.      Статистические

2. Как  в программе MS Excel 2007 включить средство «Поиск решения»

1.      Файл - параметры-надстройки- управление-надстройки - поиск решения

2.      Кнопка MS Office - параметры Excel - Надстройки - Управление-Надстройки Excel -  перейти - поставить флажок Поиск решения

3.      Разработчик-Пакеты расширения- поставить флажок Поиск решения

4.      Кнопка MS Office - параметры Excel-Дополнительно-Формулы- поставить флажок Поиск решения

3. Выберите неправильную формулу для ячейки MS Excel:

1.      =5+sin(5)

2.      =A2^2+2

3.      –A10-10

4.      A1+sin(A1)

4. Выберите неправильную адресацию к ячейкам MS Excel:

1.      N11

2.      $N11

3.      N11$

4.      R7C7

5. Документ программы Excel называется:

1.      Журналом

2.      Книгой

3.      Страницей

4.      Брошюрой    

6. Что такое оптимальное планирование:

1.      Выбор плана с доходом

5.      Выбор наилучшего плана

6.      Выбор плана без ограничений

7. Что такое ресурсы:

1.      Количественная мера возможности выполнения какой-либо деятельности

2.      Качественная мера возможности выполнения какой-либо деятельности

3.      Прибыль, поступающая в бюджеты различных уровней

8. Задача линейного программирования состоит из:

1.      Целевой функции и системы ограничений

2.      Целевой функции и системы возможностей

3.      Целевой функции, системы возможностей и ограничений

9. Что такое стратегическое планирование:

1.      Перспективные направления деятельности организации, обеспечивающие ее рост и процветание

2.      Долгосрочное планирование

3.      Разработка плановых заданий «от достигнутого»

10.  Система ограничений задачи линейного программирования это:

1.      Система неравенств и уравнений

2.      Ограничения, накладываемые на целевую функцию

3.      Диапазон изменения параметров целевой функции

Приложение 2

Практическая работа «Решение экономических (оптимизационных) задач»

Список задач:

Пример 1. Задача об использовании ресурсов.

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

Вид изделия Тип ресурса	Затраты ресурса на изготовление одного изделия			Запас ресурса
	A	B	C
Оборудование	3	6	7	220
Металлопрокат	5	7	4	380
Электроэнергия	5	8	9	290
Людские ресурсы	3	2	4	410
Прибыль от одного изделия	11	13	18

Расходы в условных единицах для каждого вида ресурса при изготовлении одного изделия определенного типа указаны в таблице.

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

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

Пример 2. Задача о рационе.

При откорме животных в день каждое животное должно получить не менее 70 ед. питательного вещества A, не менее 40 ед. вещества B и не менее 19 ед. вещества С. Указанные питательные вещества содержатся в трех видах кормов.

Содержание единиц питательных веществ в одном килограмме каждого вида корма приведено в таблице:

Вид корма Питательные вещества	Количество единиц питательного вещества в 1 кг корма
	1	2	3
А	3	4	1
В	2	2	2
С	1	2	1

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

Пример 3. Для сохранения нормальной жизнедеятельности человек должен в сутки потреблять белков не менее 120 условных единиц (усл. ед.), жиров – не менее 70 и витаминов – не менее 10 усл. ед. Содержание их в каждой единице продуктов П₁ и П₂ равно соответственно (0,2; 0,075; 0) и (0,1; 0,1; 0,1) усл. ед. Стоимость 1 ед. продукта П₁ – 2 руб., П₂ –3 руб. Постройте математическую модель задачи, позволяющую так организовать питание, чтобы его стоимость была минимальной, а организм получил необходимое количество питательных веществ.

Пример 4. В районе лесного массива имеются лесопильный завод и фанерная фабрика. Чтобы получить 2,5 м³ коммерчески реализуемых комплектов пиломатериалов, необходимо израсходовать 2,5 м³ еловых и 7,5 м³ пихтовых лесоматериалов. Для приготовления листов фанеры по 100 м² требуется 5 м³ еловых и 10 м³ пихтовых лесоматериалов. Лесной массив содержит 80 м³ еловых и 180 м³ пихтовых лесоматериалов.

Согласно условиям поставок, в течение планируемого периода необходимо произвести по крайней мере 10 м³ пиломатериалов и 1200 м² фанеры. Доход с 1 м³ пиломатериалов составляет 160 руб., а со 100 м³ фанеры – 600 руб.

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

Пример 5. Служба снабжения завода получила от поставщиков 500 стальных прутков длиной 5 м. Их необходимо разрезать на детали А и B длиной соответственно 2 и 1,5 м, из которых затем составляются комплекты. В каждый комплект входят 3 детали А и 2 детали B. Характеристики возможных вариантов раскроя прутков представлены в таблице.

Характеристики возможных вариантов раскроя прутков

Вариант раскроя	Количество деталей, шт./пруток		Отходы, м/пруток
	А	B
1	2	0	1
2	1	2	0
3	0	3	0,5
Комплектность, шт./компл.	3	2

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

Пример 6. Малое предприятие выпускает детали А и В. Для этого оно использует литье, подвергаемое токарной обработке, сверлению и шлифованию. Производительность станочного парка предприятия по обработке деталей А и В приведена в таблице.

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

Станки	Производительность, шт./ч		Стоимость станочного времени, руб./ч
	А	В
Токарные	25	40	20
Сверлильные	28	35	14
Шлифовальные	35	25	17,5
Цена детали, руб.:
покупная	2	3
продажная	5	6

Пример 7. Мебельная фабрика выпускает столы, стулья, бюро и книжные шкафы. При изготовлении этих товаров используются два различных типа досок, причем фабрика имеет в наличии 1500 м досок I типа и 1000 м досок II типа. Кроме того, задан объём трудовых ресурсов в количестве 800 чел.-ч. В следующей таблице приведены нормативы затрат каждого из видов ресурсов на изготовление единицы каждого изделия, а также получаемая прибыль.

Изделие Ресурсы	Затраты на одну ед. изделия
	Столы	Стулья	Бюро	Книжные шкафы
Доски I типа(м) Доски I I типа(м) Труд. ресурсы (чел.-ч.) Прибыль (грн./шт.)	5 2 3 12	1 3 2 5	9 4 5 15	12 1 10 10

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

Пример 8. Четыре издательства используют бумагу для журналов, имеющуюся на трех опто- вых базах. Суточная потребность каждого издательства в бумаге (т), запасы бумаги на базах, а также цены за перевозку одной тонны бумаги с i-й базы j-ому издательству представлены в таблице.

Потребность                  издательства, т Запасы бумаги, т	400	300	100	100
400	2	8	2	1
400	1	4	6	3
100	1	5	9	2

Построить модель доставки бумаги издательствам, при которой общие затраты на доставку будут минимальными.

ТЕХНОЛОГИЧЕСКАЯ КАРТА

Этапы решения задачи	Деятельность ученика
Усвоить условие задачи	Выбрать свой вариант задачи, согласно рабочему месту
Построить математическую модель	a.       определить изменяемые (поисковые) переменные; b.          задать ограничения; c.          выбрать целевую функцию;
Решить задачу в программе Excel, с помощью средства «Поиск решения»	1.       Запустить программу MS Excel, сохранить ее под именем «Задача_опт» в своей папке. Установить пароль на открытие файла: Кнопка MSOffice-Подготовить-Зашифровать документ. 2.       Внести данные уравнений (неравенств) ограничений и целевой функции в форму средства Поиск решения. 3.       Нажать кнопку «Выполнить», проанализировать полученный результат
Показать результат учителю	Получить оценку

Использованные источники

1.    Присяжнюк С.И., Сборник задач по линейному программированию. Часть 1. Учебно-методическое пособие по высшей математике. – Пятигорск: КМВИС, 2011.

2.    Алесинская Т.В., Учебное пособие по решению задач по курсу "Экономико-математические методы и модели". Таганрог: Изд-во ТРТУ, 2002.

3.    Ходыкин В.Ф., Преображенский А.А. Сборник задач по математическому программированию. – Донецк: ДонНУ, 2002.

4.    http://mytest.klyaksa.net/