Excel在会计与财务管理中的应用 课件 项目一 现金日记账

Excel在会计与财务管理中的应用项目一现金日记账(1)了解现金日记账的手工记账方法；(2)掌握Excel中表对象和数据透视表的基本使用方法；(3)掌握SUBTOTAL和IF等函数的使用方法；(4)能够熟练绘制表格及使用函数和公式；(5)学会应用Excel解决实际案例中遇到的现金日记账问题。学习目标日记账包括现金日记账和银行日记账，由出纳人员（资金会计）按照业务流程发生的时间顺序逐笔登记，逐日反映库存现金或者银行账户存款的收支余情况。由于现金日记账和银行日记账的记账方式完全相同，只是体现的资金类型不同，因此本项目仅以现金日记账为例介绍通过Excel设计日记账的两种方法，一种是通过表对象和数据透视表完成设计，另一种是通过函数和公式的设定完成现金日记账的功能。通过本项目的学习，学习者可以了解利用Excel进行财务设计的思路和方法。情景引入简易现金日记账1账页式现金日记账2目录CONTENTS任务一简易现金日记账知识准备Excel本身不是一种结构严谨的数据库，只是它的格式和数据库中的数据表太像了，以至于用户常常期待可以通过Excel实现某些只有数据库才具有的功能。Excel中进行数据的计算有两种基本的思路，一种是使用数据透视表，另一种是使用函数。数据透视表是Excel中功能最为强大的工具，具有分析数据快速、简单的特点，尤其对于大数据量的分析计算，效率很高。本任务的内容就是按照数据透视表的思路对现金日记账的余额进行计算。数据透视表是利用Excel进行会计电算化设计的常用方法。任务目标（1）设计并完成福源公司2022年1月和2月现金日记账；（2）借方发生额、贷方发生额和余额数据必须保留两位小数，并按照“会计专用”格式显示；（3）按日计算每日余额，按月分析每月的收支情况。任务资料福源公司在2022年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）打开Excel，将工作簿保存为“简易日记账.xlsx”，双击Sheet1工作表标签，将Sheet1重命名为“现金日记账”。（2）在A1:H1单元格区域内依次输入年、月、日、编号、内容摘要、借方发生额、贷方发生额以及余额8个字段，如图1-1所示。1.数据输入设计图1-1

