[本文来自：www.11jj.com]

Excel成长课堂 

跟着课堂，一起成长！

[原文来自：www.11jj.com]

                            

 

文/牛哥

今天给大家介绍一个群里遇到按家庭统计补贴金额的真实案例。

要求根据C列的个人发放金额，在D列户主对应位置计算出每个家庭发放金额总数。

题目的要求可以表述为：当B列对应的行的单元格内容为“户主”时，D列对应的单元格依次输入各家庭的发放总金额，总金额的计算为从户主开始到本户的最后一个成员为止的发放金额合计；而对应行单元格内容为”非户主”时，G列对应的单元格依为空值。

D2单元格的公式为：=IF(B2="户主",SUM(OFFSET(B2,,1,MATCH("户主",B3:$B$14,0),1)),"")，向下填充即可。

1、 MATCH("户主",B3:$B$14,0)：

这个公式为统计“户主”在参数范围B3:$B$14内的精确位置，如果范围内有多个符合条件时，返回第一个符合条件单元格在该范围内的位置信息。需要强调的时，这里范围写法为B3:$B$14，B3要相对引用，，而B14要绝对引用，这是一个动态范围，下拉的时候B3会依次增加，从而改变统计范围，以家庭1为例，MATCH("户主",B3:$B$14,0)返回的是第一个满足条件的“户主”在B3:B14这个范围内的单元格位置，我们发现第一个满足该条件的为B5单元格，因此上面MATCH公式应该返回B5在B3：B14中的精确位置，也就是3，这样就得到家庭1的人数为3了。

2、 OFFSET(B2,,1,MATCH("户主",B3:$B$14,0),1))：

利用OFFSET函数来动态获得每一个户主对应的所有家庭成员发放金额的单元格范围，以第一个家庭为例，B2为户主对应的位置；二参省略表示行不偏移；三参为1表示列偏移1，这样就偏移到C列金额所在的单元格C2了；四参为上面MATCH函数计算出来的家庭人口数3，表示从C2单元格开始向下连续选择3个单元格（包含C2本身），这样选择的范围就是C2:C4单元格，也就是第一个家庭所有成员对应的发放金额的单元格；

3、 SUM(OFFSET(…))：

利用SUM函数对上面OFFSET获得的没一个家庭成员发放金额范围求和，就得到了每个家庭的发放总金额；

4、 =IF(B2="户主",SUM(…),"")：
只有当B列对应的单元格为“户主”时，才显示每个家庭的发放总金额，而非户主是显示空白；

5、为防止最后一个家庭人数无法确定的问题，可以在B列最后一个有效单元格下面即B14单元格手动填上“户主”，上面函数的下限范围也同样要选择B14单元格。

怎么样，小伙伴们，这个方法你学会了吗？利用OFFSET和MATCH函数获取动态范围是非常经典的组合用法，大家一定要牢记哦！

本文中使用的数据表格模板，可在公众号主页回复“不定行求和”获取下载链接。

关注成长课堂，

我们会持续输出实战干货，

欢迎推荐给您身边的朋友和同事。