Ｅｘｃｅｌ函數(shù)在報(bào)銷單制作中的應(yīng)用

［摘 要］ 本文通過差旅費(fèi)報(bào)銷單制作的應(yīng)用實(shí)例，重點(diǎn)闡述Excel函數(shù)在差旅費(fèi)報(bào)銷單制作、數(shù)據(jù)單元格的有效性、函數(shù)公式編輯、格式優(yōu)化等方面的使用方法。

［關(guān)鍵詞］ 差旅費(fèi)報(bào)銷單制作；數(shù)據(jù)有效性；函數(shù)公式編輯；格式優(yōu)化

［中圖分類號(hào)］F232［文獻(xiàn)標(biāo)識(shí)碼］A［文章編號(hào)］1673-0194（2008）05-0016-04

會(huì)計(jì)電算化環(huán)境中，雖然專門的財(cái)務(wù)軟件在日常會(huì)計(jì)核算中起主導(dǎo)作用，但也有一定的局限性，財(cái)務(wù)處理系統(tǒng)以外的大量數(shù)據(jù)需要用其他軟件來處理。Excel數(shù)據(jù)處理軟件以其強(qiáng)大的數(shù)據(jù)處理功能成為辦公自動(dòng)化的重要工具，更是會(huì)計(jì)人員的必備工具，幾乎任何復(fù)雜的數(shù)據(jù)處理，用Excel都能完成。Excel內(nèi)置的函數(shù)功能十分豐富，所謂Excel函數(shù)就是Excel內(nèi)部預(yù)先定義的公式，用這些函數(shù)來處理會(huì)計(jì)數(shù)據(jù)，能極大地提高會(huì)計(jì)人員的工作效率和質(zhì)量。

目前在大、中型醫(yī)院，人員外出培訓(xùn)、進(jìn)修，參加學(xué)術(shù)會(huì)議，外出考察等活動(dòng)十分頻繁，填制差旅費(fèi)報(bào)銷單成為會(huì)計(jì)人員經(jīng)常性的工作，但采用手工方式下效率低、易出錯(cuò)、不夠規(guī)范，可運(yùn)用Excel函數(shù)自編差旅費(fèi)報(bào)銷單制作系統(tǒng)來解決這個(gè)問題。圖1是“數(shù)據(jù)錄入”工作表，圖2是“打印報(bào)銷單”工作表，只要在“數(shù)據(jù)錄入”工作表中錄入報(bào)銷項(xiàng)目，報(bào)銷單的填制與計(jì)算將在“打印報(bào)銷單”工作表中自動(dòng)生成。這些公式全部利用Excel函數(shù)來完成。本文共運(yùn)用10個(gè)函數(shù)，分別為IF、OR、SUM、ISTEXT、AND、N、INT、TEXT、TODAY、RIGHT。現(xiàn)分別說明公式的建立過程。

一、 數(shù)據(jù)錄入工作表的建立

差旅費(fèi)報(bào)銷單主要包括職工姓名、報(bào)銷日期、地點(diǎn)、事由、費(fèi)用種類、出差補(bǔ)貼及報(bào)銷金額等內(nèi)容。

啟動(dòng)Excel程序，新建工作簿并命名為“差旅費(fèi)報(bào)銷單”，然后將工作表Sheet 1命名為“數(shù)據(jù)錄入”，并在單元格內(nèi)依次輸入如圖1所示的項(xiàng)目。

（1）為了防止在輸入“事由”行的信息時(shí)出現(xiàn)錯(cuò)誤，對(duì)此項(xiàng)進(jìn)行有效性控制。合并單元格E4至G4，選擇【數(shù)據(jù)】—【有效性】菜單項(xiàng)，彈出【數(shù)據(jù)有效性】對(duì)話框，切換到【設(shè)置】選項(xiàng)卡中。在【有效條件】組合框中的【允許】下拉列表中選擇【序列】選項(xiàng)，然后在【來源】文本框中輸入“出差，學(xué)術(shù)會(huì)，國家級(jí)學(xué)術(shù)會(huì)，培訓(xùn)，進(jìn)修，外出學(xué)習(xí)，開會(huì)，探親，考察”。

切換到【輸入信息】選項(xiàng)卡中，在其中設(shè)置的內(nèi)容就是以后在選定單元格時(shí)出現(xiàn)的系統(tǒng)提示信息。選中【選定單元格時(shí)顯示輸入信息】復(fù)選框，然后在【輸入信息】文本框中輸入“選擇類別”。

