Екцел формула: Сортирање и издвајање јединствених вредности -

Генеричка формула

=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)

Резиме

Да бисте динамички сортирали и издвојили јединствене вредности са листе података, помоћу формуле низа можете успоставити ранг у помоћном ступцу, а затим користити посебно конструисану формулу ИНДЕКС и МАТЦХ за издвајање јединствених вредности. У приказаном примеру, формула за успостављање ранга у Ц5: Ц13 је:

=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))

где је „податак“ именовани опсег Б5: Б13.

Напомена: ово је формула са више ћелија, унесена са цонтрол + схифт + ентер.

Објашњење

Напомена: срж идеје ове формуле прилагођена је примеру из изврсне књиге Мике Гирвина Цонтрол + Схифт + Ентер.

Приказани пример користи неколико формула, које су описане у наставку. На високом нивоу, функција ММУЛТ се користи за израчунавање нумеричког ранга у помоћној колони (колона Ц), а тај ранг се затим користи ИНДЕКС и МАТЦХ формулом у колони Г за издвајање јединствених вредности.

Вредности података о рангирању

Функција ММУЛТ врши множење матрице и користи се за додељивање нумеричког ранга свакој вредности. Први низ се креира са следећим изразом:

--(data>TRANSPOSE(data))

Овде користимо функцију ТРАНСПОСЕ за стварање хоризонталног низа података и све вредности се упоређују једна с другом. У суштини, свака вредност се упоређује са сваком другом вредношћу да би се одговорило на питање „да ли је ова вредност већа од сваке друге вредности“. Резултат је дводимензионални низ, 9 колона к 9 редова, испуњен вредностима ТРУЕ и ФАЛСЕ. Двоструки негатив (-) користи се за присиљавање вредности ТРУЕ ФАЛСЕ на 1с и нуле. Добијени низ можете визуализовати овако:

Матрица од 1с и нула изнад постаје низ1 унутар функције ММУЛТ. Арраи2 се креира са овим изразом:

ROW(data)^0

Овде се сваки број реда у „подацима“ подиже на нулу да би се створио једнодимензионални низ, 1 колона к 9 редова, испуњен бројем 1. ММУЛТ затим враћа матрични производ два низа, који постају вредности које се виде у колони ранга.

Свих 9 рангирања враћамо истовремено у низ, тако да резултате морамо одједном ставити у различите ћелије. У супротном, свака ћелија ће показати само прву рангирану вредност у пољу која се враћа.

Напомена: ово је формула са више ћелија, унесена са цонтрол + схифт + ентер, у опсегу Ц5: Ц13.

Руковање празним ћелијама

Празним ћелијама се рукује овим делом формуле за рангирање:

=IF(data="",ROWS(data)

Овде, пре него што покренемо ММУЛТ, проверимо да ли је тренутна ћелија у „подацима“ празна. Ако је тако, додељујемо вредност ранга која је једнака броју редова у подацима. То се ради како би се празне ћелије присилиле на дно листе, где се касније могу лако изузети када се издвоје јединствене вредности (објашњено у наставку).

Бројање јединствених вредности

За бројање јединствених вредности у подацима, формула у Е5 је:

=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)

Будући да горња формула за рангирање свакој вредности додељује нумерички ранг, можемо да користимо функцију ФРЕКУЕНЦИ са СУМ за бројање јединствених вредности. Ова формула је овде детаљно објашњена. Затим од резултата одузимамо 1 ако у подацима има празних ћелија:

-(blank>0)

где је „празно“ именовани опсег Е8 и садржи ову формулу:

=COUNTBLANK(data)

У основи јединствени број смањујемо за један ако у подацима постоје празне ћелије, јер их не укључујемо у резултате. Јединствени број у ћелији Е5 назива се "јединствен" (за јединствени број), а користи га формула ИНДЕКС и МАТЦХ за филтрирање празних ћелија (описано у наставку).

Издвајање јединствених вредности

Да би издвојио јединствене вредности, Г5 садржи следећу формулу, копирану доле:

=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))

Пре него што покренемо формулу ИНДЕКС и МАТЦХ, прво проверимо да ли је тренутни број редова у подручју екстракције већи од јединственог броја именованог опсега „јединствени“ (Е5):

=IF(ROWS($G$5:G5)>unique,"",

Ако је то случај, завршили смо са издвајањем јединствених вредности и вратили смо празан низ (""). Ако не, покрећемо формулу екстракције:

INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))

Имајте на уму да су овде две функције МАТЦХ, једна у другој. Унутрашњи МАТЦХ користи опсег који се шири за низ и именовани опсег „подаци“ за вредност претраживања:

MATCH(data,$G$4:G4,0)

Приметите да опсег ширења почиње у „реду изнад“, 4. реду у примеру. Резултат из унутрашње МАТЦХ је низ који за сваку вредност у подацима садржи или нумерички положај (вредност је већ издвојена) или грешку # Н / А (вредност још увек није извучена). Затим користимо ИФ и ИСНА за филтрирање ових резултата и враћамо вредност ранга за све вредности у „подацима“ који још нису извучени:

IF(ISNA(results),rank))

Ова операција резултира низом, који се уноси у функцију МИН како би се добила „минимална вредност ранга“ за вредности података које још нису извучене. МИН функција враћа ову вредност спољном МАТЦХ-у као вредност претраживања и именовани опсег "ранк" као низ: