点击查看第二章
点击查看第三章
Excel在会计和财务管理中的应用
第4版

杨维忠 庄 君 黄国芬 编著

第1章 日记账

日记账包括现金日记账和银行日记账，是由出纳人员（资金会计）按照业务流程发生的时间顺序逐笔登记，逐日反映库存现金或者银行账户存款的收支余的情况。由于现金日记账和银行日记账的记账方式完全相同，只是体现的资金类型不同，因此本章仅以现金日记账为例介绍通过Excel设计日记账的方法：一种是通过表对象和数据透视表来完成设计；另一种是通过函数和公式的设定来完成现金日记账的功能。通过本章的学习，可以了解利用Excel进行财务设计的思路和方法。

现金日记账是以天为顺序，逐日反映库存现金的收支余情况，具有结构简单的特点。在简易的现金日记账中，要实现预定的功能，就需要了解以下基本知识：

• 表对象（ListObject）
• 数据透视表

Excel本身不是一种结构严谨的数据库，只是它的格式和数据库中的数据表太像了，以至于用户常常期待可以通过Excel来实现某些只有数据库才具有的功能。
使用Excel进行数据分析是比较容易发生错误的，常常无法得到预想中的计算结果。原因通常是用户在单元格中输入的值具有比较大的随意性，没有按照规定的数据类型来输入数据。发生错误的时候，Excel一般不会给出提示，不太容易发现输入错误。为了更好地使用Excel做会计电算化设计，模拟数据库中的数据表，就需要有一种好的Excel工具——表对象。
表对象的英文名称为ListObject，因此常被称为表格、表对象或者表格对象，为了不至于引起混淆，本书统一按照表对象来称呼。使用表对象比单元格区域方便，例如表对象能够自动扩展表的大小，当用户添加新的记录的时候，新增的记录会自动添加到表对象内。表对象中数据的引用也比完全使用工作表简单，使用公式的时候只需要知道字段名称就可以引用数据，无须知道具体的行列位置。灵活使用表对象是今后用户进行复杂的财务系统设计必须掌握的一项技能。
Excel中进行数据的计算有两种基本的思路，一种是使用数据透视表；另一种是使用函数。数据透视表是Excel中功能最为强大的工具，具有分析数据快速简单的特点，尤其对于大数据量的分析计算，效率尤其高。本节的实验内容就是按照数据透视表的思路来对现金日记账的余额进行计算的。表对象和数据透视表的结合使用是利用Excel进行会计电算化设计的常用方法。

（一）实验目的
了解表对象，学会使用表对象来收集数据，使用数据透视表来分析数据。
（二）实验要求
了解现金日记账的手工记账方法，了解Excel中表对象和数据透视表的基本使用方法。

金盛公司在2018年1月1日后发生了如下与现金有关的业务：
（1）2018年1月1日转入上年余额2500元。
（2）2018年1月3日支付办公室电话费210元。
（3）2018年1月5日支付12月电费878元。
（4）2018年1月7日报销经理差旅费500元。
（5）2018年1月6日提现金130000元。
（6）2018年1月6日预付员工差旅费3600元。
（7）2018年1月6日支付工资120000元。
（8）2018年1月14日支付办公用品费用3752元。
（9）2018年1月15日订阅报刊2788元。
（10）2018年2月2日提现金5000元。
（11）2018年2月3日支付办公室电话费255元。
（12）2018年2月2日出售废旧电脑500元。
（13）2018年2月3日支付1月份电费900元。
（14）2018年2月7日提取现金125000元。
（15）2018年2月7日支付员工工资130000元。
通过为表对象设计字段，将数据直接输入表格中。最终的现金日记账要满足以下要求：
（1）所有输入的日期必须在2018年1月1日到2018年12月31日之间。
（2）借方发生额、贷方发生额和余额数据必须保留两位小数，并按照“会计专用”格式来显示。
（3）按日计算每日的余额。
（4）按月分析每月的收支情况。

1．数据输入设计

为了更好地进行数据分析，本实验设计了年、月、日、编号、内容摘要、借方发生额、贷方发生额以及余额8个字段，具体操作步骤如下：

1. 打开Excel，将工作簿保存为“简易日记账.xlsx”，双击Sheet1工作表标签，将Sheet1重命名为“现金日记账”。
2. 在A1~H1单元格区域内依次输入年、月、日、编号、内容摘要、借方发生额、贷方发生额以及余额8个字段，如图1.1所示。

