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

點選檢視第一章 點選檢視第二章

第3章 會計賬簿

憑證庫就相當于整個賬務處理系統的資料中心，它為賬簿和報表提供了所需的資料。從Excel應用的角度來看，賬簿和報表隻是用不同的分析角度來看待憑證庫資料。本章介紹了如何建立科目彙總表、科目餘額表、總分類賬、明細分類賬以及導航頁面的設計等内容。

科目彙總表的作用是定期對全部記賬憑證進行彙總，按各個會計科目列出其借方發生額和貸方發生額的一種彙總憑證。根據借貸記賬法的基本原理，每一張記賬憑證都必須符合“有借必有貸，借貸必相等”的原則，每個月産生的科目彙總表中各個會計科目的借方發生額合計與貸方發生額合計必然相等。是以，科目彙總表具有試算平衡的作用。

科目彙總表适用于業務量較大的機關。在手工記賬的條件下，科目彙總表還為總分類賬提供了資料作為登記的依據，但是在會計電算化條件下，由于科目彙總表無法反映科目的對應關系，不便于經濟業務核查，是以在電算化軟體的設計中會計憑證應該來自于最明細的原始單據。是以，不能夠依據科目彙總表來登記入賬，其作用僅限于試算平衡。

使用Excel進行科目彙總表的設計，需要解決兩方面的問題：

一是會計科目從何而來？會計科目不能在工作表中預先設立并固定不變。原因是科目彙總表中預先設立的會計科目都是基本的會計科目而不是全部的科目，實際輸入憑證的過程中有部分會計科目在指定的月份中并未使用，而有部分會計科目是在實際工作中後續添加的，如何反映這部分會計科目是需要解決的第一個問題。

二是金額從何而來？既然是會計電算化軟體，就應當具有實時檢視資料的功能，如何實時重新整理資料是需要解決的第二個問題。

Excel中有一個Microsoft Query元件，該元件的作用相當于資料庫的查詢分析器，能夠通過該元件從資料庫中提取資料并顯示到工作表中。雖然Excel并不是真正意義上的資料庫，但是Microsoft Query元件依舊能夠從指定的工作簿（包括目前正在使用的工作簿）中提取資料。如果把“科目彙總表”工作表當作資料表看待，就可以解決會計科目的來源問題，如果把“憑證庫”工作表當資料表來看，就可以解決金額來源的問題。

（一）實驗目的

熟悉科目彙總表的結構和Microsoft Query提取資料的方法。

（二）實驗要求

了解Microsoft Query的使用方法。

科目彙總表包括的内容：

（1）日期資訊。

（2）科目代碼。

（3）科目名稱。

（4）指定月借方發生額。

（5）指定月貸方發生額。

1．科目彙總表界面

科目彙總表的界面僅僅是一個簡單的表頭，建立科目彙總表具體操作步驟如下：

1. 在“賬務處理.xlsm”中建立一張工作表，将其重命名為“科目彙總表”。
2. 選中A1:D1單元格區域，單擊“開始”頁籤，執行“對齊方式”組中的“合并後居中”指令，将指定的内容合并居中，然後在A1單元格中輸入“=B2&"年"&D2&"月科目彙總表"”，完成表頭标題的設定。
3. 從A2單元格開始輸入如圖3.1所示的内容，完成科目彙總表界面的設定。

2．科目彙總表資料

憑證庫中包含會計科目名稱和借貸方指定月份的發生額等資訊内容。科目彙總表的資料來源于憑證庫工作表，從憑證庫工作表中提取資料的具體操作步驟如下：

1. 選中A4單元格，選中“資料”頁籤，在“擷取外部資料”組中，執行“自其他來源”中的“來自Microsoft Query”指令，打開如圖3.2所示的“選擇資料源”對話框。

1. 在如圖3.2所示的“選擇資料源”對話框中，選擇資料庫類型為“Excel Files*”，單擊“确定”按鈕，打開如圖3.3所示的“選擇工作簿”對話框。

1. 在驅動器中選擇D盤，在目錄中選擇“data正文”，資料庫的名稱就指定為“賬務處理.xlsm”，單擊“确定”按鈕，打開如圖3.4所示的“查詢向導-選擇列”對話框，單擊“可用的表和列”中“憑證庫”前的“+”号，展開該表所包含的列，選中要顯示的列為科目代碼、總賬科目、借金額和貸金額。

1. 單擊“下一步”按鈕，完成要顯示的列的選擇，進入“查詢向導-篩選資料”對話框，如圖3.5所示。

1. 單擊“下一步”按鈕，跳過篩選的過程，打開如圖3.6所示的“查詢向導-排序順序”對話框，在該步驟中不需要設定任何内容。

1. 單擊“下一步”按鈕，打開如圖3.7所示的“查詢向導-完成”對話框，在“請确定下一步的動作”中，選中“在Microsoft Query中檢視資料或編輯查詢”，單擊“完成”按鈕。

完成查詢向導的操作，進入Microsoft Query視窗，如圖3.8所示。

