当前位置: 首页 > news >正文

Excel·VBA时间范围筛选及批量删除整行

看到一个帖子《excel吧-筛选开始时间,结束时间范围内的所有记录》,根据条件表中的开始时间和结束时间构成的时间范围,对数据表中的开始时间和结束时间范围内的数据进行筛选

目录

    • 批量删除整行,整体删除
    • 批量删除整行,分段删除
      • 不同分段行数速度对比

  • 数据举例
    条件表中,开始时间为随机生成,结束时间为开始时间依次增加180、360天。20人,每人50个场所,共1000行条件时间范围(每人的每个地点只有一行时间范围)
    数据表中,开始时间为随机生成,结束时间为开始时间依次增加1-12个月。共50万行时间范围
    在这里插入图片描述

批量删除整行,整体删除

采用《Excel·VBA指定条件删除整行整列》先Union行再删除的方法可大幅提高速度

Sub 时间范围筛选()Dim dict As Object, rng As Range, arr, i&, k$Set dict = CreateObject("scripting.dictionary"): tm = TimerApplication.ScreenUpdating = False  '关闭屏幕更新,加快程序运行arr = Worksheets("条件").[a1].CurrentRegionFor i = 2 To UBound(arr)k = arr(i, 1) & "_" & arr(i, 2)dict(k) = Array(CDbl(arr(i, 3)), CDbl(arr(i, 4)))NextWorksheets("数据").Copy after:=Sheets(Sheets.Count)With ActiveSheet.Name = "筛选结果": arr = .[a1].CurrentRegion: ReDim brr(1 To UBound(arr))For i = 2 To UBound(arr)k = arr(i, 1) & "_" & arr(i, 2)If Not dict.Exists(k) Then  '不存在的直接删除If rng Is Nothing ThenSet rng = .Rows(i)ElseSet rng = Union(rng, .Rows(i))End IfElse'符合条件时间范围If Not (dict(k)(0) <= CDbl(arr(i, 3)) And CDbl(arr(i, 4)) <= dict(k)(1)) ThenIf rng Is Nothing ThenSet rng = .Rows(i)ElseSet rng = Union(rng, .Rows(i))End IfEnd IfEnd IfNextIf Not rng Is Nothing Then rng.DeleteEnd WithApplication.ScreenUpdating = TrueDebug.Print "筛选完成,用时" & Format(Timer - tm, "0.00")  '耗时
End Sub
  • 筛选结果:运行几个小时也未能生成结果
    这显然不合理,就算是50万行的数据,使用字典也不可能耗时如此之久
    Union行的操作全部注释改为计数后可以发现,遍历50万行并判断是否符合条件时间范围,仅用时2.25秒,而之前的经验都是“先Union行再删除的方法”比“倒序循环依次删除整行的方法”速度更快,但本例中Union行的操作却很慢,那么就是行数太多导致反复Union行消耗太多时间

批量删除整行,分段删除

既然上面的代码运行缓慢可能是“反复Union行消耗太多时间”,那么就应该试试看倒序分段删除

Sub 时间范围筛选2()Dim dict As Object, rng As Range, arr, brr, i&, j&, k$, x&Set dict = CreateObject("scripting.dictionary"): tm = TimerApplication.ScreenUpdating = False  '关闭屏幕更新,加快程序运行arr = Worksheets("条件").[a1].CurrentRegionFor i = 2 To UBound(arr)k = arr(i, 1) & "_" & arr(i, 2)dict(k) = Array(CDbl(arr(i, 3)), CDbl(arr(i, 4)))NextWorksheets("数据").Copy after:=Sheets(Sheets.Count)With ActiveSheet.Name = "筛选结果": arr = .[a1].CurrentRegion: ReDim brr(1 To UBound(arr))For i = 2 To UBound(arr)k = arr(i, 1) & "_" & arr(i, 2)If Not dict.Exists(k) Then  '不存在的直接删除j = j + 1: brr(j) = iElse'符合条件时间范围If Not (dict(k)(0) <= CDbl(arr(i, 3)) And CDbl(arr(i, 4)) <= dict(k)(1)) Thenj = j + 1: brr(j) = iEnd IfEnd IfNextFor i = j To 1 Step -1  '倒序分段删除x = x + 1If rng Is Nothing ThenSet rng = .Rows(brr(i))ElseSet rng = Union(rng, .Rows(brr(i)))End IfIf x = 1000 Then rng.Delete: Set rng = Nothing: x = 0NextIf Not rng Is Nothing Then rng.DeleteEnd WithApplication.ScreenUpdating = TrueDebug.Print "筛选完成,用时" & Format(Timer - tm, "0.00")  '耗时
End Sub
  • 筛选结果:成功生成符合条件时间范围的筛选结果,共保留57668行数据
    在这里插入图片描述

不同分段行数速度对比

分段行数1005001000500010000
耗时秒数697.84643629.43687888.17

可以发现,分段在1万行以内时,运行速度差异还不明显,而总共需要删除的行数为442332行,因此以上“行数太多导致反复Union行消耗太多时间”的猜测是对的

而如果将筛选条件改为,时间范围完全不重叠

'条件开始时间 > 筛选结束时间,或条件结束时间 < 筛选开始时间
If dict(k)(0) > CDbl(arr(i, 4)) Or dict(k)(1) < CDbl(arr(i, 3)) Then

总共需要删除的行数为242931行时,可能是需要删除的行与行之间分散的更稀碎,导致比上面的删除442332行耗时差异更加明显,测试如下图

分段行数1005001000500010000
耗时秒数1233.981234.91268.611939.344079.09

需要删除的行数变少,但在同样的分段下不仅消耗时间更多,而且分段为1万行时消耗时间增长率也更高,那么可以得出结论,不仅反复Union行消耗太多时间,而且行与行之间太分散也会消耗更多时间

http://www.lryc.cn/news/287044.html

相关文章:

  • Map转成String,String 转换成Map
  • 分享一个剧本(改编自我)
  • 结合Tensuns管理prometheus的blackbox与告警设置
  • printf实现
  • Elasticsearch 中的 term、terms 和 match 查询
  • 美易官方:开盘:美股高开科技股领涨 标普指数创盘中新高
  • STM32F407移植OpenHarmony笔记2
  • 数据仓库-相关概念
  • 线程的面试八股
  • Jmeter 配置元件
  • Java- @FunctionalInterface声明一个接口为函数式接口
  • Java使用Netty实现端口转发Http代理Sock5代理服务器
  • Linux环境docker安装Neo4j,以及Neo4j新手入门教学(超详细版本)
  • C++ inline 关键字有什么做用?
  • eNSP学习——理解ARP及Proxy ARP
  • Unity中UGUI在Mask剪裁粒子特效的实现
  • 精通 VS 调试技巧,学习与工作效率翻倍!
  • yarn 安装包时报“certificate has expired”
  • Qt5项目拆解第一集解决:中文乱码| 全局字体|注册表|QSS/CSS
  • 消息队列RabbitMQ.01.安装部署与基本使用
  • 1.24号c++
  • 【GitHub项目推荐--12 年历史的 PDF 工具开源了】【转载】
  • React16源码: React中的PortalComponent创建, 调和, 更新的源码实现
  • Hive-SQL语法大全
  • 编译原理2.3习题 语法制导分析[C++]
  • JUC-CAS
  • Effective C++——关于重载赋值运算
  • vscode debug
  • 数据库选型其实技术维度不太重要
  • 【C++】入门(二)