数据
taller_2000
不积跬步,无以至千里;不积小流,无以成江海
展开
-
Excel数据检视——对角线连续数据连线
数据表如下图所示,现需要根据规则,在符合要求的单元格上,添加连线。连续单元格位于对角线方向单元格内容相同连续单元格数量不少于7个原创 2024-09-24 12:55:17 · 514 阅读 · 0 评论 -
VBA快速对比数据行
实例需求:对于存在多行数据(示例中为双行)的项目,对比同一个项目的每列数据,高亮显示数据不同的单元格。原创 2024-07-29 20:13:15 · 359 阅读 · 0 评论 -
VBA递归过程快速组合数据
实例需求:数据表包含的列数不固定,有的列(数量和位置不固定)包含组合数据,例如C2单元格为`D,P`,说明`Unit Config`有两种分别为D和P。现在需要将所有的组合罗列出来。原创 2024-06-25 00:45:39 · 578 阅读 · 0 评论 -
使用VBA实现快速梳理产品BOM
实例需求:源数据表如左图所示,包含两列:产品编号和类别,其中类别为多级列别列表,使用大于号分隔,类别级别不固定。现在需要按照列表进行拆分,产品编号依次重复,如右图所示。原创 2024-05-22 06:28:50 · 369 阅读 · 0 评论 -
Excel百变多列筛选任你用
实例需求:数据表从A4开始,列数和行数不确定,用户使用B1:D2(下文简称为用户过滤器,以区别与表格的筛选器)实现对于数据表格的定制筛选,如果需要可以仔细增减过滤器个数,过滤器先后顺序不一定和表格标题行相同。原创 2024-02-28 08:04:55 · 367 阅读 · 0 评论 -
Excel数据表定制分组排序
实例需求:某学校体育活动统计表如下图左侧表格所示,数据按照班级排列,现在需要根据如下规格对表格进行排序- “幼儿”班级排列在表格最后- 按照“次数”降序排列- “幼儿”班级同样按“次数”降序排列原创 2024-02-28 03:26:38 · 478 阅读 · 0 评论 -
VBA快速智能拆分日期
现在需要将A列日期,按照如下规则筛选,并提取开始日期和结束日期,填写在B列和C列时间段(包含起止日期)大于等于5天如果没有符合条件的时间段,B列和C列留空如果有多个符合条件的时间段,提取最后一个,例如A2单元格中,5.15-19和5.29-6.2都符合条件,那么从5.29-6.2中提取起止日期原创 2024-01-27 12:36:44 · 567 阅读 · 0 评论 -
VBA快速填充缺失数据
实例需求:数据表中F列中存在数据缺失,如下图所示。现需要根据A列中的内容(类别,图中C1、C2、B1为不同类别),补充F列数据,已知每个类别中F列存在不少于一个非空单元格,并且其内容相同。原创 2023-12-15 13:51:26 · 227 阅读 · 0 评论 -
使用VBA快速创建日历表(时间维度表)
在数据统计分析中经常用到日历表,也有很多方法创建日历表,例如如下几篇博客分享如何使用Power Query创建日历表(相关链接参见本博文的最后部分)。本次将要分享如何使用VBA创建如下格式的日历表,需要注意的是`周次`列,其中第x周的计算需要一些技巧。原创 2023-12-05 11:52:27 · 745 阅读 · 0 评论 -
如何快速查找最后(最右侧)隐藏列
通常思路是从工作表最后列开始,倒序检查每个列,直到找到隐藏列或者检查完毕(无隐藏列)。其实可以借助VBA的一些特殊方法更快速实现这个需求。原创 2023-12-05 08:49:39 · 133 阅读 · 0 评论 -
使用VBA实现快速模糊查询数据
实例需求:基础数据保存在`Database`工作表中,如下图所示。基础数据有37个字段,上图仅展示部分字段内容,下图中黄色字段为需要提取的数据字段。在`Search`工作表B1单元格输入查询内容。原创 2023-10-02 02:57:37 · 2748 阅读 · 0 评论 -
用户权限数据转换为用户组列表(3/3) - Excel PY公式
最近Excel圈里的大事情就是微软把PY塞进了Excel单元格,可以作为公式使用,轻松用PY做数据分析。系好安全带,老司机带你玩一把。实例需求:如下是AD用户的列表,每个用户拥有该应用程序的只读或读写权限,现在需要创建新的AD用户组,并根据用户当前的权限,将这些用户添加到相应的组中。原创 2023-09-10 03:42:34 · 340 阅读 · 0 评论 -
VBA快速合并数据
实例需求:原始数据保存在工作表的A列至C列,现需要根据材料编号合并交付日期和交付数量,并且交付日期的日期格式采用两位数字年份简写格式,合并后的数据保存在E列和F列,如下图所示。原创 2023-07-01 12:06:41 · 770 阅读 · 0 评论 -
包含合并单元格的表格快速排序
现需要将原料配方成分表按照“原料含量”从高到低排序,如下图所示。由于表格中包含合并单元格,因此Excel的排序功能无法正常。原创 2023-06-01 12:13:19 · 765 阅读 · 0 评论 -
提取页码去重再编号
实例需求:A列为档号数据,由多段数字组成,使用减号作为分隔符,最后一段数字代表页数,注意页数是不连续的,倒数第二段数字是代表档案中的第几本,每个档案都是从1开始。现在需要在B列创建“卷内顺序号”,按照顺序递增,如果A列中最后一段数字(页数)相同,那么序号也相同(如黄色区域所示)。最终效果如下图所示。原创 2023-02-01 07:24:03 · 246 阅读 · 0 评论 -
VBA之正则表达式(38)-- 提取规格数据(1/2)
实例需求:A列为某产品名称,现需要提取其中的规格数据,具体规则如下:规格数据以如下关键字开头:RO、RE、SQ、SD、QD、OB、HX、ET、QR、D2规则数据可能有多段(截图中红色部分)提取规格数据之后,乘号(即x)替换为星号R(或者r)之前增加星号剔除无意义的0和小数点,如C6和C15所示。原创 2023-01-02 01:47:33 · 1286 阅读 · 0 评论 -
Excel聚光灯--双箭头指示
实例需求:在工作表中核对数据时,虽然行列标题都会高亮显示,但是似乎并不明显,因此添加两个列标到活动单元格的箭头,以便于更好的定位。原创 2023-01-02 01:43:54 · 961 阅读 · 0 评论 -
拍案叫绝的VBA MID函数用法
看到这个标题,有的同学肯定认为,这篇博客就是一个“标题党”,MID函数可以说是VBA入门级别的函数,但凡学习过几天的小白用户都会用,那么各位客官不要着急,我说说你听听,如果大家都知道这个用法,只能说明我孤陋寡闻了,如果大家觉得这是个新奇用法,那就烦请大家点个赞吧!实例需求:将一个字符串(长度不定)进行转换,规则为倒数第3个字符和倒数第1个字符互换位置,例如:字符串为`12345`,转换结果为`12543`。原创 2023-01-02 01:42:23 · 4731 阅读 · 1 评论 -
VBA之正则表达式(37)-- 去除无意义的零
实例需求:A列为某产品规格,现需要剔除其中无意义的数字0,具体解释参见C列。原创 2022-11-02 13:22:40 · 853 阅读 · 0 评论 -
VBA实现全文件快速替换
大家对于VBA处理文本文件并不陌生,`Open`打开文件,`Line Input`逐行读取处理,然后再使用`Print`写入到目标文件,整个过程并不复杂,但是如果源文件数据行数较多,那么逐行读取将花费较多的时间。VBA内置的打开文本文件的方法,并无法实现读取全部文件内容,借助`FileSystemObject`可以实现一次性快速替换。原创 2022-11-02 13:21:19 · 2354 阅读 · 0 评论 -
快速删除包含指定数字的数据
实例需求:工作表Data中A3单元格开始保存原始数据,第一行为要查找的关键数字,如果该列数据不包含指定数字,那么删除該单元格,并且单元格上移补充空位,结果如工作表Result所示。需要注意的是:例如A4单元格内容为“14*0”,这并不包括关键数字“1”,只包含数字“14”和“0”。原创 2022-11-02 10:06:35 · 419 阅读 · 0 评论 -
使用VBA创建数字金字塔
代码实现其实很简单,重要的是找到数字排练的规律,简单总结:对于第n行,第一个数字位于(总行数-n+1)列,最后一个数字位于(2*n-1)列。如果需要创建多种不同层数、不同起始数字的金字塔,大家可用将上述代码改造成带参数的Sub过程。第7~12行代码使用双重循环创建数组金字塔。第7行代码循环变量i为行数(金字塔的层数)。第6行代码重新声明态数组,用于保存结果。第9行代码将数字保存在结果数组中。第3行代码设置金字塔共10层。第14行代码将结果写入工作表。第4行代码设置数字的初始值。第8行代码循环变量j为列数。原创 2022-09-15 11:10:37 · 1052 阅读 · 0 评论 -
VBA之正则表达式(36)-- 提取年份范围
如果yrs数组元素不为空,在第16行代码将年份写入数组中。实例需求:原始数据在A列,先需求整理为C列。第24行代码释放对象变量占用的系统资源。第10~11行代码执行正则替换。第12行代码将字符串拆分为年份。第13~19行代码循环处理年份。第23行代码将结果写入单元格中。第3行代码将数据读取到数组中。第5行代码创建正则表达式对象。第9~20行代码循环处理数据。示例代码运行结果如下图所示。第7行代码设置全局匹配。第8行代码设置匹配模式。第22行代码清空D列。原创 2022-09-15 10:40:23 · 592 阅读 · 0 评论 -
数据整理(提取年份)
数组中只有一个元素,那么说明该段数据为单个年份,第14行代码声明动态数组,第15行代码将年份写入数组中。第6~7行代码将分隔符统一替换为半角分号(也可以使用其他分隔符),这样后续拆分代码中无需再判断分隔符。数组中有多个元素,那么第18行代码判断第二年份的长度,如果为两位数字,则需要补足4位。第11行代码判断数据长度,如果为零,说明该行数据以分隔符结尾,跳过后续处理过程。第19行代码声明动态数组,第20~21行代码将年份写入数组中。第12行代码将数据以减号为分隔符拆分年份。第8行代码使用半角分号拆分数据。原创 2022-09-15 00:46:33 · 336 阅读 · 0 评论 -
使用VBA快速完成不规则数据整理
示例需求:原始数据如下图所示,药名的字数不相同,每个汉子的拼音顺序保存在C列至G列。现在需要将数据整理为如下3列的格式。原创 2022-08-25 10:06:12 · 680 阅读 · 0 评论 -
单词拆分(1/2)
实例需求:一段多行英文保存在A1单元格,现需要拆分出其中的单词,依次写入A5开始的第一列单元格中。原创 2022-06-15 00:45:54 · 207 阅读 · 1 评论 -
VBA之正则表达式(34)-- 提取照片规格
实例需求:按照指定规则从A数据提取相应的规格至B和C列,示例数据如下所示。示例代码如下。Sub Demo() Dim regExp As Object Dim aRes, arr Set regExp = CreateObject("vbscript.regExp") regExp.Global = True regExp.Pattern = "([\d\.]+)x(\d+(\.\d+)*)" arr = [a1].CurrentRegion.Value原创 2022-04-02 11:32:07 · 884 阅读 · 2 评论 -
VBA之正则表达式(33)-- 网页表格提取数据
实例需求:示例数据如下所示。```<td><a href="javascript:;" data-url="/gstdHb/queryInfo" data-param="{stdId:'46826'}" data-tempid="shb-info">电力变压器</a></td><td class="txt-nowrap">DL/T 1539-2016</td><td>1</td>```从示例数据提取名称、规格和数量>电力变压器>DL/T 1539-2016>1原创 2022-03-09 14:27:24 · 2464 阅读 · 0 评论 -
嵌套字典统计非重复日期
示例数据如A列和B列所示,现在需要统计项目编号的不重复日期次数。声明一下,这个问题其实完全不需要使用VBA代码,数据透视表和公式实现起来都更直接。这个主题来自于一个网友的提问,希望研究如何使用VBA中的字典解决此问题。原创 2022-03-09 13:51:26 · 324 阅读 · 0 评论 -
VBA中查找日期格式数据
使用VBA代码查找日期数据,经常出现错误,明明在工作表中存在该日期,但是代码就是查找不到,原因很简单,就是日期格式的问题。原创 2022-03-09 13:27:40 · 4321 阅读 · 2 评论 -
定位指定字符在字符串中第N次出现的位置
VBA中的Instr函数可以查找指定字符首次出现的位置,但是有时需要查找的字符第N此出现的位置,VBA并没有提供可以支持这个功能的函数。实例:查找字符串A123-B1234-C12345-D12345中减号第3次出现的位置。方法1:Sub Demo1() msg = "A123-B1234-C12345-D12345" cnt = 0 nth_pos = 3 For i = 1 To Len(msg) If Mid(msg, i, 1) = "-原创 2022-01-01 07:29:23 · 4346 阅读 · 0 评论 -
VBA之正则表达式(29)-- 提取规格(电视)
实例需求:待处理字符串如下。changhong长虹智能电视 58M89 ppt海尔Haier 58GT66C Max智能电视,康佳LED58G3 pro58英寸4K液晶电视机Konka 21K9S5需要提取其中的规格。示例代码如下。Sub Demo1() Dim strWord As String Dim objRegExp As Object, objMH As Object Set objRegExp = CreateObject("VBSCRIPT.REGE原创 2021-11-02 12:51:43 · 284 阅读 · 2 评论 -
VBA之正则表达式(28)-- 提取带编号的文本
实例需求:待处理字符串如下,需要提取其中被标记颜色的部分,注意数字序号之后的符号有多种。示例代码如下。Sub Demo() Dim strWord As String Dim objRegExp As Object, objMH As Object Set objRegExp = CreateObject("VBSCRIPT.REGEXP") strWord = [a1] With objRegExp .Global = True原创 2021-11-02 12:27:34 · 1143 阅读 · 1 评论 -
VBA之正则表达式(27)-- 提取包含关键字的字段
实例需求:待处理字符串如下,需要提取其中包含“电子”的部分,即:电子计算机、微型电子管、新创电子-电脑-电脑硬盘-电子计算机-晶体管-微型电子管-新创电子-示例代码如下。Sub Demo1() Dim strWord As String Dim objRegExp As Object, objMH As Object Set objRegExp = CreateObject("VBSCRIPT.REGEXP") strWord = "-电脑-电脑硬盘-电子计算机-晶原创 2021-11-02 11:44:13 · 3934 阅读 · 0 评论 -
VBA之正则表达式(26)-- 提取价格
实例需求:待处理文本如下,需要提取其中的价格(下图中红色字体文字),注意不提取其他数字。示例代码如下。Sub Demo() Dim strWord As String Dim objRegExp As Object, objMH As Object Set objRegExp = CreateObject("VBSCRIPT.REGEXP") strWord = Trim([a1]) With objRegExp .Global = True原创 2021-11-02 11:27:12 · 595 阅读 · 3 评论 -
非空数据提取之 -- 俄罗斯方块
插图来自互联网,版权归属原作者大家认识上图吗?如果是想起童年时光,只能说:客官,你暴露年龄了言归正传,需要处理的数据如下图所示,A列至O列数据出现的位置无规律,现在需要将所有数据整理如右侧,就像俄罗斯方块游戏中,方块最终排列整齐落到底部一样。示例代码如下。Sub Demo() With Sheet1 .Columns("P:AAA").Clear For i = 1 To 4 Set TargetCel = .Cells(1,..原创 2021-10-01 00:23:36 · 178 阅读 · 0 评论 -
VBA之正则表达式(24)-- 杂乱数据提取
实例需求:待处理字符串:>西装,皮带,领带各10,西装20上衣30,西装40上衣50西装皮带领带各60,西装70上衣80,西装90上衣80,70西装60上衣,西装,皮带,领带各50,40西装,上衣、领带各30,西装皮带领带各20,皮带领带各10处理规则:仅提取其中包含“xx各yy”的部分,如下图黄色部分所示。原创 2021-09-01 10:57:20 · 1171 阅读 · 0 评论 -
如何在数据验证单元格区域禁用粘贴
为了确保数据准确性,在A列设置了数据验证,可以输入值的列表如下图所示。但是如果用户使用复制-粘贴,仍然可以输入其他值,如下图所示。原创 2021-09-01 10:54:45 · 3651 阅读 · 7 评论 -
快速删除不规则空列
实例需求:由于原始数据文件不规范,导入Excel的数据存在空列(例如A列),并且位置不固定,现需要使用代码删除空列。原创 2021-09-01 10:52:35 · 211 阅读 · 0 评论 -
VBA之正则表达式(24)-- 字符串转数组
最近有个网友提问,下面的代码中并没有指定正则表达式的匹配模式(Pattern属性),为什么也可以执行,正则对象根据什么规则去进行匹配。原创 2021-05-22 13:09:18 · 1872 阅读 · 0 评论