4.7 Excel的高级应用

4.7.1 数据模拟分析和运算

如前面4.3节所述，Excel提供了丰富的公式和函数来完成数据运算。 一旦在工作表中输入公式后，即可进行假设分析，查看当改变公式中的某些值(自变量)时怎样影响其结果，模拟运算表提供了一个操作所有变化的捷径。

模拟运算表是工作表中的一个单元格区域，它显示了当给定公式中的一个或两个自变量发生变化时，公式结果的变化情况。 有两种类型的模拟运算表：单变量模拟运算表和双变量模拟运算表。 在单变量模拟运算表中，用户可以对一个变量键入不同的值从而查看它对给定公式计算结果的影响。 在双变量模拟运算表中，用户可以对两个变量输入不同值，从而查看它对给定公式计算结果的影响。

1.单变量模拟运算表

单变量模拟运算表是指Excel在模拟运算中，发生变化的只有一个变量。 这个变量的数值可以写在同一列，也可以写在同一行中。

例如，对图4-73中所示的梯形面积计算进行模拟运算，假设梯形的“高”发生改变，而其他量不变时，梯形面积将发生怎样的变化。

进行模拟运算之前，先在单变量模拟运算表开头的单元格中引用要计算的公式，该公式中必须引用了存放自变量数据的单元格，称为输入单元格。将自变量的变化数据输入在一行或一列中，自变量数据在一行中的称为行引用，在一列中的称为列引用。

其具体操作步骤如下：

(1)在B3单元格中输入公式：=(A5+B5)*C5/2，计算梯形面积;

(2)在A10∶A14区域中输入梯形“高”的变化值，如图4-73所示;

(3)在B9单元格输入公式：=B3，将B3单元格中(A5+B5)*C5/2的关系引用到此处;

(4)选定A9∶B14区域，然后选择“数据”选项卡中“模拟分析\模拟运算表”命令，弹出如图4-74所示“模拟运算表”对话框，其中，“输入引用行的单元格”表示自变量的各个值排列在一行中，“输入引用列的单元格”表示自变量的各个值排列在一列中;

(5)在“输入引用列的单元格”文本框中输入C5单元格，表示指定该列数值对应的自变量在C5单元格，因为该例中，C5存放梯形的“高”就是自变量;

(6)单击“确定”按钮，在B10∶B14区域中显示出对应于左面一列数据的运算结果，如图4-75所示。

2.双变量模拟运算表

双变量模拟运算表是指Excel在模拟运算中，发生变化的有两个变量，一个变量的数值写在同一行，另一个变量的数值写在同一列。 仍以梯形面积的模拟运算为例，假设梯形的“高”不变，而“上底”和“下底”两个量发生变化，模拟运算梯形面积随其变化情况，如图4-76所示。

其具体操作步骤如下：

(1)在B1单元格输入公式：=(A3+B3)*C3/2;

(2)在C7∶G7区域中输入“上底”的变化值，B8∶B13区域输入“下底”的变化值，如图4-76所示;

(3)在B7单元格输入公式：=B1，将B1单元格中(A3+B3)*C3/2的关系引用到此处，注意这个位置是行和列交叉的单元格;

(4)选定B7∶G13区域，然后选择“数据”选项卡中“模拟分析\模拟运算表”命令，弹出“模拟运算表”对话框，在“输入引用行的单元格”文本框中输入A3单元格，因为横行表示的数值是“上底”，而A3就是表示梯形“上底”的自变量，同理，在“输入引用列的单元格”文本框中输入B3单元格，如图4-77所示;

(5)单击“确定”按钮，在C8∶G13区域中显示对应数值的运算结果，如图4-78所示。

图4-79给出了一个综合应用实例，根据银行利率、贷款总额和还款年限计算月还款额，并模拟当还款年限和贷款总额发生变化时月还款额的变化情况。 其中，D2单元格建立了月还款额公式，此处是用Excel的财务函数PMT实现的，其函数参数对话框如图4-80所示：在“Rate”文本框输入各期利率A2/12，“Nper”文本框输入贷款期总数C2*12，“Pv”文本框输入贷款额B2，其他两个文本框缺省，则D2单元格公式为：=PMT(A2/12，C2*12，B2)，计算结果为-2994.08，表示每月应还款2994.08元，A5∶B10区域是单变量模拟运算表，它显示了“年限”发生变化时月还款额的相应值;而E5∶I10区域是双变量模拟运算表，它显示了当“年限”和“贷款总额”都发生变化时月还款额的相应值。 具体操作方法与前述例子相似，请读者自行完成。

4.7.2 宏的简单应用

