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

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



НазваниеУрок по теме «Решение оптимизационных задач в среде электронных таблиц exсel»
Дата17.10.2016
Размер
ТипУрок

МОУ «Средняя общеобразовательная школа №12»


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

Учитель Барышева Е.Е.

Цели:

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

  • Формирование умений и навыков, носящих в современных условиях общенаучный и общеинтеллектуальный характер.

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

  • Научить учащихся применять современное программное обеспечение в решении нестандартных задач.

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

  • Воспитательная: развитие познавательного интереса, воспитание информационной культуры.

  • Учебная: изучить и закрепить основные навыки работы с электронными таблицами, научить применять надстройку Поиск решения для решения задач.

  • Развивающая: развитие логического мышления, расширение кругозора.

Оборудование:

  • компьютеры;

  • пакет Microsoft Office;

  • интерактивная доска;

  • Карточки с задачами для самостоятельной работы.



План урока:

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

  • Актуализация знаний

  • Изложение алгоритма решения задачи оптимизации с помощью ЭТ

  • Деловая игра

  • Подведение итогов

  • Домашнее задание

Ход урока

Учитель.С древних времен человека интересовали задачи связанные с отысканием наименьших и наибольших величин. Бурный рост промышленности в XVII-XVIII веках привел к необходимости исследования более сложных задач на экстремум. Однако лишь в XX веке при огромном размахе производства и осознании ограниченности ресурсов Земли стала актуальной задача оптимального использования энергии, материалов, рабочего времен. Большую актуальность приобрели вопросы наилучшего управления различными процессами физики, техники экономики, управления и др. Сюда относятся:

  • Задача на скорейшее достижение точки назначения;

  • Задача организации производства с целью получения максимальной прибыли при заданных затратах ресурсов;

  • Задача управления системой гидростанций и водохранилищ с целью получения максимального количества электроэнергии;

  • Задача о быстрейшем нагреве или остывании металла до заданного температурного режима и другие.

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

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

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

Учитель. Почему бы не исследовать сам оригинал, а не строить его модель? ( Анализировать модели проще и дешевле, чем экспериментировать с объектами реального мира)

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

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

  1. Задача. Пошла баба на базар на людей посмотреть да кое-что продать. Сколько надо бабе на базар для продажи живых гусей, уток и кур, чтобы выручить как можно больше денег, если она может взять товара массой не более Р килограмм? Причем известно, что:

масса одной курицы - В2, стоимость - С2

масса одной утки - В3, стоимость - С3

масса одного гуся – В4, стоимость – С4

Учитель.

Математическая модель задачи

D2, D3, D4 – число кур, уток и гусей соответственно, взятых бабой для продажи.

Задача бабы- выручить как можно больше денег- описывается целевой функцией:

С2*D2+C3*D3+C4*D4 →max.

Так как баба может взять не более Р кг товара, то должно выполняться условие:

В2*D2+В3*D3+В4*D4 ≤Р

Кроме того, имеют место ограничения:

D2 ≥ 0, D3 ≥ 0, D4 ≥ 0,

D2, D3, D4 – целые.

^ Порядок выполнения работы

Откройте Microsoft Exсel. Введите исходные данные и расчетные формулы в таблицу следующим образом:





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

  1. Выделить целевую ячейку В6.

  2. Активизируйте кнопку максимальному значению.

  3. В поле Изменяя ячейки укажите диапазон изменяемых ячеек D2:D4 (можно выделением).



  1. Перейдите в поле Ограничения (щелчком мыши) и выберите кнопку Добавить.












  1. После записи каждого ограничения нажимайте кнопку Добавить (для последнего ограничения - ОК).




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


В появившемся окне Результаты поиска решения активизируйте кнопку Сохранить найденное решение, а потом нажмите ОК.





В ячейках D2:D4 таблицы появится решение задачи.

  1. Учитель. Продолжим тему сельского хозяйства. Представьте себя сотрудниками фирмы «Оптим+». К вам обратились работники Акционерного общества «Урожай» с просьбой помочь решить такие их проблемы.

