在excel中使用宏和公式把汉字转换成拼音

本文将深入探讨宏和公式在Excel中的巧妙运用，掌握后能得心应手。

1、 打开Excel表格，输入相关数据；

2、 按“Alt+F11”组合键，进入Visual Basic编辑状态；?

3、 执行“插入→模块”命令，插入一个新模块。再双击插入的模块，进入模块代码编辑状态；?

4、 Function pinyin(p As String) As String?i = Asc(p)Select Case iCase -20319 To -20318: pinyin = "a "Case -20317 To -20305: pinyin = "ai "Case -20304 To -20296: pinyin = "an "Case -20295 To -20293: pinyin = "ang "Case -20292 To -20284: pinyin = "ao "Case -20283 To -20266: pinyin = "ba "Case -20265 To -20258: pinyin = "bai "Case -20257 To -20243: pinyin = "ban "Case -20242 To -20231: pinyin = "bang "Case -20230 To -20052: pinyin = "bao "Case -20051 To -20037: pinyin = "bei "Case -20036 To -20033: pinyin = "ben "Case -20032 To -20027: pinyin = "beng "Case -20026 To -20003: pinyin = "bi "Case -20002 To -19991: pinyin = "bian "Case -19990 To -19987: pinyin = "biao "Case -19986 To -19983: pinyin = "bie "Case -19982 To -19977: pinyin = "bin "Case -19976 To -19806: pinyin = "bing "Case -19805 To -19785: pinyin = "bo "Case -19784 To -19776: pinyin = "bu "Case -19775 To -19775: pinyin = "ca "Case -19774 To -19764: pinyin = "cai "Case -19763 To -19757: pinyin = "can "Case -19756 To -19752: pinyin = "cang "Case -19751 To -19747: pinyin = "cao "Case -19746 To -19742: pinyin = "ce "Case -19741 To -19740: pinyin = "ceng "Case -19739 To -19729: pinyin = "cha "Case -19728 To -19726: pinyin = "chai "Case -19725 To -19716: pinyin = "chan "Case -19715 To -19541: pinyin = "chang "Case -19540 To -19532: pinyin = "chao "Case -19531 To -19526: pinyin = "che "Case -11055 To -11053: pinyin = "za "Case -11052 To -11046: pinyin = "zai "Case -11045 To -11042: pinyin = "zan "Case -11041 To -11039: pinyin = "zang "Case -11038 To -11025: pinyin = "zao "Case -11024 To -11021: pinyin = "ze "Case -11020 To -11020: pinyin = "zei "Case -11019 To -11019: pinyin = "zen "Case -11018 To -11015: pinyin = "zeng "Case -11014 To -10839: pinyin = "zha "Case -10838 To -10833: pinyin = "zhai "Case -10832 To -10816: pinyin = "zhan "Case -10815 To -10801: pinyin = "zhang "Case -10800 To -10791: pinyin = "zhao "Case -10790 To -10781: pinyin = "zhe "Case -10780 To -10765: pinyin = "zhen "Case -10764 To -10588: pinyin = "zheng "Case -10587 To -10545: pinyin = "zhi "Case -10544 To -10534: pinyin = "zhong "Case -10533 To -10520: pinyin = "zhou "Case -10519 To -10332: pinyin = "zhu "Case -10331 To -10330: pinyin = "zhua "Case -10329 To -10329: pinyin = "zhuai "Case -10328 To -10323: pinyin = "zhuan "Case -10322 To -10316: pinyin = "zhuang "Case -10315 To -10310: pinyin = "zhui "Case -10309 To -10308: pinyin = "zhun "Case -10307 To -10297: pinyin = "zhuo "Case -10296 To -10282: pinyin = "zi "Case -10281 To -10275: pinyin = "zong "Case -10274 To -10271: pinyin = "zou "Case -10270 To -10263: pinyin = "zu "Case -10262 To -10261: pinyin = "zuan "Case -10260 To -10257: pinyin = "zui "Case -10256 To -10255: pinyin = "zun "Case -10254 To -10254: pinyin = "zuo "Case Else: pinyin = pEnd SelectEnd FunctionFunction getpy(str)For i = 1 To Len(str)getpy = getpy & pinyin(Mid(str, i, 1))Next iEnd Function

5、 关闭Visual Basic编辑窗口，返回Excel编辑状态;?

6、 选中转换后的拼音需要放在哪个列，例如放在D列的第2个单元格，输入公式：=getpy(A2);?

7、 去除拼音之间的空格。去掉空格的拼音放在C列，如果这个数据原来在B2单元格，去掉空格之后的拼音放在C2单元格，则在C2单元格输入： =SUBSTITUTE(B2," ","")?