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

2024-01-25 05:20

本文主要是介绍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行消耗太多时间,而且行与行之间太分散也会消耗更多时间

这篇关于Excel·VBA时间范围筛选及批量删除整行的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/642213

相关文章

python处理带有时区的日期和时间数据

《python处理带有时区的日期和时间数据》这篇文章主要为大家详细介绍了如何在Python中使用pytz库处理时区信息,包括获取当前UTC时间,转换为特定时区等,有需要的小伙伴可以参考一下... 目录时区基本信息python datetime使用timezonepandas处理时区数据知识延展时区基本信息

利用python实现对excel文件进行加密

《利用python实现对excel文件进行加密》由于文件内容的私密性,需要对Excel文件进行加密,保护文件以免给第三方看到,本文将以Python语言为例,和大家讲讲如何对Excel文件进行加密,感兴... 目录前言方法一:使用pywin32库(仅限Windows)方法二:使用msoffcrypto-too

SpringBoot整合mybatisPlus实现批量插入并获取ID详解

《SpringBoot整合mybatisPlus实现批量插入并获取ID详解》这篇文章主要为大家详细介绍了SpringBoot如何整合mybatisPlus实现批量插入并获取ID,文中的示例代码讲解详细... 目录【1】saveBATch(一万条数据总耗时:2478ms)【2】集合方式foreach(一万条数

如何使用 Python 读取 Excel 数据

《如何使用Python读取Excel数据》:本文主要介绍使用Python读取Excel数据的详细教程,通过pandas和openpyxl,你可以轻松读取Excel文件,并进行各种数据处理操... 目录使用 python 读取 Excel 数据的详细教程1. 安装必要的依赖2. 读取 Excel 文件3. 读

Python的time模块一些常用功能(各种与时间相关的函数)

《Python的time模块一些常用功能(各种与时间相关的函数)》Python的time模块提供了各种与时间相关的函数,包括获取当前时间、处理时间间隔、执行时间测量等,:本文主要介绍Python的... 目录1. 获取当前时间2. 时间格式化3. 延时执行4. 时间戳运算5. 计算代码执行时间6. 转换为指

Java中字符串转时间与时间转字符串的操作详解

《Java中字符串转时间与时间转字符串的操作详解》Java的java.time包提供了强大的日期和时间处理功能,通过DateTimeFormatter可以轻松地在日期时间对象和字符串之间进行转换,下面... 目录一、字符串转时间(一)使用预定义格式(二)自定义格式二、时间转字符串(一)使用预定义格式(二)自

redis过期key的删除策略介绍

《redis过期key的删除策略介绍》:本文主要介绍redis过期key的删除策略,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录第一种策略:被动删除第二种策略:定期删除第三种策略:强制删除关于big key的清理UNLINK命令FLUSHALL/FLUSHDB命

C#实现将Excel表格转换为图片(JPG/ PNG)

《C#实现将Excel表格转换为图片(JPG/PNG)》Excel表格可能会因为不同设备或字体缺失等问题,导致格式错乱或数据显示异常,转换为图片后,能确保数据的排版等保持一致,下面我们看看如何使用C... 目录通过C# 转换Excel工作表到图片通过C# 转换指定单元格区域到图片知识扩展C# 将 Excel

Java中Date、LocalDate、LocalDateTime、LocalTime、时间戳之间的相互转换代码

《Java中Date、LocalDate、LocalDateTime、LocalTime、时间戳之间的相互转换代码》:本文主要介绍Java中日期时间转换的多种方法,包括将Date转换为LocalD... 目录一、Date转LocalDateTime二、Date转LocalDate三、LocalDateTim

使用Python将JSON,XML和YAML数据写入Excel文件

《使用Python将JSON,XML和YAML数据写入Excel文件》JSON、XML和YAML作为主流结构化数据格式,因其层次化表达能力和跨平台兼容性,已成为系统间数据交换的通用载体,本文将介绍如何... 目录如何使用python写入数据到Excel工作表用Python导入jsON数据到Excel工作表用