有没有一种方法可以评估存储在单元格中的公式？

Question

在Google Docs电子表格中，我正在寻找类似于=EVAL(A1)的东西，其中A1设置为"=1+2"。

我发现在MS Excel中有一个EVALUATE()函数（似乎有点棘手），但我在Google Docs中找不到类似的东西。

我还通过功能列表进行了搜索，但没有找到任何有用的信息...

- Michael Krupp

---

setFormula() 和可能的 onEdit 触发器 - Robin Gertenbach

你的连接函数长什么样子？- 你可以完全不需要脚本编写来实现所有这些 - 你可以动态地将函数的各个部分拼接在一起。 - Aurielle Perlmann

@Aurielle：感谢回复。我已经成功地动态拼接了这些部分。我需要帮助编写脚本以执行它。这是我的连接方式：=CONCATENATE("=GoogleFinance(""",A10,""",""","price"",date(",E3,"),date(",E6,")")通过一些基本的脚本，我能够将公式粘贴为文本字符串到其中一个单元格中。唯一缺少的部分是执行。按两次“Enter”应该可以解决问题，但我也愿意尝试其他替代方案。 - Mirza Zia

@RobinGertenbach：setFormula + onEdit 绝对可行。我正在尝试弄清楚如何在每次更改货币时将更新的字符串传递到编辑器中。setFormula 不是静态的吗？ - Mirza Zia

Answer 1

不，Google Sheets中没有类似于Excel的EVALUATE()函数。

这个问题背后有悠久的历史，例如这篇旧帖子所述。

如果您只对简单的数学运算（如您的问题所示）感兴趣，那么可以使用自定义函数轻松完成。

function doMath( formula ) {
  // Strip leading "=" if there
  if (formula.charAt(0) === '=') formula = formula.substring(1);
  return eval(formula)
}

例如，使用您的A1，在另一个单元格中放置=doMath(A1)，它将是3。

- Mogsdad

---

可惜这不仅仅是简单的数学。但也许我可以通过一个自定义函数（就像你发布的那个）加上INDIRECT()来解决这个问题。 - Michael Krupp

@keks - 你可能会发现，你可以将这个库适配到Apps Script中。我本来以为会有很多JavaScript的库，但是没有找到。 - Mogsdad

Answer 2

我知道这是一篇旧帖子，但我很好奇为什么没有人提出以下建议：

myCell.getValue();

这将给出您的单元格中公式的结果（在您的例子中为3）。

如果您想将结果写入单元格（而不是公式），您可以使用以下代码：

function fixFormula(myCell) {
    myCell.setValue(myCell.getValue());
}

- am2124429

---

如果您已经将公式写入单元格（并刷新了工作表），那么这是有效的，但它不允许您在此之前找出公式的值将是什么。 - Collin Green

Answer 3

简短回答

如先前所述，Google Sheets没有内置的EVALUATE函数，但可以通过扩展Google Sheets来添加此功能。幸运的是，一些SocialCalc文件可用于简化此过程。

脚本

在Google电子表格上，我分享了我的进展情况。目前，我添加了我认为所需的SocialCalc文件、几个函数和多个测试用例。

注意事项:

1. Google Sheets特定的函数，如FILTER、UNIQUE等，在SocialCalc中不可用，还有一些其他函数，如SIGN。
2. 我认为SocialCalc文件应该被https://github.com/marcelklehr/socialcalc上的文件替换，因为它看起来最近已经更新。感谢eddyparkinson（请参见https://dev59.com/x2Qo5IYBdhLWcg3wI8VV#16329364）

用途

链接文件上的EVALUATE函数可以用作自定义函数。

示例1

A1：'=1+2（请注意使用撇号使公式被Google Sheets视为字符串）。

B1公式：

=EVALUATE(A1)

B1 显示值：

3

示例2

要“计算”类似于=VLOOKUP(2,A1:B3,2)的公式，此时我们需要使用“高级”参数。请参见以下示例：

B1：'=VLOOKUP(2,A1:B3,2)

C1公式：

=EVALUATE(B1,"data","A1:B3")

C1显示值：

B

Code.gs

/**
 *
 * Evaluates a string formula
 *
 * @param {"=1+1"}  formula   Formula string 
 * @param {"Tests"} sheetName Target sheet. 
 * @param {"A1"}    coord     Target cell. 
 * 
 * @customfunction
 *
 */
function EVALUATE(formula,sheetName,coord){
  // SocialCalc Sheet object
  var scSheet = new SocialCalc.Sheet();
  if(sheetName && coord){
    // Pass values from a Google sheet to a SocialCalc sheet
    GS_TO_SC(scSheet,coord,sheetName);
  }
  var parseinfo = SocialCalc.Formula.ParseFormulaIntoTokens(formula.substring(1));
  var value = SocialCalc.Formula.evaluate_parsed_formula(parseinfo,scSheet,1); // parse formula, allowing range return
  if(value.type != 'e'){
    return value.value;
  } else {
    return value.error;
  }
}
/**
 *
 * Pass the Google spreadsheet values of the specified range 
 * to a SocialCalc sheet
 *
 * See Cell Class on socialcalc-3 for details
 *
 */
