在人事管理中经常遇到需要输入姓名拼音，手工输入容易拼写错误，今天教大家用自定义函数直接输入简单方便。
先打开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 -1电脑9467 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 SelectEnd FunctionFunction xmpy(str)For i = 1 To Len(str)xmpy = xmpy & \"\" & pinyin(Mid(str, i, 1))If i = 1 Thenxmpy = xmpy & \" \"End IfNext iEnd Function
保存后关闭编辑器
在功能区找到公式选项点击名称管理器，新建名称界面，名称输入\"拼音\",范围\"人事资料\",引用位置\"=xmpy(人事资料！$b2)\"后点确定。
单击单元格C2输入\"=拼音\"，向下拉完成。有一些特殊字的拼音还无法识别，需人工输入。
电脑 电脑