V tomto článku se naučíme Jak najít hodnotu percentilu, pokud s danými kritérii v aplikaci Excel
Scénář:
Jednoduše řečeno, při práci s dlouhou rozptýlenou datovou sadou někdy potřebujeme najít percentil čísel s určitými kritérii. Například nalezení percentilu platů v konkrétním oddělení nebo více kritérií pro datum, jména, oddělení nebo dokonce čísla, jako jsou platy pod hodnotou nebo množství nad hodnotou. K tomu musíte ručně extrahovat požadovaná čísla a poté vypočítat percentil pole s kritérii. Použití funkce IF s maticovými vzorci.
Jak problém vyřešit?
Určitě přemýšlíte, jak je to možné, provádět logické operace přes pole tabulky pomocí funkce IF. Pokud je funkce v Excelu velmi užitečná, provede vás některými obtížnými úkoly v aplikaci Excel nebo v jiných kódovacích jazycích. Funkce IF testuje podmínky na poli odpovídajícím požadovaným hodnotám a vrací výsledek jako pole odpovídající podmínkám True jako 1 a False jako 0
Pro tento problém budeme používat následující funkce:
- Funkce PERCENTILE
- IF funkce
Budeme vyžadovat tyto výše uvedené funkce a nějaký základní smysl pro provoz dat. logické podmínky na polích lze aplikovat pomocí logických operátorů. Tyto logické operátory pracují na textu i číslech. Níže je obecný vzorec. { } curly braces je kouzelný nástroj k provádění maticových vzorců s funkcí IF.
Obecný vzorec:
| { = PERCENTILE (IF ((rozsah op krit), percentil_pole), k) } |
Poznámka: Pro složená rovnátka ( { } ) Použijte Ctrl + Shift + Enter při práci s poli nebo rozsahy v Excelu. To ve výchozím nastavení ve vzorci vygeneruje složené závorky. NEPOKOUŠEJTE se tvrdě kódovat složené závorky.
rozsah: pole, kde platí podmínka
op: operátor, operace použita
krit: kritéria aplikovaná na rozsah
percentile_array: pole, kde je potřeba percentil
k: kth percentil (například 33% -> k = 0,33 hodnota jako číslo)
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 data o přijatých produktech z různých regionů spolu s odpovídajícím datem, množstvím a cenou. Zde máme data a potřebujeme najít 25. percentil nebo hodnotu odpovídající 25% s ohledem na objednávky, které jsou přijímány pouze z „východního“ regionu. Nyní jste připraveni získat požadovaný výsledek pomocí níže uvedeného vzorce.
Použijte vzorec:
| { = PERCENTILNÍ (IF (B2: B11 = "východ", D2: D11), 0,25) } |
Vysvětlení :
- Oblast B2: B11 = "Východ": zkontroluje hodnoty v oblasti pole, aby odpovídaly "Východu".
- Logický operátor vrací True pro přiřazenou hodnotu a False pro neshodnou hodnotu.
- Nyní funkce IF vrací pouze hodnoty ceny odpovídající 1 s a False tak, jak jsou. Níže je rozsah vrácený funkcí IF a přijatý funkcí PERCENTILE.
{ NEPRAVDIVÉ; 303,63; NEPRAVDIVÉ; 153,34; NEPRAVDIVÉ; 95,58; NEPRAVDIVÉ; NEPRAVDIVÉ; 177; NEPRAVDIVÉ }
- Funkce PERCENTILE vrací cenu percentilu 25% (k = 0,25) pro vrácené pole.
Zde jsou pole uvedena pomocí odkazu na buňku. Nyní je cena odpovídající 25% pole uvedena níže.
Jak vidíte, cena začíná být 138,9, ze tří objednávek z „Východu“ s cenovými hodnotami 303,63, 153,34 a 95,58. Nyní získejte hodnotu ceny s různým percentilem pouhou změnou hodnoty kth na 0,5 pro 50%, 0,75 pro 75% a 1 pro hodnotu 100% percentilu.
Jak vidíte, máme všechny hodnoty percentilu odpovídající daným kritériím. Nyní použijte vzorec s hodnotou data jako kritérii.
percentil, pokud s kritérii data v aplikaci Excel:
Datum jako kritéria je v Excelu ošemetná věc. Protože Excel ukládá hodnoty data jako číslo. Pokud tedy hodnota data není rozpoznána Excelem, pak chyba vzorce vrátí. Zde musíme najít hodnotu 25% percentilu, která má objednávky přijaté po 15. lednu 2019.
Použijte vzorec:
| { = PERCENTILNÍ (IF (A2: A11> H3, D2: D11), 0,25)} |
>: větší než operátor aplikovaný na pole data.
Zde jsou pole uvedena pomocí odkazu na buňku. Nyní je cena odpovídající 25% pole uvedena níže.
Jak vidíte, 90,27 je 25. hodnota percentilu s datem po 15. lednu 2019. Nyní získejte percentil pro jinou hodnotu kth.
Zde jsou uvedeny všechny hodnoty percentilu
Zde jsou všechny pozorovací poznámky týkající se používání vzorce.
Poznámky:
- The percentile_array in the formula works only with numbers.
- NEDOKLADUJTE pevně složené závorky pomocí vzorce.
- Pokud vzorec vrací chybu #HODNOTA, musí být ve vzorci uvedena kontrola složených závorek, jak je uvedeno v příkladech v článku.
- Operace jako rovná se ( = ), menší než rovno ( <= ), větší než ( > ) nebo se nerovná () lze provést v rámci použitého vzorce, pouze s čísly.
Doufám, že tento článek o tom, jak najít percentil, pokud s kritérii v aplikaci Excel je vysvětlující. Další články o součtových vzorcích najdete zde. 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.
Oblíbené č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.