Excel для математиков

В этой главе я продолжу рассмотрение задач, которые входят в сферу традиционного образования прикладных математиков и, обычно, даются при изучении программирования. Возможно, задачи этой главы потребуют чуть большей математической подготовки. Основное внимание я буду уделять здесь решению этих задач программным путем, даже в тех случаях, когда их решение может быть получено с использованием встроенных стандартных функций. Но прежде, чем перейти к рассмотрению отдельных задач давайте рассмотрим несколько вопросов, проясняющих семантику вычислений в Excel. Я хочу рассмотреть некоторые особенности пользовательских функций - функций, написанных на VBA и вызываемых в формулах рабочего листа

Функции с побочным эффектом и неявная передача данных

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

• ПравильнаяФункция( X As Variant) As Variant. Это пример хорошей, правильно построенной функции. Через параметр X ей передается значение некоторой ячейки рабочего листа (объект Range ). В качестве результата она возвращает значение функции,- в нашем примере результат является копией входного параметра X.
• ПобочныйЭффект(X As Variant, Y As Variant) As Variant. В данной функции помимо вычисления результата изменяется и значение параметра Y. Поскольку по умолчанию параметр передается по ссылке ( By Ref ), то это должно было бы привести к побочному эффекту и изменить содержимое ячейки рабочего листа, переданной в качестве параметра Y. Мы увидим, что этого, однако, не происходит.
• НеявнаяПередача(X As Variant) As Variant В данной функции результат зависит не только от входного параметра X, но и от значения другой, неявно используемой ячейки рабочего листа.

Вот как выглядят описания наших функций:

Public Function ПравильнаяФункция(X As Variant) As Variant
	'При вызове функции в формуле рабочего листа ей может быть передан объект
	'Range - отдельная ячейка или диапазон. Возвращаемый результат
	'также является объектом Range. Если передается и возвращается массив,
	'то, естественно, функция должна вызываться в формуле над массивами.
	
		ПравильнаяФункция = X
End Function
Public Function ПобочныйЭффект(X As Variant, ByRef Y As Variant) As Variant
	'Также, как и ПравильнаяФункция данная функция возвращает
	'в качестве результата переданный ей параметр X.
	ПобочныйЭффект = X
	
	'Побочным эффектом является изменение параметра Y, переданного по ссылке.
	'Однако заметьте, это изменение не затрагивает ячеек рабочего листа!
	Y = X
	
	'Попытка явного изменения значений ячеек рабочего листа
	'также не приводит к успеху. В этом случае и функция не возвращает
	'правильный результат. Ее результат в этом случае - #ЗНАЧ.
	'Range("C4") = 777
	
	Const mes1 = "Если объект Range, переданный "
	Const mes2 = " в качестве второго аргумента функции ПобочныйЭффект"
	Const mes3 = "	хранит значение, несовпадающее с первым аргументом, "
	Const mes4 = " то побочный эффект отсутствует!"
	MsgBox (mes1 & vbCrLf & mes2 & vbCrLf & mes3 & vbCrLf & mes4)
 End Function

Public Function НеявныеДанные(X As Variant) As Variant
	'Передача данных из рабочего листа в функцию,
	'минуя параметры, является возможной!
	Dim R As Range
	Set R = Range("C4")
	MsgBox ("В ячейке C4 хранится значение " & R.Value)
	НеявныеДанные = X.Value + R.Value
End Function

Вот как выглядит рабочий лист Excel, на котором вызываются эти функции:

Рис. 2.1. Побочный эффект и неявная передача данных

Анализируя полученные результаты, обратим внимание на следующие моменты:

• Функция, которую я назвал " ПравильнаяФункция ", при вызове ее из рабочей формулы получает объект Range в качестве своего параметра X, и возвращает объект Range в качестве результата выполнения функции. Объект Range может быть единственной ячейкой, и в этом случае функция может вызываться в обычной рабочей формуле. Если же функция получает массив ячеек и возвращает массив, то она должна вызываться в формуле над массивами. На рабочем листе я продемонстрировал оба способа вызова функции. В ячейку D4 я записал формулу " =ПравильнаяФункция(B4) ". Поскольку функция в качестве результата возвращает переданный ей аргумент, то значение, записанное ячейку D4, будет совпадать со значением 17, хранящемся в ячейке B4. Затем я в ячейки D5:E5 записал формулу над массивами " {=ПравильнаяФункция(B4:C4)} ". В результате эта же функция позволяет скопировать диапазон ячеек.
• В функции ПобочныйЭффект, хотя Y и получает "правильное" значение переменной X, но это никак не сказывается на значении ячейки рабочего листа, переданной в качестве параметра Y, хотя параметр передается по ссылке. Так что можно полагать, что передача объектов Range рабочего листа Excel в функцию всегда происходит по значению, а не по ссылке.
• Всякая попытка явно или неявно изменить значения ячеек рабочего листа в процессе работы функции рабочего листа помимо возвращаемого функцией результата оканчивается неуспехом. Более того, попытка явно изменить значение в ячейке приводит к тому, что результат функции становится неопределенным. Так что побочный эффект во всех его проявлениях запрещен. Изменить содержимое листа можно только, возвращая результат работы вызываемых функций в формулах рабочего листа.
• В функции НеявныеДанные создается локальный объект Range. Он получает значение одной из ячеек рабочего листа, и это значение влияет на результат, возвращаемый функцией. Тем самым становится возможной неявная передача данных, минуя аппарат формальных параметров. Заметьте, что Excel не может обнаружить такой способ зависимости между ячейками. Мы специально отразили существующие, по мнению Excel зависимости. Как видите, Excel не подозревает, что ячейка D10 зависит от ячейки C4. Именно возможность неучтенных зависимостей заставляет Excel полностью проводить вычисления всех наличествующих пользовательских функций.
• Ни функции с побочным эффектом, ни функции с неявной передачей данных не вызывают никаких предупреждающих сообщений.

