Generisk formel
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Resumé
For at opsummere de øverste n-værdier i et kriterium, der matcher intervallet, kan du bruge en formel baseret på den store funktion, pakket ind i funktionen SUMPRODUCT. I den generiske form med formlen (ovenfor) repræsenterer rækkevidde et interval af celler, der sammenlignes med kriterier , værdier repræsenterer numeriske værdier, hvorfra topværdier hentes, og N repræsenterer ideen om Nth-værdi.
I eksemplet indeholder den aktive celle denne formel:
=SUMPRODUCT(LARGE((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.
Forklaring
I sin enkleste form returnerer LARGE værdien "Nth største" i et interval med denne konstruktion:
=LARGE (range,N)
Så for eksempel:
=LARGE (C5:C12,2)
returnerer den næststørste værdi i området C5: C12, som er 12 i det viste eksempel.
Men hvis du leverer en "arraykonstant" (f.eks. En konstant i formen (1,2,3)) til LARGE som det andet argument, returnerer LARGE et array med resultater i stedet for et enkelt resultat. Så formlen:
=LARGE (C5:C12, (1,2,3))
returnerer 1., 2. og 3. største værdi C5: C12 i en matrix som denne: (12,12,10)
Så tricket her er at filtrere værdierne baseret på farve, før LARGE kører. Vi gør dette med udtrykket:
(color=E5)
Hvilket resulterer i en række TRUE / FALSE-værdier. Under multiplikationsoperationen tvinges disse værdier til en og nuller:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Så det endelige resultat er, at kun værdier tilknyttet farven "rød" overlever operationen:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
og de andre værdier tvinges til nul.
Bemærk: denne formel håndterer ikke tekst i værdiområdet. Se nedenunder.
Håndtering af tekst i værdier
Hvis du har tekst overalt i værdiområderne, kaster LARGE-funktionen en #VALUE-fejl og stopper formlen fra at fungere.
For at håndtere tekst i værdiområdet kan du tilføje IFERROR-funktionen som denne:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Her fanger vi fejl fra STOR forårsaget af tekstværdier og erstatter med nul. Brug af IF inde i STOR kræver, at formlen indtastes med kontrol + skift + enter, så vi skifter til SUM i stedet for SUMPRODUCT.
Bemærk: Jeg løb ind i denne formel indsendt af den fantastiske Barry Houdini på stackoverflow.
