Формулы массивов в Excel — синтаксис формул массивов и массивов констант

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

Что такое массив?

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

формулы массивов варианты

Когда мы говорим о формулах массива, мы подразумеваем, что это нормальная формула Excel (СУММ, МАКС, СЧЁТЕСЛИ…), но немного измененная, чтобы принять в качестве входных данных массив или набор массивов. Это то, что лежит в основе формул массива и делает его столь мощным.

Формула массива вводится определенным образом – простой ввод работать не будет. Давайте рассмотрим пример. Откройте пустой рабочий лист и введите несколько значений, как показано на рисунке. Теперь предположим, что вам необходимо определить адрес ячейки с наименьшим значением, для этого введите формулу, указанную ниже и нажмите сочетание клавиш Ctrl + Shift + Enter.

=АДРЕС(МИН(ЕСЛИ((A1:A9)=МИН(A1:A9);СТРОКА(A1:A9);»»));1)

формула массива минимум

Результатом в этом случае будет адрес ячейки с наименьшим значением в данном диапазоне. Как вы можете увидеть, при обновлении данных в диапазоне, результат тоже меняется. Подобного эффекта можно также добиться с помощью обычной формулы =АДРЕС(ПОИСКПОЗ(МИН(A1:A9);A1:A9;0);1). Однако ж мы только начали изучение, в дальнейшем вы обнаружите, что некоторые вещи можно делать только с помощью формулы массива, либо с большим количеством обычных формул.

Части формул массивов в Excel

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

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

(Прежде чем мы пойдем дальше, убедитесь, что при вводе формул массива, вы вводите Ctrl + Shift + Enter, а не обычный Enter, как при обычных формулах).

Массив констант в формулах массивов

Массив констант – это набор статических значений. Эти значения не ссылаются на другие ячейки или диапазоны. Поэтому они будут всегда одинаковыми независимо от изменений происходящих на листе.

Горизонтальный массив констант

Горизонтальный массив констант вводиться как последовательность чисел, разделенных точкой с запятой (;), заключенных в фигурные скобки. Например: {1;2;3;4;5}. Горизонтальные массивы могут быть использованы в качестве входных данных для формулы массива. Они также могут быть введены в таблицу, как показано ниже.

горизонтальный массив

Вертикальный массив констант

В отличие от горизонтального, в вертикальном массиве констант значения разделяются двоеточием (:) и также заключаются в фигурные скобки. Например: {1:2:3:4:5}.

вертикальный массив

Операторы массива в формулах массивов

Оператор массива сообщает формуле, какую операцию необходимо совершить над массивами, предоставленными в качестве массива. К тому же, вы можете использовать операторы И (альтернативный вариант написания — *) и ИЛИ альтернативный вариант написания — +).

Оператор массива И

Оператор И возвращает значение ИСТИНА в случаях, когда все условия выражения возвращают значение ИСТИНА. Пример ниже показывает использование оператора массива И (*) между массивами:

формулы массивов в excel

Оператор массива ИЛИ

Оператор ИЛИ возвращает значение ИСТИНА, если хотя бы один из условий выражения возвращает значение ИСТИНА. Пример ниже показывает использование оператора массива ИЛИ (+) между массивами:

формулы массивов в excel

Что такое диапазон массива?

Диапазон массива вводиться точно также, как и обычная формула (например, A1:A10). Их не обязательно сразу же заключать в скобки (Например, =СУММ(ЕСЛИ((A1:A10)=10;10;»»)) ) или (=СУММ(ЕСЛИ((A1:A10=10);10;»»))). Но для упрощения отладки, я предпочитаю сразу установить скобки в формулах.

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

Возможно, вы уже получили кое-какое представление об этой части статьи. Все что вам нужно, чтобы написать формулу массива – это использовать в качестве аргументов массив и нажать сочетание клавиш Ctrl + Shift + Enter. Давайте рассмотрим некоторые основные моменты синтаксиса формул массивов.

Сортировка с помощью формулы массива

Скажем, у вас есть набор данных в ячейках D2:D10 и вы хотите отсортировать их в порядке возрастания. Вы уже наверное догадались, что нам понадобиться функция НАИМЕНЬШИЙ(), которая возвращает n-ое наименьшее значение и заданного диапазона. Нам также понадобиться диапазон, где мы будем производить вычисления.

Обычная функция НАИМЕНЬШИЙ для одной ячейки выглядит следующим образом =НАИМЕНЬШИЙ(D2:D10;1). Такая формула вернет нам наименьшее значение диапазона D2:D10. Но нам необходимо скопировать эту функцию во все остальные ячейки и внести изменения во второй аргумент, чтобы получить отсортированный список. Для начала выделим диапазон, в котором мы хотим увидеть список, затем вводим формулу в первую ячейку и жмем Ctrl + Shift + Enter. Формула будет скопирована на весь диапазон, результатом станет отсортированный список.

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

