Generieke formule
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Samenvatting
Om de onderste n-waarden in een bereik dat overeenkomt met criteria op te tellen, kunt u een matrixformule gebruiken op basis van de functie KLEIN, verpakt in de functie SOM. In de generieke vorm van de formule (hierboven) vertegenwoordigt bereik1 het celbereik vergeleken met criteria , bevat bereik2 numerieke waarden waaruit onderste waarden worden opgehaald, en staat N voor "n-de".
In het voorbeeld bevat de actieve cel deze formule:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Waarbij kleur het benoemde bereik B5: B12 is en waarde het benoemde bereik C5: C12.
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter.
Uitleg
In zijn eenvoudigste vorm retourneert KLEIN de "N-de kleinste" waarde in een bereik met deze constructie:
=SMALL (range,N)
Dus bijvoorbeeld:
=SMALL (C5:C12,2)
retourneert de op één na kleinste waarde in het bereik C5: C12, wat 5 is in het getoonde voorbeeld.
Als u echter een "matrixconstante" (bijv. Een constante in de vorm (1,2,3)) opgeeft aan SMALL als het tweede argument, zal SMALL een reeks resultaten retourneren in plaats van een enkel resultaat. Dus de formule:
=SMALL (C5:C12, (1,2,3))
retourneert de 1e, 2e en 3e kleinste waarde C5: C12 in een array als deze: (4,5,7).
De truc hier is dus om de waarden te filteren op basis van kleur voordat SMALL wordt uitgevoerd. Dit doen we met een uitdrukking gebaseerd op de ALS-functie:
IF(color=E5,value)
Dit bouwt de reeks waarden op die in SMALL worden ingevoerd. In wezen komen alleen waarden die aan de kleur rood zijn gekoppeld, in de array. Waar kleur gelijk is aan "rood", bevat de array een getal, en waar de kleur niet rood is, bevat de array FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
De SMALL-functie negeert de FALSE-waarden en retourneert de 3 kleinste waarden in de array: (4,8,10). De functie SOM retourneert het eindresultaat, 22.
