Všeobecný vzorec
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Zhrnutie
Ak chcete sčítať prvých n hodnôt do kritérií zhody rozsahu, môžete použiť vzorec založený na funkcii LARGE, zabalený do funkcie SUMPRODUCT. V generické forme všeobecného vzorca (pozri vyššie), rozsah predstavuje rozsah buniek, ktoré sú v porovnaní s kritériami , hodnoty predstavujú číselné hodnoty, z ktorých sú vyberané najlepšie hodnoty, a N predstavuje myšlienku N-teho hodnoty.
V príklade obsahuje aktívna bunka tento vzorec:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Kde farba je pomenovaný rozsah B5: B12 a hodnota je pomenovaný rozsah C5: C12.
Vysvetlenie
Vo svojej najjednoduchšej podobe vracia LARGE „N-tú najväčšiu“ hodnotu v rozsahu s touto konštrukciou:
=LARGE (range,N)
Napríklad:
=LARGE (C5:C12,2)
vráti druhú najväčšiu hodnotu v rozsahu C5: C12, ktorá je v zobrazenom príklade 12.
Ak však zadáte „konštantu poľa“ (napr. Konštantu vo forme (1,2,3)) do parametra LARGE ako druhý argument, funkcia LARGE vráti namiesto jedného výsledku pole výsledkov. Takže vzorec:
=LARGE (C5:C12, (1,2,3))
vráti 1., 2. a 3. najväčšiu hodnotu C5: C12 v poli ako je toto: (12,12,10)
Trik teda spočíva v filtrovaní hodnôt podľa farby pred spustením LARGE. Robíme to s výrazom:
(color=E5)
Výsledkom je pole hodnôt TRUE / FALSE. Počas operácie násobenia sú tieto hodnoty vynútené do jednotiek a núl:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Konečným výsledkom je, že operáciu prežijú iba hodnoty spojené s farbou „červená“:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
a ostatné hodnoty sú vynútené na nulu.
Poznámka: tento vzorec nebude spracovávať text v rozsahu hodnôt. Pozri nižšie.
Zaobchádzanie s textom v hodnotách
Ak máte text kdekoľvek v rozsahu hodnôt, funkcia LARGE vyhodí chybu #HODNOTA a zastaví fungovanie vzorca.
Ak chcete spracovať text v rozsahu hodnôt, môžete pridať funkciu IFERROR takto:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Tu zachytíme chyby z VEĽKÉHO spôsobené textovými hodnotami a nahradíme ich nulou. Použitie IF vo vnútri LARGE vyžaduje, aby bol vzorec zadaný pomocou klávesov control + shift + enter, takže namiesto SUMPRODUCT prepneme na SUM.
Poznámka: Narazil som na tento vzorec, ktorý zverejnil úžasný Barry Houdini pri stackoverflow.
