Линейное программирование в Excel

Решение задач линейного программирования с использованием microsoft excel

К оглавлению…

Цель работы

Приобретение навыков решения задач линейного программирования (ЛП) в табличном редакторе Microsoft Excel.

Порядок выполнения работы

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

Инструкция по использованию microsoft excel для решения задач линейного программирования

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

Ввести условие задачи:

a) создать экранную форму для ввода условия задачи:

  • переменных,
  • целевой функции (ЦФ),
  • ограничений,
  • граничных условий;

b) ввести исходные данные в экранную форму:

  • коэффициенты ЦФ,
  • коэффициенты при переменных в ограничениях,
  • правые части ограничений;

c) ввести зависимости из математической модели в экранную форму:

  • формулу для расчета ЦФ,
  • формулы для расчета значений левых частей ограничений;

d) задать ЦФ (в окне «Поиск решения»):

  • целевую ячейку,
  • направление оптимизации ЦФ;

e) ввести ограничения и граничные условия (в окне «Поиск решения»):

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

Решить задачу:

a) установить параметры решения задачи (в окне «Поиск решения»);

b) запустить задачу на решение (в окне «Поиск решения»);

с) выбрать формат вывода решения (в окне «Результаты поиска решения»).

Возможно эта страница вам будет полезна:

Предмет математическое программирование

Одноиндексные задачи линейного программирования

К оглавлению…

Рассмотрим пример нахождения решения для следующей одноиндексной задачи ЛП:

Линейное программирование в Excel задачи с решением

Ввод исходных данных

Создание экранной формы и ввод в нее условия задачи

Экранная форма для ввода условий задачи (1.1) вместе с введенными в нее исходными данными представлена на рис. 1.1.

Линейное программирование в Excel задачи с решением

В экранной форме на рис. 1.1 каждой переменной и каждому коэффициенту задачи поставлена в соответствие конкретная ячейка в Excel. Имя ячейки состоит из буквы, обозначающей столбец, и цифры, обозначающей строку, на пересечении которых находится объект задачи линейного программирования. Так, например, переменным задачи (1.1) соответствуют ячейки Линейное программирование в Excel задачи с решениемЛинейное программирование в Excel задачи с решением, коэффициентам ЦФ соответствуют ячейки

Линейное программирование в Excel задачи с решением
Линейное программирование в Excel задачи с решением

правым частям ограничений соответствуют ячейки

Линейное программирование в Excel задачи с решением

Ввод зависимостей из математической модели в экранную форму

Зависимость для ЦФ

В ячейку F6, в которой будет отображаться значение ЦФ, необходимо ввести формулу, по которой это значение будет рассчитано. Согласно (1.1) значение ЦФ определяется выражением

Линейное программирование в Excel задачи с решением

Используя обозначения соответствующих ячеек в Excel (см. рис. 1.1), формулу для расчета ЦФ (1.2) можно записать как сумму произведений каждой из ячеек, отведенных для значений переменных задачи (ВЗ, СЗ, D3, ЕЗ), на соответствующую ячейку, отведенную для коэффициентов ЦФ (В6, С6, D6, Е6), то есть

Линейное программирование в Excel задачи с решением

Чтобы задать формулу (1.3) необходимо в ячейку F6 ввести следующее выражение и нажать клавишу «Enter»

Линейное программирование в Excel задачи с решением

где символ $ перед номером строки 3 означает, что при копировании этой формулы в другие места листа Excel номер строки 3 не изменится;

символ : означает, что в формуле будут использованы все ячейки, расположенные между ячейками, указанными слева и справа от двоеточия (например, запись В6:Е6 указывает на ячейки В6, С6, D6 и Е6). После этого в целевой ячейке появится 0 (нулевое значение) (рис. 1.2).

Линейное программирование в Excel задачи с решением

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

• курсор в поле F6;

• нажав кнопку «Линейное программирование в Excel задачи с решением«, вызовите окно «Мастер функций — шаг 1 из 2»;

