谷歌电子表格获取单元格指向的地址
我有一个如下表所示的电子表格
+---+-----------------+-----+--------------------------------+
| | A | B | C |
+---+-----------------+-----+--------------------------------+
| 1 | This is my text | =A1 | 'This should be the value "A1" |
+---+-----------------+-----+--------------------------------+
，其中 A1 是文本“这是我的文本”，B1 是函数 =A1。我需要一个 C1 函数来为我提供文本 A1，因为这是 B1 指向的单元格。 INDEX 和 间接 似乎没有给我我想要的东西。
I have an spreadsheet like the table below
+---+-----------------+-----+--------------------------------+
| | A | B | C |
+---+-----------------+-----+--------------------------------+
| 1 | This is my text | =A1 | 'This should be the value "A1" |
+---+-----------------+-----+--------------------------------+
where A1 is the text "This is my text", and B1 is the function =A1. I need a function for C1 that gives me the text A1, because that's the cell that B1 points to. INDEX and INDIRECT do not seem to give me what I'm after.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
据我所知，没有 Google 电子表格函数可以返回单元格的公式，但您可以使用脚本编辑器定义自定义函数。定义一个自定义函数公式，在给定单元格的行号和列号的情况下，该函数返回单元格的公式。然后在单元格 C1 的公式中使用自定义函数。单元格 C1 的公式将包含：
这将导致单元格 C1 的值成为字符串：
对单元格 C1 中的公式进行一些有用的改进。首先，使用
row()和column()函数根据单元格引用计算行和列：其次，剪掉
=(根据您的问题陈述，您不需要）：要定义函数公式，请转到“工具”、“脚本编辑器”，然后输入以下脚本：
I know of no Google Spreadsheet function which returns the formula of a cell, but you can define a custom function using the Script Editor. Define a custom function formula which returns the formula of a cell, given the row and column number of the cell. Then use the custom function in the formula of cell C1. The formula of cell C1 would contain:
And this would cause the value of cell C1 to be the string:
A couple of useful refinements to the formula in cell C1. First, calculate the row and column from a cell reference using the
row()andcolumn()functions:Second, trim off the
=(which, according to your problem statement, you do not want):To define the function formula, go to Tools, Script Editor, and enter the following script:
我知道这个问题被问到已经有九年了，但由于只有一个答复，而且它以“我不知道有任何 Google 电子表格函数可以返回单元格公式”开头，所以我想我应该继续，回答这个问题是因为事实上有一个函数可以返回单元格的公式。
=FORMULATEXT([CELL])
这将完全按照它的说明执行，并返回该单元格内的公式文本。从那里您可以使用标准字符串函数 LEFT、RIGHT、MID 从中提取您想要的内容。
I know it's been like nine years since this question was asked, but since there is only one response and it starts out with "I know of no Google Spreadsheet function which returns the formula of a cell", I figured I'd go ahead and answer this since there is, in fact, a function that returns the formula of a cell.
=FORMULATEXT([CELL])
This will do exactly as it says and return the text of the formula within that cell. From there you can use the standard string functions LEFT, RIGHT, MID to extract what you want out of it.