在excel中实现汉字转换拼音全拼

在excel 2003和2007中均已经验证，可用。代码为网上收集，原作者已不可考。

Option ExplicitOption Compare TextPublic 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 StringDim i As Integer, j As IntegerarrWord(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"arrWord(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 + STRNextHZQP = 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)