I den här artikeln kommer vi att lära oss hur man hittar percentilvärdet om det finns givna kriterier i Excel
Scenario:
I enkla ord, när vi arbetar med en lång spridd datauppsättning, måste vi ibland hitta percentilen av siffror med några kriterier över den. Till exempel att hitta procentilen av löner i en viss avdelning eller ha flera kriterier över datum, namn, avdelning eller kan till och med räkna upp data som löner under värde eller kvantitet över värde. För detta måste du manuellt extrahera nödvändiga nummer och sedan beräkna percentilen för en array med kriterier. Användning av IF -funktion med matrisformler.
Hur löser man problemet?
Du måste tänka på hur detta är möjligt för att utföra logiska operationer över tabellarrayer med IF -funktion. Om funktionen i Excel är mycket användbar, kommer den att ta dig igenom några svåra uppgifter i Excel eller andra kodningsspråk. IF -funktion testar villkor på array som motsvarar erforderliga värden och returnerar resultatet som array som motsvarar True -villkor som 1 och Falskt som 0
För detta problem kommer vi att använda följande funktioner:
- PERCENTIL funktion
- IF -funktion
Vi kommer att kräva dessa funktioner ovan och en grundläggande känsla av datadrift. logiska villkor för matriser kan tillämpas med logiska operatorer. Dessa logikoperatorer arbetar både med text och siffror. Nedan är den generiska formeln. { } lockiga hängslen är det magiska verktyget för att utföra matrisformler med IF -funktion.
Generisk formel:
| { = PERCENTIL (OM ((intervall op crit), percentile_array), k) } |
Obs: För lockiga hängslen ( { } ) Använda sig av Ctrl + Skift + Enter när du arbetar med matriser eller intervall i Excel. Detta genererar som standard Curly Braces på formeln. Försök INTE att koda lockiga tandställningstecken.
intervall: array, där villkor gäller
op: operatör, drift tillämpad
crit: kriterier tillämpade på intervall
percentile_array: array, där percentilen behövs
k: kth percentil (till exempel 33% -> k = 0,33 värde som tal)
Exempel:
Allt detta kan vara förvirrande att förstå. Så, låt oss testa denna formel genom att köra den på exemplet som visas nedan. Här har vi uppgifter om mottagna produkter från olika regioner tillsammans med motsvarande datum, kvantitet och pris. Här har vi data och vi måste hitta den 25: e percentilen eller värdet motsvarande 25% med tanke på order som endast tas emot från "öst" -regionen. Nu är du redo att få önskat resultat med hjälp av formeln nedan.
Använd formeln:
| { = PERCENTIL (OM (B2: B11 = "Öst", D2: D11), 0,25) } |
Förklaring :
- Region B2: B11 = "East": kontrollerar värdena i array -regionen för att matcha med "East".
- Logisk operator returnerar True för det matchade värdet och Falskt för det oöverträffade värdet.
- Nu returnerar IF -funktionen endast prisvärdena som motsvarar 1: orna och False som det är. Nedan är intervallet som returneras av IF -funktionen och mottas av PERCENTILE -funktionen.
{FALSKT; 303,63; FALSKT; 153,34; FALSKT; 95,58; FALSKT; FALSKT; 177; FALSKT}
- PERCENTILE -funktionen returnerar 25% (k = 0,25) percentilpriset för den returnerade matrisen.
Här ges matriserna med cellreferensen. Nu anges priset motsvarande 25% av matrisen nedan.
Som du kan se kommer priset att bli 138,9, från de tre beställningarna från "Öst" med prisvärden 303,63, 153,34 och 95,58. Få nu prisvärdet med olika percentil bara genom att ändra kth -värdet till 0,5 för 50%, 0,75 för 75% och 1 för 100% percentilvärde.
Som du kan se har vi alla percentilvärden som motsvarar givna kriterier. Använd nu formeln med datumvärde som kriterier.
percentil om med datumkriterier i Excel:
Datum som kriterier är en knepig sak i Excel. Som Excel lagrar datumvärden som nummer. Så om datumvärdet inte känns igen av excel så återkommer formelreturfelet. Här måste vi hitta 25% -procentilvärdet med order mottagna efter 15 januari 2019.
Använd formeln:
| { = PERCENTIL (OM (A2: A11> H3, D2: D11), 0,25)} |
>: större än operator som tillämpas över datummatrisen.
Här ges matriserna med cellreferensen. Nu anges priset motsvarande 25% av matrisen nedan.
Som du kan se är 90,27 det 25: e percentilen som har datum efter den 15 januari 2019. Få nu percentilen för det olika kth -värdet.
Här är alla percentilvärden som resultat
Här är alla observationsanteckningar om användning av formeln.
Anmärkningar:
- Procentil_arrayen i formeln fungerar bara med siffror.
- KODA INTE håriga hängslen med formel.
- Om formeln returnerar #VÄRDE -fel, kontrollera om de lockiga hängslen måste finnas i formeln som visas i exemplen i artikeln.
- Operationer som är lika med ( = ), mindre än lika med ( <= ), större än ( > ) eller inte är lika med () kan utföras inom en tillämpad formel, endast med siffror.
Hoppas den här artikeln om hur man hittar percentilen om med kriterier i Excel är förklarande. Hitta fler artiklar om Summing formler här. Om du gillade våra bloggar, dela den med dina fristarts på Facebook. Och du kan också följa oss på Twitter och Facebook. Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen
Så här använder du SUMPRODUCT -funktionen i Excel: Returnerar SUMMEN efter multiplikation av värden i flera matriser i excel.
SUMMA om datumet är mellan : Returnerar summan av värden mellan givna datum eller period i excel.
Summa om datum är större än angivet datum: Returnerar summan av värden efter det angivna datumet eller perioden i excel.
2 sätt att summera per månad i Excel: Returnerar summan av värden inom en viss specifik månad i excel.
Hur man summerar flera kolumner med villkor: Returnerar SUMMAN av värden över flera kolumner med villkor i excel.
Populära artiklar:
50 Excel -genväg för att öka din produktivitet : Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.
Hur man använder than VLOOKUP -funktion i Excel : Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
Så här använder du funktionen COUNTIF i Excel : Räkna värden med villkor med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel : Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.