大家好，chengreng相信很多的网友都不是很明白，包括excel根据姓名自动输入拼音也是一样，不过没有关系，接下来就来为大家分享关于chengreng和excel根据姓名自动输入拼音的一些知识点，大家可以关注收藏，免得下次来找不到哦，下面我们开始吧！
1、在人事管理中经常遇到需要输入姓名拼音，手工输入容易拼写错误，今天教大家用自定义函数直接输入简单方便。
2、先打开excel选项找到自定义功能区勾选开发工具点确定
3、在功能区点击开发工具打开VBA编辑器，插入模板后复制代码粘贴到模块中，
4、Functionpinyin(pAsString)AsStringni=Asc(p)nSelectCaseinCase-20319To-20318:pinyin="A"nCase-20317To-20305:pinyin="AI"nCase-20304To-20296:pinyin="AN"nCase-20295To-20293:pinyin="ANG"nCase-20292To-20284:pinyin="AO"nCase-20283To-20266:pinyin="BA"nCase-20265To-20258:pinyin="BAI"nCase-20257To-20243:pinyin="BAN"nCase-20242To-20231:pinyin="BANG"nCase-20230To-20226,-20063To-20052:pinyin="BAO"nCase-20051To-20037:pinyin="BEI"nCase-20036To-20033:pinyin="BEN"nCase-20032To-20027:pinyin="BENG"nCase-20026To-20003:pinyin="BI"nCase-20002To-19991:pinyin="BIAN"nCase-19990To-19987:pinyin="BIAO"nCase-19986To-19983:pinyin="BIE"nCase-19982To-19977:pinyin="BIN"nCase-19976To-19970,-19807,-19806:pinyin="BING"nCase-19805To-19787:pinyin="BO"nCase-19786To-19776:pinyin="BU"nCase-19775:pinyin="CA"nCase-19774To-19764:pinyin="CAI"nCase-19763To-19757:pinyin="CAN"nCase-19756To-19752:pinyin="CANG"nCase-19751To-19747:pinyin="CAO"nCase-19746To-19742:pinyin="CE"nCase-19741To-19740:pinyin="CENG"nCase-19739To-19729:pinyin="CHA"nCase-19728To-19726:pinyin="CHAI"nCase-19725To-19716:pinyin="CHAN"nCase-19715,-19714,-19551To-19541:pinyin="CHANG"nCase-19540To-19532:pinyin="CHAO"nCase-19531To-19526:pinyin="CHE"nCase-19525To-19516:pinyin="CHEN"nCase-19515To-19501:pinyin="CHENG"nCase-19500To-19485:pinyin="CHI"nCase-19484To-19480:pinyin="CHONG"nCase-19479To-19468:pinyin="CHOU"nCase-19467To-19458,-19295To-19290:pinyin="CHU"nCase-19289:pinyin="CHUAI"nCase-19288To-19282:pinyin="CHUAN"nCase-19281To-19276:pinyin="CHUANG"nCase-19275To-19271:pinyin="CHUI"nCase-19270To-19264:pinyin="CHUN"nCase-19263,-19262:pinyin="CHUO"nCase-19261To-19250:pinyin="CI"nCase-19249To-19244:pinyin="CONG"nCase-19243:pinyin="COU"nCase-19242To-19239:pinyin="CU"nCase-19238To-19236:pinyin="CUAN"nCase-19235To-19228:pinyin="CUI"nCase-19227To-19225:pinyin="CUN"nCase-19224To-19219:pinyin="CUO"nCase-19218To-19213:pinyin="DA"nCase-19212To-19202,-19039:pinyin="DAI"nCase-19038To-19024:pinyin="DAN"nCase-19023To-19019:pinyin="DANG"nCase-19018To-19007:pinyin="DAO"nCase-19006To-19004:pinyin="DE"nCase-19003To-18997:pinyin="DENG"nCase-18996To-18978:pinyin="DI"nCase-18977To-18962:pinyin="DIAN"nCase-18961To-18953:pinyin="DIAO"nCase-18952To-18946:pinyin="DIE"nCase-18783To-18775:pinyin="DING"nCase-18774:pinyin="DIU"nCase-18773To-18764:pinyin="DONG"nCase-18763To-18756:pinyin="DOU"nCase-18755To-18742:pinyin="DU"nCase-18741To-18736:pinyin="DUAN"nCase-18735To-18732:pinyin="DUI"nCase-18731To-18723:pinyin="DUN"nCase-18722To-18711:pinyin="DUO"nCase-18710To-18698:pinyin="E"nCase-18697:pinyin="EN"nCase-18696To-18690,-18527:pinyin="ER"nCase-18526To-18519:pinyin="FA"nCase-18518To-18502:pinyin="FAN"nCase-18501To-18491:pinyin="FANG"nCase-18490To-18479:pinyin="FEI"nCase-18478To-18464:pinyin="FEN"nCase-18463To-18449:pinyin="FENG"nCase-18448To-18448:pinyin="FO"nCase-18447:pinyin="FOU"nCase-18446To-18434,-18271To-18240:pinyin="FU"nCase-18239To-18238:pinyin="GA"nCase-18237To-18232:pinyin="GAI"nCase-18231To-18221:pinyin="GAN"nCase-18220To-18212:pinyin="GANG"nCase-18211To-18202:pinyin="GAO"nCase-18201To-18185:pinyin="GE"nCase-18184:pinyin="GEI"nCase-18183To-18182:pinyin="GEN"nCase-18181To-18178,-18015To-18013:pinyin="GENG"nCase-18012To-17998:pinyin="GONG"nCase-17997To-17989:pinyin="GOU"nCase-17988To-17971:pinyin="GU"nCase-17970To-17965:pinyin="GUA"nCase-17964To-17962:pinyin="GUAI"nCase-17961To-17951:pinyin="GUAN"nCase-17950To-17948:pinyin="GUANG"nCase-17947To-17932:pinyin="GUI"nCase-17931To-17929:pinyin="GUN"nCase-17928To-17923:pinyin="GUO"nCase-17922:pinyin="HA"nCase-17759To-17753:pinyin="HAI"nCase-17752To-17734:pinyin="HAN"nCase-17733To-17731:pinyin="HANG"nCase-17730To-17722:pinyin="HAO"nCase-17721To-17704:pinyin="HE"nCase-17703To-17702:pinyin="HEI"nCase-17701To-17698:pinyin="HEN"nCase-17697To-17693:pinyin="HENG"nCase-17692To-17684:pinyin="HONG"nCase-17683To-17677:pinyin="HOU"nCase-17676To-17666,-17503To-17497:pinyin="HU"nCase-17496To-17488:pinyin="HUA"nCase-17487To-17483:pinyin="HUAI"nCase-17482To-17469:pinyin="HUAN"nCase-17468To-17455:pinyin="HUANG"nCase-17454To-17434:pinyin="HUI"nCase-17433To-17428:pinyin="HUN"nCase-17427To-17418:pinyin="HUO"nCase-17417To-17410,-17247To-17203:pinyin="JI"nCase-17202To-17186:pinyin="JIA"nCase-17185To-17162,-17160To-17154,-16991To-16984:pinyin="JIAN"nCase-16983To-16971:pinyin="JIANG"nCase-16970To-16943:pinyin="JIAO"nCase-16942To-16916:pinyin="JIE"nCase-16915To-16898,-16735,-16734:pinyin="JIN"nCase-16733To-16709:pinyin="JING"nCase-16708,-16707:pinyin="JIONG"nCase-16706To-16690:pinyin="JIU"nCase-16689To-16665:pinyin="JU"nCase-16664To-16658:pinyin="JUAN"nCase-16657To-16648:pinyin="JUE"nCase-16647To-16642,-16479To-16475:pinyin="JUN"nCase-16474To-16471:pinyin="KA"nCase-16470To-16466:pinyin="KAI"nCase-17161,-16465To-16460:pinyin="KAN"nCase-16459To-16453:pinyin="KANG"nCase-16452To-16449:pinyin="KAO"nCase-16448To-16434:pinyin="KE"nCase-16433To-16430:pinyin="KEN"nCase-16429,-16428:pinyin="KENG"nCase-16427To-16424:pinyin="KONG"nCase-16423To-16420:pinyin="KOU"nCase-16419To-16413:pinyin="KU"nCase-16412To-16408:pinyin="KUA"nCase-16407To-16404:pinyin="KUAI"nCase-16403,-16402:pinyin="KUAN"nCase-16401To-16394:pinyin="KUANG"nCase-16393To-16386,-16223To-16221:pinyin="KUI"nCase-16220To-16217:pinyin="KUN"nCase-16216To-16213:pinyin="KUO"nCase-16212To-16206:pinyin="LA"nCase-16205To-16203:pinyin="LAI"nCase-16202To-16188:pinyin="LAN"nCase-16187To-16181:pinyin="LANG"nCase-16180To-16172:pinyin="LAO"nCase-16171,-16170:pinyin="LE"nCase-16169To-16159:pinyin="LEI"nCase-16158To-16156:pinyin="LENG"nCase-16155To-16130,-15967To-15960:pinyin="LI"nCase-15959:pinyin="LIA"nCase-15958To-15945:pinyin="LIAN"nCase-15944To-15934:pinyin="LIANG"nCase-15933To-15921:pinyin="LIAO"nCase-15920To-15916:pinyin="LIE"nCase-15915To-15904:pinyin="LIN"nCase-15903To-15890:pinyin="LING"nCase-15889To-15879:pinyin="LIU"nCase-15878To-15874,-15711To-15708:pinyin="LONG"nCase-15707To-15702:pinyin="LOU"nCase-15701To-15682:pinyin="LU"nCase-15681To-15668:pinyin="LV"nCase-15667To-15662:pinyin="LUAN"nCase-15661,-15660:pinyin="LUE"nCase-15659To-15653:pinyin="LUN"nCase-15652To-15641:pinyin="LUO"nCase-15640To-15632:pinyin="MA"nCase-15631To-15626:pinyin="MAI"nCase-15625To-15618,-15455:pinyin="MAN"nCase-15454To-15449:pinyin="MANG"nCase-15448To-15437:pinyin="MAO"nCase-15436:pinyin="ME"nCase-15435To-15420:pinyin="MEI"nCase-15419To-15417:pinyin="MEN"nCase-15416To-15409:pinyin="MENG"nCase-15408To-15395:pinyin="MI"nCase-15394To-15386:pinyin="MIAN"nCase-15385To-15378:pinyin="MIAO"nCase-15377To-15376:pinyin="MIE"nCase-15375To-15370:pinyin="MIN"nCase-15369To-15364:pinyin="MING"nCase-15363:pinyin="MIU"nCase-15362,-15199To-15184:pinyin="MO"nCase-15183To-15181:pinyin="MOU"nCase-15180To-15166:pinyin="MU"nCase-15165To-15159:pinyin="NA"nCase-15158To-15154:pinyin="NAI"nCase-15153To-15151:pinyin="NAN"nCase-15150:pinyin="NANG"nCase-15149To-15145:pinyin="NAO"nCase-15144:pinyin="NE"nCase-15143,-15142:pinyin="NEI"nCase-15141:pinyin="NEN"nCase-15140:pinyin="NENG"nCase-15139To-15129:pinyin="NI"nCase-15128To-15122:pinyin="NIAN"nCase-15121,-15120:pinyin="NIANG"nCase-15119,-15118:pinyin="NIAO"nCase-15117To-15111:pinyin="NIE"nCase-15110:pinyin="NIN"nCase-15109To-15106,-14943,-14942:pinyin="NING"nCase-14941To-14938:pinyin="NIU"nCase-14937To-14934:pinyin="NONG"nCase-14933To-14931:pinyin="NU"nCase-14930:pinyin="NV"nCase-14929:pinyin="NUAN"nCase-14928,-14927:pinyin="NUE"nCase-14926To-14923:pinyin="NUO"nCase-14922:pinyin="O"nCase-14921To-14915:pinyin="OU"nCase-14914To-14909:pinyin="PA"nCase-14908To-14903:pinyin="PAI"nCase-14902To-14895:pinyin="PAN"nCase-14894To-14890:pinyin="PANG"nCase-14889To-14883:pinyin="PAO"nCase-14882To-14874:pinyin="PEI"nCase-14873,-14872:pinyin="PEN"nCase-14871To-14858:pinyin="PENG"nCase-14857To-14850,-14687To-14679:pinyin="PI"nCase-14678To-14675:pinyin="PIAN"nCase-14674To-14671:pinyin="PIAO"nCase-14670,-14669:pinyin="PIE"nCase-14668To-14664:pinyin="PIN"nCase-14663To-14655:pinyin="PING"nCase-14654To-14647:pinyin="PO"nCase-14646:pinyin="POU"nCase-14645To-14631:pinyin="PU"nCase-14630To-14595,-6151:pinyin="QI"nCase-14594,-14431,-14430:pinyin="QIA"nCase-14429To-14408:pinyin="QIAN"nCase-14407To-14400:pinyin="QIANG"nCase-14399To-14385:pinyin="QIAO"nCase-14384To-14380:pinyin="QIE"nCase-14379To-14369:pinyin="QIN"nCase-14368To-14356:pinyin="QING"nCase-14355,-14354:pinyin="QIONG"nCase-14353To-14346:pinyin="QIU"nCase-14345To-14338,-14175To-14171:pinyin="QU"nCase-14170To-14160:pinyin="QUAN"nCase-14159To-14152:pinyin="QUE"nCase-14151,-14150:pinyin="QUN"nCase-14149To-14146:pinyin="RAN"nCase-14145To-14141:pinyin="RANG"nCase-14140To-14138:pinyin="RAO"nCase-14137,-14136:pinyin="RE"nCase-14135To-14126:pinyin="REN"nCase-14125,-14124:pinyin="RENG"nCase-14123:pinyin="RI"nCase-14122To-14113:pinyin="RONG"nCase-14112To-14110:pinyin="ROU"nCase-14109To-14100:pinyin="RU"nCase-14099,-14098:pinyin="RUAN"nCase-14097To-14095:pinyin="RUI"nCase-14094,-14093:pinyin="RUN"nCase-14092,-14091:pinyin="RUO"nCase-14090To-14088:pinyin="SA"nCase-14087To-14084:pinyin="SAI"nCase-14083,-14082,-13919,-13918:pinyin="SAN"nCase-13917To-13915:pinyin="SANG"nCase-13914To-13911:pinyin="SAO"nCase-13910To-13908:pinyin="SE"nCase-13907:pinyin="SEN"nCase-13906:pinyin="SENG"nCase-13905To-13897:pinyin="SHA"nCase-13896,-13895:pinyin="SHAI"nCase-13894To-13879:pinyin="SHAN"nCase-13878To-13871:pinyin="SHANG"nCase-13870To-13860:pinyin="SHAO"nCase-13859To-13848:pinyin="SHE"nCase-13847To-13832:pinyin="SHEN"nCase-13831To-13826,-13663To-13659:pinyin="SHENG"nCase-13658To-13612:pinyin="SHI"nCase-13611To-13602:pinyin="SHOU"nCase-13601To-13570,-13407:pinyin="SHU"nCase-13406,-13405:pinyin="SHUA"nCase-13404To-13401:pinyin="SHUAI"nCase-13400,-13399:pinyin="SHUAN"nCase-13398To-13396:pinyin="SHUANG"nCase-13395To-13392:pinyin="SHUI"nCase-13391To-13388:pinyin="SHUN"nCase-13387To-13384:pinyin="SHUO"nCase-13383To-13368:pinyin="SI"nCase-13367To-13360:pinyin="SONG"nCase-13359To-13356:pinyin="SOU"nCase-13355To-13344:pinyin="SU"nCase-13343To-13341:pinyin="SUAN"nCase-13340To-13330:pinyin="SUI"nCase-13329To-13327:pinyin="SUN"nCase-13326To-13319:pinyin="SUO"nCase-13318To-13314,-13151To-13148:pinyin="TA"nCase-13147To-13139:pinyin="TAI"nCase-13138To-13121:pinyin="TAN"nCase-13120To-13108:pinyin="TANG"nCase-13107To-13097:pinyin="TAO"nCase-13096:pinyin="TE"nCase-13095To-13092:pinyin="TENG"nCase-13091To-13077:pinyin="TI"nCase-13076To-13069:pinyin="TIAN"nCase-13068To-13064:pinyin="TIAO"nCase-13063To-13061:pinyin="TIE"nCase-13060To-13058,-12895To-12889:pinyin="TING"nCase-12888To-12876:pinyin="TONG"nCase-12875To-12872:pinyin="TOU"nCase-12871To-12861:pinyin="TU"nCase-12860,-12859:pinyin="TUAN"nCase-12858To-12853:pinyin="TUI"nCase-12852To-12850:pinyin="TUN"nCase-12849To-12839:pinyin="TUO"nCase-12838To-12832:pinyin="WA"nCase-12831,-12830:pinyin="WAI"nCase-12829To-12813:pinyin="WAN"nCase-12812To-12803:pinyin="WANG"nCase-12802,-12639To-12608:pinyin="WEI"nCase-12607To-12598:pinyin="WEN"nCase-12597To-12595:pinyin="WENG"nCase-12594To-12586:pinyin="WO"nCase-12585To-12557:pinyin="WU"nCase-12556To-12546,-12383To-12360:pinyin="XI"nCase-12359To-12347:pinyin="XIA"nCase-12346To-12321:pinyin="XIAN"nCase-12320To-12301:pinyin="XIANG"nCase-12300To-12290,-12127To-12121:pinyin="XIAO"nCase-12120To-12100:pinyin="XIE"nCase-12099To-12090:pinyin="XIN"nCase-12089To-12075:pinyin="XING"nCase-12074To-12068:pinyin="XIONG"nCase-12067To-12059:pinyin="XIU"nCase-12058To-12040:pinyin="XU"nCase-12039To-12034,-11871To-11868:pinyin="XUAN"nCase-11867To-11862:pinyin="XUE"nCase-11861To-11848:pinyin="XUN"nCase-11847To-11832:pinyin="YA"nCase-11831To-11799:pinyin="YAN"nCase-11798To-11782:pinyin="YANG"nCase-11781To-11778,-11615To-11605:pinyin="YAO"nCase-11604To-11590:pinyin="YE"nCase-11589To-11537,-8504:pinyin="YI"nCase-11536To-11522,-11359:pinyin="YIN"nCase-11358To-11341:pinyin="YING"nCase-11340:pinyin="YO"nCase-11339To-11325:pinyin="YONG"nCase-11324To-11305:pinyin="YOU"nCase-11304To-11266,-11103To-11098,-5980,-4390:pinyin="YU"nCase-11097To-11078:pinyin="YUAN"nCase-11077To-11068:pinyin="YUE"nCase-11067To-11056:pinyin="YUN"nCase-11055To-11053:pinyin="ZA"nCase-11052To-11046:pinyin="ZAI"nCase-11045To-11042:pinyin="ZAN"nCase-11041To-11039:pinyin="ZANG"nCase-11038To-11025:pinyin="ZAO"nCase-11024To-11021:pinyin="ZE"nCase-11020:pinyin="ZEI"nCase-11019:pinyin="ZEN"nCase-11018To-11015:pinyin="ZENG"nCase-11014To-11010,-10847To-10839:pinyin="ZHA"nCase-10838To-10833:pinyin="ZHAI"nCase-10832To-10816:pinyin="ZHAN"nCase-10815To-10801:pinyin="ZHANG"nCase-10800To-10791:pinyin="ZHAO"nCase-10790To-10781:pinyin="ZHE"nCase-10780To-10765,-10591:pinyin="ZHEN"nCase-10764To-10754,-10590To-10588:pinyin="ZHENG"nCase-10587To-10545:pinyin="ZHI"nCase-10544To-10534:pinyin="ZHONG"nCase-10533To-10520:pinyin="ZHOU"nCase-10519To-10498,-10335To-10332:pinyin="ZHU"nCase-10331,-10330:pinyin="ZHUA"nCase-10329:pinyin="ZHUAI"nCase-10328To-10323:pinyin="ZHUAN"nCase-10322To-10316:pinyin="ZHUANG"nCase-10315To-10310:pinyin="ZHUI"nCase-10309,-10308:pinyin="ZHUN"nCase-10307To-10297:pinyin="ZHUO"nCase-10296To-10282:pinyin="ZI"nCase-10281To-10275:pinyin="ZONG"nCase-10274To-10271:pinyin="ZOU"nCase-10270To-10263:pinyin="ZU"nCase-10262,-10261:pinyin="ZUAN"nCase-10260To-10257:pinyin="ZUI"nCase-10256,-10255:pinyin="ZUN"nCase-10254To-10247:pinyin="ZUO"nCase-5718:pinyin="nan"nCase-6483:pinyin="shu"nCase-5445:pinyin="hao"nCaseElse:pinyin=pnEndSelectnEndFunctionnFunctionxmpy(str)nFori=1ToLen(str)nxmpy=xmpy&""&pinyin(Mid(str,i,1))nIfi=1Thennxmpy=xmpy&""nEndIfnNextinEndFunctionn
保存后关闭编辑器
5、在功能区找到公式选项点击名称管理器，新建名称界面，名称输入"拼音",范围"人事资料",引用位置"=xmpy(人事资料！$b2)"后点确定。
6、单击单元格C2输入"=拼音"，向下拉完成。有一些特殊字的拼音还无法识别，需人工输入。
文章分享结束，chengreng和excel根据姓名自动输入拼音的答案你都知道了吗？欢迎再次光临本站哦！
