从上面的单元格获得价值 [英] Get value from the cell above

问题描述

在Google Spreadsheets中可以达到上面单元格的值吗？

含义：在一个单元格 A2 我想显示上面单元格的值，即 A1 。不是因为它是坐标，而是因为它是上面的单元格。我只需将值设置为与上面的单元格相同即可：

我们知道，值没有变化，因为参照不是这样的。我怎样才能使它相对于细胞，所以我总是选择上面的细胞什么改变？我想要的结果是这样的：

术语 relative 是当然是错误的，但我希望你明白我的观点。我 都需要通常的相对行为（所以我可以移动单元格本身，其引用将适合新的颜色和行）以及引用的行为不是指向特定的固定坐标，而是指向细胞本身的特定路径。

/ p>

  = INDIRECT（ADDRESS（ROW（） -  1，COLUMN（）））
  

COLUMN（）返回对当前列的数字引用

ROW（）返回数字参考当前行。

在这里的例子中，从行中减去1会得到前一行。这个数学可以应用于ROW（）和/或COLUMN（），但在回答你的问题时，这个公式将引用上面的单元格。

然后我们有ADDRESS（）接受一个数字行和列引用，并返回一个单元格引用作为字符串。

最后，INDIRECT（）允许你以字符串形式传递一个单元格引用，并将其作为公式进行处理。

Google Spreadsheets在您输入时为您提供帮助提示，因此在输入上述公式时应详细解释每个函数in。

Is it possible in Google Spreadsheets to reach the value of the cell just above?

Meaning: In one cell A2 I want to display the value from the cell above, that is A1. Not because it is that coordinate, but because it is the cell above. I do this by simply setting the value equal to the above cell:

If I create a new row in between those two, I get this:

As we know, no change in the values, since the reference is not relative in this way. How can I make it relative to the cell, so I always pick the cell above nomatter what is changed? I want the result to be this:

The term relative is wrong in the case of course, but I hope you get my point. I both want the usual relative behavior (so I can move the cell itself and its reference will fit to the new coloumn and row) as well as the behavior that the reference does not point towards a specific fixed coordinate but rather a specific path from the cell itself.

解决方案

You can address it like this:

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))

COLUMN() returns a numeric reference to the current column

ROW() returns a numeric reference to the current row.

In the example here, subtracting 1 from the row gives you the previous row. This math can be applied to the ROW() and/or the COLUMN(), but in answering your question, this formula will reference the cell above.

Then we have ADDRESS() which accepts a numeric row and column reference and returns a cell reference as a string.

Finally INDIRECT() allows you to pass a cell reference in as a string, and it processes it as a formula.

Google Spreadsheets gives you help hints as you type, so you should get a full explanation of each function as you type the formula above in.

这篇关于从上面的单元格获得价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！