同樣切換到【出錯(cuò)警告】選項(xiàng)卡中，在此選項(xiàng)卡中設(shè)置內(nèi)容的目的是如果輸入了錯(cuò)誤的信息系統(tǒng)則會(huì)發(fā)出“出現(xiàn)錯(cuò)誤信息”的警告。選中【輸入無效信息時(shí)顯示出錯(cuò)警告】復(fù)選框，然后在【輸入無效數(shù)據(jù)時(shí)顯示下列出錯(cuò)警告】組合中的【樣式】下拉列表中選擇【警告】選項(xiàng)，在【標(biāo)題】文本框中輸入“輸入錯(cuò)誤“，在【錯(cuò)誤信息】文本框中輸入“ 單擊下拉列表選擇”。

切換到【輸入法模式】選項(xiàng)卡中，然后在【模式】下拉列表中選擇“隨意”選項(xiàng)。單擊“確定”按鈕，則完成對(duì)“事由”的有效控制。

同樣，對(duì)“途中伙食補(bǔ)助費(fèi)”中的“每天標(biāo)準(zhǔn)”進(jìn)行有效性控制。本例中規(guī)定駐地縣境內(nèi)出差3元，在省地級(jí)市、行政轄區(qū)出差6元，到上述轄區(qū)外出差8元、深圳、珠海、廈門、汕頭和海南省14元。分別選定E11、F11、G11，按照“事由” 有效性控制設(shè)置的方法，在【設(shè)置】選項(xiàng)卡【來源】文本框中輸入“3，6，8，14” ，單擊“確定”按鈕，則完成對(duì)“事由”的有效控制。

同樣的道理，對(duì)E18進(jìn)行有效性控制，在【設(shè)置】選項(xiàng)卡【來源】文本框中輸入“50％，60％，1.5，1.8” ，單擊“確定”按鈕，則完成對(duì)E18的有效控制。

（2）為了使“事由”與“費(fèi)用種類”相一致，需要對(duì)“費(fèi)用種類”項(xiàng)目進(jìn)行Excel函數(shù)編輯。

合并單元格B14、B15，并在此單元格中輸入：“=IF（OR（E4=\"學(xué)術(shù)會(huì)\"，E4=\"國家級(jí)學(xué)術(shù)會(huì)\"，E4=\"開會(huì)\"），\"會(huì)務(wù)費(fèi)\"，IF（OR（E4=\"培訓(xùn)\"，E4=\"外出學(xué)習(xí)\"），\"培訓(xùn)費(fèi)\"，IF（E4=\"進(jìn)修\"，\"進(jìn)修費(fèi)\"，\"其他\"）））”。

合并單元格B17、B18，并在此單元格中輸入：“=IF（E4=\"進(jìn)修\"，\"進(jìn)修補(bǔ)助費(fèi)\"，IF（E4=\"外出學(xué)習(xí)\"，\"學(xué)習(xí)補(bǔ)助費(fèi)\"，IF（E4=\"培訓(xùn)\"，\"培訓(xùn)補(bǔ)助費(fèi)\"，\"夜間乘火車補(bǔ)助\"）））。在單元格D17輸入：“=IF（B17=\"夜間乘火車補(bǔ)助\"，\"票價(jià)\"，\"天數(shù)\"）；在單元格D18輸入：“=IF（B17=\"夜間乘火車補(bǔ)助\"，\"補(bǔ)助比例\"，\"每天標(biāo)準(zhǔn)\"）”。

（3）為了能自動(dòng)計(jì)算車票票據(jù)張數(shù)，在E19輸入：“=IF（COUNT（H:H）=0，\"\"，COUNT（H:H））”，自動(dòng)計(jì)算車票金額，在E20I輸入：“=IF（SUM（H:H）=0，\"\"，SUM（H:H））”。

為了核對(duì)車票金額是否輸入準(zhǔn)確，在J1輸入：“=IF（SUM（I:I）=E20，\"√\"，\"×\"）”，在J2輸入：“=IF（OR（H2=I2，I2=\"\"），\"\"，\"×\"）”，并將J2復(fù)制到J3至J100列。

這樣就完成了“數(shù)據(jù)錄入”單元格的設(shè)置。

二、 打印報(bào)銷單的建立

完成了“數(shù)據(jù)錄入”單元格的設(shè)置，并在所對(duì)應(yīng)的項(xiàng)目中輸入差旅費(fèi)各項(xiàng)，希望在“打印報(bào)銷單”工作表中全部自動(dòng)填充并自動(dòng)計(jì)算，這就需要對(duì)“打印報(bào)銷單”進(jìn)行設(shè)置。將工作表Sheet 2命名為“打印報(bào)銷單”，并在單元格內(nèi)依次輸入如圖2所示的項(xiàng)目。

