arrays - 跨行求和并计算小于 0 的行数

Question

我正在尝试确定每个商店的库存短缺情况以及我供应的面包类型。

示例表显示了商店对每种面包的需求。

第 8 行是我试图用公式实现的目标。
根据现有数量，我可以有条件地格式化以根据短缺情况将单元格突出显示为红色或绿色。
我无法得到计算红细胞数量的公式。

我想我需要使用 sumproduct。

=SUMPRODUCT(--($B9:$B11<C9:C11))

以上内容适用于识别商店 A(C 列)的短缺情况。然而，当拖到 D 列和 E 列时，它不记得其他商店需要什么。

如果不添加范围内的所有行，我就无法对行进行求和。我需要将每一行与现有数量进行单独比较。我假设需要使用数组。

Answer 1

这就是我提到的公式。它使用 mmult 的标准方法来获取矩阵的行总计，然后将它们与可用数量进行比较:

=SUM(--($B9:$B11<MMULT($C9:C11,TRANSPOSE(COLUMN($C9:C11)^0))))

进入C8并靠岸。必须使用 CtrlShiftEnter

作为数组公式输入

编辑

OP 评论说，如果商店不需要特定商品，即使该商品的库存已售完，也不应将其列为短缺。

因此，只有当前列在特定行中的数字 >0 并且行总和大于可用数量时，才应将其注册为短缺:

=SUM((C9:C11>0)*($B9:$B11<MMULT($C9:C11,TRANSPOSE(COLUMN($C9:C11)^0))))

如果您只想选择某些行，它看起来像

=SUM(ISNUMBER(MATCH($A9:$A11,{"Wheat","Rye"},0))*(C9:C11>0)*($B9:$B11<MMULT($C9:C11,TRANSPOSE(COLUMN($C9:C11)^0))))