EXCEL 中文姓名转换姓名全拼音字母的Excel宏

打开Excel->工具->宏->Viaual Basic编辑器
在弹出来的窗口中对着VBAproject点右键->插入->模块
下面会出现一个名为"模块1"，点击
在右边的空白栏中粘贴以下内容：



Option Explicit
Option Compare Text

Public Function HZQP(expression As String, Optional delimiter As String = " ", Optional limit As Integer = -1) As String

'expression
' 必需的?包含要转换的字符串?
'delimiter
' 可选的。用于标识分隔拼音的字符串字符。如果忽略，则使用空格字符(" ")作为分隔符。如果delimiter是一个长度为零的字符串，则返回仅包含一个元素，即完整的 expression字符串。
'limit
' 可选的。要返回拼音字符数，–1表示返回所有的拼音字符数，1表示返回拼音首字母。


Dim STR As String, arrWord(400) As String
Dim i As Integer, j As Integer
arrWord(1) = "吖a"
arrWord(2) = "哎ai"
arrWord(3) = "腤an"
arrWord(4) = "肮ang"
arrWord(5) = "凹ao"
arrWord(6) = "八ba"
arrWord(7) = "挀bai"
arrWord(8) = "扳ban"
arrWord(9) = "邦bang"
arrWord(10) = "勹bao"
arrWord(11) = "陂bei"
arrWord(12) = "奔ben"
arrWord(13) = "崩beng"
arrWord(14) = "皀bi"
arrWord(15) = "边bian"
arrWord(16) = "杓biao"
arrWord(17) = "憋bie"
arrWord(18) = "邠bin"
arrWord(19) = "仌bing"
arrWord(20) = "拨bo"
arrWord(21) = "峬bu"
arrWord(22) = "嚓ca"
arrWord(23) = "猜cai"
arrWord(24) = "飡can"
arrWord(25) = "仓cang"
arrWord(26) = "撡cao"
arrWord(27) = "冊ce"
arrWord(28) = "嵾cen"
arrWord(29) = "噌ceng"
arrWord(30) = "*cha"
arrWord(31) = "拆chai"
arrWord(32) = "辿chan"
arrWord(33) = "伥chang"
arrWord(34) = "抄chao"
arrWord(35) = "车che"
arrWord(36) = "抻chen"
arrWord(37) = "阷cheng"
arrWord(38) = "吃chi"
arrWord(39) = "充chong"
arrWord(40) = "抽chou"
arrWord(41) = "出chu"
arrWord(42) = "搋chuai"
arrWord(43) = "巛chuan"
arrWord(44) = "刅chuang"
arrWord(45) = "吹chui"
arrWord(46) = "旾chun"
arrWord(47) = "踔chuo"
arrWord(48) = "呲ci"
arrWord(49) = "从cong"
arrWord(50) = "凑cou"
arrWord(51) = "粗cu"
arrWord(52) = "汆cuan"
arrWord(53) = "崔cui"
arrWord(54) = "邨cun"
arrWord(55) = "搓cuo"
arrWord(56) = "咑da"
arrWord(57) = "呆dai"
arrWord(58) = "丹dan"
arrWord(59) = "当dang"
arrWord(60) = "刀dao"
arrWord(61) = "恴de"
arrWord(62) = "灯deng"
arrWord(63) = "仾di"
arrWord(64) = "敁dian"
arrWord(65) = "刁diao"
arrWord(66) = "爹die"
arrWord(67) = "丁ding"
arrWord(68) = "丟diu"
arrWord(69) = "东dong"
arrWord(70) = "剅dou"
arrWord(71) = "嘟du"
arrWord(72) = "耑duan"
arrWord(73) = "垖dui"
arrWord(74) = "吨dun"
arrWord(75) = "多duo"
arrWord(76) = "妸e"
arrWord(77) = "奀en"
arrWord(78) = "儿er"
arrWord(79) = "发fa"
arrWord(80) = "帆fan"
arrWord(81) = "方fang"
arrWord(82) = "飞fei"
arrWord(83) = "分fen"
arrWord(84) = "丰feng"
arrWord(85) = "仏fo"
arrWord(86) = "紑fou"
arrWord(87) = "夫fu"
arrWor

