感谢今日酷米的邀请，大家好，我是excel奇妙之旅，专注于excel知识和技能的分享
excel如何自动将姓名转换为拼音？以下是我的解决方法，不是用vba，而是借助word来操作
操作思路：
第一：把excel中的姓名粘贴到word中，借助word添加拼音，再把word中添加拼音的结果粘贴到excel中
第二：用phonetic函数取得拼音
第三：用substitute函数将拼音声调去掉（这一步视需求，如不需要删除声调可以忽略）
接下来详细介绍操作方法：
1）第一：把excel中的姓名粘贴到word中，借助word添加拼音,注意粘贴的时候选择只保留文本那个选项，添加拼音后再粘贴回excel
注意粘贴到word的时候选择只保留文本那个选项,不然word无法添加拼音
第二：用phonetic函数取得拼音
在c3单元格输入公式==phonetic(b3)，然后下拉公式，得到所有姓名拼音
第三：用substitute函数将拼音声调去掉（这一步视需求，如不需要删除声调可以忽略
在d3单元格输入公式=substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(c3,"ā","a"),"á","a"),"ǎ","a"),"à","a"),"ē","e"),"é","e"),"ě","e"),"è","e"),"ī","i"),"í","i"),"ǐ","i"),"ì","i"),"ō","o"),"ó","o"),"ǒ","o"),"ò","o"),"ū","u"),"ú","u"),"ǔ","u"),"ù","u"),"ǖ","ü"),"ǘ","ü"),"ǚ","ü"),"ǜ","ü")
这个公式利用substitute函数把拼音中的字母为韵母的声调都替换成了不带声调的韵母，也就让拼音的声调去除了
我是excel奇妙之旅，专注于excel知识和技能的分享,关注excel奇妙之旅，带给你更多的实战技巧网友解答:excel如何自动将姓名转换为拼音？
感谢悟空小秘的邀请！
excel如何自动将姓名转换为拼音，由于excel中没有汉字自动转换拼音的功能，所以要在vba中自定义一个转换函数。
具体操作步骤
第一步，要把excel文档转换为xlsm格式，启用宏。
第二步，在文件，设置，自定义功能区中勾选开发工具。
第三步，打开vba编辑器。
第四步，插入模块。
第五步，在模块中输入如下代码：
functionpinyin(passtring)asstring
i=asc(p)
selectcasei
case-20319to-20318:pinyin="a"
case-20317to-20305:pinyin="ai"
case-20304to-20296:pinyin="an"
case-20295to-20293:pinyin="ang"
case-20292to-20284:pinyin="ao"
case-20283to-20266:pinyin="ba"
case-20265to-20258:pinyin="bai"
case-20257to-20243:pinyin="ban"
case-20242to-20231:pinyin="bang"
case-20230to-20052:pinyin="bao"
case-20051to-20037:pinyin="bei"
case-20036to-20033:pinyin="ben"
case-20032to-20027:pinyin="beng"
case-20026to-20003:pinyin="bi"
case-20002to-19991:pinyin="bian"
case-19990to-19987:pinyin="biao"
case-19986to-19983:pinyin="bie"
case-19982to-19977:pinyin="bin"
case-19976to-19806:pinyin="bing"
case-19805to-19785:pinyin="bo"
case-19784to-19776:pinyin="bu"
case-19775to-19775:pinyin="ca"
case-19774to-19764:pinyin="cai"
case-19763to-19757:pinyin="can"
case-19756to-19752:pinyin="cang"
case-19751to-19747:pinyin="cao"
case-19746to-19742:pinyin="ce"
case-19741to-19740:pinyin="ceng"
case-19739to-19729:pinyin="cha"
case-19728to-19726:pinyin="chai"
case-19725to-19716:pinyin="chan"
case-19715to-19541:pinyin="chang"
case-19540to-19532:pinyin="chao"
case-19531to-19526:pinyin="che"
case-19525to-19516:pinyin="chen"
case-19515to-19501:pinyin="cheng"
case-19500to-19485:pinyin="chi"
case-19484to-19480:pinyin="chong"
case-19479to-19468:pinyin="chou"
case-19467to-19290:pinyin="chu"
case-19289to-19289:pinyin="chuai"
case-19288to-19282:pinyin="chuan"
case-19281to-19276:pinyin="chuang"
case-19275to-19271:pinyin="chui"
case-19270to-19264:pinyin="chun"
case-19263to-19262:pinyin="chuo"
case-19261to-19250:pinyin="ci"
case-19249to-19244:pinyin="cong"
case-19243to-19243:pinyin="cou"
case-19242to-19239:pinyin="cu"
case-19238to-19236:pinyin="cuan"
case-19235to-19228:pinyin="cui"
case-19227to-19225:pinyin="cun"
case-19224to-19219:pinyin="cuo"
case-19218to-19213:pinyin="da"
case-19212to-19039:pinyin="dai"
case-19038to-19024:pinyin="dan"
case-19023to-19019:pinyin="dang"
case-19018to-19007:pinyin="dao"
case-19006to-19004:pinyin="de"
case-19003to-18997:pinyin="deng"
case-18996to-18978:pinyin="di"
case-18977to-18962:pinyin="dian"
case-18961to-18953:pinyin="diao"
case-18952to-18784:pinyin="die"
case-18783to-18775:pinyin="ding"
case-18774to-18774:pinyin="diu"
case-18773to-18527:pinyin="dong"
case-18526to-18519:pinyin="fa"
case-18518to-18502:pinyin="fan"
case-18501to-18491:pinyin="fang"
case-18490to-18479:pinyin="fei"
case-18478to-18464:pinyin="fen"
case-18463to-18449:pinyin="feng"
case-18448to-18448:pinyin="fo"
case-18447to-18447:pinyin="fou"
case-18446to-18240:pinyin="fu"
case-18239to-18238:pinyin="ga"
case-18237to-18232:pinyin="gai"
case-18231to-18221:pinyin="gan"
case-18220to-18212:pinyin="gang"
case-18211to-18202:pinyin="gao"
case-18201to-18185:pinyin="ge"
case-18184to-18184:pinyin="gei"
case-18183to-18182:pinyin="gen"
case-18181to-18013:pinyin="geng"
case-18012to-17998:pinyin="gong"
case-17997to-17989:pinyin="gou"
case-17988to-17971:pinyin="gu"
case-17970to-17965:pinyin="gua"
case-17964to-17962:pinyin="guai"
case-17961to-17951:pinyin="guan"
case-17950to-17948:pinyin="guang"
case-17947to-17932:pinyin="gui"
case-17931to-17929:pinyin="gun"
case-17928to-17923:pinyin="guo"
case-17922to-17760:pinyin="ha"
case-17759to-17753:pinyin="hai"
case-17752to-17734:pinyin="han"
case-17733to-17731:pinyin="hang"
case-17730to-17722:pinyin="hao"
case-17721to-17704:pinyin="he"
case-17703to-17702:pinyin="hei"
case-17701to-17698:pinyin="hen"
case-17697to-17693:pinyin="heng"
case-17692to-17684:pinyin="hong"
case-17683to-17677:pinyin="hou"
case-17676to-17497:pinyin="hu"
case-17496to-17488:pinyin="hua"
case-17487to-17483:pinyin="huai"
case-17482to-17469:pinyin="huan"
case-17468to-17455:pinyin="huang"
case-17454to-17434:pinyin="hui"
case-17433to-17428:pinyin="hun"
case-17427to-17418:pinyin="huo"
case-17417to-17203:pinyin="ji"
case-17202to-17186:pinyin="jia"
case-17185to-16984:pinyin="jian"
case-16983to-16971:pinyin="jiang"
case-16970to-16943:pinyin="jiao"
case-16942to-16916:pinyin="jie"
case-16915to-16734:pinyin="jin"
case-16733to-16709:pinyin="jing"
case-16708to-16707:pinyin="jiong"
case-16706to-16690:pinyin="jiu"
case-16689to-16665:pinyin="ju"
case-16664to-16658:pinyin="juan"
case-16657to-16648:pinyin="jue"
case-16647to-16475:pinyin="jun"
case-16474to-16471:pinyin="ka"
case-16470to-16466:pinyin="kai"
case-16465to-16460:pinyin="kan"
case-16459to-16453:pinyin="kang"
case-16452to-16449:pinyin="kao"
case-16448to-16434:pinyin="ke"
case-16433to-16430:pinyin="ken"
case-16429to-16428:pinyin="keng"
case-16427to-16424:pinyin="kong"
case-16423to-16420:pinyin="kou"
case-16419to-16413:pinyin="ku"
case-16412to-16408:pinyin="kua"
case-16407to-16404:pinyin="kuai"
case-16403to-16402:pinyin="kuan"
case-16401to-16394:pinyin="kuang"
case-16393to-16221:pinyin="kui"
case-16220to-16217:pinyin="kun"
case-16216to-16213:pinyin="kuo"
case-16212to-16206:pinyin="la"
case-16205to-16203:pinyin="lai"
case-16202to-16188:pinyin="lan"
case-16187to-16181:pinyin="lang"
case-16180to-16172:pinyin="lao"
case-16171to-16170:pinyin="le"
case-16169to-16159:pinyin="lei"
case-16158to-16156:pinyin="leng"
case-16155to-15960:pinyin="li"
case-15959to-15959:pinyin="lia"
case-15958to-15945:pinyin="lian"
case-15944to-15934:pinyin="liang"
case-15933to-15921:pinyin="liao"
case-15920to-15916:pinyin="lie"
case-15915to-15904:pinyin="lin"
case-15903to-15890:pinyin="ling"
case-15889to-15879:pinyin="liu"
case-15878to-15708:pinyin="long"
case-15707to-15702:pinyin="lou"
case-15701to-15682:pinyin="lu"
case-15681to-15668:pinyin="lv"
case-15667to-15662:pinyin="luan"
case-15661to-15660:pinyin="lue"
case-15659to-15653:pinyin="lun"
case-15652to-15641:pinyin="luo"
case-15640to-15632:pinyin="ma"
case-15631to-15626:pinyin="mai"
case-15625to-15455:pinyin="man"
case-15454to-15449:pinyin="mang"
case-15448to-15437:pinyin="mao"
case-15436to-15436:pinyin="me"
case-15435to-15420:pinyin="mei"
case-15419to-15417:pinyin="men"
case-15416to-15409:pinyin="meng"
case-15408to-15395:pinyin="mi"
case-15394to-15386:pinyin="mian"
case-15385to-15378:pinyin="miao"
case-15377to-15376:pinyin="mie"
case-15375to-15370:pinyin="min"
case-15369to-15364:pinyin="ming"
case-15363to-15363:pinyin="miu"
case-15362to-15184:pinyin="mo"
case-15183to-15181:pinyin="mou"
case-15180to-15166:pinyin="mu"
case-15165to-15159:pinyin="na"
case-15158to-15154:pinyin="nai"
case-15153to-15151:pinyin="nan"
case-15150to-15150:pinyin="nang"
case-15149to-15145:pinyin="nao"
case-15144to-15144:pinyin="ne"
case-15143to-15142:pinyin="nei"
case-15141to-15141:pinyin="nen"
case-15140to-15140:pinyin="neng"
case-15139to-15129:pinyin="ni"
case-15128to-15122:pinyin="nian"
case-15121to-15120:pinyin="niang"
case-15119to-15118:pinyin="niao"
case-15117to-15111:pinyin="nie"
case-15110to-15110:pinyin="nin"
case-15109to-14942:pinyin="ning"
case-14941to-14938:pinyin="niu"
case-14937to-14934:pinyin="nong"
case-14933to-14931:pinyin="nu"
case-14930to-14930:pinyin="nv"
case-14929to-14929:pinyin="nuan"
case-14928to-14927:pinyin="nue"
case-14926to-14923:pinyin="nuo"
case-14922to-14922:pinyin="o"
case-14921to-14915:pinyin="ou"
case-14914to-14909:pinyin="pa"
case-14908to-14903:pinyin="pai"
case-14902to-14895:pinyin="pan"
case-14894to-14890:pinyin="pang"
case-14889to-14883:pinyin="pao"
case-14882to-14874:pinyin="pei"
case-14873to-14872:pinyin="pen"
case-14871to-14858:pinyin="peng"
case-14857to-14679:pinyin="pi"
case-14678to-14675:pinyin="pian"
case-14674to-14671:pinyin="piao"
case-14670to-14669:pinyin="pie"
case-14668to-14664:pinyin="pin"
case-14663to-14655:pinyin="ping"
case-14654to-14646:pinyin="po"
case-14645to-14631:pinyin="pu"
case-14630to-14595:pinyin="qi"
case-14594to-14430:pinyin="qia"
case-14429to-14408:pinyin="qian"
case-14407to-14400:pinyin="qiang"
case-14399to-14385:pinyin="qiao"
case-14384to-14380:pinyin="qie"
case-14379to-14369:pinyin="qin"
case-14368to-14356:pinyin="qing"
case-14355to-14354:pinyin="qiong"
case-14353to-14346:pinyin="qiu"
case-14345to-14171:pinyin="qu"
case-14170to-14160:pinyin="quan"
case-14159to-14152:pinyin="que"
case-14151to-14150:pinyin="qun"
case-14149to-14146:pinyin="ran"
case-14145to-14141:pinyin="rang"
case-14140to-14138:pinyin="rao"
case-14137to-14136:pinyin="re"
case-14135to-14126:pinyin="ren"
case-14125to-14124:pinyin="reng"
case-14123to-14123:pinyin="ri"
case-14122to-14113:pinyin="rong"
case-14112to-14110:pinyin="rou"
case-14109to-14100:pinyin="ru"
case-14099to-14098:pinyin="ruan"
case-14097to-14095:pinyin="rui"
case-14094to-14093:pinyin="run"
case-14092to-14091:pinyin="ruo"
case-14090to-14088:pinyin="sa"
case-14087to-14084:pinyin="sai"
case-14083to-13918:pinyin="san"
case-13917to-13915:pinyin="sang"
case-13914to-13911:pinyin="sao"
case-13910to-13908:pinyin="se"
case-13907to-13907:pinyin="sen"
case-13906to-13906:pinyin="seng"
case-13905to-13897:pinyin="sha"
case-13896to-13895:pinyin="shai"
case-13894to-13879:pinyin="shan"
case-13878to-13871:pinyin="shang"
case-13870to-13860:pinyin="shao"
case-13859to-13848:pinyin="she"
case-13847to-13832:pinyin="shen"
case-13831to-13659:pinyin="sheng"
case-13658to-13612:pinyin="shi"
case-13611to-13602:pinyin="shou"
case-13601to-13407:pinyin="shu"
case-13406to-13405:pinyin="shua"
case-13404to-13401:pinyin="shuai"
case-13400to-13399:pinyin="shuan"
case-13398to-13396:pinyin="shuang"
case-13395to-13392:pinyin="shui"
case-13391to-13388:pinyin="shun"
case-13387to-13384:pinyin="shuo"
case-13383to-13368:pinyin="si"
case-13367to-13360:pinyin="song"
case-13359to-13357:pinyin="sou"
case-13356to-13344:pinyin="su"
case-13343to-13341:pinyin="suan"
case-13340to-13330:pinyin="sui"
case-13329to-13327:pinyin="sun"
case-13326to-13319:pinyin="suo"
case-13318to-13148:pinyin="ta"
case-13147to-13139:pinyin="tai"
case-13138to-13121:pinyin="tan"
case-13120to-13108:pinyin="tang"
case-13107to-13097:pinyin="tao"
case-13096to-13096:pinyin="te"
case-13095to-13092:pinyin="teng"
case-13091to-13077:pinyin="ti"
case-13076to-13069:pinyin="tian"
case-13068to-13064:pinyin="tiao"
case-13063to-13061:pinyin="tie"
case-13060to-12889:pinyin="ting"
case-12888to-12876:pinyin="tong"
case-12875to-12872:pinyin="tou"
case-12871to-12861:pinyin="tu"
case-12860to-12859:pinyin="tuan"
case-12858to-12853:pinyin="tui"
case-12852to-12850:pinyin="tun"
case-12849to-12839:pinyin="tuo"
case-12838to-12832:pinyin="wa"
case-12831to-12830:pinyin="wai"
case-12829to-12813:pinyin="wan"
case-12812to-12803:pinyin="wang"
case-12802to-12608:pinyin="wei"
case-12607to-12598:pinyin="wen"
case-12597to-12595:pinyin="weng"
case-12594to-12586:pinyin="wo"
case-12585to-12557:pinyin="wu"
case-12556to-12360:pinyin="xi"
case-12359to-12347:pinyin="xia"
case-12346to-12321:pinyin="xian"
case-12320to-12301:pinyin="xiang"
case-12300to-12121:pinyin="xiao"
case-12120to-12100:pinyin="xie"
case-12099to-12090:pinyin="xin"
case-12089to-12075:pinyin="xing"
case-12074to-12068:pinyin="xiong"
case-12067to-12059:pinyin="xiu"
case-12058to-12040:pinyin="xu"
case-12039to-11868:pinyin="xuan"
case-11867to-11862:pinyin="xue"
case-11861to-11848:pinyin="xun"
case-11847to-11832:pinyin="ya"
case-11831to-11799:pinyin="yan"
case-11798to-11782:pinyin="yang"
case-11781to-11605:pinyin="yao"
case-11604to-11590:pinyin="ye"
case-11589to-11537:pinyin="yi"
case-11536to-11359:pinyin="yin"
case-11358to-11341:pinyin="ying"
case-11340to-11340:pinyin="yo"
case-11339to-11325:pinyin="yong"
case-11324to-11304:pinyin="you"
case-11303to-11098:pinyin="yu"
case-11097to-11078:pinyin="yuan"
case-11077to-11068:pinyin="yue"
case-11067to-11056:pinyin="yun"
case-11055to-11053:pinyin="za"
case-11052to-11046:pinyin="zai"
case-11045to-11042:pinyin="zan"
case-11041to-11039:pinyin="zang"
case-11038to-11025:pinyin="zao"
case-11024to-11021:pinyin="ze"
case-11020to-11020:pinyin="zei"
case-11019to-11019:pinyin="zen"
case-11018to-11015:pinyin="zeng"
case-11014to-10839:pinyin="zha"
case-10838to-10833:pinyin="zhai"
case-10832to-10816:pinyin="zhan"
case-10815to-10801:pinyin="zhang"
case-10800to-10791:pinyin="zhao"
case-10790to-10781:pinyin="zhe"
case-10780to-10765:pinyin="zhen"
case-10764to-10588:pinyin="zheng"
case-10587to-10545:pinyin="zhi"
case-10544to-10534:pinyin="zhong"
case-10533to-10520:pinyin="zhou"
case-10519to-10332:pinyin="zhu"
case-10331to-10330:pinyin="zhua"
case-10329to-10329:pinyin="zhuai"
case-10328to-10323:pinyin="zhuan"
case-10322to-10316:pinyin="zhuang"
case-10315to-10310:pinyin="zhui"
case-10309to-10308:pinyin="zhun"
case-10307to-10297:pinyin="zhuo"
case-10296to-10282:pinyin="zi"
case-10281to-10275:pinyin="zong"
case-10274to-10271:pinyin="zou"
case-10270to-10263:pinyin="zu"
case-10262to-10261:pinyin="zuan"
case-10260to-10257:pinyin="zui"
case-10256to-10255:pinyin="zun"
case-10254to-10254:pinyin="zuo"
caseelse:pinyin=p
endselect
endfunction
functiongetpy(str)
fori=1tolen(str)
getpy=getpy&pinyin(mid(str,i,1))
nexti
endfunction
第六步，输入拼音转换函数。
快速复制函数完成转换。
excel如何自动将姓名转换为拼音操作演示完成。
李老师简介：创办电脑学校，专职从事电脑教学二十余年。酷米号：李老师电脑教学课堂，简单实用，清晰明了，专门系统讲解电脑知识，软件使用技巧，欢迎关注。
