Математическая формулировка

Подбор параметра

Пусть имеется некоторая функция одного аргумента, которую обозначим f(x). Предположим, что значение аргумента x мы можем назначать по своему усмотрению. Задача состоит в том, чтобы установить такое значение аргумента x, при котором функция f(x) примет заданное значение c. Мы пришли к известной математической задаче решения функционального уравнения f(x) = с. Пусть f(x) =sin(x) – cos(2x),c = 0,и нам нужно найти корень уравнения на заданном отрезке [0; 2].

Выполнение задания

1. ЗапуститеприложениеMicrosoftExcel и сохраните файл Книга1в своей рабочей папке под именем Параметр.xlsx.

2. Для начала нужно построить график функции на заданном отрезке, чтобы убедиться, что корень на этом отрезке существует. Подготовьте таблицу из двух строк, в верхней из которых будут записаны значения аргумента x на отрезке [0; 2] с шагом 0,2 (вспомните, как создать арифметическую прогрессию), а в нижней – значения функции sin(x) – cos(2x) (см. рис. 1).

Рис. 1. Таблица функции sin(x) – cos(2x)

3. Мы видим, что в точке 0,4 значение функции отрицательно, а в точке 0,6 – положительно. Значит, где-то на этом отрезке находиться корень уравнения. Постройте график функции. У вас должен получиться график, соответствующий рис. 2.

Рис. 2. График функции sin(x) – cos(2x)

4. Мы видим, что график функции пересекает ось x примерно в точке 0,5. Это значение можно принять как начальное приближение при поиске корня уравнения. Запишите в ячейки, например, A4:A5 подписи таблицы x и sin(x) – cos(2x), а в ячейки B4:B5 – значение 0,5 и формулу для вычисления функции.

5. Поставьте курсор в ячейку B5и выберите пункт Подбор параметра… из выпадающего списка Анализ «что-если», который находится в группе Работа с данными на вкладке Данные. Вы увидите диалоговое окно для ввода параметров инструмента Подбор параметра…(см. рис. 3).В поле Установить в ячейке уже должна находиться ссылка на выделенную ячейку с формулой.

Рис. 3. Диалоговое окно «Подбор параметра»

6. В поле Значение введите величину, которой должно быть равнозначение в целевой ячейке в результате подбора. В нашем случае это значение 0.

7. В поле Изменяя значение ячейки введите ссылку на ячейку В4 (для этого достаточно просто щелкнуть по этой ячейке), влияющую на результат вычислений по формуле.

8. Нажмите кнопкуОК. Появится диалоговое окно Результат подбора параметра(см. рис. 4).Нажмите кнопкуОК. Подобранное значение будет записано в регулируемую ячейкуВ4.

Рис. 4. Диалоговое окно «Результат подбора параметра»

9. Если выполнение итерационного процесса затянулось, нажмите в диалоговом окне Результат подбора параметра кнопкуПауза или Отмена. После нажатия кнопкиПауза можно выполнять процесс подбора по шагам. Для этого используется кнопка Шаг. Для возобновления автоматического процесса подбора следует нажать кнопкуПродолжить.

10. Результат подбора параметра должен соответствовать рис. 5. Попробуйте добиться большей точности вычисления корня, изменяя начальное значение в ячейке В4.

Рис. 5. Результат подбора параметра

11. Решите функциональное уравнение, заданное вашим вариантом в таблице вариантов:

Номер варианта	Левая часть уравнения f (x)=0	Область поиска решения
1		[2;3]
2		[0;0,85]
3		[0;1]
4		[0;1]
5	0,25x3 – x – 1,2502	[2;3]
6	0,1x2 – x ln x	[1;2]
7	3x – 4ln x – 5	[2;4]
8	ex – e–x – 2	[0;1]
9		[0,4;1]
10		[0;0,8]
11		[1;2]
12	sin(ln x)–cos(ln x) + 2ln x	[1;3]
13	lnx – x+1,8	[2;3]
14		[1;2]
15		[0,2;1]
16	tg(0,55x + 0,1) – x2	[0;1]
17		[1,2;2]
18	1 + sinx – ln(1 + x) – x	[0;1,5]
19	cos(x0,52 + 2) + x	[0,5;1]
20		[2;3]
21	ex + ln x – 10x	[3;4]
22	3x – 14 + ex –e-x	[1;3]
23	2ln2x + 6lnx – 5	[1;3]
24	2x sinx –cosx	[0,4;1]
25		[1;2]
26		[0;0,9]
27	sinx2 + cosx2 – 10x	[0;1]
28		[-1;0]
29		[0;0,9]
30		[1;2]

