Generieke formule
=SUMPRODUCT(SMALL(rng,(1,2,n)))
Samenvatting
Om de laagste n waarden in een bereik op te tellen, kunt u een formule gebruiken die is gebaseerd op de functie KLEIN en de functie SOMPRODUCT. In de generieke vorm van de formule (hierboven) staat rng voor een celbereik dat numerieke waarden bevat en staat n voor het aantal laagste waarden dat moet worden opgeteld. In het getoonde voorbeeld bevat E5 deze formule:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
die de som van de drie kleinste waarden in B5 retourneert: B14, 60
Uitleg
In de eenvoudigste vorm retourneert SMALL de "n-de kleinste" waarde in een bereik. Bijvoorbeeld:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
Als u echter een matrixconstante (bijv. Een constante in de vorm (1,2,3)) opgeeft aan SMALL als tweede argument, retourneert SMALL een matrix met resultaten in plaats van één resultaat. Bijvoorbeeld:
=SMALL(A1:A10,(1,2,3))
retourneert de 1e, 2e en 3e kleinste waarden in het bereik A1: A10.
In het getoonde voorbeeld werkt SMALL van binnen naar buiten en geeft als resultaat de 3 kleinste waarden in het bereik B5: B14:
=SMALL(B4:B14,(1,2,3))
Het resultaat is een array als deze:
(10,20,30)
Deze array wordt rechtstreeks geretourneerd naar de functie SOMPRODUCT, die de getallen optelt en het totaal retourneert:
SUMPRODUCT((10,20,30)) // returns 60
Matrixformule met SOM
Het is gebruikelijk om SOMPRODUCT te gebruiken zoals hierboven, omdat het native arrays kan verwerken zonder in te voeren als een matrixformule. U kunt echter ook als volgt een matrixformule schrijven met de functie SOM:
(=SUM(SMALL(B4:B13,(1,2,3))))
Dit is een matrixformule en moet worden ingevoerd met control + shift + enter, behalve in Excel 365.
Wanneer n groot wordt
Wanneer n groot wordt, wordt het vervelend om de matrixconstante met de hand te maken - het uittypen van een matrixconstante met 20 of 30 items zal lang duren. 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 onderste 20 waarden in een bereik met de naam "rng" wilt SOMEN, kunt u een formule als volgt schrijven:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
Hier converteert INDIRECT de string "1:20" naar het bereik 1:20, dat direct wordt geretourneerd naar SMALL.
Variabele n
Om de a-formule in te stellen waarbij n een variabele is in een andere cel, kunt u binnen INDIRECT aaneenschakelen. Als A1 bijvoorbeeld N bevat, kunt u het volgende gebruiken:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
Hierdoor kan een gebruiker de waarde van n rechtstreeks op het werkblad wijzigen.
