wsf.CountIfs阵列溃败 - Excel的VBA [英] wsf.CountIfs Array Debacle - VBA Excel

问题描述

我现在面临一个位一个奇怪的问题，我不能完全解释的。

I'm facing a bit of a strange problem that I can't quite explain.

我使用工作表函数的公式COUNTIFS一起，我要使用阵列也使用SUMPRODUCT。

I'm using Worksheet function along with the CountIfs formula, and I also use Sumproduct in order to use Arrays.

不过，我尝试使用定义为阵列2不同的变量，每次我得到不正确的结果。

However, everytime I try using 2 different variables defined as arrays I get incorrect results.

让我解释一下，

当我使用：

Dim lastrow As Long
Dim wsf
lastrow = Sheet2.Cells(Sheet2.Rows.Count, "M").End(xlUp).Row
Set wsf = Application.WorksheetFunction
Doctors = Array("Peter","Sam","Henry")
Emergency = Array("Y","N")

a1 = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet2.Range("P2:P" & lastrow), Doctors, Sheet2.Range("M2:M" & lastrow), Emergency))

我得到了A1错误的结果。

I get wrong result for the a1.

然而，当我尝试：

a1 = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet2.Range("P2:P" & lastrow), Doctors, Sheet2.Range("M2:M" & lastrow), "Y"))

b1 = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet2.Range("P2:P" & lastrow), Doctors, Sheet2.Range("M2:M" & lastrow), "N"))

Final = a1 + b1

或者

a1 = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet2.Range("P2:P" & lastrow), "Peter", Sheet2.Range("M2:M" & lastrow), Emergency))

b1 = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet2.Range("P2:P" & lastrow), "Sam", Sheet2.Range("M2:M" & lastrow), Emergency))

c1 = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet2.Range("P2:P" & lastrow), "Henry", Sheet2.Range("M2:M" & lastrow), Emergency))

Final = a1 + b1 + c1

我得到正确的结果为总决赛。

I get the correct results for Final.

有没有办法让一个公式的工作，还是VBA根本不允许多个变量，这些变量数组用作单个COUNTIFS函数的标准。

Is there any way to get the first formula to work, or does vba simply not allow multiple variables that are arrays to be used as criteria in a single countifs function.

我想，也许我应该到目前为止申报医生和急救变量，但没有运气。

I thought that maybe I should declare the Doctors and Emergency variables but no luck so far.

有什么建议？

推荐答案

您可以在VBA，而不是函数使用数组：

You could use arrays in VBA instead of functions:

Option Explicit
Option Compare Text
Sub MediCount()
'
    Dim lastrow As Long
    Dim Doctors As Variant
    Dim Emergency As Variant
    Dim Profession As Variant
    Dim vData As Variant
    Dim Ct As Long
    Dim jDoc As Long
    Dim jEmr As Long
    Dim jPro As Long
    Dim j As Long
    '
    lastrow = Sheet1.Cells(Sheet1.Rows.Count, "M").End(xlUp).Row
    Doctors = Array("Peter", "Sam", "Harry")
    Emergency = Array("Y", "N")
    Profession = Array("Teacher", "Accountant", "Plumber", "Artist")
    ' assume emergency in M, Profession in N, Doctors in O, start row=2
    vData = Sheet1.Range("M2").Resize(lastrow, 3).Value2
    For j = LBound(vData) To UBound(vData)
        For jDoc = LBound(Doctors) To UBound(Doctors)
            If vData(j, 3) = Doctors(jDoc) Then
                For jEmr = LBound(Emergency) To UBound(Emergency)
                    If vData(j, 1) = Emergency(jEmr) Then
                        For jPro = LBound(Profession) To UBound(Profession)
                            If vData(j, 2) = Profession(jPro) Then
                                Ct = Ct + 1
                            End If
                        Next jPro
                    End If
                Next jEmr
            End If
        Next jDoc
    Next j
    MsgBox Ct
End Sub

（或者你可以使用我的Speed​​Tools ACOUNTIFS功能 = ACOUNTIFS（0，$ M $ 2：$ $Ö50,1,1，$ H $ 2：$ H $ 5.2，$ I $ 2：$ I $ 5,3，$ G $ 2：$ G $ 5）

但这就是商业的Excel插件）

but thats a commercial Excel addin)

这篇关于wsf.CountIfs阵列溃败 - Excel的VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！