提取列标

yvll

期待问题2能解答。

良良

BD5=IFERROR(SUBSTITUTE(ADDRESS(1,SMALL(IF(ISERR(FIND(","&$C$1:$BC$1&",",","&$BE1&",")),"",COLUMN($C:$BC)),COLUMN(A1)),4),1,),"")

BD8=IFERROR(TEXTJOIN(":",,ADDRESS(2,TEXT(SMALL(IF(ISERR(FIND(","&$C$1:$BC$1&",",","&$BE1&",")),"",COLUMN($C:$BC)),COLUMN(A1))+{-1;1},"[<"&INT(COLUMN(D1)/4)*19-16&"]"&INT(COLUMN(D1)/4)*19-16&";[>"&INT(COLUMN(D1)/4)*19-2&"]"&INT(COLUMN(D1)/4)*19-2&";0"),4)),"")

三键，各自下拉一行，再右拉。

yvll

良良 发表于 2024-7-16 20:37
BD5=IFERROR(SUBSTITUTE(ADDRESS(1,SMALL(IF(ISERR(FIND(","&$C$1:$BC$1&",",","&$BE1&",")),"",COLUMN($C: ...

感谢良良老师，多难的问题都能解决。非常感谢！
再问一下，如果改变一下条件，3个区每个区由15改为16，BE1内由4个数改为5个数，公式应该怎么改。谢谢！

良良

yvll 发表于 2024-7-16 21:00
感谢良良老师，多难的问题都能解决。非常感谢！
再问一下，如果改变一下条件，3个区每个区由15改为16，B ...

=IFERROR(SUBSTITUTE(ADDRESS(1,SMALL(IF(ISERR(FIND(","&$C$1:$BD$1&",",","&$BE1&",")),"",COLUMN($C:$BD)),COLUMN(A1)),4),1,),"")

=IFERROR(TEXTJOIN(":",,ADDRESS(2,TEXT(SMALL(IF(ISERR(FIND(","&$C$1:$BD$1&",",","&$BE1&",")),"",COLUMN($C:$BD)),COLUMN(A1))+{-1;1},"[<"&INT(COLUMN(E1)/5)*19-16&"]"&INT(COLUMN(E1)/5)*19-16&";[>"&INT(COLUMN(E1)/5)*19-1&"]"&INT(COLUMN(E1)/5)*19-1&";0"),4)),"")

yvll

良良 发表于 2024-7-16 21:32
=IFERROR(SUBSTITUTE(ADDRESS(1,SMALL(IF(ISERR(FIND(","&$C$1:$BD$1&",",","&$BE1&",")),"",COLUMN($C:$ ...

非常好，感谢您！

yvll

良良 发表于 2024-7-16 21:32
=IFERROR(SUBSTITUTE(ADDRESS(1,SMALL(IF(ISERR(FIND(","&$C$1:$BD$1&",",","&$BE1&",")),"",COLUMN($C:$ ...

=IFERROR(TEXTJOIN(":",,ADDRESS(2,TEXT(SMALL(IF(ISERR(FIND(","&$C$1:$BD$1&",",","&$BE1&",")),"",COLUMN($C:$BD)),COLUMN(A1))+{-1;1},"[<"&INT(COLUMN(E1)/5)*19-16&"]"&INT(COLUMN(E1)/5)*19-16&";[>"&INT(COLUMN(E1)/5)*19-1&"]"&INT(COLUMN(E1)/5)*19-1&";0"),4)),"")
您好，请问其中的19-16是什么意思，没看明白。

良良

yvll 发表于 2024-7-17 11:33
=IFERROR(TEXTJOIN(":",,ADDRESS(2,TEXT(SMALL(IF(ISERR(FIND(","&$C$1:$BD$1&",",","&$BE1&",")),"",COL ...

BD11=INT(COLUMN(E1)/5)*19-16，右拉，每5列分别对应1~3区域的首列号。
BD12=INT(COLUMN(E1)/5)*19-1，右拉，每5列分别对应1~3区域的末列号。

袁勇

低版本，非数组。，无文本连结函数，公式较长。
问题2：

1. =ADDRESS(2,AGGREGATE(15,6,COLUMN($C:$BE)/1%^($C$1:$BC$1<>TEXT(TEXT(TRIM(MID(SUBSTITUTE($BE1,",",REPT(" ",20)),(MOD(COLUMN(A1)-1,4)+1)*20-19,20))-1,"[=]!1;0"),"[=16]15;0")*1),INT(COLUMN(D1)/4)),4)&":"&ADDRESS(2,AGGREGATE(15,6,COLUMN($C:$BE)/1%^($C$1:$BC$1<>TEXT(TEXT(TRIM(MID(SUBSTITUTE($BE1,",",REPT(" ",20)),(MOD(COLUMN(A1)-1,4)+1)*20-19,20))+1,"[=]!1;0"),"[=16]15;0")*1),INT(COLUMN(D1)/4)),4)

复制代码

yvll

良良 发表于 2024-7-17 12:38
BD11=INT(COLUMN(E1)/5)*19-16，右拉，每5列分别对应1~3区域的首列号。
BD12=INT(COLUMN(E1)/5)*19-1， ...

BD11=INT(COLUMN(E1)/5)*19-16，右拉，每5列分别对应1~3区域的首列号。
BD12=INT(COLUMN(E1)/5)*19-1，右拉，每5列分别对应1~3区域的末列号。
再请教：
=INT(COLUMN(E1)/5)*19-16，=INT(COLUMN(E1)/5)*19-1是根据什么得出的，知道是每个区域的开头和结尾了，但是如何得出的不理解，比如我开头是J（第10列），结尾是AR列（第44列），共6个区域，区域之间间隔15列，每4列和5列的首尾怎么写公式？
谢谢！

良良

yvll 发表于 2024-7-17 16:17
BD11=INT(COLUMN(E1)/5)*19-16，右拉，每5列分别对应1~3区域的首列号。
BD12=INT(COLUMN(E1)/5)*19-1， ...

每个区域的列数相同都是：35列（44-10+1），且区域之间的间隔相同都是：15，则各区域的首列号为：10，60，110，160。。。。。，末列号为：44，94，144，194。。。。，都是公差为50的等差数列。由数学中等差数列通项公式可知，各区域首列公式：10+50*(n-1)=50*n-40；各区域末列公式：44+50*(n-1)=50*n-6。
即：=INT(COLUMN(E1)/5)*50-40；=INT(COLUMN(E1)/5)*50-6。