Excel. Введение в формулы массива

Ранее я уже посвятил несколько заметок использованию формул массива:

Полученные комментарии подсказали мне, что пользователи не часто используют формулы массива. Более того мало кто знаком с ними.

Предлагаю вашему вниманию посвященный массивам фрагмент книги Джона Уокенбаха. Подробное руководство по созданию формул в Excel 2002. – М.: Издательский дом «Вильямс», 2002. – 624 с.

Скачать заметку в формате Word, примеры в формате Excel

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

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

Формула массива, возвращающая несколько значений

Рис. 1 демонстрирует рабочий лист, на котором производится расчет объема продаж товаров. Для этого вычисляют значение в столбце D (объем продаж каждого товара), используя формулу =В2*С2, а затем копируют ее в расположенные ниже ячейки.

Рис. 1. Диапазон D2:D6 содержит традиционные формулы произведения (в ячейках D2 и D3 различные формулы, хотя и похожие)

После копирования этой формулы рабочий лист содержит пять формул в столбце D. Альтернативный путь вычислений – использование единой формулы (формулы массива) для вычисления всех пяти значений в диапазоне D2:D6. Эта единственная формула занимает пять ячеек и возвращает массив из пяти значений. Для создания единой формулы массива нужно произвести следующие операции:

1. Выделить диапазон, в котором будет сохраняться результат вычислений. В данном случае это диапазон D2:D6.
2. Ввести формулу: =В2:В6*С2:С6
3. Обычно после введения формулы нажимается клавиша <Enter>, но только не в случае формулы массива. Так как введенная формула является формулой массива, нажимается комбинация клавиш <Ctrl+Shifi+Enter>.

Формула добавляется во все пять выделенных ячеек (рис. 2), при этом в строке формул появится следующее выражение:

Рис. 2. Диапазон D2:D6 содержит формулу массива (в ячейках D2 и D3 одна и та же формула)

Excel заключил формулу в фигурные скобки. Это говорит о том, что данная формула является формулой массива.

Эта формула производит вычисления и возвращает массив из пяти элементов. В данном случае формула массива производит операции над двумя другими массивами, оба из которых хранятся в диапазонах ячеек. Значения первого массива хранятся в диапазоне В2:В6, а второго — в С2:С6. Для отображения итогового массива требуется пять ячеек, потому что в одной ячейке нельзя отобразить более чем одно значение. Этим объясняется, зачем нужно выделять пять ячеек перед введением формулы.

Конечно, эта формула массива возвращает точно такие же значения, что и пять формул, введенных в соответствующие ячейки диапазона D2:D6

Использование единой формулы массива предпочтительнее, чем использование нескольких формул, так как предоставляет определенные преимущества:

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

Формула массива, возвращающая одно значение

Обратимся к рис. 3. Следующая формула массива размещается в одной ячейке – D8:

Рис. 3. Сумма произведений в виде формулы массива

Эту формулу можно ввести в любую ячейку. При вводе формулы не нужно использовать фигурные скобки, а после введения не забудьте использовать комбинацию клавиш <Ctrl+Shift+Enter>.

Эта формула массива возвращает общую сумму, на которую продано товаров. Важно понимать, что значение, возвращаемое этой формулой, не зависит от информации в столбце D. Фактически можно удалить столбец D, а формула будет выполняться по-прежнему.

Эта формула оперирует значениями, находящимися в двух массивах. Оба массива хранятся в ячейках. Первый – в диапазоне ячеек В2:В6, второй – C2:С6. Формула перемножает соответствующие значения в этих двух массивах и создает новый массив (который существует только в памяти компьютера). Функция СУММ выполняет операцию над этим новым массивом и возвращает значение суммы его элементов.

Создание массива констант

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

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

Формула =СУММ() использует функцию СУММ, аргументом которой является массив констант. Формула возвращает значение суммы элементов массива, равное 3. Отметим, что эта формула использует массив в качестве аргумента, но при этом не является формулой массива. Поэтому не нужно использовать клавиши <Ctrl+Shift+Enter> для ввода формулы.

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

Пока, возможно, трудно заметить, какие-либо преимущества использования массивов констант. Действительно, формула =СУММ(1;0;1;0;1) возвращает точно такое же значение, как и =СУММ().

Очевидные преимущества становятся ясны при дальнейшем рассмотрении. Следующая формула использует два массива констант: =СУММ(*). Эта формула создает в памяти новый массив, который состоит из произведения соответствующих элементов двух других массивов: . Созданный массив используется впоследствии в качестве аргумента для функции СУММ, которая возвращает результат вычислений (70). Формула эквивалентна следующей формуле, которая не использует массивы: =СУММ(1*5;2*6;3*7;4*8)

Можно составлять формулы массивов для разных типов массивов. Например, можно составить формулу, в которой будет одновременно использоваться массив констант и массив, элементы которого хранятся в диапазоне ячеек (рис. 4). Для подсчета суммы значений, которые являются произведением элементов массива в диапазоне Al:D1 и соответствующих элементов массива констант, применяется следующая формула: =. Эта формула эквивалентна следующей: =СУММ(А1*1;В1*2;С1*3;D1*4)

Рис. 4. Сумма произведений массива рабочего листа и массива констант

Элементы массива констант

