V tomto článku sa naučíme, ako zosumarizovať vrchné alebo spodné hodnoty N pre dané číslo v programe Excel. Ak chcete sčítať top 4, top 5 alebo top N hodnôt v danom rozsahu, pomôže vám to tento článok.
Súčet TOP N hodnôt
V tomto článku budeme musieť používať funkciu SUMPRODUCT. Teraz z týchto funkcií vytvoríme vzorec. Tu dostaneme rozsah a musíme top 5 hodnôt v rozsahu a získať súčet hodnôt.
Všeobecný vzorec:
= SUMPRODUCT (VEĽKÝ (rozsah, {1, 2,…., N}})
rozsah: rozsah hodnôt
Hodnoty: čísla oddelené čiarkami. Ak chcete nájsť prvé tri hodnoty, použite {1, 2, 3}.
Príklad: Suma Top 5 hodnôt rozsahu
Otestujme tento vzorec spustením na príklade uvedenom nižšie.
Tu máme rozsah hodnôt od A1: A23.
Rozsah A1: A23 obsahuje hodnoty a je pomenovaný ako hodnoty.
Po prvé, musíme nájsť prvých päť hodnôt pomocou funkcie LARGE a potom vykonať súčet operácií pre týchto 5 hodnôt. Teraz použijeme nasledujúci vzorec na získanie súčtu prvých 5 hodnôt.
Použite vzorec:
= SUMPRODUCT (VEĽKÝ (rozsah, {1, 2, 3, 4, 5}))
Vysvetlenie:
- Funkcia LARGE vracia prvých 5 číselných hodnôt a vracia pole do funkcie SUMPRODUCT.
= SUMPRODUCT ({149, 123, 100, 87, 85}))
- Funkcia SUMPRODUCT získava súhrn 5 najlepších hodnôt.
Tu je rozsah uvedený ako pomenovaný rozsah. 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 544. Súčet hodnôt 149 + 123 + 100 + 87 + 85 = 544.
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Ý (rozsah, 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 sme dostali súčet 10 najlepších čísel {149; 123; 100; 87; 85; 82; 61; 58; 54; 51}), čo má za následok 850.
Súčet dolných hodnôt N.
Ako problém vyriešiť?
Všeobecný vzorec:
= SUMPRODUCT (MALÝ (rozsah, {1, 2,…., N}})
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: A23.
Tu je rozsah, ktorý je daný pomocou pomenovaného nástroja Excel.
Po prvé, musíme nájsť päť najnižších hodnôt pomocou funkcie SMALL 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 (MALÝ (rozsah, {1, 2, 3, 4, 5}))
Vysvetlenie:
- Funkcia SMALL vracia spodných 5 číselných hodnôt a vracia pole do funkcie SUMPRODUCT.
= SUMPRODUCT ({23, 27, 28, 28, 30}))
- Funkcia SUMPRODUCT získava množstvo spodných 5 hodnôt, ktoré je potrebné zhrnúť.
Tu je rozsah uvedený ako pomenovaný rozsah. Stlačením klávesu Enter získate SÚČET spodných 5 čísel.
Ako vidíte na vyššie uvedenom obrázku, tento súčet je 136. Súčet hodnôt 23 + 27 + 28 + 28 + 30 = 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:
= SUMPRODUCT (MALÝ (rozsah, RIADOK (NEPRIAMY ("1:10"))
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 307.
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 vrchných n hodnôt alebo dolných n hodnôt 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.
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
