Начала программирования в Excel

Важной особенностью, существенно расширяющей возможности Excel, является применяемый способ задания ссылок на ячейки. С одной стороны, Excel позволяет задавать абсолютные ссылки на ячейки. Заметьте, что когда Вы даете ячейкам собственные имена и позже ссылаетесь на них, то, конечно, речь идет об абсолютных ссылках на вполне конкретные ячейки, обладающие данными именами. Но Excel позволяет задавать также относительные имена ячеек - ссылки относительно положения самого объекта Range. Если изменяется объект, то соответственно меняется и ячейка, на которую он ссылается. Напомним некоторые детали. Ссылки на ячейки можно задавать в двух форматах: A1 и R1C1. Если не предпринимать специальных мер, то ссылки в первом формате являются относительными. Так, например, ссылка "F20" является относительной и вне контекста нельзя сказать, какую именно ячейку она определяет. Чтобы сделать ее абсолютной, нужно добавить специальные символы перед именем столбца и индексом строки: $F$20. В формулах часто используются относительные ссылки в формате R1C1, в котором явно можно указывать смещение относительно объекта Range, например следующим образом:

Public Sub Fib()
	'Еще раз Фибоначчи!
	ThisWorkbook.Sheets(2).Activate
		Range("A1") = 1: Range("A2") = 2
		Range("A3:A20").Formula = "=R[-2]+R[-1]"
End Sub

Здесь, как видите, при задании ссылки в формуле задано смещение по строкам, а столбец опущен, что по умолчанию предполагает использование столбца A объекта Range. Относительные ссылки позволяют реализовать рекурсивные вычисления. Надеюсь, понятно, что данная программа реализует рекуррентные соотношения, определяющие вычисление чисел Фибоначчи:

Заметьте, я привел программную реализацию, но эта задача совершенно естественно решается в Excel без всякого программирования. Достаточно записать значение 1 в ячейку A1, значение 2 - в ячейку A2, формулу " =A1+A2 " - в ячейку A3, после чего скопировать формулу в нужный диапазон ячеек от A4 до A20. Опять таки, при копировании формулы будет учтена относительность ссылок, так что в ячейке A20 соответствующая формула будет иметь вид - " =A18+A19 ". Если, используя MacroRecorder, записать эти действия, то соответствующий макрос даст другой вариант записи рекурсивных вычислений, отличный от приведенного выше. В нем используются свойства модификации ссылок при копировании. Вот текст этого макроса:

Sub Fib1()
' Fib1 Макрос
' Макрос записан 03.03.2001 (Vladimir Billig)
	Range("A1").Select
	ActiveCell.FormulaR1C1 = "1"
	Range("A2").Select
	ActiveCell.FormulaR1C1 = "2"
	Range("A3").Select
	ActiveCell.FormulaR1C1 = "=R[-2]C+R[-1]C"
	Selection.AutoFill Destination:=Range("A3:A20"), Type:=xlFillDefault
	Range("A3:A20").Select
End Sub

Пожалуй, теперь уже можно сформулировать ряд правил, задающих семантику вычислений в Excel:

