Обща формула
=SUMPRODUCT(SMALL(rng,(1,2,n)))
Обобщение
За да сумирате най-ниските n стойности в диапазон, можете да използвате формула, базирана на функцията SMALL и функцията SUMPRODUCT. В общата форма на формулата (по-горе) 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.
Работейки отвътре навън в показания пример, SMALL връща 3-те най-малки стойности в диапазона B5: B14:
=SMALL(B4:B14,(1,2,3))
Резултатът е масив като този:
(10,20,30)
Този масив се връща директно към функцията SUMPRODUCT, която сумира числата и връща общото:
SUMPRODUCT((10,20,30)) // returns 60
Формула на масив със SUM
Често се използва SUMPRODUCT, както по-горе, тъй като той може да обработва масиви първоначално, без да влиза като формула на масив. Можете обаче да напишете и формула на масив с функцията SUM по следния начин:
(=SUM(SMALL(B4:B13,(1,2,3))))
Това е формула на масив и трябва да се въведе с control + shift + enter, освен в Excel 365.
Когато n стане голям
Когато n стане голям, става досадно да се създава константата на масива на ръка - въвеждането на константа на масив с 20 или 30 елемента ще отнеме много време. В този случай можете да използвате пряк път за изграждане на константата на масива, която използва функциите ROW и INDIRECT. Например, за да СЪОБРАЖИТЕ долните 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 директно на работния лист.
