澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

宏技巧集绵,Excel汉字转换拼音首字母缩写的函数

2019-05-05 作者:办公软件   |   浏览(69)

问题:EXCEL怎么样自动将人名调换为拼音?

打开Excel->工具->宏->Viaual Basic编辑器
在弹出来的窗口中对着VBAproject点右键->插入->模块
上面会冒出一个名称叫"模块一",点击
在右侧的空白栏中粘贴以下内容:
''''''''''''''''''''''
'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
********复制到此截至,本行不复制*******

 

问题:在常常干活中会遇到,知道当中三个数量,比方姓名,在报表中输入人名后,想要自动带出网页中该姓名对应的连带数据,举例该姓名的电话机,地址等音信,怎么样成功吗?

问题:想给孩子做三个机动抽提写乌Crane语单词的小程序,正是把具有单词手工业输入Excel,每一种单词一个单元格。n当按下有些键后,自动从题Curry腾出部分单词组成表格,供子女练习,求大神辅助,不胜谢谢。

回答:

今昔转变函数已编写制定成功!关掉此编缉的窗口。
要在Excel中央银行使,方法如下:
A1         A2
中国     =getpy(A1)

什么样是VBA?它有何遵守?

回答:

回答:

多谢新浪的特邀,我们好,小编是Excel美妙之旅,专注于Excel知识和技艺的享受

 A.实现Excel中尚无达成的功力。

Excel抓取并询问互连网数据可以利用“获取和转变” “查找引用函数”的效益结合来促成。

用Excel做1个机动出题的报表分外轻松,上次自家在陪孙子参与他们的词汇竞赛以前就做了一个简约,即:从一雨后冬笋单词里,随机提取七个单词放入九宫格内,然后给壹分钟的大运回想……,具体如下:

EXCEL怎么样自动将姓名转变为拼音?以下是本身的缓和格局,不是用VBA,而是借助word来操作

 B.升高运维速度。

例:下图是百度周详“奥林匹克运动会”网页中的一个表格,大家以此为例达成抓取该表格至Excel中,并且可以透过输入第几届来查询相应的开办城市。

壹、计划单词表

本来,准备单词表的还要,还足以在Excel里做个互连网的解析(这几个公式看起来长,其实就是多个函数而已,其中是一长串的网站)和背诵进度的记录等等,如下图所示:

澳门新萄京官方网站 1

操作思路:

 C.编写自定义函数。

澳门新萄京官方网站 2

2、通过函数完结出题

一、结合RandBetween函数、CountA函数随机拿到单词位置,如下图所示:

澳门新萄京官方网站 3

二、通过Index函数依据随意地点收取单词,如下图所示:

澳门新萄京官方网站 4

本来,你能够将这个函数组合起来写成二个公式,也拾1分简单,自身入手试一下就能够。总的来讲,用Excel自动出题并轻巧,关键是对Excel中常用的函数精晓好。

澳门新萄京官方网站 5


澳门新萄京官方网站 6

率先:把excel中的姓名粘贴到word中,借助word增多拼音,再把word中增添拼音的结果粘贴到excel中

 D.达成自动化作用。

Step一:使用“获取和转移”效率将互连网数据抓取至Excel中

逐一点击“数据选项卡”、“新建查询”、“从其余源”、“从Web”。

澳门新萄京官方网站 7

弹出如下窗口,手动将百度周详“奥林匹克运动会”的网站复制粘入UMuranoL栏,并点击分明。

澳门新萄京官方网站 8

Excel与网页连接必要自然时间,稍等片刻后会弹出如下窗口,左边列表中的每一个Table都代表该网页中的3个表格,挨个点击预览后发觉,Table三是我们所需的多少。

澳门新萄京官方网站 9

点开下方的“加载”旁边的下拉箭头,选择“加载到”。

澳门新萄京官方网站 10

在弹出的窗口中,在“采用想要在做事薄中查看此数据的艺术”下抉择“表”,并点击加载。

澳门新萄京官方网站 11

如图,网页表格中的数据已被抓取至Excel中。

澳门新萄京官方网站 12

梯次点击“表格工具”、“设计”,将“表名称”改为奥林匹克运动会。

澳门新萄京官方网站 13

自家是大海,微软认证Excel专家,公司签署Power BI顾问

第三:用PHONETIC函数获得拼音

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

Step二:使用“查找与引用”函数实现数据查询

树立查询区域,包罗“届数”和“主办城市”,在届数中自便采用1届输入,下图输入“第0捌届”,在主持城市下输入vlookup函数,能够获取第0八届奥林匹克运动会的主办城市是法国巴黎,当改换届数时,对应的牵头城市也随之更动。

公式:=VLOOKUP([届数],奥运会[#全部],4,0)

澳门新萄京官方网站 14

注意点:若网页中的数据变动较频繁,则能够安装链接网页的数额定期刷新:

一将鼠标定位于导入的数据区域中,切换成选项卡,点击下拉箭头→

澳门新萄京官方网站 15

贰在弹出的对话框中,设置,比方设置为十分钟进行刷新。那样,每隔10分钟数据就能刷新一回,时刻保险收获的数量位最新的。

澳门新萄京官方网站 16


style="font-weight: bold;">「精进Excel」系头条签订契约笔者,关心自身,如若大肆点开3篇小说,未有你想要的学问,算小编耍流氓!

回答:

世家好,我是@Excel实例录像网址长@接待私信也许邀约笔者回答Excel相关难点!


有人在群里问手提式无线电话机号怎么批量查归属地,第2认为是百度时而,结果还真没找到好用的,既然如此,小编就和睦写3个吗!首先找了多少个webapi,找到个挺好用的,就用vba写了个自定义函数,测试下感觉照旧挺好用,速度也挺快

澳门新萄京官方网站 17

style="font-weight: bold;">源文件下载链接请私信回复6300伍就可以

应用方法:

①.在本表中一向在A壹列输入手提式有线电话机号就可以

二.要在其他表中,alt f11开拓vbe编辑器,复制模块中代码,在你的新表中国建工业总会公司立模块,粘贴代码就可以

3.函数参数表达

GetPhoneInfo(号码,参数)

编号—即单个手提式有线电话机号

参数(1,2,3,4):1-城市,2-省,3-运营商, 4-全部

代码如下

Dim ObjXML As Object

宏技巧集绵,Excel汉字转换拼音首字母缩写的函数。Function GetPhoneInfo(number, Optional para As Byte = 1)

'获取手机号对应的核心音信 默感到城市

'para:1-城市,2-省,3-运营商,4,全部

Dim s As String

s = GetBody("" & number)

Select Case para

Case 1

GetPhoneInfo = HtmlFilter(s, "City"":""", """")

Case 2

GetPhoneInfo = HtmlFilter(s, "Province"":""", """")

Case 3

GetPhoneInfo = HtmlFilter(s, "TO"":""", """")

Case 4

GetPhoneInfo = HtmlFilter(s, "City"":""", """") & "," & HtmlFilter(s, "Province"":""", """") & "," & HtmlFilter(s, "TO"":""", """")

End Select

GetPhoneInfo = Replace(GetPhoneInfo, " ", "")

End Function

Private Sub Test()

Dim i&, j&, k&, arr, brr

url = ""

Debug.Print GetBody(url)

End Sub

'''倘使出现乱码,UTF-八可改为GB231二