Поиск уникального значения, отвечающего определенным условиям

Предположим, мы хотим выяснить имя менеджера с наибольшими продажами. Вот где обнаруживается истинная мощь формул массивов. Ели бы мы использовали обычные формулы, нам понадобилось бы столько же строк, сколько менеджеров, если не больше. Однако мы можем сделать тоже самое в одну формулу массива =СМЕЩ(A1;МАКС(ЕСЛИ(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10))=МАКС(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10)));СТРОКА(A2:A10);»»))-1;0). То, что мы делаем здесь – это сравниваем сумму продаж конкретного менеджера с суммой продаж максимального менеджера. Если условие истинно, возвращает номер строки. Функция ЕСЛИ возвращает массив номеров строк, относящихся к менеджеру с наибольшим показателем продаж, в противном случае возвращается пустота. С помощью функции МАКС мы находим строку, где происходит последнее вхождение имени, а затем с помощью функции СМЕЩ возвращаем имя из этой строки.

суммесли формула массива

Консолидация данных по более чем одному условию

Мы также можем использовать формулу массива для поиска суммы продаж менеджера с максимальными продажами. Функция ЕСЛИ возвращает массив отдельных сумм продаж менеджера совпадающего с менеджером с максимальными продажами, иначе 0. Затем мы используем функцию СУММ для суммирования всех этих значений массива.

макс формула массива

Еще один пример консолидации данных по условию

Теперь попробуйте сами. Дайте мне сумму продаж всех записей, в которых 1) менеджер — Фёдор Абрамов И продукт Книги 2) Продажи >= 500. Подумайте минутку.

