logo标签列表

Excel - 在多个工作表/同一列中使用COUNTIF/COUNTIFS函数

excelvba
6
我想要计算在多个表格中某一列（例如，在此情况下是列I）中特定对象的数量。该列中的值是一个公式的结果（如果有关系的话）。目前我使用以下公式： =COUNTIF('Page M904'!I:I,A13)+COUNTIF('Page M905'!I:I,A13)+COUNTIF('Page M906'!I:I,A13) 这个公式是可行的，但是我需要扫描20多页表格。我想避免出现太长的公式。
我尝试过以下公式： =COUNTIFS('Page M904:Page M906'!I:I,A13)和=COUNTIF('Page M904:Page M906'!I:I,A13) 但是它们都会产生 #VALUE 的错误提示。
我认为以下公式： =COUNTIFS('Page M904'!I:I,A14,'Page M905'!I:I,A14,'Page M906'!I:I,A14) 不适用于COUNTIFS函数，因为我得到的结果是0，而不是35。
我想避免在这种情况下使用VBA，但是如果必须的话就不得不使用了 :) 预先感谢您的时间和帮助。
2
“'Page M904:Page M906'!I:I” - 尝试不错，但遗憾的是 Countif 不支持三维引用。 - Dmitry Pavliv
1
我正在努力避免使用VBA，但如果必要的话，那就只能使用它了 :) 你可以编写一个非常简单的用户定义函数来实现这个。 - Dmitry Pavliv
1
=COUNTIFS('Page M904'!I:I,A14,'Page M905'!I:I,A14,'Page M906'!I:I,A14) 返回0，因为在这种表述中它变成了IF...AND，而不是您想要的IF...OR。 - nutsch
谢谢大家。实际上，UDF对我来说是新的，但即使20年后我仍然是个新手 ;) 这里的每个人都非常有见解和容忍初学者提出的问题 :D - R West
你可以通过对这些工作表的列进行命名，来简化公式。比如说，用 M904i 来表示 Page M904 的第 I 列。这样 'Page M904'!I:I 就可以变成 M904i。但是仍然有20多个这样的命名。 - PatricK
4个回答
11

以下技巧可以不使用VBA解决。

在本例中，我正在计算工作表 Page M904、Page M905 和 Page M906 中范围 A:A 中所有数字三(3)的数量。

将所有工作表名称列在单个连续范围内，例如以下示例中的范围 D3:D5 中。

enter image description here

然后，通过在单元格 B2 中输入查找值，可以使用以下公式在单元格 B4 中找到结果:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&D3:D5&"'!A:A"), B2))
谢谢Søren，但我会选择VBA/UDF方法。我之前不知道UDFs，但现在我知道了，它似乎更简单。我非常感激您的帮助。 :D - R West
5

我希望尽量避免使用VBA。但如果必须，那就必须了：)

这里有一个相当简单的用户定义函数：

Function myCountIf(rng As Range, criteria) As Long
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        myCountIf = myCountIf + WorksheetFunction.CountIf(ws.Range(rng.Address), criteria)
    Next ws
End Function

并且这样调用它：=myCountIf(I:I,A13)

附注：如果您想排除一些表格，可以添加If语句：

Function myCountIf(rng As Range, criteria) As Long
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.name <> "Sheet1" And ws.name <> "Sheet2" Then
            myCountIf = myCountIf + WorksheetFunction.CountIf(ws.Range(rng.Address), criteria)
        End If
    Next ws
End Function

更新：

我有四张“参考”表格需要排除在扫描/搜索之外，它们目前位于工作簿的最后四张。

Function myCountIf(rng As Range, criteria) As Long
    Dim i As Integer

    For i = 1 To ThisWorkbook.Worksheets.Count - 4
        myCountIf = myCountIf + WorksheetFunction.CountIf(ThisWorkbook.Worksheets(i).Range(rng.Address), criteria)
    Next i
End Function
我有四个“参考”工作表需要排除在扫描/搜索之外。它们目前是工作簿中的最后四个，并将保持在那个位置。能否更改代码以排除这四个工作表？ - R West
愚蠢的问题...我该如何让UDF工作。我得到了一个#NAME?错误。宏已启用。 - R West
你应该创建一个新模块并将UDF放置在其中。如何创建模块请参见此图像：http://webum.by/images/blog/2013/05/excel-vba-access-bd/03-insetr-module.png - Dmitry Pavliv
2
我的第一反应本来会是“你所说的‘模块’是什么？”但我看了图片，跟着做了，它起作用了！非常感谢和尊敬。 - R West
2

我的第一篇文章... 我很快就编译好了UDF。 用法： 像下面这样选择3D范围并将其括在引号中...

=CountIf3D（“'StartSheet：EndSheet'！G16：G878”；“Criteria”）

建议工作表相邻，以避免出现意外结果。

Public Function CountIf3D(SheetstoCount As String, CriteriaToUse As Variant)

     Dim sStarSheet As String, sEndSheet As String, sAddress As String
     Dim lColonPos As Long, lExclaPos As Long, cnt As Long

    lColonPos = InStr(SheetstoCount, ":") 'Finding ':' separating sheets
    lExclaPos = InStr(SheetstoCount, "!") 'Finding '!' separating address from the sheets

    sStarSheet = Mid(SheetstoCount, 2, lColonPos - 2) 'Getting first sheet's name
    sEndSheet = Mid(SheetstoCount, lColonPos + 1, lExclaPos - lColonPos - 2) 'Getting last sheet's name

    sAddress = Mid(SheetstoCount, lExclaPos + 1, Len(SheetstoCount) - lExclaPos) 'Getting address

        cnt = 0
   For i = Sheets(sStarSheet).Index To Sheets(sEndSheet).Index
        cnt = cnt + Application.CountIf(Sheets(i).Range(sAddress), CriteriaToUse)
   Next

   CountIf3D = cnt

End Function
0

我正在寻找做同样的事情，并且我有一个解决方法，似乎使用频率和索引函数更少复杂。我使用该函数的一部分来在排除所有0的情况下对多个工作表进行平均。

=(FREQUENCY(Start:End!B1,-0.000001)+INDEX(FREQUENCY(Start:End!B1,0),2))
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接