1. 在Microsoft Query視窗中，選中“科目代碼”列中任意一個資料，執行“記錄”菜單下的“編輯列”指令，打開如圖3.9所示的“編輯列”對話框，将“字段”下的内容更改為“Left(科目代碼,4)”，單擊“确定”按鈕，完成列的設定。
2. 在Microsoft Query視窗中，選中“借金額”列中任意一個資料，執行“記錄”菜單下的“編輯列”指令，打開如圖3.10所示的“編輯列”對話框，在“字段”下拉清單中選擇“借金額”，在“總計”下拉清單中選擇“求和”，單擊“确定”按鈕，完成借金額字段的求和。

   

1. 在Microsoft Query視窗中，選中“貸金額”列中任意一個資料，執行“記錄”菜單下的“編輯列”指令，打開如圖3.11所示的“編輯列”對話框，在“字段”下拉清單中選擇“貸金額”，在“總計”下拉清單中選擇“求和”，單擊“确定”按鈕，完成貸金額字段的求和。

1. 在Microsoft Query視窗中，執行“條件”菜單下的“添加條件”指令，打開如圖3.12所示的“添加條件”對話框，保持“總計”下拉清單為空，選擇“字段”為“年”，“運算符”設定為“等于”，“指定值”設定為“[nian]”，單擊“添加”按鈕，完成年份條件的設定。保持“總計”下拉清單内為空，選擇“字段”為“月”，“運算符”設定為“等于”，“指定值”設定為“[yue]”，單擊“添加”按鈕，完成月份條件設定。

1. 執行Microsoft Query視窗中“檔案”菜單下的“将資料傳回Microsoft Excel”，打開如圖3.15所示的“導入資料”對話框，将資料放置的位置設定為“科目彙總表”的A4單元格，完成資料導入起始位置的設定。

1. 單擊“導入資料”對話框中的“确定”按鈕，打開如圖3.16所示的“輸入參數值”對話框，單擊“nian”下的文本框後的折疊按鈕 ，單擊工作表中的B2單元格，并且勾選“在以後的重新整理中使用該值或該引用”和“當單元格值更改時自動重新整理”複選框，單擊“确定”按鈕完成年份參數的設定。單擊“yue”下的文本框後的折疊按鈕 ，單擊工作表中的D2單元格，并且勾選“在以後的重新整理中使用該值或該引用”和“當單元格值更改時自動重新整理”複選框。

1. 選中A4單元格，選中“設計”頁籤，選擇“表樣式”組中的第一種樣式“無”，勾選“表樣式選項”組中的“彙總行”項，添加一個彙總行，并将借金額和貸金額的彙總方式都設定為“求和”。
2. 選中A3:D19單元格區域，設定指定的單元格區域包含邊框線。
3. 選中C5:D19單元格區域，右擊，執行“設定單元格格式”指令，選擇“數值類型”為“會計專用”，不顯示“貨币符号”類型。
4. 選中A4單元格，選擇“設計”頁籤，執行“外部表資料”組中的“屬性”指令，打開如圖3.17所示的“外部資料屬性”對話框，保證“調整列寬”複選框不被選中，單擊“确定”按鈕，完成格式和布局的設定。

1. 選中第4行，右擊，指定“隐藏”指令，隐藏表對象的标題行。

   通過上述步驟就完成了科目彙總表的所有設計，設計完成後的科目彙總表如圖3.18所示。

當彙總行的“借方發生額”和“貸方發生額”兩個資料一緻的時候，說明所有的憑證借貸方金額沒有發生輸入錯誤，進而達到試算平衡。當要檢視其他月份的時候，隻要修改D2單元格的值即可。比如當月份修改為2的時候，由于2月份并沒有輸入憑證，是以顯示的結果如圖3.19所示。

根據第2章公司發生的業務，編制公司1月份的科目彙總表。

科目餘額表是本期所有會計科目的發生額和餘額的表格，它反映了某一會計期間相關會計科目的期初餘額、本期發生額和期末餘額。

與科目彙總表類似，科目餘額表反映的是各個科目的餘額，但是各個月份的會計科目并不是固定的，部分明細科目會随着企業業務的發展而逐漸加入其中。是以，直接使用公式引用科目代碼表并不是一個很好的選擇。上一個實驗已經介紹了一種導入變動資料的方法，那就是利用Microsoft Query元件。

使用者通過Microsoft Query元件将“科目代碼表”中的科目導入工作表後，隻需要單擊重新整理，則不論什麼樣的會計科目總能夠出現在工作表中，并且這些資料是和科目代碼表中的資料完全一緻的。

同樣，科目餘額表也會遇到餘額的處理問題。科目餘額表中涉及的科目餘額計算包括期初餘額、本期發生額和期末餘額。本實驗中，企業從年初開始建賬，是以期初餘額就是年初數，本期發生額是指定期限中的累計借方和累計貸方的發生額，期末餘額則是根據期初餘額和本期發生額計算的結果。

掌握科目餘額表的結構和Microsoft Query提取資料的方法。

了解Microsoft Query的用法。

科目餘額表需要實作的功能包括：

（1）期初各個會計科目的借方和貸方餘額。

（2）當期各會計科目的借方和貸方發生額。

（3）計算本期期末各個會計科目的借方和貸方餘額。

1．科目餘額表界面

1. 建立工作表，将工作表重命名為“科目餘額表”。
2. 選中E1:G1單元格區域，執行“開始”頁籤“對齊方式”組中的“合并後居中”指令，設定字型為“仿宋_GB2312”，字号為24，并設定雙下畫線，完成字型字号的設定。
3. 選中E1單元格，在編輯欄内輸入公式“=B2 & "年" & G2 & "月科目餘額表"”，完成科目餘額表标題的設定。
4. 從第2行開始輸入如圖3.20所示的科目餘額表中的各項。

