Решение задач оптимального планирования в Microsoft Excel

Автор: Воробьева Татьяна Николаевна

Организация: ОБПОУ ''Курский автотехнический колледж''

Населенный пункт: г. Курск

Специальность: 23.02.01 Организация перевозок и управление на транспорте

Курс, группа: группа ОП-22

Тип урока: урок по формированию знаний, умений, навыков

Цели урока:

• научить решать оптимизационные задачи с помощью надстройки «Поиск решения»
• продемонстрировать студентам взаимосвязь информатики и дисциплин профессионального цикла;

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

Формируемые компетенции

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

ОК 3. Принимать решения в стандартных и нестандартных ситуациях и нести за них ответственность

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

ОК 5. Использовать информационно-коммуникационные технологии в профессиональной деятельности

ПК 1.1. Выполнять операции по осуществлению перевозочного процесса с применением современных информационных технологий управления перевозками.

Необходимо знать:

• Базовые системные программные продукты и пакеты прикладных программ;

Необходимо уметь

• Использовать изученные прикладные программные средства;

Методическая цель проведения урока: использование ИКТ в будущей профессиональной деятельности.

Материально-техническое оснащение урока:

• компьютерный класс;

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

• пакет программного обеспечения Microsoft Office;

• раздаточный материал;

• презентация к уроку;

Методы обучения: репродуктивный, объяснительно-иллюстративный, частично-поисковый, словесный, наглядный, самостоятельная работа.

Междисциплинарные связи: информатика, МДК.02.01 Организация движения (по видам транспорта), математика

Продолжительность занятия: 45 минут.

Литература:

1. Информатика: Практикум для профессий и специальностей технического и социально-экономического профилей. Н. Е. Астафьева, С. А. Гаврилова, под ред. М.С. Цветковой.- 2-е издание, стер.- М.: Издательский центр «Академия», 2019.- 272с.
2. Информационные технологии в профессиональной деятельности: учебник для студ. учреждений сред. проф. образования/ Михеева Е. В, Титова О.И. – 3-е издание, стер.- М.: Издательский центр «Академия», 2019.- 416с.

план урока

1. Организационный момент.

• рапорт дежурного;
• проверка готовности обучающихся к уроку.

2. Мотивация темы урока: беседа
3. Постановка задач урока.
4. Актуализация опорных знаний.

• Программированный контроль.

5. Изучение нового материала.

Объяснение студентами алгоритма решения задачи на построение оптимального плана и ее частного вида в MS Excel с демонстрацией решения задач

6. Закрепление учебного материала

Самостоятельная работа. «Решение задач в Microsoft Excel» (работа студентов на ПК)

7. Подведение итогов урока.
8. Домашнее задание.
9. Рефлексия

 

Ход урока

1. Организационный момент

Здравствуйте уважаемые студенты. Присаживайтесь. Кто отсутствует?

Сегодня у нас необычный урок, у нас на занятии присутствуют гости. Надеюсь, это не повлияет на вашу активность. Все готовы к уроку? Тогда начинаем.

 

Эпиграфом к сегодняшнему уроку я взяла слова великого немецкого писателя Гёте

Недостаточно только получить знания; надо найти им приложение.(Слайд 1)

2. Мотивация темы урока

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

Вспомните, с какой надстройкой в MS Excel мы познакомились на прошлом уроке? (Поиск решения).

Для решения каких задач применяется эта надстройка? (Ответы студентов)

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

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

Среди множества задач, которые можно решить с помощью Поиска решения есть задача на построение оптимального плана перевозок, при котором общие затраты на перевозку были бы минимальными. Вы эту задачу будете рассматривать на 3 курсе при изучении ИТ в ПД и решать ее с помощью специализированных программ. Мы с вами на прошлом уроке начали рассматривать решение этой задачи в Excel.

Какое условие было обязательным в этой задаче? (суммарный запас равен суммарному спросу)

Скажите, а в реальной ситуации всегда запасы равны спросу?

