Excel 2010 VBA 入门 043 按自定义序列进行排序

Excel 2010 VBA 入门 专栏收录该内容
142 篇文章 12 订阅

目录

示例:

实现代码

单元格对象的Sort方法

添加和删除自定义序列CustomList

使用自定义序列排序

实现代码2

Sort对象

排序字段SortField

按颜色排序

设置排序区域SetRange方法


示例:

    如图所示,该表为某医院医务人员一览表。现在希望能够将该表数据以C列(科室)按照“外妇科l手术室l内儿科l西医科l中医科l耳鼻喉科I放射科l检验科l超室|口腔科l针灸科I西药房l中药房l收费室J疾控科l合管办|后勤科”的依次顺序进行排序,如何利用VBA实
现?

序号姓名科室级别身份职称受聘专业参工时间工作年限入院时间入院年限职务
1张1张后勤科中级干部主治医师医生1970/9/1401970/7/140 
2李2李外妇科中级干部主治医师医生1991/8/1192008/10/12院长.书记
3江3江外妇科 工人 医生1993/9/1171999/1/112 
4陈4陈外妇科初级干部医士医生1999/10/1111999/10/111医务科长
5周5周外妇科 合同 医生2009/2/1322009/2/132 
6吴6吴外妇科中级干部主治医师医生1977/2/1341977/7/133副院长
7张7张外妇科中级干部主治医师医生1978/4/1331985/1/126 
8李8李外妇科初级干部护师护士1987/2/1241989/3/122 
9江9江外妇科 合同 护士2007/4/2642008/4/13 
10陈10陈外妇科 干部 护士2000/8/1101999/5/112 
11周11周外妇科初级干部护士护士2007/6/642007/6/14 
12吴12吴外妇科 干部 医生2006/3/152006/4/15 
13张13张外妇科 合同 医生2009/5/722009/5/72 
14李14李外妇科 合同 护士2009/9/912009/9/91 
15江15江手术室 干部 医生     
16陈16陈手术室 合同 医生2009/7/1112009/6/112 
17周17周手术室初级干部医师护士1981/12/1291981/12/129护士长
18吴18吴内儿科初级干部主治医师医生1988/8/1221988/7/122医务科副科长兼中医科科长
19张19张内儿科初级干部主治医师医生1997/10/1131997/10/113内科主任
20李20李内儿科 干部 医生2000/8/1102008/8/12 
21江21江内儿科初级干部护师护士1981/12/1292006/10/14 
22陈22陈内儿科初级干部护士护士2000/12/1102002/1/19 
23周23周内儿科 合同护士护士2007/11/1432007/11/13 
24吴24吴内儿科 干部 医生2009/2/1022009/2/102 
25张25张内儿科 干部 护士2009/5/1822009/5/192 
26李26李内儿科 合同 护士2009/9/1212009/9/121 
27江27江内儿科 合同 护士2009/5/1822009/5/192 
28陈28陈内儿科 合同 护士2009/5/1822009/5/192 
29周29周西医科中级干部主治医师医生1972/3/1391986/1/125工会主席
30吴30吴西医科中级干部主治医师医生1984/7/1261986/1/125 
31张31张中医科初级干部医师医生2009/2/122009/4/112 
32李32李耳鼻喉科 合同 医生     
33江33江放射科中级干部主治医师医生1979/3/1321989/12/121 
34陈34陈放射科初级干部医师医生1990/1/1211992/8/118门诊部主任
35周35周放射科初级干部医士医生1997/9/1132009/3/212 
36吴36吴检验科 干部 医生1992/6/1191992/5/119 
37张37张检验科 干部 医生  2010/4/121 
38李38李B超室 工人 医生2000/8/1101999/5/112 
39江39江B超室 干部 医生  2010/4/121 
40陈40陈口腔科初级干部医师医生1999/7/1112001/1/110 
41周41周口腔科中级干部主治医师医生1993/7/1171993/1/118 
42吴42吴针灸科初级干部医师医生1998/9/1121999/2/112 
43张43张西药房初级干部医士药剂1993/12/1171993/12/117 
44李44李西药房初级干部药剂师药剂1985/10/1251987/9/123 
45江45江西药房初级干部医师药剂1997/1/1141997/10/113 
46陈46陈西药房初级干部医士药剂1998/12/1122006/1/15 
47周47周中药房初级干部医士药剂1993/12/1171993/12/117 
48吴48吴收费室 合同 收费员  2011/4/10 
49张49张收费室 合同 收费员2006/1/152006/1/15 
50李50李收费室 合同 收费员2008/3/132008/3/13 
51江51江收费室初级干部医士收费员1988/10/1222007/11/13 
52陈52陈疾控科中级干部主治医师医生1977/4/1341977/4/134 
53周53周疾控科初级干部医士医生1989/12/1211989/1/122预防保健科
科长
54吴54吴疾控科初级干部医师医生1979/7/1312003/1/18 
55张55张疾控科初级干部医士医生1990/3/1211997/1/114 
56李56李疾控科中级干部主管护师护士1989/7/1211989/8/121护士长
57江57江合管办初级工人工人初级合管办2000/9/1102000/1/111 
58陈58陈合管办中级干部主管护师合管办1989/7/1211992/9/118护理部主任
59周59周合管办初级干部医士合管办1997/10/1132009/3/212 
60吴60吴后勤科 合同 清洁工1995/2/15161995/2/116 
61张61张后勤科初级干部医士财务1997/12/1131997/10/113 
62李62李后勤科 工人 办公室2002/12/182009/3/232办公室主任
63江63江后勤科 合同 驾驶员2009/12/712009/12/61 
64陈64陈后勤科 合同 洗浆工2009/12/2412009/12/241 
65周65周后勤科 合同 清洁工2008/8/122008/7/12 
66吴66吴后勤科 合同 清洁工2009/8/1612009/8/161 
67张67张后勤科中级干部会计师财务1996/10/1141999/9/111 