数据库进行数据表设计时首先需要设计字段的数据类型，在Excel中也一样。Excel会根据用户输入的内容自动判断数据类型，但是这种自动判断并不一定准确，用户可以强制指定数据类型。
在上述字段中，年、月、日和编号是整数类型；内容摘要是文本类型；借方发生额、贷方发生额以及余额是小数类型，需要保留2位小数。值得注意的是，为某个字段指定数据类型并不能够完全防止用户的误操作，但可以对用户的输入起到一定程度的规范作用，具体的操作步骤如下：

1. 选中A:D列并右击，在快捷菜单中选择“设置单元格格式”命令。打开“设置单元格格式”对话框，单击“数字”标签。在“分类”中选择“数值”，选择“小数位数”为0，如图1.2所示。单击“确定”按钮，完成确定A列到D列的年、月、日以及编号的数据类型。
2. 选中E列并右击，在快捷菜单中选择“设置单元格格式”命令，如图1.3所示。从打开的“设置单元格格式”对话框中，单击“数字”标签。在“分类”中选择类型为“文本”，完成对E列内容摘要的数据类型的设定。
   

   

1. 选中F:H列并右击，在快捷菜单中选择“设置单元格格式”，从打开的“设置单元格格式”对话框中，单击“数字”标签。在“分类”中选择类型为“会计专用”，“小数位数”设置为2，“货币符号”设置为无，完成对F列到H列借贷方发生额以及余额的数据类型设定。

为了进一步减少输入错误，可以继续添加数据验证的功能，具体操作步骤如下：

1. 选中A列，单击“数据”选项卡，执行“数据工具”组内的“数据验证”命令，如图1.4所示。然后打开如图1.5所示的“数据验证”对话框，单击“设置”选项卡，在“验证条件”的“允许”下设置为“整数”，“数据”选择“介于”，“最小值”设置为2018，“最大值”设置为2019。
   

   

1. 选择“出错警告”选项卡，在“样式”中选择“停止”样式，在“标题”中输入“数据错误提示”，在“错误信息”下的文本框中输入“输入数据超出指定范围”，如图1.6所示。单击“确定”按钮，完成年字段数据验证的设置。

1. 选中B列，单击“数据”选项卡，执行“数据工具”组内的“数据验证”命令，打开“数据验证”对话框，单击“设置”选项卡，在验证条件的“允许”下设置为“整数”，“数据”选择“介于”，“最小值”设置为1，“最大值”设置为12。选择“出错警告”选项卡，在“样式”中选择“停止”样式，在“标题”中输入“数据错误提示”，在“错误信息”下的文本框中输入“输入数据超出指定范围”，单击“确定”按钮，完成月字段数据验证的设置。
2. 选中C列，单击“数据”选项卡，执行“数据工具”组内的“数据验证”命令，打开“数据验证”对话框，单击“设置”选项卡，在“验证条件”的“允许”下设置为“整数”，“数据”选择“介于”，“最小值”设置为1，“最大值”设置为31。选择“出错警告”选项卡，在“样式”中选择“停止”样式，在“标题”中输入“数据错误提示”，在“错误信息”下的文本框中输入“输入数据超出指定范围”，单击“确定”按钮，完成日字段数据验证的设置。
3. 选中F:G列，单击“数据”选项卡，执行“数据工具”组内的“数据验证”命令，打开如图1.7所示的“数据验证”对话框，单击“设置”选项卡，在有效性的“允许”下设置为“小数”，在“数据”下拉列表中选择“介于”。在“最小值”中输入0，在“最大值”中输入200000。选择“出错警告”选项卡，在“样式”中选择“停止”样式，在“标题”中输入“数据错误提示”，在“错误信息”下的文本框中输入“输入数据超出指定范围”，单击“确定”按钮，完成借方发生额以及贷方发生额字段数据验证的设置。

2．创建公式

在表对象中，编号和余额两个字段是通过公式计算获得的。编号是和行号相关的，为余额字段设计公式时，首笔记录的公式和后续公式是不一致的。在本例中，第1笔记录是从上年余额结转而来的，余额就等于借方的发生额。第一笔业务之后的其他记录余额的计算方式是“上笔余额+本笔借方发生额?本笔贷方发生额”。将所有的公式设置完成后，就可以将工作表的单元格区域转化为表对象了，具体的操作步骤如下：

1. 选中D2单元格，在编辑栏内输入公式“=ROW()-1”，完成编号字段的设置。

