V tomto článku se naučíme, jak sečíst N největších hodnot v rozsahu v aplikaci Excel.
Scénář:
Jednoduše řečeno, při práci s datovými čísly je někdy dána podmínka, tj. Když potřebujeme získat součet čísel prvních 5 čísel pole nebo dolní části pole. Řešení tohoto problému můžete snadno provést pomocí funkcí aplikace Excel, jak je vysvětleno níže.
Součet TOP N hodnot
Jak problém vyřešit?
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:
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 uveden jako použití pojmenovaného nástroje Excel.
Za prvé, musíme najít prvních pět hodnot pomocí funkce LARGE a poté provést operaci součtu nad těmito 5 hodnotami. Nyní použijeme následující vzorec k získání součtu
Použijte vzorec:
| = SUMPRODUCT (VELKÝ (rozsah, {1, 2, 3, 4, 5})) |
Vysvětlení:
- Funkce LARGE vrací prvních 5 číselných hodnot a vrátí pole do funkce SUMPRODUCT.
= SUMPRODUCT ({149, 123, 100, 87, 85}))
- Funkce SUMPRODUCT získá pole 5 nejlepších hodnot, které obsahuje pole 5 nejlepších čísel a vrátí SUM těchto hodnot.
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?
Pro tento článek budeme muset použít funkci SUMPRODUCT. Nyní z těchto funkcí vytvoříme vzorec. Zde dostaneme rozsah a potřebujeme snížit 5 hodnot v rozsahu a získat součet hodnot.
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 uveden jako použití pojmenovaného nástroje Excel.
Za prvé, musíme najít pět spodní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á pole spodních 5 hodnot, které má pole spodních 5 čísel vrací SUM těchto hodnot.
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 jsme získali součet spodních 10 čísel, což vede k 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, jinak funkce.
Doufám, že tento článek o tom, jak sečíst N největších hodnot v rozsahu v aplikaci Excel, je vysvětlující. Další články o výpočtu hodnot a souvisejících vzorcích Excelu najdete zde. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail.
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
Oblíbené články:
Jak používat funkci IF v aplikaci Excel : Příkaz IF v aplikaci Excel zkontroluje podmínku a vrátí konkrétní hodnotu, pokud je podmínka PRAVDA, nebo vrátí jinou konkrétní hodnotu, pokud NEPRAVDA.
Jak používat funkci VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.