☰

Excel：如何更改某个值，然后根据该新值进行更新，而无需循环引用

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2007
Excel：如何更改某个值，然后根据该新值进行更新，而无需循环引用

一个基本的库存管理问题（我相信）

简单来说：

IF(A<=0,A=20,)

当然，它不起作用，因为当你添加 20 时，“A”就变成正数。Excel 重新计算并删除 20。Excel 重新计算并添加 20，等等......

我们需要一种方法来使 A=+20 ....然后查看 B、C、D 以了解下一个负面条件。

几乎所有商业模式都必须应对库存补充。......但是这是如何实现的呢？

以下是 Google 表格的链接： https://docs.google.com/spreadsheets/d/121LsvpK3GrVHVAgIK7npTX3-N9P5LOZZLZIenqeEj9s/edit?usp=sharing

下面只是以文字形式列出了上述电子表格。还有其他变量需要考虑，但首先需要回答根本问题

以下是基本库存问题的解释：

我们有销售、库存余额和交货要求（以配合缺货）。

  • 起始库存已输入。
  • 已输入每周预计的销售额。
  • 某一周，所有库存均已售出。
  • 那一周必须进行库存交付。

手动处理起来非常容易……我们查找预计的负（或零）库存余额，然后点击键盘并输入所需的库存交货。我们滚动查看库存水平——找到下周需要库存的地方，然后输入交货要求。

美好的。

只是如果预计销售量发生变化，所有交货日期也必须改变。

这意味着，再次手动更改第一个负库存水平，并再次寻找下一个负库存水平等。

然而... 财务模型需要发挥作用，只需改变预计销售额即可。

加载销售预测，找到没有库存的一周，添加发货......然后找到下一周没有库存的一周。

唯一的人为干预就是改变销售数据。

这是一个非常简单的版本：

         A               B             C          D          E          F          G          H          I
  +---------------+---------------+----------+----------+----------+----------+----------+----------+----------+
1 |  01/03/2017   |     Date      | 05/03/17 | 12/03/17 | 19/03/17 | 26/03/17 | 02/04/17 | 09/04/17 | 16/04/17 |
  +---------------+---------------+----------+----------+----------+----------+----------+----------+----------+
2 | Stock         | Delivery      |        1 |          |          |          |          |          |          |
3 |               | Consignment   |       37 |        0 |        0 |        0 |        0 |        0 |        0 |
4 |               | Stock Balance |       22 |       11 |       -1 |       -1 |       -1 |       -1 |       -1 |
5 |               | Sales         |       15 |       11 |       12 |          |          |          |          |
6 | Order Qty Ref | Item qty      |       37 |       37 |       37 |       37 |       37 |       37 |       37 |
  +---------------+---------------+----------+----------+----------+----------+----------+----------+----------+

C2:I2= 1 至 7 之间的数字

C3:I3= 监控C2:I21 至 7 的数字，然后搜索“订单数量参考”范围（第 6 行）并发布“项目数量”，例如 37，即，如果交货 = 1.....37 就是货物。

= 如果 (C2=1,$C$6, 如果 (C2=2,$D$6, 如果 (C2=3,$E$6, 如果 (C2=4,$F$6, 如果 (C2=5,$G$6, 如果 (C2=6,$H$6, 如果 (C2=7,$I$6, )))))))

C4= C3-C5.....这只是打开库存余额 - 寄售销售。

D4:I4= C4+D3-D5..... 每个单元格将之前的库存余额添加到任何新的货物中，并减去任何销售额。

C5:I5= 代表销售单位数量的数字（在模型中，此销售数字范围通过下拉列表选择）。

C6:I6= 单独的 7 个潜在库存交付范围。

模型起始位置已加载销售预测，以及第一批货物（1）。

我们将手动查找第一个 0 或负库存余额（第 4 行）——我们在列中找到第一个 E，然后在第 2 行（即在单元格中添加 1（或 1 至 7 之间的数字）  E2。

