Általános képlet
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Összegzés
A tartomány nulla legfelsõbb értékének ö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 a tartomány egy olyan cellatartományt jelent, amelyet összehasonlítunk a feltételekkel , az értékek olyan numerikus értékeket képviselnek, amelyekből a legfelső értékeket kapják, és az 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((color=E5)*(value),(1,2,3)))
Ahol a szín a B5: B12 elnevezett tartomány, az érték pedig a C5: C12 megnevezett tartomány.
Magyarázat
A legegyszerűbb formájában a LARGE ezzel a konstrukcióval adja vissza a tartomány "N-edik legnagyobb" értékét:
=LARGE (range,N)
Tehát például:
=LARGE (C5:C12,2)
a C5: C12 tartomány 2. legnagyobb értékét adja vissza, amely a bemutatott példában 12.
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 (C5:C12, (1,2,3))
az 1., 2. és 3. legnagyobb C5: C12 értéket adja vissza egy ilyen tömbben: (12,12,10)
Tehát itt az a trükk, hogy az értékeket szín alapján szűrjük le, mielőtt a LARGE fut. Ezt a következő kifejezéssel tesszük:
(color=E5)
Ami TRUE / FALSE értékek tömbjét eredményezi. A szorzási művelet során ezeket az értékeket egysé és nullává kényszerítik:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Tehát a végeredmény az, hogy csak a "piros" színhez tartozó értékek élik túl a műveletet:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
és a többi érték nullára kényszerül.
Megjegyzés: ez a képlet nem kezeli az értéktartomány szövegét. Lásd lejjebb.
Szöveg kezelése értékekben
Ha bárhol van szövege az értéktartományokban, a NAGY függvény # ÉRTÉK hibát dob, és megakadályozza a képlet működését.
Az értéktartományban lévő szöveg kezeléséhez hozzáadhatja az IFERROR függvényt a következőképpen:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Itt csapdába ejtjük a LARGE szöveghibákat, amelyeket szöveges értékek okoznak, és nullára cseréljük. Az IF használata a LARGE belsejében megköveteli, hogy a képletet a + + shift + enter billentyűkombinációval írjuk be, ezért a SUMPRODUCT helyett SUM-ra váltunk.
Megjegyzés: Belefutottam ebbe a képletbe, amelyet a csodálatos Barry Houdini tett közzé a stackoverflow-n.
