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

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

В программу Excel встроены возмож­ности решения задач математического программирования.

Средство «Поиск решения» реализовано в форме надстройки. Соответствующая команда находится в меню Сервис (MS Excel 97-2003) или в группе Анализ вкладки Данные.

Замечание. Средство поиска решения является надстройкой — вспомогательной программой, служащая для добавления в Microsoft Office специальных команд или возможностей. Чтобы использовать эту надстройку в Excel, необходимо сначала загрузить ее. Для этого в MS Excel 7 нажмите кнопку MS Office , а затем щелкните Параметры Excel. Выберите команду Надстройки, а затем в окне Управление выберите пункт Надстройки Excel. Нажмите кнопку Перейти. В окне Доступные надстройки установите флажок Поиск решения и нажмите кнопку ОК.

В случае появления сообщения о том, что надстройка для поиска решения не установлена на компьютере, нажмите кнопку Да, чтобы установить ее.

Покажем на рассмотренном нами простейшем примере («пирожки и пирожные»), как воспользоваться средством Поиск Решения.

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

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

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

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

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

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

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

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

В поле «Ограничения» надо ввести информацию о неравенствах-ограничениях, которые имеют вид B10<=D10;

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

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

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

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

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

Рис. 2.23. Форма «Параметры поиска решения»

Надо выставить флажок на переключателе «Линейная модель» Остальная информация в форме «Параметры поиска решения» служебная, автоматически устанавливаемые значения нас устраивают . Следует щелкнуть по кнопке ОК, что возвратит нас в форму «Поиск решения».

Вся информация введена. Далее надо щелкнуть по кнопке «Выполнить» — мгновенно в ячейках В5 и С5 появится оптимальное решение (числа 600 и 100), а также число 800 в ячейке В15 — максимальное значение целевой функции (рис. 2.24).

Рис. 2.24. Результаты решения задачи

Кроме того, на экране появилась еще одна форма — «Результаты поиска решения».

На первом этапе освоения возможностей программы на эту форму можно не обращать внимания (хотя в принципе в ней может оказаться очень полезная информация).

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

Решение, которое мы получили, вполне разумно как с экономической точки зрения, так и с медицинской. Много сладкого — вредно для здоровья, а пирожки и сытнее и полезнее.

Полученная электронная таблица и настроенная на нее сервисная функция «Поиск решения» являются средством, с помощью которого можно решать задачу оптимального планирования при меняющихся условиях. Например, может измениться длина рабочего дня. Тогда надо внести новое значение в ячейку D10 и оптимальный план автоматически пересчитается. Так же может измениться допустимое суммарное число изделий в ячейке D11.

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

Соответствующее изменение легко внести в электронную таблицу. Для этого достаточно в ячейке D13 вместо 0 запи­сать В5. Результаты поиска решения будут следующими: х = 200, у = 200, f(x,y) = 600. Таким планом вряд ли будет доволен директор кондитерского цеха, поскольку потери прибыли окажутся очень существенными.

Следует иметь в виду, что при решении подобных задач могут возникнуть проблемы, о которых мы здесь не говори­ли. Например, искомого оптимального решения может вовсе не существовать — тогда программа об этом сообщит

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

Цель работы: получение представления о построении оптимального плана методом линейного программирования; практическое освоение раздела 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<=D10; B11<=D11; B12>=D12; B13>=D13. Ограничения вводятся следующим образом:
> щелкнуть на кнопке Добавить;
> в появившемся диалоговом окне Добавление ограничения ввести ссылку на ячейку В10, выбрать из меню знак неравенства <= и ввести ссылку на ячейку D10;
> снова щелкнуть на кнопке Добавить и аналогично ввести второе ограничение B11<=D11 и т. д.;
> в конце щелкнуть на кнопке ОК.
=> закрыть диалоговое окно Добавление ограничения. Перед нами снова форма Поиск решения:

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

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

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

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

Задание 2

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

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

Поиск решения задач в Excel с примерами

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

Excel – это самая популярная программа в каждом офисе во всем мире. Ее возможности позволяют быстро находить эффективные решения в самых разных сферах деятельности. Программа способна решать различного рода задачи: финансовые, экономические, математические, логические, оптимизационные и многие другие. Для наглядности мы каждое из выше описанных решение задач в Excel и примеры его выполнения.

Решение задач оптимизации в Excel

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

В Excel для решения задач оптимизации используются следующие команды:

