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

  В предыдущем примере возникла необходимость передать массив ячеек в функцию, написанную на 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 даны имена. Свои действия в этом варианте опишу более подробно:

·         Ввел компоненты вектора A в ячейки D6:F6.

·         Выделил эту область и в окне имен дал вектору (объекту Range) имя VectorA 

·         Аналогично ввел компоненты вектора  B в ячейки D7:F7 и  вектору дал имя VectorB.

·         В ячейку D8 ввел формулу над массивами: "{=СУММ(VectorA*VectorB)}".

·         Получил ожидаемый результат.