Generička formula
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Sažetak
Da biste zbrojili gornjih n vrijednosti u rasponu koji odgovara kriterijima, možete upotrijebiti formulu koja se temelji na funkciji LARGE, umotanoj u funkciju SUMPRODUCT. U generičkom obliku formule (gore), raspon predstavlja raspon stanica koje se uspoređuju s kriterijima , vrijednosti predstavljaju numeričke vrijednosti iz kojih se preuzimaju vršne vrijednosti, a N predstavlja ideju N-te vrijednosti.
U primjeru aktivna ćelija sadrži ovu formulu:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Gdje je boja imenovani raspon B5: B12, a vrijednost imenovani raspon C5: C12.
Obrazloženje
U svom najjednostavnijem obliku, LARGE vraća "N-tu najveću" vrijednost u rasponu s ovom konstrukcijom:
=LARGE (range,N)
Tako, na primjer:
=LARGE (C5:C12,2)
vratit će 2. najveću vrijednost u rasponu C5: C12, što je 12 u prikazanom primjeru.
Međutim, ako kao drugi argument navedete "VELIKU konstantu" (npr. Konstantu u obliku (1,2,3)), LARGE će vratiti niz rezultata umjesto jednog rezultata. Dakle, formula:
=LARGE (C5:C12, (1,2,3))
vratit će 1., 2. i 3. najveću vrijednost C5: C12 u nizu poput ovog: (12,12,10)
Dakle, trik je ovdje filtriranje vrijednosti na temelju boje prije nego što se LARGE pokrene. To radimo s izrazom:
(color=E5)
Što rezultira nizom TRUE / FALSE vrijednosti. Tijekom operacije množenja, ove vrijednosti se prisiljavaju na jedinice i nule:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Dakle, konačni rezultat je da samo vrijednosti povezane s bojom "crvena" preživljavaju operaciju:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
a ostale su vrijednosti prisiljene na nulu.
Napomena: ova formula neće obrađivati tekst u rasponu vrijednosti. Pogledaj ispod.
Rukovanje tekstom u vrijednostima
Ako imate tekst bilo gdje u rasponima vrijednosti, funkcija LARGE izbacit će pogrešku #VALUE i zaustaviti rad formule.
Da biste obrađivali tekst u rasponu vrijednosti, možete dodati funkciju IFERROR ovako:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Ovdje zarobimo pogreške LARGE uzrokovane tekstnim vrijednostima i zamijenimo nulom. Korištenje IF unutar LARGE zahtijeva da se formula unese s control + shift + enter, pa prelazimo na SUM umjesto na SUMPRODUCT.
Napomena: Naletio sam na ovu formulu koju je objavio nevjerojatni Barry Houdini na stackoverflow.