А можно ли построить план перевозки в жизни если эти величины не совпадают? (Да, но в этом случае или на каком-то складе останется товар, или спрос будет удовлетворен не полностью)

Приложение 1

А вы можете решить такую задачу в Excel? (Нет). (Слайд 2)

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

Преподаватель сообщает тему урока «Решение задач оптимального планирования в Microsoft Excel». (Слайд 3)

3. Постановка задач урока.

Какую задачу мы ставим перед собой сегодня на уроке? Чему мы должны научиться (Студенты пытаются сформулировать: научиться строить оптимальный план перевозок если запасы не равны спросу)

Верно, сегодня на уроке мы ставим перед собой следующие задачи: (Слайд 4)

• научиться решать задачи оптимального планирования в MS Excel;
• закрепить навыки работы с надстройкой Поиск решения.

4. Актуализация опорных знаний.

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

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

Зайдите на Якласс. На прохождение теста 3 - 4 минуты.

И так время истекло, давайте посмотрим ваши результаты. (Проанализировать результаты теста).

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

Подпишите свой оценочный лист и запишите в нем полученную отметку за тест.

5. Изучение нового материала.

А теперь перейдем к решению нашей задачи на построение оптимального плана перевозок (Слайд 5)

У некоторых из вас было дано опережающее задание.

Давайте посмотрим, как вы с этим справились.

Студент 1 (ФИ), пожалуйста, начнем с тебя

Студент 1 объясняет решение задачи если запасы превышают спрос (решение демонстрирует на компьютере)

Приложение 2

Уважаемые студенты, ваши вопросы к …ФИ..

Возможные вопросы студентов: Почему добавление фиктивного потребителя не влияет на минимальную стоимость перевозки?

Вопрос преподавателя к Студенту 1: А если суммарные запасы будут меньше, чем суммарный спрос потребителей. Как быть тогда? (Тогда нужно ввести фиктивного поставщика Склад с запасами равными разнице и 0 тарифной стоимостью)

Я усложню условие задачи: со Склада1 нужно доставить 3-му потребителю 50т. Как тогда решить задачу? (В ограничениях указать D11=0)

Итак, подытожим ту информацию, которую мы изучили на прошлом уроке и ту, что сейчас сообщил ….ФИ.

Преподаватель: задача на построение оптимального плана перевозок при заданных тарифах на перевозку называется транспортная задача. (Слайд 6)

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

 

 

Поставщики	Потребители				Запасы поставщиков
	В1	В2	….	Вn
А1	C11	C12	…	C1n	Z1
А2	C21	C22	…	C2n	Z2
……	…	…	…	…	…
Аm	Cm1	Cm2	…	Cmn	Zm
Спрос потребителей	K1	K2	…	Kn

Транспортная задача бывает двух видов: открытая и закрытая.

Закрытая транспортная задача – когда суммарные потребности и суммарные запасы совпадают K₁+K₂+…+K_n=Z₁+Z₂+…+Z_m

Открытая транспортная задача – когда суммарные потребности и суммарные запасы не совпадаютK₁+K₂+…+K_n≠Z₁+Z₂+…+Z_m

Чтобы привести открытую транспортную задачу к закрытому виду, добавляем столбец (или строку) с нулевыми стоимостями.

Если превышают запасы - добавляем фиктивного потребителя (столбец)

Если превышает спрос - добавляем фиктивного поставщика (строку)

Так как в реальности фиктивный поставщик (потребитель) не существует, то стоимость доставки до него от любого пункта равна нулю.

Алгоритм решения задачи нахождения оптимального плана перевозок с помощью надстройки Поиск решения в Excel состоит из нескольких этапов: (Слайд 8)

1. 1. Ввод исходных данных в электронную таблицу.
   2. Определение типа задачи (открытого или закрытого типа)
   3. задачу открытого типа приводим к закрытому типу, добавлением столбца (строки) с нулевыми стоимостями.

ü Если превышают запасы - добавляем фиктивного потребителя (столбец)

ü Если превышает спрос - добавляем фиктивного поставщика (строку)

1. 4. Формирование расчётной таблицы, т.е. на этом этапе вводят расчетные формулы. Для решения транспортной задачи потребуются функции: СУММ, СУММПРОИЗВ
   5. Ввод зависимости для целевой функции
   6. Применение надстройки "Поиск решения".
   7. Анализ результата.

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

Студент 2 (ФИ), пожалуйста.

Студент объясняет решение задачи с демонстрацией решения на компьютере.

Приложение 3

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

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

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

6. Закрепление учебного материала

А сейчас вы сядете за компьютеры и попробуете самостоятельно решить предложенные задачи. Условия задач лежат в папках за компьютерами. Задачи разной степени сложности (на 3, 4 и 5). Вы можете начинать с любой задачи. Вводить данные вы все умеете, поэтому для экономии времени я подготовила вам файл, который находится на рабочем столе с именем Задачи.xlsx. Вы сядете за компьютеры и на рабочем столе откроете этот файл. Листы книги названы по номеру задачи. На выполнение данного задание у вас 15 минут. Прежде чем вы сядете за компьютер, я хочу напомнить о соблюдении безопасных условий при работе за компьютером. Рассаживайтесь.

Приложение 4

7. Подведение итогов урока.

И так время заканчивается. Внимание на экран! Проверьте, пожалуйста ваши ответы с помощью ключа. Если ответы совпали, то поставьте в оценочный лист соответствующую отметку.

Приложение 5

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

Итоговую отметку за урок каждому из вас я озвучу на следующем уроке, когда проверю ваши оценочные листы. Вы все сегодня работали хорошо, молодцы. Особенно хочется отметить Студентов….

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

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

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

8. Домашнее задание. (Сообщение и комментирование домашнего задания)

Материал урока был очень объемный, в тетради вы не писали для экономии времени.

Я подготовила для вас опорный конспект и на последней странице домашнее задание:

1. 1. 1. 1. Разобрать опорный конспект.
         2. Решить задачу.

Приложение 6

Домашнее задание дифференцированное (сами определитесь кто на какую отметку желает). Выполните задание в Excel и результат решения прикрепить в качестве ответа на Яклассе (Студентам подготовлена на Яклассе Проверочная работа с условиями задач, доступ к которой открывается через 1 час после окончания урока).

Рефлексия

Уважаемые студенты, посмотрите внимательно на экран.

Приложение 7

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

К формированию каких компетенций мы приблизились после сегодняшнего урока? (Студенты по очереди называют компетенцию)

Преподаватель: Я думаю, что сегодняшний урок будет полезен вам тем, что пополнил ваши практические знания и умения, которые вы потом сможете использовать в дальнейшем, в своей профессиональной деятельности. Всем спасибо за урок. До свидания.

 

Приложение 1

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

Приложение 7

Формируемые компетенции

• ОК 1. Понимать сущность и социальную значимость своей будущей профессии, проявлять к ней устойчивый интерес.
• ОК 2. Организовывать собственную деятельность, выбирать типовые методы и способы выполнения профессиональных задач, оценивать их эффективность и качество.
• ОК 3. Принимать решения в стандартных и нестандартных ситуациях и нести за них ответственность.
• ОК 4. Осуществлять поиск и использование информации, необходимой для эффективного выполнения профессиональных задач, профессионального и личностного развития.
• ОК 5. Использовать информационно-коммуникационные технологии в профессиональной деятельности.
• ОК 6. Работать в коллективе и команде, эффективно общаться с коллегами, руководством, потребителями.
• ОК 7. Брать на себя ответственность за работу членов команды (подчиненных), результат выполнения заданий.
• ОК 9. Ориентироваться в условиях частой смены технологий в профессиональной деятельности.
• ПК 1.1. Выполнять операции по осуществлению перевозочного процесса с применением современных информационных технологий управления перевозками.