《Excel数据分析工具及应用.ppt》由会员分享，可在线阅读，更多相关《Excel数据分析工具及应用.ppt（47页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、EXCELEXCEL基础及基础及应用应用第第5 5章章 Excel Excel数据分析工具及应用数据分析工具及应用数据分析工具的安装与卸载数据分析工具的安装与卸载 模拟运算表模拟运算表 方案分析方案分析 1234 规划求解规划求解 5.1 5.1 数据分析工具的安装与卸载数据分析工具的安装与卸载5.1.1安装分析工具库安装分析工具库“分分析析工工具具库库”是是一一个个加加载载宏宏文文件件，文文件件名名为为（加加载载宏宏文文件件是是可可以以自自动动执执行行一一系系列列复复杂杂任任务务的的操操作作命命令令的的组组合合）。但但这这一一工工作作在在安安装装MicrosoftOffice组件时已经加载。
2、组件时已经加载。v安装步骤如下安装步骤如下打打开开Excel工工作作簿簿，选选择择菜菜单单栏栏中中【工工具具】菜菜单单中的【加载宏】，如图中的【加载宏】，如图5-1所示。所示。图图5-15-1加载宏加载宏在打开在打开“加载宏加载宏”对话框，对话框如图对话框，对话框如图5-2所所示，单击【分析工具库】复选框，框前即出示，单击【分析工具库】复选框，框前即出现现号，表明已被选定，选择【确定】。号，表明已被选定，选择【确定】。图5-2选择分析工具库5.1.2卸载分析工具库卸载分析工具库v当不需要使用分析工具库时，可以通过设置当不需要使用分析工具库时，可以通过设置来卸载分析工具库。来卸载分析工具库。v操
3、作步骤：操作步骤：打开打开Excel工作簿，选择菜单栏中【工具】菜单工作簿，选择菜单栏中【工具】菜单中的【加载宏】，如图中的【加载宏】，如图5-1所示。所示。在打开在打开“加载宏加载宏”对话框，对话框如图对话框，对话框如图5-4所示，所示，单击【分析工具库】复选框，将框前出现单击【分析工具库】复选框，将框前出现号取号取消。消。选择取消后，单击【确定】按钮，即可自动卸选择取消后，单击【确定】按钮，即可自动卸载分析工具库。自此在载分析工具库。自此在“工具工具”菜单中的菜单中的“数数据分析据分析”选项自动隐藏。选项自动隐藏。图5-4卸载分析工具库5.2模拟运算表模拟运算表v什么是模拟运算表？什么是模
4、拟运算表？Excel模拟运算表是对工作表中一个单元格区域模拟运算表是对工作表中一个单元格区域内的数据进行模拟运算，测试使用一个或两个变内的数据进行模拟运算，测试使用一个或两个变量的公式中变量对运算结果的影响。量的公式中变量对运算结果的影响。模拟运算表的类型模拟运算表的类型基于一个输入变量的表，用这个输入变量测试它对多基于一个输入变量的表，用这个输入变量测试它对多个公式的影响；个公式的影响；单变量模拟运算表单变量模拟运算表基于两个输入变量的表，用这两个变量测试它们对于基于两个输入变量的表，用这两个变量测试它们对于单个公式的影响单个公式的影响双变量模拟运算表双变量模拟运算表5.2.1单变量模拟运算
5、表单变量模拟运算表【例【例5-1】某人想买房，由于资金不足需要向银行贷某人想买房，由于资金不足需要向银行贷款款30万，还款年限为万，还款年限为10年，目前年利率假设为年，目前年利率假设为4%，采取每月等额还款的方式。，采取每月等额还款的方式。请帮助该买房人计算在现有利率下每月的偿还额。请帮助该买房人计算在现有利率下每月的偿还额。根据国家经济的发展，银行利率也会有调整，要根据国家经济的发展，银行利率也会有调整，要了解利率变动对自身的偿还能力的影响。请帮助该了解利率变动对自身的偿还能力的影响。请帮助该买房人计算不同利率条件下的每月偿还额。买房人计算不同利率条件下的每月偿还额。由于买房人经济收入并不
6、是很稳定，需要根据实由于买房人经济收入并不是很稳定，需要根据实际情况调整还款期限。请帮助该买房人计算出还款际情况调整还款期限。请帮助该买房人计算出还款期限是期限是5年、年、10年、年、15年、年、20年、年、25年和年和30年时，年时，每月等额还款的数额。每月等额还款的数额。单变量模拟运算表主要是用来分析当其他因素单变量模拟运算表主要是用来分析当其他因素不变时，一个参数的变化对目标结果的影响。不变时，一个参数的变化对目标结果的影响。v问题问题求解求解：可利用：可利用PMT函数计算。函数计算。(1)使用【文件使用【文件/新建】或【新建】工具，建立一新工作簿，新建】或【新建】工具，建立一新工作簿，
7、并选择一张工作表，将住房贷款有关的基本数据输入该并选择一张工作表，将住房贷款有关的基本数据输入该工作表，如图工作表，如图5-5（B4:D8）单元格区域所示。）单元格区域所示。(2)在在D9单元格中输入公式单元格中输入公式“=PMT(D5/12,D8,D4)”，按下【按下【Enter】键，计算出所要的结果。上述公式中三个】键，计算出所要的结果。上述公式中三个参数的含义是：第一个参数是利率，由于偿还额按月计参数的含义是：第一个参数是利率，由于偿还额按月计算，所以年利率除以算，所以年利率除以12，将其转换为月利率。第二个参，将其转换为月利率。第二个参数是还款期，数是还款期，10年共年共120各月。第
8、三个参数是贷款金额。各月。第三个参数是贷款金额。图5-5住房贷款还款的Excel分析图 v问题问题求解求解：可利用模拟运算表计算不同：可利用模拟运算表计算不同利率下的月偿还额。利率下的月偿还额。输入贷款期各种可能的不同利率数据，如图输入贷款期各种可能的不同利率数据，如图5-6（F4:F16）单元格区域所示。）单元格区域所示。图5-6住房贷款还款的Excel模拟运算表分析图 选择包含公式和需要进行模拟运算目标单元区选择包含公式和需要进行模拟运算目标单元区域（域（F3:G16），如图），如图5-7所示。所示。图5-7住房贷款还款的Excel模拟运算表分析图单击【数据】单击【数据】【模拟运算表】命令
9、，弹出【模拟运算表】命令，弹出如图如图5-8所示的对话框。在所示的对话框。在“输入引用列的单输入引用列的单元格元格”中输入中输入$D$5，再单击【确定】按钮，再单击【确定】按钮，各分析值自动填入分析表中，如图各分析值自动填入分析表中，如图5-9（G4：G16）单元格区域所示。）单元格区域所示。图5-8住房贷款还款的Excel模拟运算表分析图图5-9模拟运算表工具使用示意图5.2.2双变量模拟运算表双变量模拟运算表v对于问题对于问题我们不仅要考虑贷款年利率的变我们不仅要考虑贷款年利率的变化，而且还要考虑还款年限。这时需要分析化，而且还要考虑还款年限。这时需要分析不同的利率和不同的贷款期限对贷款的
10、偿还不同的利率和不同的贷款期限对贷款的偿还额的影响，这时需要使用双变量模拟运算表。额的影响，这时需要使用双变量模拟运算表。v问题问题求解求解：选择某个单元格区域作为模拟运算表存放区域，选择某个单元格区域作为模拟运算表存放区域，在该区域的最左列输入假设的利率变化范围数据；在该区域的最左列输入假设的利率变化范围数据；在该区域的第一行输入可能的贷款年限数据。如在该区域的第一行输入可能的贷款年限数据。如图图5-10（B11:G11）单元格区域所示。）单元格区域所示。图5-10模拟运算表工具使用示意图在模拟运算表区域的左上角单元格输入计算月在模拟运算表区域的左上角单元格输入计算月偿还额的计算公式。选定整
11、个模拟运算表区域。偿还额的计算公式。选定整个模拟运算表区域。如图如图5-11（A11:G24）单元格区域所示。）单元格区域所示。图5-11模拟运算表工具使用示意图 单击数据菜单中的模拟运算表命令。在模拟运单击数据菜单中的模拟运算表命令。在模拟运算表对话框（如图算表对话框（如图5-12）的输入引用行的单元）的输入引用行的单元格框中输入格框中输入“$C$6”（年份）；在输入引用列（年份）；在输入引用列的单元格框中输入的单元格框中输入“$C$5”（年利率）（年利率）,单击单击【确定】。【确定】。双变量模拟运算表的计算结果如图双变量模拟运算表的计算结果如图513所示。所示。其中其中B12：G24单元格
12、区域的计算公式为单元格区域的计算公式为“=表表(C6,C5)”，表示其是一个以，表示其是一个以C6为行变量，为行变量，C5为列变量的模拟运算表。为列变量的模拟运算表。5.2.3单变量求解单变量求解v什么是单变量求解？什么是单变量求解？单变量求解就是求解只有一个变量的方程的根，单变量求解就是求解只有一个变量的方程的根，方程可以是线性方程，也可以是非线性方程。方程可以是线性方程，也可以是非线性方程。【例【例5-2】某公司想向银行贷款某公司想向银行贷款900万元人民币，万元人民币，贷款利率是贷款利率是8%，贷款限期为，贷款限期为5年，如果公司年，如果公司每年可偿还每年可偿还100万元，该公司最多可贷
13、款多万元，该公司最多可贷款多少金额？少金额？(1)在工作表中输入原始数据，如图在工作表中输入原始数据，如图5-15；图5-15贷款总额计算(2)建立可变数公式；建立可变数公式；在单元格在单元格B2中输入公式中输入公式=PMT(B1,B3,B4)”；(3)设置求解公式：菜单【工具】设置求解公式：菜单【工具】单变量求单变量求解解对话框中输入：目标单元格、目标值、对话框中输入：目标单元格、目标值、可变单元格，如图可变单元格，如图5-16所示。所示。图5-16【单变量求解】对话框 5.3方案分析方案分析v方案，就是已命名的一组输入值，这组输入方案，就是已命名的一组输入值，这组输入值保存在工作表中，并可
14、用来自动替换某个值保存在工作表中，并可用来自动替换某个计算模型的输入值，用来预测模型的输出结计算模型的输入值，用来预测模型的输出结果。对于同一解题方案的模型参数，可以创果。对于同一解题方案的模型参数，可以创建多组不同的参数值，得到各组不同的结论，建多组不同的参数值，得到各组不同的结论，每组参数和结论都是一个方案。每组参数和结论都是一个方案。【例【例5-3】某化工企业生产产品某化工企业生产产品乙烯、乙烯、丙烯、丙烯、丁二烯，在丁二烯，在2008年的销售额分别为年的销售额分别为400万元、万元、600万元和万元和300万元，销售成本分别为万元，销售成本分别为300万万元、元、380万元和万元和22
15、0万元。根据市场情况推万元。根据市场情况推测，测，2009年产品的销售情况有好、一般和年产品的销售情况有好、一般和差三种情况，每种情况下的销售额及销售成差三种情况，每种情况下的销售额及销售成本的增长率如图本的增长率如图5-17所示。所示。图5-17产品销售资料及预计增长率 5.3.1建立方案建立方案（1）设计方案计算分析，如图）设计方案计算分析，如图5-17所示，选择单元所示，选择单元格格H7并输入公式并输入公式“=SUMPRODUCT(B3:B5,1+H4:H6)-SUMPRODUCT(C3:C5,1+I4:I6)”。（2）对不同的产品进行命名，以便分析。分别将单）对不同的产品进行命名，以便
16、分析。分别将单元格元格H4的名字为的名字为“乙烯销售额增长率乙烯销售额增长率”，单元格，单元格I4的名字为的名字为“乙烯销售成本增长率乙烯销售成本增长率”，单元格，单元格H5的名字为的名字为“丙烯销售额增长率丙烯销售额增长率”，单元格，单元格I5的名的名字为字为“丙烯销售成本增长率丙烯销售成本增长率”，单元格，单元格H6的名字的名字为为“丁二烯销售额增长率丁二烯销售额增长率”，单元格，单元格I6的名字为的名字为“丁二烯销售成本增长率丁二烯销售成本增长率”，单元格，单元格H7的名字为的名字为“总销售利润总销售利润”。（3）单击【工具】菜单，选择【方案】项，系统）单击【工具】菜单，选择【方案】项，
17、系统弹出【方案管理器】对话框，如图弹出【方案管理器】对话框，如图5-18所示，单击所示，单击【添加】按钮，系统弹出【添加方案】对话框，如【添加】按钮，系统弹出【添加方案】对话框，如图图5-19所示。所示。图5-18【方案管理器】对话框 图5-19【添加方案】对话框（4）在【添加方案】对话框中，【方案名】）在【添加方案】对话框中，【方案名】编辑框中输入编辑框中输入“方案方案1好好”，【可变单元格，【可变单元格】编辑框中输入】编辑框中输入“$H$4:$I$6”，单击【，单击【确定】按钮，系统弹出【方案变量值】对确定】按钮，系统弹出【方案变量值】对话框，如图话框，如图5-20所示。所示。图5-20【
18、方案变量值】对话框（5）在【方案变量值】对话）在【方案变量值】对话框中输入每个可变单元格的框中输入每个可变单元格的值（这里要按行输入），完值（这里要按行输入），完毕后单击【添加】按钮，系毕后单击【添加】按钮，系统会弹出如图统会弹出如图5-19所示的【所示的【添加方案】对话框，对第添加方案】对话框，对第2个方案进行输入；待所有方个方案进行输入；待所有方案输入完毕后，单击【方案案输入完毕后，单击【方案变量值】对话框中的【确定变量值】对话框中的【确定】按钮，系统返回到【方案】按钮，系统返回到【方案管理器】对话框，如图管理器】对话框，如图5-21所示。此时，可单击【关闭所示。此时，可单击【关闭】按钮，
19、回到工作表。】按钮，回到工作表。图5-21【方案管理器】对话框5.3.2浏览方案浏览方案（1）打开原工作表，并激活工作表。）打开原工作表，并激活工作表。（2）单击【工具】菜单，选择【方案】项，系）单击【工具】菜单，选择【方案】项，系统弹出【方案管理器】对话框，如图统弹出【方案管理器】对话框，如图5-21所所示，选择要想查看的方案，单击【显示】按示，选择要想查看的方案，单击【显示】按钮，则系统就自动显示出该方案的执行结果，钮，则系统就自动显示出该方案的执行结果，如图如图5-17所示。所示。5.3.3编辑方案编辑方案v对做好的方案进行修改，只需在图对做好的方案进行修改，只需在图5-21所示的【所示
20、的【方案管理器】对话框中选中需要修改的方案，单方案管理器】对话框中选中需要修改的方案，单击【编辑】按钮，系统弹出如图击【编辑】按钮，系统弹出如图5-20所示的对话所示的对话框，进行相应的修改即可。框，进行相应的修改即可。v若要删除某一方案，则在图若要删除某一方案，则在图5-21所示的【方案管所示的【方案管理器】对话框中选中需要删除的方案，单击【删理器】对话框中选中需要删除的方案，单击【删除】按钮。除】按钮。v若要增加方案，则在图若要增加方案，则在图5-21所示的【方案管理器所示的【方案管理器】对话框中单击【添加】按钮，然后在图】对话框中单击【添加】按钮，然后在图5-19所所示的对话框填写相关的
21、项目。示的对话框填写相关的项目。5.3.4建立方案摘要建立方案摘要（1）在图）在图5-21所示的【方案管理器】对话框中单击所示的【方案管理器】对话框中单击【摘要】按钮，弹出【方案摘要】对话框，如图【摘要】按钮，弹出【方案摘要】对话框，如图5-22所示，在【结果类型】中选择所示，在【结果类型】中选择“方案摘要方案摘要”项，在【结果单元格】中输入项，在【结果单元格】中输入“H7”，然后单击，然后单击【确定】按钮，则系统在当前工作簿中自动建立【确定】按钮，则系统在当前工作簿中自动建立一个名为一个名为“方案总结方案总结”的工作表，如图的工作表，如图5-23所示。所示。图5-22【方案总结】对话框 图5
22、-23方案摘要5.4规划求解规划求解v在经济管理中，经常会遇到各种规划问题，在经济管理中，经常会遇到各种规划问题，例如：人力资源的调度、产品生产的安排、例如：人力资源的调度、产品生产的安排、运输线路的规划、生产材料的搭配、采购批运输线路的规划、生产材料的搭配、采购批次的确定等。这类问题有一个共同要求，那次的确定等。这类问题有一个共同要求，那就是：如何合理利用各种有限的资源实现最就是：如何合理利用各种有限的资源实现最佳的经济效益，也就是达到利润最大、成本佳的经济效益，也就是达到利润最大、成本最低、费用最省等目标。最低、费用最省等目标。规划求解具有如下三个特点：规划求解具有如下三个特点：（1）所求
23、问题都有单一的目标，如求生产的）所求问题都有单一的目标，如求生产的最低成本，求运输的最佳路线，求产品的最最低成本，求运输的最佳路线，求产品的最大盈利，求产品周期的最短时间以及求其他大盈利，求产品周期的最短时间以及求其他目标函数的最佳值等。目标函数的最佳值等。（2）总是有明确的不等式约束条件。比如库）总是有明确的不等式约束条件。比如库存不能低于一定的数量，否则造成原料短缺存不能低于一定的数量，否则造成原料短缺或产品缺货；生产产品不能超过一定额度，或产品缺货；生产产品不能超过一定额度，否则会造成商品积压等。否则会造成商品积压等。（3）问题都有直接或间接影响约束条件的一）问题都有直接或间接影响约束条
24、件的一组输入值。组输入值。5.4.1建立规划求解模型建立规划求解模型vExcel规划求解问题的基本构成规划求解问题的基本构成（1）决策变量（）决策变量（variable）一个或一组可变单元格，可变单元格称为决策变一个或一组可变单元格，可变单元格称为决策变量，一组决策变量代表一个规划求解的方案量，一组决策变量代表一个规划求解的方案（2）目标函数）目标函数目标函数表示规划求解要达到的最终目标，是规目标函数表示规划求解要达到的最终目标，是规划求解的关键。它是规划求解中可变量的函数划求解的关键。它是规划求解中可变量的函数（3）约束条件）约束条件约束条件是实现目标的限制条件。约束条件是实现目标的限制条件
25、。建立规划求解模型：建立规划求解模型：第第1步步建立求解工作表建立求解工作表(输入原始数据及相应的各公式输入原始数据及相应的各公式)第第2步步设置求解参数设置求解参数选择选择“工具工具”“规划求解规划求解”菜单，设置以下求解的各项参菜单，设置以下求解的各项参数：数：v设置目标单元格：输入目标函数所在单元格设置目标单元格：输入目标函数所在单元格(为总余额单元为总余额单元格格)v设置目标：设置目标：最大值最大值、最小值最小值或值的数值或值的数值(最大利润最大利润,即最大值即最大值)v设置可变单元格：它的确定决定结果（为生产数量）设置可变单元格：它的确定决定结果（为生产数量）v设置约束条件：单击【添
26、加】按钮设置约束条件：单击【添加】按钮输入约束条件输入约束条件按添按添加加依次输入所有约束条件依次输入所有约束条件确定确定第第3步步保存求解结果保存求解结果在规划求解对话框中按在规划求解对话框中按“求解求解”在规划求解结果对话框中在规划求解结果对话框中按按“保存规划求解结果保存规划求解结果”5.4.2求解优化问题求解优化问题【例【例5-4】某农场每天至少使用某农场每天至少使用800磅特殊饲磅特殊饲料。这种特殊饲料由玉米和大豆粉配制而成，料。这种特殊饲料由玉米和大豆粉配制而成，含有以下成份：含有以下成份：特殊饲料的营养要求是至少特殊饲料的营养要求是至少30%的蛋白质的蛋白质和至多和至多5%的纤维
27、。该农场希望确定每天的纤维。该农场希望确定每天最小成本最小成本的饲料配制。的饲料配制。v求解过程：求解过程：步骤步骤1：根据问题建立数学模型：根据问题建立数学模型因为饲料由玉米和大豆粉配制而成，所以模型的因为饲料由玉米和大豆粉配制而成，所以模型的决策变量定义为：决策变量定义为：x=每天混合饲料中玉米的重量（磅）每天混合饲料中玉米的重量（磅）y=每天混合饲料中大豆粉的重量（磅）每天混合饲料中大豆粉的重量（磅）目标函数是使配制这种饲料的每天总成本最小，目标函数是使配制这种饲料的每天总成本最小，因此表示为：因此表示为：y模型的约束条件是饲料的日需求量和对营养模型的约束条件是饲料的日需求量和对营养成份
28、的需求量，具体表示为：成份的需求量，具体表示为：x+y800/每天所需饲料每天所需饲料0.09x+0.6y0.3(x+y)/蛋白质蛋白质0.02x+0.06y0.05(x+y)/纤维纤维将上述不等式化简后，完整的模型为：将上述不等式化简后，完整的模型为：minz=0.31+0.92s.t.x+y8000.21x-0.3y00.03x-0.01y0 x,y0步骤步骤2建立工作表建立工作表工作表设计如图工作表设计如图5-23所示：所示：图5-23建立营养配方模型工作表 v其中，输入数据的单元格使用了阴影格式，其中，输入数据的单元格使用了阴影格式，即即B4:C7和和F5:F7；变量和目标函数单元格；
29、变量和目标函数单元格为为B12:D12；D4:D7中输入了约束公式到中输入了约束公式到vD4=SUMPRODUCT(B4:C4,$B$12:$C$12)vD5=SUMPRODUCT(B5:C5,$B$12:$C$12)vD6=SUMPRODUCT(B6:C6,$B$12:$C$12)vD7=SUMPRODUCT(B7:C7,$B$12:$C$12)步骤步骤3应用规划求解工具应用规划求解工具v单击【工具单击【工具规划求解】，出现如图规划求解】，出现如图5-24所示的所示的“规划求解参数规划求解参数”对话框，设计对话框，设计相应的参数。相应的参数。图5-24规划求解参数设置v并且单击【添加】按钮，
30、添加相应的约束，并且单击【添加】按钮，添加相应的约束，如图如图5-25所示。所示。图5-25添加相应的约束条件v注意，上图所示的约束中，添加了非负注意，上图所示的约束中，添加了非负限制，即限制，即$B$12:$C$12=0。步骤步骤4求解求解v设置好参数后，单击【规划求解参数】对话设置好参数后，单击【规划求解参数】对话框中的【求解】按钮，结果如图框中的【求解】按钮，结果如图5-27所示。所示。图5-27规划求解结果 本章小结v本章详细介绍了本章详细介绍了Excel数据分析工具在经济活数据分析工具在经济活动中常用的一些工具，包括模拟运算表、方动中常用的一些工具，包括模拟运算表、方案管理器和规划求解。由于案管理器和规划求解。由于“分析工具库分析工具库”中的分析工具具有很强的专业性，本章通过中的分析工具具有很强的专业性，本章通过实例介绍了这些工具的功能和使用方法。通实例介绍了这些工具的功能和使用方法。通过对本章的学习，希望读者能够对过对本章的学习，希望读者能够对Excel数据数据分析工具有深入的理解和掌握。分析工具有深入的理解和掌握。