利用Ｅｘｃｅｌ控制貨幣補貼的發放

[摘 要] Excel除了能進行一般的格式處理以外，還具有強大的計算功能，公式與函數是Excel的精華之一#65377;本文利用Excel的上述功能實現對貨幣補貼發放的控制#65377;

[關鍵詞] Excel;格式;計算;公式與函數;貨幣補貼發放;控制

[中圖分類號]F232[文獻標識碼]A[文章編號]1673-0194(2007)07-0024-04

自1998年1月起，事業單位符合條件的職工可按規定享受貨幣補貼，期限為25年#65377;當單位職工人數較多，而每位職工的貨幣補貼到期日又不一致時，容易發生多發現象;另外，當想查詢單位或個人貨幣補貼的累計發放額時，又需對歷年數據進行匯總#65380;統計，容易出錯#65377;筆者利用Excel的格式處理及計算功能，實現對貨幣補貼發放的控制，操作簡便，準確可靠#65377;實現過程如下:

一#65380;建立表格框架

表格框架見圖1-1#65380;圖1-2#65380;圖1-3，職工貨幣補貼支取情況表分4部分:

第一部分為領取標準，為符合后面公式的要求，這部分需以“職稱”為關鍵詞作升序排列#65377;

第二部分為職工情況，包括工資號#65380;姓名#65380;到期時間#65380;開始領取時間及職稱#65377;

第三部分為職工的職稱變更情況，由于貨幣補貼的發放標準有8級，所以每位職工最多出現7次職稱變更，因此，表格分列了從“職稱變更1”到“職稱變更7”七欄，下級結構相同，均包括職稱名稱#65380;領取標準#65380;開始時間和已領取金額#65377;

第四部分為累計領取貨幣補貼金額#65377;

二#65380;設置格式及公式函數

除工資號#65380;姓名#65380;開始領取時的職稱名稱及時間#65380;職稱變更的時間外，其他均設計以公式和函數實現，其思路和實現方法如下:

1. 到期時間.年=開始時間.年+25，到期時間.月=開始時間.月-1，其中還要考慮當到期時間.月的結果為“0”時，如何轉換為前一年的12月，利用IF( )函數可實現:在單元格C16中輸入“=IF(I16-1=0，H16+25-1，H16+25)”，在單元格D16中輸入“=IF(I16-1=0，12，I16-1)”#65377;

2. 職工狀態分“已領取完畢”#65380;“本月為最后一期”#65380;“未到期”3種，通過到期時間與系統時間的對比，利用IF()#65380;YEAR( )#65380;MONTH( )#65380;NOW( )等函數實現:在單元格E16中輸入“=IF(YEAR(NOW( ))C16，“已領取完畢”，IF(MONTH(NOW( ))D16，“已領取完畢”，“本月為最后一期”))))”#65377;

3. 為更直觀地反映職工現時狀態，還可設置條件格式，使“已領取完畢”#65380;“本月為最后一期”#65380;“未到期”以不同的格式反映，實現如下:選擇單元格E16，打開菜單欄“格式—條件格式”功能，出現對話框，按圖2示例完成操作:

圖2

4. 由于職稱依次有“員級”#65380;“助級”#65380;“中級3年及以下”#65380;“中級4年及以上”#65380;“副高級3年及以下”#65380;“副高級4年及以上”#65380;“正高級3年及以下”及“正高級4年及以上”8個級別，因此，只要開始領取時的職稱確定了，以后的職稱變更順序就可定下來，具體實現如下:

(1)在單元格J16中輸入“=IF(F16=“員級”，“助級”，IF(F16=“助級”，“中級3年及以下”，IF(F16=“中級3年及以下”，“中級4年及以上”，IF(F16=“中級4年及以上”，“副高3年及以下”，IF(F16=“副高3年及以下”，“副高4年及以上”，IF(F16=“副高4年及以上”，“正高3年及以下”，IF(F16=“正高3年及以下”，“正高4年及以上”，0)))))))”#65377;