Мы можем достичь этого с помощью формулы массива =СУММ(ЕСЛИ(((A2:A10=»Фёдор Абрамов»)*(B2:B10=»Книги»))+((D2:D10>=500));D2:D10;0)). Так что же мы здесь сделали? Мы проверили три условия – первые два были скомбинированы с помощью оператора И (*) и третье было добавлено с помощью оператора ИЛИ (+). В результате получилась структурированная формула массива, где были указаны все три условия в качестве аргумента функции ЕСЛИ. Функция ЕСЛИ в свою очередь генерирует массив со значениями из четвертого столбца, когда оно принимает значение ИСТИНА и 0, если ЛОЖЬ. Результат, конечно, представляет собой сумму продаж отвечающим всем трем, указанным выше, условиям.

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


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

    • Нет сомнений, что есть несколько способов решения задач, и выбор наиболее эффективного зависит от условий и требований. В данном случае вы совершенно правы, и эффективнее использовать одну функцию. Но целью данной статьи было познакомить читателей с основными принципами и правилами работы формул массивов на простых примерах

  1. «=СУММ(ЕСЛИ(((A2:A10="Фёдор Абрамов")*(B2:B10="Книги"))+((D2:D10>=500));D2:D10;0))» — разве данная формула выражает условие «менеджер — Фёдор Абрамов ИЛИ продукт Книги И Продажи >= 500». Мне показалось, здесь зашито условие «менеджер — Фёдор Абрамов И продукт Книги Или Продажи >= 500»

  2. Здравствуйте! Столкнулся с задачей нормирования труда на предприятии. Есть таблица с параметрами, согласно которым выбирается норма времени, в зависимости от того, в какой диапазон попадают вводимые данные, т.е. если в ячейку В14 я ввожу длину листового проката 1 метр, а в ячейку D14 массу листа, то норма времени получается 1,75, указанная в ячейке С17. Я попробовал прописать формулу =ЕСЛИ(И(B14<=2;D14<=50);B5;ЕСЛИ(И(B14<=4;D14<=50);C5;ЕСЛИ(И(B14<=6;D14<=50);D5;… для каждого варианта прописывать формулу очень долго получается. Есть ли возможность задать диапазон значений? К примеру: =ЕСЛИ(И(B14<=B3:E3;D14<A5:A11);Q6:T12).. знаю, эта формула не рабочая :) не силен в Excel'е, так что строго не судите!

    • Александр, попробовал решить вашу задачку, получилась немного громоздкая формула, но работает. Возможно, взяв концепт, сможете ее доработать =ИНДЕКС($C$6:$F$12;СУММПРОИЗВ(ЕСЛИ((A5:A11< $A$20)*(A6:A12>=$A$20);1;0);СТРОКА($K$1:$K$7));СУММПРОИЗВ(ЕСЛИ((B3:E3< $A$19)*(C3:F3>=$A$19);1;0);СТОЛБЕЦ($A$1:$D$1)))
      Также пришлось добавить дополнительные столбцы и строки

      Файл:  Alex220315.xlsx

  3. Ренат, здравствуйте!
    Почему такая формула не работает, если массив находится на другом листе?
    {=СУММ(ЕСЛИ(((A2:A10=»Фёдор Абрамов»)*(B2:B10=»Книги»))+((D2:D10>=500));D2:D10;0))}
    Какой должен быть синтаксис?
    Т.е. всё то же самое,но A2:A10, к примеру ‘Лист2! A2:A10’
    При написании формулы со ссылками на др листы значение 0, а если на одном — то нормальный расчет. Спасибо!

  4. Здравствуйте!
    Столкнулась с проблемой.. Если список заказчиков и суммы необходимые к оплате, полученные, оплаченные и остаток (некоторые вычислены по формулам). Нужно вывести рядом табличку, в которой будут также столбцы УЗ, ИТОгО, Получено, Оплачено, Остаток, только так, чтобы суммы складывались в том случае, если один и тот же УЗ встречается несколько раз… Пока рылась в сети поняла, что нужно использовать СУММ(ПРОСМОТР…)), но не могу понять как это применить на деле, подскажите, пожалуйста.

  5. Здравствуйте! Я ЧАЙНИК! Столкнулся с проблемой. Работаю с файлами в которых порой по 10-15 тыс строк, часть с них нужно отсортировать пронумеровав их, НО! Если в столбце «В» мне нужно обозначить НЕКОТОРЫЕ строки, например от «А4» до «А9» цифрой «2» я выбираю нужные строки фильтром, выделяю этот столбец, потом в верхней ячейке ставлю «2», жму «F4» потом «ctrl+enter» и все было бы хорошо, но так как я работаю быстро (так надо) то после «F4» я не успеваю убрать «shift» когда выделяю столбец и у меня выходит комбинация «shift+ctrl+enter»-комбинация формулы массива, и условная цифра «2» протягивается через всю последовательность строк- от «А4» до «А9»! Как отменить это?! «Отмена» как и сочетание «ctrl+z» не работают((

    Файл:  .xls

  6. Здравствуйте! Вопрос к экспертам.

    Вот прописал я себе для удобства формулы, они идентичны, просто исходные данные лежат в разных закладках, но есть одно НО, первая формула ВЕРНО работает как обычная формула, а вторая только с применением массива

    1. =СЧЁТЕСЛИ(‘Ф-2-2016’!D286:D320;»<=93")+(СЧЁТЕСЛИ('Ф-2-2016'!D323:D327;"<3")+СЧЁТ(ЕСЛИ('Ф-2-2016'!D323:D327=3;ЕСЛИ('Ф-2-2016'!E323:E327<=305;))))+СЧЁТ('Ф-2-2016'!D330:D331)

    2. {=СЧЁТЕСЛИ('Ф-2-2015'!D640:D725;"<=93")+(СЧЁТЕСЛИ('Ф-2-2015'!D728:D731;"<3")+СЧЁТ(ЕСЛИ('Ф-2-2015'!D728:D731=3;ЕСЛИ('Ф-2-2015'!E728:E731<=305;))))+СЧЁТ('Ф-2-2015'!D734:D736)}

    Собственно сам вопрос, даже два)):

    1. Почему так, на одинаковых условиях формулы считают по-разному?
    2. Первый вопрос не столь важный как этот: Можно ли применить ко всем формулам МАССИВ и жить себе счастливо, зная о том, что подсчет первой формулы, как ОБЫЧНАЯ формула и как формула МАССИВА на будущее, при добавлении строк в таблицах ВСЕГДА БУДЕТ РАБОТАТЬ ВЕРНО. т.е. ЕСЛИ ОБЫЧНАЯ формула верно подсчитывает, можно ли применить к не формулу МАССИВА?

    в первом случае ОБЫЧНАЯ формула и МАССИВ дают одинаковую цифру, во втором разнятся, но массив вернее

  7. Здравствуйте! Вопрос к экспертам.

    Предприятие предоставляет сервис обслуживание техники.
    Специалистов обслуживающих технику 30 чел.
    В Базе EXEL техника приходуется по серийному. номеру.

    Столкнулся с задачей где, необходимо применить формулу для ячейки, в которой будет отражаться адрес ячейки и фио специалиста, который обслуживал эту технику заранее.
    Заранее благодарен…

  8. Прошу помощи!
    Нужно найти совпадающие пары электронных адресов из столбцов «I» и «К», и разместить в клетке правее одного из совпавших электронных адресов столбца «I» значение клетки, которая находится правее другого совпавшего электронного адреса столбца «К».

    Файл:  .xlsx

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

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