评估公式的子公式 [英] Evaluate a sub-formula of a formula

问题描述

选择具有公式的单元格后，我们可以在Excel公式栏中选择其一部分公式并进行计算(按F9键).我需要通过JavaScript API重现对子公式的评估.

After selecting a cell having a formula, we could select a part of its formula in Excel formula bar and calculate it (by pressing F9). I need to reproduce this evaluation of sub-formulas by JavaScript API.

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

For example, let's assume the values of Cell A1, B1, C1 are respectively 1, 2, 3, and Cell D1 contains a formula =A1+B1+C1. I would like to be able to evaluate quickly a sub-formula such as A1+B1, B1+C1, and get the result 3 and 5.

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

In VBA, under manual calculation mode, we could store the initial formula of D1 in a variable, and then assign a sub-formula (eg, =A1+B1) to D1 to get the result 3, and then restore back the initial formula to D1 like nothing happened; this evaluation does not raise calculation of any other cells that are descendants of D1 (thanks to the manual mode).

但是，使用JavaScript API，重新计算仅在automatic模式下有效.如果我们为D1分配一个子公式(例如=A1+B1)，则所有D1子代的单元格都将由ctx.sync重新计算，这可能会花费很大.

However, with JavaScript API, the re-calculation only works under automatic mode. If we assign a sub-formula (eg, =A1+B1) to D1, all the cells that are descendants of D1 are re-calculated by ctx.sync, which may be costly.

那么有什么方法或解决方法可以优化它?

So is there a way or a workaround to optimise that?

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

One possible workaround is to find an isolated cell in the workbook that no cell depends on (eg, a cell outside usedRange of a worksheet, but we still need to make sure no cell depends on it, because of the nature of usedRange...), and then assign a sub-formula to that cell and get the value. The disadvantages of this approach are

1)仍然是黑客，并修改了工作表的区域.

1) it's still a hack, and modifies the area of a worksheet.

2)用户定义的功能(如果编程不正确)可能取决于工作表的尺寸或保存该工作表的单元格的位置.在这种情况下，在隔离的单元格中评估用户定义的功能可能会导致与原始单元格中的评估结果不同(或产生副作用).

2) a User-Defined Function (if programmed badly) may rely on the dimension of a worksheet or the position of the cell holding it. In this case, evaluating a user-defined function in an isolated cell may lead to different result (or side effects) from the evaluation in the original cell.

有人可以帮忙吗?

推荐答案

我认为您列出的第一种方法可能是最简单的.即，刷新单元格的公式并将其另存为javascript变量(字符串).接下来，在适当的位置修改公式，对值施加负载，然后将公式还原为保存字符串.这种方法的唯一真正的缺点是您弄乱了某个人的公式，这意味着，如果在步骤1和步骤2之间出了点问题，则会使文档处于脏状态.而且，正如您所说，它可能会对计算产生下游影响(在性能方面).

I think the first approach that you list is probably the easiest. Namely, reead the formula of the cell and save it as a javascript variable (string). Next modify the formula in place, issue a load on the value, and then revert the formula back to the save string. The only true disadvantage of this approach is that you're messing with someone's formula, which means that if something goes wrong between step 1 and step 2, you leave the document in a dirty state. And, as you say, it may have downstream impacts (perf-wise) on calculations.

或者，如果您实际上知道有什么公式，则可以使用工作表功能"功能.例如，这里您要计算20的总和+两个工作表范围的总和.

Alternatively, if you actually know what formulas are there, you could use the "worksheet functions" feature. For example, here you're calculating the sum of 20 + the sum of two worksheet ranges.

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);
});

这是最干净"的计算方式，因为它不会影响工作表状态，甚至可以链接计算:

This is the "cleanest" way to calculate in that it doesn't impact the worksheet state, and you can even chain the calculations:

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(就此而言，不适用于数组公式)，并且您需要先验地知道单元格包含哪种公式.但是，如果您正在谈论计算公式的子公式，那么我认为您已经在其中包含了有关公式的信息，因此后者可能不是问题.

The downside is that this won't work with UDFs (and with array formulas, for that matter), and that you sort of need to know a-priori what sort of formula the cell consists of. But if you're talking about calculating a sub-formula of the formula, I assume you already have some information on the formula within, so the latter may not be a problem.

这篇关于评估公式的子公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！