d(88) = "旮ga"
arrWord(89) = "该gai"
arrWord(90) = "干gan"
arrWord(91) = "冈gang"
arrWord(92) = "皋gao"
arrWord(93) = "戈ge"
arrWord(94) = "给gei"
arrWord(95) = "根gen"
arrWord(96) = "更geng"
arrWord(97) = "工gong"
arrWord(98) = "勾gou"
arrWord(99) = "估gu"
arrWord(100) = "瓜gua"
arrWord(101) = "乖guai"
arrWord(102) = "关guan"
arrWord(103) = "光guang"
arrWord(104) = "归gui"
arrWord(105) = "衮gun"
arrWord(106) = "呙guo"
arrWord(107) = "铪ha"
arrWord(108) = "嗨hai"
arrWord(109) = "佄han"
arrWord(110) = "夯hang"
arrWord(111) = "蒿hao"
arrWord(112) = "诃he"
arrWord(113) = "黒hei"
arrWord(114) = "拫hen"
arrWord(115) = "亨heng"
arrWord(116) = "叿hong"
arrWord(117) = "侯hou"
arrWord(118) = "乎hu"
arrWord(119) = "花hua"
arrWord(120) = "怀huai"
arrWord(121) = "欢huan"
arrWord(122) = "巟huang"
arrWord(123) = "灰hui"
arrWord(124) = "昏hun"
arrWord(125) = "吙huo"
arrWord(126) = "丌ji"
arrWord(127) = "加jia"
arrWord(128) = "戋jian"
arrWord(129) = "江jiang"
arrWord(130) = "艽jiao"
arrWord(131) = "阶jie"
arrWord(132) = "巾jin"
arrWord(133) = "坕jing"
arrWord(134) = "冂jiong"
arrWord(135) = "丩jiu"
arrWord(136) = "凥ju"
arrWord(137) = "姢juan"
arrWord(138) = "噘jue"
arrWord(139) = "军jun"
arrWord(140) = "咔ka"
arrWord(141) = "开kai"
arrWord(142) = "刊kan"
arrWord(143) = "忼kang"
arrWord(144) = "*kao"
arrWord(145) = "匼ke"
arrWord(146) = "肎ken"
arrWord(147) = "劥keng"
arrWord(148) = "空kong"
arrWord(149) = "抠kou"
arrWord(150) = "郀ku"
arrWord(151) = "夸kua"
arrWord(152) = "蒯kuai"
arrWord(153) = "宽kuan"
arrWord(154) = "匡kuang"
arrWord(155) = "亏kui"
arrWord(156) = "坤kun"
arrWord(157) = "扩kuo"
arrWord(158) = "垃la"
arrWord(159) = "来lai"
arrWord(160) = "兰lan"
arrWord(161) = "啷lang"
arrWord(162) = "捞lao"
arrWord(163) = "仂le"
arrWord(164) = "雷lei"
arrWord(165) = "塄leng"
arrWord(166) = "唎li"
arrWord(167) = "俩lia"
arrWord(168) = "嫾lian"
arrWord(169) = "簗liang"
arrWord(170) = "蹽liao"
arrWord(171) = "咧lie"
arrWord(172) = "邻lin"
arrWord(173) = "伶ling"
arrWord(174) = "溜liu"
arrWord(175) = "囖lo"
arrWord(176) = "龙long"
arrWord(177) = "娄lou"
arrWord(178) = "露lu"
arrWord(179) = "驴lv"
arrWord(180) = "孪luan"
arrWord(181) = "掠lue"
arrWord(182) = "抡lun"
arrWord(183) = "頱luo"
arrWord(184) = "妈ma"
arrWord(185) = "埋mai"
arrWord(186) = "颟man"
arrWord(187) = "牤mang"
arrWord(188) = "猫mao"
arrWord(189) = "庅me"
arrWord(190) = "沒mei"
arrWord(191) = "椚men"
arrWord(192) = "掹meng"
arrWord(193) = "咪mi"
arrWord(194) = "芇mian"
arrWord(195) = "喵miao"
arrWord(196) = "乜mie"
arrWord(197) = "民min"
arrWord(198) = "名ming"
arrWord(199) = "谬miu"
arrWord(200) = "摸mo"
arrWord(201) = "哞mou"
arrWord(202) = "母mu"
arrWord(203) = "拏na"
arrWord(204) = "腉nai"
arrWord(205) = "囡nan"
arrWord(206) = "囔nang"
arrWord(207) = "孬nao"

