Excel
taller_2000
不积跬步,无以至千里;不积小流,无以成江海
展开
-
Excel图表智能排序
实例需求:表格中的多个图表如下图左侧所示,对于表格进行排序时,希望第一列中的图表跟随相应数据。原创 2024-04-23 03:23:22 · 171 阅读 · 0 评论 -
Excel百变多列筛选任你用
实例需求:数据表从A4开始,列数和行数不确定,用户使用B1:D2(下文简称为用户过滤器,以区别与表格的筛选器)实现对于数据表格的定制筛选,如果需要可以仔细增减过滤器个数,过滤器先后顺序不一定和表格标题行相同。原创 2024-02-28 08:04:55 · 217 阅读 · 0 评论 -
鼠标随动指定区域高亮显示(Excel聚光灯)
实例需求:工作表中数据表实现跟随鼠标选中高亮效果,需要注意如下几个细节需求- 数据表为连续区域,但是不一定从A1单元格开始- 数据表的前两行(标题行)不使用高亮效果- 数据表中已经应用了条件格式,高亮显示取消后,不应破坏已经设置的条件格式- 如果选中整列,则不启用高亮显示原创 2024-01-09 14:04:48 · 868 阅读 · 0 评论 -
限制选中指定个数CheckBox控件(2/2)
实例需求:工作表中有8个CheckBox控件(下文中简称为控件),现在需要实现限制用户最多只能勾选4个控件。在上一篇博客中已经实现了这个需求,其基本思路是用户选中第5个控件时,事件代码将取消勾选最后一个选中的控件。其实这里案例也可以使用防患于未然的思路来解决,即用户选中4个控件之后,就禁用其他控件,用户当然也就无法再勾选更多控件。原创 2024-01-09 13:07:01 · 334 阅读 · 0 评论 -
定制样式堆积柱形图
实例需求:数据位于D2:G9,使用默认方式创建的堆积柱形图如右下图表所示。现在需要对图表格式进行样式定制。- 工作表中A列为相应的数据类别,其可能的取值与数据表中第2行标题内容相同,也就是只有4中不同的值。- 现在需要将数据类别与标题相匹配的数据点标记为蓝色,其他数据点标记为紫色。例如:对于D列Light系列,A3和A7为Light,即D3和D7为相应的匹配数据,对于的数据点标记为蓝色(参见第一个堆积矩形)。原创 2024-01-05 12:33:45 · 473 阅读 · 0 评论 -
使用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 · 692 阅读 · 0 评论 -
使用VBA快速选中指定的多个工作表
在Excel操作界面中选中工作表标签时保持或者按下,可以选中多个工作表,操作时录制宏,可以得到类似如下的代码。```Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select```这样的代码使用起来有时并不方便,例如Excel中的工作表个数较多,名称时常会发生变化等,在VBA开发过程中有更简洁的方法可以实现选中指定的多个工作表。例如现在需要选中当前工作簿中名称中包含“月”字的全部工作表。原创 2023-08-01 14:09:00 · 1780 阅读 · 0 评论 -
VBA自动动态复制数据
实例需求:根据A3单元格(黄色)的输入值动态复制第11行数据,并且在A列填充顺序编号,如下图所示。如果A3单元格输入的数值小于当前已复制的行数,则清除多余数据行。如果A3单元格为0时,则仅保留序号1的行内容,和A3单元格为1时的效果相同,如下图所示。原创 2023-07-01 12:18:34 · 956 阅读 · 0 评论 -
在Excel当前窗口显示最后一行数据
大家也许都知道Excel工作表中数据行数较多,使用组合键可以快速定位最后一行,但是如果数据不是连续的(也就是工作表中包含空行),这个方式就只能定位到当前连续数据区域的最后一行。如下实例代码可以将最后一行数据显示在当前窗口中。原创 2023-07-01 12:12:57 · 1422 阅读 · 0 评论 -
使用VBA在单元格中快速插入Unicode符号
在单元格中输入时并无法直接插入指定的Unicode编码字符,内置的插入符号功能中并不包含“Dingbats”,因为它并不是一种Windows字体。示例代码可以快速插入Dingbats符号。原创 2023-06-01 12:19:34 · 1017 阅读 · 0 评论 -
VBA为什么无法读取单元格字体名称?
这并不是VBA的bug,由于A4单元格中即有中文字符又有英文字母,虽然单元格字体设置为Arial,但是这个字体库中并不包含中文字符,因此Excel会将其中的中文字符设置为Excel的默认中文字体(我的电脑为等线字体)。代码并不复杂,使用单元格的Font.Name属性读取字体名称,从截图中可以发现C4单元格内容为空,单步调试时,可以发现irow=4时,【立即窗口】中可以查看每个字符的字体,中文字符为等线字体,并不是插图1中的单元格Arial字体。使用如下代码读取A列单元格字体名称,填写到C列中。原创 2023-05-30 07:08:17 · 192 阅读 · 0 评论 -
如何使用递归函数实现Excel列号转换列标
本博客讲述了如何使用递归方法实现Excel列号转换列标的功能。递归过程从算法上看貌似很复杂,但是本文通过举例说明了如何使用递归过程,以及如何确保递归过程能够正确的结束执行。原创 2023-05-01 07:32:24 · 1232 阅读 · 0 评论 -
单列文本数据快速导入表格
本文介绍了将单列文本数据快速导入表格的方法,该方法适用于每N行数据为一组的情况.原创 2023-05-01 07:07:47 · 1037 阅读 · 0 评论 -
VBA快速复制全部工作表
实例需求:Excel工作簿中有多个工作表(个数不确定),现需要将所有工作表复制一份,插入到原工作表之前,如下图所示。原创 2023-04-01 01:03:19 · 2430 阅读 · 0 评论 -
Excel聚光灯--双箭头指示
实例需求:在工作表中核对数据时,虽然行列标题都会高亮显示,但是似乎并不明显,因此添加两个列标到活动单元格的箭头,以便于更好的定位。原创 2023-01-02 01:43:54 · 870 阅读 · 0 评论 -
数据拆分设置超链接
实例需求:工作表B列单元格中保存了多个图片链接地址(个数不固定),现需要将地址进行拆分,依次写入L列开始的单元格中,如下图所示。原创 2022-12-02 04:15:59 · 405 阅读 · 0 评论 -
禁止Excel工作表更名(2/2)
此方案同样适用于单个工作表和不切换工作表的场景,但是凡事都有利有弊,此方案中的Commandbar对象的事件代码将会被多次激活,例如:选中单元格、切换工作表等等操作都会激活该事件代码。第5~6行代码为工作表的Open事件,在工作簿打开之后将被执行,第6行代码实现CommandBar对象的实例化。修改工作表名称后,按回车键之后,系统事件被触发,恢复工作表名称,进而实现了禁止更名。第2~4行代码为CommandBard的更新事件,用于监测工作表的名称。第1行代码声明带事件接口的对象。操作效果如下图所示。原创 2022-08-22 06:27:04 · 262 阅读 · 0 评论 -
禁止Excel工作表更名(1/2)
非常不幸的是,Excel中并没有提供工作表更名的相关事件接口,也就是说工作表更名操作没有类似于Worksheet_Change的事件,用户修改工作表名称时并没有激活某个Excel已经提供的事件。但是Excel已经提供了那么多个系统事件,肯定可以“通融”一下。Excel工作表更名非常简单,即使是办公小白也知道如何操作,有些应用场景中不希望用户修改工作表名称,使用VBA是否可以实现这个需求呢?用户切换工作表操作时,将触发此事件过程,如果第一个工作表的名称被修改了(不是默认名称Main),那么将会被自动恢复。原创 2022-08-22 06:09:45 · 332 阅读 · 0 评论 -
使用VBA设置数字格式
在Excel工作表中,使用自定义格式可以轻松实现指定宽度(数字个数)格式,例如设置单元格格式为“000”,输入数字6,则单元格显示内容为006,如下图所示。组成的字符串,只是此处作为格式字符,相当于Excel中的自定义格式。有的小伙伴可能说这个很简单,可以使用Range对象的Text数据,例如。接下来创建一个自定义函数实现格式转换,函数提供了两个参数。这个方法是可行的,其实还有更简单的方法。连接符将填充字符串和数字合并,然后使用。组成的字符串,以下简称为填充字符串。函数提取字符串从右侧开始的指定位数。...原创 2022-07-23 12:15:58 · 4921 阅读 · 0 评论 -
使用VBA快速隐藏Excel Ribbon功能区
使用Excel开发小系统时,可能会遇到希望隐藏功能区的场景。在这样的操作环境中,更容易定制用户的操作界面,避免误操作。在Excel 2003中隐藏系统菜单就可以了,但是Excel 2007开始引入的Ribbon功能区,但是VBA对象模型中并没有提供功能区的操作接口。也许VBA爱好者知道,VBA中保留了一个古董级别的方法`ExecuteExcel4Macro`,可以用来执行一个 Microsoft Excel 4.0 宏函数...原创 2022-07-23 11:34:50 · 1906 阅读 · 0 评论 -
Evaluate自定义公式替换参数
实例需求:批号列包含L、W、H,公式列为计算公式,现需要计算“结果”列。批号列为11位数字,其格式为:LLLLWWxxHHH,其xx为填充位。示例代码如下。Sub demo() Dim arr, L, W, H, Rng Set Rng = [a1].CurrentRegion arr = Rng.Value lst = UBound(arr) For i = 2 To lst L = Left(arr(i, 1), 4) W原创 2022-05-08 12:54:01 · 395 阅读 · 0 评论 -
VBA中如何判断数组为空
在复杂的代码开发过程中,程序逻辑结构可能存在多个分支,有时需要判断变量是否为空,例如对于Range变量,可以使用如下代码。原创 2022-04-04 10:03:57 · 9512 阅读 · 0 评论 -
如何获取单元格区域的行列范围
活动工作中选中的单元格区域为`B3:C7`,接下使用多种方法获取选中区域的行列范围。原创 2022-04-04 02:01:27 · 1113 阅读 · 0 评论 -
如何使用VBA实现将工作簿中全部表格转换为区域
工作簿中有很多个工作表,每个工作中又有多个表格,逐个操作实在太麻烦,而且容易出现遗漏,使用VBA快速解决这个问题。原创 2022-04-04 01:11:41 · 1768 阅读 · 0 评论 -
VBA中查找日期格式数据
使用VBA代码查找日期数据,经常出现错误,明明在工作表中存在该日期,但是代码就是查找不到,原因很简单,就是日期格式的问题。原创 2022-03-09 13:27:40 · 3890 阅读 · 2 评论 -
条件格式实现ABC分析
Excel中数据数据条可以更直观的展现数据,依次单击【条件格式】>【数据条】>【实心填充】可以获得B列的效果。原创 2022-02-02 13:59:11 · 1035 阅读 · 0 评论 -
【增强版】快速判断工作簿中是否存在指定工作表
《快速判断工作簿中是否存在指定工作表》介绍了两种方法实现判断工作表是否存在与工作簿中,最近发现有更简单的方法实现,在这里和大家分享一下。示例代码如下。Function blnSheetExist(ByVal strSht As String) As Boolean blnSheetExist = (TypeName(Application.Evaluate(strSht & "!A1")) = "Range")End FunctionSub Demo() Dim aSht原创 2022-02-02 13:54:13 · 1207 阅读 · 2 评论 -
VBA之正则表达式(31)-- 指定格式数据拆分
实例需求:按照指定规则从A列示例数据提取相应的内容,如下图所示。提取规格:1、第一段,第1个字符“-”前的字符,结果为字符串格式2、第二段,第1个“-”字符与第2个“-”字符中间的字符,结果为数字格式提取数字后,结果=1,则返回:1提取数字后,结果=2,则返回:2其余返回:03、第三段、第2个“-”字符与第3个“-”字符中间的字符,结果为字符串格式注意:有些编号没有第三个“-”,要判断。示例数据:A001-Y1-655-1-AA006-01-085A384-M1-221-1-原创 2022-01-01 07:32:04 · 909 阅读 · 0 评论 -
定位指定字符在字符串中第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 · 3867 阅读 · 0 评论 -
Excel单元格内容循环切换
实例需求:工作表中按钮实现单元格A内容循环变化,备选内容为`京东,唯品,阿里`,例如当前单元格内容为`京东`,点击按钮则单元格内容变为`唯品`,如果单元格内容为`阿里`,点击按钮则单元格内容变为`京东`。原创 2021-12-04 05:35:09 · 1339 阅读 · 2 评论 -
数字转换为固定长度字符串
在Excel中使用公式可以轻松实现将数字转换为固定长度字符串(例如以最大数字的位数为准),如下图所示。在VBA中如何实现这个功能,是否需要调用上图的Excel函数来实现?答案是:完全不需要,VBA中有多种实现方式,必有一款适合您。方法1Sub Demo1() Set Rng = Range("E1:E5") max_len = Len(Trim(Str(Application.Max(Rng)))) For Each c In Rng Debug.Prin原创 2021-12-03 13:40:12 · 1593 阅读 · 0 评论 -
如何复制透视表最后行
数据透视表是Excel中数据分析工具的精华,想必大家工作中经常用到,有时需要拷贝透视表的一部分(例如汇总行)。由于数据透视表非常灵活,所以定位最后一行也不是那么简单,首先透视表布局中可以有N多中不同字段组合方式,其次透视表位置可以从A1开始,也可以从其他单元格开始,因此不容易找到基准列。原创 2021-10-01 00:25:30 · 533 阅读 · 0 评论 -
非空数据提取之 -- 俄罗斯方块
插图来自互联网,版权归属原作者大家认识上图吗?如果是想起童年时光,只能说:客官,你暴露年龄了言归正传,需要处理的数据如下图所示,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 · 137 阅读 · 0 评论 -
自定义VBA函数(Fucntion)怎样才能只被VBA引用?
实例需求:对于VBA代码实现的自定义函数,希望只能在VBA过程中被调用,而在工作表单元格的公式使用此自定义函数。为啥存在这么奇葩的应用场景呢,其实我也不知道,只能认为存在的就是合理的。通常VBA自定义函数应该写入模块中,这样即可以被其他VBA过程调用,也可以在工作表单元格公式中使用,和内置Excel函数没有区别,不再提供效果截图。原创 2021-09-01 10:56:08 · 696 阅读 · 0 评论 -
如何在数据验证单元格区域禁用粘贴
为了确保数据准确性,在A列设置了数据验证,可以输入值的列表如下图所示。但是如果用户使用复制-粘贴,仍然可以输入其他值,如下图所示。原创 2021-09-01 10:54:45 · 3150 阅读 · 7 评论 -
快速删除不规则空列
实例需求:由于原始数据文件不规范,导入Excel的数据存在空列(例如A列),并且位置不固定,现需要使用代码删除空列。原创 2021-09-01 10:52:35 · 178 阅读 · 0 评论 -
动态设置求和公式
实例需求:现有如下审计数据,有些项目为汇总费用,现在需要填充如下公式,由于分项的个数不确定,因此无法使用固定模板,也就是说每月报表的公式都不相同,只能使用VBA动态填充。原创 2021-08-01 07:25:32 · 354 阅读 · 0 评论 -
如何在被保护的Excel工作表中设置格式
实例需求:为了避免工作表内容被意外修改,因此设置了保护工作表,但是审核者需要将错误数据标记出来(例如设置单元格填充黄色)。这个需求引发了很多讨论,有的网友建议利用工作表事件代码,在审核者选中单元格时,解除该单元格的保护,这样可以设置单元格格式了,但是单元格内容可能会被意外修改,那么将需要进一步将单元格内容保存下来,然后再进行核对 … 太复杂了,有些头疼的其实,这真的是把简单问题复杂化了,设置保护工作表时,选中【设置单元格格式】,就直接解决了,完全不需要任何代码。效果如下图所示。...原创 2021-07-31 13:52:17 · 1024 阅读 · 0 评论 -
切片器实现表格统计指标切换显示
大家对于切片器并不陌生,在透视表中通过切片器实现数据切片(筛选),其实Excel中的表格(有时被称为超级表格)也是可以使用切片器的。实例需求:对于如下统计表,由于数据列比较多,希望能够使用切片器实现控制显示统计指标。原创 2021-07-05 03:09:27 · 1308 阅读 · 5 评论 -
如何快速定位公式单元格区域
实例需求:如下数据表格中,E列和第6行为求和公式,现需要将红色框中的8个单元格区域设置背景色。原创 2021-07-02 04:03:29 · 950 阅读 · 0 评论