- 博客(331)
- 资源 (2)
- 收藏
- 关注
RSS订阅原创 VBA之正则表达式（45）-- 提取SQL语句中的函数
实例需求：数据工程师或者DBA日常工作中大量使用SQL语句，有些语句（或者存储过程）行数非常多，现在需要提取其中的所有使用了函数的相关部分，对于如下语句，需要提取Mid([编号],2,4) AS [产品]和datediff("D",[购买日期],"2024-01-01") AS [日期]两个字符串。
2024-04-24 13:32:39
260
原创 VBA智能记录指定列更新时间戳
实例需求：某公司产品统计表如下所示，为了便于追溯变更，如果用户修改了“厂家指导价”或者“供货价”，那么“修改时间列”需要记录当前的时间。这个需要似乎也并不复杂，使用`Worksheet_Change`事件判断变化发生的列，然后再时间戳即可。但是每个公司都有一些奇葩的用户（领导），他们会用尽毕生精力来折腾这个表，为了满足这些奇葩用户需求，此功能需要支持如下场景：- 用户可以在任意位置插入\删除列- 用户可以在表格之上或者左侧插入任意多行
2024-04-14 13:29:47
431
原创 使用VBA巧妙获取图表数据源区域
代码很简单，即使大家不知道`SetSourceData`方法，也可以通过录制宏的方法获得代码。现在问题来了，如何读取图表的数据源区域呢？`Chart`对象并没有`SourceData`属性，也没有`GetSourceData`方法。这只能说明微软并未提供相应的接口，我们仍然可以使用变通的方法来获取图表的数据源区域。
2024-04-14 12:19:10
343
原创 使用VBA快速梳理多层级族谱（组织架构）
实例需求：族谱（或者公司组织架构等）都是典型的带有层级关系数据，例如下图中左侧表格所示。- A列为层级（准确的讲是B列成员的层级），从一开始递增- B列和C列为成员直接的父（/母）子（/女）关系- D列为辅助标记现需要整理为右侧表格的形式，按照每个家族链依次排列，如标记颜色部分所示。
2024-03-11 03:45:54
526
原创 如何锁定Excel表格(超级表)中的公式
实例需求：Excel的格式化表格（有时被称为超级表[非官方名称]，通过`插入>表格`创建，下文简称为表格）是具备很多实用的功能，例如自动扩展表格并应用原格式和公式等等。如果表格中使用了公式列，那么有些小白用户可能会不小心修改某个单元格的公式，这种问题查找起来也很麻烦。有的同学可能说保护工作表（表格中的公式列）就可以了，其实不然，保护工作表虽然避免了用户意外修改公式，但是表格的自动扩展等功能也失效了。是否可以即不牺牲表格的易用性，又可以实现保护表格中的公式呢？效果如下图所示，B列和D列为公式列，无论如何如
2024-03-11 03:41:35
266
原创 VBA自适应多种排班计划日期填充
实例需求：某公司有两种不同排班计划- MWF: 周一周三周五-周一周三周五...- TTS: 周二周四周六-周二周四周六...但是数据表中有时会缺少部分日期，为了便于汇总多个部分的数据，现在需要将日期补全，对于补充的日期标记为黄色。
2024-03-06 03:01:07
419
原创 VBA如何记录单元格中字符内容和格式
实例需求：Excel单元格中的字符可以设置不同的字体格式（大小、颜色等），有时需要将这些信息保存起来，以便于后续代码的处理（例如替换后恢复原字体颜色，或者统计某种指定格式字符个数等等），此时使用自定义数据结构就可以高效的完成这个任务。
2024-03-06 02:59:29
297
原创 Excel百变多列筛选任你用
实例需求：数据表从A4开始，列数和行数不确定，用户使用B1:D2（下文简称为用户过滤器，以区别与表格的筛选器）实现对于数据表格的定制筛选，如果需要可以仔细增减过滤器个数，过滤器先后顺序不一定和表格标题行相同。
2024-02-28 08:04:55
219
原创 Excel数据表定制分组排序
实例需求：某学校体育活动统计表如下图左侧表格所示，数据按照班级排列，现在需要根据如下规格对表格进行排序- “幼儿”班级排列在表格最后- 按照“次数”降序排列- “幼儿”班级同样按“次数”降序排列
2024-02-28 03:26:38
423
原创 Excel工作表控件实现滚动按钮效果
工作表中有多个Button控件（工作表Form控件）和一个ScrollBar控件（工作表ActiveX控件，名称为ScrollBar2），需要实现如下图所示效果。点击ScrollBar控件实现按钮的滚动效果，实际工作表中Button控件个数不确定。
2024-02-25 04:06:45
601
原创 惊天秘闻-绕过安全设置悄悄导入VBA代码
`ThisWorkbook.Modules.Add` 可以在未启用【信任对VBA工程对象模型的访问】的情况下导入模块代码，并且新导入的模块属于`ThisWorkbook.VBProject.VBComponents`集合，是不是很神奇！
2024-01-27 14:30:12
360
原创 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
375
原创 Excel表格的快速动态扩展与删除行
实例需求：工作表中的表格（ListObject）名称为`Table1`，表格列数不确定，需要实现如下功能：- 当用户完成最后一行最后一列输入之后（如果该单元格为空，则视为输入未完成），表格自动扩展一行- 扩展行中，第一列填充自动序号，其他列填充NA- 如果用户双击表格中最后一行的任意单元格，那么删除该行，并相应调整表格单元格范围
2024-01-19 14:45:10
502
原创 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
444
原创 鼠标随动指定区域高亮显示（Excel聚光灯）
实例需求：工作表中数据表实现跟随鼠标选中高亮效果，需要注意如下几个细节需求- 数据表为连续区域，但是不一定从A1单元格开始- 数据表的前两行（标题行）不使用高亮效果- 数据表中已经应用了条件格式，高亮显示取消后，不应破坏已经设置的条件格式- 如果选中整列，则不启用高亮显示
2024-01-09 14:04:48
879
原创 限制选中指定个数CheckBox控件（2/2）
实例需求：工作表中有8个CheckBox控件（下文中简称为控件），现在需要实现限制用户最多只能勾选4个控件。在上一篇博客中已经实现了这个需求，其基本思路是用户选中第5个控件时，事件代码将取消勾选最后一个选中的控件。其实这里案例也可以使用防患于未然的思路来解决，即用户选中4个控件之后，就禁用其他控件，用户当然也就无法再勾选更多控件。
2024-01-09 13:07:01
336
原创 限制选中指定个数CheckBox控件（1/2）
实例需求：工作表中有8个CheckBox控件（下文中简称为控件），现在需要实现限制用户最多只能勾选4个控件。
2024-01-05 13:01:39
419
原创 定制样式堆积柱形图
实例需求：数据位于D2:G9，使用默认方式创建的堆积柱形图如右下图表所示。现在需要对图表格式进行样式定制。- 工作表中A列为相应的数据类别，其可能的取值与数据表中第2行标题内容相同，也就是只有4中不同的值。- 现在需要将数据类别与标题相匹配的数据点标记为蓝色，其他数据点标记为紫色。例如：对于D列Light系列，A3和A7为Light，即D3和D7为相应的匹配数据，对于的数据点标记为蓝色（参见第一个堆积矩形）。
2024-01-05 12:33:45
473
原创 VBA快速填充缺失数据
实例需求：数据表中F列中存在数据缺失，如下图所示。现需要根据A列中的内容（类别，图中C1、C2、B1为不同类别），补充F列数据，已知每个类别中F列存在不少于一个非空单元格，并且其内容相同。
2023-12-15 13:51:26
123
原创 Excel公式逆天了--使用公式修改其他单元格格式
如果用户修改单元格填充色，例如选中C列和D列清除填充色，那么当工作表中任意单元格发生变化时，公式将重新计算，C列和D列的填充色将被恢复，相当于实现了挥之不去的单元格格式，效果如下图所示。同理，可以使用公式设置多个单元格的填充色，如A3单元格所示。第2行代码设置UDF为易失函数，任何单元格发生变化时，将重算此函数，注意如果大量单元格使用易失函数，可能会引起Excel卡顿。第5行代码设置UDF返回值，如果设置返回值为空，则设置了公式的单元格中为空白，相当于被隐藏了。，此句是整个解决方案的核心，替换为。
2023-12-15 08:07:39
207
原创 使用VBA快速统计词组词频(多单词组合)（2/2）
实例需求：产品清单如A列所示，现在如下统计多单词组合词组词频。 在上一篇博客中[使用VBA快速统计词组词频(多单词组合)（1/2）](https://blog.csdn.net/taller_2000/article/details/134804555)讲解了如何实现双词的词频统计。本文将讲解如何实现3词的词频统计，掌握实现方法之后，可以很容易地将代码扩展到实现更多单词词频统计，实现的效果如下图所示。
2023-12-15 02:39:47
155
原创 快速多列查找匹配关键字
实例需求：根据第一列专业名称，在“专业分类指导目录”中，针对三个学历层次（研究生、本科生、专科生）分别查找对应专业类别，填写在对应位置，即截图中的黄色区域。
2023-12-15 02:02:28
86
原创 使用VBA快速统计词组词频(多单词组合)（1/2）
实例需求：产品清单如A列所示，现在如下统计词组词频。想必各位小伙伴都指定如何使用字典对象实现去重，进而实现单个单词的词频统计。但是统计词组词频就没有那么简单了，为了便于演示，此处的词组只限于两个单词的组合。提到词组，很多时候大家先想到的是如何将获取全部的组合，例如n个无重复单词，可以产生的无重复词组个数为C(n,2) ，但是在本示例中并不需要获取这些全部组合，实现思路自然也就不同了。
2023-12-05 13:08:42
164
原创 使用VBA快速创建日历表（时间维度表）
在数据统计分析中经常用到日历表，也有很多方法创建日历表，例如如下几篇博客分享如何使用Power Query创建日历表（相关链接参见本博文的最后部分）。本次将要分享如何使用VBA创建如下格式的日历表，需要注意的是`周次`列，其中第x周的计算需要一些技巧。
2023-12-05 11:52:27
464
原创 如何快速查找最后（最右侧）隐藏列
通常思路是从工作表最后列开始，倒序检查每个列，直到找到隐藏列或者检查完毕（无隐藏列）。其实可以借助VBA的一些特殊方法更快速实现这个需求。
2023-12-05 08:49:39
69
原创 使用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
705
原创 VBA快速合并更新数据
实例需求：Sheet1中的数据每日更新，Sheet2的数据为数据总表，现需要每天将Sheet1的数据更新至Sheet2中，如果Name+Color组合在Sheet2中已经存在，那么更新该行的Sales列数据，如果不是全新的数据，那么将该数据行追加到Sheet2数据表之后。
2023-11-17 14:26:41
132
原创 VBA如何快速识别Excel单元格中的文本数字
Excel中一种非常特殊的数字，这些数字看似数字，其实是文本格式（下文简称为文本数字），在单元格的左上角会有一个绿色小三角作为标志，如B1:B3单元格。在编程时为什么需要区分普通数字和文本数字呢？先来看以下【立即窗口】种的测试代码。A1和B1单元格内容看似相同，但是测试代码的结果却完全不同。单元格A1为普通数字，加号之后的"0"被转换为数字零，然后进行加法运算。单元格B1为文本数字，加号作为字符串连接运算符，合并两个字符串。
2023-11-17 13:42:07
847
原创 VBA动态按钮控制台
Excel工作表中可用使用两种命令按钮（ActiveX和窗体控件），但是这些按钮外观的定制并不方便，其实Shape对象完全可用作为按钮，使用方法类似窗体控件按钮，并且可用轻松定制其外观。现在需要实现如下效果：5个按钮作为一组用户单击某个按钮，此按钮变色并启用3D样式
2023-11-17 10:16:33
162
原创 VBA根据Excel内容快速创建PPT
示例需求：根据Excel中选中的单元格内容（3列）如下图所示，在已打卡的PowerPoint文件中创建页面。新增PPT Slide页面使用第二个模板页面，其中包含两个文本占位符，和一个图片占位符。将Excel选中区域中前两列写入文字占位符，第3列图片粘贴至图片占位符。
2023-11-05 03:05:57
1301
原创 VBA快速动态考勤统计
实例需求：某公司的上下班打卡记录如下所示，其中`Table_In`为上班打卡记录，`Table_Out`为下班打卡记录。现在需要根据日期整理为如下格式的考勤表。需要注意如下几点：- 每天的打卡次数不确定- 最后一列`Total/Day`统计该天的出勤总时长，忽略有缺卡的时间段- 对于缺卡记录标记为`Missing`，例如10/14，员工108500，7:59:34和14:59:34两次上班打卡记录之间并没有下班打卡记录，那么7:59:34对应的下班打卡记录为缺失
2023-11-04 02:07:26
487
原创 使用VBA打印PDF文件
使用VBA打印工作表和工作簿文件都很容易实现，但是有时需要使用VBA打印已经保存在本机的其他文件，例如PDF文件格式的账单，如果这个PDF并非由Excel生成的那么就无法使用前述方法实现。调用Windows的Shell命令可以实现打印PDF文件。
2023-11-04 01:32:19
2047
原创 VBA之正则表达式（44）-- 拆分商品和规格
实例需求：商品组清单保存在A列中，现需要将其拆分为商品名称，保存在从B列开始的后续单元格中，部分商品包含规格，并且多种规格属性使用了逗号分隔，因此无法直接使用Excel分列功能完成数据拆分。
2023-11-03 01:10:26
275
原创 限制LitstBox控件显示指定行数的最新数据（3/3）
实例需求：由于数据行数累加增加，控件加载的数据越来越多，每次用户都需要使用右侧滚动条拖动才能查看最新数据。因此希望ListBox只加载最后10行数据（不含标题行），这样用户可以非常方便地选择数据，如下图所示。
2023-11-03 01:08:21
391
原创 限制LitstBox控件显示指定行数的最新数据（2/3）
实例需求：由于数据行数累加增加，控件加载的数据越来越多，每次用户都需要使用右侧滚动条拖动才能查看最新数据。因此希望ListBox只加载最后10行数据（不含标题行），这样用户可以非常方便地选择数据，如下图所示。上一篇博客已经实现了上图效果，美中不足的是需要借助辅助单元格区域，某些`Excel洁癖`的小伙伴会觉得不爽，工作表中多了一些非必要的数据，接下来就一起来看看如果不借助辅助单元格区域实现类似的效果。
2023-10-16 13:10:50
148
原创 限制LitstBox控件显示指定行数的最新数据（1/3）
工作表Sheet1中的数据表共有3列，行数不确定，现需要将数据加载到用户窗体的ListBox控件中，设置控件的相关属性属性如下所示。窗体显示效果如下图所示，由于启用了列标题，所以`RowSource`属性指定的单元格范围从第二行开始，这个参数指定范围之上的一行数据将作为ListBox控件的标题行。ListBox控件的这几个常规属性大家也经常用到，无需多讲。由于数据行数累加增加，控件加载的数据越来越多，每次用户都需要使用右侧滚动条拖动才能查看最新数据，因此希望ListBox只加载最后10行数据
2023-10-16 12:28:21
238
原创 VBA窗体跟随活动单元格【简易版】(1/2)
这个效果是不是很酷，VBA窗体（即UserForm，下文中简称为窗体）可以实现很多功能，例如：用户输入数据，提供选项等等。如本博客标题标注，这里将要实现的一个简易版，代码行数少，容易理解，便于大家移植，但是其适用场景有局限性，如果希望完美实现，那么就需要适用Windows API来精确定位。
2023-10-16 11:25:34
534
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人