Общая формула
=SUMPRODUCT(SMALL(rng,(1,2,n)))
Резюме
Чтобы суммировать наименьшие n значений в диапазоне, вы можете использовать формулу, основанную на функции МАЛЕНЬКИЙ и функции СУММПРОИЗВ. В универсальной форме формулы (см. Выше) rng представляет собой диапазон ячеек, содержащих числовые значения, а n представляет собой количество наименьших значений для суммирования. В показанном примере E5 содержит эту формулу:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
который возвращает сумму трех наименьших значений в B5: B14, 60
Объяснение
В простейшей форме SMALL вернет "n-е наименьшее" значение в диапазоне. Например:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
Однако, если вы предоставите константу массива (например, константу в форме (1,2,3)) для SMALL в качестве второго аргумента, SMALL вернет массив результатов вместо одного результата. Например:
=SMALL(A1:A10,(1,2,3))
вернет 1-е, 2-е и 3-е наименьшие значения в диапазоне A1: A10.
В показанном примере, работая изнутри наружу, НАИМЕНЬШИЙ возвращает 3 наименьших значения в диапазоне B5: B14:
=SMALL(B4:B14,(1,2,3))
Результатом является такой массив:
(10,20,30)
Этот массив возвращается непосредственно в функцию СУММПРОИЗВ, которая суммирует числа и возвращает итог:
SUMPRODUCT((10,20,30)) // returns 60
Формула массива с СУММ
Обычно используется SUMPRODUCT, как указано выше, потому что он может обрабатывать массивы изначально, без ввода в качестве формулы массива. Однако вы также можете написать формулу массива с функцией СУММ следующим образом:
(=SUM(SMALL(B4:B13,(1,2,3))))
Это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter, кроме Excel 365.
Когда n становится большим
Когда n становится большим, создавать константу массива вручную становится утомительно - ввод константы массива с 20 или 30 элементами займет много времени. В этом случае вы можете использовать ярлык для построения константы массива, которая использует функции СТРОКА и ДВССЫЛ. Например, чтобы СУММИРОВАТЬ нижние 20 значений в диапазоне под названием «rng», вы можете написать такую формулу:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
Здесь INDIRECT преобразует строку «1:20» в диапазон 1:20, который возвращается непосредственно в SMALL.
Переменная n
Чтобы настроить формулу, где n - переменная в другой ячейке, вы можете объединить внутри INDIRECT. Например, если A1 содержит N, вы можете использовать:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
Это позволяет пользователю изменять значение n прямо на листе.
