实训二十一Excel数据处理综合功能.docx

实训二十一Excel数据处理综合功能.docx

《实训二十一Excel数据处理综合功能.docx》由会员分享，可在线阅读，更多相关《实训二十一Excel数据处理综合功能.docx（21页珍藏版）》请在冰点文库上搜索。

实训二十一Excel数据处理综合功能

实训二十一Excel数据处理综合功能

实训目的

1.掌握数据有效性定义；

2.掌握数据审核的方法；

3.能够创建、编辑、总结方案；

4.能够利用单变量求解；

5.能够利用规划求解；

实训内容及步骤

一、数据有效性定义

打开实训21文件夹下的工作簿KS5-6.xls,在工作表“订货单”中按下列要求操作：

1.按订货单样文，在“数据录入表”“数据”栏下的“日期”单元格中，设置有效性条件“允许”为“日期”，数据“大于或等于”，开始日期为“95-1-1”，选定单元格时显示下列输入信息：

标题为“输入日期”，输入信息为“请输入一个大于95年1月1日的日期”，输入无效数据时，显示警告信息，图案样式为“警告”。

在“数据”栏下的“日期”单元格中输入94-5-12，观察有什么反应。

2.按订货单样文，在“数据录入表”“数据”栏下的“数量”单元格中，设置有效性条件“允许”为“整数”，数据“介于5-20之间”，输入无效数据时，显示警告信息，图案样式为“中止”，标题为“输入错误”，错误信息为“输入的数值不在5-20之间！

”。

在“数据”栏下的“数量”单元格中输入25观察反应。

3.按订货单样文，在“数据录入表”“数据”栏下的“地区”单元格中，设置有效性条件“允许”为“序列”，来源引用单元格G6：

G9，提示下拉箭头，显示警告信息，图案样式为“信息”。

在“数据”栏下的“地区”单元格中选择“东部”。

4.按订货单样文，在“数据录入表”“数据”栏下的“编号”单元格中，设置有效性条件“允许”为“序列”，来源引用单元格F6：

F12，提示下拉箭头，显示警告信息，图案样式为“信息”，错误信息为“非允许编号！

”在“数据”栏下的“编号”单元格中输入“123”观察又何反应。

操作步骤：

1.将光标放在“数据录入表”“数据”栏下的“日期”单元格中，单击数据—有效性，打开“数据有效性”对话框，设置有效性性条件“允许”为“日期”，数据“大于或等于”，开始日期为“95-1-1”（如图21-1所示）；单击“输入信息”选项卡，按图21-2进行相应设置；单击“出错警告”选项卡，按下图21-3进行相应设置。

在“数据”栏下的“日期”单元格中输入94-5-12，则出现警告信息（如图21-4所示）。

图21-1“数据有效性”对话框

图21-2“数据有效性”对话框

图21-3“数据有效性”对话框

图21-4

2.将光标放在“数据录入表”“数据”栏下的“数量”单元格中，单击数据—有效性性，打开“数据有效性”对话框，按图21-5进行相应设置；单击“出错警告”选项卡，按下图21-6进行相应设置。

在“数据”栏下的“数量”单元格中输入25，则出现图21-7所示对话框

图21-5“数据有效性”对话框

图21-6“数据有效性”对话框

图21-7

3.将光标放在“数据录入表”“数据”栏下的“地区”单元格中，单击数据—有效性性，打开“数据有效性”对话框，按图21-8进行相应设置；单击“出错警告”选项卡，按图21-9进行相应设置。

图21-8“数据有效性”对话框

图21-9“数据有效性”对话框

4.将光标放在“数据录入表”“数据”栏下的“编号”单元格中，单击数据—有效性性，打开“数据有效性”对话框，按图21-10进行相应设置；单击“出错警告”选项卡，按下图21-11进行相应设置。

在“数据”栏下的“编号”单元格中输入“123”则出现图21-12所示的对话框。

图21-10

图21-11

图21-12

二、数据审核

打开实训21文件夹下的工作簿KS5-13.xls,按下列要求操作：

数据的有效性性及审核：

按样文，将对外借款一列数据的有效性性设定为500-5000之间的任意整数，圈出无效数据，并把无效数据的字体设为红色。

操作步骤：

1、打开实训21文件夹下的工作簿KS5-13.xls，单击sheet工作表，选择C6-C26单元格，单击数据—有效性性，打开“数据有效性”对话框，按图21-14进行相应设置；单击视图—工具栏—公式审核，出现“公式审核”工具栏（如图21-13所示），单击“公式审核”工具栏中的“圈释出无效数据”按钮

，则数据不在500-5000之间的被圈出来。

如图21-15所示。

