SUMIFS 的 VBA 代码？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/428414/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA code for SUMIFS?
提问by Dane O'Connor
I'm trying to write a custom function that will let me retrieve a cell from the first row in a range that meets x number of criteria. I imagine this would be very similar to the way SUMIFS works, just simpler in that it doesn't continue processing after the first match.
我正在尝试编写一个自定义函数，它可以让我从满足 x 个条件的范围内的第一行中检索一个单元格。我想这与 SUMIFS 的工作方式非常相似，只是更简单，因为它不会在第一次匹配后继续处理。
Does anyone know code to reproduce the SUMIFS (excel 07) function in VBA?
有谁知道在 VBA 中重现 SUMIFS (excel 07) 函数的代码？
So, for example, if I have a table in excel like:
因此，例如，如果我在 excel 中有一个表格，例如：
W X Y Z
a b 6 1
a b 7 2
b b 7 3
I want to be able to write a function that will give me the value in column Z where columns W=a, X=b, Y>=7 (in other words the value 2).
我希望能够编写一个函数，该函数将为我提供 Z 列中的值，其中列 W=a、X=b、Y>=7（换句话说，值为 2）。
SUMIFS can approximately do this, assuming the record I want is unique and I'm looking to return a number. For my purposes though, those assumptions won't work.
SUMIFS 大致可以做到这一点，假设我想要的记录是唯一的，并且我希望返回一个数字。不过，就我的目的而言，这些假设是行不通的。
回答by Fionnuala
An example using ADO.
使用 ADO 的示例。
strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
'I want to be able to write a function that will give me the value '
'in column Z where columns W=a, X=b, Y>=7 '
'(in other words the value 2).'
strSQL = "SELECT Top 1 Z " _
& "FROM [Sheet1$] " _
& "WHERE W='a' And X='b' And Y>=7"
rs.Open strSQL, cn
Result = rs.Fields("Z")
回答by Fionnuala
IMHO ADO is not suitable for use in excel worksheet functions (poor performance and cannot easily be used on the worksheet containing the data). here is a VBA alternative:
恕我直言，ADO 不适合在 excel 工作表函数中使用（性能差，不能轻易用于包含数据的工作表）。这是一个 VBA 替代方案：
Function MFind(theRange As Range, ParamArray Tests() As Variant) As Variant
'
' Parameters are:
' The Range to be searched
' the values to be searched for in successive columns
' all search values except the last use =
' the last search value uses >=
' the function returns the value from the last column in the range
'
Dim vArr As Variant
Dim j As Long
Dim k As Long
Dim nParams As Long
Dim blFound As Boolean
vArr = theRange.Value2
nParams = UBound(Tests) - LBound(Tests) + 1
If nParams >= UBound(vArr, 2) Then
MFind = CVErr(xlErrValue)
Exit Function
End If
For j = 1 To UBound(vArr)
blFound = True
For k = LBound(Tests) To nParams - 2
If vArr(j, k + 1) <> Tests(k) Then
blFound = False
Exit For
End If
Next k
If blFound Then
If vArr(j, nParams) >= Tests(nParams - 1) Then
MFind = vArr(j, UBound(vArr, 2))
Exit For
End If
End If
Next j
End Function
回答by Mark Nold
Deeno, having a UDF for this is very useful but you could also use plain old =VLOOKUP().
Deeno，为此拥有一个 UDF 非常有用，但您也可以使用普通的=VLOOKUP().
VLOOKUP()only works by looking up one "key" but you can make a concatenated key in a helper column to the left. eg:
VLOOKUP()只能通过查找一个“键”来工作，但您可以在左侧的辅助列中创建一个连接键。例如：
W X Y Z AA
a b 6 ab6 1
a b 7 ab7 2
b b 7 bb7 3
Then =VLOOKUP(A1,$Z$1:$AA$3,2,FALSE)if A1 had the value you are looking for. If your data is more complicated you could join the data with an unused character (eg: a pipe) so you have a|B|6 instead of ab6.
然后，=VLOOKUP(A1,$Z$1:$AA$3,2,FALSE)如果 A1 具有您正在寻找的值。如果您的数据更复杂，您可以使用未使用的字符（例如：管道）连接数据，这样您就可以使用 a|B|6 而不是 ab6。