(2)在單元格O16中輸入“=IF(J16=“員級”，“助級”，IF(J16=“助級”，“中級3年及以下”，IF(J16=“中級3年及以下”，“中級4年及以上”，IF(J16=“中級4年及以上”，“副高3年及以下”，IF(J16=“副高3年及以下”，“副高4年及以上”，IF(J16=“副高4年及以上”，“正高3年及以下”，IF(J16=“正高3年及以下”，“正高4年及以上”，0)))))))”#65377;

(3)把單元格O16的公式復制到單元格T16#65380;Y16#65380;AD16#65380;AI16及AN16#65377;

5. 為盡量減少出錯，領取標準亦設計根據職稱名稱改變，利用LOOKUP函數，結合A3:E11區域所列標準實現如下:

(1)在G16中輸入“=IF(F16=0，0，LOOKUP(F16，$C$4:$C$11，$E$4:$E$11))”#65377;

(2)把單元格G16的公式復制到單元格K16#65380;P16#65380;U16#65380;Z16#65380;AE16#65380;AJ16#65380;AO16#65377;

6. 在每次職稱變更時，“已領取金額”欄均計算變更前該職工已領取的貨幣補貼總額，公式=變更前標準*該次變更與上次變更的時間段+上次變更前已領取的貨幣補貼，若職稱未發生變更，該欄將顯示當前該職工已領取的貨幣補貼總額，此時應考慮該職工的貨幣補貼已領取完畢的情況，具體公式如下:“

(1)在單元格N16中輸入“=IF(￥E16=“已領取完畢”，IF(L16>=H16，((L16-H16)*12+M16-I16)*G16，(($C16-H16)*12+$D16-I16+1)*G16)，IF(L16>=H16，((L16-H16)*12+M16-I16)*G16，((YEAR(NOW())-H16)*12+MONTH(NOW()-I16)*G16))”#65377;

(2)在單元格S16中輸入“=IF(￥E16=“已領取完畢”，IF(Q16>=L16，((Q16-L16)*12+R16-M16)*K16，(($C16-L16)*12+$D16-M16+1)*K16)，IF(Q16>=L16，((Q16-L16)*12+R16-M16)*K16，((YEAR(NOW())-L16)*12+MONTH(NOW())-M16)*K16))+N16”#65377;

(3)把單元格S16的公式復制到單元格X16#65380;AC16#65380;AH16#65380;AM16#65380;AR16#65377;

7. “累計領取貨幣補貼”欄的思路:當 “職稱變更情況7”欄為空時，該欄金額等于“職稱變更情況6”的“累計領取金額”; 當 “職稱變更情況7”欄不為空時，“累計領取貨幣補貼”欄金額等于“職稱變更情況6”的“累計領取金額”加上最后職稱變更后領取的貨幣補貼，此時需考慮職工的貨幣補貼已領取完畢的情況，具體實現如下:在單元格AS16中輸入“=IF(AR16-AM16=0，AR16，IF($E16=“已領取完畢”，(($C16-AP16)*12+$D16-AQ16+1)*AO16+AR16，((YEAR(NOW())-AP16)*12+MONTH(NOW())-AQ16)*AO16+AR16))”#65377;

8. 選中單元格A16:AS16，向下拖動填充柄，復制新記錄#65377;

9. 選取單元格AS24，點擊自動求和工具(∑)兩次，即可計算單位累計發放貨幣補貼#65377;

10. 選中單元格F16，打開菜單欄“窗口—凍結窗格”功能，使查詢更方便#65380;易看#65377;

三#65380;輸入基本數據，檢驗公式正確及合理性

輸入工資號，姓名，開始領取貨幣補貼時的職稱名稱#65380;年和月，以及變更職稱時的年和月(為方便檢驗，把系統時間調為2023年12月)，結果如圖3-1#65380;圖3-2#65380;圖3-3所示#65377;

經檢驗，表中結果與預期一致，正確合理#65377;

四#65380;后續性

為保證該表的后續性，可保留一行不輸入基本數據，見圖3-3單元格A24:AS24，當有新職工需加入該表時，可先插入空白行，然后把單元格A24:AS24復制到空白行，再輸入基本數據即可#65377;

五#65380;結 論

Excel具有強大的數據處理能力，善于利用Excel函數會給工作帶來很大的便利，收到事半功倍之效#65377;利用Excel控制貨幣補貼的發放，快捷#65380;準確，易于修改，非常實用#65377;