Šiame straipsnyje mes išmoksime, kaip iš pateiktos „Excel“ lentelės rasti n -tą mažiausią pagal kriterijus ir n -tą pagal dydį pagal kriterijus.
Scenarijus:
Paprastais žodžiais tariant, dirbant su skaičiais duomenų skaičiumi, kartais suteikiama sąlyga, ty kai reikia ieškoti 5 -osios didžiausios pateiktos vertės. Galite lengvai išspręsti šią problemą naudodami „Excel“ funkcijas, kaip paaiškinta žemiau.
N pagal dydį pagal kriterijus
Kaip išspręsti problemą?
Šiame straipsnyje turėsime naudoti LARGE funkciją. Dabar iš šių funkcijų sudarysime formulę. Čia mums pateikiama lentelė ir turime rasti n -tą didžiausią diapazono vertę pagal nurodytus kriterijus.
Bendra formulė:
{ = DIDELIS (IF (c_range = "value", range), n ) }
c_ diapazonas : kriterijų diapazonas
vertės : kriterijai atitinka vertę
diapazonas : rezultatų masyvas
n : n pagal dydį
Pastaba : Rankiniu būdu nedėkite garbanotų skliaustų. Tai masyvo formulė, kurią reikia naudoti Ctrl + Shift + Enter vietoj tiesiog įveskite, kur grąžina #NUM! klaida.
Pavyzdys:
Visa tai gali būti painu suprasti. Taigi, išbandykime šią formulę, paleisdami ją žemiau pateiktame pavyzdyje.
Čia mes turime išsamią užsakymo informaciją su užsakymo data, regionu ir užsakymo kaina.
Turime išsiaiškinti 5 -ąją didžiausią užsakymo kainą iš Rytų regiono. Čia diapazonas pateikiamas kaip naudojant pavadintą „Excel“ įrankį.
regionas (C3: C16)
kaina (D3: D16)
Pirmiausia, naudojant funkciją LARGE, turime rasti penktąsias didžiausias vertes, o tada sumos operacija atliekama toms 5 reikšmėms. Dabar sumai gauti naudosime šią formulę
Naudokite formulę:
{ = DIDELIS (IF (regionas = G5, kaina), F5)}
Paaiškinimas:
- Funkcija IF patikrina kriterijus, atitinkančius visas regiono reikšmes su nurodyta Rytų reikšme G5 langelyje, ir grąžina atitinkamas TRUE vertes iš kainų diapazono.
= DIDELIS ({58.41; 303.63; FALSE; 153.34; 82.84; FALSE; 520.01; FALSE; 177; FALSE; FALSE; 57.97; 97.72; FALSE}), F5)
- Funkcija LARGE paima masyvą, kurio kainų diapazonas yra toks, kaip parodyta aukščiau, ir grąžina 5 -ąją pagal dydį masyvo vertę, kaip parodyta žemiau esančioje nuotraukoje.
Formulės laukelyje galite peržiūrėti formulę, kaip parodyta aukščiau esančioje momentinėje nuotraukoje. Norėdami gauti rezultatą, naudokite „Ctrl“ + „Shift“ + „Enter“.
Kaip matote, masyvo formulė grąžina 5 -ąją didžiausią kainą - 97,72 USD, turintį Rytų regioną.
Taip pat galite masyvą tiekti kaip masyvą, kriterijus kabutėse ir n reikšmę tiesiai į funkciją, o ne naudoti langelio nuorodą ar pavadintą diapazoną.
Naudokite formulę:
{ = DIDELIS (JEI (C3: C16 = "Vakarai", D3: D16), 3)}
Naudoti Ctrl + Shift + Enter
Matote, kad formulė veikia gerai, kai lentelėje rasite n -tą didžiausią vertę, turinčią kriterijus.
N žemiausias pagal kriterijus
Kaip išspręsti problemą?
Šiame straipsnyje mes turėsime naudoti funkciją SMALL. Dabar iš šių funkcijų sudarysime formulę. Čia mums pateikiama lentelė ir turime rasti n -ąją mažiausią diapazono vertę pagal nurodytus kriterijus.
Bendra formulė:
{ = SMALL (IF (c_range = "value", range), n ) }
c_ diapazonas : kriterijų diapazonas
vertės : kriterijai atitinka vertę
diapazonas : rezultatų masyvas
n : n pagal dydį
Pastaba : Rankiniu būdu nedėkite garbanotų skliaustų. Tai masyvo formulė, kurią reikia naudoti Ctrl + Shift + Enter vietoj tiesiog įveskite, kur grąžina #NUM! klaida.
Pavyzdys:
Visa tai gali būti painu suprasti. Taigi, išbandykime šią formulę, paleisdami ją žemiau pateiktame pavyzdyje.
Čia mes turime išsamią užsakymo informaciją su užsakymo data, regionu ir užsakymo kaina.
Turime išsiaiškinti n -tą mažiausią užsakymo kainą iš Rytų regiono. Čia diapazonas pateikiamas kaip naudojant pavadintą „Excel“ įrankį.
regionas (C3: C16)
kaina (D3: D16)
Pirma, mes turime rasti penktąją mažiausią ar mažiausią reikšmę, naudodamiesi funkcija SMALL, o tada ieškoti operacijos, kurią reikia atlikti. Dabar sumai gauti naudosime šią formulę
Naudokite formulę:
{ = MAŽAS (IF (regionas = G5, kaina), F5)}
Paaiškinimas:
- Funkcija IF patikrina kriterijus, atitinkančius visas regiono vertes su nurodyta Rytų reikšme G5 langelyje, ir grąžina atitinkamas TRUE vertes iš kainų diapazono.
= DIDELIS ({58.41; 303.63; FALSE; 153.34; 82.84; FALSE; 520.01; FALSE; 177; FALSE; FALSE; 57.97; 97.72; FALSE}), F5)
- Funkcija SMALL paima masyvą, kurio kainų diapazonas yra toks, kaip parodyta aukščiau, ir grąžina 5 -ąją pagal dydį masyvo vertę, kaip parodyta žemiau esančioje nuotraukoje.
Formulės laukelyje galite peržiūrėti formulę, kaip parodyta aukščiau esančioje momentinėje nuotraukoje. Naudoti Ctrl + Shift + Enter kad gautumėte rezultatą.
Kaip matote, masyvo formulė grąžina 5 -ą mažiausią kainą 153,34 USD, turintį Rytų regioną.
Taip pat galite masyvą tiekti kaip masyvą, kriterijus kabutėse ir n reikšmę tiesiai į funkciją, o ne naudoti langelio nuorodą ar pavadintą diapazoną.
Naudokite formulę:
{ = MAŽAS (JEI (C3: C16 = "vakarai", D3: D16), 3)}
Naudoti Ctrl + Shift + Enter
Matote, kad formulė veikia gerai, kai lentelėje rasite n -tą pagal dydį vertę, turinčią kriterijus.
Štai keletas stebėjimo pastabų, parodytų žemiau.
Pastabos:
- Formulė veikia tik su skaičiais.
- Nedėkite garbanotų skliaustų rankiniu būdu. Tai masyvo formulė, kurią reikia naudoti Ctrl + Shift + Enter vietoj tiesiog įveskite, kur grąžina #NUM! klaida.
- Reikšmė negali būti didesnė už kriterijų skaičių, arba ji grąžina #NUM! Klaida.
- IF kriterijai neatitinka formulės grąžinimo klaidos.
- Argumentų masyvas turi būti tokio paties ilgio, kitaip funkcija.
Tikimės, kad šis straipsnis yra aiškinantis, kaip rasti n -tą pagal dydį kriterijus ir n -tą mažiausią su kriterijais. Čia rasite daugiau straipsnių apie „Excel“ paieškos formules. Jei jums patiko mūsų tinklaraščiai, pasidalykite jais su draugais „Facebook“. Taip pat galite sekti mus „Twitter“ ir „Facebook“. Mes norėtume išgirsti jūsų nuomonę, praneškite mums, kaip galime patobulinti, papildyti ar naujovinti savo darbą ir padaryti jį geresnį. Parašykite mums el
Kaip naudoti „SUMPRODUCT“ funkciją programoje „Excel“: Grąžina SUM, padauginus reikšmes keliuose „Excel“ masyvuose.
SUM, jei data yra tarp : Grąžina verčių SUM tarp nurodytų datų ar „Excel“ laikotarpio.
Suma, jei data yra didesnė už nurodytą datą: Grąžina verčių SUM po nurodytos datos ar „Excel“ laikotarpio.
2 būdai suskaičiuoti pagal mėnesį „Excel“: Grąžina verčių SUM per tam tikrą „Excel“ mėnesį.
Kaip suskaičiuoti kelis stulpelius su sąlyga: Grąžina verčių SUM keliuose stulpeliuose su sąlyga „Excel“
Kaip „Excel“ naudoti pakaitos simbolius : Suskaičiuokite frazes atitinkančias frazes naudodami „Excel“ pakaitos simbolius
Populiarūs straipsniai
50 „Excel“ spartusis klavišas produktyvumui padidinti : Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.
Kaip naudotis t„VLOOKUP“ funkcija „Excel“ : Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama vertei iš įvairių diapazonų ir lapų ieškoti.
Kaip naudoti funkciją „COUNTIF“ programoje „Excel“ : Skaičiuokite vertes su sąlygomis naudodami šią nuostabią funkciją. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudoti „SUMIF“ funkciją „Excel“ : Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.