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

1启动excel 2010，打开相应的工作表1此文章未经许可获取自百度经验2按“alt+f11”组合键，进入v**ual basic编辑状态；3执行“插入→模块”命令，插入一个新模块。再双击插入的模块，进入模块代码编辑状态；4输入宏代码：function pinyin(p as string) as stringi = 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 = "** "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 -19525 to -19516: pinyin = "chen "case -19515 to -19501: pinyin = "cheng "case -19500 to -19485: pinyin = "chi "case -19484 to -19480: pinyin = "chong "case -19479 to -19468: pinyin = "chou "case -19467 to -19290: pinyin = "chu "case -19289 to -19289: pinyin = "chuai "case -19288 to -19282: pinyin = "chuan "case -19281 to -19276: pinyin = "chuang "case -19275 to -19271: pinyin = "chui "case -19270 to -19264: pinyin = "chun "case -19263 to -19262: pinyin = "chuo "case -19261 to -19250: pinyin = "ci "case -19249 to -19244: pinyin = "cong "case -19243 to -19243: pinyin = "cou "case -19242 to -19239: pinyin = "cu "case -19238 to -19236: pinyin = "cuan "case -19235 to -19228: pinyin = "cui "case -19227 to -19225: pinyin = "cun "case -19224 to -19219: pinyin = "cuo "case -19218 to -19213: pinyin = "da "case -19212 to -19039: pinyin = "dai "case -19038 to -19024: pinyin = "dan "case -19023 to -19019: pinyin = "dang "case -19018 to -19007: pinyin = "dao "case -19006 to -19004: pinyin = "de "case -19003 to -18997: pinyin = "deng "case -18996 to -18978: pinyin = "di "case -18977 to -18962: pinyin = "dian "case -18961 to -18953: pinyin = "diao "case -18952 to -18784: pinyin = "die "case -18783 to -18775: pinyin = "ding "case -18774 to -18774: pinyin = "diu "case -18773 to -18527: pinyin = "dong "case -18526 to -18519: pinyin = "fa "case -18518 to -18502: pinyin = "fan "case -18501 to -18491: pinyin = "fang "case -18490 to -18479: pinyin = "fei "case -18478 to -18464: pinyin = "fen "case -18463 to -18449: pinyin = "feng "case -18448 to -18448: pinyin = "fo "case -18447 to -18447: pinyin = "fou "case -18446 to -18240: pinyin = "fu "case -18239 to -18238: pinyin = "ga "case -18237 to -18232: pinyin = "gai "case -18231 to -18221: pinyin = "gan "case -18220 to -18212: pinyin = "gang "case -18211 to -18202: pinyin = "gao "case -18201 to -18185: pinyin = "ge "case -18184 to -18184: pinyin = "gei "case -18183 to -18182: pinyin = "gen "case -18181 to -18013: pinyin = "geng "case -18012 to -17998: pinyin = "gong "case -17997 to -17989: pinyin = "gou "case -17988 to -17971: pinyin = "gu "case -17970 to -17965: pinyin = "gua "case -17964 to -17962: pinyin = "guai "case -17961 to -17951: pinyin = "guan "case -17950 to -17948: pinyin = "guang "case -17947 to -17932: pinyin = "gui "case -17931 to -17929: pinyin = "gun "case -17928 to -17923: pinyin = "guo "case -17922 to -17760: pinyin = "ha "case -17759 to -17753: pinyin = "hai "case -17752 to -17734: pinyin = "han "case -17733 to -17731: pinyin = "hang "case -17730 to -17722: pinyin = "hao "case -17721 to -17704: pinyin = "he "case -17703 to -17702: pinyin = "hei "case -17701 to -17698: pinyin = "hen "case -17697 to -17693: pinyin = "heng "case -17692 to -17684: pinyin = "hong "case -17683 to -17677: pinyin = "hou "case -17676 to -17497: pinyin = "hu "case -17496 to -17488: pinyin = "hua "case -17487 to -17483: pinyin = "huai "case -17482 to -17469: pinyin = "huan "case -17468 to -17455: pinyin = "huang "case -17454 to -17434: pinyin = "hui "case -17433 to -17428: pinyin = "hun "case -17427 to -17418: pinyin = "huo "case -17417 to -17203: pinyin = "ji "case -17202 to -17186: pinyin = "jia "case -17185 to -16984: pinyin = "jian "case -16983 to -16971: pinyin = "jiang "case -16970 to -16943: pinyin = "jiao "case -16942 to -16916: pinyin = "jie "case -16915 to -16734: pinyin = "jin "case -16733 to -16709: pinyin = "jing "case -16708 to -16707: pinyin = "jiong "case -16706 to -16690: pinyin = "jiu "case -16689 to -16665: pinyin = "ju "case -16664 to -16658: pinyin = "juan "case -16657 to -16648: pinyin = "jue "case -16647 to -16475: pinyin = "jun "case -16474 to -16471: pinyin = "ka "case -16470 to -16466: pinyin = "kai "case -16465 to -16460: pinyin = "kan "case -16459 to -16453: pinyin = "kang "case -16452 to -16449: pinyin = "kao "case -16448 to -16434: pinyin = "ke "case -16433 to -16430: pinyin = "ken "case -16429 to -16428: pinyin = "keng "case -16427 to -16424: pinyin = "kong "case -16423 to -16420: pinyin = "kou "case -16419 to -16413: pinyin = "ku "case -16412 to -16408: pinyin = "kua "case -16407 to -16404: pinyin = "kuai "case -16403 to -16402: pinyin = "kuan "case -16401 to -16394: pinyin = "kuang "case -16393 to -16221: pinyin = "kui "case -16220 to -16217: pinyin = "kun "case -16216 to -16213: pinyin = "kuo "case -16212 to -16206: pinyin = "la "case -16205 to -16203: pinyin = "lai "case -16202 to -16188: pinyin = "lan "case -16187 to -16181: pinyin = "lang "case -16180 to -16172: pinyin = "lao "case -16171 to -16170: pinyin = "le "case -16169 to -16159: pinyin = "lei "case -16158 to -16156: pinyin = "leng "case -16155 to -15960: pinyin = "li "case -15959 to -15959: pinyin = "lia "case -15958 to -15945: pinyin = "lian "case -15944 to -15934: pinyin = "liang "case -15933 to -15921: pinyin = "liao "case -15920 to -15916: pinyin = "lie "case -15915 to -15904: pinyin = "lin "case -15903 to -15890: pinyin = "ling "case -15889 to -15879: pinyin = "liu "case -15878 to -15708: pinyin = "long "case -15707 to -15702: pinyin = "lou "case -15701 to -15682: pinyin = "lu "case -15681 to -15668: pinyin = "lv "case -15667 to -15662: pinyin = "luan "case -15661 to -15660: pinyin = "lue "case -15659 to -15653: pinyin = "lun "case -15652 to -15641: pinyin = "luo "case -15640 to -15632: pinyin = "ma "case -15631 to -15626: pinyin = "mai "case -15625 to -15455: pinyin = "man "case -15454 to -15449: pinyin = "mang "case -15448 to -15437: pinyin = "mao "case -15436 to -15436: pinyin = "me "case -15435 to -15420: pinyin = "mei "case -15419 to -15417: pinyin = "men "case -15416 to -15409: pinyin = "meng "case -15408 to -15395: pinyin = "mi "case -15394 to -15386: pinyin = "mian "case -15385 to -15378: pinyin = "miao "case -15377 to -15376: pinyin = "mie "case -15375 to -15370: pinyin = "min "case -15369 to -15364: pinyin = "ming "case -15363 to -15363: pinyin = "miu "case -15362 to -15184: pinyin = "mo "case -15183 to -15181: pinyin = "mou "case -15180 to -15166: pinyin = "mu "case -15165 to -15159: pinyin = "na "case -15158 to -15154: pinyin = "nai "case -15153 to -15151: pinyin = "nan "case -15150 to -15150: pinyin = "nang "case -15149 to -15145: pinyin = "nao "case -15144 to -15144: pinyin = "ne "case -15143 to -15142: pinyin = "nei "case -15141 to -15141: pinyin = "nen "case -15140 to -15140: pinyin = "neng "case -15139 to -15129: pinyin = "ni "case -15128 to -15122: pinyin = "nian "case -15121 to -15120: pinyin = "niang "case -15119 to -15118: pinyin = "niao "case -15117 to -15111: pinyin = "nie "case -15110 to -15110: pinyin = "nin "case -15109 to -14942: pinyin = "ning "case -14941 to -14938: pinyin = "niu "case -14937 to -14934: pinyin = "nong "case -14933 to -14931: pinyin = "nu "case -14930 to -14930: pinyin = "nv "case -14929 to -14929: pinyin = "nuan "case -14928 to -14927: pinyin = "nue "case -14926 to -14923: pinyin = "nuo "case -14922 to -14922: pinyin = "o "case -14921 to -14915: pinyin = "ou "case -14914 to -14909: pinyin = "pa "case -14908 to -14903: pinyin = "pai "case -14902 to -14895: pinyin = "pan "case -14894 to -14890: pinyin = "pang "case -14889 to -14883: pinyin = "pao "case -14882 to -14874: pinyin = "pei "case -14873 to -14872: pinyin = "pen "case -14871 to -14858: pinyin = "peng "case -14857 to -14679: pinyin = "pi "case -14678 to -14675: pinyin = "pian "case -14674 to -14671: pinyin = "piao "case -14670 to -14669: pinyin = "pie "case -14668 to -14664: pinyin = "pin "case -14663 to -14655: pinyin = "ping "case -14654 to -14646: pinyin = "po "case -14645 to -14631: pinyin = "pu "case -14630 to -14595: pinyin = "qi "case -14594 to -14430: pinyin = "qia "case -14429 to -14408: pinyin = "qian "case -14407 to -14400: pinyin = "qiang "case -14399 to -14385: pinyin = "qiao "case -14384 to -14380: pinyin = "qie "case -14379 to -14369: pinyin = "qin "case -14368 to -14356: pinyin = "qing "case -14355 to -14354: pinyin = "qiong "case -14353 to -14346: pinyin = "qiu "case -14345 to -14171: pinyin = "qu "case -14170 to -14160: pinyin = "quan "case -14159 to -14152: pinyin = "que "case -14151 to -14150: pinyin = "qun "case -14149 to -14146: pinyin = "ran "case -14145 to -14141: pinyin = "rang "case -14140 to -14138: pinyin = "rao "case -14137 to -14136: pinyin = "re "case -14135 to -14126: pinyin = "ren "case -14125 to -14124: pinyin = "reng "case -14123 to -14123: pinyin = "ri "case -14122 to -14113: pinyin = "rong "case -14112 to -14110: pinyin = "rou "case -14109 to -14100: pinyin = "ru "case -14099 to -14098: pinyin = "ruan "case -14097 to -14095: pinyin = "rui "case -14094 to -14093: pinyin = "run "case -14092 to -14091: pinyin = "ruo "case -14090 to -14088: pinyin = "sa "case -14087 to -14084: pinyin = "sai "case -14083 to -13918: pinyin = "san "case -13917 to -13915: pinyin = "sang "case -13914 to -13911: pinyin = "sao "case -13910 to -13908: pinyin = "se "case -13907 to -13907: pinyin = "sen "case -13906 to -13906: pinyin = "seng "case -13905 to -13897: pinyin = "sha "case -13896 to -13895: pinyin = "shai "case -13894 to -13879: pinyin = "shan "case -13878 to -13871: pinyin = "shang "case -13870 to -13860: pinyin = "shao "case -13859 to -13848: pinyin = "she "case -13847 to -13832: pinyin = "shen "case -13831 to -13659: pinyin = "sheng "case -13658 to -13612: pinyin = "shi "case -13611 to -13602: pinyin = "shou "case -13601 to -13407: pinyin = "shu "case -13406 to -13405: pinyin = "shua "case -13404 to -13401: pinyin = "shuai "case -13400 to -13399: pinyin = "shuan "case -13398 to -13396: pinyin = "shuang "case -13395 to -13392: pinyin = "shui "case -13391 to -13388: pinyin = "shun "case -13387 to -13384: pinyin = "shuo "case -13383 to -13368: pinyin = "si "case -13367 to -13360: pinyin = "song "case -13359 to -13357: pinyin = "sou "case -13356 to -13344: pinyin = "su "case -13343 to -13341: pinyin = "suan "case -13340 to -13330: pinyin = "sui "case -13329 to -13327: pinyin = "sun "case -13326 to -13319: pinyin = "suo "case -13318 to -13148: pinyin = "ta "case -13147 to -13139: pinyin = "tai "case -13138 to -13121: pinyin = "tan "case -13120 to -13108: pinyin = "tang "case -13107 to -13097: pinyin = "tao "case -13096 to -13096: pinyin = "te "case -13095 to -13092: pinyin = "teng "case -13091 to -13077: pinyin = "ti "case -13076 to -13069: pinyin = "tian "case -13068 to -13064: pinyin = "tiao "case -13063 to -13061: pinyin = "tie "case -13060 to -12889: pinyin = "ting "case -12888 to -12876: pinyin = "tong "case -12875 to -12872: pinyin = "tou "case -12871 to -12861: pinyin = "tu "case -12860 to -12859: pinyin = "tuan "case -12858 to -12853: pinyin = "tui "case -12852 to -12850: pinyin = "tun "case -12849 to -12839: pinyin = "tuo "case -12838 to -12832: pinyin = "wa "case -12831 to -12830: pinyin = "wai "case -12829 to -12813: pinyin = "wan "case -12812 to -12803: pinyin = "wang "case -12802 to -12608: pinyin = "wei "case -12607 to -12598: pinyin = "wen "case -12597 to -12595: pinyin = "weng "case -12594 to -12586: pinyin = "wo "case -12585 to -12557: pinyin = "wu "case -12556 to -12360: pinyin = "xi "case -12359 to -12347: pinyin = "xia "case -12346 to -12321: pinyin = "xian "case -12320 to -12301: pinyin = "xiang "case -12300 to -12121: pinyin = "xiao "case -12120 to -12100: pinyin = "xie "case -12099 to -12090: pinyin = "xin "case -12089 to -12075: pinyin = "xing "case -12074 to -12068: pinyin = "xiong "case -12067 to -12059: pinyin = "xiu "case -12058 to -12040: pinyin = "xu "case -12039 to -11868: pinyin = "xuan "case -11867 to -11862: pinyin = "xue "case -11861 to -11848: pinyin = "xun "case -11847 to -11832: pinyin = "ya "case -11831 to -11799: pinyin = "yan "case -11798 to -11782: pinyin = "yang "case -11781 to -11605: pinyin = "yao "case -11604 to -11590: pinyin = "ye "case -11589 to -11537: pinyin = "yi "case -11536 to -11359: pinyin = "yin "case -11358 to -11341: pinyin = "ying "case -11340 to -11340: pinyin = "yo "case -11339 to -11325: pinyin = "yong "case -11324 to -11304: pinyin = "you "case -11303 to -11098: pinyin = "yu "case -11097 to -11078: pinyin = "yuan "case -11077 to -11068: pinyin = "yue "case -11067 to -11056: pinyin = "yun "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 function5关闭v**ual basic编辑窗口，返回excel编辑状态6选中转换后的拼音需要放在哪个列，例如放在d列的第2个单元格，输入公式：=getpy(d2)7个别汉字不能转换，手动删除汉字，输入拼音就可以了8去除拼音之间的空格。去掉空格的拼音放在e列，如果这个数据原来在d2单元格，去掉空格之后的拼音放在e2单元格，则在e2单元格输入： =substitute(d2," ","")9如果以上经验帮到您，麻烦在左下角给点个赞，谢谢！end 20210311