Excel дает возможность решать многие задачи, не требуя привлечения языка программирования, прежде всего, благодаря свойствам машины вычислений. Понятно, что такая возможность и делает Excel столь привлекательным для многочисленных пользователей. Но работа с машиной вычислений Excel полезна и при обучении программистов, тем более что процесс вычислений не является скрытым – все формулы видны, все значения доступны. Более того, различные графики могут помочь проследить за деталями процесса вычислений. Но, конечно, для программистов главное достоинство состоит в том, что они могут сочетать работу руками с программированием на языке VBA. Языку VBA я посвятил отдельную книгу [2], где достаточно много было сказано о тех его свойствах, которые делают этот язык привлекательным для целей начального обучения программированию. Он вполне подходит на роль «первого языка», с которого следует начинать осваивать программирование и который оказывает большое влияние на последующий стиль программирования.
Программированию на VBA в среде Excel будет посвящено основное содержание этой книги. Но сейчас я хочу немного отвлечься и сказать несколько слов об одном важном инструменте, интенсивно используемом в Excel и называемом MacroRecorder. Это средство, общее для многих приложений среды Office, обеспечивает возможность «программирования без программирования», позволяя получить программный текст - макрос, как результат выполнения некоторых операций над рабочим листом. Я говорю здесь о MacroRecorder еще и потому, что он одновременно является прекрасным инструментом, используемым при обучении программированию.
По существу, MacroRecorder - это обычный транслятор "необычного" языка действий. Включив запись макроса, Вы инициируете запись всех Ваших действий: выбор той или иной ячейки или области, запись в ячейку значения или формулы, вызов некоторой функции, построение диаграммы или таблицы и т.д. В любой момент Вы можете выключить запись действий. В результате MacroRecorder создаст макрос на языке VBA, запуск которого приведет к тому же эффекту, что и Ваши действия. Таким образом, один раз, сделав все руками, создав документ или его фрагмент, Вы бесплатно получаете программу, которая делает то же самое. Вся штука в том, что программу можно запускать многократно. Что же касается действий пользователя, возможно, весьма квалифицированно работающего в среде Office, то я уже не раз говорил, что они естественным образом интерпретируются, как действия над объектами того или иного приложения Office — вызов и изменение соответствующих свойств и методов этих объектов. Для программных сред, в которых пользователю предоставляются широкие возможности манипуляции над объектами, видимыми на экране, другими словами, для сред визуального программирования инструментарий, подобный MacroRecorder, совершенно естественная вещь, — все равно за действиями пользователя нужно следить и выполнять их. Транслируя действия пользователя, MacroRecorder создает макрос — процедуру без параметров на языке VBA. Созданный макрос можно выполнить в любой нужный момент. Более того, можно создать инструментальную кнопку и связать с ней созданный макрос, — теперь щелчок пользователя по кнопке и будет запускать макрос на выполнение.
Создание макросов с использованием MacroRecorder я и называю программированием без программирования. Макросы играют двоякую роль. Они полезны, когда задачу, которую в принципе можно решить без программирования, приходится решать многократно. Не менее важно, что макросы можно использовать для обучения программированию, в частности, использованию объектов Office и VBA. Часто трудно выбрать, как лучше запрограммировать решение некоторой задачи. Если это можно сделать вручную, то есть смысл создать макрос и посмотреть, как MacroRecorder решает подобную задачу. Анализ текста макросов напоминает мне исследование под микроскопом, - открывается масса неизвестных деталей.
Коль речь идет об основах программирования в Excel, то хочу попытаться достаточно точно описать семантику Excel, — как в нем проводятся вычисления. Как я уже говорил, документ Excel является рабочей книгой, каждый рабочий лист которой представляет конечную прямоугольную таблицу, элементы которой называются ячейками. Листы книги, строки, столбцы таблицы и сами ячейки изначально именованы. В любой части этой таблицы можно выделить некоторую подобласть, обычно, прямоугольный интервал ячеек и с ним работать. Пожалуй, удобнее сразу перейти к объектной терминологии. Замечу, одним из основных объектов Excel является объект Range. Ячейки, строки, столбцы, сама таблица целиком, любые ее подобласти, в том числе и не смежные, полученные, как пересечение или объединение интервалов — все это представимо единственным объектом Range. О многих свойствах и методах этого объекта будет рассказано в главе 3, посвященной объектам Excel. О некоторых скажу сейчас, рассматривая семантику вычислений. Благодаря тому, что объект Range имеет свойство Name, можно давать собственные имена используемым объектам, в том числе и ячейкам. Более важно, что объект Range имеет свойства Value и Formula. Свойство Value позволяет задать значение объекта, любого допустимого типа. Здесь существенно используются возможности универсального типа Variant, с которым совместим тип Range. Свойство Formula позволяет связать с объектом формулу, вычисляющую значение. Поскольку Excel позволяет работать с массивами, то можно вводить и специальный класс формул - формулы над массивами. Во многом возможности Excel определяются тем, насколько широк класс задаваемых формул. Формулы строятся из констант, переменных, роль которых играют объекты Range, и стандартных функций, объединенных знаками операций. Я уже говорил, сколь велик выбор стандартных функций, как общего назначения, так и ориентированных на конкретные предметные области, прежде всего финансы и статистику. Допустимо использование в формулах и функций, разработанных на VBA программистом.
Поскольку формула, записанная в одной ячейке, может ссылаться на другие ячейки таблицы, то между ячейками возникает отношение зависимости. Дадим точные формулировки: Если формула в ячейке Y содержит ссылку на ячейку X, то говорят, что Y непосредственно зависит от X, а X непосредственно предшествует Y. Обобщая понятие непосредственной зависимости, мы говорим, Y зависит от X, а X предшествует Y, если существует цепочка ячеек Z1, Z2, …ZK, начинающаяся с X и заканчивающаяся Y, такая, что каждые два соседние элемента цепочки связаны отношением непосредственной зависимости (непосредственного предшествования). Свойства Dependents и Precedents объекта Range сохраняют списки всех его зависимых и соответственно предшествующих ячеек. Это позволяет с одной стороны графически отображать на экране зависимости между ячейками и, что более важно, эффективно организовать вычисления при изменении значений в тех или иных ячейках.
Однако Excel не всегда в состоянии определить, существует ли зависимость между ячейками. Пусть, например, в ячейку A1 введено значение 2, в ячейку B1— 7, в ячейку B2 — формула “=A1+B1", а в ячейку A2 — формула “= myfunct1(B1)”, где myfunct1(X) — собственная функция, заданная программой:
Public
Function
myfunct1(X
As
Variant)
As
Variant
'Функция принимает
объект Range
в качестве параметра
'и использует
объект Range("A1")
как глобальную переменную.
myfunct1
= Range("A1").Value + X.Value
End Function
Excel может определить, что ячейка B2 зависит от ячеек A1и В1. Но он не распознает, что ячейка A2 также зависит от A1, он обнаружит только ее зависимость от B1. Причина в том, что Excel не анализирует тексты пользовательских функций, используемых в формулах, на наличие в них ссылок на ячейки таблицы. Не из-за того, что его разработчики поленились это сделать, но потому, что эта задача алгоритмически неразрешима в общем случае. Для определения отношения зависимости одной ячейки от другой используются лишь явные ссылки в параметрах вызываемых функций (B1 для ячейки A2 в нашем примере). Увидеть связи между ячейками можно на рисунке 1, где показан общий вид первого листа книги CourseFirst, с которой мы будем работать в ближайшее время.
Важной особенностью, существенно расширяющей возможности 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. Относительные ссылки позволяют реализовать рекурсивные вычисления. Надеюсь, понятно, что данная программа реализует рекуррентные соотношения, определяющие вычисление чисел Фибоначчи:
F1 = 1;
F2 = 2; "k = 3…20 Fk = Fk-2
+ Fk-1
Заметьте, я привел программную реализацию, но эта задача совершенно естественно решается в 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.
