2分钟时间区间内重复值匹配及对应数量求和需求

需求说明 #

现有公式用于在A列和B列统计2分钟时间间隔内的重复值匹配数量，原始数据存储在D、E、F列。目前遇到的问题是无法对匹配项对应的F列数量求和，需要修改现有公式或新建C列，实现对匹配项对应F列数量的求和。

原B列公式 #

{=SUMPRODUCT((($E$2:$E$50000)>=A2)*(($E$2:$E$50000)<=A2+TIME(,2,)))- SUMPRODUCT(N(IFERROR(IF((($E$2:$E$50000)>=A2)*(($E$2:$E$50000)<=A2+TIME(,2,)), FREQUENCY(MATCH(($D$2:$D$50000)&"|"&((($E$2:$E$50000)>=A2)*(($E$2:$E$50000)<=A2+TIME(,2,))), ($D$2:$D$50000)&"|"&((($E$2:$E$50000)>=A2)*(($E$2:$E$50000)<=A2+TIME(,2,))),0),ROW(($D$2:$D$50000))-ROW($D$2)+1)=1,0),0)))}

数据示例表格 #

C列求和解决方案 #

数组公式（需按Ctrl+Shift+Enter输入） #

在C2单元格输入以下公式，下拉填充即可：

{=SUM(IF(FREQUENCY(IF(($E$2:$E$50000>=A2)*($E$2:$E$50000<=A2+TIME(0,2,0)),MATCH($D$2:$D$50000,$D$2:$D$50000,0)),ROW($D$2:$D$50000)-ROW($D$2)+1)>0,IF(($E$2:$E$50000>=A2)*($E$2:$E$50000<=A2+TIME(0,2,0)),$F$2:$F$50000,0)))}

简化版数组公式 #

如果觉得上面的公式复杂，也可以用这个更简洁的版本（同样需按Ctrl+Shift+Enter输入）：

{=SUMPRODUCT($F$2:$F$50000/COUNTIFS($D$2:$D$50000,$D$2:$D$50000,$E$2:$E$50000,">="&A2,$E$2:$E$50000,"<="&A2+TIME(0,2,0))*(($E$2:$E$50000>=A2)*($E$2:$E$50000<=A2+TIME(0,2,0))))}

公式逻辑说明

• 先筛选出落在A2对应的2分钟时间区间内的记录；
• 对每个唯一的输入数据（D列值），将其对应的所有数量（F列值）求和，避免重复统计同一匹配项的数量。

内容的提问来源于stack exchange，提问作者mjac