1. 选中H2单元格，在编辑栏内输入公式“=F2”，完成第1条记录余额的计算。
2. 从A2单元格开始，输入第1条记录的其余部分，输入的第1条记录如图1.8所示。
   

   

1. 选中A3单元格，在编辑栏内输入公式“=2018”，接着输入第2条记录，其中D3单元格中输入公式“=ROW()-1”，在H3单元格中输入公式“=H2+F3-G3”，输入的第2条记录如图1.9所示。
   

   

1. 选中A1单元格，选中“插入”选项卡，执行“表格”组中的“表格”命令，如图1.10所示。然后打开如图1.11所示的“创建表”对话框，勾选“表包含标题”复选框，单击“确定”按钮，将单元格区域转化为表。

1. 选中A1单元格，选择“设计”选项卡，在“表格样式”组中选择“白色，表样式浅色15”，如图1.12所示。

1. 单击“设计”选项卡，在“属性”组中，将表名称由默认的“表1”更改为“现金日记账”，完成表格名称的设定，如图1.13所示。

1. 继续输入剩余的记录，最终的结果如图1.14所示。
   

   

3．按月汇总

要对大量数据进行快速分析，数据透视表是最好的选择。数据透视表分析的对象是单元格区域或者表对象，它可以对数据进行分类汇总、添加计算字段以及刷新数据。按月汇总就是根据“月字段”进行汇总。数据透视表进行汇总是非常简单的，只需要简单地拖曳就能快速形成数据。字段被放置的位置可以是筛选器、列、行和值4个区域中。具体的操作步骤如下：

1. 选择“现金日记账”工作表的A1单元格，选择“插入”选项卡，执行“表格”组内的“数据透视表”命令，如图1.15所示，打开“创建数据透视表”对话框。

1. 选中“选择一个表或区域”单选按钮，已经可以自动识别要进行数据透视的区域，如果未能识别出，就在“表/区域”后输入“现金日记账”，选择放置数据透视表的位置为“新工作表”，如图1.16所示，完成数据透视表数据源的设置。

1. 按照上述步骤打开如图1.17所示的“数据透视表字段”对话框，将“月”字段和“日”字段拖曳到“行”中，将“借方发生额”和“贷方发生额”字段拖曳到“值”位置中，完成要显示的字段的设置。

1. 单击“数据透视表字段”对话框的“值”内的“计数项：借方发生额”字段，执行“值字段设置”命令，打开如图1.19所示的“值字段设置”对话框。在“计算类型”中选择“求和”，在自定义名称后的框内输入“借方发生额”。单击“值字段设置”对话框中的“数字格式”按钮，打开“设置单元格格式”对话框，在该对话框中单击“数字”标签，在分类中选择类型为“会计专用”，“小数位数”设置为2，“货币符号”设置为无，单击“确定”按钮，完成字段计算类型和数字的设置。

1. 在“数据透视表字段”对话框的“值”内的“计数项：贷方发生额”字段上单击，执行“值字段设置”命令，打开“值字段设置”对话框。在“计算类型”中选择“求和”，在自定义名称后的框内输入“贷方发生额”。单击“数字格式”按钮，打开“设置单元格格式”对话框，在该对话框中单击“数字”标签，在“分类”中选择类型为“会计专用”，“小数位数”设置为2，货币符号设置为无，单击“确定”按钮，完成字段计算类型和数字的设置。
2. 选中数据透视表中任意一个单元格，选择“分析”选项卡，执行“计算”组中的“字段、项目和集”下的“计算字段”命令，如图1.20所示。
   

   

打开如图1.21所示的“插入计算字段”对话框。

1. 将“名称”后的组合框中的“字段1”改为“余额总计”。
2. 将“公式”后的文本框中的“0”删除，选中“字段”列表框中的“借方发生额”，单击“插入字段”按钮，再输入减号“-”，然后选中“字段”列表框中的“贷方发生额”，单击“插入字段”按钮。单击“确定”按钮，完成插入计算字段的操作。

1. 选中E3单元格，将“求和项：余额总计”更改为“余额总计”。
2. 选中A3单元格，单击“布局”中的“报表布局”，选择布局类型为“以表格形式显示”，最终结果如图1.22所示。

1. 选中数据透视表中的任意一个单元格，单击“分析”选项卡，执行“数据”组内的“刷新”命令，完成数据刷新操作。

4．优化设计

通过上述操作，完成分析步骤就已经基本完成了数据透视表的分析功能，不过仍旧有改进的空间，包括对余额负数的显示处理等，具体的操作步骤如下：

