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

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

Option ExplicitOption Compare TextPublic Function HZQP(expression As String, Optional delimiter As String &＃61; " ", Optional limit As Integer &＃61; -1) As String&＃39;expression&＃39;     必需的?包含要转换的字符串?&＃39;delimiter&＃39;     可选的。用于标识分隔拼音的字符串字符。如果忽略&＃xff0c;则使用空格字符(" ")作为分隔符。如果delimiter是一个长度为零的字符串&＃xff0c;则返回仅包含一个元素&＃xff0c;即完整的 expression字符串。&＃39;limit&＃39;     可选的。要返回拼音字符数&＃xff0c;–1表示返回所有的拼音字符数&＃xff0c;1表示返回拼音首字母。Dim STR As String, arrWord(400) As StringDim i As Integer, j As IntegerarrWord(1) &＃61; "吖a"arrWord(2) &＃61; "哎ai"arrWord(3) &＃61; "腤an"arrWord(4) &＃61; "肮ang"arrWord(5) &＃61; "凹ao"arrWord(6) &＃61; "八ba"arrWord(7) &＃61; "挀bai"arrWord(8) &＃61; "扳ban"arrWord(9) &＃61; "邦bang"arrWord(10) &＃61; "勹bao"arrWord(11) &＃61; "陂bei"arrWord(12) &＃61; "奔ben"arrWord(13) &＃61; "崩beng"arrWord(14) &＃61; "皀bi"arrWord(15) &＃61; "边bian"arrWord(16) &＃61; "杓biao"arrWord(17) &＃61; "憋bie"arrWord(18) &＃61; "邠bin"arrWord(19) &＃61; "仌bing"arrWord(20) &＃61; "拨bo"arrWord(21) &＃61; "峬bu"arrWord(22) &＃61; "嚓ca"arrWord(23) &＃61; "猜cai"arrWord(24) &＃61; "飡can"arrWord(25) &＃61; "仓cang"arrWord(26) &＃61; "撡cao"arrWord(27) &＃61; "冊ce"arrWord(28) &＃61; "嵾cen"arrWord(29) &＃61; "噌ceng"arrWord(30) &＃61; "*cha"arrWord(31) &＃61; "拆chai"arrWord(32) &＃61; "辿chan"arrWord(33) &＃61; "伥chang"arrWord(34) &＃61; "抄chao"arrWord(35) &＃61; "车che"arrWord(36) &＃61; "抻chen"arrWord(37) &＃61; "阷cheng"arrWord(38) &＃61; "吃chi"arrWord(39) &＃61; "充chong"arrWord(40) &＃61; "抽chou"arrWord(41) &＃61; "出chu"arrWord(42) &＃61; "搋chuai"arrWord(43) &＃61; "巛chuan"arrWord(44) &＃61; "刅chuang"arrWord(45) &＃61; "吹chui"arrWord(46) &＃61; "旾chun"arrWord(47) &＃61; "踔chuo"arrWord(48) &＃61; "呲ci"arrWord(49) &＃61; "从cong"arrWord(50) &＃61; "凑cou"arrWord(51) &＃61; "粗cu"arrWord(52) &＃61; "汆cuan"arrWord(53) &＃61; "崔cui"arrWord(54) &＃61; "邨cun"arrWord(55) &＃61; "搓cuo"arrWord(56) &＃61; "咑da"arrWord(57) &＃61; "呆dai"arrWord(58) &＃61; "丹dan"arrWord(59) &＃61; "当dang"arrWord(60) &＃61; "刀dao"arrWord(61) &＃61; "恴de"arrWord(62) &＃61; "灯deng"arrWord(63) &＃61; "仾di"arrWord(64) &＃61; "敁dian"arrWord(65) &＃61; "刁diao"arrWord(66) &＃61; "爹die"arrWord(67) &＃61; "丁ding"arrWord(68) &＃61; "丟diu"arrWord(69) &＃61; "东dong"arrWord(70) &＃61; "剅dou"arrWord(71) &＃61; "嘟du"arrWord(72) &＃61; "耑duan"arrWord(73) &＃61; "垖dui"arrWord(74) &＃61; "吨dun"arrWord(75) &＃61; "多duo"arrWord(76) &＃61; "妸e"arrWord(77) &＃61; "奀en"arrWord(78) &＃61; "儿er"arrWord(79) &＃61; "发fa"arrWord(80) &＃61; "帆fan"arrWord(81) &＃61; "方fang"arrWord(82) &＃61; "飞fei"arrWord(83) &＃61; "分fen"arrWord(84) &＃61; "丰feng"arrWord(85) &＃61; "仏fo"arrWord(86) &＃61; "紑fou"arrWord(87) &＃61; "夫fu"arrWord(88) &＃61; "旮ga"arrWord(89) &＃61; "该gai"arrWord(90) &＃61; "干gan"arrWord(91) &＃61; "冈gang"arrWord(92) &＃61; "皋gao"arrWord(93) &＃61; "戈ge"arrWord(94) &＃61; "给gei"arrWord(95) &＃61; "根gen"arrWord(96) &＃61; "更geng"arrWord(97) &＃61; "工gong"arrWord(98) &＃61; "勾gou"arrWord(99) &＃61; "估gu"arrWord(100) &＃61; "瓜gua"arrWord(101) &＃61; "乖guai"arrWord(102) &＃61; "关guan"arrWord(103) &＃61; "光guang"arrWord(104) &＃61; "归gui"arrWord(105) &＃61; "衮gun"arrWord(106) &＃61; "呙guo"arrWord(107) &＃61; "铪ha"arrWord(108) &＃61; "嗨hai"arrWord(109) &＃61; "佄han"arrWord(110) &＃61; "夯hang"arrWord(111) &＃61; "蒿hao"arrWord(112) &＃61; "诃he"arrWord(113) &＃61; "黒hei"arrWord(114) &＃61; "拫hen"arrWord(115) &＃61; "亨heng"arrWord(116) &＃61; "叿hong"arrWord(117) &＃61; "侯hou"arrWord(118) &＃61; "乎hu"arrWord(119) &＃61; "花hua"arrWord(120) &＃61; "怀huai"arrWord(121) &＃61; "欢huan"arrWord(122) &＃61; "巟huang"arrWord(123) &＃61; "灰hui"arrWord(124) &＃61; "昏hun"arrWord(125) &＃61; "吙huo"arrWord(126) &＃61; "丌ji"arrWord(127) &＃61; "加jia"arrWord(128) &＃61; "戋jian"arrWord(129) &＃61; "江jiang"arrWord(130) &＃61; "艽jiao"arrWord(131) &＃61; "阶jie"arrWord(132) &＃61; "巾jin"arrWord(133) &＃61; "坕jing"arrWord(134) &＃61; "冂jiong"arrWord(135) &＃61; "丩jiu"arrWord(136) &＃61; "凥ju"arrWord(137) &＃61; "姢juan"arrWord(138) &＃61; "噘jue"arrWord(139) &＃61; "军jun"arrWord(140) &＃61; "咔ka"arrWord(141) &＃61; "开kai"arrWord(142) &＃61; "刊kan"arrWord(143) &＃61; "忼kang"arrWord(144) &＃61; "*kao"arrWord(145) &＃61; "匼ke"arrWord(146) &＃61; "肎ken"arrWord(147) &＃61; "劥keng"arrWord(148) &＃61; "空kong"arrWord(149) &＃61; "抠kou"arrWord(150) &＃61; "郀ku"arrWord(151) &＃61; "夸kua"arrWord(152) &＃61; "蒯kuai"arrWord(153) &＃61; "宽kuan"arrWord(154) &＃61; "匡kuang"arrWord(155) &＃61; "亏kui"arrWord(156) &＃61; "坤kun"arrWord(157) &＃61; "扩kuo"arrWord(158) &＃61; "垃la"arrWord(159) &＃61; "来lai"arrWord(160) &＃61; "兰lan"arrWord(161) &＃61; "啷lang"arrWord(162) &＃61; "捞lao"arrWord(163) &＃61; "仂le"arrWord(164) &＃61; "雷lei"arrWord(165) &＃61; "塄leng"arrWord(166) &＃61; "唎li"arrWord(167) &＃61; "俩lia"arrWord(168) &＃61; "嫾lian"arrWord(169) &＃61; "簗liang"arrWord(170) &＃61; "蹽liao"arrWord(171) &＃61; "咧lie"arrWord(172) &＃61; "邻lin"arrWord(173) &＃61; "伶ling"arrWord(174) &＃61; "溜liu"arrWord(175) &＃61; "囖lo"arrWord(176) &＃61; "龙long"arrWord(177) &＃61; "娄lou"arrWord(178) &＃61; "露lu"arrWord(179) &＃61; "驴lv"arrWord(180) &＃61; "孪luan"arrWord(181) &＃61; "掠lue"arrWord(182) &＃61; "抡lun"arrWord(183) &＃61; "頱luo"arrWord(184) &＃61; "妈ma"arrWord(185) &＃61; "埋mai"arrWord(186) &＃61; "颟man"arrWord(187) &＃61; "牤mang"arrWord(188) &＃61; "猫mao"arrWord(189) &＃61; "庅me"arrWord(190) &＃61; "沒mei"arrWord(191) &＃61; "椚men"arrWord(192) &＃61; "掹meng"arrWord(193) &＃61; "咪mi"arrWord(194) &＃61; "芇mian"arrWord(195) &＃61; "喵miao"arrWord(196) &＃61; "乜mie"arrWord(197) &＃61; "民min"arrWord(198) &＃61; "名ming"arrWord(199) &＃61; "谬miu"arrWord(200) &＃61; "摸mo"arrWord(201) &＃61; "哞mou"arrWord(202) &＃61; "母mu"arrWord(203) &＃61; "拏na"arrWord(204) &＃61; "腉nai"arrWord(205) &＃61; "囡nan"arrWord(206) &＃61; "囔nang"arrWord(207) &＃61; "孬nao"arrWord(208) &＃61; "讷ne"arrWord(209) &＃61; "馁nei"arrWord(210) &＃61; "嫩nen"arrWord(211) &＃61; "能neng"arrWord(212) &＃61; "妮ni"arrWord(213) &＃61; "拈nian"arrWord(214) &＃61; "娘niang"arrWord(215) &＃61; "鸟niao"arrWord(216) &＃61; "捏nie"arrWord(217) &＃61; "脌nin"arrWord(218) &＃61; "宁ning"arrWord(219) &＃61; "妞niu"arrWord(220) &＃61; "农nong"arrWord(221) &＃61; "奴nu"arrWord(222) &＃61; "女nv"arrWord(223) &＃61; "疟nue"arrWord(224) &＃61; "奻nuan"arrWord(225) &＃61; "郍nuo"arrWord(226) &＃61; "噢o"arrWord(227) &＃61; "讴ou"arrWord(228) &＃61; "妑pa"arrWord(229) &＃61; "拍pai"arrWord(230) &＃61; "眅pan"arrWord(231) &＃61; "乓pang"arrWord(232) &＃61; "抛pao"arrWord(233) &＃61; "呸pei"arrWord(234) &＃61; "喷pen"arrWord(235) &＃61; "匉peng"arrWord(236) &＃61; "丕pi"arrWord(237) &＃61; "片pian"arrWord(238) &＃61; "剽piao"arrWord(239) &＃61; "氕pie"arrWord(240) &＃61; "姘pin"arrWord(241) &＃61; "娉ping"arrWord(242) &＃61; "钋po"arrWord(243) &＃61; "仆pu"arrWord(244) &＃61; "七qi"arrWord(245) &＃61; "掐qia"arrWord(246) &＃61; "千qian"arrWord(247) &＃61; "呛qiang"arrWord(248) &＃61; "悄qiao"arrWord(249) &＃61; "切qie"arrWord(250) &＃61; "亲qin"arrWord(251) &＃61; "靑qing"arrWord(252) &＃61; "宆qiong"arrWord(253) &＃61; "丘qiu"arrWord(254) &＃61; "区qu"arrWord(255) &＃61; "峑quan"arrWord(256) &＃61; "炔que"arrWord(257) &＃61; "夋qun"arrWord(258) &＃61; "呥ran"arrWord(259) &＃61; "穣rang"arrWord(260) &＃61; "荛rao"arrWord(261) &＃61; "惹re"arrWord(262) &＃61; "人ren"arrWord(263) &＃61; "扔reng"arrWord(264) &＃61; "日ri"arrWord(265) &＃61; "戎rong"arrWord(266) &＃61; "厹rou"arrWord(267) &＃61; "嶿ru"arrWord(268) &＃61; "堧ruan"arrWord(269) &＃61; "桵rui"arrWord(270) &＃61; "闰run"arrWord(271) &＃61; "叒ruo"arrWord(272) &＃61; "仨sa"arrWord(273) &＃61; "毢sai"arrWord(274) &＃61; "三san"arrWord(275) &＃61; "桒sang"arrWord(276) &＃61; "掻sao"arrWord(277) &＃61; "色se"arrWord(278) &＃61; "森sen"arrWord(279) &＃61; "僧seng"arrWord(280) &＃61; "杀sha"arrWord(281) &＃61; "筛shai"arrWord(282) &＃61; "山shan"arrWord(283) &＃61; "伤shang"arrWord(284) &＃61; "弰shao"arrWord(285) &＃61; "奢she"arrWord(286) &＃61; "申shen"arrWord(287) &＃61; "升sheng"arrWord(288) &＃61; "尸shi"arrWord(289) &＃61; "収shou"arrWord(290) &＃61; "书shu"arrWord(291) &＃61; "刷shua"arrWord(292) &＃61; "衰shuai"arrWord(293) &＃61; "闩shuan"arrWord(294) &＃61; "霜shuang"arrWord(295) &＃61; "谁shui"arrWord(296) &＃61; "吮shun"arrWord(297) &＃61; "说shuo"arrWord(298) &＃61; "厶si"arrWord(299) &＃61; "忪song"arrWord(300) &＃61; "凁sou"arrWord(301) &＃61; "苏su"arrWord(302) &＃61; "狻suan"arrWord(303) &＃61; "夊sui"arrWord(304) &＃61; "孙sun"arrWord(305) &＃61; "唆suo"arrWord(306) &＃61; "他ta"arrWord(307) &＃61; "囼tai"arrWord(308) &＃61; "坍tan"arrWord(309) &＃61; "汤tang"arrWord(310) &＃61; "仐tao"arrWord(311) &＃61; "忑te"arrWord(312) &＃61; "鼟teng"arrWord(313) &＃61; "剔ti"arrWord(314) &＃61; "天tian"arrWord(315) &＃61; "旫tiao"arrWord(316) &＃61; "贴tie"arrWord(317) &＃61; "厅ting"arrWord(318) &＃61; "囲tong"arrWord(319) &＃61; "偷tou"arrWord(320) &＃61; "凸tu"arrWord(321) &＃61; "湍tuan"arrWord(322) &＃61; "推tui"arrWord(323) &＃61; "吞tun"arrWord(324) &＃61; "乇tuo"arrWord(325) &＃61; "屲wa"arrWord(326) &＃61; "歪wai"arrWord(327) &＃61; "弯wan"arrWord(328) &＃61; "尩wang"arrWord(329) &＃61; "危wei"arrWord(330) &＃61; "昷wen"arrWord(331) &＃61; "翁weng"arrWord(332) &＃61; "挝wo"arrWord(333) &＃61; "乌wu"arrWord(334) &＃61; "夕xi"arrWord(335) &＃61; "呷xia"arrWord(336) &＃61; "仙xian"arrWord(337) &＃61; "乡xiang"arrWord(338) &＃61; "灱xiao"arrWord(339) &＃61; "些xie"arrWord(340) &＃61; "心xin"arrWord(341) &＃61; "兴xing"arrWord(342) &＃61; "凶xiong"arrWord(343) &＃61; "休xiu"arrWord(344) &＃61; "戌xu"arrWord(345) &＃61; "吅xuan"arrWord(346) &＃61; "疶xue"arrWord(347) &＃61; "坃xun"arrWord(348) &＃61; "丫ya"arrWord(349) &＃61; "咽yan"arrWord(350) &＃61; "殃yang"arrWord(351) &＃61; "邀yao"arrWord(352) &＃61; "椰ye"arrWord(353) &＃61; "一yi"arrWord(354) &＃61; "乚yin"arrWord(355) &＃61; "应ying"arrWord(356) &＃61; "哟yo"arrWord(357) &＃61; "佣yong"arrWord(358) &＃61; "优you"arrWord(359) &＃61; "扜yu"arrWord(360) &＃61; "囦yuan"arrWord(361) &＃61; "曰yue"arrWord(362) &＃61; "蒀yun"arrWord(363) &＃61; "帀za"arrWord(364) &＃61; "災zai"arrWord(365) &＃61; "兂zan"arrWord(366) &＃61; "牂zang"arrWord(367) &＃61; "傮zao"arrWord(368) &＃61; "啫ze"arrWord(369) &＃61; "贼zei"arrWord(370) &＃61; "怎zen"arrWord(371) &＃61; "曽zeng"arrWord(372) &＃61; "吒zha"arrWord(373) &＃61; "夈zhai"arrWord(374) &＃61; "沾zhan"arrWord(375) &＃61; "张zhang"arrWord(376) &＃61; "佋zhao"arrWord(377) &＃61; "蜇zhe"arrWord(378) &＃61; "贞zhen"arrWord(379) &＃61; "凧zheng"arrWord(380) &＃61; "之zhi"arrWord(381) &＃61; "中zhong"arrWord(382) &＃61; "州zhou"arrWord(383) &＃61; "朱zhu"arrWord(384) &＃61; "抓zhua"arrWord(385) &＃61; "拽zhuai"arrWord(386) &＃61; "专zhuan"arrWord(387) &＃61; "妆zhuang"arrWord(388) &＃61; "隹zhui"arrWord(389) &＃61; "宒zhun"arrWord(390) &＃61; "卓zhuo"arrWord(391) &＃61; "仔zi"arrWord(392) &＃61; "宗zong"arrWord(393) &＃61; "邹zou"arrWord(394) &＃61; "租zu"arrWord(395) &＃61; "劗zuan"arrWord(396) &＃61; "厜zui"arrWord(397) &＃61; "尊zun"arrWord(398) &＃61; "昨zuo"arrWord(399) &＃61; "咗zuo"For i &＃61; 1 To Len(expression)     STR &＃61; Mid(expression, i, 1)     If (Asc(STR) <0) Then         For j &＃61; 1 To 399             If Left(arrWord(j), 1) > STR Then                 If limit &＃61; -1 Then                     STR &＃61; Mid(arrWord(j - 1), 2) & delimiter                 Else                     STR &＃61; Mid(arrWord(j - 1), 2, limit) & delimiter                 End If                 Exit For             End If         Next     End If    HZQP &＃61; HZQP &＃43; STRNextHZQP &＃61; Left(HZQP, Len(HZQP) - Len(delimiter))End Function&＃39;在你的excel中创建宏&＃xff0c;输入以下内容&＃xff0c;然后在excel中使用自定义函数&＃61;hsqp(字符表达式&＃xff0c;"",开关参数)&＃39;三种用法:    &＃61;hzqp(A2,"",-1)        &＃61;hzqp(A2,"-",-1)    &＃61;hzqp(A2," ",-1)&＃39;单词    公式      拼音    公式&＃39;我们    women      women   wo-men      wo men&＃39;&＃61;hzqp(A2,"",-1)                            &＃61;hzqp(A2,"-",-1)       &＃61;hzqp(A2," ",-1)&＃39;三种用法:  &＃61;hzqp(A2,"",-1)   &＃61;hzqp(A2,"-",-1)  &＃61;hzqp(A2," ",-1)