二维码 购物车
部落窝在线教育欢迎您!

快速统计家庭人口数的两种方法,最后一户也能统计

 

作者:老菜鸟来源:部落窝教育发布时间:2020-05-26 17:49:36点击:314

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

哈喽,大家好!如何快速统计家庭人口数呢?网上给出的大多公式不能统计最后一户的人数,最后一户需要手动填写。今天我们将提供两种方法,全自动统计所有家庭的人口数。第一个公式是从上到下统计,第二个公式是从下往上统计。使用的函数包括IFERROR、IF、MATCH、COUNTA、SUM函数等,赶紧来看看吧!


对于户籍统计工作者来说,在一份人口清单中统计每户的人口数是家常便饭了,近日就有群友提出了这方面的一个问题,询问有无公式能够得到家庭人口数,实在不想一个一个手动填写了,模拟数据源如图所示:

 

 

人员姓名系模拟数据,如有雷同纯属巧合。要求是在每家户主所在行填写对应的家庭人口数,每个小区都有几百户需要统计,纯靠手工填写想想都吓人,今天就分享两个可以统计家庭人口数的公式套路,想一起学的赶紧下载课件准备开始吧。

 

公式1=IFERROR(IF(C2="户主",MATCH(C2,C3:C9,),""),COUNTA(C3:C9)+1)

 

 

这个公式看起来有点长,其实核心只是MATCH(C2,C3:C9,)这部分,因此先从这个地方开始解释。

 

MATCH函数的基本功能是得到一个数据在一组数据中出现的位置,例如C2中的内容(“户主”)在C3:C9这个区域中出现的位置是3

 

 

注意这里区域的选择,是从户主的下一行开始的,得到的实际上是第二个户主出现的位置,但是这个数字正好就是所统计的这一户的家庭人口数,想明白这一点对理解后面的原理很重要。

 

C3:C9包含了7个单元格,如果存在超过7口人的家庭,这个范围就要扩大,否则会出现错误,至于具体用什么区域统计,明白这一点就可以自己调整了,或者直接用C3:C99也行。

 

明白了MATCH这部分之后,增加了IFIF(C2="户主",MATCH(C2,C3:C9,),"")也就不难理解了。

 

 

仅当C列为户主的时候,才显示MATCH的结果,其他都显示为空白。

 

此时的公式看似已经实现了需要的结果,但是当我们把表格拉到最下面的时候,就发现有问题。

 

 

这是因为在最后一个户主之后,MATCH无法继续找到户主就得到了错误值,解决方法有两个,第一个方法是在最下面写一个户主进去,这样不用改变公式也能得到正确结果。

 

 

第二个方法就是修改公式,利用IFERROR函数单独计算最后一户的人口数,公式为:

 

=IFERROR(IF(C31="户主",MATCH(C31,C32:C38,),""),COUNTA(C32:C38)+1)

 

 

最后一户的人口数就是单元格区域中数据的个数加1COUNTA会对区域中有内容的单元格进行计数。

 

以上就是统计家庭人口数的第一个公式套路,这个公式完全是自上而下计数的逻辑,相信经过讲解大家应该是可以理解的,但是第二个公式套路就完全是逆向思维了,是自下而上的计数逻辑,公式看上去更加简短了,但是理解难度却增加了。

 

第二个公式是这样的:=IF(C2="户主",COUNTA(C2:C35)-SUM(D3:D36),"")

 

 

这个公式的特殊之处在于D2单元格的公式用到了同一列后面的单元格数据。

 

 

而且用之前分析公式的方法似乎都有点难以解释,比如单独看COUNTA(C2:C35),结果就是统计表中人数的递减,一共34人,每往下一行人数减少1

 

 

再看=COUNTA(C2:C35)-SUM(D3:D36)这部分的结果,又全都变成了1

 

 

但是再看加了IF的效果,公式=IF(C2="户主",COUNTA(C2:C35)-SUM(D3:D36),0)的结果又完全变了。

 

 

到底为什么会这样,为了便于大家理解,我们只用三户人家来做说明。

 

第三户人数统计结果为7,其实就是这样得到的,COUNTA函数统计了后面的所有人数,由于后面没有户主了,所以IF得到的都是0,这一点从最后一行来往上看,因此这个公式的思路是自下而上的。SUM得到的结果也就是0,进而COUNTA-SUM就变成了这一户的总人数。

 

 

再看统计第二户人数的时候,随着公式下拉,公式中的区域发生变化,COUNTA统计的是除第一户以外的总人数,应该是11人,由于非户主所对应的都是0,所以SUM得到的是第三户对应的人数7,这样第二户的人数就是11-7,结果是4人。第一户的3人也是这样倒推出来的。

 

这个公式难于理解的正是这种倒推计算的思路,如果一时间还无法明白的话,只要了解这个公式套路中的要点也可以随时套用,COUNTA中的范围是实际数据范围,而SUM中的范围是公式所在单元格下方的范围。

 

小结:对比今天这两个公式的套路,公式1算是一个常规思路,理解了相关函数的基本用法就能掌握,公式2则是思路上的彻底转变,不知道这两个公式你更喜欢哪个呢,欢迎留言分享你的心得。

 

本文配套的练习课件请加入QQ群:1043683754下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

合并单元格求和双十一到底要花多少钱?一张Excel表格,让你看得明明白白!

求和函数大汇总《求和,我是认真的(Excel函数教程)》

MATCH函数解析《MATCH:函数哲学家,找巨人做伴。新出道必学!》

COUNTIFS函数解析《同样是countifs函数,为什么同事却使得比你好?原因在这里!》