excel函數(shù)在工資表中的應(yīng)用

excel函數(shù)在工資表中的應(yīng)用

隨著經(jīng)濟(jì)的不斷發(fā)展，會(huì)計(jì)行業(yè)的數(shù)量不斷增加，工作量也在增加。為減輕會(huì)計(jì)從業(yè)人員的負(fù)擔(dān),提高工作效率,通過兩例Excel函數(shù)實(shí)例,可以輕松解決日常問題。1工資條的設(shè)置使用Excel按人頭制作工資條通常有兩個(gè)解決方案,一個(gè)是利用Word的“郵件合并”功能,另一個(gè)是利用VBA(可視化基礎(chǔ)語(yǔ)言應(yīng)用程序)來實(shí)現(xiàn)。用“郵件合并”功能操作雖說不難,但數(shù)據(jù)過多時(shí)也很麻煩;用VBA處理起來倒是很方便,但幾十行的程序也夠初學(xué)者忙活的。經(jīng)過一番試驗(yàn),筆者找出了一個(gè)比較簡(jiǎn)單的方法,只需一個(gè)公式就可以按人頭打印出工資條來。新建一Excel文件,在sheet1中存放工資表的原始數(shù)據(jù),假設(shè)有M列。第一行是工資項(xiàng)目,從第二行開始是每個(gè)人的工資在sheet2中我們來設(shè)置工資條。根據(jù)實(shí)際情況,工資條由三行構(gòu)成,一行對(duì)應(yīng)工資項(xiàng)目,一行對(duì)應(yīng)一個(gè)人的工資數(shù)據(jù),然后是一個(gè)空行用來方便切割。這樣三行構(gòu)成一個(gè)工資條。工資項(xiàng)目處在行號(hào)除以3余數(shù)為1的行上;空行處在行號(hào)能整除3的行上。以上兩行不難設(shè)置,關(guān)鍵是工資數(shù)據(jù)行,牽扯到sheet1與sheet2中數(shù)據(jù)的對(duì)應(yīng),經(jīng)分析不難看出“sheetl中的數(shù)據(jù)行=INT((sheet2中的數(shù)據(jù)行+4)/3)”(圖1)。這樣我們?cè)趕heet2的A1單元格中輸入公式“=IF(MOD(ROW(),3)=0,",IF(MOD(ROW(),3)=1,Sheet1!A$1,INDEX(Sheet1!$A:$N,INT((ROW()+4)/3),COLUMN())))”。確認(rèn)后選擇A1單元格,把鼠標(biāo)放在A1單元格的右下角,鼠標(biāo)變成“+”時(shí),向右拖動(dòng)鼠標(biāo)自動(dòng)填充至M列,這樣工資條中的第一行就出來了。選定A1:M1,把鼠標(biāo)放在M1單元格的右下角,鼠標(biāo)再次變成“+”時(shí),向下拖動(dòng)鼠標(biāo)自動(dòng)填充到數(shù)據(jù)的最后一行,工資條就全部制作完成了。該公式運(yùn)用IF函數(shù),對(duì)MOD函數(shù)所取的引用行號(hào)與3的余數(shù)進(jìn)行判斷。如果余數(shù)為0,則產(chǎn)生一個(gè)空行;如果余數(shù)為1,則固定取sheet1中第一行的內(nèi)容;否則運(yùn)用INDEX函數(shù)和INT函數(shù)來取Sheet1對(duì)應(yīng)行上的數(shù)。最后來設(shè)置一下格式,選定A1:N2設(shè)上表格線,空行不設(shè)。然后選定A1:N3,拖動(dòng)N3的填充柄向下自動(dòng)填充,這樣有數(shù)據(jù)的有表格線,沒有數(shù)據(jù)的沒有表格線。最后調(diào)整一下頁(yè)邊距,千萬別把一個(gè)工資條打在兩頁(yè)上(圖2)。2利用int函數(shù)解決零頭不夠、殘余時(shí)的問題目前一些中小企業(yè)是用現(xiàn)金作為工資發(fā)放的主要形式,這給財(cái)務(wù)人員帶來了負(fù)擔(dān),特別是出現(xiàn)零頭不夠或多余時(shí)更是麻煩。如果此時(shí)能算出每個(gè)人各種面額人民幣的張數(shù),財(cái)務(wù)人員到銀行取錢時(shí)按實(shí)際支取,就不會(huì)出現(xiàn)零頭不夠或多余的問題了,巧用微軟的Excel軟件的INT函數(shù)可以輕松解決這個(gè)問題。如要完成下列表格的統(tǒng)計(jì)(見表)。2.1計(jì)算補(bǔ)貼的張數(shù)在C2中輸入=INT(B2/100)即可。INT函數(shù)是取整函數(shù),功能為取不大于參數(shù)中的最大整數(shù),對(duì)工資總額除以100(面額),再取整就可得出面額是100元的張數(shù),再拖動(dòng)填充柄(復(fù)制公式)即可算出其他人的張數(shù)。在C6中輸入=SUM(C2:C5)即可得出所有人面額是100元的總張數(shù)。2.2計(jì)算已發(fā)的額為100元的總錢額在D2中輸入=INT((B2-C2*100)/50)即可。其中C2*100為已發(fā)的面額是100元的總錢額,那么(B2-C2*100)是剩余未發(fā)的錢額,再除以50取整后就可得出面額是50元的張數(shù),再拖動(dòng)填充柄即可算出其他人的張數(shù)。在D6中輸入=SUM(D2:D5)即可得出所有人需面額是50元的張數(shù)。2.3填充柄計(jì)算張數(shù)的方法在E2中輸入=INT((B2-C2*100-D2*50)/20)即可。其中(B2-C2*100-D2*50)是剩余未發(fā)的錢額,再除以20取整后就可得出面額是20元的張數(shù),再拖動(dòng)填充柄即可算出其他人的張數(shù)。在E6中輸入=SUM(E2:E5)即可得出所有人需面額是20元的張數(shù)。2.4填充柄計(jì)算張數(shù)的方法在F2中輸入=INT((B2-C2*100-D2*50-E2*20)/10)即可。其中(B2-C2*100-D2*50-E2*20)是剩余未發(fā)的錢額,再除以10取整后就可得出面額是10元的張數(shù),再拖動(dòng)填充柄即可算出其他人的張數(shù)。在F6中輸入=SUM(F2:F5)即可得出所有人需面額是10元的張數(shù)。2.5計(jì)算每相線張數(shù)的計(jì)算在G2中輸入=INT((B2-C2*100-D2*50-E2*20-F2*10)/5)即可。其中(B2-C2*100-D2*50-E2*20-F2*10)是剩余未發(fā)的錢額,再除以5取整后就可得出面額是5元的張數(shù),再拖動(dòng)填充柄即可算出其他人的張數(shù)。在G6中輸入=SUM(G2:G5)即可得出所有人需面額是5元的張數(shù)。2.6關(guān)于填充柄在H2中輸入=INT((B2-C2*100-D2*50-E2*20-F2*10-G2*5)/2)即可。其中(B2-C2*100-D2*50-E2*20-F2*10-G2*5)是剩余未發(fā)的錢額,再除以2取整后就可得出面額是2元的張數(shù),再拖動(dòng)填充柄即可算出其他人的張數(shù)。在H6中輸入=SUM(H2:H5)即可得出所有人需面額是5元的張數(shù)。2.7計(jì)算目標(biāo)群的張數(shù)在I2中輸入=INT((B2-C2*100-D2*50-E2*20-F2*10-G2*5-H2*2)/1)即可。其中(B2-C2*100-D2*50-E2*20-F2*10-G2*5-H2*2)是剩余未發(fā)的錢額,再除以1取整后就可得出面額是