Гипермаркет знаний>>Информатика>>Информатика 11 класс>>Информатика: Компьютерный практикум: Работа 16. Решение задачи оптимального планирования в MS Excel

Компьютерный практикум

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

Цели работы:

• получение представления о построении оптимального плана диет линейного программирование;

• практическое освоение раздела MS Excel «Поиск решения» для построения оптимального плана.

Используемые программные средства: табличный процессор MS Excel.

Задание 1

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

• пирожки ( максимально возможно произвести 2000 шт. в день, если ничего больше непроизводится);

• шанежки (максимальный выпуск 1500 штук в день);

• булочки (максимальный выпуск 1200 штук в день);

• пирожные (максимальный выпуск 700 штук в день). Соотношение стоимости изделий таково: пирожок/булочка — 2/1; пирожок/шанежка — 1/2; пирожок/пирожное — 1/4.

Емкость склада равна 1100 изделий независимо от их вида.

Составь оптимальный план выпуска продукции исходя из той же цели — достижения максимальной выручки цеха. При решении задачи использовать средство Excel «Поиск решения».

Задание 2

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

Количество учащихся, которых следует отправить в поездки, таково:



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



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



Смысл чисел в таблице таков: если в ячейке Y2 стоит 600, то это значит, что поездка одного учащегося из района 2 в город У обходится в 600 рублей.

Необходимо составить такой план экскурсий, который:

• позволяет каждому из числа намеченных к поездке учащихся побывать на экскурсии;

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

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

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



Величины, стоящие в этой таблице, и являются объектами поиска. Так, x₂ есть число учащихся из района № 3, которые, по разрабатываемому плану, поедут в город X.

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

      x₁ + у₁ + z₁ = 300,

      х₂ + у₂ + z₂ = 250,                                                 (1)

      х₃ + у₃ + z₃ = 400, 

      х₄ + у₄+ z₄ = 350,

      х₅ + у₅+ z₅ = 200.

Второе условие: в каждый город поедут столько учащихся, сколько этот город в состоянии принять:

     х₁ + х₂ + хз + х₄ + х₅ = 400,

     у₁ + у₂ + у₃ + у₄ + у₅ = 500                                    (2)

    z1 + z₂ +z₃ + z₄ + z₅ = 600.

Кроме того, искомые величины, разумеется, неотрицательны:

     х₁ >= 0,..., х₅ >= 0, у_1>= 0,..., у₅ >= 0, z₁ >= 0,..., z₅ >= 0.                                     (3)

Теперь запишем общую стоимость расходов на экскурсии. Поскольку привезти, например, на экскурсию х₁ учащихся стоит хг · 500 рублей (см. таблицу стоимости поездки), то общие расходы составят:

      S = х₁ • 500 + х₂ • 700 + х₃ • 750 + х₄ • 1000 + х₅ • 1100 + у₁ • 700 + у₂ • 600 + у₃ • 400 +                       (4)

      + у₄ • 500 + у₅ ·•800 + z₁ •1200 + z_{2 •} 1000 + z₃ • 800 + z₄ • 600 + z₅ • 500.
 
Итак, мы имеем все для полной математической формулировки задачи: требуется найти наименьшее значение функции (4) при условии, что входящие в нее переменные удовлетворяют системам уравнений (1) и (2) и неравенств (3).

Это — весьма непростая задача. Однако ее вполне можно решить (как и существенно более сложные задачи) с помощью средств «Поиск решений» программы Excel, которым вам и надлежит воспользоваться.
Приведем результат решения этой задачи:

Итак, в город X поедут на экскурсию 300 учащихся из района №1 и 100 учащихся из района № 2, в город У — 100 учащихся из района № 2 и 400 из района № 3, в город Z — 50 учащихся из района № 2, 350 из района № 4 и 200 — из района № 5.

Семакин И.Г., Хеннер Е.К., Информатика и ИКТ, 11

Отослано читателями из интернет-сайтов

Содержание урока
 конспект урока                       
 опорный каркас  
 презентация урока
 акселеративные методы 
 интерактивные технологии 

Практика
 задачи и упражнения 
 самопроверка
 практикумы, тренинги, кейсы, квесты
 домашние задания
 дискуссионные вопросы
 риторические вопросы от учеников

Иллюстрации
 аудио-, видеоклипы и мультимедиа 
 фотографии, картинки 
 графики, таблицы, схемы
 юмор, анекдоты, приколы, комиксы
 притчи, поговорки, кроссворды, цитаты

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

Совершенствование учебников и уроков
 исправление ошибок в учебнике
 обновление фрагмента в учебнике 
 элементы новаторства на уроке 
 замена устаревших знаний новыми 

Только для учителей
 идеальные уроки 
 календарный план на год  
 методические рекомендации  
 программы
 обсуждения


Интегрированные уроки

Если у вас есть исправления или предложения к данному уроку, напишите нам.

Если вы хотите увидеть другие корректировки и пожелания к урокам, смотрите здесь - Образовательный форум.