加载中…
  
博文
标签:

excel

vba

分类: Excel相关
  此系列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
标签:

excel

vba

分类: Excel相关
  此系列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
标签:

excel

office365

函数

FILTER 函数可以基于定义的条件筛选一系列数据。

1)、我们使用公式 =FILTER(A5:D20,C5:C20=H2,'') 返回包含“苹果”(如单元格 H2 中所选定)的所有记录,如果没有“苹果”,则返回空字符串 ('')。
2)、返回多个条件的 FILTER(一对多查询)
标签:

excel

office365

函数

分类: Excel相关
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函数
标签:

excel

函数

office365

分类: Excel相关
先说一下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,'查无
标签:

excel

vba

分类: Excel相关
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
标签:

excel

vba

分类: Excel相关
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
标签:

excel

vba

分类: Excel相关
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
标签:

excel

vba

自定义排序

分类: Excel相关
今天讲一下自定义排序……
何谓自定义排序,就是按指定的顺序对数据源进行排序呗。

共分享了三种方法:
第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' &
  

新浪BLOG意见反馈留言板 欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

新浪公司 版权所有