☰

Excel COUNTIFS 带 OR 条件

tag-icon • tag-icon microsoft-excel-2013
Excel COUNTIFS 带 OR 条件

我想根据几个条件对单元格进行计数。其中两个条件引用同一列，应该进行“或”运算。我知道我可以添加两个 COUNTIF，但我想一定有更好的方法，但我还没有找到。

例如：
这些是我的源单元格（A 至 D 列）

    A 字符串 B 字符串 C 字符串 年月
    12345A XYZ UVW1 201301
    ABCDE XYZ UVW1 201302
    BCDEF XYZ UVW1 201301
    12345A XYZ UVW1 201303
    T2345 XYZ UVW1 201304
    T2345 XYZ UVW1 201301
    ABCDE XYZ UVW1 201301

...这就是我想要的结果（H 至 M 列）

    标题 XYZ 201301 201302 201303 201304
    紫外线1             1       0 1 1
    紫外线W2 0 0 0 0
    紫外线W3 0 0 0 0
    紫外线W4 0 0 0 0
    紫外线5 0 0 0 0
    紫外线W6 0 0 0 0
    UVW7 0 0 0 0

使用以下公式，粗体数字 1 应为 2：

=SUMPRODUCT(($B:$B=$I$1)*($C:$C=$H2)*($D:$D=J$1)*ISNUMBER(MATCH(MID($A:$A;1;2);{"1?";"T2"};0)))

不幸的是，通配符似乎不起作用。由于结果确实在不同的表中，因此以下公式（虽然可行）会非常冗长且耗时。它还会重复我不喜欢的条件。

=COUNTIFS($B:$B;$I$1;$C:$C;$H2;$D:$D;J$1;$A:$A;"1*")+COUNTIFS($B:$B;$I$1;$C:$C;$H2;$D:$D;J$1;$A:$A;"T2*")

对于不重复条件的工作公式有什么想法吗？

编辑：此外，正如 agtoever 指出的那样，我可以添加一个新的列来排序，但不幸的是，添加一列意味着要经历一些几乎无法维护的宏，这是我希望避免的。

答案1

诀窍在于意识到匹配 1? 或 T2 可以被视为两个单独的标准，但它们应该用 + 而不是 * 来组合。由于它们是互斥的，因此它们的总和将始终为 0 或 1。

=SUM(($B$2:$B$8=$I$1)*($C$2:$C$8=$H3)*($D$2:$D$8=K$1)*
     ((LEFT($A$2:$A$8,1)="1")+(LEFT($A$2:$A$8,2)="T2")))

使用 + 或 的条件总和

从您的 SUMPRODUCT 选项开始，我首先注意到它只有一个参数，因此我切换到仅使用 SUM。我还从处理整个列改为仅处理我需要的范围，因为在我的低性能系统上重新计算需要很长时间。如果您关心速度，避免处理整个列可能很重要，但您可能比我更有经验。

答案2

要创建结果，请使用数据透视表函数Excel 提供了此工具。步骤如下：

  • 选择源数据（A:D 列）
  • 插入数据透视表（通常在功能区的“插入”和/或“数据”选项卡中找到）
  • 将 C 字符串设置为行标题
  • 将 YearMonth 设置为列标题
  • 在报告过滤器中设置 B 字符串
  • 在值区域中设置一个字符串（或任何其他字段），并确保项目计数（未求和）
  • 根据您的喜好进一步调整。

要根据字符串过滤结果，您有两个选择：

  1. 在您原始表中添加一列以检查您的条件（在您的情况下，第 2 行中的内容如下：=OR(LEFT(A2;2)="T2";LEFT(A2;1)="1")然后将此字段添加到数据透视表报告过滤器并进行过滤TRUE。
  2. 将字符串 A 添加到报告过滤器中，然后手动选择要显示的每个值。请注意，这假设值的数量相对有限，否则，要保持正确的过滤效果将需要相当多的工作。

就我个人而言，我更喜欢第一个，因为它具有更好的透明度和可维护性。

相关内容