Excel表格中如何让用户按照自己的规定输入信息?(四)

当我们在编辑Excel表格的时候，如果想要用户按照我们的规定来填写信息，那么就需要对“数据有效性”进行深刻理解。前面的三篇文章中，我已经简单介绍了如何控制文本长度，日期时间，数字开头，以及简单的下拉菜单操作。

那么今天我们要介绍的是下拉菜单制作中，最难的部分，如何实现两个下拉菜单之间的关联？

也许有读者还搞不清楚这是什么意思。那么我们可以看到在上图中红色框内的部分，如何实现当我们首先选择了学院之后，就可以在后面的专业中只显示所填写学院的专业呢？比如说我选择了会计学院的话，专业的下拉菜单中就可以显示会计学院中的几个专业。

那么，我们先新建一张工作表在同一工作簿中，我们可以将其命名为“数据”。并将我们要用到的数据信息输入进去。

其次，我们选中学院下面的一列学院名的单元格区域，打开菜单“插入”－“名称”－“定义”，输入名称“Academy”并保存。

然后我们回到原来编辑的表格中，选中需要规定的单元格，单击“数据”功能区中的“数据有效性”按钮，在弹出的“数据有效性”对话框中的“设置”选项卡下，选择“允许”下面的“序列”，其来源则为刚刚命名的“Academy”区域。

最后就到最困难的部分，如何使专业选项与前面的学院选项联系起来呢？

很明显，在数据中我们将学院所设立的专业输入在了学院后方的单元格中，也就是说，我们选择的序列区域应该是与学院所在的单元格为参考点移动得到的选项，这个时候很容易想到要用到我们的OFFSET函数了。

OFFSET函数是返回对单元格或单元格区域中指定行数和列数的区域的引用。在Excel2013版本的帮助中显示，OFFSET的语法是：OFFSET(reference, rows, cols, [height], [width])。第一个参数为参考点，第二个参数为偏移的行数，第三个为偏移的列数，第四个参数和第五个参数则不是必须填写的，其中height代表需要返回的引用的行高，width代表需要返回的引用的列宽。

我们打开功能区中“公式”功能区，选择“名称管理器”，点击弹出对话框左上角的“新建”，定义名称为“Major”，在引用位置中输入“=OFFSET(数据!$A$1,MATCH(数据有效性综合应用!$B$9,Academy,0),1,,COUNTA(OFFSET(数据!$B$1:数据!$G$1,MATCH(数据有效性综合应用!$B$9,Academy,0),,)))”。

其中“数据！”“数据有效性综合应用！”表示的是数据表的引用，那么OFFSET函数中的第一部分“数据！$A$1”表示的是数据工作表中A1单元格的绝对引用地址。

MATCH函数为匹配函数,指返回指定数值在指定数组区域中的位置。而COUNTA函数的功能是返回参数列表中非空的单元格个数。利用 COUNTA 函数可以计算单元格区域或数组中包含数据的单元格个数。

三个函数的结合则成功地使确定学院之后能够精确的查找出对应的专业区域，且不会出现空白的选项，有几个专业则显示几个选项，COUNTA 函数实现了OFFSET函数的动态引用。

回到“数据有效性综合应用”工作表中，在专业后的单元格中设置“数据有效性”，选择“允许”下面的“序列”，其来源则为刚刚命名的，可以动态改变的“Major”区域。

这个时候任意选择了学院之后，比如说统计学院，保险学院，都可以对应的使得专业的下拉菜单中出现该学院内的专业了！

这样设置好表格之后，填写者可以更高效地并按照规范填写数据了！

-------------------------------------------------------------------------------------------------------------------------------------

【附】：Match函数语法为MATCH(lookup_value, lookup_array, match_type)。

lookup_value：需要在数据表（lookup_array）中查找的值。可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。可以包含通配符、星号 (*) 和问号 (?)。星号可以匹配任何字符序列；问号可以匹配单个字符。

lookup_array：可能包含有所要查找数值的连续的单元格区域，区域必须是某一行或某一列，即必须为一维数据，引用的查找区域是一维数组。

match_type：表示查询的指定方式，用数字-1、0或者1表示。

为1时，查找小于或等于lookup_value的最大数值在lookup_array中的位置，lookup_array必须按升序排列：

为0时，查找等于lookup_value的第一个数值，lookup_array按任意顺序排列：

为-1时，查找大于或等于lookup_value的最小数值在lookup_array中的位置，lookup_array必须按降序排列。利用MATCH函数查找功能时，当查找条件存在时，MATCH函数结果为具体位置（数值），否则显示#N/A错误。