☰

显示符合 COUNTIF 条件的行

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 countif
显示符合 COUNTIF 条件的行

不确定这是否可能，但使用 COUNTIF 函数时是否可以真正找到符合条件的行。

例如，我有一个 COUNTIF 公式，它查看大量数据并返回结果“5”

因此有 5 行满足 COUNTIF 条件，是否可以轻松找到这 5 行？如果可以，如何找到？

提前致谢 ：）

答案1

这是一个非常简单的例子。我们想计算列中值的数量乙超过10.我们将条件放在单元格中C1以及C2进入：

=COUNTIF(B:B,C1)

在此处输入图片描述

我们现在知道11有助于计数的项目。现在我们要找到它们。
在标准模块中输入以下用户定义函数：

Public Function CountIfFinder(rng As Range, crit As String) As String
    Dim r As Range, DQ As String

    DQ = Chr(34)
    crit = DQ & crit & DQ
    CountIfFinder = ""
    Set rng = Intersect(rng, rng.Parent.UsedRange)

    For Each r In rng
        s = "=countif(" & r.Address & "," & crit & ")"
        If Evaluate(s) = 1 Then CountIfFinder = CountIfFinder & "," & r.Address(0, 0)
    Next r

    CountIfFinder = Mid(CountIfFinder, 2)
End Function

选择一个单元格（说D1）并输入：

=CountIfFinder(B:B,C1)

在此处输入图片描述

用户定义函数 (UDF) 非常容易安装和使用：

  1. ALT-F11 打开 VBE 窗口
  2. ALT-I ALT-M 打开新模块
  3. 粘贴内容并关闭 VBE 窗口

如果您保存工作簿，UDF 将随之保存。如果您使用的是 2003 之后的 Excel 版本，则必须将文件保存为 .xlsm 而不是 .xlsx

要删除 UDF：

  1. 调出如上所示的 VBE 窗口
  2. 清除代码
  3. 关闭 VBE 窗口

要从 Excel 使用 UDF：

=我的函数（A1）

要了解有关宏的更多信息，请参阅：

http://www.mvps.org/dmcritchie/excel/getstarted.htm

和

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

有关 UDF 的详细信息，请参阅：

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

必须启用宏才能使其工作！

答案2

请参阅下面的解决方案，尽管所有步骤已在附图中详细说明。

我将对下面所示的每个步骤起草一份详细的解释，以解释它们的工作原理。

在此处输入图片描述

步骤1：

演示我们建立的搜索条件。在此示例中，我们查找 Gender = M 的所有行

下面显示了等效的 COUNTIF 函数，返回符合此条件的行数为 3

第2步：

建立一个数组公式=IF($B$2:$B$8=$B$11,ROW($B$2:$B$8))。这是一个数组公式，使用常规 IF 函数的扩展。它将数组中的值B2:B8与进行比较B11，并将比较结果作为值数组返回。当比较为真时，结果为 ROW() 数字，否则为 FALSE（因为比较为假时没有提供任何值）。

为了进一步理解这一点，您可以从下面更简单的 IF 公式开始，并在 value_if_true 和 value_if_false 中尝试不同的选项，并理解结果 `IF(B2=B11,ROW(B2),)'

`如果（B2 = B11，ROW（B2），“不匹配”）'

现在尝试将 B11 改为 F，然后看看结果会发生什么变化。

步骤3： 这里我们使用 SMALL 函数返回数组中第 n 个最小值。但是这里的技巧是更改每一行上的第 n 个值。因此，第一行应显示数组中的最小值F2:F8，第二行应返回第二小值，第三行应返回第三小值。

因此我们使用 ROW()-1 来获取相应的第 n 个变量设置，剩下的就很简单了。

步骤4：

在步骤 3 结束时，我们得到了满足搜索条件的行数。现在在这一步中，我们需要做的就是使用 INDEX 函数提取与这些行号相对应的行值。

为此，首先选择单元格 M2:O2，按下F2，光标将位于单元格 M2 中。输入公式INDEX($A$1:$C$8,J2,0)，然后同时按下 Ctrl + Shift + Enter ，使其作为数组公式运行。此公式中的 0 强制返回整行，而不是来自范围 A1:C8 中特定列的值。

现在选择 M2:O4 并按 Ctrl+D 将最顶行的公式复制到下面的单元格。

答对了！

如果您需要澄清，请发表您的评论，我非常乐意为您澄清。我使用了许多简化方法，并分解了步骤来解释其功能。所有这些公式都可以组合在一起，一次性实现相同的结果。

还有另一个简化：选择以精确的行数输入公式，但是，当您不知道搜索条件将返回的行数时，您可以将最终结果数组设置为与原始数据集范围一样大，以便在返回所有行时能够满足需求。您还可以在每个公式中添加错误处理，以便在返回的行数小于结果区域时显示空白行。希望这有意义！

相关内容