Splošna formula
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Povzetek
Če želite povzeti zgornjih n vrednosti v merilih, ki se ujemajo, lahko uporabite formulo, ki temelji na funkciji LARGE, oviti znotraj funkcije SUMPRODUCT. V generični obliki formule (zgoraj) obseg predstavlja obseg celic, ki se primerjajo z merili , vrednosti predstavljajo številske vrednosti, iz katerih se pridobijo zgornje vrednosti, N pa predstavlja N-to vrednost.
V primeru aktivna celica vsebuje to formulo:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Kjer je barva imenovani obseg B5: B12, vrednost pa imenovani obseg C5: C12.
Pojasnilo
V svoji najpreprostejši obliki vrne LARGE "N-to največjo" vrednost v območju s to konstrukcijo:
=LARGE (range,N)
Tako na primer:
=LARGE (C5:C12,2)
bo vrnil 2. največjo vrednost v območju C5: C12, kar je 12 v prikazanem primeru.
Če pa kot drugi argument vnesete LARGE kot »konstanto matrike« (npr. Konstanto v obliki (1,2,3)), bo LARGE vrnil matriko rezultatov namesto enega rezultata. Torej, formula:
=LARGE (C5:C12, (1,2,3))
bo vrnil 1., 2. in 3. največjo vrednost C5: C12 v takem polju: (12,12,10)
Trik je torej v filtriranju vrednosti glede na barvo pred zagonom LARGE. To naredimo z izrazom:
(color=E5)
Kar ima za posledico niz TRUE / FALSE vrednosti. Med operacijo množenja se te vrednosti prisilijo v enote in ničle:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Končni rezultat je torej, da samo vrednosti, povezane z barvo "rdeča", preživijo operacijo:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
ostale vrednosti pa so prisiljene na nič.
Opomba: ta formula ne bo obravnavala besedila v obsegu vrednosti. Glej spodaj.
Ravnanje z besedilom v vrednostih
Če imate besedilo kjer koli v obsegu vrednosti, bo funkcija LARGE vrnila napako #VALUE in zaustavila delovanje formule.
Za obdelavo besedila v obsegu vrednosti lahko dodate funkcijo IFERROR tako:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Tu zajamemo napake LARGE, ki jih povzročijo besedilne vrednosti, in jih nadomestimo z nič. Če uporabljate IF znotraj LARGE, je treba v formulo vnesti control + shift + enter, zato namesto SUMPRODUCT preklopimo na SUM.
Opomba: Naletel sem na to formulo, ki jo je objavil neverjeten Barry Houdini na stackoverflow.
