Classificação Densa no Excel

Question

Como posso obter um equivalente de classificação densa do SQL no Excel?

Dados não classificados (o número de dados varia de semana para semana) e as duplicatas devem ter a mesma classificação, ou seja,

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

Eu encontrei esta solução

=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. 

mas não posso usá-lo pois não sei quantas entradas terei e esta assume que conheço o conjunto de dados a priori.

    

Answer 1

Uso:

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

Para definir os limites superiores do intervalo. Isso localizará a última célula que possui um número e a definirá como a última célula do intervalo. Se a coluna tiver texto, use "ZZZ" no lugar de 1E+99 .

Para o add, adicionamos um ao MATCH:

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

Portanto, toda a fórmula será:

=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)) )

Isso agora é dinâmico, pois os valores são adicionados ou removidos da coluna A e a referência será alterada de acordo.

    

Answer 2

Você pode usar esta fórmula:

=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 press Ctrl + Shift + Enter ao mesmo tempo
MAX((A:A<>"")*(ROW(A:A))
Calcular a última célula na coluna A (dá a última linha não vazia em A)
Indireto fornecerá a referência de matriz (A1: A10) correspondente à última linha não vazia dada por max
MAX((A:A<>"")*(ROW(A:A)))+1 Para o array A1: A11

Eu prefiro nomear o intervalo A1: A10 definir nome Number por exemplo
A1: A11 Numberplus

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

Nesse caso, você altera o intervalo apenas no Gerenciador de nomes somente para os dois nomes. Sua fórmula permanecerá a mesma