function GS_TO_SC(scSheet,coord,sheetName){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  if(sheetName){
    var sheet = ss.getSheetByName(sheetName);
    var range = sheet.getRange(coord);
    } else {
      var range = ss.getRange(coord);
  }
  var rows = range.getNumRows();
  var columns = range.getNumColumns();
  var cell,A1Notation,dtype,value,vtype;
  // Double loop to pass cells in range to SocialCalc sheet
  for(var row = 1; row <= rows; row++){
    for(var column = 1; column <= columns; column++){
      cell = range.getCell(row,column);
      A1Notation = cell.getA1Notation();
      value = cell.getValue();
      if(cell.isBlank()){
        dtype = 'b';
        vtype = 'b';
      } else {
        switch(typeof value){
          case 'string':
            dtype = 't';
            vtype = 't';
            break;
          case 'date':
          case 'number':
            dtype = 'v'
            vtype = 'n';
            break;
        }
      }
      scSheet.cells[A1Notation] = {
        datavalue: value,
        datatype: dtype,
        valuetype: vtype
      }
    }
  }
}

formula1.gs

https://github.com/DanBricklin/socialcalc/blob/master/formula1.js

socialcalcconstants.gs

https://github.com/DanBricklin/socialcalc/blob/master/socialcalcconstants.js

socialcalc-3.gs

https://github.com/DanBricklin/socialcalc/blob/master/socialcalc-3.js

- Rubén

Answer 4

如果你想计算简单的数学表达式（例如A1: "(1+2)*9/3"），可以使用查询语句:

=query(,"Select "&A1&" label "&A1&" ''",0)

查询语句中的基本数学计算会由query进行评估。

- TheMaster

---

我有一些带 '=+1+1+1' 的单元格，加上 Replace(A1; 1; 1; "") 可以得出总数 - 谢谢！ - Sandra

非常好的答案-谢谢！这个方法是否也可以用于计算这些表达式中的最大值，例如使用“select max（xk）label”＆A1：A9＆“xk”？还是=max（A1：A9，“select（...）...”）？我没有成功地让它们工作.. :-( [如果您有一个好的文档页面，那也可能会有所帮助...] - Max

@Max 如果没有数据结构，很难确定你想要做什么。但看起来你可能需要使用 BYROW。 - TheMaster

我是说，如果我有一个包含像1+2或3/4这样的表达式的单元格数组，我是否可以使用这种query(...)来查找这些单元格的最小（数值）值？实际上，我使用了您的想法来实现矩阵计算[具体来说是高斯消元]，结果以分数形式给出（例如，“1/6”而不是0.1666667）。但在某些情况下，我想找到具有该形式条目的行/列中的最大元素。 - Max

嗯，我的意思是，如果我有一个包含像1+2或3/4这样的表达式的单元格数组，我能否使用这种query(...)来找到这些单元格的最小（数值）值？实际上，我使用了您的想法来实现矩阵计算[具体来说是高斯消元]，并将结果以分数形式给出（例如"1/6"而不是0.1666667）。但在某些情况下，我希望找到行/列中具有这种形式条目的最大元素。 - Max

@Max 是的。你可以使用 REDUCE: =REDUCE(2^99,A1:B2,LAMBDA(a,c,MIN(a, query(,"Select "&c&" label "&c&" ''",0) ))) 这将计算 A1:B2 中的最小值。 - TheMaster

Answer 5

在评估类似以下函数时：

"=GoogleFinance("usdeur","price",date(2013,12,1),date(2013,12,16))"

这可以通过直接引用其他单元格来完成，而无需进行计算，如下所示：

=GoogleFinance(A10,"price",E3,E6)

- Aurielle Perlmann

Answer 6

复制并粘贴公式：

也许你可以从“jQuery.sheet”中复制并粘贴所需的公式。已移至：

https://github.com/Spreadsheets/WickedGrid

看起来都是“开源”的。

不会修复问题

此外：“启用脚本使用标准电子表格函数”问题被标记为“不会修复”，请参见https://code.google.com/p/google-apps-script-issues/issues/detail?id=26

Ethercalc 有一款名为Ethercalc的类似谷歌的开源电子表格

GUI代码： https://github.com/audreyt/ethercalc

公式：https://github.com/marcelklehr/socialcalc

演示 - 在sandstorm上： https://apps.sandstorm.io/app/a0n6hwm32zjsrzes8gnjg734dh6jwt7x83xdgytspe761pe2asw0

- eddyparkinson

Answer 7

在Google电子表格中评估公式的简单技巧：

1. 选择带有公式的单元格或列
2. 进入“编辑” -> “查找和替换…”
3. 勾选“同时搜索公式”
4. 将“=”替换为“==”
5. 再将“==”替换回“=”
6. 在同一“查找和替换”窗口中取消勾选“同时搜索公式”

公式将得到评估！:)

- ksuhaster

Answer 8

感谢您的user3626588's 解决方法，它确实有效。根据您的说明，它似乎可以进一步简化。

1. 在单元格B1中输入以下内容：="=sum(A1:A5)"
2. 在单元格C1中设置数据验证，并选择下拉选项中的B1。

现在选择C1并从下拉列表中选择公式，它将自动对A1到A5之间的任何值求和。

我有一个表格，我正在为多个值创建一个复杂的公式，这个过程很有用！

再次感谢您，因为我试图避免使用脚本，因为我的工作表上有另一个程序提取的数据。在这些情况下，脚本功能并不总是自动运行。

- Adrian Castaneda

Answer 9

这里有一个技巧。在所需单元格中插入公式，然后获取该单元格的值并用此新值替换已插入的公式。

function calculateFormula(row, col){
    var spreadsheet = SpreadsheetApp.getActive();
    var sheet = spreadsheet.getSheetByName("Sheet Name");
    sheet.getRange(row,col).setValue("=sum(D6,C12:C14)");
    sheetData = sheet.getDataRange().getValues();
    var newValue = sheetData[row-1][col-1];
    sheet.getRange(row,col).setValue(newValue);
}

- Muhammad Saleem

Answer 10

如何仅转换未以“+”开头的表达式列？

92/120 67/85

等等。

- bbneo