V předchozím článku jsme se dozvěděli, jak sečíst horní nebo dolní hodnoty N. V tomto článku se snažíme shrnout horní nebo dolní hodnoty N s kritériem.
Součet TOP N hodnot s kritérii
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 dostáváme rozsah a kritéria. Potřebujeme získat prvních 5 hodnot v rozsahu a získat součet hodnot na základě daných kritérií.
Obecný vzorec:
= SUMPRODUCT (VELKÝ ((seznam = kritéria) * (rozsah), {1, 2,…., N}})
seznam: seznam kritérií
Kritéria: kritéria, která mají odpovídat
rozsah: rozsah hodnot
hodnoty: čísla oddělená čárkami, například pokud chcete najít první 3 hodnoty, použijte {1, 2, 3}.
Příklad:
Zde máme hodnoty datové sady od A1: D50.
Za prvé, musíme najít prvních pět hodnot pomocí funkce LARGE, která odpovídá městu „Boston“, 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 (VELKÝ ((Město = "Boston") * (množství), {1, 2, 3, 4, 5}))
Vysvětlení:
- City „Boston“ odpovídá uvedenému sortimentu City. Tím se vrátí pole true a false.
- Funkce LARGE vrací prvních 5 číselných hodnot z rozsahu množství a vrací pole do funkce SUMPRODUCT.
= SUMPRODUCT {193, 149, 138, 134, 123}
- Funkce SUMPRODUCT získá řadu top 5 hodnot, přičemž pole top 5 čísel vrací SUM těchto čísel.
Zde je jako pojmenovaný rozsah uveden rozsah města a množství. Stisknutím klávesy Enter získáte součet 5 nejlepších čísel.
Jak vidíte na výše uvedeném snímku, je tento součet 737. Součet hodnot 193 + 149 + 138 + 134 + 123 = 737.
Výše uvedené hodnoty můžete zkontrolovat v datové sadě pomocí možnosti filtru Excel. Použijte filtr na hlavičku Město a množství a klikněte na tlačítko se šipkou na hlavičce města, která se zobrazí. Postupujte podle níže uvedených kroků.
Kroky:
- Vyberte buňku záhlaví města. Použijte filtr pomocí zkratky Ctrl + Shift + L
- Klikněte na šipku, která se zobrazí jako možnost filtru.
- Vyberte možnost (Vybrat vše).
- Vyberte pouze město Boston.
- Nyní vyberte záhlaví množství.
- Seřaďte seznam od největšího po nejmenší a můžete zobrazit všech 5 nejlepších hodnot, které jsme vypočítali pomocí vzorce.
Jak vidíte ve výše uvedeném gifu, všech 5 hodnot, které odpovídají daným kritériím. To také znamená, že vzorec funguje dobře, aby získal počet těchto hodnot
VELKÁ N čísla
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Ý ((Město = "Boston") * (množství), ŘÁDEK (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 máme součet 10 nejlepších čísel, což má za následek 1147.
Součet spodních N hodnot s kritérii
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:
{= SUMA (MALÁ (IF (město = "Boston", množství), {1, 2, 3, 4, 5}))}
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: D50.
Zde je uveden rozsah města a množství jako nástroj Excel s pojmenovaným rozsahem.
Nejprve musíme najít pět nejnižších hodnot pomocí funkce SMALL, která odpovídá kritériím, 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:
{= SUMA (MALÁ (IF (město = "Boston", množství), {1, 2, 3, 4, 5}))}}
NEPOUŽÍVEJTE kudrnaté závorky ručně. Kudrnaté závorky aplikované pomocí Ctrl + Shift + Enter místo spravedlivého Vstupte.
Vysvětlení:
- Funkce SMALL s funkcí IF vrací dolních 5 číselných hodnot, které odpovídají City "Boston" a vrací pole do funkce SUM.
= SUMA ({23, 27, 28, 28, 30}))
- Funkce SUMA získá pole dolních 5 hodnot, které má pole dolních 5 čísel, vrátí SUM těchto čísel použitých s CTRL + SHIFT + ENTER.
Zde je jako pojmenovaný rozsah uveden rozsah města a množství. lis Ctrl + Shift + Enter získat součet spodních 5 čísel, protože toto je maticový vzorec.
Jak vidíte na výše uvedeném snímku, tato částka je 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:
{ = SUM (MALÝ (IF (město = "Boston", množství), ŘÁDEK (NEPŘÍMÝ ("1:10")))) }
NEPOUŽÍVEJTE kudrnaté závorky ručně. Místo klávesy Enter použijte Ctrl + Shift + Enter.
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 155.
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 hodnot 5 nejlepších nebo 5 nejnižších hodnot s kritérii v Excelu, 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.
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
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
