Формулы массива в EXCEL, возвращающие одно значение

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

Т.к. Формулы массива всегда производят расчеты с целым набором значений (массивом), то для вывода только одного значения потребуется функция «свертывающая» этот массив до одного значения. Например, СУММ() , МАКС() , СРЗНАЧ() и т.п.

Приведем примеры (при вводе функций не забывайте нажимать CTRL+SHIFT+ENTER ):

• Нахождение максимальной длины строки текстовых значений, содержащихся в диапазоне B2:B7 (см. статью Максимальное/ минимальное среди повторяющихся значений ):

=МАКС(ДЛСТР(B2:B7))

• Нахождение суммы 3-х наибольших значений из диапазона A2:A13 (см. статью Сумма трех наибольших значений ):

=СУММ(НАИБОЛЬШИЙ(A2:A13;{1;2;3}))

• Нахождение текстового значения, имеющего максимальную длину строки в диапазоне B2:B7 (если несколько значений имеет максимальную длину, то выводится первое сверху. См. статью Поиск значения с максимальной или минимальной длины строки ):

=ИНДЕКС(B2:B7;ПОИСКПОЗ(МАКС(ДЛСТР(B2:B7));ДЛСТР(B2:B7);0);1)

• Нахождение суммы значений, удовлетворяющих определенным критериям (продажи от 6 до 10 руб.). См. статью Сложение чисел с несколькими критериями (Часть 2: Условие И)

=СУММ((B2:B7>=6)*(B2:B7<=10)*(B2:B7))

• Нахождение последнего значения (самой нижней заполненной ячейки) в диапазоне A1:A20 (подробности в статье Последняя заполненная ячейка )

=ДВССЫЛ("A"&МАКС(СТРОКА(A1:A20)*(A1:A20<>"")))

• Нахождение значения ближайшего к среднему (диапазон поиска A1:A6 ):

=ИНДЕКС(A1:A6;ПОИСКПОЗ(МИН(ABS(A1:A6-СРЗНАЧ(A1:A6)));ABS(A1:A6-СРЗНАЧ(A1:A6));0))

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

СОВЕТ: Начальные знания о формулах массива можно прочитать в статье Формулы массива. Знакомство . О формулах массива, возвращающих несколько значений можно прочитать в статье Формулы массива, возвращающие несколько значений. Дополнительный материал ]]> на английском языке о формулах массива доступен на сайте Microsoft ]]> и ]]> здесь (по сложнее) ]]> .