Note: The other languages of the website are Google-translated. Back to English

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

Rádi byste svou každodenní práci dokončili rychle a dokonale? Kutools pro Excel přináší 300 výkonných pokročilých funkcí (kombinovat sešity, součet podle barvy, rozdělit obsah buněk, převést datum atd.) a uložit

80%
čas pro tebe.

  • Určeno pro
    1500
    pracovní scénáře, vám pomůže vyřešit
    80%
     Problémy s Excelem.
  • Omezte každý den tisíce kliknutí na klávesnici a myš a ulehčete svým unaveným očím a rukám.
  • Staňte se odborníkem na Excel za 3 minuty. Už si nemusíte pamatovat žádné bolestivé vzorce a kódy VBA.
  • 30
    -denní neomezená bezplatná zkušební verze. 60denní záruka vrácení peněz. Bezplatný upgrade a podpora po dobu 2 let.
Pás karet aplikace Excel (s nainstalovaným programem Kutools pro Excel)

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 tím
    50%
    při prohlížení a úpravách více dokumentů.
  • Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Firefox a Nový Internet Explorer.
Snímek obrazovky aplikace Excel (s nainstalovanou kartou Office)
Komentáře (0)
Zatím žádné hodnocení. Buďte první, kdo ohodnotí!
Zatím zde nejsou žádné komentáře
Zanechat své připomínky
Odesílání jako host
×
Ohodnoťte tento příspěvek:
0   Postavy
Doporučená umístění

Sociální sítě

Copyright © 2009 - www.extendoffice.com. | Všechna práva vyhrazena. Poháněno ExtendOffice. | |. | Sitemap
Microsoft a logo Office jsou ochranné známky nebo registrované ochranné známky společnosti Microsoft Corporation ve Spojených státech a / nebo jiných zemích.
Chráněno Sectigo SSL