《Excel在会计与财务管理中的应用》 教案 项目二 会计凭证

Excel在会计与财务管理中的应用课程教案课程名称：Excel在会计与财务管理中的应用课程号：课程学分：2参考学时：32课程性质：专业必修课适用专业：经管类专业上课时间：授课教师：负责人：审核单位：年月日-1-一、课程基本信息课程名称Excel在会计与财务管理中的应用课程号课程性质专业必修课开课部门课程负责人课程团队授课学期学分/学时2/32授课语言汉语课程简介Excel是使用极为广泛的电子表格软件，其以操作简便、功能强大而著称，非常适合缺乏计算机编程语言知识和对数据处理要求不高的财务管理人员使用。本书以Excel2010为基础，通过实例详细介绍了Excel在会计电算化中的应用，并应用Excel对企业运营中的会计流程和财务管理问题进行成因分析。本书应用实例典型，内容丰富，充分利用宏、MicrosofQuery组件、数据透视表组件等完成会计电算化的设计。书中各项目详细介绍了实例的操作步骤，学习者只需按照书中介绍的步骤一步步地实际操作，就能完全掌握本书的内容。授课班级

二、课程授课教案项目二讲课主题会计凭证学时2学时教学目标（1）了解科目代码表和现金流量类型的设置方法和用途；（2）了解记账凭证和后台数据表之间的联系；（3）掌握表对象的使用方法，了解录制宏的过程；（4）学会应用Excel解决实际案例中遇到的会计凭证问题。任务目标(1)设置科目代码表，并为各个科目设置年初数；(2)设置现金流量表的类型及代码。教学设计授课提纲及重难点分析教学方法及课程思政设计教学时间任务一科目代码表知识准备对于现金流量的类型名称会计上是有明确规定的，而现金流量类型的代码则可以由用户自定义。本任务中单独设置一张表来放置现金流量相关的项目。现金流量的具体内容包括：(1)销售商品、提供劳务收到的现金。(2)收到的税费返还。(3)收到其他与经营活动有关的现金。(4)购买商品、接受劳务支付的现金。(5)支付给职工以及为职工支付的现金。(6)支付的各项税费。(7)支付其他与经营活动有关的现金。(8)收回投资收到的现金。(9)取得投资收益收到的现金。(10)处置固定资产、无形资产和其他长期资产收回的现金净额。(11)处置子公司及其他营业单位收到的现金净额。(12)收到其他与投资活动有关的现金。(13)购建固定资产、无形资产和其他长期资产支付的现金。(14)投资支付的现金。(15)取得子公司及其他营业单位支付的现金净额。(16)支付其他与投资活动有关的现金。(17)吸收投资收到的现金。(18)取得借款收到的现金。(19)收到其他与筹资活动有关的现金。(20)偿还债务支付的现金。(21)分配股利、利润或偿付利息支付的现金。(22)支付其他与筹资活动有关的现金。科目代码表和现金流量类型是编制记账凭证的基础。任务资料福源公司是一家商品流通企业，公司为一般纳税人。在2021年底，各个会计科目的余额如表2-1所示（表2-1见教材17-18页）。要求：(1)根据表2-1中的内容设置科目代码表，并为各个科目设置年初数。(2)设置现金流量表的类型及代码。任务操作1.科目代码表(1)打开Excel，将工作表保存为“账务处理.xlsm”，如图2-1所示。选中Sheet1工作表，将Sheet1工作表重命名为“科目代码表”。(2)选中A1单元格，在A1:E1单元格区域内依次输入科目代码、科目名称、性质、是否明细和年初数等字段。(3)选中A列并右击，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，选择“数字”标签，选择“数字格式”为“数值”，“小数位数”设置为0，完成A列数据格式的设置。(4)选中E列并右击，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，选择“数字”标签，选择“数字格式”为“会计专用”，“货币符号”设置为无。(5)选中A1单元格，选择“插入”选项卡，执行“表格”组内的“表格”命令，打开如图2-2所示的“创建表”对话框，选中“表包含标题”复选框，将指定的单元格区域转化为表对象。(6)单击A1单元格，选择“表设计”选项卡，将“属性”组内的默认表名称更改为“kmdm”，完成表对象名称的设置，如图2-3所示。(7)选中A1单元格，选择“设计”选项卡，在“表样式”组中选择“表样式浅色9”，完成表样式的选择。2.各科目年初数(1)从A2单元格开始输入科目代码“1001”，在B2单元格内输入科目名称“库存现金”。(2)在C2单元格内输入数值“1”，完成现金科目的性质设定。(3)在D2单元格内输入“y”，完成是否明细科目的设置。(4)在E2单元格内输入数值“3630”，完成现金年初数的设定。(5)从A3单元格开始，继续输入各个会计科目的代码、名称、性质、年初数和是否明细等字段内容。最终完成的科目代码表如图2-4所示。(6)选中第1列，选择“公式”选项卡，执行“定义的名称”组内的“定义名称”命令，打开如图2-5所示的“新建名称”对话框，在“名称”文本框中输入“dm”，在“引用位置”文本框中输入“=kmdm[科目代码表]”，完成第1列名称的设置。3.现金流量类型(1)新建一张工作表，将工作表重命名为“参数”,完成参数表的设置。(2)在A1:B1单元格区域内输入“内容”和“类型代码”。(3)选中A1单元格，选择“插入”选项卡，执行“表格”组内的“表格”命令，打开如图2-6所示的“创建表”对话框，单击“确定”按钮，创建一个表对象。(4)选中A1单元格，选择“设计”选项卡，在“属性”组内将表名称更改为“xjll”，完成表对象样式和名称的设置。(5)从A2单元格开始输入实验原理中介绍的各种现金流量项目内容和代码，最终结果如图2-7所示。(6)选择“公式”选项卡，执行“定义的名称”组内的“定义名称”命令，打开如图2-8所示的“新建名称”对话框，在“名称”文本框中输入“现金流量类型”，在“引用位置”文本框中输入“=xjll[内容]”，单击“确定”按钮，完成新建名称的设置。38min任务二凭证输入知识准备1.LEFT函数LEFT函数的作用是返回指定字符串的左数前几个字符。LEFT函数的返回结果是文本类型的字符串。LEFT函数首先从C4单元格的左侧找4个字符，如代码为“100201”，那么1002就是其总账科目，代码长度为4。2.VLOOKUP函数VLOOKUP函数的作用是在指定区域的第一列中查找第一个符合要求的值。本任务要查找的区域是表对象kmdm，查找的值是“LEFT(C5,4)+0”。本任务中，LEFT函数之所以在返回之后还要加0，是因为LEFT函数返回的是文本类型的字符串，而在科目代码表中，字段“科目代码”的数据格式是“数值”，二者的数值类型不一样，因此比较时自然就不可能相等，变通的方法是为字符串类型的数字加上0，这样就可以将字符类型的数字强制转换为数值。3.IFERROR函数IFERROR函数的作用是判断接下来计算的值是否正确，如果值是正确的，就用正确的值表示，否则用指定的值显示。在本任务中，显示不正确就用空白表示。任务资料在2022年1月，福源公司发生了如下业务：(1)购买编号为JP34的商品33000元，购买编号MT5的商品21000元，购买编号为KK2的商品8000元，增值税税率为13%，以工行支付货款。(2)向海尚公司销售JP32和JP33两种商品，收入为175000元，其中工行收到了货款13475元，其余部分稍后支付。JP32成本为6000元，JP33成本为5000元。(3)工行收到欧丽公司归还的货款88000元。(4)归还一通公司货款250000元，货款已经通过工行支付。(5)销售JP35、MT5和KK3三种商品共计152800元，工行已经收到全部货款。JP35的成本为61000元，MT5的成本为28000元，KK2的成本为3350元。(6)现金支付销售部门费用98元。(7)工行支付招待费5532元。(8)向金鑫公司销售商品MT4共计254000元，未收到货款。MT4商品成本为189100元。(9)销售部门购买办公用品2200元，以工行支付。(10)销售JP34商品157200元，货款已经通过工行收讫，JP34商品的成本为72500元。(11)以工行支付税款，上月应交增值税销项税71382元，不考虑增值税外的其他税收。(12)购买商品MT4共计487000元，JP32共计45000元，JP34共计102000元，货款未支付，其中从合力赊购547940元，从兴乐公司赊购168480元。(13)工行支付前期计入其他应付款的水费6022元。(14)购买空调5台，共计35000元，以工行支付。(15)方乐出差暂领现金3500元。(16)从工行提取175000元支付职工工资，其中销售部门95000元，管理部门80000元，同时按10%计提福利费用。(17)工行收到新通讯公司归还的欠款100000元。(18)出售商品KK3和KK4共计135000元，其中KK3成本为32000元，KK4成本为28000元，货款已经通过工行收讫。(19)向海尚公司销售商品JP35共计182000元，成本为110000元，货款未收讫。(20)向一通公司和兴乐公司赊购商品MT5共计205000元，KK4共计25000元，其中向一通企业购买商品140000元，向乐星公司购买商品65000元。(21)以工行支付一通企业商品款150000元。(22)以工行支付网络费1200元。(23)以工行支付劳保用品费用8000元。任务操作1.凭证界面(1)单击Excel左上角的“文件”按钮，选择“选项”命令，打开“Excel选项”对话框，如图2-9所示。(2)在左侧选择“自定义功能区”，在右侧选中“自定义功能区”下“主选项卡”中的“开发工具”复选框，完成“开发工具”的选择。通过上述操作，选项卡中就能显示“开发工具”选项卡，如图2-10所示。(3)新建工作表，将工作表重命名为“凭证输入”。(4)在B1单元格中输入“记账凭证”，选中B1:F1单元格区域，选择“开始”选项卡，执行“对齐方式”组中的“合并后居中”命令，如图2-11所示。将“字体”设置为“仿宋_GB2312”，“字号”大小为“20”，并将B1:F1单元格区域设置为双下画线。(5)在B2单元格内输入“凭证号”，在D2单元格内输入“日期”，在G2单元格内输入“附件数:”。(6)选中E2单元格，右击，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，选择“数字”选项卡，设置为“日期”分类中的“2012/3/14”类型，完成日期格式的设置。(7)从A3单元格开始，在工作表中输入如图2-12所示的内容。(8)选择“开发工具”选项卡，选择“控件”组中“插入”下“表单控件”中的“按钮”控件，如图2-13所示。(9)选择“开发工具”选项卡，选择“控件”组中“插入”下“表单控件”中的“按钮”控件，在“添加记录”按钮后添加一个命令按钮，并将按钮上的标题更改为“获取最新凭单号”，完成按钮的设置。(10)选中A5单元格，在编辑栏内输入公式“=IF(B5<>″″,YEAR($E$2)&″-″&IF(MONTH($E$2)<10,″0″&MONTH($E$2))&″-″&IF(DAY($E$2)<10,″0″&DAY($E$2))&″-″&IF($C$2<10,″00″&$C$2,IF($C$2<100,″0″&$C$2,$C$2))&″-″&IF(ROW()-4<10,″0″&ROW()-4,ROW()-4)&″-″&IF($H$2<10,″0″&$H$2,$H$2),″″)”，完成凭证id的设计。(11)选中C5:C18单元格，选择“数据”选项卡，执行“数据工具”组内“数据验证”下的“数据”命令，打开如图2-14所示的“数据验证”对话框。(12)选中D5单元格，在编辑栏内输入公式“=IFERROR(VLOOKUP(LEFT(C5,4)+0,kmdm,2,FALSE),″″)”，完成总账科目的设置。(13)选中E5单元格，在编辑栏内输入“=IFERROR(IF(LEN(C5)=6,VLOOKUP(C5,kmdm,2,FALSE),″″),″″)”，完成明细科目的设置。(14)选中I5单元格，在编辑栏内输入公式“=IF(OR(COUNTBLANK(G5:H5)+COUNTBLANK(B5)=3,COUNTBLANK(G5:H5)+COUNTBLANK(B5)=0),″″,″错误″)”，完成错误提示的设置。(15)选中F5:F18单元格区域，选择“数据”选项卡，执行“数据工具”组内的“数据验证”命令，打开如图2-15所示的“数据验证”对话框，在“设置”选项卡下的“允许”中选择“序列”，“来源”设置为“=现金流量类型”，单击“确定”按钮，完成数据验证的设置。(16)选中G5:G18单元格区域，选择“开始”选项卡，执行“样式”组内“条件格式”下的“新建规则”命令，打开“新建格式规则”对话框，在“选择规则类型”中选择“使用公式确定要设置格式的单元格”命令，打开如图2-16所示的“编辑规则说明”，在“为符合此公式的值设置格式”文本框内输入“=SUM($H$5:$H$18)<>SUM($G$5:$G$18)”，单击“格式”按钮，选择“填充”选项卡，选择红色为填充颜色，单击“确定”按钮，完成条件格式的设置。(17)选中G5:H18单元格区域，右击，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，选择“数字”标签，将其类型设置为“会计专用”，“小数位数”设置为2，“货币符号”设置为“无”，完成指定单元格区域的格式设置。(18)选中A5单元格，将鼠标指针移动到A5单元格的右下角，向下拖曳填充公式到A18单元格区域，完成凭证id的设置。选中D5:E5单元格区域，将鼠标指针移动到单元格区域的右下角，向下拖曳填充公式至D18:E18单元格区域，完成总账科目和明细科目的设置。(19)选中I5单元格，将鼠标指针移动到I5单元格的右下角，向下拖曳填充公式到I18单元格区域，完成凭证错误提示的设置。2.输入数据(1)选中C2单元格，输入凭证号，完成凭证号的设置。(2)选中E2单元格，输入“2022-1-3”，完成凭证日期的输入。(3)选中H2单元格，输入“1”，表示该笔凭证的附件数。(4)在B5单元格内输入摘要“购买商品”，在C5单元格内输入科目代码“140503”，在G5单元格内输入金额33000，在H5单元格内输入数值0，完成第1号凭证的第1笔记录输入，第1笔记录完成后的效果如图2-17所示。(5)在B6单元格内输入摘要“购买商品”，在C6单元格内输入科目代码“140507”，在G6单元格内输入金额21000，在H6单元格内输入数值0，完成该凭证的第2笔记录输入。(6)在B7单元格内输入摘要“购买商品”，在C7单元格内输入科目代码“140508”，在G7单元格内输入金额8000，在H7单元格内输入数值0，完成该凭证的第3笔记录输入。(7)在B8单元格内输入摘要“购买商品”，在C8单元格内输入科目代码“222101”，在G8单元格内输入金额8060，在H8单元格内输入数值0，完成该凭证的第4笔记录输入。(8)在B9单元格内输入摘要“购买商品”，在C9单元格内输入科目代码“100201”，在F9单元格内选择“现金流量类型”为“购买商品、接受劳务支付的现金”，在G9单元格内输入0，在H9单元格内输入数值70060，完成该凭证的第5笔记录。3.导入凭证库(1)新建工作表，将工作表重命名为“凭证库”，建立“凭证库”工作表。(2)从A1单元格位置开始输入id、凭证id、摘要、科目代码、总账科目、明细科目、现金流量类型、借金额、贷金额、类型代码、年、月、日、凭证号、笔号、附件数、性质和审核等字段，完成凭证库所需字段的输入。(3)选中A1单元格，选择“插入”选项卡，执行“表格”组内的“表格”命令，将指定的单元格区域转化为组。选择“设计”选项卡，将“属性”组内的表对象名称更改为“pzk”，完成“凭证库”工作表的设计。(4)选中B2单元格，在B2单元格内输入字母“a”，完成首行数据的输入。打开“凭证输入”工作表，在凭证界面中输入第一张凭证，如图2-19所示。当I列没有错误提示，并且借贷方没有红色显示时，说明该凭证在结构上是正确的，就可以通过录制一个宏将数据导入凭证库工作表中。4.录制宏(1)选择“开发工具”选项卡，执行“代码”组中的“录制宏”命令，打开如图2-21所示的“录制宏”对话框，将宏名称更改为“添加新记录”，单击“确定”按钮，开始录制宏。(2)选中H5单元格，选择“开发工具”选项卡，执行“代码”组中的“使用相对引用”命令，进入相对模式下录制。(3)选中H5单元格，同时按Shift+Ctrl+向下方向键，再同时按Shift+Ctrl+向左方向键，按3次向左方向键，再按Ctrl+C键，完成要复制内容的选择复制操作。(4)选择“开发工具”选项卡，取消选中“代码”组中的“使用相对引用”命令，进入绝对引用模式。(5)选择“凭证库”工作表，选中B2单元格，选择“开发工具”选项卡，执行“代码”组中的“使用相对引用”命令，同时按Ctrl+向下方向键，再单独按一次向下方向键，在空白单元格内右击。(6)选择“开发工具”选项卡，取消选中“代码”组中的“使用相对引用”命令，进入绝对引用模式。(7)选中B1单元格，双击，退出复制模式；选中C1单元格，选择“凭证输入”工作表，选中B5:C18单元格区域，按Delete键，删除凭证摘要和科目代码；选中F5:H18单元格区域，按Delete键，删除凭证的借方金额和贷方金额，完成已经导入凭证的数据的删除。(8)选择“开发工具”选项卡，执行“代码”组中的“停止录制”命令，完成宏的录制。(9)在“添加记录”按钮上右击，在弹出的快捷菜单中选择“指定宏”命令，打开如图2-24所示的“指定宏”对话框，选择“添加新记录”选项，单击“确定”按钮，完成指定宏的设置。5.录制宏(1)选择“凭证输入”工作表，选择“开发工具”选项卡，执行“代码”组内的“录制宏”命令，打开“录制宏”对话框，将宏名称更改为“获取最新凭单号”，单击“确定”按钮，开始录制宏。(2)选中C2单元格，在编辑栏内输入公式“=MAX(IF(pzk[年]&″-″&pzk[月]=YEAR(E2)&″-″&MONTH(E2),pzk[凭证号]),0)+1”，按Ctrl+Shift+Enter键，获取凭证库中指定月份最新的凭单号。(3)选择“开发工具”选项卡，执行“代码组”中的“停止录制”命令，完成宏的录制。(4)在“获取最新凭单号”按钮上右击，在弹出的快捷菜单中选择“指定宏”命令，打开“指定宏”对话框，选择“获取最新凭单号”选项，单击“确定”按钮，完成指定宏的设置。6.其他字段数据(1)选中“凭证库”工作表，选中第2行，右击，在弹出的快捷菜单中选择“删除”命令，删除第1行记录。(2)选中A2单元格，在编辑栏内输入“=[@年]&″-″&[@月]&″-″&[@凭证号]&″-″&[@笔号]”，按Enter键确认，完成id字段的设置。(3)选中J2单元格，在编辑栏内输入公式“=IFERROR(VLOOKUP([@现金流量类型],xjll,2,FALSE),″″)”，完成现金流量类型代码的设置。(4)选中K2单元格，在编辑栏内输入公式“=LEFT([@凭证id],4)+0”，完成“年”字段的设置。(5)选中L2单元格，在编辑栏内输入公式“=MID([@凭证id],6,2)+0”，完成“月”字段的设置。(6)选中M2单元格，在编辑栏内输入公式“=MID([@凭证id],9,2)+0”，完成“日”字段的设置。(7)选中N2单元格，在编辑栏内输入公式“=MID([@凭证id],12,3)+0”，完成“凭证号”字段的设置。(8)选中O2单元格，在编辑栏内输入公式“=MID([@凭证id],16,2)+0”，完成“笔号”字段的设置。(9)选中P2单元格，在编辑栏内输入公式“=RIGHT([@凭证id],2)+0”，完成“附件数”字段的设置。(10)选中Q2单元格，在编辑栏内输入公式“=VLOOKUP([@科目代码],kmdm,3,FALSE)”，完成会计科目性质的显示。(11)选中A2单元格，选择“视图”选项卡，执行“窗口”组中“冻结窗口”下的“冻结拆分窗口”命令，完成窗口冻结。7.后续凭证输入(1)选中“凭证输入”工作表，输入日期和附件数，单击“获取最新凭单号”按钮，设置本张凭单的凭单号。(2)选中B5单元格，输入摘要“销售商品”。在C5单元格内输入科目代码100201，在F5单元格内选择“销售商品、提供劳务收到的现金”，在G5单元格内输入数值13475，在H5单元格内输入数值0。(3)选中B6单元格，输入摘要“销售商品”。在C6单元格内输入科目代码112202，在G6单元格内输入数值63000，在H6单元格内输入数值0。(4)选中B7单元格，输入摘要“销售商品”。在C7单元格内输入科目代码6402,在G7单元格内输入数值0，在H7单元格内输入数值17500。(5)选中B8单元格，输入摘要“销售商品”。在C8单元格内输