[字符争霸第五期]求各行各任意数字间最小间距[已开贴]

huangcaiguang

=small(abs(transpose(A2:G2)-A2:G2),(8-count(2:2))^2+count(2:2))  注：结果列右边不能有数字！

ddhhyy16

1. =IF(COUNT(A2:G2)=1,SUM(A2:G2),MIN(IFERROR(SMALL(A2:G2,ROW($2:$7))-SMALL(A2:G2,ROW($1:$6)),9^9)))

复制代码

先交了再说~

huangcaiguang

第一个用了迭代计算，保险一点=small(abs(transpose(A2:G2)-A2:G2),1+(7-count(A2:G2))^2+count(A2:G2))

baksy

=IF(COUNT(A2:G2)=1,MAX(A2:G2),MIN(IFERROR(SMALL(A2:G2,ROW($2:$7))-SMALL(A2:G2,ROW($1:$6)),"")))

huangcaiguang

合并同类项=small(abs(transpose(A2:G2)-A2:G2),31/4+(6.5-count(A2:G2))^2)

baksy

=MIN(IFERROR(SMALL(A2:G2,ROW($2:$7))-SMALL(A2:G2,ROW($1:$6)),MAX(A2:G2)))

1091126096

不知55字符怎么写出来的，等待开帖学习，68字符献丑

1. =SMALL(ABS(IF(A2:G2<>"",TRANSPOSE(A2:G2)-A2:G2,100)),COUNT(A2:G2,1))

复制代码

sliang28

结果正确了{:021:}
=IF(COUNT(A2:G2)=1,MAX(A2:G2),SMALL(IFERROR(SMALL(A2:G2,ROW($2:$7))-SMALL(A2:G2,ROW($1:$6)),101),1))

huangcaiguang

=small(abs(transpose(A2:G2)-A2:G2),8+(6.5-count(A2:G2))^2)

josonxu

就题目中的数字而言   我可以取巧   =MIN(FREQUENCY(ROW($1:$999),A2:G2))     这个公式即可    关于 通用公式  我的以超过55字符  =RIGHT(MIN(FREQUENCY(ROW($1:$9999),IF(A2:G2<>"",A2:G2+1000)))，3)    用这个算=LEN(FORMULATEXT(L13))那公式 是66 字符