V tomto článku se naučíme, jak najít n. Nejmenší s kritérii & n. Největší s kritérii z dané tabulky v aplikaci Excel.
Scénář:
Jednoduše řečeno, při práci s čísly v datových číslech je někdy dána podmínka, tj. Když potřebujeme vyhledat pátou nejvyšší uvedenou hodnotu. Řešení tohoto problému můžete snadno provést pomocí funkcí aplikace Excel, jak je vysvětleno níže.
Deváté největší s kritérii
Jak problém vyřešit?
Pro tento článek budeme muset použít funkci LARGE. Nyní z těchto funkcí vytvoříme vzorec. Zde dostaneme tabulku a potřebujeme najít n -tu největší hodnotu v rozsahu s danými kritérii.
Obecný vzorec:
{ = LARGE (IF (c_range = "value", range), n ) }
c_range : rozsah kritérií
hodnota : hodnota shody kritérií
rozsah : pole výsledků
n : n. největší
Poznámka : Nedávejte kudrnaté závorky ručně. Toto je maticový vzorec, který musíte použít Ctrl + Shift + Enter místo stačí zadat, kde vrací #ČÍSLO! chyba.
Příklad:
Pochopit to všechno může být matoucí. Pojďme tedy tento vzorec otestovat spuštěním na níže uvedeném příkladu.
Zde máme podrobnosti objednávky s datem objednávky, regionem a cenou objednávky.
Potřebujeme zjistit pátou největší cenu objednávky z východní oblasti. Zde je rozsah uveden jako použití pojmenovaného nástroje Excel.
region (C3: C16)
cena (D3: D16)
Nejprve musíme pomocí funkce LARGE najít Páté největší hodnoty a poté provést součet operací na těchto 5 hodnotách. Nyní použijeme následující vzorec k získání součtu
Použijte vzorec:
{ = VELKÝ (IF (region = G5, cena), F5)}
Vysvětlení:
- Funkce IF kontroluje kritéria odpovídající všem hodnotám v regionu s danou východní hodnotou v buňce G5 a vrací odpovídající SKUTEČNÉ hodnoty z cenového rozpětí.
= VELKÉ ({58,41; 303,63; NEPRAVDA; 153,34; 82,84; NEPRAVDA; 520,01; NEPRAVDA; 177; NEPRAVDA; NEPRAVDA; 57,97; 97,72; NEPRAVDA}), F5)
- Funkce LARGE vezme pole, které má všechny cenové rozpětí, jak je uvedeno výše, a vrátí pátou největší hodnotu z pole, jak ukazuje následující snímek.
Vzorec můžete zobrazit v poli vzorců jako na obrázku výše. K získání výsledku použijte Ctrl + Shift + Enter.
Jak vidíte, vzorec pole vrací 5. největší cenu 97,72 USD s východní oblastí.
Pole můžete také krmit jako pole, kritéria v uvozovkách a n hodnotu přímo do funkce namísto použití odkazu na buňku nebo pojmenovaného rozsahu.
Použijte vzorec:
{ = VELKÝ (IF (C3: C16 = "Západ", D3: D16), 3)}
Použití Ctrl + Shift + Enter
Můžete vidět, že vzorec funguje dobře, kde v tabulce najdete n -tu největší hodnotu s kritérii.
Devátá nejnižší s kritérii
Jak problém vyřešit?
V tomto článku budeme muset použít funkci SMALL. Nyní z těchto funkcí vytvoříme vzorec. Zde dostaneme tabulku a potřebujeme najít n -tu nejmenší hodnotu v rozsahu s danými kritérii.
Obecný vzorec:
{ = SMALL (IF (c_range = "value", range), n ) }
c_range : rozsah kritérií
hodnota : hodnota shody kritérií
rozsah : pole výsledků
n : n. největší
Poznámka : Nedávejte kudrnaté závorky ručně. Toto je maticový vzorec, který musíte použít Ctrl + Shift + Enter místo stačí zadat, kde vrátí #ČÍSLO! chyba.
Příklad:
Pochopit to všechno může být matoucí. Pojďme tedy tento vzorec otestovat spuštěním na níže uvedeném příkladu.
Zde máme podrobnosti objednávky s datem objednávky, regionem a cenou objednávky.
Potřebujeme zjistit n -tu nejmenší cenu objednávky z východní oblasti. Zde je rozsah uveden jako použití pojmenovaného nástroje Excel.
region (C3: C16)
cena (D3: D16)
Za prvé musíme pomocí funkce SMALL najít Pátou nejmenší nebo nejnižší hodnotu a poté nad hodnotami provést operaci vyhledávání. Nyní použijeme následující vzorec k získání součtu
Použijte vzorec:
{ = MALÝ (IF (region = G5, cena), F5)}
Vysvětlení:
- Funkce IF kontroluje kritéria odpovídající všem hodnotám v regionu s danou východní hodnotou v buňce G5 a vrací odpovídající SKUTEČNÉ hodnoty z cenového rozpětí.
= VELKÉ ({58,41; 303,63; NEPRAVDA; 153,34; 82,84; NEPRAVDA; 520,01; NEPRAVDA; 177; NEPRAVDA; NEPRAVDA; 57,97; 97,72; NEPRAVDA}), F5)
- Funkce SMALL vezme pole, které má všechny cenové rozpětí, jak je uvedeno výše, a vrátí 5. největší hodnotu z pole, jak je znázorněno na níže uvedeném snímku.
Vzorec můžete zobrazit v poli vzorců jako na obrázku výše. Použití Ctrl + Shift + Enter získat výsledek.
Jak vidíte, vzorec pole vrací pátou nejmenší cenu 153,34 $ s východní oblastí.
Pole můžete také krmit jako pole, kritéria v uvozovkách a n hodnotu přímo do funkce namísto použití odkazu na buňku nebo pojmenovaného rozsahu.
Použijte vzorec:
{ = MALÝ (IF (C3: C16 = "západ", D3: D16), 3)}
Použití Ctrl + Shift + Enter
Můžete vidět, že vzorec funguje dobře, kde najdete n -tu největší hodnotu s kritérii v tabulce.
Zde je několik pozorovacích poznámek uvedených níže.
Poznámky:
- Vzorec funguje pouze s čísly.
- Nedávejte kudrnaté závorky ručně. Toto je maticový vzorec, který musíte použít Ctrl + Shift + Enter místo stačí zadat, kde vrací #ČÍSLO! chyba.
- Hodnota nesmí být větší než počet hodnot kritérií nebo vrací #ČÍSLO! Chyba.
- Kritéria IF neodpovídají vzorci vrací chybu.
- Pole argumentů musí mít stejnou délku, jinak funkce.
Doufám, že tento článek o tom, jak najít n. Největší s kritérii & n. Nejmenší s kritérii v aplikaci Excel, je vysvětlující. Zde najdete další články o formulářích pro vyhledávání v Excelu. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail
Jak používat funkci SUMPRODUCT v Excelu: Vrací SUMU po vynásobení hodnot ve více polích v Excelu.
SUM, pokud je datum mezi : Vrátí SUMU hodnot mezi danými daty nebo obdobím v Excelu.
Součet, pokud je datum větší než dané datum: Vrátí SUMU hodnot po daném datu nebo období v aplikaci Excel.
2 způsoby součtu podle měsíce v Excelu: Vrátí SUMU hodnot v rámci daného konkrétního měsíce v aplikaci Excel.
Jak sečíst více sloupců s podmínkou: Vrátí SUMU hodnot napříč více sloupci s podmínkou v Excelu
Jak používat zástupné znaky v aplikaci Excel : Počítejte buňky odpovídající frázím pomocí zástupných znaků v aplikaci Excel
Populární články
50 Zkratka aplikace Excel pro zvýšení produktivity : Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Jak používat tFunkce VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.