V tomto článku se naučíme, jak shrnout horní nebo dolní hodnoty N dané řady čísel v aplikaci Excel. Pokud chcete sečíst top 4, top 5 nebo top N hodnot v daném rozsahu, pomůže vám to tento článek.
Součet TOP N hodnot
Pro tento článek budeme muset použít funkci SUMPRODUCT. Nyní z těchto funkcí vytvoříme vzorec. Zde dostaneme rozsah a potřebujeme dosáhnout 5 nejlepších hodnot v rozsahu a získat součet hodnot.
Obecný vzorec:
= SUMPRODUCT (VELKÝ (rozsah, {1, 2,…., N}})
rozsah: rozsah hodnot
Hodnoty: čísla oddělená čárkami, například pokud chcete najít první 3 hodnoty, použijte {1, 2, 3}.
Příklad: Součet top 5 hodnot rozsahu
Vyzkoušejte tento vzorec spuštěním na níže uvedeném příkladu.
Zde máme rozsah hodnot od A1: A23.
Rozsah A1: A23 obsahuje hodnoty a je pojmenován jako hodnoty.
Nejprve musíme pomocí funkce LARGE najít prvních pět hodnot a poté provést součet operací na těchto 5 hodnotách. Nyní použijeme následující vzorec k získání součtu prvních 5 hodnot.
Použijte vzorec:
= SUMPRODUCT (VELKÝ (rozsah, {1, 2, 3, 4, 5}))
Vysvětlení:
- Funkce LARGE vrací prvních 5 číselných hodnot a vrací pole do funkce SUMPRODUCT.
= SUMPRODUCT ({149, 123, 100, 87, 85}))
- Funkce SUMPRODUCT získá řadu 5 nejlepších hodnot, které je třeba shrnout.
Zde je rozsah uveden jako pojmenovaný rozsah. Stisknutím klávesy Enter získáte součet 5 nejlepších čísel.
Jak vidíte na výše uvedeném snímku, tento součet je 544. Součet hodnot 149 + 123 + 100 + 87 + 85 = 544.
Výše uvedený postup se používá k výpočtu součtu několika čísel shora. Ale počítat pro n (velký) počet hodnot v dlouhém rozsahu.
Použijte vzorec:
= SUMPRODUCT (VELKÝ (rozsah, ŘADA (NEPŘÍMÉ („1:10“))
Zde generujeme součet 10 nejlepších hodnot získáním pole od 1 do 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} pomocí funkcí ROW & INDIRECT Excel.
Zde jsme získali součet 10 nejlepších čísel {149; 123; 100; 87; 85; 82; 61; 58; 54; 51}), což má za následek 850.
Součet spodních hodnot N.
Jak problém vyřešit?
Obecný vzorec:
= SUMPRODUCT (MALÝ (rozsah, {1, 2,…., N}})
Rozsah: rozsah hodnot
Hodnoty: čísla oddělená čárkami, například pokud chcete najít spodní 3 hodnoty, použijte {1, 2, 3}.
Příklad:
Pochopit to všechno může být matoucí. Pojďme tedy tento vzorec otestovat spuštěním na níže uvedeném příkladu.
Zde máme rozsah hodnot od A1: A23.
Zde je rozsah, který je uveden pomocí nástroje Excel pojmenovaného rozsahu.
Za prvé, musíme najít pět nejnižších hodnot pomocí funkce SMALL a poté provést součet operací na těchto 5 hodnotách. Nyní použijeme následující vzorec k získání součtu
Použijte vzorec:
= SUMPRODUCT (MALÝ (rozsah, {1, 2, 3, 4, 5}))
Vysvětlení:
- Funkce SMALL vrací spodních 5 číselných hodnot a vrací pole do funkce SUMPRODUCT.
= SUMPRODUCT ({23, 27, 28, 28, 30}))
- Funkce SUMPRODUCT získá řadu spodních 5 hodnot, které je třeba shrnout.
Zde je rozsah uveden jako pojmenovaný rozsah. Stisknutím klávesy Enter získáte součet spodních 5 čísel.
Jak vidíte na výše uvedeném snímku, součet je 136. Součet hodnot 23 + 27 + 28 + 28 + 30 = 136.
Výše uvedený postup se používá k výpočtu součtu několika čísel zespodu. Ale počítat pro n (velký) počet hodnot v dlouhém rozsahu.
Použijte vzorec:
= SUMPRODUCT (MALÝ (rozsah, ŘADA (NEPŘÍMÝ („1:10“)))
Zde generujeme součet spodních 10 hodnot získáním pole 1 až 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} pomocí funkcí ROW & INDIRECT Excel.
Zde máme součet spodních 10 čísel, jejichž výsledkem bude 307.
Zde je několik pozorovacích poznámek uvedených níže.
Poznámky:
- Vzorec funguje pouze s čísly.
- Vzorec funguje pouze v případě, že ve vyhledávací tabulce nejsou žádné duplikáty
- Funkce SUMPRODUCT považuje nečíselné hodnoty (jako text abc) a chybové hodnoty (jako #ČÍSLO!, #NULL!) Za nulové hodnoty.
- Funkce SUMPRODUCT považuje logickou hodnotu TRUE za 1 a False za 0.
- Pole argumentů musí mít stejnou délku jako funkce.
Doufám, že tento článek o tom, jak vrátit součet horních n hodnot nebo dolních n hodnot v aplikaci Excel, je vysvětlující. Další články o funkcích SUMPRODUCT najdete zde. Sdílejte prosím svůj dotaz níže v poli pro komentáře. Pomůžeme vám.
Jak používat funkci SUMPRODUCT v Excelu: Vrací SUMU po vynásobení hodnot ve více polích v Excelu.
SUM, pokud je datum mezi : Vrátí SUMU hodnot mezi danými daty nebo obdobím v Excelu.
Součet, pokud je datum větší než dané datum: Vrátí SUMU hodnot po daném datu nebo období v aplikaci Excel.
2 způsoby součtu podle měsíce v Excelu: Vrátí SUMU hodnot v rámci daného konkrétního měsíce v aplikaci Excel.
Jak sečíst více sloupců s podmínkou: Vrátí SUMU hodnot napříč více sloupci s podmínkou v Excelu
Jak používat zástupné znaky v aplikaci Excel : Počítejte buňky odpovídající frázím pomocí zástupných znaků v aplikaci Excel
Populární články
50 Zkratka aplikace Excel pro zvýšení produktivity
Upravte rozevírací seznam
Absolutní reference v Excelu
Pokud s podmíněným formátováním
Pokud se zástupnými znaky
Vlookup podle data
Převod palců na stopy a palce v aplikaci Excel 2016
Připojte jméno a příjmení v Excelu
Počítejte buňky, které odpovídají A nebo B
