Обща формула
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Обобщение
За да сумирате най-добрите n стойности в критерии за съвпадение на диапазон, можете да използвате формула, базирана на функцията LARGE, увита във функцията SUMPRODUCT. В общата форма на формулата (по-горе) диапазонът представлява диапазон от клетки, които се сравняват с критерии , стойностите представляват числови стойности, от които се извличат най-горните стойности, а N представлява идеята за N-та стойност.
В примера активната клетка съдържа тази формула:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Където цветът е наименуваният диапазон B5: B12, а стойността е наименуваният диапазон C5: C12.
Обяснение
В най-простата си форма LARGE връща "N-тата по големина" стойност в диапазон с тази конструкция:
=LARGE (range,N)
Така например:
=LARGE (C5:C12,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)
Което води до масив от TRUE / FALSE стойности. По време на операцията за умножение тези стойности се принуждават към единици и нули:
=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)))
а останалите стойности са принудени към нула.
Забележка: тази формула няма да обработва текст в диапазона на стойностите. Виж отдолу.
Обработка на текст в стойности
Ако имате текст някъде в диапазоните на стойностите, функцията LARGE ще изведе грешка #VALUE и ще спре формулата да работи.
За да обработвате текст в диапазона на стойностите, можете да добавите функцията IFERROR по следния начин:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Тук улавяме грешки от LARGE, причинени от текстови стойности, и ги заместваме с нула. Използването на IF вътре в LARGE изисква формулата да се въведе с control + shift + enter, така че превключваме към SUM вместо SUMPRODUCT.
Забележка: Натъкнах се на тази формула, публикувана от невероятния Бари Худини в stackoverflow.
