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

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

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

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

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

  • в диапазон ячеек A1:A5 введите, например, 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.

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

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

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

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

А. Умножение векторов (столбец на столбец, строку на строку)
С помощью формулы массива умножим столбец значений (B2:B6) на массив констант {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 на английском языке]]>.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.6 (10 оценок)

Комментарии

Антон (не проверено)

Отличный сайт - давно юзаю интернет по различным вопросам Эксель - на сайт наткнулся впервые! Отлично всё объяснено - вот его отличие от тех, на которых я был. Всё грамотно и по полочкам разложено! Спасибо!!!

Creator

Приятно слышать, спасибо. Если понравилась статья, то можно ее оценить или кликнуть на иконку Вашей соц.сети (иконки расположены над блоком с комментами)

Яндекс.Метрика