項目二日記賬管理系統(tǒng)的建立

1、項目二日記賬管理系統(tǒng)的建立工作目標(biāo)設(shè)置和登記賬簿，是企業(yè)會計核算處理流程的一項核心工作， 是企業(yè)會計處理的一個重要環(huán)節(jié)。會計賬簿是由具有專門格式、 相互聯(lián)系的賬頁組成， 按照一定的程序排列或裝訂在一起， 用以序時、分類、連續(xù)地記錄和反映企業(yè)的各項經(jīng)濟業(yè)務(wù)的發(fā)生和完成情況的簿記。 我們將賬務(wù)管理系統(tǒng)分為日記賬和分類賬兩部分，本項目的主要內(nèi)容就是介紹利用 Excel 進行日記賬管理的方法和步驟。通過完成本項目的五個工作任務(wù)， 學(xué)習(xí)者能夠進行日記賬系統(tǒng)工作簿的制作； 并從中學(xué)習(xí)掌握Excel 中公式、函數(shù)及宏的基本操作方法。工作思路本項目的工作思路是首先建立日記賬管理系統(tǒng)工作簿，完成該系統(tǒng)的首頁頁面

2、的設(shè)計；制作或引用日記賬賬務(wù)處理數(shù)據(jù)源；創(chuàng)建現(xiàn)金日記賬；創(chuàng)建現(xiàn)金日報表；創(chuàng)建銀行存款日記賬； 創(chuàng)建銀行存款余額調(diào)節(jié)表。項目二工作思路如圖所示。創(chuàng)建日記賬輸入創(chuàng)建現(xiàn)金創(chuàng)建銀行輸入結(jié)賬結(jié)賬創(chuàng)建現(xiàn)金創(chuàng)建銀行存背景資料 資料 1 某公司是家商業(yè)銷售企業(yè)， 2008 年 1 月初“庫存現(xiàn)金”的余額為 2 500 元，“銀行存款”的余額為 168 900 元。資料 22008 年 1 月發(fā)生與現(xiàn)金有關(guān)的業(yè)務(wù)如下：(1)1月 3日，收到銷售商品的營業(yè)款35 000 元。(2)1月 3日，業(yè)務(wù)員張某報銷通訊費300 元。(3)1月 3日，退還供應(yīng)商 A 的商品保證金 20 000 元。(4)1月 3日，收到賒

