田偉福
摘要:本文使用Excel設計了一個銷售人員工資管理系統。在錄入"員工編號"、"產品編號"和"銷售地點"等數據時,設置了數據有效性檢查,以下拉列表的方式供用戶選擇合適數據項,如果輸入的數據超出允許范圍,系統會給出錯誤提示信息;各個銷售人員的銷售金額、提成方式和工資都使用公式自動生成。
關鍵字:數據有效性,VLOOKUP,SUMIF,DATEDIF
企業銷售人員的工資一般都由"基本工資+銷售提成"構成,且隨著銷售額的梯段增長,提成百分比也會相應提高,企業對不同工作年限的銷售人員,業績考核方式也有所不同。因此,企業銷售人員工資的計算涉及大量的數據處理工作,如果手工計算,不但效率低,而且十分容易出錯。
為了解決上述問題,使用Excel設計一個銷售人員工資管理系統,通過函數和公式自動求出銷售人員的工資金額,并且當提成計算方式發生改變時,更改會即時反映到最終計算結果中。
1.制作基本資料表格
2.制作銷售記錄表
銷售記錄表用于記錄企業銷售人員售賣產品的情況。
1)制作表格,如圖4所示。
2)"員工編號"列的數據有效性設置
打開"員工登記表",為"員工編號"列定義名稱:選中"員工編號"列,使用"插入/名稱/定義",輸入名稱為"員工編號"。
在"6月銷售記錄表"中, 選中"員工編號"列,使用"數據/有效性"命令,將"允許"條件更改成"序列",并在"來源"框中輸入:=員工編號。在"數據有效性"對話框中,選擇"輸入信息"選項卡,在"標題"欄中輸入"注意:",在"輸入信息"欄中輸入"請填寫與'員工登記表'一致的編號!",選擇"出錯警告"選項卡,在"標題"欄中輸入"出錯了!",在"錯誤信息"欄中輸入"您輸入的員工編號與員工登記表的員工編號不符,請重新輸入!"。
今后可利用下拉列表來選擇"員工編號",若輸入的員工編號與 "員工登記表"中的"員工編號"不相符,會立即報錯。
3) 實現"姓名"列的自動填充
在B2單元格輸入公式:=IF(A2="","",VLOOKUP(A2,員工登記表!$A:$B,2,FALSE)),填充至其它行。
4) "銷售地點"列的下拉列表選擇輸入
選中D列,使用"數據/有效性"命令,將 "允許"條件更改成"序列",在"來源"框中輸入"太平洋電腦城,海印電腦城,頤高數碼廣場","確定",就可利用下拉列表來選擇"銷售地點"。
5)"產品編號"列的數據有效性設置
將"產品信息表"的"產品編號"列的數據定義名稱為"產品編號";在"6月銷售記錄表"中, 選中"產品編號"列,使用"數據/有效性"命令,將 "允許"條件更改成"序列",并在"來源"框中輸入:=產品編號;在 "輸入信息"選項卡和"出錯警告"選項卡,輸入提示信息。
6) "產品名稱"列的自動填充
在F2單元格輸入公式:=IF(E2="","",VLOOKUP(E2,產品信息表!$A:$B,2,FALSE)),并填充至其它行。
7) "產品單價"列的自動填充
在G2單元格輸入公式:=IF(E2="","",VLOOKUP(E2,產品信息表!$A:$C,3,FALSE)),并填充至其它行。
8) "銷售金額"列的自動填充
在I2單元格輸入公式:=IF(H2="","",G2*H2),并填充至其它行。
3.制作工資統計表
工資統計表統計各個銷售人員的銷售金額,求得該金額對應的提成率,通過公式計算工資金額。制作工資統計表,如圖5所示。
1) 統計銷售金額
在C2單元格輸入公式:=IF(A2="","",SUMIF('6月銷售記錄表'!A:A,A2,'6月銷售記錄表'!I:I)),填充至其它行。
2) 填寫提成率
先將"提成計算方法"表的A3:B9 與 D3:E8這兩個區域分別定義名稱為"table1"和"table2";在D2單元格輸入公式:=IF(C2="","",VLOOKUP(C2,IF(VLOOKUP(A2,員工登記表!A:D,4,FALSE)<3,table1,table2),2,TRUE)),填充至其它行。
3) 計算提成金額
在E2單元格輸入公式:=IF(D2="","",C2*D2) ,并填充至其它行。
4) 計算應發工資
在F2單元格輸入公式:=IF(B2="","",B2+E2),填充至其它行。
本系統簡單易用,不需要額外投入,就能對銷售人員的工資進行高效管理,非常適用于小微企業。