以一个实例说明在excel中根据A列汉字自动在B列生成拼音字母的方法
下图所示的excel表格，A列是姓名列，根据A列的汉字姓名，自动在B列和C列分别自动生成姓名列的拼音字母。下面是小编整理出来的详细的操作步骤，分享给有需要的朋友。
第一步，由于Excel中没有现成的函数套用可以解决此问题，首先自定义一个函数。方法如下：
1. 运行“工具→宏→Visual Basic编辑器”命令（或者直接按“Alt+F11”组合键），进入Visual Basic编辑状态。
2. 运行“插入→模块”命令，插入一个新模块。再双击插入的模块，进入模块代码编辑状态。
3. 将以下的代码输入其中，保存，关闭Visual Basic编辑窗口，返回Excel编辑状态。
Function PinYin(Hz As String)
Dim PinMa As String
Dim MyPinMa As Variant
Dim Temp As Integer, i As Integer, j As Integer
PinMa = "a,20319,ai,20317,an,20304,ang,20295,ao,20292,"
PinMa = PinMa & "ba,20283,bai,20265,ban,20257,bang,20242,bao,20230,bei,20051,ben,20036,beng,20032,bi,20026,bian,20002,biao,19990,bie,19986,bin,19982,bing,19976,bo,19805,bu,19784,"
PinMa = PinMa & "ca,19775,cai,19774,can,19763,cang,19756,cao,19751,ce,19746,ceng,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 & "da,19218,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,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,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,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,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,15416,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,14937,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,qian,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,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,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 & "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,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,11604,yi,11589,yin,11536,ying,11358,yo,11340,yong,11339,you,11324,yu,11303,yuan,11097,yue,11077,yun,11067,"
PinMa = 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,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
第二步，在空白列，比如F2单元格，输入公式：=PinYin(A2)，就可以在F2单元格得到A2单元格汉字的拼音。然后下拉复制完成A列所有的汉字转拼音。
提示：公式中的“PinYin”就是第一步自定义函数的名称。
第三步，在B2单元格输入公式：=LEFT(F2,FIND(" ",F2)-1)，就可以提取出F2单元格中的第一个汉字的拼音。然后下拉复制完成A列中汉字“姓”的拼音提取。
第四步，在C2单元格输入公式：=TRIM(SUBSTITUTE(F2,B2,""))，即可得到剩余的“名”字部分的汉字的拼音。下拉复制公式。
第五步，如果需要拼音的首字母大写，可以再用PROPER函数来转换一下。最终完成的效果如下图所示。
绛旓細寮骞虫柟锛氳緭鍏ュ叕寮=SQRT(A3)锛堝鍥撅級锛涚偣鍑籩nter閿嵆鍙樉绀哄紑骞虫柟鍊硷紙濡傚浘锛夛紱杩樻湁鍙︿釜鏂规硶寮骞虫柟锛氳緭鍏ュ叕寮=A3*锛1/2锛夛紝鐐瑰嚮enter閿嵆鍙紙濡傚浘锛夛紱寮涓夋鏂癸細杈撳叆鍏紡=A3*锛1/3锛夛紝鐐瑰嚮enter閿嵆鍙紙濡傚浘锛夛紱寮鍥涙鏂癸細杈撳叆鍏紡=A3*锛1/4锛夛紝鐐瑰嚮enter閿嵆鍙紙濡傚浘锛夈
绛旓細涓嬮潰灏嗕妇瀹炰緥鏉璇存槑鍦‥xcel涓濡備綍浣跨敤璁＄畻鍏徃鏉ュ紑鏍瑰彿 鍋囪瑕佹眰16鐨勫钩鏂规牴锛屽湪Excel琛ㄦ牸涓緭鍏ュ叕寮忥細=SQRT(A2)锛堝鍥撅級锛涜緭鍏ュ叕寮忓悗鎸変笅enter閿嵆鍙緱鍑虹粨鏋滀负4 Excel鍏紡锛=SQRT(A2)鍙傜敤浜庡紑骞虫柟鏍癸紝鍦ㄥ疄闄呭簲鐢ㄤ腑锛屾垜浠繕闇瑕佸紑绔嬫柟鏍癸紝寮鍥涙鏂规牴锛屼簲娆℃柟鏍圭瓑锛屽洜姝ゆ垜浠篃鍙互鐢ㄥ叕寮忥細=A2^(1/2...
绛旓細1銆=SQRT(A1)2銆=POWER(A1,1/2)3銆=A1^(1/2)娉ㄦ剰锛氱涓绉嶅彧寮骞虫柟鏍癸紝绗2銆3涓紝鍙互寮鍏朵粬娆℃柟锛屾瘮濡傚紑5娆℃柟鏍癸紝鍒欑敤1/5浠ｆ浛1/2
绛旓細渚嬪锛岃璁＄畻鏁板瓧9鐨勫钩鏂规牴锛屽彲浠ュ湪涓涓崟鍏冩牸涓緭鍏モ=SQRT(9)鈥濓紝鐒跺悗鎸塃nter閿紝缁撴灉浼氭樉绀轰负3銆2. 閫氳繃瀹炰緥鏉ョ湅鍏蜂綋濡備綍浣跨敤锛氬亣璁鏈変竴涓鏁版嵁闆嗭紝鍖呭惈浜嗕竴浜涙暟瀛楋紝浣犳兂瑕佽绠楄繖浜涙暟瀛楃殑骞虫柟鏍广傞鍏堬紝鍦‥xcel涓鎵撳紑涓涓柊鐨勫伐浣滅翱锛屽苟杈撳叆涓浜涙暟瀛椼傜劧鍚庯紝鍦ㄧ浉閭荤殑鍗曞厓鏍间腑锛屼娇鐢⊿QRT鍑芥暟鏉ヨ绠楁瘡...
绛旓細閭ｅ氨鏄湪鍑芥暟鏍忚緭鍏モ=锛堟暟鍊兼垨琛ㄨ揪寮忥級^(1/寮鏂规暟)鈥濓紝瀹炴搷濡備笅锛氭柊寤涓涓狤xcel琛ㄦ牸锛屼负浜嗘柟渚挎紨绀哄苟杈撳叆涓瀹氭暟鎹紝濡備笅鍥俱備娇鐢‥xcel鑷甫鐨勫嚱鏁板叕寮忥細骞虫柟鏍圭殑鍏紡鏄疭QRT()锛屽亣璁炬暟鍊兼槸鍦ˋ1鍗曞厓鏍硷紝鍦˙1鍗曞厓鏍间腑杈撳叆浠ヤ笅鍏紡鈥=SQRT(A1)鈥濓紝鎸夊洖杞﹂敭纭鍚庡嵆鍙绠楀嚭A1鏁版嵁鐨勫钩鏂规牴銆
绛旓細鍦 Excel 涓锛屽彲浠ヤ娇鐢 SQRT 鍑芥暟鏉ヨ绠涓涓鏁扮殑骞虫柟鏍广備互涓嬫槸浣跨敤 SQRT 鍑芥暟鏉ュ紑鏍瑰彿鐨勬楠わ細閫夋嫨涓涓崟鍏冩牸銆傚湪閫夊畾鐨勫崟鍏冩牸涓緭鍏モ=SQRT(鏁板瓧)鈥濆叕寮忥紝鍏朵腑鈥滄暟瀛椻濅唬琛ㄨ璁＄畻骞虫柟鏍圭殑鏁板笺傛寜涓嬧淓nter鈥濋敭鎴栧崟鍑诲叕寮忔爮涓殑鈥溾垰鈥濇寜閽互璁＄畻骞虫柟鏍广備緥濡傦紝濡傛灉瑕佽绠楁暟瀛 16 鐨勫钩鏂规牴锛屽彲浠ュ湪...
绛旓細鍦‥xcel閲岄潰杩涜寮鏍瑰彿鐨勬搷浣滐紝鍏跺疄鏄潪甯哥畝鍗曚笖鐩存帴鐨勩侲xcel鎻愪緵浜嗕赴瀵岀殑鏁板鍑芥暟渚涚敤鎴蜂娇鐢紝鍏朵腑“SQRT”鍑芥暟灏辨槸涓撻棬鐢ㄦ潵璁＄畻涓涓暟鍊肩殑骞虫柟鏍圭殑銆傚叿浣撲娇鐢ㄦ楠ゅ涓嬶細1. 棣栧厛锛屾墦寮浣犵殑Excel琛ㄦ牸锛屽苟鎵惧埌浣犲笇鏈涜繘琛屽紑鏍瑰彿璁＄畻鐨勫崟鍏冩牸銆傚亣璁句綘鏈変竴涓鏁板煎湪A1鍗曞厓鏍硷紝姣斿9銆2. 鎺ョ潃锛屽湪...
绛旓細1銆佹柊寤涓涓狤xcel琛ㄦ牸锛屼负浜嗘柟渚挎紨绀哄苟杈撳叆涓瀹氭暟鎹紝濡備笅鍥俱2銆佸亣璁炬垜浠鍦―1澶勮绠楀嚭鈥淎1+B1鐨勫拰鎷挎潵寮2娆℃柟鐨勭粨鏋溾濓紝閭ｄ箞閫変腑D1鍗曞厓鏍硷紝濡備笅鍥俱3銆佸湪涓婃柟鍑芥暟鏍忚緭鍏モ=(A1+B1)^(1/2)鈥濓紝鐒跺悗鎸夊洖杞﹂敭灏变細鍑虹粨鏋滀簡锛屽涓嬪浘銆4銆佸紑鍑犳鏂瑰氨鎶婂紑鏂规暟鏀规垚鍑狅紝姣斿鎶婁笂涓姝ユ敼鎴愬紑4...
绛旓細Excel涓杈撳叆鏍瑰彿甯哥敤鐨勬柟娉曟湁涓ょ锛屽叿浣撴搷浣滃涓嬶細1銆佹墦寮Excel锛岀偣鍑绘彃鍏ワ紝鍦ㄧ鍙蜂腑閫夋嫨鏁板瓧杩愮畻绗︼紝鍑虹幇鈥溾垰鈥濋夋嫨鎻掑叆銆2銆佹墦寮杈撳叆娉曡蒋閿洏锛堜互璁涓轰緥锛夛紝鐐瑰嚮璁剧疆锛岄夋嫨鐗规畩绗﹀彿杈撳叆锛屽湪杩愮畻/鍗曚綅涓壘鍒扳溾垰鈥濓紝鐐瑰嚮鎻掑叆鍗冲彲銆
绛旓細鍦╓ord涓緭鍏ユ牴鍙锋湁涓揩鎹锋柟寮忥細鍚屾椂鎸変笅Ctrl鍜孎9閿紝鐒跺悗杈撳叆"eq绌烘牸(x,y)"銆傝繖閲岀殑"eq"浠ｈ〃鏁板鍏紡锛"x,y"鏄牴鍙蜂笅闇瑕佺殑鏁板笺傚畬鎴愯緭鍏ュ悗锛屽埆蹇樹簡鎸塅9閿‘璁ゅ苟搴旂敤銆傛敞鎰忥紝绌烘牸閿湪鏁翠釜杩囩▼涓槸蹇呬笉鍙皯鐨勶紝瀹冪敤浜庡垎闅斿叕寮忓拰鏁板笺備互涓婂氨鏄牴鍙疯緭鍏ョ殑鍏ㄨ繃绋嬶紝甯屾湜杩欎釜灏忔妧宸ц兘甯姪浣鍦‥xcel...
