利用Excel自動制作“工資條”

黃小毛 劉清鈺

一、整理基礎(chǔ)數(shù)據(jù)

本文使用的工資數(shù)據(jù)模板如下所示。

第1行為標(biāo)題行，第2行為數(shù)據(jù)的頭行，以下為數(shù)據(jù)區(qū)域。

第A列至第D列依次為“員工編號”、“員工姓名”、“員工部門”和“員工崗位”，這四列顯示了員工的基本信息。

第E列至第I列為各項(xiàng)該名員工本月應(yīng)該發(fā)放的工資，依次為“基本工資”、“績效工資”、“獎金”、“補(bǔ)貼”和“加班工資”，然后第J列為這五項(xiàng)的匯總項(xiàng)，代表了本月該員工在未扣除相關(guān)項(xiàng)目時應(yīng)得的工資，以J2為例，J2=E2+F2+G2+H2+I2。

第K列和第N列為扣除項(xiàng)，依次為“五險(xiǎn)一金扣除”、“事、病假扣除”、“其他扣款”和所得稅扣款。其中“其他扣款”指的是不能分類進(jìn)其他扣除項(xiàng)目的項(xiàng)目，例如以前月份多扣工資的返還，如果是扣除項(xiàng)則以正數(shù)表示，如果是增加項(xiàng)則以負(fù)數(shù)表示。第O列是“實(shí)發(fā)工資”，表示該名員工本月應(yīng)該收到的錢，以O(shè)2為例，O2=J2-K2-L2-M2-N2。

二、補(bǔ)充基礎(chǔ)數(shù)據(jù)

由于需要使用Outlook郵箱來發(fā)送工資條，在原有數(shù)據(jù)的右邊增設(shè)“郵箱”一列，即P列，使用VLOOKUP函數(shù)在“聯(lián)系方式工作表”中按員工編號來查找“郵箱”，以P1為例，P1輸入函數(shù)VLOOKUP（A2，聯(lián)系方式!A2：E11，5，0）。

另外在R列插入標(biāo)題為“圖片文件名”一列，在此例中，郵件的標(biāo)題與生成的工資條圖片相同，故不額外填寫郵件標(biāo)題，以R1為例，輸入公式“A2&”-“&B2&”-“2020年1月工資明細(xì)”，命名格式為“員工編號-員工姓名-2020年1月工資明細(xì)”。

三、生成工資條

首先在數(shù)據(jù)工作表中插入一個“按鈕（窗體控件1）”，命名為“Step1：Make Pay Stub”，在VBA模塊中輸入以下代碼：

這串代碼首先會聲明一個名為“number1”的Integer型的變量，變量的值為當(dāng)前工資表包含A1單元格的區(qū)域A列最下方單元格的行數(shù)，在此例中為11（第1行的標(biāo)題和第2行到第11行的數(shù)據(jù)，之后再聲明一個名為“i”的Integer型變量，接著是一個用來定義“i”的For Next語句，“i”等于3到“number1”的值的2倍減3，間隔為2，這段語句的作用是將第1行的標(biāo)題復(fù)制粘貼到從第3行（數(shù)據(jù)的第2行）開始到最后一行之中每一行的上一行，原來的數(shù)據(jù)下移一行，最終生成工資條數(shù)據(jù)區(qū)域。

四、導(dǎo)出圖片

插入一個“按鈕（窗體控件）”，命名為“Step2：Make Picture”，在VBA模塊中輸入如下代碼：

這段代碼首先會聲明一個名為“path1”的String型變量，接著會彈出一個對話框。這里要求使用者輸入接下來保存工資條圖片的文件夾路徑，如果該文件夾不存在，將會新建一個，這里輸入的是“C:Users11796Desktop工資條”，意思是保存在桌面的名為“工資條”的文件夾。接下來定義一個名為“number2”的Integer型變量，它的含義與“Step1：Make Pay Stub”中“number1”的含義相同，但兩者屬于不同的宏，所以需要重新定義。

之后會彈出主題分別為“請輸入生成工資條起始列”和“請輸入生成工資條結(jié)束列”兩個對話框，可以根據(jù)需求來自主選擇，如果需要員工個人信息在里面就分別輸入“A”和“O”，如果只需要工資信息就輸入“E”和“O”，本例中輸入的是后者。

再后會彈出一個主題為“請選擇圖片名稱列”的彈窗，這里輸入圖片文件名這列“Q”。最后定義一個名為“k”的Integer型變量，加入一個For Next語句，“k”的值從1到“number2”的值-1，間隔為2，這段語句會依次將“E1:O2”、“E3:O4”以此類推到最后的單元格導(dǎo)出為圖片并保存至剛剛創(chuàng)建的“C:Users11796Desktop工資條”文件夾，并以Q列的值命名，命名規(guī)則為“員工編號-員工姓名-2020年1月工資明細(xì)”。

五、發(fā)送郵件

插入一個名為“Step3：Send Email”的按鈕（窗體控件），輸入如下代碼：

這段代碼首先會打開Outlook郵箱應(yīng)用，然后使用默認(rèn)的發(fā)件人新建一封郵件。接下來出現(xiàn)的四個對話框的主題分別為：“請輸入文件夾路徑”，“請輸入收件人列”，“請輸入主題列”，“請輸入圖片名稱列”，第一個對話框輸入“Step2：Make Picture”中用來保存工資條圖片的文件路徑，第二個對話框輸入郵箱列，在這里輸入“P”，郵件主題和圖片文件名是相同的，所以在第三、四對話框都輸入“Q”，接下來的For Next語句將會保存在“C:Users11796Desktop工資條”文件夾下的工資條圖片，以與工資條圖片同名的主題發(fā)送至一一對應(yīng)的郵箱。

六、小結(jié)

本例中使用Excel VBA功能在模塊中制作了三段代碼，分別實(shí)現(xiàn)了生成工資條、導(dǎo)出工資條圖片和發(fā)送郵件三項(xiàng)功能，適用于數(shù)據(jù)區(qū)域左上角為A1單元格，第1行為標(biāo)題的所有工資數(shù)據(jù)模板，缺陷是outlook郵箱在實(shí)際工作中使用得較少，第三步功能有時候無法完成，但是依然提高了效率和質(zhì)量，使原來復(fù)雜的工作簡單化。