Q：Excel技巧，通过VBA代码，使中文名称转拼音/五笔简码

第一步：打开 VBA 编辑器

1. 打开 Excel 文件（建议新建一个空白文件测试）
2. 按快捷键：Alt + F11
   → 会弹出一个黑色背景的窗口，叫做 VBA 编辑器

第二步：插入一个模块

在 VBA 编辑器中：

1. 左侧找到：
   VBAProject (你的文件名)
   → 展开它
   → 右键点击 “模块”（如果没有，右键项目 → 插入 → 模块）

2. 如果没有“模块”，操作如下：

   • 右键 VBAProject (你的文件名)
   • 选择 插入 → 模块

 

第三步：粘贴完整代码

复制下面这段 完整 VBA 代码

'========================================
' 中文转拼音/五笔简码（压缩版）2025年9月4日
' 函数：=PY(A1) → ZG，=WB(A1) → KH
'========================================
Option Explicit
Private g_pyDict As Object, g_wbDict As Object

Function PY(ByVal text As String) As String
    Dim result As String: result = "": Dim i As Long, ch As String
    If g_pyDict Is Nothing Then InitPYDict
    If g_pyDict Is Nothing Then PY = "ERR": Exit Function
    For i = 1 To Len(text): ch = Mid(text, i, 1)
        If g_pyDict.Exists(ch) Then result = result & g_pyDict(ch) Else
            Dim code As Long: code = AscW(ch): If code < 0 Then code = code + 65536
            If (code >= &H4E00 And code <= &H9FFF) Then result = result & "X" Else result = result & UCase(ch)
        End If: Next i: PY = result
End Function

Function WB(ByVal text As String) As String
    If g_wbDict Is Nothing Then InitWBDict
    If g_wbDict Is Nothing Then WB = "ERR": Exit Function
    Dim i As Long, ch As String, code As String, result As String: result = ""
    For i = 1 To Len(text): ch = Mid(text, i, 1)
        If g_wbDict.Exists(ch) Then code = UCase(g_wbDict(ch)): result = result & Left(code & "  ", 2) Else result = result & "  "
    Next i: WB = result
End Function

Private Sub InitPYDict()
    On Error Resume Next: Set g_pyDict = CreateObject("Scripting.Dictionary")
    If g_pyDict Is Nothing Then Exit Sub
    With g_pyDict: .Add "中", "Z": .Add "国", "G": .Add "人", "R": .Add "大", "D": .Add "小", "X"
    .Add "明", "M": .Add "天", "T": .Add "是", "S": .Add "我", "W": .Add "你", "N": .Add "他", "T"
    .Add "她", "S": .Add "们", "M": .Add "的", "D": .Add "了", "L": .Add "一", "Y": .Add "二", "E"
    .Add "三", "S": .Add "四", "S": .Add "五", "W": .Add "六", "L": .Add "七", "Q": .Add "八", "B"
    .Add "九", "J": .Add "十", "S": .Add "上", "S": .Add "下", "X": .Add "不", "B": .Add "在", "Z"
    .Add "有", "Y": .Add "无", "W": .Add "为", "W": .Add "以", "Y": .Add "可", "K": .Add "好", "H"
    .Add "学", "X": .Add "习", "X": .Add "办", "B": .Add "公", "G": .Add "室", "S": .Add "心", "X"
    .Add "电", "D": .Add "脑", "N": .Add "技", "J": .Add "术", "S": .Add "开", "K": .Add "发", "F"
    .Add "程", "C": .Add "序", "X": .Add "软", "R": .Add "件", "J": .Add "系", "X": .Add "统", "T"
    .Add "数", "S": .Add "据", "J": .Add "文", "W": .Add "名", "M": .Add "称", "C": End With
End Sub

Private Sub InitWBDict()
    On Error Resume Next: Set g_wbDict = CreateObject("Scripting.Dictionary")
    If g_wbDict Is Nothing Then Exit Sub
    With g_wbDict: .Add "中", "kh": .Add "国", "lgyi": .Add "人", "ww": .Add "大", "dddd": .Add "小", "ih"
    .Add "明", "je": .Add "天", "gd": .Add "是", "j": .Add "我", "tr": .Add "你", "wq": .Add "他", "wng"
    .Add "她", "vng": .Add "们", "wuhn": .Add "的", "mq": .Add "了", "bn": .Add "一", "g": .Add "二", "fg"
    .Add "三", "dg": .Add "四", "lh": .Add "五", "ar": .Add "六", "uy": .Add "七", "ag": .Add "八", "wt"
    .Add "九", "vt": .Add "十", "fh": .Add "上", "h": .Add "下", "gh": .Add "不", "gi": .Add "在", "dh"
    .Add "有", "e": .Add "无", "fq": .Add "为", "yh": .Add "以", "c": .Add "可", "sk": .Add "好", "vb"
    .Add "学", "ipb": .Add "习", "nu": .Add "办", "lw": .Add "公", "wc": .Add "室", "pgk": .Add "心", "nu"
    .Add "电", "jn": .Add "脑", "ey": .Add "技", "rj": .Add "术", "ryc": .Add "开", "ga": .Add "发", "na"
    .Add "软", "ld": .Add "件", "th": .Add "系", "txu": .Add "统", "ux": End With
End Sub

第四步：保存为启用宏的文件

1. 回到 Excel
2. 点击 文件 → 另存为
3. 选择保存类型：
   Excel 启用宏的工作簿 (*.xlsm)
4. 文件名例如：拼音五笔转换.xlsm
5. 点“保存”

第五步：启用宏并测试

1. 关闭 Excel

2. 重新打开 刚才保存的 .xlsm 文件

3. 如果出现安全警告：
   → 点击 “启用内容”（非常重要！）

4. 在 Excel 中测试：

A1	B1（输入公式）	预期结果
中国	=PY(A1)	ZG
中国	=WB(A1)	KH
人工智能	=PY(A1)	RZ
人工智能	=WB(A1)	WWJE