☰

如何在 Google 表格中重复计算直至超过阈值

tag-icon • tag-icon spreadsheet google-spreadsheets
如何在 Google 表格中重复计算直至超过阈值

我有一张 Google Sheet，用于评估财务目标。这张表中有 8 个单元格。前两个单元格用于输入其他单元格中的公式所使用的参数。

第一个单元格是生成单元格 7 中结果的参数。第二个单元格是我想要达到的阈值。基本上，我想取单元格 1 中的值并将其增加 1，直到单元格 7 中看到的结果超过单元格 2 中的值。示例如下：

  A                        B
1 Current Total            $10000
2 Target Amount            $1000
3 Current Amount           =DIVIDE(B1, 100)
4 Result A                 =PRODUCT(B3, 0.35)
5 Result B                 =PRODUCT(B3, 0.50)
6 Result C                 =PRODUCT(B3, 0.15)
7 TOTAL                    =SUM(B4:B6)
8 Total Needed             ?

使用上面的例子，我想创建一个公式，将 B1 的值增加 1，直到 B7 中的值超过 B2 中的值。我想将超过阈值的金额放在单元格 B8 中。

我希望在“幕后”执行此重复操作，这样只有结果出现在单元格 B8 中。我不想显示所有“工作”。有没有办法在 Google Sheet 中执行此操作？如果有，怎么做？

答案1

有点晚了，但对相关人士来说。Total Needed将保持价值

=(B1 * (B2/B3)) - B1

我更愿意重复这个“幕后”行动，因此 结果显示在单元格 B8 中。我不想展示所有的“工作”。有没有办法在 Google Sheet 中做到这一点？如果有，怎么做？

这可以通过菜单 -> 扩展 -> Google Apps Script -> 实现添加自定义函数。但无论如何，您和任何人都不会从计算中看到任何结果，因为计算速度足够快。

变体 1 我的建议是通过计算公式来解决这个问题，计算需要加 1 的次数

B3 是当前总额的 1%。除以Target Amount by Current Amount目标金额的 1%。这是简单的数学运算。

B8 = B2/B3

将结果乘以当前总额。这将得出目标金额的 1% 在当前总额中所占的倍数

B8 = (B2/B3) * B1

接下来，从结果中减去当前总数。这是必要的，因为我们需要知道 - 在添加当前总数的值后，我们需要加 1 多少次，

直到单元格 7 中的结果与单元格 2 中的值相交

B8 = ((B2/B3) * B1) - B1

   A                  B
1 Current Total      10000
2 Target Amount       1000
3 Current Amount     = B1 / 100
4 Result A           = B3 * 0.35
5 Result B           = B3 * 0.50
6 Result C           = B3 * 0.15
7 TOTAL              = SUM(B4:B6)
8 Total Needed       = ((B2/B3) * B1) - B1

如果您遇到类似的难以解决的情况 - 尝试为其属性提供好的名称。让名称说明它们所具有的值。这将有助于思考。

排除所有已经计算的值。

Current Amount是当前总额的 100%，B3 = B1/100。因此，请考虑一下，在这种情况下，无论何时计算，此单元格都会始终保存该值 - 它将始终是=B1/100。

Results A, B and C与当前金额相同。它们也是当前总额的一部分。Result A是当前总额的 35%……其他的依此类推。它们每个都始终具有相同的值。

TOTAL始终等于当前金额，因为它总结了其中的各个部分。它也是一个硬编码值。

现在，将已知属性与其他属性区分开来。它们都代表同一个值 - 当前总计的部分 - 您不必考虑它们的计算，因为它们具有硬编码值。

   A                        B
1 Current Total                              
2 Target Amount            __________________
3 Current Amount           = B1 / 100
4 Result A                 = B3 * 0.35
5 Result B                 = B3 * 0.50
6 Result C                 = B3 * 0.15
7 TOTAL                    = SUM(B4:B6)
8 Total Needed             __________________

现在，剩下要工作的值是——当前总额和目标金额。


使用上面的例子，我想创建一个公式将 B1 的值增加 1，直到 B7 中的值超过 B2 中的值。我想将跨过门槛所需的金额放入单元格 B8 中。

变体 2 将 B1 的值增加 1，仅由您自己手动进行。

使用 C 列中的 D1 进行测试，当 C7=C2 时，结果将填充 C8，否则您必须增加或减少 D1：

   A                  B                 C                      D 
1 Current Total      10000             = B1+D1            --> 90000 <--
2 Target Amount      ____________      1000
3 Current Amount     = B1 / 100        = C1/100
4 Result A           = B3 * 0.35       = C3*0.35
5 Result B           = B3 * 0.50       = C3*0.5
6 Result C           = B3 * 0.15       = C3*0.15
7 TOTAL              = SUM(B4:B6)      = SUM(C4:C6)
8 Total Needed       ____________      = IF(C7=C2; D1; "Change D1")

相关内容