|
在日常工作和生活中，我们经常需要录入一些常用名称，比如单位名称、公司名称、大学名称等，如何提高录入效率，并确保名称的规范统一呢？如果项目较少，可以在“数据有效性”中设置下拉列表来解决；如果项目较多，使用下拉列表就会影响录入的效率。本文以2016年普通高校招生本科第三批次投档线中的学校名称为例，介绍在Excel中如何建立特定词库并实现字母模糊检索，从而实现高效规范录入。
一、词库的获取和建立
从江苏教育考试院网站下载以下数据：
1.江苏省2016年普通高校招生本科第三批投档线（文科）.xls
2.江苏省2016年普通高校招生本科第三批投档线（理科）.xls
3.江苏省2016年普通高校招生本科第三批征求平行志愿投档线（文科）.xls
4.江苏省2016年普通高校招生本科第三批征求平行志愿投档线（理科）.xls
将以上4个表的数据合并到一个名称为“2016本三.xls”的表中，并插入第一列“科类”，分别标记为“文科”“理科”“文征”“理征”。
由于四个表中的大学名称有重复，因此下一步的工作是去重以确保唯一。这里可以借助“数据透视表”快速生成去重的大学名称列表。步骤为：选取“数据”菜单下的“数据透视表和数据透视图”，创建“数据透视表”到“新建工作表”中，再拖动表格中的“院校名称”从而选择单元格区域。插入一张空白工作表，改名为“大学名称”，将“数据透视表”中的大学名称复制到该表A列中，列标题A1单元格内容为“院校名称”，这样标准的大学名称词库建立完成。
二、为词库建立索引
这一步需要生成每一所大学名称的拼音首字母，可以自己编写一个VBA函数来实现。
在“工具”菜单中打开“宏”“Visual Basic编辑器”，插入模块，输入以下代码：
Option Explicit
Function PY(TT As String) As Variant '自定义函数，目的是把一组汉字变为一组汉字拼音的第一个字母。
Dim i%, temp$
PY = ""
For i = 1 To Len(TT)
temp = Asc(Mid$(TT, i, 1))
If temp > 255 Or temp < 0 Then '是汉字吗？
PY = PY & pinyin(Mid$(TT, i, 1)) '转化为拼音首字母，
Else
PY = PY & LCase(Mid$(TT, i, 1)) '转化英文字母
End If
Next i
End Function
Function pinyin(myStr As String) As Variant '自定义函数，目的是把单个汉字变为拼音的第一个字母。
On Error Resume Next
myStr = StrConv(myStr, vbNarrow)
If Asc(myStr) > 0 Or Err.Number = 1004 Then pinyin = ""
pinyin=Application.Worksheet Function.VLookup(myStr, [{"吖","A";"八","B";"嚓","C";"咑",
"D";"挜","E";"发","F";"獓","G";
"铪","H";"夻","J";"咔","K";"垃",
"L";"嘸","M";"旀","N";"噢","O";
"妑","P";"七","Q";"囕","R";"仨",
"S";"他","T";"屲","W";"夕","X";
"丫","Y";"帀","Z"}], 2)
End Function
运行该模块时，一般情况下电脑会弹出宏安全性警告消息框，这时需要在“工具”菜单中打开“选项”“安全性”“宏安全性”，将“安全级”设为“低”。
设置“大学名称”工作表B1单元格内容为“拼音简称”，在B2单元格输入公式“=py(A2)”，双击填充柄填充到末行。至此，词库字母索引表建立完成。
三、词库的检索
检索步骤分为两步，第一步是标识出符合检索条件的名称，第二步是返回列表。
这里以E2单元格为检索框，在C列标识，在D列返回列表。
第一步，使用COUNTIF函数，检索列表中每一个元素是否以E1单元格中的内容开头。
设置“大学名称”工作表C1单元格内容为“标识”，在C2单元格输入公式“=COUNTIF(B2,E$2&"*")”，并填充到末行；
第二步，检索将列出标识为1的单元格所在行的大学名称。
设置“大学名称”工作表D1单元格内容为“大学列表”。
在D2单元格输入以下公式：
{=INDEX(A:A,SMALL(IF(C$2:C$10000=1,ROW($2:$10000),4^8),ROW(1:1)))&""}
这个是数组公式，要按Ctrl+Shift+Enter，并填充到末行。
四、模糊检索测试
设置“大学名称”工作表E1单元格内容为“检索”，在E2单元格分别输入“n”“nj”“njs”“njsf”“njsfdxz”均可检索出“南京师范大学中北学院”，效果分别如图，将其选中复制，在目标位置使用“编辑”菜单下的“选择性粘贴”“数值”，即可实现录入。
|
