Generisk formel
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Sammendrag
For å oppsummere de øverste n-verdiene i et utvalg som samsvarer med kriteriene, kan du bruke en formel basert på STOR funksjon, innpakket i SUMPRODUCT-funksjonen. I den generiske formen av formelen (ovenfor) representerer rekkevidde et område med celler som sammenlignes med kriterier , verdier representerer numeriske verdier som toppverdier hentes fra, og N representerer ideen om Nth-verdi.
I eksemplet inneholder den aktive cellen denne formelen:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Hvor farge er det navngitte området B5: B12 og verdien er det navngitte området C5: C12.
Forklaring
I sin enkleste form returnerer LARGE den "Nth største" verdien i et område med denne konstruksjonen:
=LARGE (range,N)
Så for eksempel:
=LARGE (C5:C12,2)
vil returnere den nest største verdien i området C5: C12, som er 12 i det viste eksemplet.
Imidlertid, hvis du leverer en "matrisekonstant" (f.eks. En konstant i formen (1,2,3)) til STOR som det andre argumentet, vil LARGE returnere en rekke resultater i stedet for et enkelt resultat. Så formelen:
=LARGE (C5:C12, (1,2,3))
vil returnere 1., 2. og 3. største verdi C5: C12 i en matrise som dette: (12,12,10)
Så, trikset her er å filtrere verdiene basert på farge før STOR kjøres. Vi gjør dette med uttrykket:
(color=E5)
Hvilket resulterer i en rekke SANNE / FALSE verdier. Under multiplikasjonsoperasjonen blir disse verdiene tvunget 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 resultatet er at bare verdier knyttet til fargen "rød" overlever operasjonen:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
og de andre verdiene blir tvunget til null.
Merk: denne formelen håndterer ikke tekst i verdiområdet. Se nedenfor.
Håndtering av tekst i verdier
Hvis du har tekst hvor som helst i verdiområdene, vil STOR-funksjonen kaste en #VALUE-feil og stoppe formelen fra å fungere.
For å håndtere tekst i verdiområdet, kan du legge til IFERROR-funksjonen slik:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Her fanger vi feil fra STOR forårsaket av tekstverdier og erstatter med null. Å bruke IF inne i LARGE krever at formelen legges inn med kontroll + skift + enter, så vi bytter til SUM i stedet for SUMPRODUCT.
Merk: Jeg løp inn i denne formelen som ble lagt ut av den fantastiske Barry Houdini på stackoverflow.
