ExcelTip.Net留存知识帖 ---【注:附件之前被网盘供应商清空后,现已修复-现已修复-现已修复为本地下载!】
现在位置:首页 > E问E答 > 函数与公式 > 如何对文本编码进行按行分类排序?

如何对文本编码进行按行分类排序?

作者:绿色风 分类: 时间:2022-08-17 浏览:100
楼主
gouweicao78
Q:如何对文本编码按行进行分类排序?

 
如图,A列是一些文本编码,需要实现E:I列这样的排序,即前6位编码相同的放在同一行,不同的放在不同行,每行按升序排列。

A:在E2单元格输入以下公式,按CTRL+SHIFT+ENTER结束,向下复制:

  1. =INDEX(A:A,RIGHT(MIN(IF(COUNTIF(E$1:E1,LEFT(A$2:A$67,6)&"*"&RIGHT(A$2:A$67)),9999999,COUNTIF(A$2:A$67,"<"&A$2:A$67)*1000+ROW($2:$67))),3))&""

在F2输入以下公式,按CTRL+SHIFT+ENTER结束,向右、向下复制:

  1. =INDEX($A:$A,RIGHT(MIN(IF(COUNTIF($E2:E2,$A$2:$A$67)+ISERR(SEARCH(REPLACE($E2,7,LEN($E2)-7,"*"),$A$2:$A$67)),9999999,COUNTIF($A$2:$A$67,"<"&$A$2:$A$67)*1000+ROW($2:$67))),3))&""

编码文本分类、排序.rar
2楼
gouweicao78
【公式解析】1
=INDEX(A:A,RIGHT(MIN(IF(COUNTIF(E$1:E1,LEFT(A$2:A$67,6)&"*"&RIGHT(A$2:A$67)),9999999,COUNTIF(A$2:A$67,"<"&A$2:A$67)*1000+ROW($2:$67))),3))&""


其中COUNTIF(E$1:E1,LEFT(A$2:A$67,6)&"*"&RIGHT(A$2:A$67))用于判断从E$1:E1是否出现A列前6个字符开头最后1个字符结尾的,因为E1单元格是标题,肯定没有编码,所以都是0,那么IF就返回COUNTIF(A$2:A$67,"<"&A$2:A$67)*1000+ROW($2:$67),也就是按字符的“大小”排序并乘以1000后加上行号,这样行号就跟着被排序了。
再用MIN函数取最小的那个值,在E2单元格,min得到5,也就是0个比A5小的编码*1000(还是0)+A5的行号5,从而配合INDEX取出A5内容。

E3单元格中,同理。但因为E2已经有了A5的内容,也就是说,以A5的前6个字符和最后一个字符结束作编号的,COUNTIF判断将不是0,会返回9999999(一个超过countif*1000+行号的值),MIN得到23,所以返回A23内容

E4同理,MIN返回1045,是因为有RIGHT(MIN,3)取得045,所以INDEX返回A45单元格内容。
其他依次判断,每出现一个,就会多一个9999999,直到最后记录都完全出来了,都是9999999了,这时候RIGHT返回999,也就是取A999单元格(一个空单元格)再结合&""返回空文本。


【公式解析】2
=INDEX($A:$A,RIGHT(MIN(IF(COUNTIF($E2:E2,$A$2:$A$67)+ISERR(SEARCH(REPLACE($E2,7,LEN($E2)-7,"*"),$A$2:$A$67)),9999999,COUNTIF($A$2:$A$67,"<"&$A$2:$A$67)*1000+ROW($2:$67))),3))&""

同上,只不过改为从$E2:E2判断,并且加了ISERR(SEARCH(REPLACE($E2,7,LEN($E2)-7,"*"),$A$2:$B$67))这个条件判断,即:用replace将E2已经出来的编号中间第7个字符起len-7个字符替换为*号(通配符),然后用SEARCH查找,判断是否在A列。

两个是“或”条件关系,只要$E2:E2已出现的变化,或只要开头不是E2前6个字符的,都会过滤掉。
3楼
o。星期八。o
太强大了

免责声明

有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素, 经与ExcelTip.Net站长Apolloh商议并征得其同意, 现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示, 供有需要的人士查询使用,也慰缅曾经的论坛时代。 所示各个帖子的原作者如对版权有异议, 可与本人沟通提出,或于本站点留言,我们会尽快处理。 在此,感谢ExcelTip.Net站长Apolloh的支持,感谢本站点所有人**绿色风(QQ:79664738)**的支持与奉献,特此鸣谢!
------本人网名**KevinChengCW(QQ:1210618015)**原ExcelTip.Net总版主之一

评论列表
sitemap