☰

Excel，交易之间的平均日期？

tag-icon • tag-icon microsoft-excel-2010 worksheet-function
Excel，交易之间的平均日期？

我有一组正在使用的库存数据集，但在尝试弄清楚我想做的事情时遇到了障碍。

数据集有一个名称列（物品名称；例如“锤子”或“锯子”）、一个数量列（库存数量）和一个交易时间列。（还有其他列，包括细分为日、月、年、时间和星期几的交易时间。）

对于每个唯一的库存项目名称，我想找到

  • 购买购买，
  • 从购买到销售，以及
  • 售对售。

可以根据库存（“数量”）相对于同一商品的先前交易是增加还是减少来识别交易是购买还是销售。

答案1

Excel 可能不是实现此目的的最佳方法，但我做到了。以下是我编写的一些示例数据以及我的公式的结果：

                            样本数据

（上图是一张更大图片的链接，显示了隐藏的辅助列。）我假设存在一个Date具有可接受粒度级别的列。以下是公式：

AA2: =VLOOKUP($A2, $A3:$C$22, 2, FALSE)               *笔记： $A3， 不是$A2。
AB2: =$AA2-$B2
AC2: =VLOOKUP($A2, $A3:$C$22, 3, FALSE)
AD2: =IF($AB2>0, $A2,  "")
AE2: =IF($AB2>0, $AC2, "")
AF2: =IF($AB2<0, $A2,  "")
AG2: =IF($AB2<0, $AC2, "")
AH2: =IF($AB2>0, IFERROR(VLOOKUP($AD2, $AD3:$AE$22, 2, FALSE), ""), "")
AI2: =IF($AB2>0, IFERROR($AH2-$AC2,""), "")
AJ2: =IF($AB2>0, IFERROR(VLOOKUP($AD2, $AF3:$AG$22, 2, FALSE), ""), "")
AK2: =IF($AB2>0, IFERROR($AJ2-$AC2,""), "")
AL2: =IF($AB2<0, IFERROR(VLOOKUP($AF2, $AF3:$AG$22, 2, FALSE), ""), "")
AM2: =IF($AB2<0, IFERROR($AL2-$AC2,""), "")

 X2: =AVERAGEIFS($AI$2:$AI$16, $A$2:$A$16, "="&$W2, $AB$2:$AB$16, ">0")
 Y2: =AVERAGEIFS($AK$2:$AK$16, $A$2:$A$16, "="&$W2, $AB$2:$AB$16, ">0")
 Z2: =AVERAGEIFS($AM$2:$AM$16, $A$2:$A$16, "="&$W2, $AB$2:$AB$16, "<0")

我假设您有某种方法可以W用唯一的库存项目名称填充列。  22表示包含数据的最后一行。

解释：

  • 粉色列AA:AC映射到当前行列中标识的项目的下一个交易A:C。例如，AA2:AC2映射到A4:C4。该Delta列显示这两笔交易之间的库存变化。您可以直接定义AB2为=VLOOKUP($A2, $A3:$C$22, 2, FALSE) - $B2并删除列AA；我相信这种方式更清晰。
  • 橙色列 ( )根据库存增量是正数还是负数AD:AG将列中的交易对AA:AC分为购买 ( AD:AE) 和销售 ( )，并从列中复制项目名称。AF:AGA
  • 浅绿色柱 ( AH:AI) 将每次购买映射到同一商品的下一次购买，并计算两次购买之间的天数。例如， AH2是AE8（即C10），AI2是 AE8-AE2 （即C10-C4）。
  • 深绿色列（AJ:AK）将每次购买映射到同一商品的下一次销售，并计算它们之间的天数。
  • 蓝色列（AL:AM）将每次销售映射到同一商品的下一次销售，并计算它们之间的天数。

相关内容