帶你讀《Excel在會計和财務管理中的應用》之一：日記賬

點選檢視第二章 點選檢視第三章

Excel在會計和财務管理中的應用

第4版

楊維忠 莊 君 黃國芬 編著

第1章 日記賬

日記賬包括現金日記賬和銀行日記賬，是由出納人員（資金會計）按照業務流程發生的時間順序逐筆登記，逐日反映庫存現金或者銀行賬戶存款的收支餘的情況。由于現金日記賬和銀行日記賬的記賬方式完全相同，隻是展現的資金類型不同，是以本章僅以現金日記賬為例介紹通過Excel設計日記賬的方法：一種是通過表對象和資料透視表來完成設計；另一種是通過函數和公式的設定來完成現金日記賬的功能。通過本章的學習，可以了解利用Excel進行财務設計的思路和方法。

現金日記賬是以天為順序，逐日反映庫存現金的收支餘情況，具有結構簡單的特點。在簡易的現金日記賬中，要實作預定的功能，就需要了解以下基本知識：

• 表對象（ListObject）
• 資料透視表

Excel本身不是一種結構嚴謹的資料庫，隻是它的格式和資料庫中的資料表太像了，以至于使用者常常期待可以通過Excel來實作某些隻有資料庫才具有的功能。

使用Excel進行資料分析是比較容易發生錯誤的，常常無法得到預想中的計算結果。原因通常是使用者在單元格中輸入的值具有比較大的随意性，沒有按照規定的資料類型來輸入資料。發生錯誤的時候，Excel一般不會給出提示，不太容易發現輸入錯誤。為了更好地使用Excel做會計電算化設計，模拟資料庫中的資料表，就需要有一種好的Excel工具——表對象。

表對象的英文名稱為ListObject，是以常被稱為表格、表對象或者表格對象，為了不至于引起混淆，本書統一按照表對象來稱呼。使用表對象比單元格區域友善，例如表對象能夠自動擴充表的大小，當使用者添加新的記錄的時候，新增的記錄會自動添加到表對象内。表對象中資料的引用也比完全使用工作表簡單，使用公式的時候隻需要知道字段名稱就可以引用資料，無須知道具體的行列位置。靈活使用表對象是今後使用者進行複雜的财務系統設計必須掌握的一項技能。

Excel中進行資料的計算有兩種基本的思路，一種是使用資料透視表；另一種是使用函數。資料透視表是Excel中功能最為強大的工具，具有分析資料快速簡單的特點，尤其對于大資料量的分析計算，效率尤其高。本節的實驗内容就是按照資料透視表的思路來對現金日記賬的餘額進行計算的。表對象和資料透視表的結合使用是利用Excel進行會計電算化設計的常用方法。

（一）實驗目的

了解表對象，學會使用表對象來收集資料，使用資料透視表來分析資料。

（二）實驗要求

了解現金日記賬的手工記賬方法，了解Excel中表對象和資料透視表的基本使用方法。

金盛公司在2018年1月1日後發生了如下與現金有關的業務：

（1）2018年1月1日轉入上年餘額2500元。

（2）2018年1月3日支付辦公室電話費210元。

（3）2018年1月5日支付12月電費878元。

（4）2018年1月7日報帳經理差旅費500元。

（5）2018年1月6日提現金130000元。

（6）2018年1月6日預付員工差旅費3600元。

（7）2018年1月6日支付工資120000元。

（8）2018年1月14日支付辦公用品費用3752元。

（9）2018年1月15日訂閱報刊2788元。

（10）2018年2月2日提現金5000元。

（11）2018年2月3日支付辦公室電話費255元。

（12）2018年2月2日出售廢舊電腦500元。

（13）2018年2月3日支付1月份電費900元。

（14）2018年2月7日提取現金125000元。

（15）2018年2月7日支付員工工資130000元。

通過為表對象設計字段，将資料直接輸入表格中。最終的現金日記賬要滿足以下要求：

（1）所有輸入的日期必須在2018年1月1日到2018年12月31日之間。

（2）借方發生額、貸方發生額和餘額資料必須保留兩位小數，并按照“會計專用”格式來顯示。

（3）按日計算每日的餘額。

（4）按月分析每月的收支情況。

1．資料輸入設計

為了更好地進行資料分析，本實驗設計了年、月、日、編号、内容摘要、借方發生額、貸方發生額以及餘額8個字段，具體操作步驟如下：

