Урок "Використання електронних таблиць для розв'язування задач оптимізації"

Про матеріал
Тема уроку: Використання електронних таблиць для розв'язування задач оптимізації. Тип уроку: формування і комплексного застосування знань, умінь та навичок. Архів містить розширений конспект уроку, презентацію до уроку, технологічну картку уроку, завдання для роботи в групах, файли-заготовки для виконання практичних завдань та групової роботи.
Перегляд файлу
Перегляд файлу тимчасово недоступний.
Перегляд файлу

 

КОНСПЕКТ УРОКУ

Використання електронних таблиць для розв'язування задач оптимізації

 

МЕТА УРОКУ:

Навчальна: 

§   ознайомити з основними технологічними прийомами при вирішенні задач оптимізації та побудови комп'ютерних математичних моделей; 

§   формувати практичні вміння та навички використання електронних таблиць для розв'язування задач оптимізації;

§   сприяти набуттю навичок роботи з надбудовами Excel: навчити використовувати інструмент Пошук рішення;

§   сприяти розвитку вмінь ліцеїстів самостійно, в комплексі застосовувати знання, вміння та навички роботи з електронними таблицями, здійснювати їх перенесення у нові умови;

§   формувати рівень ІК - компетентності ліцеїстів; Розвиваюча: 

§   створити умови для прояву пізнавальної активності ліцеїстів, розвитку логічного мислення - уміння виділяти головне, істотне, узагальнювати наявну інформацію;

§   розвивати критичне мислення, набуття досвіду дослідницької діяльності, розвиток комунікативності, вміння працювати в групі;  Виховна: 

§   виховувати творчий підхід до роботи, бажання експериментувати;

§   виховувати почуття взаємодопомоги, колективізму, професійної орієнтації та підготовки до трудової діяльності; 

§   сприяти вихованню творчої активності, самостійності, потреби у нових  знаннях.

 

ОБЛАДНАННЯ ТА НАОЧНІСТЬ: дошка, комп'ютер, інструкції з ТБ в комп'ютерному кабінеті, роздатковий матеріал.

ПРОГРАМНЕ ЗАБЕЗПЕЧЕННЯ: Microsoft Office PowerPoint 2007, Microsoft Office Excel 2007.

ТИП УРОКУ: формування і комплексного застосування знань, умінь та навичок.

 

 

             

 

Схематичний план уроку

 

І. Організаційний етап......................................................................................................         1 хв. II. Актуалізація і корекція опорних знань, умінь і навичок ...........................................             5 хв.

ІІІ. Мотивація навчальної діяльності, усвідомлення ліцеїстами практичної значущості знань, умінь та навичок, ознайомлення ліцеїстів із темою, метою і завданнями уроку  ...........             2 хв.

       ІV. Засвоєння нових знань і вмінь ...................................................................................       10 хв.

V.                        Практичне застосування знань, формування вмінь та навичок  у стандартних ситуаціях  (на основі виконання тренувальних вправ за зразком - заздалегідь підготовленою технологічною карткою)  ...........................................................................................................................  10 хв.

VI.                      Перенесення здобутих знань, умінь і навичок у нестандартні ситуації і використання їх на творчому рівні  .......................................................................................................................  15 хв.

VII.                    Підведення підсумків уроку; повідомлення і пояснення завдання на самостійну підготовку.

 .................................................................................................................................................      2 хв.

 

ХІД УРОКУ

І. ОРГАНІЗАЦІЙНИЙ ЕТАП

Шикування ліцеїстів, рапорт зам. командира взводу (командира відділення). Перевірка ліцеїстів, привітання.

 

II. АКТУАЛІЗАЦІЯ І КОРЕКЦІЯ ОПОРНИХ ЗНАНЬ, УМІНЬ І НАВИЧОК

 

 

 Проведення тестування засобами програми MyTestX.  Ознайомлення з критеріями оцінювання.

 

Перед початком вивчення нової теми проведемо повторення матеріалу, який ми вивчили на попередньому уроці. 

Для цього скористаємось програмою ТЕСТУВАННЯ.

 

ОЦІНЮВАННЯ вашої роботи на уроці буде складатись з трьох складових:

