У овом чланку ћемо научити како индексирати и сортирати вредности помоћу колоне помоћника у Екцелу.
Сценариј:
Понекад рад са необавезним подацима. Морамо да добијемо краћу верзију као потребне податке из података табеле на основу колоне помоћника. Овде ћемо прво разумети како да добијемо помоћну колону, а затим ћемо добити сортиране мале потребне податке на основу података табеле.
Како индексирати вредности у опсегу?
За овај чланак ћемо морати да користимо функцију ЦОУНТИФ. Сада ћемо направити формулу од функције. Овде су дате вредности помешане текста и бројева у опсегу. Морамо их индексирати у растућем редоследу прво нумерички, а затим абецедним редом.
Општа формула:-
| = ЦОУНТИФ (листа, "<=" & ф_валуе) + (ЦОУНТ (листа) * ИСТЕКСТ (ф_валуе)) |
листа: листа бројева и текста
ф_валуе: прва вредност опсега
Објашњење:
- ЦОУНТИФ функција броји број вредности у враћеној листи
- ЦОУНТ функција добија број бројева у опсегу.
- ИСТЕКСТ функција проверава да ли је вредност у изабраној ћелији текст или не. Враћа ТРУЕ за текст и ФАЛСЕ за бројеве.
Пример:
Све ово може бити збуњујуће за разумевање. Хајде да схватимо како да добијемо помоћну колону помоћу објашњене формуле. Овде ће помоћна колона бити заснована на редоследу сортирања вредности (прво бројеви, а затим абецедно). За ово ћемо користити формулу за добијање колоне Индек или ранк као помоћне колоне за податке о трошковима приказане испод.
Овде можете пронаћи вредности у опсегу. За потребе израчунавања користимо именовани распон за фиксни низ Д5: Д12 као трошак.
Користите формулу:
| = ЦОУНТИФ (трошак, "<=" & Д5) + (ЦОУНТ (трошак) * ИСТЕКСТ (Д5)) |
Формула изгледа као што је приказано на горњој снимци. Вредност и низ су дати као именовани опсег и референца ћелије у формули.
Као што видите, индекс прве ћелије у опсегу износи 5, што значи да ако трошкове распоредимо по редоследу сортирања, Електрична енергија биће постављени на пето место. Сада копирајте формулу у друге ћелије помоћу опције превлачења надоле или помоћу пречице Цтрл + Д као што је приказано испод да бисте добили индекс или ранг за остале вредности.
Као што видите, сада имамо списак индекса или ранга који је начин сортирања трошкова. Сада ћемо користити ову колону за ранг као помоћну колону како бисмо добили краћу верзију табеле са истим редоследом у новој листи или табели.
Како сортирати вредности помоћу помоћне колоне у Екцелу?
Број сортирања у Екцелу помоћу помоћне колоне мора имати индексне бројеве и потребан опсег за сортирање. Колона помоћника може бити било која потребна наруџба. На пример, ако требате да добијете листу потребним насумичним редоследом, само поставите ранг који одговара вредности у индексну или колону ранга која ће радити као помоћна колона.
- ИНДЕКС функција
- МАТЦХ функција
- РОВС функција
Сада ћемо направити формулу користећи горње функције. МАТЦХ функција ће вратити индекс најнижег подударања из опсега. Функција ИНДЕКС узима индекс реда као аргумент и враћа одговарајуће потребне резултате. Ова функција проналази
Општа формула:
| = ИНДЕКС (ретурн_арраи, МАТЦХ (РОВС (релативе_референце), Индек, 0)) |
ретурн_арраи: низ из којег се враћа вредност
Релативна референца: генерише растући редослед у формули. Може се користити са било којим низом
Индекс: Индексни низ табеле
0: потпуно подударање
Објашњење:
- РОВС (релативе_референце) враћа вредност према дужини проширене формуле. Ако се примени у првој ћелији, биће једна, па друга и настави док се не прошири.
- МАТЦХ функција подудара индекс са вредношћу ретка како би их поставила у растућем редоследу помоћу функције РОВС.
- ИНДЕКС функција враћа одговарајући индекс са одговарајућом вредношћу.
Пример:
Све ово може бити збуњујуће за разумевање. Дакле, хајде да разумемо ову формулу тако што ћемо је покренути на примеру приказаном испод. Овде смо рангирали податке насумичним редоследом да бисмо добили прве три вредности на основу колоне Индекс. Користите формулу да бисте добили прву вредност потребне кратке табеле.
Користите формулу:
| = ИНДЕКС (Б5: Б13, МАТЦХ (РЕДОВИ (Д5: Д5), Д5: Д13,0)) |
Формула изгледа као што је приказано на горњој снимци. Низ вредности је дат као именовани опсег и референца ћелије.
Као што видите, прва вредност је број из опсега за који се каже да је "Млеко", одговарајући индекс је 1. Сада копирајте формулу у друге ћелије помоћу опције повлачења надоле или помоћу пречице Цтрл + Д као што је приказано испод да бисте добили остале вредности.
Као што видите, вредности су индексиране растућим редоследом. Добили смо све вредности јер то такође доказује да формула ради добро. Можемо издвојити вредности бројева користећи формулу по истој формули.
Као што видите, имамо краће и сортиране вредности из табеле помоћу помоћне колоне која је према томе индексирана.
Ево неколико опсервационих белешки приказаних испод.
Напомене:
- Формула функционише само са бројевима и са текстом.
- Формула занемарује текстуалну вредност приликом упоређивања бројева и занемарује бројеве када се подударају са текстуалним вредностима.
- Помоћна колона може бити било која врста захтеваног реда колона.
Надам се да је овај чланак о томе како индексирати и сортирати вредности помоћу помоћне колоне у Екцелу објашњен. Овде истражите још чланака о формулама за тражење у Екцелу. Ако вам се допадају наши блогови, поделите их са пријатељима на Фацебооку. Такође нас можете пратити на Твиттер -у и Фацебоок -у. Волели бисмо да чујемо од вас, реците нам како можемо побољшати, допунити или иновирати наш рад и учинити га бољим за вас. Пишите нам на веб локацији е -поште
Како се користи функција СУМПРОДУЦТ у програму Екцел: Враћа СУМ након множења вредности у више низа у Екцелу.
ЗБИРАЈТЕ ако је датум између : Враћа ЗБОР вредности између датих датума или периода у Екцелу.
Зброј ако је датум већи од датума: Враћа ЗБОР вредности након датог датума или тачке у Екцелу.
2 начина збрајања по месецима у Екцелу: Враћа ЗБОР вредности у датом месецу у Екцелу.
Како збрајати више колона са условом: Враћа СУМ вредности у више колона са условом у екцелу
Како користити џокере у екцелу : Броји ћелије које се подударају са фразама користећи џокер знакове у Екцелу
Популарни чланци
50 Екцел пречица за повећање ваше продуктивности : Убрзајте свој задатак. Ових 50 пречица ће учинити да радите још брже на Екцелу.
Како се користи тФункција ВЛООКУП у Екцелу : Ово је једна од најчешће кориштених и најпопуларнијих функција програма Екцел која се користи за тражење вриједности из различитих распона и листова.
Како се користи функција ЦОУНТИФ у програму Екцел : Бројте вредности са условима користећи ову невероватну функцију. Не морате да филтрирате податке да бисте рачунали одређене вредности. Цоунтиф функција је неопходна за припрему ваше контролне табле.
Како се користи функција СУМИФ у програму Екцел : Ово је још једна битна функција контролне табле. Ово вам помаже да сумирате вредности под одређеним условима.