Generisk formel
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Resumé
For at opsummere de nederste n-værdier i et kriterium for områdematchning kan du bruge en matrixformel baseret på funktionen SMALL, pakket ind i SUM-funktionen. I den generiske form med formlen (ovenfor) repræsenterer område1 rækkevidden af celler sammenlignet med kriterier , område2 indeholder numeriske værdier, hvorfra bundværdier hentes, og N repræsenterer "nth".
I eksemplet indeholder den aktive celle denne formel:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Hvor farve er det navngivne område B5: B12, og værdien er det navngivne område C5: C12.
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter.
Forklaring
I sin enkleste form returnerer SMALL værdien "Nth mindste" i et interval med denne konstruktion:
=SMALL (range,N)
Så for eksempel:
=SMALL (C5:C12,2)
returnerer den 2. mindste værdi i området C5: C12, som er 5 i det viste eksempel.
Men hvis du leverer en "matrixkonstant" (f.eks. En konstant i formen (1,2,3)) til SMALL som det andet argument, returnerer SMALL en matrix med resultater i stedet for et enkelt resultat. Så formlen:
=SMALL (C5:C12, (1,2,3))
returnerer den 1., 2. og 3. mindste værdi C5: C12 i en matrix som denne: (4,5,7).
Så tricket her er at filtrere værdierne baseret på farve, før SMALL kører. Vi gør dette med et udtryk baseret på IF-funktionen:
IF(color=E5,value)
Dette bygger opstillingen af værdier, der indføres i SMALL. I det væsentlige er det kun værdier, der er knyttet til den røde farve, der kommer ind i arrayet. Hvor farve er lig med "rød", indeholder matrixen et tal, og hvor farven ikke er rød, indeholder arrayet FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
SMALL-funktionen ignorerer FALSE-værdierne og returnerer de 3 mindste værdier i arrayet: (4,8,10). SUM-funktionen returnerer det endelige resultat, 22.
