【原】练习题078：处理户口数据的几个实用公式（函数公式）

最近推送的几篇文章：

• 自定义格式的经典应用场景，七夕：愿天上人间，占得欢娱，年年今夜。

• 练习题077：生成指定序列的数据（基本操作）

• 【赠视频课程】汇报工作如何让领导眼前一亮？《财富》新榜秒变高端图表

• 照相机的绝妙创意应用③：纵向面积图、根据大小变色的动态占比数字

·  正  ·  文  ·  来  ·  啦  ·

已知条件：

1、B列到E列为已知数据

2、每一户的户主都在该户的第一行

3、身份证号第17位是偶数则为女，奇数为男。

练习题078：

1、在A列编制公式生成序号，按户主编号，逐户递增【10分】

2、在H列编制公式，根据E列身份证号倒数第二位计算其性别，并核查D列性别是否正确【10分】

3、在F列编制公式，自动查找出B列的户主姓名【20分】

4、在G列编制公式，自动统计本户的总人数【20分】

5、使用条件格式，隔户填色，效果如“答案”工作表所示【20分】

6、使用条件格式，隔户填色及添加底部边框，效果如“答案2”工作表所示【20分】

要求：

1、F列户主姓名、G列本户人数不能引用第1问A列的数据。

答案：

1、在A列编制公式生成序号，按户主编号，逐户递增【10分】

A2单元格公式：

=COUNTIF($C$2:C2,"户主")

2、在H列编制公式，根据E列身份证号倒数第二位计算其性别，并核查D列性别是否正确【10分】

知识点：身份证号第17位是偶数则为女，奇数为男。

H2单元格公式：

先用MID函数取出身份证号第17位，用ISEVEN函数判断是否为偶数，如果是偶尔，则返回“女”，否则返回“男”，公式：

=IF(ISEVEN(MID(E2,17,1)),"女","男")

将前面公式与D列进行比较：

=IF(ISEVEN(MID(E2,17,1)),"女","男")=D2

3、在F列编制公式，自动查找出B列的户主姓名【20分】

F2单元格公式：

=LOOKUP(1,0/($C$2:C2="户主"),$B$2:B2)

注意单元格的引用类型。

关于此公式的解释，详见《偷懒的技术2：财务Excel表格轻松做》第三章。

4、在G列编制公式，自动统计本户的总人数【20分】

分析：

每一户的第一行都是户主，本户户主与下一户户主的位置数，相差的个数加1，就是本户的人数。所以可以用MATCH函数来查找本户“户主”与下一户“户主”的位置差+1，来计算本户人数。

公式：

=IF(C2="户主",MATCH("户主",C3:$C$31,0)-MATCH("户主",C2:$C$31,0)+1,G1)

5、使用条件格式，隔户填色，效果如“答案”工作表所示【20分】

选定A2：G2单元格区域，选定后A2单元格要为活动单元格

然后设置条件格式，条件格式的公式：

=MOD(COUNTIF($C$2:$C2,"户主"),2)=0

注意单元格的引用类型。

如果不了解为什么是$C$2:$C2，请参阅Excel偷懒的技术公众号之前推送的文章：

你真的理解了相对引用？这三点95%的人都不会，你呢？

6、使用条件格式，隔户填色及添加底部边框，效果如“答案2”工作表所示【20分】

选定A2：G2单元格区域，选定后A2单元格要为活动单元格，然后设置条件格式

=COUNTIF($A$2:$A$31,$A2)=COUNTIF($A$2:$A2,$A2)

本文知识点

函数公式

条件格式

----------------------

Excel畅销书推荐：

《“偷懒”的技术：打造财务Excel达人》

2017年当当网畅销榜Excel类第一名，办公类第二名，好评率99.8%，学Excel必选书籍！

《“偷懒”的技术2：财务Excel表格轻松做》

《偷懒2》不是《偷懒1》的改版，两者内容是完全不同的，《偷懒2》主要介绍灵活如何用函数公式、功能技巧专业地设计表格。并将日常工作常用的公式设计成模型公式，要用的时候，直接比照套用就是了。

宗旨是：

表格不会做，照搬即可；

公式不会写，套用就行。