Плотный ранг в Excel

Question

Как я могу получить эквивалент плотного ранга из SQL в Excel?

У меня есть несортированные данные (количество данных варьируется от недели к неделе), и дубликаты должны иметь одинаковый ранг, т.е.

number    rank
6          1
12         2
23         3
6          1
6          1

Я нашел это решение

=SUMPRODUCT( (FREQUENCY($A$1:$A$10, $A$1:$A$10) > 0) * (A1 >= $A$1:$A$11) )

Note the extra row required in the second expression. 

но я не могу использовать его, так как не знаю, сколько записей у меня будет, и этот предполагает, что я знаю набор данных априори.

Answer 1

Использование:

INDEX(A:A,MATCH(1E+99,A:A))

Чтобы установить верхние границы диапазона. Это найдет последнюю ячейку с номером и установит ее как последнюю ячейку в диапазоне. Если вместо этого в столбце есть текст, вместо "ZZZ" используйте 1E+99 .

Для добавления мы просто добавляем один в МАТЧ:

INDEX(A:A,MATCH(1E+99,A:A)+1))

Таким образом, вся формула будет:

=SUMPRODUCT( (FREQUENCY($A$1:INDEX(A:A,MATCH(1E+99,A:A)), $A$1:INDEX(A:A,MATCH(1E+99,A:A))) > 0) * (A1 >= $A$1:INDEX(A:A,MATCH(1E+99,A:A)+1)) )

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

Answer 2

Вы можете использовать эту формулу:

=SUMPRODUCT((FREQUENCY(INDIRECT("$A$1:$A"&MAX((A:A<>"")*(ROW(A:A)))),INDIRECT("$A$1:$A"&MAX((A:A<>"")*(ROW(A:A)))))>0)*(A1>=INDIRECT("$A$1:$A"&(MAX((A:A<>"")*(ROW(A:A)))+1))))  

Array Formula нажмите Ctrl + Shift + Enter одновременно
MAX((A:A<>"")*(ROW(A:A))
Вычислить последнюю ячейку в столбце A (дает последнюю непустую строку в A)
Indirect даст ссылку на массив (A1:A10), соответствующую последней непустой строке, заданной параметром max
MAX((A:A<>"")*(ROW(A:A)))+1 для массива A1:A11

Я предпочитаю вас назвать диапазон A1:A10 определить имя Number для примера
A1:A11 Numberplus

=SUMPRODUCT( (FREQUENCY(Number, Number) > 0) * (A1 >= Numberplus) )

В этом случае вы изменяете диапазон только в Name Manager только для обоих имен, ваша формула останется прежней