Обща формула
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Обобщение
За да динамично сортирате и извличате уникални стойности от списък с данни, можете да използвате формула на масив, за да установите ранг в помощна колона, след което да използвате специално конструирана формула INDEX и MATCH за извличане на уникални стойности. В показания пример формулата за установяване на ранг в C5: C13 е:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
където "данни" е наименованият диапазон B5: B13.
Забележка: това е формула на многоклетъчен масив, въведена с control + shift + enter.
Обяснение
Забележка: основната идея на тази формула е адаптирана от пример в отличната книга на Майк Гирвин Control + Shift + Enter.
Показаният пример използва няколко формули, които са описани по-долу. На високо ниво функцията MMULT се използва за изчисляване на числов ранг в помощна колона (колона C) и след това този ранг се използва от формула INDEX и MATCH в колона G за извличане на уникални стойности.
Стойности на данните за класиране
Функцията MMULT извършва умножение на матрица и се използва за присвояване на числов ранг на всяка стойност. Първият масив се създава със следния израз:
--(data>TRANSPOSE(data))
Тук използваме функцията TRANSPOSE, за да създадем хоризонтален масив от данни и всички стойности се сравняват една с друга. По същество всяка стойност се сравнява с всяка друга стойност, за да се отговори на въпроса „тази стойност по-голяма ли е от всяка друга стойност“. Това води до двумерен масив, 9 колони x 9 реда, изпълнен със стойности TRUE и FALSE. Двойният отрицателен (-) се използва за принуждаване на стойностите TRUE FALSE до 1s и нули. Можете да визуализирате получения масив по следния начин:
Матрицата от 1s и нули по-горе става array1 във функцията MMULT. Array2 се създава с този израз:
ROW(data)^0
Тук всеки номер на ред в "данни" се повишава до степен нула, за да се създаде едномерен масив, 1 колона х 9 реда, запълнен с число 1. След това MMULT връща матричния продукт на двата масива, които се превръщат стойности, които се виждат в колоната за ранг.
Връщаме всички 9 класирания едновременно в масив, така че трябва да поставим резултатите в различни клетки наведнъж. В противен случай всяка клетка ще покаже само първата стойност за класиране в масива, който се връща.
Забележка: това е формула на многоклетъчен масив, въведена с control + shift + enter, в диапазона C5: C13.
Работа с празни клетки
Празни клетки се обработват с тази част от формулата за класиране:
=IF(data="",ROWS(data)
Тук, преди да стартираме MMULT, проверяваме дали текущата клетка в "data" е празна. Ако е така, ние присвояваме стойност на ранга, която е равна на броя на редовете в данните. Това се прави, за да се принудят празни клетки до края на списъка, където лесно могат да бъдат изключени по-късно, когато се извличат уникални стойности (обяснено по-долу).
Преброяване на уникални стойности
За да преброите уникални стойности в данните, формулата в E5 е:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Тъй като формулата за класиране по-горе присвоява числов ранг на всяка стойност, можем да използваме функцията FREQUENCY със SUM, за да преброим уникални стойности. Тази формула е обяснена подробно тук. След това изваждаме 1 от резултата, ако в данните има празни клетки:
-(blank>0)
където "празен" е наименованият диапазон E8 и съдържа тази формула:
=COUNTBLANK(data)
По същество намаляваме уникалния брой с едно, ако в данните има празни клетки, тъй като не ги включваме в резултатите. Уникалният брой в клетка E5 се нарича "уникален" (за уникален брой) и се използва от формулата INDEX и MATCH за филтриране на празни клетки (описани по-долу).
Извличане на уникални стойности
За да извлечете уникални стойности, G5 съдържа следната формула, копирана надолу:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Преди да стартираме формулата INDEX и MATCH, първо проверяваме дали текущият брой на редовете в областта за извличане е по-голям от броя на уникалите, наречен диапазон "уникален" (E5):
=IF(ROWS($G$5:G5)>unique,"",
Ако е така, приключваме с извличането на уникални стойности и връщаме празен низ (""). Ако не, изпълняваме формулата за извличане:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Имайте предвид, че тук има две функции MATCH, една в друга. Вътрешният MATCH използва разширяващ се диапазон за масив и посочения диапазон "данни" за справочната стойност:
MATCH(data,$G$4:G4,0)
Забележете, че разширяващият се диапазон започва от "реда по-горе", ред 4 в примера. Резултатът от вътрешния MATCH е масив, който за всяка стойност в данните съдържа или числово положение (стойността вече е извлечена), или грешка # N / A (стойността все още не е извлечена). След това използваме IF и ISNA за филтриране на тези резултати и връщаме стойността на ранга за всички стойности в "данни", които все още не са извлечени:
IF(ISNA(results),rank))
Тази операция води до масив, който се подава във функцията MIN, за да се получи "минималната стойност на ранга" за стойности на данни, които все още не са извлечени. Функцията MIN връща тази стойност към външния MATCH като справочна стойност и посочения диапазон "rank" като масив:
MATCH(min_not_extracted,rank)),rank,0)
И накрая, MATCH връща позицията на най-ниската стойност на ранга на INDEX като номер на ред, а INDEX връща стойността на данните в текущия ред на диапазона на извличане.