Массивы констант могут состоять из чисел, текста, логических значений (например, ИСТИНА, ЛОЖЬ) и даже значений ошибок, таких как #Н/Д. Числа в массиве могут быть целыми, с десятичной точкой или экспоненциальными. Текст в массиве констант заключается в двойные кавычки (например, " Вторник " ). Массив констант может состоять из элементов разного типа, например:

Массивы констант не могут включать в себя формулы, функции или другие массивы. Числовые значения не могут включать символы доллара, точки с запятой (точки), круглые скобки или символ процента. Например, неверный массив констант:

Понятие размерности массива

Массивы могут быть одномерными или двумерными. Ориентация одномерного массива может быть горизонтальной или вертикальной.

Одномерные горизонтальные массивы

Элементы в одномерном горизонтальном массиве разделяются точкой с запятой (запятыми в английской версии программы). Ниже приведен пример одномерного горизонтального массива констант:

Для отображения этого массива в диапазоне необходимо пять последовательных ячеек в строке. Чтобы ввести этот массив в диапазон, выделите диапазон ячеек, состоящий из одной строки и пяти столбцов. Затем введите: = и нажмите <Ctrl+Shtft+Enter> (рис. 5).

Рис. 5. Одномерный горизонтальный массив.

Обратите внимание, внутренние фигурные скобки обозначают массив констант; внешние фигурные скобки – формулу массива. Если ввести этот массив в горизонтальный диапазон, состоящий более чем из пяти ячеек, то лишние ячейки будут содержать #Н/Д (что указывает на недопустимые значения). Если ввести этот массив в вертикальный диапазон ячеек, то в каждой ячейке диапазона появится первый элемент (1) массива.

Ниже приведен пример другого горизонтального массива. Он состоит из семи элементов и создает текстовую последовательность: < " Понедельник " ; " Вторник " ; " Среда " ; " Четверг " ; " Пятница " ; " Суббота " ; " Воскресенье " >. Для ввода этого массива выделите семь элементов в строке и введите следующий текст: =< " Понедельник " ; " Вторник " ; " Среда " ; " Четверг " , " Пятница " ; " Суббота " ; " Воскресенье " >. Нажмите <Ctrl+Shift+Enter>.

Одномерные вертикальные массивы

Элементы в одномерном вертикальном массиве разделяются двоеточием (точкой с запятой в английской версии программы). Ниже приведен пример вертикального массива констант, состоящего из шести элементов: . Для отображения этого массива в диапазоне необходимо шесть ячеек в столбце. Чтобы ввести этот массив в диапазон, выделите диапазон ячеек, состоящий из шести строк и одного столбца. Затем введите: = и нажмите <Ctrl+Shift+Enter>.

Ниже приведен пример другого вертикального массива, состоящего из четырех элементов:

Двумерные массивы

В двумерных массивах используются точки с запятыми (запятые в английской версии программы) для разделения горизонтальных элементов и двоеточия (точки с запятой в английской версии программы) для разделения вертикальных элементов. Ниже приведен пример массива констант размерностью 3×4: . Для отображения этого массива в диапазоне требуется двенадцать ячеек. Чтобы ввести этот массив в диапазон, выделите диапазон ячеек, состоящий из трех строк и четырех столбцов. Затем введите следующую формулу = после чего нажмите <Ctrl+Shift+Enter> (рис. 6).

Рис. 6. Двумерный массив

Если ввести массив в диапазон, состоящий из большего числа ячеек, Ехсеl отобразит #Н/Д в лишних ячейках (рис. 7).

Рис. 7. Показано, что получится, если ввести массив 3×4 в диапазон ячеек 10×5.

Каждая строчка двумерного массива должна содержать одинаковое количество элементов. Например, представленный ниже массив недопустимый, потому что его третья строка состоит только из трех элементов; . Excel не даст возможности ввести формулу, в которой присутствует недопустимый формат массива.

Присвоение имени массивам констант

Excel позволяет создать массив констант, присвоить ему имя и затем использовать это имя в формулах. Фактически присвоение имени массиву заключается в присвоении имени формуле (рис. 8). Пройдите по меню: Формулы – Диспетчер имен.

Рис. 8. Показано, как присвоить имя массиву, используя диалоговое окно Создание имени.

Массиву присвоено имя ДеньНедели, которое соответствует следующему массиву констант: < " Понедельник " ; " Вторник " ; " Среда " ; " Четверг " ; " Пятница " ; " Суббота " ; " Воскресенье " >. Отметим, что в диалоговом окне Создание имени для задания массива необходимо использовать знак равенства (=) перед массивом констант. В противном случае массив будет воспринят как текстовая строка. Также необходимо самостоятельно взять элементы массива в фигурные скобки. Excel в этом случае не вводит скобки автоматически. После присвоения имени массиву, его имя можно использовать в формулах (рис. 9).

Рис. 9. Ввод поименованного массива в диапазон Al:G1

Так как элементы массива разделяют точки с запятой, ориентация массива будет горизонтальной. Для создания вертикальных массивов используются двоеточия. Также можно воспользоваться функцией транспонирования для того, что бы вставить горизонтальный массив в вертикальный диапазон (рис. 10). Для этого введите в диапазон семи вертикальных ячеек A1:A7 формулу массива:

Рис. 10. Ввод горизонтального массива в вертикальный диапазон

При использовании функции Excel ИНДЕКС можно получить доступ к отдельному элементу массива. Например, ниже приведенная формула возвращает значение «Четверг», четвертого элемента массива ДеньНедели: =ИНДЕКС(ДеньНедели;4)