V tem članku se bomo naučili, kako poiskati nth najmanjšega s kriteriji & nth največjega s kriteriji iz dane tabele v Excelu.
Scenarij:
Preprosto povedano, pri delu s številkami v podatkovnih številkah včasih pod pogojem, tj. Ko moramo poiskati 5. najvišjo podano vrednost. Rešitev te težave lahko preprosto rešite s pomočjo excelovih funkcij, kot je razloženo spodaj.
Nth največji s kriteriji
Kako rešiti težavo?
Za ta članek bomo morali uporabiti funkcijo LARGE. Zdaj bomo iz teh funkcij naredili formulo. Tukaj dobimo tabelo in moramo najti n -to največjo vrednost v razponu z danimi merili.
Splošna formula:
{ = LARGE (IF (c_range = "vrednost", obseg), n ) }
c_range : obseg meril
vrednost : vrednost se ujema z merili
obseg : rezultatni niz
n : nth največji
Opomba : Ne postavljajte kodrastih oklepajev ročno. To je matrična formula, ki jo je treba uporabiti Ctrl + Shift + Enter namesto samo Enter, kjer vrne #NUM! napaka.
Primer:
Vse to je lahko zmedeno za razumevanje. Torej, preizkusimo to formulo tako, da jo zaženemo na spodnjem primeru.
Tu imamo podrobnosti naročila z datumom naročila, regijo in ceno naročila.
Ugotoviti moramo peto največjo ceno naročila iz vzhodne regije. Tukaj je obseg podan z uporabo imenovanega orodja excel.
regija (C3: C16)
cena (D3: D16)
Najprej moramo s funkcijo LARGE poiskati peto največjo vrednost, nato pa za teh 5 vrednosti opraviti vsoto. Zdaj bomo uporabili naslednjo formulo, da dobimo vsoto
Uporabite formulo:
{ = VELIKO (ČE (regija = G5, cena), F5)}
Pojasnilo:
- Funkcija IF preveri merila, ki se ujemajo z vsemi vrednostmi v regiji z dano vrednostjo East v celici G5 in vrne ustrezne vrednosti TRUE iz cenovnega razreda.
= VELIKO ({58,41; 303,63; FALSE; 153,34; 8,84; FALSE; 520,01; FALSE; 177; FALSE; FALSE; 57,97; 97,72; FALSE}), F5)
- LARGE funkcija vzame matriko, ki ima ves cenovni razpon, kot je prikazano zgoraj, in vrne peto največjo vrednost iz matrike, kot je prikazano na spodnjem posnetku.
Formulo si lahko ogledate v polju za formule, kot je prikazano na zgornjem posnetku. Če želite dobiti rezultat, uporabite kombinacijo tipk Ctrl + Shift + Enter.
Kot lahko vidite, matrična formula vrača peto največjo ceno 97,72 USD z vzhodno regijo.
Niz lahko posredujete tudi kot matriko, merila v narekovajih in vrednost n neposredno v funkcijo, namesto da uporabite referenco celice ali imenovani obseg.
Uporabite formulo:
{ = VELIKO (ČE (C3: C16 = "zahod", D3: D16), 3)}
Uporaba Ctrl + Shift + Enter
Vidite, da formula dobro deluje, če najdete n -to največjo vrednost z merili v tabeli.
N -ti najnižji z merili
Kako rešiti težavo?
Za ta članek bomo morali uporabiti funkcijo SMALL. Zdaj bomo iz teh funkcij naredili formulo. Tukaj dobimo tabelo in moramo najti n -to najmanjšo vrednost v razponu z danimi merili.
Splošna formula:
{ = MALO (ČE (c_range = "vrednost", obseg), n ) }
c_range : obseg meril
vrednost : vrednost se ujema z merili
obseg : rezultatni niz
n : nth največji
Opomba : Ne postavljajte kodrastih oklepajev ročno. To je matrična formula, ki jo je treba uporabiti Ctrl + Shift + Enter namesto samo Enter, kjer vrne #NUM! napaka.
Primer:
Vse to je lahko zmedeno za razumevanje. Torej, preizkusimo to formulo tako, da jo zaženemo na spodnjem primeru.
Tu imamo podrobnosti naročila z datumom naročila, regijo in ceno naročila.
Ugotoviti moramo n -to najmanjšo ceno naročila iz vzhodne regije. Tukaj je obseg podan z uporabo imenovanega orodja excel.
regija (C3: C16)
cena (D3: D16)
Najprej moramo s funkcijo SMALL poiskati pete najmanjše ali najnižje vrednosti in nato poiskati operacijo nad vrednostmi. Zdaj bomo uporabili naslednjo formulo, da dobimo vsoto
Uporabite formulo:
{ = MALO (ČE (regija = G5, cena), F5)}
Pojasnilo:
- Funkcija IF preveri merila, ki se ujemajo z vsemi vrednostmi v regiji z dano vrednostjo East v celici G5 in vrne ustrezne vrednosti TRUE iz cenovnega razreda.
= VELIKO ({58,41; 303,63; FALSE; 153,34; 8,84; FALSE; 520,01; FALSE; 177; FALSE; FALSE; 57,97; 97,72; FALSE}), F5)
- Funkcija SMALL vzame matriko, ki ima ves cenovni razpon, kot je prikazano zgoraj, in vrne peto največjo vrednost iz matrike, kot je prikazano na spodnjem posnetku.
Formulo si lahko ogledate v polju za formule, kot je prikazano na zgornjem posnetku. Uporaba Ctrl + Shift + Enter da bi dobili rezultat.
Kot lahko vidite, matrična formula vrne peto najmanjšo ceno 153,34 USD z vzhodno regijo.
Niz lahko posredujete tudi kot matriko, merila v narekovajih in vrednost n neposredno v funkcijo, namesto da uporabite referenco celice ali imenovani obseg.
Uporabite formulo:
{ = MALO (ČE (C3: C16 = "zahod", D3: D16), 3)}
Uporaba Ctrl + Shift + Enter
Vidite, da formula dobro deluje, če najdete n -to največjo vrednost z merili v tabeli.
Spodaj je nekaj opazovalnih opomb.
Opombe:
- Formula deluje samo s številkami.
- Ne postavljajte ročatih oklepajev ročno. To je matrična formula, ki jo je treba uporabiti Ctrl + Shift + Enter namesto samo Enter, kjer vrne #NUM! napaka.
- Vrednost ne sme biti večja od števila vrednosti merila ali pa vrne #NUM! Napaka.
- Če se merila IF ne ujemajo, formula vrne napako.
- Polje argumentov mora biti enake dolžine, sicer je funkcija.
Upam, da je ta članek o tem, kako najti nth največjega s kriteriji & nth najmanjšega s kriteriji v excelu, razlaga. Raziščite več člankov o iskalnikih v Excelu tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletni strani elektronske pošte
Kako uporabljati funkcijo SUMPRODUCT v Excelu: Vrne SUM po množenju vrednosti v več matrikah v Excelu.
SUM, če je datum med : Vrne SUM vrednosti med danimi datumi ali obdobjem v Excelu.
Vsota, če je datum večji od navedenega datuma: Vrne SUM vrednosti po danem datumu ali obdobju v Excelu.
2 načina seštevanja po mesecih v Excelu: Vrne SUM vrednosti v določenem mesecu v Excelu.
Kako sešteti več stolpcev s pogojem: Vrne SUM vrednosti v več stolpcih s pogojem v excelu
Kako uporabljati nadomestne znake v Excelu : Preštejte celice, ki se ujemajo z frazami, z uporabo nadomestnih znakov v Excelu
Priljubljeni članki
50 Bližnjica v Excelu za večjo produktivnost : Hitreje opravite svojo nalogo. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati tFunkcija VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.