1. 打开“现金日记账”工作表，选择H列，在“开始”选项卡中执行“样式”组内的“条件格式”命令，如图1.23所示。

选择“突出显示单元格规则”下的“小于”，打开如图1.24所示的“小于”对话框，在“为小于以下值的单元格设置格式”框中输入0，格式设置为“红色文本”，单击“确定”按钮，完成小于零的数值格式的设定。

1. 选中数据透视表中任意一个单元格，选择“分析”选项卡，执行“数据透视表”中的“选项”命令，如图1.25所示。

打开如图1.26所示的“数据透视表选项”对话框。

1. 单击“布局和格式”标签，取消勾选“更新时自动调整列宽”和“更新时保留单元格格式”复选框，完成对数据透视表刷新时不更改格式的设置。

金邦公司在2018年1月和2月发生了如下和现金有关的业务，请根据实验内容编制简易现金日记账：
（1）1月1日转入上年余额2200元。
（2）1月3日支付差旅费320元。
（3）1月5日支付12月电费550元。
（4）1月5日提现金25000元。
（5）1月6日支付招待费3500元。
（6）1月6日支付差旅费2400元。
（7）1月6日支付手续费35元。
（8）1月14日支付办公用品费6400元。
（9）1月15日购买打印机1500元。
（10）2月2日支付办公室电话费650元。
（11）2月2日出售废旧包装物320元。
（12）2月2日支付1月份电费3320元。
（13）2月3日支付差旅费2500元。
（14）2月7日支付绿化苗木款4000元。
（15）2月7日支付招待费2200元。

简易现金日记账外观简陋，显示余额的方式也不直观，和用户平时使用的现金日记账的外观相去甚远，不太符合普通用户的习惯。理想的现金日记账应当有一个和用户平时的纸质账页类似的外观，而且要能够实现以下功能：
（1）输入的数据能够完整反映业务情况。
（2）实时反映每笔业务完成后的现金余额。
（3）按月计算本月的借贷方累计发生额、本年的借贷方累计发生额以及余额。
要完成复杂格式的功能，表对象和数据透视表就无能为力了。表对象和数据透视表最大的优点在于数据能够按照数据库样式排列，能够通过刷新实时获得数据，缺点是外观一般都比较简单。而纸质账页的外观复杂，这也就决定了用户不需要通过公式和函数的功能来完成现金日记账的设计。

（一）实验目的
了解表格设计方法，掌握SUBTOTAL和IF等函数的方法。
（二）实验要求
能够熟练绘制表格，能够熟练使用函数和公式。

金盛公司在2018年1月1日后发生了如下和现金有关的业务：
（1）2018年1月1日转入上年余额2500元。
（2）2018年1月3日支付办公室电话费210元。
（3）2018年1月5日支付12月电费878元。
（4）2018年1月7日报销经理差旅费500元。
（5）2018年1月6日提现金130000元。
（6）2018年1月6日预付员工差旅费3600元。
（7）2018年1月6日支付工资120000元。
（8）2018年1月14日支付办公用品费用3752元。
（9）2018年1月15日订阅报刊2788元。
（10）2018年2月2日提现金5000元。
（11）2018年2月3日支付办公室电话费255元。
（12）2018年2月2日出售废旧电脑500元。
（13）2018年2月3日支付1月份电费900元。
（14）2018年2月7日提取现金125000元。
（15）2018年2月7日支付员工工资130000元。

1．绘制界面

绘制界面的过程就是将纸质账页移植到Excel工作表的过程，具体的操作步骤如下：

1. 打开Excel，将文件保存为“账页式现金日记账.xlsx”，选中Sheet1，重命名为“现金日记账”。
2. 在工作表内输入如图1.27所示的现金日记账界面，完成界面的设置。

1. 选中F、G和I列，右击，从打开的快捷菜单中执行“设置单元格格式”命令，如图1.28所示。

打开“设置单元格格式”对话框，单击“数字”标签。在“分类”中选择类型为“会计专用”，“小数位数”设置为2，“货币符号”设置为无，单击“确定”按钮，完成对指定列的数字格式的设置，如图1.29所示。

2．创建公式

界面设计完成后，只是搭建了一个数据输入平台。截至目前，该工作表只有数据输入功能，而不具备计算功能，为此需要为以下内容添加计算公式：

• 初始余额
• 月份信息
• 本月借贷方发生总额
• 本年借贷方累计发生总额
• 借贷方向
• 余额

