Обща формула
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Обобщение
За да сумирате долните n стойности в критерии за съвпадение на диапазон, можете да използвате формула на масив, базирана на функцията SMALL, увита във функцията SUM. В общата форма на формулата (по-горе), range1 представлява диапазона от клетки в сравнение с критериите , range2 съдържа числови стойности, от които се извличат дънните стойности, а N представлява "n-та".
В примера активната клетка съдържа тази формула:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Където цветът е наименуваният диапазон B5: B12, а стойността е наименуваният диапазон C5: C12.
Забележка: това е формула на масив и трябва да се въведе с control + shift + enter.
Обяснение
В най-простата си форма SMALL връща "N-тата най-малка" стойност в диапазон с тази конструкция:
=SMALL (range,N)
Така например:
=SMALL (C5:C12,2)
ще върне втората най-малка стойност в диапазона C5: C12, което е 5 в показания пример.
Ако обаче предоставите „константа на масива“ (например константа във формата (1,2,3)) на SMALL като втори аргумент, SMALL ще върне масив от резултати вместо единичен резултат. И така, формулата:
=SMALL (C5:C12, (1,2,3))
ще върне 1-ва, 2-ра и 3-та най-малка стойност C5: C12 в масив като този: (4,5,7).
И така, трикът тук е да филтрирате стойностите въз основа на цвета, преди да се изпълни SMALL. Правим това с израз, базиран на функцията IF:
IF(color=E5,value)
Това изгражда масива от стойности, подадени в SMALL. По същество в масива влизат само стойности, свързани с червения цвят. Когато цветът е равен на "червен", масивът съдържа число, а когато цветът не е червен, масивът съдържа FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
Функцията SMALL игнорира FALSE стойностите и връща 3-те най-малки стойности в масива: (4,8,10). Функцията SUM връща крайния резултат, 22.
