分旬科目匯總表在Excel中的模型構(gòu)建

□衣

(山東經(jīng)貿(mào)職業(yè)學(xué)院 山東濰坊261011）

一、建立會(huì)計(jì)科目表

(一）設(shè)置包含明細(xì)科目的會(huì)計(jì)科目表

圖1 會(huì)計(jì)科目表(部分)

(二）定義動(dòng)態(tài)范圍名稱[會(huì)計(jì)科目表]

區(qū)域?yàn)椋篛FFSET(會(huì)計(jì)科目表!$A$2:$C$2,0,0,COUNTA(會(huì)計(jì)科目表!$A:$A)-1)

二、建立會(huì)計(jì)憑證表

圖2 會(huì)計(jì)憑證表(部分)

圖3 數(shù)據(jù)透視表布局設(shè)置

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

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

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

下同，可選擇G2:H2，用填充控制點(diǎn)下拉進(jìn)行復(fù)制。

(二）定義動(dòng)態(tài)的范圍名稱[會(huì)計(jì)憑證表]

區(qū)域?yàn)椋篛FFSET(會(huì)計(jì)憑證表!A1:J1,0,0,COUNTA(會(huì)計(jì)憑證表!A:A))

三、生成科目匯總表

執(zhí)行菜單[數(shù)據(jù)]|[數(shù)據(jù)透視表數(shù)據(jù)透視圖]命令，打開(kāi)數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之1，直接單擊下一步；在數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之2中，執(zhí)行菜單[插

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

再經(jīng)過(guò)布局調(diào)整，將[數(shù)據(jù)]拖到[匯總]上，隱藏[科目編碼]字段的分類匯總，生成下頁(yè)圖4“科目匯總表1”。圖4“科目匯總表1”是按有發(fā)生額的末級(jí)科目進(jìn)行的分類匯總，有的[總賬科目]被分成多行，如原材料等，不能滿足登記總賬的要求。

如將圖3“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А季帧敝械腫科目編碼]拖離行字段，則生成數(shù)據(jù)透視表“科目匯總表2”見(jiàn)下頁(yè)圖5。圖5“科目匯總表2”按[總賬科目]對(duì)本期發(fā)生額進(jìn)行了正確的匯總，但不符合科目編碼次序。

要實(shí)現(xiàn)按[科目編碼]對(duì)[總賬科目]進(jìn)行排序，筆者經(jīng)過(guò)若干次嘗試，發(fā)現(xiàn)先添加按[科目編碼]對(duì)[總賬科目]排序的新序列后，再重新生成透視表，則科目匯總表會(huì)自動(dòng)按已定義好的新序列進(jìn)行排序，這是一條方便、快捷的可行方案。方法如下：

(一）建立總賬科目表

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

定義動(dòng)態(tài)范圍名稱[總賬科目表]

區(qū)域?yàn)椋簅ffset(總賬科目表!B2,0,0,counta(總賬科目表!$B:$B)-1)

(二）自定義序列

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

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

圖6 總賬科目表(部分)

執(zhí)行[工具]|[選項(xiàng)]命令，單擊[自定義序列]選項(xiàng)卡，在圖7右下方的矩形框內(nèi)輸入范圍名稱[總賬科目表]，單擊 [導(dǎo)入]按鈕，即添加了按[科目編碼]對(duì)[總賬科目]排序的新序列。

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

刪除圖5“科目匯總表2”，按其生成數(shù)據(jù)透視表的相同方法再執(zhí)行一次，即可自動(dòng)得到排好序的“科目匯總表 3”，見(jiàn)圖 8。

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

在圖8“科目匯總表3”中，選擇行字段[日]并右擊，選擇[組及顯示明細(xì)數(shù)據(jù)]|[組合]命令，選擇默認(rèn)的步長(zhǎng)為10，單擊[確定]按鈕，即可生成“分旬科目匯總表”，見(jiàn)圖9。

圖7 根據(jù)范圍名稱定義自定義序列

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

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

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

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

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