Vispārēja formula
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Kopsavilkums
Apkopojot diapazona augšējās vērtības, varat izmantot formulu, kuras pamatā ir funkcija LARGE, kas iesaiņota funkcijas SUMPRODUCT iekšpusē. Formulas (iepriekš) vispārīgajā formā rng apzīmē šūnu diapazonu, kas satur skaitliskas vērtības, un N ir ideja par N vērtību.
Piemērā aktīvajā šūnā ir šāda formula:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Paskaidrojums
Vienkāršākajā formā LARGE atgriezīs diapazona "N lielāko" vērtību. Piemēram, formula:
=LARGE(B4:B13, 2)
atgriezīs 2. lielāko vērtību diapazonā B4: B13, kas iepriekš minētajā piemērā ir skaitlis 9.
Tomēr, ja jūs kā otro argumentu LARGE piegādājat "masīva konstanti" (piem., Konstanti formā (1,2,3)), LARGE viena rezultāta vietā atgriezīs rezultātu masīvu. Tātad, formula:
=LARGE(B4:B13,(1,2,3))
atgriezīs 1., 2. un 3. lielāko vērtību diapazonā B4: B13. Iepriekš minētajā piemērā, kur B4: B13 ir skaitļi 1-10, rezultāts no LARGE būs masīvs (8,9,10). Pēc tam SUMPRODUCT summē skaitļus šajā masīvā un atgriež kopējo summu, kas ir 27.
SUMPRODUCT vietā SUM
SUMPRODUCT ir elastīga funkcija, kas ļauj izmantot šūnu atsauces k funkcijai LARGE.
Tomēr, ja izmantojat vienkāršu cieti kodētu masīva konstanti, piemēram, (1,2,3), varat izmantot tikai funkciju SUM:
=SUM(LARGE(B4:B13,(1,2,3)))
Ņemiet vērā, ka šī formula jāievada kā masīva formula, ja izmantojat šūnu atsauces, nevis k masīva konstanti lielumam LARGE.
Kad N kļūst liels
Kad N kļūst liels, masīva konstantes izveidošana ar rokām kļūst garlaicīga - ja vēlaties summēt līdz 20 vai 30 lielākajām vērtībām lielajā sarakstā, masīva konstantes ar 20 vai 30 vienumiem ierakstīšana prasīs daudz laika. Šajā gadījumā jūs varat izmantot saīsni, lai izveidotu masīva konstanti, kas izmanto funkcijas ROW un INDIRECT.
Piemēram, ja vēlaties apkopot 20 lielākās vērtības diapazonā, ko sauc par “rng”, varat uzrakstīt šādu formulu:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Mainīgais N
Ja nepietiek datu, fiksētais N var izraisīt kļūdas. Šajā gadījumā varat izmēģināt šādu formulu:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Šeit mēs izmantojam MIN ar COUNT, lai summētu top 3 vērtības vai vērtību skaitu, ja tas ir mazāks par 3.
