Если Вы довольно много работаете в Excel то наверняка уже слышали выражение "формула массива". Так же его часто можно встретить на форумах и сайтах, посвященных Excel. Но не все знают что это такое и тем более, как пользоваться. Главное это не путать формулы массива с функциями для работы с массивами и функциями для работы с базами данных. Итак, не буду затягивать.
Большинство уже наверняка знакомо с функцией СУММ(SUMM). Она суммирует значения в заданных диапазонах ячеек. Теперь рассмотрим поближе. Запишем в ячейки А1:А5 числа от одного до пяти. В ячейке В1 запишем функцию: =СУММ(A1:A5). Получим сумму этих чисел - 15. Все просто и понятно. И вроде бы - функция уже работает с массивами - ведь группа ячеек А1:А5 по сути уже массив ячеек. Но это не формула массива. А теперь изменим функцию на такую: =СУММ(A1:A5+1). Что я хочу получить от такой функции? Чтобы суммировались значения ячеек А1:А5, но с прибавлением к каждому аргументу 1. Ввожу функцию и... Результат будет 2. Явно что-то не так. Верно, ведь при такой записи Excel произведет сложение ячейки А1 и 1. Все дело в том, что напрямую Excel не понимает, что необходимо производить подобные операции с массивами(т.е. обрабатывать поочередно каждую ячейку). Ему надо явно указать, что мы хотим это сделать и результат при этом вернуть в одну ячейку. Чтобы ему указать это, надо при вводе функции нажать не Enter, как мы привыкли, а целую комбинацию клавиш - Ctrl+Shift+Enter. Т.е. записали функцию в ячейку и не жмем Enter, чтобы завершить ввод, а жмем Ctrl+Shift+Enter. Функция при таком вводе будет заключена в фигурные скобки - {=СУММ(A1:A5+1)}. Если Вы после ввода их увидели - значит все сделано верно и формула массива введена правильно. И результат будет 20.

Важно: не надо пытаться ввести фигурные скобки вручную с клавиатуры -результатом будет лишь текст в ячейке {=СУММ(A1:A5+1)} и ни о какой формуле и суммировании речи быть уже не может.

Что же происходит внутри функции в этот момент? Все очень просто. Мы в ячейки А1:А5 ввели поочередно цифры от 1 до 5. Т.е. получили: 1, 2, 3, 4, 5. В сумме они дают 15. Я разложу на слагаемые: =СУММ(1;2;3;4;5). Теперь мы изменили функцию и ввели её как формулу массива: {=СУММ(A1:A5+1)}. И внутри происходит сначала прибавление к каждому числу 1, а затем сложение уже измененных аргументов: =СУММ(1+1;2+1;3+1;4+1;5+1) =СУММ(2;3;4;5;6)


Рассмотрим еще один пример, когда формула массива может решить задачу непосильную стандартной формуле(да еще и в одной ячейке без доп.столбцов). Необходимо получить минимальное значение из массива чисел: 0;1;5;5;9;0;6;2;6;3
Применив обычную формулу =МИН(A1:A10) мы получим нуль. Что будет верным. Но если нам как раз нуль учитывать не надо? Мы можем ввести такую формулу:
=МИН(ЕСЛИ(A1:A10<>0;A1:A10))
=MIN(IF(A1:A10<>0,A1:A10))

Казалось бы условие задано верно и мы должны получить нужный результат, т.е. 1. Но! Т.к. это простая формула, она обрабатывает не массив значений, а только первое значение массива(A1:A10) из условия ЕСЛИ, т.е. только А1. Сама по себе функция ЕСЛИ не станет работать с массивом значений в данном случае. Это означает, что формула не просматривает весь заданный массив. Но если ввести её как формулу массива
{=МИН(ЕСЛИ(A1:A10<>0;A1:A10))}
то в таком случае формула последовательно просмотрит каждое значение из массива на предмет выполнения условия и выполнит необходимые вычисления, заданные в этой формуле, так как будто бы мы последовательно для каждой строки в отдельном столбце вывели результат выполнения заданного условия ЕСЛИ и уже по этим результатам определили минимальное значение. И результат формулы будет - 1.


