I denne artikel lærer vi, hvordan man finder procentværdien, hvis der er givet givne kriterier i Excel
Scenarie:
Kort sagt, når vi arbejder med et langt spredt datasæt, skal vi nogle gange finde percentilen af tal med nogle kriterier over det. For eksempel at finde procentdelen af lønninger i en bestemt afdeling eller have flere kriterier over dato, navne, afdeling eller kan endda nummerere data som lønninger under værdi eller mængde over værdi. Til dette skal du manuelt udtrække nødvendige numre og derefter beregne percentilen af en matrix med kriterier. Brug af IF -funktion med matrixformler.
Hvordan løser man problemet?
Du skal tænke, hvordan er dette muligt, for at udføre logiske operationer over bordarrays ved hjælp af IF -funktion. HVIS funktion i Excel er meget nyttig, vil den få dig igennem nogle vanskelige opgaver i Excel eller andre kodningssprog. IF funktion tester betingelser på array svarende til nødvendige værdier og returnerer resultatet som array svarende til True betingelser som 1 og Falsk som 0
Til dette problem bruger vi følgende funktioner:
- PERCENTIL funktion
- IF -funktion
Vi vil kræve disse ovenstående funktioner og en vis grundlæggende følelse af datadrift. logiske betingelser for arrays kan anvendes ved hjælp af logiske operatorer. Disse logiske operatorer arbejder på både tekst og tal. Nedenfor er den generiske formel. { } krøllede seler er det magiske værktøj til at udføre matrixformler med IF -funktion.
Generisk formel:
| { = PERCENTIL (HVIS ((område op crit), percentil_array), k) } |
Bemærk: Til krøllede seler ( { } ) Brug Ctrl + Shift + Enter når du arbejder med arrays eller intervaller i Excel. Dette genererer som standard Curly Braces på formlen. Forsøg IKKE at hardt kode krøllede seler tegn.
område: array, hvor betingelsen gælder
op: operatør, drift anvendt
krit: kriterier anvendt på rækkevidde
percentile_array: array, hvor percentilen er nødvendig
k: kth percentil (f.eks. 33% -> k = 0,33 værdi som tal)
Eksempel:
Alt dette kan være forvirrende at forstå. Så lad os teste denne formel ved at køre den på eksemplet nedenfor. Her har vi data om modtagne produkter fra forskellige regioner sammen med tilsvarende dato, mængde og pris. Her har vi dataene, og vi skal finde den 25. percentil eller værdi svarende til 25% i betragtning af ordrer, der kun modtages fra "øst" -regionen. Nu er du klar til at få det ønskede resultat ved hjælp af nedenstående formel.
Brug formlen:
| { = PERCENTIL (HVIS (B2: B11 = "Øst", D2: D11), 0,25) } |
Forklaring:
- Region B2: B11 = "Øst": kontrollerer værdierne i matrixområdet for at matche med "Øst".
- Logisk operator returnerer True for den matchede værdi og Falsk for den værdi uden værdi.
- Nu returnerer IF -funktionen kun prisværdierne svarende til 1'erne og False som den er. Nedenfor er intervallet returneret af IF -funktionen og modtaget af PERCENTILE -funktionen.
{ FALSK; 303,63; FALSK ; 153,34; FALSK ; 95,58; FALSK ; FALSK ; 177; FALSK }
- PERCENTILE -funktionen returnerer 25% (k = 0,25) percentilprisen for det returnerede array.
Her er arrays givet ved hjælp af cellereferencen. Nu er prisen svarende til de 25% af arrayet angivet nedenfor.
Som du kan se, kommer prisen til 138,9, fra de tre ordrer fra "Øst" med prisværdier 303,63, 153,34 og 95,58. Få nu prisværdien med en anden percentil ved blot at ændre kth -værdien til 0,5 for 50%, 0,75 for 75% og 1 for 100% percentilværdi.
Som du kan se, har vi alle percentilværdierne, der svarer til givne kriterier. Brug nu formlen med datoværdi som kriterier.
percentil hvis med dato kriterier i Excel:
Dato som kriterier er en vanskelig ting i Excel. Da Excel gemmer datoværdier som tal. Så hvis datoværdien ikke genkendes af excel, er formelreturfejl. Her skal vi finde værdien på 25% percentilen, hvor ordrer modtages efter den 15. januar 2019.
Brug formlen:
| { = PERCENTIL (HVIS (A2: A11> H3, D2: D11), 0,25)} |
>: større end operator, der er anvendt over datormatrisen.
Her er arrays givet ved hjælp af cellereferencen. Nu er prisen svarende til de 25% af arrayet angivet nedenfor.
Som du kan se, er 90,27 den 25. percentilværdi, der har dato efter 15. januar 2019. Få nu percentilen for den forskellige kth -værdi.
Her er alle percentilværdierne som resultater
Her er alle observationsnotater vedrørende brug af formlen.
Bemærkninger:
- Procentil_arrayet i formlen fungerer kun med tal.
- Kode IKKE krøllede seler med formel.
- Hvis formlen returnerer #VÆRDI -fejl, skal du kontrollere, om de krøllede seler skal være til stede i formlen som vist i eksemplerne i artiklen.
- Handlinger som er lig med ( = ), mindre end lig med ( <= ), bedre end ( > ) eller ikke er lig med () kan udføres inden for en anvendt formel, kun med tal.
Håber, at denne artikel om hvordan man finder percentilen, hvis med kriterier i Excel er forklarende. Find flere artikler om Summing formler her. Hvis du kunne lide vores blogs, kan du dele den med dine fristarts på Facebook. Og også du kan følge os på Twitter og Facebook. Vi vil meget gerne høre fra dig, lad os vide, hvordan vi kan forbedre, supplere eller innovere vores arbejde og gøre det bedre for dig. Skriv til os på e -mail -stedet
Sådan bruges funktionen SUMPRODUCT i Excel: Returnerer SUMMEN efter multiplikation af værdier i flere arrays i excel.
SUM, hvis datoen er mellem : Returnerer SUM af værdier mellem givne datoer eller periode i excel.
Sum, hvis datoen er større end den givne dato: Returnerer SUM af værdier efter den givne dato eller periode i excel.
2 måder at summe efter måned i Excel: Returnerer SUM af værdier inden for en given specifik måned i excel.
Sådan summeres flere kolonner med betingelse: Returnerer SUM af værdier på tværs af flere kolonner med betingelse i excel.
Populære artikler:
50 Excel -genvej til at øge din produktivitet : Bliv hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
Sådan bruges than VLOOKUP -funktion i Excel : Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
Sådan bruges funktionen COUNTIF i Excel : Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.
Sådan bruges SUMIF -funktionen i Excel : Dette er en anden vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.