Součet nejmenších nebo dolních hodnot N na základě kritérií v aplikaci Excel
V předchozím tutoriálu jsme diskutovali jak sečíst nejmenší hodnoty n v datovém rozsahu. V tomto článku provedeme další pokročilou operaci – sečteme nejnižší hodnoty n na základě jednoho nebo více kritérií v Excelu.
Součet nejmenších nebo dolních hodnot N na základě kritérií v aplikaci Excel
Předpokládejme, že mám řadu dat, jak je zobrazeno na níže uvedeném snímku obrazovky, nyní chci sečíst nejnižší 3 objednávky produktu Apple.
Chcete-li v aplikaci Excel sečíst dolních n hodnot v rozsahu s kritérii, můžete vytvořit maticový vzorec pomocí funkcí SUM, SMALL a IF. Obecná syntaxe je:
Array formula, should press Ctrl + Shift + Enter keys together.
- range=criteria: Rozsah buněk, které odpovídají konkrétním kritériím;
- values: Seznam, který obsahuje spodních n hodnot, které chcete sečíst;
- N: N-tá spodní hodnota.
Chcete-li vyřešit výše uvedený problém, použijte níže uvedený maticový vzorec do prázdné buňky:
A pak stiskněte Ctrl + Shift + Enter klávesy dohromady, abyste získali správný výsledek, jak je znázorněno na níže uvedeném snímku obrazovky:
Vysvětlení vzorce:
=SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))
- IF(($A$2:$A$14=D2), $B$2:$B$14): Pokud se produkt v rozsahu A2:A14 rovná „Apple“, vrátí relativní číslo ze seznamu objednávek (B2:B14); Pokud produkt není „Apple“, zobrazí se FALSE. Výsledek dostanete takto: {800;FALSE;FALSE;FALSE;1000;230;FALSE;FALSE;1600;FALSE;900;FALSE;500}.
- SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}): Tato funkce SMALL bude ignorovat hodnoty FALSE a vrátí 3 spodní hodnoty v poli, takže výsledek je tento: {230,500,800 XNUMX XNUMX}.
- SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))=SUM({230,500,800}): Nakonec funkce SUM sečte čísla v poli a získá výsledek: 1530.
Tipy: Vypořádejte se se dvěma nebo více podmínkami:
Pokud potřebujete sečíst spodních n hodnot na základě dvou nebo více kritérií, stačí přidat další rozsah a kritéria pomocí znaku * v rámci funkce IF takto:
Array formula, should press Ctrl + Shift + Enter keys together.
- Range1=criteria1: První rozsah buněk, který odpovídá prvnímu kritériu;
- Range2=criteria2: Druhý rozsah buněk, který odpovídá druhému kritériu;
- Range3=criteria3: Třetí rozsah buněk, které odpovídají třetímu kritériu;
- values: Seznam, který obsahuje spodních n hodnot, které chcete sečíst;
- N: N-tá spodní hodnota.
Například chci sečíst 3 poslední objednávky produktu Apple, který prodává Kerry, použijte prosím níže uvedený vzorec:
A pak stiskněte Ctrl + Shift + Enter klíče dohromady, abyste získali výsledek, jak potřebujete:
Použitá relativní funkce:
- SOUČET:
- Funkce SUM přidává hodnoty. Můžete přidat jednotlivé hodnoty, odkazy na buňky nebo rozsahy nebo kombinaci všech tří.
- SMALL:
- Funkce Excel SMALL vrací číselnou hodnotu na základě její pozice v seznamu, když je seřazena podle hodnoty ve vzestupném pořadí.
- IF:
- Funkce KDYŽ testuje konkrétní podmínku a vrací odpovídající hodnotu, kterou zadáte pro hodnotu TRUE nebo FALSE.
Další články:
- Součet nejmenších nebo dolních hodnot N
- V Excelu je pro nás snadné sečíst rozsah buněk pomocí funkce SUM. Někdy může být nutné sečíst nejmenší nebo dolní 3, 5 nebo n čísel v datovém rozsahu, jak je znázorněno na obrázku níže. V tomto případě vám SUMPRODUCT spolu s funkcí SMALL může pomoci vyřešit tento problém v Excelu.
- Mezisoučet fakturovaných částek podle věku v Excelu
- Sečtení fakturovaných částek na základě věku, jak je uvedeno níže, může být v Excelu běžným úkolem, tento tutoriál vám ukáže, jak sečíst mezisoučet fakturovaných částek podle věku pomocí normální funkce SUMIF.
- Sečíst všechny buňky čísel Ignorování chyb
- Při sčítání rozsahu čísel, které obsahují nějaké chybové hodnoty, nebude normální funkce SUM fungovat správně. Chcete-li sečíst pouze čísla a přeskočit chybové hodnoty, může vám pomoci funkce AGGREGATE nebo SUM spolu s funkcemi IFERROR.
Nejlepší kancelářské nástroje produktivity
Kutools pro Excel - pomůže vám vyniknout před davem
Kutools pro Excel se může pochlubit více než 300 funkcemi, Zajištění toho, že to, co potřebujete, je jen jedno kliknutí...
Záložka Office - Povolte čtení a úpravy na záložkách v Microsoft Office (včetně Excelu)
- Jednu sekundu přepnete mezi desítkami otevřených dokumentů!
- Snižte stovky kliknutí myší každý den, sbohem s myší rukou.
- Zvyšuje vaši produktivitu o 50% při prohlížení a úpravách více dokumentů.
- Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Edge a Firefox.