HR人士要掌握的EXCEL汉字转拼音方法及步骤

1、HR 人士要掌握的 EXCEL 汉字转拼音方法及步骤HR 人士在工作中管理员工的数据，如果是在欧美外企，因为国外总部通常会需 要将汉字员工的信息转换成拼音， 将这些信息录入存储在人事系统中， 或者以人 事报表形式将拼音信息汇报给总部。因为员工数据通常会用 EXCEL 统计，掌握 EXCEL 汉字转拼音，会大大提高办公效率。最常用的操作是员工姓名，家属姓名汉字转拼音等。下面以名字的转换为例子，介绍 EXCEL 中汉字转拼音方法的两个主要步骤：* 在 EXCEL 中设定汉字转拼音的自定义函数 = Pinyin().* 通过 EXCEL 中自带函数 与 自己设定的汉字转拼音自定义函数组合，将目标 汉

2、字转换成拼音，再复制粘贴保存到合适位置。Excel 中设定自定义函数 = Pinyin(). 的步骤：1.新建一个新的 Excel 文件，打开显示出工作表；2. 按“Alt+F11 ”组合键，进入Visual Basic编辑状态;3. 再按“F7”键，进入模块代码编辑状态；4 .将下面的代码，复制粘贴到模块代码编辑中;Function PinYin(Hz As String)Dim PinMa As StringDim MyPinMa As VariantDim Temp As Integer, i As Integer, j As IntegerPinMa = a,20319,ai,2031

3、7,an,20304,ang,20295,ao,20292, PinMa = PinMa & ba,20283,bai,20265,ban,20257,bang,20242,bao,20230,bei,20051,ben,20036,ben g,20032,bi,20026,bian,20002,biao,19990,bie,19986,bin,19982,bing,19976,bo,198 05,bu,19784,PinMa = PinMa &ca,19775,cai,19774,can,19763,cang,19756,cao,19751,ce,19746,ceng,19741,cha,1

4、9739,chai,19728,chan,19725,chang,19715,chao,19540,che,19531,chen,19525,c heng,19515,chi,19500,chong,19484,chou,19479,chu,19467,chuai,19289,chuan,19 288,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 & da,19

5、218,dai,19212,dan,19038,dang,19023,dao,19018,de,19006,deng,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 & e,18710,en,18697,er,18696,PinMa = PinMa & fa,18526,fan,18518,fang,18501,fei,18490,fen,

6、18478,feng,18463,fo,18448,fou,18447,fu,18446,PinMa = PinMa & ga,18239,gai,18237,gan,18231,gang,18220,gao,18211,ge,18201,gei,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 & ha,17922,hai,17759,han,17752,

7、hang,17733,hao,17730,he,17721,hei,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 &ji,17417,jia,17202,jian,17185,jiang,16983,jiao,16970,jie,16942,jin,16915,jing,16733,jiong,16708,jiu,16706,ju,16689,juan,

8、16664,jue,16657,jun,16647,PinMa = PinMa &ka,16474,kai,16470,kan,16465,kang,16459,kao,16452,ke,16448,ken,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 & la,16212,lai,16205,lan,16202,lang,16187,lao,16180,le,16171,

9、lei,16169,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 & ma,15640,mai,15631,man,15625,mang,15454,mao,15448,me,15436,mei,15435, men,15419,meng,1541

10、6,mi,15408,mian,15394,miao,15385,mie,15377,min,15375, ming,15369,miu,15363,mo,15362,mou,15183,mu,15180,PinMa = PinMa & na,15165,nai,15158,nan,15153,nang,15150,nao,15149,ne,15144,nei,15143,nen,15141,neng,15140,ni,15139,nian,15128,niang,15121,niao,15119,nie,15117,nin,15110,ning,15109,niu,14941,nong,14

11、937,nu,14933,nv,14930,nuan,14929,nue,14928,nuo,14926,PinMa = PinMa & o,14922,ou,14921,PinMa = PinMa & pa,14914,pai,14908,pan,14902,pang,14894,pao,14889,pei,14882,pen,14873,peng,14871,pi,14857,pian,14678,piao,14674,pie,14670,pin,14668,ping,14663,po,14654,pu,14645,PinMa = PinMa & qi,14630,qia,14594,qi

12、an,14429,qiang,14407,qiao,14399,qie,14384,qin,14379,qing,14368,qiong,14355,qiu,14353,qu,14345,quan,14170,que,14159,qun,14151, PinMa = PinMa & ran,14149,rang,14145,rao,14140,re,14137,ren,14135,reng,14125,ri,14123,rong,14122,rou,14112,ru,14109,ruan,14099,rui,14097,run,14094,ruo,14092,PinMa = PinMa &sa

13、,14090,sai,14087,san,14083,sang,13917,sao,13914,se,13910,sen,13907,seng, 13906,sha,13905,shai,13896,shan,13894,shang,13878,shao,13870,she,13859,sh en,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,1 336

14、7,sou,13359,su,13356,suan,13343,sui,13340,sun,13329,suo,13326, PinMa = PinMa & ta,13318,tai,13147,tan,13138,tang,13120,tao,13107,te,13096,teng,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 & wa,12838,wai