选择C6-C26单元格，单击格式—条件格式，按图21-16进行相应设置。

图21-13

图21-14

图21-15

图21-16

三、数组公式和单变量求解

打开实训21文件夹下的工作簿KS5-12.xls，按下列要求操作。

1、公式及数组公式的应用：

按照公式“资金利税率（%）=利税总额/（固定资产平均余额+流动资产平均余额）”运用数组的功能计算出表-1中资金利税的值，并以百分比的格式表示。

2、单变量求解：

按样文，以表-2中的资金利税率的值作为目标单元格的值，依次求出表-1中所有空白单元格的值。

操作步骤：

1、打开实训21文件夹下的工作簿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，单击格式—单元格，打开“单元格格式”对话框，按图21-１7进行相应设置。

图21-17“单元格格式”对话框

2、选定Ｆ４单元格，单击工具—单变量求解，打开“单变量求解”对话框，按图21-18进行相应设置，单“确定”按钮，得到图21-19所示的对话框，同时在E4单元格得到相应结果为2454.33。

按同样的方法求出其他数据。

结果如图21-20所示。

图21-18“单变量求解”对话框图21-19“单变量求解状态”对话框

图21-20

四、规划求解

某工厂制造A，B两种产品，制造A产品每吨需要用煤9t，电力4kW，3个工作日；制造B产品每吨需要用煤5t，电力5kW，10个工作日。

已知制造产品A和B每吨分别获利7000元和12000元，现在该厂由于条件限制，只有煤360t，电力200kW，工作日300个可以利用，问A，B两种产品各生产多少吨才能获得最大利润？

请你利用规划求解的方法求出结果。

操作步骤：

分析：

如果用变量

，

（单位:

t）分别表示A，B产品的计划生产数量，用f表示利润（单位:

元），则f可表示为：

f=7000

+12000

，其耗煤量为9

+5

（t），电力为4

+5

（kW），耗工作日3

+10

，但这些都不能超过生产条件的限制，即

，

应满足：

≤360

≤200

≤300

≥0，

≥0

（1）、启动Excel，在工作表中的A1和B1单元格分别输入文字A产品（

）与B产品（

），在单元格A4，B4，C4单元格中分别输入煤（t）、功率（kW）、工作日（个）。

由于制造A产品每吨需要用煤9t，制造B产品每吨需要用煤5t，所以在A5单元格输入公式“=9*A2+5*B2”；同理，由于制造A产品需要用电的功率为4kW，制造B产品每吨需要用电的功率为5kW，所以在B5单元格输入公式“=4*A2+5*B2”；由于制造A产品每吨需要用3个工作日，制造B产品每吨需要用10个工作日，故在C5单元格输入公式“=3*A2+10*B2”。

在单元格A7中输入文字利润（f），由于制造产品A和B每吨分别获利7000元和12000元，故在单元格A8中输入公式“=7000*A2+12000*B2”。

如下图21-21所示

图21-21数据输入

（2）、单击“工具”菜单中的“规划求解”命令，弹出图21-22所示的“规划求解参数”对话框。

（注意：

如果在“工具”菜单中没有见到“规划求解”命令，则要单击“工具”→“加载宏”命令，在弹出的“加载宏”对话框中选择“规划求解”，Excel可能会提醒需要CD来安装此功能）。

图21-22“规划求解参数”对话框

（3）、在“规划求解参数”对话框中选中“最大值”前的单选按钮，设置目标单元格为$A$8，可变单元格为$A$2：

$B$2。

（4）、单击“规划求解参数”对话框中的“添加”按钮，打开“添加约束”对话框，单击单元格引用位置文本框，然后选定工作表的A5单元格，则在文本框中显示“$A$5”，选择“<=”约束条件，在约束值文本框中输入“360”，如图21-23所示。

单击添加按钮，把所有的约束条件都添加到“规划求解参数”对话框的“约束”列表框中。

图21-23“添加约束”对话框

（5）、在“规划求解参数”对话框中单击“求解”按钮，弹出图21-24所示的“规划求解结果”对话框，选中“保存规划求解结果”前的单选按钮。

图21-24“规划求解结果”对话框

（6）、在“规划求解结果”对话框单击“确定”按钮，工作表中就显示出规划求解的结果，如图21-25所示。

从表中可以看出，A产品生产20t，B产品生产24t时利润最大，且最大利润是428000元。

此时用去煤300t，电的功率200kw，工作日300个。

图21-25结果显示

如果要生成运算结果报告，可在“规划求解结果”对话框中选择“报告”列表框中的“运算结果报告”。

单击“确定”按钮，则产生如图21-26所示的运算结果报告表，在该表中对约束条件和结果做出了更为详细的说明。

图21-26运算结果报告表

五、单变量求解和方案管理

打开实训21文件夹下的工作簿KS5-3.xls，按下列要求操作。

1、设置“贷款试算表”的格式：

设置“月偿还额”一列单元格的数字格式为：

货币、保留两位小数。

2、单变量分析：

按样文“贷款试算表操作结果”工作表，利用模拟运算表来进行单变量分析，运用函数PMT（），实现通过“利率”的变动计算“月偿还额”的功能。

3、创建、编辑、总结方案：

1）按样文“方案总结结果”工作表，在方案管理器中添加一个方案，命名为“KS5-3”

2）设置“利率”为可变单元格，输入一组可变单元格的值为“0.075”、“0.090”、“0.100”、“0.110”、“0.120”、“0.130”。

3）设置“月偿还额”为结果单元格，报告类型为“方案总结”。

操作步骤：

1、打开实训21文件夹下的工作簿KS5-3.xls，单击“贷款试算表”，选定单元格E6-E10，单击格式—单元格，打开“单元格格式”对话框，按图21-27进行相应的设置。

图21-27“单元格格式”对话框

2、在单元格E4输入“=-PMT（C6/12,C7,C5）”，选定单元格区域D4-E10，单击数据—模拟运算表，打开“模拟运算表”对话框，按图21-28进行相应设置。

单击确定则可得到相应结果。

图21-27“模拟运算表”

3、将光标放在工作表的任意有数据的位置，单击工具—方案，打开“方案管理器”对话框（如图21-28所示），单击“添加”按钮，打开“方案编辑”对话框，按图21-29进行相应设置。

在图21-29对话框中单击“确定”按钮，打开图21-30所示的“方案变量值”对话框，按题目要求输入相应值，如图21-30，单击“确定”按钮。

返回到“方案管理器”对话框（如图21-31），单击“摘要”按钮，打开图21-32所示的“方案摘要”对话框，在“结果单元格”中选择“E5-E10”单元格，单击“确定”按钮。

得到相应结果（如图21-33所示）。

图21-28“方案管理器”对话框图21-29“方案编辑”对话框

图21-30“方案变量值”对话框

图21-31“方案管理器”对话框图21-32“方案摘要”对话框

图21-33

六、利用图表绘制数学函数的图像

利用图表向导的方法在同一坐标系中做出函数y=sinx和y=cosx在闭区间[-2π，2π]的图像。

操作步骤：

1）新建Excel工作簿，在A1单元格输入x，B1单元格输入y=sinx，C1单元格输入y=cosx，在A2单元格输入“=-2*PI（）”,A3单元格输入“=-3*PI（）/2”,，A4单元格输入“=-PI（）”，A5单元格输入“=-PI（）/2”，A6单元格输入“0”，A7单元格输入“=PI（）/2”，A8单元格输入“=PI（）”，A9单元格输入“=3*PI（）/2”，A10单元格输入“=2*PI（）”，得到图1所示的A列中的数据。

在B2单元格输入函数“=sin（A2）”，在C2单元格输入函数“=cos（A2）”，然后利用自动填充的方法将B2单元格的公式复制到B3-B10，将C2单元格的公式复制到C3-C10，得到图21-34所示的数据。

图21-34

2）选定A1到C10单元格，单击工具栏上的图表向导按钮

，弹出图21-35所示的“图表类型”对话框，在“图表类型”中选择“XY散点图”，在“子图表类型”中选择“平滑线散点图”。

图21-35“图表类型”对话框

3）在图21-35中单击“下一步”，得到图21-36所示的“图表数据源”对话框，在“系列产生在：

”中选择“列”，在单击“系列”选项卡，得到图21-37所示的对话框，在“系列”中选择“系列1”，在名称中输入“y=sinx的图像”，再在在“系列”中选择“系列2”，在名称中输入“y=cosx的图像”。

图21-36“图表数据源”对话框

图21-37“图表数据源”对话框

4）在图21-37中单击“下一步”，得到图21-38所示的对话框，单击“标题”选项卡，在“图表标题”框中输入“绘制函数图像”作为图表标题，在“数值（X）轴”框中输入“x”，在“数值（Y）轴”框中输入“y”。

图21-38“图表选项”对话框

5）在图21-38中单击“下一步”按钮，得到图21-39所示的对话框，选择“作为其中的对象插入”。

图21-39“图表位置”对话框

6）在图21-39中单击“完成”按钮，便得到函数“y=sinx”和“y=cosx”的图像。

7）对得到的图表进行颜色、图案、线条、填充、边框、线条的设置，便得到如图21-40所示的图像。

图21-40