帮忙看下怎么实现

hjw123123

各位大神：


  帮忙看一下，我想自动要实现结果sheet1的的效果，怎么处理。sheet1结果怎么自动合并后面客户一至客户九的内容，跪谢！

zhumumu1816

你这个用宏比较好，用公式方法比较笨，可以参考下：

=FILTER(HSTACK(SCAN(NA,TAKE(VSTACK(HSTACK(SHEETSNAME(客户一!B9:C10),(客户一!B9:C10)),HSTACK(SHEETSNAME(客户二!B1:C9),客户二!B9:C12),HSTACK(SHEETSNAME(客户三!B9:C195),客户三!B9:C195),HSTACK(SHEETSNAME(客户四!B8:C12),客户四!B8:C12),HSTACK(SHEETSNAME(客户五!B10:C11),客户五!B10:C11),HSTACK(SHEETSNAME(客户六!B9:C12),客户六!B9:C12),HSTACK(SHEETSNAME(客户七!B9:C27),客户七!B9:C27),HSTACK(SHEETSNAME(客户八!B62:C70),客户八!B62:C70),HSTACK(SHEETSNAME(客户九!B9:C14),客户九!B9:C14)),,1),LAMBDA(a,b,IFERROR(b,a))),TAKE(VSTACK(HSTACK(SHEETSNAME(客户一!B9:C10),(客户一!B9:C10)),HSTACK(SHEETSNAME(客户二!B1:C9),客户二!B9:C12),HSTACK(SHEETSNAME(客户三!B9:C195),客户三!B9:C195),HSTACK(SHEETSNAME(客户四!B8:C12),客户四!B8:C12),HSTACK(SHEETSNAME(客户五!B10:C11),客户五!B10:C11),HSTACK(SHEETSNAME(客户六!B9:C12),客户六!B9:C12),HSTACK(SHEETSNAME(客户七!B9:C27),客户七!B9:C27),HSTACK(SHEETSNAME(客户八!B62:C70),客户八!B62:C70),HSTACK(SHEETSNAME(客户九!B9:C14),客户九!B9:C14)),,-2)),TAKE(HSTACK(SCAN(NA,TAKE(VSTACK(HSTACK(SHEETSNAME(客户一!B9:C10),(客户一!B9:C10)),HSTACK(SHEETSNAME(客户二!B1:C9),客户二!B9:C12),HSTACK(SHEETSNAME(客户三!B9:C195),客户三!B9:C195),HSTACK(SHEETSNAME(客户四!B8:C12),客户四!B8:C12),HSTACK(SHEETSNAME(客户五!B10:C11),客户五!B10:C11),HSTACK(SHEETSNAME(客户六!B9:C12),客户六!B9:C12),HSTACK(SHEETSNAME(客户七!B9:C27),客户七!B9:C27),HSTACK(SHEETSNAME(客户八!B62:C70),客户八!B62:C70),HSTACK(SHEETSNAME(客户九!B9:C14),客户九!B9:C14)),,1),LAMBDA(a,b,IFERROR(b,a))),TAKE(VSTACK(HSTACK(SHEETSNAME(客户一!B9:C10),(客户一!B9:C10)),HSTACK(SHEETSNAME(客户二!B1:C9),客户二!B9:C12),HSTACK(SHEETSNAME(客户三!B9:C195),客户三!B9:C195),HSTACK(SHEETSNAME(客户四!B8:C12),客户四!B8:C12),HSTACK(SHEETSNAME(客户五!B10:C11),客户五!B10:C11),HSTACK(SHEETSNAME(客户六!B9:C12),客户六!B9:C12),HSTACK(SHEETSNAME(客户七!B9:C27),客户七!B9:C27),HSTACK(SHEETSNAME(客户八!B62:C70),客户八!B62:C70),HSTACK(SHEETSNAME(客户九!B9:C14),客户九!B9:C14)),,-2)),,-1)>0)

dambcer

用VBA处理。。。。

zwzc

=DROP(REDUCE("",TEXT(ROW(1:9),"[DBNum1]"),LAMBDA(x,y,VSTACK(x,IFNA(HSTACK("客户"&y,OFFSET(INDIRECT("客户"&y&"!A"&MATCH("序号",INDIRECT("客户"&y&"!A:A"),)),1,1,COUNT(INDIRECT("客户"&y&"!A:A")),2)),"客户"&y)))),1)

lanjian229

zhumumu1816 发表于 2024-12-11 11:12
你这个用宏比较好，用公式方法比较笨，可以参考下：

=FILTER(HSTACK(SCAN(NA,TAKE(VSTACK(HSTACK(SHEETS ...

你这个公式太夸张了

lanjian229

=DROP(REDUCE(0,SHEETSNAME(,1,1),LAMBDA(x,y,VSTACK(x,IFERROR(HSTACK(y,FILTER(INDIRECT(y&"!B:C"),ISNUMBER(INDIRECT(y&"!A:A")))),y)))),1)

lanjian229

如果需要唯一的可以套个unique

hjw123123

能帮忙写个VBA 后者是 宏吗？