为了提高工作效率，可以使用Excel提供的“宏”功能，而且有些操作不使用宏的话会很麻烦。 我们每次听到“宏”总是和病毒联系在一块儿，一般自己做的宏是不会对自己有害的。Excel的宏是由一系列的Visual Basic语言代码构成的，因此，如果你熟悉Visual Basic语言，可以用它编写为宏，如果不熟悉VB语言，最简单的宏可以通过录制来建立。

1.设置使用宏功能

在Excel 2010中要想使用宏功能，需要添加“开发工具”选项卡。 首先单击“文件”选项卡，然后单击“Excel选项”按钮，如图4-81所示，在“自定义功能区”选项中，将“主选项卡”中的“开发工具”勾选，单击确定返回Excel界面后，我们就可以看到在功能区中多了一个“开发工具”选项卡，如图4-82所示。

2.宏录制

现在比如我们写一个宏，让它删除A2单元格的内容：单击“开发工具”选项卡，单击“”按钮，弹出“录制新宏”对话框，如图4-83所示，在“宏名”输入框中输入宏的名字，单击“确定”按钮。

此时我们就可以进行宏的录制了，在“开发工具”选项卡中最左侧可以看到目前的状态是“”，表示当我们在创建一个宏后，就已经开始在录制宏了。单击A2单元格，按一下“Delete”键，这个宏的操作就算完成了，单击“”。

3.执行宏

现在，在A2单元格中随便填上数值，单击其他任意的单元格，找到“开发工具”选项卡，单击“”按钮，打开“宏”对话框，如图4-84所示，选择我们刚才录制的宏，单击“执行”按钮，A2单元格的内容就没有了。

4.VBA简介

我们用宏录制的方法创建并执行了一个简单的宏，但这种方法创建的“宏”无逻辑判断和循环能力、人机交互能力差，当我们要使用Excel自动完成一些较复杂的重复工作时，可在Excel中使用VBA(Visual Basic for Applications)来实现，VBA是通过运行“宏”来工作的，事实上，“宏”是用Visual Basic编写的分步过程，如图4-85所示是用前面“宏录制”方法创建的宏的代码。 使用VBA还可以创建Excel的新功能，如开发新算法分析数据、构建简单的管理信息系统等，这些需要一定的Visual Basic编程基础，在此不再赘述。

5.保存录制了宏的工作簿

在录制了宏的工作簿中单击“保存”按钮时，会出现如图4-86所示的提示信息。

单击“否”按钮，弹出“另存为”对话框，单击“保存类型”下拉按钮，在弹出的列表中选择“Excel启用宏的工作簿(*.xlsm)”，如图4-87所示。 再单击“保存”按钮即可。

如果我们打开一个带有宏的工作簿，Excel会提醒我们打开的文件中带有宏，如果你不能确定宏是否带有恶意的成分就选择“禁用宏”，否则可以选择“启用宏”;在禁用之后即使宏中有恶意成分也不会对你的机器起作用了。

本章小结

本章主要介绍了Excel 2010的基本操作、公式与函数、数据统计处理、数据分析、报表打印等知识技能，通过学习重点掌握数据的创建、统计、分析、运算与图形化等操作方法与技巧，便于学习者在日常工作与学习中的轻松、快捷应用。

操作题

学生成绩分析

问题描述：制作学生成绩统计分析表，结果如图4-88所示。 主要任务为通过学生的期末考试成绩计算总成绩、名次和等级等，并对该学期的成绩进行汇总，如统计每小题的得分情况，统计每分数段的人数与所占百分比、最高分、最低分和平均分等，同时制作图表反应考试情况，并对期末考试成绩不合格及优秀的记录用条件格式把它们区别开来。

基础知识：

Excel的基本操作，如单元格格式设置、自动填充、插入函数、数据管理等知识。

实验要求：

1.创建工作表、输入文字与数据、编辑工作表

(1)启动 Excel 2010，打开 Excel工作薄，默认情况下包含“Sheet1”“Sheet2”“Sheet3”工作表。

(2)将“Sheet1”工作表改名为“成绩分析表”。

(3)删除Sheet2和Sheet3工作表。

(4)在“成绩分析表”中输入如图4-88所示数据。 输入数据时，如A17单元格数据设置单元格格式为自动换行，C17、E17等几个单元格在数据输入时用ALT+ENTER换行。

(5)合并A1∶L1、A13∶A14、A17∶A20、C17∶D17、C18∶D18等单元格，合并的单元格后结果如图4-88所示。

2.数据计算与统计

统计计算机总成绩、名次、等级、平均分等。 总成绩为学生各小题的得分值，可通过自动求和按钮或函数方式实现，具体操作已在前面章节中介绍，因此本处只给出具体公式或函数表达式。

