EXCLE字符串编码取最大值编码，求公式

Question

请问这样的编码，有多个小数点的数字编码级别，我如何取出最大编码值，求公式；例如 列为 1.0.16、 1.0.12 、1.0.1，通过公式取出1.0.16

Answer 1

除B列外的随便单元格：

=MAX(IFERROR(TRIM(MID(SUBSTITUTE($B$2:$B$1001,".",REPT(" ",100)),1,100))*1,""))&"."&MAX(IFERROR(TRIM(MID(SUBSTITUTE(IF(IFERROR(TRIM(MID(SUBSTITUTE($B$2:$B$1001,".",REPT(" ",100)),1,100))*1,"")=MAX(IFERROR(TRIM(MID(SUBSTITUTE($B$2:$B$1001,".",REPT(" ",100)),1,100))*1,"")),$B$2:$B$1001,""),".",REPT(" ",100)),101,100))*1,""))&"."&MAX(IFERROR(TRIM(MID(SUBSTITUTE(IF(IFERROR(TRIM(MID(SUBSTITUTE(IF(IFERROR(TRIM(MID(SUBSTITUTE($B$2:$B$1001,".",REPT(" ",100)),1,100))*1,"")=MAX(IFERROR(TRIM(MID(SUBSTITUTE($B$2:$B$1001,".",REPT(" ",100)),1,100))*1,"")),$B$2:$B$1001,""),".",REPT(" ",100)),101,100))*1,"")=MAX(IFERROR(TRIM(MID(SUBSTITUTE(IF(IFERROR(TRIM(MID(SUBSTITUTE($B$2:$B$1001,".",REPT(" ",100)),1,100))*1,"")=MAX(IFERROR(TRIM(MID(SUBSTITUTE($B$2:$B$1001,".",REPT(" ",100)),1,100))*1,"")),$B$2:$B$1001,""),".",REPT(" ",100)),101,100))*1,"")),IF(IFERROR(TRIM(MID(SUBSTITUTE($B$2:$B$1001,".",REPT(" ",100)),1,100))*1,"")=MAX(IFERROR(TRIM(MID(SUBSTITUTE($B$2:$B$1001,".",REPT(" ",100)),1,100))*1,"")),$B$2:$B$1001,""),""),".",REPT(" ",100)),201,100))*1,""))

编辑状态下同时按下Ctrl+Shift+Enter,当出现如图箭头所指大括号即可。

Answer 2

B列添加辅助列，B2输入公式：=SUBSTITUTE(A2,".","")*1并向下填充。

在提取最大编码单元格输入以下公式：=INDEX($A$2:$A$5,MATCH(MAX($B$2:$B$5),$B$2:$B$5,0),0)带圆点的编号如何提取最大值

本回答被提问者采纳

Answer 3

1.0.2与2.0.1哪个大

Answer 4

能用辅助列或者vba宏或者js宏吗