☰

计算唯一字段的两个日期之间的平均天数

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
计算唯一字段的两个日期之间的平均天数

我需要一些帮助，使用一个公式来查找唯一任务，然后获取两个日期之间的平均值。请看以下示例：

任务已关闭已创建
任务1 2018/3/13 2018/1/1
任务1 2018/3/13 2018/1/1
任务1 2018/3/13 2018/1/1
任务2 2018/3/13 2018/2/1 任务2 2018/3/13
2018/2/1
任务3 2018/3/13 2018/3/1

我需要执行任务 1，然后从 2018 年 1 月 1 日减去 2018 年
3 月 13 日，得到 71 我需要执行任务 2，然后从 2018 年 2 月 1 日减去 2018 年 3 月 13 日，得到 40
我需要执行任务 3，然后从 2018 年 3 月 1 日减去 2018 年 3 月 13 日，得到 12
这样我得到的平均答案是 41

有没有公​​式可以用来得到这个答案 41？
我试过 {=AVERAGE(B2:B6 - C2:C7)}，但我不知道如何让它先检查是否唯一。类似于 {=如果 A2:A7 是唯一的，AVERAGE(B2:B6 - C2:C7)}

更复杂的是，有时 B 列中没有任何值 (Closed)

答案1

您可以在相邻列中使用此公式来计算每个任务的发生次数：

=COUNTIF(A$1:A1,A1)

您需要将其放入第一行并拖动以填充到列的下方。这样可以使其正确转置以产生所需的结果。该公式计算到当前行为止有多少行包含相同的值：

Task1   1
Task1   2   
Task2   1
Task3   1
Task1   3   
Task4   1
Task2   2   
Task3   2

然后，在新列中使用另一个公式来捕获日期差异，但仅当出现次数等于 1 时才这样做：

=IF(D1=1,B1-C1,"")

Task1   1/01/2011   1/01/2010   1   365
Task1   1/01/2011   1/01/2010   2   
Task2   4/01/2011   1/04/2010   1   278
Task3   6/01/2011   1/06/2010   1   219
Task1   1/01/2011   1/01/2010   3   
Task4   10/01/2011  1/10/2010   1   101
Task2   4/01/2011   1/04/2010   2   
Task3   6/01/2011   1/06/2010   2

然后取最后一列的平均值。

您当然可以结合上述两个公式 - 我只是为了解释而提供这两个公式。

请注意，日期不会影响出现次数 - 只有第一次出现才会有日期计算（因此这假设您不允许不同行使用不同的日期范围，否则这将不起作用 - 您的示例显示重复的日期范围）。

您需要决定如何处理空的 B 列。例如，您可以忽略日期差异公式中未关闭的任务，如下所示：

=IF(AND(NOT(ISBLANK(B4)),D4=1),B4-C4,"")

相关内容