在H3单元格输入“=SUM(C3∶G3)“、I3单元格输入“=RANK(H3，$H$3∶$H$12)“、J3单元格输入“=IF(H3〉=90，“优秀“，IF(H3〉=80，“良好“，IF(H3〉=70，“中等“，IF(H3〉=60，“合格“，“不合格“))))“、C13单元格中输入“=AVERAGE (C3∶C12)“。

拖动复制各公式至相关其他单元格。

在C14∶G14输入各小题原小题总分值，分别是16、32、10、27、15，那么每小题的得分率可为该小题的平均得分/小题总分。 也即在C15单元格输入“=C13/C14”，然后拖动复制公式到G14，计算结果如图4-88所示。

统计学生的考试情况，C18单元格为C18与D18合并，该单元格存放期末考试成绩在90分上的记录，那么在该单元格中输入“=COUNTIF(H3∶H12，“〉=90“)”即可， E18为分数80～89之间统计值，那么在该单元格中输入“=COUNTIF(H3∶H12，“〉=80“)-COUNTIF(H3∶H12，“〉=90“)”，对应其他几个单元格G18、I18、K18中的输入分别为“=COUNTIF(H3∶H12，“〉=70“) -COUNTIF(H3∶H12，“〉=80“)”“=COUNTIF(H3∶H12，“〉=60“)-COUNTIF(H3∶H12，“〉=70“)”“=COUNTIF(H3∶H12，“〈60“)”，其他的单元格C20、G20、K20中分别为“=max(H3∶G12)”“=min (H3∶G12)”、“=average(H3∶G12)”。 C19及后几个单元格用于存放各分数段所占人数的百分比，C19中的输入为“=C18/SUM($C18∶$L18)”，拖动复制该公式至K19，再设置该几个单元格的数字显示格式为百分比方式显示。

3.创建图表

为直观地反应各小题的得分率，现创建一个在各小题上得分率的图表。 选择默认图表(柱形图)，数据区域为“C15∶G15”，系列为“小题完成得分率”，名称为“各题完成得分率”。

4.设置条件格式

为了能够非常清晰明了地找出成绩中最差、最好的那部分学生(即成绩优秀与不合格)，可通过对数据表设置条件格式。 首先选择要设置格式的表格区域A3∶J12，再设置条件格式，条件为“〈60”“〉=90”。

5.表格布局与保存

根据需要，对表格及数据显示进行修饰，如调整行列宽，设置字体大小等，使之看起来比较美观。

若修改表中数据，通过计算获得的数据也会自动更新，数据分析表创建完成后可以保存为成绩分析模板，应用此模板使用者只需填入原始数据就可以了，这使处理数据创建报表更方便。

【一级计算机基础及Office应用认证试题】

1.在Excel 2010中，对工作表的数据进行一次排序，排序主要关键字( )、次要关键字( )。

A.只能一列、可以多列 B.只能两列、可以多列

C.最多三列、可以多列 D.任意多列、可以多列

2.以下操作中不属于Excel的操作是( )。

A.自动排版 B.自动填充数据

C.自动求和 D.自动筛选

3.在Excel 2010中，下列叙述不正确的是( )。

A.每个工作簿可以由多个工作表组成

B.输入的字符不能超过单元格宽度

C.每个工作表有1048576行，16384列

D.单元格中输入的内容可以是文字、数字、公式

4.复制选定单元格数据时，需要按住( )键，鼠标指向( )并拖动鼠标器。

A.Shift单元格数据 B.Ctrl单元格边框

C.Alt单元格数据 D.Esc单元格边框

5.公式“=SUM(C2∶C6)”的作用是( )。

A.求C2到C6这五个单元格数据之和

B.求C2和C6这两个单元格数据之和

C.求C2和C6这两个单元格的比值

D.以上说法都不对

6.在Excel 2010中，字符型和数值型数据默认显示方式分别是( )。

A.中间对齐、左对齐 B.右对齐、中间对齐

C.左对齐、右对齐 D.自定义、左对齐

7.Excel 2010工作簿存盘时默认的文件扩展名为( )。 (大写字母改小写字母)

A.xlsx B.xls C.doc D.gib

8.以下单元格引用中，下列哪一项属于混合应用( )。

A.E3 B.$CE$18 C.C$20 D.$D$13

9.在Excel公式中用来进行乘方的标记为( )。

A.× B.( ) C.∧D.*

10.在Excel中当鼠标键移到自动填充柄上，鼠标指针变为( )。

A.双箭头 B.双十字 C.黑十字 D.黑矩形

11.当输入数字超过单元格能显示的位数时，则以( )来表示。

A.科学记数法 B.百分比 C.货币 D.自定义

12.下列各项中不属于Excel 2010日期格式的是( )。

A.2013-3-4 B.2013年1月19日

C.2013/3/4 D.23/6-2013

