用好函數(shù)批量添加Excel文件的表頭

平淡

在日常的統(tǒng)計中，我們經(jīng)常需要為一些數(shù)據(jù)添加表頭，比如下表是某公司的原始工資單數(shù)據(jù)，現(xiàn)在需要在每個員工姓名的上方添加工資明細(xì)的表頭（即圖1中下半部分顯示的樣式）（圖1）。手動添加非常繁瑣，還容易出錯。其實對于類似結(jié)構(gòu)的轉(zhuǎn)換，我們通過Excel 2019的內(nèi)置函數(shù)就能高效地完成。

1. 數(shù)據(jù)分析

從圖1下半部分所示的數(shù)據(jù)可以看出，轉(zhuǎn)換要求是在每個員工姓名的上方都插入第一行的表頭，同時在員工的工號和表頭之間再插入一個包含“——”字符的空行（用于后續(xù)裁剪工資單時使用）。我們的操作思路是，在原始工資單數(shù)據(jù)之間插入兩個空行，然后在新表中填充表頭和分割字符，并引用填充了空行的原始工資單數(shù)據(jù)。

2. 引用原始工資單數(shù)據(jù)

假設(shè)原始工資單數(shù)據(jù)保存在Sheet1中，我們先復(fù)制A 2 ： J10區(qū)域中的數(shù)據(jù)。接著新建Sheet2并定位到它的A1單元格，然后依次點擊菜單欄中的“開始→粘貼→其他粘貼選項→粘貼鏈接”，將原始工資單數(shù)據(jù)以鏈接的形式粘貼到A1：J9區(qū)域（圖2）。

3. 在數(shù)據(jù)間插入兩個空行

在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窗口，依次點擊“開發(fā)工具→宏→插入空行”（即通過上述代碼生成的宏），再點擊“執(zhí)行”，即可在鏈接的工資單數(shù)據(jù)之間插入兩個空行。接著在表格的最上方再插入一個空行，在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區(qū)域，向右填充公式到J1： J 4區(qū)域，并為第3行和第4行單元格填充合適的顏色，這樣就完成了一個工資單數(shù)據(jù)的基本結(jié)構(gòu)設(shè)計（圖5）。

繼續(xù)選中A 2： J 4區(qū)域，下拉填充到A 27：J27區(qū)域，這樣就在Sheet3的第1行完成了表頭的引用，第2 行完成了引用Sheet 2中的工資單數(shù)據(jù)，第3行完成了分割字符的填充，后續(xù)行的引用以此類推（圖6）。

5. 生成電子工資單

為了方便員工核對工資，我們還可以制作一份電子工資單圖片。定位到Sheet3的L3單元格，依次點擊“數(shù)據(jù)→數(shù)據(jù)驗證→設(shè)置”，在彈出的對話框中將“允許”設(shè)置為“序列”、“數(shù)據(jù)”選擇“介于”、“來源”選擇“=Sheet1！$A$2：$A$10”（即Sheet1中的員工工號），這樣在L3單元格中點擊下拉箭頭就可以方便地選擇某個工號的員工數(shù)據(jù)（圖7）。

繼續(xù)在M3單元格中輸入公式“=VLOOKUP（$L$3，$A$2：$J$27，COLUMN（B2），0）”，并向右填充公式到U3單元格。公式表示以L3單元格中的數(shù)據(jù)為基準(zhǔn)，在$A$2：$J$27區(qū)域中查找第2列的數(shù)據(jù)（即部門），由于嵌套了COLUMN函數(shù)，這樣向右填充公式后會依次變?yōu)椴檎业?列、 第4列……的數(shù)據(jù)。最后選中L3：U4區(qū)域并復(fù)制，然后定位到任意一個空白單元格（如L13單元格），依次點擊“開始→粘貼→其他粘貼選項→鏈接的圖片”。這樣在L3單元格中選擇不同的工號后，在L13單元格中就會自動生成對應(yīng)員工的工資單圖片，將該圖片發(fā)送給員工就可以核對工資了（圖8）。

6. 實際使用

因為這里的操作全部使用了數(shù)據(jù)引用，所以我們可以將其保存為模板使用。以后我們只要在Sheet1中輸入員工新的工資單數(shù)據(jù)，在Sheet3中就會自動完成表頭的添加。

如果表頭數(shù)據(jù)是不同的內(nèi)容（如需要制作不同產(chǎn)品內(nèi)容的標(biāo)簽），我們可以在模板文件中多準(zhǔn)備幾組工作表（每組都包含上述的三個工作表），再參照上述的方法使用函數(shù)進(jìn)行引用即可。