点击查看第一章
点击查看第二章

第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和回车键，完成数组公式的输入。

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”，按回车键确认，然后将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+回车键，完成借金额期初数的设置。

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+回车键，完成贷金额期初数的设置。
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+回车键，完成指定月借方发生额的设置。
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+回车键，完成指定月贷方发生额的设置。
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. 选中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))”，完成会计科目名称的设置。

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所示。

4．获取最新数据

与总分类账类似，要保证数据显示的结果总是最新的，就需要执行刷新操作，为明细分类账添加刷新功能的具体操作过程如下：

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所示。

创建一个导航页面，通过单击链接能够快速到达凭证输入、凭证修改与删除、凭证审核与打印页面。输入指定的月份信息能够快速生成科目余额表、总分类账和明细分类账。