1. 打開Excel，将工作簿儲存為“簡易日記賬.xlsx”，輕按兩下Sheet1工作表标簽，将Sheet1重命名為“現金日記賬”。
2. 在A1~H1單元格區域内依次輸入年、月、日、編号、内容摘要、借方發生額、貸方發生額以及餘額8個字段，如圖1.1所示。

資料庫進行資料表設計時首先需要設計字段的資料類型，在Excel中也一樣。Excel會根據使用者輸入的内容自動判斷資料類型，但是這種自動判斷并不一定準确，使用者可以強制指定資料類型。

在上述字段中，年、月、日和編号是整數類型；内容摘要是文本類型；借方發生額、貸方發生額以及餘額是小數類型，需要保留2位小數。值得注意的是，為某個字段指定資料類型并不能夠完全防止使用者的誤操作，但可以對使用者的輸入起到一定程度的規範作用，具體的操作步驟如下：

1. 選中A:D列并右擊，在快捷菜單中選擇“設定單元格格式”指令。打開“設定單元格格式”對話框，單擊“數字”标簽。在“分類”中選擇“數值”，選擇“小數位數”為0，如圖1.2所示。單擊“确定”按鈕，完成确定A列到D列的年、月、日以及編号的資料類型。
2. 選中E列并右擊，在快捷菜單中選擇“設定單元格格式”指令，如圖1.3所示。從打開的“設定單元格格式”對話框中，單擊“數字”标簽。在“分類”中選擇類型為“文本”，完成對E列内容摘要的資料類型的設定。

   

1. 選中F:H列并右擊，在快捷菜單中選擇“設定單元格格式”，從打開的“設定單元格格式”對話框中，單擊“數字”标簽。在“分類”中選擇類型為“會計專用”，“小數位數”設定為2，“貨币符号”設定為無，完成對F列到H列借貸方發生額以及餘額的資料類型設定。

為了進一步減少輸入錯誤，可以繼續添加資料驗證的功能，具體操作步驟如下：

1. 選中A列，單擊“資料”頁籤，執行“資料工具”組内的“資料驗證”指令，如圖1.4所示。然後打開如圖1.5所示的“資料驗證”對話框，單擊“設定”頁籤，在“驗證條件”的“允許”下設定為“整數”，“資料”選擇“介于”，“最小值”設定為2018，“最大值”設定為2019。

   

1. 選擇“出錯警告”頁籤，在“樣式”中選擇“停止”樣式，在“标題”中輸入“資料錯誤提示”，在“錯誤資訊”下的文本框中輸入“輸入資料超出指定範圍”，如圖1.6所示。單擊“确定”按鈕，完成年字段資料驗證的設定。

1. 選中B列，單擊“資料”頁籤，執行“資料工具”組内的“資料驗證”指令，打開“資料驗證”對話框，單擊“設定”頁籤，在驗證條件的“允許”下設定為“整數”，“資料”選擇“介于”，“最小值”設定為1，“最大值”設定為12。選擇“出錯警告”頁籤，在“樣式”中選擇“停止”樣式，在“标題”中輸入“資料錯誤提示”，在“錯誤資訊”下的文本框中輸入“輸入資料超出指定範圍”，單擊“确定”按鈕，完成月字段資料驗證的設定。
2. 選中C列，單擊“資料”頁籤，執行“資料工具”組内的“資料驗證”指令，打開“資料驗證”對話框，單擊“設定”頁籤，在“驗證條件”的“允許”下設定為“整數”，“資料”選擇“介于”，“最小值”設定為1，“最大值”設定為31。選擇“出錯警告”頁籤，在“樣式”中選擇“停止”樣式，在“标題”中輸入“資料錯誤提示”，在“錯誤資訊”下的文本框中輸入“輸入資料超出指定範圍”，單擊“确定”按鈕，完成日字段資料驗證的設定。
3. 選中F:G列，單擊“資料”頁籤，執行“資料工具”組内的“資料驗證”指令，打開如圖1.7所示的“資料驗證”對話框，單擊“設定”頁籤，在有效性的“允許”下設定為“小數”，在“資料”下拉清單中選擇“介于”。在“最小值”中輸入0，在“最大值”中輸入200000。選擇“出錯警告”頁籤，在“樣式”中選擇“停止”樣式，在“标題”中輸入“資料錯誤提示”，在“錯誤資訊”下的文本框中輸入“輸入資料超出指定範圍”，單擊“确定”按鈕，完成借方發生額以及貸方發生額字段資料驗證的設定。

2．建立公式

在表對象中，編号和餘額兩個字段是通過公式計算獲得的。編号是和行号相關的，為餘額字段設計公式時，首筆記錄的公式和後續公式是不一緻的。在本例中，第1筆記錄是從上年餘額結轉而來的，餘額就等于借方的發生額。第一筆業務之後的其他記錄餘額的計算方式是“上筆餘額+本筆借方發生額?本筆貸方發生額”。将所有的公式設定完成後，就可以将工作表的單元格區域轉化為表對象了，具體的操作步驟如下：

1. 選中D2單元格，在編輯欄内輸入公式“=ROW()-1”，完成編号字段的設定。

1. 選中H2單元格，在編輯欄内輸入公式“=F2”，完成第1條記錄餘額的計算。
2. 從A2單元格開始，輸入第1條記錄的其餘部分，輸入的第1條記錄如圖1.8所示。

   

1. 選中A3單元格，在編輯欄内輸入公式“=2018”，接着輸入第2條記錄，其中D3單元格中輸入公式“=ROW()-1”，在H3單元格中輸入公式“=H2+F3-G3”，輸入的第2條記錄如圖1.9所示。

   

1. 選中A1單元格，選中“插入”頁籤，執行“表格”組中的“表格”指令，如圖1.10所示。然後打開如圖1.11所示的“建立表”對話框，勾選“表包含标題”複選框，單擊“确定”按鈕，将單元格區域轉化為表。

1. 選中A1單元格，選擇“設計”頁籤，在“表格樣式”組中選擇“白色，表樣式淺色15”，如圖1.12所示。

1. 單擊“設計”頁籤，在“屬性”組中，将表名稱由預設的“表1”更改為“現金日記賬”，完成表格名稱的設定，如圖1.13所示。

1. 繼續輸入剩餘的記錄，最終的結果如圖1.14所示。

   

3．按月彙總

要對大量資料進行快速分析，資料透視表是最好的選擇。資料透視表分析的對象是單元格區域或者表對象，它可以對資料進行分類彙總、添加計算字段以及重新整理資料。按月彙總就是根據“月字段”進行彙總。資料透視表進行彙總是非常簡單的，隻需要簡單地拖曳就能快速形成資料。字段被放置的位置可以是篩選器、列、行和值4個區域中。具體的操作步驟如下：

1. 選擇“現金日記賬”工作表的A1單元格，選擇“插入”頁籤，執行“表格”組内的“資料透視表”指令，如圖1.15所示，打開“建立資料透視表”對話框。

1. 選中“選擇一個表或區域”單選按鈕，已經可以自動識别要進行資料透視的區域，如果未能識别出，就在“表/區域”後輸入“現金日記賬”，選擇放置資料透視表的位置為“新工作表”，如圖1.16所示，完成資料透視表資料源的設定。

1. 按照上述步驟打開如圖1.17所示的“資料透視表字段”對話框，将“月”字段和“日”字段拖曳到“行”中，将“借方發生額”和“貸方發生額”字段拖曳到“值”位置中，完成要顯示的字段的設定。

1. 單擊“資料透視表字段”對話框的“值”内的“計數項：借方發生額”字段，執行“值字段設定”指令，打開如圖1.19所示的“值字段設定”對話框。在“計算類型”中選擇“求和”，在自定義名稱後的框内輸入“借方發生額”。單擊“值字段設定”對話框中的“數字格式”按鈕，打開“設定單元格格式”對話框，在該對話框中單擊“數字”标簽，在分類中選擇類型為“會計專用”，“小數位數”設定為2，“貨币符号”設定為無，單擊“确定”按鈕，完成字段計算類型和數字的設定。

1. 在“資料透視表字段”對話框的“值”内的“計數項：貸方發生額”字段上單擊，執行“值字段設定”指令，打開“值字段設定”對話框。在“計算類型”中選擇“求和”，在自定義名稱後的框内輸入“貸方發生額”。單擊“數字格式”按鈕，打開“設定單元格格式”對話框，在該對話框中單擊“數字”标簽，在“分類”中選擇類型為“會計專用”，“小數位數”設定為2，貨币符号設定為無，單擊“确定”按鈕，完成字段計算類型和數字的設定。
2. 選中資料透視表中任意一個單元格，選擇“分析”頁籤，執行“計算”組中的“字段、項目和集”下的“計算字段”指令，如圖1.20所示。

   

打開如圖1.21所示的“插入計算字段”對話框。

