google-sheets - 带时间间隔的计数

Question

我的Google表格:

name   paid   time
john    y     2015/08/02 14:14
john    n     2015/08/02 14:13
john    n     2015/08/02 14:12
pat     n     2015/08/02 14:11
peter   n     2015/08/02 14:10
greg    n     2015/08/02 14:09
ricardo n     2015/08/02 14:05
barack  y     2015/08/02 14:02
john    n     2015/08/02 20:14

我需要计算在10分钟内不付款或不付款的人(如果该人在10分钟内不付款，那么他还没有付款)。我尝试了:

=SUMPRODUCT(--(A2:A9<>"")/COUNTIF(A2:A9,A2:A9&""))-COUNTIF(B2:B9,"y")

这是一个很好的开始，但是我没有时间间隔作为参数。

在这种情况下， 会返回4，但不会返回5，因为我希望，因为 John在10分钟(14:12与14:14之间)之间支付，但不在20:14时支付。

例如，它从2015年8月2日14:12开始为John(第一个NO-> n)开始，然后让我们看看接下来10分钟内会发生什么。

如果没有，那么我们认为他没有付款。
否则为“y”，因此他付款了

而且我们无论如何稍后继续看看john n 2015/08/02 20:14会发生什么(他没有付款，因为20:14之后什么都没有)

所以 总共有5个人，而不是4个人。

Answer 1

简短答案

首先获取每个n和y类别的最新日期，然后计算最近n个日期比y日期新的那些名称。

评论

故意忽略了有关“在10分钟内不付款(如果该人在10分钟内不付款，因此他还没有付款)”的部分，因为OP并未包含有关如何收集数据的详细信息，也没有包含有关何时以及如何使用计算结果。

解释

源数据

考虑以下内容作为源数据

+----+---------+------+-------------------+
|    |    A    |  B   |         C         |
+----+---------+------+-------------------+
|  1 | name    | paid | time              |
|  2 | john    | y    | 8/2/2015 14:14:00 |
|  3 | john    | n    | 8/2/2015 14:13:00 |
|  4 | john    | n    | 8/2/2015 14:12:00 |
|  5 | pat     | n    | 8/2/2015 14:11:00 |
|  6 | peter   | n    | 8/2/2015 14:10:00 |
|  7 | greg    | n    | 8/2/2015 14:09:00 |
|  8 | ricardo | n    | 8/2/2015 14:05:00 |
|  9 | barack  | y    | 8/2/2015 14:02:00 |
| 10 | john    | n    | 8/2/2015 20:14:00 |
+----+---------+------+-------------------+

步骤1

将以下公式添加到单元格E1中

=QUERY(A:C,"select A,max(C) where A <>'' group by A pivot B",1)

结果

+---+---------+-------------------+-------------------+
|   |    E    |         F         |         G         |
+---+---------+-------------------+-------------------+
| 1 | name    | n                 | y                 |
| 2 | barack  |                   | 8/2/2015 14:02:00 |
| 3 | greg    | 8/2/2015 14:09:00 |                   |
| 4 | john    | 8/2/2015 20:14:00 | 8/2/2015 14:14:00 |
| 5 | pat     | 8/2/2015 14:11:00 |                   |
| 6 | peter   | 8/2/2015 14:10:00 |                   |
| 7 | ricardo | 8/2/2015 14:05:00 |                   |
+---+---------+-------------------+-------------------+

步骤1的说明

结果是一个表，显示y和n类别的最新日期。

第2步

将以下公式添加到单元格I2中

=ArrayFormula(SUM(IF(F2:F>G2:G,1,0)))

步骤2的说明

计算每个名称，其中n个日期比y个日期新。

结果

+---+---+
|   | I |
+---+---+
| 1 |   |
+---+---+
| 2 | 5 |
+---+---+

单一配方替代

=transpose(
    QUERY(
       QUERY(
          A:C,
          "select A,max(C) where A <>'' group by A pivot B",1),
       "select count(Col1) where Col2 > Col3",1)
  )

结果

+------------+---+
| count name | 5 |
+------------+---+