Public Function GetBody(ByVal url$, Optional ByVal Coding$ = "utf-8")

On Error Resume Next

Set ObjXML = CreateObject("Microsoft.XMLHTTP")

With ObjXML

.Open "Get", url, False, "", ""

'.setRequestHeader "If-Modified-Since", "0"

'.setRequestHeader "User-Agent", _

".Mozilla/5.0 (Windows NT 6.1; WOW64; rv:47.0) Gecko/20100101 Firefox/47.0"

.Send

GetBody = .ResponseBody

澳门新萄京官方网站,End With

GetBody = BytesToBstr(GetBody, Coding)

Set ObjXML = Nothing

End Function

Public Function BytesToBstr(strBody, CodeBase)

Dim ObjStream

Set ObjStream = CreateObject("Adodb.Stream")

With ObjStream

.Type = 1: .Mode = 3: .Open:

.Write strBody: .Position = 0: .Type = 2: .Charset = CodeBase

BytesToBstr = .ReadText: .Close

End With

Set ObjStream = Nothing

End Function

Public Function HtmlFilter(ByVal htmlText$, ByVal Label1$, ByVal label2$)

'再次来到html字符串lable壹和不久前的lable贰标签中的数据

Dim pStart As Long, pStop As Long

pStart = InStr(htmlText, Label1) Len(Label1)

If pStart <> 0 Then

pStop = InStr(pStart, htmlText, label2)

HtmlFilter = Mid(htmlText, pStart, pStop - pStart)

End If

End Function

回答:

正式的人做正规专门的工作。

让大家一道念书,共同升高!

回答:

您好,看到那些主题材料本人相当慢乐,因为刚刚本人做了二个简短的随机出题的VBA小程序,也是3个VBA小练习,可能对您有救助,因为无法上传附属类小部件,只好简要讲述用法。借使急需源文件,能够私信俺。

澳门新萄京官方网站 18

一、新建三个EXCEL职业簿,命名称为“随机出题”,将率先个办事表命名称叫“题库”,新建一个专业表,命名字为“试卷”。具体方法请参见如上动图。

澳门新萄京官方网站 19

二、在名字为“题库”的做事表中,A列为编号,B列为英文,C列为汉语。将难题对应粘贴进去。在名称为“试卷”的劳作表A1单元格录入“标题”2字作为标题。具体方法请参见如上动图。

澳门新萄京官方网站 20

三、点击→Visual Basic→右键点击插入模块,将上边的代码复制进去。具体方法请参见如上动图。

Sub 随机出题()

Dim d As Object, arr, rng As Range, arr1, n As Integer, i As Integer, j As Integer, x As Long

Set d = CreateObject("scripting.dictionary")

x = Application.CountA(Sheets("题库").Range("a:a"))

line1: i = InputBox("请输入随机出题数量", "出题数量")

If i > x Then

MsgBox ("数量抢先标题总的数量" & x & ",请重新输入")

GoTo line1

End If

line2: j = InputBox("请输入随机出题类型", "出题类型", "一为英文,二为华语")

If j <> 1 And j <> 2 Then

MsgBox ("输入内容不符合须求,请重新输入")

GoTo line2

End If

Do

d.Item(Application.RandBetween(1, i)) = ""

Loop Until d.Count = i

ReDim arr(1 To d.Count * 2)

For Each arr1 In d.keys

n = n 1

Set rng = Sheets("题库").Range("a:a").Find(arr1)

arr(n) = n & "、" & rng.Offset(0, j).Value

Next

Rows("2:1048576").Clear

Range("a2").Resize(d.Count * 2, 1) = Application.Transpose(arr)

End Sub

澳门新萄京官方网站 21

四、关闭VBA编辑器,回到名叫“试卷”的专门的学问表,插入猖獗形状作为运营宏的开关,选中形状,右键单击,选用内定宏就能够。具体方法请参见如上动图。

澳门新萄京官方网站 22

伍、将文件另存为后缀名称叫.xlsm的格式。具体方法请参见如上动图。

澳门新萄京官方网站 23

6、具体使用办法如上动图。

如需课件源文件,请私信小编o(* ̄︶ ̄*)o

回答:

世家好,笔者是@EXCEL实例录像网址长@欢迎关心自己的头条,每日都会分享Excel实战技艺!


参照一下上边包车型地铁案例

其3:用substitute函数将拼音声调去掉(这一步视要求,如无需删除声调能够忽略)

VBA在哪个地方存放的?怎么运作?

若果只是偶然有那几个职分,依旧在互连网出点钱,找人做了。

消费的钱真的不多。几百元充足了。

EXCEL版小学数学四则混合运算随机出题模板 小学老师及家长必备神器 录像演示

n

{!-- PGC_VIDEO:{"thumb_url": "2a390002d930ea0a0242", "thumb_height": 358, "group_id": 6434820503055237377, "media_id": 50090367555, "neardup_id": 694872295238498507, "vname": "\u5f55\u5236_2017_06_23_20_13_59_888.mp4", "video_size": {"high": {"h": 478, "subjective_score": 0, "w": 854, "file_size": 5229508}, "ultra": {"h": 718, "subjective_score": 0, "w": 1280, "file_size": 7756124}, "normal": {"h": 358, "subjective_score": 0, "w": 640, "file_size": 4183785}}, "sp": "toutiao", "vposter": "", "vid": "a23dd58cb99a454daa20e550554627cc", "thumb_width": 640, "item_id": 6435027917507396098, "vu": "a23dd58cb99a454daa20e550554627cc", "duration": 260, "user_id": 2962477694, "md5": "0917655c62f64d98c338b60ade49dd6f", "thumb_uri": "2a390002d930ea0a0242", "hash_id": 694872295238498507} --}

回答:

先在某一列填入题库,在另壹列写入随机函数randbetween来得到随机数,在另1列用indirect函数依据随机数收取题库列的单词,会有微量双重,不过简单轻巧完成。每一次按一下空格键就可以再一次出题。

澳门新萄京官方网站 24

 A.模块中

假如果平时职务多,且有早晚的功底,学习一下未必不可。

老猫是通过VBA操作的,写三个代码,抓取数据,也很有益于。

