用好电子表格VBA,打印30万张表格不用太久
以前在人力部门上班的时候,上级安排对16—59周岁的人群进行劳动力转移就业调查,需要打印包含个人基本信息的调查表进行入户校对,但调查表是空表,基本信息得自己填写。
全县符合条件人群近30万人,如果一个一个填写那还得了,时间要求又紧。我平时就喜欢捣鼓电子表格,通过电子表格,在很多工作上极大地提高了效率。
好在以前采集过全县所有人口的基本信息,这就好办了。我的思路是:利用EXCEL电子表格把基本信息导入调查表,然后批量打印。
图1
图2
上述表册信息为随机生成,调查表也不长这样,主要是为了演示方法。
一、利用编号取值
因为每个人的编号都是独立的,所以我们要通过编号进行比对取值,先完成调查表填充。
如图2,在姓名处B3单元格输入函数=INDEX(基本信息!B:B,MATCH($S$2,基本信息!A:A,))调取对应编号的姓名。
公式解析:
首先用MATCH($S$2,基本信息!A:A,)取得编号所在的行号,$S$2为我们录入的编号,因为编号在基本信息表A列,所以用基本信息!A:A。
然后用NDEX(基本信息!B:B,行号)提取我们录入编号的对应行信息,基本信息!B:B表示姓名所在的列。
复制刚才的函数,依次在其他对应单元格提取信息,只需要修改基本信息!B:B列标即可。
图3
细心的朋友可能会问,基本信息表里的身份证号码为一个单元格,但是调查表里是18个单元格,该如何调用呢?
其实很简单,在调查表身份证第一个单元格按照刚才的方法提取身份证号码,然后用mid+column提取每个身份证中的号码。函数如下:
=MID(INDEX(基本信息!$D:$D,MATCH($S$2,基本信息!$A:$A,)),COLUMN(A1),1)
这里要注意的是,调用基本信息表的列要锁定,要不然往后拖动复制就会出错,提示未找到查找区域。
COLUMN(A1)表示第一列,往后拖动复制会变成COLUMN(B1),表示第二列。
二、利用VBA进行批量打印
在调查表合适位置按下图制作打印的开始编号和结束编号,然后制作命令按钮。
图4
命令按钮制作方法:
在“开发工具”选项卡下找到“命令”按钮,单击后在电子表格空白处拖动绘制,然后右击按钮点击“属性”,设置显示名称、字体字号等信息。
当然,默认也可以。
图5
提示:如果我们用的办公软件是WPS的,需要安装WPSVBA运行库。本文中还需要写代码,所以保存的电子表格必须启用宏。
我常用启用宏的电子表格后缀名:xls和xlsm。
右击我们刚才创建的“命令”按钮,点击“查看代码”,然后把如下代码复制粘贴到代码窗口。
Private Sub CommandButton1_Click()
For i = Range('x3') To Range('x4')
Range('s2') = i
ActiveSheet.PrintOut
Next
End Sub
代码大致意思是:
CommandButton1_Click表示按钮名称,不是标题。
用For i = Range('x3') To Range('x4')指定开始编号和结束编号,如果对应我们输入的编号,则逐份打印。
图6
到此,整个过程就完成了,我们设置调查表打印区域后,指定开始编号和结束编号,然后点击批量打印按钮即可逐份打印。
提示:批量打印前,要先退出命令按钮设计模式哦。输入编号后,点击软件左上角的打印可以打印当前编号的内容。
怎么样,是不是很轻松,领导以为要一个月才能完成打印的工作我们一星期就解决了。