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

Оптимизация

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

Хранение данных

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

Программирование на VBA

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

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

По существу, MacroRecorder - это обычный транслятор "необычного" языка действий. Включив запись макроса, Вы инициируете запись всех Ваших действий: выбор той или иной ячейки или области, запись в ячейку значения или формулы, вызов некоторой функции, построение диаграммы или таблицы и т.д. В любой момент Вы можете выключить запись действий. В результате MacroRecorder создаст макрос на языке VBA, запуск которого приведет к тому же эффекту, что и Ваши действия. Таким образом, один раз, сделав все руками, создав документ или его фрагмент, Вы бесплатно получаете программу, которая делает то же самое. Вся штука в том, что программу можно запускать многократно. Что же касается действий пользователя, возможно, весьма квалифицированно работающего в среде Office, то я уже не раз говорил, что они естественным образом интерпретируются, как действия над объектами того или иного приложения Office - вызов и изменение соответствующих свойств и методов этих объектов. Для программных сред, в которых пользователю предоставляются широкие возможности манипуляции над объектами, видимыми на экране, другими словами, для сред визуального программирования инструментарий, подобный MacroRecorder, совершенно естественная вещь, - все равно за действиями пользователя нужно следить и выполнять их. Транслируя действия пользователя, MacroRecorder создает макрос - процедуру без параметров на языке VBA. Созданный макрос можно выполнить в любой нужный момент. Более того, можно создать инструментальную кнопку и связать с ней созданный макрос, - теперь щелчок пользователя по кнопке и будет запускать макрос на выполнение.

Создание макросов с использованием MacroRecorder я и называю программированием без программирования. Макросы играют двоякую роль. Они полезны, когда задачу, которую в принципе можно решить без программирования, приходится решать многократно. Не менее важно, что макросы можно использовать для обучения программированию, в частности, использованию объектов Office и VBA. Часто трудно выбрать, как лучше запрограммировать решение некоторой задачи. Если это можно сделать вручную, то есть смысл создать макрос и посмотреть, как MacroRecorder решает подобную задачу. Анализ текста макросов напоминает мне исследование под микроскопом, - открывается масса неизвестных деталей.

Как работает машина вычислений Excel

Коль речь идет об основах программирования в 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.1, где показан общий вид первого листа книги CourseFirst, с которой мы будем работать в ближайшее время.