У овом чланку ћемо научити како да пронађемо н -ти најмањи са критеријумима и н -ти највећи са критеријумима из дате табеле у Екцелу.
Сценариј:
Једноставним речима, док радимо са бројевима у бројевима података, понекад се даје услов, односно када морамо да потражимо 5. највећу дату вредност. Решење овог проблема можете лако извршити помоћу екцел функција како је доле објашњено.
Н -ти највећи са критеријумима
Како решити проблем?
За овај чланак ћемо морати да користимо функцију ЛАРГЕ. Сада ћемо направити формулу од ових функција. Овде нам је дата табела и морамо да пронађемо н -ту највећу вредност у опсегу са датим критеријумима.
Општа формула:
{ = ВЕЛИКО (ИФ (ц_ранге = "валуе", ранге), н ) }
ц_ранге : распон критеријума
вредност : вредност критеријума се подудара
домет : матрица резултата
н : нтх највеће
Белешка : Не стављајте коврчаве заграде ручно. Ово је формула низа, мора се користити Цтрл + Схифт + Ентер уместо само Ентер где враћа #НУМ! грешка.
Пример:
Све ово може бити збуњујуће за разумевање. Дакле, хајде да тестирамо ову формулу тако што ћемо је покренути на примеру приказаном испод.
Овде имамо детаље поруџбине који садрже датум наруџбе, регион и цену налога.
Морамо сазнати пету највећу цену наруџбине из источног региона. Овде је опсег дат као коришћење именованог алата Екцел екцел.
регион (Ц3: Ц16)
цена (Д3: Д16)
Прво морамо пронаћи пету највећу вредност помоћу функције ЛАРГЕ, а затим операцију збира извршити над тих 5 вредности. Сада ћемо користити следећу формулу да добијемо збир
Користите формулу:
{ = ВЕЛИКО (ИФ (регион = Г5, цена), Ф5)}
Објашњење:
- ИФ функција проверава критеријуме који одговарају свим вредностима у региону са датом источном вредношћу у ћелији Г5 и враћа одговарајуће ТРУЕ вредности из распона цена.
= ВЕЛИКО ({58,41; 303,63; ФАЛСЕ; 153,34; 82,84; ФАЛСЕ; 520,01; ФАЛСЕ; 177; ФАЛСЕ; ФАЛСЕ; 57,97; 97,72; ФАЛСЕ}), Ф5)
- ЛАРГЕ функција узима низ који има цео распон цена као што је приказано горе и враћа 5. највећу вредност из низа као што је приказано на доњем снимку.
Формулу можете погледати у пољу за формуле као што је приказано на горњој снимци. Користите Цтрл + Схифт + Ентер да бисте добили резултат.
Као што видите, формула низа враћа пету највећу цену од 97,72 долара у источном региону.
Такође можете да унесете низ као низ, критеријуме унутар наводника и н вредности директно у функцију уместо да користите ћелијску референцу или именовани опсег.
Користите формулу:
{ = ВЕЛИКО (АКО (Ц3: Ц16 = "Запад", Д3: Д16), 3)}
Употреба Цтрл + Схифт + Ентер
Можете видети да формула добро функционише где пронаћи н -ту највећу вредност која има критеријуме у табели.
Н -ти најнижи са критеријумима
Како решити проблем?
За овај чланак ћемо морати да користимо функцију СМАЛЛ. Сада ћемо направити формулу од ових функција. Овде нам је дата табела и морамо пронаћи н -ту најмању вредност у опсегу са датим критеријумима.
Општа формула:
{ = МАЛО (АКО (ц_ранге = "вредност", опсег), н ) }
ц_ранге : распон критеријума
вредност : вредност критеријума се подудара
домет : матрица резултата
н : нтх највеће
Белешка : Не стављајте коврчаве заграде ручно. Ово је формула низа, мора се користити Цтрл + Схифт + Ентер уместо само Ентер где враћа #НУМ! грешка.
Пример:
Све ово може бити збуњујуће за разумевање. Дакле, хајде да тестирамо ову формулу тако што ћемо је покренути на примеру приказаном испод.
Овде имамо детаље поруџбине који садрже датум наруџбе, регион и цену налога.
Морамо да сазнамо н -ту најмању цену наруџбине из источног региона. Овде је опсег дат коришћењем именованог алата Екцел екцел.
регион (Ц3: Ц16)
цена (Д3: Д16)
Прво, морамо пронаћи Пету најмању или најнижу вредност помоћу функције СМАЛЛ, а затим потражити операцију преко вредности. Сада ћемо користити следећу формулу да добијемо збир
Користите формулу:
{ = МАЛО (АКО (регион = Г5, цена), Ф5)}
Објашњење:
- ИФ функција проверава критеријуме који одговарају свим вредностима у региону са датом источном вредношћу у ћелији Г5 и враћа одговарајуће ТРУЕ вредности из распона цена.
= ВЕЛИКО ({58,41; 303,63; ФАЛСЕ; 153,34; 82,84; ФАЛСЕ; 520,01; ФАЛСЕ; 177; ФАЛСЕ; ФАЛСЕ; 57,97; 97,72; ФАЛСЕ}), Ф5)
- СМАЛЛ функција узима низ који има цео распон цена као што је приказано горе и враћа 5. највећу вредност из низа као што је приказано на доњем снимку.
Формулу можете погледати у пољу за формуле као што је приказано на горњој снимци. Употреба Цтрл + Схифт + Ентер да бисте добили резултат.
Као што видите, формула низа враћа пету најмању цену од 153,34 УСД за источни регион.
Такође можете да унесете низ као низ, критеријуме унутар наводника и н вредности директно у функцију уместо да користите ћелијску референцу или именовани опсег.
Користите формулу:
{ = МАЛО (АКО (Ц3: Ц16 = "запад", Д3: Д16), 3)}
Употреба Цтрл + Схифт + Ентер
Можете видети да формула ради добро тамо где пронађете н -ту највећу вредност која има критеријуме у табели.
Ево неколико опсервационих белешки приказаних испод.
Напомене:
- Формула ради само са бројевима.
- Не стављајте коврчаве заграде ручно. Ово је формула низа, мора се користити Цтрл + Схифт + Ентер уместо само Ентер где враћа #НУМ! грешка.
- Вредност не може бити већа од броја критеријума или враћа #НУМ! Грешка.
- Ако критеријуми не одговарају формули, враћа се грешка.
- Аргумент низ мора бити исте дужине, иначе функција.
Надам се да је овај чланак о томе како пронаћи н -ти највећи са критеријумима и н -ти најмањи са критеријумима у екцелу објашњен. Овде истражите још чланака о претраживачким формама у Екцелу. Ако вам се допадају наши блогови, поделите их са пријатељима на Фацебооку. Такође нас можете пратити на Твиттер -у и Фацебоок -у. Волели бисмо да чујемо од вас, реците нам како можемо побољшати, допунити или иновирати наш рад и учинити га бољим за вас. Пишите нам на веб локацији е -поште
Како се користи функција СУМПРОДУЦТ у програму Екцел: Враћа СУМ након множења вредности у више низа у Екцелу.
ЗБИРАЈТЕ ако је датум између : Враћа ЗБОР вредности између датих датума или периода у Екцелу.
Зброј ако је датум већи од датума: Враћа ЗБОР вредности након датог датума или тачке у Екцелу.
2 начина збрајања по месецима у Екцелу: Враћа ЗБОР вредности у датом месецу у Екцелу.
Како збрајати више колона са условом: Враћа СУМ вредности у више колона са условом у екцелу
Како користити џокере у екцелу : Броји ћелије које се подударају са фразама користећи џокер знакове у Екцелу
Популарни чланци
50 Екцел пречица за повећање ваше продуктивности : Убрзајте свој задатак. Ових 50 пречица ће учинити да радите још брже на Екцелу.
Како се користи тФункција ВЛООКУП у Екцелу : Ово је једна од најчешће кориштених и најпопуларнијих функција програма Екцел која се користи за тражење вриједности из различитих распона и листова.
Како се користи функција ЦОУНТИФ у програму Екцел : Бројте вредности са условима користећи ову невероватну функцију. Не морате да филтрирате податке да бисте рачунали одређене вредности. Цоунтиф функција је неопходна за припрему ваше контролне табле.
Како се користи функција СУМИФ у програму Екцел : Ово је још једна битна функција контролне табле. Ово вам помаже да сумирате вредности под одређеним условима.