Obecný vzorec
=SUMPRODUCT(SMALL(rng,(1,2,n)))
souhrn
Chcete-li sečíst nejnižší hodnoty n v rozsahu, můžete použít vzorec založený na funkci MALÉ a funkci SUMPRODUCT. V obecné formě vzorce (výše) představuje rng rozsah buněk, které obsahují číselné hodnoty a n představuje počet nejnižších hodnot, které se mají sečíst. V zobrazeném příkladu obsahuje E5 tento vzorec:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
který vrací součet tří nejmenších hodnot v B5: B14, 60
Vysvětlení
Ve své nejjednodušší formě vrátí funkce SMALL hodnotu „n-té nejmenší“ v rozsahu. Například:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
Pokud však jako druhý argument zadáte SMALL konstantu pole (např. Konstantu ve tvaru (1,2,3)) SMALL, vrátí SMALL místo jednoho výsledku pole výsledků. Například:
=SMALL(A1:A10,(1,2,3))
vrátí 1., 2. a 3. nejmenší hodnoty v rozsahu A1: A10.
Při práci zevnitř ven v uvedeném příkladu vrátí funkce SMALL 3 nejmenší hodnoty v rozsahu B5: B14:
=SMALL(B4:B14,(1,2,3))
Výsledkem je pole jako toto:
(10,20,30)
Toto pole se vrací přímo do funkce SUMPRODUCT, která sčítá čísla a vrací součet:
SUMPRODUCT((10,20,30)) // returns 60
Pole vzorce se SUMOU
Je běžné používat SUMPRODUCT, jak je uvedeno výše, protože dokáže nativně zpracovat pole bez zadávání jako maticový vzorec. Můžete však také napsat maticový vzorec s funkcí SUM takto:
(=SUM(SMALL(B4:B13,(1,2,3))))
Toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter, kromě Excel 365.
Když se n zvětší
Když se n zvětší, bude únavné vytvářet konstantu pole ručně - vypisování konstanty pole s 20 nebo 30 položkami bude trvat dlouho. V tomto případě můžete použít zástupce pro vytvoření konstanty pole, která používá funkce ROW a INDIRECT. Například pro SUMA spodních 20 hodnot v rozsahu zvaném "rng" můžete napsat vzorec takto:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
Zde INDIRECT převede řetězec "1:20" na rozsah 1:20, který je vrácen přímo do SMALL.
Proměnná n
Chcete-li nastavit vzorec, kde n je proměnná v jiné buňce, můžete zřetězit uvnitř INDIRECT. Například pokud A1 obsahuje N, můžete použít:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
To umožňuje uživateli změnit hodnotu n přímo v listu.