15、,12831,wan,12829,wang,12812,wei,12802,wen,12607,weng,12597, wo,12594,wu,12585,PinMa = PinMa & xi,12556,xia,12359,xian,12346,xiang,12320,xiao,12300,xie,12120,xin,12099,xing, 12089,xiong,12074,xiu,12067,xu,12058,xuan,12039,xue,11867,xun,11861,PinMa = PinMa &ya,11847,yan,11831,yang,11798,yao,11781,ye,1

16、1604,yi,11589,yin,11536,ying,11358,yo,11340,yong,11339,you,11324,yu,11303,yuan,11097,yue,11077,yun,11067HPinMa = PinMa & za,11055,zai,11052,zan,11045,zang,11041,zao,11038,ze,11024,zei,11020,zen,11019,zeng,11018,zha,11014,zhai,10838,zhan,10832,zhang,10815,zhao,10800,zhe,10790,zhen,10780,zheng,10764,z

17、hi,10587,zhong,10544,zhou,10533,zhu,10519 ,zhua,10331,zhuai,10329,zhuan,10328,zhuang,10322,zhui,10315,zhun,10309,zhu o,10307,zi,10296,zong,10281,zou,10274,zu,10270,zuan,10262,zui,10260,zun,102 56,zuo,10254MyPinMa = Split(PinMa, ,)For i = 1 To Len(Hz)Temp = Asc(Mid(Hz, i, 1)If Temp 0 ThenTemp = Abs(T

18、emp)For j = 791 To 1 Step -2If Temp = Val(MyPinMa(j) ThenPinYin = PinYin & MyPinMa(j - 1) & Exit ForEnd IfNextEnd IfNextPinYin = Trim(PinYin)End Function1 Biiair Bnokl0 VicTpfrn-ft鼻世范盘购阪IIkgL 方*7h：3Vorli-DokHfidi外捋程序 s口迪fiftewa妙行町-列匚.&冈*A(U棺式辺调试创话行JJ J $ 2r 当血 JJ 口血 1 -1roRcli OH ririTin.(i Ax Str:

19、nr) tb PiTfli A? Si.rLPc 旳肝出.h V世i沁I Di* Imp Az IdIc Etr haUx = - ficJli Fa nil-1 = fidiL = 打 cJk - FaMI = mm - FjcJk = Fba1 = 珀 cJU ranfl-i = PbcJfl-i.= FjcJU =比*4 =FjpJk = 剋1九=FjcJli FjeJU =mm F価4 = FjoH.=Fl Dili = F*yf t-nWft tie i =_.T中叩 r A.SC tlidOf=. l, I) 歩丄 _i_ i A.X Inteper j As LuttcerHF

20、HBCH.皿 ffi96j 神和9Finfk 1 %2Dea 気 ba打 aa耳 t 皿 so 瓯r jst sa 烈込.Zd, smso.htn早 swe上务 eHeJIi 4 *!：. ISfTTS, c JflTTA. CUI. LATH., cult IFTS-g LffTSl. citcU 恂T3S_i：気巧 rlk 4 血扌G J他jP-d皿 L9D3a,1023 do. 19013 da 河DO也1900?牡八的第,！Finfk Jk *t. Lane, JB&flL -, LS6%.Pa rJla A 血SK电 g I怨】电f mt】蒯L.他.I8蒯-fv厲耳阳杓匚1刖63红】

21、枫电如用M3轨0 Firlk 1 ALMaa. ji-ft, LSMlQ,ji-,ta94. jitJ6915JittiaiJ3, jFinik M虫JEQTgk 皿1初眄”血比Le45fl.lc.L64S2. c4.ICUB.kui,LfidSl 1比 IE42flkca电FiiJU A*1.K1 jeans, lui.L6HE4mCL6eo4,I&L7L Ita.LELMjmt l6L5fl,2i, 6巧rJk 4恪xJBfiqDj皿L5E5,ne：L5i5J,ftD.L5a4fl,暫打L別35,僧15flL9. 汕石 LF】nfk *ii5l65.nu.巧LHnigL5153.两l5L5

22、.ur.1514?.l5L44.ui.L5143.twi. iSKl. itnc LhZ* A 5冷9叫】 riiJli 14914, ppi J4如屯卩皿 L49(pm&Le9. L, 1436. pin, l-lffTS, pant ISTL. pi. LFwlli A ii.H630.ii4, M5gi.qiuUq429_iLQL LAflDfT.五g M3W打 丄&帀, 14JTE 叱丄開，切 fb 鼻九 A *Ff t-lJ 怕k L-tt-i? vAj 1-l4Clj y*, 1-4 L3Tp 尸询Ld 込 *ii L-4t2S ri J 41% z 忸 J 42 v 叫 Finf

23、k iL4KX. sut13917, so.L3flL4.=中.门9汕小d LSffifT.=切 I39M.止丄 LFjpIU a13315.门1391215.ISLSO.L31CTT. jaMB, lit 3tH5. ti. L309. taJL LF, E-Jfli 4叫却朗3巳 I2eai, * 皿L2BM,l2Sl2, vtt.12802, z, ISAVI,皿血印 IHST v片吃59d晒r：iJ14 4 *HiJES6.ML4, l235B.iciui.lKI6. MLint LZ330. ziio. LSM.Hit. iZlZO. lict IZDflB. Jtmt IZOEfl. i