☰

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

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
使 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，.....）

相关内容