参考文献：
1.首字母大写：/thread-168029-1-1.html（里面下载一个excel，里面有宏）
中文转拼音：
2./s/blog_4bca87330100n8c3.html（之前用过可行）
3./1706.html（这个博主说上面的不能完美转换，找了另外的）
操作过程
参考文献1中的函数
普通输出：=chinese2spell(A2,1) //参数为1
首字母大写：=chinese2spell(A2,0) //参数为0
去除空格：=SUBSTITUTE(chinese2spell(A2,0)," ","")
1 Public Function Chinese2Spell(sChinese As String, Optional iflag As Integer = 0) As String 2 Dim C2S As String 3 Set d = CreateObject("Scripting.Dictionary") 4 d.Add "a", -20319 5 d.Add "ai", -20317 6 d.Add "an", -20304 7 d.Add "ang", -20295 8 d.Add "ao", -20292 9 d.Add "ba", -20283 10 d.Add "bai", -20265 11 d.Add "ban", -20257 12 d.Add "bang", -20242 13 d.Add "bao", -0 14 d.Add "bei", -1 15 d.Add "ben", -6 16 d.Add "beng", -2 17 d.Add "bi", -20026 18 d.Add "bian", -20002 19 d.Add "biao", -19990 20 d.Add "bie", -19986 21 d.Add "bin", -19982 22 d.Add "bing", -19976 23 d.Add "bo", -19805 24 d.Add "bu", -19784 25 d.Add "ca", -19775 26 d.Add "cai", -19774 27 d.Add "can", -19763 28 d.Add "cang", -19756 29 d.Add "cao", -19751 30 d.Add "ce", -19746 31 d.Add "ceng", -19741 32 d.Add "cha", -19739 33 d.Add "chai", -19728 34 d.Add "chan", -19725 35 d.Add "chang", -19715 36 d.Add "chao", -19540 37 d.Add "che", -19531 38 d.Add "chen", -19525 39 d.Add "cheng", -19515 40 d.Add "chi", -19500 41 d.Add "chong", -19484 42 d.Add "chou", -19479 43 d.Add "chu", -19467 44 d.Add "chuai", -19289 45 d.Add "chuan", -19288 46 d.Add "chuang", -19281 47 d.Add "chui", -19275 48 d.Add "chun", -19270 49 d.Add "chuo", -19263 50 d.Add "ci", -19261 51 d.Add "cong", -19249 52 d.Add "cou", -19243 53 d.Add "cu", -19242 54 d.Add "cuan", -19238 55 d.Add "cui", -19235 56 d.Add "cun", -19227 57 d.Add "cuo", -19224 58 d.Add "da", -19218 59 d.Add "dai", -19212 60 d.Add "dan", -19038 61 d.Add "dang", -19023 62 d.Add "dao", -19018 63 d.Add "de", -19006 64 d.Add "deng", -19003 65 d.Add "di", -18996 66 d.Add "dian", -18977 67 d.Add "diao", -18961 68 d.Add "die", -18952 69 d.Add "ding", -18783 70 d.Add "diu", -18774 71 d.Add "dong", -18773 72 d.Add "dou", -18763 73 d.Add "du", -18756 74 d.Add "duan", -18741 75 d.Add "dui", -18735 76 d.Add "dun", -18731 77 d.Add "duo", -18722 78 d.Add "e", -18710 79 d.Add "en", -18697 80 d.Add "er", -18696 81 d.Add "fa", -18526 82 d.Add "fan", -18518 83 d.Add "fang", -18501 84 d.Add "fei", -18490 85 d.Add "fen", -18478 86 d.Add "feng", -18463 87 d.Add "fo", -18448 88 d.Add "fou", -18447 89 d.Add "fu", -18446 90 d.Add "ga", -18239 91 d.Add "gai", -18237 92 d.Add "gan", -18231 93 d.Add "gang", -18220 94 d.Add "gao", -18211 95 d.Add "ge", -18201 96 d.Add "gei", -18184 97 d.Add "gen", -18183 98 d.Add "geng", -18181 99 d.Add "gong", -18012100 d.Add "gou", -17997101 d.Add "gu", -17988102 d.Add "gua", -17970103 d.Add "guai", -17964104 d.Add "guan", -17961105 d.Add "guang", -17950106 d.Add "gui", -17947107 d.Add "gun", -17931108 d.Add "guo", -17928109 d.Add "ha", -17922110 d.Add "hai", -17759111 d.Add "han", -17752112 d.Add "hang", -17733113 d.Add "hao", -17730114 d.Add "he", -17721115 d.Add "hei", -17703116 d.Add "hen", -17701117 d.Add "heng", -17697118 d.Add "hong", -17692119 d.Add "hou", -17683120 d.Add "hu", -17676121 d.Add "hua", -17496122 d.Add "huai", -17487123 d.Add "huan", -17482124 d.Add "huang", -17468125 d.Add "hui", -17454126 d.Add "hun", -17433127 d.Add "huo", -17427128 d.Add "ji", -17417129 d.Add "jia", -1730 d.Add "jian", -17185131 d.Add "jiang", -16983132 d.Add "jiao", -16970133 d.Add "jie", -16942134 d.Add "jin", -16915135 d.Add "jing", -16733136 d.Add "jiong", -16708137 d.Add "jiu", -16706138 d.Add "ju", -16689139 d.Add "juan", -16664140 d.Add "jue", -16657141 d.Add "jun", -16647142 d.Add "ka", -16474143 d.Add "kai", -16470144 d.Add "kan", -16465145 d.Add "kang", -16459146 d.Add "kao", -16452147 d.Add "ke", -16448148 d.Add "ken", -16433149 d.Add "keng", -16429150 d.Add "kong", -16427151 d.Add "kou", -16423152 d.Add "ku", -16419153 d.Add "kua", -16412154 d.Add "kuai", -16407155 d.Add "kuan", -16403156 d.Add "kuang", -16401157 d.Add "kui", -16393158 d.Add "kun", -1629 d.Add "kuo", -16216160 d.Add "la", -16212161 d.Add "lai", -16205162 d.Add "lan", -1663 d.Add "lang", -16187164 d.Add "lao", -16180165 d.Add "le", -16171166 d.Add "lei", -16169167 d.Add "leng", -16158168 d.Add "li", -16155169 d.Add "lia", -15959170 d.Add "lian", -15958171 d.Add "liang", -15944172 d.Add "liao", -15933173 d.Add "lie", -1594 d.Add "lin", -15915175 d.Add "ling", -15903176 d.Add "liu", -15889177 d.Add "long", -15878178 d.Add "lou", -15707179 d.Add "lu", -15701180 d.Add "lv", -15681181 d.Add "luan", -15667182 d.Add "lue", -15661183 d.Add "lun", -15659184 d.Add "luo", -15652185 d.Add "ma", -15640186 d.Add "mai", -15631187 d.Add "man", -15625188 d.Add "mang", -15454189 d.Add "mao", -15448190 d.Add "me", -15436191 d.Add "mei", -15435192 d.Add "men", -15419193 d.Add "meng", -15416194 d.Add "mi", -15408195 d.Add "mian", -15394196 d.Add "miao", -15385197 d.Add "mie", -15377198 d.Add "min", -15375199 d.Add "ming", -15369200 d.Add "miu", -15363201 d.Add "mo", -15362202 d.Add "mou", -15183203 d.Add "mu", -15180204 d.Add "na", -15165205 d.Add "nai", -15158206 d.Add "nan", -15153207 d.Add "nang", -15150208 d.Add "nao", -15149209 d.Add "ne", -15144210 d.Add "nei", -15143211 d.Add "nen", -15141212 d.Add "neng", -15140213 d.Add "ni", -15139214 d.Add "nian", -15128215 d.Add "niang", -15121216 d.Add "niao", -15119217 d.Add "nie", -15117218 d.Add "nin", -15110219 d.Add "ning", -15109220 d.Add "niu", -14941221 d.Add "nong", -14937222 d.Add "nu", -14933223 d.Add "nv", -14930224 d.Add "nuan", -14929225 d.Add "nue", -14928226 d.Add "nuo", -14926227 d.Add "o", -14922228 d.Add "ou", -14921229 d.Add "pa", -14914230 d.Add "pai", -14908231 d.Add "pan", -14902232 d.Add "pang", -14894233 d.Add "pao", -14889234 d.Add "pei", -14882235 d.Add "pen", -14873236 d.Add "peng", -14871237 d.Add "pi", -14857238 d.Add "pian", -14678239 d.Add "piao", -14674240 d.Add "pie", -14670241 d.Add "pin", -14668242 d.Add "ping", -14663243 d.Add "po", -14654244 d.Add "pu", -14645245 d.Add "qi", -14630246 d.Add "qia", -14594247 d.Add "qian", -14429248 d.Add "qiang", -14407249 d.Add "qiao", -14399250 d.Add "qie", -14384251 d.Add "qin", -14379252 d.Add "qing", -14368253 d.Add "qiong", -14355254 d.Add "qiu", -14353255 d.Add "qu", -14345256 d.Add "quan", -14170257 d.Add "que", -14159258 d.Add "qun", -14151259 d.Add "ran", -14149260 d.Add "rang", -14145261 d.Add "rao", -14140262 d.Add "re", -14137263 d.Add "ren", -14135264 d.Add "reng", -14125265 d.Add "ri", -14123266 d.Add "rong", -14122267 d.Add "rou", -14112268 d.Add "ru", -14109269 d.Add "ruan", -14099270 d.Add "rui", -14097271 d.Add "run", -14094272 d.Add "ruo", -14092273 d.Add "sa", -14090274 d.Add "sai", -14087275 d.Add "san", -14083276 d.Add "sang", -13917277 d.Add "sao", -13914278 d.Add "se", -13910279 d.Add "sen", -13907280 d.Add "seng", -13906281 d.Add "sha", -13905282 d.Add "shai", -13896283 d.Add "shan", -13894284 d.Add "shang", -13878285 d.Add "shao", -13870286 d.Add "she", -13859287 d.Add "shen", -13847288 d.Add "sheng", -13831289 d.Add "shi", -13658290 d.Add "shou", -13611291 d.Add "shu", -13601292 d.Add "shua", -13406293 d.Add "shuai", -13404294 d.Add "shuan", -13400295 d.Add "shuang", -13398296 d.Add "shui", -13395297 d.Add "shun", -13391298 d.Add "shuo", -13387299 d.Add "si", -13383300 d.Add "song", -13367301 d.Add "sou", -13359302 d.Add "su", -13356303 d.Add "suan", -13343304 d.Add "sui", -13340305 d.Add "sun", -13329306 d.Add "suo", -13326307 d.Add "ta", -13318308 d.Add "tai", -13147309 d.Add "tan", -13138310 d.Add "tang", -13120311 d.Add "tao", -13107312 d.Add "te", -13096313 d.Add "teng", -13095314 d.Add "ti", -13091315 d.Add "tian", -13076316 d.Add "tiao", -13068317 d.Add "tie", -13063318 d.Add "ting", -13060319 d.Add "tong", -12888320 d.Add "tou", -12875321 d.Add "tu", -12871322 d.Add "tuan", -12860323 d.Add "tui", -12858324 d.Add "tun", -12852325 d.Add "tuo", -12849326 d.Add "wa", -12838327 d.Add "wai", -12831328 d.Add "wan", -12829329 d.Add "wang", -12812330 d.Add "wei", -12802331 d.Add "wen", -12607332 d.Add "weng", -12597333 d.Add "wo", -12594334 d.Add "wu", -12585335 d.Add "xi", -12556336 d.Add "xia", -12359337 d.Add "xian", -12346338 d.Add "xiang", -12320339 d.Add "xiao", -12300340 d.Add "xie", -12120341 d.Add "xin", -12099342 d.Add "xing", -12089343 d.Add "xiong", -12074344 d.Add "xiu", -12067345 d.Add "xu", -12058346 d.Add "xuan", -12039347 d.Add "xue", -11867348 d.Add "xun", -11861349 d.Add "ya", -11847350 d.Add "yan", -11831351 d.Add "yang", -11798352 d.Add "yao", -11781353 d.Add "ye", -11604354 d.Add "yi", -11589355 d.Add "yin", -11536356 d.Add "ying", -11358357 d.Add "yo", -11340358 d.Add "yong", -11339359 d.Add "you", -11324360 d.Add "yu", -11303361 d.Add "yuan", -11097362 d.Add "yue", -11077363 d.Add "yun", -11067364 d.Add "za", -11055365 d.Add "zai", -11052366 d.Add "zan", -11045367 d.Add "zang", -11041368 d.Add "zao", -11038369 d.Add "ze", -11024370 d.Add "zei", -11020371 d.Add "zen", -11019372 d.Add "zeng", -11018373 d.Add "zha", -11014374 d.Add "zhai", -10838375 d.Add "zhan", -10832376 d.Add "zhang", -10815377 d.Add "zhao", -10800378 d.Add "zhe", -10790379 d.Add "zhen", -10780380 d.Add "zheng", -10764381 d.Add "zhi", -10587382 d.Add "zhong", -10544383 d.Add "zhou", -10533384 d.Add "zhu", -10519385 d.Add "zhua", -10331386 d.Add "zhuai", -10329387 d.Add "zhuan", -10328388 d.Add "zhuang", -10322389 d.Add "zhui", -10315390 d.Add "zhun", -10309391 d.Add "zhuo", -10307392 d.Add "zi", -10296393 d.Add "zong", -10281394 d.Add "zou", -10274395 d.Add "zu", -10270396 d.Add "zuan", -10262397 d.Add "zui", -10260398 d.Add "zun", -10256399 d.Add "zuo", -10254400 num = Asc(sChinese)401 a = d.Items402 b = d.keys403 Application.Volatile404 For l = 1 To Len(sChinese)405num = Asc(Mid(sChinese, l, 1))406For i = d.Count - 1 To 0 Step -1407 If a(i) <= num Then Exit For408Next409Select Case iflag410Case 0 '返回拼音(首字大写)411 C2S = C2S & Application.WorksheetFunction.Proper(b(i))412Case 1 '返回小写拼音413 C2S = C2S & b(i)414Case 2 '返回大写拼音415 C2S = C2S & UCase$(b(i))416Case 3 '返回小写拼音首字417 C2S = C2S & Left$(b(i), 1)418Case 4 '返回大写拼音首字419 C2S = C2S & UCase$(Left$(b(i), 1))420End Select421If Len(sChinese) > 1 And iflag < 3 Then C2S = C2S & " "422 Next423 Chinese2Spell = C2S424 Set d = Nothing425 End Function
View Code
参考文献2中的函数
用法：=UPPER(SUBSTITUTE(PinYin(B3)," ",""))，全部大写，去掉空格
1 Function PinYin(Hz As String) 2 Dim PinMa As String 3 Dim MyPinMa As Variant 4 Dim Temp As Integer, i As Integer, j As Integer 5 PinMa = "a,20319,ai,20317,an,20304,ang,20295,ao,20292," 6 PinMa = PinMa & "ba,20283,bai,20265,ban,20257,bang,20242,bao,0,bei,1,ben,6,beng,2,bi,20026,bian,20002,biao,19990,bie,19986,bin,19982,bing,19976,bo,19805,bu,19784," 7 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," 8 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," 9 PinMa = PinMa & "e,18710,en,18697,er,18696,"10 PinMa = PinMa & "fa,18526,fan,18518,fang,18501,fei,18490,fen,18478,feng,18463,fo,18448,fou,18447,fu,18446,"11 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,"12 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,"13 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,"14 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,"15 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,"16 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,"17 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,"18 PinMa = PinMa & "o,14922,ou,14921,"19 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,"20 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,"21 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,"22 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,"23 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,"24 PinMa = PinMa & "wa,12838,wai,12831,wan,12829,wang,12812,wei,12802,wen,12607,weng,12597,wo,12594,wu,12585,"25 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,"26 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,"27 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"28 MyPinMa = Split(PinMa, ",")29 30 For i = 1 To Len(Hz)31 Temp = Asc(Mid(Hz, i, 1))32If Temp < 0 Then33 Temp = Abs(Temp)34 For j = 791 To 1 Step -235 If Temp <= Val(MyPinMa(j)) Then36PinYin = PinYin & MyPinMa(j - 1) & " "37Exit For38 End If39 Next40End If41 Next42 PinYin = Trim(PinYin)43 End Function
View Code