1.Оцінка з тестування

2.Оцінка практичної роботи

3. Самооцінка. Критерієм самооцінки буде – як ви оцінюєте свою роботу, наскільки ви оволоділи новими засобами роботи в ЕТ. Оцінку заносимо в он-лайн журнал оцінювання Google.

 

На робочому столі відкрийте папку ЕТ_11 клас, де знаходиться ярлик тестової програми та файл тестування TEST_ET.mtf

 

ІІІ.МОТИВАЦІЯ НАВЧАЛЬНОЇ ДІЯЛЬНОСТІ, УСВІДОМЛЕННЯ ЛІЦЕЇСТАМИ ПРАКТИЧНОЇ ЗНАЧУЩОСТІ ЗНАНЬ, УМІНЬ ТА НАВИЧОК, ОЗНАЙОМЛЕННЯ ЛІЦЕЇСТІВ ІЗ ТЕМОЮ, МЕТОЮ І ЗАВДАННЯМИ УРОКУ 

 

 

(Очікувані відповіді: найбільш сприятливий, найкращий спосіб, рішення, яке буде найкращим серед інших)

 

У різних областях своєї діяльності людині регулярно доводиться зустрічатись з проблемою прийняття рішень для досягнення тих чи інших цілей. 

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

Для досягнення цілей людині необхідно приймати певні рішення, як правило, властиве прагнення вибрати найкраще для нього рішення (оптимальне).

Як сказав американський журналіст Евві Неф, Кожна проблема має вирішення. Єдина складність полягає в тому, щоб його знайти. 

 

 

Сьогодні ми спробуємо з вами навчитись шукати рішення використовуючи сучасні інформаційні технології, а саме редактор електронних таблиць MS Excel.

 

Отже, тема сьогоднішнього уроку: «Використання електронних таблиць для розв'язування задач оптимізації». 

 

 

В результаті нашої роботи ви отримаєте ЗНАННЯ про способи розв’язування оптимізаційних задач та ВМІННЯ вирішувати оптимізаційні задачі засобами табличного процесора MS Excel.

 

 

 

 

ІV. ЗАСВОЄННЯ НОВИХ ЗНАНЬ І ВМІНЬ

Вч. – Як було вже зазначено

 

 

 

 

У багатьох задачах вимагається не просто знайти який-небудь розв'язок, а підібрати серед усіх розв'язків найкращий (оптимальний). 

 

Йдеться про такі задачі: 

підбір збалансованого раціону харчування, оптимізація асортименту продукції, оптимізація транспортних перевезень та багато інших. 

Їх називають оптимізаційними

 

 

 

 

 

 

Перш ніж приступати до розв'язування оптимізаційної задачі, потрібно описати її в математичному вигляді, тобто побудувати її математичну модель.

Де за допомогою математичних формул, залежностей описати розв’язок задачі:

  Змінні – Невідомі величини, які треба знайти 

  Цільова функція – Величина, яку треба максимізувати або мінімізувати 

  Критерій – Вимога максимізації/мінімізації цільової функції 

  Обмеження – Умови, яким мають задовольняти змінні 

 

 

Алгоритм розв’язання оптимізаційної задачі  • Введення позначень. 

  Створення цільової функції та критерію. 

  Складання системи обмежень. 

  Розв'язання задачі.

 

Отже,             зараз ми      з          вами спробуємо на       конкретному          прикладі розв’язати оптимізаційну задачу.

1)                Створимо її математичну модель

2)                Реалізуємо модель на комп’ютері

3)                Спробуємо знайти розв’язок використовуючи можливості Microsoft Office Excel 2007

 

 

Умова є в технологічних картках. Для полегшення розуміння візуалізуємо умову. Ми розглядаємо транспортну задачу закритого типу (збалансовану).

 

Спробуймо розробити математичну модель. Для цього введемо позначення.

Нехай Х – об’єм перевезень (к-сть тон)

Для того щоб орієнтувались, яким саме шляхом буде постачатись пальне, використаємо індекси.

 

 min

 

Визначимо обмеження на запаси:

х11 + х12+ х13 =240 х21 + х22+ х23 =120

 

 

