Массивы констант в EXCEL

Массив значений (или константа массива или массив констант) – это совокупность чисел или текстовых значений, которую можно использовать в формулах массива . Константы массива необходимо вводить в определенном формате, например, для чисел {1:2:3:4:5} или для текстовых значений {"Север":"ЮГ":"Восток":"Запад"}.

В обычную формулу можно ввести ссылку на ячейку, содержащую значение, или на само значение, называемое также константой. Подобным образом в формулу массива можно ввести или ссылку на массив, или массив значений , содержащихся в ячейках (в справке EXCEL его называют константой массива ). Константы массива необходимо вводить в определенном формате.

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

Перед созданием Массива констант посмотрим повнимательнее на диапазон ячеек, содержащий обычные значения (см. Файл примера )

• в диапазон ячеек A 1: A 5 введите, например, 5 последовательных чисел от 1 до 5.

• в ячейке B1 введите формулу =A1:A5 ;

• в Строке формул выделите A1:A5 и нажмите клавишу F9 ;
• получим некую запись {1:2:3:4:5}, представляющую собой набор значений из диапазона A1:A5

Этот набор значений, как и формулы массива , обрамлен в фигурные скобки, сами значения разделены двоеточиями. Если бы значения были размещены в строке (в диапазоне A1:E1 ), а не в столбце, то значения были бы разделены точкой с запятой {1;2;3;4;5}.

Создадим константу массива в ячейке B2 . Для этого введем в ячейку выражение ={1:2:3:4:5} и нажмем ENTER . Массив значений не заключается в скобки автоматически, как формулы массива после нажатия CTRL+SHIFT+ENTER . Это необходимо делать вручную. В ячейке отразится только первое значение массива, т.е. 1.

Обычно массив значений не вводят в одну ячейку, т.к. в этом случае невозможно вытащить отдельные значения. Чтобы отобразить все значения нашего массива значений нужно выделить 5 ячеек в столбце (например, B1:B5 ), в Строке формул ввести выражение ={1:2:3:4:5} и нажать CTRL+SHIFT+ENTER .

Теперь попробуем удалить один элемент массива, например из ячейки B 3 . Получим предупреждение « Нельзя изменить часть массива » - это определенного вида защита массива.

Чтобы избежать утомительного ввода последовательных чисел для вертикального массива констант можно воспользоваться формулой =СТРОКА(1:5) . Записав ее любой пустой ячейке, выделите ее в строке формул и нажмите F9 , а затем нажмите ENTER . Получите массив констант {1:2:3:4:5} . Скопируйте содержимое ячейки в буфер обмена ( CTRL+C ), затем выделите вертикальный диапазон ячеек, соответствующий размерности массива, вставьте в активную ячейку содержимое Буфера обмена и нажмите CTRL+SHIFT+ENTER.

Чтобы избежать утомительного ввода последовательных чисел для горизонтального массива констант используйте формулу =ТРАНСП(СТРОКА(1:5)) или =СТОЛБЕЦ(A:E) . Получите массив констант ={1;2;3;4;5} . Значения массива будут разделены точкой с запятой.

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

А. Умножение векторов (столбец на столбец, строку на строку) С помощью формулы массива умножим столбец значений ( B 2: B 6 ) на массив констант {1:2:3:4:5} и просуммируем. Массив констант записан в «вертикальном» виде с использованием двоеточия, т.е. также представляет собой столбец. Размерности столбца и массива должны совпадать.

=СУММ(B2:B6*{1:2:3:4:5})

После ввода формулы необходимо нажать CTRL+SHIFT+ ENTER .

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

=СУММ(B2*1;B3*2; B4*3; B5*4; B6*5)

В последнюю очередь выполняется сложение значений при помощи функции СУММ() .

Б. Проверка значений Проверим, равно ли значение в ячейке А1 одному из определенных значений: 4, 6 или 9.

=ИЛИ(A1={4;6;9})

После ввода формулы нет необходимости нажимать CTRL+SHIFT+ENTER . Такая запись может существенно сократить время создания формулы по сравнению с использованием вложенных функций ЕСЛИ() .

Именование массива констант

Массиву констант можно присвоить Имя . Обычно так поступают с константами, образующими группу однотипных значений, например последовательности с конечным количеством элементов ={1:2:3} .

Чтобы присвоить массиву констант имя необходимо сделать следующее:

• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя .
• В поле Имя введите Массив123 .
• В поле Диапазон введите массив констант (не забудьте ввести скобки вручную), например {1:2:3} ;
• Нажмите кнопку ОК.

Пример, найдем сумму 3-х наибольших значений , записав формулу =СУММПРОИЗВ(НАИБОЛЬШИЙ(A1:A10;Массив123)) .

Предполагается, что в диапазоне A1:A10 имеется список числовых значений.

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

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

• выделите в книге диапазон ячеек из четырех столбцов и трех строк ( A1:D3 ).
• в активной ячейке ( А1 ), в Cтроке формул введите выражение ={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;"декабрь"}

Чтобы избежать утомительного ввода этого массива создайте два столбца, один с числами от 1 до 12, другой с названиями месяцев (см. статью Текстовые последовательности ). Затем, в Строке формул введите ссылку на этот диапазон, нажмите F9 и скопируйте в Буфер обмена .

Теперь записав формулу =ВПР(A2;Месяцы;2) , где в ячейке A2 - номер месяца, получим желаемый результат.

Для желающих получить дополнительную информацию о константах массива - ]]> ссылка на статью сайта Microsoft на английском языке ]]> .