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

Excel 表格中如何让用户按照自己的规定输入信息？（四） 当我们在编辑 Excel 表格的时候，如果想要用户按照我 们的规定来填写信息，那么就需要对“数据有效性”进行深刻 理解。前面的三篇文章中，我已经简单介绍了如何控制文本 长度，日期时间，数字开头，以及简单的下拉菜单操作。那 么今天我们要介绍的是下拉菜单制作中，最难的部分，如何 实现两个下拉菜单之间的关联？也许有读者还搞不清楚这 是什么意思。那么我们可以看到在上图中红色框内的部分， 如何实现当我们首先选择了学院之后，就可以在后面的专业 中只显示所填写学院的专业呢？比如说我选择了会计学院 的话，专业的下拉菜单中就可以显示会计学院中的几个专业。 那么，我们先新建一张工作表在同一工作簿中，我们可以将 其命名为“数据”。并将我们要用到的数据信息输入进去。其 次，我们选中学院下面的一列学院名的单元格区域，打开菜 后我们回到原来编辑的表格中，选中需要规定的单元格，单 击“数据”功能区中的“数据有效性”按钮，在弹出的“数据有效 性”对话框中的“设置”选项卡下，选择“允许”下面的“序列”其， 单“插入”－“名称”－“定义”，输入名称“Academy 单“插入”－“名称”－“定义”，输入名称“ Academy”并保存。然 来源则为刚刚命名的“ Academy”区域。最后就到最困难的部 分，如何使专业选项与前面的学院选项联系起来呢？很明显， 在数据中我们将学院所设立的专业输入在了学院后方的单 元格中，也就是说，我们选择的序列区域应该是与学院所在 的单元格为参考点移动得到的选项，这个时候很容易想到要 用到我们的 OFFSET 函数了。 OFFSET 函数是返回对单元 格或单元格区域中指定行数和列数的区域的引用。在 Excel2013 版本的帮助中显示， OFFSET 的语法是： OFFSET(reference, rows, cols, [height], [width]) 。第一个参 数为参考点，第二个参数为偏移的行数，第三个为偏移的列 数，第四个参数和第五个参数则不是必须填写的，其中 数，第四个参数和第五个参数则不是必须填写的， 其中 height 代表需要返回的引用的行高， width 代表需要返回的引用的 列宽。我们打开功能区中公式”功能区，选择“名称管理器”，点击弹出对话框左上角的“新建”，定义名称为“Major 列宽。我们打开功能区中 公式”功能区，选择“名称管理器”， 点击弹出对话框左上角的“新建”，定义名称为“ 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_t ype)。look up _value :需要在数据表(look up _array ) 中查找的值。 可以为数值 (数字、 文本或逻辑值) 或对数字、 文本或逻辑值的单元格引用。可以包含通配符、星号(*) 文本或逻辑值的单元格引用。可以包含通配符、星号 (*) 和 问号 (?) 。星号可以匹配任何字符序列；问号可以匹配单个 字符。 lookup_array ：可能包含有所要查找数值的连续的单 元格区域， 区域必须是某一行或某一列， 即必须为一维数据， 引用的查找区域是一维数组。 match_type ：表示查询的指定 方式，用数字-1、0 或者 1 表