将文本字符串转换为电子表格中的公式 [英] Convert a text string to a formula in a spreadsheet

问题描述

我正在尝试开发一个交互式电子表格，以创建预算文件的叙述.将有多种选择.一旦用户选择了一个项目，它将帮助他们计算总数.我想设置它们填充的选项框.例如，输入B1:B4将允许四个单元格.我要命名四个单元格中的每个单元格(即A，B，C，D).在参考文档中，我想编写各种公式.在某些情况下，我可能还需要(A + B)* C"，在其他情况下，我可能需要(A * B * C)"或(A + B + C)/D" ...电子表格会查找文本公式，然后我想将其转换.因此，在查找(A + B)* C"的情况下，我希望它将其转换为=(indirect(A)+ indirect(B))* indirect(C)，然后从A(这是B1)，B(这是B2)，依此类推.

I'm trying to develop an interactive spreadsheet that creates a narrative for a budget document. There will be a variety of options. Once the user selects an item, it will help them calculate the total. I want to setup option boxes that they fill in. For example, four cells will be allowed for input B1:B4. I am going to name each of the four cells (i.e. A, B, C, D). In the reference document I want to write various formulas. In some cases I might need "(A+B)*C" in another I might need "A * B * C" or "(A+B+C)/D" ... The spreadsheet would lookup the text formula, and I want to then convert it. So in the case of the lookup finding "(A+B)*C" I want it to convert it to =(indirect(A)+indirect(B))*indirect(C) which would then get the values from A (which is B1), B (which is B2) and so on.

基本上，我想使用或创建与Excel的 FORMULATEXT()函数完全相反的东西.我希望在Google表格中执行此操作，但如果需要，我愿意使用Excel.我对Google的脚本和VBA都有非常基本的了解，并且愿意在必要时创建脚本，但甚至不确定如何解决它.

Essentially, I would like to use or create something that is exactly the opposite of Excel's FORMULATEXT() function. I would prefer to do this in Google Sheets but I am willing to use Excel if I need to. I have a very basic understanding of both Google's scripting and VBA, and am willing to create if necessary, but not even sure how to tackle it.

建议?

推荐答案

我在Google Apps脚本中找到了一种方法:

I found a way to do it in Google Apps Script:

function reCalc() { 
var sheet = SpreadsheetApp.getActiveSheet();  
var src = sheet.getRange("J26");   // The cell which holds the formula
var str = src.getValue(); 
str = str.replace('A', 'indirect("OPTA")').replace('B', 'indirect("OPTB")').replace('C', 'indirect("OPTC")').replace('D', 'indirect("OPTD")').replace('ENR', 'indirect("ENR")'); 
var cell = sheet.getRange("J30");  // The cell where I want the results to be
cell.setFormula(str);              // Setting the formula.
}

感谢SpiderPig给我这个主意！

Thank you to SpiderPig for giving me the idea!

这篇关于将文本字符串转换为电子表格中的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！