Визначимо обмеження на замовлення:

х11 + х21=150

х12 + х22 =120

х13 + х23 =90

 

 

 

Тепер спробуймо створити комп’ютерну модель задачі. Для цього в редакторі електронних таблиць створимо таблицю та заповнюємо її (КОРИСТУЄМОСЬ ТЕХНОЛОГІЧНОЮ КАРТКОЮ)

 

 

 

Демонструю комп’ютерну модель

 

V. ПРАКТИЧНЕ ЗАСТОСУВАННЯ ЗНАНЬ, ФОРМУВАННЯ ВМІНЬ ТА НАВИЧОК  У СТАНДАРТНИХ СИТУАЦІЯХ 

(на основі виконання тренувальних вправ за зразком, заздалегідь підготовленою технологічною карткою) 

 

а . Практичне виконання завдання

Для роботи використовуємо технологічну картку.

Розв’язання оптимізаційних задач за допомогою Microsoft Office Excel 2007

1.       Згідно з завданням, визначте і заповніть області початкових і розрахункових даних

(використовуйте файл – OPTIMIZATION1.xlsх, розташований на робочому столі)

2.       На Листі 1 заповніть розрахункову таблицю. У відповідні комірки уведіть формули:

В18 - цільову функцію (витрати); В11:В12 – формули обмеження запасів;

В14:В16 – формули обмеження замовлень.

 

 

 

 

 

 

Інструментом для розв’язання оптимізаційних задач у середовищі Microsoft Office Excel 2007є надбудова Пошук рішення.

 

 

Пошук рішень є частиною блоку завдань, який іноді називають аналіз "щоякщо". Процедура пошуку рішення дозволяє знайти оптимальне значення формули міститься в комірці, яка називається цільовою. Ця процедура працює з групою комірок, прямо або побічно пов'язаних з формулою в цільовій комірці. Щоб отримати за формулою, що міститься в цільовій комірці, заданий результат, процедура змінює значення у комірках. Щоб звузити безліч значень, які використовуються в моделі, застосовуються обмеження. Ці обмеження можуть посилатися на інші впливові комірки.

Демонструю роботу надбудови

 

3.       Використавши вказівку меню Дані  Аналіз  Пошук рішення (Solver), заповніть поля вікна діалогу Пошук рішення:  у поле Установити цільову комірку розташуйте посилання на комірку із цільовою функцією — $В$18; у полі Рівній перемикач поставте у положення мінімальному значенню; у полі Змінюючи комірки - $В$2:$В$7; у полі Обмеження вкажіть обмеження, що накладаються на аргументи, для чого викличте вікно Додати аргументи

 

у поле Посилання на комірку введіть ліву частину обмеження, у поле Обмеження — праву частину нерівності, а між ними вкажіть тип відношення між лівою і правою частинами:

$В$11=$C$11

$В$12=$C$12

$В$14=$C$14

$В$15=$C$15

$В$16=$C$16

$В$2:$В$7>=0

 

4.       Натисніть кнопку Виконати. У вікні діалогу Результати пошуку рішення оберіть тип звіту - Результати.

 

 

5.       Проаналізуйте результати.

VI. ПЕРЕНЕСЕННЯ ЗДОБУТИХ ЗНАНЬ, УМІНЬ І НАВИЧОК У

НЕСТАНДАРТНІ СИТУАЦІЇ І ВИКОРИСТАННЯ ЇХ НА ТВОРЧОМУ РІВНІ 

 

b . Самостійна робота в групах

Обов’язки помічників-консультантів:

*  Не виконують завдання безпосередньо у середовищі Microsoft Office Excel 2007.

*  Керують роботою групи, консультують, намагаються досягнути стовідсоткового виконання завдання учасниками групи

*  Доповідають про результати роботи групи, демонструють виконане завдання з використанням ретрансляції на мультимедійну дошку засобами HiClass, проводять аналіз результатів.

 

Хід роботи

 

1. Самостійно розробіть математичну модель даної задачі.

Цільова функція______________________________________________________________________

Обмеження

_____________________________________________________________________________________________________________________________ _____________________________________________________________________________________________________________________________ _____________________________________________________________________________________________________________________________

