Generieke formule
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Samenvatting
Om de topwaarden in een bereik op te tellen, kunt u een formule gebruiken die is gebaseerd op de LARGE-functie, verpakt in de SUMPRODUCT-functie. In de generieke vorm van de formule (hierboven) staat rng voor een celbereik dat numerieke waarden bevat en staat N voor het idee van de N- de waarde.
In het voorbeeld bevat de actieve cel deze formule:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Uitleg
In de eenvoudigste vorm retourneert LARGE de "N-de grootste" waarde in een bereik. Bijvoorbeeld de formule:
=LARGE(B4:B13, 2)
retourneert de op een na grootste waarde in het bereik B4: B13, wat in het bovenstaande voorbeeld het getal 9 is.
Als u echter een "matrixconstante" (bijv. Een constante in de vorm (1,2,3)) opgeeft aan LARGE als tweede argument, retourneert LARGE een matrix met resultaten in plaats van een enkel resultaat. Dus de formule:
=LARGE(B4:B13,(1,2,3))
retourneert de 1e, 2e en 3e grootste waarde in het bereik B4: B13. In het bovenstaande voorbeeld, waarbij B4: B13 de nummers 1-10 bevat, is het resultaat van LARGE de array (8,9,10). SOMPRODUCT telt vervolgens de getallen in deze array op en retourneert een totaal, dat 27 is.
SOM in plaats van SOMPRODUCT
SUMPRODUCT is een flexibele functie waarmee u celverwijzingen voor k kunt gebruiken in de LARGE-functie.
Als u echter een eenvoudige hardgecodeerde arrayconstante zoals (1,2,3) gebruikt, kunt u gewoon de functie SOM gebruiken:
=SUM(LARGE(B4:B13,(1,2,3)))
Merk op dat u deze formule als een matrixformule moet invoeren als u celverwijzingen gebruikt en niet een matrixconstante voor k binnen LARGE.
Wanneer N groot wordt
Wanneer N groot wordt, wordt het vervelend om de matrixconstante met de hand te maken - Als u de top 20 of 30 waarden in een grote lijst wilt optellen, duurt het lang om een matrixconstante met 20 of 30 items uit te typen. In dit geval kunt u een snelkoppeling gebruiken voor het bouwen van de matrixconstante die gebruikmaakt van de functies RIJ en INDIRECTE.
Als u bijvoorbeeld de top 20 waarden in een bereik met de naam "rng" wilt SUMMen, kunt u een formule als volgt schrijven:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Variabele N
Bij onvoldoende data kan een vaste N fouten veroorzaken. In dit geval kunt u een formule als deze proberen:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Hier gebruiken we MIN met COUNT om de top 3 waarden bij elkaar op te tellen, of het aantal waarden, indien minder dan 3.
