Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Формулы и функции

Функции MAXIFS, MINIFS, LARGE, SMALL и TEXTJOIN в Excel: поиск экстремумов и объединение данных

forest_paw 17.02.2026 745

Функции МАКСЕСЛИ / 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 для поиска наименьшего значения.

  1. Для базового поиска максимума используйте Русская версия: =МАКС(диапазон)English version: =MAX(диапазон).
  2. Чтобы найти максимум с критерием (например, для товара "Виджет"), примените Русская версия: =МАКСЕСЛИ(диапазон_значений; диапазон_критериев1; критерий1; ...)English version: =MAXIFS(диапазон_значений, диапазон_критериев1, критерий1, ...).
  3. Например, =МАКСЕСЛИ(D13:D126;B13:B126;"Виджет") / =MAXIFS(D13:D126,B13:B126,"Виджет"). Диапазоны должны иметь одинаковую форму и размер; условия объединяются по И. Если совпадений нет, результат равен 0.
Примеры формул для поиска наибольшего (MAX), наименьшего (MIN), второго наибольшего (LARGE) и второго наименьшего (SMALL) значений, а также использования MAXIFS с критериями.
Примеры формул для поиска наибольшего (МАКС / MAX), наименьшего (МИН / MIN), второго наибольшего (НАИБОЛЬШИЙ / LARGE) и второго наименьшего (НАИМЕНЬШИЙ / SMALL) значений, а также использования МАКСЕСЛИ / MAXIFS с критериями.

Функции НАИБОЛЬШИЙ / LARGE и НАИМЕНЬШИЙ / SMALL

Если нужны не просто максимум и минимум, а, например, второе или третье по величине значение, на помощь приходят НАИБОЛЬШИЙ / LARGE и НАИМЕНЬШИЙ / SMALL.

  • Русская версия: =НАИБОЛЬШИЙ(диапазон; k)English version: =LARGE(диапазон, k) возвращает k-е наибольшее значение.
  • Русская версия: =НАИМЕНЬШИЙ(диапазон; k)English version: =SMALL(диапазон, k) возвращает k-е наименьшее значение.

Суммирование топ-N значений по критериям с динамическими массивами

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

Сравнение старой формулы массива (CSE) и новой с использованием FILTER, LARGE и SEQUENCE для суммирования топ-N значений по критериям.
Сравнение старой формулы массива (CSE) и новой с использованием ФИЛЬТР / FILTER, НАИБОЛЬШИЙ / LARGE и ПОСЛЕДОВ / SEQUENCE для суммирования топ-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)))
English version:
=SUM(LARGE(FILTER(D13:D126, (B13:B126=B2)*(C13:C126=C2)), SEQUENCE(D2)))

Объединение диапазона с помощью ОБЪЕДИНИТЬ / TEXTJOIN

Функция ОБЪЕДИНИТЬ / TEXTJOIN — мощный инструмент для конкатенации текста с заданным разделителем и возможностью игнорировать пустые ячейки.

  1. Базовый синтаксис: Русская версия: =ОБЪЕДИНИТЬ("разделитель"; пропуск_пустых; диапазон)English version: =TEXTJOIN(delimiter,ignore_empty,text1).
  2. Например, Русская версия: =ОБЪЕДИНИТЬ(", "; ИСТИНА; A1:A10)English version: =TEXTJOIN(", ", TRUE, A1:A10) объединит имена через запятую и пробел, пропуская пустые ячейки.

Совет: ОБЪЕДИНИТЬ / TEXTJOIN отлично работает с массивами. Можно создать условный список, объединив только те элементы, которые соответствуют критерию.

Примеры использования TEXTJOIN для простого объединения списка и для объединения с условием (только те, кто ответил 'Yes').
Примеры использования ОБЪЕДИНИТЬ / TEXTJOIN для простого объединения списка и для объединения с условием (только те, кто ответил 'Yes').

Старая формула массива (CSE) для условия:

Русская версия:
=ОБЪЕДИНИТЬ(", "; ИСТИНА; ЕСЛИ(B10:B18="Да"; A10:A18; ""))
English version:
=TEXTJOIN(", ", TRUE, IF(B10:B18="Да", A10:A18, ""))

Современная альтернатива с ФИЛЬТР / FILTER (не требует CSE). В данных ниже ответ записан как «Да» в обеих языковых версиях; для исходного Yes на иллюстрации замените текст критерия на Yes. Последний аргумент ФИЛЬТР задаёт пустую строку при отсутствии совпадений:

Русская версия:
=ОБЪЕДИНИТЬ(", "; ИСТИНА; ФИЛЬТР(A10:A18; B10:B18="Да"; ""))
English version:
=TEXTJOIN(", ", TRUE, FILTER(A10:A18, B10:B18="Да", ""))

НАИБОЛЬШИЙ и НАИМЕНЬШИЙ учитывают повторы: для 10, 10, 5 второе наибольшее равно 10. N или k больше числа подходящих чисел вызывает #ЧИСЛ! / #NUM!, а ФИЛЬТР без аргумента пустого результата при отсутствии совпадений — #ВЫЧИС! / #CALC!. ОБЪЕДИНИТЬ возвращает #ЗНАЧ! / #VALUE!, если результат длиннее 32767 символов.

Заключение

Комбинация функций для условного поиска (МАКСЕСЛИ / MAXIFS, МИНЕСЛИ / MINIFS), работы с порядковыми величинами (НАИБОЛЬШИЙ / LARGE, НАИМЕНЬШИЙ / SMALL) и текстовой обработки (ОБЪЕДИНИТЬ / TEXTJOIN) представляет собой мощный арсенал для анализа данных в Excel. Их использование в связке с динамическими массивами делает формулы более читаемыми, гибкими и эффективными, избавляя от необходимости применять устаревшие массивы CSE.

Поделиться:

Мы используем cookies для улучшения работы сайта. Продолжая использовать сайт, вы соглашаетесь с политикой конфиденциальности.