实现代码

Option Explicit
'辅助列法
Sub 自定义排序()
    Dim RowN As Long
    Range("O1").Value = "外妇科"
    Range("O2").Value = "手术室"
    Range("O3").Value = "内儿科"
    Range("O4").Value = "西医科"
    Range("O5").Value = "中医科"
    Range("O6").Value = "耳鼻喉科"
    Range("O7").Value = "放射科"
    Range("O8").Value = "检验科"
    Range("O9").Value = "B超室"
    Range("O10").Value = "口腔科"
    Range("O11").Value = "针灸科"
    Range("O12").Value = "西药房"
    Range("O13").Value = "中药科"
    Range("O14").Value = "收费室"
    Range("O15").Value = "疾控科"
    Range("O16").Value = "合管办"
    Range("O17").Value = "后勤科"
    '将辅助列数据添加入自定义列表
    Application.AddCustomList Range("O1:O17")
    '执行排序
    Range("A1").CurrentRegion.Sort key1:=Range("C1"), order1:=xlAscending, Header:=xlYes, _
                orderCustom:=Application.CustomListCount + 1
    '删除辅助列
    Range("O1:O17").Clear
    '删除自定义列表
    Application.DeleteCustomList Application.CustomListCount
End Sub

单元格对象的Sort方法

Sort方法可以用来对单元格进行排序,其语法为:

Rng.Sort([Keyl],[Order1],[Key2],[Type],[Order2],[Key3],[Order3],[ Header],
[OrderCuStom],[ MatchCase],[Orientation],[SortMethod],[ DataOptionl],[ DataOption2],[DataOption3])

