Question

excel 中在一个单元格中用拼音简码显示另一个单元格姓名

Answer 1

用自定义函数：按Alt+F11，将以下的代码粘贴到模块窗口中，然后关闭VBE返回到excel。假设姓名拆拿在A1单元格中，则可在B1中写入公式：=py(a1) 代码为： Public Function PY(TT As String) As Variant '自定义函数，目的：把一组汉字变为一组汉字拼音的第一个字母。
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

Public 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.WorksheetFunction.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 顺便说一下，这段代码是从网上搜到的，我试过了，挺好用的。http://zhidao.baidu.com/question/159308933.html

Answer 2

要先把这些拼码和姓名都写在一个表上作为查询表然后用vlookup