专栏首页完美ExcelExcel VBA解读(137): 让使用用户定义函数的数组公式更快

Excel VBA解读(137): 让使用用户定义函数的数组公式更快

学习Excel技术,关注微信公众号:

excelperfect

Excel数组公式能够做很多令人惊讶的事情。除了在输入完后要按Ctrl+Shift+Enter组合键外,与普通公式一样。本文主要研究使用用户定义函数的数组公式。

有两类数组公式:

  • 单单元格数组公式输入在单个单元格中,循环遍历其参数(通常是计算的参数)并返回单个结果。
  • 多单元格数组公式输入在多个单元格中,在每个单元格中都返回结果。

使用这种功能需要付出代价:因为数组公式正处理很多工作,所以计算速度很慢(特别是单单元格数组公式)。

可以将VBA用户定义函数所花费的时间分成下列组成部分:

  • 调用用户定义函数的开销时间。
  • 用户定义函数获取将要使用的数据的时间。
  • 执行计算的时间。
  • 返回结果的开销时间。

每次的VBA读写调用都有相当大的开销,因此一次读取和写入大块数据通常要快得多。

因此,应该让VBA用户定义函数在单个块中尽可能多地读取数据并将数据尽可能大地返回到Excel。

而输入多单元数组公式做到了尽可能多地写入数据到Excel,最小化了调用开销,并且通常它可以一次读取数据并多次重复使用。

如何创建多单元格数组公式?

我们沿用《Excel VBA解读(133):编写高效的Function过程——让代码运行更快的技术》中的示例,创建自定义函数的数组版本AverageTolE函数,功能是找到除多个误差之外的数据的平均值。

假设:这些误差值全在一行中;数据和误差值都以单元格区域提供给函数;忽略错误处理;函数返回与误差行对应的结果。

自定义函数代码:

Public Function AverageTolM(theRange As Range, theTols As Range) As Variant

Dim vArr As Variant

Dim vArrTols As Variant

Dim v As Variant

Dim d As Double

Dim r As Double

Dim k As Long

Dim vOut() As Variant

Dim dTol As Double

Dim lCount As Long

On Error GoTo FuncFail

vArr = theRange.Value2

vArrTols = theTols.Value2

ReDim vOut(1 To 1, 1 To UBound(vArrTols, 2))

On Error GoTo skip

For k = 1 To UBound(vArrTols, 2)

dTol = CDbl(vArrTols(1, k))

r = 0#

lCount = 0

For Each v In vArr

d = CDbl(v)

If Abs(d) > dTol Then

r = r + d

lCount = lCount + 1

End If

skip:

Next v

vOut(1, k) = r / lCount

Next k

AverageTolM = vOut

Exit Function

FuncFail:

AverageTolM = CVErr(xlErrNA)

End Function

在这个自定义函数中:

  • theTols区域被强制转换成变体数组:vArrTols = theTols.Value2
  • 创建相同大小的输出数组:ReDim vOut(1 To 1, 1 ToUBound(vArrTols, 2))
  • 遍历误差数组并填充输出数组
  • 将输出数组赋值给函数变量:AverageTolM = vOut

注意到,声明函数返回变体(包含一个数组)而不是返回变体数组。

小结:

1.在许多实际的例子中,使用多单元格数组的用户定义函数可能是最快的计算方法。

2.将通常的用户定义函数转换成多单元格数组用户定义函数很简单。

本文分享自微信公众号 - 完美Excel(excelperfect)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-04-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Matlab加上VBA编程,表格就能画画了

    之前学习Matlab是为了参加一个数学建模的比赛,但是在慢慢的学习当中发现了matlab这款软件是真的有趣,真的非常有用,大家没事也可以去学习一下使用matla...

    FB客服
  • Excel VBA解读(143): 在自定义函数中使用整列引用时,如何更有效率?

    Excel用户经常发现在公式中使用整列的引用很方便,这样可避免每次添加新数据时都必须调整公式。因此,当编写用户自定义函数时,可能会使用:

    fanjy
  • Excel应用实践03:使用Excel进行个人计划执行记录与统计分析

    一转眼,2019年已至4月,自从年初立下flag后,便努力朝着实现它的方向奔跑。有些执行得很好,比如每天更新完美Excel微信公众号,坚持每天学习,而有些则还没...

    fanjy
  • Excel VBA解读(134): 使用Excel函数提高自定义函数的效率

    在上篇文章中,我们展示了自定义函数有效的方式是通过将单元格区域读取到Variant型数组来传递单元格区域数据。本文将介绍在自定义函数中最有效的方式是使用Exce...

    fanjy
  • Excel VBA解读(135): 影响工作表公式中运用自定义函数效率的Bug及解决方法

    在前面的两篇文章中,我们通过简单地修改VBA代码来使自定义函数运行得更快。本文将聚焦于Excel中会影响到自定义函数的Bug,并探讨如何避免它们。

    fanjy
  • Excel应用实践04:分页单独打印Excel表中的数据

    在实际工作中,我们经常会遇到想将工作表中的数据(如下图1所示的“数据”工作表)导入到固定的表格(如下图2所示)中并打印。

    fanjy
  • Excel VBA解读(138): 自定义函数时使用字节数组实现更快的字符串处理

    如果有很多行,要查找每行字符串第一个大写字母的位置,则使用数组公式会花费不少时间。

    fanjy
  • VBA实用小程序50: 在指定的单元格中插入指定的形状

    下面的自定义函数使用Shapes集合对象的AddShape方法及其参数,可以在指定的单元格中插入指定的形状。

    fanjy
  • Excel应用实践06:进行多条件统计

    这是在知乎上看到的一个问题,我试着用VBA来解决。欢迎大家就自已使用Excel中遇到的问题或想要的解决方案提问,我将尽力解答。

    fanjy

扫码关注云+社区

领取腾讯云代金券