• выберите в окне «Категория» категорию «Математические»;

• в окне «Функция» выберите функцию СУММПРОИЗВ;

• в появившемся окне «СУММПРОИЗВ» в строку «Массив 1» введите выражение В$3:Е$3, а в строку «Массив 2» — выражение В6:Е6 (рис. 1.3);

• после ввода ячеек в строки «Массив 1» и «Массив 2» в окне «СУММПРОИЗВ» появятся числовые значения введенных массивов (см. рис. 1.3), а в экранной форме в ячейке F6 появится текущее значение, вычисленное по введенной формуле, то есть 0 (так как в момент ввода формулы значения переменных задачи нулевые).

Линейное программирование в Excel задачи с решением

Зависимости для левых частей ограничений

Левые части ограничений задачи (1.1) представляют собой сумму произведений каждой из ячеек, отведенных для значений переменных задачи (ВЗ, СЗ, D3, ЕЗ), на соответствующую ячейку, отведенную для коэффициентов конкретного ограничения (В 10, СЮ, D10, ЕЮ — 1-е ограничение; В11, С11, D11, El 1 — 2-е ограничение и В12, С12, D12, Е12 — 3-е ограничение). Формулы, соответствующие левым частям ограничений, представлены в табл. 1.1.

Линейное программирование в Excel задачи с решением

Как видно из табл. 1.1, формулы, задающие левые части ограничений задачи (1.1), отличаются друг от друга и от формулы (1.4) в целевой ячейке F6 только номером строки во втором массиве. Этот номер определяется той строкой, в которой ограничение записано в экранной форме. Поэтому для задания зависимостей для левых частей ограничений достаточно скопировать формулу из целевой ячейки в ячейки левых частей ограничений. Для этого необходимо:

• поместить курсор в поле целевой ячейки F6 и скопировать в буфер содержимое ячейки F6 (клавишами «Ctrl-Insert»);

• помещать курсор поочередно в поля левой части каждого из ограничений, то есть в F10, F11 и F12, и вставлять в эти поля содержимое буфера (клавишами «Shift-Insert») (при этом номер ячеек во втором массиве формулы будет меняться на номер той строки, в которую была произведена вставка из буфера);

• на экране в полях F10, F11 и F12 появится 0 (нулевое значение) (см. рис. 1.2).

Проверка правильности введения формул

Для проверки правильности введенных формул производите поочередно двойное нажатие левой клавиши мыши на ячейки с формулами. При этом на экране рамкой будут выделяться ячейки, используемые в формуле (рис. 1.4 и 1.5).

Линейное программирование в Excel задачи с решением

Задание ЦФ

Дальнейшие действия производятся в окне «Поиск решения», которое вызывается из меню «Сервис» (рис. 1.6):

• поставьте курсор в поле «Установить целевую ячейку»;

• введите адрес целевой ячейки $F$6 или сделайте одно нажатие левой клавиши мыши на целевую ячейку в экранной форме — это будет равносильно вводу адреса с клавиатуры;

• введите направление оптимизации ЦФ, щелкнув один раз левой клавишей мыши по селекторной кнопке «максимальному значению».

Линейное программирование в Excel задачи с решением

Ввод ограничений и граничных условий

Задание ячеек переменных

В окно «Поиск решения» в поле «Изменяя ячейки» впишите адреса $BS3:$E$3. Необходимые адреса можно вносить в поле «Изменяя ячейки» и автоматически путем выделения мышью соответствующих ячеек переменных непосредственно в экранной форме.

Задание граничных условий для допустимых значений переменных

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

• Нажмите кнопку «Добавить», после чего появится окно «Добавление ограничения» (рис. 1.7).

• В поле «Ссылка на ячейку» введите адреса ячеек переменных $BS3:$E$3. Это можно сделать как с клавиатуры, так и путем выделения мышью всех ячеек переменных непосредственно в экранной форме.

• В поле знака откройте список предлагаемых знаков и выберите >.

