În acest articol, vom afla cum să găsim valoarea percentilă dacă avem criterii date în Excel
Scenariu:
În cuvinte simple, atunci când lucrăm cu un set de date lung împrăștiat, uneori trebuie să găsim percentila numerelor cu câteva criterii peste el. De exemplu, găsirea percentilei de salarii într-un anumit departament sau având mai multe criterii peste dată, nume, departament sau poate chiar numera date precum salarii sub valoare sau cantitate peste valoare. Pentru aceasta trebuie să extrageți manual numerele necesare și apoi să calculați percentila unui tablou cu criterii. Utilizarea funcției IF cu formule matrice.
Cum se rezolvă problema?
Trebuie să vă gândiți cum este posibil acest lucru, pentru a efectua operații logice peste tablouri folosind funcția IF. Dacă funcția în Excel este foarte utilă, vă va ajuta să faceți câteva sarcini dificile în Excel sau în orice alte limbi de codare. Funcția IF testează condițiile matricei corespunzătoare valorilor solicitate și returnează rezultatul ca matrice corespunzătoare condițiilor True ca 1 și False ca 0
Pentru această problemă, vom folosi următoarele funcții:
- Funcția PERCENTILĂ
- Funcția IF
Vom solicita aceste funcții de mai sus și un anumit simț al funcționării datelor. condițiile logice pe tablouri pot fi aplicate folosind operatori logici. Acești operatori logici lucrează atât la text cât și la numere. Mai jos este formula generică. { } acoladele sunt instrumentul magic pentru a efectua formule matrice cu funcția IF.
Formula generică:
| { = PERCENTIL (IF ((interval op crit), percentile_array), k) } |
Notă: Pentru acolade ( { } ) Utilizare Ctrl + Shift + Enter când lucrați cu tablouri sau intervale în Excel. Aceasta va genera în mod implicit aparate dentare pe formulă. NU încercați să codificați caractere dentare cu coduri dure.
interval: matrice, unde se aplică condiția
op: operator, operație aplicată
crit: criterii aplicate pe interval
percentile_array: matrice, unde este necesară percentila
k: a percentila (de exemplu 33% -> k = 0,33 valoare ca număr)
Exemplu:
Toate acestea ar putea fi confuze pentru a înțelege. Deci, să testăm această formulă executând-o pe exemplul prezentat mai jos. Aici avem date despre produsele primite din diferite regiuni, împreună cu data, cantitatea și prețul corespunzător. Aici avem datele și trebuie să găsim percentila 25 sau valoarea corespunzătoare a 25% având în vedere comenzile care sunt primite doar din regiunea „Est”. Acum sunteți gata să obțineți rezultatul dorit folosind formula de mai jos.
Folosiți formula:
| { = PERCENTIL (IF (B2: B11 = "Est", D2: D11), 0,25) } |
Explicație:
- Regiunea B2: B11 = "Est": verifică valorile din regiunea matricei pentru a se potrivi cu "Est".
- Operatorul logic returnează True pentru valoarea potrivită și False pentru valoarea neegalată.
- Acum funcția IF returnează doar valorile prețurilor corespunzătoare 1s și False așa cum este. Mai jos este intervalul returnat de funcția IF și primit prin funcția PERCENTILĂ.
{ FALS; 303,63; FALS ; 153,34; FALS ; 95,58; FALS ; FALS ; 177; FALS }
- Funcția PERCENTILE returnează prețul percentilei de 25% (k = 0,25) pentru matricea returnată.
Aici tablourile sunt date folosind referința celulei. Acum prețul corespunzător celor 25% din matrice este dat mai jos.
După cum puteți vedea, prețul ajunge să fie de 138,9, din cele trei comenzi din „Est” având valori de preț 303,63, 153,34 și 95,58. Acum obțineți valoarea prețului având percentile diferite doar schimbând valoarea kth la 0,5 pentru 50%, 0,75 pentru 75% și 1 pentru valoarea percentilei 100%.
După cum puteți vedea, avem toate valorile percentilei corespunzătoare criteriilor date. Acum utilizați formula cu valoarea Date ca criterii.
percentilă dacă are criterii de dată în Excel:
Data ca criteriu este un lucru dificil în Excel. Pe măsură ce Excel stochează valorile datei ca număr. Deci, dacă valoarea datei nu este recunoscută de excel, atunci formula returnează eroarea. Aici trebuie să găsim valoarea procentuală de 25% având comenzi primite după 15 ianuarie 2019.
Folosiți formula:
| { = PERCENTIL (IF (A2: A11> H3, D2: D11), 0,25)} |
>: mai mare decât operatorul aplicat peste matrice de date.
Aici tablourile sunt date folosind referința celulei. Acum prețul corespunzător celor 25% din matrice este dat mai jos.
După cum puteți vedea, 90,27 este cea de-a 25-a valoare percentilă care are data după 15 ianuarie 2019. Acum obțineți percentila pentru diferită valoare kth.
Iată toate valorile percentilei ca rezultate
Iată toate notele observaționale privind utilizarea formulei.
Note:
- Matricea percentilă din formulă funcționează numai cu numere.
- NU codificați acoladele cu formulă.
- Dacă formula returnează eroarea #VALUE, verificați dacă aparatele dentare trebuie să fie prezente în formulă așa cum se arată în exemplele din articol.
- Operații precum este egal cu ( = ), mai mic decât egal cu ( <= ), mai mare ca ( > ) sau nu egal cu () poate fi efectuat în cadrul unei formule aplicate, numai cu numere.
Sper că acest articol despre Cum se găsește percentila dacă are criterii în Excel este explicativ. Găsiți mai multe articole despre formulele de rezumare aici. Dacă ți-au plăcut blogurile noastre, împărtășește-le cu prietenii tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook. Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrieți-ne pe site-ul de e-mail
Cum se utilizează funcția SUMPRODUCT în Excel: Returnează SUMA după multiplicarea valorilor în mai multe tablouri în excel.
SUMA dacă data este între : Returnează SUMA valorilor între date sau perioade date în Excel.
Suma dacă data este mai mare decât data dată: Returnează SUMA valorilor după data sau perioada dată în Excel.
2 moduri de a suma prin lună în Excel: Returnează SUMA valorilor într-o anumită lună specifică în Excel.
Cum să însumați mai multe coloane cu condiție: Returnează SUMA valorilor pe mai multe coloane cu condiție în excel.
Articole populare:
50 Comandă rapidă Excel pentru a vă crește productivitatea : Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Cum se folosește tFuncția VLOOKUP în Excel : Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
Cum se utilizează funcția COUNTIF în Excel : Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel : Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.