利用Ｅｘｃｅｌ控制貨幣補(bǔ)貼的發(fā)放

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

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

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

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

一#65380;建立表格框架

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

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

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

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

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

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

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

1. 到期時(shí)間.年=開始時(shí)間.年+25，到期時(shí)間.月=開始時(shí)間.月-1，其中還要考慮當(dāng)?shù)狡跁r(shí)間.月的結(jié)果為“0”時(shí)，如何轉(zhuǎn)換為前一年的12月，利用IF( )函數(shù)可實(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種，通過(guò)到期時(shí)間與系統(tǒng)時(shí)間的對(duì)比，利用IF()#65380;YEAR( )#65380;MONTH( )#65380;NOW( )等函數(shù)實(shí)現(xiàn):在單元格E16中輸入“=IF(YEAR(NOW( ))C16，“已領(lǐng)取完畢”，IF(MONTH(NOW( ))D16，“已領(lǐng)取完畢”，“本月為最后一期”))))”#65377;

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

圖2

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

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

(2)在單元格O16中輸入“=IF(J16=“員級(jí)”，“助級(jí)”，IF(J16=“助級(jí)”，“中級(jí)3年及以下”，IF(J16=“中級(jí)3年及以下”，“中級(jí)4年及以上”，IF(J16=“中級(jí)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. 為盡量減少出錯(cuò)，領(lǐng)取標(biāo)準(zhǔn)亦設(shè)計(jì)根據(jù)職稱名稱改變，利用LOOKUP函數(shù)，結(jié)合A3:E11區(qū)域所列標(biāo)準(zhǔn)實(shí)現(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. 在每次職稱變更時(shí)，“已領(lǐng)取金額”欄均計(jì)算變更前該職工已領(lǐng)取的貨幣補(bǔ)貼總額，公式=變更前標(biāo)準(zhǔn)*該次變更與上次變更的時(shí)間段+上次變更前已領(lǐng)取的貨幣補(bǔ)貼，若職稱未發(fā)生變更，該欄將顯示當(dāng)前該職工已領(lǐng)取的貨幣補(bǔ)貼總額，此時(shí)應(yīng)考慮該職工的貨幣補(bǔ)貼已領(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. “累計(jì)領(lǐng)取貨幣補(bǔ)貼”欄的思路:當(dāng) “職稱變更情況7”欄為空時(shí)，該欄金額等于“職稱變更情況6”的“累計(jì)領(lǐng)取金額”; 當(dāng) “職稱變更情況7”欄不為空時(shí)，“累計(jì)領(lǐng)取貨幣補(bǔ)貼”欄金額等于“職稱變更情況6”的“累計(jì)領(lǐng)取金額”加上最后職稱變更后領(lǐng)取的貨幣補(bǔ)貼，此時(shí)需考慮職工的貨幣補(bǔ)貼已領(lǐng)取完畢的情況，具體實(shí)現(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，向下拖動(dòng)填充柄，復(fù)制新記錄#65377;

9. 選取單元格AS24，點(diǎn)擊自動(dòng)求和工具(∑)兩次，即可計(jì)算單位累計(jì)發(fā)放貨幣補(bǔ)貼#65377;

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

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

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

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

四#65380;后續(xù)性

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

五#65380;結(jié) 論

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