Excel中利用VBS自界说函数将中文转为拼音首字母
1、在“开发工具”中，点击“Visual Basic”。如果没有“开发工具”，则添加。https://i-blog.csdnimg.cn/direct/31df457b96b242d0988074e96aa1c7c6.png
https://i-blog.csdnimg.cn/direct/5de86a5b668049cea3c96c0ec0906024.png
https://i-blog.csdnimg.cn/direct/af973205c08d4ea89acab7cec34f6022.png
2、添加“模块”，在窗口中添加自界说函数。
https://i-blog.csdnimg.cn/direct/d6903b60e26f45d8967480819bc1216b.png
Function MyGetPYChar(char)
MyCodeNumber = 65536 + Asc(char)
If (MyCodeNumber >= 45217 And MyCodeNumber <= 45252) Then
MyGetPYChar = “A”
ElseIf (MyCodeNumber >= 45253 And MyCodeNumber <= 45760) Then
MyGetPYChar = “B”
ElseIf (MyCodeNumber >= 45761 And MyCodeNumber <= 46317) Then
MyGetPYChar = “C”
ElseIf (MyCodeNumber >= 46318 And MyCodeNumber <= 46825) Then
MyGetPYChar = “D”
ElseIf (MyCodeNumber >= 46826 And MyCodeNumber <= 47009) Then
MyGetPYChar = “E”
ElseIf (MyCodeNumber >= 47010 And MyCodeNumber <= 47296) Then
MyGetPYChar = “F”
ElseIf (MyCodeNumber >= 47297 And MyCodeNumber <= 47613) Then
MyGetPYChar = “G”
ElseIf (MyCodeNumber >= 47614 And MyCodeNumber <= 48118) Then
MyGetPYChar = “H”
ElseIf (MyCodeNumber >= 48119 And MyCodeNumber <= 49061) Then
MyGetPYChar = “J”
ElseIf (MyCodeNumber >= 49062 And MyCodeNumber <= 49323) Then
MyGetPYChar = “K”
ElseIf (MyCodeNumber >= 49324 And MyCodeNumber <= 49895) Then
MyGetPYChar = “L”
ElseIf (MyCodeNumber >= 49896 And MyCodeNumber <= 50370) Then
MyGetPYChar = “M”
ElseIf (MyCodeNumber >= 50371 And MyCodeNumber <= 50613) Then
MyGetPYChar = “N”
ElseIf (MyCodeNumber >= 50614 And MyCodeNumber <= 50621) Then
MyGetPYChar = “O”
ElseIf (MyCodeNumber >= 50622 And MyCodeNumber <= 50905) Then
MyGetPYChar = “P”
ElseIf (MyCodeNumber >= 50906 And MyCodeNumber <= 51386) Then
MyGetPYChar = “Q”
ElseIf (MyCodeNumber >= 51387 And MyCodeNumber <= 51445) Then
MyGetPYChar = “R”
ElseIf (MyCodeNumber >= 51446 And MyCodeNumber <= 52217) Then
MyGetPYChar = “S”
ElseIf (MyCodeNumber >= 52218 And MyCodeNumber <= 52697) Then
MyGetPYChar = “T”
ElseIf (MyCodeNumber >= 52698 And MyCodeNumber <= 52979) Then
MyGetPYChar = “W”
ElseIf (MyCodeNumber >= 52980 And MyCodeNumber <= 53640) Then
MyGetPYChar = “X”
ElseIf (MyCodeNumber >= 53689 And MyCodeNumber <= 54480) Then
MyGetPYChar = “Y”
ElseIf (MyCodeNumber >= 54481 And MyCodeNumber <= 62289) Then
MyGetPYChar = “Z”
Else '如果不是中文，则不处置惩罚
MyGetPYChar = char
End If
End Function
Function MyGetPY(str)
For i = 1 To Len(str)
MyGetPY = MyGetPY & MyGetPYChar(Mid(str, i, 1))
Next i
End Function
3、引用自界说函数
https://i-blog.csdnimg.cn/direct/245a8cedc38a46bfa4e846068d9d3b38.png
https://i-blog.csdnimg.cn/direct/dfa8ee3db360420fa491b6f9acd6ca75.png
https://i-blog.csdnimg.cn/direct/a67c9ed6f755411ab79ffaf0243b306e.png
4、转换效果
https://i-blog.csdnimg.cn/direct/48b5e2ed80af407b91e95af3e47090e4.png
5、如果非本地Excel文件，大概受限，如下图处置惩罚
https://i-blog.csdnimg.cn/direct/6c5c81e3093b4f94bb7d38a1cf62d352.png
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]