精华内容
下载资源
问答
  • 1. 缩减工作薄文件大小,提高运行效率一般而言只是使用 Excel 的内置工作表函数,在运算方面还是很高效的,但有时因为一个单元格牵扯的计算太多,比如调用多单元格数据,对结果文本进行部分替换,按照优先级和条件...

    223722927b61297fe545f8b652b2aaf4.png

    为什么使用数组?

    1. 缩减工作薄文件大小,提高运行效率

    一般而言只是使用 Excel 的内置工作表函数,在运算方面还是很高效的,但有时因为一个单元格牵扯的计算太多,比如调用多单元格数据,对结果文本进行部分替换,按照优先级和条件判断来确定不同的返回结果,这都会造成一个单元格的公式文本过长,当以此单元格为基础进行数千行的相对引用填充时,必然会导入工作薄快速的膨胀;

    如果因为某些复杂的处理,而使用了自定义函数,且应用的单元格也是几千行级别的,那么用户甚至会被迫选择在 "公式选项卡->计算选项",将自动计算变更为手动计算的方式来避免这种 Excel 自动进行全局计算的引发卡顿问题;

    使用数组避免这一问题的方式就是将计算在内存完成,单元格只写入一个数值或字符串;

    2. 数组的运算效率远高于读写单元格

    看个实例来体会一下单元格逐个处理,和数组内存处理后统一导入的效率差异;

    插入代码块的高亮显示实在不理想,先贴图吧;

    97e6cac6b93e72cea7146ef2bb298164.png

    FillRandom 在 A1:D10000 这四万个单元格中生成随机数;拷贝随机数为常数,避免触发自动计算带来的开销;

    任务目标:对每个随机数求平方,结果放置回工作表;

    ForEachCellImplement 使用逐个单元格取值,计算后放回的方式实现;

    ArrayImplement 使用将 A1:D10000 导入数组,在内存中完成计算,再统一放回工作表的方式实现;

    前者耗时 12718 毫秒,后者 47 毫秒;约 270 倍的效率差异;

    3. 在某些应用场景下,数组处理问题最简单

    把一行或一列单元格的数据以逗号拼接成字符串,在不使用数组的情况下一般会这样做;

    即便省略 resStr,If 语句整体压缩在一行,也需要 5 行代码,其中包含循环结构和条件判断处理字符串拼接的开头部分 ;

    如果使用数组,只需要一行就可以了;

    acd786cc16828ad4baeb9017947cd755.png

    4. 强化部分 Excel 功能

    如,SpecialCells,也就是 Excel 快捷键 F5 定位条件,Excel 中可以批量定位一种类型的单元格区域,但对定位结果进行多种条件处理,再将数据放回或者以这些数据为基础再进行其他数据的生成,Excel 菜单功能是无法完成的;

    Find 也就是 Excel 的 Ctrl + F,查找功能,同样可以利用 VBA 来完成,一旦拿到返回的一组单元格区域,处理方式就灵活多样了;

    整体来看数组可以极大的拓展 “返回值为单元格区域” 的功能的操作边界;

    5. VBA 其他容器使用效果也不理想

    比如 Collection(类似 Python 中的 List),Dictionary等,但支持的方法太有限,同时转换类型也没有便捷的方法来支持;

    这一点我个人理解是 Microsoft 还是希望用户以 Range 为核心,配合 Excel 本身的功能,以及这些功能对应的 VBA 调用,以此来完成任务目标; 如果用户的任务目标超越了这个界限用户就应该去使用 Microsoft 的其他产品如 Power BI,VSTO(Visual Studio Tools for Office)借助 C# 应该是想干什么都可以了;或者干脆极易上手 Python,借助 pandas 和 numpy 这些工具来处理 .xlsx .csv 等等;

    坑的原因

    VBA 数组的坑主要是由三个原因引起的:

    1.脚本语言的用户对数据类型的重视度不足;

    2.Range对象的存在,造成了数组一些潜规则式的转换机制;可以在下文 “感受 Range 的混乱” 部分体会一下;

    3.静态数组和动态数组的一些限制和数据导入规则;这远没有静态语言数组声明后全生命周期大小不可变,扩容需要重新创建来的清晰;

    感受 Range 的混乱

    如果这部分看蒙了就暂时过的它吧,后面看过 “Array() 和 Range 对象”,“数组初始化方式3”,“数组导入到单元格区域”,在回过头来理解一下这个部分会更有收获;

    1.Range("") 赋值给未声明的变量,TypeName 变为 Variant;TypeName() 是获取变量的数据类型;

    2.Variant() 不能使用 Range 类型的属性,如,.Address;

    3.想要使用 Range 类型的属性,先 Set varName = Range("");

    4.对于被 Set 成 Range 类型的变量:

    (1)IsArray() 仍然是 True;

    (2)不能将 Range 类型的变量赋值给已经声明的数组,报错 “类型不匹配”;

    (3)可以赋值给未声明的变量或 Variant 类型的变量(Dim x 或 Dim x as Variant),该 Range 类型的变量会自动转型为 Variant();

    最让人懵逼的地方是 Set 一个 Range() 它的 TypeName 是 Range,IsArray 是 True,但不能把它赋值给一个数组,如果把它赋值给一个 未声明的 或 Variant 变量,它又被自动转换成了 Variant(),看下面这个例子,注意 TypeName:

    1868043e32afe21645cbe23c14db44f5.png

    以下主要从以下几个方面来谈谈数组避坑:

    1. 数组声明
    2. Array() 方法 和 Range 对象
    3. 数组初始化
    4. 数组导入到单元格区域
    5. 数组作为参数和返回值

    对于脚本语言我个人更倾向,不开启强制变量声明;

    在不声明的状态下,直接对一个变量赋值,某些时候是更好的策略,比如,Filter() 的返回值,如果声明了 Variant() 去接收则报错(必须声明为 String()),For Each 的临时变量,会强制用户声明等等;如果要开启强制声明,在模块最上方加入如下语句:

    Option Explicit

    数组声明

    静态数组

    在 Option Base 的指定值不同的情况下,不设置编号的静态数组声明的大小是不同的;

    30b0eb2b41652f2b69c4f2278f49cc8c.png

    动态数组

    477041fa27dcd0f040c789eb85938055.png

    Array() 和 Range 对象

    讨论初始化之前,先来看两个给数组赋值的常用形式;

    所谓一次性装入,就是以一个数组或对象为数组赋值,赋值在一条语句中完成,Array() 和 Range 对象都属于一次装入;

    非一次性装入,就是利用循环结构逐个调用数组元素并进行赋值;

    1. Array() 函数

    Array(ParamArray ArgList() As Variant)

    注意 Array() 方法的参数和数据类型是 Variant,其函数的返回值是 Variant();

    a89fb978e94a233f64f2eb76465bf374.png
    官方文档 Variant 数据类型docs.microsoft.com

    Variant 包含除固定长度 String 数据以外的任何类型的数据;也就是说 Array() 的元素可以是任意类型,也可以是数组;

    2. Range 对象

    先说明一下本文用到的几个称呼,它们都是 Range对象 :

    (1)单元格区域,特指工作表中由单元格(Cell)所组成的 Range;

    (2)Range 类型,将 Range("A1:C3") 这种形式通过 Set 设置后得到的对象变量;

    (3)Range(""),特指赋值给变量,数据类型转型为 Variant() 的 Range;

    (4)Range 对象,指代 (1)-(3)中的情况,需要结合上下文来理解;

    15cba702e63cf1717a074b4ed8897056.png

    数组初始化

    几条规则:

    1.静态数组不能一次性装入数据;

    2.只声明是数组、大小及编号的数组,系统会分配为 Variant类型数组;

    3.只声明一个变量 (如,Dim varTemp),默认的数据类型 TypeName(varTemp) 是 Empty,varTemp 可以接收动态或静态数组,且可以是任意类型的数组;这种声明方式实际上就相当于在 非 Option Explicit 情况下,不声明直接使用变量;

    4.可以给 Variant数组 的元素赋值任意类型(逐个赋值的方式),但不能将 非Variant数组,赋值给 Variant数组(一次装入的模式),实际这一条是特指动态数组的,因为静态数组已经被不能一次装入的规则限制了;

    5.可以用静态数组为动态数组赋值,但静态数组只能是 Variant数组;

    2b210bc3698ce2f807772c757a27e8ac.png

    在 VBE 本地窗格对比一下数组元素的顺序 和 For Each 取元素的顺序;

    0d9493655ffddc69057e86ed02641da9.png

    22cb7b5ce72457c758beed80214a65f1.png

    推荐使用不声明的方式,因为部分函数,如 Filter() 的返回值是 String(),Variant 数组使用元素逐个赋值的方式可以接收 String 类型的数据,但 Variant数组 不能接收 String数组 的一次性赋值,如果一定要为数组声明类型则需要声明为 Dim arrDyn() as String,这点个人觉得挺坑的;

    7c106bf18622b53f74de39653bfa7cba.png

    数组清理

    这个用的不多顺便提一下吧;

    Erase arraylist

    arraylist 是一个或多个用逗号分隔开的需要清除的数组变量;只给出数组名,不带括号和下标;

    Erase 释放动态数组所使用的内存,下次引用该动态数组之前,必须 ReDim

    ecfeb02ecb8c3447ee57f76d5c683c11.png

    数组导入到单元格区域

    数组导入到单元格区域:

    如果 数组 的大小超过了单元格区域的大小,则相当于数组被单元格区域截取,如,例1;

    如果 单元格区域 的大小超过了数组的大小,则不足的部分以 "#N/A" 来填充,如,例2;

    f684c87328f33365af29962497a3817d.png

    例2,数组的大小是 3行4列,单元格区域的大小是 4行5列,不足的部分被 #N/A填充了;

    9d4f8fa01a212746def37db6b87c9fa7.png

    8411b21c53675414665efac7dee702d9.png

    虽然在数组初始化 方式3 中,提到 Range("") 赋值给数组,即便只有一行,它也是二维数组,但是将数组赋值给单元格区域时,可以使用一维数组;

    0b6ecd7c863504078968b1097b15d883.png

    数组的转置

    a2ef04c82b1e940a88b496cb1356a459.png

    前面提到从 Range("") 导入的数组,即是是一行,也是二维数组,即 Range("A1:J1") 是 (1 to 1, 1 to 10) 的二维数组;

    (1 to 1, 1 to 10) 形式的数组,也可以通过 Transpose 转置为一列数组,因此可以利用这个机制将一个 (1 to 1, 1 to 10) 转变为一个 (1 to 10) 的数组;

    4e67fe67e6ccf35b4fed08f40bc21699.png

    这也就文章开头部分的两次转置的原理;在 VBE 本地窗口看一下结构

    7c588834eba40ec01548fa4810251302.png

    数组作为参数和返回值

    58a1b087a0cd14d5cf58728c48bc0d74.png

    结束

    数组日常使用能看到的出现概率较高的坑应该是都在这里了,以后想到其他的再补充吧,欢迎交流!

    最后在补充一句,在文章开头的部分测试了四万个单元格的逐个读取和逐个写入,后来我又单独跑了一下逐个读取,时间是16毫秒左右,所以可以确定消耗资源的是单元格写入,在操作中尽量避免对单元格的写入吧。

    展开全文
  • 函数作用:选取当前工作表中公式出错的单元格﹐关返回出错个数...............................................101 '101.函数作用:将工作表中最后一列作为页脚打印在每一面页尾101 '102.函数作用:获取vbproject引用...
  • VBA常用技巧

    2014-12-21 16:39:28
    技巧6 替换单元格内字符串 12 技巧7 复制单元格区域 12 技巧8 仅复制数值到另一区域 12 8-1 使用选择性粘贴 12 8-2 直接赋值的方法 12 技巧9 单元格自动进入编辑状态 12 技巧10 禁用单元格拖放功能 12 技巧11 单元格...
  • 01038设置新工作簿中的工作表个数 01039设置文件的默认位置 01040设置保存自动恢复文件的时间间隔和保存位置 01041停止屏幕刷新 01042使事件无效 01043使取消键无效 01044不显示警告信息对话框 01045设置使用的...
  • 147.单元格内数据排序 148.对多栏排序 149.返回计算公式的值 [,值的计算公式] 150.把第一列=某个值对应的第二列的内容连在一起,并用、隔开 151.取得系统使用模式 152.计算机注销/关机/重启 153.更改计算机名称 154....
  • VBA编程技巧大全

    2013-08-05 09:03:19
    技巧6 替换单元格内字符串 26 技巧7 复制单元格区域 27 技巧8 仅复制数值到另一区域 30 8-1 使用选择性粘贴 30 8-2 直接赋值的方法 31 技巧9 单元格自动进入编辑状态 32 技巧10 禁用单元格拖放功能 32 技巧11 单元格...
  • 中文版Excel.2007高级VBA编程宝典 1/2

    热门讨论 2012-04-06 16:00:16
     11.1.13 确定单元格区域是否包含在另一个单元格区域  11.1.14 确定单元格的数据类型  11.1.15 读写单元格区域  11.1.16 在单元格区域中插入值的更好方法  11.1.17 传递一维数组中的内容  11.1.18 将单元格...
  • 5.2.7 计算字符串中的字符个数 5.2.8 重复字符或字符串 5.2.9 创建文本直方图 5.2.10 填充数字 5.2.11 删除额外的空格和非打印字符 5.2.12 改变文本的大小写 5.2.13 从字符串中提取字符 5.2.14 替换文本 ...
  • 5.2.7 计算字符串中的字符个数 5.2.8 重复字符或字符串 5.2.9 创建文本直方图 5.2.10 填充数字 5.2.11 删除额外的空格和非打印字符 5.2.12 改变文本的大小写 5.2.13 从字符串中提取字符 5.2.14 替换文本 ...
  • 函数功能与参数:按背景颜色计算区域中同背景之数据个数。第一参数为参照值。第二参数为计数区域。 函数名称:工作表 函数功能与参数:取工作表名。一个参数,输入工作表地址即返回工作表名,建立的工作表将带的...
  • EXCEL 2007 宝典 附光盘文件

    热门讨论 2010-04-02 14:43:05
    counting text in a range.xlsx:一演示计算区域内字符数量的各种方式的工作簿。 cumulative sum.xlsx:一演示如何计算累积和的工作簿。 frequency distribution.xlsx:一演示创建频率分布的四种方法的...
  • 计算参数区域中去除最大值与最小值之再求平均,参数个数有255个(Excel2003中是1到30个) hesum左右合并再求和。将1/2类型的数字换算成1.5类型数据后再求和;若为12则按12计算,若为1/2则按1.5计算 NOWW不改变的当前...
  • Excel百宝箱8.0

    2011-06-07 21:32:17
    计算参数区域中去除最大值与最小值之再求平均,参数个数有255个(Excel2003中是1到30个)。 函数名称:hesum 函数功能与参数:左右合并再求和。将1/2类型的数字换算成1.5类型数据后再求和;若为12则按12计算,若为1...
  • Excel百宝箱9.0无限制破解版

    热门讨论 2012-02-03 19:05:29
    【合并到选区】:将一个单元格的值合到一区域中去,可以插入到原字符之前也可以插入到原字符之后 【可还原的合并居中】:合并数据时可以保留所有数据,可以随心所欲定义分隔符。还可以随时取消合并,还原所有数据 ...
  • Excel百宝箱

    2012-10-27 17:09:21
    【合并到选区】:将一个单元格的值合到一区域中去,可以插入到原字符之前也可以插入到原字符之后 【可还原的合并居中】:合并数据时可以保留所有数据,可以随心所欲定义分隔符。还可以随时取消合并,还原所有数据 ...
  • 【综合计算显示】 综合计算并显示选中区域存储格的最大值、最小值、平均值、求和、存储格个数、筛选状态下的的加总求和,以及显示选区包含的数字、字符、汉字的个数等信息。 【GB2转BIG5】 将选中区域存储格的简体字...
  • EXCEL集成工具箱V6.0

    2010-09-11 01:44:37
    【综合计算显示】 综合计算并显示选中区域存储格的最大值、最小值、平均值、求和、存储格个数、筛选状态下的的加总求和,以及显示选区包含的数字、字符、汉字的个数等信息。 【GB2转BIG5】 将选中区域存储格的简体...
  • 【综合计算显示】 综合计算并显示选中区域存储格的最大值、最小值、平均值、求和、存储格个数、筛选状态下的的加总求和,以及显示选区包含的数字、字符、汉字的个数等信息。 【GB2转BIG5】 将选中区域存储格的简体...
  • 【综合计算显示】 综合计算并显示选中区域存储格的最大值、最小值、平均值、求和、存储格个数、筛选状态下的的加总求和,以及显示选区包含的数字、字符、汉字的个数等信息。 【GB2转BIG5】 将选中区域存储格的简体...
  • 【综合计算显示】 综合计算并显示选中区域存储格的最大值、最小值、平均值、求和、存储格个数、筛选状态下的的加总求和,以及显示选区包含的数字、字符、汉字的个数等信息。 【GB2转BIG5】 将选中区域存储格的简体...
  • │ │ 技巧199 统计选定区域数据个数.xls │ │ 技巧200 认识COUNTIF函数.xls │ │ 技巧201 单字段多条件计数.xls │ │ 技巧202 动态统计及格人数.xls │ │ 技巧203 认识SUMIF函数.xls │ │ 技巧204 单字段...
  • excel 工具箱

    2012-01-22 15:04:34
    【合并到选区】:将一个单元格的值合到一区域中去,可以插入到原字符之前也可以插入到原字符之后 【可还原的合并居中】:合并数据时可以保留所有数据,可以随心所欲定义分隔符。还可以随时取消合并,还原所有数据 ...
  • 中文版Excel.2007图表宝典 2/2

    热门讨论 2012-04-06 19:01:36
    4.5.4 将标题文本链接到一个单元格/96 4.6 处理图表的图例/98 4.6.1 添加或删除图例/98 4.6.2 移动图例或重新设置图例的大小/98 4.6.3 设置图例格式/99 4.6.4 更改图例文本/99 4.6.5 删除图例项/99 4.7 处理图表的...
  • 任务181: 演示一有多少成员存在于另一(下) 任务182: 小结 第17章: Tableau初级篇(函数与计算) 任务183: 运算符 任务184: 数字函数 任务185: 字符串函数 任务186: 日期函数 任务187: ...

空空如也

空空如也

1 2
收藏数 25
精华内容 10
关键字:

vba计算单元格内字符个数