CKT_l.r.02_NU / Решение нелинейных уравнений в Excel

	Теоретическая справка	щихся приемах реализации этого про-	гебраических уравнений и задач оп-
		цесса можно найти, например, в [1].	тимизации) интересна тем, что пакет
	Довольно часто на практике при мо-	Второй этап приближенного вычис-	Excel установлен практически на каж-
	делировании различных процессов	ления корня уравнения f(x) = 0 мож-	дом современном компьютере, в то
	(экономических, физических, техни-	но осуществить с помощью множест-	время как такие известные специали-
	ческих, социальных и др.) приходит-	ва разработанных для этой цели мето-	зированные математические пакеты,
	ся сталкиваться с задачами, решение	дов уточнения корня, среди которых	как Mathematica, Maple, Matlab, Math
	которых сводится к численному реше-	наиболее известны:	CAD, имеющие специальные встроен-
	нию нелинейных уравнений. Так, на-	• метод половинного деления (он	ные функции для получения числен-
	пример, непосредственно к решению	же — метод бисекции);	ного решения нелинейных уравнений,
	таких уравнений сводятся многие за-	• метод хорд;	используются значительно меньшей
	дачи теоретической и строительной	• метод касательных (он же — метод	пользовательской аудиторией.
	механики, задачи по сопротивлению	Ньютона);	Ниже на конкретном примере рас-
	материалов и др.	• метод последовательных прибли-	сматривается решение задачи об оп-
	Как известно из курса вычислитель-	жений (метод итераций).	ределении равновесной цены продук-
	ной математики, корнем уравнения	Следует отметить, что авторы не	ции, которое сводится к численному
	f(x) = 0, где функция f(x) определена	преследовали цель изложить в данной	решению нелинейного уравнения.
	и непрерывна на некотором интерва-	статье курс численных методов реше-	Пример
	ле [a; b], называется такое значение	ния нелинейных уравнений с той или
	x* [a; b], при котором функция f(x)	иной степенью полноты. Основные	На фирме, реализующей некоторые
	обращается в нуль.	задачи статьи:	изделия, специалисты по маркетингу
	Приближенное вычисление корней	• на конкретном примере показать	определили функцию спроса p = d(q)
	уравнения обычно состоит из следу-	возможности пакета Excel при ре-	и функцию предложения p = s(q) отно-
	ющих двух этапов [1].	шении нелинейных уравнений;	сительно количества изделий q, при-
	1. Отделение корней, т. е. установ-	• продемонстрировать, каким эф-	обретенного потребителями или пред-
	ление возможно тесных промежутков,	фективным инструментом реше-	ложенного для продажи по цене p, где
	в которых содержится строго по одно-	ния подобных задач является над-	d(q) = 150 – 5q;
	му корню данного уравнения, причем	стройка «Поиск решения» [2,3];
			s(q) = 0,25q2 + 0,5 q + 70
	каждый такой отрезок называется от-	• проиллюстрировать, как относи-
	резком изоляции корня.	тельно легко такие задачи могут	Необходимо, используя надстройку
	2. Уточнение приближенных значе-	быть решены даже пользователем,	«Поиск решения», найти равновесную
	ний корней, т. е. доведение их до за-	не владеющим глубокими знания-	цену продукции, а также решить эту
	данной точности.	ми по численным методам.	задачу графически.
	Обычно процесс отделения корней,	Предложенная в статье методика по	Решение
	исходя из физического смысла зада-	овладению навыками решения сред-
	чи, проводят графически или с помо-	ствами Excel такого рода задач (см.	Для определения равновесной цены
	щью таблиц значений функции f(x).	также [4, 5], где приведены методики	продукции следует найти координату
	Подробную информацию об имею-	решения в Excel систем линейных ал-	точки пересечения функций спроса

46	Компьютеры + Программы № 3 (89) 2002

SOFT –> ТЕОРИЯ

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

R(q) = d(q) = d(q) — s(q)

инайти решение уравнения R(q) = 0. Вот как это решение можно найти

в Excel.

В диапазоне ячеек А1:D2 формиру- Рис. 1. Исходная таблица ем исходную таблицу (рис. 1).

1. В ячейку А2 заносим начальное приближение корня уравнения, т. е. начальное количество изделий, приобретенных потребителями. Примем

Рис. 2. Содержимое ячеек исходной таблицы

его равным 1.

2. В ячейку В2 заносим формулу для функции спроса =150 – 5 А2.

Напоминаем, что для этого следует с клавиатуры набрать знак равенства и в строке формул записать выражение 150 – 5 , затем левой кнопкой мыши щелкнуть на ячейке с адресом А2 — и Excel занесет этот адрес ячейки в продолжение нашей формулы. Визуально в строке формул проверяем, верно ли записано наше выражение, и нажимаем клавишу «Ок».

В ячейку С2 записываем формулу для функции предложения:

= 0,25 A2 ^ 2 + 0,5 A2 + 70

Рис. 3. Диалоговое окно надстройки «Поиск решения»

Вячейку D2 вводим выражение для вычисления целевой функции B2–C2.

Врезультате экран у вас должен выглядеть как на рис. 1. А в режиме отображения формул — как на рис. 2.

Далее из меню «Сервис» следует вызвать команду «Поиск решения». При этом откроется диалоговое окно, изображенное на рис. 3. Ставим курсор

вполе «Установить целевую ячейку» Рис. 4. Диалоговое окно «Результаты поиска решения»

и набираем адрес ячейки D2 с абсо-

лютной ссылкой, т. е. $D$2. Посколь-	решение. Если решение найдено	пользован в дальнейшем, в средстве
ку необходимо найти решение урав-	(рис. 4), то далее следует выбрать один	«Диспетчер сценариев» следует щелк-
нения R(q) = 0, то в переключателе	из следующих возможных вариантов:	нуть на кнопке «Сохранить сценарий»
«Равной значению:» записываем значе-	• сохранить найденное решение,	и дать этому сценарию имя.
ние правой части уравнения (т. е. 0).	т. е. заменить исходные значения	Если в результате выполнения
В поле «Изменяя ячейки:» заносится	в изменяемых ячейках на значения,	процедуры поиска решения само ре-
абсолютный адрес ячейки $А$2.	полученные в результате решения	шение не будет найдено (об этом по-
Теперь все исходные данные, необ-	задачи;	явится сообщение в диалоговом окне
ходимые для выполнения процедуры	• восстановить исходные значения	«Результаты поиска решения»), хотя
поиска решения, введены. Для запус-	в изменяемых ячейках.	известно, что такое решение сущест-
ка процесса решения задачи следует	Для создания отчетов о ходе процес-	вует, то очень часто подобную пробле-
щелкнуть по кнопке «Выполнить».	са поиска решения следует выбрать не-	му удается решить, изменив одну или
В строке состояния будет отображать-	обходимый отчет из списка типов от-	несколько опций в диалоговом окне
ся ход решения задачи. Затем на экра-	четов («Результаты», «Устойчивость»,	«Параметры поиска решения» и по-
не появится диалоговое окно «Резуль	«Пределы»).	вторно запустив процедуру поиска ре-
таты поиска решения» с информаци-	Для сохранения решения в виде	шения. Чтобы появилось диалоговое
ей о том, найдено или нет искомое	сценария, который может быть ис-	окно «Параметры поиска решения»,

№ 3 (89) 2002 Компьютеры + Программы

47

Рис. 5. Диалоговое окно «Параметры поиска решения»

Рис. 6. Результаты решения уравнения R(q) = 0

Рис. 7. Таблица значений исследуемых функций

Рис. 8. Содержимое ячеек таблицы на рис. 7

которое изображено на рис. 5, щелк-	са (ячейка В2) совпадает с функцией
ните в диалоговом окне «Поиск реше	предложения (ячейка С2).
ния» на кнопке «Параметры». Подроб-	Ниже с помощью пакета Excel при-
нее об опциях диалогового окна «Па	ведено графическое решение уравне-
раметры поиска решения» будет сказа-	ния R(q) = 0.
но ниже.	• Для построения графика в Excel
Остается щелкнуть по клавише «Оk»	предварительно необходимо задать
в диалоговом окне «Результаты поис	диапазон значений для величины q.
ка решения», чтобы получить решение	Например, возьмем значение коли-
уравнения, приведенное на рис. 6.	чества изделий, приобретенных
Итак, из таблицы на рис. 6 следует,	потребителями, начиная с нуля и
что решением уравнения R(q) = 0 яв-	с шагом в пять единиц, и запишем
ляется значение q = 10, т. е. для полу-	его в диапазон ячеек A5:A9.
чения равновесной цены необходимо	• В ячейку В5 записываем формулу
иметь 10 изделий, причем равновес-	для функции спроса = 150 – 5 × A5
ная цена равна 100 — функция спро-	и копируем набранную нами фор-

мулу в диапазон ячеек В6:В9 с относительными ссылками, т. е. ссылки перенастраиваются на новые адреса ячеек.

•в ячейку С5 записываем форму-

лу для функции предложения

=0,25 × A5 ^ 2 + 0,5 × A5 + 70

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

С5:С9.

•формулу = B5 – C5 для целевой функции записываем в ячейку D5

икопируем ее с относительными ссылками в диапазон ячеек D5:D9.

Получим таблицу, представленную на рис. 7–8.

Используя стандартную методику построения и оформления диаграмм в пакете Excel с помощью «Мастера диаграмм», на основе таблицы значений исследуемых функций из рис.7 получаем графики, приведенные на

рис. 9–10.

На рис. 9 приведено графическое решение данного уравнения. Из рисунка видно, что значение q = 10 является корнем уравнения R(q) = 0, т. е. при q = 10 будем иметь равновесную цену.

Исследуя поведение графиков функций спроса и предложения (рис. 10), можно сделать некоторые выводы. Интерес представляет точка пересечения кривых спроса и предложения — точка равновесия (q = 10). Соответствующая этой точке цена называется равновесной ценой (p = 100). Пересечение графиков при p = 100 означает, что спрос и предложение при этой цене совпадают, т. е. весь произведенный товар раскупается. При ценах ниже равновесной (p < 100), т. е. когда спрос превышает предложение (s(q) < d(q)), возникает «дефицит» товара и производители могут повышать цену: рыночная цена при этом будет повышаться до равновесной. Если же цена будет выше равновесной цены (p > 100), т. е. предложение превысит спрос (s(q) > d(q)), то часть продукции остается нереализованной, что вынудит производителей снизить цену, и следовательно, рыночная цена будет снижаться до равновесной. Следует отметить упрощенность рассмотренной здесь модели, так как цена — не единственный фактор, определяющий изменение спроса и предложения.

48	Компьютеры + Программы № 3 (89) 2002

SOFT –> ТЕОРИЯ

О параметрах

целочисленных решений. Его следует

Автоматическое масштабирование.

поиска решения

устанавливать только для целочис-

Эта опция служит для включения

Разберем несколько подробнее оп-

ленных ограничений.

автоматической нормализации вход-

ции диалогового окна «Параметры по

Сходимость. Эта опция служит для

ных и выходных значений, качествен-

иска решения» (см. рис. 5). С их помо-

прекращения процесса поиска реше-

но отличающихся по величине, на-

щью можно управлять процессом ре-

ния, если относительное изменение

пример — максимизация прибыли в

шения задачи.

значения в целевой ячейке за послед-

процентах по отношению к вложени-

Примечание. Если вы не считаете се-

ние пять итераций становится мень-

ям, исчисляемых в миллионах гривен.

бя опытным математиком, то лучше

ше числа, указанного в текстовом по-

Примечание. Если вы установили

в качестве параметров использовать

ле «Сходимость». Это число может

флажок

опции

«Автоматическое

их значения, заданные по умолчанию.

принимать произвольные значения

масштабирование», убедитесь, что из-

Параметры процедуры поиска ре-

в интервале от 0 до 1. Опция «Сходи

меняемые ячейки содержат значения

шений следующие.

мость» применяется только к нели-

того же порядка, который вы ожида-

Максимальное время. Опция служит

нейным задачам.

ете увидеть в ответе. Нежелательно

для ограничения времени, отпускае-

Линейная модель. Эта опция позво-

при запуске «Поиска решения» начи-

мого на поиск решения задачи. В по-

ляет ускорить поиск решения линей-

нать изменяемые ячейки с нуля.

ле можно ввести время (в секундах),

ной задачи или линейной аппрокси-

не превышающее 32767, причем зна-

мации нелинейной задачи.

Раздел «Оценки» служит для указа-

чение 100, используемое по умолча-

Неотрицательные значения. Позво-

ния метода экстраполяции, использу-

нию, подходит для решения боль-

ляет установить нулевую нижнюю

емого для получения исходных оце-

шинства простых задач. При дости-

границу для тех ячеек, для которых

нок значений переменных в каждом

жении максимального времени поиск

она не была указана в поле «Ограниче

одномерном поиске: При этом пере-

решения прекращается.

ния:» диалогового окна «Добавить».

ключатель «Линейная» служит для ис-

Предельное число итераций. Эта оп-

Показывать результаты итераций. Ес-

пользования линейной экстраполя-

ция служит для управления временем

ли в диалоговом окне установлен фла-

ции вдоль касательного вектора, а пе-

решения задачи, ограничивая число

жок опции «Показывать результаты

реключатель «Квадратичная» — для

итераций, а следовательно — и объ-

итераций», то средство «Поиск реше

использования квадратичной экстра-

ем промежуточных вычислений.

ния» делает паузу после каждой итера-

поляции, которая дает лучшие резуль-

Относительная погрешность. Эта оп-

ции, чтобы показать вам промежуточ-

таты при решении нелинейных задач.

ция служит для задания точности реше-

ные результаты. Появляется диалого-

В разделе «Разности» следует уста-

ния. Она может принимать произволь-

вое окно «Текущее состояние поиска ре

новить переключатель «Прямые», ес-

ные значения в интервале от 0 до 1. Чем

шения». Чтобы выполнить следующую

ли решение задачи — гладкая и непре-

точнее определяется решение задачи

итерацию, щелкните на кнопке «Про

рывная функция (как, например, в ли-

(то есть чем меньше значение относи-

должить». Если вы удовлетворены ре-

нейной модели). Если же функция

тельной погрешности), тем больше вре-

зультатами и захотите остановиться,

имеет разрывную производную, то ус-

мени требуется средству «Поиск реше

щелкните на кнопке «Стоп». Для со-

тановите переключатель «Централь

ния» для нахождения решения.

хранения текущих данных, прежде чем

ные». Но при этом «Поиск решения»

Допустимое отклонение — макси-

продолжать — щелкните на кнопке

может выдать сообщение о том, что не

мальное отклонение в процентах для

«Сохранить сценарий».

может улучшить результат.

100,0

150,0

R(q)

p

50,0

q

0,0

100,0

50,0

100,0

q

150,0

50,0

0

5

10

15

20

0

5

10

15

20

функция спроса d(q)

функция предложения s(q)

Рис. 9. Графическое решение уравнения R(q) = 0

Рис. 10. Поведение функций спроса и предложения

№ 3 (89) 2002 Компьютеры + Программы

49

SOFT –> ТЕОРИЯ

В разделе «Метод поиска» можно выбрать алгоритм оптимизации — направление поиска для каждой итерации. С помощью установленного пе реключателя Ньютона лучше всего решать простые задачи. Этот метод поиска более быстрый и требует для решения задачи меньшего числа итераций, хотя обычно требует больше памяти, чем метод сопряженных градиентов. Если задача достаточно сложная, то попробуйте установить пере ключатель сопряженных градиентов.

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

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

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

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

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

ввиде формул, которые соответствуют вашим установкам (последняя ячейка

всохраняемом диапазоне представляет собой формулу массива, которая со-

держит установки опций). Для загрузки этих установок используется диалоговое окно, вызываемое с помощью кнопки «Загрузить модель».

Литература

1.Б.П. Демидович, И.А. Марон. Основы вычислительной математики.— М.: Наука, 1966.— 664 с.

2.Дж. Уокенбах. Excel 97. Библия пользователя.— К.: Диалектика, 1997.— 624 с.

3.П. Дж. Бернс, Дж. Р. Николсон. Секреты Excel для Windows 95.— К.: Диалектика, 1996.— 576 с.

4.В.В. Гавриленко, Л.М. Парохненко. Excel и системы линейных алгебраических уравнений // Компьютеры + Программы.—2001.—№7.—С.50–51.

5.В.В. Гавриленко, Л.М. Парохненко. Excel и задачи линейного программирования // Компьютеры + Программы.— 2001.— № 12.— С. 46–49.

ГАВРИЛЕНКО В.В.,

доктор физ.*мат. наук, профессор

ПАРОХНЕНКО Л.М.,

ассистент (Национальный транспортный университет)

Трехкратный рост AOpen

20 февраля, во время работы выставки EnterEX, Украину посетил Бен Ли (Ben Lee) — менеджер Европейского отделения AOpen по линейке корпусов и систем barebone1. Так как AOpen является одним из самых популярных и ходовых «брендов» на нашем рынке, мы не могли упустить возможности поговорить с его представителем.

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

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

•за истекший год AOpen продала в Европе около 1 млн. корпусов;

•в Украине за минувший год прирост продаж корпусов увеличился почти втрое;

•в текущем году планируется, как минимум, сохранить существующие темпы роста;

•кроме корпусов для ПК, AOpen производит и собирается активно продвигать в Украине rackmount-кор- пуса (монтируемые в стойку) для серверов, высотой в 4, 3, и 2 юнита, а также одноюнитовый barebone. Сейчас корпуса AOpen сертифицируются компанией Intel, кроме того, AOpen — один из самых крупных

1Русскоязычный термин, соответствующий Barebone, еще не устоялся, и пока используется не очень благозвучное «голые системы». Фактически это высокоинтегрированный комплект из корпуса и материнки,

как правило, компактных или нестандартных типоразмеров — как для ПК, так и для серверов. Для изготовления готовой системы на базе Barebone необходимо добавить процессор, память и винчестер по вкусу -.

OEM-поставщиков корпусов для производителей серверов;

•AOpen не планирует продавать отдельно свои блоки питания.

Господин Ли ответил и на несколько наших вопросов, не связанных напрямую с его подразделением. В частности, нас интересовали взаимоотношения компаний внутри Acer Group, куда входит и AOpen. AOpen создавалась как подразделение по производству компонентов и позже выделилась в отдельную компанию. Свою основную направленность она сохранила и поныне, хотя внутри Acer Group она иногда конкурирует с другими поставщиками — в частности, с Acer Peripherals (на нашем рынке активно конкурируют CD-приводы AOpen и Acer), которая теперь будет называться Benq.

AOpen активно сотрудничает с ALI (поставщиком чипсетов, который входит в Acer Group), однако не отдает ни ему, ни любому другому поставщику ка- ких-либо предпочтений.

50	Компьютеры + Программы № 3 (89) 2002