excel - 如何使用AverageIf工作表函数计算平均日期差异（如果填写日期）

Question

我正在总结任务列表和相应的日期（开始日期、第一次回答日期……）。

它看起来或多或少类似于以下内容：

Title    Start date    First answer
Task1     29/06/2018   02/07/2018
Task2     09/05/2018   
Task3     13/06/2018   14/06/2018

我想计算给出第一个答案所需的平均时间。如果尚未给出第一个答案，则在计算平均值时需要忽略此条目。

为了能够更好地理解公式，我决定为标题使用名称，例如：

Name "Header_Title"        has value "Title"
Name "Header_Start_Date"   has value "Start Date"
Name "Header_First_Answer" has value "First answer"

此外，定义为COUNTA(OFFSET(Header_Title;1;0):A1048576)具有名称的条目数：Total_Count.

接下来，我为列值的范围创建了名称：

"All_Start_Dates"    is defined as =OFFSET(Header_Start_Date;1;0):OFFSET(Header_Start_Date;Total_Count;0)
"All_First_Answered" is defined as =OFFSET(Header_First_Answer;1;0):OFFSET(Header_First_Answer;Total_Count;0)

解释：取标题下的第一个条目（列标题）并转到对应于最后一个任务的行。

这使得编写计算这些日期列之间的平均差异的公式变得非常容易：

{=AVERAGE(All_First_Answered_Dates-All_Start_Dates)}
// mind the {} for showing this is an array formula

现在的问题是：如何使用AverageIf工作表功能才能不考虑First answer未填写的情况？
我已经尝试过使用">0"and ">"&0，但这不起作用，据说这些公式无效：

{=AVERAGEIF(All_First_Answered_Dates-All_Start_Dates;">0")}
{=AVERAGEIF(All_First_Answered_Dates-All_Start_Dates;">"&0)}

有人有想法吗？
提前致谢

PS1。如您所见，我使用单元格范围A1048576作为 column 的最后一个条目A，有人知道更优雅的描述方式吗？
PS2。还有一件事可以让我的生活更轻松，那就是可以查看哪些单元格有名称（我正在考虑条件格式，但我没有找到这样做的方法）。有谁知道是否有办法突出显示与名称相关的单个单元格？

标签： excelexcel-formulaconditional-formattingarray-formulas

Answer 1

所以我建议的答案是

=AVERAGEIF(All_First_Answered,">0")-AVERAGEIF(All_First_Answered,">0",All_Start_Dates)

我在这里假设所有开始日期都存在，但第一次回答的日期可能会丢失：如果您使用 AVERAGEIFS，您可以轻松地为开始日期添加额外条件。公式的两个部分都包含相同的条件，因此它们在相同的行上工作。

上面的中间列只是为了说明而包括在内。