多工作表动态累计求和
大家好,今天和大家分享“多工作表动态累计求和',对于跨工作表求和对于初级学员,新手来说是一个棘手的问题,跨工作簿就更不说了,中级用户也搞不定,因为跨工作簿要用到vba和透视里的SQL这些知识,也许你会说,这些我都没有听过,说明你的学习Excel路还很长,如果你天天要和Excel打交道2个小时以上的朋友,建议好好学学Excel
一、这问朋友的提问
A列公式中的求和怎么根据B1单元格月份的变动来求相应的累计值?
如:B2=2月,A列求的累计值是1月:2月这工作表之间的合计值;B2=5月时,A列求的累计值是1月:5月这工作表之间的合计值,具体效果看下面的动画
二、方法1:用计算器相加
这种方法,来读今天这篇文章的肯定没有了,但是现实生活中真有这样的人,他不知Excel这个电子表格可以计算,他一这打开Excel,一边用手指敲着计算器。
三、方法2:用公式一个一个引用各个工作表
这一类朋友可以占的比例就多了,输入一个等号,然后一个一个工作表去引用
1、公式截图
2、公式
='1月'!A1 '2月'!A1 '3月'!A1 '4月'!A:A '5月'!A1
3、公式解释
如果单纯是这样求和,这样做也是可以的,但是如果再什么条件用这种方法就没有办法实现了
这种方法,当然也实现不了动态,也就是不能根据B1单元格值而改变求和的工作表数目,只能手动重新修改一个公式,再填充公式,它的缺点显而易见。
四、方法3:N降维实现
1、公式截图
2、公式
=SUM(N(INDIRECT(ROW(INDIRECT('1:'&SUBSTITUTE($B$1,'月','')))&'月!A'&ROW(A1))))
3、公式解释
这是数组公式,把公式输入之好,光标定位到编辑栏里,然后三键一齐下Ctrl Shift 回车
INDIRECT('1:'&SUBSTITUTE($B$1,'月',''))把月替换掉得到月份的数字这样和到文本“1:5',我们在它外面嵌套一个indirect函数就变成了1到5行的单元格区域,记得是1到5行的单元格区域,得到是单元格区域,重要的事我说三遍了。我们在它外面再嵌套一个row函数,得到1到5行的的行号:1;2;3;4;5
ROW(INDIRECT('1:'&SUBSTITUTE($B$1,'月','')))&'月!A'&ROW(A1)这个返回的是{'1月!A1';'2月!A1';'3月!A1';'4月!A1';'5月!A1'},得到每个工作表的A1单元格文本,记得是文本字符串,只有再给它嵌套一个indirect函数,才会变成每个工作表的A1单元格,这个是动态的,下拉A1会变成A2,得到公式INDIRECT(ROW(INDIRECT('1:'&SUBSTITUTE($B$1,'月','')))&'月!A'&ROW(A1))
为什么要在indirect函数前面加N函数呢?,因为indirect函数这里构建了多维,所以要通过N函数来降维
最后用sum函数求和得到我们想要结果
五、方法4:Sumif降维实现
1、公式截图
2、公式
=SUM(SUMIF((INDIRECT(ROW(INDIRECT('1:'&SUBSTITUTE($B$1,'月','')))&'月!A'&ROW(A1))),'<>0'))
3、公式解释
大部分和第3种解法差不多,也是数组公式
sumif这里也是起到一个降维作用,相当于N函数
sumif函数第1参数:条件所在的区域是(INDIRECT(ROW(INDIRECT('1:'&SUBSTITUTE($B$1,'月','')))&'月!A'&ROW(A1)))得到单元格区域
sumif函数第2参数:条件是不'<>0',因为我们这里是求和,这样设置和条件,不管它是负数,还是正数都可以
sumif函数第3参数和第1参数如果是一样的,第3参数可以不写。
六、小结
前面2种方法不可取,后面2种方法才体现Excel函数博大精深,这话一点也不假,当然,如果你确实不会函数,那也只能用前面2种方法,当然两者之间办事效率就不用我说了
联系客服