V tomto článku sa naučíme, ako sčítať N najväčších hodnôt v rozsahu v programe Excel.
Scenár:
Jednoducho povedané, pri práci s údajovými číslami je niekedy daná podmienka, tj. Keď potrebujeme získať súčet čísel prvých 5 čísel poľa alebo spodnej časti poľa. Riešenie tohto problému môžete vykonať jednoducho pomocou funkcií programu Excel, ako je to popísané nižšie.
Súčet TOP N hodnôt
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 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:
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 daný ako pomocou programu Excel pomenovaného rozsahu.
Najprv musíme nájsť prvých päť hodnôt pomocou funkcie LARGE a potom vykonať súčet operácií nad týmito 5 hodnotami. Teraz použijeme nasledujúci vzorec na získanie súčtu
Použite vzorec:
| = SUMPRODUCT (VEĽKÝ (rozsah, {1, 2, 3, 4, 5})) |
Vysvetlenie:
- Funkcia LARGE vráti 5 prvých číselných hodnôt a vráti pole do funkcie SUMPRODUCT.
= SUMPRODUCT ({149, 123, 100, 87, 85}))
- Funkcia SUMPRODUCT získa pole 5 najlepších hodnôt, ktoré má pole 5 prvých čísel a vráti SÚČET týchto čísel.
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 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:
| = 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 daný ako pomocou programu Excel pomenovaného rozsahu.
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íska pole dolných 5 hodnôt, ktoré má pole dolných 5 čísel a vráti SÚČET týchto čísel.
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 sme dostali súčet spodných 10 čísel, čo vedie k 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, inak bude mať funkcia.
Dúfam, že tento článok o tom, ako zhrnúť N najväčších hodnôt v rozsahu v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o výpočte hodnôt a súvisiacich vzorcoch programu Excel. 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:
Ako používať funkciu IF v programe Excel : Príkaz IF v programe Excel skontroluje podmienku a vráti konkrétnu hodnotu, ak je podmienka PRAVDA, alebo vráti inú konkrétnu hodnotu, ak je NEPRAVDA.
Ako používať funkciu VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.