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

相关文章

使用C#代码在PDF文档中添加、删除和替换图片

《使用C#代码在PDF文档中添加、删除和替换图片》在当今数字化文档处理场景中,动态操作PDF文档中的图像已成为企业级应用开发的核心需求之一,本文将介绍如何在.NET平台使用C#代码在PDF文档中添加、... 目录引言用C#添加图片到PDF文档用C#删除PDF文档中的图片用C#替换PDF文档中的图片引言在当

macOS无效Launchpad图标轻松删除的4 种实用方法

《macOS无效Launchpad图标轻松删除的4种实用方法》mac中不在appstore上下载的应用经常在删除后它的图标还残留在launchpad中,并且长按图标也不会出现删除符号,下面解决这个问... 在 MACOS 上,Launchpad(也就是「启动台」)是一个便捷的 App 启动工具。但有时候,应

Java实现时间与字符串互相转换详解

《Java实现时间与字符串互相转换详解》这篇文章主要为大家详细介绍了Java中实现时间与字符串互相转换的相关方法,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录一、日期格式化为字符串(一)使用预定义格式(二)自定义格式二、字符串解析为日期(一)解析ISO格式字符串(二)解析自定义

java中使用POI生成Excel并导出过程

《java中使用POI生成Excel并导出过程》:本文主要介绍java中使用POI生成Excel并导出过程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录需求说明及实现方式需求完成通用代码版本1版本2结果展示type参数为atype参数为b总结注:本文章中代码均为

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T

利用Python开发Markdown表格结构转换为Excel工具

《利用Python开发Markdown表格结构转换为Excel工具》在数据管理和文档编写过程中,我们经常使用Markdown来记录表格数据,但它没有Excel使用方便,所以本文将使用Python编写一... 目录1.完整代码2. 项目概述3. 代码解析3.1 依赖库3.2 GUI 设计3.3 解析 Mark

Java时间轮调度算法的代码实现

《Java时间轮调度算法的代码实现》时间轮是一种高效的定时调度算法,主要用于管理延时任务或周期性任务,它通过一个环形数组(时间轮)和指针来实现,将大量定时任务分摊到固定的时间槽中,极大地降低了时间复杂... 目录1、简述2、时间轮的原理3. 时间轮的实现步骤3.1 定义时间槽3.2 定义时间轮3.3 使用时

C++从序列容器中删除元素的四种方法

《C++从序列容器中删除元素的四种方法》删除元素的方法在序列容器和关联容器之间是非常不同的,在序列容器中,vector和string是最常用的,但这里也会介绍deque和list以供全面了解,尽管在一... 目录一、简介二、移除给定位置的元素三、移除与某个值相等的元素3.1、序列容器vector、deque

C++原地删除有序数组重复项的N种方法

《C++原地删除有序数组重复项的N种方法》给定一个排序数组,你需要在原地删除重复出现的元素,使得每个元素只出现一次,返回移除后数组的新长度,不要使用额外的数组空间,你必须在原地修改输入数组并在使用O(... 目录一、问题二、问题分析三、算法实现四、问题变体:最多保留两次五、分析和代码实现5.1、问题分析5.

SQL Server清除日志文件ERRORLOG和删除tempdb.mdf

《SQLServer清除日志文件ERRORLOG和删除tempdb.mdf》数据库再使用一段时间后,日志文件会增大,特别是在磁盘容量不足的情况下,更是需要缩减,以下为缩减方法:如果可以停止SQLSe... 目录缩减 ERRORLOG 文件(停止服务后)停止 SQL Server 服务:找到错误日志文件:删除