Суммирование наименьших или нижних N значений в Excel

В Excel легко суммировать диапазон ячеек с помощью функции СУММ (SUM). Однако иногда возникает необходимость сложить наименьшие — например, 3, 5 или n — чисел в диапазоне данных, как показано на снимке экрана ниже. В этом случае на помощь придут функции СУММПРОИЗВ (SUMPRODUCT) и НАИМЕНЬШИЙ (SMALL), которые эффективно решат эту задачу в Excel.

Суммирование наименьших или нижних N значений с помощью функций СУММПРОИЗВ и НАИМЕНЬШИЙ

Чтобы просуммировать наименьшие n чисел в диапазоне данных, сначала определите эти наименьшие n значений с помощью функции НАИМЕНЬШИЙ (SMALL), а затем используйте функцию СУММПРОИЗВ (SUMPRODUCT) для прямого суммирования полученного массива. Общий синтаксис формулы:

• range: диапазон ячеек, содержащий числа для суммирования;
• N: обозначает N-е наименьшее значение.

Здесь я покажу, как просуммировать 4 наименьших числа в диапазоне B2:B12. Введите или скопируйте приведённую ниже формулу в пустую ячейку и нажмите клавишу Enter, чтобы получить результат:

Пояснение формулы:

=SUMPRODUCT(SMALL(B2:B12,{1,2,3,4}))

• SMALL(B2:B12,{1,2,3,4}): эта функция НАИМЕНЬШИЙ (SMALL) возвращает 1-е, 2-е, 3-е и 4-е наименьшие значения из диапазона B2:B12, формируя массив вида: {200,230,345,560}.
• SUMPRODUCT(SMALL(B2:B12,{1,2,3,4}))=SUMPRODUCT({200,230,345,560}): функция СУММПРОИЗВ (SUMPRODUCT) суммирует все числа в массиве и возвращает результат — 1335.

Совет: решение для случаев, когда N велико:

Иногда у вас может быть длинный список чисел, и суммирование наименьших 50 или даже большего количества значений в диапазоне становится утомительным — ведь приходится вручную перечислять числа 1, 2, 3, 4…50 в функции НАИМЕНЬШИЙ (SMALL). В таких случаях вы можете использовать функции СТРОКА (ROW) и ДВССЫЛ (INDIRECT), чтобы автоматически сформировать константу массива.

Например, чтобы суммировать наименьшие 10 значений в диапазоне, используйте приведённые ниже формулы:

Ручной ввод числа:

Используйте Ссылка на ячейку:

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

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