关于有合并单元格的列标题提取问题

alexli

表中列标题有三行，有合并单元格，想要把标题合并到一行中，结果在附件第6行，求帮忙

无聊的黑猫

=INDEX(LOOKUP(COLUMN($A$2:$AY$2),IF($A$2:$AY$2<>"",COLUMN($A$2:$AY$2)),$A$2:$AY$2)&IFERROR(LOOKUP(COLUMN($A$3:$AY$3),IF($A$3:$AY$3<>"",COLUMN($A$3:$AY$3)),$A$3:$AY$3),"")&$A$4:$AY$4,COLUMN(A1))三键 右拉

丢丢表格

=IF(COUNTA(A2:A4),INDEX(2:2,MAX(IF($A2:A2>0,COLUMN($A:A))))&INDEX(3:3,MAX(IF($A2:A2>0,COLUMN($A:A)),IF($A3:A3>0,COLUMN($A:A))))&A4,"")

alexli

无聊的黑猫 发表于 2023-3-28 16:33
=INDEX(LOOKUP(COLUMN($A$2:$AY$2),IF($A$2:$AY$2"",COLUMN($A$2:$AY$2)),$A$2:$AY$2)&IFERROR(LOOKUP(CO ...

不完全对，不过谢谢

高个子

1. =SUBSTITUTE(TRIM(CLEAN(IF(AND(A2:A4=""),"",LOOKUP("座",$A2:A2)&IF(AND(A3="",B3=""),"",IFERROR(LOOKUP("座",$A3:A3),"")))&A4)),"  ",)

复制代码

alexli

丢丢表格 发表于 2023-3-28 16:38
=IF(COUNTA(A2:A4),INDEX(2:2,MAX(IF($A2:A2>0,COLUMN($A:A))))&INDEX(3:3,MAX(IF($A2:A2>0,COLUMN($A:A)), ...

，谢谢大神，膜拜

alexli

高个子 发表于 2023-3-28 16:44

谢谢大神

mmlzh

=IF(A2&A3&A4="","",LOOKUP("座",$A2:A2)&IFERROR(LOOKUP("座",INDIRECT("r3c"&LOOKUP("座",$A2:A2,COLUMN($A:A))&":r3c"&COLUMN(),)),"")&A4)

alexli

mmlzh 发表于 2023-3-28 16:51
=IF(A2&A3&A4="","",LOOKUP("座",$A2:A2)&IFERROR(LOOKUP("座",INDIRECT("r3c"&LOOKUP("座",$A2:A2,COLUMN( ...

谢谢，试试

我的城市你来过

=IFNA(INDEX($A$2:$AY$2,,LOOKUP(COLUMN($A$2:$AY$2),IF($A$2:$AY$2<>"",COLUMN($A$2:$AY$2),"")))&INDEX($A$3:$AY$3,,LOOKUP(COLUMN($A$3:$AY$3),IF($A$3:$AY$3<>"",COLUMN($A$3:$AY$3),"")))&$A$4:$AY$4,$A$2:$AY$2)