用好函数批量添加Excel文件的表头

2022-05-30平淡

电脑爱好者 2022年3期

平淡

在日常的统计中，我们经常需要为一些数据添加表头，比如下表是某公司的原始工资单数据，现在需要在每个员工姓名的上方添加工资明细的表头（即图1中下半部分显示的样式）（图1）。手动添加非常繁琐，还容易出错。其实对于类似结构的转换，我们通过Excel 2019的内置函数就能高效地完成。

1. 数据分析

从图1下半部分所示的数据可以看出，转换要求是在每个员工姓名的上方都插入第一行的表头，同时在员工的工号和表头之间再插入一个包含“——”字符的空行（用于后續裁剪工资单时使用）。我们的操作思路是，在原始工资单数据之间插入两个空行，然后在新表中填充表头和分割字符，并引用填充了空行的原始工资单数据。

2. 引用原始工资单数据

假设原始工资单数据保存在Sheet1中，我们先复制A 2 ： J10区域中的数据。接着新建Sheet2并定位到它的A1单元格，然后依次点击菜单栏中的“开始→粘贴→其他粘贴选项→粘贴链接”，将原始工资单数据以链接的形式粘贴到A1：J9区域（图2）。

3. 在数据间插入两个空行

在Sheet2中按下“Alt+F11”组合键打开VBA编辑窗口，依次点击“插入→模块”，将下列代码输入到代码框中（图3）。

Sub 插入空行（）

D im i As Long

For i = Range（"A50"）.End（xlUp）.Row To 2 Step -1

R ows（i）.Resize（2）.Insert

N ext

End Sub

返回到Sh e et 2窗口，依次点击“开发工具→宏→插入空行”（即通过上述代码生成的宏），再点击“执行”，即可在链接的工资单数据之间插入两个空行。接着在表格的最上方再插入一个空行，在A1单元格中输入公式“=Sheet1！A1”，然后向右填充公式到J1单元格，这样就会将Sheet1中的表头引用到此（图4）。

4. 引用表头

新建S h e e t 3 ，在A1 单元格中输入公式“= S h e e t 1！A1”、A 2 单元格中输入公式“=Sheet 2！A 2”、A3单元格中输入“'--------”（即分割字符）、A4单元格中输入公式“= A1”。接着选中A1： A 4区域，向右填充公式到J1： J 4区域，并为第3行和第4行单元格填充合适的颜色，这样就完成了一个工资单数据的基本结构设计（图5）。

继续选中A 2： J 4区域，下拉填充到A 27：J27区域，这样就在Sheet3的第1行完成了表头的引用，第2 行完成了引用Sheet 2中的工资单数据，第3行完成了分割字符的填充，后续行的引用以此类推（图6）。

5. 生成电子工资单

为了方便员工核对工资，我们还可以制作一份电子工资单图片。定位到Sheet3的L3单元格，依次点击“数据→数据验证→设置”，在弹出的对话框中将“允许”设置为“序列”、“数据”选择“介于”、“来源”选择“=Sheet1！$A$2：$A$10”（即Sheet1中的员工工号），这样在L3单元格中点击下拉箭头就可以方便地选择某个工号的员工数据（图7）。

继续在M3单元格中输入公式“=VLOOKUP（$L$3，$A$2：$J$27，COLUMN（B2），0）”，并向右填充公式到U3单元格。公式表示以L3单元格中的数据为基准，在$A$2：$J$27区域中查找第2列的数据（即部门），由于嵌套了COLUMN函数，这样向右填充公式后会依次变为查找第3列、第4列……的数据。最后选中L3：U4区域并复制，然后定位到任意一个空白单元格（如L13单元格），依次点击“开始→粘贴→其他粘贴选项→链接的图片”。这样在L3单元格中选择不同的工号后，在L13单元格中就会自动生成对应员工的工资单图片，将该图片发送给员工就可以核对工资了（图8）。