excel技巧：中文转成拼音字母（包括首字母大写）

  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 ", -20230
 14 d.Add " bei ", -20051
 15 d.Add " ben ", -20036
 16 d.Add " beng ", -20032
 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 ", -18012
100 d.Add " gou ", -17997
101 d.Add " gu ", -17988
102 d.Add " gua ", -17970
103 d.Add " guai ", -17964
104 d.Add " guan ", -17961
105 d.Add " guang ", -17950
106 d.Add " gui ", -17947
107 d.Add " gun ", -17931
108 d.Add " guo ", -17928
109 d.Add " ha ", -17922
110 d.Add " hai ", -17759
111 d.Add " han ", -17752
112 d.Add " hang ", -17733
113 d.Add " hao ", -17730
114 d.Add " he ", -17721
115 d.Add " hei ", -17703
116 d.Add " hen ", -17701
117 d.Add " heng ", -17697
118 d.Add " hong ", -17692
119 d.Add " hou ", -17683
120 d.Add " hu ", -17676
121 d.Add " hua ", -17496
122 d.Add " huai ", -17487
123 d.Add " huan ", -17482
124 d.Add " huang ", -17468
125 d.Add " hui ", -17454
126 d.Add " hun ", -17433
127 d.Add " huo ", -17427
128 d.Add " ji ", -17417
129 d.Add " jia ", -17202
130 d.Add " jian ", -17185
131 d.Add " jiang ", -16983
132 d.Add " jiao ", -16970
133 d.Add " jie ", -16942
134 d.Add " jin ", -16915
135 d.Add " jing ", -16733
136 d.Add " jiong ", -16708
137 d.Add " jiu ", -16706
138 d.Add " ju ", -16689
139 d.Add " juan ", -16664
140 d.Add " jue ", -16657
141 d.Add " jun ", -16647
142 d.Add " ka ", -16474
143 d.Add " kai ", -16470
144 d.Add " kan ", -16465
145 d.Add " kang ", -16459
146 d.Add " kao ", -16452
147 d.Add " ke ", -16448
148 d.Add " ken ", -16433
149 d.Add " keng ", -16429
150 d.Add " kong ", -16427
151 d.Add " kou ", -16423
152 d.Add " ku ", -16419
153 d.Add " kua ", -16412
154 d.Add " kuai ", -16407
155 d.Add " kuan ", -16403
156 d.Add " kuang ", -16401
157 d.Add " kui ", -16393
158 d.Add " kun ", -16220
159 d.Add " kuo ", -16216
160 d.Add " la ", -16212
161 d.Add " lai ", -16205
162 d.Add " lan ", -16202
163 d.Add " lang ", -16187
164 d.Add " lao ", -16180
165 d.Add " le ", -16171
166 d.Add " lei ", -16169
167 d.Add " leng ", -16158
168 d.Add " li ", -16155
169 d.Add " lia ", -15959
170 d.Add " lian ", -15958
171 d.Add " liang ", -15944
172 d.Add " liao ", -15933
173 d.Add " lie ", -15920
174 d.Add " lin ", -15915
175 d.Add " ling ", -15903
176 d.Add " liu ", -15889
177 d.Add " long ", -15878
178 d.Add " lou ", -15707
179 d.Add " lu ", -15701
180 d.Add " lv ", -15681
181 d.Add " luan ", -15667
182 d.Add " lue ", -15661
183 d.Add " lun ", -15659
184 d.Add " luo ", -15652
185 d.Add " ma ", -15640
186 d.Add " mai ", -15631
187 d.Add " man ", -15625
188 d.Add " mang ", -15454
189 d.Add " mao ", -15448
190 d.Add " me ", -15436
191 d.Add " mei ", -15435
192 d.Add " men ", -15419
193 d.Add " meng ", -15416
194 d.Add " mi ", -15408
195 d.Add " mian ", -15394
196 d.Add " miao ", -15385
197 d.Add " mie ", -15377
198 d.Add " min ", -15375
199 d.Add " ming ", -15369
200 d.Add " miu ", -15363
201 d.Add " mo ", -15362
202 d.Add " mou ", -15183
203 d.Add " mu ", -15180
204 d.Add " na ", -15165
205 d.Add " nai ", -15158
206 d.Add " nan ", -15153
207 d.Add " nang ", -15150
208 d.Add " nao ", -15149
209 d.Add " ne ", -15144
210 d.Add " nei ", -15143
211 d.Add " nen ", -15141
212 d.Add " neng ", -15140
213 d.Add " ni ", -15139
214 d.Add " nian ", -15128
215 d.Add " niang ", -15121
216 d.Add " niao ", -15119
217 d.Add " nie ", -15117
218 d.Add " nin ", -15110
219 d.Add " ning ", -15109
220 d.Add " niu ", -14941
221 d.Add " nong ", -14937
222 d.Add " nu ", -14933
223 d.Add " nv ", -14930
224 d.Add " nuan ", -14929
225 d.Add " nue ", -14928
226 d.Add " nuo ", -14926
227 d.Add " o ", -14922
228 d.Add " ou ", -14921
229 d.Add " pa ", -14914
230 d.Add " pai ", -14908
231 d.Add " pan ", -14902
232 d.Add " pang ", -14894
233 d.Add " pao ", -14889
234 d.Add " pei ", -14882
235 d.Add " pen ", -14873
236 d.Add " peng ", -14871
237 d.Add " pi ", -14857
238 d.Add " pian ", -14678
239 d.Add " piao ", -14674
240 d.Add " pie ", -14670
241 d.Add " pin ", -14668
242 d.Add " ping ", -14663
243 d.Add " po ", -14654
244 d.Add " pu ", -14645
245 d.Add " qi ", -14630
246 d.Add " qia ", -14594
247 d.Add " qian ", -14429
248 d.Add " qiang ", -14407
249 d.Add " qiao ", -14399
250 d.Add " qie ", -14384
251 d.Add " qin ", -14379
252 d.Add " qing ", -14368
253 d.Add " qiong ", -14355
254 d.Add " qiu ", -14353
255 d.Add " qu ", -14345
256 d.Add " quan ", -14170
257 d.Add " que ", -14159
258 d.Add " qun ", -14151
259 d.Add " ran ", -14149
260 d.Add " rang ", -14145
261 d.Add " rao ", -14140
262 d.Add " re ", -14137
263 d.Add " ren ", -14135
264 d.Add " reng ", -14125
265 d.Add " ri ", -14123
266 d.Add " rong ", -14122
267 d.Add " rou ", -14112
268 d.Add " ru ", -14109
269 d.Add " ruan ", -14099
270 d.Add " rui ", -14097
271 d.Add " run ", -14094
272 d.Add " ruo ", -14092
273 d.Add " sa ", -14090
274 d.Add " sai ", -14087
275 d.Add " san ", -14083
276 d.Add " sang ", -13917
277 d.Add " sao ", -13914
278 d.Add " se ", -13910
279 d.Add " sen ", -13907
280 d.Add " seng ", -13906
281 d.Add " sha ", -13905
282 d.Add " shai ", -13896
283 d.Add " shan ", -13894
284 d.Add " shang ", -13878
285 d.Add " shao ", -13870
286 d.Add " she ", -13859
287 d.Add " shen ", -13847
288 d.Add " sheng ", -13831
289 d.Add " shi ", -13658
290 d.Add " shou ", -13611
291 d.Add " shu ", -13601
292 d.Add " shua ", -13406
293 d.Add " shuai ", -13404
294 d.Add " shuan ", -13400
295 d.Add " shuang ", -13398
296 d.Add " shui ", -13395
297 d.Add " shun ", -13391
298 d.Add " shuo ", -13387
299 d.Add " si ", -13383
300 d.Add " song ", -13367
301 d.Add " sou ", -13359
302 d.Add " su ", -13356
303 d.Add " suan ", -13343
304 d.Add " sui ", -13340
305 d.Add " sun ", -13329
306 d.Add " suo ", -13326
307 d.Add " ta ", -13318
308 d.Add " tai ", -13147
309 d.Add " tan ", -13138
310 d.Add " tang ", -13120
311 d.Add " tao ", -13107
312 d.Add " te ", -13096
313 d.Add " teng ", -13095
314 d.Add " ti ", -13091
315 d.Add " tian ", -13076
316 d.Add " tiao ", -13068
317 d.Add " tie ", -13063
318 d.Add " ting ", -13060
319 d.Add " tong ", -12888
320 d.Add " tou ", -12875
321 d.Add " tu ", -12871
322 d.Add " tuan ", -12860
323 d.Add " tui ", -12858
324 d.Add " tun ", -12852
325 d.Add " tuo ", -12849
326 d.Add " wa ", -12838
327 d.Add " wai ", -12831
328 d.Add " wan ", -12829
329 d.Add " wang ", -12812
330 d.Add " wei ", -12802
331 d.Add " wen ", -12607
332 d.Add " weng ", -12597
333 d.Add " wo ", -12594
334 d.Add " wu ", -12585
335 d.Add " xi ", -12556
336 d.Add " xia ", -12359
337 d.Add " xian ", -12346
338 d.Add " xiang ", -12320
339 d.Add " xiao ", -12300
340 d.Add " xie ", -12120
341 d.Add " xin ", -12099
342 d.Add " xing ", -12089
343 d.Add " xiong ", -12074
344 d.Add " xiu ", -12067
345 d.Add " xu ", -12058
346 d.Add " xuan ", -12039
347 d.Add " xue ", -11867
348 d.Add " xun ", -11861
349 d.Add " ya ", -11847
350 d.Add " yan ", -11831
351 d.Add " yang ", -11798
352 d.Add " yao ", -11781
353 d.Add " ye ", -11604
354 d.Add " yi ", -11589
355 d.Add " yin ", -11536
356 d.Add " ying ", -11358
357 d.Add " yo ", -11340
358 d.Add " yong ", -11339
359 d.Add " you ", -11324
360 d.Add " yu ", -11303
361 d.Add " yuan ", -11097
362 d.Add " yue ", -11077
363 d.Add " yun ", -11067
364 d.Add " za ", -11055
365 d.Add " zai ", -11052
366 d.Add " zan ", -11045
367 d.Add " zang ", -11041
368 d.Add " zao ", -11038
369 d.Add " ze ", -11024
370 d.Add " zei ", -11020
371 d.Add " zen ", -11019
372 d.Add " zeng ", -11018
373 d.Add " zha ", -11014
374 d.Add " zhai ", -10838
375 d.Add " zhan ", -10832
376 d.Add " zhang ", -10815
377 d.Add " zhao ", -10800
378 d.Add " zhe ", -10790
379 d.Add " zhen ", -10780
380 d.Add " zheng ", -10764
381 d.Add " zhi ", -10587
382 d.Add " zhong ", -10544
383 d.Add " zhou ", -10533
384 d.Add " zhu ", -10519
385 d.Add " zhua ", -10331
386 d.Add " zhuai ", -10329
387 d.Add " zhuan ", -10328
388 d.Add " zhuang ", -10322
389 d.Add " zhui ", -10315
390 d.Add " zhun ", -10309
391 d.Add " zhuo ", -10307
392 d.Add " zi ", -10296
393 d.Add " zong ", -10281
394 d.Add " zou ", -10274
395 d.Add " zu ", -10270
396 d.Add " zuan ", -10262
397 d.Add " zui ", -10260
398 d.Add " zun ", -10256
399 d.Add " zuo ", -10254
400 num = Asc(sChinese)
 401 a = d.Items
 402 b = d.keys
 403 Application.Volatile
 404 For l = 1 To Len(sChinese)
 405     num = Asc(Mid(sChinese, l, 1))
 406     For i = d.Count - 1 To 0 Step -1
407         If a(i) <= num Then Exit For
408     Next
409     Select Case iflag
 410     Case 0 '返回拼音(首字大写)
411         C2S = C2S & Application.WorksheetFunction.Proper(b(i))
 412     Case 1 '返回小写拼音
413         C2S = C2S & b(i)
 414     Case 2 '返回大写拼音
415         C2S = C2S & UCase$(b(i))
 416     Case 3 '返回小写拼音首字
417         C2S = C2S & Left$(b(i), 1)
 418     Case 4 '返回大写拼音首字
419         C2S = C2S & UCase$(Left$(b(i), 1))
 420     End Select
421     If Len(sChinese) > 1 And iflag < 3 Then C2S = C2S & " "
422 Next
423 Chinese2Spell = C2S
 424 Set d = Nothing
425 End Function

 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,20230,bei,20051,ben,20036,beng,20032,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))
 32     If Temp < 0 Then
33       Temp = Abs(Temp)
 34       For j = 791 To 1 Step -2
35           If Temp <= Val(MyPinMa(j)) Then
36               PinYin = PinYin & MyPinMa(j - 1) & " "
37               Exit For
38           End If
39       Next
40     End If
41 Next
42 PinYin = Trim(PinYin)
 43 End Function