标题行任务操作（3）选中A:D列并右击，在弹出的快捷菜单中选择“设置单元格格式”命令，在打开的“设置单元格格式”对话框中选择“数字”标签。在“分类”列表框中选择类型为“数值”，选择“小数位数”为0，如图1-2所示。单击“确定”按钮，完成A列到D列的年、月、日以及编号的数据类型设置。1.数据输入设计图1-2设置年月日以及编号的数据类型任务操作（4）选中E列并右击，在弹出的快捷菜单中选择“设置单元格格式”命令，如图1-3所示。从打开的“设置单元格格式”对话框中选择“数字”标签，在“分类”列表框中选择类型为“文本”，完成对E列内容摘要的数据类型的设置。1.数据输入设计图1-3选择“设置单元格格式”命令任务操作（5）选中F:H列并右击，在弹出的快捷菜单中选择“设置单元格格式”命令，如图1-3所示。从打开的“设置单元格格式”对话框中选择“数字”标签，在“分类”列表框中选择类型为“会计专用”，“小数位数”设置为2，“货币符号”设置为无，完成对F列到H列借贷方发生额以及余额的数据类型设置。1.数据输入设计任务操作（1）选中D2单元格，在编辑栏内输入公式“=ROW()-1”，完成“编号”字段的设置。【提示】：编号和行号的关系就是当前记录所在的行号减去1，计算当前记录所在行在Excel中使用ROW函数。（2）选中H2单元格，在编辑栏内输入公式“=F2”，完成第1条记录余额的计算。（3）从A2单元格开始，输入第1条记录的其余部分。输入的第1条记录如图1-4所示。2.创建公式图1-4第1条记录任务操作（4）选中A3单元格，在编辑栏内输入公式“=2022”，接着输入第2条记录，其中D3单元格中输入公式“=ROW()-1”，H3单元格中输入公式“=H2+F3-G3”。输入的第2条记录如图1-5所示。2.创建公式图1-5第2条记录任务操作（5）选中A1单元格，选择“插入”选项卡，执行“表格”组中的“表格”命令，如图1-6所示。打开如图1-7所示的“创建表”对话框，选中“表包含标题”复选框，单击“确定”按钮，将单元格区域转化为表。2.创建公式图1-6“表格”命令图1-7“创建表”对话框任务操作（6）选中A1单元格，选择“表设计”选项卡，在“表格样式选项”组中选择“白色，表样式浅色15”，如图1-8所示。2.创建公式图1-8“表格样式”组任务操作（7）选择“表设计”选项卡，在“属性”组中将表名称由默认的“表1”更改为“现金日记账”，完成表格名称的设置，如图1-9所示。2.创建公式图1-9设置表对象名称任务操作（8）继续输入剩余的记录，最终结果如图1-10所示。2.创建公式图1-10输入完成后的现金日记账表格任务操作（1）选择“现金日记账”工作表的A1单元格，选择“插入”选项卡，执行“表格”组中的“数据透视表”命令，如图1-11所示，打开“创建数据透视表”对话框。3.创建公式图1-11“数据透视表”命令任务操作（2）选中“选择一个表或区域”单选按钮，已经可以自动识别要进行数据透视的区域。如果未能识别，则在“表/区域”后输入“现金日记账”。选择放置数据透视表的位置为“新工作表”，如图1-12所示，完成数据透视表数据源的设置。3.创建公式图1-12设置数据透视表数据源任务操作（3）按照上述步骤打开如图1-13所示的“数据透视表字段”对话框，将“月”字段和“日”字段拖动到“行”中，将“借方发生额”和“贷方发生额”字段拖动到“值”中，完成要显示的字段的设置。3.创建公式图1-13“数据透视表字段”对话框任务操作（4）单击“数据透视表字段”对话框的“值”内的“求和项:借方发生额”字段，选择“值字段设置”命令，打开如图1-14所示的“值字段设置”对话框。单击“值字段设置”对话框中的“数字格式”按钮，打开“设置单元格格式”对话框，选择“数字”标签，在“分类”列表框中选择类型为“会计专用”，“小数位数”设置为2，“货币符号”设置为无，单击“确定”按钮，完成字段计算类型和数字的设置。3.创建公式图1-14“值字段设置”对话框任务操作（5）单击“数据透视表字段”对话框的“值”内的“求和项:贷方发生额”字段，选择“值字段设置”命令，打开“值字段设置”对话框。单击“值字段设置”对话框中的“数字格式”按钮，打开“设置单元格格式”对话框，选择“数字”标签，在“分类”列表框中选择类型为“会计专用”，“小数位数”设置为2，“货币符号”设置为无，单击“确定”按钮，完成字段计算类型和数字的设置。3.创建公式任务操作（6）选中数据透视表中任意一个单元格，选择“分析”选项卡，执行“计算”组中的“字段、项目和集”下的“计算字段”命令(如图1-15所示)，打开如图1-16所示的“插入计算字段”对话框。3.创建公式图1-15“字段、项目和集”下的“计算字段”命令图1-16“插入计算字段”对话框（7）将“名称”文本框中的“字段1”改为“余额总计”。（8）将“公式”文本框中的“0”删除，选择“字段”列表框中的“借方发生额”，单击“插入字段”按钮，再输入减号，然后选择“字段”列表框中的“贷方发生额”，单击“插入字段”按钮。单击“确定”按钮，完成插入计算字段的操作。任务操作3.创建公式任务操作（9）最终结果如图1-17所示。3.创建公式图1-17按月和日分类汇总的数据透视表任务二账页式现金日记账知识准备COUNTIF函数的功能是统计在指定范围内符合统计条件的单元格的个数。COUNTIF函数包括Range和Criteria两个参数，完整的格式为COUNTIF(Range,Criteria)。Range:表示参与统计的单元格区域。Criteria：表示统计的条件。1.COUNTIF函数知识准备SUBTOTAL函数对选定区域进行分类汇总，其完整的格式为SUBTOTAL(Function_num,Ref1,Ref2,…)。Function_num：本示例参数的值为9，表示分类求和。Ref1,Ref2,…：表示分类计算的区域引用。在本示例中引用的是F6:F11单元格区域，如果在第6行和第11行之间插入空行，则该公式所引用的单元格区域会自动发生变化。2.SUBTOTAL函数任务目标（1）完成账页式现金日记账页面设计；（2）在账页式现金日记账中创建公式；（3）完成福源公司在2022年1月1日后发生的现金有关的业务。任务资料福源公司在2022年1月1日后发生了如下和现金有关的业务：(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元。福源公司在2022年1月1日后发生了如下和现金有关的业务：任务操作（1）打开Excel，将文件保存为“账页式现金日记账.xlsx”，选中Sheet1，重命名为“现金日记账”。（2）在工作表内输入如图1-18所示的现金日记账界面，完成界面的设置，如图1-18所示。1.绘制界面图1-18完成后的界面任务操作(3)选中F、G和I列并右击，从弹出的快捷菜单中选择“设置单元格格式”命令，如图1-19所示。1.绘制界面图1-19选择“设置单元格格式”命令任务操作（4）打开“设置单元格格式”对话框，选择“数字”标签，在“分类”列表框中选择类型为“会计专用”，“小数位数”设置为2，“货币符号”设置为无，单击“确定”按钮，完成对指定列的数字格式的设置，如图1-20所示。1.绘制界面图1-20“设置单元格格式”对话框任务操作（1）选中I5单元格，在编辑栏内输入公式“=F5”；选中H5单元格，输入“借”字，完成现金初始余额的计算。（2）选中B14单元格，在编辑栏内输入“=COUNTIF()”，将光标移至括号内，单击“插入函数”按钮，如图1-21所示。2.创建公式图1-21单击“插入函数”按钮任务操作打开如图1-22所示的“函数参数”对话框，在Range参数后输入“$E$6:E15”，在Criteria参数后输入“本月合计”，确定本月月份。2.创建公式图1-22COUNTIF函数任务操作【提示】这一步是利用COUNTIF函数自动计算月份，计算月份的原理就在于统计指定的单元格范围内“本月合计”单元格出现的次数。假定现金日记账是从1月份开始记账，那么很显然“本月合计”在1月底时的出现次数为1，就表示1月份；在2月份底时的出现次数为2，就表示2月份。依此类推，通过“本月合计”出现的次数就可以知道正在汇总的月份为几月。本例中使用“$E$6”，用绝对引用的方式则是表示今后统计时，总是将指定的E6单元格作为初始位置开始统计。2.创建公式任务操作

（3）选中B15单元格，在编辑栏内输入公式“=B14”，确定“本年累计”行的月份设置。（4）选中F14单元格，在编辑栏内输入“=SUBTOTAL()”，将光标移至括号内，单击“插入函数”按钮，打开如图1-23所示的“函数参数”对话框，在Function_num参数后输入数值9，在Ref1参数后输入单元格区域“F6:F13”，完成本月借方发生总额的计算。2.创建公式图1-23SUBTOTAL函数任务操作【提示】SUBTOTAL函数的作用是在指定范围内进行分类求和计算。虽然是进行求和计算，但是此处不能使用Sum函数进行汇总，原因是Sum函数计算时会将以前各月的借贷方累计发生额都加入计算范围内，从而对最终结果产生干扰，而SUBTOTAL则不会这样。SUBTOTAL函数有一个特点是所引用的单元格区域中如果包含其他的SUBTOTAL函数，那么所有包含SUBTOTAL函数的单元格不会被计算在内，所以可以通过SUBTOTAL函数避免重复计算。2.创建公式任务操作（5）选中G14单元格，在编辑栏内输入“=SUBTOTAL()”，将光标移至括号内，单击“插入函数”按钮，打开“函数参数”对话框，在Function_num参数后输入数值9，在Ref1参数后输入单元格区域“G6:G13”，完成本月贷方发生总额的计算。（6）选中F15单元格，在编辑栏内输入“=SUBTOTAL()”，将光标移至括号内，单击“插入函数”按钮，打开“函数参数”对话框，在Function_num参数后输入数值9，在Ref1参数后输入单元格区域“$F$6:F13”，完成本年借方累计发生额的计算。2.创建公式任务操作（7）选中G15单元格，在编辑栏内输入“=SUBTOTAL()”，将光标移至括号内，单击“插入函数”按钮，打开“函数参数”对话框，在Function_num参数后输入数值9，在Ref1参数后输入单元格区域“$G$6:G13”，完成本年贷方累计发生额的计算。（8）SUBTOTAL(9,$G$6:G13)>0,″借″,IF($I$5+SUBTOTAL(9,$F$6:F13)-SUBTOTAL(9,$G$6:G13)<O,″贷″,″平″))”，完成余额方向的确定。（9）选中I15单元格，输入“=ABS($I$5+F15-G15)”，完成余额的计算。2.创建公式任务操作（1）选中第11行并右击，在弹出的快捷菜单中选择“插入”命令，完成插入行的操作。（2）将数据填写到现金日记账1月份的对应位置，1月份填报完成后的样式如图1-24所示3.持续记账设计图1-241月份的现金日记账任务操作（3）选中第6～15行，选择“开始”选项卡，执行“剪贴板”中的“复制”命令，完成1月份数据区域的复制。（4）选中A16单元格并右击，在弹出的快捷菜单中选择“粘贴”命令，将1月份的数据全部粘贴过来。（5）选中第16～23行，按Delete键，删除复制过来的1月份的数据。3.持续记账设计任务操作（6）将2月份数据填写到现金2月份的对应位置，2月份填报完成后的样式如图1-25所示。3.持续记账设计图1-25输入2月份的记录本项目主要介绍了简易现金日记账和账页式现金日记账的