I denne artikkelen vil vi lære å finne nth minste med kriterier og nth største med kriterier fra den angitte tabellen i Excel.
Scenario:
Med enkle ord, mens vi jobber med tall i datatall, noen ganger gitt en betingelse, dvs. når vi må slå opp for den femte høyeste verdien som er gitt. Du kan enkelt utføre løsningen på dette problemet ved å bruke Excel -funksjonene som forklart nedenfor.
Nth største med kriterier
Hvordan løse problemet?
For denne artikkelen må vi bruke LARGE -funksjonen. Nå skal vi lage en formel av disse funksjonene. Her får vi en tabell, og vi må finne den nth største verdien i området med gitte kriterier.
Generisk formel:
{ = STOR (HVIS (c_range = "verdi", område), n ) }
c_range : kriterier
verdi : kriterier matchende verdi
område : resultatmatrise
n : nth største
Merk : Ikke sett krøllparenteser manuelt. Dette er en matriseformel, må brukes Ctrl + Shift + Enter i stedet for bare Enter der den returnerer #NUM! feil.
Eksempel:
Alt dette kan være forvirrende å forstå. Så la oss teste denne formelen ved å kjøre den på eksemplet nedenfor.
Her har vi ordredetaljer med ordredato, region og ordrepris.
Vi må finne ut den femte største ordreprisen fra Øst -regionen. Her er området gitt som ved bruk av det navngitte området excel -verktøyet.
region (C3: C16)
pris (D3: D16)
For det første må vi finne de femte største verdiene ved å bruke LARGE -funksjonen og deretter utføre sumoperasjon over de fem verdiene. Nå vil vi bruke følgende formel for å få summen
Bruk formelen:
{ = STOR (HVIS (region = G5, pris), F5)}
Forklaring:
- IF -funksjonen sjekker kriteriene som samsvarer med alle verdiene i regionen med den angitte østverdien i G5 -cellen og returnerer tilsvarende SANNE verdier fra prisklasse.
= STOR ({58.41; 303.63; FALSK; 153.34; 82.84; FALSK; 520.01; FALSK; 177; FALSK; FALSK; 57.97; 97.72; FALSK}), F5)
- LARGE -funksjonen tar matrisen som har alle prisklasser som vist ovenfor og returnerer den femte største verdien fra matrisen som vist i øyeblikksbildet nedenfor.
Du kan se formelen i formelboksen som vist ovenfor øyeblikksbilde. Bruk Ctrl + Shift + Enter for å få resultatet.
Som du kan se, gir matriseformelen den femte største prisen $ 97,72 med Øst -regionen.
Du kan også mate array som array, kriterier innenfor anførselstegn og n verdi direkte til funksjonen i stedet for å bruke cellereferanse eller navngitt område.
Bruk formelen:
{ = STOR (HVIS (C3: C16 = "Vest", D3: D16), 3)}
Bruk Ctrl + Shift + Enter
Du kan se at formelen fungerer fint der den niende største verdien har kriterier i tabellen.
Niende laveste med kriterier
Hvordan løse problemet?
For denne artikkelen må vi bruke SMALL -funksjonen. Nå skal vi lage en formel av disse funksjonene. Her får vi en tabell, og vi må finne den nth minste verdien i området med gitte kriterier.
Generisk formel:
{ = LITEN (HVIS (c_range = "verdi", område), n ) }
c_range : kriterier
verdi : kriterier matchende verdi
område : resultatmatrise
n : nth største
Merk : Ikke sett krøllete braketter manuelt. Dette er en matriseformel, må brukes Ctrl + Shift + Enter i stedet for bare Enter der den returnerer #NUM! feil.
Eksempel:
Alt dette kan være forvirrende å forstå. Så la oss teste denne formelen ved å kjøre den på eksemplet nedenfor.
Her har vi bestillingsdetaljer med ordredato, region og ordrepris.
Vi må finne ut den niende minste ordreprisen fra Øst -regionen. Her er området gitt som ved bruk av det navngitte området excel -verktøyet.
region (C3: C16)
pris (D3: D16)
For det første må vi finne de femte minste eller laveste verdiene ved hjelp av SMALL -funksjonen og deretter slå opp operasjonen utført over verdiene. Nå vil vi bruke følgende formel for å få summen
Bruk formelen:
{ = LITEN (HVIS (region = G5, pris), F5)}
Forklaring:
- IF -funksjonen sjekker kriteriene som samsvarer med alle verdiene i regionen med den angitte østverdien i G5 -cellen og returnerer tilsvarende SANNE verdier fra prisklasse.
= STOR ({58.41; 303.63; FALSK; 153.34; 82.84; FALSK; 520.01; FALSK; 177; FALSK; FALSK; 57.97; 97.72; FALSK}), F5)
- SMALL -funksjonen tar matrisen som har alle prisklasser som vist ovenfor og returnerer den femte største verdien fra matrisen som vist i øyeblikksbildet nedenfor.
Du kan se formelen i formelboksen som vist ovenfor. Bruk Ctrl + Shift + Enter for å få resultatet.
Som du kan se, gir matriseformelen den femte minste prisen $ 153,34 med Øst -regionen.
Du kan også mate array som array, kriterier innenfor anførselstegn og n verdi direkte til funksjonen i stedet for å bruke cellereferanse eller navngitt område.
Bruk formelen:
{ = LITEN (HVIS (C3: C16 = "vest", D3: D16), 3)}
Bruk Ctrl + Shift + Enter
Du kan se formelen fungerer fint der du finner den nth største verdien som har kriterier i tabellen.
Her er noen observasjonsnotater vist nedenfor.
Merknader:
- Formelen fungerer bare med tall.
- Ikke sett krøllete braketter manuelt. Dette er en matriseformel, må brukes Ctrl + Shift + Enter i stedet for bare Enter der den returnerer #NUM! feil.
- Verdien av kan ikke være større enn antall kriterier, eller den returnerer #NUM! Feil.
- IF kriterier samsvarer ikke med formelen returnerer feil.
- Argumentmatrisen må ha samme lengde, ellers funksjonen.
Håper denne artikkelen om hvordan du finner nth største med kriterier og nth minste med kriterier i excel er forklarende. Utforsk flere artikler om oppslagsformler i Excel her. Hvis du likte bloggene våre, del den med vennene dine på Facebook. Og du kan også følge oss på Twitter og Facebook. Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv til oss på e -post
Slik bruker du SUMPRODUCT -funksjonen i Excel: Returnerer SUMMEN etter multiplikasjon av verdier i flere matriser i excel.
SUM hvis datoen er mellom : Returnerer SUMMEN av verdier mellom gitte datoer eller periode i excel.
Sum hvis datoen er større enn gitt dato: Returnerer SUMMEN av verdier etter gitt dato eller periode i excel.
2 måter å summere etter måned i Excel: Returnerer SUMMEN av verdier i en gitt spesifikk måned i excel.
Slik summerer du flere kolonner med betingelse: Returnerer SUM av verdier på tvers av flere kolonner som har betingelse i excel
Hvordan bruke jokertegn i excel : Tell celler som matcher setninger ved hjelp av jokertegnene i excel
Populære artikler
50 Excel -snarvei for å øke produktiviteten : Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
Slik bruker du tVLOOKUP -funksjonen i Excel : Dette er en av de mest brukte og populære funksjonene i excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du COUNTIF -funksjonen i Excel : Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel : Dette er en annen viktig funksjon i instrumentbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.