• Когда приходит пора вычислить формулу, записанную в ячейке таблицы, Excel работает, как обычный интерпретатор выражений (формула - это выражение). Конечно, разбор формулы происходит один раз при ее записи в ячейку. Вычисленное значение выражения получает свойство Value этой ячейки (объекта Range ). Возможно, что при вычислении выражения его результат не удается получить из-за отсутствия аргументов или несогласованности их типов или значений с операциями, выполняемыми над ними. В этом случае результат вычислений представляет значение, указывающее на возможную причину ошибки ( #VALUE, #NAME ).
• Представим себе, что вся таблица вычислена. Что произойдет, если изменить значение в одной ячейке таблицы? Результат может быть ошеломляющим. В одном из рассказов Рея Брэдбери убийство одной бабочки сказывается в веках, поскольку в мире все взаимосвязано. В мире ячеек Excel, как Вы знаете, также определено отношение зависимости. Поэтому, как только изменится значение в одной ячейке, автоматически тут же будут пересчитаны все формулы в ячейках, связанных с ней отношением зависимости. Это приведет и к изменению таблиц и графиков, построенных на данных, значения которых изменились. Несмотря на очевидную простоту идеи, эффект от нее колоссальный. Исследователи получают мощнейший инструмент - можно анализировать разные варианты, выбирать наилучший из них, проводить анализ "что если" и т.д.
• Важно понимать, как Excel проводит перевычисление таблицы. Прежде всего, заметим, что перевычисляется не только таблица, ячейки которой были изменены, но и вся рабочая книга, более того, - все открытые рабочие книги. Заметьте, что изменения могут затрагивать и закрытые книги, если в них есть ссылки на ячейки с изменившимися значениями. Вычисления в таких книгах пройдут при их первом открытии. Если при каждом изменении значения в одной ячейке нужно повторно вычислять все формулы на всех листах всех открытых книг, то затраты могут быть слишком велики и всю прелесть эффекта перевычисления можно потерять. Excel пытается поступать "разумно". Всегда, когда Excel может определить, что такая стратегия безопасна, повторно вычисляются те и только те формулы, которые зависят от изменившегося значения. Вы понимаете, что при этом используется свойство Dependents объектов Range, хранящее зависимости. Однако в ряде случаев Excel проводит больше вычислений, часть из которых может быть излишней. Поскольку, как я уже говорил, Excel не может определить, какие ячейки используются в функциях, написанных программистом и вызываемых в формулах рабочего листа, то Excel использует безопасную стратегию и вычисляет каждый раз все такие функции.
• Чтобы справиться с проблемой долгих вычислений, Excel предусматривает возможность управления этим процессом. Режим автоматического вычисления при каждом изменении данных в ячейках, может быть отключен. Изменения можно накапливать, а затем одним махом пересчитать таблицу, вызвав метод Calculate вручную или программно. Можно также вести перевычисления в пределах одного листа. Вручную это делается при выборе вкладки Вычисления (Calculation) в пункте Параметры (Options) из меню Сервис (Tools).

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

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

Давайте перейдем теперь к рассмотрению примеров. При рассмотрении этих примеров, так или иначе, будет продолжено обсуждение того, как проводятся вычисления в Excel.

8 классических задач, не требующих программирования

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

Задача 1

Начну с постановки задачи:

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

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

• Я начал с создания новой книги Excel с именем CourseFirst и на первой ее странице записал постановку задачи. Заметьте, для этого я использовал готовый, выделенный курсивом текст, который Вы только что прочли. Перенос текста и любых других объектов из приложения Word в Excel не представляет никаких трудностей и выполняется через буфер обычными операциями Copy и Paste. Конечно, можно записать текст, описывающий постановку задачи и непосредственно в ячейках Excel. В данном случае пришлось прибегнуть к копированию, поскольку для вящей красоты при записи функции использовались греческие буквы. Эти буквы можно вставлять в редакторе Word, но такой возможности нет при записи текстов в Excel. Замечу, что уже на следующем шаге мне придется от них отказаться. Взгляните на рисунок, отражающий результаты моей работы на этом и последующих шагах.

увеличить изображение
Рис. 1.1. Решение задачи 1

• На следующем шаге я подготовил раздел исходных данных. Здесь я использовал все те возможности работы с данными, которые описаны в первом пункте "Представление данных". Ячейкам, хранящим значения параметров функции, дал собственные имена, снабдил их метками, сгруппировал и выделил эту группу ячеек специальным цветом. Заметьте, при задании имен я отказался от использования греческих символов и использовал русскоязычные термины: Амплитуда, Частота, Фаза и ДельтаТ для параметра, задающего шаг изменения аргумента Т.

• Теперь перед нами стоит "более сложная" задача. Предстоит вычислить ряд значений функции Y(t_k), - нам ведь нужно в дальнейшем построить график. Для простоты будем полагать, что значения аргумента t_k, задающего время, изменяются с фиксированным шагом и описываются следующими рекуррентными соотношениями:

  t0 = 0; 	tk = tk-1 + ДельтаТ;	k = 1…N;

  Параметр ДельтаТ предусмотрительно был введен ранее среди других параметров нашей задачи. Итак, нам нужно построить два ряда данных: аргументов и соответствующих им значений функции. Все делается достаточно просто. В ячейку D10 я записал 0 - начальное значение аргумента t. В соседнюю ячейку E10 - формулу " =D10+ДельтаТ ". Затем осталось только скопировать эту формулу в ячейки F10, G10, и т.д., получив нужный ряд аргументов. Копирование и относительная ссылка D10 реализуют соответствующее рекуррентное соотношение. Относительную ссылку в формуле можно было бы записать и в формате R1C1-" =C[-1]+ДельтаТ ", что, возможно, более наглядно отражает смысл рекуррентного соотношения. Заметьте, для этого нужно включить соответствующий флажок (Сервис | Параметры | Общие | Стиль ссылок R1C1). Задать ряд для значений функций в некотором смысле еще проще, поскольку здесь нет начальных значений. Поэтому достаточно записать в ячейку D11 формулу " Амплитуда*Sin(Частота*D10 +Фаза) ", а затем скопировать эту формулу в соседние ячейки строки таблицы. И здесь, конечно, при желании можно было бы в формуле использовать не адрес ячейки, а формат R1C1, задав смещение R[-1].

  Обратите внимание на одну маленькую, но важную деталь. Я приписал метки нашим рядам: t и Y(t) соответственно. Однако метка t смещена влево, чтобы в источнике данных при построении графика левый верхний угол таблицы был свободным, тогда, напоминаю, первая строка будет интерпретироваться как строка аргументов функции.
• На завершающем этапе остается построить график. Мастер диаграмм позволяет сделать это без особого труда. По ходу дела я задал названия осей и дал имя самой диаграмме. Все это можно увидеть на рис.1.1.
• Задача уже решена, но теперь начинается самое интересное - исследование того, как влияют параметры функции на ее поведение. Здесь возможности не ограничены, можно изменять каждый параметр независимо, можно анализировать изменение их в совокупности. Важно, что любое изменение параметра тут же приведет к изменению значений функции и, соответственно, ее графика.
• В заключение остается только сказать, что, по моему мнению, Excel идеально подходит для решения подобных задач.