vba 如何使 SUMIFS 文本与另一列中的多个条件匹配？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16505954/
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
How to do SUMIFS text match with multiple criteria from another column?
提问by user2374537
I wish to categorize my transactions in a way where I can alter the categories on the fly. I think it's easier explained by showing what I have.
我希望以一种可以即时更改类别的方式对我的交易进行分类。我认为通过展示我拥有的东西更容易解释。
I have the following tables
我有以下表格
Transactions
交易
- A: Date
- C: Name
- D: Amount
- 一个约会
- C: 姓名
- D：金额
Fast Food List:
快餐清单：
- L: Name (partial name since going to be doing string search)
- L：名称（部分名称，因为要进行字符串搜索）
I wish to sum the transaction amount based on multiple criteria, such as date and category. Here's a formula that works:
我希望根据多个条件（例如日期和类别）对交易金额求和。这是一个有效的公式：
=SUMIFS(D:D,A:A,"*03/2013*",C:C,"*"&L3&"*")
There's one fundamental problem: it only supports ONE item from the Fast Food List. Is there any way I can simply do a text stringth search across the entire Fast Food names? ""&L3&"" to ""&L:L&"" or something?
有一个基本问题：它只支持 Fast Food List 中的一项。有什么方法可以简单地对整个快餐名称进行文本字符串搜索？" "&L3&"" 到 " "&L:L&"" 或者什么？
Here are some things I've tried.
这是我尝试过的一些事情。
1) Modify the SUMIFS criteria ""&L3&"" with a boolean UDF. The issue I run into here is that I can't figure out how to pass the current Row being looped by SUMIF into the function.
1)使用布尔 UDF修改 SUMIFS 标准“ "&L3&"”。我在这里遇到的问题是我不知道如何将 SUMIF 循环的当前 Row 传递到函数中。
Public Function checkRange(Check As String, R As Range) As Boolean
For Each MyCell In R
If InStr(Check, MyCell.Value) > 0 Then
checkRange = True
End If
Next MyCell
End Function
If I could send Check to this function, well I would be set.
如果我可以将 Check 发送到这个函数，那么我就会被设置。
2) Replace the sum_range of the SUMIFS with a UDF that returns the range of rows
2) 用返回行范围的 UDF 替换 SUMIFS 的 sum_range
Public Function pruneRange(Prune_range As Range, Criteria_range As Range) As Range
Dim Out_R As Range
Dim Str As String
ActiveWorkbook.Sheets("Vancity Trans").Activate
' Loop through the prune_range to make sure it belongs
For Each Cell In Prune_range
' loop through criteria to see if it matches current Cell
For Each MyCell In Criteria_range
If InStr(Cell.Value, MyCell.Value) > 0 Then
' Now append cell to Out_r and exit this foreach
' Str = Str & Cell.Address() & ","
Str = Str & "D" & Cell.Row() & ","
Exit For
End If
Next MyCell
Next Cell
' remove last comma form str
Str = Left(Str, Len(Str) - 1)
' use str to set the range
Set Out_R = Range(Str)
' MsgBox (Str)
Set pruneRange = Out_R
End Function
This works for a regular SUM loop, but for some reason it returns #Value when I try using it in a SUMIF or SUMIFS. Another issue is that even in the SUM loop if use C:C instead of C1:CX where X is however many rows, it crashes excel or takes forever to loop through. I'm guessing it's because excel doesn't know when to stop in a UDF unless I somehow tell it to?
这适用于常规 SUM 循环，但由于某种原因，当我尝试在 SUMIF 或 SUMIFS 中使用它时，它会返回 #Value。另一个问题是，即使在 SUM 循环中，如果使用 C:C 而不是 C1:CX，其中 X 是多行，它会导致 excel 崩溃或需要永远循环。我猜这是因为 excel 不知道何时停止在 UDF 中，除非我以某种方式告诉它？
回答by barry houdini
Try this formula
试试这个公式
=SUMPRODUCT(SUMIFS(D:D,A:A,"*03/2013*",C:C,"*"&L3:L30&"*"))
=SUMPRODUCT(SUMIFS(D:D,A:A,"*03/2013*",C:C,"*"&L3:L30&"*"))
By using a range (L3:L30) for the final criterion the SUMIFS formula will generate an "array" (of 28 values - one for each value in L3:L30) ...and SUMPRODUCT is used to sum that array and get the result you want
通过使用范围 (L3:L30) 作为最终标准，SUMIFS 公式将生成一个“数组”（28 个值 - L3:L30 中的每个值一个）......并且 SUMPRODUCT 用于对该数组求和并获得你想要的结果
