1职场实例
小伙伴们大家好,今天我们来解决一个公众号后台粉丝留言咨询的Excel职场案例:如何通过下拉列表选择不同的截止月份,实现对产品产量的动态累计求和,这个问题涉及到多个基础的常见的,必学必会的Excel技巧,所以小编整理好了思路分享给大家,以备不时之需。
如下图所示:
A2:F5为一张各种水果不同月份的产量二维明细报表,我们想要通过在F2单元格的下拉列表中获取不同的截止月份,实现对水果的产量按月累计汇总求和。比如说我们以“苹果”为例,当F2单元格切换到1月时,即截止到1月的累计产量为100;当F2单元格切换到2月时,即截止到2月的累计产量为100+200=300,以此类推。
2解题思路
首先我们需要利用数据验证功能制作下拉列表。
选中F2单元格,点击【数据-数据验证】,在弹出的【数据验证】的功能面板中,将【允许】设置为【序列】,在【来源】处,框选区域:B2:E2,最后点击【确定】按钮后即可完成下拉列表的设置,具体操作过程如下图所示:
我们在F3单元格输入函数公式:
=MATCH($F$2,$B$2:$E$2,0)
MATCH函数返回指定的值在指定数组中的相对位置。
基本语法结构:
=Match(查找值,查找范围,[匹配模式])
匹配模式有-1、0、1三种,分别为:大于、精准匹配、小于。当省略此参数时,默认为精准匹配。
我们利用Match函数,查找F2单元格中的“截止月份”数据,获取在B2:E2区域内的相对位置。比如F2单元格中的“2月”,在B2:E2区域内的相对位置为2;比如F2单元格中的“1月”,在B2:E2区域内的相对位置为1。
我们继续完善F3单元格中的函数公式为:
=OFFSET(B3,,,,MATCH($F$2,$B$2:$E$2,0))
OFFSET函数是以指定的引用为参照系,通过给定的偏移量返回新的引用。
基本语法结构:
=offset(参照单元格,偏移至第几行,偏移至第几列,选取几行,选取几列)
本例中我们省略了OFFSET函数的第2、第3、第4个参数,表示以B3单元格为基准参照单元格,偏移0行,偏移0列,选取0行,选取的列数我们用上一步的MATCH函数的返回值表示。获取到一个新的引用区域。
比如说:当F2单元格切换到“2月”时,MATCH函数的返回值为2,即以B3单元格为基准参照单元格,偏移0行,偏移0列,选取0行,选取2列,返回新的引用,并以数组的方式存储:
={100,200}
再比如说:当F2单元格切换到“3月”时,MATCH函数的返回值为3,即以B3单元格为基准参照单元格,偏移0行,偏移0列,选取0行,选取3列,返回新的引用,并以数组的方式存储:
={100,200,250}
至此我们就实现了:根据F2单元格切换到不同的截止月份后,我们就得到了对应的数组,并存储了对应的明细月份产量值。
我们继续完善F3单元格中的函数公式为:
=SUM(OFFSET(B3,,,,MATCH($F$2,$B$2:$E$2,0)))
最后我们只需要用SUM求和函数对上一步数组内各元素求和,就可以实现通过下拉列表选择不同的截止月份,实现对产品产量的动态累计求和的效果了。如下图所示: