Excel数据分析教程

Excel数据分析教程XX学院XX系XXX课程和教材简介本课程为经管学院各专业基础类平台课程、必修课程。本教材由电子工业出版社出版，全书总字数47万，总页数285页，出版日期为2017年8月第1版，2021年9月第2版，定位作为普通高等教育“十三五”规划教材，适用经济、管理大类的各专业，也可用作其他专业选修。Excel数据分析之前——数据获取与准备1Excel数据分析入门——数据透视2Excel数据分析进阶——函数的应用4Excel数据分析初步——模拟运算3Excel数据分析提高——应用加载宏5Excel数据可视化——图表的应用6Excel数据分析自动化——控件和宏的应用7Excel二次开发——VBA和Python的应用8透视分析通过分类汇总、数据透视表、数据透视图、切片器等几个部分介绍了Excel的基本分析方法。数据准备模拟运算介绍了模拟运算表、方案编辑器等分析工具的使用以及目标搜索等分析方法，可算是Excel的通用分析方法入门。常用函数数据展示介绍了Excel各种图表类型的使用范围、图表的基本操作，以及组合图、动态图的制作方法。高级分析控件和宏Excel在统计、预测、规划领域的高级应用，是高级分析方法的应用实践。二次开发课程基本内容掌握数据获取的几种途径以及对数据初步整理的方法排序与筛选。通过一个风险分析的实例，介绍了Excel的主要函数类别，重点是查找与引用函数。通过对VBA以及Python在Excel中的的初步应用的学习，掌握Excel二次开发的基础。通过对控件、宏和加载项的初步学习，为读者进入更高阶段的学习打下了基础。概述获取数据数据准备数据分析概述数据获取数据准备本章概要在现代企业的经营管理过程中，数据已经成为了最重要的资源。因此，高效的数据管理成为了企业成功的关键。本章将简要介绍数据分析的含义、作用和基本流程，并介绍了对数据进行初步整理的方法。1第1章Excel数据分析之前——数据获取与准备学习目标：1、了解数据分析的含义、作用2、理解数据分析的一般过程即基本步骤3、掌握获取数据的几种主要途径4、熟练掌握用Excel进行数据排序和筛选的主要方法本章知识点数据分析概述数据分析的含义数据分析的价值和作用数据分析的基本步骤数据分析中可能的错误数据获取从数据库获取数据从互联网获取数据通过市场调查获取一手数据数据排序默认排序简单排序复杂排序利用函数排序数据筛选自动筛选自定义条件筛选高级筛选1.1数据分析概述①数据的定义数据（data）是观察的结果，是对客观事物的逻辑归纳，是用于表示客观事物的未经加工的原始素材。数据可以是连续的值，比如声音、图像，称为模拟数据。也可以是离散的，如符号、文字，称为数字数据。1、数据分析的含义1.1.1数据分析的含义和基本步骤②数据分析的定义数据分析是指用合适的统计分析方法对收集到的数据进行分析，将这些数据进行汇总，并形成可以被人们理解的资料，从中提取有用的信息，进而发挥数据的作用。概言之，数据分析是为了提取有用的信息和形成结论而对数据进行详细研究与概括总结的过程。数据分析常常以数量的形式展现，通过实验、观察、调查等方式获取结果。1.1数据分析概述1、数据分析的含义1.1.1数据分析的含义和基本步骤1.1数据分析概述1、数据分析的含义1.1.1数据分析的含义和基本步骤③数据分析的发展数据分析的数学基础在20世纪早期就已确立，但直到计算机的出现才使得实际操作成为可能，并使得数据分析得以推广。数据分析是数学与计算机科学相结合的产物。一般来说，数据分析具有3个价值，值得人们去学习和运用。帮助

决策规避

风险把握

