我正在尝试对一小列值进行排序(降序)通过使用不同列中的公式。因此,如果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。