【最新整理，下载后即可编辑】
HR人士要掌握的EXCEL汉字转拼音方法及步骤
HR人士在工作中管理员工的数据，如果是在欧美外企，因为国外总部通常会需要将汉字员工的信息转换成拼音，将这些信息录入存储在人事系统中，或者以人事报表形式将拼音信息汇报给总部。因为员工数据通常会用EXCEL统计，掌握EXCEL汉字转拼音，会大大提高办公效率。
最常用的操作是员工姓名，家属姓名汉字转拼音等。下面以名字的转换为例子， 介绍EXCEL中汉字转拼音方法的两个主要步骤：
* 在EXCEL中设定汉字转拼音的自定义函数 = Pinyin().
* 通过EXCEL中自带函数 与 自己设定的汉字转拼音自定义函数组合，将目标汉字转换成拼音，再复制粘贴保存到合适位置。
Excel中设定自定义函数= Pinyin().的步骤：
1.新建一个新的 Excel文件，打开显示出工作表； 2.按“Alt+F11”组合键，进入Visual Basic编辑状态； 3.再按“F7”键，进入模块代码编辑状态；
4.将下面的代码，复制粘贴到模块代码编辑中；
Function PinYin(Hz As String) Dim PinMa As String Dim MyPinMa As Variant
Dim Temp As Integer, i As Integer, j As Integer
PinMa = \PinMa = PinMa &
\en,20036,beng,20032,bi,20026,bian,20002,biao,19990,bie,19986,bi
【最新整理，下载后即可编辑】
n,19982,bing,19976,bo,19805,bu,19784,\PinMa = PinMa &
\ng,19741,cha,19739,chai,19728,chan,19725,chang,19715,chao,19540,che,19531,chen,19525,cheng,19515,chi,19500,chong,19484,chou,19479,chu,19467,chuai,19289,chuan,19288,chuang,19281,chui,19275,chun,19270,chuo,19263,ci,19261,cong,19249,cou,19243,cu,19242,cuan,19238,cui,19235,cun,19227,cuo,19224,\PinMa = PinMa &
\eng,19003,di,18996,dian,18977,diao,18961,die,18952,ding,18783,diu,18774,dong,18773,dou,18763,du,18756,duan,18741,dui,18735,dun,18731,duo,18722,\
PinMa = PinMa & \PinMa = PinMa &
\,18448,fou,18447,fu,18446,\PinMa = PinMa &
\,18184,gen,18183,geng,18181,gong,18012,gou,17997,gu,17988,gua,17970,guai,17964,guan,17961,guang,17950,gui,17947,gun,17931,guo,17928,\
PinMa = PinMa &
\ei,17703,hen,17701,heng,17697,hong,17692,hou,17683,hu,17676,hua,17496,huai,17487,huan,17482,huang,17468,hui,17454,hun,17433,huo,17427,\PinMa = PinMa &
\6915,jing,16733,jiong,16708,jiu,16706,ju,16689,juan,16664,jue,16657,jun,16647,\PinMa = PinMa &
【最新整理，下载后即可编辑】
\n,16433,keng,16429,kong,16427,kou,16423,ku,16419,kua,16412,kuai,16407,kuan,16403,kuang,16401,kui,16393,kun,16220,kuo,16216,\
PinMa = PinMa &
\169,leng,16158,li,16155,lia,15959,lian,15958,liang,15944,liao,15933,lie,15920,lin,15915,ling,15903,liu,15889,long,15878,lou,15707,lu,15701,lv,15681,luan,15667,lue,15661,lun,15659,luo,15652,\PinMa = PinMa &
\36,mei,15435,men,15419,meng,15416,mi,15408,mian,15394,miao,15385,mie,15377,min,15375,ming,15369,miu,15363,mo,15362,mou,15183,mu,15180,\PinMa = PinMa &
\ei,15143,nen,15141,neng,15140,ni,15139,nian,15128,niang,15121,niao,15119,nie,15117,nin,15110,ning,15109,niu,14941,nong,14937,nu,14933,nv,14930,nuan,14929,nue,14928,nuo,14926,\PinMa = PinMa & \PinMa = PinMa &
\en,14873,peng,14871,pi,14857,pian,14678,piao,14674,pie,14670,pin,14668,ping,14663,po,14654,pu,14645,\PinMa = PinMa &
\qin,14379,qing,14368,qiong,14355,qiu,14353,qu,14345,quan,14170,que,14159,qun,14151,\PinMa = PinMa &
\,14123,rong,14122,rou,14112,ru,14109,ruan,14099,rui,14097,run,14094,ruo,14092,\
【最新整理，下载后即可编辑】
PinMa = PinMa &
\13907,seng,13906,sha,13905,shai,13896,shan,13894,shang,13878,shao,13870,she,13859,shen,13847,sheng,13831,shi,13658,shou,13611,shu,13601,shua,13406,shuai,13404,shuan,13400,shuang,13398,shui,13395,shun,13391,shuo,13387,si,13383,song,13367,sou,13359,su,13356,suan,13343,sui,13340,sun,13329,suo,13326,\PinMa = PinMa &
\13095,ti,13091,tian,13076,tiao,13068,tie,13063,ting,13060,tong,12888,tou,12875,tu,12871,tuan,12860,tui,12858,tun,12852,tuo,12849,\
PinMa = PinMa &
\7,weng,12597,wo,12594,wu,12585,\PinMa = PinMa &
\xin,12099,xing,12089,xiong,12074,xiu,12067,xu,12058,xuan,12039,xue,11867,xun,11861,\PinMa = PinMa &
\11536,ying,11358,yo,11340,yong,11339,you,11324,yu,11303,yuan,11097,yue,11077,yun,11067,\PinMa = PinMa &
\,11020,zen,11019,zeng,11018,zha,11014,zhai,10838,zhan,10832,zhang,10815,zhao,10800,zhe,10790,zhen,10780,zheng,10764,zhi,10587,zhong,10544,zhou,10533,zhu,10519,zhua,10331,zhuai,10329,zhuan,10328,zhuang,10322,zhui,10315,zhun,10309,zhuo,10307,zi,10296,zong,10281,zou,10274,zu,10270,zuan,10262,zui,10260,zun,10256,zuo,10254\
MyPinMa = Split(PinMa, \
【最新整理，下载后即可编辑】
For i = 1 To Len(Hz)
Temp = Asc(Mid(Hz, i, 1)) If Temp < 0 Then Temp = Abs(Temp) For j = 791 To 1 Step -2
If Temp <= Val(MyPinMa(j)) Then
PinYin = PinYin & MyPinMa(j - 1) & \ Exit For End If Next End If Next
PinYin = Trim(PinYin) End Function
5. 点击右上角的模块编辑十字叉，关闭Visual Basic编辑窗口，再点击左上角的 EXCEL图标，返回Excel编辑状态；自定义函数：=PinYin() 在EXCEL中就可以应用了。
【最新整理，下载后即可编辑】
HR人士要掌握的EXCEL汉字转拼音方法及步骤(精品资料).doc
