Általános képlet
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Összegzés
A tartomány felső értékeinek összegzéséhez használhatja a NAGY függvényen alapuló képletet, a SUMPRODUCT függvénybe csomagolva. A (fenti) képlet általános formájában az rng olyan cellatartományt jelent, amely numerikus értékeket tartalmaz, és N az N-edik érték ötletét jelenti.
A példában az aktív cella a következő képletet tartalmazza:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Magyarázat
A legegyszerűbb formájában a LARGE a tartomány "N-edik" értékét adja vissza. Például a képlet:
=LARGE(B4:B13, 2)
a B4: B13 tartomány 2. legnagyobb értékét adja vissza, amely a fenti példában a 9. szám.
Ha azonban egy "tömbállandót" (pl. Konstans (1,2,3) formában) ad meg a LARGE-nek második argumentumként, a LARGE egy eredménytömböt ad vissza egyetlen eredmény helyett. Tehát, a képlet:
=LARGE(B4:B13,(1,2,3))
a B4: B13 tartomány 1., 2. és 3. legnagyobb értékét adja vissza. A fenti példában, ahol a B4: B13 az 1-10 számokat tartalmazza, a LARGE eredménye a tömb lesz (8,9,10). Ezután a SUMPRODUCT összegzi a tömbben található számokat, és egy összeget ad vissza, ami 27.
SUMPRODUCT helyett SUM
A SUMPRODUCT egy rugalmas függvény, amely lehetővé teszi a k cellasejt-hivatkozások használatát a LARGE függvényen belül.
Ha azonban egyszerű, keményen kódolt tömbállandót használ, mint például (1,2,3), akkor csak a SUM függvényt használhatja:
=SUM(LARGE(B4:B13,(1,2,3)))
Ne feledje, hogy ezt a képletet tömbképletként kell megadnia, ha cellahivatkozásokat használ, és nem a k tömbállandóját a LARGE-n belül.
Amikor N nagy lesz
Amikor N nagy lesz, unalmas lesz a tömbállandót kézzel létrehozni - Ha egy nagy listában a 20 vagy 30 legfelsõbb értéket akarja összegezni, akkor egy tömbállandó 20 vagy 30 elemmel való beírása 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, ha összegezni szeretné az "rng" nevű tartomány top 20 értékét, írhat egy ilyen képletet:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
N változó
Hiányos adat esetén a fix N hibákat okozhat. Ebben az esetben kipróbálhat egy ilyen képletet:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Itt a MIN és COUNT értékeket használjuk a felső 3 érték összegzéséhez, vagy az értékek számának összegzéséhez, ha kevesebb, mint 3.
