Generisk formel
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Sammanfattning
För att summera de översta n-värdena i ett intervallmatchningskriterium kan du använda en formel baserad på den STORA funktionen, insvept i SUMPRODUCT-funktionen. I generisk form med formeln (ovan), intervall utgör en serie av celler som jämförs med kriterier , värden representerar numeriska värden från vilken toppvärden hämtas, och N representerar tanken på Nth värde.
I exemplet innehåller den aktiva cellen följande formel:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Där färg är det namngivna området B5: B12 och värdet är det namngivna området C5: C12.
Förklaring
I sin enklaste form returnerar LARGE värdet "Nth största" i ett intervall med denna konstruktion:
=LARGE (range,N)
Så till exempel:
=LARGE (C5:C12,2)
returnerar det näst största värdet i intervallet C5: C12, vilket är 12 i exemplet som visas.
Men om du anger en "arraykonstant" (t.ex. en konstant i formen (1,2,3)) till LARGE som det andra argumentet, kommer LARGE att returnera en array med resultat istället för ett enda resultat. Så formeln:
=LARGE (C5:C12, (1,2,3))
returnerar det 1: a, 2: a och 3: e största värdet C5: C12 i en sådan matris: (12,12,10)
Så tricket här är att filtrera värdena baserat på färg innan STORA körningar. Vi gör detta med uttrycket:
(color=E5)
Vilket resulterar i en rad sanna / falska värden. Under multiplikationsåtgärden tvingas dessa värden till en och nollor:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Så det slutliga resultatet är att endast värden associerade med färgen "röd" överlever operationen:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
och de andra värdena tvingas till noll.
Obs! Den här formeln hanterar inte text i värdeintervallet. Se nedan.
Hantering av text i värden
Om du har text någonstans i värdeintervallen kommer funktionen STORA att kasta ett #VALUE-fel och stoppa formeln från att fungera.
För att hantera text i värdeområdet kan du lägga till IFERROR-funktionen så här:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Här fångar vi fel från STORA orsakade av textvärden och ersätter med noll. Att använda IF inuti LARGE kräver att formeln anges med kontroll + shift + enter, så vi byter till SUM istället för SUMPRODUCT.
Obs! Jag stötte på den här formeln som publicerades av den fantastiska Barry Houdini på stackoverflow.
