Ako nájsť percentil, ak s kritériami v programe Excel

Anonim


V tomto článku sa naučíme Ako nájsť hodnotu percentilu, ak s danými kritériami v programe Excel

Scenár:

Jednoducho povedané, pri práci s dlhým rozptýleným súborom údajov niekedy potrebujeme nájsť percentil čísel s určitými kritériami. Napríklad zistenie percentilu platov na konkrétnom oddelení alebo viacnásobné kritérium dátumu, názvu, oddelenia alebo dokonca číslovania údajov, ako sú platy pod hodnotou alebo množstvo nad hodnotou. Na to musíte manuálne extrahovať požadované čísla a potom vypočítať percentil poľa s kritériami. Použitie funkcie IF s maticovými vzorcami.

Ako problém vyriešiť?

Musíte premýšľať, ako je to možné, vykonávať logické operácie nad tabuľkovými poliami pomocou funkcie IF. Ak je funkcia v programe Excel veľmi užitočná, prevedie vás náročnými úlohami v programe Excel alebo v iných kódovacích jazykoch. IF funkcia testuje podmienky na poli zodpovedajúcom požadovaným hodnotám a vráti výsledok ako pole zodpovedajúce skutočným podmienkam ako 1 a nepravdivým ako 0

Na tento problém použijeme nasledujúce funkcie:

  1. Funkcia PERCENTILE
  2. IF funkcia

Budeme požadovať tieto vyššie uvedené funkcie a nejaký základný zmysel pre prácu s dátami. logické podmienky na polia je možné použiť pomocou logických operátorov. Tieto logické operátory pracujú s textom aj s číslami. Nasleduje generický vzorec. { } curly braces je magický nástroj na vykonávanie vzorcov poľa s funkciou IF.

Všeobecný vzorec:

{ = PERCENTILE (IF ((rozsah op krit), percentile_array), k) }

Poznámka: Pre zložené rovnátka ( { } ) Použiť Ctrl + Shift + Enter pri práci s poľami alebo rozsahmi v programe Excel. Štandardne sa vo vzorci vygenerujú kučeravé zátvorky. NEPOKÚŠAJTE sa pevne kódovať znaky zložených zátvoriek.

rozsah: pole, kde platia podmienky

op: operátor, použitá operácia

krit: kritériá aplikované na rozsah

percentile_array: pole, kde je potrebný percentil

k: kth percentil (napríklad 33% -> k = 0,33 hodnota ako číslo)

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 údaje o prijatých produktoch z rôznych regiónov spolu s príslušným dátumom, množstvom a cenou. Tu máme údaje a musíme nájsť 25. percentil alebo hodnotu zodpovedajúcu 25% vzhľadom na objednávky, ktoré sú prijímané iba z regiónu "Východ". Teraz ste pripravení dosiahnuť požadovaný výsledok pomocou nižšie uvedeného vzorca.

Použite vzorec:

{ = PERCENTILE (IF (B2: B11 = "východ", D2: D11), 0,25) }

Vysvetlenie:

  1. Región B2: B11 = "Východ": skontroluje, či sa hodnoty v oblasti poľa zhodujú s "východom".
  2. Logický operátor vracia hodnotu True pre priradenú hodnotu a hodnotu False pre hodnotu bez priradenia.
  3. Teraz funkcia IF vracia iba hodnoty ceny zodpovedajúce 1 s a False tak, ako sú. Nižšie je uvedený rozsah vrátený funkciou IF a prijatý funkciou PERCENTILE.

{FALSE; 303,63; NEPRAVDA; 153,34; NEPRAVDA; 95,58; NEPRAVDA; NEPRAVDA; 177; FALSE}

  1. Funkcia PERCENTILE vráti percentilnú cenu 25% (k = 0,25) pre vrátené pole.

Tu sú polia uvedené pomocou odkazu na bunku. Teraz je cena zodpovedajúca 25% poľa uvedená nižšie.

Ako vidíte, cena začína byť 138,9 z troch objednávok z „východu“ s cenovými hodnotami 303,63, 153,34 a 95,58. Teraz získajte hodnotu ceny s rôznym percentilom iba zmenou hodnoty kth na 0,5 pre 50%, 0,75 pre 75% a 1 pre hodnotu 100% percentilu.

Ako vidíte, máme všetky hodnoty percentilu zodpovedajúce daným kritériám. Teraz použite vzorec s hodnotou dátumu ako kritéria.

percentil, ak s kritériami dátumu v programe Excel:

Dátum ako kritérium je v programe Excel zložitá vec. Pretože Excel ukladá hodnoty dátumu ako číslo. Ak teda hodnota dátumu nie je rozpoznaná programom Excel, potom chyba vrátenia vzorca. Tu musíme nájsť 25% percentil hodnoty objednávok prijatých po 15. januári 2019.

Použite vzorec:

{ = PERCENTIL (IF (A2: A11> H3, D2: D11), 0,25)}

>: väčšie ako operátor aplikovaný na pole dátumov.

Tu sú polia uvedené pomocou odkazu na bunku. Teraz je cena zodpovedajúca 25% poľa uvedená nižšie.

Ako vidíte, 90,27 je 25. percentilná hodnota s dátumom po 15. januári 2019. Teraz získajte percentil pre inú hodnotu kth.

Tu sú všetky percentilové hodnoty ako výsledky

Tu sú všetky pozorovacie poznámky týkajúce sa použitia vzorca.

Poznámky:

  1. Pole percentilu_vzorec vo vzorci funguje iba s číslami.
  2. NEKÓDUJTE zložené zátvorky so vzorcom.
  3. Ak vzorec vráti chybu #HODNOTA, vo vzorci musí byť prítomná kontrola zložených zátvoriek, ako je uvedené v príkladoch v článku.
  4. Operácie ako sa rovná ( = ), menej ako sa rovná ( <= ), väčší než ( > ) alebo sa nerovná () je možné vykonať v rámci použitého vzorca, iba s číslami.

Dúfam, že tento článok o tom, ako nájsť percentil, ak s kritériami v programe Excel je vysvetľujúci. Tu nájdete ďalšie články o súhrnných vzorcoch. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi prvý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.

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.