2．導入科目代碼

引用科目代碼的操作過程如下：

1. 選中“資料”頁籤，在“擷取外部資料”組中，執行“自其他來源”中的“來自Microsoft Query”指令，打開“選擇資料源”對話框。
2. 在“選擇資料源”對話框中，選擇“資料庫類型”為“Excel Files*”，單擊“确定”按鈕，打開如圖3.21所示的“選擇工作簿”對話框。

1. 選擇工作簿所在的檔案路徑，單擊“确定”按鈕，打開如圖3.22所示的“查詢向導-選擇列”對話框，單擊“科目代碼表”前的“+”号，展開該表所包含的字段，選中要顯示的列為科目代碼、科目名稱、性質和年初數。

1. 單擊“下一步”按鈕，完成要顯示的列的選擇，進入“查詢向導-篩選資料”對話框，單擊“下一步”按鈕，跳過篩選的過程，打開“查詢向導-排序順序”對話框，在該步驟中不需要設定任何内容。單擊“下一步”按鈕，打開“查詢向導-完成”對話框，在“請确定下一步的動作”選擇中，選中“在Microsoft Query中檢視資料或編輯查詢”，單擊“完成”按鈕，完成查詢向導的操作，進入Microsoft Query界面。
2. 在“Microsoft Query”視窗中，執行“條件”菜單下的“添加條件”指令，打開如圖3.23所示的“添加條件”對話框，選擇“字段”為“是否明細”，“指定值”為“y”，單擊“添加”按鈕，完成條件設定，單擊“關閉”按鈕。

1. 執行Microsoft Query視窗中檔案菜單下的“将資料傳回Microsoft Excel”中，打開如圖3.24所示的“導入資料”對話框，将“資料的放置位置”設定為“科目餘額表”的A5單元格，完成資料導入的操作。

1. 選中A5單元格，選中“設計”頁籤，在“表樣式”組中選擇一種樣式。選中D5單元格，右擊，在打開的快捷菜單中執行“插入”下的“在右側插入表列”指令，插入一個新列。使用同樣的操作方法插入其他5列，完成表主體結構的設定。
2. 選中A5單元格，打開“資料”頁籤，執行“排序和篩選”組内的“篩選”指令，退出篩選狀态。
3. 選中A5單元格，選中“設計”頁籤，将“屬性”組内的表名稱更改為“kmye”，完成表名稱的設定。最終完成的結果如圖3.25所示。

   

3．科目餘額

科目餘額是通過計算獲得的，需要計算的内容包括期初餘額、本期借方和貸方發生額以及期末餘額。科目餘額表完成後，當G2單元格中月份資訊發生變動的時候，期初餘額、本期借方和貸方發生額以及期末餘額也會随之發生變化。計算科目餘額的具體操作過程如下：

1. 選中E6單元格，在編輯欄内輸入公式“=IF([@性質]=1,[@性質]*[@年初數]+(SUMIFS(pzk[借金額],pzk[科目代碼],[@科目代碼],pzk[月],"<"&$G$2)-SUMIFS(pzk[貸金額],pzk[科目代碼],[@科目代碼],pzk[月],"<"&$G$2)),0)”，完成期初餘額的計算。

