Функции МАКСЕСЛИ / MAXIFS, МИНЕСЛИ / MINIFS, НАИБОЛЬШИЙ / LARGE, НАИМЕНЬШИЙ / SMALL и ОБЪЕДИНИТЬ / TEXTJOIN в Excel: поиск экстремумов и объединение данных
Освоение функций для анализа данных, таких как МАКСЕСЛИ / MAXIFS и ОБЪЕДИНИТЬ / TEXTJOIN, открывает новые возможности для эффективной работы в Excel, позволяя находить экстремальные значения по условиям и гибко объединять текстовые данные.
Статья подойдет тем, кто изучает Python в Excel, Copilot и новые AI-возможности Excel для анализа данных и автоматизации.
Эти инструменты, особенно в связке с динамическими массивами, значительно упрощают решение сложных аналитических задач без необходимости написания громоздких формул.
Поиск максимальных и минимальных значений по критериям
Функция МАКСЕСЛИ / MAXIFS, представленная в Microsoft 365, находит наибольшее значение в диапазоне, соответствующее одному или нескольким заданным условиям. Аналогично работает МИНЕСЛИ / MINIFS для поиска наименьшего значения.
- Для базового поиска максимума используйте Русская версия:
=МАКС(диапазон)English version:=MAX(диапазон). - Чтобы найти максимум с критерием (например, для товара "Виджет"), примените Русская версия:
=МАКСЕСЛИ(диапазон_значений; диапазон_критериев1; критерий1; ...)English version:=MAXIFS(диапазон_значений, диапазон_критериев1, критерий1, ...). - Критерий можно указать прямо в формуле или сослаться на ячейку.
Функции НАИБОЛЬШИЙ / LARGE и НАИМЕНЬШИЙ / SMALL
Если нужны не просто максимум и минимум, а, например, второе или третье по величине значение, на помощь приходят НАИБОЛЬШИЙ / LARGE и НАИМЕНЬШИЙ / SMALL.
- Русская версия:
=НАИБОЛЬШИЙ(диапазон; k)English version:=LARGE(диапазон, k)возвращает k-е наибольшее значение. - Русская версия:
=НАИМЕНЬШИЙ(диапазон; k)English version:=SMALL(диапазон, k)возвращает k-е наименьшее значение.
Суммирование топ-Ч / N значений по критериям с динамическими массивами
С появлением динамических массивов сложные задачи, такие как суммирование семи наибольших значений, удовлетворяющих условиям, решаются элегантнее.
Старая формула требовала ввода с Ctrl + Shift + Enter (CSE):
{=СУММ(АГРЕГАТ(14,4,($B$13:$B$126=$B$2)*($C$13:$C$126=$C$2)*($D$13:$D$126), СТРОКА(ДВССЫЛ("1:"&D2))))}Новая формула с динамическими массивами проще и не требует CSE:
=СУММ(НАИБОЛЬШИЙ(ФИЛЬТР(D13:D126; (B13:B126=B2)*(C13:C126=C2)); ПОСЛЕДОВ(D2)))=SUM(LARGE(FILTER(D13:D126, (B13:B126=B2)*(C13:C126=C2)), SEQUENCE(D2)))Объединение диапазона с помощью ОБЪЕДИНИТЬ / TEXTJOIN
Функция ОБЪЕДИНИТЬ / TEXTJOIN — мощный инструмент для конкатенации текста с заданным разделителем и возможностью игнорировать пустые ячейки.
- Базовый синтаксис: Русская версия:
=ОБЪЕДИНИТЬ("разделитель"; ИГНОРИРОВАТЬ_ПУСTЫF; диапазон)English version:=TEXTJOIN("разделитель", ИГНОРИРОВАТЬ_ПУСTЫF, диапазон). - Например, Русская версия:
=ОБЪЕДИНИТЬ(", "; ИСТИНА; A1:A10)English version:=TEXTJOIN(", ", TRUE, A1:A10)объединит имена через запятую и пробел, пропуская пустые ячейки.
Совет: ОБЪЕДИНИТЬ / TEXTJOIN отлично работает с массивами. Можно создать условный список, объединив только те элементы, которые соответствуют критерию.
Старая формула массива (CSE) для условия:
=ОБЪЕДИНИТЬ(", "; ИСТИНА; ЕСЛИ(B10:B18="Да"; A10:A18; ""))=TEXTJOIN(", ", TRUE, IF(B10:B18="Да", A10:A18, ""))Современная альтернатива с ФИЛЬТР / FILTER (не требует CSE):
=ОБЪЕДИНИТЬ(", "; ИСТИНА; ФИЛЬТР(A10:A18; B10:B18="Да"))=TEXTJOIN(", ", TRUE, FILTER(A10:A18, B10:B18="Да"))Заключение
Комбинация функций для условного поиска (МАКСЕСЛИ / MAXIFS, МИНЕСЛИ / MINIFS), работы с порядковыми величинами (НАИБОЛЬШИЙ / LARGE, НАИМЕНЬШИЙ / SMALL) и текстовой обработки (ОБЪЕДИНИТЬ / TEXTJOIN) представляет собой мощный арсенал для анализа данных в Excel. Их использование в связке с динамическими массивами делает формулы более читаемыми, гибкими и эффективными, избавляя от необходимости применять устаревшие массивы CSE.