我的书签
添加书签
移除书签20.10 运用数据透视表制作会计三栏账
制作会计三栏账，是会计工作电算化中的一项重要工作，运用数据透视表也能实现这一目标。
图20-65、图20-66和图20-67展示的是“年初（期初）科目余额明细表”、“会计凭证清单”和“科目明细表”等会计资料，如果用户希望根据这些资料制作通用的会计三栏账，请参照以下步骤。
图20-65 期初余额表
图20-66 会计凭证清单
图20-67 科目表
步 骤1
将“期初余额”和“凭证清单”两个工作表中的数据分别定义名称“data0”和“data1”，如图20-68所示。
期初余额：data0=期初余额！$A$4：$J$17
凭证清单：data2=凭证清单！$A$6：$J$665
图20-68 定义名称
步 骤2
为了动态分析“期初余额”和“凭证清单”工作表中的数据，分别将两个工作表的数据区域设置成“表”，并将“凭证清单”工作表中的“表”的表样式设置为“无”，保留数据原有的格式。
步 骤3
插入一张新工作表，并命名为“三栏账”，运用导入外部数据的方法打开【连接属性】对话框，在【定义】选项卡的【命令文本】框中输入如下SQL语句，如图20-69所示。
select日期，day（日期）as日，凭证号，摘要，科目代码l，一级科目，科目代码2，二级科目，借方，贷方，序号from [data0]
union all
select日期，day（日期）as日，凭证号，摘要，科目代码1，一级科目，科目代码2，二级科目，借方，贷方，序号from [data1]
思路解析：
两次使用select语句，分别从“dataO”和“data1”两个定义的数据区域中选取“日期”、“凭证号”、“摘要”、“科目代码1”、“一级科目”、“科目代码2”、“二级科目”、“借方”、“贷方”、“序号”等字段。
day(日期）as日，用于使用day函数取得“日期”字段中的“日”。
用union all将2个select语句连接起来，进行数据合并。
图20-69 输入SQL语句
在“三栏账”工作表中创建数据透视表，并对“日期”按“年”和“月”分组，初步创建的数据透视表，如图20-70所示。
图20-70 初步创建的数据透视表
步 骤5
定义名称code2，用于根据C6单元格的数据透视表“报表筛选”字段的选择项，动态获取“二级科目”名称：
code2=OFFSET（科目表！$B$1，MATCH（三栏账！$C$6，科目表！$B$1：$B$998，）-1，2，COUNTIF（科目表！$B$4：$B$998，三栏账！$C$6））
步 骤6
在“三栏账”工作表的H6单元格中输入“二级科目”字样，合并I6:J6单元格区域，并设置如图20-71所示的数据有效性。
图20-71 设置数据有效性
步 骤7
在I11单元格中输入公式，并向下填充足够的行数（本例填充至I236行），取余额的值用于设置余额方向，公式如下：
=IF（J11=“”，“”,ROUND(J11,2))
步 骤8
在J11单元格中输入公式，并向下填充足够的行数（本例填充至J236行），用于计算三栏账的余额：
=IF(SUM(G11:H11)<>0,IF(ISNUMBER(FIND(“总”，C11)），N(J10),ROUND(N(J10)+G11- H11,2)),“”)
在J11:J236数据区域设置自定义单元格格式，用于将数字设置为“会计专用”格式，并将负数用正数显示。
自定义代码：#，##0.00）;#，##0.00）
步 骤10
在111：I236数据区域设置自定义单元格格式，用于设置余额方向，如图20-72所示。
自定义代码：[>0]”借”；[<0]”贷”；“平”
图20-72 设置条件格式
步 骤11
按下<Alt+F11>组合键，打开VBE窗口，插入模块，输入VBA代码，用于将16单元格选择的数据传递给数据透视表“二级科目”报表筛选字段，如图20-73所示。
图20-73 输入VBA代码
代码如下：
将文件另存为”Excel启用宏的工作簿”类型的文件。
步 骤13
在“Excel启用宏的工作簿”文件的“三栏账”工作表中插入图片，如“七角星”，并为图片指定创建的宏select2，如图20-74所示。
图20-74 插入图片并指定宏
步 骤14
分别对B11：B236、I11：I236和J11：J236单元格区域设置“条件格式”，如图20-75所示。
图20-75 设置B、I、J列数据条件格式
设置条件格式的作用：
(1)动态显示出单元格边框。
(2)将各月汇总行所对应的单元格动态显示为“绿色”边框和“淡蓝色”底色。
步 骤15
对数据透视表作进一步美化设置，最终生成的结果如图20-76所示。
图20-76 最终完成的会计三栏账
步 骤16
当在“一级科目”字段的下拉列表中选择“银行存款”字段项，“二级科目”相应的单元格下拉列表中，只显示银行存款相应的子目选项。当选定具体子目后，单击“生成账簿”按钮，即可生成相应科目及其对应的明细科目三栏账，显示效果如图20-77所示。
图20-77 使用三栏账
提示
如果用户希望生成“一级科目”项下所有“二级科目”的明细账，只需将15单元格的数据清空，再单击“生成账簿”按钮即可。
本例运用了多种Excel功能完成通用三栏账的制作，主要运用到的功能包括：
• 使用了定义名称和创建“表”功能保持数据的动态性。
• 使用SQL语句将“凭证清单”和“期初余额”两个数据表合并，并用数据透视表进行快速汇总。
• 使用数据有效性，将透视表中的报表筛选字段“一级科目”与“二级科目”进行联动。
• 运用函数公式设置账簿余额，摆脱了数据透视表自身的局限。
• 使用自定义单元格格式设置“余额”和“方向”字段。
• 使用单元格条件格式，设置表格边框、汇总行底色等。