為了使“填單日期”在任何時(shí)候打開工作表時(shí)，顯示的永遠(yuǎn)都是當(dāng)前日期，需要對(duì)單元格進(jìn)行設(shè)置。合并單元格L2至N2，并輸入：“=TODAY（）”。

合并E3至H3，并輸入：“=IF（ISTEXT（數(shù)據(jù)錄入!E2），數(shù)據(jù)錄入!E2，\"\"）”。

合并K3至O3，并輸入：“=IF（ISTEXT（數(shù)據(jù)錄入!E3），數(shù)據(jù)錄入!E3，\"\"）”。

合并R3至S3，并輸入：“=IF（ISTEXT（數(shù)據(jù)錄入!E4），數(shù)據(jù)錄入!E4，\"\"）”。

合并I4至K4，并輸入：“=數(shù)據(jù)錄入!B17”。

合并N4至O4，并輸入：“=數(shù)據(jù)錄入!B19”。

合并P4至Q4，并輸入：“=數(shù)據(jù)錄入!B14”。

在單元格A7輸入：“=IF（數(shù)據(jù)錄入!E5=0，\"\"，數(shù)據(jù)錄入!E5）”。

在單元格A7輸入：“=IF（數(shù)據(jù)錄入!E5=0，\"\"，數(shù)據(jù)錄入!E5）”。

在單元格B7輸入：“=IF（數(shù)據(jù)錄入!E6=0，\"\"，數(shù)據(jù)錄入!E6）”。

在單元格C7輸入：“=IF（數(shù)據(jù)錄入!E7=0，\"\"，數(shù)據(jù)錄入!E7）”。

在單元格D7輸入：“=IF（數(shù)據(jù)錄入!E8=0，\"\"，數(shù)據(jù)錄入!E8）”。

在單元格E7輸入：“=IF（數(shù)據(jù)錄入!E9=0，\"\"，數(shù)據(jù)錄入!E9）”。

在單元格F7輸入：“=IF（數(shù)據(jù)錄入!E10=0，\"\"，數(shù)據(jù)錄入!E10）”。

在單元格G7輸入：“=IF（數(shù)據(jù)錄入!E11=0，\"\"，數(shù)據(jù)錄入!E11）”。

在單元格H7輸入：“=IF（F7=\"\"，\"\"，F(xiàn)7*G7）”。

在單元格I7輸入：“=IF（數(shù)據(jù)錄入!E17=0，\"\"，數(shù)據(jù)錄入!E17）”。

在單元格J7輸入：“=IF（數(shù)據(jù)錄入!E18=0，\"\"，數(shù)據(jù)錄入!E18）”。

在單元格K7輸入：“=IF（I7=\"\"，\"\"，I7*J7）”。

在單元格L7輸入：“=IF（數(shù)據(jù)錄入!E12=0，\"\"，數(shù)據(jù)錄入!E12）”。

在單元格M7輸入：“=IF（數(shù)據(jù)錄入!E13=0，\"\"，數(shù)據(jù)錄入!E13）”。

在單元格N7輸入：“=IF（數(shù)據(jù)錄入!E19=0，\"\"，數(shù)據(jù)錄入!E19）”。

在單元格O7輸入：“=IF（數(shù)據(jù)錄入!E20=0，\"\"，數(shù)據(jù)錄入!E20）”

在單元格P7輸入：“=IF（數(shù)據(jù)錄入!E14=0，\"\"，數(shù)據(jù)錄入!E14）”。

在單元格Q7輸入：“=IF（數(shù)據(jù)錄入!E15=0，\"\"，數(shù)據(jù)錄入!E15）”。

在單元格E10輸入：“=IF（SUM（E7:E9）=0，\"\"，SUM（E7:E9））”。

在單元格F10輸入：“=IF（SUM（F7:F9）=0，\"\"，SUM（F7:F9））”。

在單元格H10輸入：“=IF（SUM（H7:H9）=0，\"\"，SUM（H7:H9））”。

在單元格I10輸入：“=IF（SUM（I7:I9）=0，\"\"，SUM（I7:I9））”。

在單元格K10輸入：“=IF（SUM（K7:K9）=0，\"\"，SUM（K7:K9））”。

在單元格L10輸入：“=IF（SUM（L7:L9）=0，\"\"，SUM（L7:L9））”。

在單元格M10輸入：“=IF（SUM（M7:M9）=0，\"\"，SUM（M7:M9））”。

在單元格N10輸入：“=IF（SUM（N7:N9）=0，\"\"，SUM（N7:N9））”。