2.   Запустіть вказаний файл MS Excel.

3.   На основі розробленої математичної моделі введіть всі необхідні дані.

4.   За допомогою команди Дані  Аналіз  Пошук рішення (Solver), завантажте надбудову. 

5.   За допомогою діалогового вікна «Пошук рішення» отримаєте необхідні результати і збережіть знайдене рішення.

Оцінку за виконання практичних завдань заносимо в он-лайн журнал оцінювання Google.

VII. ПІДВЕДЕННЯ ПІДСУМКІВ УРОКУ; ПОВІДОМЛЕННЯ І ПОЯСНЕННЯ ЗАВДАННЯ НА САМОСТІЙНУ ПІДГОТОВКУ. 

Заносимо в он-лайн журнал оцінювання Google самооцінку

Д/з 

Придумати задачу, яку можна вирішити надбудовою «Пошук рішення».

 

 

Перегляд файлу
Перегляд файлу тимчасово недоступний.
Перегляд файлу

 



лат. Optimus - найкращий


• вибір найкращого варіанту з можливих для досягнення найбільшої ефективності будьякого процесу.

На даний час оптимізація знаходить своє застосування в математиці, економіці, логістиці, техніці, сфері бізнесу,  та в будь-якій

іншій сфері людської діяльності

       


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







У двох пунктах відправлення А1 і А2 знаходиться відповідно 240 і

120 т палива. В пункти В1, В2, В3 потрібно доставити 150 відповідно, 120 і 90 т палива. Вартість перевезення тонни палива з пункту А1 в пункти В1, В2, В3 становить відповідно 6, 6 і 8 грошових одиниць, а з пункту А2 - 8, 4 і 5 грошових одиниць. Розробити оптимальний план перевезень палива так, щоб сума транспортних витрат була найменшою.


Закритого типу (збалансована) транспортна задача










       


є одним із найефективніших інструментів Microsoft Excel 2007 вирішення оптимізаційних задач.







Перегляд файлу

У двох пунктах відправлення А1 і А2 знаходиться відповідно 240 і 120 т палива. В пункти В1, В2, В3 потрібно доставити 150 відповідно, 120 і 90 т палива. Вартість перевезення тонни палива з пункту А1 в пункти В1, В2, В3 становить відповідно 6, 6 і 8 грошових одиниць, а з пункту А2 - 8, 4 і 5 грошових одиниць. 

Розробити оптимальний план перевезень палива так, щоб сума транспортних витрат була найменшою.

Цільова функція_______________________________________________________________________

Обмеження

_______________________________________________________________________________________________________

_______________________________________________________________________________________________________ _______________________________________________________________________________________________________ _______________________________________________________________________________________________________

_______________________________________________________________________________________________________

_______________________________________________________________________________________________________ _______________________________________________________________________________________________________

______________________________________________________________________________________________________

 

             

РОЗВ’ЯЗАННЯ ОПТИМІЗАЦІЙНИХ ЗАДАЧ ЗА ДОПОМОГОЮ MICROSOFT OFFICE EXCEL 2007

УВАГА! ПІД ЧАС РОБОТИ З КОМП’ЮТЕРОМ ДОТРИМУЙТЕСЯ ПРАВИЛ БЕЗПЕКИ  ТА САНІТАРНО-ГІГІЄНІЧНИХ НОРМ.

1.     Згідно з завданням, визначте і заповніть області початкових і розрахункових даних (використовуйте файл – OPTIMIZATION1.xlsх, розташований на робочому столі)

2.     На Листі 1 заповніть розрахункову таблицю. У відповідні комірки уведіть формули:

 

3.     Використавши вказівку меню Дані  Пошук рішення (Solver), заповніть поля вікна діалогу Пошук рішення:

*           у поле Установити цільову комірку розташуйте посилання на комірку із цільовою функцією — $В$18;  у полі Рівній перемикач поставте у положення мінімальному значенню;  у полі Змінюючи комірки - $В$2:$В$7;

*           у полі Обмеження вкажіть обмеження, що накладаються на аргументи, для чого викличте вікно Додати аргументи

 

