我有一张 Excel 表格，其中包含我的团队所处理问题的统计数据：
|Date opened|Date closed|Description|...|
|-----------|-----------|-----------|---|
|2020-03-05 |2020-04-23 | foo |...|
|2020-04-30 | | bar |...|
我希望有一个仪表板，在每个月末报告已打开问题的平均“年龄”。
我正在尝试构建的数据透视表截至今天（5 月 27 日）的内容如下：
March 31st - 26d
April 30th - 0d
May 27th - 28d
我一直在摆弄数据透视表向导，但我无法完成它，并且我必须使用错误的关键字来描述我正在尝试做的事情，因为谷歌在这一点上不是我的朋友。
答案1
我将源数据转换为表格（Ctrl+T），然后将表重命名为case_data。
然后我添加了一个名为“营业日”的列，使用以下公式：
=DAYS(IF([@[Date closed]]<>"",[@[Date closed]],TODAY()),[@[Date opened]])
如果您希望工作日开放，请使用网络日并考虑将假期列表传递到该函数中。
做完这些之后，我意识到我没有必要这么做，但是我还是放弃了，因为，为什么不呢？
为了获取任意一天的未结案件数量以及未结案件总数（如果案件期限重叠），我创建了一个新表，一年中的每一天占一行：
如果您感兴趣，可以使用 SEQUENCE 函数获取两个日期之间的日期列表。这对于调整闰年很有用。这是单元格 G2 中的公式：
=DATE($F$2,1,SEQUENCE(DAYS(DATE($F$2,12,31),DATE($F$2,1,1))+1))
所讨论的年份位于单元格 F2 中。
要计算任何一天的未结案件总数，我们只需检查相关日期是否在开庭日期和结案日期之间。为此，我们可以测试日期是否大于开庭日期，如果是则返回 1，如果不是则返回 0（使用 -- 运算符），然后执行相同操作以检查日期是否小于或等于结案日期（如果案件仍处于开庭状态，则为今天），然后对这两个数组的乘积求和。
换句话说，如果日期晚于开庭日期且小于或等于闭庭日期（或仍然开庭时为今天），则返回 1。对所有案件求和，以计算当天有多少案件开庭。
=SUMPRODUCT(--(G2>case_data[Date opened]),--(G2<=IF(case_data[Date closed]="",TODAY(),case_data[Date closed])))
最后，要计算案件审理天数，请使用以下公式：
=IF(H2=0,0,I1+H2)
也就是说，如果今天的未结案件值为零，则未结天数也应为零。如果不为零，则未结天数应为今天的未结案件数加上前一天未结天数值之和。
因此，第一个案件开启后的第一天（即 3 月 6 日），未结案件数为 1，未结天数为 1。在开启日期和关闭日期之间的所有天数中，未结案件数为 1，未结天数不断迭代，直到未结案件数再次为零。即：
然后：
回顾您的问题，如果我们根据您指定的日期过滤表格：
4 月 30 日至 5 月 27 日之间应该开放 27 天，而不是 28 天（假设您不将开放日期算作第一天）。
要获取任何给定日期的平均开庭天数，我们需要考虑同一天有多个案件开庭的情况。因此，我添加了一些虚拟数据：
您可以看到，三月份“未结案件”栏发生了变化：
我使用以下公式添加了“平均营业天数”：
=IFERROR(I2/H2,0)
3 月 6 日，有 1 起案件未结。因此未结天数为 1。3 月 8 日，有 2 起案件未结。其中一个案件已结案 3 天，另一个案件已结案 1 天。因此，3 月 8 日的总未结天数为 4，平均未结天数为 4 / 2 = 2。
答案2
您可以通过以下步骤实现此目的，
在源数据中创建一个新列，如开放问题年限（天数）
我已经将数据源创建为包含一些随机日期值的表格，并计算了如下所示的“未解决问题的年限（天）”，
您可以看到，对于年龄计算，我使用今天的日期作为参考。
然后添加数据透视表 （在我的示例中，我将其添加到了新的工作表中）
在“字段列表”窗格中，请将数据源字段（通过选中项目或通过拖放）添加到数据透视表的指定“区域”下方，
字段列表项 > 区域
i. 已打开 > ROWS
二、 （拖放）已打开 > 值 （我们将用它来计算未解决问题的数量）
iii. 关闭 > 过滤器
iv. 未解决问题的年限（天） > 价值
最初，数据透视表看起来如下所示，
在过滤字段中关闭（第 1 行），选中“选择多个项目”，取消选中“全部”，最后选中（空白的）
现在您的数据透视表已设置为过滤并仅显示开放式问题。
在行标签列中列出已打开日期数据已经为我分组月份而不是单独的日期
如果它为你显示单独的日期，那么
右键单击“行标签”列中的一个单元格>团体>选择‘月份’
您可以通过依次单击其他项目来取消选择它们
当您的数据源包含多年的日期时，您还可以选择按“年份”分组
接下来，右键单击“未结问题年限总和（天数）”列中的一个单元格>值字段设置>在“汇总值字段依据”部分下选择平均的
接下来，您可以调整结果平均值的小数位数。点击“数字格式”按钮> 类别：数字>根据需要调整小数位数
执行完步骤 4 到步骤 7 后，你应该看到如下所示的数据透视表，
希望这可以帮助！