Vispārēja formula
=SUMPRODUCT(SMALL(rng,(1,2,n)))
Kopsavilkums
Apkopojot zemākās n vērtības diapazonā, varat izmantot formulu, kuras pamatā ir funkcija MAZA un funkcija SUMPRODUCT. Formulas (iepriekš) vispārīgajā formā rng apzīmē šūnu diapazonu, kas satur skaitliskas vērtības, un n apzīmē zemāko summējamo vērtību skaitu. Parādītajā piemērā E5 satur šo formulu:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
kas atgriež B5 trīs mazāko vērtību summu: B14, 60
Paskaidrojums
Vienkāršākajā formā SMALL atgriezīs diapazona "n mazāko" vērtību. Piemēram:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
Tomēr, ja jūs piegādājat masīva konstanti (piemēram, konstanti formā (1,2,3)) SMALL kā otro argumentu, SMALL viena rezultāta vietā atgriezīs masīvu rezultātus. Piemēram:
=SMALL(A1:A10,(1,2,3))
atgriezīs 1., 2. un 3. mazāko vērtību diapazonā A1: A10.
Strādājot no iekšpuses uz āru parādītajā piemērā, SMALL atgriež 3 mazākās vērtības diapazonā B5: B14:
=SMALL(B4:B14,(1,2,3))
Rezultāts ir šāds masīvs:
(10,20,30)
Šis masīvs tiek atgriezts tieši funkcijā SUMPRODUCT, kas summē skaitļus un atgriež kopējo summu:
SUMPRODUCT((10,20,30)) // returns 60
Masīva formula ar SUM
Parasti SUMPRODUCT tiek izmantots tāpat kā iepriekš, jo tas var apstrādāt masīvus dabiski, neievadot kā masīva formulu. Tomēr jūs varat uzrakstīt masīva formulu ar funkciju SUM šādi:
(=SUM(SMALL(B4:B13,(1,2,3))))
Šī ir masīva formula, un tā jāievada ar vadības taustiņu + Shift + Enter, izņemot Excel 365.
Kad n kļūst liels
Kad n kļūst liels, masīva konstanta izveidošana ar rokām kļūst garlaicīga - masīva konstante ar 20 vai 30 vienumiem tiks ierakstīta ilgu laiku. Šajā gadījumā jūs varat izmantot saīsni, lai izveidotu masīva konstanti, kas izmanto funkcijas ROW un INDIRECT. Piemēram, lai summētu 20 apakšējās vērtības diapazonā ar nosaukumu "rng", varat uzrakstīt šādu formulu:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
Šeit INDIRECT pārveido virkni "1:20" par diapazonu 1:20, kas tiek atgriezta tieši SMALL.
Mainīgais n
Lai iestatītu formulu, kur n ir mainīgais citā šūnā, varat saķerties INDIRECT iekšpusē. Piemēram, ja A1 satur N, varat izmantot:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
Tas ļauj lietotājam mainīt n vērtību tieši darblapā.
