在Microsoft Excel中使用VBA函数进行索引匹配 [英] Index Match using VBA Function in Microsoft Excel

问题描述

我是Excel VBA的新手，正在尝试编写一个函数，让我使用 INDEX ， MATCH 和 COUNTIFS 函数进行查找通过匹配其他列中的条件(多个)来获得给定列的值.

I'm new to Excel VBA and am trying to write a function that lets me use INDEX, MATCH and COUNTIFS functions to find value for a given column by matching criteria in other columns (multiple).

我有一个名为 Price 的表，其中包含根据分配的类别在不同位置提供的一些价格:

I have a table named Price which contains some prices offered in different locations based on an assigned category:

Area    Category    Cost    Retail Price    Wholesale Price
USA Bad 1   13  25
Canada  Okay    2   14  26
Mexico  Good    3   15  27
USA Excellent   4   16  28
Canada  Bad 5   17  29
Mexico  Okay    6   18  30
USA Good    7   19  31
Canada  Excellent   8   20  32
Mexico  Bad 9   21  33
USA Okay    10  22  34
Canada  Good    11  23  35
Mexico  Excellent   12  24  36

在Excel中，我可以使用数组公式来获取它(请参见视频示例此处).以下公式可让我获取墨西哥

In Excel I can use the an array formula to get this (see video example here). The below formula lets me get Wholesale Price for Okay category in Mexico

{=INDEX(Price,MATCH(1,COUNTIFS(L12,Price[Area],M12,Price[Category]),0),MATCH(N12,Price[#Headers],0))}

哪里

L12 = Mexico
M12 = Okay 
N12 = Wholesale Price 

我想制作一个VBA函数 ProdPrice ，该函数可以返回该值而无需看起来凌乱的公式.这是我到目前为止的内容:

I'd like to make a VBA function ProdPrice that can return this value without a messy-looking formula. Here's what I have so far:

Function ProdPrice(locs, cat, pricetype)
    
    'Get column number corresponding to selected "pricetype"
    col_num = WorksheetFunction.Match( _
                pricetype, _
                ActiveSheet.ListObjects("Price").HeaderRowRange.Select, _
                0)
                
    ProdPrice = WorksheetFunction.Index( _
                    ActiveSheet.ListObjects("Price"), _
                    WorksheetFunction.Match( _
                        1, _
                        WorksheetFunction.CountIfs( _
                            ActiveSheet.ListObjects("Price").ListColumns("Area").DataBodyRange.Select, _
                            locs, _
                            ActiveSheet.ListObjects("Price").ListColumns("Category").DataBodyRange.Select, _
                            cat), _
                        0), _
                    col_num)

End Function

我没有使用一个调用，而是使用 _ 将函数分成了两个部分，以提高可读性.

Instead of a single call, I broke the function into two segments with _ for better readability.

运行此命令时，我得到一个 #VALUE！输出.

When I run this I get a #VALUE! output.

关于我该如何处理的任何建议?根据我的理解，上面的函数将在工作表中查找名为 price 的表-我希望它能够查看 price

Any suggestions on how I can go about this? Also from what I understand, the above function will look for a table called price in the worksheet where the function is called - I'd like for it to be able to look at price in the workbook instead.

推荐答案

我假定您的值始终在Excel结构化表中

I assume that your values are always in an Excel structured table

备注:

• 函数不区分大小写
• 表头和值不包含 | 字符
• 函数将返回第一个匹配值( area 和 category )
• 列顺序无关

请阅读代码的注释并对其进行调整以适合您的需求

Please read code's comments and adjust it to fit your needs

代码

Public Function lookupPrice(ByVal table As Range, ByVal lookup_area As String, ByVal lookup_category As String, ByVal pricetype_header As String) As Variant
    
    ' Define column headers
    Dim areaHeader As String
    Dim categoryHeader As String
    areaHeader = "Area"
    categoryHeader = "Category"
    
    ' Get area column number from headers
    Dim areaColumn As Long
    areaColumn = Application.Match(areaHeader, table.ListObject.HeaderRowRange, False)
    
    ' Get category column number from headers
    Dim categoryColumn As Long
    categoryColumn = Application.Match(categoryHeader, table.ListObject.HeaderRowRange, False)
    
    ' Get price column number from headers according to function parameter
    Dim priceColumn As Long
    priceColumn = Application.Match(pricetype_header, table.ListObject.HeaderRowRange, False)
    
    
    ' Get area column values into 1d array
    Dim areaValues As Variant
    areaValues = WorksheetFunction.Transpose(Application.Index(table.Columns(areaColumn), 0, 1))
    
    ' Get category column values into 1d array
    Dim categoryValues As Variant
    categoryValues = WorksheetFunction.Transpose(Application.Index(table.Columns(categoryColumn), 0, 1))
    
    ' Define and redimension an array to hold the concatenated area and category values
    Dim areaCategoryValues() As Variant
    ReDim areaCategoryValues(1 To UBound(areaValues))
    
    ' Concatenate the area and category values and store them in an array
    Dim counter As Long
    For counter = 1 To UBound(areaValues)
        areaCategoryValues(counter) = areaValues(counter) & "|" & categoryValues(counter)
    Next counter
    
    ' Get the matching row according to lookup values
    Dim resultRow As Long
    resultRow = Application.Match(lookup_area & "|" & lookup_category, areaCategoryValues, False)
    
    ' Get the result value according to the price column number
    Dim result As Variant
    result = Application.Index(table.Columns(priceColumn), resultRow)
    
    ' Return the value
    lookupPrice = result
    
End Function

让我知道它是否有效

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