Подбор параметра
Пусть имеется некоторая функция одного аргумента, которую обозначим f(x). Предположим, что значение аргумента x мы можем назначать по своему усмотрению. Задача состоит в том, чтобы установить такое значение аргумента x, при котором функция f(x) примет заданное значение c. Мы пришли к известной математической задаче решения функционального уравнения f(x) = с. Пусть f(x) = sin(x) – cos(2x), c = 0, и нам нужно найти корень уравнения на заданном отрезке [0; 2].
Выполнение задания
1. Запустите приложение Microsoft Excel и сохраните файл Книга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 | Область поиска решения |
| [2;3] | |
| [0;0,85] | |
| [0;1] | |
| [0;1] | |
| 0,25 x 3– x – 1,2502 | [2;3] | |
| 0,1 x 2– x ln x | [1;2] | |
| 3 x – 4ln x – 5 | [2;4] | |
| e x – e– x – 2 | [0;1] | |
| [0,4;1] | |
| [0;0,8] | |
| [1;2] | |
| sin(ln x) –cos(ln x) + 2ln x | [1;3] | |
| ln x – x +1,8 | [2;3] | |
| [1;2] | |
| [0,2;1] | |
| tg(0,55 x + 0,1) – x 2 | [0;1] | |
| [1,2;2] | |
| 1 + sin x – ln(1 + x) – x | [0;1,5] | |
| cos(x 0,52+ 2) + x | [0,5;1] | |
| [2;3] | |
| e x+ ln x – 10 x | [3;4] | |
| 3 x – 14 + e x –e -x | [1;3] | |
| 2ln2 x + 6ln x – 5 | [1;3] | |
| 2 x sin x –cos x | [0,4;1] | |
| [1;2] | |
| [0;0,9] | |
| sin x 2+ cos x 2– 10 x | [0;1] | |
| [-1;0] | |
| [0;0,9] | |
| [1;2] |
12. Покажите результаты работы преподавателю.
Вопросы для контроля
1. Какие задачи решаются подбором параметра?
2. Как осуществить подбор параметра?
Оптимизация
В состав приложения Microsoft Excel входит мощный инструмент – Решатель. С его помощью можно решать задачи нелинейного программирования. В частных случаях с помощью Решателя может быть получено решение функционального уравнения, системы линейных уравнений, найден максимум или минимум функции нескольких переменных.
Математическая формулировка
Задачи, которые могут быть решены с помощью Решателя, в общей постановке формулируются так.
Найти значения переменных x 1, x 2, …, xn, такие, что целевая функция f(x 1, x 2, …, xn) примет заданное значение, или минимальное значение, или максимальное значение. При этом могут быть заданы ограничения вида g(x 1, x 2, …, xn), принимающие заданные значения, или значения меньшие или равные заданным, или значения большие или равные заданным.
Искомые переменные – ячейки рабочего листа Microsoft Excel называются регулируемыми ячейками. На регулируемые ячейки можно наложить дополнительные ограничения, например, можно потребовать, чтобы значения были положительными или целочисленными.
Выполнение задания
Продемонстрируем использование Решателя на примере, описанном в журнале «Компьютер Пресс» (№ 7, 1997) в статье В. Очкова «Как я продавал программу (компьютерный этюд)», в которой автор рассказал о применении Решателя для оптимизации своего заработка. Суть задачи состоит в том, что автор продал некоторую программу лакокрасочному предприятию за 14 млн. руб. Наличными деньгами предприятие не располагало, но было готово расплатиться в пределах этой суммы своей продукцией – краской. Краска выпускалась в двух видах тары – больших и малых банках (барабанах), ёмкость которых соответственно составляла 55 и 15 л, а стоимость пустых барабанов – 30 и 24 тыс. руб. Литр краски стоил 14600 руб. Автор статьи был заинтересован в том, чтобы, не выходя за пределы договорной суммы, получить от предприятия как можно больше краски. При этом имеется возможность лишь указать количество больших и малых барабанов с краской, но нельзя взять краску в разлив. Это типичная оптимизационная задача.
1. Создайте новый файл приложения Microsoft Excel и его в своей рабочей папке под именем Оптимизация.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. Закройте приложение Microsoft Excel.
|
| Рис. 11. Настройка параметров решателя |
|
| Рис. 12. Результат поиска максимума объема полученной краски |
Вопросы для контроля
1. Какие задачи могут быть решены с помощью Решателя?
2. Как запустить Решатель?
3. Как добавить необходимый инструмент?
4. Как задаётся ячейка с целевой функцией?
5. Как задать регулируемые ячейки?
6. Как установить допустимую ошибку поиска?
7. Как заставить Решатель искать только положительные значения?
8. Как заставить Решатель искать только целочисленные значения?
[*] В последних версиях MS Excel наилучший результат получается, если задать целочисленную оптимальность равной 0%.