专栏首页完美ExcelExcel VBA解读(134): 使用Excel函数提高自定义函数的效率

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

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

excelperfect

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

线性插值是一种常用技术,用来查找缺失值或者计算两个值之间的值。例如下表:

图1

现在,想要知道Level的66.25对应的Flow1的值是多少?假设该值在66的Level对应的Flow1值6.19与66.5的Level对应的Flow1值8.64构成的直线上,可以计算66.25对应的Flow1的值如下:

8.64与6.19之差是2.45,66.25是66与66.5之间的中间值,所以将2.45的一半加上6.19得到7.415。

公式为:

=6.19+(8.64-6.19)*(66.25-66.0)/(66.5-66.0)

编写一个简单的自定义函数如下:

Function VINTERPOLATEA(Lookup_ValueAs Variant, _
   Table_Array As Range, _
   Col_Num As Long)
   Dim vArr As Variant
   Dim j As Long
   vArr = Table_Array.Value2
   For j = 1 To UBound(vArr)
        If vArr(j, 1) > Lookup_Value Then
            Exit For
        End If
   Next j
   VINTERPOLATEA = (vArr(j - 1, Col_Num) + _
   (vArr(j, Col_Num) - vArr(j - 1, Col_Num)) * _
   (Lookup_Value - vArr(j - 1, 1)) / (vArr(j, 1) - vArr(j - 1, 1)))
   End Function

代码中,Lookup_value是在单元格区域Table_Array的第1列中要找的值,Col_Num是要进行插值的数据的列号索引(本例中为2)。

这个自定义函数计算速度已经很快了。然而,还可以更快!

仔细分析这个自定义函数代码,实际的计算仅使用2行数据,但要获得这2行数据必须将所有数据导入到数组并在第1列执行线性查找。

因此,让我们试着在自定义函数代码中通过Application.WorksheetFunction.MATCH来使用Excel的MATCH函数。由于数据已排序,所以可以使用近似匹配查找MATCH。一旦通过MATCH获取行号,就可以获得我们需要的数据所在的2行。

修改后的自定义函数如下:

Function VINTERPOLATEB(Lookup_Value As Variant, _
   Table_Array As Range, _
   Col_Num As Long)
   Dim jRow As Long
   Dim rng As Range
   Dim vArr As Variant
   Set rng = Table_Array.Columns(1)
   jRow = Application.WorksheetFunction.Match(Lookup_Value, rng, 1)
   vArr = Table_Array.Resize(2).Offset(jRow - 1, 0).Value
   VINTERPOLATEB = (vArr(1, Col_Num) + _
   (vArr(2, Col_Num) - vArr(1, Col_Num)) * _
   (Lookup_Value - vArr(1, 1)) / (vArr(2, 1) - vArr(1, 1)))
End Function

代码使用MATCH函数查找到所需的行,然后使用Resize和Offset将区域调整为仅需要的2行数据。

注意,有两种方法从VBA调用像MATCH这样的Excel函数:Application.Match和Application.WorksheetFunction.Match。其差别主要在于错误处理(例如,当在完全匹配选项时找不到完全匹配项):

  • Application.Match返回包含错误的Variant型值,允许使用IsError:

If IsError(Application.Match)

  • Application.WorksheetFunction.Match触发VBA错误,需要On Error语句处理。

并且Application.Math更快些。

因此,需要添加错误处理和达到数据边界的情况处理:

  • 使用On Error来捕捉非数字数据
  • 检查要查找的值是否在表中数据范围之外
  • 检查要查找的值是否是表中最后一个值

代码如下:

Function VINTERPOLATEC(Lookup_ValueAs Variant, _
   Table_Array As Range, _
   Col_Num As Long)
   Dim jRow As Long
   Dim rng As Range
   Dim vArr As Variant
   Dim vValue As Variant
   On Error GoTo FuncFail
   Set rng = Table_Array.Columns(1)
    '检查是否是最后一行
   vValue = rng.Cells(rng.Rows.Count, 1).Value2
   If Lookup_Value = vValue Then
        VINTERPOLATEC =Table_Array.Cells(rng.Rows.Count, Col_Num).Value2
        Exit Function
   End If
    '如果Lookup_Value不在rng中则返回错误
   If Lookup_Value > vValue Or Lookup_Value < rng.Cells(1).Value2Then
        VINTERPOLATEC = CVErr(xlErrNA)
        Exit Function
   End If
    '使用MATCH查找行号
   jRow = Application.WorksheetFunction.Match(Lookup_Value, rng, 1)
    '获取2行数据
   vArr = Table_Array.Resize(2).Offset(jRow - 1, 0).Value2
   VINTERPOLATEC = (vArr(1, Col_Num) + _
   (vArr(2, Col_Num) - vArr(1, Col_Num)) * _
   (Lookup_Value - vArr(1, 1)) / (vArr(2, 1) - vArr(1, 1)))
   Exit Function
FuncFail:
   VINTERPOLATEC = CVErr(xlErrValue)
End Function

下面是代码的图片版:

小结:唯一比将所有数据一次性传递到VBA中更快的方法是,使用Excel函数且仅传递给该函数所需的最少数据。

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

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

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    fanjy

扫码关注云+社区

领取腾讯云代金券