使 COUNTIFS 仅对重复值进行一次计数

使 COUNTIFS 仅对重复值进行一次计数

我获得了有关测试运行的所有数据:

controller  start   end start ts    end ts
1           1:13    1:15    1:00    1:30
1           2:08    2:25    2:00    2:30
4           4:02    4:16    4:00    4:30    
4           4:17    5:35    4:00    6:00
2           4:03    5:39    4:00    6:00

虽然每个测试都在特定时间开始和结束,但是测试调度程序保留了整个半小时的时间段,因此我用 FLOOR 和 CEILING 来填充时间。

每个测试都使用特定的控制器,并且在运行期间,其他测试不能使用该控制器。

我想根据这些数据计算特定时间段内同时使用的控制器的最大数量。为此,我认为可以计算给定测试开始时使用的控制器数量,因为这是使用中的控制器数量唯一会增加的时间。

所以,我的公式是:(使用 2010 年非常好的表格数据语法)

=COUNTIFS([start timeslot],"<="&[@[start timeslot]],[end timeslot],">"&[@[start timeslot]])

但是,由于时间段舍入会在实际上不重叠的测试之间引入重叠,因此 countifs 会按同一控制器上发生的测试次数进行计数。

我需要一种方法来让 COUNTIFS 在匹配范围内出现重复值时仅计算一次。

这是一个包含一些示例数据和我的公式的 Excel 文件:

http://dl.dropbox.com/u/123900/sumproducttest.xlsx

更多信息:

我实际上构建了一个数组公式来计算我想要的数字:

{=SUM(--([start timeslot]<=[@[start timeslot]])*--([end timeslot]>[@[start timeslot]])*IFERROR(1/COUNTIFS([controller],[controller],[start timeslot],"<="&[@[start timeslot]],[end timeslot],">"&[@[start timeslot]]),0))}

该数组公式中的 COUNTIF 会生成一个加权数组,表示当前测试启动时运行的测试集中特定控制器出现的次数。因此,sum 函数会将两个使用该控制器的测试分别计为 1/2,从而导致该控制器总共只被计数一次。

但是,我的真实数据集有 3000 多行,而我的数组公式让可怜的 Excel 不堪重负。(即使我错误报告的 COUNTIFS 函数在我的旧双核笔记本电脑上也需要大约 30 秒,因此数组公式每分钟左右计算 1% ;_;)

答案1

根据这些数据,我想计算特定时间段内使用的并发控制器的最大数量。

如果你想在 Excel 中执行此操作,为什么不使用数据透视表呢?它们设计用于数据汇总,如下所示:

(请注意右外栏的 countif)

Excel 包含源数据和数据透视表,并在数据透视表的右侧显示 countif

PS. 您可以选择任何您想要的数据分组(开始、结束、开始 ts、结束 ts,.....)

相关内容