Obecný vzorec
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
souhrn
Chcete-li sečíst dolní hodnoty n v kritériích shody rozsahu, můžete použít maticový vzorec založený na funkci SMALL, zabalený do funkce SUM. V obecné formě vzorce (výše) představuje range1 rozsah buněk ve srovnání s kritérii , range2 obsahuje číselné hodnoty, ze kterých se načítají spodní hodnoty, a N představuje „n-tý“.
V příkladu obsahuje aktivní buňka tento vzorec:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Kde barva je pojmenovaný rozsah B5: B12 a hodnota je pojmenovaný rozsah C5: C12.
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Vysvětlení
Ve své nejjednodušší podobě vrátí funkce SMALL hodnotu „N-té nejmenší“ v rozsahu s touto konstrukcí:
=SMALL (range,N)
Například:
=SMALL (C5:C12,2)
vrátí 2. nejmenší hodnotu v rozsahu C5: C12, což je 5 v zobrazeném příkladu.
Pokud však jako druhý argument zadáte SMALL „konstantu pole“ (např. Konstantu ve tvaru (1,2,3)) SMALL, SMALL vrátí pole výsledků namísto jediného výsledku. Vzorec tedy:
=SMALL (C5:C12, (1,2,3))
vrátí 1., 2. a 3. nejmenší hodnotu C5: C12 v takovém poli: (4,5,7).
Trik tedy spočívá v filtrování hodnot na základě barev před spuštěním MALÉHO. Děláme to s výrazem založeným na funkci IF:
IF(color=E5,value)
Tím se vytvoří pole hodnot přiváděných do SMALL. V podstatě se do pole dostanou pouze hodnoty spojené s červenou barvou. Tam, kde se barva rovná „červené“, obsahuje pole číslo a kde barva není červená, obsahuje pole FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
Funkce SMALL ignoruje FALSE hodnoty a vrací 3 nejmenší hodnoty v poli: (4,8,10). Funkce SUM vrátí konečný výsledek, 22.