老猫正在开垦的一款足彩软件程序救市从网络抓取大批量数额。然后分析和展望足彩。

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

   在Excel 20第10中学若未有“开拓工具”项,通过“文件”——“选项”——“自定义功用区”——选中“开拓工具”——“明确”(图一)。

那是抓取的交锋列表:

澳门新萄京官方网站 25

一)第一:把excel中的姓名粘贴到word中,借助word增添拼音,注意粘贴的时候采用只保留文本这几个选项,增多拼音后再粘贴回Excel

(注:为了加强word20第10中学插入的图片的成色,“文件”——“选项”——“高档”——选中“不压缩文件图像品质”——“明确”。)

那是VBA程序代码

澳门新萄京官方网站 26

澳门新萄京官方网站 27

 

那是抓取的赔率数据

澳门新萄京官方网站 28

总来说之,假如想学是易如反掌的。

回答:

以EXCEL200叁为例来给你验证。

一、首先张开EXCEL200三,在菜单栏找到“数据”然后在下拉菜单点击“导入外部数据-新建WEB查询”
澳门新萄京官方网站 29
2、然后在开垦的对话框中的地址栏中,将你要导入的网站输入进去,按下转到按键。
澳门新萄京官方网站 30
三、在弹开的对话框中原则要求导入的区域,按下导入按键,那一年,数据就被导入到EXCEL里面啦!
澳门新萄京官方网站 31终极,你的微型Computer得链接网络,要不没有数据,那样导入的便宜是,能够和网址上保持一致,不要求举行手动更新,很有益。

留神粘贴到Word的时候选用只保留文本那么些选项,不然word不恐怕增加拼音

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

澳门新萄京官方网站 32

图2 ”自定义成效区”—“开拓工具”——“鲜明”

第3:用PHONETIC函数获得拼音

 

在C三单元格输入公式= =PHONETIC(B三),然后下拉公式,获得全体人名拼音

图三 最后的分界面多出了“开辟工具”选项卡

澳门新萄京官方网站 33

 