у поле Посилання на комірку введіть ліву частину обмеження, у поле Обмеження — праву частину нерівності, а між ними вкажіть тип відношення між лівою і правою частинами:

$В$11=$C$11

$В$12=$C$12

$В$14=$C$14

$В$15=$C$15

$В$16=$C$16

$В$2:$В$7>=0

 

4.     Натисніть кнопку Виконати. У вікні діалогу Результати пошуку рішення оберіть тип звіту - Результати.

 

 

5.     Проаналізуйте результати.

             

 

САМОСТІЙНА РОБОТА

1. Самостійно розробіть математичну модель отриманої задачі.

Цільова функція_______________________________________________________________________

Обмеження

_______________________________________________________________________________________________________ _______________________________________________________________________________________________________

_______________________________________________________________________________________________________ _______________________________________________________________________________________________________ _______________________________________________________________________________________________________

_______________________________________________________________________________________________________ _______________________________________________________________________________________________________ _______________________________________________________________________________________________________ _______________________________________________________________________________________________________

______________________________________________________________________________________________________

 

2.  Запустіть вказаний учителем файл MS Excel.

3.  На основі розробленої математичної моделі введіть всі необхідні дані.

4.  За допомогою команди Дані  Аналіз  Пошук рішення (Solver), завантажте надбудову. 

5.  За допомогою діалогового вікна «Пошук рішення» отримаєте необхідні результати і збережіть знайдене рішення.

 

 

Перегляд файлу
Перегляд файлу тимчасово недоступний.
Перегляд файлу

 

UNIFORM

* Файл UNIFORM.xlsx

 

https://pbs.twimg.com/media/CCss1x5VEAAKR_n.jpgАтельє займається пошиттям обмундирування для військових - костюми літні польові, костюми зимові польові, куртки літні, куртки зимові, мішки спальні.

На складі є запас фурнітури - тканина, ґудзики, нитки, тасьма х/б, тканина підкладкова, утеплювач.

Виникла необхідність звільнити складські приміщення.

Що саме і в яких кількостях потрібно виготовити, щоб отримати максимальний прибуток та оптимально використати ресурси на складі?

Мінімальне замовлення кожного виробу становить не менше 5 шт.

Витрата матеріалів на пошиття одиниці товару і ціни наведені в таблиці:

 

Вид фурнітури

Костюм літній польовий

Костюм зимовий польовий

Куртка літня

Куртка зимова

Мішок спальний

Ресурс

Тканина (м)

5,04

5,6

3,2

3,9

5,9

300

Ґудзики (шт.)

12

15

13

14

7

800

Нитки (м)

600

650

600

620

500

40000

Тасьма х/б (м)

1,76

1,85

1,03

1,45

3,1

120

Тканина підкладкова (м)

0,4

7,3

4

4,5

8,4

300

Утеплювач (м)

0

6,9

0

7,2

4,4

300

Ціна за одиницю  (грн.)

280

450

205

298

112

 

 


 

 

FOOD

* Файл FOOD.xlsx

 

http://www.motherearthnews.com/~/media/Images/MEN/Editorial/Articles/Magazine%20Articles/1977/11-01/Fresh%20Vegetable%20Salad%20Recipe/fresh-vegetable-salad-recipe-2.jpgНавесні для вітамінізації харчування військовослужбовців у їдальні військової частини треба обов'язково готувати салати зі свіжих овочів.

У їдальні готують салати п’яти видів (Салат А, Салат В і Салат С, Салат D, Салат Е) з використанням під час приготування інгредієнтів шести видів (капусти, моркви, огірків, помідорів, редиски, цибулі). Витрата інгредієнтів в грамах на кожну страву задається наступною таблицею:

Розподіл кількості кожного продукту на порцію у грамах

Вид інгредієнта

Салат А

Салат В

Салат С

Салат D

Салат Е

Капуста

30

50

0

0

0

Морква

30

35

0

0

0

Огірки

30

0

40

30

0

Помідори

0

0

40

30

0

Редиска

0

0

0

30

60

Цибуля

10

15

20

10

40

Щодня зі складу військової частини їдальня отримує 3 кг капусти, 2 кг моркви, по 4 кг огірків та помідорів, по 1 кг редису та 3 кг цибулі.

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

