跨sheet分年分月取数汇总

欢喜自在

分别有美团和携程2个sheets，有N年1-10月的表，但年份月份太多只需要抽取合计里有数据的月份
效果如图，见附件，A-D列都是未知条件，有N个sheets，16-2X年份，满足sheet里合计里有金额就抽取A-D的数据
大佬帮看看怎么设？



汇总.zip (13.63 KB, 下载次数: 84)

2021-11-18 22:33 上传

点击文件名下载附件
汇总.zip

有趣的灵魂三百斤

=IF(COLUMNS($A:A)=1,IF(ROW()-2<COUNTIF(INDIRECT("美团!D:D"),">0"),"美团","携程"),INDEX(IF({1,0,0,0},LOOKUP(ROW($1:$99),ROW($1:$99)/FIND("年",T(INDIRECT(IF(ROW()-2<COUNTIF(INDIRECT("美团!D:D"),">0"),"美团","携程")&"!A"&ROW($1:$99))))^0,T(INDIRECT(IF(ROW()-2<COUNTIF(INDIRECT("美团!D:D"),">0"),"美团","携程")&"!A"&ROW($1:$99))))&T(INDIRECT(IF(ROW()-2<COUNTIF(INDIRECT("美团!D:D"),">0"),"美团","携程")&"!A"&ROW($1:$99))),N(INDIRECT(IF(ROW()-2<COUNTIF(INDIRECT("美团!D:D"),">0"),"美团","携程")&"!R"&ROW($1:$99)&"C"&{1,2,3,4},))),SMALL(IF(SUBTOTAL(9,INDIRECT(IF(ROW()-2<COUNTIF(INDIRECT("美团!D:D"),">0"),"美团","携程")&"!D"&ROW($1:$99)))>0,ROW($1:$99)),COUNTIF($A$2:$A2,$A2)),COLUMN()-1))

你要的公式……

必修二十五

如果有N个sheet呢，也就是美团携程后面还有N个

听离歌为对面凑响离歌

那我就不管了，谁让你一开始不上真实数据的

醉酒笑红尘

刷新上传了，实际有几十个sheets的

梦醒了心碎了

C2=INDIRECT($A2&"!"&CHAR(COLUMN(B1)+64)&MATCH(TEXT($B2,"e年"),INDIRECT($A2&"!A:A"),)+TEXT($B2,"m"))
下拉右拉。