EXCEL如何自动将姓名转换为拼音,Excel汉字调换拼

2019-10-30 19:54 来源:未知

操作步骤如下

图片 1

回答:

网上有不少用VBA制成的自定义函数,搜索下姓名转拼音可以找到好多的。然后可以像使用普通工作表函数那样使用,生僻字保证不了,但3000常用汉字准确率还是非常高的

打开Excel->工具->宏->Viaual Basic编辑器
在弹出来的窗口中对着VBAproject点右键->插入->模块
下面会出现一个名为"模块1",点击
在右边的空白栏中粘贴以下内容:
''''''''''''''''''''''
'VBA代码转换者:惟惟
'联系方法:Email:wuweiyin@yeah.net
'个人博客:
'如有不正确的地方,请提出来或是帮忙完善
'谢谢
''''''''''''''''''''''''''''''
Function pinyin(p As String) As String
i = Asc(p)
Select Case i
Case -20319 To -20284: pinyin = "A"
Case -20283 To -19776: pinyin = "B"
Case -19775 To -19219: pinyin = "C"
Case -19218 To -18711: pinyin = "D"
Case -18710 To -18527: pinyin = "E"
Case -18526 To -18240: pinyin = "F"
Case -18239 To -17923: pinyin = "G"
Case -17922 To -17418: pinyin = "H"
Case -17417 To -16475: pinyin = "J"
Case -16474 To -16213: pinyin = "K"
Case -16212 To -15641: pinyin = "L"
Case -15640 To -15166: pinyin = "M"
Case -15165 To -14923: pinyin = "N"
Case -14922 To -14915: pinyin = "O"
Case -14914 To -14631: pinyin = "P"
Case -14630 To -14150: pinyin = "Q"
Case -14149 To -14091: pinyin = "R"
Case -14090 To -13319: pinyin = "S"
Case -13318 To -12839: pinyin = "T"
Case -12838 To -12557: pinyin = "W"
Case -12556 To -11848: pinyin = "X"
Case -11847 To -11056: pinyin = "Y"
Case -11055 To -2050: pinyin = "Z"
Case Else: pinyin = p
End Select
End Function
Function getpy(str)
For i = 1 To Len(str)
getpy = getpy & pinyin(Mid(str, i, 1))
Next i
End Function
********复制到此结束,本行不复制*******

     图片 2

第一:把excel中的姓名粘贴到word中,借助word添加拼音,再把word中添加拼音的结果粘贴到excel中

现在转换函数已编写完成!关掉此编缉的窗口。
要在Excel中使用,方法如下:
A1         A2
中国     =getpy(A1)

’注:将b列和c列相乘的结果

李老师简介:创办电脑学校,专职从事电脑教学二十余年。

  B. 单击绿右三角即可运行。或者关闭VBA编辑窗口,单击“宏”——选中“test”——“执行”。

这个公式利用substitute函数把拼音中的字母为韵母的声调都替换成了不带声调的韵母,也就让拼音的声调去除了

      Sheets("A"),名称为A的excel工作表

EXCEL如何自动将姓名转换为拼音?以下是我的解决方法,不是用VBA,而是借助word来操作

       If Range("a1").Value > 0 Then

第二:用PHONETIC函数取得拼音

    x = 1

问题:EXCEL如何自动将姓名转换为拼音?

   Sub d1()

操作思路:

      a、Exit Sub语句

接下来详细介绍操作方法:

        Range("a3") = IIf(Range("a1") <= 0, "负数或零", "负数")

第三:用substitute函数将拼音声调去掉(这一步视需求,如不需要删除声调可以忽略)

      dim public

图片 3

           For J = 4 To 930

第二:用PHONETIC函数取得拼音

         '设置断点

回答:

     Next x

我是Excel奇妙之旅,专注于Excel知识和技能的分享,关注Excel奇妙之旅,带给你更多的实战技巧

 A.实现Excel中没有实现的功能。

在C3单元格输入公式= =PHONETIC(B3),然后下拉公式,得到所有姓名拼音

     Workbooks ("工作簿名称")

注意粘贴到Word的时候选择只保留文本那个选项,不然word无法添加拼音

     (5)字符型(String)

图片 4

     End Sub

图片 5

D、变量的存活周期

第三:用substitute函数将拼音声调去掉(这一步视需求,如不需要删除声调可以忽略

 B.提高运行速度。

回答:

       Cells(x, 3) = Cells(x, 2) * Cells(x, 1)

头条号:李老师电脑教学课堂,简单实用,清晰明了,专门系统讲解电脑知识,软件使用技巧,欢迎关注。

回答:

目前用Excel需要使用VBA来实现,操作过于复杂,在小白不懂的情况下,也可能会有安全性问题。

建议换一种思路,那就是利用网站来完成拼音转换后,再粘贴回Excel中。操作步骤如下:

      Set wb = ThisWorkbook

图片 6

           有效数为7位 表示-3.37E 38至3.37E 38之间的实数

1)第一:把excel中的姓名粘贴到word中,借助word添加拼音,注意粘贴的时候选择只保留文本那个选项,添加拼音后再粘贴回Excel

        End If

打开转换网址

     注:在A2:A6单元格区域中,找到b项,共两个,将所对应的B列中的单元格值相加,即3 5=8。

在D3单元格输入公式=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C3,"ā","a"),"á","a"),"ǎ","a"),"à","a"),"ē","e"),"é","e"),"ě","e"),"è","e"),"ī","i"),"í","i"),"ǐ","i"),"ì","i"),"ō","o"),"ó","o"),"ǒ","o"),"ò","o"),"ū","u"),"ú","u"),"ǔ","u"),"ù","u"),"ǖ","ü"),"ǘ","ü"),"ǚ","ü"),"ǜ","ü")

        Workbooks("A.xls").Sheets(1).Range("a1") = 100

图片 7

   3 全局级变量: 在所有的模块中都可以调用,值会保存到EXCEL关闭时才会被释放。

感谢今日头条的邀请,大家好,我是Excel奇妙之旅,专注于Excel知识和技能的分享

   G、 保护工作表

EXCEL如何自动将姓名转换为拼音?

感谢悟空小秘的邀请!

EXCEL如何自动将姓名转换为拼音,由于EXCEL中没有汉字自动转换拼音的功能,所以要在VBA中自定义一个转换函数。

具体操作步骤

第一步,要把EXCEL文档转换为 XLSM格式,启用宏。

图片 8

第二步,在文件,设置,自定义功能区中勾选开发工具。

图片 9

第三步,打开VBA编辑器。

图片 10

第四步,插入模块。

图片 11

第五步,在模块中输入如下代码:

Function pinyin(p As String) As String

i = Asc(p)

Select Case i

Case -20319 To -20318: pinyin = "a "

Case -20317 To -20305: pinyin = "ai "

Case -20304 To -20296: pinyin = "an "

Case -20295 To -20293: pinyin = "ang "

Case -20292 To -20284: pinyin = "ao "

Case -20283 To -20266: pinyin = "ba "

Case -20265 To -20258: pinyin = "bai "

Case -20257 To -20243: pinyin = "ban "

Case -20242 To -20231: pinyin = "bang "

Case -20230 To -20052: pinyin = "bao "

Case -20051 To -20037: pinyin = "bei "

Case -20036 To -20033: pinyin = "ben "

Case -20032 To -20027: pinyin = "beng "

Case -20026 To -20003: pinyin = "bi "

Case -20002 To -19991: pinyin = "bian "

Case -19990 To -19987: pinyin = "biao "

Case -19986 To -19983: pinyin = "bie "

Case -19982 To -19977: pinyin = "bin "

Case -19976 To -19806: pinyin = "bing "

Case -19805 To -19785: pinyin = "bo "

Case -19784 To -19776: pinyin = "bu "

Case -19775 To -19775: pinyin = "ca "

Case -19774 To -19764: pinyin = "cai "

Case -19763 To -19757: pinyin = "can "

Case -19756 To -19752: pinyin = "cang "

Case -19751 To -19747: pinyin = "cao "

Case -19746 To -19742: pinyin = "ce "

Case -19741 To -19740: pinyin = "ceng "

Case -19739 To -19729: pinyin = "cha "

Case -19728 To -19726: pinyin = "chai "

Case -19725 To -19716: pinyin = "chan "

Case -19715 To -19541: pinyin = "chang "

Case -19540 To -19532: pinyin = "chao "

Case -19531 To -19526: pinyin = "che "

Case -19525 To -19516: pinyin = "chen "

Case -19515 To -19501: pinyin = "cheng "

Case -19500 To -19485: pinyin = "chi "

Case -19484 To -19480: pinyin = "chong "

Case -19479 To -19468: pinyin = "chou "

Case -19467 To -19290: pinyin = "chu "

Case -19289 To -19289: pinyin = "chuai "

Case -19288 To -19282: pinyin = "chuan "

Case -19281 To -19276: pinyin = "chuang "

Case -19275 To -19271: pinyin = "chui "

Case -19270 To -19264: pinyin = "chun "

Case -19263 To -19262: pinyin = "chuo "

Case -19261 To -19250: pinyin = "ci "

Case -19249 To -19244: pinyin = "cong "

Case -19243 To -19243: pinyin = "cou "

Case -19242 To -19239: pinyin = "cu "

Case -19238 To -19236: pinyin = "cuan "

Case -19235 To -19228: pinyin = "cui "

Case -19227 To -19225: pinyin = "cun "

Case -19224 To -19219: pinyin = "cuo "

Case -19218 To -19213: pinyin = "da "

Case -19212 To -19039: pinyin = "dai "

Case -19038 To -19024: pinyin = "dan "

Case -19023 To -19019: pinyin = "dang "

Case -19018 To -19007: pinyin = "dao "

Case -19006 To -19004: pinyin = "de "

Case -19003 To -18997: pinyin = "deng "

Case -18996 To -18978: pinyin = "di "

Case -18977 To -18962: pinyin = "dian "

Case -18961 To -18953: pinyin = "diao "

Case -18952 To -18784: pinyin = "die "

Case -18783 To -18775: pinyin = "ding "

Case -18774 To -18774: pinyin = "diu "

Case -18773 To -18527: pinyin = "dong "

Case -18526 To -18519: pinyin = "fa "

Case -18518 To -18502: pinyin = "fan "

Case -18501 To -18491: pinyin = "fang "

Case -18490 To -18479: pinyin = "fei "

Case -18478 To -18464: pinyin = "fen "

Case -18463 To -18449: pinyin = "feng "

Case -18448 To -18448: pinyin = "fo "

Case -18447 To -18447: pinyin = "fou "

Case -18446 To -18240: pinyin = "fu "

Case -18239 To -18238: pinyin = "ga "

Case -18237 To -18232: pinyin = "gai "

Case -18231 To -18221: pinyin = "gan "

Case -18220 To -18212: pinyin = "gang "

Case -18211 To -18202: pinyin = "gao "

Case -18201 To -18185: pinyin = "ge "

Case -18184 To -18184: pinyin = "gei "

Case -18183 To -18182: pinyin = "gen "

Case -18181 To -18013: pinyin = "geng "

Case -18012 To -17998: pinyin = "gong "

Case -17997 To -17989: pinyin = "gou "

Case -17988 To -17971: pinyin = "gu "

Case -17970 To -17965: pinyin = "gua "

Case -17964 To -17962: pinyin = "guai "

Case -17961 To -17951: pinyin = "guan "

Case -17950 To -17948: pinyin = "guang "

Case -17947 To -17932: pinyin = "gui "

Case -17931 To -17929: pinyin = "gun "

Case -17928 To -17923: pinyin = "guo "

Case -17922 To -17760: pinyin = "ha "

Case -17759 To -17753: pinyin = "hai "

Case -17752 To -17734: pinyin = "han "

Case -17733 To -17731: pinyin = "hang "

Case -17730 To -17722: pinyin = "hao "

Case -17721 To -17704: pinyin = "he "

Case -17703 To -17702: pinyin = "hei "

Case -17701 To -17698: pinyin = "hen "

Case -17697 To -17693: pinyin = "heng "

Case -17692 To -17684: pinyin = "hong "

Case -17683 To -17677: pinyin = "hou "

Case -17676 To -17497: pinyin = "hu "

Case -17496 To -17488: pinyin = "hua "

Case -17487 To -17483: pinyin = "huai "

Case -17482 To -17469: pinyin = "huan "

Case -17468 To -17455: pinyin = "huang "

Case -17454 To -17434: pinyin = "hui "

Case -17433 To -17428: pinyin = "hun "

Case -17427 To -17418: pinyin = "huo "

Case -17417 To -17203: pinyin = "ji "

Case -17202 To -17186: pinyin = "jia "

Case -17185 To -16984: pinyin = "jian "

Case -16983 To -16971: pinyin = "jiang "

Case -16970 To -16943: pinyin = "jiao "

Case -16942 To -16916: pinyin = "jie "

Case -16915 To -16734: pinyin = "jin "

Case -16733 To -16709: pinyin = "jing "

Case -16708 To -16707: pinyin = "jiong "

Case -16706 To -16690: pinyin = "jiu "

Case -16689 To -16665: pinyin = "ju "

Case -16664 To -16658: pinyin = "juan "

Case -16657 To -16648: pinyin = "jue "

Case -16647 To -16475: pinyin = "jun "

Case -16474 To -16471: pinyin = "ka "

Case -16470 To -16466: pinyin = "kai "

Case -16465 To -16460: pinyin = "kan "

Case -16459 To -16453: pinyin = "kang "

Case -16452 To -16449: pinyin = "kao "

Case -16448 To -16434: pinyin = "ke "

Case -16433 To -16430: pinyin = "ken "

Case -16429 To -16428: pinyin = "keng "

Case -16427 To -16424: pinyin = "kong "

Case -16423 To -16420: pinyin = "kou "

Case -16419 To -16413: pinyin = "ku "

Case -16412 To -16408: pinyin = "kua "

Case -16407 To -16404: pinyin = "kuai "

Case -16403 To -16402: pinyin = "kuan "

Case -16401 To -16394: pinyin = "kuang "

Case -16393 To -16221: pinyin = "kui "

Case -16220 To -16217: pinyin = "kun "

Case -16216 To -16213: pinyin = "kuo "

Case -16212 To -16206: pinyin = "la "

Case -16205 To -16203: pinyin = "lai "

Case -16202 To -16188: pinyin = "lan "

Case -16187 To -16181: pinyin = "lang "

Case -16180 To -16172: pinyin = "lao "

Case -16171 To -16170: pinyin = "le "

Case -16169 To -16159: pinyin = "lei "

Case -16158 To -16156: pinyin = "leng "

Case -16155 To -15960: pinyin = "li "

Case -15959 To -15959: pinyin = "lia "

Case -15958 To -15945: pinyin = "lian "

Case -15944 To -15934: pinyin = "liang "

Case -15933 To -15921: pinyin = "liao "

Case -15920 To -15916: pinyin = "lie "

Case -15915 To -15904: pinyin = "lin "

Case -15903 To -15890: pinyin = "ling "

Case -15889 To -15879: pinyin = "liu "

Case -15878 To -15708: pinyin = "long "

Case -15707 To -15702: pinyin = "lou "

Case -15701 To -15682: pinyin = "lu "

Case -15681 To -15668: pinyin = "lv "

Case -15667 To -15662: pinyin = "luan "

Case -15661 To -15660: pinyin = "lue "

Case -15659 To -15653: pinyin = "lun "

Case -15652 To -15641: pinyin = "luo "

Case -15640 To -15632: pinyin = "ma "

Case -15631 To -15626: pinyin = "mai "

Case -15625 To -15455: pinyin = "man "

Case -15454 To -15449: pinyin = "mang "

Case -15448 To -15437: pinyin = "mao "

Case -15436 To -15436: pinyin = "me "

Case -15435 To -15420: pinyin = "mei "

Case -15419 To -15417: pinyin = "men "

Case -15416 To -15409: pinyin = "meng "

Case -15408 To -15395: pinyin = "mi "

Case -15394 To -15386: pinyin = "mian "

Case -15385 To -15378: pinyin = "miao "

Case -15377 To -15376: pinyin = "mie "

Case -15375 To -15370: pinyin = "min "

Case -15369 To -15364: pinyin = "ming "

Case -15363 To -15363: pinyin = "miu "

Case -15362 To -15184: pinyin = "mo "

Case -15183 To -15181: pinyin = "mou "

Case -15180 To -15166: pinyin = "mu "

Case -15165 To -15159: pinyin = "na "

Case -15158 To -15154: pinyin = "nai "

Case -15153 To -15151: pinyin = "nan "

Case -15150 To -15150: pinyin = "nang "

Case -15149 To -15145: pinyin = "nao "

Case -15144 To -15144: pinyin = "ne "

Case -15143 To -15142: pinyin = "nei "

Case -15141 To -15141: pinyin = "nen "

Case -15140 To -15140: pinyin = "neng "

Case -15139 To -15129: pinyin = "ni "

Case -15128 To -15122: pinyin = "nian "

Case -15121 To -15120: pinyin = "niang "

Case -15119 To -15118: pinyin = "niao "

Case -15117 To -15111: pinyin = "nie "

Case -15110 To -15110: pinyin = "nin "

Case -15109 To -14942: pinyin = "ning "

Case -14941 To -14938: pinyin = "niu "

Case -14937 To -14934: pinyin = "nong "

Case -14933 To -14931: pinyin = "nu "

Case -14930 To -14930: pinyin = "nv "

Case -14929 To -14929: pinyin = "nuan "

Case -14928 To -14927: pinyin = "nue "

Case -14926 To -14923: pinyin = "nuo "

Case -14922 To -14922: pinyin = "o "

Case -14921 To -14915: pinyin = "ou "

Case -14914 To -14909: pinyin = "pa "

Case -14908 To -14903: pinyin = "pai "

Case -14902 To -14895: pinyin = "pan "

Case -14894 To -14890: pinyin = "pang "

Case -14889 To -14883: pinyin = "pao "

Case -14882 To -14874: pinyin = "pei "

Case -14873 To -14872: pinyin = "pen "

Case -14871 To -14858: pinyin = "peng "

Case -14857 To -14679: pinyin = "pi "

Case -14678 To -14675: pinyin = "pian "

Case -14674 To -14671: pinyin = "piao "

Case -14670 To -14669: pinyin = "pie "

Case -14668 To -14664: pinyin = "pin "

Case -14663 To -14655: pinyin = "ping "

Case -14654 To -14646: pinyin = "po "

Case -14645 To -14631: pinyin = "pu "

Case -14630 To -14595: pinyin = "qi "

Case -14594 To -14430: pinyin = "qia "

Case -14429 To -14408: pinyin = "qian "

Case -14407 To -14400: pinyin = "qiang "

Case -14399 To -14385: pinyin = "qiao "

Case -14384 To -14380: pinyin = "qie "

Case -14379 To -14369: pinyin = "qin "

Case -14368 To -14356: pinyin = "qing "

Case -14355 To -14354: pinyin = "qiong "

Case -14353 To -14346: pinyin = "qiu "

Case -14345 To -14171: pinyin = "qu "

Case -14170 To -14160: pinyin = "quan "

Case -14159 To -14152: pinyin = "que "

Case -14151 To -14150: pinyin = "qun "

Case -14149 To -14146: pinyin = "ran "

Case -14145 To -14141: pinyin = "rang "

Case -14140 To -14138: pinyin = "rao "

Case -14137 To -14136: pinyin = "re "

Case -14135 To -14126: pinyin = "ren "

Case -14125 To -14124: pinyin = "reng "

Case -14123 To -14123: pinyin = "ri "

Case -14122 To -14113: pinyin = "rong "

Case -14112 To -14110: pinyin = "rou "

Case -14109 To -14100: pinyin = "ru "

Case -14099 To -14098: pinyin = "ruan "

Case -14097 To -14095: pinyin = "rui "

Case -14094 To -14093: pinyin = "run "

Case -14092 To -14091: pinyin = "ruo "

Case -14090 To -14088: pinyin = "sa "

Case -14087 To -14084: pinyin = "sai "

Case -14083 To -13918: pinyin = "san "

Case -13917 To -13915: pinyin = "sang "

Case -13914 To -13911: pinyin = "sao "

Case -13910 To -13908: pinyin = "se "

Case -13907 To -13907: pinyin = "sen "

Case -13906 To -13906: pinyin = "seng "

Case -13905 To -13897: pinyin = "sha "

Case -13896 To -13895: pinyin = "shai "

Case -13894 To -13879: pinyin = "shan "

Case -13878 To -13871: pinyin = "shang "

Case -13870 To -13860: pinyin = "shao "

Case -13859 To -13848: pinyin = "she "

Case -13847 To -13832: pinyin = "shen "

Case -13831 To -13659: pinyin = "sheng "

Case -13658 To -13612: pinyin = "shi "

Case -13611 To -13602: pinyin = "shou "

Case -13601 To -13407: pinyin = "shu "

Case -13406 To -13405: pinyin = "shua "

Case -13404 To -13401: pinyin = "shuai "

Case -13400 To -13399: pinyin = "shuan "

Case -13398 To -13396: pinyin = "shuang "

Case -13395 To -13392: pinyin = "shui "

Case -13391 To -13388: pinyin = "shun "

Case -13387 To -13384: pinyin = "shuo "

Case -13383 To -13368: pinyin = "si "

Case -13367 To -13360: pinyin = "song "

Case -13359 To -13357: pinyin = "sou "

Case -13356 To -13344: pinyin = "su "

Case -13343 To -13341: pinyin = "suan "

Case -13340 To -13330: pinyin = "sui "

Case -13329 To -13327: pinyin = "sun "

Case -13326 To -13319: pinyin = "suo "

Case -13318 To -13148: pinyin = "ta "

Case -13147 To -13139: pinyin = "tai "

Case -13138 To -13121: pinyin = "tan "

Case -13120 To -13108: pinyin = "tang "

Case -13107 To -13097: pinyin = "tao "

Case -13096 To -13096: pinyin = "te "

Case -13095 To -13092: pinyin = "teng "

Case -13091 To -13077: pinyin = "ti "

Case -13076 To -13069: pinyin = "tian "

Case -13068 To -13064: pinyin = "tiao "

Case -13063 To -13061: pinyin = "tie "

Case -13060 To -12889: pinyin = "ting "

Case -12888 To -12876: pinyin = "tong "

Case -12875 To -12872: pinyin = "tou "

Case -12871 To -12861: pinyin = "tu "

Case -12860 To -12859: pinyin = "tuan "

Case -12858 To -12853: pinyin = "tui "

Case -12852 To -12850: pinyin = "tun "

Case -12849 To -12839: pinyin = "tuo "

Case -12838 To -12832: pinyin = "wa "

Case -12831 To -12830: pinyin = "wai "

Case -12829 To -12813: pinyin = "wan "

Case -12812 To -12803: pinyin = "wang "

Case -12802 To -12608: pinyin = "wei "

Case -12607 To -12598: pinyin = "wen "

Case -12597 To -12595: pinyin = "weng "

Case -12594 To -12586: pinyin = "wo "

Case -12585 To -12557: pinyin = "wu "

Case -12556 To -12360: pinyin = "xi "

Case -12359 To -12347: pinyin = "xia "

Case -12346 To -12321: pinyin = "xian "

Case -12320 To -12301: pinyin = "xiang "

Case -12300 To -12121: pinyin = "xiao "

Case -12120 To -12100: pinyin = "xie "

Case -12099 To -12090: pinyin = "xin "

Case -12089 To -12075: pinyin = "xing "

Case -12074 To -12068: pinyin = "xiong "

Case -12067 To -12059: pinyin = "xiu "

Case -12058 To -12040: pinyin = "xu "

Case -12039 To -11868: pinyin = "xuan "

Case -11867 To -11862: pinyin = "xue "

Case -11861 To -11848: pinyin = "xun "

Case -11847 To -11832: pinyin = "ya "

Case -11831 To -11799: pinyin = "yan "

Case -11798 To -11782: pinyin = "yang "

Case -11781 To -11605: pinyin = "yao "

Case -11604 To -11590: pinyin = "ye "

Case -11589 To -11537: pinyin = "yi "

Case -11536 To -11359: pinyin = "yin "

Case -11358 To -11341: pinyin = "ying "

Case -11340 To -11340: pinyin = "yo "

Case -11339 To -11325: pinyin = "yong "

Case -11324 To -11304: pinyin = "you "

Case -11303 To -11098: pinyin = "yu "

Case -11097 To -11078: pinyin = "yuan "

Case -11077 To -11068: pinyin = "yue "

Case -11067 To -11056: pinyin = "yun "

Case -11055 To -11053: pinyin = "za "

Case -11052 To -11046: pinyin = "zai "

Case -11045 To -11042: pinyin = "zan "

Case -11041 To -11039: pinyin = "zang "

Case -11038 To -11025: pinyin = "zao "

Case -11024 To -11021: pinyin = "ze "

Case -11020 To -11020: pinyin = "zei "

Case -11019 To -11019: pinyin = "zen "

Case -11018 To -11015: pinyin = "zeng "

Case -11014 To -10839: pinyin = "zha "

Case -10838 To -10833: pinyin = "zhai "

Case -10832 To -10816: pinyin = "zhan "

Case -10815 To -10801: pinyin = "zhang "

Case -10800 To -10791: pinyin = "zhao "

Case -10790 To -10781: pinyin = "zhe "

Case -10780 To -10765: pinyin = "zhen "

Case -10764 To -10588: pinyin = "zheng "

Case -10587 To -10545: pinyin = "zhi "

Case -10544 To -10534: pinyin = "zhong "

Case -10533 To -10520: pinyin = "zhou "

Case -10519 To -10332: pinyin = "zhu "

Case -10331 To -10330: pinyin = "zhua "

Case -10329 To -10329: pinyin = "zhuai "

Case -10328 To -10323: pinyin = "zhuan "

Case -10322 To -10316: pinyin = "zhuang "

Case -10315 To -10310: pinyin = "zhui "

Case -10309 To -10308: pinyin = "zhun "

Case -10307 To -10297: pinyin = "zhuo "

Case -10296 To -10282: pinyin = "zi "

Case -10281 To -10275: pinyin = "zong "

Case -10274 To -10271: pinyin = "zou "

Case -10270 To -10263: pinyin = "zu "

Case -10262 To -10261: pinyin = "zuan "

Case -10260 To -10257: pinyin = "zui "

Case -10256 To -10255: pinyin = "zun "

Case -10254 To -10254: pinyin = "zuo "

Case Else: pinyin = p

End Select

End Function

Function getpy(str)

For i = 1 To Len(str)

getpy = getpy & pinyin(Mid(str, i, 1))

Next i

End Function

图片 12

第六步,输入拼音转换函数。

图片 13

快速复制函数完成转换。

图片 14

EXCEL如何自动将姓名转换为拼音操作演示完成。

       Sub t4()

     End Sub

             sh.Range("a1") = "测试"

      Range("A1").Copy Range("A2")   ’将A1中的内容复制到A2

F.Do While……Loop语句

      MsgBox "在第" & x & "行出错了"

    End Sub

            Sheets("sheet2").Select

   1 变量的类型

        If Len(sr) = 0 Or Len(sr) = 5 Then GoTo 100

    End Sub

       Cells(x, 3) = Cells(x, 2) * Cells(x, 1)

  B.IIF判断语句

 F、 excel文件复制和删除

    E、 excel工作表的复制

      Sub 判断3()

     Sub 判断1() '单条件判断

                                                                                                                               

        Sub s8() '判断工作表是否添加了保护密码

        Sub s3()

       End Sub  

         Sub e1()

图1 选中“文件”—“选项”

     End Sub

  End Sub

   Dim rg As Range

         Sheets("Sheet1").Move after:=Sheets(Sheets.Count) 'sheet1移动到所有工作表的最后面

    Range("b2") = 0.05

    Sub W6()

       MsgBox "A文件存在"

      Case Else

10.在一个乡政府的文件中要求将Excel中一个表格的身份证号,配对并填充到另一个表中。宏代码如下:

B、代码窗口

                                                                                

    wb.Close False

       Dim X As Integer    ’X存放整数类型

    Sub W2()

    x = 1

      End Sub

      图片 15

         逻辑型又称布尔型,其数据只有True(真)和False(假)两个值

     workbooks(2),按打开顺序,第二个打开的工作簿。

    End Sub

           For x = 1 To 100

      End Function

         Range("b1") = "正数"

        Sub t5()

           Dim wb As Workbook

     End Sub

   A、excel工作表的分类

     (2)长整型(Long):

       End If

           Range("b1") = "正数"

    Do While x < 18

              Cells(1, 1) = x

         Exit Do

    End Sub

         Range("d16") = Evaluate("=SUMIF(A2:A6,""b"",B2:B6)")

'对应工程窗口的对象和模板,显示其所具体的一些特征。

        Debug.Print "第" & x & "次运行结果:" & st

        Workbooks(2).Sheets(2).Range("a1") = 200

                                                    图7 运行结果

      Dim wb As Workbook

             For x = 1 To 100

     Option Explicit

                                                                                                                                                     

       End Sub

  ElseIf Range("a2") > 3000 Then

                                                                                                                                           

                                                                                                                                            

    (7)日期型(Date)

             Dim x As Integer

    Set wb = Workbooks.Open("D:/B.xls")

   3 声明变量

        Dim rg As Range      ’rg是单元格类型

                                                           

    Sub s1()

         Sub t7()

     B、 excel工作表的插入

     Sub t6()

  

       Sub s5() '在本工作簿中

   B、工作簿窗口

   A.if判断语句

       ' public 变量

        Select Case Range("a1").Value

           Next x

          Do

     Next x

   图片 16

         Dim x As Integer

      Next

      Dim x As Integer

   Sub t5()

      Next x

 D、本地窗口

     Cells(行数,列数)    Range(“B3”)和Cells(3,2)表示相同的单元格

      Exit Sub

     Thisworkbook,VBA程序所在的工作簿,无论你打开多少个工作簿,无论当前是哪个工作簿是活动的,thisworkbook就是指它所在的工作簿。

      Workbooks  工作簿集合,泛指excel文件或工作簿

      Range("d4") = Range("b4") * Range("c4")   ’将b4单元格与c4单元格相乘并赋值给d4单元格

 

         Next J

       Sheets("Sheet2").Range("a1").Value = "abcd"   ’给sheet2工作表的a1单元格填充字符串“abcd”

        Function ff()

    4、 放数组

单击向右的绿三角,即可以运行。

Sub select区间判断()

   Exit Sub

   Range("b2") = 0.05

           MsgBox qq

     3、 放对象

   

      Dim rg As Range

             Exit Sub

     End Sub

      excel文件就是excel工作簿,excel文件打开需要excel程的支持

         '逐句运行

      For Each rg In Range("a1:b7,d5:e9")

      Sub ttt3()

7.VBA对象

           Range("b1") = "0"

     rg = rg.Offset(0, -1) * rg.Offset(0, -2)

                                                                                                                           

   Sub t3()

         Dim x As Integer

         Case Is = 0

End Sub

                Exit For

                                                                                                                               

     一般情况下,过程级变量在过程运行结束后就会自动从内存中释放,而只有一些从外部借用的对象变量才需要使用set 变量=nothing进行释放。

          Range("A2").Interior.ColorIndex = 3  ’将A2单元格的背景颜色设置为红色

5.编写一个宏。

     Range("d" & x) = Range("b" & x) * Range("c" & x)

 

      FileCopy "D:/ABC.XLS", "E:/ABCd.XLS"

12.循环语句

       c、Exit for语句

         用两个“#”符号把日期和时间的值括起来       如:#08/20/2001#、#2001-08-20#

         Range("A2").Font.ColorIndex = 3      ’将A2单元格的字体颜色设置为红色

     Sub 配对()

          For X = 1 To 10

               wb.SaveAs ThisWorkbook.Path & "/1日.xls"

      End Sub

            Sheets("模板").Copy

     Sheet1 表示第一个插入的工作表,Sheet2表示第二个插入的工作表....

  Sub w4()

       Windows("A.xls"),A工作簿的窗口,使用windows可以设置工作簿窗口的状态,如是否隐藏等。

  End Sub

      End Select

  End Sub

         End Function

          Exit Sub

       For X = 1 To 10

                                                                                                                            

          Loop Until x = 100

 

    a 注释文字的设置

B、 Exit语句:退出指定的语句

     Sheets("工作表名称")

 C.在程序中应运语句。

     End Sub

    VBA属性就是VBA对象所具有的特点,表示某个对象的属性如下:对象.属性=属性值

     End Sub

    c 代码强制转行的设置

     a、Goto语句,跳转到指定的地方

 Case Is > 3000

14.函数与公式

   2 模块级变量:变量的值只在本模块中保持,工作簿关闭时随时释放   

     For x = 1 To 10

         Case Is > 0

      Dim I, J As Integer

  图片 17

       End Sub

        Dim X As Integer

      Function wn()

              Range("b1") = 100

        Windows(2).Visible = True

             If x = 5 Then

 

           rg = 0

     A、判断A工作表文件是否存在

  End If

    Return    '跳到gosub 100 这一句

E.Do ……Loop Until语句

              If x = 5 Then

   Range("b2") = 0.01

c、on error resume next '遇到错误,跳过继续执行下一句

          sh.Range("a1") = 100

  Sub ttt4()

           Range("b1") = "负数"

   1 过程级变量:过程结束,变量值释放

     Dim x As Integer, st As String

 

  图片 18

         Sub e2()

    注:“100:”就是一个Goto语句可以跳入的标志。“Len(sr)=0”表示输入框没有输入,“Len(sr) = 5”表取消。其实质就是“false”是5个字符。

    Loop Until x = 18

                                                           

9.VBA方法

b、gosub..return ,跳过去,再跳回来

   VBA方法是作用于VBA对象上的动作,表示用某个方法作用于VBA的对象上,可以用下面的格式:

        If rg = "" Then

    Sheet1.Move before:=Sheets("Sheet3")   ’将sheet1表移动到sheet3之前

         Range("b1") = "负数或0"

                                                                              

   Dim x As Integer

       Dim X As Integer 'x就是一个整形变量

     wb.SaveAs "D:/B.xls"

       Sub s1()

        Sub t2()

     图片 19

       Sub t4()

              sh.Name = "1日"

6.VBA语句

         在VB中字符串常量是用双引号“ ”括起的一串字符,例 如"ABC","abcdefg","123","0","VB程序设计"等。

    a 显示工作簿工作表对象

      sheets  工作表集合,泛指excel各种工作表

     Workbooks 代表工作簿集合,所有的工作簿,Workbooks(N),表示已打开的第N个工作簿

        Sub s7()

        End Sub

       Cells(x, 4) = "=b" & x & "*c" & x     ’将b列乘以c列赋值给d列

     End Sub

      x = x 1

   Sub t4()

           End If

    Next x

         Set rg = Range("a1") ’Set关键词就是给对象变量指定值

      Range("a1").Value = 100     ’给单元格a1填充数值100

     Activecell 正在选中或编辑的单元格

                                                                              

        End Sub

               Exit Do

               wb.Sheets(1).Range("b1") = "测试"

            Next x

range("a1")=10

       Sub t3()

         表示日期和时间

      Next rg

                图12 offset函数分析图

            arr(X) = X

  B.工作表对象

       wb.Sheets("sheet1").Range("a1") = "abcd"

   Sub W3()

       Else

    A、excel文件和工作簿概念

                                                                                                                             

 

 

 Case 0 To 1000

         Next

        Sub t2()

           Application.DisplayAlerts = False

           End If

      End Sub

     Sub t7()

   Range("b2") = 0.03

    Dim x As Integer

         Sub t6()

      Workbooks("A.xls"),名称为A的excel工作簿

      Range("d3") = Range("b3") * Range("c3")   ’将b3单元格与c3单元格相乘并赋值给d3单元格

    End Sub

     Cells(x, 1) = "偶数"

      Cells(x, 4) = Cells(x, 2) * Cells(x, 3)

    e 对象列表框和过程列表框

        Next X

    Range("b2") = 0.03

          表示-2,147,483,648至2,147,483,647之间的整数

  C.select判断

     D、 excel工作表的移动

        sr = Application.InputBox("请输入数字", "输入提示")

             ff = 100

    Next rg

C、变量的类型和声明

                Cells(1, 1) = x

          rg = 100

   ’注:offset就是偏移,针对d2而言,Offset(0, -1)指的就是向左移动1个单元格,即c2;而Offset(0, -2)指的就是向左移动2个单元格,即b2;第一个参数是垂直移动,正为向上,负为向下;第二个参数是水平移动,正为向右,负为向左。

    MsgBox wb.Sheets("sheet1").Range("a1").Value

      If Cells(x 1, 1) <> Cells(x, 1) 1 Then

          m = 7

      Dim x As Integer

    Do

             If x = 5 Then

      Range("C20") = VBA.InStr(Range("a20"), "E")

                                                                                          

  图片 20

      Range("d6") = Range("b6") * Range("c6")   ’将b6单元格与c6单元格相乘并赋值给d6单元格

                                                                                          

B、用VBA在单元格输入带引号的公式

     Sub W1()

   '在本地窗口中可以显示运行中断时对象信息、变量值、数组信息等

      For x = 2 To 6

    Next x

 宏通常既可以录制又可以手动编程,而VBA一般是通过编程完成。宏可以和VBA相结合使用。

                                                                                                                                     

        End Sub

       End Sub

      End If

  Sub ttt5()

           Application.DisplayAlerts = True

    F、工作表的保存

             End If

     Range ("单元格地址")

              End If

     C、 excel工作表隐藏和取消隐藏

A、用VBA在单元格中输入普通公式

     Sub t1()

  If Range("a2") <= 1000 Then

   ’注:在a1到b7单元区域和d5到e9单元格区域中的遍历所有单元格,若为空,就赋值0。

    Sub ttt1()

B.运行宏

       Dim st As String    ’st存放字符串

   

 C、立即窗口

13. 学习变量

      excel工作表有两大类,一类是我们平常用的工作表(worksheet),另一类是图表、宏表等。这两类的统称是sheets

     Sub t1()

         For X = 1 To Sheets.Count

      On Error Resume Next

       Sub t3()

(注:为了提高word2010中插入的图片的质量,“文件”——“选项”——“高级”——选中“不压缩文件图像质量”——“确定”。)

         Cells(x, 3) = Cells(x, 2) * Cells(x, 1)

   ’注:Cell(行,列),即上文中的x指的是行。将第二列和第三列相乘赋值给第四列。

   Sub w5()

                                                                                                                                            

       End Sub

     Next x

           sh.Name = "模板"

            Range("b1") = "负数或0"

                                                                                                                                        

    

         

     ’单元格的内部的填充色

 Case 1001 To 3000

           Set sh = ActiveSheet

        End Sub

  A.“开发工具”——“录制宏”——宏名为“输入100”——“录制”——在A1单元格中输入100——“停止”。

   Sub t5()

  Sub if区间判断()

  A.工作簿对象

 

17、Excel文件操作的几个概念

    End Sub

   注:将10000行数据中每隔3行的b列和c列相乘赋值给d列。

   End Sub

       For X = 1 To 10

     Sub t1()

         Sheets("Sheet2").Move before:=Sheets("sheet1") 'sheet2移动到sheet1前面

D、 excel文件打开和关闭  

             MsgBox "A工作表存在"

E、 excel文件保存和备份

      b、Exit function语句

     End Sub

    d 类模块

D.循环语句。

   B、小盒子里可以放什么?

        Dim x As Integer

      Sub t1()

8. VBA属性

         Cells(x, 2) = "断点"

       End Sub

         End Sub

作用:强制退出所有正在运行的程序。

                 Exit Sub

        st = st & "Excel精英培训"

        End Sub

            For x = 1 To 100

        End Sub

      For X = 1 To Windows.Count

立即窗口可以把运行过程中的值立即显示出来,主要用于程序的调试

            MsgBox "A-G"

          Dim arr(1 To 10) As Integer, X As Integer  

     ’竟然没写入End IF,提示错误“Next 缺少 For”

     (4)双精度实型(Double):

     ThisWorkBook '代码所在的工作簿

       End Sub

     Dim x As Integer, k As Integer

     End Sub

          MsgBox "A文件打开了"

Option Explicit

   2 为什么要声明变量

    End Sub

         Range("d9") = Evaluate("=SUM(B2:B6*C2:C6)")

    End Sub

  End Sub

    Sub d()

4.录制一个宏。

    For x = 1 To 10

      Next x

                                                                                                                                  

     (3)单精度实型(Single):

    b 代码缩进的设置

 

             Cells(1, 1) = x

            Set wb = ActiveWorkbook

         If Range("a3") < "G" Then

   Sub s2()

C、用VBA在单元格中输入数组公式

      Select Case Range("a1").Value

    Do

   On Error Resume Next

什么是VBA?它有什么作用?

  A.宏程序语句。

      Range("d5") = Range("b5") * Range("c5")   ’将b5单元格与c5单元格相乘并赋值给d5单元格

        Range("d8") = Application.WorksheeFunction.CountIf(Range("A1:A10"), "B")

   End Sub

     Sub t2()

    On Error GoTo 100

       Next I

   图片 21

   End Sub

        End Sub

D、利用单元格公式返回值

    (6) 逻辑型(Boolean)

C、For Next语句

  ElseIf Range("a2") <= 3000 Then

             End If

C、excel文件新建和保存

      For x = 1 To 10

        Sub e3()

  I、工作表的选取

        图片 22

  Sub t3()

        st = st & Cells(x, 1)

        End Sub

     For x = 1 To 10

   End Sub

      End Sub

         Sheets(2).Visible = True

    End Sub

    Sub 判断1() '单条件判断

    100:

         Exit Sub

 宏(Macro),是一种批量处理。

11.判断语句

     Else

  20、工作表的操作

     Range("c16") = "=SUMIF(A2:A6,""b"",B2:B6)" '遇到单引号就把单引号加倍

19、工作表的概念

      Range("d2") = Range("b2") * Range("c2")   ’将b2单元格与c2单元格相乘并赋值给d2单元格

 E、变量的释放

    Dim x As Integer

           If Sheets(X).Name = "A" Then

  B、 判断A.Xls文件是否打开

 Select Case Range("a2").Value

         wn = Application.Caller.Parent.Name

               If Sheets("Sheet4").Range("b" & I).Value = Sheets("黄门乡").Range("b" & J).Value Then   

3.什么是宏?宏和VBA有什么关系?

   End Sub

       End Sub

 E.通过插入窗体做小型管理软件。

     d、Exit do 语句

      ’数组arr(1),arr(2),arr(3)……arr(10)都是整形类型

图2 ”自定义功能区”—“开发工具”——“确定”

        Sub s2()

    Dim wb As Workbook

          表示-32768至32767之间的整数           例如:10   110   20

    Sub ttt2()

                                                               图4 代码存放在“模块”中

       Range("d2") = "=b2*c2"   ’将b2乘以c2赋值给d2

      Range("c9").FormulaArray = "=SUM(B2:B6*C2:C6)"

           Range("b1") = 100

 

        100:

    End Sub

A、工程窗口

            x = x 1

      Case Is > 0

          Next X

             MsgBox "工作簿保护了"

     Sheets(n) 表示按排列顺序,第n个工作表

           有效数为15位

        If Windows(X).Caption = "A.XLS" Then

        Sub s4()

 

          Dim sh As Worksheet

         Cells(X, 1) = X

16.VBA分支语句与End语句

           If Sheets("sheet2").ProtectContents = True Then

    d 代码运行和调试

     End If

      Next x

  End Sub

    Sub 判断2() '多条件判断

    For x = 10000 To 2 Step -3

          Set sh = Sheets.Add

  A.“开发工具”——“Visual Basic”——“视图”——“工程资源管理器”——“插入”——“模块”——在右侧输入代码:

           例如:长整型的书写:    23454444554     

d、on error goto  '出错时跳到指定的行数

E、借用工作表函数

  B.函数程序语句

 A.模块中

           Else

      Cells(x, 4) = Cells(x, 2) * Cells(x, 3)

       Sub s6() '另存为新工作簿

   Dim x As Integer

VBA在哪里存放的?怎么运行?

          Sheets(2).Range("a1") = 200

      x = x 1

                                                                                                                                           

   End Sub

     Sub t3()

        End Sub

G.Do ……Loop Until语句

     ActiveWorkbook ,当打开多个excel工作簿时,你正在操作的那个就是ActiveWorkbook(活动工作簿)

F、利用VBA函数

        Dim sr

                                                                                                                                       

   在Excel 2010中若没有“开发工具”项,通过“文件”——“选项”——“自定义功能区”——选中“开发工具”——“确定”(图1)。

     worksheet 也表示工作表,但不包括图表工作表、宏工作表等。

    100:

         End Sub

           Sheets("sheet2").Protect "123"

 D.实现自动化功能。

               wb.Close True

       Sub s9()

     变量通过索引可以存储更多的值,在循环结构中的作用大。

  C.单元格对象

         End Select

 

   

    Range("b2") = 0.01

    End Sub

D.区间判断

     'ActiveSheet ,当打开多个excel工作簿时,你正在操作的那个就是ActiveSheet

     Sub t5()

      If x > 5 Then On Error GoTo 0

   End Sub

    Loop Until x = 14

              Range("b1") = 100

         End Sub

 A、END语句

    Sub ttt()

    Loop

        k = k Cells(x, 1)

      For I = 3 To 225

      wb.SaveCopyAs "D:/ABC.xls"

           Dim x As Integer

        Sheets("A").Range("a1") = 100

         End If

         MsgBox "A工作表不存在"

   Sub t4()

 e、on error goto 0 '取消错误跳转

      Dim x As Integer

图3 最终的界面多出了“开发工具”选项卡

18、Excel文件的操作

       MsgBox "A文件不存在"

    b 窗体

         End Sub

     ' workbooks(2),按打开顺序,第二个打开的工作簿。

End Sub

   ’注:Cell(行,列),即上文中的x指的是行。将第二列和第三列相乘赋值给第四列。

        End If

        End Sub

                                                          图6  选中test并“运行”

     (1)整型(Integer):

       If Cells(x, 1) Mod 2 = 0 Then GoSub 100

            Next x

    Sub t4()

             MsgBox "工作簿没有添加保护"

 End Select

         End Sub

   ’判断第1列中的数据不连续,就在其后的单元格输入一个“断点”。

  End Sub

  C、VBA分支语句

      For x = 1 To 10

 

15. VBE编辑器

     cells 所有单元格

            Range("b1") = "正数"

         Case Else

    c 模块

     Selection 正被选中或选取的单元格或单元格区域

     Dim wb As Workbook

      x = x 1

          Dim sh As Worksheet

   A、什么是变量?

                     Sheets("Sheet4").Range("e" & I).Value = Sheets("黄门乡").Range("d" & J).Value

G、编写自定义函数

     Dim X As Integer

   Sub t2()

     Set wb = Workbooks.Add

     ActiveWorkbook 正在操作的工作簿

     Sub t1()

                                                   图5 右绿三角运行  

          Windows("A.xls").Visible = False

                                                                                                                                    

     ActiveSheet 表示活动工作表,光标所在工作表

 

       Next X

      Kill "D:/ABC.XLS"

    Sub 判断4()

    For Each rg In Range("d2:d18")

  B.For each 循环语句

      wb.Save

       Sub s10()

     所谓变量,就是可变的量。就好象在内存中临时存放的一个小盒子,这个小盒子放的什么物体不固定。

      1、放数字   2、放文本

               Exit Function

       '如t1

            m = 1

        Sub t1()

   Dim x As Integer

   Sub t2()

 

     If Len(Dir("d:/A.xls")) = 0 Then

 C.编写自定义函数。

      ’Range("A1").Copy Destination:= Range("A2")

   End Sub

              Sheets("模板").Delete

              End If

  B.“宏”——选中“输入100”——“执行”。

      Sub t2()

   H、工作表删除

               If x = 5 Then

       End Sub

      Sheets(1).Name = "工作表改名了"   ’给工作簿重命名为“工作表改名了”

               End If

   对象.方法  参数名称:=参数值

          MsgBox m

End Sub

D、For Each语句(应用offset方法来制定单元格)

 

     End Sub

     Dim m As Integer

   A、 判断A.Xls文件是否存在

     For x = 1 To 10

           Sheets("模板").Copy before:=Sheets(1)

   A.单语句代码

TAG标签: 韦德娱乐1946
版权声明:本文由韦德娱乐1946_韦德娱乐1946网页版|韦德国际1946官网发布于韦德娱乐1946,转载请注明出处:EXCEL如何自动将姓名转换为拼音,Excel汉字调换拼