下拉框模糊匹配_巧用数据验证制作模糊匹配的下拉列表

雨点打透心脏的1/2处 2023-01-06 04:25 110阅读 0赞

对于Excel的参照性输入，我们一般会使用数据验证里面的序列功能。常规下我们用作系列的数据源无非就是一个数据列表。但这种处理有个很大的问题，那就是如果系列里面的数据项太多，那么在下拉列表里面非常难以查找，他就会变成如下图这般，很多个列表项目全部混在一起，需要用滚动条不停的滚动才能找到。

![46abc39463f3c47ce81b36f39958d032.png][]

以上这种常规的做法效率非常低，体验并不好，那么有没有一种快速定位列表项的办法呢？当然有，且看下面我们怎么实现它。

### 1、建立数据列表项目的数据源。 ###

在此，为了更加快速的输入，我们采用助记码方式，而非直接输入汉字。毕竟字母输入的效率肯定比汉字输入要高。

![2327c791f5e4805ef0424985a59a78aa.png][]

### 2、设置数据验证 ###

具体办法是在需要做下拉列表的地方做数据验证，这里我就不废话了。毕竟大家都知道如何操作。这里最关键的地方有两点：

第一点，就是数据来源的设定。结合上表，我们在此输入的数据来源的公式为：

=OFFSET($B$1,MATCH("*"&E2&"*",$C$2:$C$11,0),0,COUNTIF($C$2:$C$11,"*"&E2&"*"))

该公式就不做过多解释了，这里不是Excel函数教育的地方。

![9eb2f8186722a2f11dff98f01cf8a22a.png][]

第二点，出错警告这里必须去除选项，否则直接没法完成查找引用。

![8e48efb91d5dcc4997840c96891fa1da.png][]

### 3、实际使用场景 ###

在此，我们在做完数据验证的E2单元格输入助记码“tk”，然后单击下拉框，于是就自动列出了包含助记码为“tk”的项目名称。当然了，这里助记码是不分大小写的。助记码可以输入任意字符完成模糊匹配

![e614f4398ca7d6ee8c8deec7dde026a8.png][]

![ae6a78decb9c4a4e050c8b17f43b32b4.png][]

### 4、深化助记码 ###

执行到第三步时，实际上已经完全可以使用了。但鉴于用户体验的问题，如果还想更进一步，那么助记码这个都可以使用自定义函数来自动生成。当然了，如果不愿意使用自定义函数，则仍然可以使用手工编制助记码。但更推荐这种使用函数自动对全字段取码的方式，因为这个是全字段拼音简码，匹配的范围要比部分拼音码要更宽一些。

函数取码的效果如下：

![b136dcc988a370fc3ddeb7d6fa92f43c.png][]

当然了要实现以上自动生成汉字拼音首字母，我们得先用VBA做一个自定义函数“PinYin”。如下图，在VBA编辑器里面插入一个模块，然后输入如下代码：

Function PinYin(ByVal Expression) As String    Dim lngI    As Long    Dim strWord As String       Expression = Trim$(IIf(IsNull(Expression), 0, Expression))    For lngI = 1 To Len(Expression)        strWord = Mid$(Expression, lngI, 1)        Select Case Asc(strWord)            Case -20319 To -20284: PinYin = PinYin & "A"            Case -20283 To -19776: PinYin = PinYin & "B"            Case -19775 To -19219: PinYin = PinYin & "C"            Case -19218 To -18711: PinYin = PinYin & "D"            Case -18710 To -18527: PinYin = PinYin & "E"            Case -18526 To -18240: PinYin = PinYin & "F"            Case -18239 To -17923: PinYin = PinYin & "G"            Case -17922 To -17418: PinYin = PinYin & "H"            Case -17417 To -16475: PinYin = PinYin & "J"            Case -16474 To -16213: PinYin = PinYin & "K"            Case -16212 To -15641: PinYin = PinYin & "L"            Case -15640 To -15166: PinYin = PinYin & "M"            Case -15165 To -14923: PinYin = PinYin & "N"            Case -14922 To -14915: PinYin = PinYin & "O"            Case -14914 To -14631: PinYin = PinYin & "P"            Case -14630 To -14150: PinYin = PinYin & "Q"            Case -14149 To -14091: PinYin = PinYin & "R"            Case -14090 To -13319: PinYin = PinYin & "S"            Case -13318 To -12839: PinYin = PinYin & "T"            Case -12838 To -12557: PinYin = PinYin & "W"            Case -12556 To -11848: PinYin = PinYin & "X"            Case -11847 To -11056: PinYin = PinYin & "Y"            Case -11055 To -10247: PinYin = PinYin & "Z"            Case Else:             PinYin = PinYin & strWord        End Select    NextEnd Function

完成以上自定义函数之后就可以在工作簿内调用该函数了。

如此，在参照输入时，只需输入拼音简码，即可快速定位列表项目，比简单的列表要高效得很多。

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

创作不易，转载请注明出处！

[46abc39463f3c47ce81b36f39958d032.png]: /images/20221119/79d8f3b8063848d186d1d8d993ed3663.png
[2327c791f5e4805ef0424985a59a78aa.png]: /images/20221119/c5e97c055143425987f2ebe54ffbd7f1.png
[9eb2f8186722a2f11dff98f01cf8a22a.png]: /images/20221119/90dafb6ab0f949238d074d6f86de5da8.png
[8e48efb91d5dcc4997840c96891fa1da.png]: /images/20221119/e034c0b140e1460e86db31327f6e0cae.png
[e614f4398ca7d6ee8c8deec7dde026a8.png]: /images/20221119/a9d445ac5d7143239c1f6bc4026af319.png
[ae6a78decb9c4a4e050c8b17f43b32b4.png]: /images/20221119/d10438c6d7514e2197039bc421dbf090.png
[b136dcc988a370fc3ddeb7d6fa92f43c.png]: /images/20221119/675182dcaccf43efbadad3f4f35ff89f.png