为界面添加计算公式的具体操作步骤如下：

1. 选中I5单元格，在编辑栏内输入公式“=F5”，选中H5单元格，输入“借”字，完成现金初始余额的计算。

1. 选中B14单元格，在编辑栏内输入“=COUNTIF(”，单击插入函数按钮 ，如图1.30所示。

打开如图1.31所示的“函数参数”对话框，在Range参数后输入“$E$6:E15”，在Criteria参数后输入“本月合计”，确定本月月份。

1. 选中B15单元格，在编辑栏内输入公式“=B14”，确定“本年累计”行的月份设置。
2. 选中F14单元格，在编辑栏内输入“=SUBTOTAL(”，单击插入函数按钮 ，打开如图1.32所示的“函数参数”对话框，在“Function_num”参数后输入数值9，在Ref1参数后输入单元格区域“F6:F13”，完成本月借方发生总额的计算。

1. 选中G14单元格，在编辑栏内输入“=SUBTOTAL(”，单击插入函数按钮 ，打开“函数参数”对话框，在“Function_num”参数后输入数值9，在Ref1参数后输入单元格区域“G6:G13”，完成本月贷方发生总额的计算。
2. 选中F15单元格，在编辑栏内输入“=SUBTOTAL(”，单击插入函数按钮 ，打开“函数参数”对话框，在“Function_num”参数后输入数值9，在Ref1参数后输入单元格区域“$F$6:F13”，完成本年借方累计发生额的计算。

1. 选中G15单元格，在编辑栏内输入“=SUBTOTAL(”，单击插入函数按钮 ，打开“函数参数”对话框，在“Function_num”参数后输入数值9，在Ref1参数后输入单元格区域“$G$6:G13”，完成本年贷方累计发生额的计算。
2. 选中H15，输入“=IF($I$5+SUBTOTAL(9,$F$6:F13)-SUBTOTAL(9,$G$6:G13)>0,"借",IF($I$5+ SUBTOTAL(9,$F$6:F13)-SUBTOTAL(9,$G$6:G13)<0,"贷","平"))”，完成余额方向的确定。

1. 选中I15单元格输入“=ABS($I$5+F15-G15)”，完成余额的计算。

3．持续记账设计

在图1.27所示的账页式现金日记账中，为1月份预留的行数是从第6行开始到第13行结束，共计8行，对企业来说，这些行数可能是不够的，为此需要添加行。
添加的空白行一般不太可能和用户实际需要用到的行一致，通常为了防止行不够，会尽可能多地添加一些空行。根据会计规范对现金日记账的要求，现金日记账不得存在空记录，如果添加的行超过了所需的行数，则这些空白的行必须删除，一般我们可以在月底输入完成后删除空行。
为1月份添加更多行的操作步骤如下：

1. 选中第11行，右击，执行“插入”命令，完成插入行的操作。

1. 将数据填写到现金日记账1月份的对应位置，1月份填报完成后的样式如图1.33所示。

1. 选中第6~15行，右击，选择“开始”选项卡，执行“剪贴板”中的“复制”命令，完成1月份数据区域的复制。
2. 选中A16单元格，右击，执行“粘贴”命令，将1月份的数据全部粘贴过来。
3. 选中第16~23行，按键盘上的Delete键，删除复制过来的1月份的数据。

通过上述操作使2月份产生了若干条空记录。当然，这些空记录有可能不够，就要按照前面讲到的方法插入更多的行，如果最终输入完成后，有空余未输入数据的行，则需要删除这些空行。
1月份数据复制过来后，所有为1月份设计的公式完全不需要修改就可以适应2月份的情况。输入完成后如图1.34所示。

金邦公司在2018年1月和2月发生了如下和现金有关的业务，请根据下列内容利用函数和公式创建现金日记账：
（1）1月1日转入上年余额2200元。
（2）1月3日支付差旅费320元。
（3）1月5日支付12月电费550元。
（4）1月6日支付招待费3500元。
（5）1月6日支付差旅费2400元。
（6）1月6日支付手续费35元。
（7）1月7日提现金25000元。
（8）1月14日支付办公用品费用6400元。
（9）1月15日购买打印机1500元。
（10）2月2日支付办公室电话费650元。
（11）2月2日支付1月份电费3320元。
（12）2月3日出售废旧包装物320元。
（13）2月3日支付差旅费2500元。
（14）2月7日支付绿化苗木款4000元。
（15）2月7日支付招待费2200元。