Splošna formula
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Povzetek
Za seštevanje najvišjih vrednosti v obsegu lahko uporabite formulo, ki temelji na funkciji LARGE, zaviti v funkcijo SUMPRODUCT. V splošni obliki formule (zgoraj) rng predstavlja obseg celic, ki vsebujejo številske vrednosti, N pa idejo N-te vrednosti.
V primeru aktivna celica vsebuje to formulo:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Pojasnilo
V svoji najpreprostejši obliki bo LARGE vrnil "N-to največjo" vrednost v obsegu. Na primer formula:
=LARGE(B4:B13, 2)
bo vrnil 2. največjo vrednost v območju B4: B13, kar je v zgornjem primeru številka 9.
Č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(B4:B13,(1,2,3))
vrne 1., 2. in 3. največjo vrednost v območju B4: B13. V zgornjem primeru, kjer B4: B13 vsebuje številke 1-10, bo rezultat iz LARGE matrika (8,9,10). Nato SUMPRODUCT sešteje števila v tej matriki in vrne seštevek, ki je 27.
SUM namesto SUMPRODUCT
SUMPRODUCT je prilagodljiva funkcija, ki vam omogoča uporabo referenc celic za k znotraj funkcije LARGE.
Če pa uporabljate preprosto trdo kodirano konstanto matrike, kot je (1,2,3), lahko uporabite samo funkcijo SUM:
=SUM(LARGE(B4:B13,(1,2,3)))
Upoštevajte, da morate to formulo vnesti kot matrično formulo, če uporabljate sklice na celice in ne konstante matrike za k znotraj LARGE.
Ko N postane velik
Ko N postane velik, postane ročno ustvarjanje konstante matrike dolgočasno - če želite na velikem seznamu sešteti 20 ali 30 vrednosti, bo vnos konstante matrike z 20 ali 30 elementi trajal dolgo. V tem primeru lahko uporabite bližnjico za gradnjo konstante polja, ki uporablja funkciji ROW in INDIRECT.
Če želite na primer SEŠČETI zgornjih 20 vrednosti v obsegu, imenovanem "rng", lahko napišete tako formulo:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Spremenljivka N
Če ni dovolj podatkov, lahko fiksni N povzroči napake. V tem primeru lahko poskusite s formulo, kot je ta:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Tu uporabljamo MIN z COUNT za seštevanje top 3 vrednosti ali števila vrednosti, če je manj kot 3.
