U ovom ćemo članku iz navedene tablice u Excelu naučiti kako pronaći n -ti najmanji s kriterijima i n -ti najveći s kriterijima.
Scenarij:
Jednostavnim riječima, dok radimo s brojevima u podatkovnim brojevima, ponekad se daje uvjet, tj. Kada trebamo potražiti 5. najveću zadanu vrijednost. Rješenje ovog problema možete jednostavno riješiti pomoću excelovih funkcija kako je dolje objašnjeno.
N -ti najveći s kriterijima
Kako riješiti problem?
Za ovaj članak trebat ćemo koristiti funkciju LARGE. Sada ćemo od ovih funkcija napraviti formulu. Ovdje nam je dana tablica i moramo pronaći n -tu najveću vrijednost u rasponu s zadanim kriterijima.
Opća formula:
{ = VELIKO (IF (c_range = "vrijednost", raspon), n ) }
c_područje : raspon kriterija
vrijednost : vrijednost kriterija odgovara
domet : rezultatni niz
n : nth najveća
Bilješka : Ne stavljajte kovrčave zagrade ručno. Ovo je formula niza, mora se koristiti Ctrl + Shift + Enter umjesto samo Enter gdje vraća #NUM! pogreška.
Primjer:
Sve ovo može biti zbunjujuće za razumijevanje. Dakle, isprobajmo ovu formulu tako da je pokrenemo na dolje prikazanom primjeru.
Ovdje imamo detalje narudžbe s datumom narudžbe, regijom i cijenom narudžbe.
Moramo saznati 5. najveću cijenu narudžbe iz istočne regije. Ovdje je raspon dat kao pomoću imenovanog alata Excel excel.
regija (C3: C16)
cijena (D3: D16)
Prvo, moramo pronaći petu najveću vrijednost pomoću funkcije LARGE, a zatim operaciju zbroja izvršiti nad tih 5 vrijednosti. Sada ćemo koristiti sljedeću formulu za dobivanje zbroja
Koristite formulu:
{ = VELIKO (IF (regija = G5, cijena), F5)}
Obrazloženje:
- IF funkcija provjerava kriterije koji odgovaraju svim vrijednostima u regiji s danom istočnom vrijednošću u ćeliji G5 i vraća odgovarajuće TRUE vrijednosti iz raspona cijena.
= VELIKO ({58,41; 303,63; FALSE; 153,34; 82,84; FALSE; 520,01; FALSE; 177; FALSE; FALSE; 57,97; 97,72; FALSE}), F5)
- LARGE funkcija uzima niz koji ima cijeli raspon cijena kako je gore prikazano i vraća 5. najveću vrijednost iz niza kako je prikazano na donjoj snimci.
Formulu možete vidjeti u okviru s formulom kao što je prikazano na gornjoj snimci. Pritisnite Ctrl + Shift + Enter da biste dobili rezultat.
Kao što vidite, formula niza vraća petu najveću cijenu od 97,72 USD u istočnoj regiji.
Također možete unijeti niz kao niz, kriterije unutar navodnika i n vrijednosti izravno u funkciju umjesto korištenja reference ćelije ili imenovanog raspona.
Koristite formulu:
{ = VELIKO (AKO (C3: C16 = "Zapad", D3: D16), 3)}
Koristiti Ctrl + Shift + Enter
Možete vidjeti da formula radi dobro gdje pronaći n -tu najveću vrijednost koja ima kriterije u tablici.
N -ti najniži s kriterijima
Kako riješiti problem?
Za ovaj ćemo članak morati koristiti funkciju MALO. Sada ćemo od ovih funkcija napraviti formulu. Ovdje nam se daje tablica i moramo pronaći n -tu najmanju vrijednost u rasponu s zadanim kriterijima.
Opća formula:
{ = MALO (AKO (c_range = "vrijednost", raspon), n ) }
c_područje : raspon kriterija
vrijednost : vrijednost kriterija odgovara
domet : rezultatni niz
n : nth najveća
Bilješka : Ne stavljajte kovrčave zagrade ručno. Ovo je formula niza, mora se koristiti Ctrl + Shift + Enter umjesto samo Enter gdje vraća #NUM! pogreška.
Primjer:
Sve ovo može biti zbunjujuće za razumijevanje. Dakle, isprobajmo ovu formulu tako da je pokrenemo na dolje prikazanom primjeru.
Ovdje imamo detalje narudžbe s datumom narudžbe, regijom i cijenom narudžbe.
Moramo saznati n -tu najmanju cijenu narudžbe iz istočne regije. Ovdje je raspon dat kao pomoću imenovanog alata Excel excel.
regija (C3: C16)
cijena (D3: D16)
Prvo, moramo pronaći Petu najmanju ili najnižu vrijednost pomoću funkcije SMALL, a zatim potražiti operaciju nad vrijednostima. Sada ćemo koristiti sljedeću formulu za dobivanje zbroja
Koristite formulu:
{ = MALO (AKO (regija = G5, cijena), F5)}
Obrazloženje:
- IF funkcija provjerava kriterije koji odgovaraju svim vrijednostima u regiji s danom istočnom vrijednošću u ćeliji G5 i vraća odgovarajuće TRUE vrijednosti iz raspona cijena.
= VELIKO ({58,41; 303,63; FALSE; 153,34; 8,84; FALSE; 520,01; FALSE; 177; FALSE; FALSE; 57,97; 97,72; FALSE}), F5)
- SMALL funkcija uzima niz koji ima cjenovni raspon kako je gore prikazano i vraća 5. najveću vrijednost iz niza kako je prikazano na donjoj snimci.
Formulu možete vidjeti u okviru s formulom kao što je prikazano na gornjoj snimci. Koristiti Ctrl + Shift + Enter da biste dobili rezultat.
Kao što vidite, formula niza vraća petu najmanju cijenu od 153,34 USD za istočnu regiju.
Također možete unijeti niz kao niz, kriterije unutar navodnika i n vrijednost izravno u funkciju umjesto da koristite referencu ćelije ili imenovani raspon.
Koristite formulu:
{ = MALO (AKO (C3: C16 = "zapad", D3: D16), 3)}
Koristiti Ctrl + Shift + Enter
Možete vidjeti da formula dobro radi ako u tablici pronađete n -tu najveću vrijednost koja ima kriterije.
Evo nekoliko opservacijskih napomena prikazanih u nastavku.
Bilješke:
- Formula radi samo s brojevima.
- Ne stavljajte kovrčave zagrade ručno. Ovo je formula niza, mora se koristiti Ctrl + Shift + Enter umjesto samo Enter gdje vraća #NUM! pogreška.
- Vrijednost ne može biti veća od broja kriterija ili vraća #NUM! Pogreška.
- Ako kriteriji ne odgovaraju formuli, vraća se pogreška.
- Polje argumenata mora biti iste duljine, osim funkcije.
Nadam se da je ovaj članak o tome kako pronaći n -ti najveći s kriterijima i n -ti najmanji s kriterijima u excelu objašnjen. Ovdje istražite više članaka o obrascima za pretraživanje u Excelu. Ako vam se sviđaju naši blogovi, podijelite ih sa svojim prijateljima na Facebooku. Također nas možete pratiti na Twitteru i Facebooku. Voljeli bismo čuti vaše mišljenje, javite nam kako možemo poboljšati, nadopuniti ili inovirati svoj rad i učiniti ga boljim za vas. Pišite nam na web stranici e -pošte
Kako koristiti funkciju SUMPRODUCT u Excelu: Vraća SUM nakon množenja vrijednosti u više niza u Excelu.
ZBIRAJTE ako je datum između : Vraća ZBOR vrijednosti između zadanih datuma ili razdoblja u Excelu.
Zbroj ako je datum veći od navedenog datuma: Vraća ZBOR vrijednosti nakon zadanog datuma ili razdoblja u Excelu.
2 načina zbrajanja po mjesecima u Excelu: Vraća ZBOR vrijednosti unutar određenog određenog mjeseca u Excelu.
Kako zbrojiti više stupaca s uvjetom: Vraća ZBOR vrijednosti u više stupaca sa uvjetom u excelu
Kako koristiti zamjenske znakove u Excelu : Brojanje ćelija koje se podudaraju s izrazima pomoću zamjenskih znakova u Excelu
Popularni članci
50 Excel prečac za povećanje vaše produktivnosti : Brže izvršite svoj zadatak. Ovih 50 prečaca učinit će da radite još brže na Excelu.
Kako koristiti tFunkcija VLOOKUP u Excelu : Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
Kako koristiti funkciju COUNTIF u Excelu : Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste brojali određene vrijednosti. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.
Kako koristiti funkciju SUMIF u Excelu : Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.
