提取列标

yvll

请教提取列标问题，谢谢！

提取列标.rar (10.33 KB, 下载次数: 40)

2024-7-16 16:43 上传

点击文件名下载附件

袁勇

问题1：

1. =SUBSTITUTE(ADDRESS(1,AGGREGATE(15,6,COLUMN($C:$BE)/ISNUMBER(FIND(","&$C$1:$BC$1&",",","&$BE1&",")),COLUMN(A1)),4),1,)

复制代码

gadxjyang

问题1借用楼上方法：
=SUBSTITUTE(ADDRESS(1,TOROW(IF((--TEXTSPLIT(BE1,,",")=C1:BC1)*(C1:BC1<>""),COLUMN(C2:BC2),NA()),2,TRUE),4),1,)

qwer401245

=SUBSTITUTE(TOROW(ADDRESS(1,IF(FIND(","&C$1:BC$1/1&",",","&BE1&","),COLUMN(C:BC)),4),3),1,)

yvll

袁勇 发表于 2024-7-16 16:57
问题1：

非常好，谢谢您，期待能解问题2.

yvll

gadxjyang 发表于 2024-7-16 17:15
问题1借用楼上方法：
=SUBSTITUTE(ADDRESS(1,TOROW(IF((--TEXTSPLIT(BE1,,",")=C1:BC1)*(C1:BC1""),COLUMN ...

谢谢，我的excel版本不支持函数TOROW

yvll

qwer401245 发表于 2024-7-16 17:26
=SUBSTITUTE(TOROW(ADDRESS(1,IF(FIND(","&C$1:BC$1/1&",",","&BE1&","),COLUMN(C:BC)),4),3),1,)

谢谢，我的excel版本不支持函数TOROW

yvll

期待问题能解答。

wlc5201130

=SUBSTITUTE(MID(CELL("address",OFFSET($B$1,,SMALL(IF(ISNUMBER(FIND(","&$C$1:$BC$1&",",","&$BE1&",")),COLUMN($A$1:$BA$1)),COLUMN(A1)),)),2,9),"$1",)

yvll

wlc5201130 发表于 2024-7-16 17:37
=SUBSTITUTE(MID(CELL("address",OFFSET($B$1,,SMALL(IF(ISNUMBER(FIND(","&$C$1:$BC$1&",",","&$BE1&",")) ...

谢谢您，期待问题2能解答。