Гипермаркет знаний>>Информатика>>Информатика 11 класс>>Информатика: Использование MS Excel для решения задачи оптимального планирования

                                          Использование MS Excel для решения задачи оптимального планирования

       В математическом программировании существуют свои сложные методы решения задач. Их изучение не входит в наши планы. Поступим так же, как поступали и раньше — воспользуемся тем, что в программу Excel возможности решения задач математического программирования заложены и можно находить решение, не владея его механизмом.

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

      Вначале надо подготовить электронную таблицу к решению задачи оптимального планирования. В режиме отображения формул таблица показана на рис. 2.20. Ячейки В5 и С5 зарезервированы соответственно для значений х (план по изготовлению пирожков) и у (план по изготовлению пирожных). Ниже этих ячеек представлена система неравенств , определяющая ограничения на искомые решения. Неравенства разделены на левую часть (столбец В) и правую часть (столбец D). Знаки неравенств в столбце С имеют чисто оформительское значение. Целевая функция' занесена в ячейку В15.
 

           Рис. 2.20. Таблица, подготовленная к вычислению оптимального плана

      Теперь следует вызвать программу оптимизации «Поиск решения» и сообщить ей, где расположены данные. Для этого надо выполнить команду => Сервис => Поиск решения. На экране откроется соответствующая форма (рис. 2.21).

             Рис. 2.21. Начальное состояние формы «Поиск решения»

      Далее надо выполнить следующий алгоритм:

       1. Ввести координату ячейки с целевой функцией. В нашем случае это В15. (Заметим, что если перед этим установить курсор на ячейку В15, то ввод произойдет автоматически).

      2. Поставить отметку «максимальному значению», то есть сообщить программе, что нас интересует нахождение максимума целевой функции.

     3. В поле «Изменяя ячейки» ввести В5:С5, то есть сообщать, какое место отведено под значения переменных  плановых показателей.

     4. В поле «Ограничения» надо ввести информацию о неравенствах-ограничениях, которые имеют вид B10<=D10; B11<=D11; В12>=D12; В13>=D1З. Ограничения вводятся следующим образом:

      => щелкнуть по кнопке «Добавить»;

в появившемся диалоговом окне «Добавление ограничения» ввести ссылку на ячейку В10, выбрать из меню знак неравенства <= и ввести ссылку на ячейку D10; снова щелкнуть по кнопке «добавить» и аналогично ввести второе ограничение B11<=D11 и так далее. В конце надо щелкнуть по кнопке ОК.
Пчмпк р
Восстэкеип,
5.    Закрыть диалоговое окно «Добавление ограничения».
Снова появится форма «Поиск решения» (рис. 2.22).
 



Рис. 2.22. Форма «Поиск решения» после ввода информации
 
Математическое моделирование в планировании и управлении:
 
133
 


6. Теперь надо дать последние указания: задача является линейной (это многократно облегчит программе ее реше¬ние). Для этого следует щелкнуть по кнопке «Парамет¬ры* — появится форма «Параметры поиска решения» (рис. 2.23).
 
Подомни* 1У>исхл г***«
 

а*}
 

 
Максимальное время:    11 СЮ     секунд
Предельное *»кло итераций: ) 100
 

ПК

От немо
 
Относительная погрешность: |o,COCOGl
Дргтустииое отктюн?***:       ]5~~
Сходипость:    ДОЮ
 



%
 
Загрузить модель... Сохранить модель...
 

 
Р гьщеЛея модель
 
" Автоматическое масштабах»».*»*;
 
Г" Неотрицетвльнув значения    Г" Показывать результаты итераций
 
Оценки   
(• линоииая
С квадратичная
Г
Ра>юсти

црнгрюные
 
Метод поиска -&*>тона f сопряженных градиентов
 


Рис. 2.23. Форма «Параметры поиска решения»
7.    Надо выставить флажок на переключателе «Линейная модель». Остальная информация в форме «Параметры поиска решения» служебная, автоматически устанавли¬ваемые значения нас устраивают, и вникать в их смысл мы не будем. Следует щелкнуть по кнопке ОК, что воз¬вратит нас в форму «Поиск решения».
8.    Вся информация введена. Далее надо щелкнуть по кноп¬ке «Выполнить» — мгновенно в ячейках В5 и С5 появит¬ся оптимальное решение (числа 600 и 100), а также число 800 в ячейке В15 — максимальное значение целевой функции (рис. 2.24).
 
    A    В    С    D
1    Оптимальное плаакронанке           
2               
3    Плановые показатели           
4        X (пирожки)    Y (пирожные)   
5        600    100   
               
7    Ограничения           
8               
9        Левая часть    Знак    Правая наешь
10    Время
производства:    1000        1000
11    Общее количество:    700    <=    700
12    Положительность X:    600    >~    0
13    Положительность У:    100    >«=    0
14               
15    Целевая функция    800       
j 16_               



Кроме того, на экране появилась еще одна форма — «Ре¬зультаты поиска решения» (рис, 2.25).
На первом этапе освоения возможностей программы на эту форму можно не обращать внимания (хотя в принципе в ней может оказаться очень полезная информация).
Итак, в результате применения инструмента «Поиск ре¬шения», мы получим следующий оптимальный план днев¬ного производства кондитерского цеха: нужно выпускать 600 пирожков и 100 пирожных. Эти плановые показатели соответствуют положешю точки В на рис- 2.19. В этой точ¬ке значение целевой функции /(600,100) = 800. Если один пирожок стоит 2 рубля, то полученная выручка составит 1600 рублей.
Решение, которое мы получили, вполне разумно как с экономической точки зрения, так и с медицинской. Много сладкого — вредно для здоровья, а пирожки и сытнее, и по¬лезнее.
Полученная электронная таблица и настроенная на нее сервисная функция «Поиск решения» являются средством, с помощью которого можно решать задачу оптимального планирования при меняющихся условиях. Например, мо¬жет измениться длина рабочего дня. Тогда надо внести но¬вое значение в ячейку Ш0, и оптимальный план автомати¬чески пересчитается. Также может измениться допустимое суммарное число изделий в ячейке D11.
Представьте себе, что в вашей школе учатся неисправи¬мые сладкоежки. И, кроме всех прочих ограничений, перед кондитерским цехом ставится обязательное условие; число пирожных должно быть не меньше числа пирожков. При та¬кой постановке задачи система неравенств (а) примет вид:
[* + 4у i 1000; х + у \ 700;
pics
У ] £•
Соответствующее изменение легко внести в электронную таблицу. Для этого достаточно в ячейке ШЗ вместо 0 запи¬сать В5. Результаты поиска решения будут следующими: х = 200, у = 200, f{xty) = 600. Таким планом вряд ли будет? доволен директор кондитерского цеха, поскольку потери прибыли окажутся очень существенными.
Следует иметь в виду, что при решении подобных зада** могут возникнуть проблемы, о которых мы здесь не говори¬ли. Например, искомого оптимального решения может вовсе не существовать — тогда программа об этом сообщит. Одна ко мы не ставили перед собой цели детально разобраться в задачах оптимального планирования и методах математи¬ческого программирования. Достаточно того, что вы позна¬комились с постановкой таких задач и с компьютерными средствами для их решения.

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

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

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

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

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

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


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

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

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