13.&表示( )。

A.算术运算符 B.文字运算符

C.引用运算符 D.比较运算符

14.默认的图表类型是二维的( )图。

A.饼 B.折线 C.条型 D.柱型

15.以下图标中，( )是“自动求和”按钮。

A.∑ B.S C.f D.fx

16.下列( )函数是计算工作表中数据区数值的个数。

A.SUM(A1∶A10) B.AVG(A1∶A10)

C.MIN(A1∶A10) D.COUNT(A1∶A10)

17.下列各项中全部属于Excel 2010运算符号的一项是( )。

A.数字运算符、关系运算符、逻辑运算符

B.算术运算符、文本运算符、关系运算符

C.逻辑运算符、算术运算符、函数运算符

D.比较运算符、算术运算符、文本运算符

18.在单元格输入1∶5，结果为( )。

A.数值1/5 B.日期1月5日 C.数值0.2 D.时间1时5分

19.在Excel 2010中，关于“选择性粘贴”的叙述，错误的是( )。

A.选择性粘贴可以只粘贴格式

B.选择性粘贴可以只粘贴公式

C.选择性粘贴可以将源数据的排序旋转90℃，即“转置”粘贴

D.选择性粘贴只能粘贴数值型数据

20.在数据移动过程中，如果目的地已经有数据，则Excel会( )。

A.请示是否将目的地的数据后移 B.请示是否将目的地的数据覆盖

C.直接将目的地的数据后移 D.直接将目的地的数据覆盖

21.在Excel 2010中，下面关于分类汇总的叙述正确的是( )。

A.分类汇总前必须按汇总项排序数据库

B.汇总方式只能是求和

C.分类汇总的汇总项只能是一个字段

D.分类汇总可以被删除，但删除汇总后排序操作不会撤销

22.在Excel 2010中，设置页眉和页脚的内容可以通过( )进行。

A.“编辑”选项卡“页面设置” B.“视图”选项卡“页面布局”

C.“格式”选项卡“页面样式” D.“工具”选项卡“页眉和页脚”

23.在Excel 2010中，关于“筛选”的正确叙述是( )。

A.自动筛选和高级筛选都可以将结果筛选至另外的区域中

B.不同字段之间进行“或”运算必须使用高级筛选

C.自动筛选的条件只能是一个，高级筛选的条件可以是多个

D.如果所选的条件出现在多列中，并且条件间有“与”的关系，必须使用高级筛选

24.下列关于在Excel 2010中对数据进行分类汇总的说法错误的是( )。

A.通过使用“分类汇总”命令可以自动计算列的列表中的分类汇总和总计

B.如果正在处理Microsoft Excel表格，则“分类汇总”命令不会灰显

C.若要只显示分类汇总和总计的汇总，则单击行编号旁边的分级显示符号，使用和符号来显示或隐藏各个分类汇总的明细数据行

D.选择包含分类汇总区域的单元格，在“分类汇总”对话框中，单击“全部删除”可删除分类汇总

25.在Excel 2010中，选择数据清单中满足条件的数据显示在工作表上，其他数据隐藏是通过( )完成的。

A.在“数据”选项卡中选择“分类汇总”命令

B.在“数据”选项卡中选择“记录单”进行条件查询

C.在“数据”选项卡中选择“有效数据”命令

D.在“数据”选项卡中选择“筛选”命令

26.下列关于Excel 2010图表的说法不正确的是( )。

A.修改数据区数据，图表也会随着变化

B.可能更改图表的标题

C.不能更改图表的类型

D.图表项目可以删除

27.在Excel 2010中，下列各项关于打印区域的说法错误的是( )。

A.可以打印活动工作表 C.可以打印整个工作簿

B.不能忽略打印区域 D.可以打印选定的区域

28.下列序列中，不能直接利用自动填充快速输入的是( )。

A.Jan、Feb、Mar…… B.Mon、Tue、Wed……

C.第一名、第二名、第三名…… D.子、丑、寅……

29.清除单元格的内容后，( )。

A.单元格的格式、边框、批注都不被清除

B.单元格的边框也被清除

C.单元格的批注也被清除

D.单元格的格式也被清除

30.在Excel 2010中，关于区域名字的叙述不正确的是( )。

A.区域名可以与工作表中某一单元格地址相同

B.同一个区域可以有多个名字

C.一个区域名只能对应一个区域

D.区域的名字既能在公式中引用，也能作为函数的参数

参考答案：

1.A 2.A 3.B 4.B 5.A 6.C 7.A 8.C 9.C 10.C 11.A 12.D 13.B 14.D 15.A 16.D 17.B 18.D 19.D 20.D 21.AD 22.B 23.B 24.B 25.D 26.C 27.B 28.C 29.A 30.A