1. 選中F6單元格，在編輯欄内輸入公式“=IF(kmye[[#此行],[性質]]=-1,ABS(kmye[[#此行],[性質]]*kmye[[#此行],[年初數]]+(SUMIFS(pzk[貸金額],pzk[科目代碼],kmye[[#此行],[科目代碼]],pzk[月],"<"&$G$2)-SUMIFS(pzk[借金額],pzk[科目代碼],kmye[[#此行],[科目代碼]],pzk[月],"<"&$G$2))),0)”，完成期初貸方餘額的計算。

1. 選中G6單元格，在編輯欄内輸入公式“=SUMIFS(pzk[借金額],pzk[科目代碼],[@科目代碼],pzk[月],$G$2)”，完成本月借方發生額的計算。

1. 選中H6單元格，在編輯欄内輸入公式“=SUMIFS(pzk[貸金額],pzk[科目代碼],[@科目代碼],pzk[月],$G$2)”，完成本月貸方發生額的計算。
2. 選中I6單元格，在編輯欄内輸入公式“=IF([@性質]=1,[@列1]+[@列3]-[@列4],0)”，完成借方餘額的計算。

1. 選中J6單元格，在編輯欄内輸入公式“=IF([@性質]=-1,[@列2]+[@列4]-[@列3],0)”，完成貸方餘額的計算。

1. 選中C6:J79單元格區域，右擊，執行“設定單元格格式”指令，選擇數值類型為“會計專用”，不顯示貨币符号類型。
2. 選中“科目餘額表”的A5單元格，選中“設計”頁籤，執行“表格樣式選項”組内的“彙總行”指令，如圖3.26所示，完成添加“彙總行”的指令。

1. 在“彙總”行中，選中列5所在的列，從下拉清單中選擇彙總方式為“求和”，如圖3.27所示，完成彙總期末借方餘額的計算。

1. 用同樣的方法，将彙總行中列1、列2、列3和列4字段所在的列彙總方式設定為“求和”。
2. 在“彙總”行中，選中年初數所在的列，在編輯欄内輸入公式“=SUM([性質]*[年初數])”，同時按Shift+Ctrl和Enter鍵，完成數組公式的輸入。

1. 選中第5行，也就是字段名稱所在的行，右擊，執行“隐藏”指令，隐藏該行。
2. 選中C列和D列，右擊，執行“隐藏”指令，隐藏指定的列。
3. 選中A6單元格，選擇“設計”頁籤，執行“外部表資料”組内的“屬性”指令，打開“外部資料屬性”對話框，如圖3.28所示。在“資料格式和布局”下不要選中“調整列寬”項，單擊“确定”按鈕，完成外部資料屬性的設定。

通過上述方式，最終形成的科目餘額表如圖3.29所示。

4．擷取最新資料

會計科目并非是在設計科目代碼表的時候一次性設定完畢的，使用者可根據業務的需要設定新的會計科目，于是科目代碼表中的會計科目和科目餘額表中的會計科目在某個時候會有不一緻的情況。使用者在憑證庫中導入資料後，這些資料不會馬上就在科目餘額表中展現出來，為了獲得最新的資料，這時就需要設定一個重新整理機制。隻要使用者查詢科目餘額表的時候單擊一下重新整理按鈕，就能自動獲得新增加的科目，具體操作步驟如下：

1. 選中“科目餘額表”，選中“開發工具”頁籤，執行“控件”組中的“表單控件”，選擇“按鈕控件”，在J1單元格内拖出一個指令按鈕，将指令按鈕内的文字更改為“重新整理”，如圖3.30所示，完成指令按鈕的放置。

1. 選擇“開發工具”頁籤，執行“代碼”組内的“錄制宏”指令，打開“錄制宏”對話框，将宏名稱更改為“重新整理科目”，如圖3.31所示，單擊“确定”按鈕，開始錄制宏。

1. 選中A6單元格，右擊，執行“重新整理”指令。單擊“開發工具”頁籤的“代碼組”内的“停止錄制”指令，完成宏的錄制。
2. 在“重新整理資料”按鈕上右擊，執行“指定宏”指令，打開“指定宏”對話框，選擇“重新整理科目”項，單擊“确定”按鈕，完成指定宏的設定。

假設在“科目代碼表”中添加1個科目代碼為122103的其他應收款的子科目，在科目代碼表中添加完成後的情況如圖3.32所示。

選擇“科目餘額表”，單擊“重新整理”按鈕，完成資料的重新整理操作。重新整理後的科目餘額表如圖3.33所示。

從圖3.33可以看出，隻需要單擊重新整理操作，科目代碼表中的科目代碼就會同步到科目餘額表中，進而保證兩個表之間的科目一緻性。

根據公司發生的業務，編制公司1月份的科目餘額表。

總分類賬就是我們通常所稱的總賬，它是根據總分類科目開設賬戶，用來登記全部經濟業務，進行總分類核算，提供總括核算資料的分類賬簿。總分類賬提供了編制會計報表的主要依據，是所有機關都必須設立的賬簿。總分類賬全面且總括地反映了業務機關的财務收支和經濟活動情況。

總分類賬的結構本身并不複雜，使用Excel進行設計的時候，隻需要更改日期資訊和會計科目，就能夠得到期初餘額、當期的借方發生額和貸方發生額以及期末餘額，但是并不反映具體的業務資訊。

日期資訊是由使用者手工更改的，會計科目資訊可以通過資料驗證的方式交由使用者選擇而不是直接輸入進而避免錯誤，對于各種餘額和發生額則都是通過函數從憑證庫中提取的。

掌握總分類賬的結構。

了解資料驗證的方法和帶條件的計算求和方法。

要求根據已經完成的科目餘額表中的期初數和憑證庫中的憑證資料編制總分類賬。

1．總分類賬界面

總分類賬中要更改的資訊包括年份資訊、月份資訊和科目代碼資訊。總分類賬界面的設計過程如下：

1. 建立一張工作表，将工作表名稱更改為“總分類賬”，選中D1~E1單元格，将該單元格區域合并并居中，設定“字号”為24号，“字型”為“仿宋_GB2312”，在D1單元格内輸入“總分類賬”，完成總分類賬标題的設定。
2. 從A2單元格開始輸入如圖3.34所示的表格内容。

1. 選中C3單元格，選中“資料”頁籤，單擊“資料工具”組中的“資料驗證”按鈕，打開如圖3.35所示的“資料驗證”對話框，在“允許”下拉清單中選擇“序列”，在“來源”下的文本框内輸入“=dm”，單擊“确定”按鈕，完成資料驗證設定。

1. 選中F3單元格，在編輯欄内輸入公式“=IF(LEN(C3)=4,VLOOKUP($C$3,kmdm,2,FALSE), (VLOOKUP(LEFT($C$3,4)+0,kmdm,2,FALSE)) & "-" & VLOOKUP($C$3,kmdm,2,FALSE))”，完成會計科目名稱的設定。

将年份資訊設定為2018，月份資訊設定為1，科目代碼設定為1001，最終顯示的科目代碼如圖3.37所示。

2．總分類賬資料

為總分類賬填充資料的具體操作步驟如下：

1. 選中C2單元格，在編輯欄内輸入公式“=科目餘額表!B2”，選中F2單元格，在編輯欄内輸入公式“=科目餘額表!G2”，選中A4單元格，在編輯欄内輸入公式“=C2&"年"”，完成年份和月份資訊的設定。
2. 選中A6單元格，在編輯欄内輸入公式“=$F$2”，按Enter鍵确認，然後将A6單元格的内容向下拖曳到A7和A8單元格内，完成月份資訊的填制。
3. 在B6單元格内輸入數值1，選中B7單元格，在編輯欄内輸入公式“=DAY(DATE($C$2, $F$2+1,0))”，計算指定月份的天數，選中B8單元格，在編輯欄内輸入公式“=B7”，完成日資訊的填制。

1. 在C6單元格内輸入“期初餘額”，在C7單元格内輸入“本月發生額”，在C8單元格内輸入“本月合計”，完成摘要内容的設定。
2. 選中D6單元格，在編輯欄内輸入公式“=IF(LEN(C3)=6,SUMIFS(kmye[列1],kmye[科目代碼],C3),SUM(IF(LEFT(kmye[科目代碼],4)+0=LEFT($C$3,4)+0,kmye[列1],0)))”，同時按鍵盤上的Ctrl+Shift+Enter鍵，完成借金額期初數的設定。

1. 選中E6單元格，在編輯欄内輸入公式“=IF(LEN(C3)=6,SUMIFS(kmye[列2],kmye[科目代碼],C3),SUM(IF(LEFT(kmye[科目代碼],4)+0=LEFT($C$3,4)+0,kmye[列2],0)))”，同時按鍵盤上的Ctrl+Shift+Enter鍵，完成貸金額期初數的設定。
2. 選中F6單元格，在編輯欄内輸入公式“=IF(SUMIF(kmdm[科目代碼],$C$3,kmdm[性質])>0,"借","貸")”，完成借貸方向的設定。
3. 選中G6單元格，在編輯欄内輸入公式“=IF(F6="借",D6-E6,E6-D6)”，完成期初餘額的設定。
4. 選中D7單元格，在編輯欄内輸入公式“=IF(LEN(C3)=6,SUMIFS(kmye[列3],kmye[科目代碼],C3),SUM(IF(LEFT(kmye[科目代碼],4)+0=LEFT($C$3,4)+0,kmye[列3],0)))”，同時按鍵盤上的Ctrl+Shift+Enter鍵，完成指定月借方發生額的設定。
5. 選中E7單元格，在編輯欄内輸入公式“=IF(LEN(C3)=6,SUMIFS(kmye[列4],kmye[科目代碼],C3),SUM(IF(LEFT(kmye[科目代碼],4)+0=LEFT($C$3,4)+0,kmye[列4],0)))”，同時按鍵盤上的Ctrl+Shift+Enter鍵，完成指定月貸方發生額的設定。
6. 選中G7單元格，在編輯欄内輸入公式“=IF($F$6="借",D7-E7,E7-D7)”，完成當月借方發生額差額的計算。
7. 選中F7單元格，在編輯欄内輸入公式“=IF(AND($F$6="借",G7<0),"貸",IF(AND($F$6="貸",G7<0),"借",IF(G7=0,"平",$F$6)))”，完成借貸方向的設定。
8. 選中D8單元格，在編輯欄内輸入公式“=SUM(D6:D7)”，完成本月借方合計數的計算。
9. 選中E8單元格，在編輯欄内輸入公式“=SUM(E6:E7)”，完成本月貸方合計數的計算。
10. 選中G8單元格，在編輯欄内輸入公式“=IF($F$6="借",D8-E8,E8-D8)”，完成指定科目當月餘額的計算。
11. 選中F8單元格，在編輯欄内輸入公式“=IF(AND($F$6="借",G8<0),"貸",IF(AND($F$6="貸",G8<0),"借",IF(G8=0,"平",$F$6)))”，完成指定科目餘額借貸方向的設定。

當使用者輸入不同的科目代碼的時候會顯示不同的内容：

（1）輸入科目代碼為1001時，長度為4且為一個明細科目，顯示的内容如圖3.38所示。

（2）輸入科目代碼為100201時，長度為6且為一個明細科目，顯示的内容如圖3.39所示。

（3）輸入科目代碼為1002時，長度為4且不為一個明細科目，顯示的内容如圖3.40所示。

3．優化顯示

指定科目餘額産生方向為借方，但是當月發生的借方和貸方差額卻出現在貸方，那麼在餘額一欄内就會産生負數，這通常不符合使用者檢視的習慣。為此，需要将G列中内容的顯示方式進行修改，具體的設定方法如下：

1. 選中G7:G8單元格，右擊，執行“設定單元格格式”指令，打開如圖3.41所示的“設定單元格格式”對話框。選擇“數字”頁籤，在分類中選擇“自定義”，在類型中輸入“#,##0.00;#,##0.00”，單擊“确定”按鈕，完成數值顯示方式的設定。

1. 選中整張工作表，右擊，執行“設定單元格格式”指令，選中“保護”頁籤，保持“鎖定”被勾選，單擊“确定”按鈕，完成全部單元格的鎖定操作。
2. 選中C3單元格，右擊，執行“設定單元格格式”指令，選中“保護”頁籤，保持“鎖定”不被勾選，單擊“确定”按鈕，完成可編輯單元格的指定操作。
3. 單擊“開始”頁籤，執行“單元格”組内“格式”下的“保護工作表”指令，打開“保護工作表”對話框，設定一個合适的密碼，完成工作表的保護操作。

   

根據公司發生的業務，編制公司1月份的總分類賬。

使用者從總分類賬中得到的僅僅是某個會計科目在指定期間内總的發生額，但是該科目到底發生了什麼業務并不明确，這就要求使用明細分類賬來顯示具體的業務資訊。

明細分類賬是根據明細分類賬戶進行分類登記的賬簿，是根據機關開展經濟管理的需要對經濟業務的詳細内容進行的核算，是對總分類賬進行的補充反映。

從技術角度來說，建立明細分類賬需要使用者指定的資訊如下。

• 會計科目：使用者查詢的是哪個明細分類賬戶的資訊。
• 日期資訊：使用者查詢的是哪個月份的資訊。
• 期初餘額：明細分類賬期初餘額資訊，有了期初餘額和本月發生資訊，就可以計算期末的科目餘額。

會計科目是由使用者指定的，月份資訊可以由使用者在科目餘額表中指定，期初餘額資料通過公式從科目餘額表中獲得。具體的業務資訊資料來自于“憑證庫”工作表，為此需要通過Microsoft Query元件建立查詢來獲得相關的資料。期末的餘額是通過期初餘額和本期借貸方發生額來獲得的。

掌握利用明細分類賬的設計方法。

了解Microsoft Query的用法，掌握宏的錄制和指定。

明細分類賬要實作如下幾個功能：

（1）列出指定月份和科目代碼的期初數。

（2）列出本期指定會計科目的發生記錄，并将這些記錄填到表中。

（3）計算本期期末的借方和貸方餘額。

1．明細分類賬界面

明細分類賬的界面資訊包括需要指定的資訊和要顯示内容的表頭資訊，界面設計的具體操作過程如下：

1. 建立一張工作表，将工作表名稱更改為“明細分類賬”。選中A1~H1單元格，将該單元格區域合并并居中，設定“字号”為24号，在編輯欄内輸入公式“=F3&"明細分類賬"”，完成明細分類賬标題的設定。
2. 從A2單元格開始設定如圖3.43所示的内容。

1. 選中C3單元格，選中“資料”頁籤，單擊“資料工具”組中的“資料驗證”按鈕，打開如圖3.44所示的“資料驗證”對話框，将“允許”下拉清單中選擇“序列”，在“來源”下的文本框内輸入“=dm”，單擊“确定”按鈕，完成資料有效性設定。

1. 選中F2單元格，在編輯欄内輸入“=科目餘額表!G2”，選中A6單元格，在編輯欄内輸入公式“=F2”，完成月份的設定。
2. 在B6單元格中輸入數值1，在D6單元格内，摘要内容輸入“期初餘額”。

1. 選中E6單元格，在編輯欄内輸入公式“=SUMIF(kmye[科目代碼],$C$3,kmye[列1])”，完成期初借方金額的設定。

1. 選中F6單元格，在編輯欄内輸入公式“=SUMIF(kmye[科目名稱],$C$3,kmye[列2])”，完成期初貸方金額的設定。
2. 選中G6單元格，在編輯欄内輸入公式“=IF(SUMIF(kmdm[科目代碼],$C$3,kmdm[性質])>0,"借","貸")”，完成借貸方向的設定。

1. 選中H6單元格，在編輯欄内輸入公式“=IF(G6="借",E6-F6,F6-E6)”，完成期初餘額的設定。

2．導入業務資訊

明細分類賬中顯示的主體是業務内容及金額，是從憑證庫中導入的，導入指定月份的業務資訊的操作步驟如下：

1. 選中“資料”頁籤，在“擷取外部資料”組中執行“自其他來源”中的“來自Microsoft Query”指令，打開如圖3.45所示的“選擇資料源”對話框。
2. 在“選擇資料源”對話框中選擇資料庫類型為“Excel Files*”，單擊“确定”按鈕，打開如圖3.46所示的“選擇工作簿”對話框。

   

1. 選擇本工作簿所在的路徑，單擊“确定”按鈕，打開如圖3.47所示的“查詢向導-選擇列”對話框，單擊“可用的表和列”中“憑證庫”前的“+”号，展開該表所包含的列，選中要顯示的列為月、日、憑證号、摘要、借金額和貸金額。

1. 單擊“下一步”按鈕，完成要顯示的列的選擇，進入“查詢向導-篩選資料”對話框，直接單擊“下一步”按鈕，跳過篩選的過程，打開如圖3.48所示的“查詢向導-排序順序”對話框，“主要關鍵字”設定為“憑證号”，完成排序的操作。

1. 單擊“下一步”按鈕，打開“查詢向導-完成”對話框，在“請确定下一步的動作”中，選中“在Microsoft Query中檢視資料或編輯查詢”，單擊“完成”按鈕，完成查詢向導的操作，進入Microsoft Query界面。
2. 在Microsoft Query視窗中，執行“條件”菜單下的“添加條件”指令，打開如圖3.49所示的“添加條件”對話框，選擇“字段”為“月”，“指定值”為“[yue]”，完成第一個參數條件的設定。

1. 單擊“添加”按鈕，打開如圖3.50所示的“輸入參數值”對話框，此時不需要設定任何參數，單擊“确定”按鈕，完成第一個參數的設定。

1. 在“添加條件”對話框中，選擇“字段”為“科目代碼”，“指定值”為“[kmdm]”，如圖3.51所示，完成第二個參數條件的設定，單擊“添加”按鈕，打開 “輸入參數值”對話框，此時不需要設定任何參數，單擊“确定”按鈕，完成第二個參數的設定。再次單擊“添加條件”對話框中的“關閉”按鈕，完成所有參數條件的設定。
2. 執行Microsoft Query視窗中檔案菜單下的“将資料傳回Microsoft Excel”，打開如圖3.52所示的“導入資料”對話框，将資料放置的位置設定為“明細分類賬”的A7單元格，完成資料導入的操作。

   

1. 單擊“導入資料”對話框中的“确定”按鈕後，打開如圖3.53所示的“輸入參數值”對話框，在“yue”下的文本框中，選擇工作表中的F2單元格，并且勾選“在以後的重新整理中使用該值或該引用”和“當單元格值更改時自動重新整理”複選框。同樣，在打開的指定參數kmdm中指定科目代碼連結的單元格為C3單元格，完成參數的指定操作。

1. 選中F7貸金額字段所在的單元格，右擊，執行“插入”下的“在右側插入表列”指令，插入一個新列，列名稱自動設定為“列1”。選中“列1”所在的單元格，右擊，再次執行“插入”下的“在右側插入表列”指令，插入一個新列，列名稱為“列2”，完成列的插入操作。
2. 選中A7單元格，選擇“設計”頁籤，在“表樣式”組内選擇“表樣式淺色8”，完成表樣式的選擇。
3. 選擇“設計”頁籤，在“屬性”組内，将表名稱更改為“flz”。
4. 選中第7行，右擊，執行隐藏指令，完成表頭行的隐藏操作。操作完成後的明細分類賬如圖3.54所示。

   

3．添加彙總行

為了了解指定月份的借方發生額合計和貸方發生額合計數，需要為表對象添加一個彙總行，具體的操作步驟如下：

1. 選中A8單元格，選中“設計”頁籤，執行“表樣式選項”組内的“彙總行”指令，完成添加“彙總行”的指令。
2. 删除“月”字段所在列中的“彙總”字樣，在彙總行中，在“摘要”字段所在的單元格内輸入文字“本月合計”。
3. 在“彙總”行中，選中“借金額”所在的列，在下拉清單中選擇“彙總方式”為“求和”，如圖3.55所示，完成本月借方發生額的計算。

1. 在“彙總”行中，選中“貸金額”所在的列，在下拉清單中選擇彙總方式為“求和”，完成本月貸方發生額的計算。
2. 在“彙總”行中，選中“列1”所在的列，在編輯欄中輸入公式“=G6”，完成借貸方向的設定。
3. 在“彙總”行中，選中“列2”所在的列，在編輯欄内輸入公式“=IF($G$6="借",$H$6+flz[[#彙總],[借金額]]-flz[[#彙總],[貸金額]],H6+flz[[#彙總],[貸金額]]-flz[[#彙總],[借金額]])”，完成期末餘額的計算。

1. 調整各列的寬度到合适的位置，選中A7單元格，選擇“設計”頁籤，單擊“外部表資料”組内的屬性按鈕，打開如圖3.56所示的“外部資料屬性”對話框，保持不勾選“調整列寬”複選框，單擊“确定”按鈕，完成列寬格式的設定。

通過上述操作，最終的表樣式如圖3.57所示。

将C3單元格的科目代碼更改為100201，就會顯示工行的明細分類賬，如圖3.58所示。

與總分類賬類似，要保證資料顯示的結果總是最新的，就需要執行重新整理操作，為明細分類賬添加重新整理功能的具體操作過程如下：

1. 選中“明細分類賬”工作表，選中“開發工具”頁籤，執行“控件”組中“插入”下的“表單控件”，選擇按鈕控件，在H2:H3單元格内拖曳一個指令按鈕，如圖3.59所示，将指令按鈕内的文字更改為“資料重新整理”，完成指令按鈕的放置。
2. 選擇“開發工具”頁籤，執行“代碼”組内的“錄制宏”指令，打開“錄制宏”對話框，将宏名稱更改為“重新整理明細分類賬”，單擊“确定”按鈕，開始錄制宏。
3. 選中A8單元格，單擊“資料”頁籤，執行“連接配接”組内的“全部重新整理”指令，完成資料重新整理操作。

1. 單擊在“開發工具”頁籤中“代碼組”内的“停止錄制”指令，完成宏的錄制。
2. 在“重新整理資料”按鈕上右擊，執行“指定宏”指令，打開“指定宏”對話框，選擇“重新整理明細分類賬”項，單擊“确定”按鈕，完成為按鈕指定宏的操作。

根據公司發生的業務，編制公司1月份的明細分類賬。

使用者為自己設計的軟體添加了諸多功能後，可能會碰到一個問題，就是如何快速找到自己所需的功能。在一般的軟體中是通過添加菜單、導航欄或者添加連結來解決的。由于我們設計的财務管理系統并沒有采用程式設計的方式，是以無法自定義菜單和頁籤。使用者要達到相同的目的，可以在工作簿中添加一個導航頁面，利用工作表連結的方法快速到達指定的頁面。

科目彙總表、科目餘額表、總分類賬和明細分類賬都涉及指定月份的功能，而科目餘額表又都為總分類賬和明細分類賬提供了期初的餘額，這就要求上述表格的資料要保持一緻性。是以，可以在導航頁面上設定月份資訊，而其他表格的月份資訊都和導航頁面中指定的月份資訊一緻，進而保證不至于因為月份不同步而發生期初餘額引用資料上的錯誤。

掌握利用導航頁面來完成在各個頁面之間的跳轉。

了解連結的方法。

導航頁面要實作的功能包括：

（1）通過導航快速到達指定的頁面。

（2）科目彙總表、科目餘額表、總分類賬和明細分類賬的月份資訊與導航頁面的月份資訊一緻。

1．導航頁面

導航頁面是通過超連結來完成的，建立該頁面的具體操作步驟如下：

1. 建立一張新的工作表，将工作表的名稱更改為“首頁”。
2. 單擊“檔案”按鈕，單擊“Excel 選項”按鈕，打開“Excel 選項”對話框，在對話框左側選擇“進階”，在右側的“此工作表的顯示選項”下保持“顯示行和列标題”和“顯示網格線”複選框不被選中，如圖3.60所示，單擊“确定”按鈕，完成去除網格線和行列标題的操作。

1. 選擇“插入”頁籤，執行“插圖”組中的“聯機圖檔”指令，打開如圖3.61所示的搜尋視窗，在搜尋範圍内輸入“計算機”，按Enter鍵确認後，選擇一張合适的圖檔，單擊“插入”按鈕，完成圖檔的插入操作。

1. 将圖檔放置在合适的位置，拉升到合适的大小，并在圖檔下端輸入文字“賬務處理”，完成後的結果如圖3.62所示。

2．設定導航功能

1. 在工作表的H8單元格内輸入“憑證處理”。
2. 在工作表的I8單元格内輸入“憑證錄入”，選中“插入”頁籤，執行“連結”組内的“超連結”指令，打開如圖3.63所示的“編輯超連結”對話框，左側選擇連結到的位置為“本文檔中的位置”，右側指定文檔中選擇的位置為“憑證輸入”工作表，單擊“确定”按鈕，完成憑證輸傳入連結接的指定。

1. 在工作表的J8單元格内輸入“憑證編輯”，選中“插入”頁籤，執行“連結”組内的“超連結”指令，左側選擇連結到的位置為“本文檔中的位置”，右側指定文檔中選擇的位置為“憑證編輯”工作表，單擊“确定”按鈕，完成憑證編輯連結的指定。
2. 在工作表的K8單元格内輸入“稽核列印”，選中“插入”頁籤，執行“連結”組内的“超連結”指令，左側選擇連結到的位置為“本文檔中的位置”，右側指定文檔中選擇的位置為“憑證稽核與列印”工作表，單擊“确定”按鈕，完成憑證稽核與列印連結的指定。
3. 在G10中輸入“查詢月份”，選中H10單元格，設定邊框為雙下畫線，選中“資料”頁籤，執行“資料”組内的“資料驗證”指令，打開如圖3.64所示的“資料驗證”對話框，在對話框的“設定”頁籤的“允許”下選擇“序列”，序列的内容手工填入“1,2,3,4,5,6,7,8,9,10,11,12”。選中“出錯警告”頁籤，标題為“月份錯誤”，錯誤資訊指定為“指定的月份資訊錯誤”，單擊“确定”按鈕，完成月份的指定。

1. 在工作表的I10單元格内輸入“科目餘額表”，選中“插入”頁籤，執行“連結”組内的“超連結”指令，左側選擇連結到的位置為“本文檔中的位置”，右側指定文檔中選擇的位置為“科目餘額表”工作表，單擊“确定”按鈕，完成科目餘額表連結的指定。
2. 在工作表的J10單元格内輸入“總分類賬”，選中“插入”頁籤，執行“連結”組内的“超連結”指令，左側選擇連結到的位置為“本文檔中的位置”，右側指定文檔中選擇的位置為“總分類賬”工作表，單擊“确定”按鈕，完成總分類賬連結的指定。
3. 在工作表的K10單元格内輸入“明細分類賬”，選中“插入”頁籤，執行“連結”組内的“超連結”指令，左側選擇連結到的位置為“本文檔中的位置”，右側指定文檔中選擇的位置為“明細分類賬”工作表，單擊“确定”按鈕，完成明細分類賬連結的指定。
4. 選中“科目彙總表”，選中D2單元格，在編輯欄内輸入公式“=首頁!H10”，完成查詢月份的指定。
5. 選中“科目餘額表”工作表，選中G2單元格，在編輯欄内輸入公式“=首頁!H10”，完成查詢月份的指定。
6. 選中“總分類賬”工作表，選中F2單元格，在編輯欄内輸入公式“=首頁!H10”，完成查詢月份的指定。

7. 選中“明細分類賬”工作表，選中F2單元格，在編輯欄内輸入公式“=首頁!H10”，完成查詢月份的指定。

   建立完成後的導航頁如圖3.65所示。

建立一個導航頁面，通過單擊連結能夠快速到達憑證輸入、憑證修改與删除、憑證稽核與列印頁面。輸入指定的月份資訊能夠快速生成科目餘額表、總分類賬和明細分類賬。