3Excel在制作日常财务表格中的应用（课件）《EXCEL在财务中的应用》（航空工业出版社）

Excel在制作日常财务表格中的应用 项目三 主讲人：周颖 1 知识目标： ①掌握Excel中基本表格的制作。 ②熟练掌握SUM函数、IF函数、ROUND函数的基本用法。 能力目标： ①学会用Excel制作借款单。 ②学会用Excel制作差旅费报销单。 ③学会用Excel制作现金支票。 素养目标： ①树立服务意识、节约意识。 ②提升风险识别能力，牢固树立风险意识。 ③通过讲解各种操作技巧，锻炼善于思考的意识。 学习目标 冀中职业学院 目录 制作借款单 任务3.1 冀中职业学院 制作现金支票 任务3.3 制作差旅费报销单 任务3.2 Excel电子版表格快速转化到Word 任务3.4 3.1.1 案例介绍 冀中职业学院 任务 3.1 制作借款单 2015年12月5日销售部员工薛玉洁和苏娜从石家庄到武汉出差参加经销商大会，并于12月10日回到石家庄，出差前预借差旅费4000元。公司规定，往返交通工具费用实报实销，住宿标准每人每天不高于200元，市内交通补助实报实销，误餐补贴每人每天60元。请用 Excel 制作一个借款单和差旅费报销单，计算应报销金额并判断是否超出借款金额。 3.1.2 设计借款单 冀中职业学院 任务 3.1 制作借款单 1.借款单的结构 一个简单的贷款单的结构如表所示。 3.1.1 案例介绍 冀中职业学院 任务 3.1 制作借款单 2.制作借款单操作步骤 步骤一：新建一个工作簿文件，起名为“借款单”,在此工作簿中新建一个“借款单模板”工作表。 步骤二：在A1单元格输入“借款单”,选中A1:H1单元格区域，单击“开始”选项卡“对齐方式”分组中的“合并后居中”按钮，设置合适的行高。设置“借款单”字体字号，单击“开始”选项卡“字体”分组中的字体为“黑体”,字号为“18”,单击“下划线”右边的下拉三角选择“双下划线”。 步骤三：在F2、G2、H2单元格分别输入“年”“月”“日”,设置合适的行高。 步骤四：在A3单元格输入“借款部门”,F3单元格输入“借款人”,A4单元格输入“借款用途及理由”,A5单元格输入“借款金额”,B5单元格输入“大写”,G5单元格输入“小写”,A6单元格输入“本部门领导批示”,E6单元格输入“主管财务领导批示”,A7单元格输入“公司负责人批示”,E7单元格输入“财务主管人员核准”。 3.1.1 案例介绍 冀中职业学院 任务 3.1 制作借款单 步骤五：选中B3:E3单元格区域，单击“开始”选项卡“对齐方式”分组中的“合并后居中”按钮，并分别对G3:H3、B4:H4、C5:F5、A6:B6、G6:H6、E6:F6、C6:D6、A7:B7、C7:D7、E7:F7、G7:H7单元格区域进行合并。 步骤六：选中 A3:H7单元格区域，右击选中的区域，在弹出的快捷菜单中，单击“设置单元格格式”命令，单击“边框”选项卡，设置外边框和内部边框，如图所示。 3.1.1 案例介绍 冀中职业学院 任务 3.1 制作借款单 步骤七：选中 A3:H3单元格区域，单击“开始”选项卡“字体”分组中的字体为“宋体”,字号为“11”号字，最后结果如图所示。 3.1.1 案例介绍 冀中职业学院 任务 3.1 制作借款单 3.设计借款单中的格式和公式 步骤八：单击H5单元格，单击“开始”选项卡“数字”分组中的数字格式中的“货币”,此区域将为货币格式。 步骤九：单击C5单元格，在编辑栏中输入如下公式： “=IF(ROUND(H5,2)<0,"无 效 数 值",IF(ROUND(H5,2)=0,"零", IF(ROUND(H5,2)<1,"",TEXT(INT(ROUND(H5,2)),"[dbnum2]")&"元")&IF(INT(ROUND(H5,2)*10)INT(ROUND(H5,2))*10=0,IF(INT(ROUND(H5,2))*(INT(ROUND(H5,2)*100)-INT(ROUND(H5,2)*10)*10)=0,"","零"), TEXT(INT(ROUND(H5,2)*10)-INT(ROUND(H5,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(H5,2)*100)-INT(ROUND(H5,2)*10)*10)=0,"整",TEXT((INT(ROUND(H5,2)*100)-INT(ROUND(H5,2)*10)*10),"[dbnum2]")&"分")))" 3.1.1 案例介绍 冀中职业学院 任务 3.1 制作借款单 4.导入数据 将案例中的数据输入到“借款单”中，如图所示。 3.2.1 案例介绍 冀中职业学院 任务3.2 制作差旅费报销单 2015年12月5日销售部员工薛玉洁和苏娜从石家庄到武汉出差参加经销商大会，并与12月10日早回到石家庄，出差前预借差旅费4000元。公司规定，往返交通工具费用实报实销，住宿标准每人每天不高于200元，市内交通补助实报实销，误餐补贴每人每天60元。请用 Excel 制作一个借款单和差旅费报销单，计算应报销金额并且判断是否超出借款金额。在任务3.1 中制作了借款单，任务3.2 中制作差旅费报销单。 3.2.2 设计差旅费报销单 冀中职业学院 任务3.2 制作差旅费报销单 1.差旅费报销单的结构 3.2.1 案例介绍 冀中职业学院 任务3.2 制作差旅费报销单 2.制作差旅费报销单操作步骤 步骤一：新建一个工作簿文件，起名为“差旅费报销单”,在此工作簿中新建一个“差旅费报销单模板”工作表。 步骤二：在A1单元格输入“差旅费报销单”,选中A1至L1单元格，单击“开始”选项卡“对齐方式”分组中的“合并后居中”按钮，设置合适的行高。设置“差旅费报销单”字体字号，单击“开始”选项卡“字体”分组中的字体为“黑体”,字号为“18”,单击“下划线”右边的下拉三角选择“双下划线”。 步骤三：在A2单元格输入“单位名称：”,在J2单元格输入“报销日期：”,在K2单元格输入“年 月日”。选中A2:F2单元格区域，单击“开始”选项卡“对齐方式”分组中的“合并后居中”按钮，在对齐方式中设置“左对齐”;选中K2:L2单元格区域，进行合并。 步骤四：在A3单元格输入“姓名”,在G3单元格输入“事由”,在A4单元格输入“起”,在C4单元格输入“止”,在E4单元格输入“起始地点”,在G4单元格输入“人数”,在H4单元格输入“交通工具”,在14单元格输入“交通费金额”,J4单元格输入“其他补助”。 3.2.1 案例介绍 冀中职业学院 任务3.2 制作差旅费报销单 步骤五：选中A3:B3单元格区域，单击“开始”选项卡“对齐方式”分组中的“合并后居中”按钮，并分别对C3:F3、G3:H3、I3:L3、A4:B4、C4:D4、E4:F4、J4:L4 单元格区域进行合并。 步骤六：在A5单元格输入“月”,在B5单元格输入“日”,在C5单元格输入“月”,在 D5单元格输入“日”,在E5单元格输入“起”,在F5单元格输入“止”,在J5单元格输入“项目”,在K5单元格输入“天数”,在L5单元格输入“金额”。在J6单元格输入“住宿费”,在J7单元格输入“市内交通”,在J8单元格输入“邮电费”,在J9单元格输入“住勤费”,在J10单元格输入“误餐补贴”,在J11单元格输入“其他”。在A13单元格输入“合计”,在A14单元格输入“报销总额”,在E14单元格输入“大写”,在I14单元格输入“预支”,在K14单元格输入“补领/退回”,在A15单元格输入“单位负责人：”,在F15单元格输入“会计主管：”,在I15单元格输入“出纳：”,在K15单元格输入“报销人：”。 3.2.1 案例介绍 冀中职业学院 任务3.2 制作差旅费报销单 步骤七：选中A13:H13区域，单击“开始”选项卡“对齐方式”分组中的“合并后居中”按钮，并分别对A14:B14、C14:D14、F14:H14、I3:L3、A4:B4、C4:D4、E4:F4、J4:L4、J13:L13、A15:C15、D15:E15、F15:G15 单元格区域进行合并。 步骤八：选中 A3:L14区域，右击选中的区域，在弹出的快捷菜单中，单击“设置单元格格式”命令，单击“边框”选项卡，设置外边框和内部边框。最后差旅费报销单显示结果，如图所示。 3.2.1 案例介绍 冀中职业学院 任务3.2 制作差旅费报销单 3.设计差旅费报销单中的格式和公式 步骤九：选中16:113单元格区域，单击“开始”选项卡“数字”分组中的数字格式中的“货币”,此区域将为货币格式。选中L6:L12、J13、C14、J14、L14 单元格区域，将这些单元格区域都设置为货币格式。 步骤十：选中I13单元格，设置公式为“=SUM(I6:112)”;选中J13单元格，设置公式为“=SUM(16:112)”;选中C14单元格，设置公式为“=I13+J13”;选中L14单元格，设置公式为“=ABS(J14-C14)”。 3.2.1 案例介绍 冀中职业学院 任务3.2 制作差旅费报销单 步骤十一：单击F14单元格，在编辑栏中输入如下公式： "=IF(ROUND(C14,2)<0,"无效数值",IF(ROUND(C14,2)=0,"零",IF(ROUND(C14,2)<1,"", TEXT(INT(ROUND(C14,2)),"[dbnum2]")&"元")&IF(INT(ROUND(C14,2)*10)-INT(ROUND(C14,2))*10=0,IF(INT(ROUND(C14,2))*(INT(ROUND(C14,2)*100)-INT(ROUND(C14,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(C14,2)*10)-INT(ROUND(C14,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(C14,2)*100)-INT(ROUND(C14,2)*10)*10)=0,"整",TEXT((INT(ROUND(C14,2)*100)-INT(ROUND(C14,2)*10)*10),"[dbnum2]")&"分")))”把“报销总额”的小写总额利用公式转换为大写金额。 3.2.1 案例介绍 冀中职业学院 任务3.2 制作差旅费报销单 4.导入数据 将案例中的数据输入到差旅费报销单中，如图所示。 3.3.1 案例介绍 冀中职业学院 任务 3.3 制作现金支票 现金支票是用于支取现金的一种原始凭证，它可以由存款人签发，用于银行为本单位提取现金；也可以签发给其他单位和个人，用来办理结算或委托银行代为支付现金给收款人。现金支票由企业所开户的银行进行签发，其格式是固定的。随着办公自动化的发展，很多企业也直接将原始凭证进行了“电子化”,统一将现金支票的数据以 Excel表格的形式进行填写和保存。下面就以工商银行的“现金支票”为模板，在 Excel 中模仿其格式，制作现金支票的电子版。 3.3.2 设计现金支票 冀中职业学院 任务 3.3 制作现金支票 1.制作现金支票操作步骤 步骤一：新建一个工作簿文件，起名为“现金支票”,在此工作簿中新建一个“现金支票”工作表。 步骤二：选择A列，在“开始”选项卡的“单元格”命令组中单击“格式”按钮，在弹出的下拉列表中选择“列宽”选项，弹出“列宽”对话框，在文本框中输入“1”,单击“确定”按钮，如图所示。用同样的方法设置B列的列宽为“2”,D、E、F列的列宽均为 3.5。 3.3.2 设计现金支票 冀中职业学院 任务 3.3 制作现金支票 步骤三：选择第2行，在“开始”选项卡的“单元格”命令组中单击“格式”按钮，在弹出的下拉列表中选择“行高”选项，弹出“行高”对话框，在文本框中输入“6”,单击“确定”按钮，如图所示。 3.3.2 设计现金支票 冀中职业学院 任务 3.3 制作现金支票 步骤四：使用与步骤三相同的方法设置第3行的行高为“32”,合并后居中C3:E3单元格区域，在其中输入文本“中国工商银行”。设置第4行行高为“25”,合并后居中C4:E4单元格区域，在其中输入文本“现金支票存根”。合并后居中F3:F4单元格区域，在其中输入“(冀)”,如图 所示。 3.3.2 设计现金支票 冀中职业学院 任务 3.3 制作现金支票 步骤五：在C6单元格中输入“附加信息”。合并C6:F6、C7:F7、C8:F8单元格区域，设置其对齐方式为左对齐。在C10、C11、C12、C13、C14单元格中分别输入“出票日期”“收款人：”“金额：”“用途：”“单位主管”。合并D10:F10、D11:F11、D12:F12、D13:F13 单元格区域，设置对齐方式为左对齐。合并后居中D14:F14单元格区域，在其中输入文本“会计”,如图所示。 3.3.2 设计现金支票 冀中职业学院 任务 3.3 制作现金支票 步骤六：将C3和C4单元格中的文本加粗。设置F3单元格中文本的字体为“隶书”,字体颜色为“红色”,设置其余字体的字号为“9”号，如图所示。 3.3.2 设计现金支票 冀中职业学院 任务 3.3 制作现金支票 步骤七：选择C3单元格并打开“设置单元格格式”对话框，选择“对齐”选项卡。在“垂直对齐”下拉列表中选择“靠下”选项，单击“确定”按钮完成设置，如图所示。 3.3.2 设计现金支票 冀中职业学院 任务 3.3 制作现金支票 步骤八：选择C4单元格并打开“设置单元格格式”对话框，选择“对齐”选项卡。在“垂直对齐”下拉列表中选择“居中”选项，单击“确定”按钮完成设置。 步骤九：为C3:F14单元格区域添加外边框，为C6:F8单元格区域添加中间边框线，为C11:F13 单元格区域添加所有边框，完成后即可在工作表中查看其效果，如图所示。 3.3.2 设计现金支票 冀中职业学院 任务 3.3 制作现金支票 步骤十：使用类似的方法，在右侧输入现金支票的其他内容。设置I列列宽为“3”,J列列宽为“5”,设置T列到AD列的列宽为“1.5”。合并K3:N3单元格区域，合并 P3:R3单元格区域，合并T3:W3单元格区域，合并J4:K4、J5:K5单元格区域，合并R4:T4单元格区域，合并R5:T5单元格区域，合并J6:K7单元格区域，合并L6:R7单元格区域，合并I6:I12 单元格区域，合并Q10:AD10 单元格区域，合并J11:K13 单元格区域。 步骤十一：在K3单元格输入文字“中国工商银行”,在P3单元格输入文字“现金支票”,在T3单元格输入文字“(冀)”。使用同样的方法，输入其他的内容。 步骤十二：单击15单元格，单击“开始”选项卡“对齐方式”命令组中“方向”按钮后边的下拉三角，在弹出的菜单中单击“竖排文字”命令，在I5单元格输入竖排文字“本支票付款期限十天”。 步骤十三：在J11单元格输入“上列款项请从我账户内支付出票人签章”,单击“开始”选项卡“对齐方式”命令组中的“自动换行”按钮，将其设置为“自动换行”。 3.3.2 设计现金支票 冀中职业学院 任务 3.3 制作现金支票 步骤十四：将“中国工商银行”的字号设置为“20”并加粗，将“现金支票”文本设置为黑体、20号字，将“(冀)”设置为“隶书、20号”,并加粗显示金额的小写区域，如图所示。 3.3.2 设计现金支票 冀中职业学院 任务 3.3 制作现金支票 步骤十五：选择合并后的L16单元格，打开“设置单元格格式”对话框，选择“填充”选项卡，在“图案颜色”下拉列表中选择“红色”选项，在“图案样式”下拉列表框中选择“细水平条纹”选项，单击“确定”按钮完成设置，如图所示。 3.3.2 设计现金支票 冀中职业学院 任务 3.3 制作现金支票 步骤十六：添加银行标志。选择“插入”选项卡中“插图”命令组中的“图片”按钮，打开“插入图片”对话框，在下拉列表中选择“工商logo.png”文件，完成后单击“插入”按钮。如图所示。 3.3.2 设计现金支票 冀中职业学院 任务 3.3 制作现金支票 步骤十七：复制银行标志。将鼠标指针放在插入图片的右上角，调整图片的大小，将其放至文本“中国工商银行”之前。选择调整好的图片，同时按住鼠标左键和“Ctrl”键，将图片拖动至左侧的“中国工商银行”前，完成图片的复制，如图所示。 3.3.2 设计现金支票 冀中职业学院 任务 3.3 制作现金支票 步骤十八：隐藏网格线。选择“视图”选项卡中的“显示”命令组，取消选中“网格线”复选框，隐藏工作表中的网格线，如图所示。 3.3.2 设计现金支票 冀中职业学院 任务 3.3 制作现金支票 步骤十九：添加虚线。选中H2:H14单元格区域，打开“设置单元格格式”对话框，选择“边框”选项卡，在“线条”中选择“虚线”,在预览框中的左边框处单击一下，单击“确定”按钮，为G列和H列之间的边框添加“虚线边框样式”,如图所示。 3.3.2 设计现金支票 冀中职业学院 任务 3.3 制作现金支票 至此“现金支票”制作完成，效果如图所示。 3.4.1 案例介绍 冀中职业学院 任务 3.4 Excel 电子表格快速转换到 Word 小张做好了公司的Excel文件，可是在报送时，上级部门要求把Excel电子表格转换成 Word文件报送。这时问题来了，Excel电子表格很大，复制到 Word中以后不能完全显示，这可把小张急坏了，这应该怎么办呢?难道需要在 Word中重新输入或者一行一行地复制吗? 3.4.2 案例操作步骤 冀中职业学院 任务 3.4 Excel 电子表格快速转换到 Word 步骤一：打开“任务四 Excel电子表格工资表”,在名称框内输入“A?:S222”,按回车键快速选中此区域，右击选中的区域，在弹出的快捷菜单中单击“复制”命令。 步骤二：在桌面上双击“Microsoft Word”打开 Word 程序，在编辑区域右击，在弹出的菜单中单击“粘贴”命令，会发现表格很宽，在 Word文档中只显示了一部分。 步骤三：这一步很关键。单击文件左上角的表格全选按钮选中整个表格，在选中的区域右击，在弹出的菜单中单击“自动调整”中的“根据窗口调整表格”,如图所示，表格就可以在 Word 文档中完整显示了。 3.4.2 案例操作步骤 冀中职业学院 任务 3.4 Excel 电子表格快速转换到 Word 步骤四：设置文件的纸张大小和方向。单击“页面布局”选项卡中“页面设置”命令组中的“纸张大小”按钮下面的下拉三角，单击选择“A3”;单击“纸张方向”按钮下面的下拉三角，单击选择“横向”。这样表格看起来就很舒服了。 步骤五：设置标题行重复，使每一页都可以显示标题。选中第1行至第4行，单击“表格工具”选项卡中“布局”中“数据”命令组中的“重复标题行”按钮，如图1所示。 至此，最后的结果如图2所示。 图1 图2 $$