Obecný vzorec
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
souhrn
Chcete-li shrnout prvních n hodnot do kritérií shody rozsahu, můžete použít vzorec založený na funkci VELKÝ zabalený do funkce SUMPRODUCT. V generické formě obecného vzorce (viz výše), rozsah představuje oblast buněk, které jsou ve srovnání s kritérii , hodnoty představují číselné hodnoty, ze kterých jsou vybírány nejlépe hodnoty, a N představuje myšlenku N-tého hodnoty.
V příkladu obsahuje aktivní buňka tento vzorec:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Kde barva je pojmenovaný rozsah B5: B12 a hodnota je pojmenovaný rozsah C5: C12.
Vysvětlení
Ve své nejjednodušší podobě vrátí LARGE hodnotu „N-té největší“ v rozsahu s touto konstrukcí:
=LARGE (range,N)
Například:
=LARGE (C5:C12,2)
vrátí 2. největší hodnotu v rozsahu C5: C12, což je v uvedeném příkladu 12.
Pokud však jako druhý argument zadáte „konstantu pole“ (např. Konstantu ve tvaru (1,2,3)) do LARGE, vrátí VELKÁ namísto jediného výsledku pole výsledků. Vzorec tedy:
=LARGE (C5:C12, (1,2,3))
vrátí 1., 2. a 3. největší hodnotu C5: C12 v takovém poli: (12,12,10)
Trik tedy spočívá v filtrování hodnot na základě barev před spuštěním LARGE. Děláme to s výrazem:
(color=E5)
Což má za následek pole hodnot TRUE / FALSE. Během operace násobení jsou tyto hodnoty převedeny na jedničky a nuly:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Konečným výsledkem tedy je, že operaci přežijí pouze hodnoty spojené s barvou „červená“:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
a ostatní hodnoty jsou vynuceny na nulu.
Poznámka: tento vzorec nebude zpracovávat text v rozsahu hodnot. Viz. níže.
Zpracování textu v hodnotách
Pokud máte text kdekoli v rozsahu hodnot, funkce LARGE způsobí chybu #HODNOTA a zastaví vzorec v práci.
Chcete-li zpracovat text v rozsahu hodnot, můžete přidat funkci IFERROR takto:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Zde zachycujeme chyby z LARGE způsobené textovými hodnotami a nahrazujeme je nulou. Použití IF uvnitř LARGE vyžaduje, aby byl vzorec zadán pomocí control + shift + enter, takže místo SUMPRODUCT přepneme na SUM.
Poznámka: Narazil jsem na tento vzorec, který zveřejnil úžasný Barry Houdini na stackoverflow.
