Решение задач оптимизации в помощью электронных таблиц Excel
Конспект урока
Решение задач оптимизации с помощью электронных таблиц Excel
Учитель информатики и ИКТ
Кабанова Татьяна Витальевна
ГБОУ школа №58
Приморского района Санкт-Петербурга
Тема. Решение задач оптимизации в Excel.
Тип урока: обобщение и систематизация знаний.
Цели урока:
образовательные обобщение и систематизация знаний по теме «Обработка числовой информации»
Задачи:
закрепление знаний об общих принципах работы табличного процессора Microsoft Excel;
практическое применение изученного материала, приобретение навыков в составлении таблиц разного типа;
развитие умения выбирать наиболее оптимальную структуру таблицы, создать и оформить таблицу;
формирование представления о вычислениях в электронной таблице как наиболее важных в изучении информатики и широко применяемых на практике.
развивающие продолжить развитие логического мышления, глубины и гибкости ума.
Задачи:
развитие познавательного интереса, речи и внимания учащихся;
развитие способности логически рассуждать;
формирование информационной культуры и потребности приобретения знаний;
развитие умения применять полученные знания для решения задач различных предметных областей.
развивать у школьников теоретическое мышление, формирование операционного мышления, направленного на выбор оптимальных решений.
воспитательные продолжать воспитывать информационную культуру, общечеловеческие качества личности школьника
Задачи:
воспитание творческого подхода к работе, желания экспериментировать;
воспитание трудолюбия, чувства уважения к науке;
продолжить воспитывать культуру общения;
продолжить формировать чувство долга, настойчивости, дисциплинированность; продолжить формирование творческих, исследовательских качеств учащихся;
продолжить воспитывать эстетический вкус.
Ход урока:
Организационный этап. (Цель: настроить учащихся на работу на уроке)
Сегодня мы продолжаем изучение темы «Обработка числовой информации». На предыдущих занятиях мы узнали основные информационные единицы электронной таблицы, типы и форматы данных, используемых в Excel, основные функций, используемых при записи формул, общие правила подготовки электронной таблицы, а так же графические возможности табличного процессора, учились решать задачи с использованием ЭТ.
Сегодня цель нашего урока: закрепить и обобщить знания, полученные по этой теме, развивать умение применять полученные знания для решения задач из различных предметных областей.
II. Актуализация знаний и фронтальный опрос
– Итак, что мы с вами знаем и умеем делать: создавать редактировать и формировать табличный документ, выполнять вычисления по формулам, применять относительные и абсолютные ссылки, использовать функции. Давайте вспомним и повторим то, что нам уже знакомо.
Вопросы
Ответ
1
Как называется документ, созданный в электронных таблицах?
Книга
2
Что является основным элементом электронной таблицы?
Ячейка
3
Что не может включать в себя формула в электронных таблицах?
Текст
4
Как записывают в формуле адрес ячейки, если необходимо отменить принцип относительной адресации при переносе формулы?
Перед именем столбца и номером строки ставится символ $
Какой формат числа вы примените для отображения:
Количества товара?
Времени начала уроков?
Дней рождения знакомых?
Порядковых номеров в списке?
число
время
дата
число
5
Каким будет результат вычислений в ячейке С1?
[ Cкачайте файл, чтобы посмотреть картинку ]
15
Какие виды адресации ячеек вы знаете?
Относительная, абсолютная, смешанная.
В каких случаях необходимо использовать абсолютные адреса ячеек в формулах?
Для указания фиксированного адреса ячейки.
6
В ячейке электронной таблицы С5 записана формула =B5*А5. Какая формула будет получена из нее при копировании в ячейку С6?
= В6 * А6
7
Дан фрагмент электронной таблицы:
[ Cкачайте файл, чтобы посмотреть картинку ]
Значение ячейки С1 вычисляется по формуле = В1+ $A$1. Чему будет равно после копирования формулы значение в ячейке С3?
25
Перечислите области деятельности человека, к которым можно отнести использование возможностей табличного процессора MS Excel?
Это – наука, производство, бухгалтерия, торговля, статистика, экология
Таким образом, можно сделать вывод: области применения электронных таблиц очень разнообразны, без них не может обойтись практически ни один современный специалист.
III. Изложение нового материала
Человек, совершая те или иные деяния, стремился вести себя таким образом, чтобы результат, достигаемый как следствие некоторого поступка, оказался в определенном смысле наилучшим. Двигаясь из одного пункта в другой, он стремился найти кратчайший среди возможных путь. Строя жилище, он искал такую его геометрию, которая при наименьшем расходе топлива, обеспечивала приемлемо комфортные условия существования. Занимаясь строительством кораблей, он пытался придать им такую форму, при которой вода оказывала бы наименьшее сопротивление. Можно легко продолжить перечень подобных примеров.
Задачи на отыскание оптимального решения называются задачами оптимизации. Применяемые в процессе оптимизации методы получили название методов оптимизации. При постановке и решении задач оптимизации возникают два вопроса: что и как оптимизировать?
Ответ на первый вопрос получается как результат глубокого изучения проблемы, которую предстоит решить. Выявляется тот параметр, который определяет степень совершенства решения возникшей проблемы. Этот параметр обычно называют целевой функцией или критерием качества. Далее устанавливается совокупность величин, которые определяют целевую функцию. Наконец, формулируются все ограничения, которые должны учитываться при решении задачи. После этого строится математическая модель, заключающаяся в установлении аналитической зависимости целевой функции от всех аргументов и аналитической формулировки сопутствующих задаче ограничений. Далее приступают к поиску ответа на второй вопрос.
Понятие «наилучший, оптимальный» может быть выражено количественными критериями – минимум затрат, минимум времени, максимум прибыли и т.д. Для решения таких задач в ЭТ используется надстройка Поиск решения.
При решении задач оптимизации с помощью MS Excel применяют алгоритм:
разбор условия задачи;
построение математической модели;
выбор изменяемых данных (параметров);
задание ограничений;
выбор целевой функции;
решение задачи на компьютере;
анализ полученных результатов.
Виды задач, которые могут быть решены с помощью Поиска решения:
Составление оптимального плана производства;
Решение системы линейных уравнений;
Транспортная задача;
Задача о назначениях;
Решение уравнения регрессии
Предлагаю для рассмотрения одну из таких задач.
На участке работает 20 человек; каждый из них в среднем работает 1800 ч в год. Выделенные ресурсы: 32 т металла, 54 тыс. кВт
·ч электроэнергии. План реализации: не менее 2 тыс. изделий А и не менее 3 тыс. изделий Б. На выпуск 1 тыс. изделий А затрачивается 3 т металла, 3 тыс. кВт
·ч электроэнергии и 3 тыс. ч рабочего времени. На выпуск 1 тыс. изделий Б затрачивается 1 т металла, 6 тыс. кВт
·ч электроэнергии и 3 тыс. ч рабочего времени. От реализации 1 тыс. изделий А завод получает прибыль 500 тыс. р., от реализации 1 тыс. изделий Б – 700 тыс. р. Выпуск каждого количества изделий А и Б (в тыс. штук) надо запланировать, чтобы прибыль от их реализации была наибольшей. Составить модель и решить задачу.
Построим математическую модель:
Пусть х(тыс. шт.) – искомое количество изделий А.
у(тыс.шт.) – искомое количество изделий Б.
Для изготовления 1 тыс. изделий А и 1 тыс. изделий Б используется металла: 3
·х+1
·у.
Для изготовления 1 тыс. изделий А и 1 тыс. изделий Б затрачивается электроэнергии: 3
·х+6
·у (тыс.кВт
·ч)
Для изготовления 1 тыс. изделий А и 1 тыс. изделий Б затрачивается рабочего времени: 3
·х+3
·у (тыс.кВт
·ч)
Прибыль от реализации 1 тыс. изделий А и 1 тыс. изделий Б: х
·500+у
·700 (тыс.р.)
Зададим ограничения:
Использование металла: (3
·х+1
·у)
·32.
Затрата электроэнергии: (3
·х+6
·у)
·54.
Затрата рабочего времени: (3
·х+3
·у)
·36 (т.к. 20 рабочих по 1800 часов в год, получается 20
·1800=36000. Поэтому ставим число 36 тыс. часов).
Прибыль должна быть максимальной, то есть х
·500+у
·700 = max
Решение задачи на компьютере:
Внести данные в таблицу
Найдём оптимальное решение, для этого:
Выделим целевую ячейку
Выбрать ДанныеПоиск решения
Установим целевую ячейку, равную максимальному значению;
Укажем изменяемые ячейки (количество изделий А и изделий В)
Добавить записи ограничений (затраты на использование металла, расход на электроэнергию и затраты рабочего времени)
Таким образом, с помощью электронной таблицы найдено оптимальное решение для максимизации дохода данного кооператива.
Работа с данной надстройкой вызывает наибольшее затруднение, так как для того, чтобы Excel смоделировал «осмысленное» значение, необходимо правильно отобрать входные данные и определить все ограничения. Другими словами, правильно построить математическую модель. Основные проблемы, с которыми сталкиваются при решении задач на оптимизацию, это определение изменяемых ячеек и указание ограничений. Необходимо обратить внимание на то, что параметры должны быть прямо, или косвенно связаны с целевой ячейкой формулой.
Рассмотренные задачи позволяют сделать акцент на практическую значимость формализации задачи, способов решения задач с помощью электронных таблиц.
III. Оперирование знаниям. (Практическая самостоятельная работа).
(Цель: проверить умение применения знаний при решении практической задачи)
В оставшееся время мы выполним практическую самостоятельную работу. Скопировать задачу на Лист2 и решить задачу: Кооператив из 20 человек выпускает А и Б. Кооператив намерен получать прибыль не менее 6,5 млн.р. в год. Ему выделили 54 тыс. кВт
·ч электроэнергии. Какое минимальное количество металла потребуется кооперативу, чтобы обеспечить нужную прибыль?
Сохраните свои работы в личных папках
IV. Подведение итогов.
Итак, сегодня на уроке мы в очередной раз убедились в многофункциональности возможностей компьютерной техники. А именно, в возможностях электронных таблиц Excel. Мы увидели, что с помощью этой программы можно решить оптимизационные задачи, вспомнили, как выбирается оптимальная структура таблицы, как используются различные виды ссылок. Т.е. цель нашего урока полностью достигнута.
В качестве домашнего задания я предлагаю вам выбрать любую дисциплину и составить задачу оптимизации с использованием программы MS Excel.
13PAGE 15
13PAGE 14815
Заголовок 315