☰

Excel 中的密集排序

tag-icon • tag-icon microsoft-excel microsoft-excel-2013
Excel 中的密集排序

如何从 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) )

在这种情况下，您只在名称管理器中更改两个名称的范围，您的公式将保持不变

相关内容