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

Важной особенностью, существенно расширяющей возможности 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.

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

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

Задача 1

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

Вычислить функцию $$Y(t) = Asin(\Omega t+\psi)$$, построить ее график и исследовать поведение при различных значениях параметров: $$A, \Omega, \psi$$.

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

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

Задача 2

Постановка задачи:

Вычислить ex, используя разложение в ряд:

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

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

Приведем краткие комментарии:

Задача 3

Постановка задачи:

Найти корень уравнения 4-й степени:

F(x) = ax4  + bx3 +cx2 +dx +e = 0,

используя метод Ньютона.

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

В этом соотношении F1(X) - производная функции, которая для нашего уравнения задается соотношением:

F1(x) = 4ax3 + 3bx2 + 2cx + d

Константа h задает шаг, позволяющий регулировать процесс сходимости. Обычно выбирается в пределах от 0,5 до 1,5. Замечу, что скорость сходимости во многом зависит также от выбора начального приближения X0.

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

Вот мои действия по решению этой задачи:

Как обычно, я записал на рабочем листе постановку задачи и подготовил исходные данные.

На следующем шаге я записал рекуррентные соотношения позволяющие вычислить функцию на некотором интервале с фиксированным шагом. Реализуется все достаточно просто, как это уже описывалось для ранее рассмотренных подобных задач. В ячейку A12 я занес начальное значение аргумента функции - левый конец исследуемого интервала поведения функции. В ячейку A13 занес формулу " = A12 + DeltaX " и затем скопировал ее так, чтобы получить нужный мне интервал. В ячейку B13 я записал формулу, вычисляющую функцию F(x) по схеме Горнера - " = (((Cof1*A12 + Cof2)*A12 + Cof3)*A12 + Cof4)*A12 + Cof5 "

Скопировав эту формулу, я получил два ряда данных - аргументов и значений функции. Заметьте, DeltaX, Cof1, Cof2, …Cof5 - это имена, которые я дал исходным данным нашей задачи. Имена Cof1 - Cof5 заменяют имена коэффициентов функции - a, b, c, d, e.

Имея источник данных, я построил график функции в заданном интервале, вызвав Мастера построения диаграмм. Варьируя параметрами X0 и DeltaX, я подобрал для моей функции интервал так, чтобы на нем был хотя бы один корень. Благодаря тому, что все изменения мгновенно отражаются на графике, для функции, с которой я работал:

F(x) = 2x4 -3x3 -4x2  + 5x - 6

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

(рис 1.3) График функции F(x) в задаче 3

Действия на втором этапе немногим отличаются от действий первого этапа. Отличие чисто техническое - создаются три ряда значений: Xk, F(Xk), F1(Xk) и для определения Xk используется другая формула.

При создании ряда Xk в ячейку A26 я ввел начальное значение X0, а в ячейку A27 формулу: " =A26 - Step*B26/C26 ", задающую изменение X в схеме Ньютона. Затем эта формула была скопирована так, чтобы получить достаточное число приближений.

Для получения ряда значений функции F(Xk) в ячейку B26 была введена формула, задающая функцию: " =(((Cof1*A26 + Cof2)*A26 + Cof3)*A26 + Cof4)*A26 + Cof5 "

Затем эта формула была скопирована параллельно значениям аргументов.

Аналогичные операции были проделаны для получения значений ряда производных F'(Xk). Формула, введенная в ячейку C26, имела вид: " =((4*Cof1*A26 +3*Cof2)*A26 + 2*Cof3)*A26 + Cof4 "

Затем и эта формула была скопирована параллельно значениям аргументов.

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

(рис 1.4) Зависимость ячеек

Все завершается, как всегда, построением графика. На следующем рисунке можно видеть, как для выбранного начального приближения сходится итерационный процесс, Xk - стремится к значению корня, F(Xk) - к нулю. Здесь же показано и поведение производной, которая тоже в данном случае стремится к постоянному значению. Оцените удобство Excel для решения подобных задач. Известно, что для итерационных процессов успех во многом зависит от правильного выбора начального приближения. Здесь подобрать начальное приближение легко и просто. Заодно отметим, что данное решение позволяет найти поочередно все корни уравнения. Известно, что с ростом степени уравнения итерационные методы начинают "спотыкаться" и перестают сходиться. Нам удавалось, однако, найти решение этого и других подобных уравнений, используя даже такой плохо сходящийся метод, как метод простой итерации. Часто процесс приближения расходился, но найти корень уравнения с приемлемой точностью за сч ет подбора начального приближения все же удавалось.

(рис 1.5) Нахождение корня уравнения методом Ньютона

Задача 4

Найти корень уравнения F(x)=0 , где функция F(x) задается программистом и реализована на VBA.

