Excel数据分析工具及应用.pptVIP

EXCEL基础及应用;第5章 Excel数据分析工具及应用 ;5.1 数据分析工具的安装与卸载;图5-1加载宏;在打开“加载宏”对话框，对话框如图5-2所示，单击【分析工具库】复选框，框前即出现√号，表明已被选定，选择【确定】。 ;5.1.2 卸载分析工具库;图5-4卸载分析工具库;5.2 模拟运算表;5.2.1 单变量模拟运算表 ;单变量模拟运算表主要是用来分析当其他因素不变时，一个参数的变化对目标结果的影响。 ;图5-5住房贷款还款的Excel分析图 ;问题②求解：可利用模拟运算表计算不同利率下的月偿还额。 输入贷款期各种可能的不同利率数据，如图5-6（F4:F16）单元格区域所示。;选择包含公式和需要进行模拟运算目标单元区域（F3:G16），如图5-7所示。;单击【数据】→【模拟运算表】命令，弹出如图5-8所示的对话框。在“输入引用列的单元格”中输入$D$5，再单击【确定】按钮，各分析值自动填入分析表中，如图5-9（ G4：G16 ）单元格区域所示。;图5-9模拟运算表工具使用示意图;5.2.2 双变量模拟运算表 ;图5- 10模拟运算表工具使用示意图;在模拟运算表区域的左上角单元格输入计算月偿还额的计算公式。选定整个模拟运算表区域。如图5-11（A11:G24）单元格区域所示。 ;单击数据菜单中的模拟运算表命令。在模拟运算表对话框（如图5-12）的输入引用行的单元格框中输入“$C$6”（年份）；在输入引用列的单元格框中输入“$C$5”（年利率）,单击【确定】。 ; 双变量模拟运算表的计算结果如图5－13所示。其中B12：G24单元格区域的计算公式为“{=表(C6,C5) }”，表示其是一个以C6为行变量，C5为列变量的模拟运算表。;5.2.3 单变量求解;【例5-2】某公司想向银行贷款900万元人民币，贷款利率是8%，贷款限期为5年，如果公司每年可偿还100万元，该公司最多可贷款多少金额？ (1) 在工作表中输入原始数据，如图5-15； ;(2) 建立可变数公式； 在单元格B2中输入公式=PMT(B1,B3,B4)”； (3) 设置求解公式：菜单【工具】—单变量求解—对话框中输入：目标单元格、目标值、可变单元格，如图5-16所示。;5.3 方案分析 ;【例5-3】某化工企业生产产品?乙烯、?丙烯、丁二烯，在2008年的销售额分别为400万元、600万元和300万元，销售成本分???为300万元、380万元和220万元。根据市场情况推测，2009年产品的销售情况有好、一般和差三种情况，每种情况下的销售额及销售成本的增长率如图5-17所示。;图5-17产品销售资料及预计增长率 ;5.3.1 建立方案;（3）单击【工具】菜单，选择【方案】项，系统弹出【方案管理器】对话框，如图5-18所示，单击【添加】按钮，系统弹出【添加方案】对话框，如图5-19所示。;（4）在【添加方案】对话框中，【方案名】编辑框中输入“方案1好”，【可变单元格】编辑框中输入“$H$4:$I$6”，单击【确定】按钮，系统弹出【方案变量值】对话框，如图5-20所示。;（5）在【方案变量值】对话框中输入每个可变单元格的值（这里要按行输入），完毕后单击【添加】按钮，系统会弹出如图5-19所示的【添加方案】对话框，对第2个方案进行输入；待所有方案输入完毕后，单击【方案变量值】对话框中的【确定】按钮，系统返回到【方案管理器】对话框，如图5-21所示。此时，可单击【关闭】按钮，回到工作表。;5.3.2 浏览方案;5.3.3 编辑方案;5.3.4? 建立方案摘要;图5-23方案摘要;5.4 规划求解;规划求解具有如下三个特点：;5.4.1 建立规划求解模型;建立规划求解模型：;5.4.2??求解优化问题;求解过程： 步骤1：根据问题建立数学模型 因为饲料由玉米和大豆粉配制而成，所以模型的决策变量定义为： x=每天混合饲料中玉米的重量（磅）y=每天混合饲料中大豆粉的重量（磅） 目标函数是使配制这种饲料的每天总成本最小，因此表示为： z=0.3×x+0.9×y;模型的约束条件是饲料的日需求量和对营养成份的需求量，具体表示为： x+y≥800 //每天所需饲料 0.09×x+0.6×y≥0.3(x+y) //蛋白质 0.02×x+0.06×y≤0.05(x+y) //纤维 将上述不等式化简后，完整的模型为： min z=0.3×1+0.9×2 s.t. x+y≥800 0.21×x-0.3×y≤0 0.03×x-0.01×y≥0 x,y≥0;步骤2 建立工作表工作表设计如图5-23所示：;其中，输入数据的单元格使用了阴影格式，即B4:C7和F5:F7；变量和目标