|
|
关于下拉菜单的教程已经写过很多篇了，
基本上都是基于单个单元格区域或小范围单元格区域联动的技巧。
通过这些技巧的灵活搭配，或者与其他技巧（如条件格式等）的配合使用，可以解决几乎所有的下拉菜单需求。还没看过的，点击下列文字链接直达。
<下拉菜单大闯关，你在什么段位？>
<下拉菜单新玩法>
<下拉菜单总是能给人惊喜>
<下拉菜单高阶用法，只有1%的人会用>
那么，下拉菜单还有什么可以讲的？
今天就再给大家分享一个倾向于整体解决方案的下拉菜单技巧。
先看一下效果：
左侧为用户界面，右侧为维护界面。
为方便演示，将用户界面和维护界面放在了一个Sheet页中，实际操作时可以将维护界面隐藏。
用户界面中的参数名、参数选项可以在维护界面中动态维护，实时更新。
包括参数的位置、参数选项的数量都可以动态维护。
在做一些大型的表单时，这个技巧非常的实用。
做过表单的同学会知道，界面的维护其实是很麻烦的，特别是在表单有更新需求时，可能前期设计了很久的界面，因为一个参数位置的变化，所有的格式或数据有效性都要重新设置一遍，相当的痛苦。
做成动态维护后，就可以实现在一个固定的区域，能通过参数代码配合后台维护，灵活调整参数和下拉选项内容。
比如，更改参数名称：
比如，更改参数位置：
比如，更改参数选项：
比如，增加/减少参数选项：
比如，增加新的参数：
别看功能这么强大，设置起来并不算复杂。
不过如果之前的下拉菜单的相关教程你都没看过，那么可能会吃力一点。
教程如下，请打开Excel一起操作。
（核心操作在第4步，有基础的可以直接跳到第4步）
1、准备用户界面
准备如图所示用户界面，在参数名称之前留一列空列，用来填写参数代码。
此处参数代码列可以设置为自定义格式：;;;，隐藏内容。
可以根据实际情况，灵活布局。
2、准备后台维护界面和数据
在另外的Sheet页中，准备如图所示的后台数据。
包括参数代码、参数名称和参数选项。
其中，参数选项因为可能存在数量不同的情况，需在表格区域中留足空间。
3、用户界面参数名称的设置
这一步很简单，在参数名称单元格中用VLOOKUP函数根据前一列的参数代码从后台数据中取值即可。
某些参数可能需要留空，同时也避免参数代码输错，公式中增加IFERROR函数来处理错误。
具体公式如下：
=IFERROR(VLOOKUP(A2,QY_List,2,0),&#34;&#34;)
其中，QY_List是对包含参数代码和参数名称的后台数据的命名引用。
4、用户界面参数选项的设置
之前的关于下拉菜单的教程中，根据其他单元格的内容来调整下拉菜单选项范围用的是命名+INDIRECT函数的方法。
原理是，通过INDIRECT函数来生成一个动态的引用区域。
这里用这个方法也可以行得通，即先将参数选项区域以参数代码命名，再用INDIRECT函数引用参数代码。
但是考虑到参数可能较多，同时参数选项可能有多有少，甚至后续可能还会进一步的维护。
这里换一种方法。
选中所有需要设置参数选项的单元格，打开数据验证，选择序列，并在来源中输入如下公式：
=OFFSET($J$1,MATCH(A2,$J:$J,0)-1,2,1,COUNTA(OFFSET($J$1,MATCH(A2,$J:$J,0)-1,2,1,10)))
公式比较长，详细解释一下。
核心函数为OFFSET，它可以返回对单元格或单元格区域中指定行数和列数的区域的引用。
OFFSET函数的语法为：
OFFSET(reference, rows, cols,[height], [width])
它有5个参数。以上述公式为例，我们可以将它输入在单元格中查看具体参数和结果。
1参-reference：$J$1
需要偏移的引用基准。这里我们取J1单元格，并用绝对引用。
2参-rows：MATCH(A2,$J:$J,0)-1
需要偏移的行数。这里利用MATCH函数，取得用户界面中参数代码在后台数据中J列的位置，再减1，即为所需要的代码相对于J1单元格的行数偏移量。
例如，A2单元格中代码为QYA，用MATCH函数，能得到QYA在J列的相对位置为2，即J2单元格。而J2单元格相对于J1单元格，偏移的行数为2-1=1。
3参-cols：2
需要偏移的列数。和2参行数类似，我们最终需要返回的是参数选项，它起始于L列，相对于J1单元格，向右偏移了2列。
4参-height：1
需要返回的引用的行高。这里我们只需要返回一行的内容，行高设为1即可。
5参-width：
COUNTA(OFFSET($J$1,MATCH(A2,$J:$J,0)-1,2,1,10))
需要返回的引用的列宽。这里嵌套了一个OFFSET函数，前4参数与之前相同，仅5参列宽设为了定值10，代表返回固定的10列列宽，即从L列到U列。——当然，这个得根据你的实际情况相应调整。
再用COUNTA函数统计这10列中的非空单元格数据，得到的值即为参数选项的数量，用它来作为上一个OFFSET函数的5参，即可以实现根据不同参数代码，返回对应的参数选项区域。
最后
只需要在用户界面中的指定位置，填写上对应的参数代码，参数名称和对应的下拉选项均可自动根据参数代码的变化而变化了。
总结，
这里花了比较大的篇幅来讲解OFFSET函数，主要是为了让大家能理解动态引用的逻辑。
不过，可以动态引用的函数不仅仅只有OFFSET，大家可以根据自己的情况灵活使用合适的函数。
动态引用是这个案例中设置下拉菜单选项的核心操作，但，这整个技巧的核心在于，引入了后台维护的概念，类似于网页设计——有前端界面，有后台维护。
虽然前期的设计稍微麻烦了一些，但对于后期维护而言，方便得不是一点半点。
我自己也用这个技巧做了很多大型的表单，使用效果非常好。
思维再发散一些，如果将后台数据存放在服务器上的另外一个工作簿中，同时在用户界面的工作簿中增加一段打开时自动从服务器上同步后台参数数据的代码，就能实现远程的动态维护了。
再更进一步，还可以在用户界面中增加一些功能，比如填写完成后自动发送邮件至指定地址，或者根据填写的参数测算出价格，又或者根据填写的参数从后台数据表中匹配出合适的结果，等等。
可以动态维护，可以远程更新，可以自动出结果，想一想，你们公司的报价系统、尺寸测算系统、任务跟踪系统……是不是也不过如此？
如果不想花高额的费用去开发定制软件或系统，用Excel来实现也不失为一种好的解决方案。
只要你敢想、会做，Excel统统都能实现。
不会也没关系，来趣言Excel。 |
|
发表于 2022-11-30 10:27:54