excel根据姓名自动输入拼音-pinpai

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

先打开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输入"=拼音"，向下拉完成。有一些特殊字的拼音还无法识别，需人工输入。