V tem članku se bomo naučili, kako najti vrednost percentila, če z določenimi merili v Excelu
Scenarij:
Preprosto povedano, pri delu z dolgim razpršenim naborom podatkov moramo včasih najti percentil števil z nekaterimi merili. Na primer, če ugotovite percentil plač v določenem oddelku ali če imate več meril glede datuma, imen, oddelka ali lahko celo oštevilčite podatke, kot so plače pod vrednostjo ali količina nad vrednostjo. Za to morate ročno izvleči zahtevane številke in nato izračunati percentil niza s kriteriji. Uporaba funkcije IF z matričnimi formulami.
Kako rešiti težavo?
Morate razmišljati, kako je to mogoče, da izvajate logične operacije nad matrikami tabel z uporabo funkcije IF. Če je funkcija IF v Excelu zelo uporabna, vas bo vodila skozi nekaj težkih nalog v Excelu ali katerem koli drugem kodiranju. Funkcija IF preizkuša pogoje na matriki, ki ustrezajo zahtevanim vrednostim, in vrne rezultat kot niz, ki ustreza resničnim pogojem kot 1 in napačno kot 0
Za to težavo bomo uporabljali naslednje funkcije:
- PERCENTILNA funkcija
- IF funkcija
Potrebovali bomo te zgornje funkcije in nekaj osnovnega občutka delovanja s podatki. logične pogoje za matrike je mogoče uporabiti z logičnimi operaterji. Ti logični operaterji delujejo tako na besedilo kot na številke. Spodaj je splošna formula. { } kodrasti oklepaji so čarobno orodje za izvajanje matričnih formul s funkcijo IF.
Splošna formula:
| { = PERCENTILNO (IF ((obseg op crit), percentile_array), k) } |
Opomba: Za kodraste naramnice ( { } ) Uporaba Ctrl + Shift + Enter pri delu z matrikami ali obsegi v Excelu. To bo privzeto ustvarilo zavite oklepaje po formuli. NE poskušajte kodirati znakov kodrastih zagrad.
range: polje, kjer velja pogoj
op: operater, operacija uporabljena
crit: merila za doseg
percentile_array: polje, kjer je potreben percentil
k: k -ti percentil (na primer 33% -> k = 0,33 vrednost kot število)
Primer:
Vse to je lahko zmedeno za razumevanje. Torej, preizkusimo to formulo tako, da jo zaženemo na spodnjem primeru. Tu imamo podatke o prejetih izdelkih iz različnih regij skupaj z ustreznim datumom, količino in ceno. Tu imamo podatke in moramo najti 25. percentil ali vrednost, ki ustreza 25% glede na naročila, ki jih prejmemo samo iz regije "East". Zdaj ste pripravljeni doseči želeni rezultat z uporabo spodnje formule.
Uporabite formulo:
| { = PERCENTILNO (ČE (B2: B11 = "vzhod", D2: D11), 0,25) } |
Pojasnilo:
- Regija B2: B11 = "Vzhod": preveri, ali se vrednosti v območju matrike ujemajo z "Vzhod".
- Logični operator vrne True za ujemajočo se vrednost in False za neprimerljivo vrednost.
- Zdaj funkcija IF vrne samo vrednosti cen, ki ustrezajo 1s in False. Spodaj je obseg, ki ga vrne funkcija IF in sprejme funkcija PERCENTILE.
{FALSE; 303,63; FALSE; 153,34; FALSE; 95,58; FALSE; FALSE; 177; FALSE}
- Funkcija PERCENTILE vrne 25% (k = 0,25) percentilno ceno za vrnjeno matriko.
Tu so matrike podane s sklicem na celico. Zdaj je spodaj navedena cena, ki ustreza 25% matrike.
Kot lahko vidite, cena znaša 138,9, pri treh naročilih iz "Vzhoda", ki imajo vrednosti 303,63, 153,34 in 95,58. Zdaj dobite cenovno vrednost, ki ima drugačen percentil, tako da spremenite kth vrednost na 0,5 za 50%, 0,75 za 75% in 1 za 100% vrednost percentila.
Kot lahko vidite, imamo vse vrednosti percentila, ki ustrezajo danim merilom. Zdaj uporabite formulo z vrednostjo Date kot merilo.
percentil, če z merili datuma v Excelu:
Datum kot merilo je težavna stvar v Excelu. Ker Excel shrani datumske vrednosti kot številko. Torej, če vrednost datuma ne prepozna Excel, potem pride do napake pri vrnitvi formule. Tu moramo najti 25 -odstotno vrednost percentila z naročili, prejetimi po 15. januarju 2019.
Uporabite formulo:
| { = PERCENTILNO (ČE (A2: A11> H3, D2: D11), 0,25)} |
>: večji od operatorja, uporabljenega nad nizom datumov.
Tu so matrike podane s sklicem na celico. Zdaj je spodaj navedena cena, ki ustreza 25% matrike.
Kot lahko vidite, je 90,27 vrednost 25. percentila z datumom po 15. januarju 2019. Zdaj dobite percentil za drugo k -to vrednost.
Tu so vse vrednosti percentila kot rezultati
Tu so vse opombe glede uporabe formule.
Opombe:
- Percentilski_mat v formuli deluje samo s številkami.
- NE kodirajte okroglih naramnic s formulo.
- Če formula vrne napako #VALUE, preverite, ali morajo biti v formuli naviti zaviti oklepaji, kot je prikazano v primerih v članku.
- Operacije, podobne ( = ), manj kot enako ( <= ), večji kot ( > ) ali ni enako () je mogoče izvesti v uporabljeni formuli, samo s številkami.
Upam, da je ta članek o tem, kako najti percentil, če je z merili v Excelu razložljiv. Več člankov o formulah za seštevanje najdete tukaj. Če so vam bili naši blogi všeč, jih delite s svojimi začetniki na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletni strani elektronske pošte
Kako uporabljati funkcijo SUMPRODUCT v Excelu: Vrne SUM po množenju vrednosti v več matrikah v Excelu.
SUM, če je datum med : Vrne SUM vrednosti med danimi datumi ali obdobjem v Excelu.
Vsota, če je datum večji od navedenega datuma: Vrne SUM vrednosti po danem datumu ali obdobju v Excelu.
2 načina seštevanja po mesecih v Excelu: Vrne SUM vrednosti v določenem mesecu v Excelu.
Kako sešteti več stolpcev s pogojem: Vrne SUM vrednosti v več stolpcih s pogojem v excelu.
Priljubljeni članki:
50 Bližnjica v Excelu za večjo produktivnost : Hitreje opravite svojo nalogo. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati tFunkcija VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.