Если Вы довольно много работаете в Excel то наверняка уже слышали выражение "формула массива". Так же его часто можно встретить на форумах и сайтах, посвященных Excel. Но не все знают что это такое и тем более, как пользоваться. Главное это не путать формулы массива с функциями для работы с массивами и функциями для работы с базами данных. Итак, не буду затягивать.
Большинство уже наверняка знакомо с функцией
Важно: не надо пытаться ввести фигурные скобки вручную с клавиатуры -результатом будет лишь текст в ячейке{=СУММ(A1:A5+1)} и ни о какой формуле и суммировании речи быть уже не может.
Что же происходит внутри функции в этот момент? Все очень просто. Мы в ячейки
Рассмотрим еще один пример, когда формула массива может решить задачу непосильную стандартной формуле(да еще и в одной ячейке без доп.столбцов). Необходимо получить минимальное значение из массива чисел: 0;1;5;5;9;0;6;2;6;3
Применив обычную формулу
Казалось бы условие задано верно и мы должны получить нужный результат, т.е. 1. Но! Т.к. это простая формула, она обрабатывает не массив значений, а только первое значение массива(
то в таком случае формула последовательно просмотрит каждое значение из массива на предмет выполнения условия и выполнит необходимые вычисления, заданные в этой формуле, так как будто бы мы последовательно для каждой строки в отдельном столбце вывели результат выполнения заданного условия ЕСЛИ и уже по этим результатам определили минимальное значение. И результат формулы будет - 1.
Так же формула массива может вернуть несколько значений. Очень наглядно это демонстрирует функция
- выделяем диапазон ячеек(
), равный по количеству ячеек исходному диапазону значений(D1:M3 ), которые необходимо транспонироватьA1:C10 - вписываем функцию
ТРАНСП (TRANSPOSE) - в качестве аргумента указываем ссылку на исходный диапазон значений:
=ТРАНСП( A1:C10 ) - завершаем ввод функции сочетанием клавиш
Ctrl +Shift +Enter .
В диапазоне
Какие
- во всех ячейках формула отображается совершенно одинаково, даже если ссылки на ячейки относительные. Это не должно вас пугать - так надо;
- ячейки диапазона, в который подобным образом введена формула массива нельзя изменять по отдельности - только все вместе. В противном случае просто получите сообщение "Нельзя изменять часть массива!". Бывает очень удобно иногда в целях защиты формул от изменений.
Итак, что самое важное надо запомнить для использования формул массива:
- ввод формулы завершается сочетанием клавиш
Ctrl +Shift +Enter - Если формулу массива записать сразу в несколько ячеек, то формула будет одна для всех ячеек и вернет для каждой ячейки свой результат
начиная с Excel 2021 и в Excel 365 появились динамические массивы. С их появлением выделять сразу несколько ячеек для ввода формулы массива не обязательно - они сами определяют будущий размер области результата. Так же динамические массивы не требует ввода тремя клавишами Важно: Ctrl +Shift +Enter
Огромнейшее спасибо автору за подробное и доступное объяснение формулы массива. Теперь с этой формулой спектр решаемых мной задач заметно расширился.
Хочу сказать ОГРОМНОЕ спасибо автору. Всё расписано очень подробно и сразу все стало понятно)
Спасибо. Понятно, доступно. Все проделала. Получилось.
Спасибо большое за помощь-очень доступно объяснили!
Как перетащить формулу массива на другие строки?
Формула у меня такая, но соответственно в фигурных скобках.
=СУММ((отчеты!H5:H5555="гонорары")*(отчеты!F5:F5555))
При перетаскивании приходится менять слово "гонорары"на другие статьи затрат сметы, это понятно, но почему меняется диапозон ячеек? Получается при копировании так:
=СУММ((отчеты!H6:H5556="прочие_расходы")*(отчеты!F6:F5556))
Формула смещается из-за указания относительных ссылок на ячейки.
Необходимо закрепить суммируемый диапазон. Попробуйте так:
=СУММ((отчеты!H$5:H$5555="гонорары")*(отчеты!F$5:F$5555)).
А в целом в данном случае кажется более удобным использование СУММЕСЛИ.
Есть такой вопросик.. Мне надо, чтобы при протягивании формулы сумм диапазон суммируемых ячеек уменьшался на одну, например, я указала сумму рядом стоящих 10-ти ячеек, протягиваю формулу вниз, а она суммирует уже первых 9, потом 8 и так далее... Мне почему-то кажется, что мне поможет формула массива. Но я не знаю, как её применить. Помогите, пожалуйста! =)
Ольга, формула массива ничем не поможет. А вот знание того, что такое относительные и абсолютные ссылки на ячейки - очень даже. Вы используете относительные, а нужны абсолютные. Можно заменить в нужных формулах ссылки относительные на абсолютные:Как поменять в формулах относительные ссылки на абсолютные и наоборот?
Доходчивое объяснение, позволило посчитать среднее значение без учета нулевых ячеек. Огромное спасибо автору!
Большое спасибо. Очень полезная информация.