ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器,一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 32639|回复: 64

[分享] 汉字转拼音首字母,自定义函数,多方案对比

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2018-6-30 14:47 | 显示全部楼层 |阅读模式
本帖最后由 vitrel 于 2018-6-30 16:09 编辑

用VBA编写快速录入、模糊查询类的代码时,经常都需要将汉字转换为拼音首字母。
可惜的是,Excel没有为我们提供现成的解决办法。
我能想到的其中一个原因就是,汉字有多音字的情况,这令微软也无所适从。


以下为大家提供五套将汉字转换为拼音首字母的方案,并提供代码汇总供大家比较,
每套方案,各有优缺点,大家可以根据自己的实际情况选用
最优方案是第四、第五套,分别在5、6楼



汉字首字母.rar

74.14 KB, 下载次数: 586

评分

8

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-6-30 14:48 | 显示全部楼层
本帖最后由 vitrel 于 2018-6-30 16:11 编辑

方案一(误码率极高,不推荐
  1. Function PyA$(hzpy$)
  2.   Dim hzstring As String, pystring As String
  3.   Dim hzpysum As Integer, hzi As Integer, hzpyhex As Integer
  4.   hzstring = Trim(hzpy)
  5.   hzpysum = Len(Trim(hzstring))
  6.   pystring = ""
  7.   For hzi = 1 To hzpysum
  8.     hzpyhex = "&H" + Hex(Asc(Mid(hzstring, hzi, 1)))
  9.     Select Case hzpyhex
  10.       Case &HB0A1 To &HB0C4: pystring = pystring + "A"
  11.       Case &HB0C5 To &HB2C0: pystring = pystring + "B"
  12.       Case &HB2C1 To &HB4ED: pystring = pystring + "C"
  13.       Case &HB4EE To &HB6E9: pystring = pystring + "D"
  14.       Case &HB6EA To &HB7A1: pystring = pystring + "E"
  15.       Case &HB7A2 To &HB8C0: pystring = pystring + "F"
  16.       Case &HB8C1 To &HB9FD: pystring = pystring + "G"
  17.       Case &HB9FE To &HBBF6: pystring = pystring + "H"
  18.       Case &HBBF7 To &HBFA5: pystring = pystring + "J"
  19.       Case &HBFA6 To &HC0AB: pystring = pystring + "K"
  20.       Case &HC0AC To &HC2E7: pystring = pystring + "L"
  21.       Case &HC2E8 To &HC4C2: pystring = pystring + "M"
  22.       Case &HC4C3 To &HC5B5: pystring = pystring + "N"
  23.       Case &HC5B6 To &HC5BD: pystring = pystring + "O"
  24.       Case &HC5BE To &HC6D9: pystring = pystring + "P"
  25.       Case &HC6DA To &HC8BA: pystring = pystring + "Q"
  26.       Case &HC8BB To &HC8F5: pystring = pystring + "R"
  27.       Case &HC8F6 To &HCBF9: pystring = pystring + "S"
  28.       Case &HCBFA To &HCDD9: pystring = pystring + "T"
  29.       Case &HEDC5: pystring = pystring + "T"
  30.       Case &HCDDA To &HCEF3: pystring = pystring + "W"
  31.       Case &HCEF4 To &HD1B8: pystring = pystring + "X"
  32.       Case &HD1B9 To &HD4D0: pystring = pystring + "Y"
  33.       Case &HD4D1 To &HD7F9: pystring = pystring + "Z"
  34.       Case Else
  35.         pystring = pystring + Mid(hzstring, hzi, 1)
  36.     End Select
  37.   Next
  38.   PyA = pystring
  39. End Function
复制代码



方案一的原理是用Asc()函数返回字符的ANSI代码,
再根据ANSI代码划分区间,以确定汉字的拼音首字母。
此类方案在使用时,大部分结果还是正确的,但遇到部分汉字,如“庵飚邸窦笃梵芙缑崮皓泓桦晖葭婕瑾泾璟焗徕岚崂璘蔺楠腩岐茜倩榕佘韬婷薇炆雯解鑫娅闫奕懿瑜昱钰媛芸翟喆梓昊……”,结果就出错了。
究其原因,就是ANSI代码在制定时,确实有大部分的汉字都是按拼音为排序依据的,
但后来又补充了相当一部分汉字,补充进去的汉字是不可能再插进原来的汉字之间的,只能给它们分配新的编号。因此单纯地以ANSI代码编号来划分拼音区间,必然导致误码极高。因此并不推荐使用。



TA的精华主题

TA的得分主题

 楼主| 发表于 2018-6-30 14:48 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 vitrel 于 2018-6-30 16:12 编辑

方案二(误码率还是高,不推荐):
  1. Function PyB(hanzi)
  2.     Dim i%
  3.     Dim tmp As Long
  4.     Dim char$, getpychar$, OK$
  5.     For i = 1 To Len(hanzi)
  6.         char = Mid(hanzi, i, 1)
  7.         tmp = 65536 + Asc(char)
  8.         If (tmp >= 45217 And tmp <= 45252) Then getpychar = "A"
  9.         If (tmp >= 45253 And tmp <= 45760) Then getpychar = "B"
  10.         If (tmp >= 45761 And tmp <= 46317) Then getpychar = "C"
  11.         If (tmp >= 46318 And tmp <= 46825) Then getpychar = "D"
  12.         If (tmp >= 46826 And tmp <= 47009) Then getpychar = "E"
  13.         If (tmp >= 47010 And tmp <= 47296) Then getpychar = "F"
  14.         If (tmp >= 47297 And tmp <= 47613) Then getpychar = "G"
  15.         If (tmp >= 47614 And tmp <= 48118) Then getpychar = "H"
  16.         If (tmp >= 48119 And tmp <= 49061) Then getpychar = "J"
  17.         If (tmp >= 49062 And tmp <= 49323) Then getpychar = "K"
  18.         If (tmp >= 49324 And tmp <= 49895) Then getpychar = "L"
  19.         If (tmp >= 49896 And tmp <= 50370) Then getpychar = "M"
  20.         If (tmp >= 50371 And tmp <= 50613) Then getpychar = "N"
  21.         If (tmp >= 50614 And tmp <= 50621) Then getpychar = "O"
  22.         If (tmp >= 50622 And tmp <= 50905) Then getpychar = "P"
  23.         If (tmp >= 50906 And tmp <= 51386) Then getpychar = "Q"
  24.         If (tmp >= 51387 And tmp <= 51445) Then getpychar = "R"
  25.         If (tmp >= 51446 And tmp <= 52217) Then getpychar = "S"
  26.         If (tmp >= 52218 And tmp <= 52697) Then getpychar = "T"
  27.         If (tmp >= 52698 And tmp <= 52979) Then getpychar = "W"
  28.         If (tmp >= 52980 And tmp <= 53688) Then getpychar = "X"
  29.         If (tmp >= 53689 And tmp <= 54480) Then getpychar = "Y"
  30.         If (tmp >= 54481 And tmp <= 62289) Then getpychar = "Z"
  31.         '以下是自行添加的例外字
  32.         If char = "庵" Then getpychar = "A"
  33.         If char = "飚" Then getpychar = "B"
  34.         If char = "邸" Then getpychar = "D"
  35.         If char = "窦" Then getpychar = "D"
  36.         If char = "笃" Then getpychar = "D"
  37.         If char = "梵" Then getpychar = "F"
  38.         If char = "芙" Then getpychar = "F"
  39.         If char = "缑" Then getpychar = "G"
  40.         If char = "崮" Then getpychar = "G"
  41.         If char = "皓" Then getpychar = "H"
  42.         If char = "泓" Then getpychar = "H"
  43.         If char = "桦" Then getpychar = "H"
  44.         '……此处省略
  45.         If char = "(" Then getpychar = "("
  46.         If char = ")" Then getpychar = ")"
  47.         OK = OK + getpychar
  48.     Next i
  49.     PyB = OK
  50. End Function
复制代码


方案二所用的原理及代码与方案一相同,只是代码中加入了修正部分,
将“能找到”的特殊字,如上面提到的“庵飚邸窦笃梵芙缑崮皓泓桦晖葭婕瑾泾璟焗徕岚崂璘蔺楠腩岐茜倩榕佘韬婷薇炆雯解鑫娅闫奕懿瑜昱钰媛芸翟喆梓昊”都加进代码,
因此,方案二的准确率确实比方案一有所提升。
问题是,所谓的“特殊字”有太多太多了,能全部都加进代码吗?
所以,方案二的误码率还是相当高的。


PS:可能是由于出现时间最早的原因,网上还有大量代码利用此原理,将汉字转换为拼音、拼音首字母。
直到今天,此类方案还经常在此论坛上出现。
我可以很负责地告诉大家,凡是根据ANSI代码编号来划分拼音区间的方案都会造成转换效果大量误码,绝不推荐大家再使用
也希望论坛的高手们,不要再将此法推荐给新手们。

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-6-30 14:49 | 显示全部楼层
本帖最后由 vitrel 于 2018-6-30 15:03 编辑

方案三(可以使用):
  1. Function PyC$(str$) '获取拼音首字母,适用于简繁体汉字和各语系的计算机
  2.     Dim spy$, n&, i&, s$
  3.     spy = Worksheets("PyC数据").[A1].Value    '保存Unicode中20902个汉字的拼音首字母,顺序一一对应
  4.     For i = 1 To Len(str)
  5.         s = Mid(str, i, 1)
  6.         n = AscW(s) '获取汉字的Unicode编码
  7.         If n < 128 And n > 0 Then
  8.             PyC = PyC & s   '非汉字,直接输出
  9.         Else
  10.             If n < 0 Then n = n + 65536
  11.             n = n - 19967   '汉字的Unicode编码是从19968开始的
  12.             PyC = PyC & Mid(spy, n, 1)
  13.         End If
  14.     Next
  15. End Function
复制代码

为了解决上述两套方案的误码率,有位高手想出了一个办法,
就是将“所有”汉字(共20902个)的拼音首字母按照汉字的Unicode编码顺序,一一记录在Excel文档中,
代码运行时便能准确地“查找”出汉字所对应的拼音首字母。
此方案代码看上去简单,但在工作表“PyC数据”的A1单元格里,存放了20902个拼单首字母。


优点:代码原理简单直白,只要作者在制作这20902个对应的拼音首字母时没有出错的话,
不考虑多音字的情况下,函数的结果应该是100%准确的。
缺点:工作簿多出了21K的内容。

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-6-30 14:49 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 vitrel 于 2018-6-30 16:24 编辑

方案四(推荐使用):
  1. Function Py$(ByVal rng$)
  2.     Dim i%, pyArr, str$, ch$
  3.     pyArr = [{"吖","A";"八","B";"攃","C";"咑","D";"妸","E";"发","F";"旮","G";"哈","H";"丌","J";"咔","K";"垃","L";"妈","M";"乸","N";"噢","O";"帊","P";"七","Q";"冄","R";"仨","S";"他","T";"屲","W";"夕","X";"丫","Y";"帀","Z"}]
  4.     str = Replace(Replace(rng, " ", ""), " ", "")          '去空格和Tab
  5.     For i = 1 To Len(str)
  6.         ch = Mid(str, i, 1)
  7.         If ch Like "[一-龥]" Then   '如果是汉字,进行转换
  8.             Py = Py & WorksheetFunction.Lookup(Mid(str, i, 1), pyArr)
  9.         Else
  10.             'Py = Py & UCase(ch)     '如果不是汉字,直接输出
  11.         End If
  12.     Next
  13. End Function
复制代码
或:
  1. '注意:本函数须配合声明中的“Option Compare Text”使用
  2. Function Pyy$(ByVal rng$)
  3.     Dim i%, k%, str$, ch$
  4.     str = Replace(Replace(rng, " ", ""), " ", "")          '去空格和Tab
  5.     For i = 1 To Len(str)
  6.         k = 1
  7.         ch = Mid(str, i, 1)
  8.         If ch Like "[一-龥]" Then       '如果是汉字,进行转换
  9.             Do Until Mid("八攃咑妸发旮哈丌丌咔垃妈乸噢帊七冄仨他屲屲屲夕丫帀咗", k, 1) > ch
  10.                 k = k + 1
  11.             Loop
  12.             Pyy = Pyy & Chr(64 + k)
  13.         Else
  14.             'Pyy = Pyy & UCase(ch)       '如果不是汉字,直接输出
  15.         End If
  16.     Next
  17. End Function
复制代码

介绍此方案原理前,我先举个例子。
Excel具有对单元格内容进行排序的功能(基本上用过Excel的人都用过此功能),此功能也适用于汉字,
而Excelc对汉字的排序的依据的顺序是:先按声母、再按韵母、再按声调、以上相同的再按笔划顺序等。
既然Excel有此功能,那么方案五的的思路就是,将“所有”汉字(共20902个)在Excel的单元格内进行一次排序,
然后找到每个拼音区域所对应的第1个汉字,如A区域的第1个汉字是“吖”,B区域的第1个汉字是“八”……
这23个(不是26个,因为I、U、V都没有对应汉字)汉字找准了以后,
那么只要你随便举例一个汉字,如“爱”,Excel排序时肯定会将它排在“吖”和“八”中间,那么就可以确定“爱”的拼音首字母是“A”。方案四中的两套代码,都是利用这个原理所编写,
关键在于找准这23个关键汉字吖八攃咑妸发旮哈丌咔垃妈乸噢帊七冄仨他屲夕丫帀咗,以准确划分拼音首字母区间
Excel的单元格排序算法是经过千锤百炼、优中选点出来的,速度跟准确率都是毋庸置疑的,因此利用此原理所编写的代码,准确率也是100%的(不考虑多音字)。

优点:代码简单,只有短短几行,移植容易;不考虑多音字的话结果100%准确。
缺点:适用于Excel2007~2016,不适用于Excel2003(Excel2003的单元格排序对汉字支持不好,算法有误差),WPS未验证过。


补充一下:网上也曾出现过类似的方案,但因为没有找准A~Z这23个区域所对应的第1个汉字,因此代码思路虽正确,但结果会有误码。而本方案两例子中的23个汉字“吖八攃咑妸发旮哈丌咔垃妈乸噢帊七冄仨他屲夕丫帀咗”,是我在多个版本的Excel中穷举所有汉字后再排序(Excel2007~2013的排序结果都一字不差)所得到的,如果您所用代码并非使用这23个关键字的话,就说明划分的区间不完全准确,函数如果就必然存在误码,请注意。


评分

10

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-6-30 14:50 | 显示全部楼层
本帖最后由 vitrel 于 2018-6-30 15:10 编辑

方案五(推荐使用):


论坛高手贡献出来的一套“汉字转拼音的完美解决方案”(http://club.excelhome.net/thread-229924-1-1.html)。
原理是从微软自家的微软拼音输入法中直接提取汉字所对应的拼音全码(可带声调或不带声调)或拼音首字母。


优点:经过高手的多次的完善,效果近乎完美,还支持多音字,是汉字转拼音全码的最优方案。
缺点:(鸡蛋里挑一下骨头),系统要预装微软拼音输入法(针对Win8.1、Win10要用特殊办法安装,高手的贴子上都有提到);代码还是蛮多的,如果只用来转拼音首字母的话,有点牛刀杀鸡的感觉。

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-6-30 15:02 | 显示全部楼层
我自己在用的
    On Error Resume Next
    Const hanzi = "啊芭擦搭蛾发噶哈击喀垃妈拿哦啪期然撒塌挖昔压匝ABCDEFGHJKLMNOPQRSTWXYZZ"
    Dim i As Long, j As Byte, temp As String
    For i = 1 To Len(R)
        For j = 1 To 24
            If Asc(Mid(R, i, 1)) >= Asc(Mid(hanzi, j, 1)) Then temp = Mid(hanzi, 23 + j, 1)
        Next j
    PinYin = PinYin & temp
    Next
    PinYin = UCase(PinYin)

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-6-30 15:29 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-6-30 15:44 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
yylucke 发表于 2018-6-30 15:02
我自己在用的
    On Error Resume Next
    Const hanzi = "啊芭擦搭蛾发噶哈击喀垃妈拿哦啪期然撒塌挖 ...



朋友,抱歉地跟您说句,
我拿您的代码跟我方案四的代码对比过运行结果,结果相差甚远,
您代码的运行结果不仅有很多汉字出不来结果,就算出来的结果也有极多是误码,
我提供文件您运行看看。

下图D列的我的函数的结果,而E列是您函数的结果:
结果对比.jpg

两函数运行结果对比.rar

233.44 KB, 下载次数: 117

TA的精华主题

TA的得分主题

发表于 2018-6-30 15:57 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 yylucke 于 2018-6-30 16:01 编辑
vitrel 发表于 2018-6-30 15:44
朋友,抱歉地跟您说句,
我拿您的代码跟我方案四的代码对比过运行结果,结果相差甚远,
您代码的运 ...

的确,我的那个是有很多问题。在我采用那个方案的时候就懒得研究,反正用于常见人名和地名的。
现在改用你推荐的方案4.
谢谢你的分享!
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-12-25 14:27 , Processed in 0.044121 second(s), 10 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表