Google表格 - 您如何从单元格中获取公式而不是价值？ [英] Google Sheets - how do you get the formula from a cell instead of the value?
问题描述
你如何从单元格中获取文字值（公式）而不是结果值？
举例如下：
当然，CELL（）不支持公式参数，这只是为了说明意图。我查看了函数列表，没有发现任何东西。
USE CASE：
我想引用另一行，并基于该引用从行中获取其他单元格。设置明确的行号将不起作用（例如B3 =2），因为在修改行时它不会自动更正。具体而言，我希望一行中的多个列相对于另一行中的列，并且能够在一个位置更改相关行，而无需编辑每个列。
通常，为了使一行相对于另一行，您应该将列值设置为：= A2 + 5和= B2 + 10。这意味着A列是相对行值+5，而B列是相对行值+ 10。如果要更改相对行（例如，第56行），则需要将每个列更改为= A56 + 5和= B56 + 10。如何通过编辑一个字段来实现这一点？
对于一个实际的例子，考虑一张表格是一个任务列表，每个表格可以被标记为跟随另一个以用于计算结束日期，但是你会喜欢能够改变参考任务并支持任务之间的N：1关系。
[更新]
其实，我确实有一个解决方案的具体用例。但我仍然对原始问题感到好奇：获得单元格中的值的公式。
解决方案1：
- A2：= ROW （）
- B2：Foo
- C3：= A2显示2并自动调整以便在添加/删除行时自动调整参考值
解决方案2：另一种解决方案是添加一个唯一的id列并通过id存储引用，然后使用LOOKUP（） 。
编辑：这个答案适用于问题描述中的用例，只有原生函数（无脚本）。
根据AdamL对这个问题的评论，答案是你不能从单元格获得公式的价值）。然而，对于我试图解决的实际使用案例，= ROW（A42）可用于获取对特定行的引用，该行将随着更改自动更新到行。
How do you get the literal value (the formula) from a cell instead of the result value?
EXAMPLE DESIRED:
- A2: "Foo"
- B3: "=A2" - so it displays "Foo"
- C3: "=CELL("formula", B3)" displays "=A2"
Of course, CELL() doesn't support a "formula" parameter, that's just for demonstrating the intent. I've looked over the function list and nothing jumps out at me.
USE CASE:
I would like to make a reference to another row, and based on that reference get other cells from the row. Putting an explicit row number won't work (e.g. B3 = "2"), since it will not auto-correct when rows are modified. Specifically, I would like for multiple columns in one row to be relative to columns in another row, and be able to change the relative row in one place without having to edit each of those columns.
Normally, to make one row relative to another you would put column values like this: "=A2+5" and "=B2+10". This means that A column is "relative row value +5" and B column is "relative row value + 10". If you want to change the relative row (for example, to row 56), you need to change each of the columns to "=A56+5" and "=B56+10". How to accomplish this by editing just one field?
For a practical example, consider a sheet that is a list of tasks and each one may be marked as "following" another for purposes of computing end dates, but you would like to be able to change the reference task and to support a N:1 relationship between tasks.
[Update]
Actually, I do have a solution to the specific use case. But I am still curious about the original question: getting access to the formula behind the value in a cell.
SOLUTION 1: - A2: "=ROW()" - B2: "Foo" - C3: "=A2" displays "2" and auto-adjusts to maintain reference as rows are added/removed
SOLUTION 2:
Another solution would be to add a unique "id" column and store references by id, then find the row using LOOKUP().
EDIT: This answer is for the use case in the problem description, with the requirement of doing so with only native functions (no scripting).
Based on comments from AdamL to the question, the answer is that you cannot get the formula from a cell (instead of the value).
However, for the actual use case I was trying to solve, =ROW(A42) can be used to get a reference to a specific row that will update automatically with changes to rows.
这篇关于Google表格 - 您如何从单元格中获取公式而不是价值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
