从另一个单元格调用公式，而不是值，并在新单元格中执行公式。

Question

我正在尝试找到一种方法，将B3中使用的公式（目前是一个简单的AVERAGE公式）调用到G3中并在那里使用它。绿色单元格是用于测试的静态值。如果我在G3中输入“= B3”，我将得到来自B3的值，这不是我想要的。我想要F3和H3的平均值。基本上像一个长范围的可更新的复制/粘贴或FormulaArray。如果有道理的话。由于我将在其中实施此操作的工作表中将具有许多公式实例，因此我希望能够在一个位置更改它们，并自动进行更新，而无需复制/粘贴。我还将从另一个文档中调用公式。因此，我希望找到一种方法，可以将B3更改为MULTIPLY公式，并自动将该更改应用于G列中的单元格，当然涉及相对单元格。这是否可能？ 我找到了一个类似这个问题的问题： 如何从单元格中获取公式而不是值？ 但在这两个可行的解决方案中，Ruben的CELLFORMULA设法将函数作为字符串（就像在G5中）引入，这很接近！而Luiz的对我根本不起作用，尽管他似乎正在尝试做我想要的事情。我试着摆弄它，但无论我做什么都无法使它工作。其他建议根本没有起作用。

这是我制作的一个测试表格，以说明我要找的东西： Call Formula Test Sheet 另外：

正如我所说，Ruben的代码完美地实现了它的预期目的，而Luiz则表示他进行了一些自己的调整，以便在新单元格中执行公式。但是复制和粘贴Luiz的代码只会产生错误，而我试图自己调整它是徒劳的，因为我最近才开始涉足脚本和编码。我只是尽力比较和对比Ruben的原始代码与Luiz修改后的代码，并发现可能是错误的任何差异。类似这样的东西：

function CELLFORMULA(reference) {
  var ss = SpreadsheetApp;
  var sheet = ss.getActiveSheet();
  var formula = ss.getActiveRange().getFormula();
  var re = /cellformula\((.*)\);/g;
  var args = re.exec(formula);
  try {
    var range = sheet.getRange(args[1]);
  }
  catch(e) {
    throw new Error(args[1] + ' is not a valid range');
  }
  return range.getFormula();
}

似乎卢伊斯没有像鲁本那样调用变量？我不确定。但两个代码之间的根本区别似乎是：

var args = formula.match(/=\w+\((.*)\)/i);

在Ruben的版本中与Luiz的（未更改）中发现的内容：

 re = /cellformula\((.*)\);/g;
  args = re.exec(formula);

在我看来，re.exec(formula) 是 Luiz 的代码片段，应该会执行公式而不仅仅是将其作为字符串放入单元格，但我可能错了。

正如我所说，我对此很陌生，“(/=\w+((.))/i)” 和 “/cellformula((.));/g” 部分完全超出了我的理解范围。我尝试搜索这个语法以更好地理解它，但没有成功。

- Filteredgenius

---

欢迎。请添加您的代码尝试将参考答案适应于您的特定目标。 - Rubén

Answer 1

正如我所说，我对此还很陌生，“(/=\w+((.))/i)”和“/cellformula((.));/g”这两部分完全超出了我的理解范围。我试图搜索这些语法以更好地理解它们，但没有成功。

它们是正则表达式。

---

为了能够用另一个公式替换单元格公式，不能使用自定义函数，但可以通过可安装的编辑函数来实现。

为简单起见，以下代码将非常具体：如果B3上的公式发生更改，则G7上的公式将被更新。

function updateFormula(e) {
  var formula = e.range.getFormulaR1C1();
  if(e.range.rowStart == 3 && e.range.columnStart == 2 && formula != ""){
    var sheet = e.range.getSheet();
    var target = sheet.getRange('G7');
    target.setFormulaR1C1(formula);
  }
}

请记得创建一个可安装的编辑触发器，调用上述函数。如果您想从脚本编辑器测试它，请查看如何在GAS中测试触发器函数？

- Rubén