计算公式的子公式

Question

在选择有公式的单元格后，我们可以在Excel公式栏中选择它的一部分公式并计算它(通过按F9)。我需要通过JavaScript API复制子公式的评估结果。

例如，假设单元格A1、B1、C1的值分别为1、2、3和CellD1包含公式=A1+B1+C1。我希望能够快速评估一个子公式，如A1+B1、B1+C1，并得到结果3和5。

在VBA中，在manual计算模式下，我们可以将D1的初始公式存储在一个变量中，然后将一个子公式(例如，=A1+B1)分配给D1，以得到结果3，然后将初始公式恢复到D1，就像什么都没有发生一样；此评估不会增加对D1后代的任何其他单元的计算(感谢manual模式)。

然而，使用JavaScript API，重新计算只能在automatic模式下工作.如果我们将一个子公式(例如，=A1+B1)分配给D1，那么所有D1的后代细胞都会被ctx.sync重新计算，这可能会很昂贵。

那么，有什么办法或解决办法来优化这一点呢？

一个可能的解决方法是在工作簿中找到一个没有单元格依赖的独立单元(例如，工作表的usedRange之外的一个单元，但由于usedRange的性质，我们仍然需要确保没有任何单元格依赖于它)，然后给该单元格分配一个子公式并得到值。这种方法的缺点是

1)它仍然是一个黑客，并且修改工作表的区域。

2)用户定义的函数(如果编程不当)可能依赖工作表的尺寸或单元格的位置。在这种情况下，在隔离单元中评估用户定义的功能可能会导致与原始单元中的评估不同的结果(或副作用)。

有人能帮忙吗？

Answer 1

我认为你列出的第一个方法可能是最简单的。也就是说，读取单元格的公式并将其保存为javascript变量(string)。接下来，在适当的位置修改公式，对值发出一个加载，然后将公式还原回保存字符串。这种方法唯一真正的缺点是，您正在处理某人的公式，这意味着，如果在步骤1和步骤2之间出错，那么文档就会处于脏状态。而且，正如你所说，它可能会对计算产生下游影响(从纵向上看)。

或者，如果您确实知道存在哪些公式，则可以使用“工作表函数”功能。例如，这里计算的是20 +两个工作表范围的和。

Excel.run(function (ctx) {
    var result = ctx.workbook.functions.sum(
        20,
        ctx.workbook.worksheets.getItem("Sheet1").getRange("F3:F5"),
        ctx.workbook.worksheets.getItem("Sheet1").getRange("H3:H5")
    ).load();

    return ctx.sync();
}).catch(function(e) {
    console.log(e);
});

这是“最干净”的计算方法，因为它不影响工作表状态，甚至可以连锁计算：

Excel.run(function (ctx) {
    var range = ctx.workbook.worksheets.getItem("Sheet1").getRange("E2:H5");

    var sumOfTwoLookups = ctx.workbook.functions.sum(
        ctx.workbook.functions.vlookup("Wrench", range, 2, false),
        ctx.workbook.functions.vlookup("Wrench", range, 3, false)
        );
    sumOfTwoLookups.load();

    return ctx.sync();
}).catch(function(e) {
    console.log(e);
});

它的缺点是，这不适用于UDF(以及数组公式)，而且您需要事先知道单元格是由哪种公式组成的。但是如果你说的是计算公式的子公式，我假设你已经有一些关于公式的信息，所以后者可能不是一个问题。