Пример третий. Задание 10. §11. Решение оптимизационных задач линейного инелинейного программирования с помощью процедуры«Поиск решения»

Пример третий

Клиент кладет в банк А рублей под P процентов в год на срок n лет. В конце каждого года клиент снимает со счѐ та В рублей.  

Нужно найти сумму вклада S в конце срока с использованием функции Будущая стоимость БС (P, n, В, -A, 0).

Пусть клиент кладет в банк 50000 рублей под 12% в год на срок 3года. В конце каждого года клиент снимает со счета 1200 рублей.

Чтобы найти сумму вклада в конце срока с использованием функции БС, ведем на листе Excel (рис. 3. 29) в столбце А идентификаторы параметров функции, а в столбце В численные значения этих параметров (процентная ставка в виде десятичной дроби, вкладываемая сумма в виде отрицательного числа). Ниже введем саму функцию (f_x, финансовые, БС). Появится окно Аргументы функции, куда последовательно введем адреса аргументов из столбца В.  

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

 

Рис. 3. 29 Нахождение суммы вклада в конце срока

 

Для контроля вычислите сумму вклада S в конце срока c использованием обычной алгебраической функции S=A(1+P)ⁿ–B((1+P)^n-1+(1+P)^n-2+…+(1+P)¹+1).

 

Пусть теперь нужно узнать, через сколько лет сумма вклада удвоится?

Скопируем данные из столбца В в столбец С. Выделим ячейку С6 и вызовем функцию Подбор параметров. Теперь в С6 нужно получить 100000 рублей изменяя параметр n (количество периодов). Ответ: вложенная сумма удвоится за 7, 16 года.

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

 

Задание 10

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

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

3. По данным примера 3 (рис. 3. 29, столбец В) с помощью функций БС и ПП найдите:

а) Какую сумму нужно положить в банк, чтобы через 3 года сумма вклада достигла 100000 рублей?

в) Под какой процент нужно положить в банк 50000 рублей, чтобы через 3 года получить 70000 рублей?

с) Какую сумму можно ежегодно снимать со счета, чтобы через 3 года получить (вернуть) вложенные 50000 рублей?

д) Постройте график зависимости суммы вклада S от срока в годах за пять лет.

 

 

§11. Решение оптимизационных задач линейного инелинейного программирования с помощью процедуры«Поиск решения»

 

Электронный процессор Excel обладает мощной процедурой решения оптимизационных задач Поиск решения.  

Загрузку надстройки Поиск решения можно выполнить следующим образом.

1. В Файл выбрать Параметры Excel

2. В окне Параметры выбрать Надстройки.

3. В поле Управление выбрать Надстройки Excel и нажать кнопку 

Перейти.

4. В окне Надстройки включить Поиск решения.

5. На вкладке Данные становится доступной функция Поиск решения.

 

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

Рассмотрим вначале линейные, квадратичные и нелинейные задачи общего вида, затем транспортные задачи ЛП. Пусть нужно найти max целевой функции z = x₁ – 2x₂

при условиях

-2 x₁ + 3 x₂ 12,

 3 x₁ – x₂ 18, x₁ + x₂ 6, x₁, x₂ 0.

На листе Excel эти данные удобно расположить так, как показано на рис. 3. 30.

 

 

 

Рис. 3. 30 Решение линейной задачи общего вида (формулы)

 

Исходные     значения переменных x_j можно задать произвольно, например равными единице.

n

Z   c _j x _j

                Для формирования целевой функции         ^{j 1}             и всех

n

 aij x j

левых частей ограничений ^{j 1} (i=1, …, m) используется функция     СУММПРОИЗВ (f_x

→ математические→ СУММПРОИЗВ ), которая вызывается в строку коэффициентов целевой функции c_j столбца левых частей (D3), а затем, после закрепления адреса строки «значения переменных х_j», копируется в остальные строки этого столбца.

Для удобства ввода условий в процедуру Поиск решения их следует сгруппировать. Например, вначале записать все условия типа , затем , затем =.

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

После подготовки исходных данных вызывается процедура Поиск решения ( Данные → Анализ → Поиск решения ).

В еѐ диалоговом окне (рис. 3. 31) устанавливается адрес ячейки, где находится целевая функция (D3), нужное значение экстремума (max), диапазон адресов значений переменных (B2: C2), диапазон адресов левых и правых частей условий типа  ( Добавить → D4: D5 F4: F5 → Добавить ), диапазон адресов левых и правых частей условий типа  ( Добавить – D6 F6 – «OK»).

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

 

 

 

