《实验12 运用假设、统计分析数据》由会员分享，可在线阅读，更多相关《实验12 运用假设、统计分析数据（12页珍藏版）》请在金锄头文库上搜索。
1、实验十二 数据处理综合应用实验目的和要求：1、掌握数据有效性定义；2、掌握数据审核的方法；3、能够创建、编辑、总结方案； 4、能够利用单变量求解。5、能够利用规划求解；实验重点和难点：1、数据有效性定义；2、创建、编辑、总结方案；3、单变量求解和规划求解实验内容：实验题一 数据有效性定义打开实验12文件夹下的工作簿KS5-6.xls,在工作表“订货单”中按下列要求操作：1. 按订货单样文，在“数据录入表”“数据”栏下的“日期”单元格中，设置有效性条件“允许”为“日期”，数据“大于或等于”，开始日期为“95-1-1”，选定单元格时显示下列输入信息：标题为“输入日期”，输入信息为“请输入一个大于9
2、5年1月1日的日期”，输入无效数据时，显示警告信息，图案样式为“警告”。在“数据”栏下的“日期”单元格中输入94-5-12，观察有什么反应。2. 按订货单样文，在“数据录入表”“数据”栏下的“数量”单元格中，设置有效性条件“允许”为“整数”，数据“介于5-20之间”，输入无效数据时，显示警告信息，图案样式为“中止”，标题为“输入错误”，错误信息为“输入的数值不在5-20之间！”。在“数据”栏下的“数量”单元格中输入25观察反应。3. 按订货单样文，在“数据录入表”“数据”栏下的“地区”单元格中，设置有效性条件“允许”为“序列”，来源引用单元格G6：G9，提示下拉箭头，显示警告信息，图案样式为“
3、信息”。在“数据”栏下的“地区”单元格中选择“东部”。4. 按订货单样文，在“数据录入表”“数据”栏下的“编号”单元格中，设置有效性条件“允许”为“序列”，来源引用单元格F6：F12，提示下拉箭头，显示警告信息，图案样式为“信息”，错误信息为“非允许编号！”在“数据”栏下的“编号”单元格中输入“123”观察又何反应。实验步骤：1、 将光标放在“数据录入表”“数据”栏下的“日期”单元格中，单击数据有效性，打开“数据有效”对话框，设置有效性条件“允许”为“日期”，数据“大于或等于”，开始日期为“95-1-1”（如图1所示）；单击“输入信息”选项卡，按图2进行相应设置；单击“出错警告”选项卡，按下图
4、3进行相应设置。在“数据”栏下的“日期”单元格中输入94-5-12，则出现警告信息（如图4所示）。图1图2图3图42、 将光标放在“数据录入表”“数据”栏下的“数量”单元格中，单击数据有效性，打开“数据有效”对话框，按如图5所示进行相应设置；单击“出错警告”选项卡，按下图6进行相应设置。在“数据”栏下的“数量”单元格中输入25，则出现图7所示对话框图5图6图73、 将光标放在“数据录入表”“数据”栏下的“地区”单元格中，单击数据有效性，打开“数据有效”对话框，按如图8所示进行相应设置；单击“出错警告”选项卡，按下图9进行相应设置。图8图94、 将光标放在“数据录入表”“数据”栏下的“编号”单元
5、格中，单击数据有效性，打开“数据有效”对话框，按如图10所示进行相应设置；单击“出错警告”选项卡，按下图11进行相应设置。在“数据”栏下的“编号”单元格中输入“123”则出现图12所示的对话框。图10图11图12实验题二 数据审核打开实验12文件夹下的工作簿KS5-13.xls,按下列要求操作：数据的有效性及审核：按样文，将对外借款一列数据的有效性设定为500-5000之间的任意整数，圈出无效数据，并把无效数据的字体设为红色。实验步骤：1、 打开实验12文件夹下的工作簿KS5-13.xls，单击sheet工作表，选择C6-C26单元格，单击数据有效性，打开“数据有效”对话框，按如图14所示进行
6、相应设置；单击视图工具栏公式审核，出现“公式审核”工具栏（如图13所示），单击“公式审核”工具栏中的“圈释出无效数据”按钮，则数据不在500-5000之间的被圈出来。如图15所示。选择C6-C26单元格，单击格式条件格式，按图17进行相应设置。图13图14图15图17实验题三 数组公式和单变量求解打开实验12文件夹下的工作簿KS5-12.xls，按下列要求操作。1、 公式及数组公式的应用：按照公式“资金利税率（%）=利税总额/（固定资产平均余额+流动资产平均余额）”运用数组的功能计算出表-1种资金利税的值，并以百分比的格式表示。2、 单变量求解：按样文，以表-2中的资金利税率的值作为目标单元格
7、的值，依次求出表-1中所有空白单元格的值。实验步骤：1、 打开实验12文件夹下的工作簿KS5-12.xls，单击工作表Sheet1，选择单元格F4-F8，单击公式编辑区使光标定位到公式编辑区，输入等于号“=”，选定单元格E4-E8，输入符号“/”，输入左括号“（”，选定单元格C4-C8，输入加号“+”，选定单元格D4-D8，输入右括号“）”，按键盘的“CTRL+SHIFT+ENTER”，则公式编辑区的公式变为“=E4:E8/(C4:C8+D4:D8)”，同时在单元格F4-F8出现相应结果，选定单元格F4-F8，单击格式单元格，打开“单元格格式”对话框，按下图进行相应设置。图2、 选定单元格，单
8、击工具单变量求解，打开“单变量求解对话框，按下图2进行相应设置，单“确定”按钮，得到图3所示的对话框，同时在E4单元格得到相应结果为2454.33。按同样的方法求出其他数据。结果如图4所示。 图2 图3图4实验题四 规划求解某工厂制造A，B两种产品，制造A产品每吨需要用煤9 t，电力4 kW，3个工作日；制造B产品每吨需要用煤5 t，电力5 kW，10个工作日。已知制造产品A和B每吨分别获利7 000元和12 000元，现在该厂由于条件限制，只有煤360 t，电力200 kW，工作日300个可以利用，问A，B两种产品各应该生产多少吨才能获得最大利润？请你利用规划求解的方法求出结果。实验步骤：分
9、析：如果用变量 ，（单位:t）分别表示A，B产品的计划生产数量，用f表示利润（单位:元），则f可表示为：f=7 000+12 000 ，其耗煤量为9+5（t），电力为4+5（kW），耗工作日3+10， 但这些都不能超过生产条件的限制，即，应满足：3602003000，0(1)、启动Excel，在工作表中的A1和B1单元格分别输入文字A产品()与B产品()，在单元格A4，B4，C4单元格中分别输入煤（t）、功率(kW)、工作日(个)。由于制造A产品每吨需要用煤9 t，制造B产品每吨需要用煤5 t，所以在A5单元格输入公式“=9*A2+5*B2”；同理，由于制造A产品需要用电的功率为4 kW，制造
10、B产品每吨需要用电的功率为5 kW，所以在B5单元格输入公式“=4*A2+5*B2”；由于制造A产品每吨需要用3个工作日，制造B产品每吨需要用10个工作日，故在C5单元格输入公式“=3*A2+10*B2” 。在单元格A7中输入文字利润（f），由于制造产品A和B每吨分别获利7 000元和12 000元，故在单元格A8中输入公式“=7000*A2+12000*B2”。如下图1所示图 1数据输入 (2)、单击“工具”菜单中的 “规划求解” 命令，弹出图2所示的“规划求解参数”对话框。（注意：如果在“工具”菜单中没有见到“规划求解”命令，则要单击“工具”“加载宏”命令，在弹出的“加载宏”对话框中选择“
11、规划求解”，Excel可能会提醒需要CD来安装此功能）。图 2“规划求解参数”对话框 (3)、在“规划求解参数”对话框中选中“最大值”前的单选按钮，设置目标单元格为$A$8，可变单元格为$A$2：$B$2。(4)、单击“规划求解参数”对话框中的“添加”按钮，打开“添加约束”对话框，单击单元格引用位置文本框，然后选定工作表的A5单元格，则在文本框中显示“$A$5”，选择“=”约束条件，在约束值文本框中输入“360”，如图3所示。单击添加按钮，把所有的约束条件都添加到“规划求解参数”对话框的“约束”列表框中。图3 “添加约束”对话框 (5)、在“规划求解参数”对话框中单击“求解”按钮，弹出图4所示
12、的“规划求解结果”对话框，选中“保存规划求解结果”前的单选按钮。图 4 “规划求解结果”对话框 (6)、在“规划求解结果”对话框单击“确定”按钮，工作表中就显示出规划求解的结果，如图5所示。从表中可以看出，A产品生产20t，B产品生产24t时利润最大，且最大利润是428000元。此时用去煤300t，电的功率200kw，工作日300个。图 5结果显示如果要生成运算结果报告，可在“规划求解结果”对话框中选择“报告”列表框中的“运算结果报告”。单击“确定”按钮，则产生如图6所示的运算结果报告表，在该表中对约束条件和结果作出了更为详细的说明。图 6 运算结果报告表实验题五 单变量求解和方案管理 打开实
13、验12文件夹下的工作簿KS5-3.xls，按下列要求操作。1、 设置“贷款试算表”的格式：设置“月偿还额”一列单元格的数字格式为：货币、保留两位小数。2、 单变量分析：按样文“贷款试算表操作结果”工作表，利用模拟运算表来进行单变量分析，运用函数PMT（），实现通过“利率”的变动计算“月偿还额”的功能。3、 创建、编辑、总结方案：1) 按样文“方案总结结果”工作表，在方案管理器中添加一个方案，命名为“KS5-3”2) 设置“利率”为可变单元格，输入一组可变单元格的值为“0.075”、“0.090”、“0.100”、“0.110”、“0.120”、“0.130”。3) 设置“月偿还额”为结果单元格
14、，报告类型为“方案总结”。实验步骤：1、 打开实验12文件夹下的工作簿KS5-3.xls，单击“贷款试算表”，选定单元格E6-E10，单击格式单元格，打开“单元格格式”对话框，按下图1进行相应的设置。图12、 在单元格E4输入“=-PMT(C6/12,C7,C5)”，选定单元格区域D4-E10，单击数据模拟运算表，打开“模拟运算”对话框，按图2进行相应设置。单击确定则可得到相应结果。图23、 将光标放在工作表的任意有数据的位置，单击工具方案，打开“方案管理器”对话框（如图3所示），单击“添加”按钮，打开“方案编辑”对话框，按图4进行相应设置。在图4对话框中单击“确定”按钮，打开图5所示的“方案变量值”对话框，按题目要求输入相应值，如图5，单击“确定”按钮。返回到“方案管理器”对话框（如图6），单击“摘要”按钮，打开图7所示的“方案摘要”对话框，在“结果单元格”中选择“E5-E10”单元格，单击“确定”按钮。得到相应结果（如图8所示）。 图3 图4图5图6图7图8
