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.rhkb.cn/news/247195.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

多维时序 | Matlab实现DBO-LSTM蜣螂算法优化长短期记忆神经网络多变量时间序列预测

多维时序 | Matlab实现DBO-LSTM蜣螂算法优化长短期记忆神经网络多变量时间序列预测 目录 多维时序 | Matlab实现DBO-LSTM蜣螂算法优化长短期记忆神经网络多变量时间序列预测效果一览基本介绍程序设计参考资料 效果一览 基本介绍 1.Matlab实现DBO-LSTM多变量时间序列预测&#x…

关机恶搞小程序

1. system("shutdown")的介绍 当system函数的参数是"shutdown"时&#xff0c;它将会执行系统的关机命令。 具体来说&#xff0c;system("shutdown")的功能是向操作系统发送一个关机信号&#xff0c;请求关闭计算机。这将触发操作系统执行一系列…

vscode的扩展商店使用时报错 XHR failed

文章目录 前言解决扩展商店的使用github的相关问题 前言 这几天使用vscode扩展商店安装插件时发现一搜索就报错&#xff0c;网上搜了一些解决方法&#xff0c;看起来不大靠谱。而且用了好几年了vscode都没出现过这个问题&#xff0c;感觉是网络问题。 解决扩展商店的使用 首先…

用 CanvasKit 实现超级丝滑的原神地图(已开源)!!!

首先给大家送上预览地址&#xff1a; 官网地址&#xff1a;https://webstatic.mihoyo.com/ys/app/interactive-map/index.html canvaskit地址&#xff1a;http://106.55.55.247/ky-genshin-map/ 为什么 canvaskit 有如此高的性能&#xff1f; 第一个问题&#xff0c;官方网页…

JVM性能分析工具——Arthas及火焰图的使用

Arthas的使用 Arthas常用命令Arthas的安装Linux压测工具Apache Bench安装火焰图的使用火焰图如何分析火焰图的互动 Arthas常用命令 help &#xff1a;查看所有命令dashboard &#xff1a;仪表板&#xff0c;查看线程的CPU信息等heapdump &#xff1a;不同类对象占用内存比重&a…

python算法与数据结构---滑动窗口双指针

学习目标 了解滑动窗口的基本原理&#xff1b;学会用使用python语言解答滑动窗口经典题目&#xff1b;了解双指针的基本原理&#xff1b;学会使用python语言解答双指针经典题目&#xff1b; 滑动窗口 209. 长度最小的子数组 https://leetcode.cn/problems/minimum-size-sub…

初识人工智能,一文读懂机器学习之逻辑回归知识文集(6)

&#x1f3c6;作者简介&#xff0c;普修罗双战士&#xff0c;一直追求不断学习和成长&#xff0c;在技术的道路上持续探索和实践。 &#x1f3c6;多年互联网行业从业经验&#xff0c;历任核心研发工程师&#xff0c;项目技术负责人。 &#x1f389;欢迎 &#x1f44d;点赞✍评论…

深入理解TCP网络协议(1)

目录 1.TCP协议的段格式 2.TCP原理 2.1确认应答 2.2超时重传 3.三次握手(重点) 4.四次挥手 1.TCP协议的段格式 我们先来观察一下TCP协议的段格式图解: 源/目的端口号:标识数据从哪个进程来,到哪个进程去 32位序号/32位确认号:TCP会话的每一端都包含一个32位&#xff08…

linux 的nobody是什么用户? 对安全有没有影响?

目录 一、nobody是不是可疑用户&#xff1f; 二、Linux系统中的nobody用户&#xff1f; 三、有nobody用户存在&#xff0c;安全吗&#xff1f; 一、nobody是不是可疑用户&#xff1f; 在Linux系统中&#xff0c;nobody是一个特殊的用户账户&#xff0c;通常用于运行系统服务…

UE5在VisualStudio升级后产生C++无法编译的问题

往期的虚幻引擎项目在VS更新后&#xff0c;编译时会报错&#xff0c;这一般出现在VS升级之后&#xff0c;UE对于VC的编译器定位没有更新导致&#xff1b; 有出现如下问题&#xff1a; 问题1&#xff1a; Running I:/EPCI/Epic Games/UE_5.3/Engine/Build/BatchFiles/Build.ba…