+---------------+---------------+----------+----------+----------+----------+----------+----------+----------+
|  01/03/2017   |     Date      | 05/03/17 | 12/03/17 | 19/03/17 | 26/03/17 | 02/04/17 | 09/04/17 | 16/04/17 |
+---------------+---------------+----------+----------+----------+----------+----------+----------+----------+
| Stock         | Delivery      |        1 |          |    →   1 |          |          |          |          |
|               | Consignment   |       37 |        0 |       37 |        0 |        0 |        0 |        0 |
|               | Stock Balance |       22 |       11 |       35 |       20 |        9 |       -4 |      -19 |
|               | Sales         |       15 |       11 |       13 |       15 |       11 |       13 |       15 |
| Order Qty Ref | Item qty      |       37 |       74 |       37 |       37 |       37 |       37 |       37 |
+---------------+---------------+----------+----------+----------+----------+----------+----------+----------+

然后，我们手动查找下一个零或负库存余额（在单元格中找到 H4），并再添加 1（在单元格中 H2）。

+---------------+---------------+----------+----------+----------+----------+----------+----------+----------+
|  01/03/2017   |     Date      | 05/03/17 | 12/03/17 | 19/03/17 | 26/03/17 | 02/04/17 | 09/04/17 | 16/04/17 |
+---------------+---------------+----------+----------+----------+----------+----------+----------+----------+
| Stock         | Delivery      |        1 |          |        1 |          |          |    →   1 |          |
|               | Consignment   |       37 |        0 |       37 |        0 |        0 |       37 |        0 |
|               | Stock Balance |       22 |       11 |       35 |       20 |        9 |       33 |       18 |
|               | Sales         |       15 |       11 |       13 |       15 |       11 |       13 |       15 |
| Order Qty Ref | Item qty      |       37 |       37 |       37 |       37 |       37 |       37 |       37 |
+---------------+---------------+----------+----------+----------+----------+----------+----------+----------+

好的，很简单 - 简单的计算是：IF (Stock_balance<=0,Delivery=1,)

问题是，一旦库存余额被调整为正数……Excel 就会删除 1……使库存余额变为负数——无限循环（循环引用错误）。

显然，我们需要 Excel 从左到右计算每个单元格，直到满足零或负数条件。输入股票交割...然后从第一个单元格重新开始计算，直到满足零或负数条件。

实际上，我们需要 Excel 来模仿人类干预。

例如，

IF(Stock_balance<=0,Delivery=1[stop_recalculate_until_no_negatives_are_found],)

之前已经有人做过了，但是我搜索不到答案。因此我认为这个问题非常适合超级用户。

答案1

首先，我可以为第 3 行提供更简洁的公式形式，并为第 4 行提供另一种形式（不需要C4特殊情况）：

  • 第 3 行 →=IF(C$2=0, 0, INDEX($C$6, 1, C$2))
    这很简单 — 如果C2为 0，C3则为零。（请记住，空白测试等于零。）否则，使用从$C$6、$D$6、$E$6、 … 中索引的数字C2。
  • 第 4 行 →=SUM($C3:C3)-SUM($C5:C5)
    在单元格C4，这会减少到C3-C5。当您将其拖拽/填充到右侧时，每个范围内的第一个单元格将固定在 Column 中， C 而第二个单元格则会自动增加。
    因此D4是=SUM(C3:D3)-SUM(C5:D5)，E4是=SUM(C3:E3)-SUM(C5:E5)，等等。

问题是你想根据第 4 行的值来选择第 2 行的值 申请前将第 2 行和第 3 行的值添加到当前列的第 4 行的值 — 因此您得到了一个循环引用。解决方案是建立一个“辅助行”，精确计算这个幻像值（在应用第 2 行和第 3 行的值之前的第 4 行的值）。例如，我将其放入第 9 行†：

  • C9→=-C5
  • D9→ =SUM($C3:C3)-SUM($C5:D5) （并将其拖拽/填充至右侧）。

您可以看到，在添加第 3 行的值之前，这等于第 4 行的值。然后只需将第 2 行设置为=IF(C9<=0, 1, 0) （这是您已有的公式）并将单元格格式化为不显示零即可。（或者，如果您愿意，也可以创建公式=IF(C9<=0, 1, "")。）现在，您可以更改预计销售额（第 5 行），第 2 行和第 3 行的交货时间表将自动更新。

电子表格

我更改了日期的格式，因为我将日期写为 mm/dd/yy（是的，我知道这不合理，但这是文化问题），而格式为 dd/mm/yy 的日期让我的眼睛很不舒服。________
†
当然，您可以将辅助行放在任何您想要的位置，例如第 99 行，或放在另一张工作表上，一旦一切正常，您就可以将其隐藏。或者我们可以完全删除辅助行，但代价是使第 2 行公式变得更加复杂。

相关内容