第6章　Excel中的控件

你一定在Excel报告上见过按钮、下拉框、滚动条等小玩意儿，可能会觉得这个Excel报告非常专业，以为只有Excel高手才能实现。其实不然，普通的Excel用户也能够非常容易地使用这些小玩意儿。

这些小玩意儿就是Excel中的控件，不要觉得“控件”这个名字很高深，其实，“控件”就是“控制Excel的零件”，是一种能够很方便地控制和更改Excel单元格中数据的工具。

我们知道，改变Excel单元格中的数据能够驱动工作表中的公式重新计算，从而立即得到新的计算结果，但是，手动改变单元格中的内容毕竟不是十分方便，有时为了查看不同的计算结果需要“连续地”改变公式所引用的单元格中的数值，如果一次次地向单元格中手动输入不同的数值，操作起来会很不方便。

使用Excel控件，可以很方便地改变单元格中的数值，从而“连续地”驱动公式重新计算，快速地查看公式在不同条件下的计算结果。

大部分Excel控件的使用逻辑基本是相同的，具体说明如下。

（1）添加控件到Excel工作表。

（2）设置控件，使控件和Excel工作表上的某个单元格建立联系。

如图6-1所示，我们可以通过拖动滚动条控件上的滑块，快速地改变B2单元格中的数值，方便地查看在不同的本金下，一年存款的到期金额。

图6-1

Excel中的所有控件位于Excel功能区的“开发工具”标签下，如图6-2所示。

图6-2

如果在你的Excel界面中没有找到“开发工具”标签，选择“文件→选项”命令，在打开的对话框中选择“自定义功能区”，在右侧的“自定义功能区”下拉框中选择“主选项卡”，在“主选项卡”下方的列表中勾选“开发工具”复选框即可，如图6-3所示。

图6-3

Excel中的“控件”最常用的场合就是用来控制Excel单元格数值的变化。Excel控件分为两组，一组称为“表单控件”，另外一组称为“ActiveX控件”，两组控件有很多类似的地方。“ActiveX控件”比较适合在VBA编程环境下使用；对于尚不是十分熟悉VBA的用户来说，“表单控件”简单易用，已经能够解决实际应用中的大部分问题了。

下面结合案例介绍几个常用表单控件的用法。

6.1　复选框控件

在大部分企业的行政后勤部门，为了提高部门的服务水平，经常需要发放一些调查问卷，下面我们看一看如何用Excel表单控件制作员工调查问卷。

如图6-4所示，该Excel工作簿由两个工作表组成，一个是“问卷题目”，另一个是“问卷结果”，员工在“问卷题目”中勾选了相应的选项后，对应的数据便会保存到“问卷结果”的相应的位置，以便我们对数据进一步处理。

图6-4

整个调查表的制作过程如下。

为了问卷显得更整洁，首先去掉Excel工作表的网格线，其方法是：在“视图→显示”功能区中取消勾选“网格线”复选框。

接着选择“开发工具→控件→插入→表单控件→分组框”命令。在Excel工作表界面上画一个分组框控件，调整好大小和位置，如图6-5所示。

图6-5

然后修改分组框控件的标题，选择“开发工具→控件→插入→表单控件→复选框”命令，插入“复选框”表单控件，如图6-6所示。

图6-6

在分组框中用鼠标画一个复选框控件，如6-7所示。

图6-7

接着复制多个复选框表单控件。

调整好所有复选框的位置，并修改复选框右边的文字说明，如图6-8所示。

图6-8

把Sheet1工作表重命名为“问题”，增加一个新的工作表，重命名为“答案”，并制作如图6-9所示的表格，该表格用来保存用户选择的答案。

图6-9

下面设置控件格式，使控件与工作表上的单元格建立联系。

选中第一个控件，单击鼠标右键，在弹出的快捷菜单中选择“设置控件格式”命令，打开“设置对象格式”对话框。

在“设置对象格式”对话框中，选择“控制”标签，在“单元格链接”文本框中，利用文本框右侧的单元格地址拾取按钮选择与该复选框对应的用户答案保存位置，这里我们选择“答案”工作表上的$B$2单元格，如图6-10所示。

图6-10

按照上述方法设置好所有复选框控件的链接单元格的位置。

现在，当我们在问卷中选择不同的复选框时，可以看到其所链接的单元格内容相应地发生了变化，选中复选框时，显示为TRUE，否则显示为FALSE，如图6-11所示。

图6-11

在前面介绍SUMPRODUCT函数的时候我们已经知道，在Excel中，TRUE和FALSE在参与数学计算的时候可以当做1和0使用，因此下面对“答案”表中的数据做一个简单的数学计算，把每一个答案乘以1，这样，我们把TRUE变成1，把FALSE和空单元格变成0，如图6-12所示。

