虎虎生威

swyrlbq

1. =SUBSTITUTE(CONCAT(IF(ISERR(FIND(","&ROW($1:$99)&",",","&C2&","))*(ROW($1:$99)<=B2),IF((1-ISERR(FIND(","&ROW($1:$99)-1&",",","&C2&",")))+(ROW($1:$99)=1),","&ROW($1:$99),IF(1-ISERR(FIND(","&ROW($1:$99)+1&",",","&C2&",")),"-"&ROW($1:$99),"")),"")),",",,1)

复制代码

swyrlbq

简化一下，178字符

1. =SUBSTITUTE(CONCAT(IF((MMULT({0,1,1}-ISERR(FIND(","&ROW($1:$99)+{0,-1,1}&",",",0,"&B2+1&","&C2&",")),{-1,-1;1,-1;0,1})=2),IF(ROW($1:$99)>B2,"",{",","-"}&ROW($1:$99)),"")),",",,1)

复制代码

MHanys

只为解决实际问题，火车长又长，思路很简单

=TEXTJOIN(",",,SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(CONCAT(IF(ISNUMBER(FIND(","&ROW(INDIRECT("1:"&B2))&",",","&C2&",")),"",ROW(INDIRECT("1:"&B2)))&" "),"  ",REPT(" ",500)),ROW($1:$99)*500-499,500))," ",REPT(" ",500)),500))&"-"&IF(TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(CONCAT(IF(ISNUMBER(FIND(","&ROW(INDIRECT("1:"&B2))&",",","&C2&",")),"",ROW(INDIRECT("1:"&B2)))&" "),"  ",REPT(" ",500)),ROW($1:$99)*500-499,500))," ",REPT(" ",500)),500))=TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(CONCAT(IF(ISNUMBER(FIND(","&ROW(INDIRECT("1:"&B2))&",",","&C2&",")),"",ROW(INDIRECT("1:"&B2)))&" "),"  ",REPT(" ",500)),ROW($1:$99)*500-499,500))," ",REPT(" ",500)),500)),",",TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(CONCAT(IF(ISNUMBER(FIND(","&ROW(INDIRECT("1:"&B2))&",",","&C2&",")),"",ROW(INDIRECT("1:"&B2)))&" "),"  ",REPT(" ",500)),ROW($1:$99)*500-499,500))," ",REPT(" ",500)),500))),"-,","")," ,",""))

dipperrrr

蒙一个：
=SUBSTITUTE(TRIM(CONCAT(TEXT(IF(ROW($1:99)>B2," ",ROW($1:99)),MID("0 0 0-",8-MMULT(IFERROR(FIND(","&ROW($1:99)+{-1,0,1}&",",",0,"&C2&","&B2+1&",")^0-1,1),{-2;7;-4}),2))))," ",",")

dim麻辣君

1. =MID(CONCAT(IFERROR(CHOOSE(MMULT(--ISERR(FIND(","&ROW($2:$100)-{2,1,0}&",",",0,"&C2&","&B2+1&",")),{1;2;4}),1/0,",","-",1/0,1/0,",",1/0)&ROW($1:$99)/(ROW($1:$99)<=B2),"")),2,99)

复制代码

dipperrrr

减几个字符：
=SUBSTITUTE(TRIM(CONCAT(TEXT(ROW($1:99),"[>"&B2&"] ;"&MID("0 0 0-",8-MMULT(--ISERR(FIND(","&ROW($1:99)+{-1,0,1}&",",",0,"&C2&","&B2+1&",")),{-2;7;-4}),2))))," ",",")

swyrlbq

168字符
=SUBSTITUTE(CONCAT(IF((MMULT(1-ISERR(FIND(","&ROW($1:$99)+{0,-1,1}&",",",0,"&B2+1&","&C2&",")),{-1,-1;1,-1;0,1})=1)*(ROW($1:$99)<=B2),{",","-"}&ROW($1:$99),"")),",",,1)
还可以减几个字符，159
=MID(CONCAT(IF((MMULT(1-ISERR(FIND(","&ROW($1:$99)+{0,-1,1}&",",",0,"&B2+1&","&C2&",")),{-1,-1;1,-1;0,1})=1)*(ROW($1:$99)<=B2),{",","-"}&ROW($1:$99),"")),2,99)

丢丢表格

=MID(CONCAT(TEXT(TEXT(MMULT(-ISERR(FIND(TEXT(ROW($1:99)-{1,0,-1},"[<="&B2&"]!,0!,;"),",0,"&C2&",")),{2;-3;1})-1,"1;!0;-1")*ROW($1:99),"!,0;-0;")),2,99)

151字

你的函数方向 太刁钻了，有点跑偏 ，必须退退步。（别让函数累着了）

丢丢表格

swyrlbq 发表于 2021-12-31 09:26
168字符
=SUBSTITUTE(CONCAT(IF((MMULT(1-ISERR(FIND(","&ROW($1:$99)+{0,-1,1}&",",",0,"&B2+1&","&C2&", ...

用你的方法写：

=MID(CONCAT(IF(MMULT(-ISERR(FIND(TEXT(ROW($1:99)-{1,0,-1},"[<="&B2&"]!,0!,;"),",0,"&C2&",")),{2;-3;1})={3,2,1},{",",",","-"}&ROW($1:99),"")),2,99)

146字 。

swyrlbq

丢丢表格 发表于 2021-12-31 17:25
用你的方法写：

=MID(CONCAT(IF(MMULT(-ISERR(FIND(TEXT(ROW($1:99)-{1,0,-1},"[

多谢指点，也曾谋划用TEXT函数，未获成功。看了大师的公式，奇巧精妙，深感佩服，我不可完成。