欢喜自在 发表于 2021-11-18 22:33:00

跨sheet分年分月取数汇总

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



有趣的灵魂三百斤 发表于 2021-11-21 21:07:25

=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))

你要的公式……

必修二十五 发表于 2021-12-4 02:28:10

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

听离歌为对面凑响离歌 发表于 2021-12-23 10:43:06

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

醉酒笑红尘 发表于 2022-1-5 05:17:00

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

梦醒了心碎了 发表于 2022-1-13 06:53:38

C2=INDIRECT($A2&"!"&CHAR(COLUMN(B1)+64)&MATCH(TEXT($B2,"e年"),INDIRECT($A2&"!A:A"),)+TEXT($B2,"m"))
下拉右拉。
页: [1]
查看完整版本: 跨sheet分年分月取数汇总