☰

使用多个条件计算重复项（如果频率匹配则求和）

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
使用多个条件计算重复项（如果频率匹配则求和）

我正在尝试使用 SUM IF FREQUENCY MATCH 公式组合从事件日志中获取崩溃次数。 A 列具有所有事件 ID（但在计数时我想忽略重复项）。E 列有发生事故的道路名称，我只想计算在特定道路上发生的事故（I-96，I-196，M-6，US-131，US-31）。 J 列具有事件类型，我只想计算“碰撞”事件。事件类型必须是碰撞才能计算，但它可以是列出的 5 条道路中的任何一条（因此有 and/or 函数）。E 列中偶尔会有空白，所以我不希望这些空白搞砸 Match 函数，但引用的其他列没有任何空白。目前这是我下面的公式，但在 CRTL/SHIFT/ENTER 之后，我只得到了“0”的值。有人知道我哪里错了吗？

=SUM(IF(FREQUENCY(IF('Sheet1'!$E:$E<>"",IF(AND('Sheet1'!$J:$J="Crash",OR('Sheet1'!$E:$E="I-96",'Sheet1'!$E:$E="I-196",'Sheet1'!$E:$E="M-6",'Sheet1'!$E:$E="US-131",'Sheet1'!$E:$E="US-31")),MATCH('Sheet1'!$A:$A,'Sheet1'!$A:$A,0))),ROW('Sheet1'!$A:$A)-ROW('Sheet1'!$A$1)),1))

答案1

听起来数据透视表是有序的：

尝试旋转（允许您过滤）道路数据，然后将碰撞类型放在您的列中，然后汇总计数并为您提供您正在寻找的表格。

如果 Id 是唯一的，那么您应该首先“删除重复项”。

在此处输入图片描述

答案2

我想建议两种不同的方法来解决这个问题：

方法 1：

在此处输入图片描述

  • 要获取发生事故的道路的唯一列表，请在单元格中输入此数组（CSE）公式E64，最后输入Ctrl+Shift+Enter& 向下填充。

    {=IFERROR(INDEX($B$64:$B$75, MATCH(0, IF($G$63=$C$64:$C$75, COUNTIF($E$63:$E63, $B$64:$B$75), ""), 0)),"")}

  • 单元格中的公式F64，也填下来。

    =COUNTIFS($B$64:$B$75,E64,$C$64:$C$75,$G$63)

  • 单元格中的公式F70，

    =SUM(F64:F68)。

方法 2：

  • 获取发生事故的道路的唯一列表后，使用上面显示的数组公式，在单元格中输入该列表F71。

    =SUMPRODUCT(ISNUMBER(MATCH($B$64:$B$75,$E$64:$E$68,0))*($C$64:$C$75=$G$63))

根据需要调整公式中的单元格引用。

相关内容