Версия 20:04, 25 августа 2010

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

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

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

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

      Вначале надо подготовить электронную таблицу к решению задачи оптимального планирования. В режиме отображения формул таблица показана на рис. 2.20. Ячейки В5 и С5 зарезервированы соответственно для значений х (план по изготовлению пирожков) и у (план по изготовлению пирожных). Ниже этих ячеек представлена система неравенств ', определяющая ограничения на искомые решения. Неравенства разделены на левую часть (столбец В) и правую часть (столбец D). Знаки неравенств в столбце С имеют чисто оформительское значение. Целевая функция' 'занесена в ячейку В15.
 
Рис. 2-20. Таблица, подготовленная к вычислению оптимального плана
Теперь следует вызвать программу оптимизации «Поиск решения» и сообщить ей, где расположены данные. Для это¬го надо выполнить команду => Сервис =з» Поиск решения. На экране откроется соответствующая форма (рис. 2.21).
П«мск решения
 
Установить деловую    1.%Н1ЯИ \i
Равной; (• досимальноиу эм*ч*»#о       £ч*ю»*»о: fo

Г* мтпдльмоггу эндемию
 


Закрыть
 
Ц-^-аиетры

33    Предпопо^иь [
3D ДСБМТЬ JJ        УДАЛИТЬ



Рис. 2.21. Начальное состояние формы "Поиск решения»
Далее надо выполнить следующий алгоритм;
1.    Ввести координату ячейки с целевой функцией. В нашем случае это В15. (Заметим, что если перед этим устано¬вить курсор на ячейку В15, то ввод произойдет автомати¬чески).
2.    Поставить отметку «максимальному значению», то есть сообщить программе, что нас интересует нахождение максимума целевой функции.
3.    В поле «Изменяя ячейки» ввести В5:С5, то есть сооб¬щать, какое место отведено под значения переменных -плановых показателей.
4.    В поле «Ограничения» надо ввести информацию о нера¬венствах-ограничениях, которые имеют вид B10<=D10; B11<HD11; В12>=Ш2; В13>=ШЗ. Ограничения вводят¬ся следующим образом:
^> щелкнуть по кнопке «Добавить»;
в появившемся диалоговом окне «Добавление ограниче¬ния» ввести ссылку на ячейку В10, выбрать из меню знак неравенства <=* и ввести ссылку на ячейку ШО; снова щелкнуть по кнопке «добавить» и аналогично ввести второе ограничение B11<=D11 и так далее. В конце надо щелкнуть по кнопке ОК.
Пчмпк р
Восстэкеип,
5.    Закрыть диалоговое окно «Добавление ограничения».
Снова появится форма «Поиск решения» (рис. 2.22).
 

|*0|5:$С$5    3J    Пр*ДПОЛО£ИТЬ |

       

       
       

       
       




Рис. 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

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

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

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

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

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


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

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

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