Általános képlet
=SUMPRODUCT(SMALL(rng,(1,2,n)))
Összegzés
A tartomány legalacsonyabb n értékének összegzéséhez használhatja a KIS és a SUMPRODUCT függvényen alapuló képletet. A (fenti) képlet általános formájában az rng olyan cellatartományt jelent, amely numerikus értékeket tartalmaz, és n az összesítendő legkisebb értékek számát jelenti. A bemutatott példában az E5 a következő képletet tartalmazza:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
amely a B5 három legkisebb értékének összegét adja vissza: B14, 60
Magyarázat
A SMALL a legegyszerűbb formájában a tartomány n. Legkisebb értékét adja vissza. Például:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
Ha azonban egy tömbállandót (pl. Konstans (1,2,3) formában) ad meg a SMALL-nak második argumentumként, akkor a SMALL egy eredménytömböt ad vissza egyetlen eredmény helyett. Például:
=SMALL(A1:A10,(1,2,3))
az A1: A10 tartomány 1., 2. és 3. legkisebb értékét adja vissza.
A bemutatott példában belülről kifelé haladva a SMALL a B5: B14 tartomány 3 legkisebb értékét adja vissza:
=SMALL(B4:B14,(1,2,3))
Az eredmény egy ilyen tömb:
(10,20,30)
Ez a tömb visszakerül a SUMPRODUCT függvénybe, amely összegzi a számokat és visszaadja az összeget:
SUMPRODUCT((10,20,30)) // returns 60
Tömbképlet SUM-tal
Gyakori a SUMPRODUCT használata a fentiek szerint, mert natívan képes kezelni a tömböket anélkül, hogy tömbképletként adnánk meg. Írhat egy tömbképletet is a SUM függvénnyel így:
(=SUM(SMALL(B4:B13,(1,2,3))))
Ez egy tömbképlet, amelyet a Control + Shift + Enter billentyűkombinációval kell megadni, az Excel 365 kivételével.
Amikor n nagy lesz
Amikor n nagy lesz, unalmas lesz a tömbállandót kézzel létrehozni - egy tömbállandó 20 vagy 30 elemmel való begépelése hosszú időt vesz igénybe. Ebben az esetben parancsikont használhat a tömbállandó felépítéséhez, amely a ROW és INDIRECT funkciókat használja. Például az "rng" nevű tartomány alsó 20 értékének összegzéséhez írhat egy ilyen képletet:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
Itt a INDIRECT az "1:20" karakterláncot 1:20 tartományra konvertálja, amely közvetlenül a KIS értékre tér vissza.
N. Változó
Az a képlet beállításához, ahol n egy változó egy másik cellában, összefűzheti az INDIRECT-et. Például, ha az A1 N-t tartalmaz, használhatja:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
Ez lehetővé teszi a felhasználó számára, hogy az n értékét közvetlenül a munkalapon módosítsa.