Можно ли сочетать программирование без программирования с "настоящим" программированием. Ответ - безусловно "да". Данный пример позволяет продемонстрировать эту возможность. Я покажу, что функцию, корень которой разыскивается, и ее производную можно написать на VBA. При вызове этих функций в формулах рабочего листа в качестве параметров им можно передавать ячейки таблицы, записанные как в абсолютных, так и в относительных адресах. Копирование таких формул производит прежний эффект. Решение задачи 4 обобщает решение задачи 3, распространяя его на те ситуации, когда функция, корень которой разыскивается, не может быть задана простой формулой, и ее нельзя описать с помощью формул рабочего листа. Такая ситуация достаточно типична, например, когда требуются циклические вычисления для получения значения функции. Все эти трудности снимаются, благодаря тому, что Excel позволяет вызывать в формулах рабочего листа функции, написанные на VBA . В качестве примера функции F(x) я буду использовать ту же функцию, что и в задаче 3, - полином 4-й степени. Это позволит нам сравнить два решения и убедиться в том, что решение, полученное с использованием программирования, выглядит даже более элегантно, я бы сказал. С другой стороны, заметьте, сохраняются все преимущества, которые дает машина вычислений Excel, не требующая программирования. Обратите внимание, пример демонстрирует передачу данных из рабочего листа в функцию, написанную на VBA и обратно.

Для решения задачи 4 я написал на VBA две функции. Вот как они выглядят:

Public Function Polinom4(Cofs As Variant, X As Variant) As Variant
	'Вычисляет значение полинома 4-й степени в точке,
	'заданной параметром X.
	'Коэффициенты полинома передаются в первом параметре Cofs.
	'Значением параметра Cofs может быть объект Range.
	Polinom4 = (((Cofs.Cells(1) * X + Cofs.Cells(2)) * X + _
		Cofs.Cells(3)) * X + Cofs.Cells(4)) * X + Cofs.Cells(5)
End Function

Public Function Pr4(Cofs As Variant, X As Variant) As Variant
	'Вычисляет значение производной полинома 4-й степени в точке,
	'заданной параметром X.
	'Коэффициенты полинома передаются в первом параметре Cofs.
	'Значением параметра Cofs может быть объект Range.
	Pr4 = ((4 * Cofs.Cells(1) * X + 3 * Cofs.Cells(2)) * X + _
		2 * Cofs.Cells(3)) * X + Cofs.Cells(4)
End Function

Затем я практически повторил действия, предпринятые для решения задачи 3. Реально, конечно, я ничего не повторял, а просто создал копию листа 4 и внес в нее необходимые изменения. Эти изменения коснулись тех ячеек, в которых вычисляется значение функции и ее производной. В ячейке B12 я заменил формулу, вычисляющую функцию, на вызов функции Polinom4:

"=Polinom4($C$9 : $G$9; A12)"

Аналогично в ячейке B26 появилась формула:

"=Polinom4($C$9 : $G$9; A26)"

а в ячейке C26:

"=Pr4($C$9 : $G$9; A26)"

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

Наш пример интересен и тем, что в нем показано, как передается массив ячеек и отдельная ячейка рабочего листа в функцию VBA. С другой стороны, результат вычисления функции передается в формулу рабочего листа. Заметьте, что хотя формальный параметр функций имеет тип Variant, ему можно в качестве фактического параметра передавать объекты Range. В теле функции можно работать с этим параметром, как с объектом Range, вызывая его свойства и методы, как это делается в наших функциях, где вызывается свойство Cells этого объекта. Я еще вернусь в последующем к теме передачи информации между рабочим листом и процедурами и функциями VBA и остановлюсь на этом более подробно. В заключение, взгляните, как выглядит решение этой задачи. Поскольку ищутся корни той же функции, что и в задаче 3, то, чтобы избежать повторения рисунков, я привожу решение, в котором найден другой корень нашей функции:

Массивы, формулы с массивами

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

Создание одномерных массивов (векторов) и двумерных (матриц) на рабочем листе Excel вручную представляется совершенно естественным делом и вряд ли стоит, что-либо говорить по этому поводу, - берешь и создаешь. Также просто при желании дать имена этим массивам. Программно это можно сделать, используя свойство Name объекта Range. Вручную это можно делать по-разному, выделив объект Range и задав его имя в окошке имен или использовать для этой цели диалоговое окно, до которого можно добраться из меню Вставка | Имя.

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

Если результатом такой формулы является скаляр (одно значение), то такая формула вводится, как обычно, в ячейку, в которой и будет находиться результат вычислений. Единственное, но важное отличие состоит в том, завершить ввод формулы над массивами следует нажатием комбинации клавиш: "Ctrl+ Shift + Enter". Это немедленно отобразится в поле ввода формулы тем, что такая формула будет заключена в фигурные скобки. Заметьте, что нельзя самому задать эти скобки для придания формуле статуса "массивной". Формулу следует всегда начинать со знака равенства, а не с фигурной скобки.

