我需要一些帮助,以便处理我将使用 Excel 处理的场景。任何帮助都将不胜感激。
我将拥有越来越多的特定项目的日期范围,我需要制作一个 Excel 表来确定每个项目之间的平均天数。基本上,这是我计划如何将数据制成表格的简化示例:
Item Code | Date
A.ITEM | January 15, 2017
B.ITEM | January 16, 2017
A.ITEM | January 22, 2017
C.ITEM | January 25, 2017
A.ITEM | January 31, 2017
C.ITEM | February 2, 2017
B.ITEM | February 12, 2017
B.ITEM | February 24, 2017
C.ITEM | March 7, 2017
然后,我将创建另一个表格,显示每个项目日期之间的平均持续时间。我想它会看起来像这样:
Item Code | Average Life Span
A.ITEM | 9 days
B.ITEM | 20.5 days
C.ITEM | 21.5 days
我需要什么公式才能使第二个表格成为可能?我已经绞尽脑汁一段时间了,而且由于我对 Excel 中的日期函数不太熟悉,所以我仍然不知道该怎么做。这可能吗?
谢谢你!
答案1
请注意,差异的平均值只是 (max-min)/count:(d1-d2) + (d2-d3) + (d3-d4) +... = d1-dn
这样,你就可以使用如下公式
(MAX(d1:dn)-MIN(d1:dn))/COUNT(d1:dn)
不过,这会将所有日期放在一个容器中,因此您需要另外按代码进行过滤 - 而不是简单地MAX(d1:dn)
使用MAX(IF(a1:an=code,d1:dn,0)
矩阵公式。为 MIN 和 COUNT 添加类似的 if(或使用 COUNTIF);请注意,对于 MIN,else 值不能为 0,而必须为非常大的值。
答案2
在这个例子中,商品代码在 A 列,日期在 B 列,然后我添加新数据。第 1 行是标题。
首先,每次添加新行时都需要对数据进行排序,采用两层排序:
- 按商品代码排序
- 按日期排序的第二个
然后,在日期右侧的列中添加时间间隔计算并向下拖动/填充:
=IF(A2=A1,B2-B1,"")
然后计算平均时间。在其他地方列出项目代码 A、B、C - 我将它们放在测试的 F 列中。在 A 旁边,输入并向下拖动/填充:
=AVERAGEIF($A$2:$A$6491,F2,$C$2:$C$6491)
我有:
- 答:8
- 乙:19.5
- 碳:20.5
(A:1 月 22 日至 1 月 15 日为 7 天,1 月 31 日至 1 月 22 日为 9 天,平均为 8 天)