其中,Rng是需要进行排序的单元格区域。

  •   参数Key1、Key2和Key3为排序的关键字,可以为数据的标题或者该数据标题下所表示的任意数据的单元格对象。参数Order1、Order2、Order3分别为关键字排序顺序,可以为xIDescending(降序)或者xIAscending(升序)。
  •   参数DataOptionl、DataOption2、DataOption3分别表示对应关键字的数据类型,可以为xISortTextAsNumbers(将文本作为数字型数据排序)或者xISortNormal(分别对数字和文本数据进行排序)。
  •   参数Header表示数据区域是否含有标题,可以为xIGuess(由Excel自行判断)、xIYes(含有标题)或者xINo(无标题)。
  •   参数OrderCustom为自定义序列的序列号,如本例中,当需要使用按照自定义列表进行排序时,使用该选项。
  •   参数MatchCase为是否区分大小写,可以为True(区分大小写)或者False(不区分大小写)。
  •   参数Orientation表示排序的方向,可以为xISortRows或xITopToButtom(默认值,按行排序,即数据标题为首行),以及xISortColumns或xILeftToRight(按列排序,即数据标题为首列)。
  •   参数SortMethod表示排序的类型,可以为xIPinYin(按拼音顺序)或者xIStroke(按笔画顺序)。
  •   参数Type在对数据透视表排序时有效,其指定要排序的元素。可以为xISortLabels(按标签对数据透视表排序)或者xISortValues(按值对数据透视表排序)。

  本例需要对部门列进行排序,仅有一个关键字,因而参数Keyl设为Range(“C1”),Header设为xIYes,表示含有标题行,OrderCustom设为自定义列表的序号,表示按照自定义序列排序。

添加和删除自定义序列CustomList

    自定义序列CutomList是Excel程序对象级(Application)的一个特殊对象,VBA中提供了几个方法和属性来供开发者访问自定义序列,如表所示。

               表  Validation Add方法的Operator参数

    方法或属性

    说  明

AddCustomList(ListArray, [Byrow])

  添加自定义序列

  参数ListArray可以为数组或者单元格区域对象

  参数Byrow表示当ListArray为单元格区域时,按行取值(设置为True)或者按列取值(设置为False)

DeleteCustomList(ListNum)

  删除自定义序列。参数ListNum表示自定义序列的序号

GetCustornListContents(ListNum)

  教取自定义序列内容。参数ListNum表示自定义序列的序号

GetCustomListNum(ListArray)

  获取自定义序列的序号。参数ListArray表示自定义序列的数组

CustomListCount

  返回自定义序列数量

    本例中,首先利用程序创建一个序列的临时区域列表,然后通过AddCustomList方法将该区域添加至自定义序列中,待排序完成后,再次利用DeleteCustomList方法删除。
    当有新的自定义序列添加时,新添加的自定义序列序号始终等于添加完该序列后的自定义序列数量,因而可以用CustomListCount来获取该自定义序列的序号。

使用自定义序列排序

    在使用自定义序列排序时,参数OrderCustom使用的是自定义序列所在的序号。值得注意的是,此处的序号除了考虑自定序列之外,还包括“新序列”选项,因而此处使用的序号必须为自定义序列的序号+1。因而本例中该参数设置为Application. CustomListCount+1才能正确排序。

实现代码2

Sub 自定义排序2()
    With ActiveSheet.Sort
        '清除原有排序
        .SortFields.Clear
        '添加排序字段
        .SortFields.Add _
            Key:=Range("C2", Cells(Rows.Count, "C").End(xlUp)), _
            CustomOrder:="外妇科,手术室,内儿科,西医科,中医科,耳鼻喉科,检验科,B超室,口腔科," & _
                         "针炙科,西药房,中药房,收费室,疾控科,合管办,后勤科"
        '设置排序区域
        .SetRange Range("A1").CurrentRegion
        '含标题行
        .Header = xlYes
        '区分大小写
        .MatchCase = False
        '排序方式为拼音
        .SortMethod = xlPinYin
        '应用排序
        .Apply
    End With
End Sub

Sort对象

    自Excel 2007版本开始,Sort对象被引入并用于排序操作。Sort对象是工作表(Worksheet)的一个属性。Sort对象是在Sort方法的基础上发展而来的。Sort对象的属性几乎和使用Sort方法时所使用的参数相同,不同的是与关键字相关的参数在Sort对象中以排序字段(SortFields)表示。因而在使用Sort对象进行排序时,首先需要创建排序字段,待设置完各属性后,使用Appy方法应用排序。

排序字段SortField

    创建排序字段可以使用SortFields. Add方法,当需要删除排序字段时使用SortFields. Clear方法。SortFields. Add方法可以返回一个SortField对象,其语法如下:

SortFields.Add (Key,[SortOn],[Order],[CustomOrder],[DataOption])
  •     参数Key表示排序字段(关键字)所代表的单元格对象(Range)。
  •     参数SortOn表示排序的方法,可以为xISortOnValues(按值排序)、xISortOnCeIIColor(按单元格填充颜色排序)、xISortOnFontColor(按字体颜色排序)、xISortOnlcon(按图标排序)。
  •    参数Order表示排序顺序,可以为xIDescending(降序)或者xIAscending(升序)。
  •    参数CustomOrder表示是否按自定义列表进行排序,当需要此方法时,只需将该参数设为自定义列表所组成的文本(字符串)即可。该文本按照先后顺序排列列表项,各项目之间以逗号隔开。
  •    参数DataOption表示数据类型,可以为xISortTextAsNumbers(将文本作为数字型数据排序)或者xISortNormal(分别对数字和文本数据进行排序)。

按颜色排序

    使用Sort对象进行排序时,允许开发者按照颜色排序。在添加排序字段(SortFields)时,需要将添加的排序字段(SordField)中SortOnValue的Colort值设置为所需要排序的颜色值,该值可以利用RGB函数得到。如需要将填充色为红色的单元格排在前面,则可以使用以下语句:

Sort.SortFields.Add(Rng,xlSortOnCellColor,xlAscending,,xlSortNormal).SortOnValue .Color=RGB(255,0,0)

当需要对多个颜色进行排序时,序字段,依次设置排序颜色为红色、比如红色在最前,蓝色随后,绿色最后,需要添加3个排蓝色和绿色。

设置排序区域SetRange方法

使用Sort对象进行排序,需要使用SetRange方法设置需要排序的数据区域。其语法为:

Sort.SetRange(Rng)

其中,Rng为单元格区域对象。
 

 

  • 0
    点赞
  • 0
    评论
  • 7
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

