Меню

Как в excel называется средство применяемое для решения задач оптимального планирования

Знания в формате 4 и 5

Меню навигации

Пользовательские ссылки

Информация о пользователе

Вы здесь » Знания в формате 4 и 5 » 11 класс (Семакин И.Г.) » П/р № 3.19. Решение задач оптимального планирования в Microsoft Excel

П/р № 3.19. Решение задач оптимального планирования в Microsoft Excel

Сообщений 1 страница 1 из 1

Поделиться12013-02-07 09:28:01

  • Автор: Евгений Александрович
  • Администратор
  • Зарегистрирован : 2012-02-01
  • Приглашений: 0
  • Сообщений: 468
  • Провел на форуме:
    25 дней 12 часов
  • Последний визит:
    Вчера 14:41:05

Цель работы: получение представления о построении оптимального плана методом линейного программирования; практическое освоение раздела Microsoft Excel «Поиск решения» для построения оптимального плана.
Используемое программное обеспечение: табличный процессор Microsoft Excel.

Справочная информация

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

Задание 1

Школьный кондитерский цех готовит пирожки и пирожные. В силу ограниченности условий можно приготовить не более 700 штук изделий. Рабочий день длится 8 часов. За день можно произвести не более 250 пирожных, пирожков – 1000 (по отдельности).
Стоимость пирожного вдвое выше стоимости пирожка. Требуется составить такой дневной план производства, чтобы обеспечить наибольшую выручку.

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

1. Подготовить таблицу к решению задачи оптимального планирования.

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

Формулы:
B10=B5+4*C5
B11=B5+C5
B15=B5+2*C5

2. Вызвать программу оптимизации и сообщить ей, где расположены данные. Для этого выполнить команду Сервис -> Поиск решения. На экране откроется соответствующая форма:

3. Выполнить следующий алгоритм:
=> ввести адрес ячейки с целевой функцией. В нашем случае это В15 (заметим, что если перед этим установить указатель мыши на ячейку В15, то ввод произойдет автоматически);
=> поставить отметку максимальному значению, т. е. сообщить программе, что нас интересует нахождение максимума целевой функции;
=> в поле Изменяя ячейки ввести В5:С5, т. е. сообщить, какое место отведено под значения переменных — плановых показателей;
=> в поле Ограничения ввести неравенства-ограничения, которые имеют вид: B10 =D12; B13>=D13. Ограничения вводятся следующим образом:
> щелкнуть на кнопке Добавить;
> в появившемся диалоговом окне Добавление ограничения ввести ссылку на ячейку В10, выбрать из меню знак неравенства снова щелкнуть на кнопке Добавить и аналогично ввести второе ограничение B11 в конце щелкнуть на кнопке ОК.
=> закрыть диалоговое окно Добавление ограничения. Перед нами снова форма Поиск решения:

=> указать, что задача является линейной (это многократно облегчит программе ее решение). Для этого щелкнуть на кнопке Параметры, после чего открывается форма Параметры поиска решения:

=> установить флажок линейная модель. Остальная информация на форме Параметры поиска решения чисто служебная, автоматически устанавливаемые значения нас устраивают, и вникать в их смысл не будем. Щелкнуть на кнопке ОК. Снова откроется форма Поиск решения.
=> щелкнуть на кнопке Выполнить — в ячейках B5 и С5 появляется оптимальное решение:

Читайте также:  Как будет правильно средства или средства ударение

Справочная информация

В результате применения инструмента Поиск решения, получен следующий оптимальный план дневного производства кондитерского цеха:
нужно выпускать 600 пирожков и 100 пирожных. Эти плановые показатели соответствуют положению точки В на рис. 6.9 в учебнике. В этой точке значение целевой функции /(600, 100) = 800. Если один пирожок стоит 5 руб., то полученная выручка составит 4000 руб.

Задание 2

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

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

Источник



Как в excel называется средство применяемое для решения задач оптимального планирования

Урок 55. Практическая работа № 17. Решение задачи оптимального планирования в MS Excel

Практикум
Практическая работа № 3.6
Решение задачи оптимального планирования

Цель работы: получение представления о построении оптимального плана методом линейного программирования; практическое освоение раздела Microsoft Excel Поиск решения для построения оптимального плана.

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

Справочная информация

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

Задание 1

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

1. Подготовить таблицу к решению задачи оптимального планирования.

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

2. Вызвать программу оптимизации и сообщить ей, где расположены данные. Для этого выполнить команду Сервис —> Поиск решения. На экране откроется соответствующая форма:

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

=> ввести адрес ячейки с целевой функцией. В нашем случае это В15 (заметим, что если перед этим установить указатель мыши на ячейку В15, то ввод произойдет автоматически);
=> поставить отметку максимальному значению, т. е. сообщить программе, что нас интересует нахождение максимума целевой функции;
=> в поле Изменяя ячейки ввести В5 : С5, т. е. сообщить, какое место отведено под значения переменных — плановых показателей;
=> в поле Ограничения ввести неравенства-ограничения, которые имеют вид: B10 = D12; B13 >= D13.

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

☛ щелкнуть на кнопке Добавить;
☛ в появившемся диалоговом окне Добавление ограничения ввести ссылку на ячейку В10, выбрать из меню знак неравенства закрыть диалоговое окно Добавление ограничения. Перед нами снова форма Поиск решения:

Читайте также:  Технические средства индивидуальной защиты от поражения током


