另类排序

金色和谐家园

各位老师，

取字符间不同的数值，进行排序，不知公式哪里错了，请指教。

1、取字符间不同的数值进行排序
2、按照顺序，求对应的名称

VLOOKUP("*"&":",LEFT(INDEX($B:$B,MOD(LARGE(IF(ISNUMBER(C$6:C$65),LEFT(C$6:C$65,FIND(":",C$6:C$65&":")-1)/1%%%%+ROW($6:$65)),ROW($A$1)),10^3))&CHAR(10),ROW($6:$65)-{0,1}),2,)

万分感谢

工作簿123.rar (9.12 KB, 下载次数: 26)

2024-5-15 08:38 上传

点击文件名下载附件

魚。。

1. =INDEX(B:B,99-MOD(SMALL(IF($C$6:$C$66<>"",LEFT($C$6:$C$66,FIND(":",$C$6:$C$66)-1)/1%%%+99-ROW($6:$66)),ROW(A1)),100))

复制代码

1. =INDEX(B:B,99-MOD(SMALL(IF($C$6:$C$66<>"",MID($C$6:$C$66,FIND("║",$C$6:$C$66)+1,9)/1%%%+99-ROW($6:$66)),ROW(A1)),100))

复制代码

1. =INDEX(B:B,99-MOD(SMALL(IF($C$6:$C$66<>"",MID(SUBSTITUTE($C$6:$C$66,"║",REPT(" ",9)),FIND(":",$C$6:$C$66)+1,9)/1%%%+99-ROW($6:$66)),ROW(A1)),100))

复制代码

mjm04

注意，数据源表的 B列有内容，但后面的C列中有“假空”，看上去是空白单元格，其实有个0在里面！！！

排序-1
=CHOOSEROWS(SORTBY(B6:C1000,TEXTBEFORE(C6:C1000," : ")*1,1),SEQUENCE(,COUNTA(C6:C1000),1,1))

排序-2
=CHOOSEROWS(SORTBY(B6:C1000,TEXTAFTER(C6:C1000,"║")*1,1),SEQUENCE(,COUNTA(C6:C1000),1,1))

排序-3
=CHOOSEROWS(SORTBY(B6:C1000,TEXTBEFORE(TEXTAFTER(C6:C1000," : "),"║")*1,1),SEQUENCE(,COUNTA(C6:C1000),1,1))

区域数组函数

cnmlgb9998

用PQ解决，不香么？？？？？？？？？？？？

jisijie630

=IFERROR(INDEX(B:B,MOD(SMALL(IF(ISERR((0&$C$6:$C$65)/1),LEFT($C$6:$C$65,FIND(":",$C$6:$C$65)-1)/1%%+ROW($6:$65),""),ROW(A1)),100)),"")


=IFERROR(INDEX(B:B,MOD(SMALL(IF(ISERR((0&$C$6:$C$65)/1),MID($C$6:$C$65,FIND("║",$C$6:$C$65)+1,9)/1%%%+ROW($6:$65),""),ROW(A1)),100)),"")


=IFERROR(INDEX(B:B,MOD(SMALL(IF(ISERR((0&$C$6:$C$65)/1),MID(SUBSTITUTE($C$6:$C$65,"║",REPT(" ",9)),FIND(":",$C$6:$C$65)+1,9)/1%+ROW($6:$65),""),ROW(A1)),100)),"")

金色和谐家园

jisijie630 发表于 2024-5-15 14:11
=IFERROR(INDEX(B:B,MOD(SMALL(IF(ISERR((0&$C$6:$C$65)/1),LEFT($C$6:$C$65,FIND(":",$C$6:$C$65)-1)/1%%+ ...

正解。感谢老师帮助。

金色和谐家园

魚。。 发表于 2024-5-15 08:59

正解。感谢老师帮助。

leepoison

1. =TAKE(SORTBY(B20:C38,(--TEXTBEFORE(C20:C38,":"))),COUNTA(C20:C38))
   
2. =TAKE(SORTBY(B20:C38,(--TEXTAFTER(C20:C38,"║"))),COUNTA(C20:C38))
   
3. =TAKE(SORTBY(B20:C38,--TEXTAFTER(TEXTBEFORE(C20:C38,"║"),":")),COUNTA(C20:C38))

复制代码

袁勇

低版本。
排序1：

1. =INDEX(B:B,RIGHT(AGGREGATE(15,6,(TEXT(TEXT([color=Magenta]1%[/color],SUBSTITUTE(SUBSTITUTE(C$6:C$99," : ",";"),"║",";")),"g/通用格式;0;0;!9!E!5")*10^6+ROW($6:$99))/($C$6:$C$99<>""),ROW(A1)),2))

复制代码

=INDEX(B:B,RIGHT(AGGREGATE(15,6,(TEXT(TEXT(1%,SUBSTITUTE(SUBSTITUTE($C$6:$C$99," : ",";"),"║",";")),"g/通用格式;0;0;!9!E!5")*10^6+ROW($6:$99))/($C$6:$C$99<>""),ROW(A1)),2))

排序2，红色处改为-1%
排序3：红色片改为0%