Přejít k hlavnímu obsahu

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:

{=SUM(SMALL(IF(range=criteria,values),{1,2,N}))}
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:

=SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))

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:

{=SUM(SMALL(IF((range1=criteria1)*(range2=criteria2) *(range3=criteria3)…,values),{1,2,N}))}
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:

=SUM(SMALL(IF(($A$2:$A$14=E2)*($B$2:$B$14=F2), $C$2:$C$14),{1,2,3}))

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

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty  |  Odstranit prázdné řádky  |  Kombinujte sloupce nebo buňky bez ztráty dat  |  Kolo bez vzorce ...
Super VLookup: Více kritérií  |  Vícenásobná hodnota  |  Přes Multi-Sheets  |  Fuzzy vyhledávání...
Adv. Rozbalovací seznam: Snadno rozevírací seznam  |  Závislý rozbalovací seznam  |  Vícenásobný výběr rozevíracího seznamu...
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  Porovnejte sloupce s Vyberte stejné a různé buňky ...
Doporučené funkce: Zaměření mřížky  |  Návrhové zobrazení  |  Velký Formula Bar  |  Správce sešitů a listů | Knihovna zdrojů (Automatický text)  |  Výběr data  |  Zkombinujte pracovní listy  |  Šifrovat/dešifrovat buňky  |  Odesílat e-maily podle seznamu  |  Super filtr  |  Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky ...)  |  50+ Graf Typ nemovitosti (Ganttův diagram ...)  |  40+ Praktické Vzorce (Vypočítejte věk na základě narozenin ...)  |  19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty ...)  |  12 Konverze Tools (Čísla na slova, Přepočet měny ...)  |  7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky Excelu ...)  |  ... a více

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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations