Hitta nth största med kriterier & nth minsta med kriterier i excel

Anonim

I den här artikeln lär vi oss hur man hittar n: a minsta med kriterier och nth största med kriterier från den angivna tabellen i Excel.

Scenario:

Med enkla ord, medan vi arbetar med siffror i datanummer, ibland med ett villkor, dvs. när vi behöver leta efter det femte högsta värdet som ges. Du kan enkelt utföra lösningen på detta problem med hjälp av Excel -funktionerna enligt nedan.

N: e största med kriterier

Hur löser man problemet?

För den här artikeln kommer vi att behöva använda funktionen STOR. Nu kommer vi att göra en formel av dessa funktioner. Här får vi en tabell och vi måste hitta det n: e största värdet i intervallet med givna kriterier.

Generisk formel:

{ = STOR (OM (c_range = "värde", intervall), n ) }

c_område : kriterier

värde : matchningsvärde för kriterier

räckvidd : resultatmatris

n : nth största

Notera : Placera inte lockfästen manuellt. Detta är en matrisformel, måste användas Ctrl + Skift + Enter i stället för bara Enter där den returnerar #NUM! fel.

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 orderinformation med orderdatum, region och orderpris.

Vi måste ta reda på det femte största orderpriset från East region. Här anges intervallet med hjälp av det namngivna excel -verktyget.

region (C3: C16)

pris (D3: D16)

För det första måste vi hitta de femte största värdena med hjälp av funktionen LARGE och sedan utföra summan operation över dessa 5 värden. Nu kommer vi att använda följande formel för att få summan

Använd formeln:

{ = STOR (OM (region = G5, pris), F5)}

Förklaring:

  • IF -funktionen kontrollerar kriterierna som matchar alla värden i regionen med det givna östra värdet i G5 -cellen och returnerar motsvarande SAN -värden från prisklass.

= STORT ({58.41; 303.63; FALSKT; 153.34; 82.84; FALSKT; 520.01; FALSKT; 177; FALSKT; FALSKT; 57.97; 97.72; FALSKT}), F5)

  • LARGE -funktionen tar arrayen som har alla prisklasser som visas ovan och returnerar det femte största värdet från arrayen som visas i ögonblicksbilden nedan.


Du kan se formeln i formelrutan som som visas ovan ögonblicksbild. Använd Ctrl + Skift + Retur för att få resultatet.

Som du kan se matrisformeln returnerar det femte största priset $ 97,72 med East region.

Du kan också mata array som array, kriterier inom citattecken och n -värde direkt till funktionen istället för att använda cellreferens eller namngivet intervall.
Använd formeln:

{ = STOR (OM (C3: C16 = "Väst", D3: D16), 3)}

Använda sig av Ctrl + Skift + Enter

Du kan se formeln fungerar bra där hitta det n: e största värdet med kriterier i tabellen.

N: e lägsta med kriterier

Hur löser man problemet?

För den här artikeln kommer vi att behöva använda SMALL -funktionen. Nu kommer vi att göra en formel av dessa funktioner. Här får vi en tabell och vi måste hitta det n: e minsta värdet i intervallet med givna kriterier.

Generisk formel:

{ = LILLA (OM (c_range = "värde", intervall), n ) }

c_område : kriterier

värde : matchningsvärde för kriterier

räckvidd : resultatmatris

n : nth största
Notera : Placera inte lockfästen manuellt. Detta är en matrisformel, måste användas Ctrl + Skift + Enter i stället för bara Enter där den returnerar #NUM! fel.

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 orderinformation med orderdatum, region och orderpris.

Vi måste ta reda på det n: a minsta orderpriset från East region. Här anges intervallet med hjälp av det namngivna excel -verktyget.

region (C3: C16)

pris (D3: D16)

För det första måste vi hitta de femte minsta eller lägsta värdena med funktionen SMALL och sedan leta upp operationen över värdena. Nu kommer vi att använda följande formel för att få summan

Använd formeln:

{ = LILLA (OM (region = G5, pris), F5)}

Förklaring:

  • IF -funktionen kontrollerar kriterierna som matchar alla värden i regionen med det angivna östra värdet i G5 -cellen och returnerar motsvarande SAN -värden från prisklass.

= STORT ({58.41; 303.63; FALSKT; 153.34; 82.84; FALSKT; 520.01; FALSKT; 177; FALSKT; FALSKT; 57.97; 97.72; FALSKT}), F5)

  • SMALL -funktionen tar arrayen som har alla prisklasser som visas ovan och returnerar det femte största värdet från arrayen som visas i ögonblicksbilden nedan.

Du kan se formeln i formelrutan som som visas ovan ögonblicksbild. Använda sig av Ctrl + Skift + Enter för att få resultatet.

Som du kan se matrisformeln returnerar det femte minsta priset $ 153,34 med östra regionen.

Du kan också mata array som array, kriterier inom citattecken och n -värde direkt till funktionen istället för att använda cellreferens eller namngivet intervall.
Använd formeln:

{ = Liten (OM (C3: C16 = "väst", D3: D16), 3)}

Använda sig av Ctrl + Skift + Enter

Du kan se formeln fungerar bra där du hittar det n: e största värdet med kriterier i tabellen.

Här är några observationsanteckningar som visas nedan.

Anmärkningar:

  1. Formeln fungerar bara med siffror.
  2. Placera inte lockfästen manuellt. Detta är en matrisformel, måste användas Ctrl + Skift + Enter i stället för bara Enter där den returnerar #NUM! fel.
  3. Värdet på får inte vara större än antalet kriterievärden eller det returnerar #NUM! Fel.
  4. OM kriterierna inte matchar formelreturfelet.
  5. Argumentmatrisen måste ha samma längd annars funktionen.

Hoppas den här artikeln om hur man hittar n: a största med kriterier och nth minsta med kriterier i excel är förklarande. Utforska fler artiklar om uppslagsformler i Excel här. Om du gillade våra bloggar, dela den med dina vänner 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

Hur man använder jokertecken i excel : Räkna celler som matchar fraser med jokertecken 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.