V tomto článku sa naučíme, ako nájsť n -tú najmenšiu s kritériami a n -tú najväčšiu s kritériami z danej tabuľky v programe Excel.
Scenár:
Jednoducho povedané, pri práci s číslami v údajových číslach je niekedy daná podmienka, tj. Keď potrebujeme vyhľadať piatu najvyššiu uvedenú hodnotu. Riešenie tohto problému môžete vykonať jednoducho pomocou funkcií programu Excel, ako je to popísané nižšie.
Deviata najväčšia s kritériami
Ako problém vyriešiť?
V tomto článku budeme musieť používať funkciu LARGE. Teraz z týchto funkcií vytvoríme vzorec. Tu dostaneme tabuľku a musíme nájsť n -tú najväčšiu hodnotu v rozsahu s danými kritériami.
Všeobecný vzorec:
{ = VEĽKÉ (IF (c_range = "value", range), n ) }
c_range : rozsah kritérií
hodnotu : hodnota zhody kritérií
rozsah : pole výsledkov
n : n. najväčšia
Poznámka : Nedávajte kučeravé zátvorky ručne. Toto je maticový vzorec, ktorý musíte použiť Ctrl + Shift + Enter namiesto stačí zadať miesto, kde sa vráti #ČÍSLO! chyba.
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 podrobnosti objednávky s dátumom objednávky, regiónom a cenou objednávky.
Musíme zistiť 5. najväčšiu cenu objednávky z regiónu Východ. Tu je rozsah daný ako pomocou programu Excel pomenovaného rozsahu.
oblasť (C3: C16)
cena (D3: D16)
Najprv musíme pomocou funkcie VEĽKÁ nájsť piate najväčšie hodnoty 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:
{ = VEĽKÉ (IF (oblasť = G5, cena), F5)}
Vysvetlenie:
- Funkcia IF skontroluje kritériá zodpovedajúce všetkým hodnotám v regióne s danou východnou hodnotou v bunke G5 a vráti zodpovedajúce PRAVDIVÉ hodnoty z cenového rozpätia.
= VEĽKÉ ({58,41; 303,63; FALSE; 153,34; 82,84; FALSE; 520,01; FALSE; 177; FALSE; FALSE; 57,97; 97,72; FALSE}), F5)
- Funkcia LARGE vezme pole, ktoré má celý cenový rozsah, ako je uvedené vyššie, a vráti piatu najväčšiu hodnotu z poľa, ako je znázornené na obrázku nižšie.
Vzorec si môžete pozrieť v poli vzorcov ako na obrázku vyššie. Na dosiahnutie výsledku použite Ctrl + Shift + Enter.
Ako vidíte, vzorec poľa vracia piatu najväčšiu cenu 97,72 dolára s východným regiónom.
Pole môžete tiež podávať ako pole, kritériá v úvodzovkách a hodnotu n priamo do funkcie namiesto použitia odkazu na bunku alebo pomenovaného rozsahu.
Použite vzorec:
{ = VEĽKÉ (IF (C3: C16 = "Západ", D3: D16), 3)}
Použite Ctrl + Shift + Enter
Môžete vidieť, že vzorec funguje dobre, ak v tabuľke nájdete n -tú najväčšiu hodnotu s kritériami.
Deviata najnižšia s kritériami
Ako problém vyriešiť?
V tomto článku budeme musieť používať funkciu SMALL. Teraz z týchto funkcií vytvoríme vzorec. Tu dostaneme tabuľku a musíme nájsť n -tú najmenšiu hodnotu v rozsahu s danými kritériami.
Všeobecný vzorec:
{ = MALÉ (IF (c_range = "value", range), n ) }
c_range : rozsah kritérií
hodnotu : hodnota zhody kritérií
rozsah : pole výsledkov
n : n. najväčšia
Poznámka : Nedávajte kučeravé zátvorky ručne. Toto je maticový vzorec, ktorý musíte použiť Ctrl + Shift + Enter namiesto stačí zadať miesto, kde sa vráti #ČÍSLO! chyba.
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 podrobnosti objednávky s dátumom objednávky, regiónom a cenou objednávky.
Potrebujeme zistiť n -tú najmenšiu cenu objednávky z východného regiónu. Tu je rozsah daný ako pomocou programu Excel pomenovaného rozsahu.
oblasť (C3: C16)
cena (D3: D16)
Po prvé, musíme nájsť Piatu najmenšiu alebo najnižšiu hodnotu pomocou funkcie MALÉ a potom nad hodnotami vykonať operáciu vyhľadávania. Teraz použijeme nasledujúci vzorec na získanie súčtu
Použite vzorec:
{ = MALÉ (IF (oblasť = G5, cena), F5)}
Vysvetlenie:
- Funkcia IF skontroluje kritériá zodpovedajúce všetkým hodnotám v regióne s danou východnou hodnotou v bunke G5 a vráti zodpovedajúce PRAVDIVÉ hodnoty z cenového rozpätia.
= VEĽKÉ ({58,41; 303,63; FALSE; 153,34; 82,84; FALSE; 520,01; FALSE; 177; FALSE; FALSE; 57,97; 97,72; FALSE}), F5)
- Funkcia SMALL vezme pole, ktoré má celý cenový rozsah, ako je uvedené vyššie, a vráti piatu najväčšiu hodnotu z poľa, ako je znázornené na obrázku nižšie.
Vzorec môžete zobraziť v poli vzorcov ako na obrázku vyššie. Použite Ctrl + Shift + Enter aby ste dosiahli výsledok.
Ako vidíte, vzorec poľa vracia piatu najmenšiu cenu 153,34 USD s východným regiónom.
Pole môžete tiež podávať ako pole, kritériá v úvodzovkách a hodnotu n priamo do funkcie namiesto použitia odkazu na bunku alebo pomenovaného rozsahu.
Použite vzorec:
{ = MALÉ (IF (C3: C16 = "západ", D3: D16), 3)}
Použite Ctrl + Shift + Enter
Môžete vidieť, že vzorec funguje dobre, ak v tabuľke nájdete n -tú najväčšiu hodnotu s kritériami.
Tu je niekoľko pozorovacích poznámok uvedených nižšie.
Poznámky:
- Vzorec funguje iba s číslami.
- Kučeravé zátvorky nedávajte ručne. Toto je maticový vzorec, ktorý musíte použiť Ctrl + Shift + Enter namiesto stačí zadať miesto, kde sa vráti #ČÍSLO! chyba.
- Hodnota nemôže byť väčšia ako počet hodnôt kritérií alebo vráti #ČÍSLO! Chyba.
- Ak kritériá nezodpovedajú vzorcu, vráti chybu.
- Pole argumentov musí mať rovnakú dĺžku, inak bude mať funkcia.
Dúfam, že tento článok o tom, ako nájsť n -tú najväčšiu s kritériami a n -tú najmenšiu s kritériami v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o formulároch na vyhľadávanie v programe 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
50 Skratka pre Excel, ktorá zvýši vašu produktivitu : Zrýchlite sa vo svojej úlohe. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať tFunkcia 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 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.
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.