Задача 1. Бригаде рабочих, находящейся в поле, необходимо как можно скорее эвакуировать неисправную технику в город. Попасть в город можно тремя путями:

  • по кратчайшему пути от точки Б (бригада) до точки Г (город), при этом скорость эвакуатора будет равна скорости движения по полю, Vполя = 25 км/ч;

  • сначала по кратчайшему пути на дорогу от точки Б до точки Д (дорога), со скоростью Vполя = 25 км/ч, потом по дороге от точки Д до точки Г со скоростью Vдор = 80 км/ч;

  • сначала в некоторую точку Е на дороге, со скоростью Vполя, а потом по дороге до точки Г со скоростью Vдор.

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




^ Математической модель.

1) Оптимизируемая величина – время движения, т.к. в задаче требуется выяснить, когда время будет наименьшим. Обозначим время буквой t, а маршрут движения – буквами БЕГ.

2) Время зависит от расстояния, поэтому обозначим расстояние ^ ДЕ=х.

Тогда ЕГ=ДГ-ЕГ=25-х (км), т.к. по т. Пифагора БД =(км). Т.о., реальные границы изменения независимой переменной 0< х< 25.

3) Для вычисления tдвижения также найдем по теореме Пифагора расстояние БЕ=(км), тогда (ч), хI [ а;в] .

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

Ограничения: 0< х< 25.

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



 

A

B

C

D

1

ДГ=

2500

м

Заполняем ячейки А1:С4 исходя из условия задачи

2

БГ=

3000

м

3

Vполя=

25,0

км/ч

4

Vдороги=

130

км/ч

5

ДЕ=Х

1000

м

Вводим изначально любое число, <= 2500 м

6

БГ2-ДГ2=

2750000

м2

По т. Пифагора квадрат В6=(В2)2–(В1)2

7

БД=

1658,312

м

БД= корень(В6)

8

БЕ2=

3750000

м2

Вводим формулу: = (В7)2+(В5)2

9

БЕ=

1936,492

м

В9=корень(В8)

10

t=

77,46

мин

Время = Расстояние/Скорость, поэтому

вводим формулы: В10=В9/В3 и В11=(В1–В5)/В4

11

tег=

11,53846

мин

12

tобщ=

89,00

мин

В12=В11+В10 и В12 в итоге должна быть минимальной



Заполнив таблицу, выделяем ячейку В12 и выполняем команду: “Сервис” —> “Поиск решения”.

В появившемся диалоговом окне “Поиск решения” устанавливаем целевую ячейку $B$12 равную минимальному значению. Изменяя ячейку $B$5, добавляем ограничения: $B$5<=$B$1 и $B$5>=0.



После указания всех ограничений, задаем Параметры поиска решения: Линейная модель, Неотрицательные значения, ОК. После этого нажимаем команду “Выполнить”, предоставляя компьютеру ПК самостоятельно найти решение.

В результате решение задачи выглядит следующим образом: Для транспортировки неисправной техники в город эвакуатор должен попасть сначала в некоторую точку Е на дороге, удаленную от точки Д на 545,5447 метров, со скоростью 25 км/ч, а потом по дороге со скоростью 80 км/ч. При этом бригада рабочих проделает путь: БЕ+ЕГ = =37 км, а время, затраченное на доставку неисправной техники, будет наименьшим tобщее=0,94 мин.

 

А

В

С

1

ДГ=

25

км

2

БГ=

30

км

3

Vполя=

25

км/ч

4

Vдороги=

80

км/ч

5

ДЕ=Х

5,455447

км

6

БГ2-ДГ2=

275

км2

7

БД=

16,58312

км

8

БЕ2=

304,7619

км2

9

БЕ=

17,45743

км

10

tполя=

0,70

ч

11

tдороги=

0,244307

ч

12

tобщ=

0,94

ч

13

БЕГ

37,00

км


Провести исследование:

    1. Доказать, что путь БЕГ является самым коротким, сравнив его с путем БДГ и БГ.

    2. Какое условие доставки техники надо изменить, чтобы сократить время. Доказать.

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













































































































чения во влияющих ячейках. построением диаг

Культура

Площадь, га

^ Урожай, ц/га

Затраты, руб./га

Цена за 1 ц, руб.

