1、实例实例13 13 制作贷款购车计算器制作贷款购车计算器 插入插入PMT函数函数 插入与设置形状插入与设置形状 保护工作表保护工作表 保存模板保存模板学习情境学习情境贷款购物在当今已成为一种时尚，对于无大量存款，但每月还小额款项基本没问题的个人或家庭而言，房贷、车贷等分期购物方式较好地解决了资金流动的难题。小张也想根据自己目前的经济状况买车，希望自己设计一个贷款购车计算器，从而衡量哪种贷款方式较为合适。编排效果编排效果掌握技能掌握技能通过本实例，将学会以下技能：插入PMT函数。插入与设置形状。保护工作表。保存模板。设置文档结构设置文档结构1.启动Excel程序，打开自动命名为“工作簿1”的Ex
2、cel文档，按照图中所示内容输入相应文字。1.输入2.选中列B至列F。3.右键单击选中列的列标处。4.在弹出的快捷菜单中，单击“列宽”命令，打开“列宽”对话框。5.输入“列宽”的值“15”，单击“确定”按钮返回工作表。2.选中4.单击3.右击5.输入6.按下“Ctrl”键，选中以下单元格：C6、C10:C11、C13:C16。7.右键单击选中区域，在弹出的快捷菜单中，单击“设置单元格格式”命令，打开“设置单元格格式”对话框。8.单击“数字”选项卡“货币”分类，再单击“小数位数”右侧的向下箭头，调整小数位数至“0”。6.选中7.单击8.单击添加下拉列表添加下拉列表为了方便使用，可将单元格设置成具
3、有下拉列表的形式，不需输入，仅通过选择相应的项即可将内容显示至表单中。1.对B2:F2单元格执行“合并后居中”操作。2.在F4单元格中，输入“5.33%”；在C6单元格中，输入“150000”。3.单击C7单元格。4.单击“数据”选项卡“数据工具”组“数据有效性”按钮。5.在打开的“数据有效性”对话框中，单击“设置”选项卡“允许”栏下方的列表框箭头，选中“序列”项。6.在“来源”栏下方的编辑框中输入“30%,40%,50%,60%”。7.单击C8单元格。8.单击“数据”选项卡“数据工具”组“数据有效性”按钮。9.在“数据有效性”对话框中，选中“序列”项，在“来源”处输入“1,2,3,4,5”。
4、1.合并2.输入3.单击4.单击5.选中6.输入7.单击9.输入8.单击引用动态数据引用动态数据在单元格中输入的数据通常是不可变的，为了提高数据的可重用性，一般使用引用单元格地址的方式进行计算，当被引用单元格的数据发生变化时，其他数据也能够动态地更新，从而做到输入较少数据即可进行大量运算的效果。1.单击C10单元格，在编辑栏中，输入“=”号，激活公式编辑状态。2.单击C6单元格，引用其地址至公式中，继续输入“*”号，再单击C7单元格，引用其地址至公式中。单击“输入”按钮完成计算。2.引用1.单击3.单击C11单元格，在编辑栏中，输入“=”号，激活公式编辑状态。4.单击C6单元格，引用其地址至公
5、式中，继续输入“-”号，再单击C10单元格，引用其地址至公式中。单击“输入”按钮完成计算。5.单击C12单元格，在编辑栏中，输入“=”号，激活公式编辑状态。6.单击C8单元格，引用其地址至公式中，继续输入“*”号，再输入“12”。单击“输入”按钮完成计算。4.引用3.单击6.引用5.单击插入插入PMT函数函数PMT函数属于财务类函数的一种，常用于计算在固定利率下，贷款的等额分期偿还额。PMT函数共包含Rate、Nper、Pv、Fv、Type 5个参数，其中前3个参数非常重要。Rate代表各期利率，利率/12代表一个月的还款额；Nper代表贷款期限，若Rate中计算的是一个月的还款额，那么此处需
6、输入还款几个月；Pv为贷款总额，因贷款是出账，为了阅读方便，可在其值前加负号。1.单击C13单元格。2.单击“公式”选项卡“函数库”组“财务”按钮“PMT”函数，打开“函数参数”对话框。1.单击2.单击3.设置“Rate”参数的值为“F4/12”。其中，F4单元格为银行公布的年利率，此项需根据每年公布的实际情况而定。4.设置“Nper”参数的值为“C12”。此处不需要输入，单击C12单元格即可完成引用。5.单击C11单元格，将其地址引用至“Pv”参数处，再在最前面输入负号。6.单击“确定”按钮完成函数设置。每月还款额会根据年利率、贷款时间和贷款金额的变化而变化。6.单击3.输入5.输入4.引用
7、结果减少小数位减少小数位1.单击C14单元格，在编辑栏中，输入“=”号，激活公式编辑状态。2.单击C13单元格，引用其地址至公式中，继续输入“*”号，再单击C12单元格，引用其地址至公式中。单击“输入”按钮完成计算。2.引用1.单击3.单击“开始”选项卡“数字”组“减少小数位数”按钮。3.单击4.单击C15单元格，在编辑栏中，输入“=”号，激活公式编辑状态。5.引用C14单元格至公式中，继续输入“-”号，再引用C11单元格。单击“输入”按钮完成计算。6.单击C16单元格，在编辑栏中，输入“=”号，激活公式编辑状态。7.引用C10单元格至公式中，继续输入“+”号，再引用C14单元格。单击“输入”
8、按钮完成计算。添加总结语添加总结语至此，贷款购车所需数据已进行了计算，而为了更加清晰地展示计算结果，可将结果引用至醒目的位置，比如添加总结语。1.单击B18单元格，输入图中所示内容。1.输入2.单击D18单元格。3.输入“=”号后，引用C10单元格地址的内容至活动单元格。2.单击3.引用4.单击F18单元格。5.输入“=”号后，引用C13单元格地址的内容至活动单元格。4.单击5.引用6.单击C19单元格。7.输入“=”号后，引用C16单元格地址的内容至活动单元格。6.单击7.引用8.单击E19单元格。9.输入“=”号后，引用C16单元格，继续输入“-”号，再引用C6单元格地址的内容至活动单元格
9、。按“Enter”键完成计算。8.单击9.引用定位单元格定位单元格1.选中B18:F19单元格，按组合键“Ctrl+G”定位单元格。2.单击“定位”对话框中的“定位条件”按钮。1.选中2.单击3.在“定位条件”对话框中，选中“公式”项。4.单击“确定”按钮返回，并选中满足定位条件的单元格。5.设置选中单元格的字号为“14”号。6.单击“开始”选项卡“字体”组“字体颜色”按钮，按照默认设置将字体颜色设置为“红色”，对齐方式设置为“居中”。3.选中4.单击6.单击5.选中插入形状插入形状1.选中B2单元格，设置表格标题字体为“华文彩云”，字号为“24”号。2.选中A3:F19单元格，设置正文字体为
10、“微软雅黑”。3.将鼠标置于任意列列标的右侧，拖动鼠标适当调整列宽。1.设置3.拖动2.设置4.单击“插入”选项卡“插图”组“形状”按钮“矩形”项，此时鼠标呈“”形状。4.单击5.拖动鼠标自B2单元格的左上角至F2单元格的右下角，释放鼠标，完成形状的插入。5.拖动美化形状美化形状1.选中上面插入的形状，激活“绘图工具”。2.单击“格式”选项卡“形状样式”组“形状填充”按钮，在下拉列表中，单击“无填充颜色”项。3.单击“形状轮廓”按钮，在下拉列表中，单击“自动”项。4.单击“形状效果”按钮，在下拉列表中，单击“阴影”“右下斜偏移”项。美化边框美化边框1.选中B6:D8和E4:F4单元格。2.单击
11、“开始”选项卡“字体”组“边框”按钮“其他边框”项，打开“设置单元格格式”对话框。1.选中2.单击3.在“边框”选项卡中，单击“颜色”栏下拉列表中的“绿色”。4.单击“线条样式”选择外边框样式。5.单击“外边框”按钮，为选中区域增加外边框。6.单击“线条样式”选择内部框线样式。7.单击“内部”按钮，为选中区域增加内部框线。8.单击“确定”按钮完成设置。3.单击5.单击4.单击6.单击7.单击8.单击9.选中B10:B16单元格，重复步骤28，其中框线颜色改为“红色”。9.设置设置填充颜色设置填充颜色1.按住“Ctrl”键，同时选中E4:F4、B6:D8和B10:D16单元格。2.单击“开始”选
12、项卡“字体”组“填充”按钮右侧的箭头，在下拉列表中，单击“其他颜色”项。2.单击1.选中3.在“颜色”对话框中，单击需要的颜色。4.单击“确定”按钮完成选中色彩的填充。3.单击4.单击5.选中C6:C8和F4单元格。6.单击“开始”选项卡“字体”组“填充”按钮右侧的箭头，在下拉列表中，单击“浅蓝”项。5.选中6.单击复制形状复制形状1.选中矩形，按组合键“Ctrl+C”完成复制。2.按组合键“Ctrl+V”粘贴矩形。3.将粘贴后的矩形拖动至B18:F19单元格，并将鼠标置于矩形边缘，当鼠标呈“”形状时，拖动矩形改变其至合适的大小。2.粘贴3.拖动1.复制4.选中表格标题四周的矩形，按住“Ctr
13、l”键，当鼠标呈“”形状时，向下拖动至B6:F7单元格。4.拖动5.将鼠标置于矩形边缘，当鼠标呈“”形状时，拖动矩形改变其至合适的大小，使矩形位于E6:F16区域。6.选中位于E6:F16区域的矩形，单击“绘图工具”功能区“格式”选项卡“形状样式”组“彩色轮廓-蓝色，强调颜色1”项。7.单击“形状填充”按钮“无填充颜色”项。5.调整7.单击6.单击设置段落格式设置段落格式在插入的自选图形中，可以增加文字内容，也可以为文字设置不同的格式。若文字数量与形状大小不匹配，可采用设置段落大小的方式，扩大或缩小行间距。1.右键单击矩形的边缘。2.在弹出的快捷菜单中，单击“编辑文字”命令，进入为形状添加文字
14、的状态。2.单击1.右击3.输入图中所示文字。4.选中输入的文字，右键单击选中区域，在弹出的快捷菜单中，单击“段落”命令。5.在打开的“段落”对话框中，单击“行距”右侧的列表框，在下拉列表中选中“1.5倍行距”项，设置行距。6.单击“确定”按钮完成设置。5.选中6.单击插入剪贴画插入剪贴画1.单击“插入”选项卡“插图”组“剪贴画”按钮，在文档右侧出现“剪贴画”任务窗格。2.在“搜索文字”框中，输入需要的图片名称，如“车”。3.单击“剪贴画”任务窗格“搜索”按钮，在窗格下方出现与名称相符的图片。4.在剪贴画预览区域，单击所需图形完成插入。4.在剪贴画预览区域，单击所需图形完成插入。5.调整插入的
15、剪贴画的大小，并拖动其至合适的位置。至此，本文档已基本设计完成，为了观察设计效果，可取消网格线。方法为：单击“视图”选项卡“显示”组，默认状态下，Excel为我们勾选“网格线”项，单击复选框，即可取消对其的勾选。保护工作表保护工作表工作表设计完成后，若不希望他人进行修改，可为工作表设置保护措施，如加密。1.选中C6:C8和F4单元格。2.单击“开始”选项卡“单元格”组“格式”按钮“设置单元格格式”项。3.在打开的“设置单元格格式”对话框中，单击“保护”选项卡“锁定”项，取消对其的勾选。4.单击“审阅”选项卡“更改”组“保护工作表”按钮，显示“保护工作表”对话框。5.输入密码，如“123”。6.
16、选中“选定锁定单元格”项，同时自动选中“选定未锁定的单元格”项。单击“确定”按钮打开“确认密码”对话框。7.再次输入密码后，单击“确定”按钮完成设置。保存为模板保存为模板Excel提供了多种模板供用户使用，而当我们设计好一个Excel文档后，为了方便使用，同样可存储为模板，再次使用时与Excel本机自带模板的使用方法一致。1.Excel文档设计完成后，单击“快速启动工具栏”中的“保存”按钮，打开“另存为”对话框。1.单击2.单击“保存类型”右侧的箭头，在下拉列表中，选中“Excel模板(*.xltx)”项。3.选择模板存取位置。4.输入模板名称，如“贷款购车计算器”。5.单击“保存”按钮完成模板的存储。双击该模板，将自动建立名为“贷款购车计算器1”的文档，因工作表被保护，仅可以在未设置锁定的区域修改信息。2.选中3.选择4.输入5.单击
