V predchádzajúcom článku sme sa dozvedeli, ako zhrnúť horné alebo dolné hodnoty N. V tomto článku sa pokúšame zhrnúť horné alebo dolné hodnoty N s kritériami.
Súčet TOP N hodnôt s kritériami
Ako problém vyriešiť?
V tomto článku budeme musieť používať funkciu SUMPRODUCT. Teraz z týchto funkcií vytvoríme vzorec. Tu uvádzame rozsah a kritériá. Musíme získať 5 najlepších hodnôt v rozsahu a získať súčet hodnôt na základe daných kritérií.
Všeobecný vzorec:
= SUMPRODUCT (VEĽKÝ ((zoznam = kritériá) * (rozsah), {1, 2,…., N}})
zoznam: zoznam kritérií
Kritériá: kritériá, ktoré sa majú zhodovať
rozsah: rozsah hodnôt
hodnoty: čísla oddelené čiarkami, napríklad ak chcete nájsť prvé 3 hodnoty, použite {1, 2, 3}.
Príklad:
Tu máme hodnoty množiny údajov od A1: D50.
Po prvé, musíme nájsť prvých päť hodnôt pomocou funkcie LARGE, ktorá zodpovedá mestu „Boston“, a potom vykonať súhrnnú operáciu pre týchto 5 hodnôt. Teraz použijeme nasledujúci vzorec na získanie súčtu
Použite vzorec:
= SUMPRODUCT (VEĽKÝ ((Mesto = "Boston") * (množstvo), {1, 2, 3, 4, 5}))
Vysvetlenie:
- City „Boston“ sa zhoduje so spomínaným sortimentom City. To vráti pole pravdivých a nepravdivých.
- Funkcia LARGE vracia prvých 5 číselných hodnôt z rozsahu množstva a vracia pole do funkcie SUMPRODUCT.
= SUMPRODUCT {193, 149, 138, 134, 123}
- Funkcia SUMPRODUCT získa pole 5 najlepších hodnôt, pričom pole prvých 5 čísel vracia SUM týchto čísel.
Tu je ako pomenovaný rozsah uvedený rozsah mesta a množstva. Stlačením klávesu Enter získate SÚČET 5 najlepších čísel.
Ako vidíte na vyššie uvedenom obrázku, tento súčet je 737. Súčet hodnôt 193 + 149 + 138 + 134 + 123 = 737.
Vyššie uvedené hodnoty môžete skontrolovať v množine údajov pomocou možnosti filtra Excel. Použite filter na hlavičku Mesto a množstvo a kliknite na tlačidlo šípky na hlavičke mesta, ktorá sa zobrazí. Postupujte podľa nižšie uvedených krokov.
Kroky:
- Vyberte bunku hlavičky mesta. Použite filter pomocou skratky Ctrl + Shift + L
- Kliknite na šípku, ktorá sa zobrazí ako možnosť filtra.
- Vyberte možnosť (Vybrať všetko).
- Vyberte iba mesto Boston.
- Teraz vyberte hlavičku množstva.
- Zoraďte zoznam od najväčšieho po najmenší a môžete si pozrieť všetkých 5 prvých hodnôt, ktoré sme vypočítali pomocou vzorca.
Ako vidíte na vyššie uvedenom gife, všetkých 5 hodnôt, ktoré zodpovedajú daným kritériám. To tiež znamená, že vzorec funguje dobre na získanie počtu týchto hodnôt
VEĽKÉ N čísla
Vyššie uvedený postup sa používa na výpočet súčtu niekoľkých čísel zhora. Ale počítať pre n (veľký) počet hodnôt v dlhom rozsahu.
Použite vzorec:
= SUMPRODUCT (VEĽKÝ ((Mesto = "Boston") * (množstvo), RIADOK (NEPRIAMY ("1:10"))
Tu generujeme súčet 10 najlepších hodnôt získaním poľa od 1 do 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} pomocou funkcií ROW & INDIRECT Excel.
Tu máme súčet 10 najlepších čísel, ktorých výsledkom je 1147.
Súčet dolných N hodnôt s kritériami
Ako problém vyriešiť?
V tomto článku budeme musieť používať funkciu SUMPRODUCT. Teraz z týchto funkcií vytvoríme vzorec. Tu dostaneme rozsah a musíme znížiť 5 hodnôt v rozsahu a získať súčet hodnôt.
Všeobecný vzorec:
{= SUM (MALÝ (IF (mesto = "Boston", množstvo), {1, 2, 3, 4, 5}))}
Rozsah: rozsah hodnôt
Hodnoty: čísla oddelené čiarkami, napríklad ak chcete nájsť 3 najnižšie hodnoty, použite {1, 2, 3}.
Príklad:
Toto všetko môže byť mätúce na pochopenie. Otestujme teda tento vzorec spustením na príklade uvedenom nižšie.
Tu máme rozsah hodnôt od A1: D50.
Tu je uvedený rozsah mesta a množstva ako použitie nástroja Excel s pomenovaným rozsahom.
Po prvé, musíme nájsť päť najnižších hodnôt pomocou funkcie SMALL, ktorá zodpovedá kritériám, a potom vykonať súhrnnú operáciu pre týchto 5 hodnôt. Teraz použijeme nasledujúci vzorec na získanie súčtu
Použite vzorec:
{= SUM (MALÝ (IF (mesto = "Boston", množstvo), {1, 2, 3, 4, 5}))}}
NEPOUŽÍVAJTE kučeravé zátvorky ručne. Kučeravé zátvorky aplikované pomocou Ctrl + Shift + Enter namiesto spravodlivých Zadajte.
Vysvetlenie:
- Funkcia SMALL s funkciou IF vracia spodných 5 číselných hodnôt, ktoré zodpovedajú mestu „Boston“ a vracia pole do funkcie SUM.
= SUM ({23, 27, 28, 28, 30}))
- Funkcia SUMA získa pole dolných 5 hodnôt, ktoré má pole dolných 5 čísel, vráti SÚČET týchto čísel použitých s klávesmi CTRL + SHIFT + ENTER.
Tu je ako pomenovaný rozsah uvedený rozsah mesta a množstva. Stlačte Ctrl + Shift + Enter na získanie SUMU spodných 5 čísel, pretože toto je vzorec poľa.
Ako vidíte na vyššie uvedenom obrázku, táto suma je 136.
Vyššie uvedený postup sa používa na výpočet súčtu niekoľkých čísel zospodu. Ale počítať pre n (veľký) počet hodnôt v dlhom rozsahu.
Použite vzorec:
{ = SUM (MALÝ (IF (mesto = "Boston", množstvo), RIADOK (NEPRIAMY ("1:10")))) }
NEPOUŽÍVAJTE kučeravé zátvorky ručne. Namiesto klávesu Enter použite Ctrl + Shift + Enter.
Tu generujeme súčet najnižších hodnôt 10 získaním poľa od 1 do 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} pomocou funkcií ROW & INDIRECT Excel.
Tu máme súčet spodných 10 čísel, ktorých výsledkom bude 155.
Tu je niekoľko pozorovacích poznámok uvedených nižšie.
Poznámky:
- Vzorec funguje iba s číslami.
- Vzorec funguje iba vtedy, ak vo vyhľadávacej tabuľke nie sú žiadne duplikáty
- Funkcia SUMPRODUCT považuje nečíselné hodnoty (ako text abc) a chybové hodnoty (ako #ČÍSLO!, #NULL!) Za nulové hodnoty.
- Funkcia SUMPRODUCT považuje logickú hodnotu TRUE za 1 a False za 0.
- Pole argumentov musí mať rovnakú dĺžku ako funkcia.
Dúfam, že tento článok o tom, ako vrátiť súčet prvých 5 hodnôt alebo najnižších hodnôt s kritériami v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o funkciách SUMPRODUCT. Podeľte sa o svoj dotaz nižšie v poli pre komentáre. Pomôžeme vám.
Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku
Ako používať funkciu SUMPRODUCT v programe Excel: Vráti SUMU po vynásobení hodnôt vo viacerých poliach v programe Excel.
SUM, ak je dátum medzi : Vráti SÚčet hodnôt medzi danými dátumami alebo obdobím v programe Excel.
Suma, ak je dátum väčší ako daný dátum: Vráti SÚčet hodnôt po danom dátume alebo období v programe Excel.
2 spôsoby súčtu podľa mesiacov v programe Excel: Vráti SÚčet hodnôt v rámci daného konkrétneho mesiaca v programe Excel.
Ako sčítať viac stĺpcov s podmienkou: Vráti SÚčet hodnôt vo viacerých stĺpcoch s podmienkou v programe Excel
Ako používať zástupné znaky v programe Excel : Spočítajte bunky zodpovedajúce frázam pomocou zástupných znakov v programe Excel
Populárne články
50 Skratka pre Excel, ktorá zvýši vašu produktivitu
Upravte rozbaľovací zoznam
Absolútna referencia v Exceli
Ak s podmieneným formátovaním
Ak so zástupnými znakmi
Vlookup podľa dátumu
V programe Excel 2016 preveďte palce na nohy a palce
Pripojte meno a priezvisko v Exceli
Spočítajte bunky, ktoré sa zhodujú s A alebo B