12. Покажите результаты работы преподавателю.

Вопросы для контроля

1. Какие задачи решаются подбором параметра?

2. Какосуществить подбор параметра?

Оптимизация

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

Математическая формулировка

Задачи, которые могут быть решены с помощью Решателя, в общей постановке формулируются так.

Найти значения переменных x₁, x₂, …, x_n, такие, что целевая функция f(x₁, x₂, …, x_n) примет заданное значение, или минимальное значение, или максимальное значение. При этом могут быть заданы ограничения вида g(x₁, x₂, …, x_n), принимающие заданные значения, или значения меньшие или равные заданным, или значения большие или равные заданным.

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

Выполнение задания

Продемонстрируем использование Решателя на примере, описанном в журнале «Компьютер Пресс» (№ 7, 1997) в статье В. Очкова «Как я продавал программу (компьютерный этюд)», в которой автор рассказал о применении Решателя для оптимизации своего заработка. Суть задачи состоит в том, что автор продал некоторую программу лакокрасочному предприятию за 14 млн. руб. Наличными деньгами предприятие не располагало, но было готово расплатиться в пределах этой суммы своей продукцией – краской. Краска выпускалась в двух видах тары – больших и малых банках (барабанах), ёмкость которых соответственно составляла 55 и 15 л, а стоимость пустых барабанов – 30 и 24 тыс. руб. Литр краски стоил 14600 руб. Автор статьи был заинтересован в том, чтобы, не выходя за пределы договорной суммы, получить от предприятия как можно больше краски. При этом имеется возможность лишь указать количество больших и малых барабанов с краской, но нельзя взять краску в разлив. Это типичная оптимизационная задача.

1. Создайте новый файлприложенияMicrosoftExcel и егов своей рабочей папке под именем Оптимизация.xlsx.

2. Создайте таблицу, соответствующую рис. 6. При этом в ячейки B6:B7 и B10:B12 запишите очевидные расчетные формулы.

3. Сначала подсчитайте, сколько краски можно получить, если всю её взять в малых барабанах. Вот очевидная формула, которая позволяет подсчитать количество малых барабанов, которое можно получить в пределах договорной суммы: 14000000/243000 = 57,6. Итак, можно получить 57 малых барабанов.

4. Введите в ячейкуB8вашей таблицы значение 57 и нажмите клавишу Enter. Если воввёденных формулах вы не допустили ошибок, то увидите, что при таком решении всего будет получено 855 л краски, а у покупателя программы останутся не выбранными 149000 руб.

Рис. 6. Исходные данные для оптимизации

5. Заслуживает внимания другое решение – взять краску в больших барабанах, а остаток выбрать малыми барабанами. Количество больших барабанов, которое можно получить, очевидно, равно 14000000/833000 = 16,8.

6. Введите в ячейкуB8 таблицы значение 0, а в ячейкуB9– значение 16. При этом вы увидите, что будет получено880 л краски, а остаток денег составит 672000 руб. Последнее решение по количеству полученной краски уже лучше, чем предыдущее. Кроме того, за счет остатка денег можно взять еще некоторое количество малых барабанов краски, которое равно 672000/243000 = 2,8.

7. Введите в ячейкуB8 значение 2. Окончательный результат принятого решения позволяет получить 910 л краски, а остаток денег составит 186000 руб. Итак, во втором варианте будет получено на 55 л краски больше, чем в первом варианте. Возникает естественный вопрос: существует ли решение, при котором можно получить еще больше краски? Как вы увидите, такое решение действительно существует. Для решения задачи поиска максимума полученной краски можно применить Решатель. Ячейки В8 и B9 будут регулируемые ячейками, а ячейка B10 – это ячейка с целевой функцией.

8. Проверьте наличие кнопки Поиск решения в группе Анализ на вкладке Данные. Если такой кнопки нет, то требуется установить соответствующий инструмент. Для этого нажмите кнопку Office, затем Параметры Excel, выберите раздел Надстройки. В выпадающем списке Управление выберите Надстройки Excel и нажмите кнопку Перейти… В появившемся диалоговом окне установите флажок для надстройки Поиск решенияи нажмите кнопку ОК. Теперь кнопка Поиск решениядолжна появиться.

