分旬科目匯總表在Excel中的模型構建

□衣

(山東經貿職業學院 山東濰坊261011）

一、建立會計科目表

(一）設置包含明細科目的會計科目表

圖1 會計科目表(部分)

(二）定義動態范圍名稱[會計科目表]

區域為：OFFSET(會計科目表!$A$2:$C$2,0,0,COUNTA(會計科目表!$A:$A)-1)

二、建立會計憑證表

圖2 會計憑證表(部分)

圖3 數據透視表布局設置

(一）定義總賬科目及明細科目公式

G2=IF(LEN(F2)=0,"",VLOOKUP(F2,會計科目表,2,0))

H2=IF(LEN(F3)<=4,"",VLOOKUP(F2,會計科目表,3,0))

下同，可選擇G2:H2，用填充控制點下拉進行復制。

(二）定義動態的范圍名稱[會計憑證表]

區域為：OFFSET(會計憑證表!A1:J1,0,0,COUNTA(會計憑證表!A:A))

三、生成科目匯總表

執行菜單[數據]|[數據透視表數據透視圖]命令，打開數據透視表和數據透視圖向導——3步驟之1，直接單擊下一步；在數據透視表和數據透視圖向導——3步驟之2中，執行菜單[插

入]|[名稱]|[粘貼]命令，選擇前面定義好的范圍名稱[會計憑證表]。單擊下一步；在數據透視表和數據透視圖向導——3步驟之3中，單擊[布局]按鈕，設置布局如圖3，單擊 [確定]按鈕。（注:本文采用的Excel是2003版,其他版本類同)

再經過布局調整，將[數據]拖到[匯總]上，隱藏[科目編碼]字段的分類匯總，生成下頁圖4“科目匯總表1”。圖4“科目匯總表1”是按有發生額的末級科目進行的分類匯總，有的[總賬科目]被分成多行，如原材料等，不能滿足登記總賬的要求。

如將圖3“數據透視表和數據透視圖向導——布局”中的[科目編碼]拖離行字段，則生成數據透視表“科目匯總表2”見下頁圖5。圖5“科目匯總表2”按[總賬科目]對本期發生額進行了正確的匯總，但不符合科目編碼次序。

要實現按[科目編碼]對[總賬科目]進行排序，筆者經過若干次嘗試，發現先添加按[科目編碼]對[總賬科目]排序的新序列后，再重新生成透視表，則科目匯總表會自動按已定義好的新序列進行排序，這是一條方便、快捷的可行方案。方法如下：

(一）建立總賬科目表

在“會計科目表”工作表中，單擊數據清單內任一單元格，執行[數據]|[篩選]|[自動篩選]命令，單擊字段名稱[科目編碼]下拉三角按鈕，選擇“自定義”命令，打開[自定義自動篩選方式]窗口，設置科目編碼等于“????”（注意這里輸入的是英文半角狀態下的“????”），單擊[確定]按鈕，即可篩選出編碼長度是4的[總賬科目]相關記錄。復制相關記錄到新建的“總賬科目表”工作表下，格式見下頁圖6。

定義動態范圍名稱[總賬科目表]

區域為：offset(總賬科目表!B2,0,0,counta(總賬科目表!$B:$B)-1)

(二）自定義序列

圖4 科目匯總表1(部分)

圖5 科目匯總表2(部分)

圖6 總賬科目表(部分)

執行[工具]|[選項]命令，單擊[自定義序列]選項卡，在圖7右下方的矩形框內輸入范圍名稱[總賬科目表]，單擊 [導入]按鈕，即添加了按[科目編碼]對[總賬科目]排序的新序列。

(三）重新生成科目匯總表

刪除圖5“科目匯總表2”，按其生成數據透視表的相同方法再執行一次，即可自動得到排好序的“科目匯總表 3”，見圖 8。

(四）生成分旬科目匯總表

在圖8“科目匯總表3”中，選擇行字段[日]并右擊，選擇[組及顯示明細數據]|[組合]命令，選擇默認的步長為10，單擊[確定]按鈕，即可生成“分旬科目匯總表”，見圖9。

圖7 根據范圍名稱定義自定義序列

在圖9“分旬科目匯總表”中，單擊頁字段[年]下拉列表，選擇[12]年，單擊頁字段[月]下拉列表，選擇[01]月；單擊行字段[日]下拉列表，選擇 [1-10]。即可得到2012年1月 “第一旬科目匯總表”，見圖 10。說明：正確生成的分旬科目匯總表的要點為：第一，生成數據透視表的數據源（本例指范圍名稱[會計憑證表]）內不能有空白行；第二，行字段[日]格式必須一致，不能有空白項，可設置為 “數字”或“日期”等格式，但不允許是“文本”格式。如果單位業務較少，一個月只進行兩次科目匯總，可將步長設為15，或根據單位需要進行其他步長設置即可。如果建立的會計科目體系只有總賬科目，可在第三步圖4的基礎上，參照第四步圖9、圖10的步驟生成分旬科目匯總表，即不需自定義總賬科目序列。

綜上所述，本文對手工會計核算中的科目匯總表編制進行了一系列探索，完成了基于Excel的自動按[科目編碼]對[總賬科目]排序的分旬科目匯總表模型構建。在手工會計日常核算中，只要將日常發生的經濟業務及時錄入模型中的會計憑證表，即可根據需要隨時、快捷、準確、自動地生成分旬科目匯總表。該模型還進行了相關動態范圍名稱的設置，當增、刪、修改會計科目或會計憑證時，只要稍做調整，并刷新數據透視表即可輕松更新，從而將會計人員從繁重的手工作業中解脫出來，以期為會計實際工作或會計實踐教學提供參考借鑒。

圖8 科目匯總表3(部分)

圖9 分旬科目匯總表(部分)

圖10 第一旬科目匯總表(部分)