Všeobecný vzorec
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Zhrnutie
Ak chcete sčítať najvyššie hodnoty v rozsahu, môžete použiť vzorec založený na funkcii LARGE zabalený do funkcie SUMPRODUCT. V generickej forme vzorca (vyššie) predstavuje rng rozsah buniek, ktoré obsahujú číselné hodnoty, a N predstavuje myšlienku N-tej hodnoty.
V príklade obsahuje aktívna bunka tento vzorec:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Vysvetlenie
V najjednoduchšej podobe vráti funkcia LARGE „N-tú najväčšiu“ hodnotu v rozsahu. Napríklad vzorec:
=LARGE(B4:B13, 2)
vráti druhú najväčšiu hodnotu v rozsahu B4: B13, čo je v príklade vyššie číslo 9.
Ak však zadáte „konštantu poľa“ (napr. Konštantu vo forme (1,2,3)) do parametra LARGE ako druhý argument, funkcia LARGE vráti namiesto jedného výsledku pole výsledkov. Takže vzorec:
=LARGE(B4:B13,(1,2,3))
vráti 1., 2. a 3. najväčšiu hodnotu v rozsahu B4: B13. Vo vyššie uvedenom príklade, kde B4: B13 obsahuje čísla 1-10, bude výsledkom z VEĽKÉHO pole (8,9,10). SUMPRODUCT potom sčíta čísla v tomto poli a vráti celkovú hodnotu, ktorá je 27.
SUM namiesto SUMPRODUCT
SUMPRODUCT je flexibilná funkcia, ktorá vám umožňuje používať odkazy na bunky pre k vo vnútri funkcie LARGE.
Ak však používate jednoduchú pevne naprogramovanú konštantu poľa ako (1,2,3), môžete jednoducho použiť funkciu SUM:
=SUM(LARGE(B4:B13,(1,2,3)))
Upozorňujeme, že tento vzorec musíte zadať ako vzorec poľa, ak používate odkazy na bunky, a nie konštantu poľa pre k vo vnútri LARGE.
Keď sa N stane veľkým
Keď bude N veľké, bude namáhavé ručne vytvárať konštantu poľa - Ak chcete vo veľkom zozname sčítať prvých 20 alebo 30 hodnôt, vypisovanie konštanty poľa s 20 alebo 30 položkami bude trvať dlho. V takom prípade môžete použiť skratku na vytvorenie konštanty poľa, ktorá používa funkcie ROW a INDIRECT.
Napríklad, ak chcete SUMAŤ 20 najlepších hodnôt v rozsahu zvanom „rng“, môžete napísať nasledujúci vzorec:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Premenná N
Pri nedostatku údajov môže pevný N spôsobiť chyby. V takom prípade môžete vyskúšať nasledujúci vzorec:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Tu použijeme MIN s COUNT, aby sme spočítali najvyššie 3 hodnoty alebo počet hodnôt, ak je menší ako 3.
