Массив — это совокупность элементов, которые интерпретируются как единый объект или как набор отдельных объектов. В Excel массивы могут быть одномерными (их еще называют векторами) или двухмерными (матрицы). Размерность массива зависит от количества строк и столбцов. Например, одномерный массив может храниться в диапазоне, состоящем из одной строки (горизонтальный массив), или столбце (вертикальный массив). Двухмерный массив хранится в прямоугольном диапазоне ячеек. Excel (в отличие от языка программирования VBA) не поддерживает трехмерные массивы.
Массивы вовсе не обязательно хранить в ячейках. С помощью формул массива можно выполнять операции над массивами, которые существуют только в памяти Excel. Кроме того, формулы массива можно использовать для выполнения операций и получения результатов вычисления выражений. Формула массива может находиться во многих ячейках или только в одной.
Обычно после ввода формулы следует нажать клавишу <Enter>, однако это не относится к формулам массивов. При вводе формулы массива нужно нажать комбинацию клавиш <Ctrl+Shift+Enter>.
Excel заключает формулу в фигурные скобки {}. Это говорит о том, что данная формула является формулой массива. При вводе формулы можно не использовать фигурные скобки, однако после введения не забудьте нажать, комбинацию клавиш <Ctrl+Shift+Enter>.
Использование единой формулы массива предпочтительнее, чем нескольких, так как она предоставляет определенные преимущества.
• Несложно убедиться, что все формулы в диапазоне идентичны.
• Использование формул массива, возвращающих сразу несколько значений, избавляет от случайных изменений в формуле. Невозможно в формуле массива изменить одну ячейку.
• Использование формул массива почти всегда предотвращает вмешательство начинающих пользователей, которые хотят внести свои изменения в формулы.
Чаще всего невозможно формулу массивов заменить одной функцией.
Создание массива констант
Массив не обязательно должен храниться в диапазоне ячеек. Массив, который хранится исключительно в памяти, называется массивом констант.
При создании массива констант список его элементов нужно заключить в фигурные скобки. Ниже приведен пример горизонтального массива констант, состоящего из пяти элементов.
{1;0;1;0;1}
Следующая формула содержит функцию СУММ, аргументом которой является предыдущий массив констант. Формула возвращает сумму элементов массива, равную трем. Отметим, что данная формула принимает массив в качестве аргумента, но при этом не является формулой массива. Поэтому для ввода формулы не нужно использовать клавиши <Ctrl+Shift+Enter>.
=СУММ({1;0;1;0;1})
Элементы массива констант
Массивы констант могут состоять из чисел, текста, логических значений (например, ИСТИНА, ЛОЖЬ) и даже значений ошибки (таких как #Н/Д). Числа в массиве могут быть целыми, с десятичной точкой или в экспоненциальном формате. Текст в массиве констант заключается в двойные кавычки (например, “Вторник”). Массив констант может состоять из элементов разного типа, как показано в следующем примере:
{1;2;3;ИСТИНА;ЛОЖЬ;ИСТИНА;“Вася”;“Петя”}
Массивы констант не могут включать в себя формулы, функции или другие массивы. Числовые значения не должны содержать символы доллара, точки с запятой, круглые скобки или символ процента. Например, ниже приведен неверный массив констант.
{КОРЕНЬ(32);$56,32;12,5%}
Одномерные горизонтальные массивы
Элементы в одномерном горизонтальном массиве разделяются точкой с запятой. Ниже приведен пример одномерного горизонтального массива констант.
{1;2;3;4;5}
Для отображения этого массива в диапазоне потребуется пять последовательных ячеек в строке. Чтобы ввести этот массив в диапазон, выделите диапазон ячеек, состоящий из одной строки и пяти столбцов. Затем введите формулу ={1;2;3;4;5} и нажмите <Ctrl+Shift+Enter>.
Если ввести этот массив в горизонтальный диапазон, состоящий более чем из пяти ячеек, то лишние ячейки будут содержать #Н/Д (что указывает на недопустимые значения). Если ввести этот массив в вертикальный диапазон ячеек, то в каждой ячейке диапазона появится первый элемент (1) массива.
Одномерные вертикальные массивы
Элементы в одномерном вертикальном массиве разделяются двоеточием (точкой с запятой в английской версии программы). Ниже приведен пример вертикального массива констант, состоящего из шести элементов.
{10:20:30:40:50:60}
Для отображения этого массива в диапазоне потребуется шесть ячеек в столбце. Чтобы ввести этот массив в диапазон, выделите диапазон ячеек, состоящий из шести строк и одного столбца. Затем введите приведенную ниже запись и нажмите <Ctrl+Shift+Enter>.
={10:20:30:40:50:60}
Двухмерные массивы
В двухмерных массивах используется точка с запятой (запятая в английской версии программы) для разделения горизонтальных элементов и двоеточие (точка с запятой в английской версии программы) для разделения вертикальных элементов. Ниже приведен пример массива констант размерностью 3×4.
{1;2;3;4:5;6;7;8:9;10;11;12}
Для отображения этого массива в диапазоне требуется двенадцать ячеек. Чтобы ввести его на рабочем листе, выделите диапазон ячеек, состоящий из трех строк и четырех столбцов. Затем введите следующую формулу, после чего нажмите <Ctrl+Shift+Enter>:
={1;2;3;4:5;6;7;8:9;10;11;12}
Рисунок ниже демонстрирует, как будет выглядеть этот массив 3×4, если его ввести в диапазон ячеек (в данном случае в диапазон ВЗ:Е5).
Если ввести массив в диапазон, состоящий из большего количества ячеек, чем сам массив, Excel отобразит #Н/Д в лишних ячейках.
Каждая строка двухмерного массива должна содержать одинаковое количество элементов. Например, представленный ниже массив не может существовать, так как его третья строка состоит только из трех элементов.
{1;2;3;4:5;6;7;8:9;10;11}
Excel не позволит ввести формулу, в которой присутствует недопустимый формат массива.
Значение #Н/Д можно использовать в качестве заполнителя отсутствующего элемента массива. Например, следующий массив синтаксически правильный.
={1;2;3;4:5;6;7;8:#Н/Д;10;11;12}
Присвоение имен массивам констант
Можно создать массив констант, присвоить ему имя и затем использовать это имя в формулах. Присвоение имени массиву сводится к присвоению имени формуле.
На рисунке показано, как присвоить имя массиву, используя диалоговое окно Присвоение имени. Чтобы открыть его, выберите команду Формулы→Определенные имена→Присвоить имя→Присвоить имя.
Приведенному ниже массиву констант присвоено имя ДниНедели.
{“Понедельник”;“Вторник”;“Среда”;“Четверг”;“Пятница”;“Суббота”;“Воскресенье”}
В диалоговом окне Создание имени для задания массива необходимо использовать знак равенства (=). В противном случае массив будет воспринят как текстовая строка. Также необходимо самостоятельно заключить элементы массива в фигурные скобки. Excel в данном случае не вводит скобки автоматически.
После присвоения имени массиву это имя можно использовать в формулах. На рисунке представлен рабочий лист, в котором находится формула, вводящая массив в диапазон А1:G1. Она имеет следующий вид.
{=ДниНедели}
Так как элементы массива разделяет точка с запятой, ориентация массива будет горизонтальной. Для создания вертикальных массивов используется двоеточие. Также можно воспользоваться функцией ТРАНСП и вставить горизонтальный массив в вертикальный диапазон ячеек. Для этого введите в диапазон из семи вертикальных ячеек следующую формулу массива:
{=ТРАНСП(ДниНедели)}
При использовании функции ИНДЕКС можно получить доступ к отдельному элементу массива. Например, приведенная ниже формула возвращает значение “Четверг” – четвертый элемент массива ДниНедели.
=ИНДЕКС(ДниНедели;4)