在單元格O10輸入：“=IF（SUM（O7:O9）=0，\"\"，SUM（O7:O9））”。

在單元格P10輸入：“=IF（SUM（P7:P9）=0，\"\"，SUM（P7:P9））”。

在單元格Q10輸入：“=IF（SUM（Q7:Q9）=0，\"\"，SUM（Q7:Q9））”。

在單元格R10輸入：“=IF（AND（H10=\"\"，K10=\"\"，M10=\"\"，O10=\"\"，Q10=\"\"），\"\"，N（H10）+N（K10）+N（M10）+N（O10）+N（Q10））”。

在單元格S10輸入：“=IF（AND（L10=\"\"，N10=\"\"，P10=\"\"），\"\"，N（L10）+N（N10）+N（P10））”。

合并R11至S11輸入：“=IF（R10=\"\"，0，R10）”。

合并E12至S12輸入：“=IF（數(shù)據(jù)錄入!C16=\"\"，\"\"，數(shù)據(jù)錄入!C16）”。

為了使小寫合計(jì)金額轉(zhuǎn)換為大寫合計(jì)金額，要利用Excel函數(shù)對(duì)單元格進(jìn)行公式設(shè)置。

合并單元格H11至P11，輸入：“=IF（（R11-INT（R11））=0，TEXT（R11，\"[DBNUM2]\"）\"元整\"，IF（INT（R11*10）-R11*10=0，TEXT（INT（R11），\"[DBNUM2]\"）\"元\"TEXT（（INT（R11*10）-INT（R11）*10），\"[DBNUM2]\"）\"角整\"，TEXT（INT（R11），\"[DBNUM2]\"）\"元\"IF（INT（R11*10）-INT（R11）*10=0，\"零\"，TEXT（INT（R11*10）-INT（R11）*10，\"[DBNUM2]\"）\"角\"）TEXT（RIGHT（R11，1），\"[dbnum2]\"）\"分\"））”。

從整體來講，這是一個(gè)多層嵌套條件公式，共有4層嵌套來完成條件判斷，根據(jù)貨幣金額的不同情況生成不同格式的中文大寫。可以將整個(gè)條件函數(shù)公式表述為：

如果單元格R11中數(shù)字為整數(shù)（假如為35 566），那么人民幣大寫就是“叁萬伍仟伍佰陸拾陸元整”；

如果單元格R11中數(shù)字為含有一位小數(shù)數(shù)字（假如35 566.2），那么人民幣大寫就是“叁萬伍仟伍佰陸拾陸元貳角整”；

如果單元格R11中數(shù)字為含有兩位小數(shù)數(shù)字（假如35 566.08），且十分位為0，那么人民幣大寫就是“叁萬伍仟伍佰陸拾陸元零捌分”；

如果單元格R11中數(shù)字為含有兩位小數(shù)數(shù)字（假如35 566.28），且十分位不為0，那么人民幣大寫就是“叁萬伍仟伍佰陸拾陸元貳角捌分”。

為了避免不小心移動(dòng)或破壞工作表及函數(shù)公式，應(yīng)對(duì)工作表進(jìn)行保護(hù)。

分別兩次選定“數(shù)據(jù)錄入”，“打印報(bào)銷單”工作表，單擊【格式】菜單的【單元格】，在【單元格】對(duì)話框“保護(hù)”選項(xiàng)卡中勾選“鎖定”，“隱藏”，單擊【確定】。

同時(shí)，在“打印報(bào)銷單”工作表中，單擊【工具】菜單的【保護(hù)】子菜單的【保護(hù)工作表】，在【保護(hù)工作表】對(duì)話框中輸入密碼，單擊【確定】。

在“數(shù)據(jù)錄入”工作表中，選定需要開放的單元格區(qū)域（如“錄入項(xiàng)目”中的E18以上單元格，“車票金額”和 “核對(duì)”以下單元格），單擊【格式】菜單的【單元格】，在【單元格】對(duì)話框“保護(hù)”選項(xiàng)卡中取消勾選的“鎖定”，“隱藏”，單擊【確定】。然后單擊【工具】菜單的【保護(hù)】子菜單的【保護(hù)工作表】，在【保護(hù)工作表】對(duì)話框中輸入密碼，單擊【確定】。

以上用Excel自編的報(bào)銷單制作系統(tǒng)，雖然函數(shù)較多，但遠(yuǎn)沒有編程復(fù)雜，且簡(jiǎn)便易行，自動(dòng)化程度很高，不失為會(huì)計(jì)工作人員的實(shí)用工具。