Знакомство с массивами

Массив — это совокупность элементов, которые интерпретируются как единый объект или как набор отдельных объектов. В 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)

В начало

Полезное

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *