头部广告

Excel动态求和秘籍!告别繁琐,一个公式轻松搞定!

我是【桃大喵学习记】,欢迎大家关注哟~,每天为你分享职场办公软件使用技巧干货!

——首发于微信号:桃大喵学习记

日常工作中,关于Excel动态求和这个问题,经常难道很多新手小伙伴。今天就从2种应用场景跟大家分享一下Excel动态求和秘籍,告别繁琐,一个公式轻松搞定!

场景一、根据截止月份进行动态求和

如下图所示,我们需要根据选择的姓名、截止月份对数据进行动态自动求和。

1.gif

方法:

第一步、制作姓名/截止月份下拉菜单

选择要创建下拉菜单的数据单元格→点击【数据】-【有效性】调出“数据有效性”窗口→在“数据有效性”窗口中的有效性条件“允许”选中【序列】,【来源】中选择所有姓名数据区域即可。然后,再使用相同的方法制作“截止月份”下拉菜单即可

2.gif

第二步、使用公式动态求和

在目标单元格中输入公式:

=SUM(OFFSET(A1,MATCH(A13,A2:A10,0),,,MATCH(B13,A1:N1,0)))

然后点击回车即可

3.gif

解读:

上面组合公式的重点在OFFSET函数,就是运用OFFSET函数根据选择的下拉菜单数据,构建一个动态的数据区域,最后用SUM函数进行求和,最终达到动态求和的效果。

OFFSET函数介绍

功能:OFFSET函数为偏移函数,它可以通过位置的偏移获取一段单元格范围区域;返回的引用可以为一个单元格或单元格区域,并可以指定返回的行数或列数。

语法:=OFFSET(起始位置,行数,列数,[高度],[宽度])

下面我们就以“刘备”/“5月”这个数据跟大家讲解下公式含义:

OFFSET(A1,MATCH(A13,A2:A10,0),,,MATCH(B13,A1:N1,0))

第1参数:起始位置,A1

第2参数:偏移行数,MATCH(A13,A2:A10,0)获取“刘备”这个在姓名这列中的位置,结果是6,所以函数会向下偏移6行就是想想偏移6个单元格,到达下图所示红色背景的单元格位置。

第3参数:偏移列数省略

第4参数:高度省略

第5参数:宽度,MATCH(B13,A1:N1,0)获取“截止月份”5月在A1:N1这个数据区域的位置,结果为7,所以函数返回的数据区域就是如下图所示红色背景+蓝色背景的数据区域。

4.jpg

场景二、根据起止月份时间段进行动态求和

如下图所示,这是一个产品月份销售明细表,我们根据产品名称、开始月份、结束月份动态查询汇总销售总额。

5.gif

方法:

第一步:制作下拉菜单

先制作查询表中产品名称、开始月份、结束月份的下拉菜单(原理都一样,以产品名称下拉菜单制作为例)

首先选择产品名称下面的单元格→点击【数据】-【有效性】调出“数据有效性”窗口→在弹出的“数据有效性”窗口“有效性条件”-“允许”中选择【序列】;“来源”选择销售明细表中产品名称所在数据区域→最后点击确定即可

6.gif

第二步、使用公式动态求和

在销售总额目标单元格中输入公式:

=SUMPRODUCT((B7:B14=A4)*C7:H14*(C6:H6>=B4)*(C6:H6<=C4))

点击回车即可

7.gif

解读:

①其实,实现上面提到的根据时间区间动态查询汇总求和,主要就是使用了SUMPRODUCT函数多条件求和。

②公式中的(B7:B14=A4)就是把销售明细表中的“产品名称”这列数据的每个元素跟查询表格中指定产品名称做判断,返回的结果是一个数组由逻辑值TRUE或者FALSE(备注:逻辑值TRUE可以看做1,逻辑值FALSE可以看做0)组成。

③公式中(C6:H6>=B4)就是判断销售明细表中的“月份数据”大于等于“开始月份”有哪些?返回的结果是一个数组由逻辑值TRUE或者FALSE(备注:逻辑值TRUE可以看做1,逻辑值FALSE可以看做0)组成。

④公式中(C6:H6<=C4)就是判断销售明细表中的“月份数据”小于等于“结束月份”有哪些?返回的结果是一个数组由逻辑值TRUE或者FALSE(备注:逻辑值TRUE可以看做1,逻辑值FALSE可以看做0)组成。

⑤最后4个数据区域先乘积,再求和,从而实现根据时间区间动态查询汇总求和。

以上就是【桃大喵学习记】今天的干货分享~觉得内容对你有所帮助,别忘了动动手指点个赞哦~。大家有什么问题欢迎关注留言,期待与你的每一次互动,让我们共同成长!


底部广告