- 博客(343)
- 资源 (2)
- 收藏
- 关注
原创 奇怪的Excel单元格字体颜色格式
使用VBA代码修改单元格全部字符字体颜色是个很简单的任务,例如设置A1单元格字体颜色为红色```vbRange("A1").Font.Color = RGB(255, 0, 0)```有时需要修改部分字符的颜色,如下图所示,将红色字符字体颜色修改为蓝色。代码将会稍许复杂,需要使用`Characters`设置逐个字符的字体颜色。
2024-07-24 21:13:23
554
原创 使用VBA按照指定格式快速整理多段落文档(邮件)
实例需求:客户沟通邮件,需要整理为指定格式,然后粘贴到CRM系统中。通常用户操作分为如下几步:1.拷贝邮件2.粘贴到记事本(或者其他文本编辑器)3.整理格式4.拷贝文本粘贴到CRM系统
2024-07-24 21:11:28
310
原创 使用VBA隐藏图表中的系列
Excel中很多图表相关的操作,并不能通过录制宏得到代码,这个场景中,如下希望开发代码实现自动化,就会无从下手,其实只要找到相关的属性和方法,代码可能并不复杂。Excel的线图如下所示,其中有三个系列(Test1,Test2和Test3)。通常认为系列(Series)对象,没有Visible属性,所有无法直接实现隐藏系列,建议通过设置线和数据点的颜色来实现隐藏,这个变通方案就比较复杂,需要考虑图表区域的背景色,如果背景色不是纯色填充,那么可能就无法实现隐藏了。
2024-06-26 10:53:13
256
原创 快速创建条形热力图
Excel中的条件格式可以有效的凸显数据特征,如下图中B列所示。现在需要使用图表展现热力条形图,如下图所示。由于颜色有多个过渡色,因此手工逐个设置数据条的颜色,基本上是不可能完成的任务,使用VBA代码可以快速创建这种图表。由于数据条是等高的,因此需要增加辅助列,如下图中C列所示。
2024-06-26 10:49:48
261
原创 VBA递归过程快速组合数据
实例需求:数据表包含的列数不固定,有的列(数量和位置不固定)包含组合数据,例如C2单元格为`D,P`,说明`Unit Config`有两种分别为D和P。现在需要将所有的组合罗列出来。
2024-06-25 00:45:39
514
原创 快速清理Word中的嵌套表格
实例需求:Word文档中表格有的单元格中包含嵌套表格(注意其中表格中有合并单元格),如下图所示。现在需要删除单元格顶部的嵌套表格(如上图中的表格1和表格3),如下图所示,如果表格较多,手工操作需要大量时间,而且容易出错。
2024-06-25 00:44:05
377
原创 使用Word表格数据快速创建图表
实例需求:Word的表格如下所示,标题行有合并单元格。现在需要根据上述表格数据,在Word中创建如下柱图。如果数据在Excel之中,那么创建这个图并不复杂,但是Word中就没用那么简单了,虽然Word中可以插入图表,但是其数据源仍然是来自于Excel。
2024-05-22 06:30:57
812
原创 使用VBA实现快速梳理产品BOM
实例需求:源数据表如左图所示,包含两列:产品编号和类别,其中类别为多级列别列表,使用大于号分隔,类别级别不固定。现在需要按照列表进行拆分,产品编号依次重复,如右图所示。
2024-05-22 06:28:50
343
原创 快速创建指定日期带合计的日历表
实例需求:根据用户在B1单元格输入的年份,自动创建日历表,具体要求如下。- 日历表包含指定年份的全部星期四- 每个月份首行,在第一列写入月份的英文简称- 每个月份结束之后,添加月份合计行- 每个季度结束之后,添加季度合计行- 所有合计单元格设置格式如下图所示(粗体、填充色、边框)
2024-05-21 09:10:03
299
原创 VBA之正则表达式(45)-- 提取SQL语句中的函数
实例需求:数据工程师或者DBA日常工作中大量使用SQL语句,有些语句(或者存储过程)行数非常多,现在需要提取其中的所有使用了函数的相关部分,对于如下语句,需要提取Mid([编号],2,4) AS [产品]和datediff("D",[购买日期],"2024-01-01") AS [日期]两个字符串。
2024-04-24 13:32:39
354
1
原创 VBA智能记录指定列更新时间戳
实例需求:某公司产品统计表如下所示,为了便于追溯变更,如果用户修改了“厂家指导价”或者“供货价”,那么“修改时间列”需要记录当前的时间。这个需要似乎也并不复杂,使用`Worksheet_Change`事件判断变化发生的列,然后再时间戳即可。但是每个公司都有一些奇葩的用户(领导),他们会用尽毕生精力来折腾这个表,为了满足这些奇葩用户需求,此功能需要支持如下场景:- 用户可以在任意位置插入\删除列- 用户可以在表格之上或者左侧插入任意多行
2024-04-14 13:29:47
528
原创 使用VBA巧妙获取图表数据源区域
代码很简单,即使大家不知道`SetSourceData`方法,也可以通过录制宏的方法获得代码。现在问题来了,如何读取图表的数据源区域呢?`Chart`对象并没有`SourceData`属性,也没有`GetSourceData`方法。这只能说明微软并未提供相应的接口,我们仍然可以使用变通的方法来获取图表的数据源区域。
2024-04-14 12:19:10
656
原创 使用VBA快速梳理多层级族谱(组织架构)
实例需求:族谱(或者公司组织架构等)都是典型的带有层级关系数据,例如下图中左侧表格所示。- A列为层级(准确的讲是B列成员的层级),从一开始递增- B列和C列为成员直接的父(/母)子(/女)关系- D列为辅助标记现需要整理为右侧表格的形式,按照每个家族链依次排列,如标记颜色部分所示。
2024-03-11 03:45:54
667
原创 如何锁定Excel表格(超级表)中的公式
实例需求:Excel的格式化表格(有时被称为超级表[非官方名称],通过`插入>表格`创建,下文简称为表格)是具备很多实用的功能,例如自动扩展表格并应用原格式和公式等等。如果表格中使用了公式列,那么有些小白用户可能会不小心修改某个单元格的公式,这种问题查找起来也很麻烦。有的同学可能说保护工作表(表格中的公式列)就可以了,其实不然,保护工作表虽然避免了用户意外修改公式,但是表格的自动扩展等功能也失效了。是否可以即不牺牲表格的易用性,又可以实现保护表格中的公式呢?效果如下图所示,B列和D列为公式列,无论如何如
2024-03-11 03:41:35
535
1
原创 VBA自适应多种排班计划日期填充
实例需求:某公司有两种不同排班计划- MWF: 周一周三周五-周一周三周五...- TTS: 周二周四周六-周二周四周六...但是数据表中有时会缺少部分日期,为了便于汇总多个部分的数据,现在需要将日期补全,对于补充的日期标记为黄色。
2024-03-06 03:01:07
569
原创 VBA如何记录单元格中字符内容和格式
实例需求:Excel单元格中的字符可以设置不同的字体格式(大小、颜色等),有时需要将这些信息保存起来,以便于后续代码的处理(例如替换后恢复原字体颜色,或者统计某种指定格式字符个数等等),此时使用自定义数据结构就可以高效的完成这个任务。
2024-03-06 02:59:29
386
原创 Excel百变多列筛选任你用
实例需求:数据表从A4开始,列数和行数不确定,用户使用B1:D2(下文简称为用户过滤器,以区别与表格的筛选器)实现对于数据表格的定制筛选,如果需要可以仔细增减过滤器个数,过滤器先后顺序不一定和表格标题行相同。
2024-02-28 08:04:55
345
原创 Excel数据表定制分组排序
实例需求:某学校体育活动统计表如下图左侧表格所示,数据按照班级排列,现在需要根据如下规格对表格进行排序- “幼儿”班级排列在表格最后- 按照“次数”降序排列- “幼儿”班级同样按“次数”降序排列
2024-02-28 03:26:38
463
原创 Excel工作表控件实现滚动按钮效果
工作表中有多个Button控件(工作表Form控件)和一个ScrollBar控件(工作表ActiveX控件,名称为ScrollBar2),需要实现如下图所示效果。点击ScrollBar控件实现按钮的滚动效果,实际工作表中Button控件个数不确定。
2024-02-25 04:06:45
759
原创 惊天秘闻-绕过安全设置悄悄导入VBA代码
`ThisWorkbook.Modules.Add` 可以在未启用【信任对VBA工程对象模型的访问】的情况下导入模块代码,并且新导入的模块属于`ThisWorkbook.VBProject.VBComponents`集合,是不是很神奇!
2024-01-27 14:30:12
395
原创 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
531
原创 Excel表格的快速动态扩展与删除行
实例需求:工作表中的表格(ListObject)名称为`Table1`,表格列数不确定,需要实现如下功能:- 当用户完成最后一行最后一列输入之后(如果该单元格为空,则视为输入未完成),表格自动扩展一行- 扩展行中,第一列填充自动序号,其他列填充NA- 如果用户双击表格中最后一行的任意单元格,那么删除该行,并相应调整表格单元格范围
2024-01-19 14:45:10
579
原创 VBA窗体跟随活动单元格【简易版】(2/2)
上一篇博客(文章连接如下)中使用工作表事件`Worksheet_SelectionChange`实现了窗体跟随活动单元格的动态效果。> [VBA窗体跟随活动单元格【简易版】(1/2)](https://blog.csdn.net/taller_2000/article/details/133854760)为了在用户滚动工作表窗体之后仍能够实现跟随效果,需要使用`Application.Windows(1).VisibleRange`对于显示位置进行调整。俗话说,条条大路通罗马,本文将接受另外一个实现
2024-01-19 14:01:51
604
原创 鼠标随动指定区域高亮显示(Excel聚光灯)
实例需求:工作表中数据表实现跟随鼠标选中高亮效果,需要注意如下几个细节需求- 数据表为连续区域,但是不一定从A1单元格开始- 数据表的前两行(标题行)不使用高亮效果- 数据表中已经应用了条件格式,高亮显示取消后,不应破坏已经设置的条件格式- 如果选中整列,则不启用高亮显示
2024-01-09 14:04:48
1247
1
原创 限制选中指定个数CheckBox控件(2/2)
实例需求:工作表中有8个CheckBox控件(下文中简称为控件),现在需要实现限制用户最多只能勾选4个控件。在上一篇博客中已经实现了这个需求,其基本思路是用户选中第5个控件时,事件代码将取消勾选最后一个选中的控件。其实这里案例也可以使用防患于未然的思路来解决,即用户选中4个控件之后,就禁用其他控件,用户当然也就无法再勾选更多控件。
2024-01-09 13:07:01
427
原创 限制选中指定个数CheckBox控件(1/2)
实例需求:工作表中有8个CheckBox控件(下文中简称为控件),现在需要实现限制用户最多只能勾选4个控件。
2024-01-05 13:01:39
507
原创 定制样式堆积柱形图
实例需求:数据位于D2:G9,使用默认方式创建的堆积柱形图如右下图表所示。现在需要对图表格式进行样式定制。- 工作表中A列为相应的数据类别,其可能的取值与数据表中第2行标题内容相同,也就是只有4中不同的值。- 现在需要将数据类别与标题相匹配的数据点标记为蓝色,其他数据点标记为紫色。例如:对于D列Light系列,A3和A7为Light,即D3和D7为相应的匹配数据,对于的数据点标记为蓝色(参见第一个堆积矩形)。
2024-01-05 12:33:45
489
原创 VBA快速填充缺失数据
实例需求:数据表中F列中存在数据缺失,如下图所示。现需要根据A列中的内容(类别,图中C1、C2、B1为不同类别),补充F列数据,已知每个类别中F列存在不少于一个非空单元格,并且其内容相同。
2023-12-15 13:51:26
209
原创 Excel公式逆天了--使用公式修改其他单元格格式
如果用户修改单元格填充色,例如选中C列和D列清除填充色,那么当工作表中任意单元格发生变化时,公式将重新计算,C列和D列的填充色将被恢复,相当于实现了挥之不去的单元格格式,效果如下图所示。同理,可以使用公式设置多个单元格的填充色,如A3单元格所示。第2行代码设置UDF为易失函数,任何单元格发生变化时,将重算此函数,注意如果大量单元格使用易失函数,可能会引起Excel卡顿。第5行代码设置UDF返回值,如果设置返回值为空,则设置了公式的单元格中为空白,相当于被隐藏了。,此句是整个解决方案的核心,替换为。
2023-12-15 08:07:39
348
原创 使用VBA快速统计词组词频(多单词组合)(2/2)
实例需求:产品清单如A列所示,现在如下统计多单词组合词组词频。 在上一篇博客中[使用VBA快速统计词组词频(多单词组合)(1/2)](https://blog.csdn.net/taller_2000/article/details/134804555)讲解了如何实现双词的词频统计。本文将讲解如何实现3词的词频统计,掌握实现方法之后,可以很容易地将代码扩展到实现更多单词词频统计,实现的效果如下图所示。
2023-12-15 02:39:47
213
原创 快速多列查找匹配关键字
实例需求:根据第一列专业名称,在“专业分类指导目录”中,针对三个学历层次(研究生、本科生、专科生)分别查找对应专业类别,填写在对应位置,即截图中的黄色区域。
2023-12-15 02:02:28
136
原创 使用VBA快速统计词组词频(多单词组合)(1/2)
实例需求:产品清单如A列所示,现在如下统计词组词频。想必各位小伙伴都指定如何使用字典对象实现去重,进而实现单个单词的词频统计。但是统计词组词频就没有那么简单了,为了便于演示,此处的词组只限于两个单词的组合。提到词组,很多时候大家先想到的是如何将获取全部的组合,例如n个无重复单词,可以产生的无重复词组个数为C(n,2) ,但是在本示例中并不需要获取这些全部组合,实现思路自然也就不同了。
2023-12-05 13:08:42
251
原创 使用VBA快速创建日历表(时间维度表)
在数据统计分析中经常用到日历表,也有很多方法创建日历表,例如如下几篇博客分享如何使用Power Query创建日历表(相关链接参见本博文的最后部分)。本次将要分享如何使用VBA创建如下格式的日历表,需要注意的是`周次`列,其中第x周的计算需要一些技巧。
2023-12-05 11:52:27
660
原创 如何快速查找最后(最右侧)隐藏列
通常思路是从工作表最后列开始,倒序检查每个列,直到找到隐藏列或者检查完毕(无隐藏列)。其实可以借助VBA的一些特殊方法更快速实现这个需求。
2023-12-05 08:49:39
119
原创 使用VBA创建Excel条件格式
**实例需求**:数据总行数不确定,现需要将Category区域(即C列到J列)中第3行开始的区域设置条件格式,规则如下:- 只对部分指定单元格应用色阶条件格式(3色)- 指定单元格应满足条件:该行`B列+0.5`等于该列第2行的值(Category),例如对于第6行,`B6+0.5`值为3,对应Category为3的列为E列,因此E6单元格应用色阶条件格式,该行其他单元格无填充色
2023-12-05 08:00:54
1186
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人