Сортировать по формуле (без VBA)

Question

Я пытаюсь отсортировать короткий столбец значений (по убыванию) с помощью формулы в другом столбце. Так что, если от A1 до A10 содержат числа, в B1 я ввожу:

=LARGE($A$1:$A$10,ROWS($1:1))

и скопируйте вниз:

Как видите, это формула, эквивалентная копированию столбца A в столбец B, а затем сортировке B с использованием сортировки с ленты.

Если значения в столбце A являются Text, я могу сделать то же самое, используя:

=LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$1:$A$10,">="&$A$1:$A$10)),$A$1:$A$10)

и копирование вниз:

Моя проблема заключается в разработке формулы, которая будет обрабатывать как текст, так и цифры. Вот пример данных в A и ожидаемых результатов в B:

10      zeta
alpha   gamma
zeta    beta
alpha   alpha
2       alpha
beta    10
1       4
gamma   4
4       2
4       1

Это легко с VBA, но мне нужно решение только с формулами рабочего листа.

Answer 1

Это требует, чтобы результаты начинались как минимум во второй строке, только потому, что мне нужна строка выше для функции COUNT().

Также это формула массива:

=IF(LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$1:$A$10,">="&IF(NOT(ISNUMBER($A$1:$A$10)),$A$1:$A$10))),$A$1:$A$10)<>0,LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$1:$A$10,">="&IF(NOT(ISNUMBER($A$1:$A$10)),$A$1:$A$10))),$A$1:$A$10),LARGE(IF(ISNUMBER($A$1:$A$10),$A$1:$A$10),COUNT($C$1:$C1)+1))

Будучи массивом, используйте Ctrl-Shift-Enter при выходе из режима редактирования. Если все сделано правильно, то Excel поместит {} вокруг формулы

Как это устроено:

Мы взяли формулу для сортировки текста и сделали так, чтобы она находила только нечисловые данные LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$1:$A$10,">="&IF(NOT(ISNUMBER($A$1:$A$10)),$A$1:$A$10))),$A$1:$A$10) Это делается в COUNTIF с пометкой IF().

Это вернет 0 после сортировки всех нечисловых чисел, поэтому, когда оно равно 0, мы используем функцию LARGE() только для числовых данных. LARGE(IF(ISNUMBER($A$1:$A$10),$A$1:$A$10),COUNT($C$1:$C1)+1) .

Так как COUNT() будет считать только число, мы используем его как k в LARGE(). Мы считаем все числа над текущей ячейкой и добавляем 1, чтобы получить правильное число k.

Answer 2

Можете ли вы добавить вспомогательный столбец, который изменяет числовые значения столбца A?

Если вы используете формулу

=TEXT(A1,"'0")

тогда ваша слегка измененная вторая формула будет работать

=LOOKUP(1,0/FREQUENCY(ROWS($1:4),COUNTIF($B$1:$B$10,">="&$B$1:$B$10)),$A$1:$A$10)