Решение задач оптимального планирования в Microsoft Excel
Автор: Воробьева Татьяна Николаевна
Организация: ОБПОУ ''Курский автотехнический колледж''
Населенный пункт: г. Курск
Специальность: 23.02.01 Организация перевозок и управление на транспорте
Курс, группа: группа ОП-22
Тип урока: урок по формированию знаний, умений, навыков
Цели урока:
- научить решать оптимизационные задачи с помощью надстройки «Поиск решения»
- продемонстрировать студентам взаимосвязь информатики и дисциплин профессионального цикла;
- создать условия для формирования убеждений в профессиональной значимости учебного материала;
- развитие активной мыслительной деятельности, развитие зрительной, образной, слуховой памяти, развитие позитивного отношения к информатике.
Формируемые компетенции
ОК 1. Понимать сущность и социальную значимость своей будущей профессии, проявлять к ней устойчивый интерес
ОК 3. Принимать решения в стандартных и нестандартных ситуациях и нести за них ответственность
ОК 4. Осуществлять поиск и использование информации, необходимой для эффективного выполнения профессиональных задач, профессионального и личностного развития
ОК 5. Использовать информационно-коммуникационные технологии в профессиональной деятельности
ПК 1.1. Выполнять операции по осуществлению перевозочного процесса с применением современных информационных технологий управления перевозками.
Необходимо знать:
- Базовые системные программные продукты и пакеты прикладных программ;
Необходимо уметь
- Использовать изученные прикладные программные средства;
Методическая цель проведения урока: использование ИКТ в будущей профессиональной деятельности.
Материально-техническое оснащение урока:
• компьютерный класс;
• мультимедийный проектор;
• пакет программного обеспечения Microsoft Office;
• раздаточный материал;
• презентация к уроку;
Методы обучения: репродуктивный, объяснительно-иллюстративный, частично-поисковый, словесный, наглядный, самостоятельная работа.
Междисциплинарные связи: информатика, МДК.02.01 Организация движения (по видам транспорта), математика
Продолжительность занятия: 45 минут.
Литература:
- Информатика: Практикум для профессий и специальностей технического и социально-экономического профилей. Н. Е. Астафьева, С. А. Гаврилова, под ред. М.С. Цветковой.- 2-е издание, стер.- М.: Издательский центр «Академия», 2019.- 272с.
- Информационные технологии в профессиональной деятельности: учебник для студ. учреждений сред. проф. образования/ Михеева Е. В, Титова О.И. – 3-е издание, стер.- М.: Издательский центр «Академия», 2019.- 416с.
план урока
- Организационный момент.
- рапорт дежурного;
- проверка готовности обучающихся к уроку.
- Мотивация темы урока: беседа
- Постановка задач урока.
- Актуализация опорных знаний.
- Программированный контроль.
- Изучение нового материала.
Объяснение студентами алгоритма решения задачи на построение оптимального плана и ее частного вида в MS Excel с демонстрацией решения задач
- Закрепление учебного материала
Самостоятельная работа. «Решение задач в Microsoft Excel» (работа студентов на ПК)
- Подведение итогов урока.
- Домашнее задание.
- Рефлексия
Ход урока
- Организационный момент
Здравствуйте уважаемые студенты. Присаживайтесь. Кто отсутствует?
Сегодня у нас необычный урок, у нас на занятии присутствуют гости. Надеюсь, это не повлияет на вашу активность. Все готовы к уроку? Тогда начинаем.
Эпиграфом к сегодняшнему уроку я взяла слова великого немецкого писателя Гёте
Недостаточно только получить знания; надо найти им приложение.(Слайд 1)
- Мотивация темы урока
Мы продолжаем с вами работать в программе MS Excel. В MS Excel есть все необходимые средства и возможности для выполнения экономических, производственных, оптимизационных расчетов. Для вас важно не просто овладеть навыками работы в Excel, а уметь применять их для решения своих профессиональных задач.
Вспомните, с какой надстройкой в MS Excel мы познакомились на прошлом уроке? (Поиск решения).
Для решения каких задач применяется эта надстройка? (Ответы студентов)
Т.е. для решения оптимизационных задач с учетом заданных пользователем ограничений.
А самое главное – Поиск решения используется для решения оптимизационных задач, в которых не одна, а несколько изменяемых переменных.
Среди множества задач, которые можно решить с помощью Поиска решения есть задача на построение оптимального плана перевозок, при котором общие затраты на перевозку были бы минимальными. Вы эту задачу будете рассматривать на 3 курсе при изучении ИТ в ПД и решать ее с помощью специализированных программ. Мы с вами на прошлом уроке начали рассматривать решение этой задачи в Excel.
Какое условие было обязательным в этой задаче? (суммарный запас равен суммарному спросу)
Скажите, а в реальной ситуации всегда запасы равны спросу?
А можно ли построить план перевозки в жизни если эти величины не совпадают? (Да, но в этом случае или на каком-то складе останется товар, или спрос будет удовлетворен не полностью)
Приложение 1
А вы можете решить такую задачу в Excel? (Нет). (Слайд 2)
Как вы думаете, чем мы будем заниматься сегодня на уроке? (Решать задачи на построение плана перевозок, когда запасы и спрос не совпадают).
Преподаватель сообщает тему урока «Решение задач оптимального планирования в Microsoft Excel». (Слайд 3)
- Постановка задач урока.
Какую задачу мы ставим перед собой сегодня на уроке? Чему мы должны научиться (Студенты пытаются сформулировать: научиться строить оптимальный план перевозок если запасы не равны спросу)
Верно, сегодня на уроке мы ставим перед собой следующие задачи: (Слайд 4)
- научиться решать задачи оптимального планирования в MS Excel;
- закрепить навыки работы с надстройкой Поиск решения.
- Актуализация опорных знаний.
Прежде чем приступить к выполнению наших задач, давайте повторим материал, который будет нам необходим при дальнейшей работе на уроке.
Для этого вы с помощью ваших смартфонов ответите на вопросы теста, который находится у вас в разделе проверочные работы на ресурсе Якласс под именем Тест.
Зайдите на Якласс. На прохождение теста 3 - 4 минуты.
И так время истекло, давайте посмотрим ваши результаты. (Проанализировать результаты теста).
Перед вами лежит оценочный лист, вы сегодня будете оценивать себя сами, записывая набранные баллы и просуммировав общее количество заработанных баллов получите итоговую отметку. Понятно это?
Подпишите свой оценочный лист и запишите в нем полученную отметку за тест.
- Изучение нового материала.
А теперь перейдем к решению нашей задачи на построение оптимального плана перевозок (Слайд 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 |
|
Транспортная задача бывает двух видов: открытая и закрытая.
Закрытая транспортная задача – когда суммарные потребности и суммарные запасы совпадают K1+K2+…+Kn=Z1+Z2+…+Zm
Открытая транспортная задача – когда суммарные потребности и суммарные запасы не совпадаютK1+K2+…+Kn≠Z1+Z2+…+Zm
Чтобы привести открытую транспортную задачу к закрытому виду, добавляем столбец (или строку) с нулевыми стоимостями.
Если превышают запасы - добавляем фиктивного потребителя (столбец)
Если превышает спрос - добавляем фиктивного поставщика (строку)
Так как в реальности фиктивный поставщик (потребитель) не существует, то стоимость доставки до него от любого пункта равна нулю.
Алгоритм решения задачи нахождения оптимального плана перевозок с помощью надстройки Поиск решения в Excel состоит из нескольких этапов: (Слайд 8)
-
- Ввод исходных данных в электронную таблицу.
- Определение типа задачи (открытого или закрытого типа)
- задачу открытого типа приводим к закрытому типу, добавлением столбца (строки) с нулевыми стоимостями.
ü Если превышают запасы - добавляем фиктивного потребителя (столбец)
ü Если превышает спрос - добавляем фиктивного поставщика (строку)
-
- Формирование расчётной таблицы, т.е. на этом этапе вводят расчетные формулы. Для решения транспортной задачи потребуются функции: СУММ, СУММПРОИЗВ
- Ввод зависимости для целевой функции
- Применение надстройки "Поиск решения".
- Анализ результата.
Преподаватель: Давайте рассмотрим еще одну оптимизационную задачу, сводящуюся к транспортной модели
Студент 2 (ФИ), пожалуйста.
Студент объясняет решение задачи с демонстрацией решения на компьютере.
Приложение 3
После рассмотрения решения задачи студентом
Преподаватель: Эта задача называется задача о назначениях. Это тоже оптимизационная задача, и является частным случаем более общей транспортной задачи, Она имеет такую же структуру, но обладает некоторыми особенностями.
Если число исполнителей равно числу выполняемых работ, то такая задача является закрытого типа в противном случае – открытого типа. В случае закрытой задачи о назначениях выполняются два условия: каждый исполнитель выполняет только одну работу, и каждая работа выполняется только одним исполнителем.
- Закрепление учебного материала
А сейчас вы сядете за компьютеры и попробуете самостоятельно решить предложенные задачи. Условия задач лежат в папках за компьютерами. Задачи разной степени сложности (на 3, 4 и 5). Вы можете начинать с любой задачи. Вводить данные вы все умеете, поэтому для экономии времени я подготовила вам файл, который находится на рабочем столе с именем Задачи.xlsx. Вы сядете за компьютеры и на рабочем столе откроете этот файл. Листы книги названы по номеру задачи. На выполнение данного задание у вас 15 минут. Прежде чем вы сядете за компьютер, я хочу напомнить о соблюдении безопасных условий при работе за компьютером. Рассаживайтесь.
Приложение 4
- Подведение итогов урока.
И так время заканчивается. Внимание на экран! Проверьте, пожалуйста ваши ответы с помощью ключа. Если ответы совпали, то поставьте в оценочный лист соответствующую отметку.
Приложение 5
Посчитайте общее количество баллов за урок и поставьте себе отметку. Передайте мне оценочные листы.
Итоговую отметку за урок каждому из вас я озвучу на следующем уроке, когда проверю ваши оценочные листы. Вы все сегодня работали хорошо, молодцы. Особенно хочется отметить Студентов….
Итак, сегодня на уроке мы с вами решали задачу на построение оптимального плана перевозок с помощью надстройки Поиск решения, на следующем уроке мы продолжим решать задачи с помощью этой надстройки.
Мы в очередной раз убедились в огромных возможностях электронных таблиц Excel. Мы увидели, что с помощью этой программы можно решить оптимизационные задачи.
Я думаю, что сегодняшний урок будет полезен вам тем, что пополнил ваши практические знания и умения, которые вы потом сможете использовать в дальнейшем, в своей профессиональной деятельности.
- Домашнее задание. (Сообщение и комментирование домашнего задания)
Материал урока был очень объемный, в тетради вы не писали для экономии времени.
Я подготовила для вас опорный конспект и на последней странице домашнее задание:
-
-
-
- Разобрать опорный конспект.
- Решить задачу.
-
-
Приложение 6
Домашнее задание дифференцированное (сами определитесь кто на какую отметку желает). Выполните задание в Excel и результат решения прикрепить в качестве ответа на Яклассе (Студентам подготовлена на Яклассе Проверочная работа с условиями задач, доступ к которой открывается через 1 час после окончания урока).
Рефлексия
Уважаемые студенты, посмотрите внимательно на экран.
Приложение 7
В результате освоения учебной дисциплины вы должны обладать следующими общими и профессиональными компетенциями.
К формированию каких компетенций мы приблизились после сегодняшнего урока? (Студенты по очереди называют компетенцию)
Преподаватель: Я думаю, что сегодняшний урок будет полезен вам тем, что пополнил ваши практические знания и умения, которые вы потом сможете использовать в дальнейшем, в своей профессиональной деятельности. Всем спасибо за урок. До свидания.
Приложение 1
Решить задачу. В хозяйстве имеются 3 склада с минеральными удобрениями и четыре пункта, куда их необходимо перевезти. Потребность каждого пункта в минеральных удобрениях различна, и запасы на каждом складе ограничены. Требуется составить такой план перевозок, который бы удовлетворял все потребности в сырье с учётом имеющихся запасов на каждом из складов таким образом, чтобы суммарная стоимость перевозки была минимальной.
Приложение 7
Формируемые компетенции
- ОК 1. Понимать сущность и социальную значимость своей будущей профессии, проявлять к ней устойчивый интерес.
- ОК 2. Организовывать собственную деятельность, выбирать типовые методы и способы выполнения профессиональных задач, оценивать их эффективность и качество.
- ОК 3. Принимать решения в стандартных и нестандартных ситуациях и нести за них ответственность.
- ОК 4. Осуществлять поиск и использование информации, необходимой для эффективного выполнения профессиональных задач, профессионального и личностного развития.
- ОК 5. Использовать информационно-коммуникационные технологии в профессиональной деятельности.
- ОК 6. Работать в коллективе и команде, эффективно общаться с коллегами, руководством, потребителями.
- ОК 7. Брать на себя ответственность за работу членов команды (подчиненных), результат выполнения заданий.
- ОК 9. Ориентироваться в условиях частой смены технологий в профессиональной деятельности.
- ПК 1.1. Выполнять операции по осуществлению перевозочного процесса с применением современных информационных технологий управления перевозками.