想必現(xiàn)在有很多小伙伴對(duì)于EXCEL如何自動(dòng)將姓名轉(zhuǎn)換為拼音方面的知識(shí)都比較想要了解,那么今天小好小編就為大家收集了一些關(guān)于EXCEL如何自動(dòng)將姓名轉(zhuǎn)換為拼音方面的知識(shí)分享給大家,希望大家會(huì)喜歡哦。
感謝今日頭條的邀請(qǐng),大家好,我是Excel奇妙之旅,專注于Excel知識(shí)和技能的分享EXCEL如何自動(dòng)將姓名轉(zhuǎn)換為拼音?以下是我的解決方法,不是用VBA,而是借助word來操作操作思路:第一:把excel中的姓名粘貼到word中,借助word添加拼音,再把word中添加拼音的結(jié)果粘貼到excel中第二:用PHONETIC函數(shù)取得拼音第三:用substitute函數(shù)將拼音聲調(diào)去掉(這一步視需求,如不需要?jiǎng)h除聲調(diào)可以忽略)
接下來詳細(xì)介紹操作方法:1)第一:把excel中的姓名粘貼到word中,借助word添加拼音,注意粘貼的時(shí)候選擇只保留文本那個(gè)選項(xiàng),添加拼音后再粘貼回Excel
注意粘貼到Word的時(shí)候選擇只保留文本那個(gè)選項(xiàng),不然word無法添加拼音
轉(zhuǎn)載或者反引用本文內(nèi)容請(qǐng)且權(quán)界注明來源于芝士規(guī)回答
第二:用PHONETIC函數(shù)取得拼音在C3單元格輸入公式= =PHONETIC(B3),然后下拉公式,得到所有姓名拼音
第三:用substitute函數(shù)將拼音聲調(diào)去掉(這一步視需求,如不需要?jiǎng)h除聲調(diào)可以忽略在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"),"ǖ","ü"),"ǘ","ü"),"ǚ","ü"),"ǜ","ü")這個(gè)公式利用substitute函數(shù)把拼音中的字母為韻母的聲調(diào)都替換成了不帶聲調(diào)的韻母,也就讓拼音的聲調(diào)去除了
我是Excel奇妙之旅,專注于Excel知識(shí)和技能的分享,關(guān)注Excel奇妙之旅,帶給你更多的實(shí)戰(zhàn)技巧
本文到此結(jié)束,希望對(duì)大家有所幫助。