Что такое формула массива
Если Вы довольно много работаете в Excel то наверняка уже слышали выражение "формула массива". Так же его часто можно встретить на форумах и сайтах, посвященных Excel. Но не все знают что это такое и тем более, как пользоваться. Главное это не путать формулы массива с функциями для работы с массивами и функциями для работы с базами данных. Итак, не буду затягивать.
Большинство уже наверняка знакомо с функцией СУММ
Важно: не надо пытаться ввести фигурные скобки вручную с клавиатуры -результатом будет лишь текст в ячейке
{=СУММ(A1:A5+1)} и ни о какой формуле и суммировании речи быть уже не может.
Что же происходит внутри функции в этот момент? Все очень просто. Мы в ячейки А1:А5 ввели поочередно цифры от 1 до 5. Т.е. получили: 1, 2, 3, 4, 5. В сумме они дают 15. Я разложу на слагаемые:
Рассмотрим еще один пример, когда формула массива может решить задачу непосильную стандартной формуле(да еще и в одной ячейке без доп.столбцов). Необходимо получить минимальное значение из массива чисел: 0;1;5;5;9;0;6;2;6;3
Применив обычную формулу
Так же формула массива может вернуть несколько значений. Очень наглядно это демонстрирует функция ТРАНСП
- выделяете диапазон ячеек(D1:M3), равный по количеству ячеек исходному диапазону значений(A1:C10), которые необходимо транспонировать;
- вписываете функцию ТРАНСП;
- в качестве аргумента указываете ссылку на исходный диапазон значений:
=ТРАНСП(A1:C10) ; - завершаете ввод функции сочетанием клавиш
Ctrl +Shift +Enter .
В диапазоне D1:M3 получите транспонированную таблицу. При написании функции следует учитывать, что число строк в диапазоне функции(D1:M3) должно быть равно числу столбцов в исходном диапазоне(A1:C10), а число столбцов - числу строк. Если указать меньше - не все значения будут транспонированы. Если больше - то все лишние ячейки будут заполнены значениями #Н/Д
Какие особенности подобного применения функций массива:
- во всех ячейках формула отображается совершенно одинаково, даже если ссылки на ячейки относительные. Это не должно вас пугать - так надо;
- ячейки диапазона, в который подобным образом введена формула массива нельзя изменять по отдельности - только все вместе. В противном случае просто получите сообщение "Нельзя изменять часть массива!". Бывает очень удобно иногда в целях защиты формул от изменений.
Итак, что самое важное надо запомнить для использования формул массива:
- ввод формулы завершается сочетанием клавиш
Ctrl +Shift +Enter - Если формулу массива записать сразу в несколько ячеек, то формула будет одна для всех ячеек и вернет для каждой ячейки свой результат
Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Огромнейшее спасибо автору за подробное и доступное объяснение формулы массива. Теперь с этой формулой спектр решаемых мной задач заметно расширился.
Хочу сказать ОГРОМНОЕ спасибо автору. Всё расписано очень подробно и сразу все стало понятно)
Спасибо. Понятно, доступно. Все проделала. Получилось.
Спасибо большое за помощь-очень доступно объяснили!
Как перетащить формулу массива на другие строки?
Формула у меня такая, но соответственно в фигурных скобках.
=СУММ((отчеты!H5:H5555="гонорары")*(отчеты!F5:F5555))
При перетаскивании приходится менять слово "гонорары"на другие статьи затрат сметы, это понятно, но почему меняется диапозон ячеек? Получается при копировании так:
=СУММ((отчеты!H6:H5556="прочие_расходы")*(отчеты!F6:F5556))
Формула смещается из-за указания относительных ссылок на ячейки.
Необходимо закрепить суммируемый диапазон. Попробуйте так:
=СУММ((отчеты!H$5:H$5555="гонорары")*(отчеты!F$5:F$5555)).
А в целом в данном случае кажется более удобным использование СУММЕСЛИ.
Есть такой вопросик.. Мне надо, чтобы при протягивании формулы сумм диапазон суммируемых ячеек уменьшался на одну, например, я указала сумму рядом стоящих 10-ти ячеек, протягиваю формулу вниз, а она суммирует уже первых 9, потом 8 и так далее... Мне почему-то кажется, что мне поможет формула массива. Но я не знаю, как её применить. Помогите, пожалуйста! =)
Ольга, формула массива ничем не поможет. А вот знание того, что такое относительные и абсолютные ссылки на ячейки - очень даже. Вы используете относительные, а нужны абсолютные. Можно заменить в нужных формулах ссылки относительные на абсолютные:Как поменять в формулах относительные ссылки на абсолютные и наоборот?
Доходчивое объяснение, позволило посчитать среднее значение без учета нулевых ячеек. Огромное спасибо автору!
Большое спасибо. Очень полезная информация.