动向帮助决策：提供企业现阶段整体运营情况，以及企业各项业务的构成，其中包括各项业务的发展以及变动情况，帮助企业决策者了解企业现状，从而为决策提供起点；规避风险：确定企业所存在的问题的原因，对其原因做出相应的分析并提出解决方案；把握动向：对企业未来的发展趋势做预测，便于企业制定运营目标和计划。1.1数据分析概述2、数据分析的作用和价值1.1.1数据分析的含义和基本步骤价值市场营销方面的作用：精准寻找目标用户用户行为研究行业竞品和行情监控1.1数据分析概述2、数据分析的作用和价值1.1.1数据分析的含义和基本步骤作用运营管理方面的作用：提供最新运营情况为运营管理提供决策支持监控运营活动效能团队绩效考核大数据平台支持方面的作用：平台搭建和维护基础设施进行研发与运维系统性支持数据挖掘和建模产品研发方面的作用：产品优化支持新产品研发支持数据分析在不同的企业所起到的作用大小会有所不同，互联网/电子商务行业对数据分析在市场营销和运营方面的作用尤为重。1.1数据分析概述2、数据分析的作用和价值1.1.1数据分析的含义和基本步骤应用领域数据分析主要包括6个循序渐进的基本步骤，它们缺一不可、相辅相成：目标明确搜集数据整理数据分析数据表现数据得出结论1.1数据分析概述3、数据分析的基本步骤1.1.1数据分析的含义和基本步骤①明确分析目的为数据的收集、处理、分析提供清晰的指引方向。1.1数据分析概述3、数据分析的基本步骤1.1.1数据分析的含义和基本步骤②数据的收集按照确定的数据分析目的来收集相关数据的过程。③数据的整理数据的整理常用的方法包括数据采集、数据分组、数据组织、数据计算、数据存储、数据检索、数据排序等。③数据的处理数据的处理与集成主要是完成对于已经采集到的数据进行适当的处理、清洗去噪，以及进一步的集成存储。常用的数据清洗和整理方法有三种：去重、排序和分组。去重是指删除重复的数据，以减少对后续数据分析步骤的干扰。数据经过排序后，会方便从中识别数据极值，进而发现数据反映的问题。对数据进行分组整理，分析起来就很容易找到头绪。1.1数据分析概述3、数据分析的基本步骤1.1.1数据分析的含义和基本步骤④数据分析一般企业会把数据分析划分为探索性统计分析、验证性数据分析、描述性数据分析3种方式：1.1数据分析概述3、数据分析的基本步骤1.1.1数据分析的含义和基本步骤⑤数据展现将收集的数据通过处理和分析，形成有用的信息，并且用常见的图表方式来进行展现。1.1数据分析概述3、数据分析的基本步骤1.1.1数据分析的含义和基本步骤⑥得出结论，撰写报告报告撰写是数据分析的最后一步，是对整个数据分析过程的总结，对企业决策者来说是一种参考，为决策者提供科学、严谨的决策依据。一份优秀的数据分析报告，需要有一个明确的主题，需要一个清晰的目录，图文并茂地阐述数据、条理清晰地展现，使决策者能一目了然地看出报告的核心内容，这样既能用视觉冲击阅读人的思考，又能很明确地阐述数据分析的核心内容。最后，需要放置结论以及建议，这样不仅可以为决策者指出问题，还可以提供方案和想法，以便决策者在决策时作为参考。1.1数据分析概述3、数据分析的基本步骤1.1.1数据分析的含义和基本步骤1.2数据获取数据的获取是按照确定的数据分析目的来收集相关数据的过程，它为数据分析提供依据，而一般数据来源于3种渠道。理论上，所有包含有用信息的资源我们都可以称之为数据，比如文字、图片甚至视听资源等，但是图片和音像资料需要人工分析并将结构格式化才能得到数据。即使是文字的资料，很多也是非格式化的，不能直接使用Excel来进行分析，因此我们这里所说的获取数据，是指的获取格式化后的表格类型的数据。1.2数据获取（1）从Access数据库获取数据1、从数据库获取数据1.2数据获取（2）从SQL数据库获取数据1、从数据库获取数据1.2数据获取从互联网获取数据：2、从互联网获取数据1.2数据获取市场调查是运用科学的方法，有目的地、系统地收集、记录、整理有关调查信息和资料，为市场预测和营销决策提供客观的数据资料。进行市场调查时，往往就已经有了明确的调查项目，对于调查项目会返回的数据的数量、类型、字节长度等都有了明确的预期，可以直接填入到Excel2016表格中。3、通过市场调查获取数据1.3数据准备1.3.1数据排序文本：按首字拼音的第一个字母排序。数字：从最小的负数到最大的正数排序。日期：从最早的日期到最晚的日期排序。逻辑：按False在前、True在后排序。空白单元格：无论升序或降序都在最后。1、默认排序1.3数据准备1.3.1数据排序（1）按列简单排序2、简单排序（2）按行简单排序1.3数据准备1.3.1数据排序（1）多关键字复杂排序3、复杂排序（2）中文笔划排序1.3数据准备1.3.1数据排序（3）自定义序列排序3、复杂排序（4）让数据按需排序1.3数据准备1.3.1数据排序（5）利用颜色进行排序3、复杂排序1.3数据准备1.3.1数据排序（5）利用颜色进行排序4、利用函数排序1.3数据准备1.3.2数据筛选1、自动筛选1.3数据准备1.3.2数据筛选2、自定义条件筛选1.3数据准备1.3.2数据筛选3、高级筛选小技巧：隔行插入空行利用排序实现上机题1、在Excel条件格式中运用公式进行筛选上机题2、在筛选结果中重新排序上机题3、让表中序号不参与排序课后习题：1、在Excel中，排序能不能按行标题排序？2、在自定义筛选时，如果要筛选“川”开头的字符串，可使用的方法有哪些？如果要筛选包含“川”的字符串，又该怎么做？3、在高级筛选的条件区域中，同一行的条件相互间的关系、不同行的条件之间的关系分别是什么？课后习题：4、操作：使用筛选汇总分析员工工资：①按照“工资”降序排列②按照“部门”升序排列，按照“工资”降序排列③筛选出“高级职员”的相应数据④筛选出“部门”为“开发部”、工资低于5000的数据⑤以“性别”为分类依据，统计男女的人数⑥以“部门”为分类依据，统计各部门的工资总和原始数据如图所示：请将每个操作步骤的结果截图保存课后习题：5、操作：利用模糊筛选从给定范围中筛选出适当的数据模糊筛选通常也可称为通配符筛选，模糊筛选常用的数值类型有数值型、日期型和文本型，通配符“？”和“*”只能配合“文本型”数据使用，如果数据是日期型和数值型，则需要通过设置限定范围（如大于、小于、等于等）来实现。例如，筛选出姓“马”且名字只有一个字的人名的项：Excel数据分析教程XX学院XX系XXX分类汇总切片器数据透视分类汇总与合并运算数据透视表与数据透视图切片器本章概要透视分析本质上是一种微观的、静态的三维数据分析。本章主要通过一些应用实例，介绍了排序与筛选、分类汇总、合并计算和数据透视表的应用，着重说明了Excel中数据透视表和数据透视图的使用方法和操作技巧。2第2章Excel数据分析入门——透视分析学习目标：1、了解数据汇总分析的含义、作用2、理解数据汇总分析的几种主要方法3、掌握分类汇总、分级显示、合并运算和切片器的使用方法4、熟练掌握用数据透视表和数据透视图的创建和应用本章知识点分类汇总创建和使用分类汇总组合分级显示合并运算按位置合并按分类合并表内合并数据透视表制作数据列表编辑数据透视表应用数据透视表数据透视图应用数据透视图趋势线切片器创建分类汇总分级显示汇总2.1分类汇总1、创建和使用分类汇总嵌套分类汇总删除分类汇总2.1分类汇总1、创建和使用分类汇总创建组2.1分类汇总2、组合与分级显示自定义分级显示区域2.1分类汇总2、组合与分级显示源表和目标表在不同的视图中2.2合并计算1、按位置合并源表和目标表在同一视图界面中2.2合并计算1、按位置合并源数据和目标数据在不同工作表且不在同一视图中2.2合并计算2、按分类合并源数据和目标数据在不同工作表上但都在同一视图中2.2合并计算2、按分类合并源数据和目标数据在同一工作表上2.2合并计算2、按分类合并我们用Excel处理数据，通常有两个目的，一是计算数据，二是让数据以一定的格式显示便于我们分析。当需要对明细数据做全面分析时，数据透视表是最佳工具。数据透视表是一种对大量数据快速汇总和创建交叉列表的交互式工具，可以转换行列来查看源数据的不同汇总结果，而且可以显示感兴趣的明细数据，它本质上是一种动态工作表，提供了一种以不同角度审视数据的简便方法。它有机地结合了分类汇总和合并计算的优点，可以方便地调整分类汇总的分类。2.3数据透视表制作数据列表在准备数据时应注意的问题遇到第一行不是标题行的情况遇到第一列是标题行的情况遇到标题字段中包含空白单元格的情况遇到数据源中包含空行、空列和空格的情况遇到数据源中包含合计单元格的情况遇到数据源是二维数据表的情况2.3数据透视表1.数据准备：制作数据列表2.3.1创建数据透视表2.3数据透视表2.创建数据透视表2.3.1创建数据透视表（1）新建数据透视表2.3数据透视表2.创建数据透视表2.3.1创建数据透视表（2）重设数据透视表2.3数据透视表1.数据透视表的编辑2.3.2应用数据透视表（1）添加/删除字段2.3数据透视表1.数据透视表的编辑2.3.2应用数据透视表（2）重新排列字段2.3数据透视表1.数据透视表的编辑2.3.2应用数据透视表（3）设置字段格式2.3数据透视表2.数据显示格式的设置2.3.2应用数据透视表（1）数据排序2.3数据透视表2.数据显示格式的设置2.3.2应用数据透视表（2）数据筛选2.3数据透视表2.数据显示格式的设置2.3.2应用数据透视表（3）分类显示数据2.3数据透视表2.数据显示格式的设置2.3.2应用数据透视表（4）显示或隐藏汇总数据2.3数据透视表2.数据显示格式的设置2.3.2应用数据透视表（5）显示明细数据2.3数据透视表2.数据显示格式的设置2.3.2应用数据透视表（6）调整显示方向2.3数据透视表2.数据显示格式的设置2.3.2应用数据透视表（7）改变数据显示方式2.3数据透视表2.数据显示格式的设置2.3.2应用数据透视表（8）利用颜色增加数据透视表的信息量2.3数据透视表2.数据显示格式的设置2.3.2应用数据透视表（9）设置数据透视表样式2.3数据透视表3.数据分析2.3.2应用数据透视表（1）调整分析步长2.3数据透视表3.数据分析2.3.2应用数据透视表（2）改变计算函数2.3数据透视表3.数据分析2.3.2应用数据透视表（3）添加、修改、删除计算字段2.3数据透视表3.数据分析2.3.2应用数据透视表（4）更新数据数据透视图的创建数据透视图的清除2.3数据透视表1.创建数据透视图2.3.3应用数据透视图更改数据透视图的类型设置数据透视图的布局与格式2.3数据透视表2.调整数据透视图2.3.3应用数据透视图显示筛选的数据更改计算函数更新数据透视图趋势线分析2.3数据透视表3.分析数据透视图2.3.3应用数据透视图在数据透视表中插入切片器通过切片器查看数据透视表中的数据美化切片器2.3数据透视表1.在数据透视表中使用切片器2.3.4应用切片器切片器在非透视表的场景下使用2.3数据透视表2.切片器在非透视表的场景下使用2.3.4应用切片器小技巧：批量修改错误的数据上机题：1、应用数据透视表进行多地区经济指标统计分析上机题：2、应用数据透视表进行股票收益核算上机题：3、应用数据透视表统计结果制作成绩区间课后习题：1、数据透视表的“透视性”和“只读性”指的是什么？2、制作数据透视表的数据源可以是二维数据列表吗？3、合并计算包括哪些类型？4、在进行分类汇总操作之前，除了要确定分类的依据，还必须按什么依据排序？5、在分类汇总结果中查看明细数据，可使用的方法有哪些？课后习题：6、操作：用合并计算核对工作表中的数据：在下图所示的两个工作表的数据中，核对“1月销量”和“2众销量”是否一致。课后习题：7、操作：利用数据透视表进行经济数据的统计分析：要求完成以下分析：计算出每个省市区的2008~2018年的年均国民生产总值、平均人均GDP、平均实际人均GDP。筛选出2018年每个省市区的数据，并对筛选出的数据按照国民生产总值的大小进行排序。计算每年每个省市区实际人均GDP与人均GDP的比重。用现有数据画出2018年每个省市区实际人均GDP与人均GDP的数据透视图。课后习题：8、操作：应用数据透视表成绩占比和排名现有5名职工的各5次文化课考核结果如右：现需要得到每个人的总成绩占比及排名，如左图所示：Excel数据分析教程XX学院XX系XXX模拟运算目标搜索方案分析模拟运算表方案分析目标搜索技术本章概要敏感分析是在财务、会计、管理、统计等应用领域不可缺少的工具。本章介绍了Excel的模拟运算表、方案和单变量求解的应用，在此基础上进行敏感分析的方法，以及应用方案和单变量求解工具辅助决策的方法。3第3章Excel数据分析初步——模拟运算学习目标：1、了解敏感分析的含义、作用2、掌握模拟运算表的两种主要形态的使用方法3、熟练掌握方案的创建、浏览、编辑方法分4、熟练掌握目标搜索技术及其实际应用场景本章知识点模拟运算表单变量模拟运算表双变量模拟运算表从模拟运算表中清除结果其他方面的敏感分析方案分析建立模型创建方案浏览和编辑方案方案摘要目标搜索单变量求解图上求解目标搜索技术的其他应用规划分析建立规划模型求解规划模型分析求解结果所谓模拟运算表实际上是工作表中的一个单元格区域，它可以显示一个计算公式中某些参数的值的变化对计算结果的影响。具体来说，模拟运算表是假设公式中的变量有一组替代值，代入公式取得一组结果值时使用的，该组结果值就构成一个模拟运算表。不过，Excel一个模拟运算表一次只能处理1~2个输入单元格，因此，模拟运算表又分为单变量模拟运算表和双变量模拟运算表。3.1模拟运算表单变量模拟运算表：输入一个变量的不同替代值，并显示此变量对一个或多个公式的影响。下表所示为单变量模拟运算表的一般形式：3.1模拟运算表1、单变量模拟运算表例3-1计算一元方程式：例如，一个简单的数学方程式中含有变量x，只要代入x的值后，即可算出该方程式的答案。也就是说，如果x代入0则答案为-7，如果x代入1则答案为0。当有大量的数字要代入此方程式中并分别计算出不同的结果时，就可以使用模拟运算表。3.1模拟运算表1、单变量模拟运算表例3-2计算购房贷款：例如，用户贷款10万购房，需要了解不同利率情况下每月的还贷金额，就需要使用模拟运算表，不过得先熟悉一下PMT函数，该函数主要是根据固定利率、定期付款和贷款金额，求出每期应偿还的贷款金额。PMT函数格式如下：其中：Rate：每期的利息Nper：付款期数Pv：贷款金额Fv：未来终值，默认值为0，一般银行贷款此值为0Type：缺省为0，表明期末付款，如为1，则表明期初付款3.1模拟运算表1、单变量模拟运算表例3-3计算公司贷款：假设某公司要贷款1000万元，年限为10年，目前的年利率为5％，分月偿还。则利用PMT函数可以计算出每月的偿还额。PMT函数除了用于贷款分析之外，还可以计算出别的以年金方式付款的支付额。例如，如果需要以按月定额存款方式在20年中存款100000，假设存款年利率为4%，则函数PMT可以用来计算月存款额：“＝PMT（4%/12，20*12，0，100000）”，公式计算结果为“272.65”。即向年利率4%的存款账户每月存入272.65元，20年后连本带利可获得100000元。3.1模拟运算表1、单变量模拟运算表双变量模拟运算表：当需要其它因素不变时，两个参数的变化对目标值的影响时，需要使用双变量模拟运算表，其排列方式如下表所示：3.1模拟运算表2、双变量模拟运算表例3-4二元方程：例如，利用双变量模拟运算表求解数学方程式。设有二元函数如下：3.1模拟运算表2、双变量模拟运算表例3-5公司贷款——利率加年限：又如例3-3中，如果不仅要考虑利率的变化，还可以选择贷款年限，这时需要分析不同的利率和不同的贷款期限对贷款的偿还额的影响，这时需要使用双变量模拟运算表。3.1模拟运算表2、双变量模拟运算表例3-6购房贷款——利率加额度：又如例3-2中，利用双变量模拟运算表求解不同贷款与利率的偿还额。如果要同时考虑利率变化情况和贷款额度的不同对月偿还额的影响，可使用又变量模拟运算表求解。3.1模拟运算表2、双变量模拟运算表对于不再需要的运算结果，可以将它们从工作表中清除。由于运算结果是在数组中，所以我们不能清除单个值，而必须清除所有的值。只能选中整个表，包括输入替代值的行、列和计算公式所在的单元格，然后在“开始|编辑”组中选择“清除”命令下的“全部清除”。当然，也可以只选择结果区域执行这个命令，但必须选中所有结果区域，因为模块运算的结果是存放在数组中的。3.1模拟运算表3、从模拟运算表中清除结果模拟运算表主要用来考查一个或两个决策变量的变动对于分析结果的影响，但对于一些更复杂的问题，常常需要考查更多的因素，这就要用到方案了。方案是用于预测工作表模型结果的一组数值，是一组称为可变单元格的输入值，并按照用户指定的名字保存起来。每个可变单元格的集合代表一组模拟分析的前提，用户可以在工作表中创建并保存多组不同的数值，并在这些方案之间任意切换，从而查看不同的方案结果。我们可以为每个方案定义多达32个可变单元格，也就是说对一个模型我们可以使用多达32个变量来进行模拟分析。3.2方案分析例3-7公司损益方案如图所示的是公司某时点的损益表，其中包括了各项指标的计算公式。管理人员希望分析，通过增加销售收入，减少生产费用，降低销售成本等措施对公司利润总额的影响。这可以利用Excel2016的方案工具进行分析，主要包括下述操作。3.2方案分析1、各方案使用不同变量时的操作例3-8购房贷款时多家银行的选择银行1：允许贷款20万，年利率7.5%，贷款年限最长15年；银行2：允许贷款25万，年利率8%，贷款年限最长18年；银行3：允许贷款30万，年利率8.5%，贷款年限最长20年。要求出各自的结果，然后根据自己目前的工资收入来决定选择哪家银行。3.2方案分析2、各方案使用相同变量时的操作例3-9求解最佳的销售方案“新天地蛋糕专卖店”打算推出“青豆派”，请求解最佳的销售方案：方案A：一个月预计销售900个，单价80元，需要两位糕点师，月支付工资共4800元；方案B：一个月预计销售700个，单价60元，需要一位糕点师，月支付工资共2600元；方案C：一个月预计销售800个，单价55元，需要一位糕点师，月支付工资共3000元。3.2方案分析2、各方案使用相同变量时的操作采用模拟计算的方法解决不同因素或不同方案对目标的影响。这对于计划人员、决策人员都是常用的工具。但是对于生产的组织和实施人员来说，经常遇到的是相反的问题。单变量求解是解决假定一个公式想获取某一结果值，其中变量的引用单元格应取值为多少的问题，变量的引用单元格只能是一个，公式对单元格的引用可以是直接的，也可以是间接的。Excel2016根据所提供的目标值，将引用单元格的值不断调整，直至达到所要求的公式的目标值时，变量的值才确定。3.3目标搜索1、单变量求解例3-10单变量求解假设公司下个月的利润总额指标定为一个具体数值，要考查当其他条件基本保持不变的情况下，销售收入需要增加到多少。3.3目标搜索1、单变量求解例3-11学期成绩分布学校为了全面考核学生的学期成绩，需要结合学生的平时成绩、期中考试成绩和期末考试成绩，具体为3:3:4。现在，某生已知平时成绩92分、期中成绩84分，而家长希望值是总成绩90分，那么，该生需要在期末考多少分才能达成目标？3.3目标搜索1、单变量求解例3-12商品理想售价的制订公司花了一大笔钱（固定成本50000，销售费用45000），欲将某个产品投入市场，已知产品单片成本为7.2元、产量能达到5000，想知道产品售价是多少时，才能盈利170000元？3.3目标搜索1、单变量求解其中：销售金额=单价×数量生产成本=数量×单片成本利润=销售金额–销售费用–生产成本–固定成本利用目标搜索技术可以求解许多类似的问题。例如，已知某企业近5年每月偿还贷款的能力为100000元，要计算其可以承受的贷款额度，使用目标搜索技术则可以直接求解。这些问题归纳起来都是数学上的求解反函数问题，即对已有的函数，给定y的值，反过来求解x。一般情况下可以按照y与x的依赖关系，构造一个反函数。但是当变量之间的依赖关系较为复杂，特别是对于非线性函数，构造反函数的工作也是较为复杂繁琐的。而利用目标搜索技术，则可以利用直接函数方便地完成反函数的计算。基本上，在创建数学公式时，必须先将公式中的变量x视为工作表上的某个单元格，称为变量单元格，然后在另一个单元格中输入含有此变量单元格的公式。只要改变变量单元格的内容，该公式将自动重新计算出新的结果。3.3目标搜索2、其他应用例3-13求解方程3.3目标搜索2、其他应用例3-15一元一次方程的实例：有一个两位数，其十位数与个位数的数字之和为13，若将十位数与个位数交换，所得到的新的两位数的数值比原来的两位数的数值小27。求原来的两位数是什么？假定十位数是x，则个位数就是13-x了。这样，原来的两位数就可以表述为：而当个位数与十位数交换位置后，新的两位数可以表述为：又因为新数比旧数小27，因此：将方程按写进单元格中，以B2单元格为空（即x为0），方程式的值为-117。3.3目标搜索2、其他应用小技巧：统一添加单位上机题：1、用模拟运算制作乘法九九口诀表上机题：2、用双变量模拟运算表求解多元方程上机题：3、用方案分析不同的单品产量对总利润的影响上机题：4、单变量求解非线性方程课后习题：1、模拟运算表的运算结果区域可以单独更改吗？2、对单元格区域批量命名时，如果使用“根据所选内容创建”方式，则选定区域中可以用来命名的值可以位于选定区域的哪些地方？3、要完成单变量求解的操作，正确的做法有哪些？4、写出计算下述方程式的表达式（用单元格C1和D1做变量）：课后习题：5、操作：制作某公司投资方案表，原表如下：要求第1步，定义名称，在“公式|定义的名称”组中“定义名称|定义名称”中将B3:B5单元格式定义为左侧单元格内文本所示的名称；第2步，创建方案，在“数据|预测”组中执行“模拟分析|方案管理器”，按如下条件开始创建方案：方案A：投资10万，年限5年，收益率24%；方案B：投资12万，年限6年，收益率25%；方案C：投资15万，年限8年，收益率28%。课后习题：6、操作：单变量求解可贷款金额：某企业向银行贷款的年利率为7.67%需要贷款期限为10年，企业每年的偿还能力为50万元，现在要计算出企业可贷款额度为何？7、操作：单变量求解贷款年限：某企业需要贷款100万元，年利率为7.67%，企业能承担的年偿还额为20万元，需要贷款多少年？Excel数据分析教程XX学院XX系XXX风险分析公式使用函数应用风险分析函数运用公式编辑技术本章概要本章主要通过企业的经营决策问题介绍了确定型分析、不确定型分析和风险分析的基本概念和应用方法，说明了Excel中有关函数的使用方法和技巧，并进而对Excel2016中的函数进行了分类介绍。4第4章Excel数据分析进阶——函数的应用学习目标：1、了解风险分析的含义、作用2、掌握不同风险前提下的不同目标求解方法和风险应对原则3、熟练掌握Excel中常用的几种函数的几个代表函数：文本函数逻辑函数日期及时间函数数学与三角函数4、重点掌握查找与引用函数本章知识点确定性分析单目标求解多目标求解不确定性分析乐观原则悲观原则中庸原则遗憾原则风险分析期望值法遗憾期望值法常用函数文本函数逻辑函数统计函数查找与引用函数日期及时间函数数学与三角函数4.1使用函数进行决策分析1、确定性分析（1）单目标求解所谓确定性分析是指决策的问题只存在一种自然状态，即未来的事件以及与事件有关的各种条件都是确定的。这时的决策比较简单，只需计算出各种条件下的成本、收益等指标，按照特定的目标从中选择最佳方案即可。而其中最简单的是单目标分析。假设某电器公司计划通过其销售网络推销一种廉价电器产品。计划销售价为10元/台。该电器的生产有三个方案：方案1需投资100,000元，投产后每台电器成本为5元；方案2需投资160,000元，投产后每台电器成本为4元；方案3需投资250,000元，投产后每台电器成本为3元。如果该电器的市场需求量为120,000台，应选择哪种生产方案可获得最大收益？4.1使用函数进行决策分析1、确定性分析（1）单目标求解由于该决策问题中，不同方案的投资费用、生产成本和可获得的利润都是确定的，所以可以直接按要求进行计算。属于典型的单目标求解。4.1使用函数进行决策分析1、确定性分析（1）单目标求解在本例中当然从图中就可以明显看到方案3收益最大。但为了能够处理更多方案的情况，以及便于当方案修改时都能快捷地找到最佳方案，我们还是要利用Excel2003的查找函数自动选择最佳方案。（以下操作）特别注意，在使用Lookup函数时，查找区域应按升序排序，否则将无法正确实现查找要求。4.1使用函数进行决策分析1、确定性分析（2）多目标求解有些决策问题的目标可能有多个，而且有可能这多个目标之间是相互矛盾的。例如宏观经济调控的决策，就可能有国民生产总值最高，人民生活水平最高，物价指数最低，以及发展速度平稳等多个指标。这时不同的方案就难以简单地用最大值、最小值函数比较优劣。这时可根据多个方案计算出一个理想方案，然后再计算出各方案与理想方案的“距离”，从中选择与理想方案“距离”最近的方案。以下仍以上例来说明操作步骤。（以下操作）4.1使用函数进行决策分析上面的分析中是假设已知市场需求为120,000台，在市场经济条件下，更多的情况是不知道确切的市场需求，而只是对未来市场需求有大致的估计。例如上例，假设可能出现三种自然状态：滞销状态，市场需求30,000台；一般状态，市场需求120,000台；畅销状态，市场需求200,000台。这时可得到该问题的损益矩阵如图所示。此表是将前面图中的E2单元格中的数据分别用30000、200000台替代后重新粘贴数值得到的。4.1使用函数进行决策分析2、不确定性分析（1）乐观原则所谓乐观原则是看好未来市场需求，在决策时总是选择收益最大或是损失最小的方案。其基本思路是先由各方案计算出收益最大值，然后在每个方案的最大值中找出最大值。所以也称作大中取大法。具体计算方法如下：在E列利用MAX函数计算出各方案在不同状态下的最大值。在6行自动查找最大值所在列中最大者所对应的方案名。计算结果如图所示。4.1使用函数进行决策分析2、不确定性分析（2）悲观原则所谓悲观原则是决策时从最坏情况出发，尽量减少风险。其基本思路是先由各方案计算出收益最小值，然后在每个方案的最小值中找出最大值。所以也称作小中取大法。具体计算方法如下：在E列利用MIN函数计算出各方案在不同状态下的最小值。在6行自动查找最小值所在列中最大者对应的方案名。计算结果如图所示。4.1使用函数进行决策分析2、不确定性分析（3）中庸原则更多的情况下，决策时即不简单地根据乐观原则，也不完全按照悲观原则，而是采用介于二者之间的中庸原则。这样即不过于冒险，也不过于保守。其基本思路是先由决策者主观地选取一个介于0～1之间的乐观系数α，然后依据下述公式计算出各方案的中庸数。4.1使用函数进行决策分析2、不确定性分析（3）中庸原则也就是每种方案的损益值最大出现的可能性加上其他可能性（其实就是最小值出现的可能性，因不考虑其他可能性；并且因为没有其他可能性，最小值出现的可能性只能是100%减去最大值出现的可能性）。其中H（Ai）为第i个方案的中庸数，Rij为第i个方案第j个状态的损益值。最后在每个方案的中庸数中找出最大值。可称之为中中取大法。（以下操作）4.1使用函数进行决策分析2、不确定性分析（4）遗憾原则决策时将每种状态的收益最高值作为该状态的理想目标，并将相应状态下其他值与理想值的差称作未达到理想值的遗憾值。其基本思路是先由各方案计算出理想值，再根据理想值计算出每个方案不同状态下的遗憾值（遗憾矩阵），然后根据遗憾矩阵计算出每个方案的最大遗憾值，最后在各最大遗憾值中选取最小者作为最佳方案，即根据遗憾矩阵采用大中取小法选出最佳方案。（以下操作）4.1使用函数进行决策分析3、风险分析风险分析与确定型分析和非确定型分析不同，这时决策人虽然对于未来出现那种状态不能做出确定的判断，但能根据有关资料估计或计算出各种状态出现的概率。例如上例，虽然不能确定该电器的未来市场需求状态是滞销、一般还是畅销，但是根据有关历史数据、市场调查资料等信息，知道这三种状态出现的概率分别是0.40、0.55和0.05。这时无论做出什么选择，都有一定的风险。所以这类决策问题称作风险分析。4.1使用函数进行决策分析3、风险分析（1）期望值法所谓期望值法，即先根据损益矩阵和各状态的概率，按照下述公式计算出每个方案的期望值：也就是每种方案各种状态下的损益与各种状态出现的可能的乘积的和，其中E（Ai）为第i个方案的期望值，P（Sj）为第j种状态的概率，Rij为第i个方案第j种状态下的收益。计算出各方案的期望值后再从中选取最大者。4.1使用函数进行决策分析3、风险分析（2）遗憾期望值法所谓遗憾期望值法是先由原损益矩阵计算出遗憾矩阵，然后再利用期望值法根据遗憾矩阵选择最佳方案。也就是说，该方法与期望值法不同的主要之处在于前者是根据损益矩阵计算，而后者是根据遗憾矩阵计算。因此，前者最后是从期望值中选择最大这作为最佳方案，而后者应从期望值中选取最小者作为最佳方案。4.2.1文本函数1、查找字符4.2常用Excel函数FIND(find_text,within_text,start_num)FINDB(find_text,within_text,start_num)find_text：要查找的文本within_text：包含要查找的文本的源文本start_num：指定要从文本起始位置查找的字符（1）求字符串位置——FIND和FINDB函数4.2.1文本函数1、查找字符4.2常用Excel函数find_text：要查找的文本within_text：是指要在其中搜索find_text的源文本start_num：指within_text中开始搜索的字符编号SEARCH(find_text,within_text,start_num)SEARCHB(find_text,within_text,start_num)（2）求字符位置——SEARCH和SEARCHB函数4.2.1文本函数2、转换文本4.2常用Excel函数number：用于转换的字符代码，在1~255之间，使用的是当前计算机所用字符集中的字符，如Windows操作系统为ANSI，CHAR函数可以将计算机识别的ASCII代码还原为能识别的常规字符text：为需要得到其第一个字符代码的文本字符串，也可是引用其他单元格的文本字符串（1）转换数字与字符——CHAR和CODE函数CHAR(number)CODE(text)4.2.1文本函数2、转换文本4.2常用Excel函数text：为要转换的大小写字母文本，也可以为引用或文本字符串（2）转换大小写——LOWER、UPPER和PROPER函数4.2.1文本函数2、转换文本4.2常用Excel函数text：为文本，或对包含需要更改文本的单元格的引用（3）转换字节——ASC和WIDECHAR函数ASC(text)WIDECHAR(text)4.2.1文本函数2、转换文本4.2常用Excel函数value：表示要进行转换的数值，可以为数值、对包含数字值的单元格的引用，或计算结果为数字值的公式format_text：指要转换的数字格式，可以为“单元格格式”对话框的“数字”选项卡中“分类”列表框中的文本形式的的效果如图所示：数值格式，它不能包含星号“*”。（4）转换数字格式——TEXT函数TEXT(value,format_text)4.2.1文本函数2、转换文本4.2常用Excel函数text：为代表数字的文本，或对需要进行文本转换的单元格的调用，可以是Excel中可识别的任意常数、日期或时间格式。如果“text”不是这些格式，则函数VALUE返回错误值“#VALUE!”，表示参数引用值错误。（5）将表示数字的文本转换为数字——VALUE函数VALUE(text)4.2.1文本函数2、转换文本4.2常用Excel函数number：为数字、包含数字的单元格引用或计算结果为数字的公式decimals：为十进制的小数位数（6）转换货币符号——DOLLAR和RMB函数DOLLAR(number,decimals)RMB(number,decimals)4.2.1文本函数2、转换文本4.2常用Excel函数（7）文本函数案例1——利用文本函数转换文本4.2.1文本函数3、编辑字符串4.2常用Excel函数text1、text2…：为2~255个需要合并的文本字符串，这些文本项可以为文本字符串、数字或对单个单元格的引用。（1）合并字符串——CONCATENATE函数CONCATENATE(text1,[text2],…)4.2.1文本函数3、编辑字符串4.2常用Excel函数text：需要替换其中字符的文本，或对含有文本的单元格的引用old_text：需要被换掉的旧文本new_text：用于替换旧文本的新文本，如果不指定，则用空文本替换，实际上就是删除instance_num：为一个数值，用来指定以新文本替换第几次出现的旧文本。如果此参数被指定，则只有满足条件的旧文本被替换，否则将替换所有的旧文本。（2）替换文本——SUBSTITUTE函数SUBSTITUTE(text,old_text,new_text,[instance_num])4.2.1文本函数3、编辑字符串4.2常用Excel函数old_text：指要在其中替换字符的文本start_num：指要用new_text替换的old_text中字符的位置num_chars：指希望REPLACE函数使用new_text替换old_text中字符的个数new_text：指要用于替换old_text中字符的文本num_bytes：指希望REPLACEB函数使用new_text替换old_text中字节的个数（3）替换字符串——REPLACE和REPLACEB函数REPLACE(old_text,start_num,num_chars,new_text)REPLACEB(old_text,start_num,num_bytes)4.2.1文本函数3、编辑字符串4.2常用Excel函数text：是指需要清除其中空格的文本。（4）清除空格——TRIM函数TRIM(text)4.2.1文本函数3、编辑字符串4.2常用Excel函数text：是指需要查找其长度的文本（5）求长度——LEN和LENB函数LEN(text)LENB(text)4.2.1文本函数3、编辑字符串4.2常用Excel函数text1、text2：指待比较的两个字符串。（6）判断字符串异同——EXACT函数EXACT(text1,text2)4.2.1文本函数3、编辑字符串4.2常用Excel函数number：指进行四舍五入并转换为文本字符串的数字decimals：唯一数值，用于指定小数点右边的小数位数，如果为负数，表示四舍五入到小数点左边no_commas：逻辑值，如果为TRUE，则禁止FIXED函数在返回的文本中包括逗号（7）指定位数取整——FIXED函数FIXED(number,decimals,no_commas)4.2.1文本函数3、编辑字符串4.2常用Excel函数（8）文本函数案例2——利用文本函数取整4.2.1文本函数4、返回相应值4.2常用Excel函数text：指包含要提取的字符的文本字符串num_chars：指定要提取的字符的数量，不能为0。如果此数值大于text的文本长度，则返回全部文本（1）返回左右两侧字符——LEFT和RIGHT函数LEFT(text,num_chars)RIGHT(text,num_chars)4.2.1文本函数4、返回相应值4.2常用Excel函数text：要提取字符的文本字符串start_mum：文本中要提取的第一个字符的位置（文本中第一个字符为1，以此类推）num_chars：指定希望MID函数从文本中返回字符的个数num_bytes：指定希望MIDB函数从文本中按字节返回字符的个数（2）返回中间字符——MID和MIDB函数MID(text,start_mum,num_chars)MIDB(text,start_mum,num_bytes)4.2.1文本函数4、返回相应值4.2常用Excel函数text：需要重复显示的文本number_times：指定文本重复次数的正数（3）重复显示文本——REPT函数REPT(text,number_times)4.2.1文本函数4、返回相应值4.2常用Excel函数value：是要进行检测的值，如果值是文本或引用了文本，T将返回值，如果值未引用文本，T将返回空文本。（4）显示文本——T函数T(value)4.2.1文本函数4、返回相应值4.2常用Excel函数（5）文本函数案例3——利用文本函数返回字符4.2.2逻辑函数4.2常用Excel函数（1）IF函数及其嵌套——条件判断IF(logical_test,value_if_true,value_if_false)logical_test：条件表达式value_if_true：条件为真时的操作value_if_false：条件为假时的操作4.2.2逻辑函数4.2常用Excel函数（2）AND函数——条件全部成立AND(logical1,logical2,…)logical1、logical2、…：逻辑判断表达式4.2.2逻辑函数4.2常用Excel函数（3）OR函数——条件之一成立OR(logical1,logical2,…)logical1、logical2、…：逻辑判断表达式4.2.2逻辑函数4.2常用Excel函数（4）NOT函数——转换逻辑值NOT(logical)logical：逻辑判断表达式，必需4.2.2逻辑函数4.2常用Excel函数（5）逻辑函数课堂练习1——统计招聘考试成绩4.2.2逻辑函数4.2常用Excel函数（6）直接返回逻辑值——TRUE和FALSE函数下面使用TRUETFALSE函数以及AND、IF函数判断考勤表4.2.2逻辑函数4.2常用Excel函数（7）处理函数中的错误——IFERROR函数value：需要检测是否存在错误的参数value_if_error：若公式计算出错是返回的值。计算得到的错误类型有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL!等。IFERROR(value,value_if_error)4.2.3统计函数4.2常用Excel函数（1）COUNTA函数——计数非空单元格value1、value2、…：为1~255个参数，代表要进行计算的值和单元格。值可以是任意类型的信息COUNTA(value1,value2,…)4.2.3统计函数4.2常用Excel函数（2）COUNTIF函数——计数满足条件的单元格range：为计算、筛选条件的单元格区域criteria：为筛选的条件或规则COUNTIF(range,criteria)4.2.3统计函数4.2常用Excel函数（3）FREQUENCY函数——计算符合区间的函数data_array：要计算出现次数的数据来源范围bins_array：数据区间分组的范围FREQUENCY(data_array,bins_array)4.2.4查找与引用函数1、查找函数4.2常用Excel函数lookup_value：在第一个向量（即1行或1列）中要查找的值lookup_vector：第一个向量区域result_vector：第二个向量区域，其大小须与第一个向量区域相同（1）LOOKUP函数——查找数据①LOOKUP函数的向量形式LOOKUP(lookup_value,lookup_vector,result_vector)4.2.4查找与引用函数1、查找函数4.2常用Excel函数lookup_value：在数组中要搜索的值array：与lookup_value进行比较的数组（1）LOOKUP函数——查找数据②LOOKUP函数的数组形式LOOKUP(lookup_value,array)4.2.4查找与引用函数1、查找函数4.2常用Excel函数lookup_value：需要在数据表首列搜索的值，可以是数值、引用或字符串table_array：需要在其中搜索数据的表，可以是对区域或区域名称的引用col_index_num：满足条件的单元格在数据区域table_array中的列序号，首列序号为1range_lookup：指定是大致匹配（为TRUE或忽略或任意非0值，包括小数和负数）或是精确匹配（为0或FLASE）（2）VLOOKUP函数——垂直查找VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)4.2.4查找与引用函数1、查找函数4.2常用Excel函数例如，在如图所示的区域中列表中第一列中的“2”，如果查到就返回“2”所在那一行中第3列的值，要求精确查找：（2）VLOOKUP函数——垂直查找4.2.4查找与引用函数1、查找函数4.2常用Excel函数如果要查询的那一列中没有“2”这个值，那么，模糊查询会返回仅比查找值“2”小的那个值所对应的值，如图所示：（2）VLOOKUP函数——垂直查找4.2.4查找与引用函数1、查找函数4.2常用Excel函数但如果使用了精确查找，就会返回出错信息，如图所示：（2）VLOOKUP函数——垂直查找4.2.4查找与引用函数1、查找函数4.2常用Excel函数垂直查找：制作个人成绩单（2）VLOOKUP函数——垂直查找4.2.4查找与引用函数1、查找函数4.2常用Excel函数lookup_value：需要在数据表首行搜索的值，可以是数值、引用或字符串table_array：数据列表的范围row_index_num：找到值时，返回该值所在列中第几行的数据range_lookup：逻辑值，此值为0时，需要精确匹配（3）HLOOKUP函数——水平查找HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)4.2.4查找与引用函数1、查找函数4.2常用Excel函数lookup_value：在列表中要找的值lookup_array：列表区域match_type：指定对比的方式，有-1、0、1三种值（4）MATCH函数——查找位置MATCH(lookup_value,lookup_array,[match_type])4.2.4查找与引用函数1、查找函数4.2常用Excel函数index_num：指定的参数值，它必须是1~254的数字，或者是值是1~254的公式或单元格引用value1、value2、…：表示待选数据，其数量是可选的，为1~254个数值参数，可以为数字、单元格引用、定义名称、公式、函数或文本。（5）CHOOSE函数——在列表中选择值CHOOSE(index_num,value1,value2,…)4.2.4查找与引用函数1、查找函数4.2常用Excel函数（6）查找与引用函数案例1——查询员工信息4.2.4查找与引用函数2、引用函数4.2常用Excel函数row_num：单元格引用中使用的行号column_num：单元格引用中使用的列标abs_num：指定返回的引用类型a1：指定引用的逻辑类型：TRUE或缺省为A1样式；FALSE则为R1C1样式sheet_text：引用的工作表名称，缺省则不使用任何工作表名（1）ADDRESS函数——显示引用地ADDRESS(row_num,column_num,abs_num,a1,sheet_text)4.2.4查找与引用函数2、引用函数4.2常用Excel函数reference：需要得到其列标（或行号）的单元格（2）COLUMN、ROW函数——返回引用的列标、行号COLUMN(reference)ROW(reference)4.2.4查找与引用函数2、引用函数4.2常用Excel函数array：需要得到其列数（或行数）的数组、数组公式或对单元格区域的引用。（3）COLUMNS、ROWS函数——返回引用的列数、行数COLUMN(array)ROW(array)4.2.4查找与引用函数2、引用函数4.2常用Excel函数reference：表示对某个单元格区域的引用，也可以是对多个区域的引用。如果reference参数需要将几个引用指定为一个参数，则必须用括号括起来，否则Excel将提示输入太多的参数。（4）AREAS函数——返回区域数量AREAS(reference)4.2.4查找与引用函数2、引用函数4.2常用Excel函数array：单元格区域或数组常量row_num：选择数组中的某行，函数从该行返回数值column_num：选择数组中的某列，函数从该列返回数值（5）INDEX函数——返回指定行列交叉值（交叉查找）①INDEX函数的数组形式INDEX(array,row_num,[column_num])4.2.4查找与引用函数2、引用函数4.2常用Excel函数reference：表示对一个或多个单元格区域的引用row_num：引用中的行序号column_num：引用中的列序号area_num：当reference有多个引用区域时，用于指定从其中某个引用区域返回指定值。该参数如果缺省，返回第1个引用区域。（5）INDEX函数——返回指定行列交叉值（交叉查找）②INDEX函数的引用形式INDEX(reference,row_num,column_num,[area_num])4.2.4查找与引用函数2、引用函数4.2常用Excel函数（5）INDEX函数——返回指定行列交叉值（交叉查找）INDEX函数的一个实例——铁路票价查询4.2.4查找与引用函数2、引用函数4.2常用Excel函数（6）INDIRECT函数——返回引用地INDIRECT(ref_text,a1)ref_text：表示对单元格的引用，如果ref_text是对另一个工作簿数据的引用，则该工作簿必须打开，否则将返回“#REF!”a1：输入为逻辑值，表示指定返回的引用样式，其值为TRUE或缺省时，使用A1样式，为FALSE时，为R1C1样式4.2.4查找与引用函数2、引用函数4.2常用Excel函数（7）OFFSET函数——偏移引用位置OFFSET(reference,rows,cols,height,width)reference：表示作为偏移量参照系的引用区域rows：表示相对偏移量参照系左上角的单元格上下偏移的行数，其中往下为正，往上为负cols：表示相对偏移量参照系左上角的单元格左右偏移的列数，其中往右为正，往左为负height：表示返回引用区域的行数width：表示返回引用区域的列数4.2.4查找与引用函数2、引用函数4.2常用Excel函数（8）HYPERLINK函数——快速跳转HYPERLINK(link_location,fridndly_name)link_location：文档路径和文件名，可以是存储在本地、远程的文件，也可以是指向文档中某个锚点（此时应使用#号）。fridndly_name：表示单元格中显示的跳转文本值或数字值，格式为蓝色带下划线，如缺省则将以link_location显示为跳转文本4.2.5日期及时间函数1、返回类日期及时间函数4.2常用Excel函数各种返回类日期与时间函数如下图所示4.2.5日期及时间函数1、返回类日期及时间函数4.2常用Excel函数使用返回类日期与时间函数计算车辆使用年限示例4.2.5日期及时间函数2、获取当前日期与时间4.2常用Excel函数（1）NOW函数——显示当前时间该函数无参数，直接显示当前日期与时间。（2）TODAY函数——返回当前系统的日期TODAY函数可返回当前系统的日期，该函数无参数。4.2.5日期及时间函数3、求特定日期与时间4.2常用Excel函数（1）DATEDIF函数——计算日期间隔（旧版函数）（2）求日期差——DAYS360函数（3）求天数比——YEARFRAC函数（4）求星期几——WEEKDAY函数（5）求特定日期与月末日期——EDATE与EMONTH函数（6）求工作日——NETWORKDAYS函数（7）求特定工作日日期——WORKDAY函数4.2.6数学与三角函数1、基本数学函数4.2常用Excel函数（1）求组合数——COMBIN函数（2）计算指数的乘幂——EXP函数（3）计算对数——LN、LOG和LOG10函数（4）计算余数——MOD函数（5）计算乘积——PRODUCT函数（6）获取数字的正负号——SIGN函数（7）计算随机数——RAND函数4.2.6数学与三角函数2、汇总函数4.2常用Excel函数（1）按条件汇总——SUMIF函数（2）求平方和——SUMSQ函数（3）计算分类汇总——SUBTOTAL函数（4）计算平方差之和——SUMX2MY2函数（5）计算平方和之和——SUMX2PY2函数4.2.6数学与三角函数3、舍入函数4.2常用Excel函数（1）按条件向上舍入——CEILING函数（2）向上舍入为奇数或偶数——ODD、EVEN函数（3）按条件向下舍入——FLOOR函数（4）向下取整——INT函数（5）截尾取整——TRUNC函数（6）四舍五入——ROUND、ROUNDDOWN和ROUNDUP函数4.2.6数学与三角函数4、三角函数4.2常用Excel函数（1）将弧度转换为角度——DEGREES函数（2）计算弧度值——RADIANS函数（3）计算正弦值——SIN函数（4）计算反正弦值——ASIN函数（5）计算余弦值——COS函数（6）计算反余弦值——ACOS函数（7）计算正切值——TAN函数（8）计算反正切值——ATAN、ATAN2函数小技巧：在单元格中输入分数上机题：1、利用LEFT和RIGHT函数判断客户性别上机题：2、利用VLOOKUP函数制作个人简历上机题：3、用日期与时间函数计算停车费用上机题：4、利用数学与三角函数管理原材料明细账课后习题：1、操作：利用多种文本函数，为下表中的电话号码从11位升至12位，要求凡是13X开头的手机号码，X如果是奇数，升位为“131X”；X如果是偶数，则升位为“132X”。原始数据如下：课后习题：2、操作：利用LOOKUP函数的向量形式查询员工个人信息，原文件如下：要求查询结果如下：课后习题：3、操作：运用TIME函数等日期与时间函数计算考核时间。原始文件如下：课后习题：4、操作：利用DATE函数和TODAY函数制作一个高考倒计时天数表如图：Excel数据分析教程XX学院XX系XXX统计分析规划分析预测分析描述性统计时间序列预测分析规划分析本章概要本章以Excel2016提供的数据分析工具加载宏的使用为纲，分别介绍了描述性统计分析（集中、离中、偏度、峰度）、预测分析（移动平均、指数平滑），以及解决生产管理和经营决策中的最优配置问题规划分析。5第5章Excel数据分析提高——应用加载宏学习目标：1、了解统计分析、预测分析、规划分析的含义、作用2、初步掌握使用Excel进行移动平均预测的方法3、熟练掌握描述性统计常用的集中和离中分析指标和分析工具、时间序列预测分析工具4、理解并重点规划分析的建模方法本章知识点描述性统计几何平均值调和平均值众数中位数预测分析方差与标准差偏度峰度预测分析移动平均法指数平滑法同季周期平均法规划分