[已解决]K列输入【=拼音简码(J6】就能达到G列的效果，求助！

80013025

K列输入【=拼音简码(J6】就能达到G列的效果
附件：：： 拼音简码Book1.rar (84.63 KB, 下载次数: 19)

2016-4-19 22:15 上传

点击文件名下载附件

最佳答案

月排行榜 / 总排行榜

领风飞翔

2016-4-20 08:52

拼音简码Book1.rar (84.41 KB, 下载次数: 24)

2016-4-20 08:51 上传

点击文件名下载附件

把函数后面两个参数省略默认值分别为“”和4。
Public Function 拼音简码(TXT As Variant, Optional Delimiter As String = "", Optional Tpy As Byte = 4) As String

跳转到最佳答案楼层

领风飞翔

拼音简码Book1.rar (84.41 KB, 下载次数: 24)

2016-4-20 08:51 上传

点击文件名下载附件

把函数后面两个参数省略默认值分别为“”和4。
Public Function 拼音简码(TXT As Variant, Optional Delimiter As String = "", Optional Tpy As Byte = 4) As String

铁观音621

领风飞翔 发表于 2016-4-20 08:52
把函数后面两个参数省略默认值分别为“”和4。
Public Function 拼音简码(TXT As Variant, Optional D ...

把下面的代码复制在一个VBE模块里面，然后在工作表里面像使用其他函数一样使用HZP（）函数就可以了 比如A1 是 中国人 则在B1输入公式： =HZP(a1) 就会显示结果 ZGR 还不明白的话 HI我

Option Explicit

Public Function py(mystr AsString) As String
    Dim i As Long
    i = Asc(mystr)
    Select Case i
        Case -20319 To -20284: py ="a"
        Case -20283 To -19776: py ="b"
        Case -19775 To -19219: py = "c"
        Case -19218 To -18711: py ="d"
        Case -18710 To -18527: py ="e"
        Case -18526 To -18240: py ="f"
        Case -18239 To -17923: py ="g"
        Case -17922 To -17418: py ="h"
        Case -17417 To -16475: py ="j"
        Case -16474 To -16213: py ="k"
        Case -16212 To -15641: py ="l"
        Case -15640 To -15166: py ="m"
        Case -15165 To -14923: py ="n"
        Case -14922 To -14915: py ="o"
        Case -14914 To -14631: py ="p"
        Case -14630 To -14150: py ="q"
        Case -14149 To -14091: py ="r"
        Case -14090 To -13319: py ="s"
        Case -13318 To -12839: py ="t"
        Case -12838 To -12557: py ="w"
        Case -12556 To -11848: py ="x"
        Case -11847 To -11056: py ="y"
        Case -11055 To -10247: py ="z"
        Case Else: py = mystr
    End Select
End Function
Function HZP(strn As String)
    Dim i As Integer
    For i = 1 To Len(strn)
        HZP = HZP & py(Mid(strn, i, 1))
    Next i
End Function

grf1973

1. Function py(汉字 As String) '汉字转拼首 例：在单元格中键入"=py(a2)"
   
2.      Dim hz As String
   
3.      hz = StrConv(汉字, vbNarrow)
   
4.      For i = 1 To Len(hz)
   
5.          If Asc(Mid(hz, i, 1)) = AscB(Mid(hz, i, 1)) Then
   
6.              py = py & UCase(Mid(hz, i, 1))
   
7.          Else
   
8.              py = py & Application.VLookup(Mid(hz, i, 1), [{"啊","A";"芭","B";"擦","C";"搭","D";"蛾","E";"发","F";"噶","G";"哈","H";"击","J";"喀","K";"垃","L";"妈","M";"拿","N";"哦","O";"啪","P";"期","Q";"然","R";"撒","S";"塌","T";"挖","W";"昔","X";"压","Y";"匝","Z"}], 2)
   
9.          End If
   
10.      Next i
    
11. End Function
    

复制代码

80013025

谢谢各位帮忙！