• Подбор параметров («Данные» — «Работа с данными» — «Анализ «что-если»» — «Подбор параметра») – находит значения, которые обеспечат нужный результат.
• Поиск решения (надстройка Microsoft Excel; «Данные» — «Анализ») – рассчитывает оптимальную величину, учитывая переменные и ограничения. Перейдите по ссылке и узнайте как подключить настройку «Поиск решения».
• Диспетчер сценариев («Данные» — «Работа с данными» — «Анализ «что-если»» — «Диспетчер сценариев») – анализирует несколько вариантов исходных значений, создает и оценивает наборы сценариев.

Для решения простейших задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев». Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения».

Условие. Фирма производит несколько сортов йогурта. Условно – «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 рублей. «2» — 250 рублей. «3» — 300 рублей. Сбыт, налажен, но количество имеющегося сырья ограничено. Нужно найти, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.

Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:

На основании этих данных составим рабочую таблицу:

1. Количество изделий нам пока неизвестно. Это переменные.
2. В столбец «Прибыль» внесены формулы: =200*B11, =250*В12, =300*В13.
3. Расход сырья ограничен (это ограничения). В ячейки внесены формулы: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») и =0*B11+8*B12+6*B13 («сахар»). То есть мы норму расхода умножили на количество.
4. Цель – найти максимально возможную прибыль. Это ячейка С14.

Активизируем команду «Поиск решения» и вносим параметры.

После нажатия кнопки «Выполнить» программа выдает свое решение.

Оптимальный вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» производить не стоит.

Решение финансовых задач в Excel

Чаще всего для этой цели применяются финансовые функции. Рассмотрим пример.

Условие. Рассчитать, какую сумму положить на вклад, чтобы через четыре года образовалось 400 000 рублей. Процентная ставка – 20% годовых. Проценты начисляются ежеквартально.

Оформим исходные данные в виде таблицы:

Так как процентная ставка не меняется в течение всего периода, используем функцию ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).

1. Ставка – 20%/4, т.к. проценты начисляются ежеквартально.
2. Кпер – 4*4 (общий срок вклада * число периодов начисления в год).
3. Плт – 0. Ничего не пишем, т.к. депозит пополняться не будет.
4. Тип – 0.
5. БС – сумма, которую мы хотим получить в конце срока вклада.

Вкладчику необходимо вложить эти деньги, поэтому результат отрицательный.

Для проверки правильности решения воспользуемся формулой: ПС = БС / (1 + ставка) кпер . Подставим значения: ПС = 400 000 / (1 + 0,05) 16 = 183245.

Решение эконометрики в Excel

Для установления количественных и качественных взаимосвязей применяются математические и статистические методы и модели.

Дано 2 диапазона значений:

Значения Х будут играть роль факторного признака, Y – результативного. Задача – найти коэффициент корреляции.

Для решения этой задачи предусмотрена функция КОРРЕЛ (массив 1; массив 2).

Решение логических задач в Excel

