我正在尝试获取库存的当前价值。我知道库存量，并且我有一个包含所有采购订单的表格。因此，我想从最后一个采购订单开始，获取该订单的价值和金额，如果它还不是总库存，则继续获取上一个采购订单。

假设我的库存中有 7 件产品 A。我有 3 个产品 A 的采购订单：

邮局 产品 项目 价值
氧合指数1 产物A 3 15欧元
氧分压 产物A 2 16欧元
磷酸盐 产品B 3 18欧元
磷酸盐 产物A 4 16.5 欧元

因此，产品 A 的当前库存价值为： (4 x 16,5) + (2 x 16) + (1 x 15)

我尝试使用 INDEX 和 VLOOKUP，但无法让它在特定的库存数量处停止。

我正在使用 Excel 365

3

  • 2
    类似于=SUMPRODUCT((B2:B5="ProdA")*C2:C5*D2:D5)？


    – 

  • 2
    @DarrenBartrup-Cook，这似乎并不是 OP 想要的。看起来他需要从最新的采购订单 (PO4) 开始往回数，直到达到 7 件商品。因此是 4*16,5 + 2*16 和只有 1*15（而不是 3*15）。


    – 

  • @JvdV 我不确定，所以没有将其添加为答案，但是是的…..现在我已经正确查看并添加了一些内容……可以看到 OP 说库存中有 7 件物品，而表格中显示有 9 件。


    – 


最佳答案
3

另一个解决方案：

=LET(Product,F2,
 inventory,G2,
 Products,B2:B5,
 items,FILTER(C2:C5,Products=Product),
 values,FILTER(D2:D5,Products=Product),
 rows,ROWS(items),
 onHand,REDUCE(inventory,SEQUENCE(rows-1,1,rows,-1),LAMBDA(a,c,VSTACK(MAX(0,TAKE(a,1)-INDEX(items,c)),a))),
 SUMPRODUCT(values,IF(onHand>=items,items,onHand)))

编辑

必须将仅有一个产品项目的情况视为特殊情况：

=LET(Product,F2,
 inventory,G2,
 Products,B2:B5,
 items,FILTER(C2:C5,Products=Product),
 values,FILTER(D2:D5,Products=Product),
 rows,ROWS(items),
 onHand,IF(rows=1,inventory,REDUCE(inventory,SEQUENCE(rows-1,1,rows,-1),LAMBDA(a,c,VSTACK(MAX(0,TAKE(a,1)-INDEX(items,c)),a)))),
 SUMPRODUCT(values,IF(onHand>=items,items,onHand)))

笔记

希望过滤器是不言自明的。

对于 ProdA，将剩下三行。现在我们需要以相反的顺序处理这三行的项目，以查看每个阶段剩余的库存量。我选择使用 Reduce 并设置一个从最后一行倒数的计数器（与其他 lambda 辅助函数不同，Reduce 能够在每次迭代中创建一个数组）。Reduce 生成的数组以单个值 7 开始，并在每次交易后继续堆叠剩余金额。Sequence 仅生成两个值，3 和 2。在 lambda 内部，函数从 7 中取出第三项 4，剩下 3，并将其添加到堆栈中。下次它从 3 中取出第二项 2，只剩下 1，并将其添加到堆栈中。Max(0..) 可防止结果变为负数。

在 Sumproduct 中，存储在“onHand”中的堆栈中的项目乘以各自的值以得到总数。

4

  • 您的解决方案看起来不错，但是您检查过 ProdB 吗？


    – 

  • 很好发现，在这种情况下你会得到一个零行序列，所以它会失败。


    – 

  • 您编辑的解决方案非常有效。我现在正在查找所有这些函数，以弄清楚它究竟是如何工作的（这是我第一次使用 LAMBDA）。感谢您的帮助！


    – 

  • 很好！将添加一些注释来解释方法。


    – 

初步的解决方案是：

[H2] =LET(ar,SORT(FILTER(A2:D5,B2:B5=F2),1,-1),ci,G2,
  ai,TAKE(VSTACK(ci,SCAN(ci,INDEX(ar,0,3),LAMBDA(a,i,IF(a>=i,a-i,0)))),ROWS(ar)),
  am,MAP(INDEX(ar,0,3),ai,LAMBDA(x,y,MIN(x,y))),
  SUMPRODUCT(am,INDEX(ar,0,4))
)

输入在第一行：

  1. A2:D5 – 整个数据范围
  2. B2:B5 – 产品列
  3. F2-选定产品
  4. G2——可用数量（3）。

更新

改进的单循环、无过滤器配方：

=LET(ar_3,C2:C5*(B2:B5=F2),r,ROWS(B2:B5),
  DROP(REDUCE(VSTACK(G2,0),SEQUENCE(r,,0),
    LAMBDA(a,i,LET(m,MIN(INDEX(ar_3,r-i),INDEX(a,1)),
      VSTACK(INDEX(a,1)-m,m,INDEX(a,2)+INDEX(D2:D5,r-i)*m)))),1))

Current Value使用屏幕截图中所示的表格定义，在列的第一个单元格中输入以下内容tbInventory：

=LET(
    cur_value_for_prod, LAMBDA(prod, num_in_stock,
        LET(
            rev_indices, SORT(FILTER(SEQUENCE(ROWS(tbPO)), tbPO[Product] = prod), , -1),
            instock_and_value, REDUCE(
                HSTACK(num_in_stock, 0),
                rev_indices,
                LAMBDA(acc, cur,
                    LET(
                        in_stock, INDEX(acc, 1, 1),
                        value, INDEX(acc, 1, 2),
                        items_from_po, MIN(in_stock, INDEX(tbPO[Items], cur)),
                        bal_stock, IF(in_stock > 0, in_stock - items_from_po, 0),
                        HSTACK(bal_stock, value + items_from_po * INDEX(tbPO[Value], cur))
                    )
                )
            ),
            INDEX(instock_and_value, 1, 2)
        )
    ),
    cur_value_for_prod(@[Product], @[Inventory])
)