Общая формула
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Резюме
Чтобы суммировать нижние n значений в критерии соответствия диапазона, вы можете использовать формулу массива, основанную на функции МАЛЕНЬКИЙ, заключенную внутри функции СУММ. В общей форме формулы (выше), диапазон1 представляет собой диапазон ячеек , по сравнению с критериями , диапазон2 содержит числовые значения , из которых извлекаются нижние значения, и N представляет собой «N - й».
В примере активная ячейка содержит эту формулу:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Где цвет - это именованный диапазон B5: B12, а значение - именованный диапазон C5: C12.
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Объяснение
В простейшей форме SMALL возвращает «N-е наименьшее» значение в диапазоне с помощью этой конструкции:
=SMALL (range,N)
Так, например:
=SMALL (C5:C12,2)
вернет 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(color=E5,value)
Это создает массив значений, передаваемых в SMALL. По сути, в массив попадают только значения, связанные с красным цветом. Если цвет равен «красному», массив содержит число, а если цвет не красный, массив содержит FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
Функция SMALL игнорирует значения FALSE и возвращает 3 наименьших значения в массиве: (4,8,10). Функция СУММ возвращает окончательный результат 22.
