I denne artikel lærer vi, hvordan man finder nth mindste med kriterier og nth største med kriterier fra den givne tabel i Excel.
Scenarie:
Med enkle ord, mens vi arbejder med tal i datatal, nogle gange givet en betingelse, dvs. når vi skal slå op efter den 5. højeste værdi, der er givet. Du kan let udføre løsningen på dette problem ved hjælp af excel -funktionerne som forklaret nedenfor.
Nth største med kriterier
Hvordan løser man problemet?
I denne artikel skal vi bruge funktionen LARGE. Nu vil vi lave en formel ud af disse funktioner. Her får vi en tabel, og vi skal finde den nth største værdi i området med givne kriterier.
Generisk formel:
{ = LARGE (HVIS (c_range = "værdi", område), n ) }
c_range : kriterier
værdi : kriterier matchende værdi
rækkevidde : resultat array
n : nth største
Bemærk : Sæt ikke krøllede beslag manuelt. Dette er en matrixformel, skal bruges Ctrl + Shift + Enter i stedet for bare Enter, hvor det returnerer #NUM! fejl.
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 ordredetaljer med ordredato, område og ordrepris.
Vi skal finde ud af den 5. største ordrepris fra regionen Øst. Her angives intervallet som ved hjælp af det navngivne område excel -værktøj.
område (C3: C16)
pris (D3: D16)
For det første skal vi finde de femte største værdier ved hjælp af funktionen LARGE og derefter udføres sumoperation over disse 5 værdier. Nu vil vi bruge følgende formel for at få summen
Brug formlen:
{ = STOR (HVIS (region = G5, pris), F5)}
Forklaring:
- HVIS funktion kontrollerer kriterierne, der matcher alle værdierne i regionen med den givne østlige værdi i G5 -cellen, og returnerer de korrekte SANDE værdier fra prisklassen.
= STOR ({58.41; 303.63; FALSK; 153.34; 82.84; FALSK; 520.01; FALSK; 177; FALSK; FALSK; 57.97; 97.72; FALSK}), F5)
- LARGE -funktionen tager arrayet, der har alt prisklassen som vist ovenfor, og returnerer den 5. største værdi fra arrayet som vist i nedenstående snapshot.
Du kan se formlen i formelfeltet som vist ovenfor over snapshot. Brug Ctrl + Shift + Enter for at få resultatet.
Som du kan se matrixformlen returnerer den 5. største pris $ 97,72 med East region.
Du kan også indføre array som array, kriterier inden for anførselstegn og n -værdi direkte til funktionen i stedet for at bruge cellereference eller navngivet område.
Brug formlen:
{ = STOR (HVIS (C3: C16 = "Vest", D3: D16), 3)}
Brug Ctrl + Shift + Enter
Du kan se, at formlen fungerer fint, hvor den niende største værdi har kriterier i tabellen.
Nth laveste med kriterier
Hvordan løser man problemet?
Til denne artikel skal vi bruge SMALL -funktionen. Nu vil vi lave en formel ud af disse funktioner. Her får vi en tabel, og vi skal finde den nth mindste værdi i området med givne kriterier.
Generisk formel:
{ = LILLE (HVIS (c_range = "værdi", område), n ) }
c_range : kriterier
værdi : matchende værdi for kriterier
rækkevidde : resultat array
n : nth største
Bemærk : Sæt ikke krøllede beslag manuelt. Dette er en matrixformel, skal bruges Ctrl + Shift + Enter i stedet for bare Enter, hvor det returnerer #NUM! fejl.
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 ordredetaljer med ordredato, område og ordrepris.
Vi er nødt til at finde ud af den nth mindste ordrepris fra regionen Øst. Her angives intervallet som ved hjælp af det navngivne område excel -værktøj.
område (C3: C16)
pris (D3: D16)
For det første er vi nødt til at finde de femte mindste eller laveste værdier ved hjælp af SMALL -funktionen og derefter slå op for at udføre værdierne. Nu vil vi bruge følgende formel for at få summen
Brug formlen:
{ = LILLE (HVIS (region = G5, pris), F5)}
Forklaring:
- HVIS funktion kontrollerer kriterierne, der matcher alle værdierne i regionen med den givne østlige værdi i G5 -cellen, og returnerer de korrekte SANDE værdier fra prisklassen.
= STOR ({58,41; 303,63; FALSK; 153,34; 82,84; FALSK; 520,01; FALSK; 177; FALSK; FALSK; 57,97; 97,72; FALSK}), F5)
- SMALL -funktionen tager arrayet, der har hele prisintervallet som vist ovenfor, og returnerer den 5. største værdi fra arrayet som vist i nedenstående snapshot.
Du kan se formlen i formelfeltet som vist ovenfor over snapshot. Brug Ctrl + Shift + Enter for at få resultatet.
Som du kan se matrixformlen returnerer den 5. mindste pris $ 153,34 med Østregion.
Du kan også indføre array som array, kriterier inden for anførselstegn og n -værdi direkte til funktionen i stedet for at bruge cellereference eller navngivet område.
Brug formlen:
{ = LILLE (HVIS (C3: C16 = "vest", D3: D16), 3)}
Brug Ctrl + Shift + Enter
Du kan se formlen fungerer fint, hvor du finder den nth største værdi med kriterier i tabellen.
Her er nogle observationsnoter vist nedenfor.
Bemærkninger:
- Formlen fungerer kun med tal.
- Sæt ikke krøllede beslag manuelt. Dette er en matrixformel, skal bruges Ctrl + Shift + Enter i stedet for bare Enter, hvor det returnerer #NUM! fejl.
- Værdien af må ikke være større end antallet af kriterieværdier, eller den returnerer #NUM! Fejl.
- HVIS kriterier ikke matcher formlen returnerer fejl.
- Argumentmatrixen skal have samme længde, ellers skal funktionen.
Håber, at denne artikel om, hvordan man finder nth største med kriterier og nth mindste med kriterier i excel er forklarende. Udforsk flere artikler om opslagsformler i Excel her. Hvis du kunne lide vores blogs, kan du dele den med dine venner 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
Sådan bruges jokertegn i excel : Tæl celler, der matcher sætninger ved hjælp af jokertegnene 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.