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
Excel自定义函数方法:
1.启动Excel 2003(其它版本请仿照操作),打开相应的工作表;
2.执行“工具→宏→Visual Basic编辑器”命令(或者直接按“Alt+F11”组合键),进入Visual Basic编辑状态;
3.执行“插入→模块”命令,插入一个新模块。再双击插入的模块,进入模块代码编辑状态;
4.将代码输入其中;
5.代码输入完成后,关闭Visual Basic编辑窗口,返回Excel编辑状态;
6.自定义函数就可以用了,如:选中D2单元格,输入公式:=PinYin(A2)。
分享到:
相关推荐
EXCEL中使用的中文转拼音自定义函数,可以生成多种格式的拼音。
1.启动Excel ,打开相应的工作表; 2.执行“工具→宏→Visual Basic编辑器”命令(或者直接按“Alt+F11”组合键),进入Visual Basic编辑状态;...自定义函数就可以用了,如:选中D2单元格,输入公式:=PinYin(A1)。
取汉字拼音首字母的自定义EXCEL函数.取汉字拼音首字母的自定义EXCEL函数.
最近信息录入需要把姓名转化拼音,excel自带函数没法满足需求,遂有此自定义程序。
函数作用:取得汉字拼音的第一个字母.................98 '99.函数作用:获取两栏中相同的数据....................100 '100.函数作用:选取当前工作表中公式出错的单元格﹐关返回出错个数...............................
VBA获取拼音源码.xlsm 两个自定义函数 一个是获取单个字符的拼音 一个是获取中文句子的拼音 可以直接当函数使用
.xlam文件使用方法:将.xlam文件存在固定位置----在excel中选择“excel加载项”----“浏览”选择指定.xlam文件----“确定”----勾选加载的文件,即可使用自定义公式。 此文件包含两个公式: 1.GETPY =GETPY(A1) 将A1...
98.取得汉字拼音的第一个字母 99.获取两栏中相同的数据 100.选取当前工作表中公式出错的单元格﹐关返回出错个数 101.将工作表中最后一列作为页脚打印在每一面页尾 102.获取vbproject引用项目 103.移除Excel工作表中...
VBA自定义函数__获取字符串首字母拼音.xlsm
本人是HR岗位,经常要更新人员花名册,频繁得要录入姓名,身份证号码,性别,年龄,出生日期户籍地址等信息,为了将重复工作简单化,利用Excel VBA 自定义若干函数,可以将汉字转换为拼音,可以由身份证号码获取相关...
VB.NET获取汉字的拼音
强人编写的Excel函数,可以实现如显示公式、Wlooup、人民币大小写转换、显示汉字笔画、拼音等,插入后可以像系统函数一样直接调用
本人针对此种情况,自定义了一个函数,写在加载宏里,加载后,即可以在任意工作表中,使用分列公式(“=分列(A1,“汉字”,1,1,1)”),实现模糊分列,支持多个自定义的分列字符或符号,也可以默认为汉字与非汉字...
自定义函数,集160个工具于一身,但体积小于15MB。当安装集成工具箱后,如果您使用Excel 2003,则将产生【工具箱】菜单, 包括160多个子菜单;如果您使用Excel 2007或者2010,将产生【集成】与【工具箱】功能区。...
自定义函数,集160个工具于一身,但体积小于16MB。当安装集成工具箱后,如果您使用Excel 2003,则将产生【工具箱】菜单, 包括160多个子菜单;如果您使用Excel 2007或者2010,将产生【集成】与【工具箱】功能区。...
0228 如何获取汉字拼音简码 151 0229 根据用户输入需求自动调整输入法 152 0230 根据生日自动计算年龄 153 0231 实现找零功能 153 0232 设计带记忆的数据录入窗口 154 0233 在窗体关闭时提示有未保存的...
0228 如何获取汉字拼音简码 151 0229 根据用户输入需求自动调整输入法 152 0230 根据生日自动计算年龄 153 0231 实现找零功能 153 0232 设计带记忆的数据录入窗口 154 0233 在窗体关闭时提示有未保存的...
实例075 如何获取汉字的拼音索引 实例076 如何设置输入方法 实例077 如何控制数据输入格式 实例078 如何控制编辑框中的输入法 实例079 如何设置回车键后的焦点 实例080 如何将项目保存到对象库 实例081 如何...