Знакомство с формулами массива в Excel

Формула массива – это один из наиболее мощных инструментов Excel, но также и один из самых сложных. Массивом в Excel называют набор элементов, который может обрабатываться как одно целое, так и как набор, т.е. каждый элемент по отдельности. В этом уроке мы познакомимся с базовыми сведениями о массивах.

Одномерные и двухмерные массивы

В Excel различают одномерные и двухмерные массивы. Одномерный массив, его еще называют вектором, может храниться в одной строке (горизонтальный массив) …

Формула массива в Excel

…или в одном столбце (вертикальный массив).

Формула массива в Excel

Двухмерный массив хранится в диапазоне ячеек, его еще называют матрицей.

Формула массива в Excel

В отличие от языка программирования VBA, Excel не поддерживает трехмерные массивы.

Размерность массива

Ранее мы уже отмечали, что массивы в Excel могут быть одномерными и двухмерными. В свою очередь одномерные могут быть горизонтальными и вертикальными. Теперь рассмотрим, как же разделяются элементы в массивах.

Примеры приведены для русифицированной версии Excel.

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

    Формула массива в Excel

    Если ввести его на рабочий лист Excel, получим следующий результат:

    Формула массива в Excel

Чтобы вставить такой массив в Excel, необходимо сначала выделить строку, состоящую из 5 ячеек, затем ввести формулу массива и нажать комбинацию клавиш Ctrl+Shift+Enter.

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

    Формула массива в Excel

    Если ввести его на рабочий лист, получим следующий результат:

    Формула массива в Excel

  2. В двухмерных массивах для разделения элементов в строке используется точка с запятой, а для разделения строк – двоеточие. На рисунке ниже представлен массив, который состоит из 3 строк и 5 столбцов.

    Формула массива в Excel

    Если ввести данный массив в Excel, результат будет таким:

    Формула массива в Excel

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

Формула массива в Excel

Если попробовать ввести такой массив на рабочий лист, то ничего не получится. Excel не позволит Вам это сделать.

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

Формула массива в Excel

Введя данный массив на рабочий лист Excel, получим следующее:

Формула массива в Excel

Одноячеечные и многоячеечные формулы массива

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

Формула массива в Excel

Во втором случае формула вводится в одну ячейку и возвращает только одно значение:

Формула массива в Excel

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

Урок подготовлен для Вас командой сайта office-guru.ru
Автор: Андрей Антонов
Правила перепечатки
Еще больше уроков по Microsoft Excel

21.01.2015 00:22
9463

6 комментариев

25.02.2015 09:03
Здравствуйте! У меня вопрос: Когда я пишу формулу для горизонтального массива ничего не получается. формула не работает и вместо того чтобы данные разложить по ячейкам эксел мне дает вот такой результат (то что отмечено красным):
.
Она скопировала только то что написано первым ( в моем случае слово «mon»)
А вертикальный массив работает если вместо ":" поставить";". А двухмерный вообще не работает. Помогите пожалуйста
Здравствуйте! Судя по скриншоту у Вас английская версия Excel, а данный урок создан для русифицированной версии. В Вашем случае все предельно просто — в качестве разделителя для горизонтального массива используйте запятую (,) вместо точки с запятой, а для вертикального — точку с запятой (;) вместо двоеточия. И все получится!
Надо бы этот нюанс включить в статью. Спасибо!
26.02.2015 12:07
Андрей, у меня по прежнему не выходит горизонтальный массив. Когда я исправил точку с запятой (;) на запятую (,) мне Эксел предложил след.:

Может мне надо что то поменять в настройках?
Давайте сделаем так.
1. Создайте такой диапазон с числами от 1 до 10:

2. В какой-то ячейке сделайте на него ссылку (например, в ячейке A4:

3. Нажмите клавишу F9, диапазон будет преобразован в массив констант:

4. Теперь можете посмотреть, что в вашем случае будет разделителем для горизонтальных и вертикальных элементов массива. В моем случае это ";" и ":".
5. Отпишитесь об успехах, если не сложно.
26.02.2015 18:52
Андрей, сделал все так как вы сказали. Все получилось.

Получается у меня для горизонтального идет слеш (\), для вертикального точка с запятой (;), а для двухмерного (для того, чтобы перейти на строчку ниже) нужно поставить точку с запятой (;)
Теперь я могу создать любой вид массива.

Большое спасибо.

p.s. Классный сайт. Действительно помогает. Причем бесплатно.
Очень рад, что все получилось!
Большое спасибо за p.s. )))