Так же формула массива может вернуть несколько значений. Очень наглядно это демонстрирует функция ТРАНСП(TRANSPOSE). Функция преобразовывает вертикальный массив в горизонтальный и наоборот. Массив может быть многомерным. Как работает функция(на примере исходного диапазона A1:C10):

  • выделяем диапазон ячеек(D1:M3), равный по количеству ячеек исходному диапазону значений(A1:C10), которые необходимо транспонировать
  • вписываем функцию ТРАНСП(TRANSPOSE)
  • в качестве аргумента указываем ссылку на исходный диапазон значений: =ТРАНСП(A1:C10)
  • завершаем ввод функции сочетанием клавиш Ctrl+Shift+Enter.

В диапазоне D1:M3 получим транспонированную таблицу. При написании функции следует учитывать, что число строк в диапазоне функции(D1:M3) должно быть равно числу столбцов в исходном диапазоне(A1:C10), а число столбцов - числу строк. Если указать меньше - не все значения будут транспонированы. Если больше - то все лишние ячейки будут заполнены значениями #Н/Д(#N/A).

Какие особенности подобного применения функций массива:

  • во всех ячейках формула отображается совершенно одинаково, даже если ссылки на ячейки относительные. Это не должно вас пугать - так надо;
  • ячейки диапазона, в который подобным образом введена формула массива нельзя изменять по отдельности - только все вместе. В противном случае просто получите сообщение "Нельзя изменять часть массива!". Бывает очень удобно иногда в целях защиты формул от изменений.

Итак, что самое важное надо запомнить для использования формул массива:

  • ввод формулы завершается сочетанием клавиш Ctrl+Shift+Enter
  • Если формулу массива записать сразу в несколько ячеек, то формула будет одна для всех ячеек и вернет для каждой ячейки свой результат

Важно: начиная с Excel 2021 и в Excel 365 появились динамические массивы. С их появлением выделять сразу несколько ячеек для ввода формулы массива не обязательно - они сами определяют будущий размер области результата. Так же динамические массивы не требует ввода тремя клавишами Ctrl+Shift+Enter

Loading

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

  1. Огромнейшее спасибо автору за подробное и доступное объяснение формулы массива. Теперь с этой формулой спектр решаемых мной задач заметно расширился.

  2. Как перетащить формулу массива на другие строки?
    Формула у меня такая, но соответственно в фигурных скобках.

    =СУММ((отчеты!H5:H5555="гонорары")*(отчеты!F5:F5555))
    При перетаскивании приходится менять слово "гонорары"на другие статьи затрат сметы, это понятно, но почему меняется диапозон ячеек? Получается при копировании так:
    =СУММ((отчеты!H6:H5556="прочие_расходы")*(отчеты!F6:F5556))

  3. Формула смещается из-за указания относительных ссылок на ячейки.

    Необходимо закрепить суммируемый диапазон. Попробуйте так:
    =СУММ((отчеты!H$5:H$5555="гонорары")*(отчеты!F$5:F$5555)).

    А в целом в данном случае кажется более удобным использование СУММЕСЛИ.

  4. Есть такой вопросик.. Мне надо, чтобы при протягивании формулы сумм диапазон суммируемых ячеек уменьшался на одну, например, я указала сумму рядом стоящих 10-ти ячеек, протягиваю формулу вниз, а она суммирует уже первых 9, потом 8 и так далее... Мне почему-то кажется, что мне поможет формула массива. Но я не знаю, как её применить. Помогите, пожалуйста! =)

  5. Ольга, формула массива ничем не поможет. А вот знание того, что такое относительные и абсолютные ссылки на ячейки - очень даже. Вы используете относительные, а нужны абсолютные. Можно заменить в нужных формулах ссылки относительные на абсолютные: Как поменять в формулах относительные ссылки на абсолютные и наоборот?

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

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.