C++设计模式介绍:优雅编程的艺术

物以类聚 人以群分 文章目录 简介为什么有设计模式&#xff1f; 设计模式七大原则单一职责原则&#xff08;Single Responsibility Principle - SRP&#xff09;开放封闭原则&#xff08;Open/Closed Principle - OCP&#xff09;里氏替换原则&#xff08;Liskov Substitution …

市场复盘总结 20240124

仅用于记录当天的市场情况&#xff0c;用于统计交易策略的适用情况&#xff0c;以便程序回测 短线核心&#xff1a;不参与任何级别的调整&#xff0c;采用龙空龙模式 昨日主题投资 连板进级率 19/43 28.2% 二进三&#xff1a; 进级率低 47% 最常用的二种方法&#xff1a; 方…

【数据结构四】栈与Stack详解

目录 栈与Stack 1.实现一个自己的栈 2.Stack的基本使用 3.栈的一些oj题训练 4.栈&#xff0c;虚拟机栈&#xff0c;栈帧的区别 栈与Stack 栈 &#xff1a;一种特殊的线性表&#xff0c;其 只允许在固定的一端进行插入和删除元素操作 。进行数据插入和删除操作的一端称为栈顶…

【音视频原理】音频编解码原理 ③ ( 音频 比特率 / 码率 | 音频 帧 / 帧长 | 音频 帧 采样排列方式 - 交错模式 和 非交错模式 )

文章目录 一、音频 比特率 / 码率1、音频 比特率2、音频 比特率 案例3、音频 码率4、音频 码率相关因素5、常见的 音频 码率6、视频码率 - 仅做参考 二、音频 帧 / 帧长1、音频帧2、音频 帧长度 三、音频 帧 采样排列方式 - 交错模式 和 非交错模式1、交错模式2、非交错模式 一…

微信小程序-04

rpx&#xff08;responsive pixel&#xff09;是微信小程序独有的&#xff0c;用来解决屏适配的尺寸单位。 import 后跟需要导入的外联样式表的相对路径&#xff0c;用 ; 表示语句结束。 定义在 app.wxss 中的样式为全局样式&#xff0c;作用于每一个页面。 在页面的 .wxss 文…

薅运营商羊毛?封杀!

最近边小缘在蓝点网上看到一则消息 “浙江联通也开始严格排查PCDN和PT等大流量行为 被检测到可能会封停宽带”。 此前中国联通已经在四川和上海等多个省市严查家庭宽带 (部分企业宽带也被查) 使用 PCDN 或 PT&#xff0c;当用户的宽带账户存在大量上传数据的情况&#xff0c;中…

【USTC】verilog 习题练习 46-50

46 上升沿检测 题目描述 在实际应用中&#xff0c;我们经常需要对某个信号的边沿进行检测&#xff0c;并以此作为后续动作的触发信号&#xff08;例如电脑键盘的某个按键被按下或者被松开&#xff0c;在电路中则对应的是电平的变化&#xff09;。 设计一个电路&#xff0c;包…

幻兽帕鲁服务器价格,这个价格不够电费的

幻兽帕鲁服务器价格多少钱&#xff1f;4核16G服务器Palworld官方推荐配置&#xff0c;阿里云4核16G服务器32元1个月、96元3个月&#xff0c;腾讯云换手帕服务器服务器4核16G14M带宽66元一个月、277元3个月&#xff0c;8核32G22M配置115元1个月、345元3个月&#xff0c;16核64G3…

QT之 QDebug 调试(一)

在QT中&#xff0c;进行调试&#xff0c;则需要在头文件地方加上 #include <QDebug> 加上之后&#xff0c;在编译之后则其输出的信息则在应用程序输出那里显示信息。 其QDebug 信息调试则如&#xff1a; qDebug() << " 需要插入的信息 "…

2000-2022年上市公司全要素生产率测算固定效应FE法(含原始数据+测算代码do文档+计算结果)

2000-2022年上市公司全要素生产率测算固定效应FE法&#xff08;含原始数据测算代码do文档计算结果&#xff09; 1、时间&#xff1a;2000-2022年 2、范围&#xff1a;上市公司 3、指标&#xff1a;证券代码、证券简称、统计截止日期、固定资产净额、year、股票简称、报表类型…