其3:用substitute函数将拼音声调去掉(这一步视要求,如不要求删除声调能够忽略

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

在D三单元格输入公式=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"),"ǖ","ü"),"ǘ","ü"),"ǚ","ü"),"ǜ","ü")

B.运行宏

那么些公式利用substitute函数把拼音中的字母为韵母的唱腔都替换到了不带声调的韵母,也就让拼音的腔调去除了

单击向右的绿三角,即能够运作。

澳门新萄京官方网站 34

 

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

   

回答:

                                                   图5 右绿三角运营  

EXCEL怎样自动将人名转变为拼音?

多谢悟空小秘的约请!

EXCEL怎样自动将姓名转换为拼音,由于EXCEL中从不汉字自动转换拼音的作用,所以要在VBA中自定义一个改造函数。

具体操作步骤

率先步,要把EXCEL文书档案调换为 XLSM格式,启用宏。

澳门新萄京官方网站 35

其次步,在文件,设置,自定义功效区中勾选开垦工具。

澳门新萄京官方网站 36

第三步,打开VBA编辑器。

澳门新萄京官方网站 37

第6步,插入模块。

澳门新萄京官方网站 38

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

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 "

宏技巧集绵,Excel汉字转换拼音首字母缩写的函数。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

澳门新萄京官方网站 39

第陆步,输入拼音调换函数。

澳门新萄京官方网站 40

高速复制函数完毕改变。

澳门新萄京官方网站 41

EXCEL怎么样自动将姓名转变为拼音操作演示实现。

                                                          图6  选中test并“运行”

李先生简介:创办Computer学校,全职从事计算机教学二10余年。

 

头条号:李先生计算机教学课堂,简单实用,清晰明了,专门系统批注Computer知识,软件应用本事,应接关怀。

回答:

脚下用Excel要求选取VBA来落到实处,操作过于复杂,在小白不懂的场合下,也只怕会有安全性难点。

建议换一种思路,那正是选取网址来完毕拼音调换后,再粘贴回Excel中。操作步骤如下:

                                                    图7 运维结果

开垦调换网站

3.怎么是宏?宏和VBA有哪些关系?

操作步骤如下

澳门新萄京官方网站 42

回答:

网络有那些用VBA制成的自定义函数,寻觅下姓名转拼音可以找到好些个的。然后能够像使用普通专门的职业表函数那么采纳,生僻字保险持续,但三千常用汉字正确率照旧相当高的

 宏(Macro),是1种批量拍卖。

 宏经常既能够摄像又能够手动编制程序,而VBA一般是经过编制程序完成。宏能够和VBA相结合使用。

肆.录制贰个宏。

  A.“开荒工具”——“摄像宏”——宏名字为“输入十0”——“摄像”——在A1单元格中输入100——“停止”。

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

  

5.编写制定3个宏。

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

   澳门新萄京官方网站 43

  B. 单击绿右三角就能够运转。大概关闭VBA编辑窗口,单击“宏”——选中“test”——“试行”。

 

6.VBA语句

  A.宏程序语句。

   澳门新萄京官方网站 44

  B.函数程序语句

  澳门新萄京官方网站 45

 C.在程序中应运语句。

  澳门新萄京官方网站 46

D.循环语句。

  澳门新萄京官方网站 47

7.VBA对象

  A.专门的职业簿对象

     Workbooks 代表职业簿集结,全体的职业簿,Workbooks(N),表示已开荒的第N个工作簿

     Workbooks ("工作簿名称")

     ActiveWorkbook 正在操作的专门的学业簿

     ThisWorkBook '代码所在的职业簿

  B.专业表对象

     Sheets("职业表名称")

     Sheet一 表示第叁个插入的工作表,Sheet二意味着第三个插入的干活表....

     Sheets(n) 表示按排列顺序,第n个专门的职业表

     ActiveSheet 表示活动专业表,光标所在专门的学业表

     worksheet 也代表专门的学问表,但不包涵图片专门的学业表、宏专业表等。

  C.单元格对象

     cells 全部单元格

     Range ("单元格地址")

     Cells(行数,列数)    Range(“B三”)和Cells(叁,2)表示一点差距也未有的单元格

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

     Selection 正被入选或选拔的单元格或单元格区域

8. VBA属性

    VBA属性就是VBA对象所具备的特征,表示某些对象的属性如下:对象.属性=属性值

    Sub ttt()

      Range("a一").Value = 十0     ’给单元格a一填充数值100

    End Sub

                                                                                                                                    

    Sub ttt1()

      Sheets(1).Name = "职业表改名了"   ’给专门的学业簿重命名叫“工作表改名了”

    End Sub

                                                                                                                                       

    Sub ttt2()

       Sheets("Sheet二").Range("a1").Value = "abcd"   ’给sheet2专门的学业表的a1单元格填充字符串“abcd”

    End Sub

                                                                                                                                        

      Sub ttt3()

     ’单元格的里边的填充色

          Range("A二").Interior.ColorIndex = ③  ’将A贰单元格的背景颜色设置为彩虹色

         Range("A二").Font.ColorIndex = 三      ’将A二单元格的字体颜色设置为深浅灰

      End Sub

                                                                                                                                           

9.VBA方法

   VBA方法是功力于VBA对象上的动作,表示用有个别方法效果于VBA的靶子上,能够用下边包车型大巴格式:

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

                                                                                                                                            

  Sub ttt4()

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

      Range("A一").Copy Range("A贰")   ’将A第11中学的内容复制到A二

  End Sub

                                                                                                                                            

  Sub ttt5()

    Sheet一.Move before:=Sheets("Sheet三")   ’将sheet一表移动到sheet叁此前

  End Sub

                                                                                                                                           

十.在一个乡政党的文本中须要将Excel中二个报表的身份证号,配对并填充到另1个表中。宏代码如下:

     Sub 配对()

      Dim I, J As Integer

      For I = 3 To 225

           For J = 4 To 930

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

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

              End If

         Next J

       Next I

     End Sub

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

11.决断语句

   A.if决断语句

                                                                                                                           

     Sub 推断1() '单条件剖断

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

            Range("b1") = "正数"

       Else

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

       End If

     End Sub

  B.IIF决断语句

                                                                                                                            

    Sub 判断4()

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

    End Sub

  C.select判断

                                                                                                                             

    Sub 决断一() '单条件决断

      Select Case Range("a1").Value

      Case Is > 0

         Range("b1") = "正数"

      Case Else

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

      End Select

    End Sub

                                                                                                                               

    Sub 推断二() '多规格推断

        Select Case Range("a1").Value

         Case Is > 0

           Range("b1") = "正数"

         Case Is = 0

           Range("b1") = "0"

         Case Else

           Range("b1") = "负数"

         End Select

       End Sub

                                                                                                                                                     

      Sub 判断3()

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

            MsgBox "A-G"

         End If

      End Sub

D.区间判别

                                                                                                                                     

  Sub if区间推断()

  If Range("a2") <= 1000 Then

    Range("b2") = 0.01

  ElseIf Range("a2") <= 3000 Then

    Range("b2") = 0.03

  ElseIf Range("a2") > 3000 Then

    Range("b2") = 0.05

  End If

  End Sub

                                                                                                                               

Sub select区间剖断()

 Select Case Range("a2").Value

 Case 0 To 1000

   Range("b2") = 0.01

 Case 1001 To 3000

   Range("b2") = 0.03

 Case Is > 3000

   Range("b2") = 0.05

 End Select

End Sub

                                                                                                                                  

12.循环语句

   A.单语句代码

     Sub t1()

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

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

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

      Range("d5") = Range("b5") * Range("c伍")   ’将b伍单元格与c五单元格相乘并赋值给d五单元格

      Range("d6") = Range("b6") * Range("c陆")   ’将b陆单元格与c六单元格相乘并赋值给d陆单元格

     End Sub

  B.For each 循环语句

    Sub s1()

      Dim rg As Range

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

        If rg = "" Then

           rg = 0

        End If

      Next rg

    End Sub

   ’注:在a一到b柒单元区域和d5到e玖单元格区域中的遍历全数单元格,若为空,就赋值0。

C、For Next语句

   Sub t2()

   Dim x As Integer

    For x = 10000 To 2 Step -3

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

    Next x

   End Sub

   注:将一千0行数据中每隔叁行的b列和c列相乘赋值给d列。

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

   Sub t3()

   Dim rg As Range

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

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

    Next rg

   End Sub

   ’注:offset便是偏移,针对d2来说,Offset(0, -一)指的就是向左移动三个单元格,即c二;而Offset(0, -2)指的便是向左移动贰个单元格,即b二;第三个参数是笔直运动,正为发展,负为向下;第三个参数是程度位移,正为向右,负为向左。

   

                图12 offset函数分析图

E.Do ……Loop Until语句

   Sub t4()

   Dim x As Integer

    x = 1

    Do

      x = x 1

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

    Loop Until x = 18

   End Sub

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

F.Do While……Loop语句

   Sub t5()

    x = 1

    Do While x < 18

      x = x 1

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

    Loop

   End Sub

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

G.Do ……Loop Until语句

   Sub s2()

    Dim x As Integer

    Do

      x = x 1

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

         Cells(x, 2) = "断点"

         Exit Do

      End If

    Loop Until x = 14

   End Sub

   ’判别第贰列中的数据不接二连三,就在事后的单元格输入三个“断点”。

一三. 学习变量

   A、什么是变量?

     所谓变量,正是可变的量。就好象在内存中权且存放的八个小盒子,那个小盒子放的哪些物体不稳固。

     Dim m As Integer

     Sub t1()

       Dim X As Integer 'x正是八个整形变量

       For X = 1 To 10

         Cells(X, 1) = X

        Next X

      End Sub

   B、小盒子里能够放怎么?

 

      1、放数字   2、放文本

        Sub t2()

       Dim st As String    ’st存放字符串

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

       For X = 1 To 10

        st = st & "Excel精英培养和练习"

       Next X

       End Sub

     3、 放对象

       Sub t3()

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

         Set rg = Range("a一") ’Set关键词正是给目标变量钦点值

          rg = 100

        End Sub

    4、 放数组

       Sub t4()

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

          For X = 1 To 10

            arr(X) = X

          Next X

       End Sub

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

C、变量的项目和注脚

   一 变量的花色

     (1)整型(Integer):

          表示-3276八至3276七里头的整数           比如:10   1十   20

     (2)长整型(Long):

          表示-贰,1四7,4捌三,64八至二,1四七,4八三,6四柒中间的平头

           比方:长整型的书写:    2345444455四     

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

           有效数为5人表示-三.③7E 3八至3.三7E 38之间的实数

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

           有效数为壹伍个人

     (5)字符型(String)

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

    (6) 逻辑型(Boolean)

         逻辑型又称布尔型,其数量唯有True(真)和False(假)八个值

    (7)日期型(Date)

         表示日期和时间

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

   二 为什么要表明变量

     变量通过索引能够储存越多的值,在循环结构中的效用大。

   三 申明变量

      dim public

D、变量的水保周期

   一 进程级变量:进程停止,变量值释放

       '如t1

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

 

         Sub t6()

            m = 1

         End Sub

         

        Sub t5()

          MsgBox m

          m = 7

         End Sub

 

   三 全局级变量: 在全体的模块中都能够调用,值会保存到EXCEL关闭时才会被放飞。

       ' public 变量

         Sub t7()

           MsgBox qq

         End Sub

 E、变量的放走

     一般境况下,进度级变量在经过运维甘休后就能够自动从内部存款和储蓄器中释放,而唯有一些从外表借用的目的变量才须求利用set 变量=nothing进行放飞。

1四.函数与公式

A、用VBA在单元格中输入常见公式

Option Explicit

     Sub t1()

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

     End Sub

     澳门新萄京官方网站 48

     Sub t2()

      Dim x As Integer

      For x = 2 To 6

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

      Next x

     End Sub

     澳门新萄京官方网站 49

 

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

     Sub t3()

     Range("c1陆") = "=SUMIF(A2:A陆,""b"",B二:B陆)" '蒙受单引号就把单引号加倍

     End Sub

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

      澳门新萄京官方网站 50

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

    Sub t4()

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

End Sub

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

D、利用单元格公式再次来到值

     Sub t5()

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

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

     End Sub

E、借用专门的学业表函数

     Sub t6()

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

     End Sub

F、利用VBA函数

     Sub t7()

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

     End Sub

G、编写自定义函数

      Function wn()

         wn = Application.Caller.Parent.Name

      End Function

15. VBE编辑器

A、工程窗口

    a 显示专业簿工作表对象

    b 窗体

    c 模块

    d 类模块

range("a1")=10

'对应工程窗口的对象和模板,呈现其所具体的有的风味。

    

B、代码窗口

    a 注释文字的设置

    b 代码缩进的装置

    c 代码强制转行的安装

    d 代码运维和调节和测试

         '逐句运转

         '设置断点

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

 C、马上窗口

立刻窗口能够把运维进程中的值登时展现出来,主要用来程序的调治

    Sub d()

     Dim x As Integer, st As String

     For x = 1 To 10

        st = st & Cells(x, 1)

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

     Next x

    End Sub

 D、本地窗口

   '在本地窗口中得以显示运维中断时对象音讯、变量值、数组音讯等

   Sub d1()

     Dim x As Integer, k As Integer

     For x = 1 To 10

        k = k Cells(x, 1)

     Next x

   End Sub

1陆.VBA分支语句与End语句

   

 

 A、END语句

效益:强制退出全体正在运行的顺序。

B、 Exit语句:退出钦命的言辞

      a、Exit Sub语句

         Sub e1()

             Dim x As Integer

             For x = 1 To 100

                Cells(1, 1) = x

               If x = 5 Then

                 Exit Sub

               End If

            Next x

              Range("b1") = 100

         End Sub

      b、Exit function语句

        Function ff()

           Dim x As Integer

           For x = 1 To 100

             If x = 5 Then

               Exit Function

             End If

           Next x

             ff = 100

         End Function

       c、Exit for语句

         Sub e2()

         Dim x As Integer

            For x = 1 To 100

              Cells(1, 1) = x

              If x = 5 Then

                Exit For

              End If

            Next x

              Range("b1") = 100

         End Sub

     d、Exit do 语句

        Sub e3()

         Dim x As Integer

          Do

            x = x 1

             Cells(1, 1) = x

             If x = 5 Then

               Exit Do

             End If

          Loop Until x = 100

           Range("b1") = 100

        End Sub

  C、VBA分支语句

     Option Explicit

     a、Goto语句,跳转到钦点的地点

        Sub t1()

        Dim x As Integer

        Dim sr

        100:

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

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

        End Sub

    注:“100:”正是一个Goto语句能够跳入的评释。“Len(sr)=0”表示输入框未有输入,“Len(sr) = 伍”表打消。其实质就是“false”是伍个字符。

        澳门新萄京官方网站 51

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

   Sub t2()

      Dim x As Integer

      For x = 1 To 10

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

      Next x

   Exit Sub

    100:

     Cells(x, 1) = "偶数"

    Return    '跳到gosub 100 这一句

  End Sub

c、on error resume next '蒙受错误,跳过继续实践下一句

  Sub t3()

   On Error Resume Next

   Dim x As Integer

     For x = 1 To 10

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

     Next x

   End Sub

d、on error goto  '出错开上下班时间跳到钦赐的行数

   Sub t4()

    On Error GoTo 100

    Dim x As Integer

    For x = 1 To 10

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

    Next x

      Exit Sub

    100:

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

    End Sub

 e、on error goto 0 '撤废错误跳转

   Sub t5()

      On Error Resume Next

      Dim x As Integer

      For x = 1 To 10

      If x > 5 Then On Error GoTo 0

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

      Next x

         Exit Sub

   End Sub

 

 

一柒、Excel文件操作的几个概念

    A、excel文件和工作簿概念

      excel文件正是excel专门的工作簿,excel文件展开须要excel程的支撑

      Workbooks  事业簿集合,泛指excel文件或职业簿

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

     Sub t1()

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

     End Sub

                                                           

     workbooks(贰),按张开种种,第2个张开的专门的工作簿。

      Sub t2()

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

     End Sub

                                                           

     ActiveWorkbook ,当展开多个excel职业簿时,你正在操作的10分正是ActiveWorkbook(活动职业簿)

     Thisworkbook,VBA程序所在的专门的工作簿,无论你展开多少个专门的职业簿,无论当前是哪个职业簿是移动的,thisworkbook便是指它所在的工作簿。

 

   B、职业簿窗口

       Windows("A.xls"),A专门的职业簿的窗口,使用windows能够设置专业簿窗口的情况,如是或不是隐伏等。

       Sub t3()

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

       End Sub

                                                                                          

       Sub t4()

        Windows(2).Visible = True

       End Sub

                                                                                          

1八、Excel文件的操作

   A、 推断A.Xls文件是还是不是留存

     Sub W1()

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

       MsgBox "A文件不设有"

     Else

       MsgBox "A文件存在"

     End If

    End Sub

  B、 剖断A.Xls文件是或不是展开

    Sub W2()

     Dim X As Integer

      For X = 1 To Windows.Count

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

          MsgBox "A文件展开了"

          Exit Sub

        End If

      Next

    End Sub

C、excel文件新建和保存

   Sub W3()

     Dim wb As Workbook

     Set wb = Workbooks.Add

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

     wb.SaveAs "D:/B.xls"

  End Sub

D、 excel文件张开和停业  

  Sub w4()

    Dim wb As Workbook

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

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

    wb.Close False

  End Sub

E、 excel文件保留和备份

   Sub w5()

      Dim wb As Workbook

      Set wb = ThisWorkbook

      wb.Save

      wb.SaveCopyAs "D:/ABC.xls"

    End Sub

 F、 excel文件复制和删除

    Sub W6()

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

      Kill "D:/ABC.XLS"

End Sub

1玖、工作表的定义

   A、excel专门的学业表的分类

      excel专门的学业表有两大类,一类是大家平常用的专业表(worksheet),另1类是图形、宏表等。那两类的统称是sheets

      sheets  职业表集结,泛指excel各个专门的工作表

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

                                                                              

      Sub t1()

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

      End Sub

                                                                              

     ' workbooks(二),按展开种种,第1个展开的专门的学问簿。

        Sub t2()

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

       End Sub

     'ActiveSheet ,当张开多个excel工作簿时,你正在操作的尤其便是ActiveSheet

                                                                                

  20、职业表的操作

     A、推断A工作表文件是不是存在

       Sub s1()

        Dim X As Integer

         For X = 1 To Sheets.Count

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

             MsgBox "A职业表存在"

             Exit Sub

           End If

         Next

         MsgBox "A职业表不设有"

       End Sub  

     B、 excel职业表的插入

        Sub s2()

          Dim sh As Worksheet

          Set sh = Sheets.Add

           sh.Name = "模板"

          sh.Range("a1") = 100

        End Sub

     C、 excel专门的学问表隐藏和收回隐藏

        Sub s3()

         Sheets(2).Visible = True

        End Sub

     D、 excel工作表的运动

        Sub s4()

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

         Sheets("Sheet壹").Move after:=Sheets(Sheets.Count) 'sheet1活动到持有职业表的终极面

        End Sub

    E、 excel专业表的复制

       Sub s5() '在本专业簿中

          Dim sh As Worksheet

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

           Set sh = ActiveSheet

              sh.Name = "1日"

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

        End Sub

    F、职业表的保留

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

           Dim wb As Workbook

            Sheets("模板").Copy

            Set wb = ActiveWorkbook

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

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

               wb.Close True

       End Sub

   G、 敬爱工作表

        Sub s7()

           Sheets("sheet2").Protect "123"

        End Sub

        Sub s八() '推断职业表是不是加多了保养密码

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

             MsgBox "专门的学业簿爱慕了"

           Else

             MsgBox "职业簿未有加多入保险护"

           End If

        End Sub

   H、专门的学问表删除

       Sub s9()

           Application.DisplayAlerts = False

              Sheets("模板").Delete

           Application.DisplayAlerts = True

       End Sub

  I、专门的工作表的抉择

       Sub s10()

            Sheets("sheet2").Select

        End Sub

 

本文由澳门新萄京官方网站发布于办公软件,转载请注明出处:宏技巧集绵,Excel汉字转换拼音首字母缩写的函数

关键词: