使用场景
希望把一段汉字,转换为拼音首字母,用于搜索等
实现方法
通过EXCEL的宏实现,打开EXCEL,文件→选项→自定义功能区→勾选开发工具 点击开发工具→Visual Basic →右键模块1,查询代码→把代码拷贝到右边保存 代码如下
Function pinyin( p As String ) As String
i = Asc( p)
Select Case i
Case - 20319 To - 20284 : pinyin = "a"
Case - 20283 To - 19776 : pinyin = "b"
Case - 19775 To - 19219 : pinyin = "c"
Case - 19218 To - 18711 : pinyin = "d"
Case - 18710 To - 18527 : pinyin = "e"
Case - 18526 To - 18240 : pinyin = "f"
Case - 18239 To - 17923 : pinyin = "g"
Case - 17922 To - 17418 : pinyin = "h"
Case - 17417 To - 16475 : pinyin = "j"
Case - 16474 To - 16213 : pinyin = "k"
Case - 16212 To - 15641 : pinyin = "l"
Case - 15640 To - 15166 : pinyin = "m"
Case - 15165 To - 14923 : pinyin = "n"
Case - 14922 To - 14915 : pinyin = "o"
Case - 14914 To - 14631 : pinyin = "p"
Case - 14630 To - 14150 : pinyin = "q"
Case - 14149 To - 14091 : pinyin = "r"
Case - 14090 To - 13319 : pinyin = "s"
Case - 13318 To - 12839 : pinyin = "t"
Case - 12838 To - 12557 : pinyin = "w"
Case - 12556 To - 11848 : pinyin = "x"
Case - 11847 To - 11056 : pinyin = "y"
Case - 11055 To - 2050 : pinyin = "z"
Case Else : pinyin = p
End Select
End Function
Function getpy( str )
For i = 1 To Len ( str )
getpy = getpy & pinyin( Mid ( str , i, 1 ) )
Next i
End Function
在excel中,把需要生成首字母的文本复制到A列中,B列输入公式,=getpy(A1),然后下拉即可批量生成首字母