Excel 2016数据处理与分析应用教程 (8)
Excel数据处理与分析实例教程(微课版第3版)-电子教案第8章 利用图表显示数据
第8章利用图表显示数据教学内容1.图表的组成和种类2.创建图表3.编辑图表4.修饰图表5.应用图表教学要求【知识目标】1.了解图表的类型及组成2.了解各类图表的作用【技能目标】1.掌握图表的创建方法2.掌握图表的编辑方法3.掌握图表的修饰方法教学重点1.各类图表的作用2.各类图表的创建方法教学难点 1.选择合适的图形进行数据的显示教学方法讲授法、案例法、演示法课时数5课时（讲授3课时，实验2课时）课程导入【思路】导入实例，提出问题。
学生讨论。
通过讨论，让学生了解使用数据可以创建图表，以直观显示数据，并引出第8章要介绍的内容。
【实例】显示销售业绩奖金（8.5实例）【问题】1.怎样找出销售额最多的业务员并直观显示出来？如右图所示。
2.平常你最常见的图表主要有哪些？3.柱形图的主要功能是什么？柱形图适合应用于什么场景？4.是否可以使用多个图形绘制组合图？１２３４５６8.5.3 显示产品销售情况为了更加全面地掌握公司销售情况，公司管理者不仅需要比较每名业务员的销售业绩，还需要通过更加直观的方式了解公司每种产品的销售情况。
假设在“销售管理.xlsx”工作簿文件中已经建立了名为“产品销售汇总表”的工作表，如图8-34所示。
如果希望在显示产品销售汇总情况的同时，还可以深入、直观地了解每种产品的销售完成情况是否高出销售平均值，可以在图中增加一条销售额平均值的线条。
1．创建条形图可以使用条形图来显示全年所有产品的总销售额。
2．修改数据系列图形对于创建后的图表，还可以使用更为形象的图形来表达数据的含义。
3．为图表添加纵向参考线有时用户需要快速判断出高于全年平均销售额的产品。
一般情况下，即使是添加了数据标签，也不能很清晰地分辨出哪个产品的销售额高于全年的平均销售额。
有效的方法是增加一条参考线，可以使结果一目了然。
此处将利用散点图增加一条平均线。
8.5.4 显示某业务员的销售业绩在销售管理中，公司管理者往往可以通过查看业务员销售业绩和销售趋势的图表，来全面掌握公司销售情况、比较和分析业务员的销售能力，但也需要随时了解某名业务员的销售业绩。
Excel2016数据处理与分析 第08章 函数与公式
8.1 函数与公式基础
公式中单元格引用样式
A1引用样式
默认情况下，Excel使用A1引用样式，即使用字母A~XFD表示列标，用数字 1~1048576表示行号，单元格地址由列标和行号组合而成。
R1C1引用样式
Excel的行号和列号都使用数字表示。例如R2C3单元格，即是指第2行和第3列交叉 处的单元格。其中字母“R”、“C”分别是英文“ROW”行、“COLUMN”列的 首字母。
8.1 函数与公式基础
公式中的数据类型
在Excel公式中，数据可以分为文本、数值、逻辑值和错误值等几种类型。 （1）文本在公式中使用时需要用一种半角双引号（""）所包含的内容表示。 （2）日期和时间是数值的特殊表示形式，可以直接用于数学运算。 （3）逻辑值只有TRUE和FALSE两种。 （4）错误值主要有#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、 #NUM!、#NULL!等
8.5 常用函数系列
文本类函数的应用
RIGHT函数
RIGHT函数根据所指定的字符数，返回文本字符串中最后一个或多个字符， 函数语法与LEFT函数类似。
8.5 常用函数系列
文本类函数的应用
MID函数
MID函数用于在字符串任意位置上返回指定数量的字符，函数语法为： MID(text,start_num,num_chars) 第一参数是包含要提取字符的文本字符串，第二参数用于指定文本中要提 取的第一个字符的位置，第三参数指定从文本中返回字符的个数。无论是 单字节还是双字节字符，MID函数始终将每个字符按1计数。
8.4 常用函数系列
信息与逻辑函数的应用
逻辑函数可以对数据进行相应的判断，例如判断真假值，或者进行复合检 验。在实际工作中，此类函数与其它函数嵌套应用，能够在更为宽泛的领 域完成复杂的逻辑判断。 • IF函数判断条件真假单个单元格数组公式（四）命名数组 • 逻辑函数与乘法、加法运算 • 屏蔽函数公式返回的错误值 • 常用的IS类信息函数
Excel2016数据处理与分析应用教程-教学大纲
《Excel 数据处理与分析》课程教学大纲
课程名称：Excel 数据处理与分析
总学时：48（或64）
总学分：3（或4）
适用对象:各专业
先修课程：无
一、课程目的和任务
Excel 数据处理与分析课程主要目的是培养学生掌握数据处理和分析的基本原理和方法，熟练使用Excel 2016电子表格软件。
课程的任务是从解决问题的角度，分析问题并用Excel软件来解决数据存储、加工处理的问题。
二、教学内容
熟练使用Excel进行数据录入、工作表编辑、公式、复杂的函数运算、数据管理、制作图表、宏与VBA编程、财务分析函数的应用、模拟分析与规划求解。
教学中要侧重实例的分析，首先提出问题，然后提出解决方案。
三、各教学环节学时分配
48学时分配
64学时分配
四、考核方式
课程成绩由平时上机实验成绩和期末考试成绩组成，平时上机实验成绩为30%~40%；期末机考成绩为60%~70%。
五、对学生能力培养的体现
通过课程的学习培养学生数据处理的能力，能够掌握Excel高效解决实际中的信息数据问题。
Excel数据分析功能使用教程
Excel数据分析功能使用教程在excel中我们除了输入数据制作表格外，还可以通过表格数据进行数据分析，还发现其中的相关关系和规律等等，那么数据分析功能怎么使用呢?下面就给大家介绍Excel数据分析怎么用?方法步骤1、本文将以Excel2010版本为例，新建并打开excel表格;2、首先添加数据分析插件，点击左上角文件按钮，出现菜单页面，点击选项按钮3、然后点击“加载项”选项，选中“分析工具库”，点击下方“转到”按钮4、然后出现excel加载宏界面，在”分析工具库“前方框内打勾，点击确定;5、经过上一步已经成功添加”数据分析插件“，在”数据“-”数据分析“下可以找到6、然后点击”数据分析“，可以找到相关的分析方法，如回归分析，方差分析，相关分析等;补充：excel常用Ctrl组合快捷键Ctrl+A全选Ctrl+C复制Ctrl+X剪切Ctrl+V粘贴Ctrl+D自动填充Ctrl+B加粗Ctrl+U下划线Ctrl+I斜体Ctrl+K超链接Ctrl+F查找Ctrl+H替换Ctrl+L创建表Ctrl+N新建工作簿Ctrl+O打开文件Ctrl+P打印Ctrl+S保存Ctrl+W关闭Ctrl+Y撤销后重做Ctrl+Z撤销相关阅读：excel不常用的重要技巧1.怎样快速打开设置单元格格式我们经常在输入数值等的时候需要设置单元格格式，很多人都已经习惯了点击鼠标右键选项，但是我们可以使用快捷键CTRL加1快速打开，如果自己的手不在鼠标上非常方便。
当然，我们也可以用鼠标点击如下图所示位置快速打开设置单元格格式选项。
2.excel怎样设置小数位数如果一行带小数的数字我们要快速转化为整数的话可以使用快捷键CTRL加SHIFT加1，设置起来速度非常快。
当然，我们经常不会只使用整数，小数位的缩进或者延伸我们可以选中要设置的单元格后使用点击下面两个选项进行设置。
3.关闭excel表的快捷方式我们经常需要打开一个表格完成工作后需要关闭，可以使用快捷方式CTRL加W，使用习惯后比较方便，希望能够对大家有帮助。
Excel数据处理与分析应用教程 (8)
8.5 应用实例－显示销售业绩奖金
显示超级销售人员
计 算 最 大 值
8.5 应用实例－显示销售业绩奖金
显示超级销售人员
创建折线图
8.5 应用实例－显示销售业绩奖金
显示超级销售人员
标识最大值
8.5 应用实例－显示销售业绩奖金
显示超级销售人员
修饰图表
8.5 应用实例－显示销售业绩奖金
8.4 修饰图表
设置三维图表
设置三维图表的高度和角度 设置三维图表的深度和宽度 设置三维图表的图形形状
添加趋势线
8.5 应用实例－显示销售业绩奖金
比较销售人员销售业绩
创建图表 修饰图表
组合图表
8.5 应用实例－显示销售业绩奖金
比较销售人员销售业绩
创建的图表
8.5 应用实例－显示销售业绩奖金
Excel数据处理与分析应用教程 第8章 使用图表显示数据
第8章 使用图表显示数据
认识图表 创建图表 编辑图表 修饰图表 应用实例－显示销售业绩奖金
第8章 使用图表显示数据
主要知识点
图表组成和种类 创建图表 编辑图表 修饰图表 应用图表
8.1 认识图表
图表组成
图表标题 图表区 网络线 数据序列 数 值 轴 分类轴 图例
8.5 应用实例－显示销售业绩奖金
显示产品销售情况
更换数据系 列图形
第8章 使用图表显示数据
本章小结
理解各种图表的形式以及图表中各组 成部分的含义
掌握创建图表的基本要点和方法
掌握各种图表的适应范围
Excel 2016数据处理与分析 第8章 宏与VBA编程
8.3.2 VBA程序语句
MsgBox对话框举例： f=MsgBox("要退出吗?", 4, "退出提示")
8.3.2 VBA程序语句
2. 赋值语句
[Let] 变量名=表达式
功能：计算等号右端表达式的值，并将结果赋值给等号左端 的变量。Let是可以省略。
例如：
Dim r As Single, area As Single
r = 10
'变量r赋值为常量10
area = r * r * PI '变量area赋值为计算圆面积的表达式
代码窗口 属性窗口
8.2.3 在VBE中编写代码
Excel中的宏实际上就是一个VBA子过程，子过程 名就是宏名。子过程定义格式为：
Sub 子过程名（[<形参列表>]） [<语句1>] [<语句n>]
End Sub
【例8-3】在A2单元格中输入一个圆的半径，单击 按钮后在B2单元格输出该圆的面积。
'获得A2中的数据值
area=3.14159*r*r
Hale Waihona Puke '计算圆面积Range("B2").Value=area
'将面积值写入B2
End Sub
【例8-3】在A2单元格中输入一个圆的半径，单 击按钮后在B2单元格输出该圆的面积。
操作步骤： ① 在工作表的A1:B2区域按图中所示输入内容。 ② 使用【Alt+F11】组合键打开VBE窗口。 ③ 在VBE中单击菜单“插入|模块”，然后输入“计算圆面 积”宏代码。 ④ 切换到Excel环境，在工作表中绘制一个按钮，并指定执 行刚刚创建的“计算圆面积”宏。 ⑤ 将按钮上显示的标题“按钮1”修改为“计算”。 ⑥ 此时单击“计算”按钮，即可在B2中输出面积值。修改 A2中圆的半径值，再次单击“计算”按钮，可以计算新的 圆面积。
Office2016办公软件高级应用任务式教程 任务8制作销售图表
Office2016办公自动化高级应用案例教程
a=3,b=2
操作 演示
制作销售图表
案例实现
2、图表元素的添加与格式设置
图表元素的添加与格式设置方法如下：
2）设置图例：选中图表，切换到“图表工具|设计”选项卡，单击“图表布局”功能组中的“添 加图表元素”按钮，从下拉列表中选择“图例”级联菜单中的“顶部”命令，调整图例位置；通 过“设置图例格式”窗格，设置图例填充颜色为“浅绿”、透明度为“70%”。
Office2016办公自动化高级应用案例教程
a=3,b=2
操作 演示
制作销售图表
案例实现
4、设置涨跌柱线
设置涨跌柱线设置方法如下：
1）选中图表，单击右侧的“图表元素”按钮，从展开的列表中选择“涨/跌柱线”复选框，此时 在图表中即可显示涨跌柱线。
2）选中“跌柱线1”，切换到“图表工具|格式”选项卡，单击“形状填充”下拉按钮，从下拉列 表中选择“标准色”中的“绿色”选项，单击“形状轮廓”下拉按钮，从下拉列表中选择“无轮 廓”选项。
3）用同样的方法，设置“涨柱线1”的“形状填充”为标准色中的“红色”、“形状轮廓”为 “无轮廓”。
Office2016办公自动化高级应用案例教程
a=3,b=2
操作 演示
制作销售图表
案例小结
本案例通过制作销售图表讲解了Excel中图表的创建、图表的格式化等操作。
Office2016办公自动化高级应用案例教程
Office2016办公自动化高级应用案例教程
a=3,b=2
操作 演示
制作销售图表
案例实现
2、图表元素的添加与格式设置
图表元素的添加与格式设置方法如下：
4）添加坐标轴标题：选中图表，单击右侧的“图表元素”按钮，从展开的列表中选择“坐标轴标 题”复选框；修改图表左侧纵坐标标题为“销售量”，修改图表下方横坐标标题为“月份”；设 置纵坐标标题为“竖排”文本。
如何将 Excel 运用于数据处理与分析
如何将Excel 运用于数据处理与分析Excel 是一款常用的电子表格软件，广泛应用于不同领域的数据处理和分析。
无论是商业、金融、科学、教育还是个人使用，都需要掌握 Excel 的基本操作和高级功能，以更高效地处理和分析数据。
本文将介绍如何将Excel 运用于数据处理与分析。
一、数据处理Excel 可以很好地处理数据，包括数据的输入、编辑、格式化、筛选、排序、合并等。
在处理数据之前，我们需要先规划好数据的结构和格式，以便更好地进行处理和分析。
1. 数据输入：在 Excel 中输入数据可以采用直接输入、复制粘贴、导入外部数据等方式。
直接输入数据可以通过单元格输入或填充 Series 等方式快速完成，也可以使用快速录入工具以快速输入相对较长的数据。
复制粘贴数据可以通过复制某个数据范围，然后选择目标单元格范围，使用Ctrl+V 或右键粘贴等快捷键操作粘贴数据。
导入外部数据可以通过打开外部数据源，然后选择所需数据范围，使用数据导入向导快速导入数据。
2. 数据编辑：在 Excel 中可以对数据进行编辑，包括单元格编辑、单元格批量编辑、单元格格式编辑等。
单元格编辑可以通过在单元格中直接修改或使用 F2 快捷键进入编辑模式。
单元格批量编辑可以通过选择需要批量处理的单元格范围，然后使用相应的编辑命令，如复制、填充、清除、替换等命令。
单元格格式编辑可以通过选择单元格或单元格范围，然后使用格式化 Painter、条件格式等命令对单元格格式进行编辑。
3. 数据格式化：在 Excel 中可以对数据进行格式化，包括数字、文本、日期、时间、货币等格式化。
数字格式化可以通过选择所需数据范围，然后使用数字格式命令选择相应的数字格式进行格式化。
文本格式化可以通过选择所需数据范围，然后使用文本格式命令选择相应的文本格式进行格式化。
日期、时间、货币等格式化可以使用相应的格式命令进行格式化。
4. 数据筛选：在 Excel 中可以对数据进行筛选，包括自动筛选、高级筛选等。
Excel数据分析基础与实战教学大纲8
《Excel数据分析基础与实战》教学大纲课程名称：Excel数据分析基础与实战课程类别：必修适用专业：数据分析类相关专业总学时：32学时（其中理论7学时,实验25学时）总学分：2.0学分一、课程地性质大数据时代已经到来,在商业,经济与其它领域中基于数据与分析去发现问题并做出科学,客观地决策越来越重要。
Excel作为常用地数据分析工具之一,在数据分析技术地研究与应用中,扮演着至关重要地角色。
为了满足日益增长地数据分析人才需求,特开设Excel数据分析基础与实战课程。
二、课程地任务通过本课程地学习,使学生学会使用Excel 2016编辑数据,通过排序,筛选,分类汇总等方式探索数据,通过多种函数地使用处理数据,通过多种可视化图形对数据进行可视化展示,将理论与实践相结合,为将来从事以Excel为生产力工具地人员奠定基础。
三、课程学时分配序号教学内容理论学时实验学时其它1 第1章数据分析与Excel 2016概述112 第2章外部数据获取023 第3章数据处理044 第4章函数应用285 第5章数据透视表与数据透视图026 第6章数据分析与可视化127 第7章处理新零售智能销售数据分析项目地数据118 第8章分析商品地销售情况0 29 第9章分析商品库存0 110 第10章分析用户行为0 211 第11章撰写新零售智能销售数据分析 2 0总计7 25四、教学内容与学时安排1. 理论教学序号章节名称教学目标学时1 数据分析与Excel 2016概述1. 了解数据分析流程2. 了解数据分析地应用场景3. 认识Excel 2016地用户界面4. 了解工作簿5. 了解工作表6. 了解单元格12 函数地应用1. 了解YEAR函数,MONTH函数,DAY函数,DATEDIF函数,WORKDAYS函数等各种日期与时间函数地作用2. 了解PRODUCT函数,SUM函数,SUMIF函数,QUOTIENT函数,ROUND函数等数学函数地作用3. 了解COUNT函数,COUNTIF函数,AVERAGE函数,AVERAGEIF函数,MAX函数,LARGE函数,MIN函数,SMALL函数,MODE.SNGL函数,FREQUENCY函数等统计函数地作用4. 了解EXACT函数,CONCATENATE函数,LENH函数,FIND函数,SEARCH函数,LEFT函数,RICHT函数,SUBSTITUTE函数,REPLACE函数,REPLACEB函数等文本函数地作用5. 了解IF函数,AND函数,OR函数等逻辑函数地作用23 数据分析与可视化1. 了解常见地柱形图类型与其作用2. 了解常见地条形图类型与其作用3. 了解常见地折线图类型与其作用4. 了解常见地饼图类型与其作用5. 了解常见地散点图类型与其作用6. 了解常见地雷达图类型与其作用14 处理新零售智能销售数据分析项目地数据掌握数据分析地概念与流程 15 撰写新零售智能销售数据分析1. 分析背景与目地2. 分析思路3. 分析商品销售情况4. 分析库存5. 分析用户行为6. 总结2学时合计72. 实验教学序号实验项目名称实验要求学时1 数据分析与Excel 2016概述1. 启动Excel 20162. 认识Excel 2016地标题栏,功能区,名称框,编辑栏,工作表编辑区,状态栏3. 关闭Excel 201612 外部数据获取1. 在Excel 2016中找到获取文本数据地相关命令2. 导入“客户信息.txt”数据3. 导入“客户信息.csv”数据4. 新建一个MySQL数据源5. 通过Excel 2016进行连接MySQL数据源6. 导入“info”地数据23 数据处理1. 根据会员名进行升序2. 根据会员名进行升序排序,在根据店铺名进行降序排序3. 创建一个店铺所在地地自定义序列4. 根据自定义序列进行排序5. 在“店铺所在地”字段筛选出含有蓝色标记地珠海地区6. 在“会员名”字段筛选出名为“张大鹏”与“李小东”地行7. 统计各会员地消费金额地总额8. 统计各会员地消费金额地平均值9. 统计各会员地消费金额地总额并将汇总结果分页显示44 函数应用1. 输入公式计算菜品地总价2. 输入PRODUCT函数计算菜品地总价3. 用相对引用地方式计算菜品总价4. 用绝对引用地方式输入订单地日期5. 用三维引用地方式在【9月订单详情】工作表输入98月1日地营业额6. 用外部引用地方式在【9月订单详情】工作表输入9月2日地营业额7. 使用单一单元格数组公式计算9月1日地营业额8. 使用多单元格数组公式计算各订单地菜品地总价9. 在【订单信息】工作表中提取年月日地日期数据10. 在【员工信息表】工作表中计算员工地周岁数,不满1年地月数与不满1全月地天数11. 在【员工信息表】工作表中计算员工地工作天数12. 使用PRODUCT函数计算折后金额13. 使用SUM函数计算8月营业总额（不含折扣）14. 使用SUMIF函数计算8月1日营业总额（不含折扣）15. 使用QUOTIENT函数计算8月平均每日营业额（不含折扣且计算结果只取整数部分）16. 使用ROUND函数取折后金额地整数部分17. 使用COUNT函数统计8月订单数18. 使用COUNTIF函数统计8月1日订单数19. 使用AVERAGE函数计算8月平均每日营业额20. 使用AVERAGEIF函数计算盐田分店地8月平均每日营业额21. 使用MAX函数计算消费金额地最大值22. 使用LARGE函数计算消费金额地第二大值23. 使用MIN函数计算消费金额地最小值24. 使用SMALL函数计算消费金额地第二小值25. 使用MODE.SNGL函数计算消费金额地众数26. 使用FREQUENCY函数计算消费金额在给定区域（【8月订单信息】工作表单元格区域I2:I5）出现地频率。
计算机二级考试教程MS Office高级应用第9章利用Excel 2016分析与处理数据
步骤 2
单击“数据”选项卡“排序和筛选”组中的“升 序”或“降序”按钮（见图），所选列即按升序 或降序方式进行排序。
提示
通常情况下，参与排序的数据列表需要有标题行，且为一个连续区域。 当工作表中的单元格引用了其他单元格中的数据时，有可能因排序的关 系使公式的引用地址产生错误，从而导致工作表中的数据发生错误。
9
9.1.3 自定义排序
在某些情况下，如果已有的排序规则不能满足用户的要求时，可以用自定义排序规则来解决。 用户除了可以使用 Excel 2016 内置的自定义序列进行排序外，还可以根据需要创建自定义序 列，并按创建的自定义序列进行排序。需要注意的是，用户只能基于数据创建自定义序列，而不 能基于格式创建自定义序列。 创建自定义序列并应用它进行排序的操作步骤如下：
例如，要对“员工提成”表格中的数据按“部门”为主要关键字降序排序，按“提成金额” 为次要关键字升序排序，操作步骤如下：
步骤 1
单击要进行排序操作的工作表中的任意非空单元格，然后单击“数据”选项卡 “排序和筛选”组中的“排序”按钮。
步骤 2
打开“排序”对话框，设置“主要关键字”条件。在“主要关键字”下拉列表 中选择“部门”选项；在“排序依据”下拉列表中选择“数值”选项；在“次 序”下拉列表中选择“降序”选项。
8
9.1.2 多条件排序
步骤 4
用户可根据需要添加多个次要关键字。设置完毕，单击“确定”按钮即可，效 果如图所示。此时可看到，“部门”相同的，按“提成金额”升序排序。
提示
选中“排序”对话框的“数据包含标 题”复选框，表示选定区域的第1行作为标 题，不参加排序，始终放在原来的行位置； 取消该复选框的选中状态，表示将选定区 域的第1行作为普通数据参与排序，其位置 随排序结果变动。
Excel 2016数据处理与分析应用教程 (8)
【例9-7】某人计划开一个食品厂，预计投资为11 万元，并预期今后5年的净收益分别为：1.5万元、 2.1万元、3万元、4万元和5.5万元。分别求出投资 2年、4年以及5年后的内部收益率。
• 1. PMT函数
• 语法格式：PMT(rate, per, nper, pv, [fv], [type]) • 函数功能：基于固定利率及等额分期付款方式，计算投资贷款在
某一给定期间内的利息偿还额。 • 参数说明：rate为投资（或贷款）利率；per为要计算利息数额的
期数，必须在 1 到 nper 之间；nper为总投资（或贷款）期数； pv为现值（本金）；fv为未来值或在最后一次付款后希望得到的 现金余额，省略时默认其值为0；type为付款时间类型，省略时 默认其值为0（期末）。
【例9-4】假定某项投资需要在2019-1-1支付现金3 万元，并于下述时间获取以下金额的返回资金： 2019-7-1返回8750元；2020-1-1返回7250元； 2020-7-1返回16250元；2020-12-31返回9750元。 假设资金流转贴现率为7％，则净现值为多少？
• 分析：年贴现率为7%， values参数对应所有 的金额，dates参数对 应相应的日期。
9.1.1 投资函数
• 3. NPV函数
• 语法格式：NPV(rate, value1, [value2], …） • 函数功能：基于一系列现金流和固定的各期贴现率，计算一项投
资的净现值（当前纯利润）。净现值越大，投资效益越好。 • 参数说明：rate为某一期间的贴现率，value1，value2，…编号
EXCEL2016数据处理与分析第08章 数据的查询与核对
CHOOSE函数应用实例1：礼品发放
CHOOSE函数应用实例2：销售提成
8.2.2用VLOOKUP和HLOOKUP函数进行表格查询
• 1．VLOOKUP函数的功能与语法格式
VLOOKUP函数的功能是在表格或数值数组的首列查找指定的数值，并且由 此返回表格或数组当前行中指定列处的数值。
VLOOKUP函数的语法格式如下： • VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) • 其中：
其中： – 参数index_num 用以指明待选参数序号的参数值,它必须为 1 到 29 之间 的数字，或者是包含数字 1 到 29 的公式或单元格引用。 – 如果 index_num 为 1，函数 CHOOSE 返回 value1；如果为 2，函数 CHOOSE 返回 value2，以此类推。 – 如果 index_num 小于 1 或大于列表中最后一个值的序号，函数 CHOOSE 返回错误值 #VALUE!。 – 如果 index_num 为小数，则在使用前将被截尾取整。
第8章 数据的查询与核对
【本章知识点】
利用查找命令在工作表中查询数据 掌握主要查询函数的作用及其使用方法 利用LOOKUP/VLOOKUP/HLOOKUP 函数查询数据 利用CHOOSE、MATCH、INDEX函数查询数据 应用INDIRECT 和名称构造实用的数据查询 利用OFFSET函数对动态区域进行数据查询 掌握利用DGET函数和记录单进行数据库数据查询 利用数组公式构造功能强大的统计查询 掌握数据表之间数据核对的三种不同方法
VLOOKUP函数的一个简单应用
VLOOKUP应用实例：设计学生基本情况查询表
8.2.3用MATCH和INDEX函数构造灵活的查询
EXCEL2016数据处理与分析_教案_第08部分
教 案 正 文（一）学院部门： 任课教师：课 次 第 17 次课 授 课时 间授课题目 数据的查询与核对(1) 上 课教 室授 课 方 式 课堂讲授（√）； 实践课（ ）教 学时 数2教学 目标 与 要求 知识目标：n 利用查找命令在工作表中查询数据利用查找命令在工作表中查询数据n 掌握主要查询函数的作用及其使用方法掌握主要查询函数的作用及其使用方法 n 利用LOOKUP/VLOOKUP/HLOOKUP 函数查询数据函数查询数据 n 利用CHOOSECHOOSE、、MATCHMATCH、、INDEX函数查询数据教学 方法 、 手段 、 媒介 教学方法：讲授、示教、课堂互动讲授、示教、课堂互动教学手段：使用PPT进行课堂讲解。
进行课堂讲解。
教学媒介：教科书、板书、幻灯片。
教科书、板书、幻灯片。
主要 教学 内容、重点、 难点 及 时间 分配 教学内容：8.1利用查找命令查询数据利用查找命令查询数据8.2利用查询与引用函数进行查询利用查询与引用函数进行查询教学重点：8.2利用查询与引用函数进行查询利用查询与引用函数进行查询教学难点：8.2利用查询与引用函数进行查询利用查询与引用函数进行查询教 案 正 文（二）【导课】【导课】数据的查询与核对概述数据的查询与核对概述5分钟【教学进程】【教学进程】8.1利用查找命令查询数据l 用“查找”命令进行数据查找的操作步骤用“查找”命令进行数据查找的操作步骤 l 在“查找”命令中利用通配符进行数据查找在“查找”命令中利用通配符进行数据查找8.2利用查询与引用函数进行查询上一节介绍的利用查询命令进行数据和文本查询的方法，只是将指定关键字的位置查询出来。
但是很多情况下，但是很多情况下，除了需要将指定关键字的位置查询出来之外，除了需要将指定关键字的位置查询出来之外，除了需要将指定关键字的位置查询出来之外，还需要还需要将关键字所在行或所在列的数据都查找出来。
将关键字所在行或所在列的数据都查找出来。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
9.1.1 投资函数
4. XNPV函数
语法格式：XNPV(rate, values, dates) 函数功能：计算一组现金流的净现值，这些现金流不一定 定期发生。若要计算定期现金流的净现值，请使用函数NPV。 参 数 说 明 ： rate是 应 用 于 现 金 流 的 贴 现 率， values是 与 dates中的支付时间相对应的一系列现金流转。如果第一个 values值是成本或支付，则它必须是负值，而且values系列 必须至少要包含一个正数和一个负数。所有后续支付都基于 每年365天进行贴现。
【例9-5】假设某人计划贷款8万元装修房子，贷 款5年，月支付额为1600元，计算该笔贷款的月 利率及年利率。
分 析 ： 按 照 贷 数 期 数 共 5*12 月 ， 每 月 末 等 额 支 付 1600 元 ， 贷款现值为8万元计算月利率。
RATE(nper,pmt,pv,[fv],[type],[gu ess])
语法格式：FV(rate, nper, pmt, [fv], [type]) 函数功能：基于固定利率及等额分期付款方式，计算某项 投资在将来某个日期的价值（未来值）。 参数说明：rate为投资利率；nper为投资总期数；pmt为各 期支付金额；fv为未来值或在最后一次支付后希望得到的现 金余额，省略时默认其值为0；type为付款时间类型，省略 时默认其值为0（期末）。
[type])
9.1.1 投资函数
3. NPV函数
语法格式：NPV(rate, value1, [value2], …） 函数功能：基于一系列现金流和固定的各期贴现率，计算 一项投资的净现值（当前纯利润）。净现值越大，投资效益 越好。 参 数 说 明 ： rate 为 某 一 期 间 的 贴 现 率 ， value1 ， value2，…编号可以从1到254，代表支出或收入的现金流。 value1，value2，…所属各期间的长度必须相等，而且都发 生在期末。
【例9-2】假设一个家庭5年后需要一笔比较大的 孩子教育费用支出，计划从现在起每月初存入 2000元，如果按年利率6％，按月计息，那么5 年以后该账户的存款额应该是多少呢？
分析：按照每月利率 6%/12 ， 存 期 共 5*12 月 ， 每月初等额存入2000元， 现值为0进行计算。
FV(rate, nper, pmt, [fv],
1. PV函数
语法格式：PV(rate, nper, pmt, [fv], [type]) 函数功能：根据固定利率计算投资的现值，或者说总额。 参数说明：rate为投资利率；nper为投资总期数；pmt为各 期支付金额；fv为未来值或在最后一次支付后希望得到的现 金余额，省略时默认其值为0；type为付款时间类型，省略 时默认其值为0（期末）。
【例9-4】假定某项投资需要在2019-1-1支付现 金3万元，并于下述时间获取以下金额的返回资 金：2019-7-1返回8750元；2020-1-1返回 7250元；2020-7-1返回16250元；2020-1231返回9750元。假设资金流转贴现率为7％，则 净现值为多少？
分 析 ： 年 贴 现 率 为 7% ， values参数对应所有的 金 额 ， dates 参 数 对 应 相应的日期。
【例9-3】假设要开一家食品加工厂，打算初期 投资20万元，而希望未来4年中各年的收入分别 为5万元、10万元、20万元和40万元。假定每年 的贴现率是8％（相当于通货膨胀率或竞争投资 的利率），问投资的净现值是多少？
分析：年贴现率为8%， 第 一 笔 20 万 元 付 款 发 生 在期初，所以不应包含 在value参数中。
【例9-1】假设要购买一份保险理财产品，一次 性投资30万，投资回报率7%（年回报率），购 买该理财产品后，可以在今后20年内于每月底返 还1500元。该投资合算吗？
分析：表面看1500*12*20=36万，投资可行，但考虑到资 金的时间价值，需要将每月等额收款1500元，按照每月回报 率（7%/12），折现期20*12月进行折现，看其现值是否大 于初始投资金额，如果大于，该投资合算，否则不合算。
【例9-6】假设有人建议你给他投资10万元，期 限4年，那么是每年拿回3万元收益合适还是4年 后一次性拿回13万元收益合适呢？
分析：这其实就是投资回报率多少的问题。按照期数共5年， 每年末等额支付3万元，投资现值为10万元计算投资收益率； 按照期数共4年，每年支付0元，投资现值为10万，未来值 13万计算4年一次性的投资收益率。
【例9-1】假设要购买一份保险理财产品，一次 性投资30万，投资回报率7%（年回报率），购 买该理财产品后，可以在今后20年内于每月底返 还1500元。该投资合算吗？
PV(rate, nper, pmt, [fv], [type]) 根据计算结果可知，该投资合算不合算
9.1.1 投资函数
2. FV函数
XNPV(rate,
dates)
values,
9.1.2 利率函数
功能是计算不同形式的利率。常用的利率函数： 1. RATE函数 2. IRR函数 3. MIRR函数 4. XIRR函数
9.1.2 利率函数
1. RATE函数
语法格式：RATE(nper, pmt, pv, [fv], [type], [guess]) 函数功能：用于计算连续分期等额投资（或贷款）的利率， 也可以计算一次性偿还的投资（或贷款）利率。 参数说明：nper为总投资（或贷数）期数；pmt为各期支 付金额；pv为现值（本金）；fv为未来值或在最后一次付款 后希望得到的现金余额，省略时默认其值为0；type为付款 时间类型，省略时默认其值为0（期末）；guess为预期利 率，省略时默认其值为10%。
第9章财务分析函数及应用
主要内容
9.1.1 投资函数 9.Fra bibliotek.2 利率函数 9.1.3 利息与本金函数 9.1.4 折旧函数
9.1.1 投资函数
功能是计算不同形式的投资回报。 Excel中常用的投资函数有：
1. PV函数 2. FV函数 3. NPV函数 4. XNPV函数
9.1.1 投资函数