3、銷款 25 000元。(5)1月 3日，從銀行提取現(xiàn)金 50000元，用于發(fā)放工資。(6)1月 3 日，發(fā)放工資 50 000 元。(7)1月 3 口，將庫存現(xiàn)金 40 000元存人銀行。(8)1月 3 日，收到李某個人還款500 元。(9)1月 4 日，業(yè)務(wù)員王某報銷差旅費l 200 元。(10)1月 4 日，收到銷售商品的營業(yè)款20 000 元。資料32008年 1 月發(fā)生與銀行存款有關(guān)的業(yè)務(wù)如下：(1)1月 3 日，從銀行提取現(xiàn)金 50 000元( 現(xiàn)金支票 9558#) ，用于發(fā)放工資。(2)1月 3 日，將庫存現(xiàn)金 40 000元存人銀行。(3)1月 6日，收到銀行轉(zhuǎn)來的資金匯劃補充

4、憑證，金額200 000 元，為購貨單位電匯來的貨款。(4)1月 7 日，向外地供貨單位電匯商品及增值稅款8l 900 元。(5)1月 10 日，購買銀行轉(zhuǎn)賬支票等結(jié)算憑證，支付200 元。(6)1月 15 日，開出轉(zhuǎn)賬支票 (6370#) ，將 46 200元轉(zhuǎn)賬用于補繳稅款。(7)1月 20 日，開出現(xiàn)金支票 (9559#) 一張，提取現(xiàn)金 5 000 元備用。(8)1月 21 日，擬到外地采購特向銀行申請簽發(fā)銀行匯票100 000 元，銀行給予簽發(fā) ( 銀行匯票 743#) 并收取全額保證金。(9)1月 23 日，因生產(chǎn)經(jīng)營需要向銀行貸款 500 000 元，根據(jù)借款合同，銀行將款項劃轉(zhuǎn)

5、到賬。(10)1月 28 日，收到銀行特種委托收款的付款通知(7543#) 及電信收費單據(jù)，支付電話費 5 340元。資料42008年 2 月 3 日收到銀行發(fā)來的 1 月份銀行對賬單，如表所示。XX銀行對賬單客戶名稱：2008年 1 月月日摘要借方貸方余額13上月結(jié)存13現(xiàn)支 9558#13現(xiàn)繳16匯劃17電匯110結(jié)算憑證116轉(zhuǎn)支 6370#120現(xiàn)支 9559#121銀匯 6743#123貸款128特委 7543#131匯劃進賬1 31 特委 8742#1 31 月末余額工作準(zhǔn)備首先建立一個名為“班級 姓名 項目二 .xls ”的工作簿，在此工作簿中建立“頁面” 、“現(xiàn)金日記賬”、“現(xiàn)

6、金日報表”、“銀行存款日記賬” 、“銀行存款余額調(diào)節(jié)表” 、“銀行存款類別定義” 、“現(xiàn)金類別定義” 等工作表，各工作表分別完成相應(yīng)的工作任務(wù)。 下面分別介紹各工作表的設(shè)計方法和步驟。工作任務(wù)一頁面的設(shè)計給項目二日記賬管理工作簿增加“頁面” ，我們可以將項目一中的會計憑證系統(tǒng)的首頁進行復(fù)制，并進行相應(yīng)的更改，這樣能減少一些工作量，具體步驟如下：(1) 打開“ 項目一”工 作簿，單擊“頁面”工作表。(2) 將鼠標(biāo)指針移至工作表標(biāo)簽處，點擊鼠標(biāo)右鍵，選擇“移動或復(fù)制工作表”命令。點擊【確定】，則整張“頁面”工作表就復(fù)制到“項目二”工作簿中。(3) 修改藝術(shù)字并調(diào)整位置（4）建立超鏈接工作任務(wù)二現(xiàn)金

7、日記賬的創(chuàng)建現(xiàn)金日記賬是由出納人員根據(jù)審核后的現(xiàn)金收、付款記賬憑證和銀行付款憑證，逐日逐筆按順序登記和反映現(xiàn)金增減變動情況的一種特種日記賬。每筆業(yè)務(wù)發(fā)生后， 需要計算余額； 在每日收付款項逐筆登記完畢后， 應(yīng)分別計算現(xiàn)金收入和現(xiàn)金支出的合計數(shù)及賬面余額，并與庫存現(xiàn)金實存數(shù)相核對，以此檢查每日現(xiàn)金收支和結(jié)存的情況，最終形成現(xiàn)金日記賬簿。下面介紹現(xiàn)金日記賬的設(shè)置方法。項目活動一現(xiàn)金日記賬的結(jié)構(gòu)設(shè)計設(shè)計現(xiàn)金日記賬工作表格式的具體步驟如下：(1) 打開工作簿“項目二” ，選擇工作表“現(xiàn)金日記賬” 。(2) 在單元格 B2 中輸入“現(xiàn)金日記賬” ，選取單元格區(qū)域 B2：I2 ，單擊工具欄上的【合并及居中

8、】按鈕，再右鍵設(shè)置單元格格式，打開【設(shè)置單元格格式】對話框，單擊【字體】選項卡，將“字體”設(shè)置為“楷體” ，將“字號”設(shè)置為“ 18”，將“下劃線”設(shè)置為“雙下劃線” ，將“顏色”設(shè)置為“綠色”。單擊【確定】按鈕。(3) 在單元格 B3 中輸入：“ 2016 年”，選擇單元格 B3：C3，單擊工具欄的 【合并及居中】 按鈕。(4) 在單元格 B4、C4中分別輸入“月”、“日”。(5) 在單元格 D4、E4、F4、G4、H4、 I4 中分別錄入“憑證編號” 、“類別”、“摘要”、“借方”、“貸方”和“余額” 。(6) 分別選擇單元格區(qū)域 D3： D4、E3：E4、 F3：F4、G3： G4、H3

9、： H4、I3 ：I4 ，單擊【格式】工具欄的【合并及居中】按鈕，結(jié)果如圖所示。(7) 選中第 3、4 行，右鍵打開【設(shè)置單元格格式】對話框，單擊【對齊】選項卡，設(shè)置“水平對齊”、“垂直對齊”居中（如圖所示 ) ；單擊【字體】選項卡，將“字體”設(shè)置為“宋體” ，將“字形”設(shè)置成“加粗”，將“字號”設(shè)置為“ 12”，將“顏色”設(shè)置為“綠色” ( 如圖所示 ) ，單擊【確定】按鈕。(8) 選擇單元格區(qū)域 B3：I70 ，一【單元格】命令，打開【單元格單擊【邊框】選項卡，設(shè)置“線條設(shè)置“線條樣式”為“粗實線”，和“下邊框” ；設(shè)置“線條樣式”單擊“內(nèi)部邊框” ( 如圖所示 ) ；單(9) 選擇單元格

10、區(qū)域B3：I4 ，格格式】對話框，單擊【邊框】選條顏色”為“綠色” ，設(shè)置“線條線”，單擊“下邊框”；單擊【確定】單擊菜單【格式】格式】對話框，顏色”為“綠色”，單擊“上邊框”為”細實線”，擊【確定】按鈕。打開【設(shè)置單元項卡，設(shè)置“線樣式”為“粗實按鈕。(10) 適當(dāng)調(diào)整各列寬度。完成現(xiàn)金日記賬的結(jié)構(gòu)設(shè)計。項目活動二設(shè)置數(shù)據(jù)有效性為了提高輸入數(shù)據(jù)的效率，可以對一些單元格設(shè)置數(shù)據(jù)有效性。下面介紹現(xiàn)金日記賬“類別”這一列的數(shù)據(jù)有效性設(shè)置，其具體步驟如下：(1) 打開工作簿“項目二” ，選擇工作表“現(xiàn)金類別定義” 。(2) 在列 A 輸入如右圖所示的文字。(3) 返回“現(xiàn)金日記賬”表單，選擇“類別”

11、所在的E 列，單擊【數(shù)據(jù)】菜單中的【數(shù)據(jù)有效性】命令，打開【數(shù)據(jù)有效性】對話框；設(shè)置“允許”為“序列” ( 如圖 ) ；將光標(biāo)移到“來源”位置，輸入“ =現(xiàn)金類別定義!$A$1:$A$10 ”，再單擊【確定】按鈕退出【數(shù)據(jù)有效性】對話框。(4) 選擇單元格 E5，在 E5單元格的右面便會出現(xiàn)一個下拉箭頭，單擊下拉箭頭，便可選擇相應(yīng)的類別。給“類別”列設(shè)置了有效性以后可以省去打字的麻煩，同時也避免了錄入錯誤。同樣，我們也可以給“銀行存款日記賬”的“類別”設(shè)置有效性檢查。在表單“銀行存款類別定義”的A 列輸人如圖所示內(nèi)容。項目活動三設(shè)置條件格式我們可以通過添加條件格式的方法， 給特定單元格設(shè)置醒目

12、的格式， 以方便用戶查閱。 具體步驟如下：（1）在“現(xiàn)金日記賬”表單選擇單元格 F5，單擊開始選項卡中的【條件格式】命令，打開【新建格式規(guī)則】對話框。規(guī)則類型選擇【使用公式確定要設(shè)置格式的單元格】 ，在公式編輯欄輸入條件公式 “=OR($F5= 本日合計 ,$F5= 本月累計 ,$F5= 期初余額 ) ”（2）打開【設(shè)置單元格格式】對話框，選擇字體顏色為“紅色”。（4）設(shè)置“線條樣式”為“細實線” ，設(shè)置“線條顏色”為“紅色” ，然后選擇“下邊框” 。（5）單擊【常用】工具欄上的【格式刷】按鈕，然后選擇要設(shè)置格式的單元格區(qū)域B5：I70 。設(shè)置了上述條件格式以后，當(dāng)在“摘要”欄（F 列）輸入“

13、期初余額” 、“本日合計”、“本月累計”字樣的時候，字體顏色自動設(shè)置成醒目的紅色，并且該行的下邊框也變成紅色， 而其他的地方保持不變。項目活動四余額自動計算本活動將學(xué)習(xí)余額的自動計算， 每筆業(yè)務(wù)發(fā)生后， 應(yīng)將上次的余額加上本次的借方金額， 減去貸方金額，得到本次的余額。（1）輸入期初余額和本月發(fā)生的第一筆現(xiàn)金業(yè)務(wù)。（2）在單元格 I6 輸入公式“ =IF(AND(G6=,H6=),I5+G6-H6)”，按回車自動計算出余額。（5）依次輸入背景資料中1 月 3 日發(fā)生的現(xiàn)金業(yè)務(wù)，形成現(xiàn)金日記賬。項目活動五計算本日合計和本月累計每日營業(yè)終了， 需要計算當(dāng)日的合計數(shù)和本月的累計數(shù)。 按項目活動四的例

14、子， 計算本日合計和本月合計的詳細步驟如下：(1) 在單元格 F14 輸入“本日合計”，在單元格 G14 輸入“ =SUM(G6：G13)”，并將公式填充到H14，形成本日合計的結(jié)果。(2) 單元格 F15 輸入“本月累計”，在單元格 G15輸入“=sum(G6:G13)”，并并將公式填充到 H15，形成本月累計的結(jié)果。(3) 從圖中可以直觀看出，本月合計和本月累計行的余額顯然是錯誤的。錯誤原因在于余額列的計算公式為“=if(and(G14= ” ,h14= ” )，” ,I13+G14-H14)”和“=if(and(G15= ” ,h15= ” ) ，” ,I14+G15-H15) ”，合計

15、和累計本來不該繼續(xù)計算的， 它們也進行了一次計算， 而實際上它們的值應(yīng)該等于 I13 單元格的值。所有我們應(yīng)該對公式進行一定的修改。選中 I6 單元格，把公式改為“ =IF(AND(G6=,H6=),IF(OR(F6= 本日合計 ,F6= 本月累計,F6= 本年累計 ),I5,I5+G6-H6) ”，并復(fù)制到單元格 I7:I70 區(qū)域，從而達到每一行都能夠正確顯示余額的目的。(4) 依次輸入背景資料中 1 月 4 日發(fā)生的現(xiàn)金業(yè)務(wù)，形成現(xiàn)金日記賬。(5) 在單元格 F19 輸入“本日合計”，在單元格 G19輸入相應(yīng)公式，并將公式復(fù)制到單元格 H19，形成本日合計的結(jié)果。在單元格 F20 輸入“

16、本月累計”，在單元格 G20輸入相應(yīng)公式，并將公式復(fù)制到單元格 H20，形成本月累計的結(jié)果。工作任務(wù)三現(xiàn)金日報表的編制現(xiàn)金日報表是企業(yè)管理部門為了及時掌握和了解現(xiàn)金的流動情況而要求出納人員每口提供的報表。現(xiàn)金日報表的編制不僅為企業(yè)現(xiàn)金的管理提供了方便，而且為管理者及時了解和掌握本企業(yè)的資金狀況和合理運用資金提供了參考數(shù)據(jù)。下面介紹現(xiàn)金日報表的編制方法。項目活動一現(xiàn)金日報表的格式設(shè)計在 Excel 上設(shè)計的現(xiàn)金日報表工作表具體的設(shè)計步驟如下：(1) 在單元格 B2 中輸入“現(xiàn)金日報表” ，選取單元格區(qū)域 B2：G2，單擊【格式】工具欄的【合并及居中】按鈕，再單擊菜單【格式】 - 【單元格】，打開

17、【單元格格式】對話框，單擊【字體】選項卡，將 “字體”設(shè)置為 “楷體 GB2312”，將“字號”設(shè)置為 “l(fā)8 ”，將“下劃線”設(shè)置為 “雙下劃線”，將“顏色”設(shè)置為“綠色” ，單擊【確定】按鈕關(guān)閉【單元格格式】對話框?，F(xiàn)金日報表標(biāo)題設(shè)置完成。(2) 在單元格 B4 中輸人“ 2008- l-3 ”，選擇單元格 B4：F4，單擊【格式】工具欄的【合并及居中】按鈕，再單擊菜單【格式】 - 【單元格】，打開【單元格格式】對話框，單擊【數(shù)字】選項卡，分類列表選擇 “自定義”。類型輸人 “ 日 期! ：yyyy 年m月 d 日 ”。單擊【確定】 按鈕，關(guān)閉【單元格格式】對話框。現(xiàn)金日報表日期設(shè)置結(jié)束。

18、(3) 在單元格 G4中輸入“單位：元” ，并設(shè)置其格式。現(xiàn)金日報表表頭設(shè)置完成。(4) 選擇單元格 B6：C7，單擊【格式】工具欄的【合并及居中】按鈕，輸入“項目”并設(shè)置其格式。(5) 選擇單元格 D6：F6，單擊【格式】工具欄的【合并及居中】按鈕，輸入“金額”并設(shè)置其格式。(6) 在單元格 D7、E7、 F7 中分別錄入“本日” 、“本月累計”、“本年累計”并設(shè)置其格式。(7) 選擇單元格 G6：G7，單擊【格式】工具欄的【合并及居中】按鈕，輸人“備注”并設(shè)置其格式。(8) 選擇單元格 B8； B13，單擊【格式】工具欄的【合并及居中】畫按鈕，輸入“本日收入”并設(shè)置其格式。(9) 在單元格

19、 C8：C13中分別輸入“營業(yè)款” 、“個人還款”、“保證金收人”、“取款”、“其他收入”、“本日收入合計”并設(shè)置其格式。(10) 選擇單元格 B14；B19，單擊【格式】工具欄的【合并及居中】按鈕，輸入“本日支出”并設(shè)置其格式。(11) 在單元格 C14：C19 中分別輸人“費用報銷” 、“個人借款”、“存款”、。退還保證金”、“其他支出”、“本日支出合計”并設(shè)置其格式。(12) 選取單元格區(qū)域 B20：C20，單擊【格式】工具欄的【合并及居中】按鈕，輸入“昨日現(xiàn)金余額：”并設(shè)置其格式。合并 D20：G20。(13) 選取單元格區(qū)域 B21：C21單擊【格式】工具欄的【合并及居中】按鈕，輸入

20、“本日現(xiàn)金余額：”并設(shè)置其格式。合并D21：G21。(14) 點擊菜單【工具】 - 【選項】，在視圖對話框中取消網(wǎng)格線選項。最終格式效果圖如下：項目活動二現(xiàn)金日報表數(shù)據(jù)的自動生成我們可以利用 Excel 公式和函數(shù)的操作， 將現(xiàn)金日記賬的數(shù)據(jù)引用過來，完成現(xiàn)金日報表數(shù)據(jù)的自動生成，具體步驟如下：，(1) 選擇單元格 D8，輸入公式“ =SUMPRODUCT(現(xiàn)金日記賬 !$B$6:$B$70=MONTH(現(xiàn)金日報表!$B$4)*( 現(xiàn)金日記賬 !$C$6:$C$70=DAY(現(xiàn)金日報表 !$B$4)*( 現(xiàn)金日記賬 !$E$6 ：$E$70=C8)*現(xiàn)金日記賬 !$G$6：$G$70)”。公式

21、的含義為：當(dāng)現(xiàn)金日記賬單元格區(qū)域 B6：B70 的值為現(xiàn)金日報表單元格區(qū)域 D4 月份值、現(xiàn)金日記賬單元格區(qū)域 C6：C70 的值為現(xiàn)金日報表單元格區(qū)域 D4 的天數(shù)值、現(xiàn)金日記賬單元格區(qū)域 E6：E70 的值為現(xiàn)金日報表單元格區(qū)域 C8的值 ( 營業(yè)款 ) 時，現(xiàn)金日報表單元格區(qū)域 D8 的值為現(xiàn)金日記賬單元格區(qū)域 G6：G70的正總數(shù)。應(yīng)特別注意公式中的絕對引用和相對引用。(2) 選擇單元格 D8，使用填充柄向下填充到 D9：D12。(3) 選擇單元格 D13，輸入公式“ =SUM(D8：D12)”。并填充到 E13： F13。(4) 選擇單元格 D14，輸入公式“ =SUMPRODUCT

22、(現(xiàn)金日記賬 !$B$6:$B$70=MONTH(現(xiàn)金日報表!$B$4)*( 現(xiàn)金日記賬 !$C$6:$C$70=DAY(現(xiàn)金日報表 !$B$4)*( 現(xiàn)金日記賬 !$E$6:$E$70=C14)* 現(xiàn)金日記賬 !$H$6:$H$70) ”。(5) 選擇單元格 D14，使用填充柄向下填充 D15：D18。(6) 選擇單元格 D19，輸入公式“ =SUM(D14:D18)”，并填充到 E19： F19。(7) 本月累計數(shù)只要把本日列公式中天數(shù)的條件“ =”改成“ =”。本年累計只要把本日列公式中月份的條件“ =”改成“ ”，然后 加 E 本月累計數(shù)即可。單元格 E8 的公式為“ =SUMPROD

23、UCT(現(xiàn)金日記賬 !$B$6:$B$70=MONTH(現(xiàn)金日報表 !$B$4)*( 現(xiàn)金日記賬 !$C$6： $C$70=DAY(現(xiàn)金日報表 !$B$4)*(現(xiàn)金日記賬!$E$6:$E$70=C8)* 現(xiàn)金日記賬!$G$6:$G$70) ”。單元格F8 的公式為“ =SUMPRODUCT(現(xiàn) 金 日 記 賬 !$B$6:$B$70MONTH(現(xiàn) 金 日 報 表 !$B$4)*(現(xiàn) 金 日 記賬!$E$6:$E$70=C8)* 現(xiàn)金日記賬 !$G$6:$G$70)+E8”。(8) 選擇單元格 D21，輸人公式“ =現(xiàn)金日記賬 !I5+ 現(xiàn)金日報表 !F13- 現(xiàn)金日報表 !F19 ”。(9)

24、選擇單元格 D20，輸入公式“ =D21-D13+D19。(10) 日報表的數(shù)據(jù)生成完畢，形成 2008 年 1 月 3 日的報表，如圖。(11) 選擇單元格 D4，輸入新的日期“ 200B-1-4 ”，自動生成 1 月 4 日的現(xiàn)金日報表，如圖。工作任務(wù)四銀行存款日記賬的創(chuàng)建銀行存款日記賬是由出納人員按照銀行存款的收， 付款記賬憑證逐日逐筆順序登記， 用以記錄和反映銀行存款收支及結(jié)存情況的一種特種日記賬。 銀行存款日記賬一般采用借方、 貸方、余額三欄式賬頁格式，分別反映銀行存款收入、 付出和結(jié)存情況，以便檢查監(jiān)督各項收入和支出款項， 并便于定期同銀行送來的對賬單核對。下面介紹銀行存款日記賬的

25、設(shè)計方法和步驟。項目活動一銀行存款日記賬的結(jié)構(gòu)設(shè)計銀行存款日記賬工作表格式與現(xiàn)金日記賬基本相同， 因此可以復(fù)制一份 “現(xiàn)金日記賬”工作表，再對工作表進行修改具體步驟如下：(1) 刪除現(xiàn)存的“銀行存款日記賬”工作表。(2) 選擇“現(xiàn)金日記賬”工作表。(3) 用鼠標(biāo)對準(zhǔn)“現(xiàn)金日記賬”工作表標(biāo)簽，單擊鼠標(biāo)右鍵在彈出的快捷菜單中選擇“移動或復(fù)制工作表”命令，打開【移動或復(fù)制工作表】對話框。在“下列選定工作表之前”列表框中選擇“銀行存款類別定義” ，選中【建立副本】復(fù)選框，單擊【確定】按鈕，關(guān)閉【移動或復(fù)制工作表】對話框，就建立了一個名為“現(xiàn)金日記賬 (2) “的工作表。(4) 將工作表“現(xiàn)金日記賬 (2) ”重命名為“銀行存款日記賬” (5) 將工作表中的有關(guān)文字進行修改，例如，將“現(xiàn)金日記賬”改為“銀行存款日記賬”。(6) 在 E 列和 F 列之間插入兩列，選中 E、F、G列，單擊菜單【數(shù)據(jù)】 - 【有效性】，取消之前設(shè)定的有效性規(guī)則。選擇【全部清除】 ，如圖：合并 F3:F4，G3:G4，在合并后的單元格F3 和 G3中分別輸入“結(jié)算方式”和“票據(jù)號碼” 。清空 B5:K70 的數(shù)據(jù)。(7) 選取 F 列，對其設(shè)置數(shù)據(jù)有效性， 即在【有效性】對話框中，在【設(shè)