1. 将“名稱”後的組合框中的“字段1”改為“餘額總計”。
2. 将“公式”後的文本框中的“0”删除，選中“字段”清單框中的“借方發生額”，單擊“插入字段”按鈕，再輸入減号“-”，然後選中“字段”清單框中的“貸方發生額”，單擊“插入字段”按鈕。單擊“确定”按鈕，完成插入計算字段的操作。

1. 選中E3單元格，将“求和項：餘額總計”更改為“餘額總計”。
2. 選中A3單元格，單擊“布局”中的“報表布局”，選擇布局類型為“以表格形式顯示”，最終結果如圖1.22所示。

1. 選中資料透視表中的任意一個單元格，單擊“分析”頁籤，執行“資料”組内的“重新整理”指令，完成資料重新整理操作。

4．優化設計

通過上述操作，完成分析步驟就已經基本完成了資料透視表的分析功能，不過仍舊有改進的空間，包括對餘額負數的顯示處理等，具體的操作步驟如下：

1. 打開“現金日記賬”工作表，選擇H列，在“開始”頁籤中執行“樣式”組内的“條件格式”指令，如圖1.23所示。

選擇“突出顯示單元格規則”下的“小于”，打開如圖1.24所示的“小于”對話框，在“為小于以下值的單元格設定格式”框中輸入0，格式設定為“紅色文本”，單擊“确定”按鈕，完成小于零的數值格式的設定。

1. 選中資料透視表中任意一個單元格，選擇“分析”頁籤，執行“資料透視表”中的“選項”指令，如圖1.25所示。

打開如圖1.26所示的“資料透視表選項”對話框。

1. 單擊“布局和格式”标簽，取消勾選“更新時自動調整列寬”和“更新時保留單元格格式”複選框，完成對資料透視表重新整理時不更改格式的設定。

金邦公司在2018年1月和2月發生了如下和現金有關的業務，請根據實驗内容編制簡易現金日記賬：

（1）1月1日轉入上年餘額2200元。

（2）1月3日支付差旅費320元。

（3）1月5日支付12月電費550元。

（4）1月5日提現金25000元。

（5）1月6日支付招待費3500元。

（6）1月6日支付差旅費2400元。

（7）1月6日支付手續費35元。

（8）1月14日支付辦公用品費6400元。

（9）1月15日購買列印機1500元。

（10）2月2日支付辦公室電話費650元。

（11）2月2日出售廢舊包裝物320元。

（12）2月2日支付1月份電費3320元。

（13）2月3日支付差旅費2500元。

（14）2月7日支付綠化苗木款4000元。

（15）2月7日支付招待費2200元。

簡易現金日記賬外觀簡陋，顯示餘額的方式也不直覺，和使用者平時使用的現金日記賬的外觀相去甚遠，不太符合普通使用者的習慣。理想的現金日記賬應當有一個和使用者平時的紙質賬頁類似的外觀，而且要能夠實作以下功能：

（1）輸入的資料能夠完整反映業務情況。

（2）實時反映每筆業務完成後的現金餘額。

（3）按月計算本月的借貸方累計發生額、本年的借貸方累計發生額以及餘額。

要完成複雜格式的功能，表對象和資料透視表就無能為力了。表對象和資料透視表最大的優點在于資料能夠按照資料庫樣式排列，能夠通過重新整理實時獲得資料，缺點是外觀一般都比較簡單。而紙質賬頁的外觀複雜，這也就決定了使用者不需要通過公式和函數的功能來完成現金日記賬的設計。

了解表格設計方法，掌握SUBTOTAL和IF等函數的方法。

能夠熟練繪制表格，能夠熟練使用函數和公式。

金盛公司在2018年1月1日後發生了如下和現金有關的業務：

1．繪制界面

繪制界面的過程就是将紙質賬頁移植到Excel工作表的過程，具體的操作步驟如下：

1. 打開Excel，将檔案儲存為“賬頁式現金日記賬.xlsx”，選中Sheet1，重命名為“現金日記賬”。
2. 在工作表内輸入如圖1.27所示的現金日記賬界面，完成界面的設定。

1. 選中F、G和I列，右擊，從打開的快捷菜單中執行“設定單元格格式”指令，如圖1.28所示。

打開“設定單元格格式”對話框，單擊“數字”标簽。在“分類”中選擇類型為“會計專用”，“小數位數”設定為2，“貨币符号”設定為無，單擊“确定”按鈕，完成對指定列的數字格式的設定，如圖1.29所示。

界面設計完成後，隻是搭建了一個資料輸入平台。截至目前，該工作表隻有資料輸入功能，而不具備計算功能，為此需要為以下内容添加計算公式：

