Excel VBA解读（146）： 使用隐式交集处理整列

学习Excel技术，关注微信公众号：

excelperfect

Excel有一个有趣且非常有效的技巧叫做隐式交集（Implicit Intersection），允许有效地使用大的命名区域和整列引用。

什么是隐式交集（ImplicitIntersection）?

当Excel希望获得单个单元格引用但却提供给它单元格区域时，Excel会自动计算出单元格区域与当前单元格的行或列相交的区域并使用。例如下图1所示：

图1

在单元格B6中输入公式：

=A:A

并不会返回整列A，而是返回第6行与列A相交的单元格值f。

同样，如果将单元格区域A1:A15命名为myCells，那么在单元格B13中输入：

=myCells

并不会返回A1:A15中的全部值，而是返回myCells与第13行的交叉单元格A13中的值m。

但是，如果在工作表前15行之外输入

=myCells

例如，在第18行输入该公式，由于没有交叉区域，则会返回错误值#Value，如下图2所示。

图2

然而，如果输入数组公式，就是告诉Excel想要获得多个值。如果在单元格C2中输入数组公式：

=A:A

即输入后按Ctrl+Shift+Enter完成输入，会获取列A中的第1个数据，如下图3所示。

图3

如果在多个单元格中输入上述数组公式，则会获取多个值，如下图4所示，在单元格区域C5:C9输入上面的数组公式，会得到列A中的前5个数据。

图4

那么，对于函数Excel又是怎么处理的呢？例如，VLOOKUP函数通常使用单个值或引用作为要查找的值，使用单元格区域作为查找表。例如，公式：

=VLOOKUP(A4,$A:$C,3,false)

在列A至列C组成的区域中精确查找单元格A4中的内容，并返回列C中相应的值。

如果将单元格区域作为要查找的值，并且输入的不是数组公式：

=VLOOKUP($A:$A,$A:$C,3,FALSE)

那么Excel将为查找值使用隐式交集，上面公式的结果如下图5所示。

图5

隐式交集非常快！

Excel非常有效地执行隐式交集，仅将单个单元格引用传递给公式或函数，而不是整个区域。并且只将该单个单元格视为从属单元格，因此仅当该单个单元格被改变而不是当该单元格区域中的任何单元格被改变时，才重新计算公式或函数。

在VBA用户自定义函数（UDF）中运用隐式交集技术

有2种方式可以让隐式交集技术在UDF中自动工作：

1.在函数参数前面放置+号

2.使用VBA来处理隐式交集

例如，下面的简单UDF：

Function ImplicitV(theParam AsVariant) As Variant

ImplicitV = theParam

End Function

运行结果如下图6所示。

图6

如上图6所示，在函数参数前添加一个+号后，Excel仅传递给UDF单个单元格。Excel将其视为一个表达式，并在将其传递给UDF前评估该表达式，也就是说Excel会传递给该表达式的结果给UDF。

下面是一个通用的VBA函数，可以从VBA UDF内部调用，从而执行隐式交集。

Function Implicit2V(theParam AsVariant) As Variant
   Implicit2V = fImplicit(theParam, Application.Caller)
End Function
'处理输入和被调用区域的隐式交集
Function fImplicit(theInput AsVariant, CalledFrom As Range) As Variant
    '检查theInput区域
   If TypeOf theInput Is Range Then
        If TypeOf CalledFrom Is Range Then
        'theInput和CalledFrom区域都是单元格区域
            If Not CalledFrom.HasArray AndtheInput.CountLarge > 1 Then
            ' CalledFrom不是数组公式且theInput大于1个单元格
            '因此隐含处理与行相交
                Set fImplicit =Intersect(theInput, theInput.Parent.Cells(CalledFrom.Row, 1).EntireRow)
                '如果没有交叉列可尝试
                If fImplicit Is Nothing ThenSet fImplicit = Intersect(theInput, theInput.Parent.Cells(1, CalledFrom.Column,1).EntireColumn)
                '如果仍然没有,则返回#Value来模仿XL标准行为
                If fImplicit Is Nothing ThenfImplicit = CVErr(xlErrValue)
            Else
            '两个都是区域,但隐式交集不适用
                Set fImplicit = theInput
            End If
        Else
        'CalledFrom不是单元格区域但theInput是单元格区域,因此返回theInput
            Set fImplicit = theInput
        End If
   Else
    'theInput不是单元格区域因此作为Variant返回
        fImplicit = theInput
   End If
End Function

运行上述函数的效果如下图7所示。

图7

如果使用在参数前添加+号的技巧，那么UDF参数必须是与数据类型匹配的Variant、Double、String或Boolean类型，而Range和Object不起作用，因为Excel总是传递结果值而不是引用。

如果使用不带+号的fImplicit辅助函数并传递单元格区域，那么可以使用Variant或Range或Object的参数数据类型。

小结

1.在使用函数时使用隐式交集（Implicit Intersection）非常有效。

2.+号技巧非常好，但容易忘记使用！

3.像fImplicit这样的通用辅助函数比+号更快且更友好。