KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

Суммирование N наименьших значений по критериям в Excel

АвторСяоянДата изменения

В предыдущем руководстве мы рассмотрели способ суммирования n наименьших значений в диапазоне данных. В этой статье описана более сложная операция — суммирование наименьших n значений по одному или нескольким критериям в Excel.

doc-sum-bottom-n-with-criteria-1

Суммирование N наименьших значений по критериям в Excel

Допустим, у вас есть диапазон данных, как показано на скриншоте ниже, и вы хотите просуммировать три наименьших заказа продукта Apple.

doc-sum-bottom-n-with-criteria-2

В Excel для суммирования n наименьших значений в диапазоне с учётом заданных критериев можно создать формулу массива, объединив функции СУММ, НАИМЕНЬШИЙ и ЕСЛИ. Общий синтаксис:

{=SUM(SMALL(IF(range=criteria,values),{1,2,N}))}
Array formula, should press Ctrl + Shift + Enter keys together.
  • range=criteria: Диапазон ячеек для сопоставления с конкретным критерием;
  • values: Список, содержащий n наименьших значений, которые необходимо просуммировать;
  • N: Позиция (N-го) наименьшего значения.

Чтобы решить указанную выше задачу, введите следующую формулу массива в пустую ячейку:

=SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))

Затем нажмите клавиши Ctrl + Shift + Enterодновременно, чтобы получить правильный результат, как показано на скриншоте ниже:

doc-sum-bottom-n-with-criteria-3

Объяснение формулы:

=SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))

  • IF(($A$2:$A$14=D2), $B$2:$B$14): Если продукт в диапазоне A2:A14 совпадает с «Apple», формула вернёт соответствующее значение из списка заказов (B2:B14); если продукт не «Apple» — будет показано ЛОЖЬ. Результат: {800;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;1000;230;ЛОЖЬ;ЛОЖЬ;1600;ЛОЖЬ;900;ЛОЖЬ;500}.
  • SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}): Эта функция НАИМЕНЬШИЙ игнорирует значения ЛОЖЬ и возвращает три наименьших значения из массива, поэтому результат будет следующим: {230,500,800}.
  • SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))=SUM({230,500,800}): Наконец, функция СУММ складывает числа в массиве и получает результат — 1530.

Совет: работа с двумя или более условиями:

Если необходимо суммировать наименьшие n значений по двум или более критериям, просто добавьте дополнительные диапазоны и критерии через символ * внутри функции ЕСЛИ, как показано ниже:

{=SUM(SMALL(IF((range1=criteria1)*(range2=criteria2) *(range3=criteria3)…,values),{1,2,N}))}
Array formula, should press Ctrl + Shift + Enter keys together.
  • Range1=criteria1: Первый диапазон ячеек для сопоставления с первым критерием;
  • Range2=criteria2: Второй диапазон ячеек для сопоставления со вторым критерием;
  • Range3=criteria3: Третий диапазон ячеек для сопоставления с третьим критерием;
  • values: Список, содержащий n наименьших значений, которые необходимо просуммировать;
  • N: Позиция (N-е) наименьшего значения.

Например, чтобы просуммировать три наименьших заказа продукта Apple, проданных Керри, используйте следующую формулу:

=SUM(SMALL(IF(($A$2:$A$14=E2)*($B$2:$B$14=F2), $C$2:$C$14),{1,2,3}))

Затем нажмите клавиши Ctrl + Shift + Enterодновременно, чтобы получить требуемый результат:

doc-sum-bottom-n-with-criteria-4

Используемые связанные функции:

  • СУММ:
  • Функция СУММ складывает значения — отдельные числа, ссылки на ячейки, диапазоны или любую их комбинацию.
  • НАИМЕНЬШИЙ:
  • Функция НАИМЕНЬШИЙ в Excel возвращает числовое значение в зависимости от его позиции в списке, отсортированном по возрастанию.
  • ЕСЛИ:
  • Функция ЕСЛИ проверяет, выполняется ли заданное условие, и возвращает указанное вами значение для случая ИСТИНА или ЛОЖЬ.

Другие статьи:

  • Суммирование N наименьших или последних значений
  • В Excel легко просуммировать диапазон ячеек с помощью функции СУММ. Однако иногда возникает необходимость сложить наименьшие или последние 3, 5 или n чисел в диапазоне данных — как показано на скриншоте ниже. В этом случае задачу можно эффективно решить с помощью комбинации функций СУММПРОИЗВ и НАИМЕНЬШИЙ.
  • Промежуточные итоги сумм счетов по возрасту в Excel
  • Суммирование сумм счетов по возрасту, как показано на скриншоте ниже, — распространённая задача в Excel. В этом руководстве объясняется, как получить промежуточные итоги сумм счетов по возрасту с помощью стандартной функции СУММЕСЛИ.
  • Суммирование всех числовых ячеек с игнорированием ошибок
  • При суммировании диапазона чисел, содержащего ошибки, стандартная функция СУММ работает некорректно. Чтобы складывать только числовые значения, игнорируя ошибки, используйте функцию АГРЕГАТ или комбинацию функций СУММ и ЕСЛИОШИБКА.

Лучшие инструменты для повышения продуктивности в Office

Kutools для Excel — Помогает Вам выделяться из толпы

🤖KUTOOLS AI Помощник: Революционизируйте Анализ данных на основе:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и создания диаграмм|  Вызова Расширенные функции…
Популярные функции:Поиск, выделение или Отметить дубликаты  |  Удалить пустые строки  |  Объединить столбцы или ячеек без потери данных  |  Округление без использования формул…
Расширенный VLookup:Несколько критериев  |  Несколько значений  |  Между несколькими листами  |  Распознавание нечетких соответствий…
Расш. Раскрывающийся список:Простой выпадающий список  |  Зависимый выпадающий список  |  Выпадающий список с множественным выбором…
Управление столбцами:Добавление определённого количества столбцов  |  Перемещение столбцов  |  Переключение видимости скрытых столбцов  |Сравнение столбцов для Выбрать одинаковые/разные ячейки…
Избранные функции:Сетка фокусировки  |  Просмотр дизайна  |  Улучшенная строка формулы  |  Управление книгами и листами|Библиотека ресурсов(автотекст)|  Выбор даты  |  Объединить листы  |  Шифрование/Расшифровать ячейки  |  Отправка писем по списку  |  Супер фильтр  |  Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы…)|  50+Типыдиаграмм(Диаграмма Ганта…)|  40+ Практические формулы(Рассчитать возраст на основе даты рождения…)|  19 Инструментывставки(Вставить QR-код,Вставка изображения по пути…)|  12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют…)|  7 Объединить и разделитьинструменты(Расширенное объединение строк,Разделение ячеек Excel…)|…и многое другое
Используйте Kutools на предпочитаемом языке — поддерживает английский, испанский, немецкий, французский, китайский и ещё 40+ языков!

Kutools для Excel предлагает более 300 функций,гарантируя, что всё необходимое всегда под рукой…

Office Tab — Включает вкладочное чтение и редактирование в Microsoft Office (включая Excel)

  • Переключайтесь между десятками открытых документов всего за секунду!
  • Сокращает сотни кликов мышью каждый день — забудьте о «мышечной руке» раз и навсегда!
  • Повышает продуктивность на 50 % при одновременном просмотре и редактировании нескольких документов.
  • Добавляет эффективность вкладок в Office (включая Excel) — как в Chrome, Edge и Firefox.