如何在另一个单元格中使用写为字符串的公式[评估 Google 电子表格] [英] How to use a formula written as a string in another cell [evaluate for Google Spreadsheet]

问题描述

我阅读了几篇关于 Google 电子表格缺少评估功能的旧帖子.2016年有什么解决办法吗?

最简单的例子.

• 'A1' 包含以下字符串:UNIQUE(C1:C5)
• 'B1' 我想用它来评估用 'A1' 编写的 独特 公式.

我尝试以这种方式连接:包含 ="&A1 的 'B1' 但结果是字符串 =UNIQUE(C1:C5).我也试过间接公式.

有什么建议可以打破最后的希望吗?

补充说明

目的是在电子表格中编写公式，并在其他几个电子表格中使用这些公式.因此，任何更改都必须在一个地方完成.

解决方案

简短回答

使用包含诸如 var formula = origin.getValue() 之类的脚本来获取字符串，并使用诸如 destination.setFormula(formula) 之类的脚本来返回公式.

说明

正如 OP 已经提到的，Google Sheets 没有 EVALUATE() 内置函数.不能使用自定义函数，因为自定义函数只能返回一个或多个值，而不能修改其他单元格属性.

由自定义菜单、事件或 Google Apps 脚本编辑器触发的脚本可用于更新指定单元格的公式.

由于公式将作为字符串保存，因此将它们保存在脚本中比保存在电子表格中更容易.

示例

以下是一个非常简单的脚本，用于将指定的公式添加到活动范围.

<前>函数添加公式(){var 公式 = '=UNIQUE(C1:C5)';var range = SpreadsheetApp.getActiveRange();range.setFormula(公式)；}

I read several old posts about Google Spreadsheet missing the evaluate function. There is any solution in 2016?

The easiest example.

• 'A1' contains the following string: UNIQUE(C1:C5)
• 'B1' I want to evaluate in it the unique formula written in 'A1'.

I've tried concatenating in this way: 'B1' containing ="="&A1 but the outcome is the string =UNIQUE(C1:C5). I've also tried the indirect formula.

Any suggestion to break last hopes, please?

Additional note

The aim is to write formulas in a spreadsheet and use these formulas by several other spreadsheets. Therefore, any change has to be done in one place.

解决方案

Short answer

Use a script that includes something like var formula = origin.getValue() to get the string and something like destination.setFormula(formula) to return the formula.

Explanation

As was already mentioned by the OP, Google Sheets doesn't have a EVALUATE() built-in function. A custom function can't be used because custom functions can only return one or multiple values but can't modify other cell properties.

A script triggered by a custom menu, events or from the Google Apps Script editor could be used to update the formulas of the specified cells.

Since the formulas will be kept as strings, it could be more easy to keep them in the script rather than in the spreadsheet itself.

Example

The following is a very simple script that adds the specified formula to the active range.

function addFormula() {
  var formula = '=UNIQUE(C1:C5)';
  var range = SpreadsheetApp.getActiveRange();
  range.setFormula(formula);
}

这篇关于如何在另一个单元格中使用写为字符串的公式[评估 Google 电子表格]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！