9月11日写了一篇批量打印信息表的文章,想不到点击量破了我的历史记录。

趁热打铁,今天利用EXCEL函数“万金油”INDEX+SMALL+IF+ROW组合,根据农户的户编号提取整户信息并批量打印。

老规矩,先上图。

图1

图2

图3

以上基本信息为随机生成,如有雷同,实属巧合。

今天的表只有两个,第一表为“基本信息表”,第二表为“信息对照表”,根据第一表的基础数据,在第二表录入户编号,调用该户所有家庭成员的基本信息。

一、基本信息表处理

如果想通过一个编号调用整户成员信息,那我们要先把所有家庭成员的户编号处理为同一个。

在C4单元格输入函数:=IF($I4="户主",SUM(C3,1),C3)

那为什么不在C3单元格输入函数呢?因为C3为所有数据的第一个,直接输入1。

图4

函数的意思是:如果是家庭成员,户编号和户主相同,等于上一个单元格值;如果是户主,相当于第二户,要在上一户的基础上加1。

二、家庭成员信息调用

处理好户编号后,我们开始调用整户信息。看下图,为减少跨表格调用函数的量,序号和性别我们可以用函数从其他信息(如身份证号码)中提取。

在D6单元格输入函数:=IFERROR(INDEX(基本信息表!$F$1:$F$42,SMALL(IF(基本信息表!$C$1:$C$42=$E$1,ROW($C$1:$C$42)),ROW(A1))),"")

因为是数组公式,所以要同时按Ctrl+Shift+Enter三键结束,否则结果错误。

图5

函数解析:

IF(基本信息表!$C$1:$C$42=$E$1,ROW($C$1:$C$42)):先用IF判断户编号(我们设置户编号的单元格,即E1)是否和“基本信息表”里的编号(基本信息表!$C$1:$C$42为编号所在区域)相同,如果相同则列出所在的行号。

然后用SMALL(所在的行号,ROW(A1))函数对符合条件的行号进行排序。

INDEX(基本信息表!$F$1:$F$42:显示符合条件需要调用数据的区域,比如我们要调用的是姓名,姓名在“基本信息表”的F列。

IFERROR(值,""):因为本例中的户编号不止一个,比对单个户编号会出错,所以用IFERROR隐藏错误值。

用刚才的函数和方法依次对身份证号码、与户主关系等内容进行调用。

因为户编号的位置是固定的,所以其他列只需要更改INDEX(基本信息表!$F$1:$F$42中对应的列号范围即可。

序号处理:

在第一个姓名的序号单元格直接录入1,或者用函数=IF(D6="","",1),因为本表中如果没有户主就不存在该户。

在C7单元格输入函数=IF(D7="","",SUM(C6,1)),判断姓名为空的话则显示空,如果姓名不为空,则在上一行的基础上加1。

在E6单元格输入函数:=IF(F6="","",IF(MOD(MID(F6,17,1),2)=1,"男","女"))提取性别,前面已经讲过身份证提取信息的方法,此例不再重述。

图7

通过上述方法,就完成了数据的调用,只需要在户编号单元格输入户编号,即可显示该户所有成员的信息。

批量打印的方法请参考我9月11日发布的文章《用好电子表格VBA,打印30万张表格不用太久》(网址:https://www.toutiao.com/i7006574082561147429/)。

设置“户主”所在行背景颜色的方法:

单击C3单元格,在“开始”选项卡下,“条件格式”—“新建规则”—“使用公式确定要设置格式的单元格”,录入函数:=$I3="户主",然后点“格式”—“图案”,选择自己喜欢的背景颜色,然后点击“确定”返回。

图8