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