arrWord(208) = "讷ne"
arrWord(209) = "馁nei"
arrWord(210) = "嫩nen"
arrWord(211) = "能neng"
arrWord(212) = "妮ni"
arrWord(213) = "拈nian"
arrWord(214) = "娘niang"
arrWord(215) = "鸟niao"
arrWord(216) = "捏nie"
arrWord(217) = "脌nin"
arrWord(218) = "宁ning"
arrWord(219) = "妞niu"
arrWord(220) = "农nong"
arrWord(221) = "奴nu"
arrWord(222) = "女nv"
arrWord(223) = "疟nue"
arrWord(224) = "奻nuan"
arrWord(225) = "郍nuo"
arrWord(226) = "噢o"
arrWord(227) = "讴ou"
arrWord(228) = "妑pa"
arrWord(229) = "拍pai"
arrWord(230) = "眅pan"
arrWord(231) = "乓pang"
arrWord(232) = "抛pao"
arrWord(233) = "呸pei"
arrWord(234) = "喷pen"
arrWord(235) = "匉peng"
arrWord(236) = "丕pi"
arrWord(237) = "片pian"
arrWord(238) = "剽piao"
arrWord(239) = "氕pie"
arrWord(240) = "姘pin"
arrWord(241) = "娉ping"
arrWord(242) = "钋po"
arrWord(243) = "仆pu"
arrWord(244) = "七qi"
arrWord(245) = "掐qia"
arrWord(246) = "千qian"
arrWord(247) = "呛qiang"
arrWord(248) = "悄qiao"
arrWord(249) = "切qie"
arrWord(250) = "亲qin"
arrWord(251) = "靑qing"
arrWord(252) = "宆qiong"
arrWord(253) = "丘qiu"
arrWord(254) = "区qu"
arrWord(255) = "峑quan"
arrWord(256) = "炔que"
arrWord(257) = "夋qun"
arrWord(258) = "呥ran"
arrWord(259) = "穣rang"
arrWord(260) = "荛rao"
arrWord(261) = "惹re"
arrWord(262) = "人ren"
arrWord(263) = "扔reng"
arrWord(264) = "日ri"
arrWord(265) = "戎rong"
arrWord(266) = "厹rou"
arrWord(267) = "嶿ru"
arrWord(268) = "堧ruan"
arrWord(269) = "桵rui"
arrWord(270) = "闰run"
arrWord(271) = "叒ruo"
arrWord(272) = "仨sa"
arrWord(273) = "毢sai"
arrWord(274) = "三san"
arrWord(275) = "桒sang"
arrWord(276) = "掻sao"
arrWord(277) = "色se"
arrWord(278) = "森sen"
arrWord(279) = "僧seng"
arrWord(280) = "杀sha"
arrWord(281) = "筛shai"
arrWord(282) = "山shan"
arrWord(283) = "伤shang"
arrWord(284) = "弰shao"
arrWord(285) = "奢she"
arrWord(286) = "申shen"
arrWord(287) = "升sheng"
arrWord(288) = "尸shi"
arrWord(289) = "収shou"
arrWord(290) = "书shu"
arrWord(291) = "刷shua"
arrWord(292) = "衰shuai"
arrWord(293) = "闩shuan"
arrWord(294) = "霜shuang"
arrWord(295) = "谁shui"
arrWord(296) = "吮shun"
arrWord(297) = "说shuo"
arrWord(298) = "厶si"
arrWord(299) = "忪song"
arrWord(300) = "凁sou"
arrWord(301) = "苏su"
arrWord(302) = "狻suan"
arrWord(303) = "夊sui"
arrWord(304) = "孙sun"
arrWord(305) = "唆suo"
arrWord(306) = "他ta"
arrWord(307) = "囼tai"
arrWord(308) = "坍tan"
arrWord(309) = "汤tang"
arrWord(310) = "仐tao"
arrWord(311) = "忑te"
arrWord(312) = "鼟teng"
arrWord(313) = "剔ti"
arrWord(314) = "天tian"
arrWord(315) = "旫tiao"
arrWord(316) = "贴tie"
arrWord(317) = "厅ting"
arrWord(318) = "囲tong"
arrWord(319) = "偷tou"
arrWord(320) = "凸tu"
arrWord(321) = "湍tuan"
arrWord(322) = "推tui"
arrWord(323) = "吞tun"
arrWord(324) = "乇tuo"
arrWord(325) = "屲wa"
arrWord(326) = "