• В поле «Ограничение» введите адреса ячеек нижней границы значений переменных, то есть $В$4:$Е$4. Их также можно ввести путем выделения мышью непосредственно в экранной форме.

Линейное программирование в Excel задачи с решением

Задание знаков ограничений <. >, =

• Нажмите кнопку «Добавить» в окне «Добавление ограничения».

• В поле «Ссылка на ячейку» введите адрес ячейки левой части конкретного ограничения, например $F$10. Это можно сделать как с клавиатуры, так и путем выделения мышью нужной ячейки непосредственно в экранной форме.

• В соответствии с условием задачи (1.1) выбрать в поле знака необходимый знак, например =.

• В поле «Ограничение» введите адрес ячейки правой части рассматриваемого ограничения, например $Н$10.

• Аналогично введите ограничения: $F$11>=$Н$11, $F$12<=$H$12.

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

Окно «Поиск решения» после ввода всех необходимых данных задачи (1.1) представлено на рис. 1.6.

Если при вводе условия задачи возникает необходимость в изменении или удалении внесенных ограничений или граничных условий, то это делают, нажав кнопки «Изменить» или «Удалить» (см. рис. 1.6).

Решение задачи

К оглавлению…

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

Задача запускается на решение в окне «Поиск решения». Но предварительно для установления конкретных параметров решения задач оптимизации определенного класса необходимо нажать кнопку «Параметры» и заполнить некоторые поля окна «Параметры поиска решения» (рис. 1.8).

Линейное программирование в Excel задачи с решением

Параметр «Максимальное время» служит для назначения времени (в секундах), выделяемого на решение задачи. В поле можно ввести время, не превышающее 32 767 секунд (более 9 часов).

Параметр «Предельное число итераций» служит для управления временем решения задачи путем ограничения числа промежуточных вычислений. В поле можно ввести количество итераций, не превышающее 32 767.

Параметр «Относительная погрешность» служит для задания точности, с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 до 1. Чем меньше количество десятичных знаков во введенном числе, тем ниже точность. Высокая точность увеличит время, которое требуется для того, чтобы сошелся процесс оптимизации.

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

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

Установка флажка «Линейная модель» обеспечивает ускорение поиска решения линейной задачи за счет применение симплекс-метода.

Подтвердите установленные параметры нажатием кнопки «ОК».

Запуск задачи на решение

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

После запуска на решение задачи линейного программирования на экране появляется окно «Результаты поиска решения» с одним из сообщений, представленных на рис. 1.9, 1.10 и 1.11.

Линейное программирование в Excel задачи с решением

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

Если при заполнении полей окна «Поиск решения» были допущены ошибки, не позволяющие Excel применить симплекс-метод для решения задачи или довести ее решение до конца, то после запуска задачи на решение на экран будет выдано соответствующее сообщение с указанием причины, по которой решение не найдено. Иногда слишком малое значение параметра «Относительная погрешность» не позволяет найти оптимальное решение. Для исправления этой ситуации увеличивайте погрешность поразрядно, например от 0,000001 до 0,00001 и т.д.

В окне «Результаты поиска решения» представлены названия трех типов отчетов: «Результаты», «Устойчивость», «Пределы». Они необходимы при анализе полученного решения на чувствительность (см. ниже подразд.3.3). Для получения же ответа (значений переменных, ЦФ и левых частей ограничений) прямо в экранной форме просто нажмите кнопку «ОК». После этого в экранной форме появляется оптимальное решение задачи (рис. 1.12).

Линейное программирование в Excel задачи с решением

Целочисленное программирование

К оглавлению…

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

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

• В окне «Поиск решения» (меню «Сервис»—>»Поиск решения»), нажмите кнопку «Добавить» и в появившемся окне «Добавление ограничений» введите ограничения следующим образом (рис.1.14):

  • в поле «Ссылка на ячейку» введите адреса ячеек переменных задачи, то есть $В$3:$Е$3;
  • в поле ввода знака ограничения установите «целое»;
  • подтвердите ввод ограничения нажатием кнопки «ОК».
