vba 在 Excel 中创建 AVERAGEIFS
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15266283/
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
creating AVERAGEIFS in Excel
提问by Jakob
I've never used AVERGEIFSbefore, and it's getting a little too complex for me, so please help me out.
我以前从未使用AVERGEIFS过，对我来说有点太复杂了，所以请帮助我。
I have a range, where I want to average column values in column A for rows that have column value B equal to 2 and column A values lesser than 3, so is this correct:
我有一个范围，我想在 A 列中为列值 B 等于 2 且 A 列值小于 3 的行平均列值，所以这是正确的：
WorksheetFunction.averageifs(columnA, columnA, "<3", columnB, "2")
EDITI get a "Type Mismatch"
编辑我得到一个“类型不匹配”
ColumnA and ColumnB are ranges, in if sentences this is what I want
ColumnA 和 ColumnB 是范围，如果句子这就是我想要的
IF columnA < 3 AND columnB = 2 Then Average(columnA)
Edit
编辑
I'm assuming this must have something to do with my types then, the averageifs throws a 1004 cannot get the averageifs property of class worksheetfunctionwhen I enter my parameters, however countifs throws 13 Type Mismatchgiven the same parameters:
For Each column In importsheet.UsedRange.Columns
colcount = .CountIfs(column, column, "<3", importsheet.UsedRange.Columns(DepColumn), sec)
Next
我假设这一定与我的类型有关，1004 cannot get the averageifs property of class worksheetfunction当我输入参数时，averageifs 会抛出 a ，但是 countifs 会抛出13 Type Mismatch相同的参数：
For Each column In importsheet.UsedRange.Columns
colcount = .CountIfs(column, column, "<3", importsheet.UsedRange.Columns(DepColumn), sec)
Next
DepColumn and colcount are integers,
sec is a string with an integer ("2")clusterfwor of errors above, stroke it out
DepColumn 和 colcount 是整数，sec 是一个字符串，上面有一个整数（“2”）clusterfwor 的错误，把它画出来
回答by Peter Albert
Assuming that columnAand columnBare ranges of the same size, your formula is correct:
假设columnA和columnB是相同大小的范围，您的公式是正确的：
WorksheetFunction.Averageifs(Range("A:A"),Range("A:A"),"<3",Range("B:B"),"2")
will return you the average of column A where A<3 and B=2.
将返回 A 列的平均值，其中 A<3 且 B=2。
回答by chris neilsen
The syntax of your posted code is correct provided
的您发布的代码的语法是正确的提供
- you have
Dim'edcolumnAandcolumnBasRange's andSetthen to valid ranges - the data on your sheet returns a result (ie there is at least one row that meets the criteria)
- 你已经
Dim'edcolumnA和columnBasRange'sSet然后到有效范围 - 工作表上的数据返回结果（即至少有一行符合条件）
Demo with error handling:
带有错误处理的演示：
Sub Demo()
Dim columnA As Range
Dim columnB As Range
Dim v
Set columnA = [A:A]
Set columnB = [B:B]
On Error Resume Next
v = WorksheetFunction.AverageIfs(columnA, columnA, "<3", columnB, "2")
If Err.Number = 1004 Then
'No matching data
v = CVErr(xlErrNA)
End If
On Error GoTo 0
End Sub
