分旬科目汇总表在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 第一旬科目汇总表(部分)