Общая формула
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Резюме
Чтобы суммировать верхние n значений в критерии соответствия диапазона, вы можете использовать формулу, основанную на функции НАИБОЛЬШИЙ, заключенную внутри функции СУММПРОИЗВ. В универсальной форме формулы (см. Выше) диапазон представляет собой диапазон ячеек, которые сравниваются с критериями , значения представляют собой числовые значения, из которых извлекаются верхние значения, а N представляет идею N-го значения.
В примере активная ячейка содержит эту формулу:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Где цвет - это именованный диапазон B5: B12, а значение - именованный диапазон C5: C12.
Объяснение
В своей простейшей форме LARGE возвращает "N-е наибольшее" значение в диапазоне с помощью этой конструкции:
=LARGE (range,N)
Так, например:
=LARGE (C5:C12,2)
вернет 2-е по величине значение в диапазоне C5: C12, которое в показанном примере равно 12.
Однако, если вы предоставите «константу массива» (например, константу в форме (1,2,3)) для LARGE в качестве второго аргумента, LARGE вернет массив результатов вместо одного результата. Итак, формула:
=LARGE (C5:C12, (1,2,3))
вернет 1-е, 2-е и 3-е наибольшее значение C5: C12 в таком массиве: (12,12,10)
Итак, уловка здесь в том, чтобы отфильтровать значения по цвету перед запуском LARGE. Делаем это с помощью выражения:
(color=E5)
В результате получается массив значений ИСТИНА / ЛОЖЬ. Во время операции умножения эти значения переводятся в единицы и нули:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Таким образом, окончательный результат состоит в том, что только значения, связанные с красным цветом, выдерживают операцию:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
а остальные значения обнуляются.
Примечание: эта формула не обрабатывает текст в диапазоне значений. См. ниже.
Обработка текста в значениях
Если у вас есть текст в любом месте диапазона значений, функция НАИБОЛЬШИЙ выдаст ошибку # ЗНАЧЕНИЕ и остановит работу формулы.
Чтобы обрабатывать текст в диапазоне значений, вы можете добавить функцию ЕСЛИОШИБКА следующим образом:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Здесь мы перехватываем ошибки типа LARGE, вызванные текстовыми значениями, и заменяем их на ноль. Использование IF внутри LARGE требует, чтобы формула вводилась с помощью Ctrl + Shift + Enter, поэтому мы переключаемся на SUM вместо SUMPRODUCT.
Примечание: я столкнулся с этой формулой, опубликованной замечательным Барри Гудини в stackoverflow.
