VBA Countif(s) 语法问题（和/或关于多个条件）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14126962/
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 Countif(s) Syntax issue (and/or about multiple conditions)
提问by Finch042
I've looked around the Stackoverflow forums (and basically everywhere else on Google) and have found a lot of "almost" answers to my questions that would probably suffice if I was a bit more familiar with VBA, but I've messed around with it for a while and haven't been able to sort out. Getting a little frustrated, so I figured it was time to ask! Sorry if I get verbiage etc. wrong when explaining my issue! It's probably just a problem with my syntax.
我环顾了 Stackoverflow 论坛（基本上是 Google 上的其他所有地方），并找到了很多“几乎”的答案，如果我对 VBA 更熟悉一些，这些答案可能就足够了，但我已经搞砸了它有一段时间了，一直无法理顺。有点沮丧，所以我想是时候问了！对不起，如果我在解释我的问题时出现措辞等错误！这可能只是我的语法有问题。
Basically I need to be able to take data from a column in a spreadsheet and have it do a Countifs (I think anyway) of cells of a specific set of parameters. All of the data I need would be page dimensions with the format of "Dimension1 x Dimension 2" e.g. "8.5 x 11"
基本上，我需要能够从电子表格中的列中获取数据，并让它对一组特定参数的单元格进行 Countifs（我认为无论如何）。我需要的所有数据都是格式为“Dimension1 x Dimension 2”的页面尺寸，例如“8.5 x 11”
Valtest = Application.WorksheetFunction.CountIfs(Range("P:P"), "8.5 x 11")
This formula, probably rather unsurprisingly, works just fine. But I need the countifs (or whatever I need) to also be able to give me dimensions <= 8.5 x <=11 as well as flipping the dimensions (<=11 x <=8.5).
这个公式，可能不出所料，工作得很好。但是我需要 countifs（或我需要的任何东西）才能给我尺寸 <= 8.5 x <=11 以及翻转尺寸（<=11 x <=8.5）。
I've tried changing the formula to formats like (and similar)
我试过将公式更改为（和类似的）格式
Valtest = Application.WorksheetFunction.CountIfs(Range("P:P"), "<=8.5" & " x " & "11")
But that will report a dimension such as 3 x 4 or 22 x 11. I know countifs can use multiple parameters (which is why I was messing around with it instead of just a normal countif), but I don't know if entering multiple arguments is even the correct path or if it's something with quotations being used correctly or... who knows?
但这会报告一个维度，例如 3 x 4 或 22 x 11。我知道 countifs 可以使用多个参数（这就是为什么我在处理它而不是一个普通的 countif），但我不知道是否输入多个arguments 甚至是正确的路径，或者它是否是正确使用引号的东西，或者......谁知道？
I was able to make an If-then statement work alright (using an array and cycling through each cell with a counter) but this is clearly not the fastest way to do this. Here it is, just for the sake of making my goal a little clearer.
我能够使 If-then 语句正常工作（使用数组并使用计数器循环遍历每个单元格），但这显然不是执行此操作的最快方法。在这里，只是为了让我的目标更清晰一点。
'If x(0) <= 8.5 And x(1) <= 11 Or x(1) <= 8.5 And x(0) <= 11 Then
'如果 x(0) <= 8.5 并且 x(1) <= 11 或者 x(1) <= 8.5 并且 x(0) <= 11 那么
In a related issue, I'd also need to be able to find pages that are, for example, <=11 x <=17 or something while not including search results for my previous question (8.5 X 11). So I need to know the proper syntax for multiple parameters that would involve saying something like <8.5 but less >=17.
在相关问题中，我还需要能够找到例如 <=11 x <=17 或其他内容的页面，同时不包括我上一个问题 (8.5 X 11) 的搜索结果。所以我需要知道多个参数的正确语法，这些参数涉及说<8.5但小于>=17。
Thanks in advance! Any help is quite appreciated. Let me know if I haven't explained anything adequately.
提前致谢！非常感谢任何帮助。如果我没有充分解释任何内容，请告诉我。
Edit: An example of data I would be searching:
编辑：我要搜索的数据示例：
A Count for 8.5 x 11 (expected output)
8.6 x 11 5
8.5 x 11
8.5 x 11
8.5 x 11
8.5 x 11
8.4 x 11
22 x 11
10 x 17
采纳答案by Tim Williams
You can try this UDF: copy and paste to a regular VBA module. You pass it a range, and lower/upper bounds for the small and large dimensions respectively.
您可以尝试使用此 UDF：复制并粘贴到常规 VBA 模块。您分别向它传递一个范围，以及小维度和大维度的下限/上限。
Eg: to count all sizes with small side between 8 and 10 and large side between 12 and 14 (inclusive):
例如：计算小边在 8 到 10 之间，大边在 12 到 14（含）之间的所有尺寸：
=CountSizes(A:A,8,10,12,14)
EDIT: for your specific use case of 8.5x11 or smaller
编辑：对于 8.5x11 或更小的特定用例
=countsizes(A:A, 0, 8.5, 0, 11) 'one side btw 0 and 8.5 & one side btw 0 and 11.5
EDIT3: to show how you'd use this from VBA instead of as a UDF, including your second column
EDIT3：显示您如何从 VBA 使用它而不是作为 UDF，包括您的第二列
Sub Tester()
With ThisWorkBook.Sheets("Pages")
'count only where second column has "Color"
.Range("B1").Value = CountSizes(.Range("A:B"), "Color", 0, 8.5, 0, 11)
End With
End sub
Code:
代码：
Function CountSizes(rng As Range, colorType As String, _
smallGE, smallLE, largeGE, largeLE)
Dim tmp, val, v1, v2, small, large, arr, arrVals
Dim num As Long, r As Long, nr As Long
num = 0
arr = rng.Value
nr = UBound(arr, 1)
For r = 1 To nr
val = Trim(arr(r, 1))
If val Like "*x*" Then
arrVals = Split(val, "x")
v1 = Trim(arrVals(0))
v2 = Trim(arrVals(1))
If IsNumeric(v1) And IsNumeric(v2) Then
v1 = CDbl(v1)
v2 = CDbl(v2)
If v1 > v2 Then
small = v2: large = v1
Else
small = v1: large = v2
End If
If small >= smallGE And small <= smallLE And _
large >= largeGE And large <= largeLE Then
If Trim(arr(r, 2)) = colorType Then
num = num + 1
End If
End If
End If
End If
Next r
CountSizes = num
End Function