В табличном процессоре есть встроенные логические функции. Любая из них должна содержать хотя бы один оператор сравнения, который определит отношение между элементами (=, >, =, Пример задачи. Ученики сдавали зачет. Каждый из них получил отметку. Если больше 4 баллов – зачет сдан. Менее – не сдан.

1. Ставим курсор в ячейку С1. Нажимаем значок функций. Выбираем «ЕСЛИ».
2. Заполняем аргументы. Логическое выражение – B1>=4. Это условие, при котором логическое значение – ИСТИНА.
3. Если ИСТИНА – «Зачет сдал». ЛОЖЬ – «Зачет не сдал».

Решение математических задач в Excel

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

Условие учебной задачи. Найти обратную матрицу В для матрицы А.

1. Делаем таблицу со значениями матрицы А.
2. Выделяем на этом же листе область для обратной матрицы.
3. Нажимаем кнопку «Вставить функцию». Категория – «Математические». Тип – «МОБР».
4. В поле аргумента «Массив» вписываем диапазон матрицы А.
5. Нажимаем одновременно Shift+Ctrl+Enter — это обязательное условие для ввода массивов.

Возможности Excel не безграничны. Но множество задач программе «под силу». Тем более здесь не описаны возможности которые можно расширить с помощью макросов и пользовательских настроек.

Модели оптимального планирования

В данный момент вы не можете посмотреть или раздать видеоурок ученикам

Чтобы получить доступ к этому и другим видеоурокам комплекта, вам нужно добавить его в личный кабинет.

Получите невероятные возможности

Конспект урока «Модели оптимального планирования»

Сегодня на уроке мы с вами заканчиваем изучение теоретических основ «Информационного моделирования». На прошлом уроке мы занимались моделированием корреляционных зависимостей. И узнали, что существует два вида зависимостей. Уже хорошо знакомая функциональная зависимость (когда определённому значению независимой переменной соответствует строго определённое значение зависимой переменной) и корреляционная.

Корреляционная зависимость – это такая зависимость, когда данному значению одной величины соответствует целый ряд значений другой, причём при изменении данной величины меняются и другие значения, а также и его среднее. То есть зависимость между величинами, каждая из которых подвергается неконтролируемому разбросу.

При изучении корреляционных зависимостей мы рассмотрели два вида задач.

Первый вид – Оказывает ли фактор В какое-либо заметное постоянное влияние на фактор А?

Второй – Какие из факторов B, C или D оказывают наибольшее влияние на фактор А?

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

Затем, рассмотрев пример корреляционной зависимости, мы пришли к тому, что оценку корреляции величин начинают с высказывания гипотезы о возможном характере зависимости между их значениями.

Чаще всего считают, что это линейная зависимость. Тогда мера корреляционной зависимости – это величина, которая называется коэффициентом корреляции.

Коэффициент корреляции (обычно обозначают греческой буквой ƍ) характеризует величину, отражающую степень взаимосвязи двух переменных между собой. ƍ может изменяться в пределах от -1 до +1. То есть в зависимости от того к чему будет ближе коэффициент корреляции к нулю, к плюс или минус единице, зависимость может быть соответственно сильной или отсутствовать вообще.

Но существуют задачи другого типа.

На прошлом уроке мы рассмотрели зависимость производительности труда рабочего на предприятии от энерговооружённости данного предприятия, то есть от условий работы, которые предприятие создаёт для своего рабочего.

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

Сегодня на уроке мы свами будем разбираться со следующими вопросами:

· Что такое оптимальное планирование и в чём состоит задача оптимального планирования.

· Что такое плановые показатели, ресурсы и цели.

· А также, какое программирование называется математическим, а какое линейным.

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

При постановке задачи учитывают следующее:

· Имеющиеся плановые показатели: икс, игрек и другие:

· Имеющиеся ресурсы: R1, R2 и другие. За счёт ресурсов достигаются плановые показатели. Но ресурсы практически всегда ограничены.

· И стратегическая цель. Цель зависит от значений плановых показателей и на неё ориентируются при планировании.

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

Пусть объектом планирования является транспортная компания, которая занимается доставкой товаров из нескольких предприятий, нескольким потребителям.

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

Или другой пример: Пусть объектом моделирования является средняя общеобразовательная школа.

Плановыми показателями здесь могут выступить, например, количество учителей и учащихся. Основными ресурсами деятельности школы являются объём финансирования, оснащённость учебных кабинетов средствами обучения, например, учебными стендами, плакатами, интерактивной доской и компьютерами. Естественно в школе стратегической целью является образование и воспитание школьников. А количественной мерой будет повышение среднего балла успеваемости.

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

Давайте рассмотрим пример решения задачи оптимального планирования с помощью компьютера.

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

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

Естественно, что это чисто учебный пример. Вряд ли существует такой рыбоводческий комплекс, который занимается разведением всего двух видов рыб, да и наибольшая выручка – это не единственная цель его работы.

Итак, здесь плановыми показателями являются:

Икс штук – карпов и игрек штук – толстолобиков.

Ресурсами в этом примере можно назвать количество корма каждого вида, которое должны ежедневно получать карпы и толстолобики.

Для упрощения решения задачи, будем считать, что другие ресурсы, например, электроэнергия, не ограничены.

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

Итак, для первого корма. Ежедневно карпы должны получать 2 единицы первого корма, а толстолобики 3 единицы, но в месяц количество потребления первого корма рыбами не должно превышать 150-ти единиц.

Аналогично составим неравенства для второго и третьего корма.

К трём полученным неравенствам нужно добавить условия положительности значений величин икс и игрек, так как не может быть отрицательное число карпов и толстолобиков. В итоге получим следующую систему неравенств.

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

По условию мы знаем, что прибыль от реализации одного карпа равна 16 условным единицам, а толстолобика – 12 у.е., тогда сумма 16x+12y должна стремиться к максимуму.

Цель рыбоводческого комплекса – получение максимальной выручки от продажи рыбы.

Рассмотрим записанное выражение как функцию от x, y.

То есть f_xy, игрек равно 16x + 12y.

Такая функция называется целевой функцией.

Следовательно, получение оптимального плана свелось к следующей математической задаче:

Требуется найти значения плановых показателей x и y, которые будут удовлетворять данной системе неравенств и придавать максимальное значение целевой функции.

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

Решить данную задачу нам помогут средства, реализованные в табличном процессоре Excel.

Математическое программирование — это раздел математики, содержащий методы решения задач оптимального планирования.

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

Линейное программирование – это раздел математического программирования, решающий задачи оптимального планирования с линейной целевой функцией.

Для решения задачи в MS Excel, создадим таблицу с исходными данными.

Ячейки В7 и С7 оставим соответственно для значений х (количество выращиваемых карпов) и у (количество выращиваемых толстолобиков).

В ячейки В2:С2 введём коэффициенты в ограничениях по первому корму, то есть 2 и 3 соответственно. В ячейки В3:С3 по второму корму. И в В4:С4 по третьему. Далее В диапазон ячеек Д2:Д4 запишем общее количество корма каждого вида, которое может быть использовано комплексом.

В ячейки В6 и С6 запишем коэффициенты целевой функции, в нашем случае это 16 и 12.

Далее в ячейку В8 введём формулу целевой функции. Будем использовать встроенную функцию СУММПРОИЗВ.

Данная функция перемножает соответствующие элементы заданных списков, а затем складывает полученные произведения.

Аргументами функции СУММПРОИЗВ являются диапазоны ячеек, причём все диапазоны должны иметь одинаковые размерности.

Встроенная функция СУММПРОИЗВ будет записываться следующим образом: равно СУММПРОИЗВ, далее в круглых скобках нужно указать два диапазона ячеек, которые нужно перемножить и затем сложить произведения. В нашей задаче целевая функция записана следующим образом: шестнадцать икс плюс двенадцать игрек, то есть нам нужно найти сумму произведений коэффициентов целевой функции и переменных. Указываем первый диапазон В6:С6 точка с запятой и второй диапазон В7:С7.

Функция СУММПРОИЗВ перемножит соответствующие ячейки диапазона, то есть В6 умножит на В7, а С6 умножит на С7. Затем полученные произведения суммирует.

В ячейку Е2 введём ограничения. Снова будем использовать встроенную функцию СУММПРОИЗВ. Здесь первый диапазон ячеек – это коэффициенты в ограничениях для первого корма, а второй – переменные. Аналогично заполним ограничения для второго и третьего корма.

Теперь необходимо вызвать программу оптимизации и сообщить ей, где расположены данные. Для этого необходимо сначала загрузить её.

Откроим вкладку Файл и выберем пункт Параметры. Далее выбираем команду Надстройки, а затем в окне Управление выбираем пункт Надстройки Excel. Нажимаем кнопку Перейти. Теперь в окне Доступные надстройки установим флажок Поиск решения и нажимаем кнопку ОК.

После загрузки надстройки «Поиск решения» на вкладки Данные в группе Анализ становится доступна команда Поиск решения. Выбираем её, после чего перед нами открывается соответствующая форма.

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

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

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

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

В поле «Ограничения» надо ввести информацию о неравенствах-ограничениях следующим образом: щёлкнуть по кнопке «Добавить».

Итак, первое неравенство имеет вид 150 больше либо равно два икс плюс три игрек. Теперь в появившемся диалоговом окне «Добавление ограничения» ввести ссылку на ячейку Д2, выбрать из меню знак неравенства больше либо равно и ввести ссылку на ячейку Е2; снова щёлкнуть по кнопке «добавить» и аналогично ввести второе ограничение Д3 больше либо равно Е3 и так далее. В конце нажимаем ОК.

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

То есть в результате применения инструмента Поиск решения получен следующий оптимальный план разведения карпов и толстолобиков на рыбоводческом комплексе: нужно вырастить 57 карпов и 12 толстолобиков.

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

Представим систему полученных нами неравенств на координатной плоскости.

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

В результате мы получим четырёхугольник ABCD. Любая точка четырёхугольника является решением нашей системы неравенств. Если икс равно 57, а игрек равно 12, то в этой точке значение целевой функции Эф от пятидесяти семи и двенадцати равно 1056. Если один карп стоит 16 условных единиц, а толстолобик 12, то полученная выручка составит 1056 условных единиц. Этой точке соответствует точка Бэ на нашем графике.

Итак, сегодня на уроке мы с вами построили модель оптимального планирования на рыбоводческом комплексе.

А теперь давайте повторим всё, что мы узнали сегодня на уроке:

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

Ограниченность ресурсов может описываться с помощью:

Цель описывается функцией, для которой требуется найти минимум или максимум.

Microsoft Excel имеет специальное средство Поиск решения для решения задач оптимального планирования.