Ｅｘｃｅｌ函數在報銷單制作中的應用

［摘 要］ 本文通過差旅費報銷單制作的應用實例，重點闡述Excel函數在差旅費報銷單制作、數據單元格的有效性、函數公式編輯、格式優化等方面的使用方法。

［關鍵詞］ 差旅費報銷單制作；數據有效性；函數公式編輯；格式優化

［中圖分類號］F232［文獻標識碼］A［文章編號］1673-0194（2008）05-0016-04

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

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

一、 數據錄入工作表的建立

差旅費報銷單主要包括職工姓名、報銷日期、地點、事由、費用種類、出差補貼及報銷金額等內容。

啟動Excel程序，新建工作簿并命名為“差旅費報銷單”，然后將工作表Sheet 1命名為“數據錄入”，并在單元格內依次輸入如圖1所示的項目。

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

切換到【輸入信息】選項卡中，在其中設置的內容就是以后在選定單元格時出現的系統提示信息。選中【選定單元格時顯示輸入信息】復選框，然后在【輸入信息】文本框中輸入“選擇類別”。

同樣切換到【出錯警告】選項卡中，在此選項卡中設置內容的目的是如果輸入了錯誤的信息系統則會發出“出現錯誤信息”的警告。選中【輸入無效信息時顯示出錯警告】復選框，然后在【輸入無效數據時顯示下列出錯警告】組合中的【樣式】下拉列表中選擇【警告】選項，在【標題】文本框中輸入“輸入錯誤“，在【錯誤信息】文本框中輸入“ 單擊下拉列表選擇”。

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

同樣，對“途中伙食補助費”中的“每天標準”進行有效性控制。本例中規定駐地縣境內出差3元，在省地級市、行政轄區出差6元，到上述轄區外出差8元、深圳、珠海、廈門、汕頭和海南省14元。分別選定E11、F11、G11，按照“事由” 有效性控制設置的方法，在【設置】選項卡【來源】文本框中輸入“3，6，8，14” ，單擊“確定”按鈕，則完成對“事由”的有效控制。

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

（2）為了使“事由”與“費用種類”相一致，需要對“費用種類”項目進行Excel函數編輯。

合并單元格B14、B15，并在此單元格中輸入：“=IF（OR（E4=\"學術會\"，E4=\"國家級學術會\"，E4=\"開會\"），\"會務費\"，IF（OR（E4=\"培訓\"，E4=\"外出學習\"），\"培訓費\"，IF（E4=\"進修\"，\"進修費\"，\"其他\"）））”。

合并單元格B17、B18，并在此單元格中輸入：“=IF（E4=\"進修\"，\"進修補助費\"，IF（E4=\"外出學習\"，\"學習補助費\"，IF（E4=\"培訓\"，\"培訓補助費\"，\"夜間乘火車補助\"）））。在單元格D17輸入：“=IF（B17=\"夜間乘火車補助\"，\"票價\"，\"天數\"）；在單元格D18輸入：“=IF（B17=\"夜間乘火車補助\"，\"補助比例\"，\"每天標準\"）”。

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

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

這樣就完成了“數據錄入”單元格的設置。

二、 打印報銷單的建立

完成了“數據錄入”單元格的設置，并在所對應的項目中輸入差旅費各項，希望在“打印報銷單”工作表中全部自動填充并自動計算，這就需要對“打印報銷單”進行設置。將工作表Sheet 2命名為“打印報銷單”，并在單元格內依次輸入如圖2所示的項目。

為了使“填單日期”在任何時候打開工作表時，顯示的永遠都是當前日期，需要對單元格進行設置。合并單元格L2至N2，并輸入：“=TODAY（）”。

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

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

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

合并I4至K4，并輸入：“=數據錄入!B17”。

合并N4至O4，并輸入：“=數據錄入!B19”。

合并P4至Q4，并輸入：“=數據錄入!B14”。

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

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

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

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

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

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

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

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

在單元格H7輸入：“=IF（F7=\"\"，\"\"，F7*G7）”。

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

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

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

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

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

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

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

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

在單元格Q7輸入：“=IF（數據錄入!E15=0，\"\"，數據錄入!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（數據錄入!C16=\"\"，\"\"，數據錄入!C16）”。

為了使小寫合計金額轉換為大寫合計金額，要利用Excel函數對單元格進行公式設置。

合并單元格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]\"）\"分\"））”。

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

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

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

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

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

為了避免不小心移動或破壞工作表及函數公式，應對工作表進行保護。

分別兩次選定“數據錄入”，“打印報銷單”工作表，單擊【格式】菜單的【單元格】，在【單元格】對話框“保護”選項卡中勾選“鎖定”，“隱藏”，單擊【確定】。

同時，在“打印報銷單”工作表中，單擊【工具】菜單的【保護】子菜單的【保護工作表】，在【保護工作表】對話框中輸入密碼，單擊【確定】。

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

以上用Excel自編的報銷單制作系統，雖然函數較多，但遠沒有編程復雜，且簡便易行，自動化程度很高，不失為會計工作人員的實用工具。