Если результатом формулы является массив, то предварительно этот массив должен быть выделен и затем уже следует ввести формулу над массивом, она как бы распределяется на все элементы выделенного массива. Заметьте, что при работе с формулами над массивами есть существенное ограничение - аргументы и результат должны быть, как правило, массивами одинаковой размерности и, более того, вытянуты в одном и том же направлении. Не может быть аргумент строка, а результат столбец. Именно это ограничение существенно снижает возможности работы с матрицами вручную. Давайте перейдем к примерам, на которых легче понять некоторые детали, а также семантику, задающую детали реализации формул с массивами.

Задача 5

Вычислить скалярное произведение двух векторов c = AB = Sum(Ai*Bi)

В первом варианте решения этой задачи я на рабочем листе "Лист5" нашей книги CourseFirst создал два вектора A и B, введя значения их компонент соответственно в ячейки D2:F2 и D3:F3. Для получения скалярного произведения этих векторов я написал формулу над массивами: " {=СУММ(D2:F2*D3:F3)} ". Поскольку результатом вычислений является в данном случае скаляр, то формула помещается в единственную ячейку. Я выбрал для результата ячейку D4.

Второй вариант решения задачи отличается лишь тем, что векторам A и B даны имена. Свои действия в этом варианте опишу более подробно:

Задача 6

Вычислить вектор, компоненты которого являются произведением соответствующих компонент векторов A и B

C =A*B ={A1*B1, A2*B2, … An*Bn}

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

В качестве сомножителей я использовал VectorA и VectorB предыдущей задачи. Но поскольку теперь результатом является вектор той же размерности, что и аргументы, то я выделил область ячеек для этого вектора - D12:F12 и ввел формулу над массивами:

"{= VectorA * VectorB }".

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

Задача 7

Вычислить "сигнальную" матрицу B= sign(A).

Сигнальная матрица имеет ту же размерность, что и матрица, являющаяся аргументом. Ее возможные значения (1, 0, -1) определяются знаком соответствующего элемента матрицы A.

Bij =1;	     Если Aij > 0 
Bij =0;	     Если Aij =0 
Bij = -1;    Если Aij <0

На этом примере я продемонстрирую действия, выполняемые над матрицей, а заодно покажу использование в рабочих формулах одной из любимых функций программиста - функции ЕСЛИ (IF). Для решения этой задачи я:

"{=ЕСЛИ(MatrA > 0; 1; ЕСЛИ(MatrA = 0; 0; -1))}"

Заметьте, функция ЕСЛИ реализует полноценную If - Then - Else конструкцию, где в качестве Else может снова использоваться функция ЕСЛИ. Что же касается семантики нашей формулы над массивами, то понятно, что для ее реализации организуется двойной цикл по всем элементам исходной матрицы, на каждом шаге которого проверяется свое условие в If - операторе и в зависимости от его истинности вычисляется соответствующее значение элемента матрицы B.

В заключение взгляните, как выглядит рабочий лист, на котором показано решение задач 5, 6 и 7:

При локализации Excel введены русские имена для стандартных функций, вызываемых в формулах рабочего листа. Так вместо исходных имен ,".

Задача 8

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

Постановка задачи:

Вычислить скалярное произведение векторов разной ориентации: C = SUM(Aj*Bj).

Вначале рассмотрим результаты следующего эксперимента. Вот мои действия:

Операции над векторами разной ориентации

То, что полученный результат в предыдущем примере, отличается от ожидаемого, очевидно не связано с функцией суммирования. Функция СУММ честно суммирует все компоненты своего аргумента. Все дело в том, как Excel выполняет основные операции - сложение, умножение, вычитание и деление над массивами. Результат выполнения элементарных операций над массивами есть массив, размерность и ориентация которого зависит от размерности и ориентации массивов, являющихся аргументами операции. Для простоты я ограничусь сейчас рассмотрением действий над векторами, отложив рассмотрение действий над матрицами до следующей главы. Поскольку структура массива результата не зависит от выполняемой операции, то я рассмотрю только операцию умножения, - для сложения, вычитания, деления все будет выполняться аналогично. Для того чтобы рассмотреть все возможные случаи я ввел 3 горизонтально расположенных вектора и 3 вектора с вертикальной ориентацией. Эти вектора получили соответственно имена: HorA3, HorB3, HorA2 и VerA3, VerB3, VerA2. Первые два вектора в каждой ориентации состоят из трех компонент, третий вектор имеет две компоненты, на что указывает имя вектора. Теперь все готово, чтобы рассмотреть семантику выполнения операций и продемонстрировать соответствующие примеры:

"{= HorA2* VerA3}"

Результаты всех моих действий по работе с векторами разной ориентации показаны на следующем рисунке:

Вернемся теперь к задаче 8 и посмотрим, как же все-таки вычислить настоящее скалярное произведение в том случае, когда вектора имеют разную ориентацию. Ответ понятен, проще всего привести их к одной ориентации, для чего один из них следует транспонировать. Для транспонирования можно воспользоваться стандартной функцией ТРАНСП, что я и сделал, введя в одну из ячеек - J5 формулу над массивами: " {=СУММ(VerA3*ТРАНСП(HorA3))} ". Вычисление по этой формуле дает нужный результат.

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