Obecný vzorec
=SUMPRODUCT(LARGE(rng,(1,2,N)))
souhrn
Chcete-li sečíst nejvyšší hodnoty v rozsahu, můžete použít vzorec založený na funkci VELKÝ, zabalený do funkce SUMPRODUCT. V obecné formě vzorce (výše) představuje rng rozsah buněk, které obsahují číselné hodnoty a N představuje myšlenku N- té hodnoty.
V příkladu obsahuje aktivní buňka tento vzorec:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Vysvětlení
V nejjednodušší podobě vrátí funkce LARGE hodnotu „N-té největší“ v rozsahu. Například vzorec:
=LARGE(B4:B13, 2)
vrátí druhou největší hodnotu v rozsahu B4: B13, což je ve výše uvedeném příkladu číslo 9.
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(B4:B13,(1,2,3))
vrátí 1., 2. a 3. největší hodnotu v rozsahu B4: B13. Ve výše uvedeném příkladu, kde B4: B13 obsahuje čísla 1-10, bude výsledkem LARGE pole (8,9,10). SUMPRODUCT poté sečte čísla v tomto poli a vrátí celkem, což je 27.
SUM namísto SUMPRODUCT
SUMPRODUCT je flexibilní funkce, která vám umožňuje používat odkazy na buňky pro k uvnitř funkce LARGE.
Pokud však používáte jednoduchou pevně konstantní maticovou konstantu jako (1,2,3), můžete použít pouze funkci SUM:
=SUM(LARGE(B4:B13,(1,2,3)))
Všimněte si, že tento vzorec musíte zadat jako maticový vzorec, pokud používáte odkazy na buňky, a ne konstantu pole pro k uvnitř LARGE.
Když se N zvětší
Když se N zvětší, bude únavné ručně vytvářet maticovou konstantu - Pokud chcete ve velkém seznamu sčítat prvních 20 nebo 30 hodnot, vypsá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 pokud chcete SUMIT 20 nejlepších hodnot v rozsahu zvaném „rng“, můžete napsat vzorec takto:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Proměnná N
Při nedostatečných datech může pevné N způsobit chyby. V takovém případě můžete zkusit následující vzorec:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Zde používáme MIN s COUNT k součtu prvních 3 hodnot nebo počtu hodnot, pokud je menší než 3.