=> указать, что задача является линейной (это многократно облегчит программе ее решение). Для этого щелкнуть на кнопке Параметры, после чего откроется форма Параметры поиска решения:


=> установить флажок линейная модель. Остальная информация на форме Параметры поиска решения чисто служебная, автоматически устанавливаемые значения нас устраивают, и вникать в их смысл не будем. Щелкнуть на кнопке ОК. Снова откроется форма Поиск решения;
=> щелкнуть на кнопке Выполнить — в ячейках В5 и С5 появится оптимальное решение:

Справочная информация

В результате применения инструмента Поиск решения получен следующий оптимальный план дневного производства кондитерского цеха: нужно выпускать 600 пирожков и 100 пирожных. Эти плановые показатели соответствуют положению точки В на рис. 3.10 в учебнике. В этой точке значение целевой функции f(600, 100) = 800. Если один пирожок стоит 5 руб., то полученная выручка составит 4000 руб.

Задание 2

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

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

При такой постановке задачи система неравенств (см. § 20) примет вид:

1. Внести соответствующие изменения в электронную таблицу, построенную при выполнении предыдущего задания.

2. Получить оптимальный план с помощью средства Поиск решения.

3. Проанализировать полученные результаты. Сопоставить их с результатами задания 1.

Источник

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

Решение задачи оптимального планирования в

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

R1, R2 и другие, за счет которых эти плановые показатели могут быть достигнуты

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

Постановка задачи планирования

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

Нужно определить значение плановых показателей с учетом ограниченности ресурсов при условии достижения стратегической цели. Это и будет оптимальным планом.

Пусть совхоз занимается возделыванием только двух культур — зерновых и картофеля — и располагает следующими ресурсами: пашня — 5000 га, труд — 300 тыс

Пусть совхоз занимается возделыванием только двух культур — зерновых и картофеля — и располагает следующими ресурсами:
пашня — 5000 га,
труд — 300 тыс. чел.-ч,
возможный объем тракторных работ — 28 000 условных га.
Цель производства—получение максимального объема валовой продукции (в стоимостном выражении).
Найдите оптимальное сочетание посевных площадей культур.

Этап I. Решение. культуры Затраты на 1 га посева

Затраты на 1 га посева

Стоимость валовой продукции с 1 га, р.

тракторных работ, усл. га

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

Для поиска оптимального решения задачи обозначим через
х1 -га площадь, отводимую под зерновые,
х2 га — площадь, отводимую под картофель. Тогда стоимость зерновых составит 400 х1 р.,
а стоимость картофеля — 1000 х2 р.
Отсюда стоимость всей валовой продукции составит ( 400 х1 + 1000 х2) р.
Обозначим это выражение через у и назовем его целевой функцией:
у = 400 х1 + 1000 х2

Читайте также:  Чем лечить дизентерию народными средствами

Нам надо найти максимум этой целевой функции при соблюдении следующих условий: а) общая площадь зерновых и картофеля не должна превышать 5000 га, т

Нам надо найти максимум этой целевой функции при соблюдении следующих условий:
а) общая площадь зерновых и картофеля не должна превышать 5000 га, т. е. х1 + х2≤5000;
б) общие затраты труда не должны превосходить 300 тыс. человеко-часов, т. е. 30 х1 + 150 х2≤ 300 000;
в) общий объем механизированных работ не должен превосходить 28 000 усл. га, т. е. 4 х1 + 12 х2≤28 000;
г) площади, отводимые под зерновые и картофель, могут принимать только неотрицательные значения: х1≥0 и х2 ≥0.

Таким образом, условия задачи выражаются следующей системой неравенств

Таким образом, условия задачи выражаются следующей системой неравенств

Требуется найти такие значения х1 и х2, при которых целевая функция у = 400 х1 + 1000 х2 принимает наибольшее значение.

Этап II. Решим задачу графически

Этап II. Решим задачу графически.

Построим прямую х1 + х2=5000.

Построим прямую х1 + х2=5000.

Построим прямую 30 х1 + 150 х2=300 000.

Построим прямую 4 х1 + 12 х2=28 000.

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

Таким образом, наибольшее значение целевой функции достигается в вершине М, что соответствует варианту плана, по которому под зерновые отводится 4000 га, а под картофель — 1000 га.

Решение задачи оптимального планирования в

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

Поиск решения». Для этого надо выполнить команду =>

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

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

Далее надо выполнить следующий алгоритм:
Ввести координату ячейки с целевой функцией. В нашем случае это В15. (Заметим, что если перед этим установить курсор на ячейку В15, то ввод произойдет автоматически).
Поставить отметку «максимальному значению», то есть сообщить программе, что нас интересует нахождение максимума целевой функции.
В поле «Изменяя ячейки» ввести В5:С5, то есть сообщить, какое место отведено под значения переменных -плановых показателей.
В поле «Ограничения» надо ввести информацию о неравенствах-ограничениях, которые имеют вид B10 =D12; B13>=D13.

Закрыть диалоговое окно «Добавление ограничения»

Закрыть диалоговое окно «Добавление ограничения». Снова появится форма «Поиск решения»

Теперь надо дать последние указания: задача является линейной (это многократно облегчит программе ее решение)

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

Вся информация введена. Далее надо щелкнуть по кнопке «Выполнить» — мгновенно в ячейках

Вся информация введена. Далее надо щелкнуть по кнопке «Выполнить» — мгновенно в ячейках В5 и С5 появится оптимальное решение

Практическая работа в парах (делятся на пары, каждая пара вытягивает задачу

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

Источник