Линейное программирование в Excel задачи с решением

На рис. 1.13 представлено решение задачи (1.1), к ограничениям которой добавлено условие целочисленности значений ее переменных.

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

К оглавлению…

Двухиндексные задачи линейного программирования вводятся и решаются в Excel аналогично одноиндексным задачам. Специфика ввода условия двухиндексной задачи ЛП состоит лишь в удобстве матричного задания переменных задачи и коэффициентов ЦФ.

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

Линейное программирование в Excel задачи с решением

Целевая функция и ограничения данной задачи имеют вид

Линейное программирование в Excel задачи с решением

Экранные формы, задание переменных, целевой функции, ограничений и граничных условий двухиндексной задачи (1.5) и ее решение представлены на рис. 1.15, 1.16, 1.17 и в табл. 1.3.

Линейное программирование в Excel задачи с решением
Линейное программирование в Excel задачи с решением
Линейное программирование в Excel задачи с решением

Задачи с булевыми переменными

К оглавлению…

Частным случаем задач с целочисленными переменными являются задачи, в результате решения которых искомые переменные Линейное программирование в Excel задачи с решением могут принимать только одно из двух значений: 0 или 1. Такие переменные в честь предложившего их английского математика Джорджа Буля называют булевыми. На рис. 1.18 представлена экранная форма с решением некоторой двухиндексной задачи с булевыми переменными.

Линейное программирование в Excel задачи с решением

Рис. 1.18. Решение двухиндексной задачи с булевыми переменными

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

• для наглядности восприятия ввести в экранную форму слово «булевы» в качестве характеристики переменных (см. рис. 1.18);

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

Линейное программирование в Excel задачи с решением

Вид окна «Поиск решения» для задачи с булевыми переменными, представленной на рис. 1.18, приведен на рис. 1.20.

Линейное программирование в Excel задачи с решением

Возможные ошибки при вводе условий задач линейного программирования

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

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

К оглавлению…

Цель работы

Приобретение навыков решения задач линейного программирования (ЗЛП) в табличном редакторе Microsoft Excel. ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ

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

Инструкция по использованию microsoft excel для решения задач линейного программирования

Для того чтобы решить ЗЛП в табличном редакторе Microsoft Excel, необходимо выполнить следующие действия. 1. Ввести условие задачи:

a) создать экранную форму для ввода условия задачи:

  • • переменных,
  • • целевой функции (ЦФ),
  • • ограничений,
  • • граничных условий;

b) ввести исходные данные в экранную форму:

  • • коэффициенты ЦФ,
  • • коэффициенты при переменных в ограничениях,
  • • правые части ограничений;

c) ввести зависимости из математической модели в экранную форму:

  • • формулу для расчета ЦФ,
  • • формулы для расчета значений левых частей ограничений; с!) задать ЦФ (в окне «Поиск решения»):
  • • целевую ячейку,
  • • направление оптимизации ЦФ;

е) ввести ограничения и граничные условия (в окне «Поиск решения»):

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

a)установить параметры решения задачи (в окне «Поиск решения»,);

b) запустить задачу на решение (в окне «Поиск решения»,);

c) выбрать формат вывода решения (в окне «Результаты поиска решения»).

Одноиндексные ЗЛП

Рассмотрим пример нахождения решения для следующей одноиндексной ЗЛП:

Линейное программирование в Excel задачи с решением
  • Ввод исходных данных

Создание экранной формы и ввод в нее условия задачи

Экранная форма для ввода условий задачи (1) вместе с введенными в нее исходными данными представлена на рис.1.

Линейное программирование в Excel задачи с решением

В экранной форме на рис. 1 каждой переменной и каждому коэффициенту задачи поставлена в соответствие конкретная ячейка в Excel. Так, например, переменным задачи (1) соответствуют ячейки

Линейное программирование в Excel задачи с решением

коэффициентам ЦФ соответствуют ячейки

Линейное программирование в Excel задачи с решением
Линейное программирование в Excel задачи с решением

правым частям ограничений соответствуют ячейки

Линейное программирование в Excel задачи с решением
Линейное программирование в Excel задачи с решением
  • Ввод зависимостей из математической модели в экранную форму

Зависимость для ЦФ.

В ячейку F6, в которой будет отображаться значение ЦФ, необходимо ввести формулу, по которой это значение будет рассчитано. Согласно (1 (значение ЦФ определяется выражением

Линейное программирование в Excel задачи с решением

Используя обозначения соответствующих ячеек в Excel (см. рис. 1), формулу для расчета ЦФ (2) можно записать как сумму произведений каждой из ячеек, отведенных для значений переменных задачи (ВЗ, СЗ, D3, ЕЗ), на соответствующую ячейку, отведенную для коэффициентов ЦФ (В6, С6, D6,E6):

Линейное программирование в Excel задачи с решением

После этого в целевой ячейке появится 0 (нулевое значение) (рис. 2).

Линейное программирование в Excel задачи с решением

Зависимости для левых частей ограничений

Левые части ограничений задачи (1) представляют собой сумму произведений каждой из ячеек, отведенных для значений переменных задачи(ВЗ, СЗ, D3, ЕЗ), на соответствующую ячейку, отведенную для коэффициентов конкретного ограничения (B10, С10, D10, Е10 — 1-е ограничение; В11, C11,D11, Е11 — 2-е ограничение и В12, С12, D12, Е12 — 3-е ограничение). Формулы, соответствующие левым частям ограничений, записать самостоятельно. Проверка правильности введения формул

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

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

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

Параметр «Максимальное время» служит для назначения времени (в секундах), выделяемого на решение задачи. В поле можно ввести время, не превышающее 32 767 секунд (более 9 часов).

Параметр «Предельное число итераций» служит для управления временем решения задачи путем ограничения числа промежуточных вычислений. В поле можно ввести количество итераций, не превышающее32 767. Параметр «Относительная погрешность» служит для задания точности, с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 до 1. Чем меньше количество десятичных знаков во введенном числе, тем ниже точность. Высокая точность увеличит время, которое требуется для того, чтобы сошелся процесс оптимизации.

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

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

После запуска на решение задачи линейного программирования на экране появляется окно «Результаты поиска решения» с одним из сообщений:

• Сообщение об успешном решении задачи

• Сообщение при несовместной системе ограничений задачи

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

Если при заполнении полей окна «Поиск решения» были допущены ошибки, не позволяющие Excel применить симплекс-метод для решения задачи или довести ее решение до конца, то после запуска задачи на решение на экран будет выдано соответствующее сообщение с указанием причины, по которой решение не найдено. Иногда слишком малое значение параметра»Относительная погрешность» не позволяет найти оптимальное решение. Для исправления этой ситуации увеличивайте погрешность поразрядно, например от 0,000001 до 0,00001 и т.д.

В окне «Результаты поиска решения» представлены названия трех типов отчетов: «Результаты», «Устойчивость», «Пределы». Они необходимы при анализе полученного решения на чувствительность (будет рассмотрено позже). Для получения же ответа (значений переменных, ЦФ и левых частей ограничений) прямо в экранной форме просто нажмите кнопку «ОК». После этого в экранной форме появляется оптимальное решение задачи (рис.3).

Линейное программирование в Excel задачи с решением

Целочисленное программирование

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

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

Линейное программирование в Excel задачи с решением

• В окне «Поиск решения» (меню «Сервис»—►»Поиск решения»), нажмите кнопку «Добавить» и в появившемся окне «Добавление ограничений» введите ограничения целочисленности. Сравните результаты.

Получите у преподавателя индивидуальные задания.

Примеры решения экономических задач Задача 1.

Средства очистки пола оценивают по следующим трем показателям:

  • • очищающие свойства;
  • • дезинфицирующие свойства;
  • • раздражающее воздействие на кожу.

Каждый из этих показателей измеряется по линейной шкале от 0 до 100. Продукт на рынке должен иметь по крайней мере 60 ед. очищающих свойств и по крайней мере 60 ед. дезинфицирующих свойств по соответствующей шкале. При этом раздражающее воздействие на кожу должно быть минимальным. Конечный продукт должен быть смесью трех основных очистителей, характеристики которых приведены в таблице.

Линейное программирование в Excel задачи с решением

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

Целевая функция: Линейное программирование в Excel задачи с решением (т.е. минимизируем раздражающее воздействие на кожу конечного продукта).

Ограничения:

Линейное программирование в Excel задачи с решением

Решение задачи с помощью MS Excel.

Линейное программирование в Excel задачи с решением

Заполним таблицу, содержащую исходные данные. Заполним диалоговое окно

«Поиск решения».

Линейное программирование в Excel задачи с решением

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

Линейное программирование в Excel задачи с решением

(т.е. очистители Линейное программирование в Excel задачи с решением нужно брать в долях 30%, 10% и 60% соответственно).

Линейное программирование в Excel задачи с решением

Задача 2.

Фирме требуется уголь с содержанием фосфора не более 0,03% и с примесью пепла не более 3,25%. Доступны 3 сорта угля Линейное программирование в Excel задачи с решением по следующим ценам (за тонну):

Линейное программирование в Excel задачи с решением

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

Решение задач математического программирования с помощью надстройки «Поиск решения» ЭТ Excel

К оглавлению…

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

Рассмотрим пример применения «Поиска решения» на основе решения задачи оптимизации портфеля ценных бумаг — одной из классических задач управления финансовыми средствами.

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

  1. Суммарный объем капитала составляет 100 000 $;
  2. доля средств, вложенная в один из объектов, не может превышать 25%;
  3. более 40% всех средств должны быть вложены в долгосрочные активы;
  4. доля высокорисковых активов не может превышать трети от суммарного объема.

Таблица 1 — Информация об объектах инвестирования

Линейное программирование в Excel задачи с решением

Построим экономико-математическую модель задачи.

Искомые переменные — объемы средств, вложенные в активы: Линейное программирование в Excel задачи с решениемЛинейное программирование в Excel задачи с решением.

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

Линейное программирование в Excel задачи с решением

Сформируем ограничения:

Ограничения на суммарный объем активов —

Линейное программирование в Excel задачи с решением

Ограничение на размер доли каждого актива

Линейное программирование в Excel задачи с решением

Необходимость долгосрочного инвестирования (например, более 3 лет)

Линейное программирование в Excel задачи с решением

Учет необходимости снижения риска —

Линейное программирование в Excel задачи с решением

Естественное экономическое ограничение — неотрицательность искомых переменных —

Линейное программирование в Excel задачи с решением

Для решения задачи выполним следующие шаги.

  • На рабочем листе представим необходимую для решения информацию, согласно рисунку 1.

Ячейки В13, Н9-Н11 должны содержать формулы, отражающие зависимость между искомыми переменными и условиями задачи. В данном случае целесообразно использовать функцию Суммпроизв(…), аргументами которой являются диапазоны B4-G4 и диапазоны соответствующих параметров.

Линейное программирование в Excel задачи с решением

Рисунок 1 — Исходные данные для решения ЗЛП

  • Выполнить команду Сервис/Поиск решения и заполнить все поля диалогового окна:

Указать адрес ячейки (В 13), содержащей целевую функцию, указать тип целевой функции,

В поле «изменяя ячейки» указать адреса всех искомых переменных (от В4 до G4).

Затем последовательно заполнить все ограничения (Пример на рисунке 2.)

Линейное программирование в Excel задачи с решением

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

Линейное программирование в Excel задачи с решением

Далее, если это необходимо, устанавливаются особые значения параметров (кнопка «Параметры»).

Линейное программирование в Excel задачи с решением

Результаты отражаются на рабочем листе. Результаты решения представлены на рисунке 5.

Линейное программирование в Excel задачи с решением

Рисунок 5 — Результаты решения задачи

На рисунке 6 представлена структура инвестиционного портфеля.

Линейное программирование в Excel задачи с решением

На основе решения проводится анализ, и принимаются соответствующие управленческие решения.

Технология решения транспортной задачи

К оглавлению…

1. На рабочем листе представим необходимую для решения информацию, согласно рисунку 7.

Ячейки В15 содержит формулу Суммпроизв(…), аргументами которой являются диапазоны В4-Е6 и В9-Е11. Ячейки F9-F11 должны содержать формулы, отражающие зависимость между искомыми переменными и условиями задачи. В данном случае целесообразно использовать функцию Сумм(…), аргументами которой являются диапазоны В9-Е9, В10-Е 10 и В11 -Е11. Аналогично определяются формулы в В12-Е 12.

Линейное программирование в Excel задачи с решением

Рисунок 7 — Исходные данные для решения ЗЛП

  • Выполнить команду Сервис/Поиск решения и заполнить все поля диалогового окна:

Указать адрес ячейки (В 15), содержащей целевую функцию, указать тип целевой функции (минимум),

В поле «изменяя ячейки» указать адреса всех искомых переменных (от В9 до Е11).

Затем последовательно заполнить все ограничения (Пример на рисунке 8.)

Линейное программирование в Excel задачи с решением

Если возникли ошибки ввода, то изменить или добавить ограничение можно с помощью командных кнопок «Добавить, изменить, удалить». Результаты отражаются на рабочем листе. Результаты решения представлены на рисунке 9.

Линейное программирование в Excel задачи с решением

Технология решения задачи нелинейного программирования

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

Линейное программирование в Excel задачи с решением

доходе I. Найти максимальное значение функции полезности.

Построим математическую модель задачи потребительского выбора:

Линейное программирование в Excel задачи с решением

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

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

  • На рабочем листе представим необходимую для решения информацию, согласно рисунку 10.

Ячейки В5, В6 должны содержать формулы, отражающие зависимость между искомыми переменными и условиями задачи. В данном случае ячейка В5 содержит формулу «=D2B2+E2C2», а ячейка В6 содержит формулу «=2В2Л(3/4)(С2-4)А(1/4)».

Линейное программирование в Excel задачи с решением

Рисунок 10 — Исходные данные для решения ЗНП

  • Выполнить команду Сервис/Поиск решения и заполнить все поля диалогового окна:
Линейное программирование в Excel задачи с решением

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

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

Для решения классической задачи нелинейного программирования применим метод множителей Лагранжа, для этого составим функцию Лагранжа:

Линейное программирование в Excel задачи с решением

Найдем точки экстремума функции Лагранжа.

Линейное программирование в Excel задачи с решением

Приравняем каждое уравнение к 0:

Линейное программирование в Excel задачи с решением

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

Линейное программирование в Excel задачи с решением

Подставим второе уравнение в первое и построим аналитические функции спроса:

Линейное программирование в Excel задачи с решением

Максимальное значение функции полезности-

Линейное программирование в Excel задачи с решением

Решением задачи потребительского выбора будет набор Линейное программирование в Excel задачи с решениемЛинейное программирование в Excel задачи с решением

Возможно эти страницы вам будут полезны:

  1. Решение задач по математическому программированию
  2. Примеры решения задач по математическому программированию
  3. Заказать работу по математическому программированию
  4. Помощь по математическому программированию
  5. Задачи математического программирования
  6. Задача линейного программирования
  7. Примеры решения задач по линейному программированию
  8. Решение задач по линейному программированию
  9. Методы решения задач линейного программирования
  10. Графическое решение задач линейного программирования
  11. Графический метод решения задач линейного программирования
  12. Заказать работу по линейному программированию
  13. Помощь по линейному программированию
  14. Контрольная работа по линейному программированию
  15. Курсовая работа по линейному программированию