Функции МАКСЕСЛИ / MAXIFS, МИНЕСЛИ / MINIFS, НАИБОЛЬШИЙ / LARGE, НАИМЕНЬШИЙ / SMALL и ОБЪЕДИНИТЬ / TEXTJOIN в Excel: поиск экстремумов и объединение данных
Освоение функций для анализа данных, таких как МАКСЕСЛИ / MAXIFS и ОБЪЕДИНИТЬ / TEXTJOIN, открывает новые возможности для эффективной работы в Excel, позволяя находить экстремальные значения по условиям и гибко объединять текстовые данные.
МАКСЕСЛИ, МИНЕСЛИ и ОБЪЕДИНИТЬ доступны в Microsoft 365 и Excel 2019/2021/2024. ФИЛЬТР и ПОСЛЕДОВ требуют Microsoft 365 или Excel 2021/2024; НАИБОЛЬШИЙ и НАИМЕНЬШИЙ доступны и в Excel 2016. Поддержка языка формул и разделители зависят от локализации.
Эти инструменты, особенно в связке с динамическими массивами, значительно упрощают решение сложных аналитических задач без необходимости написания громоздких формул.
Поиск максимальных и минимальных значений по критериям
Функция МАКСЕСЛИ / MAXIFS, доступная в Microsoft 365 и Excel 2019 и новее, находит наибольшее значение в диапазоне, соответствующее одному или нескольким заданным условиям. Аналогично работает МИНЕСЛИ / MINIFS для поиска наименьшего значения.
- Для базового поиска максимума используйте Русская версия:
=МАКС(диапазон)English version:=MAX(диапазон). - Чтобы найти максимум с критерием (например, для товара "Виджет"), примените Русская версия:
=МАКСЕСЛИ(диапазон_значений; диапазон_критериев1; критерий1; ...)English version:=MAXIFS(диапазон_значений, диапазон_критериев1, критерий1, ...). - Например,
=МАКСЕСЛИ(D13:D126;B13:B126;"Виджет")/=MAXIFS(D13:D126,B13:B126,"Виджет"). Диапазоны должны иметь одинаковую форму и размер; условия объединяются по И. Если совпадений нет, результат равен 0.
Функции НАИБОЛЬШИЙ / LARGE и НАИМЕНЬШИЙ / SMALL
Если нужны не просто максимум и минимум, а, например, второе или третье по величине значение, на помощь приходят НАИБОЛЬШИЙ / LARGE и НАИМЕНЬШИЙ / SMALL.
- Русская версия:
=НАИБОЛЬШИЙ(диапазон; k)English version:=LARGE(диапазон, k)возвращает k-е наибольшее значение. - Русская версия:
=НАИМЕНЬШИЙ(диапазон; k)English version:=SMALL(диапазон, k)возвращает k-е наименьшее значение.
Суммирование топ-N значений по критериям с динамическими массивами
С появлением динамических массивов сложные задачи, такие как суммирование семи наибольших значений, удовлетворяющих условиям, решаются элегантнее.
Для старых версий Excel можно использовать формулу массива с Ctrl+Shift+Enter (вводите без фигурных скобок):
=СУММ(АГРЕГАТ(14;6;D13:D126/((B13:B126=B2)*(C13:C126=C2));СТРОКА(ДВССЫЛ("1:"&D2))))
Здесь D13:D126 должны содержать числа без ошибок. Умножение значений на маску подменяло неподходящие отрицательные числа нулями; деление и опция 6 АГРЕГАТ исключают несовпадения через ошибки деления на ноль. D2 — положительное целое число N, не превышающее количество совпадений. В современном Excel формула ниже вводится обычным Enter:
=СУММ(НАИБОЛЬШИЙ(ФИЛЬТР(D13:D126; (B13:B126=B2)*(C13:C126=C2)); ПОСЛЕДОВ(D2)))=SUM(LARGE(FILTER(D13:D126, (B13:B126=B2)*(C13:C126=C2)), SEQUENCE(D2)))Объединение диапазона с помощью ОБЪЕДИНИТЬ / TEXTJOIN
Функция ОБЪЕДИНИТЬ / TEXTJOIN — мощный инструмент для конкатенации текста с заданным разделителем и возможностью игнорировать пустые ячейки.
- Базовый синтаксис: Русская версия:
=ОБЪЕДИНИТЬ("разделитель"; пропуск_пустых; диапазон)English version:=TEXTJOIN(delimiter,ignore_empty,text1). - Например, Русская версия:
=ОБЪЕДИНИТЬ(", "; ИСТИНА; A1:A10)English version:=TEXTJOIN(", ", TRUE, A1:A10)объединит имена через запятую и пробел, пропуская пустые ячейки.
Совет: ОБЪЕДИНИТЬ / TEXTJOIN отлично работает с массивами. Можно создать условный список, объединив только те элементы, которые соответствуют критерию.
Старая формула массива (CSE) для условия:
=ОБЪЕДИНИТЬ(", "; ИСТИНА; ЕСЛИ(B10:B18="Да"; A10:A18; ""))=TEXTJOIN(", ", TRUE, IF(B10:B18="Да", A10:A18, ""))Современная альтернатива с ФИЛЬТР / FILTER (не требует CSE). В данных ниже ответ записан как «Да» в обеих языковых версиях; для исходного Yes на иллюстрации замените текст критерия на Yes. Последний аргумент ФИЛЬТР задаёт пустую строку при отсутствии совпадений:
=ОБЪЕДИНИТЬ(", "; ИСТИНА; ФИЛЬТР(A10:A18; B10:B18="Да"; ""))=TEXTJOIN(", ", TRUE, FILTER(A10:A18, B10:B18="Да", ""))НАИБОЛЬШИЙ и НАИМЕНЬШИЙ учитывают повторы: для 10, 10, 5 второе наибольшее равно 10. N или k больше числа подходящих чисел вызывает #ЧИСЛ! / #NUM!, а ФИЛЬТР без аргумента пустого результата при отсутствии совпадений — #ВЫЧИС! / #CALC!. ОБЪЕДИНИТЬ возвращает #ЗНАЧ! / #VALUE!, если результат длиннее 32767 символов.
Заключение
Комбинация функций для условного поиска (МАКСЕСЛИ / MAXIFS, МИНЕСЛИ / MINIFS), работы с порядковыми величинами (НАИБОЛЬШИЙ / LARGE, НАИМЕНЬШИЙ / SMALL) и текстовой обработки (ОБЪЕДИНИТЬ / TEXTJOIN) представляет собой мощный арсенал для анализа данных в Excel. Их использование в связке с динамическими массивами делает формулы более читаемыми, гибкими и эффективными, избавляя от необходимости применять устаревшие массивы CSE.