按公式排序(无需 VBA)

按公式排序(无需 VBA)

我正在尝试对一小列值进行排序(降序)通过使用不同列中的公式。因此,如果A1通过A10包含数字,B1我输入:

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

并抄下来:

在此处输入图片描述

如您所见,这是相当于复制列的公式A到列然后排序使用功能区中的“排序”。

如果列中的值A是文本,我可以使用以下方法做同样的事情:

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

并抄录下来:

在此处输入图片描述

我的问题是设计一个可以处理文本和数字的公式。以下是数据示例A并预期成果

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

这很容易虚拟专用网络,但我需要仅包含工作表公式的解决方案。

答案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() 仅计数数字,因此我们将其用作 LARGE() 中的 k。我们计算当前单元格上方的所有数字,然后加 1 以获得正确的 k。

在此处输入图片描述

答案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)

在此处输入图片描述

相关内容