VBA Countif（s）语法问题（和/或关于多种情况） [英] VBA Countif(s) Syntax issue (and/or about multiple conditions)

问题描述

我环顾了Stackoverflow论坛（基本上在Google上的所有其他地方），发现了很多几乎答案，如果我对VBA有点熟悉的话，可能就足够了，但是我已经弄乱了好一阵子，还没整理好。有点沮丧，所以我想是时候问了！对不起，如果我在解释我的问题时遇到了错误等问题！我的语法可能只是一个问题。

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.

基本上，我需要能够从电子表格中的列中获取数据，并使其对一组特定参数的单元格进行计数（无论如何，我认为）。我需要的所有数据都是页面尺寸，格式为 Dimension1 x Dimension 2，例如 8.5 x 11

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"

Valtest = Application.WorksheetFunction.CountIfs(Range("P:P"), "8.5 x 11")

这个公式可能就不足为奇了。但是我需要计数（或任何我需要的），以便能够为我提供尺寸< = 8.5 x< = 11以及翻转尺寸（< = 11 x< = 8.5）。

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).

我尝试将公式更改为（和类似的）格式

I've tried changing the formula to formats like (and similar)

Valtest = Application.WorksheetFunction.CountIfs(Range("P:P"), "<=8.5" & " x " & "11")

但是这将报告一个尺寸，例如3 x 4或22 x11。我知道countifs可以使用多个参数（这就是为什么我在搞乱它而不是正常countif），但我不知道输入多个参数是否甚至是正确的路径，还是输入正确使用引号的东西还是...谁知道？

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?

我能够使If-then语句正常工作（使用数组并使用计数器循环遍历每个单元格），但这显然不是最快的方法这个。只是为了使我的目标更加清晰。

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.

'如果x（0）< = 8.5并且x（1）< = 11或
x（1）< = 8.5 And x（0 ）< = 11然后

'If x(0) <= 8.5 And x(1) <= 11 Or x(1) <= 8.5 And x(0) <= 11 Then

在相关问题中，我还需要能够找到例如< = 11 x< = 17之类的东西，但不包括我上一个问题的搜索结果（8.5 X 11）。因此，我需要知道多个参数的正确语法，这涉及到说< 8.5但少了> = 17。

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.

谢谢！任何帮助都非常感谢。让我知道我是否还没有足够的解释。

Thanks in advance! Any help is quite appreciated. Let me know if I haven't explained anything adequately.

编辑：我要搜索的数据示例：

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   

推荐答案

您可以尝试以下UDF：复制并粘贴到常规VBA模块中。您分别给它传递了一个范围，以及小尺寸和大尺寸的上下边界。

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.

例如：计算所有尺寸介于8到10之间的小尺寸和大尺寸的小尺寸在12到14（含）之间：

Eg: to count all sizes with small side between 8 and 10 and large side between 12 and 14 (inclusive):

=CountSizes(A:A,8,10,12,14)

编辑：针对您的8.5x11或更小的特定使用案例

for your specific use case of 8.5x11 or smaller

=countsizes(A:A, 0, 8.5, 0, 11)  'one side btw 0 and 8.5 & one side btw 0 and 11.5   

EDIT3：显示如何从VBA而不是as中使用它UDF，包括第二列

to show how you'd use this from VBA instead of as a UDF, including your second column

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

代码：

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

这篇关于VBA Countif（s）语法问题（和/或关于多种情况）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！