Також забезпечте різноманітність асортименту (мінімальна кількість порцій кожного салату - 10). Врахуйте, що оскільки салат С є улюбленим салатом військовослужбовців цієї військової частини, то загальна кількість його порцій має перевищувати усі інші.

 

Зміст слайдів
Номер слайду 1

Номер слайду 2

Кожна проблема має вирішення. Єдина складність полягає в тому, щоб його знайти. Евві Неф, амер. журналіст

Номер слайду 3

Використання електроннихтаблиць для розв'язування задач оптимізації

Номер слайду 4

Оптимізація  - вибір найкращого варіанту з можливих для досягнення найбільшої ефективності будь-якого процесу. На даний час оптимізація знаходить своє застосування в математиці, економіці, логістиці, техніці, сфері бізнесу, та в будь-якій іншій сфері людської діяльності лат. Optimus - найкращий

Номер слайду 5

Оптимізаційні задачізадачі, мета яких полягає в знаходженні найкращого (оптимального) з точки зору деякого критерію (критеріїв) варіанту використання наявних ресурсів (матеріальних, часових і ін.).

Номер слайду 6

Математична модель оптимізаційної задачі

Номер слайду 7

Алгоритм розв’язання оптимізаційної задачіКрок 1 Крок 3 Крок 2 Створення цільової функції та критерію. Складання системи обмежень. Введення позначень. Крок 4 Розв’язання задачі

Номер слайду 8

Транспортна задача

Номер слайду 9

Транспортна задача. У двох пунктах відправлення А1 і А2 знаходиться відповідно 240 і 120 т палива. В пункти В1, В2, В3 потрібно доставити 150 відповідно, 120 і 90 т палива. Вартість перевезення тонни палива з пункту А1 в пункти В1, В2, В3 становить відповідно 6, 6 і 8 грошових одиниць, а з пункту А2 - 8, 4 і 5 грошових одиниць. Розробити оптимальний план перевезень палива так, щоб сума транспортних витрат була найменшою. Закритого типу (збалансована) транспортна задача

Номер слайду 10

Постановка та аналіз задачіА1 А2240 т120 т. В1 В2 В3150 т90 т668458120 т

Номер слайду 11

Розробка математичної моделіА1 А2240 т120 т. В1 В2 В3150 т90 тх11х12х13х22х23х21120 т. Якщо Х – об’єм перевезень

Номер слайду 12

Розробка математичної моделіА1 А2240 т120 т. В1 В2 В3150 т90 т6*6*8*4*5*8*120 т6*х11+6*х12+8*х13+8*х21+4*х22+5*х23  min. Витрати на перевезення (цільова функція)х22х23х11х12х13х21

Номер слайду 13

В1 В2 В3 Розробка математичної моделіА1 А2 Обмеження на запаси150 т90 т120 тх11х12х13х22х23х21++=240 т120 т++=

Номер слайду 14

В1 В2 В3 Розробка математичної моделіА1 А2240 т120 тх11х12х13х22х23х21 Обмеження на замовлення+=150 т+=+=90 т120 т

Номер слайду 15

Комп’ютерне моделювання

Номер слайду 16

Пошук рішення (Solver) є одним із найефективніших інструментів Microsoft Excel 2007 вирішення оптимізаційних задач. “що-якщо”

Номер слайду 17

Комп’ютерне моделювання

Номер слайду 18

Комп’ютерне моделювання

Номер слайду 19

Аналіз результатів

Номер слайду 20

Інтерпретація результатів А1 А2240 т120 т. В1 В2 В3150 т90 т150 т120 тх1190 тх1230 тх2290 тх23 Витрати – 2010 грошових одиниць

Номер слайду 21

Практичне завдання

Номер слайду 22

Номер слайду 23

Завдання на самостійну підготовку. Придумати задачу, яку можна вирішити надбудовою «Пошук рішення».

Номер слайду 24

zip
Додано
22 травня 2020
Переглядів
1418
Оцінка розробки
Відгуки відсутні
Безкоштовний сертифікат
про публікацію авторської розробки
Щоб отримати, додайте розробку

Додати розробку