此系列VBA代码大部来自网络,有部分是自己写的,已实践过,可以使用。我所用系统平台是win10+office365,64位,但绝大部分代码可以用在32位系统中。
数值转文本,或文本转数值,在数据处理中经常用到,此为数值转文本格式,这个比文本转数值要难一些,网上可用资料不多,此带参数过程上是我自己所写,已证实可用。数值为啥要转为文本格式,这个也很好理解,因为某些情况下只能用数值只能用文本格式出现才可以,如:18位身份证号,如不是文本格式,则身份证号无法正常显示。
以下是数值转文本格式的几种方法(代码可以直接使用)。
1、单列数值转文本(这个比较简单):
Sub 单列数值转文本()
'//批量转换,速度快
Range('C3:C' & Cells(Rows.Count,
'a').End(xlUp).row).Select
'//指定范围转文本
Selection.TextToColumns
Destination:=Range('c3'), Fie
此系列VBA代码大部来自网络,有部分是自己写的,已实践过,可以使用。我所用系统平台是win10+office365,64位,但绝大部分代码可以用在32位系统中。
数值转文本,或文本转数值,在数据处理中经常用到,此为文本转数值。为啥要文本转数值,这个很好理解,就是便于数据处理,文本数值是无法参与汇总和统计的。
以下是文本转数值的几种方法(代码可以直接使用)。
1、单列文本转数值(这个比较简单):
Sub 单列文本转数值()
'//批量转换,速度快
Range('C3:C' & Cells(Rows.Count,
'a').End(xlUp).row).Select '//指定范围转数值
Selection.TextToColumns
Destination:=Range('c3'), FieldInfo:=Array(1, 1)
End Sub
2、多列文本转数值(固定范围):
Sub 多列文本转数值固定范围()
[c3:j55] = [1*c3:j
标签:
excel
office365
vba
|
分类:
Excel相关
|
此系列VBA代码大部来自网络,有部分是自己写的,已实践过,可以使用。我所用系统平台是win10+office365,64位,但绝大部分代码可以用在32位系统中。
最大行号和最大列号应该是VBA代码中最常用的吧,所以,放在第一篇。
Sub 取最大行号()
'//都可以使用,2021.6.15,是获取最大行号的多种方法,可以根据情况选择使用
Dim i&
i = Range('a' &
Rows.Count).End(xlUp).row
'//获取A列最大行号,不用知道标题行在第几行
i = [a1].End(4).row
'//获取A列最大行号,需要知道标题行在第几行
i = UsedRange.End(4).row
&nb
FILTER 函数可以基于定义的条件筛选一系列数据。
1)、我们使用公式 =FILTER(A5:D20,C5:C20=H2,'') 返回包含“苹果”(如单元格
H2 中所选定)的所有记录,如果没有“苹果”,则返回空字符串 ('')。
2)、返回多个条件的
FILTER(一对多查询)
UNIQUE函数-office365专属函数,功能:返回列表或范围中的一系列唯一值。
1)、返回一系列值中的唯一值:
公式:=unique(b2:b10)
2)、同时使用 SORT 和 UNIQUE 以升序返回唯一的名称列表:
公式:=sort(unique(b2:b10))
3)、仅返回一次服务的客户。 如果要识别未返回的其他服务人员,以便可以联系他们,这非常有用。
公式:=unique(b2:b10,,true)
4)、使用与号 (&) 将姓氏和名字连接成全名。 请注意,公式引用 A2:A12 和 B2:B12
中名称的整个范围。 这允许 Excel 返回所有名称的数组。
公式:=unique(b2:b10&A2:A10)
提示:
如果将名称区域的格式设置为
Excel表格,则当您添加或删除名称时,公式将自动更新。
如果要对名称列表进行排序,可以添加 SORT函数
先说一下XLOOKUP的语法,它有六个参数,成功超越大哥大OFFSET,成为参数最多的函数之一。
=XLOOKUP(查找值,查找范围,结果范围,[容错值],[匹配方式],[查询模式])
参数看起来很多,不过只有前三个是必须的,后面均可省略。
下面我们举12个例子+两道练习题,由易入难、从简到繁、从入门到进阶,让大家对XLOOKUP的作用和运算方式有一个全面的了解。
1)单条件查询:
B:D列是数据明细,需要根据F列姓名查询相关电话号码。
G2输入公式:=XLOOKUP(F2,B:B,D:D)
F2是查找值,B列是查找范围,D列是结果范围,公式的意思也就是在B列查找F2,找到后返回D列对应的结果。
2)容错查询:
B:D列是数据明细,需要根据F列姓名查询相关电话号码,但和上一个案例所不同的是,如果查无结果,需要返回指定值:查无结果。
G2输入公式:=XLOOKUP(F2,B:B,D:D,'查无
Workbooks对象是Microsoft Excel 应用程序中当前打开的所有 Workbook
对象的集合。有close、add、open等方法。
Workbooks.close
' 关闭所有打开的工作簿。
Workbooks.Add
' 创建一个新工作簿。
ActiveWorkbook
'返回当前处于活动状态的工作簿。
Workbooks.open Filename:='TEST.XLSX', ReadOnly:=True
' 将文件TEST.XLSX打开为只读工作簿
Workbook对象是一个Microsoft Excel
工作簿。有Name、Path等属性。有SaveAs等方法。有Open、Activate等事件。
ThisWorkbook属性返回运行Visual
Basic代码的工作簿。当Visual Basic代码是加载宏的组成部分时,返回加载宏的工作簿,而非调用该加载宏的工作簿。
使用 Workbooks(index)(其中 index 是工作簿名称或索引号)可返回一个 Workb
AutoFilter方法的语法及说明
下面是Range对象的AutoFilter方法的语法:
Range对象.AutoFilter(Field,Criterial1,Operator,Criteria2,VisibleDropDown)
说明:
1.参数Field,指定想要基于筛选的字段的整数偏移量。从列表的左侧算起,最左侧的字段是字段一。
2.参数Criterial1,指定判断条件(为字符串)。使用“=”查找空字段,或者使用“<>”查找非空字段。如果忽略该参数,那么判断是全部。如果参数Operator是xlTop10Items,那么参数Criterial1指定项目的数量。
3.参数Operator,指定筛选的类型,为XlAutoFilterOperator常量之一:
lxlAnd:值为1。Criteria1和Criteria2的逻辑与。
lxlOr:值2。Criteria1或Criteria2的逻辑或。
lxlTop10Items:值3。显示最大值的项(在Criteria1中指定项目数)。
lxlBottom10Items:值4。显示最小值的项(在Criteria1中指定项目数)。
lxlTop10Percent:值5。显示最大值的项(在Criteria1中指定百分比)。
lxlBottom10
1、返回应用程序完整路径:
Application.Path
2、返回当前工作薄的路径:
ThisWorkbook.Path
3、返回当前默认文件路径:
Application.DefaultFilePath
Application.ActiveWorkbook.Path
只返回路径
Application.ActiveWorkbook.FullName
返回路径及工作簿文件名
Application.ActiveWorkbook.Name
返回工作簿文件名
str1 = Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, '.') -
1) '获取当前工作簿名,不含扩展名
ActiveWorkbook.Path
得到所在的目录,没有最后一个“\”
Acti
今天讲一下自定义排序……
何谓自定义排序,就是按指定的顺序对数据源进行排序呗。
共分享了三种方法:
第1种方法是系统自带的OrderCustom,优点是代码简洁,缺点是自定义序列有字符长度限制(255个)。
第2种方法是字典+数组设置序列号,再使用了辅助列进行排序。优点是不会破坏单元格的形式和结构,比如单元格中存在的公式、背景等。
第3种方法是只使用字典+数组,借助简单桶排序的技巧,直接对数据在数组中进行排序。优点是效率较高,缺点是会破坏单元格的结构,比如消除公式等。
(第1种建议掌握,第2种建议了解,第3种……能懂就懂,不懂先放着吧~)
举个例子。
如下图所示,A:C列是数据源。
现需要根据E列所指定的部门先后顺序,对数据源进行重新排序,如果部门不在指定序列内,则排放在数据源末尾。
Sub 自定义排序1()
'eh技术论坛 VBA编程学习与实践 看见星光
Dim n&, rng As Range
Set rng = Range('e2:e' &