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

Задача 7

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

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

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

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

• Ввел матрицу A в ячейки B18:D20.
• Дал ей имя: " MatrA ".
• Выделил область ячеек F18:H20 для матрицы B.
• Дал ей имя: " MatrB ".
• Ввел в эту область формулу над массивами:

  "{=ЕСЛИ(MatrA > 0; 1; ЕСЛИ(MatrA = 0; 0; -1))}"
• Получил ожидаемый результат.

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

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

увеличить изображение
Рис. 1.7. Рабочий лист с решением задач 5, 6 и 7

При локализации Excel введены русские имена для стандартных функций, вызываемых в формулах рабочего листа. Так вместо исходных имен IF и SUM следует использовать имена: ЕСЛИ и СУММ. Лично я предпочел бы, чтобы русские имена функций были бы синонимами, возможными, но не обязательными в использовании. Но правила нужно принимать. Вместе с тем хочу обратить внимание на то, что в функциях, написанных на VBA, при вызове стандартных функций следует использовать исходные имена функций. Более того, когда MacroRecorder создает макрос, он автоматически транслирует русские имена стандартных функций в исходные имена. Есть еще одна важное отличие в вызове стандартных функций в формулах рабочего листа и на VBA. Заметьте, разделителем параметров в формулах рабочего листа является символ " ; ", а для VBA - " ,".

Задача 8

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

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

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

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

• Создал вектор с горизонтальной ориентацией на рабочем листе, для чего в ячейки F5:H5 ввел значения: 6, 7, 8. Затем выделил этот диапазон и дал созданному вектору имя HorA3.
• Аналогично создал вектор с вертикальной ориентацией на рабочем листе, для чего в ячейки A6:A8 ввел значения: 3, 5, 7. Затем выделил этот диапазон и дал созданному вектору имя VerA3.
• После чего я попытался получить скалярное произведение этих векторов, используя ту же формулу, что и в похожей задаче 5. Для этого я записал в ячейку C11 формулу над массивами: " {=СУММ(VerA3*HorA3)} "
• Результатом вычислений по этой формуле является значение 240, которое и появилось в ячейке C11. Понятно, что в данном случае эта формула вычисляет не скалярное произведение векторов, значение которого равно 92, а нечто другое. Давайте разберемся, как работает машина вычислений Excel в подобных ситуациях.

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

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

1. Операции над векторами одинаковой ориентации и одинаковой размерности. В этом случае результатом является вектор той же размерности и ориентации, что и вектора - аргументы. Каждая компонента результирующего вектора является результатом выполнения операции над соответствующими компонентами векторов - аргументов: c_j = a_j * b_j. В качестве демонстрации я перемножил между собой два вектора горизонтальной ориентации и два вектора вертикальной ориентации. В первом случае я выделил для результата горизонтально расположенный диапазон ячеек B18:D18 и ввел формулу над массивами: " {=HorA3*HorB3} ". Во втором случае в ячейки A14:A16 ввел формулу: " {=VerA3*VerB3} ". В обоих случаях были получены ожидаемые результаты.

2. Операции над векторами одинаковой ориентации и разными размерностями N и M, где N>M. В этом случае результатом выполнения операции является вектор той же ориентации, размерность которого равна N. Вектор является частично определенным, у него будет вычислено лишь M компонент, остальные компоненты получат неопределенное значение - " Н/Д ". Заметьте, нельзя считать, что результатом является полностью определенный вектор размерности M, поскольку в дальнейших операциях участвуют все компоненты. Вот подтверждающий пример. В ячейку E11 я ввел формулу над массивами: " {=СУММ(VerA3*VerA2)} ". Результат вычислений по этой формуле не определен - " Н/Д ", поскольку в суммировании будет участвовать ячейка с неопределенным значением " Н/Д ".

3. Операции над векторами разной ориентации и одной и той же размерности N. Результатом операции над такими векторами будет квадратная матрица размерности N*N, элементы которой получены применением операции ко всем возможным парам элементов: c_i,j = a_i * b_j. Для демонстрации я выделил область из 9 ячеек C14:E16 и ввел формулу над массивами: " {=VerA3*HorA3} ", в результате область заполнилась попарными произведениями элементов соответствующих векторов. Теперь можно объяснить, что произошло в примере, с которого я начал, пытаясь вычислить скалярное произведение векторов разной ориентации. Когда формула над массивами - " {СУММ(A*B)} " применяется к векторам одинаковой ориентации, то суммирование идет над элементами вектора и реализуется одинарным циклом - A_k * B_k. Когда же эта формула применяется к векторам разной ориентации, то суммирование п рименяется к элементам матрицы и реализуется двойным циклом - (A_j * B_k), что, впрочем, эквивалентно произведению сумм: .

4. Операции над вертикально ориентированным вектором размерности N и горизонтально ориентированным вектором размерности M. Как ни странно, несмотря на разную размерность векторов, результатом всегда будет полностью определенная матрица размерности N*M. Ее элементы получаются применением операции ко всем возможным парам элементов исходных векторов:

   

   Заметьте, размерность матрицы - результата не зависит от порядка следования аргументов при записи формулы над массивами, так что операции сложения и умножения коммутативны. Вот два примера. В область ячеек G14:H16 я ввел формулу над массивами: " {=VerA3*HorA2} ", в результате вычислений была создана прямоугольная матрица размерности 3*2. Аналогичная матрица была создана, когда я выделил область ячеек J14:K16 и записал туда следующую формулу:

   "{= HorA2* VerA3}"

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

увеличить изображение
Рис. 1.8. Операции над различно ориентированными векторами

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

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