Затраты, человеко-дней на 1 га.

1

x

10

50

6

2

2

y

15

80

8

10


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

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

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


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

  • задание целевой функции (ее надо максимизировать или минимизировать);

  • задание системы ограничений в форме линейных уравнений и неравенств;

  • требование неотрицательности переменных.


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

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

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

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

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

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

. (4)

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

- ограничения

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

Теперь заполним расчетную форму в табличном процессоре Excel.

Введем:

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

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




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





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




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


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





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

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



Провести исследование.

    1. В результате инфляции затраты рублей на гектар увеличились на 6%. Как изменится прибыль?

    2. В результате засушливого лета урожая собрали на 4% меньше, а затраты человеко-дней увеличились на 3 и 11 соответственно. Как изменится прибыль?

    3. Чтобы увеличить прибыль на 2%, какие условия производства надо изменить и как?

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

  1. Итоги.

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

  1. Домашнее задание.

Задача

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

^ Виды кормов

Содержание кормовых единиц в 1 кг кормов

Себестоимость кормов, в рублях.

Сено

0,5

1,5

Концентраты

1,0

2,5


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

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





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




Похожие:

Урок по теме «Решение оптимизационных задач в среде электронных таблиц exсel» icon1 Понятие оптимизационных задач, постановка задачи
Специалисты различных направлений часто сталкиваются с необходимостью решения оптимизационных задач. На практике встречаются разнообразные...
Урок по теме «Решение оптимизационных задач в среде электронных таблиц exсel» iconРешение инженерно-технических задач в среде Mathcad
Ниже приведены три примера решения типовых задач теплоэнергетики [68], которые дают представления об особенностях работы в среде...
Урок по теме «Решение оптимизационных задач в среде электронных таблиц exсel» iconУрок №3 Логика. Решение задач
Учимся составлять логические выражения из высказываний, определяем понятие “таблица истинности”, изучаем последовательность действий...
Урок по теме «Решение оптимизационных задач в среде электронных таблиц exсel» iconИерархическое планирование в оптимизационных задачах
...
Урок по теме «Решение оптимизационных задач в среде электронных таблиц exсel» iconРешение многокритериальной оптимизационной задачи распределения ресурсов (Евразийский национальный университет имени Л. Н. Гумилева, г. Астана)
Статья посвящена разработке математического и программного обеспечения решения многокритериальных оптимизационных задач распределения...
Урок по теме «Решение оптимизационных задач в среде электронных таблиц exсel» iconРешение задач по теме «Дифракция света»
Методы работы учителя и учащихся: демонстрационный эксперимент, беседа в сочетании с фронтальным экспериментом, решение задач
Урок по теме «Решение оптимизационных задач в среде электронных таблиц exсel» iconУрок физики, литературы, русского языка. Решение задач по теме: "Законы Ньютона"на литературном материале. Урок развития речи: сочинение рассказ
Учитель физики: Здравствуйте! Сегодня мы проводим интегрированный урок. Урок развития речи: сочинение-рассказ. Решение физических...
Урок по теме «Решение оптимизационных задач в среде электронных таблиц exсel» iconУрок по теме: "Формулы. Уравнения. Решение задач"
Дидактический материал: индивидуальные карточки у каждого учащегося “Решение уравнений”, “Квадрат и куб числа”, “Формулы”
Урок по теме «Решение оптимизационных задач в среде электронных таблиц exсel» iconРешение задач Учитель: Черняева Г. С
Урок математики в 1-м классе: "Закрепление табличных случаев сложения и вычитания чисел 1, Решение задач"
Урок по теме «Решение оптимизационных задач в среде электронных таблиц exсel» iconУрок по данной теме. Цель урока Предполагается, что к концу урока учащиеся будут уметь применять подобие при решении прикладных задач на определение расстояния до недоступной точки и определение высоты предмета
Создать условия для активизации познавательной деятельности учащихся через решение практических задач, умение выбирать правильное...
Разместите ссылку на наш сайт:
Уроки, сочинения


База данных защищена авторским правом ©izlov.ru 2000-2014
При копировании материала обязательно указание активной ссылки открытой для индексации.
связаться с нами