excel怎么把姓名转为拼音（excel根据姓名自动输入拼音）

excel怎么把姓名转为拼音（excel根据姓名自动输入拼音）在功能区找到公式选项点击名称管理器，新建名称界面，名称输入"拼音" 范围"人事资料" 引用位置"=xmpy(人事资料！$b2)"后点确定。单击单元格C2输入"=拼音"，向下拉完成。有一些特殊字的拼音还无法识别，需人工输入。

在人事管理中经常遇到需要输入姓名拼音，手工输入容易拼写错误，今天教大家用自定义函数直接输入简单方便。

先打开excel选项找到自定义功能区勾选开发工具点确定

在功能区点击开发工具打开VBA编辑器，插入模板后复制代码粘贴到模块中，

Function pinyin(p As String) As String i = Asc(p) Select Case i Case -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 -20226 -20063 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 -19970 -19807 -19806: pinyin = "BING" Case -19805 To -19787: pinyin = "BO" Case -19786 To -19776: pinyin = "BU" Case -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 = "CAO" 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 -19714 -19551 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 -19458 -19295 To -19290: pinyin = "CHU" Case -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 -19262: pinyin = "CHUO" Case -19261 To -19250: pinyin = "CI" Case -19249 To -19244: pinyin = "CONG" Case -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 -19202 -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 -18946: pinyin = "DIE" Case -18783 To -18775: pinyin = "DING" Case -18774: pinyin = "DIU" Case -18773 To -18764: pinyin = "DONG" Case -18763 To -18756: pinyin = "DOU" Case -18755 To -18742: pinyin = "DU" Case -18741 To -18736: pinyin = "DUAN" Case -18735 To -18732: pinyin = "DUI" Case -18731 To -18723: pinyin = "DUN" Case -18722 To -18711: pinyin = "DUO" Case -18710 To -18698: pinyin = "E" Case -18697: pinyin = "EN" Case -18696 To -18690 -18527: pinyin = "ER" 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: pinyin = "FOU" Case -18446 To -18434 -18271 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: pinyin = "GEI" Case -18183 To -18182: pinyin = "GEN" Case -18181 To -18178 -18015 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: 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 -17666 -17503 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 -17410 -17247 To -17203: pinyin = "JI" Case -17202 To -17186: pinyin = "JIA" Case -17185 To -17162 -17160 To -17154 -16991 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 -16898 -16735 -16734: pinyin = "JIN" Case -16733 To -16709: pinyin = "JING" Case -16708 -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 -16642 -16479 To -16475: pinyin = "JUN" Case -16474 To -16471: pinyin = "KA" Case -16470 To -16466: pinyin = "KAI" Case -17161 -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 -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 -16402: pinyin = "KUAN" Case -16401 To -16394: pinyin = "KUANG" Case -16393 To -16386 -16223 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 -16170: pinyin = "LE" Case -16169 To -16159: pinyin = "LEI" Case -16158 To -16156: pinyin = "LENG" Case -16155 To -16130 -15967 To -15960: pinyin = "LI" Case -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 -15874 -15711 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 -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 -15618 -15455: pinyin = "MAN" Case -15454 To -15449: pinyin = "MANG" Case -15448 To -15437: pinyin = "MAO" Case -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: pinyin = "MIU" Case -15362 -15199 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: pinyin = "NANG" Case -15149 To -15145: pinyin = "NAO" Case -15144: pinyin = "NE" Case -15143 -15142: pinyin = "NEI" Case -15141: pinyin = "NEN" Case -15140: pinyin = "NENG" Case -15139 To -15129: pinyin = "NI" Case -15128 To -15122: pinyin = "NIAN" Case -15121 -15120: pinyin = "NIANG" Case -15119 -15118: pinyin = "NIAO" Case -15117 To -15111: pinyin = "NIE" Case -15110: pinyin = "NIN" Case -15109 To -15106 -14943 -14942: pinyin = "NING" Case -14941 To -14938: pinyin = "NIU" Case -14937 To -14934: pinyin = "NONG" Case -14933 To -14931: pinyin = "NU" Case -14930: pinyin = "NV" Case -14929: pinyin = "NUAN" Case -14928 -14927: pinyin = "NUE" Case -14926 To -14923: pinyin = "NUO" Case -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 -14872: pinyin = "PEN" Case -14871 To -14858: pinyin = "PENG" Case -14857 To -14850 -14687 To -14679: pinyin = "PI" Case -14678 To -14675: pinyin = "PIAN" Case -14674 To -14671: pinyin = "PIAO" Case -14670 -14669: pinyin = "PIE" Case -14668 To -14664: pinyin = "PIN" Case -14663 To -14655: pinyin = "PING" Case -14654 To -14647: pinyin = "PO" Case -14646: pinyin = "POU" Case -14645 To -14631: pinyin = "PU" Case -14630 To -14595 -6151: pinyin = "QI" Case -14594 -14431 -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 -14354: pinyin = "QIONG" Case -14353 To -14346: pinyin = "QIU" Case -14345 To -14338 -14175 To -14171: pinyin = "QU" Case -14170 To -14160: pinyin = "QUAN" Case -14159 To -14152: pinyin = "QUE" Case -14151 -14150: pinyin = "QUN" Case -14149 To -14146: pinyin = "RAN" Case -14145 To -14141: pinyin = "RANG" Case -14140 To -14138: pinyin = "RAO" Case -14137 -14136: pinyin = "RE" Case -14135 To -14126: pinyin = "REN" Case -14125 -14124: pinyin = "RENG" Case -14123: pinyin = "RI" Case -14122 To -14113: pinyin = "RONG" Case -14112 To -14110: pinyin = "ROU" Case -14109 To -14100: pinyin = "RU" Case -14099 -14098: pinyin = "RUAN" Case -14097 To -14095: pinyin = "RUI" Case -14094 -14093: pinyin = "RUN" Case -14092 -14091: pinyin = "RUO" Case -14090 To -14088: pinyin = "SA" Case -14087 To -14084: pinyin = "SAI" Case -14083 -14082 -13919 -13918: pinyin = "SAN" Case -13917 To -13915: pinyin = "SANG" Case -13914 To -13911: pinyin = "SAO" Case -13910 To -13908: pinyin = "SE" Case -13907: pinyin = "SEN" Case -13906: pinyin = "SENG" Case -13905 To -13897: pinyin = "SHA" Case -13896 -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 -13826 -13663 To -13659: pinyin = "SHENG" Case -13658 To -13612: pinyin = "SHI" Case -13611 To -13602: pinyin = "SHOU" Case -13601 To -13570 -13407: pinyin = "SHU" Case -13406 -13405: pinyin = "SHUA" Case -13404 To -13401: pinyin = "SHUAI" Case -13400 -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 -13356: pinyin = "SOU" Case -13355 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 -13314 -13151 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: 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 -13058 -12895 To -12889: pinyin = "TING" Case -12888 To -12876: pinyin = "TONG" Case -12875 To -12872: pinyin = "TOU" Case -12871 To -12861: pinyin = "TU" Case -12860 -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 -12830: pinyin = "WAI" Case -12829 To -12813: pinyin = "WAN" Case -12812 To -12803: pinyin = "WANG" Case -12802 -12639 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 -12546 -12383 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 -12290 -12127 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 -12034 -11871 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 -11778 -11615 To -11605: pinyin = "YAO" Case -11604 To -11590: pinyin = "YE" Case -11589 To -11537 -8504: pinyin = "YI" Case -11536 To -11522 -11359: pinyin = "YIN" Case -11358 To -11341: pinyin = "YING" Case -11340: pinyin = "YO" Case -11339 To -11325: pinyin = "YONG" Case -11324 To -11305: pinyin = "YOU" Case -11304 To -11266 -11103 To -11098 -5980 -4390: 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: pinyin = "ZEI" Case -11019: pinyin = "ZEN" Case -11018 To -11015: pinyin = "ZENG" Case -11014 To -11010 -10847 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 -10591: pinyin = "ZHEN" Case -10764 To -10754 -10590 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 -10498 -10335 To -10332: pinyin = "ZHU" Case -10331 -10330: pinyin = "ZHUA" Case -10329: pinyin = "ZHUAI" Case -10328 To -10323: pinyin = "ZHUAN" Case -10322 To -10316: pinyin = "ZHUANG" Case -10315 To -10310: pinyin = "ZHUI" Case -10309 -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 -10261: pinyin = "ZUAN" Case -10260 To -10257: pinyin = "ZUI" Case -10256 -10255: pinyin = "ZUN" Case -10254 To -10247: pinyin = "ZUO" Case -5718: pinyin = "nan " Case -6483: pinyin = "shu " Case -5445: pinyin = "hao " Case Else:pinyin = p End Select End Function Function xmpy(str) For i = 1 To Len(str) xmpy = xmpy & "" & pinyin(Mid(str i 1)) If i = 1 Then xmpy = xmpy & " " End If Next i End Function

保存后关闭编辑器

在功能区找到公式选项点击名称管理器，新建名称界面，名称输入"拼音" 范围"人事资料" 引用位置"=xmpy(人事资料！$b2)"后点确定。

单击单元格C2输入"=拼音"，向下拉完成。有一些特殊字的拼音还无法识别，需人工输入。