• 初始餘額
• 月份資訊
• 本月借貸方發生總額
• 本年借貸方累計發生總額
• 借貸方向
• 餘額

為界面添加計算公式的具體操作步驟如下：

1. 選中I5單元格，在編輯欄内輸入公式“=F5”，選中H5單元格，輸入“借”字，完成現金初始餘額的計算。

1. 選中B14單元格，在編輯欄内輸入“=COUNTIF(”，單擊插入函數按鈕 ，如圖1.30所示。

打開如圖1.31所示的“函數參數”對話框，在Range參數後輸入“$E$6:E15”，在Criteria參數後輸入“本月合計”，确定本月月份。

1. 選中B15單元格，在編輯欄内輸入公式“=B14”，确定“本年累計”行的月份設定。
2. 選中F14單元格，在編輯欄内輸入“=SUBTOTAL(”，單擊插入函數按鈕 ，打開如圖1.32所示的“函數參數”對話框，在“Function_num”參數後輸入數值9，在Ref1參數後輸入單元格區域“F6:F13”，完成本月借方發生總額的計算。

1. 選中G14單元格，在編輯欄内輸入“=SUBTOTAL(”，單擊插入函數按鈕 ，打開“函數參數”對話框，在“Function_num”參數後輸入數值9，在Ref1參數後輸入單元格區域“G6:G13”，完成本月貸方發生總額的計算。
2. 選中F15單元格，在編輯欄内輸入“=SUBTOTAL(”，單擊插入函數按鈕 ，打開“函數參數”對話框，在“Function_num”參數後輸入數值9，在Ref1參數後輸入單元格區域“$F$6:F13”，完成本年借方累計發生額的計算。

1. 選中G15單元格，在編輯欄内輸入“=SUBTOTAL(”，單擊插入函數按鈕 ，打開“函數參數”對話框，在“Function_num”參數後輸入數值9，在Ref1參數後輸入單元格區域“$G$6:G13”，完成本年貸方累計發生額的計算。
2. 選中H15，輸入“=IF($I$5+SUBTOTAL(9,$F$6:F13)-SUBTOTAL(9,$G$6:G13)>0,"借",IF($I$5+ SUBTOTAL(9,$F$6:F13)-SUBTOTAL(9,$G$6:G13)<0,"貸","平"))”，完成餘額方向的确定。

1. 選中I15單元格輸入“=ABS($I$5+F15-G15)”，完成餘額的計算。

3．持續記賬設計

在圖1.27所示的賬頁式現金日記賬中，為1月份預留的行數是從第6行開始到第13行結束，共計8行，對企業來說，這些行數可能是不夠的，為此需要添加行。

添加的空白行一般不太可能和使用者實際需要用到的行一緻，通常為了防止行不夠，會盡可能多地添加一些空行。根據會計規範對現金日記賬的要求，現金日記賬不得存在空記錄，如果添加的行超過了所需的行數，則這些空白的行必須删除，一般我們可以在月底輸入完成後删除空行。

為1月份添加更多行的操作步驟如下：

1. 選中第11行，右擊，執行“插入”指令，完成插入行的操作。

1. 将資料填寫到現金日記賬1月份的對應位置，1月份填報完成後的樣式如圖1.33所示。

1. 選中第6~15行，右擊，選擇“開始”頁籤，執行“剪貼闆”中的“複制”指令，完成1月份資料區域的複制。
2. 選中A16單元格，右擊，執行“粘貼”指令，将1月份的資料全部粘貼過來。
3. 選中第16~23行，按鍵盤上的Delete鍵，删除複制過來的1月份的資料。

通過上述操作使2月份産生了若幹條空記錄。當然，這些空記錄有可能不夠，就要按照前面講到的方法插入更多的行，如果最終輸入完成後，有空餘未輸入資料的行，則需要删除這些空行。

1月份資料複制過來後，所有為1月份設計的公式完全不需要修改就可以适應2月份的情況。輸入完成後如圖1.34所示。

金邦公司在2018年1月和2月發生了如下和現金有關的業務，請根據下列内容利用函數和公式建立現金日記賬：

（4）1月6日支付招待費3500元。

（5）1月6日支付差旅費2400元。

（6）1月6日支付手續費35元。

（7）1月7日提現金25000元。

（8）1月14日支付辦公用品費用6400元。

（11）2月2日支付1月份電費3320元。

（12）2月3日出售廢舊包裝物320元。