歪wai"
arrWord(327) = "弯wan"
arrWord(328) = "尩wang"
arrWord(329) = "危wei"
arrWord(330) = "昷wen"
arrWord(331) = "翁weng"
arrWord(332) = "挝wo"
arrWord(333) = "乌wu"
arrWord(334) = "夕xi"
arrWord(335) = "呷xia"
arrWord(336) = "仙xian"
arrWord(337) = "乡xiang"
arrWord(338) = "灱xiao"
arrWord(339) = "些xie"
arrWord(340) = "心xin"
arrWord(341) = "兴xing"
arrWord(342) = "凶xiong"
arrWord(343) = "休xiu"
arrWord(344) = "戌xu"
arrWord(345) = "吅xuan"
arrWord(346) = "疶xue"
arrWord(347) = "坃xun"
arrWord(348) = "丫ya"
arrWord(349) = "咽yan"
arrWord(350) = "殃yang"
arrWord(351) = "邀yao"
arrWord(352) = "椰ye"
arrWord(353) = "一yi"
arrWord(354) = "乚yin"
arrWord(355) = "应ying"
arrWord(356) = "哟yo"
arrWord(357) = "佣yong"
arrWord(358) = "优you"
arrWord(359) = "扜yu"
arrWord(360) = "囦yuan"
arrWord(361) = "曰yue"
arrWord(362) = "蒀yun"
arrWord(363) = "帀za"
arrWord(364) = "災zai"
arrWord(365) = "兂zan"
arrWord(366) = "牂zang"
arrWord(367) = "傮zao"
arrWord(368) = "啫ze"
arrWord(369) = "贼zei"
arrWord(370) = "怎zen"
arrWord(371) = "曽zeng"
arrWord(372) = "吒zha"
arrWord(373) = "夈zhai"
arrWord(374) = "沾zhan"
arrWord(375) = "张zhang"
arrWord(376) = "佋zhao"
arrWord(377) = "蜇zhe"
arrWord(378) = "贞zhen"
arrWord(379) = "凧zheng"
arrWord(380) = "之zhi"
arrWord(381) = "中zhong"
arrWord(382) = "州zhou"
arrWord(383) = "朱zhu"
arrWord(384) = "抓zhua"
arrWord(385) = "拽zhuai"
arrWord(386) = "专zhuan"
arrWord(387) = "妆zhuang"
arrWord(388) = "隹zhui"
arrWord(389) = "宒zhun"
arrWord(390) = "卓zhuo"
arrWord(391) = "仔zi"
arrWord(392) = "宗zong"
arrWord(393) = "邹zou"
arrWord(394) = "租zu"
arrWord(395) = "劗zuan"
arrWord(396) = "厜zui"
arrWord(397) = "尊zun"
arrWord(398) = "昨zuo"
arrWord(399) = "咗zuo"
For i = 1 To Len(expression)
STR = Mid(expression, i, 1)
If (Asc(STR) < 0) Then
For j = 1 To 399
If Left(arrWord(j), 1) > STR Then
If limit = -1 Then
STR = Mid(arrWord(j - 1), 2) & delimiter
Else
STR = Mid(arrWord(j - 1), 2, limit) & delimiter
End If
Exit For
End If
Next
End If
HZQP = HZQP + STR
Next
HZQP = Left(HZQP, Len(HZQP) - Len(delimiter))
End Function

在你的excel中创建宏，输入以下内容，然后在excel中使用自定义函数=hsqp(字符表达式，"",开关参数)

三种用法: =hzqp(A2,"",-1) =hzqp(A2,"-",-1) =hzqp(A2," ",-1)

单词 公式 拼音 公式
我们 women women wo-men wo men
=hzqp(A2,"",-1) =hzqp(A2,"-",-1) =hzqp(A2," ",-1)


三种用法: =hzqp(A2,"",-1) =hzqp(A2,"-",-1) =hzqp(A2," ",-1)