Обща формула
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Обобщение
За да сумирате най-добрите стойности в диапазон, можете да използвате формула, базирана на функцията LARGE, обвита във функцията SUMPRODUCT. В общата форма на формулата (по-горе) rng представлява диапазон от клетки, които съдържат числови стойности, а N представлява идеята за N-та стойност.
В примера активната клетка съдържа тази формула:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Обяснение
В най-простата си форма LARGE ще върне "N-тата по големина" стойност в диапазон. Например формулата:
=LARGE(B4:B13, 2)
ще върне втората по големина стойност в диапазона B4: B13, което в примера по-горе е числото 9.
Ако обаче предоставите „константа на масива“ (например константа във формата (1,2,3)) на LARGE като втори аргумент, LARGE ще върне масив от резултати вместо единичен резултат. И така, формулата:
=LARGE(B4:B13,(1,2,3))
ще върне 1-ва, 2-ра и 3-та най-голяма стойност в диапазона B4: B13. В горния пример, където B4: B13 съдържа числата 1-10, резултатът от LARGE ще бъде масивът (8,9,10). След това SUMPRODUCT сумира числата в този масив и връща сума, която е 27.
SUM вместо SUMPRODUCT
SUMPRODUCT е гъвкава функция, която ви позволява да използвате препратки на клетки за k във функцията LARGE.
Ако обаче използвате проста кодирана константа на масив като (1,2,3), можете просто да използвате функцията SUM:
=SUM(LARGE(B4:B13,(1,2,3)))
Забележете, че трябва да въведете тази формула като формула на масив, ако използвате препратки към клетки, а не константа на масив за k вътре LARGE.
Когато N стане голям
Когато N стане голям, става досадно да се създава константата на масива на ръка - Ако искате да сумирате до топ 20 или 30 стойности в голям списък, въвеждането на константа на масив с 20 или 30 елемента ще отнеме много време. В този случай можете да използвате пряк път за изграждане на константата на масива, която използва функциите ROW и INDIRECT.
Например, ако искате да СБОРЕТЕ топ 20 стойности в диапазон, наречен "rng", можете да напишете формула като тази:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Променлива N
При недостатъчно данни фиксиран N може да причини грешки. В този случай можете да опитате формула като тази:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Тук използваме MIN с COUNT, за да сумираме първите 3 стойности или броя на стойностите, ако е по-малко от 3.