图6-12

经过对此调查问卷进一步修饰，就可以把设计好的问卷发给每个员工，等所有员工的问卷收集完成后，就可以方便地对调查问卷结果进行统计了。

6.2　组合框控件

组合框控件是Excel中另一个经常用到的控件，当单击组合框控件时，会展开一个有多个选项的下拉列表，我们可以从下拉列表中选择所需要的选项，组合框会把对应选项在下拉列表中的位置填写到组合框所控制的单元格内。

设置组合框的基本逻辑：首先确定填充组合框控件下拉列表的数据源的位置，然后指定控件所链接的单元格。

控件和其所链接的单元格一般是双向控制的，即控件的改变能够引起所链接的单元格内容的改变；同样，控件所链接的单元格内容的改变也能够引起控件的改变。

下面结合案例具体介绍Excel组合框控件的用法。

如图6-13所示，如果我们在单元格E2中的组合框中选择一个省份，那么该省份在组合框中的“位置序号”就会自动填充到F2单元格，在位置序号下面，这里用INDEX函数把F2单元格中的位置序号转换成对应的省份名称，顺便把该省份对应的简称和省会城市也提取出来。

图6-13

具体实现上述功能的操作如下。

首先，为了方便组合框的设置，可以为组合框的数据源所在的单元格区域命名一个名称。在这里为了让组合框中的选项数量能够自动地随着数据源的数据的增加而增加，我们在给数据源命名的时候采用一种“动态区域命名”的方法。

选择“公式→定义的名称→定义名称”命令，在弹出的“新建名称”对话框中作如下设置。

在“名称”处，为动态区域取名为“省份列表”，在“引用位置”处输入公式“=OFFSET(组合框!$A$1,0,0,COUNTA(组合框!$A:$A),3)”。完成后，单击“确定”按钮关闭对话框，如图6-14所示。

图6-14

上述公式的含义为：返回一个单元格区域，因为行偏移和列偏移都是0，所以这个返回的区域的左上角位于“组合框!$A$1”，区域的高度由COUNTA(组合框!$A:$A)决定（即，A列中有多少行，数值区域的高度就是多少），列数为3。这样我们所定义的“省份列表”就是一个动态区域名称。“省份列表”所指代的区域将会随着工作表上省份列表的扩充而扩充。如果你对OFFSET函数的用法还不太熟悉，请参看4.5节。

现在插入组合框控件。选择“开发工具→控件→插入→表单控件→组合框”命令，然后在工作表的E2单元格位置处画一个组合框控件。

选中组合框控件，单击鼠标右键，在弹出的菜单中选择“设置控件格式”命令。在弹出的“设置对象格式”对话框中，选择“控制”标签，在“数据源区域”文本框中输入刚才定义的动态区域名称“省份列表”；在“单元格链接”文本框中处，选择F2单元格，这样控件返回的数值将填写在F2单元格。单击“确定”按钮关闭对话框，如图6-15所示。

图6-15

我们看到，组合框控件中已经填充好我们所设置的“省份列表”了。这里需要说明的是，“省份列表”是一个有3列的列表，表单控件里的组合框只能显示第一列的数据，不过这也满足我们的需要了。

选择组合框中的任意一个选项后，相应选项在下拉列表中对应的位置就会显示在F2单元格中，如图6-16所示。

图6-16

如果希望在选择组合框中的选项时，返回的不是冷冰冰的数字，而是数字所代表选项的内容。可以借助Excel的公式和函数达到这个目的。具体公式如图6-17所示。如果你对INDEX函数还不太熟悉，请参看4.3节。

图6-17

下面是展示“动态区域名称”能力的时候了：在工作表A:C列的下方增加新的省份（江西省以下的部分都是新增加的数据），我们会发现，组合框中的选项也自动增加了，如图6-18所示。

图6-18

微言微语

●　90％的人做Excel表格没用过数据透视表，90％的人做PPT没用过母版，90％的人不知道Word中还有文档结构图一说，90％的人不知道Access怎么用。而事实上，只要你掌握了上面提到的任何一个Office技术，就会在你需要的时候节省你90％的时间。

●　我们的老板有一个愿景，将来部门里只需要两个雇员，“一个人”和“一个电脑屏幕上的按钮”！每天早晨只要按一下按钮，所有的商务数据分析报告就会全部自动完成，而那个人就是他自己！

●　这是一宗高科技犯罪，犯罪分子对Excel资料进行了特殊处理，我们采用国际先进的技术手段，经过3个技术人员连续5个昼夜的刻苦攻关，终于发现，原来犯罪分子是把工作表中的字符设置成了白色，给我们造成了工作表中没有数据的假象！

●　Excel受虐综合征：一日不做表，胃口会变小；一周不做表，从头疼到脚；一月不做表，简直活不了。