目录: 1.返回 Column 英文字 2.查询某一值第num次出现的值 3.返回当个人工资薪金所得为2000元(起征点为850元)时的应纳个人所得税税额 4.从形如"123545ABCDE"的字符串中取出数字 5.从形如"ABCD12455EDF"的字符串中取出数字 6.按SplitType取得RangeName串值中的起始位置 7.将金额数字转成中文大写 8.计算某种税金 9.人民币大、小写转换 10.查汉字区位码 11.把公元年转为农历 12.返回指定列数的列标 13.用指定字符替换某字符 14.从右边开始查找指定字符在字符串中的位置 15.从右边开始查找指定字符在字符串中的位置 16.计算工龄 17.计算日期差,除去星期六、星期日 18.将英文字反转的自定函数 19.计算个人所得税 20.一个能计算是否有重复单元的函数 21.数字金额转中文大写 22.将数字转成英文 23.人民币大小写转换 24.获取区域颜色值 25.获取活动工作表名 26.获取最后一行行数 27.判断是否连接在线 28.币种转换 29.检验工作表是否有可打印内容 30.查找一字符串(withinstr)在另一字符串中(findstr1)中某一次(startnum)出现时的位置,返回零表示没找到。 31.增加文件路径最后的“\”符号 32.计算所得税 33.从工作表第一行的标题文字以数字形式返回所在列号 34.在多个工作表中查找一个范围内符合某个指定条件的项目对应指定范围加总求和 35.返回 Column 英文字 36.查找指定列名的列数 37.文字格式的时间(分:秒)转化为数字格式(秒) 38.将"hh:mm:ss"格式的时分秒数转换成秒数 39.金额中文大写转数字 40.把角度转为度秒分、弧度等显示 41.身份证号码侦测 42.显示公式 43.方便财务人员理帐查找 44.数值转换为字符地址 45.字符地址转换为数值 46.等待时间(以秒计算) 47.得到字符串实际的长度(以单字节记) 48.18位身份证最后一位有效性验证 49.计算符合maturity condition的拆解金额 50.对多个用同一分隔符分隔的待查找元素,逐一在表区域首列内搜索,将返回选定单元格的值相加,相当于多个vlookup函数相加,对于查找不到的元素在批注中添加,以提醒用户。 51.根据个人所得税(工资)反算工资数 52.判断表是否存在 53.角度转弧 54.比较相同的字符串 55.对选定的数组进行排序 56.取得指定月份天数 57.排序工作表活页薄 58.统计数组中非重复数据个数 59.摘取子字符串 60.计算20000余个汉字的笔画 61.删除当前工作表中的全部超连接 62.取得相近数据 63.提取定串中汉字 64.搜索重复数据(选定范围) 65.字符型转数字型 66.小写人民币转大写人民币 67.取得指定月份人星期天个数 68.侦测档案是否包含宏 69.获取循环参照单元格 70.创建桌面快捷方式 71.自动建立多级目录 72.统计经筛选后符合条件的记录条数 73.复制单元格列高与栏宽 74.取消隐藏工作表(包括vba Project工程保护的) 75.删除单元格自定义名称 76.从文件路径中取得文件名 77.取得一个文件的扩展名 78.取得一个文件的路径 79.十进制转二进制 80.检查一个数组是否为空 81.字母栏名转数字栏名 82.数字栏名转文字栏名 83.判断一件活页夹中是否还有子目录 84.判断一个文件是否在使用中 85.列出档案详细摘要信息 86.获取菜单ID编号及名称列表 87.状态列动态显示文字 88.取得一个文件的路径2 89.取得一个文件的路径3 90.取得Activecell的栏名 91.取得单元格中指定字符前的字符 92.前单元格指定字符前的字符颜色改成红色 93.根据数字返回对应的字母列号 94.取工作表名字 95.取消所有隐藏的宏表 96.导出VBA Project代码 97.导入VBA Project代码 98.取得汉字拼音的第一个字母 99.获取两栏中相同的数据 100.选取当前工作表中公式出错的单元格﹐关返回出错个数 101.将工作表中最后一列作为页脚打印在每一面页尾 102.获取vbproject引用项目 103.移除Excel工作表中的外部数据连接 104.将选择定单元格作成镜像图片 105.反选择单元格中的数 106.在Excel中加入一个量度尺(以厘米为单位) 107.在Excel中加入一个量度尺(以寸为单位) 108.取得一个短文件名的长文件名 109.取得临时文件名 110.等用Shell调用的程序执行完成后再执行其它程序 111.将Mouse显示成动画 112.限制Mouse移动范围 113.取得当前激活窗品句柄及标题 114.取得屏幕分辨率 115.自动建立多级目录 116.将文件长度置零 117.读取WIN9X / Me共享文件夹密码 118.取得预设的打印机及设置预设的打印机 119.获得当前操作系统的打印机个数及检测打印是否存在 120.枚举打印机名称清单 121.读取网络服务器当前时间 122.下载文件到指定目录 123.自动映射网络驱动器 124.自动断开网络驱动器 125.连接选定单元格中的内容 126.获取一个单元格中有指定字体颜色部份数据 127.对指定文件加XLS加密 128.选择指定范围内使用了填充颜色的单元格 129.在特定的区域内查找文本,返回值是包含查找文本的单元格 130.返回特定区域中最大值的地址 131.删除表格中使用范围内的所有空白单元格 132.返回数组中有多少个指定的字符串 133.返回当前工作表中引用了指定的单元的地址 134.获取Excel中字型列表 135.获取一个字符串中有多少个数字字符 136.在Excel中对多列进行填充 137.对选定的范围进行数据填充(忽略单元格格式) 138.VBA Project加密及解密 139.列出收藏夹中的网址 140.计算两个日期之间相隔的年份,比如年龄,工龄等.可计算从1000年01月01日起的日期 141.从字符串提取纯数字 142.将一个数组按升序排列 143.将一个数组按降序排列 144.删除空白列 145.判断工作表是否为空白 146.将数据按类分到不同工作薄 147.单元格内数据排序 148.对多栏排序 149.返回计算公式的值 [,值的计算公式] 150.把第一列=某个值对应的第二列的内容连在一起,并用、隔开 151.取得系统使用模式 152.计算机注销/关机/重启 153.更改计算机名称 154.从n位开始取出字符串中的汉字、英文字母、数字 155.在指定列中寻找含有指定字符串的单元格,并将符合条件的单元格标为红色,并将对应的下一列单元格赋值为1 156.清除字符串中的空格 157.查找合并单元格位置 158.阴阳历转换和阴阳历生日 159.利用数组和Substitute来替换某字符 160.一键创建斜线表头 161.自动获取指定月的工作日
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页

打赏作者

ngbshzhn

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值