Excel/VBA - 使用动态范围的索引匹配函数 [英] Excel / VBA - Index Match function using Dynamic Ranges

问题描述

如何在 VBA 中有效地使用 Index/Match 公式?

How to effectively use an Index/Match formula in VBA?

背景:我有一个工作表，它严重依赖于公式的使用，该公式根据将特定名称与其名称范围以及特定日期与其日期范围进行匹配来检索输出.

Background: I have a worksheet that relies heavily on the use of a formula that retrieves an output based on matching a specific name to its name range as well as a specific date to its date range.

=INDEX(OutputRange,MATCH(1,(Name=NameRange)*(Date=DateRange),FALSE),1)

此外，还有一个硬编码的 VBA 子程序可以产生相同的输出

Additionally, there is a hard-coded VBA sub that produces the same output

Sub ExampleHardCode() 
Dim Result As Variant
  Result = Evaluate("INDEX($C$4:$C$13,MATCH(1,($G$6=$A$4:$A$13)*($G8=$B$4:$B$13),FALSE),1)")
  ActiveCell.Value = Result
End Sub

问题:我想生成一个函数，该函数返回与上述选项相同的输出，但允许用户 (i) 通过引用相应的单元格来选择名称和日期值，以及 (ii) 选择每个范围(名称范围、日期范围和输出范围).本质上是在excel中使用=examplefunction(name value, name range, date value, date range, output range).

Question: I’d like to produce a function that returns the same output as the above options but allows the user to (i) select the Name and Date values by referencing respective cells and (ii) select each range (name range, date range and output range). Essentially using =examplefunction(name value, name range, date value, date range, output range) in excel.

我尝试了许多不同的解决方案，但都没有成功.下面是我迄今为止尝试过的示例，我认为匹配部分存在问题，因为即使我尝试设置范围(使用硬编码范围)，它也会返回错误.

I’ve tried a number of different solutions but with no success. Below is an example of what I've tried so far, I think there is an issue with the match portion as even when I try to set the ranges (with hardcoded ranges) it returns an error.

Function TestIndexMatch1(NameVal As Variant, DateVal As Date)

Dim NameRng As Range
Dim DateRng As Range
Dim OutputRng As Range
Dim Var1 As Variant  'should this be a range or integer?
Dim Result As Variant 

Set NameRng = Range("$A$4:$A$13")
Set DateRng = Range("$B$4:$B$13")
Set OutputRng = Range("$C$4:$D$13")

With Application.WorksheetFunction
    Var1 = .Match(1, (NameVal = NameRng) * (DateVal = DateRng), False)
    Result = .Index(OutputRng, Var1, 1)
End With
End Function

如果对我有帮助，我有一个示例工作簿可以分享.我不确定这是否非常可行，但如果是这样，它确实可以帮助许多对 excel 不够熟悉的用户正确使用索引/匹配 excel 公式.不幸的是，我的 excel 技能远远超过了我的 VBA 技能.

I have an example workbook if it helps I can share. I'm not sure if this is very do-able but if so it'd really help a lot of users that aren't familiar enough with excel to use the index/match excel formula correctly. Unfortunately for me my excel skills far exceeds my VBA skills.

推荐答案

要在 VBA 代码中使用数组公式 将 Application 对象的 ReferenceStyle 设置为 xlR1C1 (暂时，仅在您的函数执行期间).最后调用 Evaluate 以获得公式的结果.

To use array formulas within VBA code set ReferenceStyle for Application object to xlR1C1 (temporarily, only during your function is executed). Finally call Evaluate to get the result of the formula.

Private Const TEMPLATE As String = "=INDEX({0},MATCH(1,({1}={2})*({3}={4}),{5}))"
Private Const MATCH_TYPE = 0

Public Function TestIndexMatch1(ByRef outputRange As Range, _
                                ByRef nameCriteria As Range, _
                                ByRef dateCriteria As Range, _
                                ByRef nameRange As Range, _
                                ByRef dateRange As Range)

    On Error GoTo Err_Handler
    Err.Number = 0

    Dim originalReferenceStyle
    originalReferenceStyle = Application.ReferenceStyle
    Application.ReferenceStyle = xlR1C1

    Dim myFormula As String
    myFormula = Replace(TEMPLATE, "{0}", outputRange.Address())
    myFormula = Replace(myFormula, "{1}", nameCriteria.Address())
    myFormula = Replace(myFormula, "{2}", nameRange.Address())
    myFormula = Replace(myFormula, "{3}", dateCriteria.Address())
    myFormula = Replace(myFormula, "{4}", dateRange.Address())
    myFormula = Replace(myFormula, "{5}", MATCH_TYPE)

    TestIndexMatch1 = Application.Evaluate(myFormula)

Err_Handler:
    If (Err.Number <> 0) Then MsgBox Err.Description
    Application.ReferenceStyle = originalReferenceStyle
End Function

所以它在工作表上看起来:

And so it looks on sheet:

这篇关于Excel/VBA - 使用动态范围的索引匹配函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！