9. Установите курсор в ячейку B10 с целевой функцией и нажмитекнопку Поиск решения, которая находится в группе Анализна вкладке Данные. Появится окно Поиск решения (см. рис. 7), в поле Установить целевую ячейкукоторого уже должна быть абсолютная ссылка на ячейку B10. Если же этой ссылки там не оказалось, то её следует туда поместить. Для этого надо щелкнуть мышью в поле Установить целевую ячейку, чтобы установить там курсор. Затем нужно щёлкнуть по ячейке B10.

Рис. 7. Диалоговоеокно «Solver Parameters»

10. Установите переключательРавнойна значение максимальному значению, чтобы найти максимум целевой функции.

11. Поместите в поле Изменяя ячейки диапазон регулируемых ячеек. Для этого поместите курсор в этом поле и выделите регулируемые ячейки (в рассматриваемом примере – это ячейки B8 и B9). Можно нажать кнопку Предположитьдля автоматического поиска ячеек, влияющих на формулу, ссылка на которую дана в полеУстановить целевую ячейку.

12. Добавьте ограничения, которые имеют место в рассматриваемом примере. Их два. Первое состоит в том, что сумма истраченных денег не должна превышать 14000000 руб. Нажмите кнопку Добавить. В появившемся окне Добавление ограничения(см. рис. 8) в поле Ссылка на ячейку поместите ссылку на ячейку B11, в которой записана формула вычисления размера истраченной суммы.

13. В поле Ограничение поместите ссылку на ячейку B2, в которой указана сумма договора.

14. Выберите нужный знак отношения между полями Ссылка на ячейкуи Ограничение. В результате этих действий содержание полей окна Добавление ограничениядолжно соответствовать рис. 8. Нажмите кнопку ОК.

Рис. 8. Добавление ограничения на сумму истраченных денег

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

16. ВокнеДобавление ограничениявведитевполеСсылка на ячейкудиапазонB8:B9.

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

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

Рис. 9. Добавление ограничения о целочисленности ячеек

19. По умолчанию поиск экстремума целевой функции выполняется с погрешностью 5% – это обычная инженерная погрешность. Для рассматриваемого примера эта погрешность слишком велика, так как соответствует 910·0,05 = 45,5 л краски, что намного больше емкости малого барабана. Погрешность поиска максимума в рассматриваемом примере не должна быть больше емкости малого барабана, которая составляет 15/910·100 = 1,65% общего объема краски.

20. Для повышения требуемой точности поиска решения нажмите кнопку Параметрыокна Поиск решения. В появившемся окне Параметры поиска решения(см. рис. 11) в поле Допустимое отклонение замените значение 5 на 1,5[*].

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

21. Установите в этом же окне флажок Неотрицательные значения, указав тем самым, что искомые количества барабанов краски не могут принимать отрицательные значения.

22. Закройте окно Параметры поиска решения, нажав кнопку ОК. Подготовка к поиску решения закончена.

23. Чтобы начать поиск решения, нажмите кнопку Выполнить окна Поиск решения. После окончания процесса поиска появится окно Результаты поиска решения. В этом окне включите переключатель Сохранить найденное решение и нажмите кнопку ОК.

24. Обратите внимание на значения регулируемых ячеек (см. рис. 12), которые они приобрели после окончания поиска максимума целевой функции. Проанализируйте полученное решение – теперь получено 915 л краски, то есть на 5 л больше, чем давал предыдущий вариант решения. Итак, наибольшее количество краски будет получено, если взять 15 больших и 6 малых барабанов с краской. И при этом у заказчика останется 47000 руб. И самое главное, теперь известно, что это лучшее решение.

25. Покажите результаты работы преподавателю.

26. Решите задачу минимизации суммы денег, оставленных у заказчика (ответ – 11000 руб., если взять 6 больших и 37 малых барабанов).

27. Покажите результаты работы преподавателю.

28. Закройте приложение MicrosoftExcel.

Рис. 11. Настройка параметров решателя

Рис. 12. Результат поиска максимума объема полученной краски

Вопросы для контроля

1. Какие задачи могут быть решены с помощью Решателя?

2. Какзапустить Решатель?

3. Как добавить необходимый инструмент?

4. Как задаётся ячейка с целевой функцией?

5. Как задать регулируемые ячейки?

6. Как установить допустимую ошибку поиска?

7. Как заставить Решатель искать только положительные значения?

8. Как заставить Решатель искать только целочисленные значения?

[*] В последних версиях MSExcelнаилучший результат получается, если задать целочисленную оптимальность равной 0%.