Рис. 3. 31 Окно параметров поиска решения 

 

В строке «Значения переменных х_j» таблицы (рис. 3. 32) получаем оптимальные значения неизвестных (х₁=6, х₂=0), а в ячейке целевой функции (D3) – соответствующее ее максимальное значение (Z=6).  

 

 

 

Рис. 3. 32 Решение линейной задачи общего вида

 

Если решение получено, отмечается позиция Сохранить решение и интересующие пользователя типы отчетов

(результаты, устойчивость, пределы).

Отчеты выводятся на отдельные листы Excel, которые вызывают из командной строки.

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

Для решения задачи с нелинейной целевой функцией нужно в ячейку целевой функции ввести нужное выражение и выбрать Поиск решения нелинейных задач методом ОПГ.

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

                         R = х₁²  2х₂² → max

набираем эту функцию в ячейке (F3) и указываем ее в диалоговом окне Поиск решения. В результате получим х₁=6, 35, х₂=1, 06, R=38, 12 (рис. 3. 33).

 

Рис. 3. 33 Результат решения линейной задачи общего вида

 

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

Добавив условия целочисленности, в нашей задаче получим х₁=6, х₂=0, R=36 (в диалоговом окне Поиск решения → Добавить → В2: С2=целое).

Если просто округлить предыдущее решение до целых чисел х₁=6, х₂=1, то целевая функция R=34, что хуже целочисленного max.

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

Пусть нужно найти max целевой функции

R =х₁³ − 2х²₂

при условиях  2х₁  3х₂² _ 12,

 

3х₁² − х₂  18,

 

х12  х22  6,

 

x₁, x₂ 0.

 

Исходные данные можно записать, например, в соответствии

с рис. 3. 34.

 

 

Рис. 3. 34 Решение нелинейной задачи

 

В результате решения получим

х₁=2. 470, х₂=0. 309, R=14. 89

 

При решении транспортной задачи ЛП тоже важно удобно расположить исходные данные в таблице Excel.

Пусть нужно найти min затрат при перемещении грузов из трех пунктов отправления с запасами а1=8, а2=5, а3=7 в четыре пункта назначения с потребностями b₁=4, b₂=4, b₃=2, b₄=10.

Затраты на перемещение единицы груза по каждому маршруту соответствуют матрице:

 

 

Можно рекомендовать расположить исходные данные, как показано на рис. 3. 35

 

 

 

Рис. 3. 35 Решение транспортной задачи 

 

Исходные значения всех неизвестных x_ij принимают произвольно, например, равными единицам.

Целевая функция формируется функцией СУММПРОИЗВ, в диалоговое окно которой первый массив с_ij и второй x_ij вводятся движениями курсора по главным диагоналям соответствующих матриц. Значения левых частей условий по запасам и по потребностям формируют функцией сумм (рис. 3. 35).

Отметим, что условием существования решения является равенство суммы запасов и суммы потребностей:

              m               n

 a_i   b_j . i 1 j 1

 

                              m               n

 a_i   b_j

Если ^{i 1        j 1}     , для обеспечения баланса добавляется фиктивный      пункт   назначения   с   потребностями m    n

bn 1   ai   bj

^{i 1 j 1}  . Чтобы не менять величину целевой функции, затраты на перемещение грузов к фиктивному пункту принимаются равными нулю:

C_i, n+1 = 0 (i=1, …, m).

 

                                                                   m             n

 a_i   b_j

         Аналогично, если ^{i 1             j 1}    , добавляется фиктивный пункт

                                                                                                         m             n

am 1   bj   ai

отправления с запасами ^{i 1     j 1}     и затратами на перемещение

 

C_m+1, j = 0(j=1, …, n).

 

В диалоговом окне Поиск решения устанавливают адрес целевой функции, вид экстремума, вводят адреса массива неизвестных (курсор по диагонали матрицы х_ij).

В окно условий вводят ( Добавить ) диапазон адресов левых частей условий по запасам, знак равенства и соответствующий диапазон правых частей условий по запасам. Затем выполняют то же с условиями по потребностям (рис. 3. 36).  

Отмечают Линейная модель и Неотрицательные значения. Компоненты решения получают в матрице х_ij, а соответствующее значение целевой функции Z    ci, j xi, j в ячейке F2 (рис. 3. 37).

 

 

 

Рис. 3. 36 Параметры для решения транспортной задачи

 

 

Рис. 3. 37 Результат решения транспортной задачи

 

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

 

⇐ Предыдущая27282930313233343536Следующая ⇒

Поделиться:

Воспользуйтесь поиском по сайту: