![Excel 中的密集排序](https://linux22.com/image/1526241/Excel%20%E4%B8%AD%E7%9A%84%E5%AF%86%E9%9B%86%E6%8E%92%E5%BA%8F.png)
如何从 Excel 中的 SQL 获得相当于密集排名的值?
我有未分类的数据(数据数量每周都不同),并且重复项应该具有相同的排名,即
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.
但我无法使用它,因为我不知道我将有多少个条目,并且这个假设我先验地知道数据集。
答案1
使用:
INDEX(A:A,MATCH(1E+99,A:A))
设置范围的上限。这将找到最后一个包含数字的单元格,并将其设置为范围内的最后一个单元格。如果列中包含文本,则使用"ZZZ"
代替1E+99
。
对于添加一个,我们只需将一个添加到 MATCH 中:
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 列中添加或删除值时,引用将相应地改变。
答案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
++计算 A 列中的最后一个单元格(给出 AShift
中 最后一个非空行) 间接将给出与 max 给出的最后一个非空行相对应的数组引用(A1:A10) 对于数组 A1:A11 Enter
MAX((A:A<>"")*(ROW(A:A))
MAX((A:A<>"")*(ROW(A:A)))+1
我希望你将范围命名为 A1:A10 定义名称Number
例如
A1:A11Numberplus
=SUMPRODUCT( (FREQUENCY(Number, Number) > 0) * (A1 >= Numberplus) )
在这种情况下,您只在名称管理器中更改两个名称的范围,您的公式将保持不变