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

[關(guān)鍵詞] Excel;格式;計算;公式與函數(shù);貨幣補貼發(fā)放;控制

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

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

一#65380;建立表格框架

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

第一部分為領(lǐng)取標(biāo)準(zhǔn)，為符合后面公式的要求，這部分需以“職稱”為關(guān)鍵詞作升序排列#65377;

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

第三部分為職工的職稱變更情況，由于貨幣補貼的發(fā)放標(biāo)準(zhǔn)有8級，所以每位職工最多出現(xiàn)7次職稱變更，因此，表格分列了從“職稱變更1”到“職稱變更7”七欄，下級結(jié)構(gòu)相同，均包括職稱名稱#65380;領(lǐng)取標(biāo)準(zhǔn)#65380;開始時間和已領(lǐng)取金額#65377;

第四部分為累計領(lǐng)取貨幣補貼金額#65377;

二#65380;設(shè)置格式及公式函數(shù)

除工資號#65380;姓名#65380;開始領(lǐng)取時的職稱名稱及時間#65380;職稱變更的時間外，其他均設(shè)計以公式和函數(shù)實現(xiàn)，其思路和實現(xiàn)方法如下:

1. 到期時間.年=開始時間.年+25，到期時間.月=開始時間.月-1，其中還要考慮當(dāng)?shù)狡跁r間.月的結(jié)果為“0”時，如何轉(zhuǎn)換為前一年的12月，利用IF( )函數(shù)可實現(xiàn):在單元格C16中輸入“=IF(I16-1=0，H16+25-1，H16+25)”，在單元格D16中輸入“=IF(I16-1=0，12，I16-1)”#65377;

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

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

圖2

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

(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的公式復(fù)制到單元格T16#65380;Y16#65380;AD16#65380;AI16及AN16#65377;

5. 為盡量減少出錯，領(lǐng)取標(biāo)準(zhǔn)亦設(shè)計根據(jù)職稱名稱改變，利用LOOKUP函數(shù)，結(jié)合A3:E11區(qū)域所列標(biāo)準(zhǔn)實現(xiàn)如下:

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

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

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

(1)在單元格N16中輸入“=IF(￥E16=“已領(lǐng)取完畢”，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=“已領(lǐng)取完畢”，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的公式復(fù)制到單元格X16#65380;AC16#65380;AH16#65380;AM16#65380;AR16#65377;

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

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

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

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

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

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

經(jīng)檢驗，表中結(jié)果與預(yù)期一致，正確合理#65377;

四#65380;后續(xù)性

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

五#65380;結(jié) 論

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