Všeobecný vzorec
=SUMPRODUCT(SMALL(rng,(1,2,n)))
Zhrnutie
Ak chcete sčítať najnižšie hodnoty n v rozsahu, môžete použiť vzorec založený na funkcii MALÝ a SUMPRODUCT. V generickej forme vzorca (vyššie) predstavuje rng rozsah buniek, ktoré obsahujú číselné hodnoty a n predstavuje počet najnižších hodnôt, ktoré sa majú sčítať. V zobrazenom príklade obsahuje E5 tento vzorec:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
ktorá vráti súčet troch najmenších hodnôt v B5: B14, 60
Vysvetlenie
V najjednoduchšej forme vráti funkcia SMALL „n-tou najmenšiu“ hodnotu v rozsahu. Napríklad:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
Ak však zadáte konštantu poľa (napr. Konštantu vo forme (1, 2, 3)) ako druhý argument SMALL, SMALL vráti pole výsledkov namiesto jedného výsledku. Napríklad:
=SMALL(A1:A10,(1,2,3))
vráti 1., 2. a 3. najmenšiu hodnotu v rozsahu A1: A10.
Pri postupe zvnútra smerom von v zobrazenom príklade vráti funkcia SMALL 3 najmenšie hodnoty v rozsahu B5: B14:
=SMALL(B4:B14,(1,2,3))
Výsledkom je pole ako je toto:
(10,20,30)
Toto pole sa vráti priamo do funkcie SUMPRODUCT, ktorá sčíta čísla a vráti celkový počet:
SUMPRODUCT((10,20,30)) // returns 60
Pole vzorca so SUMOU
Ako je uvedené vyššie, je bežné používať SUMPRODUCT, pretože dokáže natívne spracovať polia bez zadania ako maticový vzorec. Môžete však tiež napísať vzorec poľa s funkciou SUM takto:
(=SUM(SMALL(B4:B13,(1,2,3))))
Toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter, s výnimkou Excel 365.
Keď sa n zväčší
Keď bude n veľké, bude namáhavé vytvoriť konštantu poľa ručne - 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 na SUMA spodných 20 hodnôt v rozsahu zvanom „rng“ môžete napísať nasledujúci vzorec:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
INDIRECT tu prevedie reťazec "1:20" na rozsah 1:20, ktorý sa vráti priamo do SMALL.
Premenná č
Ak chcete nastaviť vzorec, kde n je premenná v inej bunke, môžete spojiť reťazce INDIRECT. Napríklad ak A1 obsahuje N, môžete použiť:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
To umožňuje používateľovi zmeniť hodnotu n priamo v hárku.
