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

最近推送的几篇文章：

·  正  ·  文  ·  来  ·  啦  ·

已知条件：
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》主要介绍灵活如何用函数公式、功能技巧专业地设计表格。并将日常工作常用的公式设计成模型公式，要用的时候，直接比照套用就是了。
宗旨是：
表格不会做，照搬即可；
公式不会写，套用就行。
赞 (0)

相关推荐