Циклические вычисления

Если зависимые ячейки Excel образуют цикл, то говорят, что имеют место циклические ссылки (circular references). В обычном режиме Excel обнаруживает цикл и выдает сообщение о возникшей ситуации, требуя устранить циклические ссылки. Следуя обычной семантике, он не может провести вычисления, так как циклические ссылки порождают бесконечные вычисления. Есть два выхода из этой ситуации, - устранить циклические ссылки или изменить настройку в машине вычислений так, чтобы такие вычисления стали возможными. В последнем случае, естественно, требуется, чтобы число повторений цикла было конечным. Excel допускает переход к новой семантике, обеспечивающей проведение циклических вычислений. Вручную, для этого достаточно на вкладке Вычисления (меню Сервис, пункт Параметры) включить флажок Итерации и при необходимости изменить число повторений цикла в окошке "Максимум итераций". Можно также задать точность вычислений в окошке "Максимальное изменение", что также приводит к ограничению числа повторений цикла. По умолчанию максимальное число итераций и точность вычислений соответственно имеют значения 100 и 0,0001. Понятно, что включить циклические вычисления и задать значения параметров, определяющих окончание цикла, можно и программно.

Укажем, особенности семантики циклических вычислений:

• Формулы, связанные циклическими ссылками, вычисляются многократно.
• Запись формул на листе определяет порядок их вычисления. Формулы вычисляются сверху вниз, слева направо.
• Число повторений цикла определяется параметрами, заданными на вкладке Вычисления. Цикл заканчивается при достижении максимального числа итераций или, когда изменения значений во всех ячейках не превосходят заданной точности.

В каких же ситуациях требуется прибегать к циклическим вычислениям? Это, возможно, следует делать, когда речь идет о реализации итерационного процесса, вычислениях по рекуррентным соотношениям. У нас уже были примеры реализации итерационных процессов, например, вычисление суммы ряда, задающего экспоненту, в которых не применялись циклические ссылки. Платой за это было использование дополнительных ячеек таблицы Excel. Правда, появлялись и новые возможности, - возможность построить график, проанализировать процесс сходимости и т.д. Тем не менее, программисту, привыкшему к традиционным языкам, и привыкшему "с детства" экономить на переменных, может показаться странным предложенное решение задачи о нахождении корня уравнения, где на экран выводятся результаты всех приближений. В Excel экономия ячеек не главная задача. Тем не менее, при реализации итерационных процессов можно, конечно, и в Excel иметь одну единственную ячейку X, значение которой изменяется, начиная от начального приближения до искомого результата. Это в большей степени соответствует понятию переменной в языках программирования.

Циклические вычисления и нахождение корней уравнения

Покажем, как можно использовать циклические вычисления на примере задачи нахождения корня уравнения методом Ньютона. Для простоты я начну с квадратного уравнения, а позже рассмотрю и более "серьезные" уравнения. Итак, рассмотрим квадратное уравнение: X² -5X+6 =0. Найти корень этого (и любого другого уравнения) можно, используя всего одну единственную ячейку Excel. Для этого достаточно включить режим циклических вычислений и ввести в произвольную ячейку с именем, скажем X, рекуррентную формулу, задающую вычисления по Ньютону:

= X - F(X)/F1(X),

где F и F1 задают соответственно выражения, вычисляющие функцию и производную. Для нашего квадратного уравнения после ввода формулы в ней появится значение 2, соответствующее одному из корней уравнения. А как получить второй корень? Обычно, это можно сделать путем изменения начального приближения. В нашем случае начальное приближение не задавалось, итерационный процесс вычислений начинался со значения, хранимого в ячейке X по умолчанию и равного нулю. Как же задать начальное приближение в циклических вычислениях? Возникшая проблема не связана с данной конкретной задачей. Она возникает всегда в циклических вычислениях, - до начала цикла надо задать начальные установки. В рекуррентных соотношениях всегда есть некоторый начальный отрезок. Решать задачу задания начальных установок в каждом случае можно по-разному. Я продемонстрирую один прием, основанный на использовании функции ЕСЛИ. Вот как выглядит "настоящее" решение этой задачи, использующее 4 ячейки, две из которых нужны по существу дела, а две используются для повышения наглядности процесса вычислений:

• В ячейку с именем Xinit я ввел начальное приближение.
• В ячейку Xcur, в которой и будет идти циклический счет, ввел формулу:

  = ЕСЛИ(Xcur =0; Xinit; Xcur - (6- Xcur *(5- Xcur))/(2* Xcur -5))
• В две другие вспомогательные ячейки я поместил текст этой формулы и формулу, задающую вычисление функции в точке Xcur, позволяющую следить за качеством решения.
• Заметьте, что на первом шаге вычислений, функция IF (ЕСЛИ) поместит в ячейку Xcur начальное значение, а затем уже начнет счет по формуле на последующих шагах.
• Чтобы сменить начальное приближение, недостаточно изменить содержимое ячейки Xinit и запустить процесс вычислений. В этом случае вычисления будут продолжены, начиная с последнего вычисленного значения. Чтобы обнулить значение, хранящееся в ячейке Xcur, нужно заново записать туда формулу. Для этого достаточно выбрать ячейку и выделить текст формулы непосредственно в окне ее редактирования. Щелчок по Enter начнет вычисления с новым начальным приближением.